MySQL索引优化实战二

分页查询优化

很多时候我们业务中实现分页功能时可能会用如下SQL来实现：

select * from employees LIMIT 10000,10

表示从表中中区从10001行开始的10行记录，看似只查了10条记录，但是这条SQL是先读取10010条记录，然后抛弃前10000条记录，然后读到后面10条想要的数据，因此要查询一张大表比较靠后的数据，执行效率是很低的。

1、根据自增且连续的主键排序的分页查询
select * from employees LIMIT 90000,5

在这里插入图片描述
在这里插入图片描述

select * from employees where id>90000 limit 5

在这里插入图片描述
在这里插入图片描述
改写后的SQL走了索引，且看执行时间，改写后的时间更快。
但是这种方式在很多场景中不适用，因为表中某些数据被删后造成主键空缺，导致结果不一致。
上面改写必须满足两个条件：

  • 主键自增且连续
  • 结果是按照主键排序的
2、根据非主键字段排序的分页查询
select * from employees order by name  LIMIT 90000,5

在这里插入图片描述

explain select * from employees order by name  LIMIT 90000,5

在这里插入图片描述

没有使用索引，是因为：扫描整个扫一年病查找到没索引的行比扫描全表的成本更高。

如何优化呢？
关键是让排序返回的字段尽可能的少，所以可以让排序和分页操作先查出主键，然后根据主键查找到对应的记录，如下SQL：

select * from employees e INNER JOIN (SELECT id FROM employees ORDER BY name limit 9000,5) ed on e.id = ed.id

在这里插入图片描述
执行计划：

explain select * from employees e INNER JOIN (SELECT id FROM employees ORDER BY name limit 9000,5) ed on e.id = ed.id

原SQL使用的是filesort排序，优化后的SQL使用的是索引排序
在这里插入图片描述

Join关联查询优化

