Post

创建高性能的索引

Posted Updated
By 😇 18 min read

ch5 创建高性能的索引

索引（在 MySQL 中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。

索引基础

在 MySQL 中，索引是在存储引擎层而不是服务器层实现的。

索引采用的数据结构：1️⃣哈希表，2️⃣B+树

索引的用处

  1. 快速查找匹配 WHERE 子句的行
  2. 从 consideration 中消除行,如果可以在多个索引之间进行选择，MySQL 通常会使用找到最少行的索引
  3. 如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
  4. 当有表连接的时候，从其他表检索行数据
  5. 查找特定索引列的 min 或 max 值
  6. 如果排序或分组时在可用索引的最左前缀上完成的，则对表进行排序和分组
  7. 在某些情况下，可以优化查询以检索值而无需查询数据行

索引的分类：主键索引、唯一索引、普通索引、全文索引、组合索引

B+Tree 索引

image-20240404175917536

哈希索引

基于哈希表的实现，只有精确匹配索引所有列的查询才有效；

在 MySQL 中，只有 memory 的存储引擎显式支持哈希索引；

哈希索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这让哈希索引查找的速度非常快

InnoDB 引擎有一个特殊的功能叫做“自适应哈希索引(adaptive hash index)”。当InnoDB 注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让 B-Tree 索引也具有哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的、内部的行为，用户无法控制或者配置，不过如果有必要完全可以关闭该功能。

哈希索引的限制

  1. 哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行。
  2. 哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序。
  3. 哈希索引不支持部分列匹配查找，哈希索引是使用索引列的全部内容来计算哈希值。
  4. 哈希索引支持等值比较查询，也不支持任何范围查询。
  5. 访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行。
  6. 哈希冲突比较多的话，维护的代价也会很高。

案例

当需要存储大量的 URL，并且根据 URL 进行搜索查找，如果使用 B+ 树，存储的内容就会很大。

1

select id from url where url="";

也可以利用将 url 使用 CRC32 做哈希，可以使用以下查询方式

1

select id from url where url="" and url_crc=CRC32("")

此查询性能较高原因是使用体积很小的索引来完成查找。

CRC32（Cyclic Redundancy Check，循环冗余校验）是一种校验算法，通常用于检查数据传输过程中是否发生了错误。CRC32算法通过对数据进行处理生成一个32位的校验值，这个校验值在数据传输结束后被发送方计算并附加到数据中，接收方收到数据后也会计算校验值并与接收到的校验值进行比较，以判断数据是否完整和正确。在数据库中，CRC32有时也用作一种简单的哈希算法，用于生成数据的哈希值以便于在索引中进行快速检索。

全文索引 FULLTEXT

全文索引查找的是文本中的关键词，而不是直接比较索引中的值。

文章存到了 MySQL 里面，怎么找有某关键词的文章？ Ans: 分词。

lucene -> Solr -> ES

索引的优点

  1. 大大减少了服务器需要扫描的数据量；
  2. 帮助服务器避免排序和临时表；
  3. 将随机 I/O 变成顺序 I/O

B-Tree 索引查询类型/匹配方式

全值匹配

全值匹配指的是和索引中的所有列进行匹配

匹配最左前缀

只匹配前面的几列

匹配列前缀

可以匹配某一列的值的开头部分

匹配范围值

可以查找某一个范围的数据

精确匹配某一列并范围匹配另外一列

可以查询第一列的全部和第二列的部分

只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引

高性能的索引策略

三星索引概念⭐⭐⭐

对于一个查询而言，一个三星索引，可能是其最好的索引。

满足的条件如下：

  • 索引将相关的记录放到一起则获得一星⭐（比重27%）
  • 如果索引中的数据顺序和查找中的排列顺序一致则获得二星（排序星）⭐⭐（比重27%）
  • 如果索引中的列包含了查询中需要的全部列则获得三星（宽索引星）⭐⭐⭐（比重50%）

这三颗星，哪颗最重要？第三颗星。因为将一个列排除在索引之外可能会导致很多磁盘随机读（回表操作）。第一和第二颗星重要性差不多，可以理解为第三颗星比重是50%，第一颗星为27%，第二颗星为23%，所以在大部分的情况下，会先考虑第一颗星，但会根据业务情况调整这两颗星的优先度。

一星⭐

