MySQL 优化之SQL和索引的优化

前言

“索引什么时候会用到？”
“索引什么时候会失效？”
“索引是不是创建的越多越好？”
“索引的底层实现是什么？”
“多表联合查询怎么查？”
“你做过哪些SQL和索引的优化？”

一.索引的执行分析

先来看看，一般我们做单表查询和多表联合查询是怎么使用索引的。

1）单表查询索引的执行过程以及优化 - 普通查询
我们创建过一张student学生表，如下：

可以看到student表里面有4条内容。

当我们用id作为过滤条件进行查询的时候（注意id是主键），如下：

我们用explain分析一下上面SQL语句的执行计划，如下：

       可以看到，表里面虽然有4行数据，但是找到id=3的学生信息，只查询了一行就找到了，因为使用了PRIARY主键索引（InnoDB会自动给主键字段创建主键索引树），根据前面学习的内容可知，当我们用id作为where子句的过滤条件时，首先从主键B+索引树上快速找到id，InnoDB采用聚集索引（索引关键字和数据在一起存放），因此找到id，就找到这一行的数据，不用把整个表扫描一遍，效率很高。

那如果是下面的查询呢？

       上面的SQL根据name字段查询信息，我们之前给该name字段创建过辅助索引，名字是name_index。

用explain分析一下上面的SQL执行过程，如下：

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

       可以看到，上面根据name查询学生信息，总共搜索了两次B+索引树，那么下面的这个SQL语句呢？

       上面的SQL使用到了name_index辅助索引，在辅助索引树上根据name找到对应行的主键，由于上面的select语句选择的就是id，因此在辅助索引树上就已经得到了，不用再去主键索引树上去搜索了。

再变换一下上面的SQL语句，如下：

       上面的SQL是根据name查询成绩score，由于name字段上创建了辅助索引，因此首先根据name=’liu’查询辅助索引树，找到对应的行主键id=3，由于SQL上需要选择score，为了得到score字段的值，还需要拿着id=3去主键索引树上去搜索这一行记录，然后拿到score。所以，知道了索引的执行过程，现在你知道怎么优化上面这个SQL语句吗？
       对了，如果SQL上经常根据name查询score，那么可以创建一个（name，score）的联合索引，再进行上面的SQL查询时（select score from student where name=‘liu’） ， 根据name=’liu’直接查询辅助索引树，就找到score了，不用再去查找主键索引树了（此时辅助索引树的叶子节点存储的是name,score,id），性能提高！但是注意，由于用name作为where的过滤条件，创建联合索引的时候，name列在前，score列在后。

2）单表查询索引的执行过程以及优化 - 普通查询+排序或者分组
       根据上面的例子，对于索引的执行过程应该有一些深入的了解了，那么继续看看下边的例子：
生成一个订单表，包含用户id，商品id，以及入购物车日期，如下：

给表中添加一组数据，如下：

执行下面的SQL查询语句：

可以看到，上面是查询id号位1的用户的购物信息，按日期升序排序。

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

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

那么给userid和date分别创建索引怎么样，如下：


重复上面的explain查询计划，看看和上面有什么不同：

       可见这次用到了索引，但是只用到了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在后，如下：

继续做上面的explain分析，如下：

       我们给userid和date创建联合索引后，就不会再出现using filesort了，根据userid=1查询辅助索引树，找到的数据也已经按date排序好了，然后在去主键索引树上去整行数据就完了，效率很高！
之所以使用force index（userid_date_index），是防止SQL优化器优化索引的使用，因为我们的测试数据量较少，有时候使用索引不一定比整表搜索快！
       如果上面的SQL只是select date，那么就不用再往主键索引树上查找了，在辅助索引树上就能找到date，所以select后面选择哪些列也是有讲究的。

3）多表查询-连接查询索引的执行过程以及优化
重新举一个例子，我们创建两张表，分别是runoob_tbl和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子句，那么就按条件过滤完的行数来定大小表！

4）多表查询-连接查询代替子查询
       子查询在多表联合查询时的效率是很低的，因为要产生中间表，多表查询最好优化成连接查询，效率很高！
比如拿下面的两张表做子查询操作：

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

二.索引失败

       上面第一部分列举了很多SQL语句，并分析了索引是怎么应用，怎么执行的，对于单表查询和多表连接查询中，索引的使用一定要牢牢掌握，这一部分看看哪些情况下，索引会失效！

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

runoob_author字段已经创建过索引

能用到索引！

用不到索引！

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

三.SQL和索引的优化

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毫秒。

2) 查看磁盘I/O读写的数据量

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

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

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

更多具体的SQL和索引优化措施，请看以下书籍：
《MySQL入门很简单》 第18章 《深入浅出MySQL》 第18章 《高性能MySQL》 第6章

四.优化总结

       上面的内容已经就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 ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。