为什么mysql在运行我的查询时不使用我的索引？

lhcgjxsq 于 2021-06-25 发布在 Mysql

关注(0)|答案(4)|浏览(641)

我正在使用mysql 5.5.37。现在不能升级。我有这张table

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\u code\u id和teacher\u access\u code\u 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\ u教室表上的索引？

**编辑：**针对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 |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+

sql mysql indexing full-table-scan

来源：https://stackoverflow.com/questions/39904206/why-isnt-mysql-using-my-indexes-when-running-my-query

4条答案

按热度按时间

vq8itlhq1#

问题是在这种情况下：

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';

赞(0）回复(0）举报 2021-06-25

4ktjp1zp2#

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)

赞(0）回复(0）举报 2021-06-25

tcomlyy63#

注意：我假设您只需要那些匹配的记录，并且这个数字很小。否则，您将读取所有内容，因此索引是无用的。因此，mysql不使用它们是正确的。
在这种假设下，我将开始将这些连接重写为直连接（而不是左连接），并验证是否存在如下索引

CREATE INDEX my_reg_code_ndx ON my_reg_code(ACCESS_CODE);

然后，您可以使用单个连接（如果单个记录的access和teacher access都设置为def的id，则结果可能会有细微的不同）：

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将运行外部查询分析并使用索引，或者不使用索引。
因此，一个复杂的查询（不是基于连接的）可能会使用您期望的索引、您不期望的索引或根本不使用索引。

输入提示

您可以建议mysql使用特定索引：

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

你也可以用武力代替武力。

赞(0）回复(0）举报 2021-06-25

t5zmwmid4#

假设你真的一直想要 my_classroom ，即使它没有来自两个左联接之一的相应条目，您的查询也永远不会使用索引来限制 my_classroom 别名表 davesclass0_ 这是因为：
你用它作为左连接的左手边
where子句中没有使用 my_classroom 限制结果（因为 or )
简单的解决方案是将其分成两个查询，并使用另一篇文章中提到的联合。

赞(0）回复(0）举报 2021-06-25

我来回答

为什么mysql在运行我的查询时不使用我的索引？

4条答案

输入提示

相关问题

热门标签

最新问答