MySQL 索引总结

MySQL 索引总结

leo 2204 2021-05-13

索引

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

索引的优缺点

索引不单单是用于快速定位指定记录的位置。

对于 BTree 索引，是按照顺序存储数据的，所以可以用于ORDER BY 和 GROUP BY 操作。并且索引中存储了值，某些查询可以只使用索引就可以完成，无需查询数据行（索引覆盖）。

总结一下索引的优点：

  • 索引提高数据检索的效率，降低数据库IO压力。
  • 通过索引进行排序效率高。

索引的缺点：

  • 索引会占据磁盘空间。
  • 索引会提高表的查询效率，但会降低表的更新效率。因为在维护表数据的同时也要维护索引。

索引类型

在 MySQL 中，索引是在存储引擎层实现的，而不是服务器层实现的。所以不同存储引擎支持的索引类型，以及对索引的实现都不是一样的。

主键索引

索引列中的值必须是唯一的，不允许有空值。

唯一索引

索引列中的值必须是唯一的，但是允许为空值。

普通索引

基本索引类型，允许在索引列的值重复、为空值。

全文索引

全文索引是一种特殊的索引，它比较的是文本中的关键字，而不是直接比较索引中的值。

只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。

空间索引

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。

前缀索引

在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

索引的数据结构

哈希

哈希索引（hash index）使用哈希表实现，只有在等值查询时效率高。不支持范围查询。

对于每一行数据，存储引擎都会对索引列计算一个哈希码（hash code）作为 Key，对应的数据行的指针作为 Value。

BTree

在MySQL中，讨论索引时如果没有特别指定，通常指的就是BTree索引。它使用BTree（B+Tree）数据结构来存储数据。大多数存储引擎读支持这种索引。BTree索引树中的节点是有序的。索引列定义的顺序也很重要。

如何选择索引及使用索引时的注意事项

1. 为用于搜索、排序、分组、连接的列创建索引

最佳的索引列应该是出现在 where、order by、group by、join 子句中的列。

2. 选择唯一值多、重复值少的列创建索引

唯一值多、重复值少，索引就可以很容易的将行区分开，索引效果好。

3. 索引列长度尽量小

索引列应尽量选择较小的数据类型。索引列长度小的优势：

  • 让比较更快，加快索引查找速度
  • 索引占据空间较小，减少磁盘IO
  • 缓存中可以容纳更多的数据，提高命中率

4. 字符串类型的索引列尽量创建前缀索引

好处和上一条类似。坏处是前缀索引无法做到索引覆盖。

5. 复合索引的最左前缀

当创建一个多列索引如 (col1, col2, col3)时，只有符合最左前缀要求的SQL语句才会利用到该多列（复合）索引。

该多列索引符合最左前缀要求的列表有（相当于有以下几个索引）：

  • col1,col2,col3
  • col1,col2
  • col1

6. 不要创建过多的索引

索引不是越多越好，索引自身会占据空间，影响写操作的性能，过多的索引还可能导致 MySQL 优化时语句时选择不是最优的索引。

7. 独立的列

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

select id from tab where id + 1 = 5;

select * from tab where TO_DAYS(date) - TO_DAYS(date()) <= 5;

上面的查询无法使用 id 、date的索引。我们需要尽量将索引列单独放到比较符号的一侧。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。

在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，聚簇索引就是按照表的主键构造一颗 B+ 树，同时叶子节点中存放的就是整张表的行记录数据，也将聚簇索引的叶子节点称为数据页。

注意：如果表没有定义主键，那么 InnoDB 会隐式的创建一个主键代替。

因为无法将数据行存放在不同的位置，所以一张表只能有一个聚簇索引。

聚簇索引的优缺点：

优点：

  • 数据访问快，因为聚簇索引将索引和数据放在一个 B+Tree 中，所以聚簇索引一般比非聚簇索引快。
  • 对于主键的查询和排序速度很快。
  • 支持覆盖索引。

缺点：

  • 插入速度严重依赖于插入顺序，按照主键的顺序插入速度最快。
  • 更新聚簇索引的代价很高，InnoDB 会将主键被更新的数据行移到新的位置，且移动时可能会导致“页分裂”。
  • 由于非聚簇索引（二级索引）保存的“行指针”是行的主键，那么拿到主键后还需要利用聚簇索引去查找对应的数据行。也就是说二级索引需要进行两次索引查找。

对于最后一个缺点，InnoDB 的自适应哈希索引可以减少二次索引操作。

结合聚簇索引的优缺点，在 InnoDB 中我们一般都会定义一个自增的ID列为主键，且不会去更新主键。

MySQL