索引
MySQL
|
|
|
139

9587 字
|
38 分钟

索引

1. 概述

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构

索引的本质：索引是数据结构。你可以简单理解为 “排好序的快速查找数据结构”，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现 高级查找算法

索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表的 最大索引数和 最大索引长度。所有存储引擎支持每个表最多 16 个索引，总索引长度至多为 256 字节。有些存储引擎支持更多的索引数和更大的索引长度

2. 优点

  1. 提高查询速度：通过创建索引，可以让系统不再需要进行全表扫描，直接访问到对应的数据
  2. 提高排序和分组的速度：索引可以极大地加快数据的检索速度，也加快了排序和分组的速度
  3. 实现唯一性和完整性约束：确保数据的完整性和唯一性
  4. 提升性能：减少了数据查询和排序时磁盘 I/O 的次数，从而提高了查询速度和数据库的整体性能

3. 缺点

  1. 占用存储空间：每个索引都需要占用一定的物理空间
  2. 插入、删除和修改操作变慢：每次对数据进行修改时，都需要相应地修改索引，这会增加系统的开销
  3. 管理开销：索引需要定期维护和重建
  4. 复杂性增加：需要合理选择创建哪些索引，过多的索引会影响性能和管理效率

提示：

索引可以提高查询的速度，但是会影响插入记录的速度。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引

4. 分类

索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集索引称为二级索引或者辅助索引

4.1 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（所有的用户记录都存储在了叶子结点），也就是所谓的 索引即数据，数据即索引 (InnoDB 中数据和索引都存储在一个文件中，即.ibd 文件)

主键构建的 B + 树，叫做聚簇索引，非主键构建的叫做非聚簇索引

  • 显式声明：用户可以在建表的时候通过 primary key 关键字来声明主键列
  • 唯一索引：如果用户没有声明主键列，那么 InnoDB 会使用第一个非空唯一列作为主键
  • 自动生成：如果满足以上两种条件的列都不存在，那么 InnoDB 会使用隐藏列 RowId 自动作为主键

术语 “聚簇” 表示当前数据行和相邻的键值聚簇的存储在一起

特点：

  • 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

    • 页内 的记录是按照主键的大小顺序排成一个 单向链表
    • 各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表
    • 存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个 双向链表

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

    所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）

页内单向链表，页间双向链表

InnoDB 存储引擎中，聚簇索引比二级索引多存储隐藏列信息

我们把具有这两种特性的 B + 树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在 MySQL 语句中显式的使用 INDEX 语句去创建， InnDB 存储引擎会 自动 的为我们创建聚簇索引

优点：

  • 数据访问更快 ，因为聚簇索引将索引和数据保存在同一个 B + 树中，因此从聚簇索引中获取数据比非聚簇索引更快
  • 聚簇索引对于主键的 排序查找 和 范围查找 速度非常快
  • 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的IO操作

缺点：

  • 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于 InnoDB 表，我们一般都会定义一个自增的 ID 列为主键
  • 更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于 InnoDB 表，我们一般定义主键为不可更新
  • 二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据

限制：

  • 对于 MySQL 数据库目前只有 InnoDB 数据引擎支持聚簇索引，而 MyISAM 并不支持聚簇索引
  • 由于数据物理存储排序方式只能有一种，所以每个 MySQL 的表只能有一个聚簇索引。一般情况下就是该表的主键
  • 如果没有定义主键，Innodb 会选择非空的唯一索引代替。如果没有这样的索引，Innodb 会隐式的定义一个主键来作为聚簇索引 (即RowId)
  • 为了充分利用聚簇索引的聚簇的特性，所以 innodb 表的主键列尽量选用有序的顺序id, 而不建议用无序的 id, 比如 UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长

4.2 二级索引（辅助索引、非聚簇索引）

如果我们想以别的列作为搜索条件该怎么办？肯定不能是从头到尾沿着链表依次遍历记录一遍

答案：我们可以多建几棵B+树，不同的 B + 树中的数据采用不同的排列规则。比方说我们用 c2 列的大小作为数据页、页中记录的排序规则，再建一棵 B + 树，效果如下图所示：

img

这个 B + 树与上边介绍的聚簇索引有几处不同：

  • 使用记录 c2 列的大小进行记录和页的排序，这包括三个方面的含义：
    • 页内的记录是按照 c2 列的大小顺序排成一个单向链表
    • 各个存放用户记录的页也是根据页中记录的 c2 列大小顺序排成一个双向链表
    • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的 c2 列大小顺序排成一个双向链表
  • B + 树的叶子节点存储的并不是完整的用户记录，而只是 c2列+主键这两个列的值
  • 目录项记录中不再是主键+页号的搭配，而变成了 c2列+页号的搭配（实际为 c2 列 + 加主键 + 页号）

