Почему MySQL не использует мои индексы при выполнении моего запроса?

Question

Почему MySQL не использует мои индексы при выполнении моего запроса?

Я использую MySQL 5.5.37. Это не вариант для обновления в настоящее время. У меня есть этот стол

CREATE TABLE `my_classroom` (
  `ID` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `CLASSROOM_NAME` varchar(100) COLLATE utf8_bin NOT NULL,
  `ACCESS_CODE_ID` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `TEACHER_ACCESS_CODE_ID` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UK_my_classroom` (`ACCESS_CODE_ID`),
  UNIQUE KEY `UK2_my_classroom` (`TEACHER_ACCESS_CODE_ID`),
  KEY `FK2_my_classroom` (`CLASSROOM_SCHEDULE_ID`),
  CONSTRAINT `FK3_my_classroom` FOREIGN KEY (`TEACHER_ACCESS_CODE_ID`) REFERENCES `my_reg_code` (`ID`) ON UPDATE NO ACTION,
  CONSTRAINT `FK_my_classroom` FOREIGN KEY (`ACCESS_CODE_ID`) REFERENCES `my_reg_code` (`ID`) ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Обратите внимание на индексы в столбцах ACCESS_CODE_ID и TEACHER_ACCESS_CODE_ID. Однако, когда этот запрос выполняется (он генерируется Hibernate, поэтому он выглядит немного странно), обратите внимание на происходящее полное сканирование таблицы…

mysql> explain select davesclass0_.id as id1_7_, davesclass0_.ACCESS_CODE_ID as ACCESS_13_7_,
    davesclass0_.CLASSROOM_NAME as CLASSROO7_7_, davesclass0_.TEACHER_ACCESS_CODE_ID as TEACHER15_7_
from my_classroom davesclass0_
left outer join my_reg_code myregcode1_ on davesclass0_.ACCESS_CODE_ID=myregcode1_.ID
left outer join my_reg_code accesscode2_ on davesclass0_.TEACHER_ACCESS_CODE_ID=accesscode2_.ID
where myregcode1_.ACCESS_CODE='ABCDEF' or accesscode2_.ACCESS_CODE='ABCDEF';
+----+-------------+--------------+--------+---------------+---------+---------+-------------------------------------------+---------+-------------+
| id | select_type | table        | type   | possible_keys | key     | key_len | ref                                       | rows    | Extra       |
+----+-------------+--------------+--------+---------------+---------+---------+-------------------------------------------+---------+-------------+
|  1 | SIMPLE      | davesclass0_ | ALL    | NULL          | NULL    | NULL    | NULL                                      | 1914867 |             |
|  1 | SIMPLE      | myregcode1_  | eq_ref | PRIMARY       | PRIMARY | 98      | my_db.davesclass0_.ACCESS_CODE_ID         |       1 |             |
|  1 | SIMPLE      | accesscode2_ | eq_ref | PRIMARY       | PRIMARY | 98      | my_db.davesclass0_.TEACHER_ACCESS_CODE_ID |       1 | Using where |
+----+-------------+--------------+--------+---------------+---------+---------+-------------------------------------------+---------+-------------+

Есть ли способ переписать это, чтобы получить те же результаты, но MySQL понимает, использовать ли индексы в таблице my_classroom?

Изменить: В ответ на предложение lsemi, MySql объяснить план...

mysql> explain select davesclass0_.id as id1_7_, davesclass0_.ACCESS_CODE_ID as ACCESS_13_7_, davesclass0_.TEACHER_ACCESS_CODE_ID as TEACHER15_7_ from my_classroom davesclass0_ where davesclass0_.ACCESS_CODE_ID in (select myregcode1_.ID from my_reg_code myregcode1_ where myregcode1_.ACCESS_CODE='ABCDEF') or davesclass0_.TEACHER_ACCESS_CODE_ID in (select myregcode2_.ID from my_reg_code myregcode2_ where myregcode2_.ACCESS_CODE='ABCDEF');
+----+--------------------+--------------+------+---------------+------+---------+------+--------+-----------------------------------------------------+
| id | select_type        | table        | type | possible_keys | key  | key_len | ref  | rows   | Extra                                               |
+----+--------------------+--------------+------+---------------+------+---------+------+--------+-----------------------------------------------------+
|  1 | PRIMARY            | davesclass0_ | ALL  | NULL          | NULL | NULL    | NULL | 216280 | Using where                                         |
|  3 | DEPENDENT SUBQUERY | NULL         | NULL | NULL          | NULL | NULL    | NULL |   NULL | Impossible WHERE noticed after reading const tables |
|  2 | DEPENDENT SUBQUERY | NULL         | NULL | NULL          | NULL | NULL    | NULL |   NULL | Impossible WHERE noticed after reading const tables |
+----+--------------------+--------------+------+---------------+------+---------+------+--------+-----------------------------------------------------+

Изменить 2: Объяснить план для

mysql> explain select davesclass0_.id as id1_7_, davesclass0_.ACCESS_CODE_ID as ACCESS_13_7_, davesclass0_.TEACHER_ACCESS_CODE_ID as TEACHER15_7_ from mY_classroom davesclass0_ where davesclass0_.ACCESS_CODE_ID in (select myregcode1_.ID from my_reg_code myregcode1_ where myregcode1_.ACCESS_CODE='0008F0'); 
+----+--------------------+--------------+-------+---------------------------+-------------------+---------+-------+--------+-------------+
| id | select_type        | table        | type  | possible_keys             | key               | key_len | ref   | rows   | Extra       |
+----+--------------------+--------------+-------+---------------------------+-------------------+---------+-------+--------+-------------+
|  1 | PRIMARY            | davesclass0_ | ALL   | NULL                      | NULL              | NULL    | NULL  | 216280 | Using where |
|  2 | DEPENDENT SUBQUERY | myregcode1_ | const | PRIMARY,UK_my_reg_code | UK_my_reg_code | 98      | const |      1 | Using index |
+----+--------------------+--------------+-------+---------------------------+-------------------+---------+-------+--------+-------------+
2 rows in set (0.00 sec)

mysql> explain select davesclass0_.id as id1_7_, davesclass0_.ACCESS_CODE_ID as ACCESS_13_7_, davesclass0_.TEACHER_ACCESS_CODE_ID as TEACHER15_7_ from mY_classroom davesclass0_ where davesclass0_.ACCESS_CODE_ID = 'ABCEF';
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

3

sql mysql indexing full-table-scan

Источник

user1235929 06 окт '16 в 19:29

4 ответа

Другие вопросы по тегам sql mysql indexing full-table-scan

user1428679 06 окт '16 в 19:47 2016-10-06 19:47 · Answer 1 · 2016-10-06 19:47

Примечание. Я предполагаю, что вам нужны только те записи, которые совпадают, и это число мало. В противном случае вы читаете все, и поэтому индексы бесполезны. Таким образом, MySQL не использует их правильно.

Согласно этой гипотезе, я бы начал переписывать эти СОЕДИНЕНИЯ в виде прямых СОЕДИНЕНИЙ (а не ВЛЕВОЙ СОЕДИНЕНИЯ) и проверять наличие индекса, такого как

CREATE INDEX my_reg_code_ndx ON my_reg_code(ACCESS_CODE);

Тогда, возможно, вы могли бы использовать один JOIN (результаты могут быть немного различны, если для одной записи и доступ, и доступ учителя установлены на идентификатор ABCDEF):

JOIN my_reg_code AS mrc ON (mrc.ID = ACCESS_CODE_ID OR mrc.ID = TEACHER_ACCESS_CODE_ID)

Или вы можете также переписать WHERE как другой запрос:

SELECT ... FROM my_classroom AS mc
WHERE mc.ACCESS_CODE_ID IN 
   (SELECT ID from my_reg_code WHERE ACCESS_CODE='ABCDEF')
OR mc.TEACHER_ACCESS_CODE_ID IN (...)

Обычно MySQL выполняет расчет перед выполнением каждого запроса, и этот расчет основан на структуре запроса и ограничениях. Это означает, что могут выполняться некоторые условия, что MySQL не может рассчитать в течение нескольких миллисекунд, выделенных для первоначального исследования запроса.

Одним из источников этой информации является команда ANALYZE, которая обновляет статистику индекса.

Но этого может быть недостаточно, потому что если вы делаете запрос, как

SELECT * FROM table WHERE indexedfield = value

некоторая оценка мощности поля может быть дана на основе значения; если вы делаете вместо

SELECT * FROM table WHERE indexfield = FUNCTION(value)

или же

SELECT * FROM table WHERE indexfield = SELECT(...)

тогда возможно, что MySQL не запустит функцию или запрос, и, следовательно, не сможет фактически выполнить анализ внешнего SELECT. При фактическом выполнении запроса функция или SELECT преобразуются в набор значений, а затем MySQL запускает анализ внешнего запроса и использует индекс, или нет.

Таким образом, сложный запрос (не основанный на JOIN) может использовать ожидаемый вами индекс, тот, который вы не ожидаете, или вообще никакого.

Введите подсказку

Вы можете предложить MySQL использовать определенный индекс:

SELECT * FROM my_classroom AS mc  USE INDEX (indexToUse)
    WHERE mc.ACCESS_CODE_ID IN (...) ...

Вы также можете использовать FORCE вместо USE.

user722263 06 окт '16 в 19:36 2016-10-06 19:36 · Answer 2 · 2016-10-06 19:36

Предполагая, что вы действительно всегда хотите каждую запись из my_classroom, даже если он не имеет соответствующих записей из одного из двух левых соединений, ваш запрос никогда не будет использовать индекс для ограничения my_classroom таблица псевдонимов davesclass0_

Это потому что:

вы используете его в качестве левой части левого соединения
ничто в предложении WHERE не использует индексированный столбец my_classroom ограничить результаты от него (из-за or)

Простое решение - разбить его на 2 запроса и использовать UNION, как упоминалось в другом посте.

user1920758 06 окт '16 в 19:33 2016-10-06 19:33 · Answer 3 · 2016-10-06 19:33

Проблема в этом состоянии:

myregcode1_.ACCESS_CODE='ABCDEF' or accesscode2_.ACCESS_CODE='ABCDEF';

Перепишите запрос, используя UNION:

select davesclass0_.id as id1_7_, davesclass0_.ACCESS_CODE_ID as ACCESS_13_7_,
    davesclass0_.CLASSROOM_NAME as CLASSROO7_7_, davesclass0_.TEACHER_ACCESS_CODE_ID as TEACHER15_7_
from my_classroom davesclass0_
left outer join my_reg_code myregcode1_ on davesclass0_.ACCESS_CODE_ID=myregcode1_.ID
left outer join my_reg_code accesscode2_ on davesclass0_.TEACHER_ACCESS_CODE_ID=accesscode2_.ID
where myregcode1_.ACCESS_CODE='ABCDEF';

UNION

select davesclass0_.id as id1_7_, davesclass0_.ACCESS_CODE_ID as ACCESS_13_7_,
    davesclass0_.CLASSROOM_NAME as CLASSROO7_7_, davesclass0_.TEACHER_ACCESS_CODE_ID as TEACHER15_7_
from my_classroom davesclass0_
left outer join my_reg_code myregcode1_ on davesclass0_.ACCESS_CODE_ID=myregcode1_.ID
left outer join my_reg_code accesscode2_ on davesclass0_.TEACHER_ACCESS_CODE_ID=accesscode2_.ID
where accesscode2_.ACCESS_CODE='ABCDEF';

user1766831 07 окт '16 в 01:21 2016-10-07 01:21 · Answer 4 · 2016-10-07 01:21

OR -> UNION DISTINCT
Избавляться от LEFT
Избавиться от ненужного JOINs
Сокращенные псевдонимы (для меньшего количества беспорядка)
Добавьте несколько индексов

Что-то вроде:

select  c.id as id1_7_, c.ACCESS_CODE_ID as ACCESS_13_7_,
        c.CLASSROOM_NAME as CLASSROO7_7_, c.TEACHER_ACCESS_CODE_ID as TEACHER15_7_
    from  my_classroom AS c
    JOIN  my_reg_code AS r  ON c.ACCESS_CODE_ID = r.ID
    where  r.ACCESS_CODE='ABCDEF'
UNION  DISTINCT 
select  c.id as id1_7_, c.ACCESS_CODE_ID as ACCESS_13_7_,
        c.CLASSROOM_NAME as CLASSROO7_7_, c.TEACHER_ACCESS_CODE_ID as TEACHER15_7_
    from  my_classroom AS c
    JOIN  my_reg_code AS a  ON c.TEACHER_ACCESS_CODE_ID = a.ID
    where  a.ACCESS_CODE='ABCDEF';

Индексы:

my_reg_code:   INDEX(ACCESS_CODE, ID)  -- (composite)
my_classroom:  INDEX(ACCESS_CODE_ID), INDEX(TEACHER_ACCESS_CODE_ID)  -- (separate)