Mysql 基础知识 索引

索引是数据库为了提高查找效率的一种数据结构

1. 索引简介

1.1. 索引的优缺点

优点

• 减少扫描的数据量，提高检索速度
• 将随机IO变为顺序IO
• InnoDB 会减少锁的竞争，提高并发
• 唯一索引会保证数据的唯一性，减少业务的复杂性
• 帮助服务器避免排序和临时表。

缺点

• 闯将和维护索引会消耗时间
• 会占用一定的物理空间
• INSERT/UPDATE/DELETE等操作可能会更新索引，导致数据库的写操作性能降低。

2. 索引概念

2.1. 聚簇索引和非聚簇索引

• 聚簇索引：表中该行数据的索引和数据放在一起，一个表只能有一个聚簇索引。
• 非聚簇索引：表中改行数据的索引和数据分开存储

聚簇索引和非聚簇索引的查询有什么区别

• 如果语句是 select * from T where ID=1，即聚簇索引查询方式，则只需要搜索 ID 这棵 B+ 树；
• 如果语句是 select * from T where k=1，即非聚簇索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

基于非聚簇索引的查询需要多扫描一棵索引树。

显然，主键长度越小，非聚簇索引的叶子节点就越小，非聚簇索引占用的空间也就越小。

2.2. 覆盖索引

索引上的信息足够满足查询请求，不需要回表查询数据。

2.3. 前缀索引

有时候需要索引很长的字符列，这会让索引变得大且慢。

这时，可以使用前缀索引，即只索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。对于 BLOB/TEXT/VARCHAR 这种文本类型的列，必须使用前缀索引，因为数据库往往不允许索引这些列的完整长度。

索引的选择性是指：不重复的索引值和数据表记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，查询效率也越高。如果存在多条命中前缀索引的情况，就需要依次扫描，直到最终找到正确记录。

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

那么，如何确定前缀索引合适的长度呢？

可以使用下面这个语句，算出这个列上有多少个不同的值：

select count(distinct email) as L from SUser;

然后，依次选取不同长度的前缀来看这个值，比如我们要看一下 4~7 个字节的前缀索引，可以用这个语句：

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%。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6。

此外，**order by 无法使用前缀索引，无法把前缀索引用作覆盖索引**。

2.4. 最左前缀匹配原则

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

MySQL 会一直向右匹配直到遇到范围查询 >,<,BETWEEN,LIKE 就停止匹配。

• 索引可以简单如一个列(a)，也可以复杂如多个列(a, b, c, d)，即联合索引。
• 如果是联合索引，那么 key 也由多个列组成，同时，索引只能用于查找 key 是否存在（相等），遇到范围查询(>、<、between、like 左匹配)等就不能进一步匹配了，后续退化为线性查找。
• 因此，列的排列顺序决定了可命中索引的列数。

不要为每个列都创建独立索引。

将选择性高的列或基数大的列优先排在多列索引最前列。但有时，也需要考虑 WHERE 子句中的排序、分组和范围条件等因素，这些因素也会对查询性能造成较大影响。

例如：a = 1 and b = 2 and c > 3 and d = 4，如果建立（a,b,c,d）顺序的索引，d 是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d 的顺序可以任意调整。

让选择性最强的索引列放在前面，索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，查询效率也越高。

例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;

   staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
               COUNT(*): 16049

2.5. = 和 in 可以乱序

不需要考虑 =、IN 等的顺序，Mysql 会自动优化这些条件的顺序，以匹配尽可能多的索引列。

【示例】如有索引 (a, b, c, d)，查询条件 c > 3 and b = 2 and a = 1 and d < 4 与 a = 1 and c > 3 and b = 2 and d < 4 等顺序都是可以的，MySQL 会自动优化为 a = 1 and b = 2 and c > 3 and d < 4，依次命中 a、b、c、d。

3. 数据结构

索引有很多种数据结构：哈希索引、全文索引，R-Tree索引等，这里只记录用的比较多的B+ Tree索引

B+Tree中的 B 是指balance，意为平衡。需要注意的是，B+树索引并不能找到一个给定键值的具体行，它找到的只是被查找数据行所在的页，接着数据库会把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

为什么选择 B+树

• 二叉树：遇到极端情况，二叉树会退化成链表。
• 红黑树：当数据量很大时，树的深度也很深，搜索的数据越多，需要进行的磁盘IO就越多。
• B树：如果搜索的数据量太大，无法通过一次从磁盘换出很大的数据。
• B-树：每个节点中存在了Key和Data，导致每个节点存放的Key的数量很小，到存储数据量很大时，B-Tree的深度会很大，查询时IO次数增加，影响查询效率。
• B+树：在叶节点存放数据，非叶节点存放关键字记录的指针，进行索引，保证每次搜索会加载更多的索引，提高搜索效率

2.1 B+ 树

B+树的特点：

• 所有的数据都存储在叶子节点，非叶子节点并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。
• 所有的叶子节点由指针连接。

MyISAM索引文件和数据文件是分开的，InnoDB中表数据文件本身就是按照B+Tree组织的索引结构。这就造成了 MyISAM 回表表快，InnoDB 比较慢。

B+ 树索引适用于全键值查找、键值范围查找和键前缀查找，其中键前缀查找只适用于最左前缀查找。

InnoDB 要求表必须存在主键，如果不存在，则会自动生成一个隐含字段作为主键，长度为6个字节的长整形。

不建议使用过长的字段作为主键，因为二级索引都是引用主键索引，过长的主键会导致二级索引变的过大。所以用自增主键作为主键是一种比较好的选择

4. 索引的类型

主流的关系型数据库一般都支持以下索引类型：

3.1. 主键索引（PRIMARY）

主键索引：一种特殊的唯一索引，不允许有空值。一个表只能有一个主键（在 InnoDB 中本质上即聚簇索引），一般是在建表的时候同时创建主键索引。

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (`id`)
)

3.2. 唯一索引（UNIQUE）

唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

CREATE TABLE `table` (
    ...
    UNIQUE indexName (title(length))
)

3.3. 普通索引（INDEX）

普通索引：最基本的索引，没有任何限制。

CREATE TABLE `table` (
    ...
    INDEX index_name (title(length))
)

3.4. 全文索引（FULLTEXT）

全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

全文索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的 WHERE 语句的参数匹配。全文索引配合 match against 操作使用，而不是一般的 WHERE 语句加 LIKE。它可以在 CREATE TABLE，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用 CREATE INDEX 创建全文索引，要比先为一张表建立全文索引然后再将数据写入的速度快很多。

CREATE TABLE `table` (
    `content` text CHARACTER NULL,
    ...
    FULLTEXT (content)
)

3.5. 联合索引

组合索引：多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

CREATE TABLE `table` (
    ...
    INDEX index_name (title(length), title(length), ...)
)

5. 索引最佳实践

• 只在高选择性字段添加索引，创建索引的字段尽量有很少的重复。
• 建立联合索引时，高选择性的字段放在前面。
• 字符字段必须创建前缀索引。
• 不要在索引上进行数学运算或函数运算。
• 自增列或者全局ID做innodb主键。
• 尽量使用覆盖索引进行查询，避免回表。
• 对经常更新的表避免创建过多的索引。
• 可以使用 EXPLAIN 或者 optimizer_trace 对 SQL 语句进行分析。

6. 参考文档

• 【MySQL】MySQL为什么用B+树做索引而不用B-树或红黑树？
• MySQL8索引与调优篇1-索引的数据结构
• 数据库两大神器【索引和锁】
• 高性能Mysql