mysql 优化实例演示

mysql 优化实例演示

针对表ueb_ebay_message_send(myisam和innodb引擎的索引默认是B-tree索引)

 根据从正式服务器上抓取的超过2s的慢查询，先通过explain来看看查询计划

测试一

原始语句：

explain select pid,recipientid,itemid,content,opration_date,upload_status,opration_id,upload_date,ebay_accountid from ueb_ebay_message_send  where 1  and `opration_id`='肖双梅' and date_format(`opration_date`,'%Y-%m-%d')='2017-06-06'    limit 0,25;

执行计划如下图

type=ref，key=IX_ opration_id,ref=const,rows=93476

测试二

我按照查询条件，使用常用的办法，先使用语句加一个索引：

create index IX_ opration_date on ueb_ebay_message_send(opration_date, opration_id(10));

然后再查看查询计划，索引无效，

type=ref，key=IX_ opration_id,ref=const,rows=93576

结果发现新建的索引没有任何效果，这里证实了一个问题，date_format函数会影响mysql查询分析器对索引的正确判断。

测试三

强制查询走IX_opration_date索引

explain select pid,recipientid,itemid,content,opration_date,upload_status,opration_id,upload_date,ebay_accountid from ueb_ebay_message_send force index(IX_opration_date)  where 1  and `opration_id`='肖双梅' and date_format(`opration_date`,'%Y-%m-%d')='2017-06-06'    limit 0,25;

强制使查询分析器走IX_ opration_date索引。查看查询计划，发现mysql查询分析器连索引都不走了，全表扫描，即使强制制定使用新建的索引mysql都不予理会。

type=ALL，key=NULL,ref=NULL,rows= 1281658

测试四

修改一下语句，不用date_format函数，不强制使用索引

explain select pid,recipientid,itemid,content,opration_date,upload_status,opration_id,upload_date,ebay_accountid from ueb_ebay_message_send  where 1  and `opration_id`='肖双梅' and opration_date<'2017-06-07 00:00:00' and  opration_date>'2017-06-05 23:59:59'  limit 0,25;

查看查询计划，查询计划有变化了，使用到了新索引。

type=range，key= IX_opration_date,ref=NULL,rows=11266

在这里小结一下，rows=11266这个为了得到这个结果，mysql只需要扫描11266行，这是目前为止，mysql效率最高的查询计划，遗憾的是type=range，范围扫描，这个有点拉低了查询效率。

测试五

我重新再建立一个索引，只是把IX_opration_date索引的字段调换一下位置。

CREATE INDEX IX_opration_id_opration_date ON ueb_ebay_message_send(opration_id(10),opration_date);

再次从测试一的语句开始测试

type= ref，key= IX_opration_id_opration_date,ref=const,rows= 76473

这里可以看出来，查询效率低于测试四。虽然type变成ref了，但是rows值变大了很多，反而效率低了。

测试六

这个时候我使用测试四的语句再看看查询计划。

type=range，key= IX_opration_date,ref=NULL,rows=224

此时，rows 值才224行，效率一下就提升上来了。

总结：

1、使用DATE_FORMAT之类的函数对表字段计算，会导致mysql放弃相匹配的索引。

2、再进行范围查询时，如果where条件中还有其他字段等于某个值。那么建立索引时，索引字段顺序需要把范围查询字段放在索引字段的最后。

3、上面测试，针对的索引出现了多余，比如IX_ opration_id, IX_opration_id_opration_date，前一个索引只有一个字段，后一个索引有两个字段，但是他们的第一个字段都是一样的，那么第一个索引适用的范围更小，可以删除掉。