Mysql（四）（通过索引进行优化）

.挽星.

已于 2023-06-20 16:09:13 修改

阅读量730

点赞数

分类专栏： mysql 文章标签： java mysql

于 2023-06-15 14:17:06 首次发布

本文链接：https://blog.csdn.net/qq_41449402/article/details/131226820

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1、在使用索引列查询的时候不要使用表达式进行操作

在索引列上使用表达式或函数，会使索引失效。

ALTER TABLE `dev_device` ADD INDEX test(`client_type_id`);

EXPLAIN SELECT * FROM `dev_device` WHERE client_type_id = 8;

例：建立client_type_id索引列，使用client_type_id进行精确查找，type为ref，使用了非主键索引进行精确查询。

若在client_type_id列上进行表达式操作，例client_type_id + 1 = 7，索引失效，查询变成了全表扫描。

EXPLAIN SELECT * FROM `dev_device` WHERE client_type_id - 1 = 7;

2.使用索引列进行排序操作

若需要排序的列为索引列，可以减少查询时由排序带来的性能消耗，提高查询速度。

ALTER TABLE `dev_device` ADD INDEX test1(`ctime`);

EXPLAIN SELECT * FROM `dev_device` ORDER BY `ctime`;

例：在查询中出现Using filesort，说明使用了文件排序，需要消耗额外的性能来进行排序

3.union all，or，in的选择

当查询时需要用union all，or，in匹配多个行时，优先选择in。

例：使用ip进行范围查询

ALTER TABLE `dev_device` ADD INDEX test(`ip`);

使用UNION ALL进行筛选，执行计划为两个查询，需要进行两次IO，效率较低。

EXPLAIN SELECT * FROM `dev_device` WHERE `ip` = '222.57.117.76' UNION ALL SELECT * FROM `dev_device` WHERE `ip` = '139.196.3.149';

使用OR进行筛选，为一次查询，使用索引进行了范围查询。

EXPLAIN SELECT * FROM `dev_device` WHERE `ip` = '222.57.117.76' OR `ip` = '139.196.3.149';

使用IN进行筛选，为一次查询，使用索引进行了范围查询。

EXPLAIN SELECT * FROM `dev_device` WHERE `ip` IN('222.57.117.76','139.196.3.149');

4.强制类型转换会使索引失效

例：phone为char类型，为他增加索引

ALTER TABLE `org_employee` ADD INDEX test1(`phone`);

作为字符串进行查找时，用到了索引进行精确查找：

EXPLAIN SELECT * FROM `org_employee` WHERE phone = "123456789";

若强制转换为长整型，进行查找，索引失效，走了全表扫描：

EXPLAIN SELECT * FROM `org_employee` WHERE phone = 123456789;

5.慎用不等于(!= <>)

!=,<>不等于会使索引失效，无法利用索引。

EXPLAIN SELECT * FROM `dev_device` WHERE unique_id != '6D92D67E-AC19-47C7-B08F-FAB00D691509';

6.Like要当心(%)

当Mysql的模糊查询 % 在前面时，会使索引列失效，走全表扫描。

因为Mysql的底层是B+树，B+树叶子节点的索引数据是连续的(根据索引列升序排序)，因此当 % 在前面会使B+树不知道怎样去使用索引查询，从而使索引失效。

EXPLAIN SELECT * FROM `dev_device` WHERE NAME LIKE '%aa';

7.最左匹配原则

Mysql的索引底层是B+树，所有的索引都在B+树的叶子节点并且是连续的，叶子节点会按照索引建的顺序进行构建，即最左边的索引会在叶子节点的最高层并按照升序进行排序，当第一个索引的列相等时，第二个索引才会小范围排序，这样就导致查询索引会首先匹配叶子节点的第一层，如果跳过第一层直接查询第二层是不会走索引的。

ALTER TABLE `dev_device` ADD INDEX test3(`name`,`ip`,`registrant_name`);

例：建立name、ip、registrant_name的组合索引，使用name查询可以用到索引，使用registrant_name无法用到索引。

EXPLAIN SELECT * FROM `dev_device` WHERE NAME = '1f66d22d';

EXPLAIN SELECT * FROM `dev_device` WHERE registrant_name = '未关联';

8.范围查询使用组合索引

当使用组合索引进行范围查询时，若组合索引的第一个列为范围查询，后面的索引将会失效。

ALTER TABLE `aud_vas_cd_record_log` ADD INDEX test(`ctime`,`ip`,`mac`);

EXPLAIN SELECT * FROM `aud_vas_cd_record_log` WHERE ctime = '2023-06-20 06:42:56' AND ip = '182.92.233.239' AND mac = '13:e2:5a:78:2f:3b';

EXPLAIN SELECT * FROM `aud_vas_cd_record_log` WHERE ctime > '2023-06-20 06:42:56' AND ip = '182.92.233.239' AND mac = '13:e2:5a:78:2f:3b';

当组合索引所有的都为等值查找时，索引列都可以用到。

当组合索引前面的列为范围查找时，后面的索引将会失效。

9.使用or要注意

当Mysql查询时使用到了or，且其中一个不是索引列时，Mysql不会走索引，会走全表扫描。

例：unique_id和name都有索引，可以使用索引进行查询

EXPLAIN SELECT * FROM `dev_device` WHERE unique_id = '31D91F64-B020-4476-962B-E5C44F293711' OR NAME = '680e4439';

unique_id有索引，device_type_id没有索引，无法利用索引进行查询

EXPLAIN SELECT * FROM `dev_device` WHERE unique_id = '31D91F64-B020-4476-962B-E5C44F293711' OR device_type_id = 6;

10.优化深分页

对于数据量比较大的表，当使用sql查询比较深的页数时，例如在100w数据里查询第60w条数据的十条：

SELECT * FROM `aud_vas_cd_record_log` ORDER BY ctime DESC LIMIT 600000,10;

这条sql的执行顺序是，先查找出600010条数据，然后根据ctime进行排序，因为ctime是非聚簇索引，MySQL还需要拿到其他列上的数据，需要回表600010此也就是600010次IO，拿到其他列上的数据，最终返回最后的10条数据，因此，深分页慢的原因在于回表的IO次数比较多。

使用内连接进行优化：

SELECT * FROM `aud_vas_cd_record_log`
INNER JOIN(
SELECT id FROM `aud_vas_cd_record_log` ORDER BY ctime DESC LIMIT 600000,10
) AS t ON t.id = `aud_vas_cd_record_log`.id;