看到一篇博文说,当查询扫描的数据超过了全表的20%,优化器就不使用索引,而是做全表扫描。这个我之前还不知道,于是也想测试一下。我的MySQL 版本 5.7.24 。
表结构:
mysql> show create table t_1; CREATE TABLE `t_1` ( `id` int(11) NOT NULL, `name` varchar(10) DEFAULT NULL, `phone` int(11) DEFAULT NULL, `addr` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `phone` (`phone`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
该表总共10000行,id 和phone这一列的值从0开始递增。
mysql> select * from t_1 limit 10; +----+-------+-------+-------+ | id | name | phone | addr | +----+-------+-------+-------+ | 0 | dong0 | 0 | addr0 | | 1 | dong1 | 1 | addr1 | | 2 | dong2 | 2 | addr2 | | 3 | dong3 | 3 | addr3 | | 4 | dong4 | 4 | addr4 | | 5 | dong5 | 5 | addr5 | | 6 | dong6 | 6 | addr6 | | 7 | dong7 | 7 | addr7 | | 8 | dong8 | 8 | addr8 | | 9 | dong9 | 9 | addr9 | +----+-------+-------+-------+
首先测试2001行是否使用索引。
没有使用索引。再测试2000行的情况。
怎么也没有使用索引?既然不是20%,那是不是其他的值呢?经测试这个值在1700。
当扫描的行数超过1700行就会使用全表扫描。
也就是说当一个查询扫描的行数超过17%,就会放弃索引扫描使用全表扫描。
但这个规则对主键(聚族索引)并不适用。