MySQL 索引下推

索引下推(Index Condition Pushdown,简称ICP)是MySQL优化查询性能的一种技术,它可以在索引上进行查询过滤,只将符合条件的数据行读取到内存中,而不用将所有的数据行都读取到内存中再进行查询过滤,从而减少了IO操作,提高了查询性能。

举个例子,假设有如下表结构:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `age` tinyint(4) NOT NULL DEFAULT '0',
  `gender` tinyint(4) NOT NULL DEFAULT '0',
  `email` varchar(50) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_age_gender` (`age`,`gender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我们想要查询年龄为18岁、性别为男性、邮箱以“abc”开头的用户信息,使用如下SQL语句:

SELECT id, name, age, gender, email FROM user WHERE age=18 AND gender=1 AND email LIKE 'abc%';

如果没有索引下推,MySQL会使用idx_age_gender索引先查找年龄为18岁、性别为男性的用户信息,然后再将所有符合条件的数据行读取到内存中,再对其中邮箱以“abc”开头的数据行进行过滤,最后将符合条件的数据行返回给客户端。这种方式会导致大量的数据行被读取到内存中,从而降低了查询性能。

如果使用索引下推,MySQL会在索引idx_age_gender上进行查询,只将符合条件的数据行的id值读取到内存中,然后再根据id值到主键索引上读取完整的用户信息,并对邮箱以“abc”开头的数据行进行过滤,最后将符合条件的数据行返回给客户端。这种方式只读取了必要的数据行,减少了IO操作,提高了查询性能。