|
|
Задавайте вопросы, мы ответим
Вы не зашли.
Страниц: 1
Доброго времени суток, Коллеги!
Есть таблица, содержащая, допустим, товары, у товара есть цена, имя, категория.
mysql> explain SELECT * FROM `t` WHERE cat = 10 order by price limit 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: cat_price
key: cat_price
key_len: 5
ref: const
rows: 1000
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)key_len используется не полностью (вроде как), опять же рядов собирается перебрать 1000, а не 1.
mysql> explain SELECT * FROM `t` WHERE `cat` = 10 AND `price` = 20\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: cat_price
key: cat_price
key_len: 13
ref: const,const
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)тут-то 13 длина, и rows = 1.
Подскажите, кто в теме по след. пунктам плиз:
1) Почему keylen=5? paulus писал, что это может быть обманом, что на самом деле индекс используется полностью, и нужно
исходить из того, что нету надписи filesort, как-то так?
2) Нужно ли стараться избежать using where? или именно filesort плох? Если шире - каков критерий, что запрос можно еще соптимизировать?
3) Значение rows - на него нужно или не нужно обращать внимание? Analyze table делал.
4) В чем принципиальная разница между Using index и Using index condition? Не очень понял из документации.
5) Может ли служить критерием Handler_read_rnd_next? Если зафлашить статус перед запросом, дальше выполнить запрос, и посмотреть чему Handler_read_rnd_next? и Если видим, что 1, а не 1000 - то типа все в порядке? )
Неактивен
1. keylen иногда врет, видимо в 5.7 это осталось
2,4. using where - после того как хранилище данных прочитало строку и вернуло её серверу идет проверка дополнительных условий (например, индекс только на с1, а условие where c1=.. and c2=..). Бывает, что using where пишет не по делу.
Using index condition - значит доп условия проверяются на уровне хранилища.
https://mariadb.com/kb/en/mariadb/index … -pushdown/ -- хорошие иллюстрации
аналогично using where иногда пишется не по делу
Using index - из другой серии. это значит для выполнения запроса достаточно только данных индекса.
3. имхо нет
5. да
Неактивен
Спасибо, добрый человек :-).
Немного изменив индексы таблицы, наткнулся на следующий непонятный момент:
CREATE TABLE `t` (
`price` bigint(20) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`cat` int(11) DEFAULT NULL,
KEY `cat_idx` (`cat`),
KEY `price_idx` (`price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Смотрю explain на тот же запрос с сортировкой:
mysql> explain select * from t where cat = 1 order by price limit 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: index
possible_keys: cat_idx
key: price_idx
key_len: 8
ref: NULL
rows: 997
filtered: 0.10
Extra: Using where
1 row in set, 1 warning (0.00 sec)Почему выбранный key (price_idx) не указывается в possible keys (cat_idx)? Такое возможно вообще или скорее бага?
Неактивен
Такое бывает. Можно назвать багой, но в MySQL как-то на подобные особенности EXPLAIN смотрят спокойно. Попробуй EXPLAIN EXTENDED или EXPLAIN FORMAT=JSON.
Неактивен
Ну дела...
Explain extended показывает прямо скажем другой результат, нежели explain:
mysql> explain extended select * from t where cat = 1 order by price\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: cat_idx
key: cat_idx
key_len: 5
ref: const
rows: 1000
filtered: 100.00
Extra: Using index condition; Using filesort
1 row in set, 2 warnings (0.00 sec)В то же время летит warning
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future rele
ase.
Так кому же верить, explain'у или explain extended'у? Разные ведь планы запросов получаются.
Неактивен
Попробуй
Неактивен
mysql> explain extended select * from t where cat = 1 order by price\G show warnings;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ref
possible_keys: cat_idx
key: cat_idx
key_len: 5
ref: const
rows: 1000
filtered: 100.00
Extra: Using index condition; Using filesort
1 row in set, 2 warnings (0.00 sec)
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------+
| Level | Code | Message
|
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future rele
ase.
|
| Note | 1003 | /* select#1 */ select `olegabr`.`t`.`price` AS `price`,`olega
br`.`t`.`name` AS `name`,`olegabr`.`t`.`cat` AS `cat` from `olegabr`.`t` where (
`olegabr`.`t`.`cat` = 1) order by `olegabr`.`t`.`price` |
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------+
2 rows in set (0.00 sec)mysql> explain format=json select * from t where cat = 1 order by price\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1200.00"
},
"ordering_operation": {
"using_filesort": true,
"table": {
"table_name": "t",
"access_type": "ref",
"possible_keys": [
"cat_idx"
],
"key": "cat_idx",
"used_key_parts": [
"cat"
],
"key_length": "5",
"ref": [
"const"
],
"rows_examined_per_scan": 1000,
"rows_produced_per_join": 1000,
"filtered": "100.00",
"index_condition": "(`olegabr`.`t`.`cat` <=> 1)",
"cost_info": {
"read_cost": "1000.00",
"eval_cost": "200.00",
"prefix_cost": "1200.00",
"data_read_per_join": "765K"
},
"used_columns": [
"price",
"name",
"cat"
]
}
}
}
}Так, что, видимо, используется cat_idx.
Неактивен
Так что же получается, что индекс все же используется не полностью? Судя по выводу explain'a.
Если же полностью используется, то как это понять/определить?
Смущает
"rows_examined_per_scan": 1000,
"rows_produced_per_join": 1000,
ведь по идее должен был моментально найти нужную запись (.
mysql> show create table t;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------+
| Table | Create Table
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`price` bigint(20) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`cat` int(11) DEFAULT NULL,
KEY `cat_idx` (`cat`),
KEY `price_idx` (`price`),
KEY `cat_price` (`cat`,`price`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> explain format=json select * from t use index(cat_price) where cat = 1 order by price limit 1\Gshow warnings;
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1200.00"
},
"ordering_operation": {
"using_filesort": false,
"table": {
"table_name": "t",
"access_type": "ref",
"possible_keys": [
"cat_price"
],
"key": "cat_price",
"used_key_parts": [
"cat"
],
"key_length": "5",
"ref": [
"const"
],
"rows_examined_per_scan": 1000,
"rows_produced_per_join": 1000,
"filtered": "100.00",
"index_condition": "(`olegabr`.`t`.`cat` <=> 1)",
"cost_info": {
"read_cost": "1000.00",
"eval_cost": "200.00",
"prefix_cost": "1200.00",
"data_read_per_join": "765K"
},
"used_columns": [
"price",
"name",
"cat"
]
}
}
}
}
1 row in set, 1 warning (0.00 sec)
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------+
| Level | Code | Message
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `olegabr`.`t`.`price` AS `price`,`olegabr`.`t`.`name` AS `name`,`olegabr`.`t`.`cat` AS `cat` from `olegabr`.`t` USE INDEX (`cat_price`) where
(`olegabr`.`t`.`cat` = 1) order by `olegabr`.`t`.`price` limit 1 |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------+
1 row in set (0.00 sec)Неактивен
Используется только часть `cat`, но оптимизатор считает, что условию cat=1 удовлетворяет 1000 строк. EXPLAIN FORMAT=JSON также может быть неточен.
Неактивен
Оптимизатор правильно считает, cat=1 - там правда 1000 таких строк.
А что из этого следует?
Но как-то странно получается, то есть судя по explain субд вроде собирается выполнять запрос одним способом, то есть используя только первую часть индекса (cat) а выполняет другим, судя по Handler_read_next.
Собственно:
1) Используем осознанно только индекс по категории, то есть после спуска по индексу к нужной категории сканируем 1000 строчек с товарами.
mysql> explain format=json select * from t force index(cat_idx) where cat = 1 order by price limit 1;flush status;reset query cache;select * from t force index(cat_idx) where cat =
1 order by price limit 5;show status like 'handler%';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1200.00"
},
"ordering_operation": {
"using_filesort": true,
"table": {
"table_name": "t",
"access_type": "ref",
"possible_keys": [
"cat_idx"
],
"key": "cat_idx",
"used_key_parts": [
"cat"
],
"key_length": "5",
"ref": [
"const"
],
"rows_examined_per_scan": 1000,
"rows_produced_per_join": 1000,
"filtered": "100.00",
"index_condition": "(`olegabr`.`t`.`cat` <=> 1)",
"cost_info": {
"read_cost": "1000.00",
"eval_cost": "200.00",
"prefix_cost": "1200.00",
"data_read_per_join": "765K"
},
"used_columns": [
"price",
"name",
"cat"
]
}
}
}
} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+-------+------+------+
| price | name | cat |
+-------+------+------+
| 1000 | 2002 | 1 |
| 1001 | 2004 | 1 |
| 1002 | 2006 | 1 |
| 1003 | 2008 | 1 |
| 1004 | 2010 | 1 |
+-------+------+------+
5 rows in set (0.01 sec)
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 1000 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)Handler_read_next = 1000
то есть как бе все по логике, все объяснимо.
В то же время если заставить его использовать правильный индекс, то вот что выходит
mysql> explain format=json select * from t force index(cat_price) where cat = 1 order by price limit 1;flush status;reset query cache;select * from t force index(cat_price) where cat = 1 order by price limit 5;show status like 'handler%';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-+
| EXPLAIN
|
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-+
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1200.00"
},
"ordering_operation": {
"using_filesort": false,
"table": {
"table_name": "t",
"access_type": "ref",
"possible_keys": [
"cat_price"
],
"key": "cat_price",
"used_key_parts": [
"cat"
],
"key_length": "5",
"ref": [
"const"
],
"rows_examined_per_scan": 1000,
"rows_produced_per_join": 1000,
"filtered": "100.00",
"index_condition": "(`olegabr`.`t`.`cat` <=> 1)",
"cost_info": {
"read_cost": "1000.00",
"eval_cost": "200.00",
"prefix_cost": "1200.00",
"data_read_per_join": "765K"
},
"used_columns": [
"price",
"name",
"cat"
]
}
}
}
} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-+
1 row in set, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+-------+------+------+
| price | name | cat |
+-------+------+------+
| 1000 | 2002 | 1 |
| 1001 | 2004 | 1 |
| 1002 | 2006 | 1 |
| 1003 | 2008 | 1 |
| 1004 | 2010 | 1 |
+-------+------+------+
5 rows in set (0.00 sec)
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 4 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)Судя по explain использует только первую часть индекса, с категорией.
В то же время, если исходить из Handler_read_next = 4
явно выбрал нужные товары исходя из индекса по price, иначе как бы он выбрал нужные товары (отсортированные то есть), не перебрав всю тысячу? Плюс filesort во втором случае = false, а в первом-то true.
Итого, получается explain обманывает во всех случаях? ((
А СУБД поступает по своему, и лишь по handler'у мы можем судить о том, что сделано на самом деле?
Вот бяда...
Неактивен
Красивая иллюстрация получилась. Это точно бага эксплейна и ее нужно на bugs.mysql.com
Неактивен
Ясно 
, спасибо большое! Отпишу.
Неактивен
Страниц: 1
