MySQL的单表索引优化案例

1. 创建数据表及插入数据

create table if not exists article(
	id int(10) unsigned not null primary key auto_increment,
	author_id int(10) unsigned not null,/*作者编号*/
	category_id int(10) unsigned not null,/*种类编号*/
	views int(10) unsigned not null,
	comments int(10) unsigned not null,
	title varchar(255) not null,
	content text not null
);
insert into article(author_id,category_id,views,comments,title,content) values
(1,1,1,1,'1','1'),(2,2,2,2,'2','2'),(1,1,3,3,'3','3');

2. 问题提出:查询category_id为1且comments大于1的情况下,views最多的article_id
3. 问题分析:可以将category_id和comments作为where的查询条件,根据views排序,限制显示一条信息即可
4. SQL语句:

select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;

查询是可以的:
5. 使用 explain 关键字进行分析:
出现了type=ALL和Extra 含有 using filesort 情况,这是我们极其不愿意看到的全表查询和使用文件排序,产生临时表,key为NULL表示实际上也没有用到索引
5. 在创建表的时候是没有创建索引的,接下来我们为表创建多值索引(因为我们需求用到了其中三个索引)

create index ccv on article(category_id,comments,views);

6. 再来执行上面 explain分析语句:
   
7. 显然type从All变为了 range,这是我们希望看到的,但是,从extra列看,还存在使用文件排序的情况,我们知道,使用filesort原因是索引值存在范围情况,不唯一,如果我们把 comments > 1 改成 comments = 1,我们再来看:
   type直接从 range 级别到了 ref 级别,也不存在 filesort 了,所以给我们一个提示,存在 filesort 是因为comments这一查询条件不唯一,接下来我们把索引改一下,去掉comments这一列试试:

drop index ccv on article;
create index cv on article(category_id,views);

8. 在来看 explain 执行情况:
   这样,再次查询需求完成了,性能方面也符合了需要,这仅仅是一个索引优化的小案例,希望对大家有帮助!