mysql 调优方法论

mysql 调优方法论

什么情况下该用索引，什么情况不该用索引？

理论上应该使用索引的场景：

1. where 条件后面字段；
2. distinct 字段；
3. group by 字段；
4. order by 字段；
5. join 连接字段；
6. 具有唯一特征的字段；

不建议使用索引的场景：

1. 字段选择度底的字段，比如性别；
2. 表的记录特别少；
3. 频繁更新的列；

索引失效场景：

1. 隐士类型转换，比如字段定义为varchar，而传入的值为数字，未加引号；
2. 不满足最左匹配原则；
3. 模糊查询；
4. 左边使用表达式，索引字段不独立；
5. 经过mysql 优化器分析，使用过滤效率反而不及全表扫描；
6. 使用or 连接的多个条件，部分没有索引，部分有索引
7. not null 的说法：若字段定义的允许为null，则有个标记字段专门标注是否为null。 所以尽量设置字段不为null

Group By 语句调优

1. 算法：
   a. 松散索引扫描：条件: 单张表，符合最左匹配原则，min和 max可用
   b. 紧凑索引扫描；
   c. 临时表（using temporary），需要优化

Order By 语句调优

算法： 常规排序（rowid排序）

	1. 从表中获取满足where条件的记录；
	2. 对于每条记录，将主键和排序字段放入sort buffer(由sort_buffer_size控制)
	3. sort buffer若 不能排序所有的主键和排序字段，则排序好先放入临时文件；sort  buffer排序用快排，文件合并用归并排序
	4. 获取排好序的（主键，排序字段），通过id去取select 需要返回的其他字段；
	5. 返回结果集；

全字段排序（优化排序）

		1. 从表中获取满足where条件的记录；
   		2. 对于每条记录，将主键和select字段全部放入sort buffer(由sort_buffer_size控制)
   		3. sort buffer若 不能排序所有的主键和排序字段，则排序好先放入临时文件；sort  buffer排序用快排，文件合并用归并排序
   		4. 返回结果集；

通过max_length_for _sort_data 可以控制是全字段排序还是rowid排序；

打包字段排序：

同全字段排序顺序，区别在于保存时候，将字段紧密地存储在一起而不是固定长度；

尽量防止二次排序（使用覆盖索引）；
如果做不到，考虑是否放大排序缓存区大小，减少排序文件大小；

limit 语句调优

distinct 语句调优

1. 算法： a. 松散索引扫描；b. 紧凑索引扫描；c. 临时文件

JOIN 语句调优

1. 算法：
   a. Nested Loop Join (NLJ)：

for(int a=0;a<a';a++){
	for(int b=0;b<b';b++){
		for(int c=0;c<c';c++){
			{匹配}
		}
	}
}

b. Block Nested Loop Join (BNLJ);

for(int a=0;a<a';a++){
	for(int b=0;b<b';b++){
		if(buffer 满了 ){
			for(int c=0;c<c';c++){
				{buffer里面和c匹配}
			}
		}else{
			添加到 buffer
		}	
	}
}

c. Batched key access Join(BKA);
匹配到的数据，先排序，再去主键索引树中去读取select字段数据；
特点：读取select字段数据过程，将随机IO 转换为了顺序IO（参考MRR 描述），但是增加了排序的开销；
d. Hash Join; （mysql 8.0.20之后才出现的，为了替换BNLJ。将缓存放到hash,加快速度）

count 语句调优

1. 如果不加条件，myisam 和 innodb 8.0.13 之后的，都能很快获取到；
2. 如果自己确实想要快，对准确性要求高，可以自己维护一个统计表；
3. 如果堆准确性要求比较低，可以用schame里面的，或者explain里面的都可以；
4. count(*) 和 count(1) 在效率上基本一致；count(字段)会不统计字段为null的行，需要关注；
5. 若没有非主键索引，使用主键索引count; 若有非主键索引，使用非主键索引；若有多个非主键索引，使用索引长度短的count。（原因嘛，就考虑到页存储问题，尽量一页存储的多，那读的页相对毕竟少）