记一次，不亦云，生产mysql索引失效实查[in]

最新推荐文章于 2024-08-13 04:15:37 发布

ckx0709

最新推荐文章于 2024-08-13 04:15:37 发布

阅读量1.2k

点赞数 1

本文链接：https://blog.csdn.net/ckx178/article/details/119219116

版权

SQL 索引 IN操作符性能优化数据查询

关键词由CSDN通过智能技术生成

本次生产中一个数据导出的功能，在一个月的数据量导出过程中，耗时30+-s，让人难以接受。于是对代码进行了逻辑分析，最后到SQL，于是对多场景进行了测试：

这一次颠覆了我以前的不少sql语句中迫使检索放弃索引的概念：

１、in ：只有当in执行的column是索引列，且in元素>1时，才会导致索引的失效。

场景1：

脑海概念：SQL语句中使用ｉｎ会迫使SQL放弃索引进行全表扫描。

实际：如果ｉｎ针对的column并非索引，那么是不会导致索引失效：

sql:

 explain SELECT hd.id,hd.create_time,
       hd.name,
       hd.phone,
       hd.sex,
       hd.nationality,
       hd.temprature,
       hd.contact,
       hd.test_time,
       case when hd.idcard = '' then '护照' else '身份证' end            idcardtype,
       case when hd.idcard = '' then hd.passport else hd.idcard end idcard,
       oe.name                                                      office_name
FROM health_data hd
         left join office oe on oe.id = hd.office_id
WHERE (test_time > 1 and hd.del_flag = 0 and hd.create_time between '2021-05-01 00:00:00' and '2021-06-02 00:00:00' )
  AND `hd`.`office_id` IN
      ('0215e9e09572476abb1e24a467153c40', '042463e0bb2d47e3bbea0887f9dce5bc', '047822b0f0974667894616eb626eb33f',
       '0d0b9f4f86df11eb9638000c291b8c65', '0d7a3df35e2e469e96ae94a58f6b1c91', '13e793c5844e43efa0620b1cef22cb87',
       '178a63ef28104066a331a7027be7c4e8', '1b3e632bc4324a9c8ac1930988f294af', '1fb44a2f5e46481d839d0f5219672aff',
       '1fdec482588644c0bdf45d544075de73', '237bcec4dd29447c8bb9a1ef38f7803b', '303ed648689d45c6892e82ed2d38953d',
       '3e82c0e89cb94065b694d198e685d8bc', '3fc5afe9ee1e4cfdbf95c1e407d235f7', '4cb755a5956646449fee6bc1ec1e0523',
       '4d97bebb06904531a97a2478275c22b9', '4f4525e7168b4d8984107840061ebc27', '5f853e59913f41ddad0aa42576678efa',
       '6127975f80824a07b900a1d6044ce005', '6f6a56f6e7464f268ed8f57de27be792', '824285ae80ef4f97aaf027c0175e1e94',
       '8a9fbccb00614817bdb4b3f4dad64d84', '8e7534ea9dee46a2b800577f67f7366d', '93a353bd755b4bd4b0e8eb7ceabc4a1d',
       'a2ccf07acd6540debbb1c7a5d8f7b3ac', 'aa97a17e869b41bcaa4c924b8567ca66', 'b1f8db0c8d754839a18a7a9d78935de7',
       'b7a65457d9be4f5f97812f2e199d1472', 'bb78787b466d468580f9e08a7ee78a55', 'bbf9f0eca1e04bb1986f266b48a8ffb7',
       'dd7b7bdf9e7346fca88be12c25d3ff41', 'ec3639b8770742ac9e6099864772ea6c', 'ee57409469554c43bedef11284e8ae3a',
       'f10ded3929b04161a89dcd4f551f057f', 'fbfa91c0dfe44920856847c447baf081')
ORDER BY hd.create_time desc ;

索引在【test_time】时

index index_test
	on health_data (test_time)

explain：

从执行计划可以看到使用到了索引，读取行数：148

场景2：

将索引建在in所在列，且in元素>1：

未使用索引，读取行数1015071

场景3：

将索引建在in所在列，且in元素=1：

使用到到索引，这是为什么呢？看执行计划：

select `community`.`hd`.`id`                                                 AS `id`,
       `community`.`hd`.`create_time`                                        AS `create_time`,
       `community`.`hd`.`name`                                               AS `name`,
       `community`.`hd`.`phone`                                              AS `phone`,
       `community`.`hd`.`sex`                                                AS `sex`,
       `community`.`hd`.`nationality`                                        AS `nationality`,
       `community`.`hd`.`temprature`                                         AS `temprature`,
       `community`.`hd`.`contact`                                            AS `contact`,
       `community`.`hd`.`test_time`                                          AS `test_time`,
       (case when (`community`.`hd`.`idcard` = '') then '护照' else '身份证' end) AS `idcardtype`,
       (case
            when (`community`.`hd`.`idcard` = '') then `community`.`hd`.`passport`
            else `community`.`hd`.`idcard` end)                              AS `idcard`,
       `community`.`oe`.`name`                                               AS `office_name`
from `community`.`health_data` `hd`
         left join `community`.`office` `oe` on ((`community`.`oe`.`id` = `community`.`hd`.`office_id`))
where ((`community`.`hd`.`test_time` > 1) and (`community`.`hd`.`del_flag` = 0) and
       (`community`.`hd`.`create_time` between '2021-05-01 00:00:00' and '2021-06-02 00:00:00') and
       (`community`.`hd`.`office_id` = '0215e9e09572476abb1e24a467153c40'))
order by `community`.`hd`.`create_time` desc

当sql中使用了in，但in的元素数量为1时，sql执行会自动将in转换为=处理。因此并不会导致索引的失效。