索引的优化是面试中数据库部分可以说是必问的问题，这个和平时的积累有关。本文详细介绍了SQL和索引的优化。

 

 

目录

1 索引的执行分析

1.1 单表的查询索引的执行过程及优化-普通查询

1.2 单表查询索引的执行过程以及优化-普通查询+排序或分组

1.3 多表查询-连接查询索引的执行过程及优化

1.4 多表查询-连接查询代替子查询

2 索引失效

2.1like后面的通配符在前面，索引会失效。

2.2 没有使用联合索引的第一列，not in，!=，使用MySQL函数，类型转换，or等都无法用到索引。

3 SQL和索引的优化

3.1 慢查询日志

3.2 查磁盘的I/O读写的数据量

3.3 show status

4 优化总结

 

 

如果不知道索引的原理和一些基本的问题可以参考我的另一篇文章：

MySql数据库索引详解,索引看这一篇就够了

1 索引的执行分析

1.1 单表的查询索引的执行过程及优化-普通查询

假设我们有这样一张表，信息如下图所示，并插入一些数据：

 

上述表中的id为主键，因为InnoDB会自动为主键字段创建索引树，索引树节点存储着所有数据，那么对于下面SQL语句：

 select * from student where id=3；

上述语句，只需要进行一次查询就可以找到，因为首先会从B+主键索引树上快速找到id，InnoDB采用聚集索引（索引关键字和数据在一起存放），因此找到id，就找到这一行的数据，不用把整个表扫描一遍，效率很高。

接下来我们为student表的name字段创建一个索引，索引名为name_index,那么对于如下的SQL语句：

select * from student where name='liu'；

根据name字段查询学生信息的时候，会用到name_index辅助索引，InnoDB的辅助索引叶子节点存储的是辅助索引字段值和对应该行记录的主键（此处就是存了name,id），也就是上面的这个SQL语句，先查询name字段的辅助索引B+树，找到name=’liu’的节点，获取对应行的主键id=3，然后拿id=3在主键索引树去寻找数据。

那么对于sql语句：select id from student where name='liu'；由于select语句选择的是id,因此在辅助索引树上就可以找到，因次，不用再去主键索引树上进行搜索。

再看如下SQL语句：

 select score from student where name='liu'；

上述SQL语句根据name查询成绩score,由于name字段上创建了辅助索引，因此首先根据name=’liu’查询辅助索引树，找到对应的行主键id=3，由于SQL上需要选择score，为了得到score字段的值，还需要拿着id=3去主键索引树上去搜索这一行记录，然后拿到score。

因此优化的方法为：

如果SQL上经常根据name查询score，那么可以创建一个（name，score）的联合索引，再进行上面的SQL查询时（select score from student where name='liu'），根据name=’liu’直接查询辅助索引树，就找到score了，不用再去查找主键索引树了（此时辅助索引树的叶子节点存储的是name,score,id），性能提高！但是注意，由于用name作为where的过滤条件，创建联合索引的时候，name列在前，score列在后。

1.2 单表查询索引的执行过程以及优化-普通查询+排序或分组

首先创新一个订单表，信息如下：

对于sqly语句： select * from orderlist where userid=1 order by date；我们用explain分析下执行计划，如下所示：

根据explain分析的结果，可以看到，上面的SQL查询做了整表查询，效率相当低，由于查询完userid=1的所有行记录后，还要按date字段进行升序排序，所以出现了Using filesort，这涉及文件排序，效率相当低，一般遇见Using filesort一定要进行优化！

那么接下来我们为userid和date分别创建索引：

我们再来看一下上述sql语句的执行计划：

从上图中可以看到，此次查询用到了userid_index的辅助索引，但还是出现Using filesort,上面的SQL是从查询userid的辅助索引树，找到主键id，然后再拿这些主键id，到主键索引树上去搜索数据，搜索到了以后，需要根据date字段进行文件排序，效率还是很低！