所以如果我们现在想通过 c2 列的值查找某些记录的话就可以使用我们刚网刚建好的这个 B + 树了。以查找 c2 列的值为 4 的记录为例，查找过程如下：

  1. 确定目录项记录页

    根据根页面，也就是页44，可以快速定位到目录项记录所在的页为页42（因为 2<4<9）

  2. 通过目录项记录页确定用户记录真实所在的页

    在页42 中可以快速定位到实际存储用户记录的页，但是由于 c2列 并没有唯一性约束，所以 c2 列值为 4 的记录可能分布在多个数据页中，又因为 2<4≤4，所以确定实际存储用户记录的页在页34 和页35 中

  3. 在真实存储用户记录的页中定位到具体的记录

    到页34 和页35 中定位到具体的记录

  4. 但是这个 B + 树的叶子节点中的记录只存储了 c2 和 c1(也就是主键) 两个列，所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录

即非聚簇索引 B + 树叶子节点存储的是 c2列+主键的组合，然后使用查询到的主键利用回表操作到聚簇索引的 B + 树中查找

二级索引列可能不是 unique 的，所以当该列一致时，目录页中无法判断先后顺序，所以实际上二级索引 B + 树目录页（非叶子节点）存储的都是索引列 + 加主键 + 页号的组合，额外保存了主键用于判断索引列相同的情况

回表

我们根据这个以 c2 列大小排序的 B + 树只能确定我们要查找记录的主键值，所以如果我们想根 据 c2 列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程称为回表 。也就是根据 c2 列的值查询一条完整的用户记录需要使用到 2 棵 B + 树！

问题：为什么我们还需要一次 回表 操作呢？直接把完整的用户记录放到叶子节点不 OK 吗？

答案：如果把完整的用户记录放到叶子结点是可以不用回表。但是太占地方了，相当于每建立一棵二级索引 B + 树都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了

因为这种按照非主键列建立的 B + 树需要一次回表操作才可以定位到完整的用户记录，所以这种 B + 树也被称为二级索引，或者辅助索引。由于使用的是 c2 列的大小作为 B + 树的排序规则，所以我们也称这个 B + 树为 c2 列建立的索引

非聚簇索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个非聚簇索引

img

小结：

聚簇索引与非聚簇索引的原理不同，在使用上也有一些区别：

  1. 聚簇索引的叶子节点存储的就是我们的数据记录 , 非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序
  2. 一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索
  3. 使用聚簇索引的时候，数据的查询效率高，但由于非聚簇索引不会影响数据行的物理存储顺序，如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低

4.3 联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让 B + 树按 照 c2和c3列 的大小进行排序，这个包含两层含义：

  • 先把各个记录和页按照 c2 列进行排序
  • 在记录的 c2 列相同的情况下，采用 c3 列进行排序

为 c2 和 c3 建立的索引的示意图如下：

img

如图所示，我们需要注意以下几点：

  • 每条目录项都有 c2、c3、页号这三个部分组成，各条记录先按照 c2 列的值进行排序，如果记录的 c2 列相同，则按照 c3 列的值进行排序
  • B + 树叶子节点处的用户记录由 c2、c3和主键c1列组成

注意一点，以 c2 和 c3 列的大小为排序规则建立的 B + 树称为 联合索引 ，本质上也是一个二级索引。它的意思与分别为 c2 和 c3 列分别建立索引的表述是不同的，不同点如下：

  • 建立 联合索引 只会建立如上图一样的 1 棵 B + 树
  • 为 c2 和 c3 列分别建立索引会分别以 c2 和 c3 列的大小为排序规则建立 2 棵 B + 树

5. InnoDB 的 B + 树索引的注意事项

5.1 根页面位置万年不动

我们前边介绍 B + 树索引的时候，为了大家理解上的方便，先把存储用户记录的叶子节点都画出来，然后接着画存储目录顶记录的内节点，实际上 B + 树的形成过程是这样的：

  • 每当为某个表创建一个 B + 树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个 根结点 页面。最开始表中没有数据的时候，每个 B + 树索引对应的 根结点 中即没有用户记录，也没有目录项记录
  • 随后向表中插入用户记录时，先把用户记录存储到这个根节点 中
  • 当根节点中的可用 空间用完时 继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如 页a 中，然后对这个新页进行 页分裂 的操作，得到另一个新页，比如页b 。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到 页a 或者 页b 中，而 根节点 便升级为存储目录项记录的页

