MySQL 索引

Jankeyfu
数据库
目录

索引可以让服务器快速定位到表的指定位置，但这并不是索引的唯一作用，根据索引的类型不同，索引还有一些附加的用途，如 B 树索引，由于按照顺序存储数据，所以可用来做ORDER BY 和 GROUP BY 操作。因此，索引总结下来有以下三个优点：

  • 索引大大减少了服务器需要扫描的数据量，提升了数据检索速度
  • 索引可以帮助服务器避免排序和临时表
  • 索引可以将随机 I/O 变成顺序 I/O

MySQL 支持不同的类型的索引，有哈希索引、B 树索引、全文索引等

哈希索引

哈希索引是基于哈希表实现的，通过对键值进行哈希处理得到对应的行指针，从而索引指定的行数据。

优点

  • 查找速度快，除非哈希冲突严重

缺点

  • 无法排序：因为数据并不是按照索引值进行顺序存储的。
  • 只支持等值查询：如 =、in()、<=> ，但由于哈希索引数据存储是无序的，因此无法通过索引进行范围查找。
  • 不支持部分索引或前缀匹配：索引通过哈希函数之后得到的是完全不一样的值，无法进行部分索引或者前缀数据的索引。

B 树索引

使用 B 树或者 B+ 树实现，由于 B 树对索引列是按顺序组织存储的，所以很适合查找范围数据。对于 InnoDB 存储引擎，采用的是 B+树，叶子节点存储的是主键和行数据；而对于 MyISAM 存储引擎，叶子节点存储的是行指针。

优点：

  • 支持全值匹配
  • 匹配最左前缀列
  • 匹配列前缀
  • 匹配范围值
  • 匹配某一列，并范围匹配后一列

缺点：

  • 如果不能按照索引的最左列开始查找，则无法使用索引
  • 不能跳过索引中的列
  • 如果某个列是范围查找，则后续的列都无法使用索引

按照索引数据的存储不同，MySQL 提供两种不同类型的 B 数索引，一种是聚簇索引，另一种则是非聚簇索引。

聚簇索引（主键索引）

按照表的主键构造一颗 B+ 树，同时叶子节点存放的是表的行记录，叶子节点也称为数据页。一张表只能有一个聚簇索引。

优点

  • 数据访问快：因为数据和索引在一起

缺点

  • 插入速度依赖插入顺序，顺序插入最快，否则容易出现页分裂。所以一般以自增 ID 作为主键
  • 更新主键代价很高：主键更新意味着行也得移动，因此一般定义主键不可更新
非聚簇索引（辅助键索引）

非聚簇索引与聚簇索引的区别在于，非聚簇索引叶子节点并不记录行数据，只记录行数据的辅助键和主键。因此查询数据时需要两次查询，第一次通过辅助键查询到主键，再通过主键进行聚簇索引的查询得到具体数据。

覆盖索引

覆盖索引是指索引中已经包含了需要查询的列数据，不需要回表查询主键索引对应的数据。主要体现在explain 查看执行计划的时候为 Extra: Using index 而不是 Extra: Using where。对于需要查询多个字段的情况，可以先通过关联查询的方式延迟对列的访问，从而使用覆盖索引，如

select * from tb_name l
	join (
		select id from tb_name where f1 = v1 -- 延迟关联，先只查键值，再通过关联查询其余字段
	) as r on (l.id  = r.id);

不过使用关联查询会带来相应的额外开销，所以需要分具体情况看是回表取数据的开销大还是关联查询的开销大以决定是否一定需要使用覆盖索引进行优化。

全文索引

全文索引查找的是文本中的关键词，而不是直接比较索引中的值，类似于搜索引擎，会对数据的语法进行分析等，以便能够通过关键词进行查找，全文索引适用于 MATCH AGAINST 操作，而不是普通的 WHERE 条件操作。

索引策略

1、索引列不能是表达式的一部分，否则索引会失效。

select * from user where age + 1 = 20; -- age列无法使用索引，即使它等价于 age = 19

2、尽量将索引的选择性（不重复的索引值数 / 表记录总数）高的列作为索引，而像性别只有男或者女两个值的这种情况，即使加了索引，也不能很有效的降低数据查询量。索引的选择性可以通过如下 sql 计算，

select count(distinct(field)) / count(*) from tb_name;

3、如果索引字段的值很长，可以考虑使用前缀索引，这样可以降低索引的空间占用。

alter table tb_name add key (field(length)); -- 将某个字段的前 length 位作为索引

4、合理使用多列索引，而不是为每个字段都建立单独的索引。但是当扩充的索引字段会急剧影响原有索引的时候，任然需要创建新的索引值。

-- 创建联合索引（f1,f2）比单独创建f1 和f2的索引好
select * from tb_name where f1 = v1 and f2 = v2;

5、多列索引的顺序优化：一般而言，当不需要考虑排序和分组时，将索引选择性最高的列作为索引的第一列，索引只作用于 where 条件的查找。但是需要注意一些特殊的情况，可能在某个条件下，高选择性的列对应的记录数出奇的多，这样过滤效果就不明显了。

6、使用索引扫描排序：只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向一致，才能使用索引进行排序。当查询需要关联多个表的时候，只有当 ORDER BY 子句引用的字段都是第一个表时，才能使用索引做排序。

select * from tb_name where f1 = vl order by f1,f2 asc; -- 不能是f1 asc f2 desc

7、删除重复、冗余索引：如 unique(f1)、index(f1) 就是两个重复的索引，重复的索引会增加需要的存储空间，同时也会影响 SQL 执行计划的判断，因此需要删除。