教你如何定位及优化SQL语句的性能问题

点击上方SQL数据库开发，关注获取SQL视频教程

来自：Hollis（ ：hollischuang)

在现如今的软件开发中，关系型数据库是做数据存储最重要的工具。无论是Oracale还是Mysql，都是需要通过SQL语句来和数据库进行交互的，这种交互我们通常称之为CRUD。在CRUD操作中，最最常用的也就是Read操作了。而对于不同的表结构，采用不同的SQL语句，性能上可能千差万别。本文，就基于MySql数据库，来介绍一下如何定位SQL语句的性能问题。

对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划。

执行计划

我们知道，不管是哪种数据库，或者是哪种数据库引擎，在对一条SQL语句进行执行的过程中都会做很多相关的优化，对于查询语句，最重要的优化方式就是使用索引。

而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。

基本语法

explain select ...

mysql的explain 命令可以用来分析select 语句的运行效果。

mysql> explain extended select * from account;
******** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: account
        type: ALL
possible_keys: NULL
         key: NULL
     key_len: NULL
         ref: NULL
        rows: 1
    filtered: 100.00
       Extra:
1 row in set, 1 warning (0.00 sec)


mysql> show warnings;
*************1. row ***************************
Level: Note
  Code: 1003
Message: select `dbunit`.`account`.`id` AS `id`,`dbunit`.`account`.`name` AS `name` from `dbunit`.`account`
1 row in set (0.00 sec)

(提示：可以左右滑动代码)

另外，对于分区表的查询，需要使用partitions命令。

explain partitions select ...

执行计划包含的信息

不同版本的Mysql和不同的存储引擎执行计划不完全相同，但基本信息都差不多。mysql执行计划主要包含以下信息:

id

由一组数字组成。表示一个查询中各个子查询的执行顺序;

• id相同执行顺序由上至下。

• id不同，id值越大优先级越高，越先被执行。

• id为null
  时表示一个结果集，不需要使用它查询，常出现在包含union
  等查询语句中。

select_type

每个子查询的查询类型，一些常见的查询类型。

table

查询涉及到的数据表。

type

访问类型

• ALL
    扫描全表数据

• index
  遍历索引

• range
  索引范围查找

• index_subquery
  在子查询中使用 ref

• unique_subquery
  在子查询中使用 eq_ref

• ref_or_null
  对Null
  进行索引的优化的 ref

• fulltext
  使用全文索引

• ref
    使用非唯一索引查找数据

• eq_ref
  在join
  查询中使用PRIMARY KEY
  orUNIQUE NOT NULL
  索引关联。

• const
  使用主键或者唯一索引，且匹配的结果只有一条记录。

• system const
  连接类型的特例，查询的表为系统表。

  
  

性能从好到差依次为：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了ALL之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。

所以，如果通过执行计划发现某张表的查询语句的type显示为ALL，那就要考虑添加索引，或者更换查询方式，使用索引进行查询。

possible_keys

可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL
时就要考虑当前的SQL
是否需要优化了。

key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。

TIPS:
查询中若使用了覆盖索引(覆盖索引：索引的数据覆盖了需要查询的所有数据)，则该索引仅出现在key列表中。

select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

key_length

索引长度char()、varchar()索引长度的计算公式：

(Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)

其他类型索引长度的计算公式:

CREATE TABLE `student` 
( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
`name` varchar(128) NOT NULL DEFAULT '', 
`age` int(11), PRIMARY KEY (`id`), 
UNIQUE KEY `idx` (`name`), 
KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

age 索引长度：int类型占4位，允许null
,索引长度为5。

ref

rows

extra

1. Using index 使用覆盖索引
2. Using where 使用了用where子句来过滤结果集
3. Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
4. Using temporary 使用了临时表。

一些SQL优化建议

select id from table where username like ‘%sql_road%’

关键词%sql_road%，由于sql_road前面用到了“%”，因此该查询会使用全表扫描，除非必要，否则不要在关键词前加%，

4、尽量避免使用!=或<>操作符

在where语句中使用!=或<>，引擎将放弃使用索引而进行全表扫描。

5、尽量避免使用 or 来连接条件

在 where 子句中使用 or 来连接条件，引擎将放弃使用索引而进行全表扫描。

可以用
select id from t where num=10
union all
select id from t where num=20


替代
select id from t where num=10 or num=20

7、可以考虑强制查询使用索引

select * from table force index(PRI) limit 2;
--强制使用主键


select * from table force index(hollis_index) limit 2;
--强制使用索引"hollis_index"


select * from table force index(PRI,hollis_index) limit 2;
--强制使用索引"PRI和hollis_index"

8、尽量避免使用表达式、函数等操作作为查询条件

9、尽量避免大事务操作，提高系统并发能力。

10、尽量避免使用游标

11、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

12、尽可能的使用 varchar/nvarchar 代替 char/nchar

13、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

14、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率

15、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引

长按下方二维码加入，我们的SQL训练营

点击"阅读原文"，了解SQL训练营