MySQL调优篇 | SQL调优实战

【前言】

经常有一些朋友向我咨询，如何写出高效的SQL，这不是三言两语能说得清的，索性认真来写一下，增删查改方面的知识我不再赘述，如果有基础薄弱的同学，可以好好的补一补再来看。

以MySQL为基础，MySQL调优篇内容主要包含MySQL逻辑架构、索引知识、表关联算法、explain执行计划解读及SQL调优实战等。

文章受众主要为两类人：

第一类人是工作中不可避免的会接触到MySQL的人，比如说一些项目人员、开发人员、测试人员等。

第二类人是专职DBA。

其实不管是专职的还是非专职的，就我接触到的情况而言，很多DBA平时维护MySQL看起来没什么问题，但其实没有很好的理论支撑，知其然而不知其所以然，解释一个简单的问题就能问倒一大部分的人。

比如说：MySQL的逻辑架构，分析当前业务架构优缺点？SQL工作原理是什么样的？

而且很多公司招聘面试的时候，考验的也是背后的原理居多，基本上没有机试。面试官问一个问题，即便你会解决但就是说不出原理，那么你肯定要不了高薪。

理论+实战=高薪

文章能够让大家有所收获、有所借鉴那是最好的。

【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

执行计划：

[图片上传失败...(image-5bead8-1648603645699)]

5.7版本后添加了列filtered，意思是：指返回结果的行占需要读到的行(rows列的值)的百分比，filtered的数值其实越高，表示通过索引直接返回的行很多，数值较低时，一般出现在type=ALL或者index的情况。

分析下这个执行计划，type=ALL全表扫，而且产生了filesort。

where条件加个复合索引看看：

create index idx_atc_ccv on article(category_id,comments,views);

再看执行计划：

[图片上传失败...(image-cc8b9e-1648603645699)]

虽然走了索引，但也走到了filesort，还是不够好；这个索引不起作用吗？

在Mysql中，索引中出现了范围查找，后面就失效，comments出现了范围，索引在找的时候，发现comments无法直接定位到，影响了order by views的索引排序，进而出现了filesort。

那假设我们把sql调整为comments = 1再看看执行计划。

[图片上传失败...(image-df0660-1648603645699)]

filesort没有了，type一下从range变成了ref，执行计划是好的，但是业务变了，不行！

那么怎么创建索引合适呢？既然范围之后索引失效，那么我们能不能绕过去？直接新建category_id, views的复合索引呢。（删除之前创建的索引）

执行计划告诉我们，这个索引加的很合适！

[图片上传失败...(image-1eab4-1648603645699)]

结论：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;

执行计划：

[图片上传失败...(image-bb8f76-1648603645699)]

明显这个type为ALL，索引也没有加。问题来了，索引加哪边？是加class.card还是book.card？

我们都试试，先添加右边book表的索引：

alter table book add index idx_b_card(card);

执行计划走下：book的很明显的改变，type变成了ref

[图片上传失败...(image-bd31f6-1648603645699)]

此时我把book表的索引删掉，而建立class左表的索引看看执行计划：

[图片上传失败...(image-70950c-1648603645699)]

明显，加了class表的索引后，发现type是index，并且rows20行记录，全索引扫描，性能不会有刚刚的好！

同样的sql，同样的索引列，左连接的时候，加的索引所在的表不同，效果不同；

结论：上面出现效果不同，这个是由左连接的特性决定的，left join 条件用于确定如何从右边搜索行，而左边一定是都有的；左边全有，确定核心的点在于确定如何从右表中搜索数据行，右边是关键点，要加索引！所以左连接索引加在右表上，同理，右连接也是相反加！

有没有人好奇，如果两个索引都建呢会是什么样？我们尝试下加上看看：

[图片上传失败...(image-eb0730-1648603645699)]

现在book和class表上的card字段都加了索引，效果比上面两个都好！

4、三表优化案例

先把之前创建的索引都清除掉。

SQL如下：

select * from class

执行计划：

[图片上传失败...(image-9e65fb-1648603645699)]

此时三个表都没有索引：我们走下执行计划后发现，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上？试试！

走下执行计划看看：很明显，改善很多!

[图片上传失败...(image-d352-1648603645699)]

那么很明显这个原则也成立，总结下：

尽可能减少join语句中的NestedLoop循环总次数，永远用小结果集驱动大的结果集，这里的例子，就是左表尽量数据小于右表，外层for的次数就减少了，IO次数也会降低。

其实你可以试试，如果class表加了索引，效果会比右连接稍微好点，哈哈

[图片上传失败...(image-93f524-1648603645699)]

5、索引失效案例

5.1建个复合索引

create index idx_s_nap on staffs(name,age,pos);

SQL如下：

select * from staffs where name='July';

索引会不会失效？执行计划：

[图片上传失败...(image-dbc8f9-1648603645698)]

没问题。

再来一个SQL：

select * from staffs where name='July' and age = 25;

执行计划：