一星的意思就是：如果一个查询相关的索引行是相邻的或者至少相距足够靠近的话，必须扫描的索引片宽度就会缩至最短，也就是说，让索引片尽量变窄，也就是我们所说的索引的扫描范围越小越好。

二星（排序星）⭐⭐ ：

在满足一星的情况下，当查询需要排序，group by、 order by，如果查询所需的顺序与索引是一致的（索引本身是有序的），是不是就可以不用再另外排序了，一般来说排序可是影响性能的关键因素。

三星（宽索引星）⭐⭐⭐ ：

在满足了二星的情况下，如果索引中所包含了这个查询所需的所有列（包括 where 子句和 select 子句中所需的列，也就是覆盖索引），这样一来，查询就不再需要回表了，减少了查询的步骤和IO请求次数，性能几乎可以提升一倍。

独立的列

“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

1

where actor_id + 1 = 5;

上面这个就不能解析，不能用索引，应该将单独的列放在比较符号的一侧。

前缀索引和索引选择性

索引选择性（selectivity）是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从 1/#T 到 1 之间。

索引选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。

唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。

一般情况某个列前缀的选择性也是足够高的，足以满足查询性能。要选择足够长的前缀索引，又不能太长，足够长以使得前缀索引的选择性接近于索引整个列。就是，前缀的“基数”应该接近于完整列的“基数”。

多列索引

常见的错误：为每个列创建独立的索引，或者按照错误的顺序创建多列索引。

组合索引：当包含多个列作为索引的时候，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要

案例：建立组合索引 a, b, c，不同 SQL 语句使用索引的情况

statement索引是否发挥作用
where a=3是，只使用了 a
where a=3 and b=5是，使用了a, b
where a=3 and b=5 and c=4是，使用了 a, b, c
where b=3 or where c=4否
where a=3 and c=4是，仅使用了 a
where a=3 and b>10 and c=7是，使用了 a,b
where a=3 and b like ‘%xx%’ and c=7使用了 a

选择合适的索引顺序

聚簇索引

  • 聚簇索引：不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起 （数据跟索引是否是聚集存储的）
  • 非聚簇索引：数据文件跟索引文件分开存放，MyISAM 都是，InnoDB 的二级索引（辅助索引，普通索引）

innodb的主键索引就是聚簇索引

必须要包含一个主键列的：

  1. key 如果在创建表的时候制定了主键，那么 key 就是主键；
  2. 如果没有主键，那么 key 就是唯一键，如果唯一键也没有，那么 key 就是6字节的 rowid。

聚簇索引优点

  1. 可以把相关数据保存在一起
  2. 数据访问更快，因为索引和数据保存在同一个树中
  3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

聚簇索引缺点

  1. 聚簇数据最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势
  2. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式
  3. 更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置
  4. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题
  5. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

索引覆盖

  1. 如果一个索引包含所有需要查询的字段的值，我们称之为覆盖索引
  2. 不是所有类型的索引都可以称为覆盖索引，覆盖索引必须要存储索引列的值
  3. 不同的存储实现覆盖索引的方式不同，不是所有的引擎都支持覆盖索引，Memory 不支持覆盖索引

覆盖索引优势

  1. 索引条目通常远小于数据行大小，如果只需要读取索引，那么mysql就会极大的较少数据访问量
  2. 因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多
  3. 一些存储引擎如 MYISAM 在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题
  4. 由于 INNODB 的聚簇索引，覆盖索引对 INNODB 表特别有用

使用索引扫描来做排序

MySQL 有两种方式可以生成有序结果：通过排序操作；或者按索引顺序扫描

如果 EXPLAIN 出来的 type 列的值为“index”，则说明 MySQL 使用了索引扫描来做排序（并不是 Extra 列的 Using index）

压缩（前缀压缩）索引

默认压缩字符串

冗余和重复索引

冗余：在相同列上创建多个索引，影响性能

重复索引：在相同的列上按照相同的顺序创建的相同类型的索引。应该避免创建重复索引。

未使用的索引

这种用不到的，建议直接删除

Q: 怎么找出未使用的索引？

1

SELECT * FROM sys.schema_unused_indexes;

索引和锁

索引可以让查询锁定更少的行。

Database
MySQL Database
This post is licensed under CC BY 4.0 by the author.