这个过程特别注意的是：一个 B + 树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建议一个索引，那么它的根节点的页号便会被记录到某个地方。然后凡是 InnoDB 存储引擎需要用到这个索引的时候，都会从哪个固定的地方取出根节点的页号，从而来访问这个索引

B + 树的形成不是像上面一样从下往上形成的，而是从根节点开始从上往下形成的

5.2 内节点中目录项记录的唯一性

我们知道 B + 树索引的内节点中目录项记录的内容是 索引列 + 页号 的搭配，但是这个搭配对于二级索引来说有点不严谨。还拿 index_demo 表为例，假设这个表中的数据是这样的：

img

如果二级索引中目录项记录的内容只是 索引列 + 页号 的搭配的话，那么为 c2 列建立索引后的 B + 树应该长这样：

img

如果我们想新插入一行记录，其中 c1 、c2 、c3 的值分别是: 9、1、c, 那么在修改这个为 c2 列建立的二级索引对应的 B+ 树时便碰到了个大问题：由于 页3 中存储的目录项记录是由 c2列 + 页号 的值构成的，页3 中的两条目录项记录对应的 c2 列的值都是 1，而我们 新插入的这条记录 的 c2 列的值也是 1，那我们这条新插入的记录到底应该放在 页4 中，还是应该放在 页5 中？

为了让新插入记录找到自己在那个页面，我们需要保证在 B + 树的同一层页节点的目录项记录除页号这个字段以外是唯一的。所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：

  • 索引列的值
  • 主键值
  • 页号

也就是我们把主键值也添加到二级索引内节点中的目录项记录，这样就能保住 B+ 树每一层节点中各条目录项记录除页号这个字段外是唯一的，所以我们为 c2 建立二级索引后的示意图实际上应该是这样子的：

img

这样我们再插入记录 (9, 1, 'c') 时，由于 页3 中存储的目录项记录是由 c2列 + 主键 + 页号 的值构成的，可以先把新纪录的 c2 列的值和 页3 中各目录项记录的 c2 列的值作比较，如果 c2 列的值相同的话，可以接着比较主键值，因为 B + 树同一层中不同目录项记录的 c2列 + 主键的值肯定是不一样的，所以最后肯定能定位唯一的一条目录项记录，在本例中最后确定新纪录应该被插入到 页5 中

二级索引列可能不是 unique 的，所以当该列一致时，目录页中无法判断先后顺序，所以实际上二级索引 B + 树目录页（非叶子节点）存储的都是索引列 + 加主键 + 页号的组合，额外保存了主键用于判断索引列相同的情况

5.3 一个页面最少存储 2 条记录

一个 B + 树只需要很少的层级就可以轻松存储数亿条记录，查询速度相当不错！这是因为 B + 树本质上就是一个大的多层级目录，每经过一个目录时都会过滤掉许多无效的子目录，直到最后访问到存储真实数据的目录。那如果一个大的目录中只存放一个子目录是个啥效果呢？那就是目录层级非常非常多，而且最后的那个存放真实数据的目录中只存放一条数据。所以 InnoDB 的一个数据页至少可以存放两条记录

6. MyISAM 中的索引方案

B + 树索引使用存储引擎如表所示：

索引 / 存储引擎 MyISAM InnoDB Memory
B+Tree 索引 支持 支持 支持

即使多个存储引擎支持同一种类型的索引，但是他们的实现原理也是不同的。Innodb 和 MyISAM 默认的索引是 Btree 索引；而 Memory 默认的索引是 Hash 索引

MyISAM 引擎使用 B+Tree 作为索引结构，叶子节点的 data 域存放的是数据记录的地址 (因为 MyISAM 的表文件数据和索引是分开存储的)

B + 树就是 B 树，是基于 B 树的改进

6.1 MyISAM 索引的原理

我们知道 InnoDB中索引即数据，也就是聚簇索引的那棵 B + 树的叶子节点中已经把所有完整的用户记录都包含了，而 MyISAM 的索引方案虽然也使用树形结构，但是却将索引和数据分开存储：

  • 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件（.MYD 文件）。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找
  • 使用 MyISAM 存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中（.MYI 文件）。MyISAM 会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值+数据记录地址的组合

可以理解为 MyISAM 没有聚簇索引，全是二级索引

img