‐‐ 示例表：
 CREATE TABLE `t1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `a` int(11) DEFAULT NULL,
 `b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
 KEY `idx_a` (`a`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 create table t2 like t1;

 ‐‐ 插入一些示例数据
 ‐‐ 往t1表插入1万行记录
 drop procedure if exists insert_t1;
 delimiter ;;
 create procedure insert_t1()
 begin
 declare i int;
 set i=1;
 while(i<=10000)do
 insert into t1(a,b) values(i,i);
 set i=i+1;
 end while;
 end;;
 delimiter ;

call insert_t1();

 ‐‐ 往t2表插入100行记录
 drop procedure if exists insert_t2;
 delimiter ;;
 create procedure insert_t2()
 begin
 declare i int;
 set i=1;
 while(i<=100)do
 insert into t2(a,b) values(i,i);
set i=i+1;
 end while;
 end;;
 delimiter ;
 call insert_t2();

MySQL表关联常见有两种算法

  • Nested-Loop Join 算法
  • Block Nested-Loop Join算法

嵌套循环连接算法

一次一行地循环地从第一张表（成为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）中取出满足的行，然后取出两张表的结果合集。

 explain select * from t1 inner join t2 on t1.a=t2.a

在这里插入图片描述

  • 驱动表是t2，被驱动表是t1。先执行的是驱动表。优化器一般会优先选择小表做驱动表，所以使用inner Join 时，排在前面的不一定是驱动表。
  • 当使用left Join时，左表是驱动表。当使用right Join时，右表是驱动表；当使用join时，MySQL会选择数量较小的表作为驱动表。
  • 使用了NLJ算法。一般join语句中，如果执行计划extra中未出现 Using join buffer则表示使用join算法是NLJ。

上面SQL执行流程如下：
1、从表t2中读取一行数据（如果t2表有过滤条件的，会从过滤条件中取出一行数据）
2、从第一步骤的数据中，取出关联字段a到表t1中查找；
3、取出表t1满足条件的行，跟t2中获取的结果合并，作为结果返回给客户端；
4、重复上面3步。

整个过程会读取t2表的所有数据（扫描100行），遍历这每行数据中字段a的值，根据t2表中a的值索引扫描t1表中对应行。（扫描100次t1表的索引，1次扫描可以认为最终只扫描t1表一行完整数据，也就是总共t1表也扫描了100行）。

如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低，MySQL会选择Block Nested-Loop Join算法

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

把驱动表的数据读到join buffer中，然后扫描被驱动表，把被驱动表每一行取出来跟join buffer 中的数据做对比。

explain select * from t1 inner join t2 on t1.b=t2.b

在这里插入图片描述
Extra中的Using join buffer (Block Nested Loop)说明该关联查询用的是BNL算法。

上面SQL的大致流程如下：
1、把t2的所有数据放入到join buffer中
2、把t1中每一行取出来跟join buffer中的数据做对比
3、返回满足join条件的数据

整个过程对两个表都做了一次全表扫描，因此扫描的总行数为10000（t1的数据总量）+100（表t2的数据总量）=10100.并且join buffer中的数据是无序的，因此对表t1中的每一行都要做100次判断，所以内存中的判断次数是100*10000=100万次。
如果t2表很大，join buffer放不下怎么办呢？
join buffer的大小是由参数join_buffer_size设定的，默认值为256k,如果放不下表t2的所有数据的话，就分段放。
比如t2表中有10000行记录，join_buffer一次只能放800行数据，那么执行过程就是先往join_buffer中放800条记录，然后从t1表中取数据跟join_buffer中数据对比得到部分结果，然后清空join_buffer,再放入t2表剩余200条记录，再次从t1表中取数据跟join buffer中数据对比，所以就多扫了一次表。

被驱动表的关联字段没有索引为什么要算则使用BNL算法而不使用NLJ算法呢？

如果上面第二条SQL使用NLJ算法，那么扫描行数为100*10000=100万次，这个是磁盘扫描。显然，BNL磁盘扫描次数少很多，相比与磁盘扫描，BNL内存扫描会快很多。
因此MySQL对于被驱动表的关联字段没有索引的关联查询，一般都会使用BNL算法，如果有索引一般选择NLJ算法，有索引的情况下，NLJ算法比BNL算法性能更高。

对关联SQL的优化
  • 关联字段加索引： 让MySQL做join操作时尽量选择NLJ算法
  • ==小表驱动大表：==如果明确知道那张是小表可以用straight_join写法固定连接驱动方式，省去MySQL优化器自己判断的时间。
小表的定义

按照各自的条件过滤，过滤后哪个数据量小 哪个是小表

in和exsits 优化

原则：=小表驱动大表，即小的数据集驱动大的数据集

in：

当B表的数据集小于A的数据集时，in优于exists

select * from A where id in (select id from B )
等价于：
for（select id from B）{
	select * from A where A.id = B.id
}
exists:

当A表的数据集小于B的数据集时 exists 优于in
当主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留

select * from A where exists(select 1 from B where B.id = A.id)

先查询A中的数据，然后判断是否符合与B的条件

count（*）查询优化
explain select count(1) from employees;
explain select count(id) from employees;
explain select count(name) from employees;
explain select count(*) from employees;

以上四条SQL只有根据某个字段count不会统计字段为null值的数据行
字段有索引时查询效率：
count（星）≈ count（1）>count（字段）>count(主键id)
字段有索引，count（字段）统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count（id）
字段无索引时查询效率：
count（*）≈ count（1）>count(主键id)>count（字段）
字段无索引，count（字段）统计走不了索引，count（id）还可以走主键索引，所以count（主键）>count(id)

  • count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，

  • count(字段)还需要取出字段，索引理论上count（1）比count（字段会快一点）

  • count（*）是例外，MySQL并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count（列名）或count（常量）来代替count（星）

  • 为什么对于count（id）,MySQL最终选择辅助索引而不是主键聚集索引？
    因为二级索引相对于主键存储数据更少，检索性能应该更高，mysql内部做了点优化（应该是5.7）

常见优化方法

1、查询MySQL自己维护的总行数

myisam存储引擎做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数总会被MySQL存储在磁盘上，查询不需要计算。
对于innodb存储引擎的表MySQL不会存储表的总记录，因为有mvcc机制，查询count需要实时计算。

2、show table status

如果只需要知道表总行数的估计值可以用下面SQL，性能很高。

show table status like 'employes'

在这里插入图片描述

3、将总数维护到redis

但是很难保证表操作和redis的事务一致性

4、增加数据库计数表

插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作