优化Sql语句，避免查询慢

前言

在面试过程中，经常会被问到，如果sql语句执行耗时过长，该如何优化sql，提高查询效率，在此我根据自己的实际经验，做如下总结。

一、EXPLAIN分析SQL语句

sql优化经常用到EXPLAIN来查看sql语句的执行计划，以下5点是我们重点关注的地方

EXPLAIN执行sql语句以后显示的执行计划

1、type列

通过type字段，我们可以判断出此次查询是全表扫描还是索引扫描，一个好的sql语句至少达到range级别，避免出现all级别（全表扫描）。

​

type列常用取值如下：

system： 表中只有一条数据， 这个类型是特殊的const类型。

CONST： 针对主键或唯一索引的等值查询扫描，最多只返回一行数据,const 查询速度非常快， 因为它仅仅读取一次即可。

eq_ref： 表示对于前表的每一个结果，都只能匹配到后表的一行结果,并且查询的比较操作通常是=，查询效率较高。

ref： 此类型通常出现在多表的join查询，针对于非唯一或非主键索引，或者是使用了最左前缀规则索引的查询。

range： 表示使用索引范围查询，通过索引字段范围获取表中部分数据记录，

这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。

index： 表示全索引扫描(full index scan)，和ALL类型类似，只不过 ALL类型是全表扫描，而index类型则仅仅扫描所有的索引，而不是扫描全部数据。index类型通常出现在所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据。当是这种情况时，Extra 字段 会显示 Using index。

all： 表示全表扫描，这个类型的查询是性能最差的查询之一。通常来说，我们的查询不应该出现ALL类型的查询，因为这样的查询在数据量大的情况下，对数据库的性能是巨大的灾难。如一个查询是 ALL 类型查询，那么一般来说可以对相应的字段添加索引来避免。

通常来说, 不同的 type 类型的性能关系如下:

all < index < range < ref < eq_ref < const < system

2、key列

此字段是 mysql 在当前查询时所真正使用到的索引名。

3、key_len列

表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用。

4、rows列

mysql 查询优化器根据统计信息，估算 sql 要查找到结果集需要扫描读取的数据行数，这个值非常直观的显示 sql 效率好坏， 原则上 rows 越少越好。

5、extra列

explain 中的很多额外的信息会在 extra 字段显示, 注意，常见的不太友好的值，如下：Using filesort，Using temporary。常见的有以下几种内容:

using filesort ：表示 mysql 需额外的排序操作，不能通过索引顺序达到排序效果。一般有 using filesort都建议优化去掉，因为这样的查询 cpu 资源消耗大。

using temporary：查询有使用临时表,一般出现于排序，分组和多表 join 的情况，查询效率不高，建议优化。

using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错。

using where：表名使用了where过滤。

二、索引可能失效的情况

情况1：单独引用复合索引里非第一位置的索引列

复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。

如果有一个2列的复合索引index(col1,col2),则已经对(col1)、(col1,col2)上建立了索引；如果有一个3列复合索引index(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引。

情况2：对索引列运算，运算包括（+、-、*、/、！、<>、%、like’%_’（%放在前面）、or、in、exist等），导致索引失效

错误例子：select * from user where YEAR(birthday) < 1990（索引失效）

正确例子：select * from users where birthday <’1990-01-01′

错误例子：select * from user where name like ‘%jack%’（索引失效）

正确例子：select * from users where name like ‘jack%’

情况3：索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库表设计时不要让字段的默认值为NULL，应该用0、一个特殊的值或者一个空串代替空值。

情况4：注意范围查询语句

对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。

三、如何选择合适的列创建索引

1、在经常需要搜索的列上，可以加快搜索的速度；

2、在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

3、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；这样查询可以利用索引的排序，加快排序查询时间；

4、在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。当增加索引时，会提高检索性能，但是会降低修改性能；

5、唯一性很差的字段不合适建索引，如性别

6、更新频繁的字段不适合建索引，耗时且影响性能

四、索引的优缺点

优点：

1、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

2、可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。

3、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

4、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

5、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：

1、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2、索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大，如果非聚集索引很多，一旦聚集索引改变，那么所有非聚集索引都会跟着变。

3、当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，一旦一个数据改变，并且改变的列比较多，可能会引起好几个索引跟着改变，这样就降低了数据的维护速度。

4、每个索引都有统计信息，索引越多统计信息越多，过多索引会导致优化器优化过程需要评估的组合增多。创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。