force index(userid_index)是强制索引，为什么要使用强制索引呢，这是MySQL在5.6版本后加入的新特性，SQL优化器会在索引存在的情况下，通过符合RANGE范围的条数和总数的比例来选择是使用索引还是进行全表遍历，MySQL Server认为使用索引，还不如整表搜索来得快，它就不会使用索引了，强制索引是一定会使用指定的索引的。

大家可能会问，既然我们给date也创建了索引，那为什么还要对date进行文件排序Using filesort呢？要注意，一个SQL查询一张表，一次只能使用一个索引，因此userid_index的索引使用过了，date的索引就使用不到了！

继续优化，根据上面的分析，我们删除userid和date的索引，创建一个联合索引，由于userid是where的过滤条件，因此联合索引userid在前，date在后，如下：

mysql> create index userid_date_index on orderlist (userid, date);

继续explain下面的SQL语句：

给userid和date创建联合索引后，就不会再出现using filesort了，根据userid=1查询辅助索引树，找到的数据也已经按date排序好了，然后在去主键索引树上去整行数据就完了，效率很高！

之所以使用force index（userid_date_index），是防止SQL优化器优化索引的使用，因为我们的测试数据量较少，有时候使用索引不一定比整表搜索快！

如果上面的SQL只是select date，那么就不用再往主键索引树上查找了，在辅助索引树上就能找到date，所以select后面选择哪些列也是有讲究的。

1.3 多表查询-连接查询索引的执行过程及优化

重新举一个例子，我们创建两张表，分别是a和tcount_tbl，如下：

 

上面的两张表，都给runoob_author字段创建了辅助索引！

 

那么考虑一下，下面这个SQL语句，是怎么使用索引的，如下：

 

在使用多表连接查询的时候，MySQL会首先判断runoob_tbl（别名表a）和tcount_tbl（别名表b）哪个表小，这里表小主要指的是行数少，很显然b表小，MySQL会对b表进行整表遍历，然后在a表上根据runoob_author 字段进行查询，所以b表就是小表，无论如何都是要整表遍历的，是使用不到索引的，但是大表a表的runoob_author 字段创建索引，就能使用到了！

所在在连接查询的时候，小表总是要整表搜索的，建索引没有用，大表创建索引是能提高查询效率的，小表决定查询次数，大表决定查询时间！

 

用explain分析上面SQL的执行计划，如下：

 

从上面的explain结果可以很清楚的看到，先整表搜索了b表，然后在a表的辅助索引树上查找！b表没用到索引，a表用到了索引。

 

给上面的连接查询添加where条件过滤子句，如下：

 

从上面可以看到，a表用where a.runoob_author='FK'作为条件滤过，由于a表的runoob_author创建了索引，因此a表使用了索引进行条件过滤，过滤完就1行数据，所以这次a成小表，b成大表了，b的索引就能够使用上，如上面的信息所示，大家仔细分析一下！

所以，在连接查询中，大表小表的角色是不一定的，没有where子句，那么就按表的行数来定，如果有where子句，那么就按条件过滤完的行数来定大小表！

1.4 多表查询-连接查询代替子查询

子查询在多表联合查询时的效率是很低的，因为要产生中间表，多表查询最好优化成连接查询，效率很高！

比如拿下面的两张表做子查询操作：

 

上面的子查询会产生中间表，效率没有连接查询的效率高，把子查询优化成连接查询，如下：

 

可以看到，连接查询不会产生中间表！

2 索引失效

2.1like后面的通配符在前面，索引会失效。

 

runoob_author字段已经创建过索引

 

能用到索引！

用不到索引！

 

2.2 没有使用联合索引的第一列，not in，!=，使用MySQL函数，类型转换，or等都无法用到索引。

其它的种类能否使用索引，大家可以自行验证！

3 SQL和索引的优化

介绍三种方法，去定位哪些SQL操作效率低，然后根据实际情况再去优化。

3.1 慢查询日志

