上一篇我们简单介绍了,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条件的最后,防止失效