[图片上传失败...(image-6246db-1648603645698)]

一样没问题。

但是这里其实有个问题，Extra为Null。Extra为Null的时候，如果走了索引，说明这个查询，进行了回表！

那么什么是回表呢？

简单来说，如果你查询的字段，存在非索引字段，那么查询的时候，Mysql虽然根据了你的条件得到了这个记录，但是不在索引的字段无法通过索引的方式直接得到，只能通过拿到该条记录的主键索引，再从数据行里读，我们知道Mysql索引文件和数据文件是在两个不同的文件里的，要去读磁盘；所以索引文件建立的效果，就是帮助我们对数据进行排序和查找效率的优化，不至于去读数据行进行额外的IO开销；

所以这里字段我用select *，因为复合索引里没有add_time这个字段，所以无法直接查出来add_time这个列的记录，要通过定位到主键，然后再读一次数据行才可以得到这个记录，称为回表。

如果SQL这么写，就不会出现回表，因为pos在索引列中！

select pos from staffs where name='July' and age = 25;

执行计划：

[图片上传失败...(image-3d1ab-1648603645698)]

我们来看一些特殊场景！

SQL如下：

select * from staffs where age = 23 and pos = 'dev';

执行计划：

[图片上传失败...(image-fccc9c-1648603645698)]

再来一个sql：

select * from staffs where name = 'zhangsan';

[图片上传失败...(image-d88bd-1648603645698)]

走索引了。

总结：如果查询中没有开头的索引，不好意思，只能全表扫。违背了【最佳左前缀法则】

再看下这个sql：

select * from staffs where name = 'zhangsan' and pos = 'dev';

[图片上传失败...(image-ffdf91-1648603645698)]

执行计划显示这个key_len和只有name的时候一样，说明只走了name索引，Extra中出现Using index condition，这个是5.6后新加的特性，会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；就是走到了索引上的意思。

5.2、勿在索引列做任何操作

不要在索引列上做任何操作，包括计算，函数，自动或者手动类型转换，会导致索引失效而转向全表扫描。
SQL：

select * from staffs where left(name, 4) = 'July';

[图片上传失败...(image-7ca722-1648603645698)]

查找name左往右4个字符为July的行。索引失效了！

5.3、范围之后全失效

SQL：

select * from staffs where name = 'July' and age > 14 and pos = 'manager';

[图片上传失败...(image-7aad10-1648603645698)]

age用到了索引，进行范围查找，但是后面的索引pos就失效了，这里要注意，5.7以前的优化，是如果出现了范围查找，则当前范围的索引也不走，而5.7后，范围索引之后的才失效，所以这里的key_len=78，单个name话是74，三个都走是140。

5.4、不等于场景下索引失效

SQL：

select * from staffs where name != 'July';

[图片上传失败...(image-198c8d-1648603645698)]

select * from staffs where name <> 'July';

[图片上传失败...(image-4d6823-1648603645698)]

在使用不等于的场景下，无法使用索引导致全表扫描。

5.5、is null、is not null无法使用索引

SQL：

select * from staffs where name is null;

[图片上传失败...(image-b45a2e-1648603645698)]

5.6、Like百分写最右

like以通配符开头(‘%abc…’)时，Mysql索引会失效变成全表扫！

SQL：

select * from staffs where name like '%July%';

[图片上传失败...(image-48803f-1648603645698)]

因为like是范围查找，百分号在后面，Mysql会拿到字典序进行排序的方式查找对应的情况，而百分号在前面，Mysql就不知道从哪个字母开始找，于是便全表扫描。

实际面试中经常会这么问：如何解决like ‘%xxx%’ 字符时索引不被使用的情况？

答案是用覆盖索引避免索引失效，我们这里的索引是(name, age, pos)，索引我们在查询的时候不要写select *，只要写具体的字段值，任何一个列被覆盖索引覆盖，就可以解决两边百分号的问题！！！

5.7、字符串不加单引号索引失效

SQL：

explain select * from staffs where name = 222;

[图片上传失败...(image-cc47c5-1648603645698)]

索引失效。

而这个是成功走到索引的：

select * from staffs where name = '222';

[图片上传失败...(image-5f1fc9-1648603645698)]

Mysql很聪明，你以为你给我的我就查不到了，你给我的Int型的时候，实际这个字段是varchar型，传入数字会隐式的帮你转换成varchar类型，前面说过不要让Mysql做这些自动或者手动的类型转换，否则索引失效！当然查询的结果，是不会有变化的，只是sql执行上有转换。

5.8、少用or SQL：

select * from staffs where name = 'July' or name = 'z3';

[图片上传失败...(image-2f3df0-1648603645698)]

少用or，会导致索引失效，不是不用；

【结语】
MySQL调优篇写到这里就差不多告一段落了，希望大家都能真真正正能写出高性能的SQL，结合实践中不断的实验和摸索，早日晋级资深或者架构师。

后面有机会讲一些Mysql的其他知识点。共勉！