这里设表一共有三列，假设我们以 Col1 为主键，上图是一个 MyISAM 表的主键索引 (Primary key) 示意。可以看出MyISAM 的索引文件仅仅保存数据记录的地址。在 MyISAM 中，主键索引和二级索引 (Secondary key) 在结构上没有任何区别，只是主键索引要求 key 是唯一的，而二级索引的 key 可以重复。如果我们在 Col2 上建立一个二级索引，则此索引的结构如下图所示：

img

同样也是一棵 B+Tree,data 域保存数据记录的地址。因此，MyISAM 中索引检索的算法为：首先按照 B+Tree 搜索算法去搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记录

6.2 MyISAM 与 InnoDB 对比

MyISAM 的索引方式都是 “非聚簇” 的，与 InnoDB 包含 1 个聚簇索引是不同的。小结两种引擎中索引的区别：

  1. 在 InnoDB 存储引擎中，我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录，而在 MyISAM 中却需要进行一次 回表 操作，意味着 MyISAM 中建立的索引相当于全部都是 二级索引

  2. InnoDB 的数据文件本身就是索引文件，而 MyISAM 索引文件和数据文件是 分离的 ，索引文件仅保存数据记录的地址

  3. InnoDB 的非聚簇索引 data 域存储相应记录 主键的值 ，而 MyISAM 索引记录的是 地址 。换句话说， InnoDB 的所有非聚簇索引都引用主键作为 data 域

  4. MyISAM 的回表操作是十分 快速 的，因为是拿着地址偏移量直接到文件中取数据的，反观 InnoDB 是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。（所以说 InnoDB 速度比不上 MyISAM）

  5. InnoDB 要求表 必须有主键 （ MyISAM可以没有）。如果没有显式指定，则 MySQL 系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则 MySQL 自动为 InnoDB 表生成一个隐含字段 rowId 作为主键，这个字段长度为 6 个字节，类型为长整型

7. 设计原则

7.1 适合创建索引的场景

（1）字段的数值有唯一性的限制

索引本身可以起到约束的作用，比如唯一索引、主键索引都是可以起到唯一性约束的，因此在我们的数据表中如果某个字段是唯一性的，就可以直接创建唯一性索引，或者主键索引。这样可以更快速地通过该索引来确定某条记录

例如，学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个学生的信息，如果使用姓名的话，可能存在同名现象，从而降低查询速度

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引（来源：Alibaba）

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的

即：唯一值的字段例如学号身份证号等，必须添加唯一约束 (索引)

（2）频繁作为 WHERE 查询条件的字段

某个字段在 SELECT 语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率

（3）经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引 。如果待排序的列有多个，那么可以在这些列上建立组合索引

（4）UPDATE、DELETE 的 WHERE 条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护

（5）DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率

比如，我们想要查询课程表中不同的 student_id 都有哪些，如果我们没有对 student_id 创建索引，执行 SQL 语句：

SELECT DISTINCT(student_id) FROM `student_info`;

运行结果（197955 条记录，运行时间 0.991s ）

如果我们对 student_id 创建索引，再执行 SQL 语句：

SELECT DISTINCT(student_id) FROM `student_info`;

运行结果（197955 条记录，运行时间 0.065s ）

你能看到 SQL 查询效率有了提升，同时显示出来的 student_id 还是按照递增的顺序 进行展示的。这是因为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多

（6）多表 JOIN 连接操作时，创建索引注意事项

首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率

其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的

最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。比如 course_id 在 student_info 表和 course 表中都为 int (11) 类型，而不能一个为 int 另一个为 varchar 类型

因为使用函数隐式转换类型过后，索引就会失效

举个例子，如果我们只对 student_id 创建索引，执行 SQL 语句：

SELECT s.course_id, name, s.student_id, c.course_name
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE name = 'ZADpQh';

运行结果（6 条数据，运行时间 0.491s ）

这里我们对 name 创建索引，再执行上面的 SQL 语句，运行时间为 0.024s

（7）使用列的类型小的创建索引

我们这里所说的类型大小指的就是该类型表示的数据范围的大小

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT、INT、 BIGINT等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT就不要使用BIGINT,能使用MEDIUMINT就不要使用INT。这是因为：

  • 数据类型越小，在查询时进行的比较操作越快
  • 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/O

（8）使用字符串前缀创建索引

假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树中有这么两个问题：

B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大

如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间

我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题

例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间，如果只检索字段前面的若干字符，这样可以提高检索速度

创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引

CREATE TABLE shop(address varchar(120) NOT NULL);
ALTER TABLE shop ADD INDEX(address(12));

问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的；截取得少了，重复内容太多，字段的散列度 (选择性) 会降低。怎么计算不同的长度的选择性呢？

