性能调优之数据库（二）

特定语句的原理与调优

　　1.JOIN语句

　　　　1）种类、算法与原理

　　　　　　种类：

　　　　　　　　inner join：内连接

　　　　　　　　left join：左连接

　　　　　　　　right join：右连接

　　　　　　　　full outer join：全外连接

　　　　　　　　cross join：笛卡尔连接，如果cross join带有on 子句，就相当于inner join

　　　　　　　　

 

 

 　　　　　　JOIN算法：

　　　　　　　　1.Nested-Loop Join(NLJ)：嵌套循环Join，相当于sql拆分的时候，最原始不可取的那种方法，嵌套for循环

　　　　　　　　2.Block Nested-Loop Join(BNLJ)：块嵌套循环Join，先把外层结果通过join buffer缓存起来，相当于sql拆分的时候，把外层结果放到一个list当中，通过IN语句放到内存里面

　　　　　　　　　　使用join buffer的条件：

　　　　　　　　　　　　　　

 　　　　　　　　　　　　　　　

　　　　　　　　　　查询、修改join buffer的值及sql验证

　　　　　　　　　　　　　　　　　　　

　　　　　　　　3.Batched Key Access Join(BKA)：批量键值访问，相当于2的基础上，把外层查询出来的结果根据主键id进行排序之后，在通过IN放到内层去，目的是把随机IO变成顺序IO，从而提交查询效率

　　　　　　　　　　　　mysql5.6引入

　　　　　　　　　　　　BKA的基石：Multi Range Read(MRR)

　　　　　　　　　　　　　　　　

 　　　　　　　　　　　　MRR核心：把随机IO转换成顺序IO，从而提升性能　　　

　　　　　　　　　　　　 MRR参数：

　　　　　　　　　　　　　　　　

　　　　　　　　　　　　开启MRR：

　　　　　　　　　　　　　　　　　　

 

 

 　　　　　　　　　　　   开启BKA：

　　　　　　　　　　　　　　　　　　

　　　　　　　　　　4.Hash Join

　　　　　　　　　　　　mysql8.0版本出现，用来替换掉BNLJ

　　　　　　　　　　　　join buffer缓存外部循环的hash表，内能循环遍历时到hash表匹配

　　　　　　　　　　　　hash join注意点：

　　　　　　　　　　　　　　　　　　

 

 

 　　　　　　　　　　　　　　　　　　

　　　　2）JOIN语句优化

　　　　　　　　驱动表VS被驱动表：

　　　　　　　　　　　　外层循环的表是驱动表，内存循环的表是被驱动表

　　　　　　　　　　　　一般不需要人为关注，myqsl优化器会自动进行优化，把较小的表作为驱动表，以减少内层表循环扫描次数。

　　　　　　　　　　　　可以通过explain看到谁是驱动，谁是被驱动。id相同，从上到下执行，id不同，由大到小执行

　　　　　　　　　　　　　　　　

 

 

 　　　　　　　Join调优原则　　

　　　　　　　　　　1.小表驱动大表，一般不需人为关注，关联查询优化器会自动选择最优的执行顺序。如果优化器抽风，可以使用STRAIGHT_JOIN

　　　　　　　　　　　　　　

　　　　　　　　　　2.如果有where条件，应该尽可能使用索引，并减少外层循环的数据量

　　　　　　　　　　3.join的字段尽量创建索引

　　　　　　　　　　4.如果join字段在两张表的数据类型不同，会导致隐示转换，可能导致索引失效，从而引发全表扫描（可以通过mysql终端的show warnings查看细节）　　　

　　　　　　　　　　　　　　

　　　　　　　　　　5.尽量减少扫描的行数（explain-rows），尽量控制在百万以内

　　　　　　　　　　6.参与join的表不要太多，阿里编程规约要求不超过三张，如果业务确实需要关联很多表，需要进行sql拆分

　　　　　　　　　　　　　　tips：sql拆分的好处？

　　　　　　　　　　　　　　　　　　1.复杂sql转变成简单sql，减少了mysql进行sql解释、分析、调优的开销

　　　　　　　　　　　　　　　　　　2.简单sql大多是主键索引，性能佳

　　　　　　　　　　　　　　　　　　3.拆分后的sql可读性，可分析性，可维护性都大大提高，杜绝一条sql搞定一切，编写复杂sql的能力并不值得炫耀

　　　　　　　　　　7.如果被驱动表的join字段用不了索引，且内存较为充足，可以考虑把join buffer设置的大一些，从而降低内层循环的次数

　　2.Limit语句优化

　　　　1）limit语句，当偏移量很大的时候，性能显著下降，因为全表扫描的rows很大

　　　　　　　　

 

 

 　　　　2）优化方案：

