MySQL的sql语句单表索引优化的实例

    上一篇我们简单介绍了，MySQL数据库的逻辑分层，索引以及执行计划的执行和分析执行计划结果，今天我们通过一个小栗子，来讲解下单表查询索引优化的实例。

    首先我们创建个临时表，以及准备几条数据。

create table book(
  bid int(4) primary key,
  name varchar(20) not null,
  authorid int(4) not null,
  publicid int(4) not null,
  typeid int(4) not null
);

insert into book values(1,'java',1,1,2);
insert into book values(2,'javaweb',2,1,3);
insert into book values(3,'mysql',3,3,3);
insert into book values(4,'spring',4,4,5);

    创建的表中只有主键，没有任何索引，我们接下来查询book表中，typeid为2或3的数据 且authorid是1的数据，由于是优化的例子，我们就不看结果，直接通过执行SQL语句的执行计划。

mysql> explain select bid from book where typeid in(2,3) and authorid = 1 order by typeid;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

    从执行结果来看，type是ALL，没有进行过任何优化，而且Extra中出现Using filesort，可见现在的sql是多么的可怕，下面我们就开始优化，首先我们就是需要加索引，那我们就把sql中出现的字段顺序，来加索引。

alter table book add index index_bta(bid,typeid,authorid);

mysql> alter table book add index index_bta(bid,typeid,authorid);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

    追加完索引后，我们再来运行执行计划。

mysql> explain select bid from book where typeid in(2,3) and authorid = 1 order by typeid;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | book  | NULL       | index | NULL          | index_bta | 12      | NULL |    4 |    25.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

    追加完索引后，type变成index，但是using filesort还是出现了，我们还可以继续优化，虽然性能有一些提升，但是，有个地方不是很合适，哪里呢，我们上一篇文章讲过，sql语句的解析过程是from。。。where。。。再是select。。。显然，索引字段的追加顺序是不对的，我们根据SQL实际解析的顺序，来调整索引的顺序，先删除索引，重新追加索引，这里有个疑问，为什么还是使用bid作为索引呢，bid放到索引中可以提升使用using index。

drop index index_bta on book；

alter table book add index index_tab(typeid,authorid,bid);

     然后重新执行下sql的执行计划

mysql> explain select bid from book where typeid in(2,3) and authorid = 1 order by typeid;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | book  | NULL       | range | index_tab     | index_tab | 8       | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

     从执行结果来看，using filesort已经不见了，并且type提升到range级别了，范围检索有时会索引实现，我们试试将in的检索条件放到最后来试试，继续优化，同样删除索引，将in条件放到最后

drop index index_tab on book；

alter table book add index index_atb(authorid,typeid,bid);

mysql> explain select * from book where authorid = 1 and typeid in(2,3) order by typeid;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | book  | NULL       | range | index_atb     | index_atb | 8       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

    执行结果是一样的，这样我们就完成了一次单表查询的优化。

总结

• 最佳左前缀，保持索引的定义和使用顺序要一致，即where条件的字段顺序要和索引的顺序是一致的
• 索引需要逐步优化
• 将含in的范围查询放到where条件的最后，防止失效