Mysql优化原理(深度解剖)
一、查询过慢原因
1.有规律速度过慢:每天下午2-4点查询速度过慢,用户量激增。
解决方法:做mysql集群来解决,进行数据库的分库分表,做负载均衡,读写分离。MHA+Atlas或Mycat来解决单个数据库的访问负载。
- 无规律速度过慢
原因:跟表中的数据量有变化
解决方法:做sql语句的优化
二、判断查询语句是否需要优化依赖工具
- 慢查询记录;
看看之前的查询语句和现在查询语句的查询时间,是不是变慢。
- Explain(desc)执行计划;
看看之前创建的索引是否生效了。
- 相关的日志文件;
一般都是做多字段索引,将哪些字段归于一个索引,了解客户的查询习惯去灵活性的归纳索引。例如:一般ABC归于一个,但是客户喜欢ACD索引查询。
三、mysql查询句中七个查询命令特征(根据七个查询执行优先级)
- from:优先级最高;
[作用]
1)将硬盘上的表文件加载到内存,生成一个全新的临时表
- 定位内存中已经存在的临时表
[注意]:
- 在一个查询语句中,第一个执行永远都是from
- from定位的是内存中一个临时表,这个临时表必须手动指定表名。
- where:
[作用]:
1)where命令操作的由from命令生成的临时表
2)where命令循环遍历当前临时表中每一个数据行
将满足条件的数据行保存到一个全新的临时表
[注意]:
由于where命令每次操作只是一个数据行,因此在where使用过程中
是无法使用聚合函数作为判断条件
3.group by
[作用]:
1)首先对临时表中的数据进行一次排序处理
2)然后将其有相同特征的数据行保存到一个临时表中
[注意]:
- 多字段分组
首先,分组字段执行顺序对于查询结果来说没有任何影响的
然后,第二个字段开始,操作临时表是上一个分组字段生成的临时表。
- 如果select操作临时表是由group by提供。
此时select将遍历group by生成的每一个临时表
在操作具体临时表时,select只会读取指定字段中第一个数据行内容。
4.Having
[作用]:
负责将group by生成的临时表中不满足条件的数据删除
[注意]:
- having命令不能独立出现,只能出现在group by命令后面
- Having命令每次操作的是一个临时表,因此选择判断条件
应该都来自于聚合函数
- Select
[作用]:
- select操作的临时表,是由from和where命令来提供的,
Select将指定字段中所有内容读取出来。
将读取的内容组成一个全新的临时表
- select操作的临时表是有group by或者having提供的
此时select将遍历生成每一个临时表,在操作具体临时表时,
Select只会读取指定字段中第一个数据行内容。
- order by
[作用]:
专门针对select生成的临时表中数据进行排序
将排序后内容组成一个全新的临时表。
[注意]:
1)如果order by使用字段名称进行排序时,字段名可以不出现在select
所生成的临时表
2)如果order by使用字段顺序进行排序时,索引位置必须在select查询语
中出现
7、limit
[作用]:
对于临时表中数据行进行截取
四、查询语句特征:
- 七个查询命令中,除了having命令之外,剩下的六个查询命令执行完毕后都会生成全新的临时表。
- 七个查询命令中,除了from命令之外,剩下的六个查询命令操作的临时表,都是上一个查询命令生成的临时表。
- 在当前查询命令执行完毕后,mysql自动将上一个查询命令生成的临时表进行销毁处理,所以在一个查询语句执行完毕后,用户只能看到最后一个查询命令所生成的临时表。
- 在进行多字段分组查询时,从第二个分组字段开始,操作的临时表是上一个分组字段所生成的临时表
五、七个查询命令中,哪些命令执行效率过慢 [最慢到慢的排序]
- group by:
[原因]:group by 执行时首先将临时表数据进行[排序]然后进行[分组]
2.Order by:
[原因]:需要对select生成的临时表中数据进行一个[排序]然后将排好序的数据行[组成一个全新的临时表]
3.Where:
[原因]:需要对表中所有数据行进行遍历,如果临时表中数据行过多的话,会导致where的执行效率非常慢。
4.Limit:
[原因]:如果指定的起始行数过大,导致查询速度的过慢
5.对于多表查询来说,没有指定表文件加载顺序,也会导致查询速度过慢