MySQL可以设置慢查询日志，当SQL执行的时间超过我们设定的时间，那么这些SQL就会被记录在慢查询日志当中，然后我们通过查看日志，用explain分析这些SQL的执行计划，来判定为什么效率低下，是没有使用到索引？还是索引本身创建的有问题？或者是索引使用到了，但是由于表的数据量太大，花费的时间就是很长，那么此时我们可以把表分成n个小表，比如订单表按年份分成多个小表等。

 

慢查询日志相关的参数如下所示：

慢查询日志记录了包含所有执行时间超过参数 long_query_time（单位：秒）所设置值的 SQL语句的日志，在MySQL上用命令可以查看，如下：

这个值当然是可以修改的，如下：

mysql> set long_query_time = 1;   #注意，单位是秒

Query OK, 0 rows affected (0.00 sec)

现在修改成超过1秒的SQL都会被记录在慢查询日志当中！可以设置为0.01秒，表示10毫秒。

 

慢查询日志，默认名称是host_name-slow.log，存放在MySQL的数据路径下，在我的电脑上就是这个数据路径：

C:\ProgramData\MySQL\MySQL Server 5.7\Data

这里面存放的就是慢查询日志，内容格式显示大致如下：

# Query_time: 0.012000  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 139

use tuluneducation;

SET timestamp=1534527397;

select id,author from subject where content like '%linux%' and title like '%c++ linux%';

通过查询慢查询日志，发现项目运行过程中，上面这条SQL语句的执行时间超过了设定的慢查询时间，那么接下来就需要用explain分析一下该SQL的执行计划了，根据具体情况找出SQL和索引该怎么去优化。

log_queries_not_using_indexes参数设置为ON，可以捕获到所有未使用索引的SQL语句。

3.2 查磁盘的I/O读写的数据量

数据库毕竟是磁盘存储，我们可以通过项目运行过程中，检测磁盘I/O读写的数据量，来定位效率低下的SQL。在windows上打开任务管理器就可以检测，Linux上通过命令可以检测，这里以windows为示例：

选择I/O读写查看列，检测我们Java程序运行过程中，执行哪些SQL时花费的磁盘I/O太大，读写的字节数过多，一般不加索引，或者索引创建不正确没有使用到索引，查询花费的磁盘I/O字节数都会上M，而添加了索引的查询一般消耗在K级别。

3.3 show status

MySQL提供了show status命令，查看MySQL Server的运行参数，可以查看select，insert，delete，update语句的执行频率，慢查询次数，事务的提交和回滚的次数，如下：

mysql> show status like 'Com_%';

打印的信息比较多，主要关注如下：

Com_select：执行 select 操作的次数，一次查询只累加 1。

Com_insert：执行 INSERT 操作的次数， 对于批量插入的 INSERT 操作， 只累加一次。

Com_update：执行 UPDATE 操作的次数。

Com_delete：执行 DELETE 操作的次数。

 

针对InnoDB存储引擎的参数：

mysql> show status like 'InnoDB_%';

Innodb_rows_read：select 查询返回的行数。

Innodb_rows_inserted：执行 INSERT 操作插入的行数。

Innodb_rows_updated：执行 UPDATE 操作更新的行数。

Innodb_rows_deleted：执行 DELETE 操作删除的行数。

 

还有以下几个参数，同样可以用show status打印查看：

Connections：试图连接 MySQL 服务器的次数。

Uptime：服务器工作时间。

Slow_queries：慢查询的次数。

Com_commit 和 Com_rollback： 可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。

4 优化总结

上面的内容已经就SQL和索引的优化做了详细的讲解，那么在这里总结一下，我们常用的优化方式：

 

1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where和order by 涉及的列上建立索引。
2. 注意前面罗列的会使索引失效的那些运算符，这样SQL是无法使用索引的。
3. 应尽量避免在 where 子句中使用or 来连接条件，否则将导致MySQL放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20
4. in和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3
5. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100*2
6. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)='abc' ，name以abc开头的id，应改为:select id from t where name like 'abc%'
7. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
8. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
9. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用，这个是属于MySQL的SQL优化器对索引的一种优化。
10. 索引并不是越多越好，索引固然可 以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
11. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
12. 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。