MySQL——索引-CSDN博客

本文链接：https://blog.csdn.net/qq_45013531/article/details/118680510

MySQL - 索引

索引的类型
索引的优点
索引策略

索引的类型

B-Tree索引
索引在没有特别指明时，一般指B-Tree索引。
B-Tree适用于：
· 全值匹配：和索引中所有的列进行匹配；
· 匹配最左前缀：和索引的第一列进行匹配；
· 匹配列前缀：可以只匹配某一列的值得开头部分；
· 匹配范围值：可以匹配某一列的一个范围；
· 精确匹配某一列且范围匹配另外一列。
限制：
必须从最左列开始查找，且不能跳过索引中的某列，如果查询中有某个列是范围查询，则它右边的所有列都无法使用索引优化。
–
数据库的树
哈希索引
哈希索引是基于哈希值实现的，只有精确匹配索引所有列的查询才能有效！
对每一行数据，会对其所有的索引列进行哈希值计算，如果哈希值冲突，则会以链表的形式存储。哈希索引包含哈希值和指向数据行的指针。
哈希局限：
· 哈希索引存储的是哈希值和行指针，不是存储字段，所以不能使用索引中的值来避免读取行，但由于内存中读取速度非常快，所以影响并不明显；
· 哈希索引数据存储并不是按照索引值顺序存储，不能排序；
· 不支持索引列匹配查找；
· 只支持等值比较查询；
· 出现哈希冲突必须遍历链表中所有的行指针，逐行比较；哈希冲突也层架索引的维护代价。
哈希优势：速度快！
支持哈希索引的引擎：Memory(支持非唯一索引)，NDB（支持唯一哈希索引）
InnoDB具有特殊的“自适应哈希索引”，这是一个伪哈希索引，可以使得B树具有哈希索引的优点。
自适应哈希索引：就是对于一些存储量较大的索引，可以新增其哈希列，用where的方式手动指定使用的哈希值：
```
例子：一个表中存储了大量的URL，且需要根据URL值进行查找。如果用B树来存储URL，需要较大的空间存储。
一般情况下的查询： SELECT id FROM url_table where url = "www.mysqlsuoyin.com";
解决办法：新增一个被索引的url_crc列，这个列存储的是URL的哈希值。查询就可以变成以下形式：
SELECT id FROM url_table WHERE url_crc = CRC32(""www.mysqlsuoyin.com"")  AND url = "www.mysqlsuoyin.com";
这样mysql就会选择这个体积小的基于url_crc列的索引来完成查找。
值得注意的是，哈希列的值不能过大，不然就失去了其意义。
（当然 哈希值可以手动维度，也可以通过触发器维护）
```
1. 空间数据索引（R-Tree）
  MyISAM支持空间数据索引，可用作地理数据存储。无前缀查询，查询时可以利用任意有效的维度来组合查询。
2. 全文索引
  目的是查找文本中的关键词。

索引的优点

让服务器快速定位到表的指定位置；
大大减少了服务器需要扫描的数据量；
帮助服务器避免排序和临时表；
索引可以将随机I/O变为顺序I/O；

索引策略

注意索引的选择

必须是独立的列，索引不能是表达式的一部分，也不能是函数的参数；
对于很长的字符列，除了可以通过自适应哈希索引的方法外，可以使用前缀索引。但前缀索引不能太大（都选择前缀了肯定为了值小），但也不能太小，太小会导致索引选择性太低。
索引合并虽然是一种优化的结果，但同时也说明了表上的索引建的很糟糕。
索引列顺序：将选择性最高的列放在最前列（但同时要考虑排序、分组和范围条件等因素）！
要尽量避免重复索引和冗余索引。删除从未使用的索引。

聚簇索引

在这里插入图片描述
聚簇索引将索引和数据保存在同一个B-Tree中。
优点：

把相关数据保存在一起。减少读磁盘的次数；
数据访问更快
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点：

如果数据全部放在内存中，则减少读磁盘次数就没有意义了。
插入速度严重依赖于插入顺序；
更新聚簇索引列的代价更高
基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”问题。
可能导致全表扫描变慢
二级索引（非聚簇索引）可能会较大。

InnoDB与MyISAM数据分布对比

InnoDB是聚簇索引，而MyISAM是非聚簇索引存储。数据存储如下图
在这里插入图片描述
最终对比图

其他说明

InnoDB中数据按主键顺序插入行，应尽量避免随机的聚簇索引（即不连续且分布范围大）
在InnoDB中，如果没有什么数据需要聚集，可以使用代理键作为主键，这个主键的数据与应用无关。如自增列AUTO_INCREMWNT。可以保证数据按顺序插入！

覆盖索引

概念：一个索引包含所有需要查询的字段的值，就称为覆盖索引。
优点：

索引条目数通常远小于数据行大小，如果只取读索引，MySQL就能极大的减少数据访问量；
索引是按照列值顺序存储的，对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多；
一些存储引擎（如MyISAM）只缓存索引，数据缓存依赖于操作系统，所以访问数据需要一次系统调用，导致性能问题；
对于聚簇索引的InnoDB，其二级索引包含了行的主键值，所以如果二级主键能够覆盖，则避免了二次查询。

局限：

覆盖索引必须要存储索引列的值，像哈希索引、空间索引、全文索引等都不存储索引列的值，所以只有B-Tree索引做覆盖索引。
不同的存储引擎实现覆盖索引的方式也不同，并不是所有的存储引擎都支持覆盖索引。

使用索引扫描来做排序

两种情况可以使用索引扫描来做排序

索引的列顺序与ORDER BY子句的顺序完全一致，并且所有列的排序方向（正序或倒序）都一样。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。且ORDER BY子句需要满足索引的左前缀要求。即order by使用的列满足左前缀，且排序方向一致（排序方向有待验证）。
在一种情况下可以不满足左前缀要求，就是前导列为常量。即where是等于一个常量。

压缩索引

压缩索引可以减少索引的大小，从而可以让更多的索引放入内存，提升性能。但会导致某些操作可能更慢，无法使用二分查找，且倒序查找也很慢。
压缩索引方法：完全保存索引块中的第一个值，其他值和第一个值进行比较，保存相同前缀的字节数和剩余的不同的后缀部分。例如：第一个值为"home"，则直接保存，第二个值为"hometown"，则保存为"4,town"。