MySQL——SQL查询优化

SQL优化总说

对于后台开发而言，涉及的知识面广，且如果要想进大厂的话还要求钻的深，而对于数据库，事务、存储引擎、索引、SQL优化等都是必备的技能，所以本篇博客就开启后序述说MySQL数据库的这些知识点的篇章，本篇将主要说SQL语句的优化、和解释explain、以及涉及的fileSort的两种算法的解析等。
对于SQL的优化总体思路如下：
1、观察，通常是让SQL跑一段时间
2、开启慢查询日志，设置慢查询标准，抓取慢SQL
3、对慢SQL采用explain+SQL分析其在数据库内部的执行计划
4、show profile查看更详细的SQL在服务器内部的生命周期以及执行细节
5、调整MySQL服务器的参数

所以接下来就来说下explain

explain细说

1）用法：就是explain+SQL语句即可

2）字段解析（重点）

id:表示加载表或者说对表操作的顺序，有三种情况
	1、id都一致，那么各表的加载顺序就按执行计划给出的表顺序从上到小依次加载
	2、id都不一致，那么id大的先被加载
	3、id有相同的也有不同的，那么相同的就按从上到下的顺序依次执行，而id不同的id大的优先被加载
select_type:表示这次查询操作在这张表上的类型
	1、SIMPLE:简单的select查询，查询中不包含子查询或者UNION
  	2、PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY（最后加载的那一个 ）
	3、SUBQUERY:在SELECT或WHERE列表中包含了子查询
	4、DERIVED:在FROM列表中包含的子查询被标记为DERIVED（衍生）Mysql会递归执行这些子查询，把结果放在临时表里。
 	5、UNION:若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM字句的查询中，外层				   SELECT将被标记为:DERIVED
	6、UNION  RESULT:从UNION表获取结果的SELECT
table:即表示MySQL给出的执行计划的每一行的信息所属的表
type：表示查询的类型
	1、System:表只有一行记录，这是const类型的特例，平时不会出现(忽略不计)
	2、const:表示通过索引一次就找到了,const用于比较primary key或者unique索引，因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
	3、eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
	4、ref：非唯一索引扫描，返回匹配某个单独值的行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
	5、range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
	6、index:FULL INDEX SCAN,index与all区别为index类型只遍历索引树。这通常比all快，因为索引文件通常比数据文件小。
possible keys:即表示在这张表上的查询可能用到的索引
key:即实际使用到的索引
key_len:表示索引中使用的字节数，可通过该列计算查询中索引的长度，在不损失精确性的情况下，长度越短越好，key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
ref:显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引上的值。
rows:根据表统计信息及索引选用情况，大致估算出找到所需记录所需要读取的行数
extra（重要）
	1、Using filesort:说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MYSQL中无法利用索引完成的排序操作称为“文件排序”
	2、Using temporary:使用了临时表保存中间结果，MYSQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by 
	3、Using index:表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找操作。
	4、Using Where：表示索引用于查找数据而非读取数据

Where查询字段优化（索引失效）问题

知道了explain分析的各个字段的含义那么我们接下来就要看下索引在where的优化及怎么避免索引失效：

Where语句后的索引字段
1、最左前缀原则
2、不要在索引列上计算、用函数、类型转换
3、索引列是字符的话要用单引号
4、in、>、 < 、between、等范围查询那么范围后的索引不会命中，且用于范围查询的那个字段也只是用到了索引排序的功能
5、!=、<>用在索引字段上索引不起作用
6、like kk% k%kk%这种那么该字段可以用到索引并且它后面的索引字段也都能用到
7、like %kk、%kk%这种那么该字段只会用到索引的排序并且它后面的索引字段也不会命中

OrderBy字段优化

