MySQL索引详解

create table index_demo(
	c1 int,
	c2 int,
    c3 char(1),
    primary key(c1)
)ROW_FORMAT=COMPACT;

1 Record type

• 0：普通记录

• 1：B+树非叶子节点的记录

• 2：Infimun

• 3：Spuremum

2 下页大于上页的主键

insert into index_demo values
	(1,4,'u'),
	(3,9,'d'),
	(5,3,'y');

insert into index_demo values
	(4,6,'p');

先把比这个大的记录搬运到其他数据页，然后再插入这条记录

这个过程叫做页分裂.

当这个页数量很多的时候，我们是时候需要创建一个页面索引来加快查询速度了。

索引页中的记录的 record type 就是1了，注意还有之前提到过的min_rec_flag也是1.

我们真正的记录都存在B+树最底层的叶子节点上。

3 InnoDB索引

3.1 索引分类

3.1.1 聚簇索引

• 页内记录按照主键大小顺序排成一个单链表，页内划分为多个组，每个组中主键最大的记录的偏移量会放到页目录的槽位中，使用二分法快速定位到主键等于某个值的记录。

• 各个存放记录的页根据页中记录主键的大小排成一个双向链表。

• 存放目录项记录的页分为不同的层级，同级中的页根据主键大小排序成双向链表。

• B+树叶子节点存储的是完整的用户记录。

3.2.2 二级索引

在c2列上建立二级索引

• 如上述，把叶子节点中的数据换成单只有一个主键。

• 所有按照主键的排序规则，全部换成按照索引列排序。

• 按照索引列找到叶子节点上的主键值，然后去聚簇索引上按照主键再查一遍。

• 如果二级索引列为非UNIQUE的，在两条记录c2列相等情况下，需要按照主键来进行排序，所以虽然看上去是二级索引，其实是二级索引列与主键组成的联合索引。

• 在索引列要求 NOT NULL , UNIQUE 的情况下，才是真正的二级索引。

3.2.3 联合索引

使用c2,c3两列做联合索引的情况下

• 如同二级索引，叶子节点依然存储主键。
• 排序规则换成先按照c2排序，c2一样的按照c3排序，c2、c3两列都一样，按照主键排序，所以还是需要主键参与的。

3.2.4 B+树形成过程

• 每当为表创建一个B+树索引时，都会为索引创建一个根页面，这个根页面创建好之后，页号就不会再发生变化！！！
• 最开始的时候表中没有数据，每个B+树都不会有用户记录，也不会有目录项记录
• 插入过程中，先把记录插入到根节点中
• 根节点空间用完的时候，，会把所有数据复制到新创建的页面中，然后对这个页面进行分裂，得到两个页面，然后插入记录
• 此时根节点中加入新创建页面的目录项记录

一个页面中至少存在两条记录

4 MyISAM索引

将表中的记录按照顺序存在单独的文件中

索引信息单独存到一个文件中，索引叶子节点存储的是主键值+行号。

MyISAM的索引都需要一次回表，因为所有的索引都是相当于二级索引

5 创建索引语句

create table index_demo(
	c1 int,
	c2 int,
    c3 char(1),
    primary key(c1),
    key (c2),        # 创建索引
    index (c3)       # 创建索引
)ROW_FORMAT=COMPACT;

alter table 表名 add index 索引名 (c2);  # 创建索引
alter table 表名 add key 索引名 (c2);	# 创建索引

alter table 表名 drop index 索引名 (c2); #删除索引
alter table 表名 drop key 索引名 (c2);	#删除索引

6 索引的代价

6.1 空间代价

每个索引的非叶子节点都需要额外的存储空间

6.2 时间代价

• 增删改查可能对节点和记录的排序造成破坏，需要额外的时间进行页面分裂、页面回收等操作，用来维护记录的顺序准确性

• 计算使用不同索引进行查询的执行计划的成本

