mysql sql优化实例1（force index使用）

今天和运维同学一块查找mysql慢查询日志，发现了如下一条sql:

SELECT sum(`android` + ios) total,pictureid,title,add_time FROM `juzi_access_statistic` LEFT JOIN juzi_news ON juzi_access_statistic.pictureid=juzi_news.id GROUP BY `pictureid` HAVING total >= 100000 AND pictureid >= 8092 AND title IS NOT NULL LIMIT 2;

该sql语句花费了 2.7s，那么总共多少条呢？

总共54万条记录，其实也不算太慢，但是我觉得应该有很大的优化空间。

一：先看一下表结构

CREATE TABLE `juzi_access_statistic` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `pictureid` int(11) NOT NULL DEFAULT '0' COMMENT '文章id',

  `date` date NOT NULL COMMENT '日期',

  `ios` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '苹果app访问量',

  `android` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '安卓app访问量',

  PRIMARY KEY (`id`),

  UNIQUE KEY `pictureid` (`date`,`pictureid`) USING BTREE,

  KEY `indexpictureid` (`pictureid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=‘访问统计表’;

二： 查看一下执行计划

在这里我发现了问题：juzi_access_statistic表竟然是全表扫描,pictureid字段上存在索引，为什么没有使用上呢？

我个人觉得原因是：因为存在条件pictureid >= 8092 和  juzi_access_statistic.pictureid=juzi_news.id（等价传递），所以mysql觉得使用juzi_news表的主键id查询效率是最高的。

三：使用force index 优化

看到效果了吧：key字段显示使用到了pictureid字段的索引，但扫描的行没有减少，执行时间如下：

四：正确使用where

   以上sql中的pictureid >= 8092 其实应该放到where子句中，以便过滤到更多的无用记录，修复后的执行计划如下：

效果也很明显：rows减少到不足10万，速度可想而知，见下图：

从开始的2.74s 优化到17ms, 优化了100倍