MySQL索引优化二

学习来源：图灵课堂

https://vip.tulingxueyuan.cn

 

分页优化

一般来说，我们的后台管理系统都是有翻页功能的，并且有时候还要加上一些筛选过滤条件；如果对查询没有经过特别的优化，那么就会发现翻页越往后就越慢，这是为什么呢？

因为如果单独是使用limit，例如limit 90000, 5;这个并不是从第90000条数据开始，往后查询5条数据，而是从第一条开始查，查到90005条数据，然后抛弃前90000条数据，所以效率比较差。

针对这样的查询可以进行优化的。针对的是select *

1.如果主键索引是完全自增且连续的，并且查询结果是使用主键排序的，就可以改成 where id > 90000 limit 5;如此是可以的，但是这个条件太苛刻了，实际上很难满足，那么就不用这种方式。

2.针对没有选择过滤条件的查询，那么就去主键索引查询，也可以避免频繁的回表操作。如果加上的有过滤条件，并且这些过滤条件是有联合索引的， 那么就可以修改为使用到联合索引查询到主键值，然后再通过这些主键值去回表查，回表次数也会少很多；例如:

select * from table where id in (select id from table where  联合索引字段 = 条件 order by 联合索引字段 limit xx) 

或者 select * from table t inner join (select id from table where  联合索引字段 = 条件 order by 联合索引字段 limit xx) ta on t.id = ta.id

 

如果扫描整个二级索引无法获取到全部查询的值，并且还要频繁的回表，那么优化器就会直接全表扫描，放弃使用索引。

总结来说，分页优化，就是如果是复杂查询，尽量使用到联合索引，先过滤出来主键值，这样可以过滤掉很多数据，然后再拿这些主键值去回表查询，这样效率就会高很多。

 

 

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();

 

 

注意t1和t2表结构完全一致；并且t1一万条数据，t2一百条数据。

嵌套循环：也就是先扫描出一个表符合条件的值，然后用这些值去另外一个关联表中去循环查询比对。开发中尽量少使用这样的查询。

mysql的表关联常见有两种算法

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

一次一行的从第一张表（驱动表）获取数据，然后根据关联字段，去另外一张表（被驱动表）中取出相关的行，展示数据。通过explain看，如果id一致，那么第一行就是第一张表驱动表，第二行才是被驱动表；如果id不一致，那么id值大的就是驱动表。

1.也就是说t2是驱动表，t1是被驱动表；优化器针对查询进行了优化，让小表去驱动大表；所以说，排在前面的表并不一定就是小表，排在inner后面的不一定是大表。

2.当使用left join的时候，已经明确指定了left前面的是驱动表，left之后的是被驱动表；使用right join的时候，right前面的是被驱动表，right后面的是驱动表；此时不管是大表还是小表了。

 EXPLAIN SELECT * FROM t1 LEFT JOIN t2 on t1.id = t2.id;
 EXPLAIN SELECT * FROM t1 RIGHT JOIN t2 on t1.id = t2.id;

 

 3.使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

 

上面的执行流程：

1. 首先从驱动表中取出一条符合要求的数据；
2. 然后取出关联条件，去被驱动表中查询；
3. 然后从被驱动表查询出来数据，进行组装；
4. 重复上述三个步骤。

整个过程中，会去t2查询一百行，同时因为关联条件是id，是主键索引，那么也可以认为去t1表也是查询一百行；加起来就是一共查询二百行；使用索引就会使用这种方式，这种方式比较高效。

针对有索引的关联查询会使用这样的方式。这种方式是磁盘扫描。

 

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

会将驱动表的数据加载到join_buffer中，然后去扫描被驱动表，将被驱动表的每一行取出来放到buffer中进行比对。buffer中的数据是无序的，极端情况下，里面的数据都会全部扫描一次。

但是要注意，在buffer中比对也是比较快的，速度要高于从磁盘中读取数据。

MySQL8.0版本的是hash join方式；5.7版本是BNL方式。

 EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;

 如果是BNL的方式分析：

1. 把 t2 的所有数据放入到 join_buffer 中

2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比

3. 返回满足 join 条件的数据。

因为t2的数据比较少，是100条，会一次性将这些数据都load到内存buffer中；这是一百行；然后也会全表扫描t1，就是一万行，加起来就算10100行；并且从t1获取的数据要在内存中和t2的全表数据比对，极限情况就是100*10000次的比较。判断次数就是1000000次。

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段放。假如t2有1000行数据，会先加载600行数据，之后再加载剩下的400行数据。分批执行。t1表就是要多扫描一次。

问题：为什么没有索引的要使用BNL，而不使用NLJ？

因为如果关联字段没有索引，如果使用NLJ，就要进行多次的磁盘扫描读取数据，磁盘的IO是很耗时并且消耗性能的；如果使用了BNL，虽然也是要进行多次比较，但是这个是比较过程是在内存中的，可以大幅减少磁盘IO操作。BNL会更快速。

所以针对有索引的关联查询，可以使用NLJ；没有走索引的关联查询可以使用BNL。

针对关联查询的优化总结

