覆盖索引(Covering Index)

索引可以用来找到行,也可以用来直接读取需要的数据,因为索引的叶子节点保存了索引的数据。包含查询所需的所有数据的索引称为覆盖索引(Covering Index)。

使用覆盖索引在某些情况下可以显著的提升性能:

  • 对于类似InnoDB这样的引擎来说,二级索引的叶子节点保存了主键值,覆盖索引可以避免回表过程
  • 索引按照索引列进行排序,顺序扫描比随机地从磁盘读取每一行(回表)要要快得多
  • 索引记录通常小于全行大小,因此,如果只读取索引,可以极大地减少数据访问量,进而可以更好的利用缓存

覆盖索引必须保存它所索引的数据,哈希索引、空间索引、全文索引等不会保存这些值,因此只能使用B-Tree索引来覆盖查询。

我们来看一个例子。

mysql> show create table salaries;  
+----------+--------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                 |
+----------+--------------------------------------------------------------------------------------------------------+
| salaries | CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  KEY `idx_date` (`from_date`,`to_date`),
  KEY `idx_date_salary` (`from_date`,`salary`),
  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(1) from salaries;  
+----------+
| count(1) |
+----------+
|  2844047 |
+----------+
1 row in set (0.41 sec)  

表salaries共有数据284w行,我们来执行一条SQL:

mysql>  select sum(salary)/count(1) from salaries force index(idx_date_salary)  where from_date > '1999-1-1' group by emp_no;  
...
|           74125.0000 |
+----------------------+
244333 rows in set (1.54 sec)  

需要注意的是,这条SQL里强制指定了索引idx_date_salary,对这条SQL来说,所有涉及到的字段都可以从idx_date_salary中读取到(包括emp_no,主键也会存储在索引中),所以是不需要回表操作的。

作为对比,我们选择索引idx_date,那么这条SQL需要回表读取salary:

mysql>  select sum(salary)/count(1) from salaries force index(idx_date)  where from_date > '1999-1-1' group by emp_no;  
...
|           74125.0000 |
+----------------------+
244333 rows in set (8.30 sec)  

由这个例子可见,回表产生的代价非常大;而覆盖索引带来的性能提升也十分明显。

flacro

Read more posts by this author.