（四）MySQL索引优化实战二

最新推荐文章于 2024-09-08 16:18:33 发布

俺是老鲁

最新推荐文章于 2024-09-08 16:18:33 发布

阅读量48

点赞数

分类专栏： MySQL专栏文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_38946363/article/details/132856120

版权

MySQL专栏专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文介绍了SQL分页查询的原理，优化策略，如主键自增排序、非主键字段排序的处理，以及关联SQL的优化技巧，包括NLJ和BNL算法的选择。还讨论了in和exists查询的优化，以及count(*)查询的性能比较。

摘要由CSDN通过智能技术生成

分页查询sql说明

SELECT * from tableA WHERE col1 ='xxx' LIMIT m,n;

这条sql会先读取m+n条数据，然后丢弃前面m条数据，只取后面n条数据；所以分页查询时，页码越大，速度越慢。

常见分页场景及分页技巧

1、根据主键自增且连续的主键排序分页查询

优化后：

能够按上面sql优化的前提条件：

1）主键递增且连续

2）按主键排序

2、非主键字段排序分页查询

根据字段name排序，取9001-9005

-- 常规写法
SELECT * from employees order by name LIMIT 9000,5;

-- 优化后
SELECT * from employees m INNER JOIN (SELECT id from employees ORDER BY name LIMIT 9000,5) n on m.id=n.id;

常规分页查询写法：扫描二级索引拿到主键id，再回表聚簇索引成本比全表扫描还高，故没走索引

优化后：

分析：可以通过走二级索引，取到符合条件的记录id，作为衍生表（结果集就5条记录），这时再去关联这个子查询结果，效率就很快

对于关联sql的优化

left join：左边是驱动表，右边是被驱动表

right join：右边是驱动表，左边是被驱动表

join：MySQL会自动选择数据量小作为驱动表，大表作为被驱动表

1、关联sql优化一般遵循下面两点

1）关联字段加索引：大表关联的字段一定要加索引，小表字段无所谓（驱动表会全表扫描），让关联sql做join时，尽量选择NLJ算法

2）小表驱动大表：也就是执行计划里面，先执行的一定要是小表（因为驱动表需要把数据扫描到join_buffer，如果是大表，就需要分多次扫描到join_buffer）；如果多表关联时，明确知道哪张是小表，可以用straight_join写法固定连接驱动方式，省去MySQL优化器自己判断的时间

2、MySQL的表关联常见的两种算法

示例准备说明：T1、T2表结构一样，T1表10000数据，T2表100数据，其中a字段建立了索引，b字段没有建立索引；

2.1、嵌套循环连接NLJ算法：

-- 关联字段是索引：a字段建了索引 
EXPLAIN SELECT * from T1 INNER JOIN T2 on T1.a = T2.a;

执行流程：驱动表T2表100条记录全表扫描（也就是磁盘扫描）到内存里，再逐行取T2.a去T1表走二级索引拿到主键id，回表获取数据，也就是T2每一行数据的a只需通过一次索引定位就可以取到数据，换句话说T1也要扫描100次磁盘，就可拿到关联查询的结果；

2.2、基于块的嵌套循环连接BNL算法

-- 关联字段非索引 
EXPLAIN SELECT * from T1 INNER JOIN T2 on T1.b = T2.b;

执行流程：驱动表T2表100条记录全表扫描加到内存join_buffer中，因为b字段不是索引，故内存中的T2表的b字段是无序的；再从T1表里逐行取出来去内存中join_buffer比对（每次取出一行就表示一次磁盘扫描），也就是T1的每一行都要比对100次，共需要在内存中比对10000x100 = 100W次。

3、问题：大表得关联字段没有建立索引，为什么选用BNL算法？（大表关联字段建索引，就直接用NLJ算法）

若大表关联字段没有索引，

由BNL算法示例可知：磁盘扫描T2表=100次，扫描T1表=10000次，总共扫描磁盘 = 100+10000 = 10100次，内存比对10000x100 = 100W次；

如果是NLJ算法：磁盘扫描T2表=100次，因为没有b字段不是索引，故需要遍历T1表聚集索引的叶子节点（是索引时，可以直接定位，只需要磁盘扫描一次），极端情况下，需要扫描10000次磁盘，故极端情况总共扫描磁盘 = 100x10000 = 100w次；

磁盘扫描效率远远低于内存扫描，故选择的是BNL算法

in和exists优化

-- in查询：小表应该放在in后面 
EXPLAIN SELECT * from A where id in (SELECT id from B); 
#等价于 
for(SELECT id from B){ 
    SELECT * from A where A.id = B.id 
} 

-- exists查询：exists后面放大表 
-- EXISTS (subquery)返回的是true或false 
EXPLAIN SELECT * from A where EXISTS (SELECT 1 from B where A.id=B.id); 
#等价于 
for(SELECT * from A ){ 
    SELECT * from B where A.id=B.id 
}

in操作能避免则避免，若实在避免不了，需要仔细评估in后面的集合不要超过1000个。

上一篇中可知，in查询在数据量大的情况，可能走索引，当然数据量小的时候有可能走全表扫描；也就是跟表数据量有关

count（*）查询优化

1、sql写法

-- count()查询优化 
EXPLAIN SELECT count(1) from employees; 
EXPLAIN SELECT count(*) from employees; 
EXPLAIN SELECT count(name) from employees; 
EXPLAIN SELECT count(id) from employees;

name字段有索引前提下，4个sql的执行计划一样，说明效率差不多

需要注意的是：count(字段)不会统计null值情况，count(*)会统计null值情况，也就是尽量使用count(*)