MySQL创建高性能的索引（三）

MySQL

索引是存储引擎快速找到记录的一种数据结构。使用索引可以极大的提高MYSQL的查询性能，接下来讲介绍索引的基础、索引的分类和区别、索引的注意事项、以及关于聚簇索引和覆盖索引的知识点。

索引基础

当你去看一本书，如何快速找到你想打开的页？ 你一定会先看书的索引部分，在根据索引的页码数直接定位到目标页。相比你一页页的去翻看极大的节省了时间。

MYSQL的索引也是同样的原理，当我们创建某个索引时，MYSQL会为我们维护一套索引树，MYSQL的索引是在存储引擎层面实现的，所以不同的存储引擎，索引存储的数据结构也不相同，Innodb使用的是B+Tree的数据结构，是B-Tree的一种变种。

B-Tree索引适用的查询类型

1. 全值匹配，指索引是1个或者n个字段组合，所有索引字段参与匹配查询
2. 匹配最左前缀，指索引是n个字段组合，需要从最左前缀进行匹配
3. 匹配列前缀，指索引可以使用索引字段，like匹配前缀内容，如like ‘abc%’
4. 精确匹配某一列并范围匹配另外一列
5. 只访问索引查询，这种就是覆盖查询，只会遍历索引，不会访问数据行

B-Tree注意事项

1. 多列索引必须按照最左前缀匹配，否则无法应用索引
2. 多列索引中不能跳过索引列，比如A，B，C字段三个列组合索引，如果查询使用where A=1 and C=1，则只会使用A列索引
3. 多列索引中范围查询的某个列，后面的字段都无法使用索引查询，如果范围查询有限，可以使用IN代替
4. Like匹配不可以使用like ‘%123’，不会走索引，而like ‘123%’可以走索引

索引分类和区别

索引主要分B-Tree、Hash索引、全文索引、空间数据索引

B-Tree索引

1. B-Tree索引是有序排列，非常适合范围查询、排序
2. B-Tree多列组合索引，排序按照列的顺序排序
3. B-Tree索引支持全值匹配、匹配最左前缀、匹配列前缀、精确匹配某一列范围匹配另外一个列、只访问索引数据（索引覆盖查询）
4. B-Tree不能索引非最左前缀、不能跳过索引列、查询有某个列范围查询，则后面的查询无法使用索引查询（可以使用等于条件代替范围查询）

Hash索引

1. 基于哈希表实现，需要精准匹配
2. 不支持范围、部分查询，不支持排序，
3. hash冲突使用链表法
4. Innodb支持自适应hash索引，即当MYSQL发现某些索引频繁使用时，基于内存创建Hash索引

空间索引

空间索引一般做地理数据存储，一般用的不多

全文索引

索引比较特殊，它查找是文本中的关键词，而不是比较索引中的值，实现比较复杂，一般使用搜索引擎(Elasticsearch)代替

索引注意事项

1. 索引不能有表达式、函数参与，否则无法使用索引，且无法应用查询缓存
2. 多列索引应该注意索引的顺序，一般选择性较高的放在第一列
3. 注意某个列是否出现相同的值频率比较高，比如用户名默认设置guest，导致在某种查询（查询等于guest）会趋于扫描全表的性能
4. 对于索引字段过长，可以使用hash索引代替

聚簇索引

聚簇索引并不是单独的一种索引类型，而是一种索引的存储方式，
聚簇索引叶子页会包括数据行所有的列，节点页只包含索引列。

MYSQL会选择主键作为聚簇索引，如果没有主键会选择唯一非空索引，否则MYSQL会隐式创建一个主键索引

优点：

1. 访问数据更快。因为索引数据都保存在B-tree树上，无需在去获取数据行数据
2. 使用覆盖索引扫描查询可以直接使用页节点中的主键值

缺点：

1. 更新速度严重依赖插入顺序
2. 更新聚簇索引代价比较高，会强制InnoDB把每个被更新的行移动到新的位置
3. 更新移动的同时，有可能会产生页分裂，因为移动的新位置原本的页已经满了，需要分裂页进行存储

索引覆盖

索引覆盖是一种优化方式，当查询的列是索引全部覆盖的列，则只会查询索引树，无需再回表查询；

比如对于分页偏移量比较大的情况，如limit 100000,20, MYSQL需要先查询100020条数据，在取20条，偏移量越大越影响性能；

因此可以采用覆盖索引的方式；
x = select id from table where id > 100000 limit 20;
在查询select * from table where id in (x);

上面的方式依赖于自增主键的形式，同时记录列上次的浏览记录。

本文如有任何问题，请留言指教，以免误导他人