使用explain分析SQL索引使用情况

在my.ini中通过

long_query_time=1

log-slow-queries=/mnt/mysql_log/mysqlslow.log

把执行时间超过1s的sql记录在慢查询日志中.

通过慢查询日志可以找到执行较慢的sql语句,找到之后可以使用mysql explain分析sql语句查出索引使用情况在进行优化。

建立一张’pt_xiaocai’表并写入50w条数据,用于测试.

CREATE TABLE `pt_xiaocai` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`userid` int(11) DEFAULT NULL,

`username` varchar(50) DEFAULT NULL,

`caption` varchar(200) DEFAULT NULL,

`explain` varchar(300) DEFAULT NULL,

`time` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=523956 DEFAULT CHARSET=utf8;

输入:

mysql> explain  select * from pt_xiaocai where userid=2148;

从结果中可以看出,本次查询进行了全表扫描并且没有用到索引.这是查询中效率最低的要尽量避免全表扫描.

type=ALL :全表扫描

key=NULL:未用到索引

Using where: 使用where条件限制匹配返回给客户端(当查询的extra字段值不是 using where 并且表连接类型是 all 或 index时可能表示有问题)

输入:

mysql> explain  select * from pt_xiaocai where id=2148;

使用主键索引一次就匹配到了数据,性能很高的查询。

type=const : 表示通过索引一次就找到了

key=PRIMARY: 表示使用主键索引

输入:

alter table pt_xiaocai add index userid(userid);

explain  select * from pt_xiaocai where userid=2148;

可以看出添加了userid字段索引后,查询从全表扫描变成了根据索引多行匹配。

type=ref : 多个匹配行

key=userid : 使用了userid索引

possible_keys : 索引名称

输入:

explain  select * from pt_xiaocai where time=1385950873 and userid=2148;

在单索引的情况下,mysql会先将带索引字段进行搜索然后将数据存放在结果集中,然后依次过滤掉其它条件数据,最后赛选出唯一符合条件的记录。

如上语句查询时mysql自动将条件顺序调整为 userid=2148 and time=1385950873 ,先根据索引查询出userid=2148数据然后在根据其它条件剔除数据。

这样查询的时候mysql不用扫描整张表,查询效率有所提高,但如果userid=2148匹配的数据较多时可能还是达不到性能要求。

ps:

在有些特定的情况下这种单索引的情况也有可能触发全表扫描,如: columnB为索引字段,然后 columnA > 1000 and columnB > 200,最好是使用explain查看下索引情况。

输入:

alter table pt_xiaocai add index time(time);

explain  select * from pt_xiaocai where time=1385950873 and userid=2148;

若这时候给time字段也加上索引,type就变为了index_merge,表示有一条sql中包含多个索引。

但mysql每次查询只能使用一个索引,虽然这样比全表扫描效率要高,但如果将userid和time做为一个复合索引效率将更好。

ps:

   如果是time=1385950873 or userid=2148

   将多个列组合成一个索引要比每个列单独建一个索引效率要好的多。

   每列单独建立索引将占用更多的磁盘空间,并且在更新数据速度也会慢很多。

index_merge主要分为两大类,多个索引交集访问(intersections),多个索引并集访问(union)

输入:

alter table pt_xiaocai add index `index`(userid,caption,time);

explain select * from pt_xiaocai where time=1385950873 and userid=2148;


将’userid,caption,time’建立成一个复合索引,type变为ref了,引用的索引为index.

此时查询条件为’userid=x’,’userid=x and caption=x’,’userid=x and caption=x and time=x’,’userid=x and other=x’都能够使用index索引

这是因为 mysql 复合索引“最左前缀”的结果,简单的理解就是只从最左面的开始组合,如下这条sql语句则无法使用index索引了.

explain select * from pt_xiaocai where time=1385950873 and  `explain`=’TEDSGLSDGJLSKDGHSDGS2148′;


ps: 因为“最左前缀”的结果所以在建立索引的时候应当按使用频率由左往右排序。

其它说明:

1.explain的type显示的是访问类型,一项重要的指标,从好到坏依次的排序为:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

2.若where条件中带有!=时则无法使用索引

explain select * from pt_xiaocai where userid!=2148;