Mysql索引、回表、索引覆盖
1. 先说什么是索引?
索引是一种数据结构
百度百科是这样解释的: 索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她,则与在表中搜索所有的行相比,索引有助于更快地获取信息。 索引的一个主要目的就是加快检索表中数据,亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。
不同引擎对索引的实现方式不同,InnoDB采用B+树作为索引结构。
2.聚簇索引、非聚簇索引
索引可分为聚簇索引和非聚簇索引两种。
聚簇索引(Clustered Index):聚簇索引的数据的物理存放顺序与索引顺序是一致的。
非聚簇索引(二级索引)(Secondary Index):非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系,与数据的存储物理结构没有关系。
每个InnoDB的表中聚簇索引有且只有一个,而非聚簇索引可以有多个。
聚簇索引按照如下规则创建:
- 当定义了主键后,InnoDB会利用主键来生成其聚簇索引;
- 如果没有主键,InnoDB会选择一个非空的唯一索引来创建聚簇索引;
- 如果这也没有,InnoDB会隐式的创建一个自增的列(rowid)来作为聚簇索引。
3. 回表
举个栗子
假设有个t表(id PK, name KEY, sex, flag),这里的id是聚集索引,name则是普通索引。
表中有四条记录:
id | name | sex | flag |
1 | sj | m | A |
3 | zs | m | A |
5 | ls | m | B |
9 | ww | f | A |
聚集索引的B+树索引(id是PK,叶子节点存储行记录):
普通索引的B+树索引(name是KEY,叶子节点存储PK值,即id):
此时使用不同索引对第三条记录进行查询:
1.
select name,sex from t where id = 5
其执行过程是:
可看出聚簇索引查询的会很快,因为可以直接从索引树中取到想要的数据。
select name,sex from t where name = "ls"
其执行过程是:
可以看出需要扫描两遍索引树,第一遍先通过普通索引定位到主键值id=5,然后第二遍再通过聚集索引定位到具体行记录。这就是所谓的回表查询,即先定位主键值,再根据主键值定位行记录,性能相对于只扫描一遍聚集索引树的性能要低一些。
这就是所谓的回表,即先定位主键值,再根据主键定位行记录。
那么,如何避免回表?
答:索引覆盖(Cover Index)
4. 索引覆盖
如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index),也就是所说的不需要回表操作。简单来说就是将被查询的字段,建立到联合索引里去。
覆盖索引的定义与注意事项
如果一个索引覆盖(包含)了所有需要查询的字段的值,这个索引就是覆盖索引。因为索引中已经包含了要查询的字段的值,因此查询的时候直接返回索引中的字段值就可以了,不需要再到表中查询,避免了对主键索引的二次查询,也就提高了查询的效率。
要注意的是,不是所有类型的索引都可以成为覆盖索引的。因为覆盖索引必须要存储索引的列值,而哈希索引、空间索引和全文索引等都不存储索引列值,
另外,当发起一个被索引覆盖的查询(索引覆盖查询)时,在explain(执行计划)的Extra列可以看到【Using Index】的信息。
覆盖索引的优点
1.索引条目通常远小于数据行的大小,因为覆盖索引只需要读取索引,极大地减少了数据的访问量。
2.索引是按照列值顺序存储的,对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO小很多。
3.一些存储引擎比如MyISAM在内存中只缓存索引,数据则依赖操作系统来缓存,因此要访问数据的话需要一次系统调用,使用覆盖索引则避免了这一点。
4.由于InnoDB的聚簇索引,覆盖索引对InnoDB引擎下的数据库表特别有用。因为InnoDB的二级索引在叶子节点中保存了行的主键值,如果二级索引能够覆盖查询,就避免了对主键索引的二次查询。
- 哪些场景适合使用索引覆盖来优化SQL
1.全表count查询优化
mysql> create table user(
-> id int(10) auto_increment,
-> name varchar(30),
-> age tinyint(4),
-> primary key (id),
-> )engine=innodb charset=utf8mb4;
例如:select count(age) from user;
使用索引覆盖优化:创建age字段索引
create index idx_age on user(age);
2.列查询回表优化
前文在描述索引覆盖使用的例子就是
例如:select name,sex from t where name = “ls”
使用索引覆盖:建组合索引idx_age_name(name,sex)即可。
3.分页查询
例如:select id,age,name from user order by age limit 100,2;
name字段不是索引,所以在分页查询需要进行回表查询,此时Extra为Using filesort文件排序,查询性能低下。
使用索引覆盖:建组合索引idx_age_name(age,name)