orderby语句后的索引字段
1、orderby的排序字段没有在where语句中出现过，那么如果此时orderby之后的字段不匹配左前缀原则就会出现filesort
2、orderby的排序字段出现在了where语句中，但是是作为A>B这种类型的范围，那么此时orderby后的排序字段如果不匹配最左前缀原则也会出现filesort
3、orderby的排序的字段出现在了where语句中，且是作为常量A=B这种类型，那么此时orderby后的排序可以不单独严格遵守最左前缀原则，而是和前面的字段一起遵守最左前缀原则（此时有两种情况：where A=''
orderby B,C;where A=''orderby B,A因为这里A是常量所以等效于orderby B,2）
4、如果排序字段显示采用了DESC、ASC并且不一致的话那么orderby后的字段索引也会失效
总结：
1、orderby排序的字段没在where中出现过那么orderby和where前的字段完全无关系
2、orderby排序的字段在where中出现过但是不是常量形式，那么此时orderby后的字段也和where后的字段无关
3、orderby排序的字段在where中出现过是常量形式，那么此时orderby后的字段也和where后的字段有关系（可以配合满足最左前缀）

总结：可能你在看到这里了还对上面where和orderby的优化规则很懵，那么建议你就去实践下看。。

fileSort两种排序算法

说下当orderby索引失效后出现filesort的排序算法底层的两种算法：双路算法、单路算法
1、双路算法
核心思想：第一次IO，读取行指针和排序的字段在内存中排好序后，第二次IO利用排好序的行指针去读取真的数据，所以会发生两次IO，故效率可能会比较低。

2、单路算法
就是对双路的改进，核心思想：一次IO解决，一次就把需要的字段数据读入内存排好序后再输出，不用再去读表了；但它有一个瓶颈，就是如果服务器端的参数sort_buffer_size的值没设置好或者说要读取的数据大于了sort_buffer_size的值那么，单路会在第一次只能读取sort_buffer_size这么大的数据，产生临时表，之后还会去磁盘IO，直到能成功解决，所以这里可以看出，单路算法可能会产生多次IO

3、说下sort_buffer_size、max_length_for_sort_data这两个参数
sort_buffer_size：设置太小，可能会造成单路和双路产生原本设定的IO次数；设置太多，可能造成查询的数据超过了max_length_for_sort_data的值，就会采用双路
max_length_for_sort_data：即当select后查询的字段容量大于这个值设定的容量时，这时会采用双路算法而不是单路，该值设定的太大，可能会造成单路IO产生多次IO，即要读取的数据量超过了sort_buffer_size；设定太小，就会增加采用双路算法的概率

慢查询日志

慢查询日志是记录在会话中SQL查询消耗时间超过设定的阙值的SQL语句日志，可以利用它来捕捉慢查询的SQL语句，精准的优化SQL
1）如何查看是否开启和开启该功能

1、show variables like '%slow_query_log'; 默认是关闭的
2、set global slow_query_log = 1;临时开启，如果永久生效就配置在配置文件中（一般不允许）

2）查看设定的阙值

1、show variables like 'long_query_time';默认为10秒
2、set global long_query_time=3;设定阙值

3）到指定的慢查询日志去查看慢查询里的慢查询SQL语句

在这里select sleep(4)就是慢查询的SQL语句，之后我们可以利用explain+SQL分析来分析它慢的原因以此来优化它

4）生产中慢查询日志中的记录会很多，这时人工捕捉很费时，就利用MySQL的mysqldumpslow工具来导出优化人员着重关注的慢SQL语句

show profiles

show profiles是一种更细粒度的SQL语句优化，利用它可以记录最近一段时间内SQL语句在服务端执行的整个生命周期内消耗的资源和SQL语句每个生命期间所消耗的时间，这对DBA优化SQL很有帮助
1）查看show profiles功能是否开启

show variables like 'profiling';默认没有开启

2）

set  profiling = on开启

3）执行SQL

4）查看show profiles；记录最近一段时间内的SQL语句执行情况

在这里显示了SQL语句所消耗的时间，如果要看某条SQL的消耗资源的具体情况如下：

show profile  cpu,block io for query id（id是某条SQL的id）

这里这张图就显示了SQL语句的整个生命周期和所消耗的资源情况，其中我们可以查看SQL的cpu、block io、memory、context switches（上下文切换）等资源的消耗

对于表中出现的如下几个字段要特别注意：

Converting heap to disk查询的数据太多直接存到磁盘上
Creat temp table创建临时表
Copy to temp table on disk复制临时表内容到磁盘上
locked锁