mysql中如何美化sql_⼿把⼿教你给SQL做个优化
在开始之前，咱们要知道：如果我的 SQL 语句执⾏的⾜够快，还有没有必要去做优化？
完全没有必要对吧
所以我们⼀般说，要给 SQL 做个优化，那肯定就是这条 SQL 语句执⾏的⽐较慢了
那么，为什么它会执⾏⽐较慢呢？
SQL 语句执⾏较慢的 3 个原因
没有建⽴索引，或者索引失效导致了 SQL 语句执⾏较慢
这个应该是⽐较好理解的，如果数据⽐较多，在千万级别以上，然后呢⼜没有建⽴索引，在这千万级别的数据中查你想要的内容，简直就
是在⾁搏啊(哎呦，可了不得，竟然敢⾁搏
索引失效这块内容说起来就⽐较多了，⽐如在查询的时候，让 like 通配符在前⾯了，⽐如经常念叨的“最左匹配原则”，⼜⽐如我们在查询条件中使⽤ or ，⽽且 or 前后条件中有⼀个列没有索引，等等这些情况
都会导致索引失效
锁等待
常⽤的存储引擎主要有 InnoDB 和 MyISAM 这两种了，前者⽀持⾏锁和表锁，后者就只⽀持表锁
如果数据库操作都是基于表锁的话，意思就是说，现在有个更新操作，就会把整张表锁起来，那么查询的操作都不被允许，所以就不要说提⾼系统的并发性能了聪明的你肯定就知道了，既然 MyISAM 只⽀持表锁，那么使⽤ InnoDB 不就好了？你以为 InnoDB 的⾏锁不会升级成表锁嘛？too young too simple ！
如果对⼀张表进⾏⼤量的更新操作， mysql 就觉得你这样⽤会让事务的执⾏效率降低，到最后还是会导致性能下降，这样的话，还不如把你的⾏锁升级成表锁呢
还有⼀点，⾏锁可是基于索引加的锁，在执⾏更新操作时，条件索引都失效了，那么这个锁也会执⾏从⾏锁升级为表锁
不恰当的 SQL 语句
这个也⽐较常见了，啥是不恰当的 SQL 语句呢？就⽐如，明明你需要查的内容是 name ， age ，
但是呢，为了省事，直接 select *，或者在 order by 时，后⾯的条件不是索引字段，这就是不恰当的 SQL 语句
优化 SQL 语句
在知道了 SQL 语句执⾏⽐较慢的原因之后，接下来要做的就是对症下药了
针对 没有索引/索引失效 这块，最有效的办法就是 EXPLAIN 语法了，那你知不知道 Show Profile 也可以嘞
针对 锁等待 这块，没办法了，只能⾃⼰多注意
针对 不恰当的 SQL 语句 这块，介绍⼏个常⽤的 SQL 优化，⽐如分页查询怎么优化⼀下可以查询的更快⼀些呀，你不是说 select * 不是正确的打开⽅式嘛？那什么是正确的 select ⽅式呢？别急嘛，阿粉下⾯都会说到的
废话不多说，咱们开始了
先来个表
为了确保优化后的结果和我写的⼀样(起码 90% 是相符的
所以咱们⽤⼀样的数据库好不好？乖~
⾸先建个 demo 的数据库
接下来咱们建表，就建个⾮常简单的表好不好
CREATE TABLE demo.table(
id int(11) NOT NULL,
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
PRIMARY KEY(id)
) ENGINE = INNODB
然后插⼊ 10 万条数据
sql语句优化方式DROP PROCEDURE IF EXISTS demo_insert;
CREATE PROCEDURE demo_insert()
BEGIN
DECLARE i INT;
SET i = 1;
WHILE i <= 100000 DO
INSERT INTO demo.`table` VALUES (i, i, i);
SET i = i + 1 ;
END WHILE;
END;
CALL demo_insert();
OK ，准备⼯作做好了，接下来开始实战
通过 EXPLAIN 分析 SQL 是怎样执⾏的
只要说 SQL 调优，那就离不开 EXPLAIN
EXPLAIN SELECT * FROMtableWHERE id < 100 ORDER BY a;
咱们能够看到有好⼏个参数:id ：每个执⾏计划都会有⼀个 id ，如果是⼀个联合查询的话，这⾥就会显⽰好多个 id
select_type ：表⽰的是 select 查询类型，常见的就是 SIMPLE (普通查询，也就是没有联合查询/⼦查询), PRIMARY (主查询), UNION ( UNION 中后⾯的查询), SUBQUERY (⼦查询)
table ：执⾏查询计划的表，在这⾥我查的就是 table ，所以显⽰的是 table， 那如果我给 table 起了别名 a ，在这⾥显⽰的就是 a
type ：查询所执⾏的⽅式，这是咱们在分析 SQL 优化的时候⼀个⾮常重要的指标，这个值从好到坏依次是: system > const > eq_ref > ref > range > index > ALLsystem/const ：说明表中只有⼀⾏数据匹配，这个时候根据索引查询⼀次就能到对应的数据
eq_ref ：使⽤唯⼀索引扫描，这个经常在多表连接⾥⾯，使⽤主键和唯⼀索引作为关联条件时可以看到
ref :⾮唯⼀索引扫描，也可以在唯⼀索引最左原则匹配扫描看到
range ：索引范围扫描，⽐如查询条件使⽤到了 < ， > ， between 等条件
index ：索引全表扫描，这个时候会遍历整个索引树
ALL ：表⽰全表扫描，也就是需要遍历整张表才能到对应的⾏
possible_keys ：表⽰可能使⽤到的索引
key ：实际使⽤到的索引
key_len ：使⽤的索引长度
ref ：关联 id 等信息
rows ：到符合条件时，所扫描的⾏数，在这⾥虽然有 10 万条数据，但是因为索引的缘故，所以扫描了 99 ⾏的数据
Extra ：额外的信息，常见的有以下⼏种Using where ：不⽤读取表⾥⾯的所有信息，只需要通过索引就可以拿到需要的数据，这个过程发⽣在对表的全部请求列都是同⼀个索引部分时
Using temporary ：表⽰ mysql 需要使⽤临时表来存储结果集，常见于 group by / order by
Using filesort ：当查询的语句中包含 order by 操作的时候，⽽且 order by 后⾯的内容不是索引，这样就没有办法利⽤索引完成排序，就会使⽤"⽂件排序"，就像例⼦中给出的，建⽴的索引是 id ， 但是我的查询语句 order by 后⾯是 a ，没有办法使⽤索引
Using join buffer ：使⽤了连接缓存
Using index ：使⽤了覆盖索引
如果对这些参数了解的⾮常不错，那么 EXPLAIN 这块内容就难不住你了
Show Profile 分析下 SQL 执⾏性能
通过 EXPLAIN 分析执⾏计划，只能说明 SQL 的外部执⾏情况，如果想要知道 mysql 具体是如何查询的，需要通过 Show Profile 来分析
可以通过 SHOW PROFILES; 语句来查询最近发送给服务器的 SQL 语句，默认情况下是记录最近已经执⾏的 15 条记录，如下图我们可
以看到:
我想看具体的⼀条语句，看到 Query_ID 了嘛？然后运⾏下 SHOW PROFILE FOR QUERY 82; 这条命令就可以了:
可以看到，在结果中， Sending data 耗时是最长的，这是因为此时 mysql 线程开始读取数据并且把这些数据返回到客户端，在这个过程中会有⼤量磁盘 I/O 操作
通过这样的分析，我们就能知道， SQL 语句在查询过程中，到底是 磁盘 I/O 影响了查询速度，还是 System lock 影响了查询速度，知道了病症所在，接下来对症下药就容易多了
分页查询怎么可以更快⼀些
在使⽤分页查询时，都会使⽤ limit 关键字
但是对于分页查询，其实还可以优化⼀步
我这⾥给出的数据库不是太好，因为它太简单了，看不出来有什么区别，我使⽤⽬前项⽬上正在⽤的表来做个实验，可以看下区别(使⽤的SQL 语句如下⾯):
EXPLAIN SELECT * FROM `te_paper_record` ORDER BY id LIMIT 10000, 20;
EXPLAIN SELECT * FROM `te_paper_record` WHERE id >= ( SELECT id FROM `te_paper_record` ORDER BY id LIMIT
10000, 1) LIMIT 20;
上⾯⼀张图⽚，我没有使⽤⼦查询，可以看到执⾏了 0.033s ，下⾯的查询语句，我使⽤了⼦查询去做优化，能够看到执⾏了 0.007s ，优化的结果还是很显⽽易见的
那么，为什么使⽤了⼦查询，查询的速度就提上来了呢，这是因为当我们没有使⽤⼦查询时，查询到的 10020 ⾏数据都返回回来了，接下来要对这 10020 ⾏数据再进⾏过滤操作
那可不可以直接就返回需要的 20 ⾏数据呢，这样就不需要再做过滤操作了，直接返回就可以了嘛
你也太聪明了吧。⼦查询就是在做这件事情
所以查询时间上有了⼀个很⼤的优化
正确的 select 打开⽅式
在查询时，有时为了省事，直接使⽤ select * from table where id = 1 这样的 SQL 语句，但是这样的写法在⼀些环境下是会存在⼀定的性能损耗的
所以最好的 select 查询就是，需要什么字段就查询什么字段
⼀般在查询时，都会有条件，按照条件查
这个时候正确的 select 打开⽅式是什么呢？
如果可以通过主键索引的话， where 后⾯的条件，优先选择主键索引
为什么呢？这就要知道 MySQL 的存储规则
MySQL 常⽤的存储引擎有 MyISAM 和 InnoDB ， InnoDB 会创建主键索引，⽽主键索引属于聚簇索引，也就是在存储数据时，索引是基于 B+ 树构成的，具体的⾏数据则存储在叶⼦节点
也就是说，如果是通过主键索引查询的，会直接搜索 B+ 树，从⽽查询到数据
如果不是通过主键索引查询的，需要先搜索索引树，得到在 B+ 树上的值，再到 B+ 树上搜索符合条件的数据，这个过程就是“回表”
很显然，回表能够产⽣时间。
这也是为什么建议， where 后⾯的条件，优先选择主键索引
其他调优
看完上⾯的，⼼⾥应该就⼤概有数了， SQL 调优主要就是建⽴索引/防⽌产⽣锁等待/使⽤恰当的 SQL 语句去查询
但是，如果问你除了索引，除了上⾯这些⼿段，还有没有其他调优⽅式
啥？竟然还有？！
有的，这就需要跳出来，不要局限在具体的 SQL 语句上了，需要在数据库设计之初就考虑好
⽐如说，我们常说的要遵循三范式，但是在有的业务场景⾥⾯，如果在数据库⾥⾯多⼏个冗余字段的话，可能要⽐严格遵循三范式带来的性能要好很多。
但是这点就及其考验平时的积累了，阿粉在这⾥把这⼀点提出来之后，希望读者们可以看看⾃⼰项⽬上⽬前⽤的数据库有没有多余的字段，为什么要这样设计呢？这样多去观察，你的技术能⼒想不提⾼都很难

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。