MySQL索引优化二

分页查询优化

很多时候我们的业务系统实现分页功能可能会用如下sql实现

select * from employees limit 10000,10;

表示从表employees中取出从10001行开始的10条记录.看似只查询了10条记录,实际这条sql是先读取10010条记录,然后抛弃前10000条记录,然后读到后面10条想要的数据,因此查询一张大表比较靠后的数据,执行效率是非常低的.

常见的分页查询优化

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

   首先来看一个根据自增且连续主键排序的分页查询的例子:

   select * from employees limit 90000,5
   

   

   该SQL表示查询从第90001开始的5条记录,没添加单独order by,表示通过 主键排序.再看表employees,因为主键是自增并且连续的,所以可以改写成按照主键去查询第90001开始的5条记录,如下:

   select * from employees where id > 90000 limit 5
   

   

   查询结果是一致的.再对比下执行计划

   explain select * from employees limit 90000,5;
   explain select * from employees where id > 90000 limit 5
   

   
   

   显然改写后的sql走了索引,而且扫描行数大大减少,执行效率更高.

   但是,这条改写的sql再很多场景并不实用,因为表中可能某些记录被删后,主键空缺,导致结果不一致.

   先删除一条前面的记录,然后再测试原sql和改写后的sql:
   

   

   两条sql的结果并不一样,因此,如果主键不连续,不能使用上面描述的优化方法.

   另外如果原sql是order by 非主键字段,按照上面说的方法会导致两条sql的结果不一致.所以这种改写得满足一下两个条件:

   • 主键是连续且自增
   • 结果是按照主键排序的

2. 根据非主键字段排序的分页查询

   select * from employees order by name limit 90000,5;
   

   

   explain select * from employees order by name limit 90000,5;
   

   

   发现key字段对应的值为null,并没有使用name字段的索引.扫描整个索引并查找到没有索引的行,需要遍历多个B+树,成本比扫描全表的成本更高,所以优化器放弃使用索引.

   知道不走索引的原因后.可以做出针对性的优化.关键是 让排序时返回的字段尽可能的少,所以可以让排序和分页操作先查出主键,然后根据主键查到对应的记录,sql改写如下

   select e.* from employees e inner join(select id from employees order by name limit 90000,5)ed on e.id = ed.id;
   

   

   需要的结果与原sql一致,执行时间减少了一半以上,再对比优化前后sql的执行计划:

   explain select e.* from employees e inner join(select id from employees order by name limit 90000,5)ed on e.id = ed.id;
   

   

   原sql使用的是filesort排序,而优化后的sql使用的是索引排序.

   Join关联查询优化

   drop procedure if exists insert_t1; 
   delimiter ;;
   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万行记录
   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算法

1. 嵌套循环连接 Nested-Loop Join(NLJ)算法

   一次一行循环的从第一张表(驱动表)中读取行,在这行数据中取到关联字段,根据关联字段在另一张表(被驱动表)里取出满足条件的行,然后取出两张表结果合集.

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

   

   从执行计划中可以看到这些信息:

   • 驱动表是t2,被驱动表是t1.先执行的就是驱动表(执行计划结果的id如果一样,则按从上到下的顺序执行sql);优化器一般都会优先选择 小表做驱动表,用where条件过滤完驱动表,然后再跟被驱动表做关联查询.所以使用inner join 时,排在前面的并不一定就是驱动表
   • 当使用了left join时,左表是驱动表,右表是被驱动表,当使用right join时,右表是驱动表,左表是被驱动表,当使用join时,mysql会选择数据量较小的表作为驱动表,大表作为被驱动表
   • 使用了NLJ算法.一般join语句中,如果执行计划Extra字段中未出现 Using join buffer则表示使用的join算法是NLJ

   上面sql的大致流程如下:

   1. 从表t2中读取一行数据(如果t2的查询有where条件,先用条件过滤完,再从过滤结果取一行数据)
   2. 从第1步的数据中,取出关联字段a,到表t1中查找
   3. 取出表t1满足条件的行,跟t2中获取到的结果合并,作为结果返回给客户端
   4. 重复上面3步

   整个过程会读取t2表的所有数据(扫描 100行),然后遍历这每行数据中字段a的值,根据t2表中的a的值索引扫描t1表中对应的行(扫描100次t1的索引,1次扫描可以认为最终只扫描t1表一行完整数据,也就是总共t1表也扫描了100行).因此整个过程扫描了200行.

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

