MySQL索引知识总结

阳光穿透心脏的1/2处 2022-01-20 22:09 299阅读 0赞

索引是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响越发重要。在数据量比较小且负载较低时，不恰当的索引对性能的影响可能还不明显，但是当数据逐渐增大时，性能会急剧下降。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易地将查询性能提高几个数量级，最优的索引有时比一个好的索引性能要高两个数量级。

## 一、一些索引的介绍 ##

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式地定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距很远。

优点：

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

缺点：

1.  聚簇索引最大限度地提高了I/O密集型应用的性能，但如果数据都放到内存中，则访问顺序就没那么重要了，聚簇索引也就没什么优势了。
2.  插入速度严重依赖插入顺序
3.  更新聚簇索引的代价很高
4.  基于聚簇索引的表在插入新行，或者主键备更新导致需要移动行的时候，可能面临“页分裂”的问题，页分裂会导致占用更多的磁盘空间
5.  聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
6.  二级索引（非聚簇索引）可能比想象的要强大，因为在二级索引的叶子节点包含了引用行的主键列。
7.  二级索引访问需要两次索引查找，而不是一次。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

优点：

1.  索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量。
2.  因为索引是按照列值顺序存储的（至少在单页上如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。
3.  一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题
4.  由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键的二次查询。

## 二、索引的优点 ##

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

注：对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但是对于特大型的表，建立和使用索引的代价将随之增长，在这种情况下，则需要一种技术可以直接区分出查询所需要的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

## 三、InnoDB引擎的索引 ##

InnoDB使用的是B+Tree索引。 存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中的上限和下限，最终存储引擎要么找到对应的值，要么该记录不存在。

可以使用B-Tree索引的查询类型（从技术上说也是B+Tree索引的查询类型）。

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

2）匹配最左前缀

3）匹配列前缀

4）可以只匹配某一列的值得开头部分

5）匹配范围值

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

B-Tree索引的限制：

1、如果不是按照索引的最左列开始查找，则无法使用索引。

2、不能跳过索引中的列

3、如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查找。

综上：关于索引的设置主要考虑以下两个因素：合理的选择索引列以及选择合适的额索引顺序。当不考虑排序和分组时，将选择性最高的列放在前面通常是很好的。