MySQL - поиск оптимального индекса для запроса

#mysql #sql #select #query-optimization

#mysql #sql #выберите #оптимизация запросов

Вопрос:

У меня есть запрос: SELECT DISTINCT a1, a2, a3 FROM t WHERE a4 = '' ORDER BY a1, a5

и индекс: (a4, a1, a5, a2, a3)

Объяснение запроса: Using where; Using index; Using temporary

Какой порядок полей в индексе является оптимальным или, возможно, запрос можно оптимизировать?

UPD

EXPLAIN FORMAT=JSON SELECT ... :

     {
     "query_block": {
       "select_id": 1,
       "temporary_table": {
         "function": "buffer",
         "table": {
           "table_name": "t",
           "access_type": "range",
           "possible_keys": ["my_index"],
           "key": "my_index",
           "key_length": "92",
           "used_key_parts": ["a4"],
           "rows": 113479,
           "filtered": 100,
           "attached_condition": "(t.a4 = '')",
           "using_index": true
         }
       }
     }

1. Без DISTINCT , я думаю, ваш индекс является лучшим. Пожалуйста, предоставьте EXPLAIN FORMAT=JSON SELECT ...

2. @RickJames Я добавил информацию к вопросу

3. Сколько строк вы получаете без DISTINCT ? ( SELECT COUNT(*) WHERE a4=''; )

4. @RickJames около 120 000

5. Звучит так, как будто DISTINCT in не нужен?

Ответ №1:

Для вашего запроса, казалось бы, оптимальный индекс будет:

 (a4, a1, a2, a3, a5)
-^ for the where clause
-----^ returned in the select and perhaps used for the select distinct
-----------------^ covers the query so the data pages are not fetched

Тем не менее, вашему запросу все равно потребуется отсортировать результаты. Я не думаю, что есть способ устранить это с помощью индекса.

1. Я добавил ваш индекс, но mysql выбирает из двух моих. Запрос объясняется вашим индексом: Using where; Using index; Using temporary; Using filesort

2. @Vv . . . . В этом случае MySQL не знает, как использовать индекс для агрегирования, и поэтому они эквивалентны. Другие базы данных имеют более умные оптимизаторы.

3. @Vv. — Вы рассчитали время с этим индексом? Ответ: что EXPLAIN FORMAT=JSON говорит об этом?

Ответ №2:

Имеющийся у вас индекс — это лучшее, что вы можете получить для этого запроса.

Модификатор DISTINCT запроса заставляет запрос использовать временную таблицу для накопления отдельных строк.

Но, по крайней мере, вашему запросу не нужно читать таблицу. Он получает все необходимые столбцы, считывая только индекс. Это Using index примечание, которое вы видите в ОБЪЯСНЕНИИ. Это преимущество называется «покрывающим индексом».

Вы не видите Using filesort , когда он использует ваш индекс, потому что оптимизатор полагается на чтение индекса в том порядке (a1, a5) , в котором вы хотели (учитывая, что все строки в противном случае привязаны к одному значению (a4) . Поэтому не нужно выполнять какую-либо дополнительную сортировку результата.