【前言】
经常有一些朋友向我咨询，如何写出高效的SQL，这不是三言两语能说得清的，索性认真来写一下，增删查改方面的知识我不再赘述，如果有基础薄弱的同学，可以好好的补一补再来看。
以MySQL为基础，MySQL调优篇内容主要包含MySQL逻辑架构、索引知识、表关联算法、explain执行计划解读及SQL调优实战等。
文章受众主要为两类人：
第一类人是工作中不可避免的会接触到MySQL的人，比如说一些项目人员、开发人员、测试人员等。
第二类人是专职DBA。
其实不管是专职的还是非专职的，就我接触到的情况而言，很多DBA平时维护MySQL看起来没什么问题，但其实没有很好的理论支撑，知其然而不知其所以然，解释一个简单的问题就能问倒一大部分的人。
比如说：MySQL的逻辑架构，分析当前业务架构优缺点？SQL工作原理是什么样的？
而且很多公司招聘面试的时候，考验的也是背后的原理居多，基本上没有机试。面试官问一个问题，即便你会解决但就是说不出原理，那么你肯定要不了高薪。
理论+实战=高薪
文章能够让大家有所收获、有所借鉴那是最好的。
【EXPLAIN执行计划解读】
使用explain分析SQL的执行计划，从而知道MySQL是如何处理SQL语句的，有助于分析SQL语句的性能瓶颈。
本文截图基于MySQL版本5.7.27。
1、explain的作用
通过explain+sql语句可以知道如下内容：
- 表的读取顺序。（id）
- 数据读取操作的操作类型。（select_type）
- 显示sql操作属于哪张表的（table)
- 哪些索引可以使用。（possible_keys）
- 哪些索引被实际使用。（key）
- 表直接的引用。（ref）
- 每张表有多少行被优化器查询。（rows）
2、explain包含的信息
通过执行explain可以获得sql语句执行的相关信息。
image
下面对explain的表头字段含义进行解释。
2.1、ID
ID列：描述select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序；
1> id相同，执行顺序从上到下
image
2> id不同，如果是子查询，id的序号会递增，id值越大执行优先级越高。
image
3> id相同不同，同时存在。
image
总结：id的值表示select子句或表的执行顺序，id相同，执行顺序从上到下，id不同，值越大的执行优先级越高。
2.2、select_type
查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。其值主要有六个：
image
2.3、table
显示sql操作属于哪张表的。
2.4、type
type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
需要记住的
system>const>eq_ref>ref>range>index>ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。
1> system
System：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。
Const：表示通过索引一次就找到了。
const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。
image
注：对于system和const可能实际意义并不是很大，因为单表单行查询本来就快，意义不大。
2> eq_ref
唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引扫描。
image
3> ref
非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回匹配某值（某条件）的多行值，属于查找和扫描的混合体。
image
4> range
只检索给定范围的行，使用一个索引来检索行，可以在key列中查看使用的索引，一般出现在where语句的条件中，如使用between、>、<、in等查询。这种索引的范围扫描比全索引扫描要好，因为索引的开始点和结束点都固定，范围相对较小。
image
5> index
全索引扫描，index和ALL的区别：index只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。虽说index和ALL都是全表扫描，但是index是从索引中读取，ALL是从磁盘中读取。
image
6> ALL
全表扫描。
2.5、possible_keys和key、key_len
possible_keys:可能使用的key。
Key:实际使用的索引。如果为NULL，则没有使用索引
key_len：表示索引中所使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。
简单理解：possible_keys表示理论上可能用到的索引，key表示实际中使用的索引。
2.6、ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
2.7、rows
根据表统计信息及索引选用情况大致估算出找到所需记录所要读取的行数。当然该值越小越好。
2.8、filtered
百分比值，表示存储引擎返回的数据经过滤后，剩下多少满足查询条件记录数量的比例。
2.9、Extra
包含不适合在其他列中显示但十分重要的额外信息。
【总结】
- id，select子句或表执行顺序，id相同，从上到下执行，id不同，id值越大，执行优先级越高。
- type，type主要取值及其表示sql的好坏程度（由好到差排序）：system>const>eq_ref>ref>range>index>ALL。保证range，最好到ref。
- key，实际被使用的索引列。
- ref，关联的字段，常量等值查询，显示为const，如果为连接查询，显示关联的字段。
- Extra，额外信息，使用优先级Using index>Using filesort>Using temporary。
着重关注上述五个字段信息，结合实践中不断的实验和摸索，对调优十分有用。
下一篇讲SQL调优实战相关的知识，希望对大家的学习或者工作具有一定的参考价值。
网友评论