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，前一个索引只有一个字段，后一个索引有两个字段，但是他们的第一个字段都是一样的，那么第一个索引适用的范围更小，可以删除掉。