　　　　　　　　1.覆盖索引（全表扫描变成全索引扫描）

　　　　　　　　　　　　

 　　　　　　　　2.覆盖索引+join

　　　　　　　　　　　　

 

 

 　　　　　　　　3.覆盖索引+子查询

　　　　　　　　　　　　

 　　　　　　　　4.范围查询+limit语句

　　　　　　　　　　　　

 

 

 　　　　　　　　5.如果能获得起始主键值&结束主键值

　　　　　　　　　　　　

 

 

 　　　　　　　　6.禁止传入过大的页码，超过阈值，默认返回首页（参考百度，淘宝等）

　　3.COUNT语句优化

　　　　　　1）count(*)

　　　　　　　　　　

　　　　　　2）count(字段)

　　　　　　　　　　

　　　　　　3）count(1)

　　　　　　　　　　

 　　　　　　4）总结：

　　　　　　　　　　1.count(*)和count(1)一样

　　　　　　　　　　2.count(*)会选择最小的非主键索引，如果不存在任何的非主键索引，则会使用主键

　　　　　　　　　　3.count(*)不会排除为null的行，而count(字段)会排除

　　　　　　　　　　4.对于不带查询条件的count(*)语句，MyISAM和InnoDB(MySQL>=8.0.13)都做了优化

　　　　　　　　　　5.如果没有特殊需求，尽量使用count(*)

　　　　　　5)count语句优化方案：

　　　　　　　　

 

 

 　　　　　　　　

 

 

 　　　　　　　　

　　4.ORDER BY语句优化

　　　　1）最好的方案：利用索引避免排序（如果sql进行explain之后，extra里面有using filesort字段，则意味着不可以利用索引进行避免排序）

　　　　　　　　原理：利用mysql索引本身的有序性，让mysql跳过排序过程

　　　　　　　　

 

 

 　　　　　　　　

 

 

 　　　　　　　　

 

 

 　　　　　　　　

 

 

 　　　　2）排序模式：

　　　　　　　　1.rowid排序(常规排序)

　　　　　　　　　　

 

 

 　　　　　　　　　　

　　　　　　　　　　　　

　　　　　　　　　　rowid排序的特点

　　　　　　　　　　　　　

　　　　　　　　　　tips：1）快速排序：

　　　　　　　　　　　　　　　　1.可以理解为二分递归排序

　　　　　　　　　　　　　　　　2.取一个中间值，把所有数与这个中间值进行比较，小的放在一边，大的放在另一边

　　　　　　　　　　　　　　　　3.把两边的数分别再取一个中间值，依次进行比较，小的放在一边 ，大的放在另一边，如此分割、排序，循环递归下去

　　　　　　　　　　　　　　　　4.直到每一个小块左边的数和右边的数相等，则完成排序

　　　　　　　　　　　　　　　　5.由于是一个递归算法，所以栈深度比较大，对栈内存开销比较严重

　　　　　　　　　　　　　　　　6.被认为是目前最好的一种内部排序算法

　　　　　　　　　　　　  2） 归并算法：

　　　　　　　　　　　　　　　　

 

 

 　　　　　　　　　　　　　　　　　　

　　　　　　　　　　　　　　　　　　速度仅次于快速排序，为稳定排序算法，一般用于对总体无序，但是各子项相对有序的数列　　　

　　　　　　　　　　2.全字段排序（优化排序）

　　　　　　　　　　　　　　

　　　　　　　　　　　　　　　全字段排序 VS rowid排序

　　　　　　　　　　　　　　　　

 

 

 　　　　　　　　　　　　　　　算法如何选择？

　　　　　　　　　　　　　　　　　　

 

 

 　　　　　　　　3.打包字段排序

　　　　　　　　　　　　

 

 

 　　　　3）参数汇总

　　　　　　　　

　　　　4）ORDER BY语句调优

　　　　　　　

 

 

 　　　　　　　　

 

 

 　　　　　　　　

 

 

 　　　　5）调优实战

　　　　　　　　

　　5.GROUP BY语句

　　　　1）松散索引扫描：无需扫描所有满足条件的索引即可返回结果

　　　　　　　　使用松散索引扫描的条件

　　　　　　　　　　　　

 

 　　　　　　　　　　　　

 

 　　　　　　　　　　　　

 

 　　　　　　　　　　　　　　　　

 

 　　　　　　　　　　　　　　　　　　

 

 　　　　　　　　　　　　　　　　　　

　　　　2）紧凑索引扫描：需要扫描满足条件的所有索引键才能返回结果

　　　　3）临时表

　　　　　　　　　　　     

　　　　总结：

　　　　　　　　

　　　　　　　　　　tips：松散/紧凑索引扫描有点类似于覆盖索引

表结构设计优化

　　数据库设计三范式：

　　　　

 

 　　　　　　

 

 

 　　　　　　

　　　　1.数据库设计原则上需要满足三范式，目的是消除冗余

　　　　2.但有些时候，需要进行一些反模式设计，目的是加快查询效率，空间换时间

　　　　表设计原则：