宋涛的博客
所有文章 标签 分类 Go Linux 分布式 架构 关于
宋涛的博客

目录

7 MYSQL 索引

 收录于 存储
   约 2858 字   预计阅读 6 分钟 

B+树索引

/images/mysql/MySQL45%E8%AE%B2/innodb_index.png

1. InnoDB 的索引模型

实现索引的方式有很多方式，N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

  1. 主键索引: 又称聚簇索引 clustered index，叶子节点保存的是整行数据。
  2. 非主键索引: 又称为二级索引 secondary index，叶子节保存的是主键的值。InnoDB会把主键字段放到索引定义字段后面， 当然同时也会去重。

下面是一个示例，可以帮助我们理解主键，非主键索引的关系:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

索引:

  • 主键: 是 a,b 字段的聚簇索引，相当于 order by a,b
  • 索引 c: 按 c 排序，同时记录主键，因为主键有排序，所以相当于 order by c,a,b
  • 索引 ca: 先按 c 排序，再按 a 排序，同时记录主键，主键部分是 b，不是 ab，而是只有 b,相当于 order by c,a
  • 索引 cb: 先按 c 排序，在按 b 排序，同时记录主键，主键部分只有 a，相当于 order by c,b

所以索引 ca 这里是重复的，应该被删除。

1.1 主键的选择

由于树的有序性，并且每个叶子节点对应的数据页所能容纳的行数是有限制的，因此在数据的插入和删除过程中就会发生页的分裂和合并，因而会影响数据更新的性能。所以大多数情况下我们都建议使用自增主键

自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。同时每个非主键索引的叶子节点上都是主键的值。显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

1.2 索引优化

B+树索引有四种常见的优化方式:

  1. 覆盖索引: 索引包含的字段能够覆盖查询的需求，不用回表进行二次查询
  2. 左前缀索引: 合理调整索引字段的顺序，可以提高索引复用率，减少索引个数
    • 常用的查询字段或者查询组合应该应该靠前
    • 其次，考虑空间因素，例如name 字段是比 age 字段大的 ，应该创建一个（name,age) 的联合索引和一个 (age) 的单字段索引，而不是 (age, name) (name) 两个索引
  3. 索引下推: MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
  4. 索引可能因为删除，或者页分裂等原因，导致数据页有空洞，适时地重建索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。
  5. 对于主键索引，不论是删除主键还是创建主键，都会将整个表重建，因此主键索引的重建应该使用 alter table T engine=InnoDB，而不是 drop primary key, add primary key

2. 普通索引与唯一索引

通过上面我们已经知道了Innodb B+ 树索引的基本机构，那对于普通索引和唯一索引我们应该怎么选择呢。我们就从这两种索引对查询语句和更新语句的性能影响来进行分析。

2.1 查询分析

假设我们要执行 select id from T where k=5，字段 k 上有索引

  1. 对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
  2. 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

我们知道 InnoDB 的数据是按数据页为单位来读写的，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。所以二者的性能相差微乎其微

2.2 更新分析

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。而这必须要将数据页读入内存才能判断。因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

2.3 索引选择

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响，所以，我建议你尽量选择普通索引。即使是对于 changer buffer 不适用的场景，比如所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。

特别地，在使用机械硬盘时，change buffer 这个机制的收效是非常显著的。所以，当你有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

最后关于普通索引和唯一索引的选择，首先，业务正确性优先。如果需要数据库保证数据唯一性，肯定是优先选择唯一索引

在一些“归档库”的场景，你是可以考虑使用普通索引的。比如，线上数据只需要保留半年，然后历史数据保存在归档库。这时候，归档数据已经是确保没有唯一键冲突了。要提高归档效率，可以考虑把表里面的唯一索引改成普通索引。

3. 如何给字符串添加索引

MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。指定前缀创建索引的好处是占用的空间会更小，可能会增加额外的记录扫描次数。

选择多长的前缀作为索引取决于索引的区分度，区分度越高，重复值越少，检索效率越高。长度越长区分度肯定越好，但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# 计算待索引列上有多少不同的值
mysql> select count(distinct email) as L from SUser;

# 查看不同长度前缀有多少不同的值
mysql> select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5%，在可接受的范围内选择最短的长度构建索引。

除了区分度的影响，因为系统并不确定前缀索引的定义是否截断了完整信息，所以总是要回表查询整行数据，因此也就无法使用覆盖索引。这也是是否使用前缀索引需要考虑的因素。

有时候，我们会遇到前缀的区分度不够好的情况，使用太长的前缀，又会占用太多的存储空间。此时我们可以采用倒序存储或者 hash 字段方式创建索引。因此使用字符串创建索引，有以下几种方式:

  1. 直接创建完整索引，这样可能比较占用空间；
  2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
  3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题，不支持范围扫描；
  4. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

3.1 字符串索引使用示例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

# 创建前缀索引
mysql> alter table SUser add index index1(email);
mysql> alter table SUser add index index2(email(6));

# 倒序索引，每次写和读的时候，都需要额外调用一次 reverse 函数
mysql> select field_list from t where id_card = reverse('input_id_card_string');

# hash 字段
mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);
# hash 值可能存在冲突，必须在查询条件加上原始字段
mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'
更新于 2020-03-07
阅读原始文档
 极客时间, MySQL实战45讲
返回 | 主页