Блог

В таблице около 20 тыс. строк и следующий код создания:

 CREATE TABLE `inventory` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `TID` int(11) DEFAULT NULL,
      `RID` int(11) DEFAULT NULL,
      `CID` int(11) DEFAULT NULL,
      `value` text COLLATE utf8_unicode_ci,
      PRIMARY KEY (`ID`),
      KEY `index_TID_CID_value` (`TID`,`CID`,`value`(25))
    );
  

и это результат запроса explain

 mysql> explain select rowID from inventory where TID=4 and CID=28 and value=3290843588097;
 ---- ------------- ------------ ------ ------------------------ ----------------------- --------- ------------- ------ ------------- 
| id | select_type | table      | type | possible_keys          | key                   | key_len | ref         | rows | Extra       |
 ---- ------------- ------------ ------ ------------------------ ----------------------- --------- ------------- ------ ------------- 
|  1 | SIMPLE      | inventory  | ref  | index_TID_CID_value    | index_TID_CID_value   | 10      | const,const | 9181 | Using where |
 ---- ------------- ------------ ------ ------------------------ ----------------------- --------- ------------- ------ ------------- 
1 row in set (0.00 sec)
  

Комбинация TID = 4 и CID = 28 содержит около 13 тыс. строк в таблице.

Мои вопросы:

1. Почему результат explain сообщает мне, что для получения конечного результата будет проверено около 9 тыс. строк?

2. Почему столбец ref отображается только const,const с тех пор, как 3 столбца включены в многоколоночный индекс, которого не должно ref быть const,const,const ?

Обновление 7 октября 2016

Запрос:

 select rowID from inventory where TID=4 and CID=28 and value=3290843588097;
  

Я запустил его около 10 раз и взял время последних пяти (они были одинаковыми)

• Нет индекса — 0,02 секунды
• Индекс (TID, CID) — 0,03 секунды
• Индекс (TID, CID, значение) — 0,00 секунды

Также тот же запрос explain выглядит по-другому сегодня, как??обратите внимание, что ключ len изменился на 88 , а ссылка изменилась на const,const,const , также строки для проверки сократились до 2 .

 mysql> explain select rowID from inventory where TID=4 and CID=28 and value='3290843588097';
 ---- ------------- ----------- ------ ---------------------- --------------------- --------- ------------------- ------ ------------- 
| id | select_type | table     | type | possible_keys        | key                 | key_len | ref               | rows | Extra       |
 ---- ------------- ----------- ------ ---------------------- --------------------- --------- ------------------- ------ ------------- 
|  1 | SIMPLE      | inventory | ref  | index_TID_CID_value  | index_TID_CID_value | 88      | const,const,const |    2 | Using where |
 ---- ------------- ----------- ------ ---------------------- --------------------- --------- ------------------- ------ ------------- 
1 row in set (0.04 sec)
  

Чтобы явно ответить на ваши вопросы.

1. План объяснения дает вам запрос ~ 9 тыс. строк из-за того, что движку необходимо выполнить поиск по дереву индексов, чтобы найти идентификаторы строк, соответствующие вашим критериям where-предложения. Индекс будет отображать каждую возможную комбинацию значений столбцов индекса в список идентификаторов строк, связанных с этой комбинацией. По сути, движок ищет эти комбинации, чтобы найти правильную; это делается путем сканирования комбинации, отсюда и сумма ~ 9 тыс.

2. Поскольку ваш критерий where-предложения включает все три столбца индекса, движок оптимизирует поиск, используя индекс для первых двух столбцов, а затем замыкая третий столбец и получая все результаты rowID для этой комбинации.

В вашем конкретном случае использования я предполагаю, что вы хотите оптимизировать производительность поиска. Я бы рекомендовал вам создать только индекс для TID и CID (не значение). Причина этого в том, что в настоящее время у вас есть только 2 комбинации этих значений из ~ 20 тыс. записей. Это означает, что, используя индекс всего с 2 столбцами, движок сможет почти сразу вырезать половину записей при выполнении поиска по всем трем значениям. (Все это предполагает, что этот индекс будет применен к таблице с гораздо большим набором данных.) Поскольку ваши показатели основаны на меньшем наборе данных, вы можете не видеть порядок различий в производительности между использованием индекса и нет.