这是我参与「第五届青训营 」伴学笔记创作活动的第 10 天

记录最近这段时间学习MySQL遇到的知识点

SQL优化手段

sql语句优化

尽量避免使用子查询而使用连接查询
缩小查找范围,读取适当的记录LIMIT M,N，而不要读多余的记录
优化group by,如果对group by语句的结果没有排序要求，要在语句后面加 order by null(group 默认会排序)
    尽量让group by过程用上表的索引

索引优化

尽量避免索引失效(Q27)
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致））
SQL 执行计划 explain 中的 type：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好

通过慢查询日志定位那些执行效率低的SQL语句

explain分析执行计划

主要关注两个参数

select_type:SIMPLE>PRIMARY>SUBQUERY>DERIVED>UNION>UNION RESULT
type:NULL>system>const>eq_ref>ref>range>index>ALL

show profile分析SQL

能够在做SQL优化时帮助我们了解时间都耗费在什么地方

trace分析优化器执行计划

MySQL5.6提供对SQL的追踪trace，通过trace文件能够进行近一步了解优化器执行的计划,以及sql解析的语句

join原理

MySQL5.5之前:嵌套循环

假设有A,B两张表，无索引，A为驱动表，B为非驱动表，从表A中取出一条数据a1，遍历B表，将匹配的数据放到result，
以此类推，驱动表A中每一条记录与表B的记录进行匹配，

MySQL8.0之前:Block Nested-Loop Join(BNLJ算法:块嵌套循环连接)

Block Nested-Loop Join方式不再是逐条获取驱动表的数据，而是一块一块的获取，
引入了 join buffer 缓冲区，将驱动表 join 相关的部分数据列(大小受 join buffer 的限制)缓冲到 join buffer 中，
然后全表扫描被驱动表，被驱动表的每一条记录一次性和 join buffer 中的所有驱动表记录进行匹配(内存中操作)，
将简单嵌套循环中的多次比较合并成一次，降低了被驱动表的访问频率。

MySQL8.0之后:Hash Join

Hash Join是做大数据集连接时的常用方式，优化器使用两个表中较小的表利用 Join Key 在内存中建立 散列表 ，然后扫描较大的表探测散列表，找出与 Hash表匹配的行。
这种方式适用于较小的表完全可以放在内存中的情况，这样总成本就是访问两个表的成本之和。
在表很大的情况下并不能完全放在内存，这时优化器会将它分割成 若干个不同的分区 ，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高 I/O 的性能。
它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。大多数人都说它是Join的重型升降机。Hash Join 只能应用于等值连接，这是由Hash的特点决定的。