0、导读
饱受诟病的InnoDB表COUNT(*)性能问题在5.7下做了优化,果真如此吗?
1、经典需求:InnoDB表COUNT(*)
InnoDB引擎表经常被抱怨执行COUNT(*)的效率太差,因此此类需求通常会被建议用其他方法来满足,比如另外加一个计数器表,或者用SHOW TABLE STATUS查看大概数量。
不过,从MySQL 5.7.2起,这个问题得到了解决,我们来看看。
2、MySQL 5.7版本InnoDB对COUNT(*)的优化
MySQL每发布一个新版本,都会放出相应的Release Notes,我们注意到5.7.2版本的发布说明中提到:
InnoDB:
SELECT COUNT(*) FROM t
statements now invoke a single handler call to the storage engine to scan the clustered index and return the row count to the Optimizer. Previously, a row count was typically performed by traversing a smaller secondary index and invoking a handler call for each record. A single handler call to the storage engine to count rows in the clustered index generally improvesSELECT COUNT(*) FROM t
performance. However, in the case of a large clustered index and a significantly smaller secondary index, performance degradation is possible compared to performance using the previous, non-optimized implementation. For more information, see Limits on InnoDB Tables.