老革命碰到新问题,之前做了一个系统状态监控,其中需要统计一个资讯表的实时数据量,其实总量并不大,也就7万多条,但是感觉执行效率很低,浏览器刷新挺长时间才出来。今天空下来找找原因,对每个查询做了一下时间分析,吓我一跳。
在执行total统计的时候,时间太长了,看了一下语句:
SELECT COUNT(*) FROM ai_news
没毛病啊,从Mysql进行explain 语句,结果是秒出的:
不过这里有个问题,使用了 n_s_time这个索引,返回的结果是不对的,因为n_s_time不是Unique索引。而这个表里有好几个字段都是索引。
然后就是不断百度学习,先总结知识点:
一、InnoDB模式对行数是不计数的,MyISAM是计数的,所以select count(*) from table 这么写在MyISAM表里是没有毛病的,会很快。
二、InnoDB模式对count(*)也有优化,就是会找最短索引,像我的表中n_s_time的索引最短,原则上会找这个索引,但是实际显然不对,所以Explain是不准的。
三、InnoDB模式对PRIMARY索引的select count(*)是非常慢的,切记,如果需要select count(*),一定要另外再建立一个索引。
好了,知识点基本就这几个,网上的方法基本上就是到建立索引了。但是我试下来,因为我的表是多个索引,MySQL找不到正确的索引来计数。于是我测试了用use index或者force index,结果都不行。
SELECT COUNT(*) FROM ai_news use index (n_sid)
SELECT COUNT(*) FROM ai_news force index (n_sid)
n_sid 是一个varchar 的unique索引。 Mysql的执行还是不按我的思路来,头疼,感觉use index应该是无效的,可能必须在where子句里才能发挥作用。
于是乎我灵光一现,是不是可以用where子句来强制Mysql使用索引?
SELECT COUNT(*) FROM ai_news where n_sid<>''
结果非常完美,秒出了。
这才是正确状态嘛,哈哈,总结一下:
当MySQL在InnoDB模式下需要对表table做select count(*) from table的时候,一、必须建立二级索引,二、对二级索引字段使用where子句,让Mysql使用索引进行统计以提高执行效率。