Задавайте вопросы, мы ответим
Вы не зашли.
Страниц: 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