MySQL-索引优化案例

单表优化

建表

create table article(
id int unsigned not null primary key auto_increment,
author_id int unsigned not null,
category_id int unsigned not null,
views int unsigned not null,
comments int unsigned not null,
title varchar(255) not null,
content text not null
);
           

插入数据

insert into article(`author_id`,`category_id`,`views`,`comments`,`title`,`conte
nt`) values
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');
           

现在比如说我们要查询category_id为1且comments大于1的情况下,views最多的article_id

我们要完成这个功能应该使用的sql语句为

我们使用explain分析这条sql语句发现出现了Using filesort,说明mysql使用了外部的索引排序，这是不好的，

我们应该通过添加索引来优化他，那么这个索引应该添加到哪个列上呢。

前面讲过使用索引查询时范围之后全失效。所以不能添加在comments这个列上，那我们尝试着添加在category_id和views上。再进行explain语句分析

发现Using filesort没了，那么便是添加的索引有效了。

双表优化

建表

商品类别
create table class(
	id int unsigned not null primary key auto_increment,
	card int unsigned not null
);
图书表
create table book(
	bookid int unsigned not null auto_increment primary key,
	card int unsigned not null
);
           

添加数据

在MySQL中rand函数表示生成随机小数，floor表示将数向下取整

insert into class values(0,floor(rand()*100));
insert into book values(0,floor(rand()*100));
           

我们将这两条数据分别执行20次，向两个表中随机插入20条数据

现在比如说我们要查找两个表中card相同的数据

并使用explain分析该sql语句，会发现出现了Using join buffer,使用了连接缓存。那么我们也得创建索引来进行优化。那么我们应该在哪个表上创建索引呢。

当使用左连接left join时，应该往右表book上添加索引

此时再使用explain分析sql语句发现Using join buffer不见了。

同理使用右连接right join时，应该往左表添加索引

使用inner join时随便向一个表添加索引即可

这样添加的原因主要是因为：

驱动表的概念，mysql中指定了连接条件时，满足查询条件的记录行数少的表为驱动表；如未指定查询条件，则扫描行数少的为驱动表。mysql优化器就是这么粗暴以小表驱动大表的方式来决定执行顺序的。