create table single_table (
	id int not null auto_increament,
    key1 varchar(100),
    key2 int,
    key3 varchar(100),
    key_part1 varchar(100),
    key_part2 varchar(100),
    key_part3 varchar(100),
    common_feild varchar(100),
    primary key (id),
    key index_key1 (key1),
    UNIQUE key index_key2 (key2),
    key index_key3 (key3),
    key index_part (key_part1, key_part2, key_part3)
)

7 索引的使用

7.1 扫描区间和边界条件

7.1.1 聚簇索引

select * from single_table where id >= 2 and id <= 100;

查找[2, 100]之间的聚簇索引记录。

通过主键找到 id = 2的记录，然后沿着单向链表一直找到 id = 100的记录。

7.1.2 二级索引

select * from single_table where key2 in (1438, 6328) or (key2 >= 38 and key2 <= 79);

• 区间1：[1438, 1438]
• 区间2：[6238, 6238]
• 区间3：[38, 79]

由于这是二级索引，所以需要回表操作

7.1.3 不参与边界条件

select * from single_table where key1 < 'a' and key3 >= 'z' and common_feild = 'abc'

使用 key1进行查询，key2和common_feild就是普通搜索条件，形成的搜索区间就是 (-MIN, a).

只要是索引列使用= != >= <= IN NOT IN IS NULL IS NOT NULL BETWEEN LIKE都可以形成扫描区间

7.1.4 更多案例

详见小孩子4919的《MySQL是怎样运行的》110页

7.2 索引用于排序

注意事项

• 联合索引：ORDER BY子句后面的内容一定按照索引列顺序给出，且不能跳过列
• 不能 ASC、DESC混用
• 排序列包含多个非联合索引列
• 形成扫描区间的索引列与排序列不同
• 排序列不是以单独列名字的形式出现：ORDER BY Upper(key2)

8 索引用于分组

select key_part1, key_part2, key_part3 ,count(1)
	from single_table
	group by key_part1, key_part2, key_part3

• 步骤一：收集到的结果先按照 key_part1分组
• 步骤二：将一的结果在按照 key_part2 分成更小的组
• 步骤三：将二的结果在按照 key_part3 分成更更小的组

统计小小小组的条数。

如果没有index_part 索引的话，需要先建立一个临时表，扫描聚簇索引的过程中把结果放到临时表中。

当有了索引之后，index_key_part的二级索引列的值也是排好序的，就可以使用索引进行分组，就不用临时表了。

9 回表的代价

select * from single_table where key1 > 'a' and key1 < 'c';

两种方式：

• 全表扫描，找到条件成立的发送到客户端
• 使用index_key1索引回表查询：

数据页会被存放到磁盘中的一个或者多个文件中，页面中的页号对应着该页在磁盘中的偏移量。二级索引中叶子节点中的主键排序是无序的。无序的主键去做回表，回表的页号也是无规律的。这样的回表操作会造成大量的随机I/O。

在大量随机I/O的情况下，优化器越倾向于使用全表扫描。

一般情况下，指定LIMIT子句来限制查询返回记录数。

10 索引使用原则

• 只用于搜索、排序或者分组创建索引

• 索引列尽可能小

  一次I/O可以读取跟多的记录加载到内存中，读写效率越高。

• 索引列中尽可能少的有重复值

  某个列的重复值越多，则在区间扫描的时候能命中的记录数也越多，回表代价也越高

• 前缀索引

  100个字符的列，后90个字符都一样，只有前10个字符有区分度，则可以使用前缀索引

  alter table single_table add index index_key1 (key1(10));
  

  但是前缀索引无法支持索引排序

  select * from single_table ORDER BY key1;
  

  老老实实走全表扫描。

• 覆盖索引

  为了彻底规避回表操作，最好查询列就包含在这个二级索引中

  select key1, id from single_table where key1 < 'a' and key1 > 'c';
  

• 让索引列单独出现

  select * from single_table where key2 * 2 < 4;  # 全表扫描
  select * from single_table where key2 < 4 / 2;
  

• 新插入记录时主键大小对效率的影响

  因为插入的主键值如果一直是增大的，就会规避掉页分裂的操作。