一般情况下我们写 SQL 语句不会去选择索引。
我们先建个简单表,并往里面添加 100000 行数据,例如:(1,1,1)。。。(100000,100000,100000)
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;
当想查看一条 SQL 语句的执行情况时,可以选择 expain 命令。比如:
explain select * from t where a between 10000 and 20000;
可见,key 为 a ,表示使用的是 a 这个索引。可有时却会使用错。
rows 这个字段表示预计扫描的行数
优化器的逻辑
优化器的目的是找到一个最优的解决方案,并用最小的代价去执行语句。
其中,扫描行数是影响执行代价的因素之一。
扫描行数是怎么判断的呢?
MySQL 在真正执行语句之前不会知道扫多少行,只能根据统计信息来估算记录数。
这个就是索引的区分度。一个索引上不同的值越多,这个索引的区分度就越高。一个索引上不同值的个数,我们称为“基数”(cardinality)。基数越大,索引的区分度越好。
可以使用 show index
的方法来,看到索引的基数。但这个数往往不是准确的。
那 MySQL 怎么得到的这个基数呢?
如果把整张表拿出来统计,开销太大了。于是使用“采样统计”方法。
InnoDB 会默认选择 N 个数据页,统计这些页上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这索引的基数。
而表上的数据会进行更新的,所以,当变更的数据行数超过 1/M 的时候,会自动触发一次索引统计。
在 MySQL 中有两种索引统计方式,可以设置 innodb_stats_persistent
的值来选择:
- 设置为 on 时,表示统计信息会持久化,默认 N 为 20,M 是 10;
- 设置为 off 是,表示统计信息只会放在内存中,默认 N 是 8,M 是 16。
不论是哪一种,这个基数很容易不准。
其他的判断因素
当扫描器认为,在普通索引上要扫 37000 行,主键索引要扫 100000 行,也有可能去扫主键。
因为,扫描器认为,每次从普通索引上拿一个值,就要去主键索引上查出整行数据,这个代价优化器也要算进去。直接在主键索引上扫,没有额外开销。
我们可以用 analyze table t
来重新统计索引信息。这样会好一点。
索引选择异常和处理
第一种方式,直接在 SQL 语句中加入 force index(indexName)
强行选择索引。但是不推荐,原因如下:
- 这么写不好看,而且索引改了名字,这个语句也得改
- 变更的及时性。一般来说,选错索引情况比较少,在开发的时候不会先写上
force index
。而是等到上线出现问题了才会修改 SQL 语句。
第二种方式,可以修改 SQL 语句,引导 MySQL 使用我们期望的索引。
比如我们可以把 order by b limit 1
改成 order by b,a limit 1
,语义是相同的。
之前优化器选择使用索引 b,是因为它认为使用索引 b 可以避免排序(b 本身是索引,已经是有序的了,如果选择索引 b的话,不需要再做排序,只需要遍历),所以即使扫描行数多,也判定为代价更小。
现在 order byb,a
这种写法,要求按照 b,a 排序,就意味着使用这两个索引都需要排序。因此,扫描行数成了影响决策的主要条件,于是此时优化器选了只需要扫描 1000 行的索引 a。
当然,这种修改并不是通用的优化手段,只是刚好在这个语句里面有 limit 1,因此如果有满足条件的记录, order byb limit 1
和 order byb,a limit 1
都会返回 b 是最小的那一行,逻辑上一致,才可以这么做。
如果你觉得修改语义这件事儿不太好,这里还有一种改法。
select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1;
在这个例子里,我们用 limit 100 让优化器意识到,使用b索引代价是很高的。其实是我们根据数据特征诱导了一下优化器,也不具备通用性。
第三种方式是,可以单独给场景建一个更适合的索引,给优化器选择。
不过,在这个例子中,我没有找到通过新增索引来改变优化器行为的方法。这种情况其实比较少,尤其是经过 DBA 索引优化过的库,再碰到这个 bug,找到一个更合适的索引一般比较难。
如果我说还有一个方法是删掉索引 b,你可能会觉得好笑。但实际上我碰到过两次这样的例子, 最终是 DBA 跟业务开发沟通后,发现这个优化器错误选择的索引其实根本没有必要存在,于是就删掉了这个索引,优化器也就重新选择到了正确的索引。
——引用自大佬原话