Форум пользователей MySQL

deadka · 26.03.2016 00:35:17

Доброго времени суток, Коллеги!

Есть таблица, содержащая, допустим, товары, у товара есть цена, имя, категория.

CREATE TABLE  `t` (

  `price` bigint(20) unsigned NOT NULL,

  `name` varchar(255) NOT NULL,

  `cat` int(11) DEFAULT NULL,

  KEY `cat_price` (`cat`,`price`) USING BTREE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

задача - максимально быстро выбрать самый дешевый товар из определенной категории (ID известен).
В таблице 1000000 записей, 1000 категорий, в каждой по 1000 товаров.

По логике, должен полноценно использоваться индекс cat_price в запросе

SELECT name FROM `t` use index(cat_price) WHERE cat = 10 order by price limit 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 - то типа все в порядке? )

vasya · 26.03.2016 11:01:22

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. да

deadka · 27.03.2016 13:40:30

Спасибо, добрый человек :-).

Немного изменив индексы таблицы, наткнулся на следующий непонятный момент:

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)? Такое возможно вообще или скорее бага?

rgbeast · 27.03.2016 14:08:38

Такое бывает. Можно назвать багой, но в MySQL как-то на подобные особенности EXPLAIN смотрят спокойно. Попробуй EXPLAIN EXTENDED или EXPLAIN FORMAT=JSON.

deadka · 27.03.2016 21:23:36

Ну дела...

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'у? Разные ведь планы запросов получаются.

rgbeast · 27.03.2016 21:31:00

Попробуй

 explain extended select * from t where cat = 1 order by price\G

 SHOW WARNINGS

Согласно последним рекомендациям, нужно верить EXPLAIN FORMAT=JSON.

deadka · 27.03.2016 21:36:51

Код:

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.

deadka · 27.03.2016 22:36:36

Так что же получается, что индекс все же используется не полностью? Судя по выводу 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)

rgbeast · 27.03.2016 23:22:01

Используется только часть `cat`, но оптимизатор считает, что условию cat=1 удовлетворяет 1000 строк. EXPLAIN FORMAT=JSON также может быть неточен.

deadka · 28.03.2016 00:23:16

Оптимизатор правильно считает, 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'у мы можем судить о том, что сделано на самом деле?
Вот бяда...

rgbeast · 28.03.2016 10:11:56

Красивая иллюстрация получилась. Это точно бага эксплейна и ее нужно на bugs.mysql.com

deadka · 29.03.2016 00:46:54

Ясно , спасибо большое! Отпишу.

Форум пользователей MySQL

#1 26.03.2016 00:35:17

mysql 5.7.9(windows7),keylen составного индексав случае = а потом sort

Код:

Код:

#2 26.03.2016 11:01:22

Re: mysql 5.7.9(windows7),keylen составного индексав случае = а потом sort

#3 27.03.2016 13:40:30

Re: mysql 5.7.9(windows7),keylen составного индексав случае = а потом sort

Код:

#4 27.03.2016 14:08:38

Re: mysql 5.7.9(windows7),keylen составного индексав случае = а потом sort

#5 27.03.2016 21:23:36

Re: mysql 5.7.9(windows7),keylen составного индексав случае = а потом sort

Код:

#6 27.03.2016 21:31:00

Re: mysql 5.7.9(windows7),keylen составного индексав случае = а потом sort

#7 27.03.2016 21:36:51

Re: mysql 5.7.9(windows7),keylen составного индексав случае = а потом sort

Код:

Код:

#8 27.03.2016 22:36:36

Re: mysql 5.7.9(windows7),keylen составного индексав случае = а потом sort

Код:

#9 27.03.2016 23:22:01

Re: mysql 5.7.9(windows7),keylen составного индексав случае = а потом sort

#10 28.03.2016 00:23:16

Re: mysql 5.7.9(windows7),keylen составного индексав случае = а потом sort

Код:

Код:

#11 28.03.2016 10:11:56

Re: mysql 5.7.9(windows7),keylen составного индексав случае = а потом sort

#12 29.03.2016 00:46:54

Re: mysql 5.7.9(windows7),keylen составного индексав случае = а потом sort

Board footer