MySQL 索引

索引基础

索引的类型

• B-Tree 索引： 最常见的索引类型，大部分索引都支持 B 树索引。
• HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
• R-Tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
• Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

测试数据

本文所使用测试数据如下：

创建索引

CREATE  INDEX index_title_price ON tb_item(title,price)

B-Tree索引简介

B-Tree索引适用于全键值、键值范围或键前缀查找。

前面所述的索引对如下类型的查询有效：

• 全值匹配：全值匹配指的是和索引中所有列进行匹配。
• 匹配最左前缀：前面建立的索引可以匹配titile是’货物5号’的行
• 匹配列前缀：也可以匹配某一列的值的开头部分
• 匹配范围值
• 精确匹配某一列并范围匹配另一列

下面是一些B-Tree的限制：

• 如果不是按照索引最左列开始查找则无法使用索引
• 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效
• 不要在索引列上进行运算操作， 索引将失效

下面演示匹配列前缀的情况

mysql> EXPLAIN SELECT * FROM tb_item WHERE title LIKE '货物20%';
+----+-------------+---------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_item | NULL       | range | index_title_price | index_title_price | 302     | NULL | 1111 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM tb_item WHERE title LIKE '%物20%';
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | tb_item | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 71073 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

查询’货物20%‘使用了索引，而查询’%物20%'不能使用前缀。

高性能的索引策略

1 独立的列

独立的列指索引列不能是表达式的一部分，例如下面这个查询无法使用索引

SELECT id FROM tb_item WHERE id+1=5;

很容易看出要查询id=4的列，但MySQL无法自动解析方程式，故无法使用索引。

2 前缀索引和索引选择性

有时候需要索引很长的字符串，这时可以索引列开始的部分字符，可以大大节约索引空间。
要选择足够长的前缀以保证较高的选择性，同时又不能太长（节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。

3 多列索引

一个常见的错误是，为每个列创建独立的索引，或按照错误的顺序创建索引。

4 选择合适的索引列顺序

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序。对于如何选择索引顺序有一个经验法则：将选择性最高的放在索引最前列。例如学号的选择性就比性别选择性高。

5 聚簇索引

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页，所以一个表只能有一个聚簇索引。
优点：

• 可以把相关数据保存在一起。
• 数据访问更快。

缺点：

• 插入速度依赖于插入顺序
• 更新聚簇索引的代价很高，会强制InnoDB将每个被更新的行移动到新的位置
• 基于聚簇索引的表插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题
• 二级索引（非聚簇索引）可能比想象的要大，因为二级索引的叶子节点包含了引用行的主键列。故通过二级索引进行查找，储存引擎需要找到二级索引的叶子节点并获得主键值，如何根据这个值去聚簇索引查找对应的行。

6 覆盖索引

一个索引包含所需要查询的字段的值，我们称之为覆盖索引。
好处：

• 索引条目远小于数据行大小，如果只读取索引，MySQL可以极大地减少数据访问量。
• 可以 避免对主键索引的二次查询。