7、mysql索引

2022-08-08 本文已影响0人 wuqingfeng

7.1 索引类型

7.1.1 B-Tree索引

我们谈论索引的时候，如果没有特殊说明，多半是指B-Tree索引。
各种存储引擎以不同方式使用B-Tree索引，如MyISAM使用前缀压缩技术使索引变的更小，但InnoDB则直接使用原始数据。再如MyISAM通过索引数据的物理位置来引用被索引的行，而InnoDB则根据主键引用被索引的行。
B-Tree索引抽象表示和举例图示如下：

image.png

7.1.2 哈希索引

哈希索引（hash index）基于哈希表实现。只有精确匹配所有的列，查询才有效。
对于每一行数据，存储引擎会针对索引列计算出一个哈希码，哈希索引将哈希索引存在索引中，同时在哈希表中保存指向每个数据行的指针。
在mysql数据库中，只有memory显式支持hash索引。
InnoDB有一个功能，叫做自适应哈希索引（adaptive hash index），当InnoDB注意到某些索引值被使用的比较频繁时，它会基于B-Tree索引之上再创建一个哈希索引，不过这一过程是一个自动的，内部的行为，用户可以关闭该功能，但是不能对其进行配置。

7.1.3 空间数据索引（R-Tree）

MyISAM支持空间索引，可以用于地理数据存储。总体来讲，mysql对于GIS支持并不完善。

7.1.4 全文索引

全文索引是一种特殊的索引，它基于文本中的关键词进行查找，而不是比较索引中的值。

7.2 使用索引优势

使用索引可以为我们带来如下收益：

减少服务器扫描数据量
避免排序和临时表
将随机IO转变为顺序IO

7.3 高性能索引策略

7.3.1 独立的列

独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。如对于下面的查询语句：

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

是无法使用索引的，我们在书写查询语句时，应当养成将索引行放在比较符号一侧的习惯。
下面的查询语句：

SELECT ... WHERE TO_DAVS(CURRENT_DATE) - TO_DAVS(date_col)<10;

则将索引列当作函数参数放在了查询语句中，同样不能使用索引。

7.3.2 前缀索引和索引选择性

有时候需要索引很长的列，这会让索引体积变的很大。这时候可以索引开始的部分字符，从而达到节省索引空间，提升索引效率的目的。
诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长以便节约空间。

索引的选择性
不重复的索引值（也称为基数，cardinality ）和数据表的记录总数（＃T）的比值，范围从1/#T 到1 之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL 在查找时过撞掉更多的行。唯一索引的选择性是1 ，这是最好的索引选择性，性能也是最好的。

7.3.3 多列索引

查询条件中包含and或者or时，where条件中的每一列创建单独的索引不是一个好的办法，需要使用顺序合适的多列索引才能获取更好的查询效果。
如以下查询：

--该查询数据源为mysql官方测试数据库数据
mysql> explain select film_id,actor_id from sakila.film_actor where actor_id=1 or film_id=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: index_merge
possible_keys: PRIMARY,idx_fk_film_id
          key: PRIMARY,idx_fk_film_id
      key_len: 2,2
          ref: NULL
         rows: 29
     filtered: 100.00
        Extra: Using union(PRIMARY,idx_fk_film_id); Using where
1 row in set, 1 warning (0.01 sec)

对该查询语句explain的结果中，type为index_merge，这通常表明服务器需要消耗较多的cpu和内存资源用于数据的缓存、合并、排序。

7.3.4 索引顺序

7.3.5 聚簇索引

聚簇索引不是一种索引类型，是一种索引存储方式。
当表使用聚簇索引时，它的数据行存储在了索引的叶子页（leaf page）上。具体如下图所示：

image.png

7.3.6 覆盖索引

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

7.3.7 使用索引扫描做排序

mysql有两种方式可以生成有序结果：通过排序、按照索引顺序扫描。

7.3.8 压缩索引

7.3.9 冗余索引和重复索引

7.3.10 未使用的索引

未使用的索引完全时累赘，建议删除。

7.4 总结

在mysql中，大多数情况下使用B-Tree索引，在选择索引和利用这些索引进行查询时，应当注意以下几点：

单行访问时很慢的。一次读取尽可能多的包含所需要的行。
按顺序访问范围数据时很快的。
覆盖索引是很快的。如果一个索引包含了查询所需要的所有的列，存储引擎就不需要再进行回表查询了，就避免了大量单行访问。