2. 基于块的嵌套循环连接Block Nested-Loop Join(BNL)算法

   把 驱动表的数据读入到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条件的数据

   整个过程对表t1和t2都做了一次全表扫描,因此扫描的总行数为10000(t1)+100(t2)=10010.并且jion_buffer里的数据是无序的,因此对表中的每一行,都要做100次判断,所以内存中判断的次数是100*10000=100W次.

   示例表t2才100行,如果t2是一个大表,join_buffer放不下怎么处理?

   join_buffer的大小是join_buffer_size设定的,默认值是256k.如果放不下所有数据的话,策略很简单,就是 分段放.

   例如t2表有100行数据,join_buffer一次只能放80行,那么执行过程就是先往join_buffer里放80行记录,然后从t1表里取数据跟join_buffer中数据对比得到部分结果,然后清空join_buffer,再放入t2剩余的20行.再次从t1表里取数据跟join_buffer中数据对比.所以就多扫了一次t1表.

   被驱动表的关联字段没索引为什么使用BNL而不是NLJ?

   如果上面第二条sql使用NLJ,那么扫描行数为100*10000=100W次,这个是磁盘扫描,

   很显然,用BNL磁盘扫描次数少很多,相比于磁盘扫描,BNL的内存计算会快很多

   因此MySQL对于被驱动表的关联字段没索引的关联查询使用的是BNL.如果有索引的情况下一般选择NLJ.有索引的情况下NLJ比BNL快.

   对于关联sql的优化

   • 关联字段加索引:让mysql做join操作时尽量选择NLJ算法,驱动表因为需要全部查询出来,索引过滤条件也尽量走索引,避免全表扫描,总之,能走索引的尽量都走索引
   • 小表驱动大表:写多表连接sql时如果 明确知道那张表是小表可以用straight_join写法固定连接驱动方式,省去mysql优化器自己判断的时间

   **straight_join解释:**straight_join功能同join类似,但能让左边表来驱动右边的表,能改表优化器对于联表查询的执行顺序.

   -- 指定t2作为驱动表
   select * from t2 straight_join t1 on t2.a = t1.a
   

   • straight_join只适用于inner join,并不适用于left join,right join(因为left,right已经指定了表的执行顺序)
   • 尽可能让优化器去判断,因为大部分情况下优化器是比开发人员判断的更准确.使用straight_join一定要慎重,因为部分情况下手动指定的不一定会有优化器选择的靠谱

   对于小表的明确定义

   再决定那个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与join的各个字段的总数据量,数据量小的那个表,就是"小表",应该作为驱动表.

in和exists优化

原则:小表驱动大表,即小的数据集驱动大的数据集

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)
-- 等价于
for(select * from A){
	select * from B where B.id=A.id
}
-- A表与B表的ID字段应建立索引

1. exists(subquery)只返回true/false,因此子查询中的select *也可以select 1替换,官方说法是实际执行时会忽略select清单,因此没有区别
2. exists子查询的实际过程中可能经过了优化,而不是我们理解的逐条比对
3. exists子查询往往也可以用join来代替.那种最优需要具体问题具体分析

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的执行计划一样.说明这四个sql执行效率应该差不多.

字段有索引:count(*)≈count(1)>count(字段)>count(id),字段有索引,count(字段)走二级索引,二级索引存储数据比主键索引少,所以count(字段)>count(id)

字段无索引:count(*)≈count(1)>count(id)>count(字段),字段无索引,count(字段)走不了索引,count(id)还可以走主键索引,所以count(id)>count(字段)

count(1)跟count(字段)执行过程类似,不过count(1)不需要取出字段统计,就用常量1做统计,count(字段)还需要取出字段,所以理论上count(1)比count(字段)会快一点.

count(*)时例外,mysql并不会把全部字段取出来,而是专门做了优化,不取值按行累加,效率很高,所以不需要用count(字段)或count(1)来代替count( *).

为什么对于count(id),mysql最终选择辅助索引而不是主键聚簇索引?

二级索引相对于主键索引存储数据更少,检索性能更高.mysql内部做了优化

常见优化方法

1. 查询mysql自己维护的总行数

   对于MyISAM存储引擎的表做不带where条件的count查询性能是很高的,因为MyISAM存储引擎的表会被mysql存储再磁盘上,查询不需要计算

   -- 示例表
   CREATE TABLE `test_myisam` (
     `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=MyISAM AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8;
   

   

   对于 InnoDB存储引擎的表mysql不会存储表的总记录行数(因为涉及MVCC机制),查询count需要实时计算

2. show table status

   如果只需要知道表总行数的估计值可以用以下sql查询,性能很高

   show table status like 'employees'
   

   

3. 将总数维护到Redis里面

   插入或删除表数据时同时维护redis里的表总行数key的计数值(用incr/decr),但这种方式可能不准,很难保证表操作和redis操作的事务一致性

4. 增加数据库计数表

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

以上操作只支持无条件的count统计