如何给SQL做个优化

本文主要讲解“如何优化SQL”。感兴趣的朋友不妨看看。本文介绍的方法简单、快速、实用。让边肖带你学习如何优化SQL。

SQL 语句执行较慢的 3 个原因

没有建立索引，或者索引失效导致了 SQL 语句执行较慢

这应该很容易理解。如果有更多的数据，也就是千万级以上，然后没有指标，在这千万级的数据中寻找自己想要的东西简直就是肉搏战(哦，太棒了，敢肉搏战。

关于索引失效有很多事情。比如查询时，让like通配符先来，比如“最左匹配原则”，或者我们在查询条件中使用or，or前后一列没有索引等等，都会导致索引失效。

锁等待

常用的存储引擎主要有InnoDB和MyISAM。前者支持行锁和表锁，后者只支持表锁。

如果所有的数据库操作都是基于表锁的，那就意味着如果现在有一个更新操作，整个表都会被锁定，所以查询操作是不允许的，更不用说提高系统的并发性能了。

如果你聪明，你就会知道。既然MyISAM只支持表锁，为什么不用InnoDB呢？你认为InnoDB的线锁不会升级为手表锁吗？太年轻太单纯！

如果对一个表做大量的更新操作，mysql认为这样会降低事务的执行效率，最终会导致性能下降。在这种情况下，最好将行锁升级为表锁。

另一点是行锁可以基于索引加锁。执行更新操作时，条件索引失败，因此该锁也将从行锁升级为表锁。

不恰当的 SQL 语句

这也是相当常见的。什么是不合适的SQL语句？例如，很明显，你需要查找的内容是姓名和年龄，但为了省事，直接选择*，或者按排序时，以下条件不是索引字段，这是不合适的SQL语句。

优化 SQL 语句

知道了SQL语句执行缓慢的原因后，下一步就是对症下药了。

解决无索引/索引失效问题最有效的方法是解释语法。你知道节目简介也可以用吗？

没有办法等这个锁了，只能自己多注意了。

针对不合适的SQL语句，介绍了几种常用的SQL优化方法，如如何优化分页查询以更快地查询。你不是说选择*不是正确的打开方式吗？正确的选择方式是什么？别担心，A Pink以后再谈。

不用多说，让我们开始吧。

先来个表

为了保证优化后的结果和我写的一样(至少90%是一致的。

那我们为什么不用同一个数据库呢？乖点~

首先，构建一个演示数据库。

接下来，让我们制作一块手表。一块非常简单的手表怎么样？

创建演示。表(ident (11)不为null，ain (11)默认为null，bint (11)默认为null，主键(id))引擎=innodb并插入100，000条数据。

dropperoceduerfexistsdemo _ insert；createProcedureDemo _ insert()BeginDeclarient；SETi=1；WHILEi=插入到演示中. ` table`VALUES(i，I，I)；（=NationalBureauofStandards）国家标准局

p;     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 > ALL

• system/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 语句上了，需要在数据库设计之初就考虑好

比如说，我们常说的要遵循三范式，但是在有的业务场景里面，如果在数据库里面多几个冗余字段的话，可能要比严格遵循三范式带来的性能要好很多。

到此，相信大家对“如何给SQL做个优化”有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！