1. 被关联的字段最好加上索引；或者是使用有索引的列进行关联；被驱动的表关联字段最好加上索引。
2. 尽量保证小表驱动大表，如果已经提前确认了大小表，可以使用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间。

straight_join解释：

1. straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。
2. 比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。
3. straight_join只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序）
4. 尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。 

针对小表的定义：

并不是说一张表1万条数据就一定是大表，要看过滤之后的数据有多少，两张表过滤之后，符合要求数据量最少的才是小表。

in和exsits优化 

原则上是尽量小标驱动大表去查询。

select * from A where id in (select id from B)

注意使用in的时候，in后面的查询先执行，也就是说小表放到in后面，大表放到in前面；如上所示，B先执行，A后执行。

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

使用exist的时候，exist前面的先执行，小表要放到前面，大表放到exist后面。先查询A中符合的数据，然后去B中匹配。

count(*)查询优化

EXPLAIN select count(1) from t1;
EXPLAIN select count(id) from t1;
EXPLAIN select count(a) from t1;
EXPLAIN select count(*) from t1;

 

 

 

 查询结果如上图所示：表明查询结果类似，并没有什么明显的差距。

四个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(字段)，如果字段有null值，是不会统计到这行数据的。这个要注意。

count(1)和count(字段)两个类似，唯一不同的就是count(1)并不会去索引或者表中获取到字段值，而是直接进行统计计算，这样就可以减少查询的数据量，所以效率比较高一些。

count(id)为什么也使用了二级索引，而不使用主键索引；因为一般来说主键索引都会带上本行的全部数据，查询的数据量较大，经过MySQL优化器之后，认为查询二级缓存可以减少查询数据量，提升查询效率，所以count(id)也是用的二级索引。

高版本的MySQL，针对count(*)专门进行了优化，不会取出来字段值，而是直接进行查询，查询效率是非常高的。

针对查询总数据量的优化

1. 如果是myisam引擎，会专门去维护一个全表数据量，count(*)查询效率非常高；为什么innodb没有呢？因为innodb为了解决不可重复读，引入了MVCC机制，当前读和快照读读取的数量可能不一致。

2. innodb引擎下如果要知道一个大概的数据量，可以使用 

show table STATUS LIKE '表名';

 这只是一个大概的预估值。

3. 将总数据量维护在redis中，但是这个并不能保证百分百的准确性，因为不在同一个事务中。

4. 引入另外一张表。这个看业务要求。

数据类型的选择

首先是要确认是什么大类型：数字，字符串，时间，二进制；

然后确认具体的类型：有无符号，范围大小，定长变长，精度等；

一个原则：在确保业务的情况下，要尽量选择符合的数据类型，没有必要为了什么所谓的准则去设计字段。

1. 整数类型

int从小到大分为tinyint，smallint，int，bigint；1个字节，2两个字节，4个字节，8个字节；如果没有符合，那就是从0到最大值；如果有符号，那就是从负数到整数。

优化建议

1. 如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
2. 建议使用TINYINT代替ENUM、BITENUM、SET。
3. 避免使用整数的显示宽度，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用INT。
4. DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置。
5. 建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。
6. 整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。 

2. 日期和时间

日期主要是date，datetime，timestamp三种；占用空间大小分别为3字节，8字节，4字节。

格式分别为YYYY-MM-DD，YYYY-MM-DD HH:MM:SS，YYYYMMDDhhmmss。

MySQL高版本进行了timestamp的优化扩容（5.6.4及之后），数据量也变大了。所以首选是timestamp；如果空间没有压力，使用datetime最好。

• 对于DATETIME，如果你不指定默认值，那么它的默认值是NULL。
• 对于TIMESTAMP，如果你不指定默认值，那么它的默认值是当前的日期和时间。
• 在选择使用DATETIME还是TIMESTAMP时，你应该根据你的具体需求来决定。如果你需要存储一个与时区无关的日期和时间，或者你需要一个更大的日期范围，那么DATETIME可能是一个更好的选择。如果你需要自动更新日期和时间，或者你需要节省存储空间，那么TIMESTAMP可能更适合你。

字符串类型

char：定长，0-255字节，当插入的数据不足长度时，后面会加上空格补齐，但是在进行运算的时候会去掉空格。

varchar：变长，0-65535 字节，varchar(n)中的n代表最大列长度，插入的字符串实际长度不足n时不会补充空格。

TINYBLOB：0-255字节，不超过 255 个字符的二进制字符串 

text：极大字符串，可以保存文本，长文本

BLOB ：二进制形式的长文本数据 。

 

1. 字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。
2. CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计算的准确性和完整性。
3. 尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。
4. BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。BLOB和TEXT都不能有默认值。 

`id` TINYINT(2) UNSIGNED ZEROFILL

这个2表示补齐长度的意思，当存入的值小于10的时候，例如是1，查出来的就是01，实际存储的值还是1，并且存储的范围也没有变，就是在MySQL输出的时候前面补齐加0.没有多少实际意义。

在MySQL命令中，字段的类型长度TINYINT(2)、INT(11)不会影响数据的插入，只会在使用ZEROFILL时有用，让查询结果前填充0。