先看一下字段在全部数据中的选择度：

SELECT COUNT(DISTINCT address) / COUNT(*) FROM shop;

通过不同长度去计算，与全表的选择性对比：

公式：

COUNT(DISTINCT LEFT(列名, 索引长度)) / COUNT(*)

例如：

select count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度
count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度
count(distinct left(address,20)) / count(*) as sub12, -- 截取前20个字符的选择度
count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度
from shop;

越接近于 1 越好，说明越有区分度

引申另一个问题：索引列前缀对排序的影响

如果使用了索引列前缀，比方说前边只把 address 列的前12个字符放到了二级索引中，下边这个查询可能就有点尴尬了：

SELECT * FROM shop
ORDER BY address
LIMIT 12;

因为二级索引中不包含完整的 address 列信息，所以无法对前 12 个字符相同，后边的字符不同的记录进行排序，也就是使用索引列前缀的方式无法支持使用索引排序，只能使用文件排序

拓展：Alibaba《Java 开发手册》
【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90% 以上 ，可以使用 count (distinct left (列名，索引长度)) / count (*) 的区分度来确定

（9）区分度高 (散列性高) 的列适合作为索引

列的基数指的是某一列中不重复数据的个数，比方说某个列包含值 2, 5, 8, 2, 5, 8, 2, 5, 8，虽然有9条记录，但该列的基数却是 3。也就是说在记录行数一定的情况下，列的基数越大，该列中的值越分散；列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引，为基数太小的列的简历索引效果可能不好

可以使用公式 select count(distinct a) / count(*) from t1 计算区分度，越接近 1 越好，一般超过33%就算比较高效的索引了

扩展：联合索引把区分度高 (散列性高) 的列放在前面

（10）使用最频繁的列放到联合索引的左侧

这样也可以较少的建立一些索引。同时，由于“最左前缀原则”，可以增加联合索引的使用率

（11）在多个字段都要创建索引的情况下，联合索引优于单值索引

7.2 限制索引的数目

在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个。原因：

  1. 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大
  2. 索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担
  3. 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能

7.3 不适合创建索引的场景

（1）在 where 中使用不到的字段，不要设置索引

WHERE 条件 (包括 GROUP BY、ORDER BY) 里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。举个例子：

SELECT course_id, student_id, create_time
FROM student_info
WHERE student_id = 41251;

因为我们是按照 student_id 来进行检索的，所以不需要对其他字段创建索引，即使这些字段出现在 SELECT 字段中

（2）数据量小的表最好不要使用索引

如果表记录太少，比如少于 1000 个，那么是不需要创建索引的。表记录太少，是否创建索引对查询效率的影响并不大。甚至说，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果

结论：在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的

（3）有大量重复数据的列上不要建立索引

在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引。比如在学生表的 “性别” 字段上只有 “男” 与 “女” 两个不同值，因此无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度

结论：当数据重复度大，比如高于 10% 的时候，也不需要对这个字段使用索引

（4）避免对经常更新的表创建过多的索引

第一层含义：频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率

第二层含义：避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。此时，虽然提高了查询速度，同时却降低更新表的速度

（5）不建议用无序的值作为索引

例如身份证、UUID (在索引比较时需要转为 ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等

（6）删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响

（7）不要定义夯余或重复的索引

  • 冗余索引

举例：建表语句如下

CREATE TABLE person_info(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
    KEY idx_name (name(10))
);

我们知道，通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索，再创建一个专门针对 name 列的索引就算是一个冗余索引 ，维护这个索引只会增加维护的成本，并不会对搜索有什么好处

  • 重复索引

另一种情况，我们可能会对某个列重复建立索引 ，比方说这样：

CREATE TABLE repeat_index_demo (
col1 INT PRIMARY KEY,
col2 INT,
UNIQUE uk_idx_c1 (col1),
INDEX idx_c1 (col1)
);

我们看到，col1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免

🌟 如果您喜欢我的文章，欢迎赞赏支持，您的支持是我创作的最大动力！🌟
🖋 作者：Enndfp
🔗链接：https://blog.enndfp.cn
📜版权声明：您可以自由转载，但请务必注明原文地址，感谢您的尊重与支持~
MySQL
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																

							
							
						

																

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇 

                    

                    
			        推荐文章
		            

		            

							
日志

							
							

							
多版本并发控制

							
							

							
锁

							
							

							
事务

							
							

							
索引优化与查询优化

							
							

							
性能分析工具的使用

							
							

							
存储引擎

							
							

							
逻辑架构