【前言】
经常有一些朋友向我咨询，如何写出高效的SQL，这不是三言两语能说得清的，索性认真来写一下，增删查改方面的知识我不再赘述，如果有基础薄弱的同学，可以好好的补一补再来看。
以MySQL为基础，MySQL调优篇内容主要包含MySQL逻辑架构、索引知识、表关联算法、explain执行计划解读及SQL调优实战等。
文章受众主要为两类人：
第一类人是工作中不可避免的会接触到MySQL的人，比如说一些项目人员、开发人员、测试人员等。
第二类人是专职DBA。
其实不管是专职的还是非专职的，就我接触到的情况而言，很多DBA平时维护MySQL看起来没什么问题，但其实没有很好的理论支撑，知其然而不知其所以然，解释一个简单的问题就能问倒一大部分的人。
比如说：MySQL的逻辑架构，分析当前业务架构优缺点？SQL工作原理是什么样的？
而且很多公司招聘面试的时候，考验的也是背后的原理居多，基本上没有机试。面试官问一个问题，即便你会解决但就是说不出原理，那么你肯定要不了高薪。
理论+实战=高薪
文章能够让大家有所收获、有所借鉴那是最好的。
【MySQL逻辑架构】
1、整体架构图
MySQL的架构不同于其他数据库，它的插件式的存储引擎架构可以在多种不同场景中应用并发挥良好作用。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
各层介绍：
1.1 连接层
最上层是客户端，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。
1.2 服务层
1.3.引擎层
存储引擎负责MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
show engines:查看所有的数据库引擎
show variables like '%engine%' 查看默认的数据库引擎
MyISAM和InnoDB对比
1.4.存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。
2、查询流程
MySQL的查询流程大致是：
客户端通过协议与DB服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析。
语法解析器和预处理：首先MySQL通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。解析器将使用语法规则验证和解析查询；预处理器则根据一些规则进一步检查解析数是否合法。
查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
流程图：
3、SQL的执行顺序
一般SQL语法：
SQL解析：
真正执行的顺序：
4、SQL性能问题
常见的几个点：
- SQL逻辑复杂且糟糕
- 无效的索引
- 服务器调优和各个参数的配置（缓冲，线程数等）
SQL逻辑复杂且糟糕，通常表现在嵌入很多子查询，各种表关联等。
无效的索引，通常表现为索引建立了但没用上，索引分为单值索引和复合索引和唯一索引，所谓单值索引指的是一个索引有且只包含一个列，一个表中可以有多个单列索引。复合索引是指索引能够同时覆盖多个数据列；唯一索引是指索引列的值必须唯一，但可以为null。
服务器调优一般部署MySQL阶段就调整好了。
【索引知识】
面试的时候，提起索引不要再拿目录类比索引的优点了，这样显的很菜。一句话：索引是数据结构，是一个排好序且快速查找的数据结构。
1、索引的本质
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。
查询是数据库的最主要功能之一，速度当然是越快越好，因此数据库系统的设计者会从查询算法的角度进行优化。常见的查询算法有顺序查找（linear search）、二分查找（binary search）、二叉树查找（binary tree search）等。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。
这种数据结构，就是索引。
看一个例子：
图1
图1展示了一种可能的索引方式。
左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。
2、索引结构
MySQL 索引一般是哈希表或 B+ 树，常用的 InnoDB 引擎默认使用的是 B+ 树来作为索引的数据结构。
2.1为什么是B+树
B+树是B树的变种，是基于B树来改进的。为什么B+树会比B树更加优秀呢？
B树：有序数组+平衡多叉树；
B+树：有序数组链表+平衡多叉树；
B+树的关键字全部存放在叶子节点中，非叶子节点用来做索引，而叶子节点中有一个指针指向一下个叶子节点。
B+ 树查找过程
磁盘块 1 中存储 17 和 35 数据项，还有 P1、P2、P3 指针，P1 表示数据项小于 17 的磁盘块，P2 表示数据项在 17 和 35 之间的数据项，P3 表示数据项大于 35 的数据项。非叶子节点不储存数据，只储存指引搜索方向的数据项。
我们知道每次 IO 读取一个数据页的大小，也就是一个磁盘块。
假设我们要查找 29 这个数据项，首先进行第一次 IO 将磁盘块 1 读进内存，发现17 < 29 < 35，然后选用 P2 指针进行第二次 IO 将磁盘块 3 读进内存，发现26 < 29 < 30，然后选用 P2 指针将磁盘块 8 读进内存，在内存中做二分查找，找到 29，结束查询。
通过分析查询过程，我们可以知道 IO 次数和 B+ 树的高度成正比。H 为树的高度，M 为每个磁盘块的数据项个数，N 为数据项总数。
从下面的公式可以看出如果数据量N一定，M越大相应的H越小。
M 等于磁盘块的大小除以数据项大小，由于磁盘块大小一般是固定的，所以减小数据项大小才能使得 M 更大从而让树更矮胖。这也是为什么 B+ 树把真实数据放在叶子节点而不是非叶子节点的原因。
如果真实数据放在非叶子结点，磁盘块存储的数据项会大幅度减少，树就会增高相应查询数据时的 IO 次数就会变多。
3、索引实现
在MySQL中，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。
3.1、MyISAM索引实现
MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
下图是MyISAM索引的原理图：
在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。
3.2、InnoDB索引实现
虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
1、InnoDB的数据文件本身就是索引文件。
2、InnoDB的辅助索引data域存储相应记录主键的值而不是地址。
这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
4、如何使用索引
说了那么多原理，总结一下索引的优缺点和使用时机。
4.1、优点
- 提高数据检索的效率，降低数据库的IO成本；
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗；
所以记住，索引功能是：搜索+排序
4.2、缺点
- 索引提升查询效率的同时也会降低更新的效率，更新表时，MySQL不仅要保存数据，还要保存一下索引文件，每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息；
- 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
可见，Hash Join也依赖Join Buffer，在最好的场景下，如果Join Buffer能覆盖驱动表所有相关字段，那么在查询的过程中驱动表和被驱动表都只需要扫描一次，如果散列算法够好，比较次数也只是被驱动表的记录数。
Hash Join只能用于等值连接，大表连接Hash Join的优化效果比较明显。
优化思路
- 用小结果集驱动大结果集，尽量减少 join 语句中的Nested Loop循环总次数。
- 优先优化 Nested Loop 内层循环，因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能。
- 对被驱动表的 join 字段上建立索引，并且Join ON 条件的字段应该是相同类型的。
- 当被驱动表的 join 字段上无法建立索引的时候，设置足够的 Join Buffer Size。
- 对于非主键的连接查询，如果被驱动表数据特别多，建议先使用子查询查出一个临时的结果集然后再连接。（待验证）
- 对于可以直接从一个表中取数据的情况。（例如同一个表中取交集，例如好友表，互相关注才是好友）这样的情况，使用 Join 效率是要高于子查询的。
【总结】
掌握表连接算法，结合实践中不断的实验和摸索，从而真正达到高效使用MySQL算法的目的。
【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语句执行的相关信息。
下面对explain的表头字段含义进行解释。
2.1、ID
ID列：描述select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序；
1> id相同，执行顺序从上到下
2> id不同，如果是子查询，id的序号会递增，id值越大执行优先级越高。
3> id相同不同，同时存在。
总结：id的值表示select子句或表的执行顺序，id相同，执行顺序从上到下，id不同，值越大的执行优先级越高。
2.2、select_type
查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。其值主要有六个：
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索引。因为只匹配一行数据，所以很快。
注：对于system和const可能实际意义并不是很大，因为单表单行查询本来就快，意义不大。
2> eq_ref
唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引扫描。
3> ref
非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回匹配某值（某条件）的多行值，属于查找和扫描的混合体。
4> range
只检索给定范围的行，使用一个索引来检索行，可以在key列中查看使用的索引，一般出现在where语句的条件中，如使用between、>、 index
全索引扫描，index和ALL的区别：index只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。虽说index和ALL都是全表扫描，但是index是从索引中读取，ALL是从磁盘中读取。
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调优实战】
1、环境准备
每张表模拟一些数据进去。
article表CREATE TABLE IF NOT EXISTS `article`(`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`author_id` INT (10) UNSIGNED NOT NULL,`category_id` INT(10) UNSIGNED NOT NULL , `views` INT(10) UNSIGNED NOT NULL , `comments` INT(10) UNSIGNED NOT NULL,`title` VARBINARY(255) NOT NULL,`content` TEXT NOT NULL); class表 CREATE TABLE IF NOT EXISTS `class`(`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`card` INT (10) UNSIGNED NOT NULL); book表 CREATE TABLE IF NOT EXISTS `book`(`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`card` INT (10) UNSIGNED NOT NULL); phone表CREATE TABLE IF NOT EXISTS `phone`(`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,`card` INT (10) UNSIGNED NOT NULL)ENGINE = INNODB; staffs表CREATE TABLE staffs(id INT PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间')CHARSET utf8 COMMENT'员工记录表'; ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`)
2、单表优化案例
需求分析：
查询category_id为1且comments大于1的情况下，views最多的article_id
select id, author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
执行计划：
5.7版本后添加了列filtered，意思是：指返回结果的行占需要读到的行(rows列的值)的百分比，filtered的数值其实越高，表示通过索引直接返回的行很多，数值较低时，一般出现在type=ALL或者index的情况。
分析下这个执行计划，type=ALL全表扫，而且产生了filesort。
where条件加个复合索引看看：
create index idx_atc_ccv on article(category_id,comments,views);
再看执行计划：
虽然走了索引，但也走到了filesort，还是不够好；这个索引不起作用吗？
在Mysql中，索引中出现了范围查找，后面就失效，comments出现了范围，索引在找的时候，发现comments无法直接定位到，影响了order by views的索引排序，进而出现了filesort。
那假设我们把sql调整为comments = 1再看看执行计划。
filesort没有了，type一下从range变成了ref，执行计划是好的，但是业务变了，不行！
那么怎么创建索引合适呢？既然范围之后索引失效，那么我们能不能绕过去？直接新建category_id, views的复合索引呢。（删除之前创建的索引）
执行计划告诉我们，这个索引加的很合适！
结论：type变成了range，这是可以忍受的，但是Extra里出现了filesort是无法接受的，但是我们建立了索引为什么没有用，这是因为按照Mysql的BTREE工作原理，先排序category_id，如果遇到相同的，再排序comments，如果遇到相同的，再排序views，当comments位置处于联合（复合）索引的中间位置时，Mysql无法对范围（range）后面的字段进行索引排序，从而后面的字段索引失效！
3、两表优化案例
来看个SQL：
select * from class left join book on class.card = book.card;
执行计划：
明显这个type为ALL，索引也没有加。问题来了，索引加哪边？是加class.card还是book.card？
我们都试试，先添加右边book表的索引：
alter table book add index idx_b_card(card);
执行计划走下：book的很明显的改变，type变成了ref
此时我把book表的索引删掉，而建立class左表的索引看看执行计划：
明显，加了class表的索引后，发现type是index，并且rows20行记录，全索引扫描，性能不会有刚刚的好！
同样的sql，同样的索引列，左连接的时候，加的索引所在的表不同，效果不同；
结论：上面出现效果不同，这个是由左连接的特性决定的，left join 条件用于确定如何从右边搜索行，而左边一定是都有的；左边全有，确定核心的点在于确定如何从右表中搜索数据行，右边是关键点，要加索引！所以左连接索引加在右表上，同理，右连接也是相反加！
有没有人好奇，如果两个索引都建呢会是什么样？我们尝试下加上看看：
现在book和class表上的card字段都加了索引，效果比上面两个都好！
4、三表优化案例
先把之前创建的索引都清除掉。
SQL如下：
select * from classleft join book on class.card = book.card left join phone on book.card = phone.card;
执行计划：
此时三个表都没有索引：我们走下执行计划后发现，Extra字段多了Using join buffer；首先join buffer意思是使用了连接缓存。
在5.7之后，Mysql对表和表之间的连接，做了优化缓存，实际上在A left join B的过程，Mysql会更在意B的表往A中相同的部分，所以类似一个for循环，最外层for A，内层是for B，找到B中的每一行满足A行的记录，因为是要A的全部，所以最外层一定是A，然后合并行，最后输出；而在3表中，等于3个for循环。
其中其实发现有个Block Nested-Loop Join——BNL算法，这个算法将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数。所以最外层的表是class，先for整个class，然后放在join buffer里，接下来循环内表的时候，直接取buffer的行去比对，减少对磁盘的IO。
但是整个type=ALL，rows都是20，全表扫，这是我们无法接受的。
那么三张表怎么加索引呢？可以想想，左连接建右表上，那么这个是不是说class左表，建立索引在book和phone上？试试！
走下执行计划看看：很明显，改善很多!
那么很明显这个原则也成立，总结下：
尽可能减少join语句中的NestedLoop循环总次数，永远用小结果集驱动大的结果集，这里的例子，就是左表尽量数据小于右表，外层for的次数就减少了，IO次数也会降低。
其实你可以试试，如果class表加了索引，效果会比右连接稍微好点，哈哈
5、索引失效案例
5.1建个复合索引
create index idx_s_nap on staffs(name,age,pos);
SQL如下：
select * from staffs where name='July';
索引会不会失效？执行计划：
没问题。
再来一个SQL：
select * from staffs where name='July' and age = 25;
执行计划：
一样没问题。
但是这里其实有个问题，Extra为Null。Extra为Null的时候，如果走了索引，说明这个查询，进行了回表！
那么什么是回表呢？
简单来说，如果你查询的字段，存在非索引字段，那么查询的时候，Mysql虽然根据了你的条件得到了这个记录，但是不在索引的字段无法通过索引的方式直接得到，只能通过拿到该条记录的主键索引，再从数据行里读，我们知道Mysql索引文件和数据文件是在两个不同的文件里的，要去读磁盘；所以索引文件建立的效果，就是帮助我们对数据进行排序和查找效率的优化，不至于去读数据行进行额外的IO开销；
所以这里字段我用select *，因为复合索引里没有add_time这个字段，所以无法直接查出来add_time这个列的记录，要通过定位到主键，然后再读一次数据行才可以得到这个记录，称为回表。
如果SQL这么写，就不会出现回表，因为pos在索引列中！
select pos from staffs where name='July' and age = 25;
执行计划：
我们来看一些特殊场景！
SQL如下：
select * from staffs where age = 23 and pos = 'dev';
执行计划：
再来一个sql：
select * from staffs where name = 'zhangsan';
走索引了。
总结：如果查询中没有开头的索引，不好意思，只能全表扫。违背了【最佳左前缀法则】
再看下这个sql：
select * from staffs where name = 'zhangsan' and pos = 'dev';
执行计划显示这个key_len和只有name的时候一样，说明只走了name索引，Extra中出现Using index condition，这个是5.6后新加的特性，会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；就是走到了索引上的意思。
5.2、勿在索引列做任何操作
不要在索引列上做任何操作，包括计算，函数，自动或者手动类型转换，会导致索引失效而转向全表扫描。
SQL：
select * from staffs where left(name, 4) = 'July';
查找name左往右4个字符为July的行。索引失效了！
5.3、范围之后全失效
SQL：
select * from staffs where name = 'July' and age > 14 and pos = 'manager';
age用到了索引，进行范围查找，但是后面的索引pos就失效了，这里要注意，5.7以前的优化，是如果出现了范围查找，则当前范围的索引也不走，而5.7后，范围索引之后的才失效，所以这里的key_len=78，单个name话是74，三个都走是140。
5.4、不等于场景下索引失效
SQL：
select * from staffs where name != 'July';
select * from staffs where name 'July';
在使用不等于的场景下，无法使用索引导致全表扫描。
5.5、is null、is not null无法使用索引
SQL：
select * from staffs where name is null; select * from staffs where name is not null;
5.6、Like百分写最右
like以通配符开头('%abc...')时，Mysql索引会失效变成全表扫！
SQL：
select * from staffs where name like '%July%';select * from staffs where name like '%July'; select * from staffs where name like 'July%';
因为like是范围查找，百分号在后面，Mysql会拿到字典序进行排序的方式查找对应的情况，而百分号在前面，Mysql就不知道从哪个字母开始找，于是便全表扫描。
实际面试中经常会这么问：如何解决like '%xxx%' 字符时索引不被使用的情况？
答案是用覆盖索引避免索引失效，我们这里的索引是(name, age, pos)，索引我们在查询的时候不要写select *，只要写具体的字段值，任何一个列被覆盖索引覆盖，就可以解决两边百分号的问题！！！
5.7、字符串不加单引号索引失效
SQL：
explain select * from staffs where name = 222;
索引失效。
而这个是成功走到索引的：
select * from staffs where name = '222';
Mysql很聪明，你以为你给我的我就查不到了，你给我的Int型的时候，实际这个字段是varchar型，传入数字会隐式的帮你转换成varchar类型，前面说过不要让Mysql做这些自动或者手动的类型转换，否则索引失效！当然查询的结果，是不会有变化的，只是sql执行上有转换。
5.8、少用or
SQL：
select * from staffs where name = 'July' or name = 'z3';
少用or，会导致索引失效，不是不用；
【结语】
MySQL调优篇写到这里就差不多告一段落了，希望大家都能真真正正能写出高性能的SQL，结合实践中不断的实验和摸索，早日晋级资深或者架构师。
来源：数据与人
