mysql索引 – 极客空间

REF：高性能mysql

1. mysql支持的索引

B树索引
hash索引
空间数据索引
全文索引

不同的存储引擎支持的索引类型不同，只有Memory引擎显式支持哈希索引。不同的存储引擎对于同类型的索引实现可能不同。

2. 索引类型详细说明

2.1. B树索引

合理使用B树索引，可以提升查询及排序性能，B树本事为支持有序遍历的数据结构。

2.1.1. 索引结构

B树索引在InnoDB的实现为B+树，非叶子节点存储索引key，只有叶子节点才存储数据，并在叶子节点增加一个指针指向相邻叶子节点（页上会存储上一页和下一页）就形成了带有顺序访问指针的B+树。
MYSQL-B+TREE索引原理以及为什么使用的B+Tree而非B-Tree
B+Tree index structures in InnoDB

2.1.2. B树适用的查询类型

B树索引适用于全键值、键值范围和键前缀查找。其中键前缀查找只适用于根据最左前缀查找。

全值匹配
全值匹配即对索引中所有列进行匹配。如有索引index(last_name,first_name),那么全值匹配即为where last_name='xxx' and 'first_name'='xxx'
匹配最左前缀
使用索引的前N个列进行匹配。
匹配列前缀
匹配某一列的值的开头部分。
匹配范围值
精确匹配某一列并范围匹配另外一列
只访问索引的查询
B树索引通常可以支持值访问索引的查询，即查询只需要访问索引，无需访问数据行。

2.1.3. B树索引有效性限制

如果不是按照索引的最左列开始查找则无法使用索引。
不能跳过（组合）索引中的列直接通过后面的列查找
如果查询中有某个列的范围查询，则其右边的列都无法使用索引优化查找。
比如有索引index(a,b,c)，如果查询where a='xxx' and b<'yyy' and c='zzz' 那么第三格条件c无法使用索引。

2.2. 哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效，不支持范围查询。
mysql引擎对每一行数据的索引列进行hash作为键，值保存的是指向每个数据行的指针。

3. 索引的优缺点

优点
- 大大减小了服务器需要扫描的数据量
- 帮助服务器避免排序和临时表
- 可以将I/O变为顺序I/O
缺点
- 增加插入、更新、删除的维护成本

4. 高性能索引策略

4.1. 独立的列

索引列不能是表达式的一部分，也不能是函数的一部分，否则索引失效。

4.2. 哈希索引模拟

通过对需要索引的长列A创建其对应哈希值的列A‘，从而对该列创建索引转而对A’创建索引。通过该方法解决存储引擎不支持哈希索引或索引列的长度过长导致性能不佳的方案

4.3. 前缀索引和索引的选择性

前缀索引是仅仅通过索引数据列值的前n个字符以解决的是当索引列过长导致索引效率降低的问题。
----那么n如何定？----
解决索引过长的问题我们希望n越小越好，但是n过小可能导致重复索引数据过多，索引同样低效，所以我们需要一个平衡点。
----索引的选择性-----
索引的选择性即不重复的索引值与数据表的总记录的比值。索引的选择性越高查询效率越高。

----找出最佳n----
从n=3开始获取该值下重复的索引数以及不重复的索引值与数据表的总记录的比值

4.4. 多列索引（组合索引）

组合查询场景多或者查询频繁时尽量使用多列索引，对查询列建立单独的索引性能不高。
查询时一定要根据实际查询顺序创建顺序合理的多列索引（考虑where和order）。常用的顺序参考：
- 根据查询频繁度的顺序创建，充分利用最左前缀。
- 根据单列过滤数据量大小的顺序(索引的选择性大小)，由大到小。

4.5. 聚簇索引

聚簇索引把数据行和相邻的键值紧凑存储在一起，由于无法同时把数据行存放在两个不同的地方，所以一张表只能有一个聚簇索引。
聚簇索引的节点页只包含了索引列，叶子页包含了行的全部数据。
InnoDB中主键即为聚簇索引，没有主键时会选择一个唯一的非空索引代替。如果没有这样的索引，会隐式定义一个主键作为聚簇索引。
聚簇索引优点
- 可以把相关数据保存在一起。？？
- 数据访问更快。
- 使用覆盖索引扫描的查询可以直接使用页节点的主键值。
聚簇索引缺点
- 插入速度严重依赖于插入顺序。非顺序插入时要考虑使用OPTIMIZE TABLE命令重新组织一下表。
- 更新聚簇索引的代价很高。
- 基于聚簇索引的表在插入新行或者主键被更新导致需要移动行的时候可能面临“页分裂”问题
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏或者由于页分裂导致数据存储不连续时。
- 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
- 二级索引访问需要两次索引查找。
自增主键问题
- 在高并发工作负载中，主键顺序插入可能会造成明显的争用，产生间隙锁竞争，另一个热点是AUTO_INCRMENT锁机制。出现这类问题需要考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mode配置.

4.6. 覆盖索引

普通索引中索引列覆盖所有需要查询的字段的值，称之为覆盖索引，这样的查询不需要再通过主键索引回表查询，查询效率高。

4.7. 使用索引扫描来做排序

1. mysql生成有序结果的操作方式：

排序操作。即先过滤数据再对数据进行排序。
按索引顺序扫描（explain出来的type列的值为index表示按索引顺序扫描）

索引可以用来进行条件查询，同时也可用于排序操作，而按照索引顺序扫描的效率是比较高的，排序是否能使用到索引同样要满足最左前缀匹配，前期尽可能的设计使其满足。

4.8. 压缩（前缀压缩）索引

MyISAM存储引擎使用前缀压缩来减小索引大小，从而让更多的索引可以放入内存中。

4.9. 冗余和重复索引

mysql允许在相同列上创建多个索引，mysql需要单独维护重复的索引，并且优化器在优化查询到时候也需要逐个地进行考虑，会影响性能，尽量避免这样的事情。

4.10. 未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引，建议删除。

定位方法：

Percona Server或者MariaDB打开userSTATES服务器变量（默认关闭），然后服务正常运行一段时间，再查询INFORMATION_SCHEMA.INDEX_STATISTICS就可以查询到每个索引的使用频率。还可以使用Percona Toolkit的pt-index-usage查找。

4.11. 索引和锁

索引可以让查询锁定更少的行。

5. 索引设计及查询优化

5.1. 考虑查询和排序优先级

设计索引时首先要考虑排序和查询的场景，是使用索引查询再排序，还是使用索引排序，如果使用索引排序会严格限制索引和查询的设计。

5.2. 设计考虑支持多种过滤条件

分析列数据选择性
分析查询使用频率
避免维护多个组合索引

根据以上分析考虑创建合适的组合索引，一般情况下查询频率高或者数据选择性大的排在前面。当两者冲突，比如查询频率最高，但是数据选择性很低也可以考虑放在前面。比如社交网站搜索条件可能包含性别、年龄、国际、区域等，性别是多种查询中选择频率最高的，几乎所有的查询都用到了该列，那么这种情况下可以考虑将性别放在最前面（避免放到后面无法满足比如只匹配性别而需要维护多个不同的组合索引）,这种情况下当碰到不需要使用性别查询的可以在条件中增加AND sex IN('M','F')来让mysql选择该索引。

5.3. 避免多个范围条件

对于范围查询，mysql无法再使用范围列后面的其他索引列，但对于多个等值条件查询则没有这个限制。

5.4. 优化排序

创建适合的索引
延迟关联（先排序得到最终数据主键，再用主键获取需要的最终数据）

6.索引下推

//TODO

参考

7. 索引总结

索引的规划很重要，需要尽量多的考虑查询场景，来建立合理的索引。
一条查询语句中mysql会选择一个最佳索引来查询(使用多个索引并不会有什么优化效果)，所以当查询条件涉及多个列时尽量考虑组合索引。
组合索引条件语句中只能使用一个范围查询，范围查询后的列无法使用到索引来过滤数据
索引查询优化核心点
- 只拉取需要的行数据
- 只拉取需要的列数据（可以为了开发扩展性在考虑到全部字段拉取后果的情况下使用）
- 多条件考虑使用组合索引，通过最左前缀规则命中索引
- 使用覆盖索引，少回表查询
- 排序操作时使用最左前缀（where+order by字段满足最左前缀）使用索引排序
- 延迟关联

8. 扩展示例说明

假设一个用户社交平台，以下为用户的相关信息，常用查询：性别、省、市、年龄（范围查询）联合查询

CREATE TABLE user (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  sex char(1) NOT NULL,
  birthday date DEFAULT NULL,
  province varchar(255) DEFAULT NULL,
  city varchar(255) DEFAULT NULL,
  address varchar(255) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY index_name (name) USING BTREE,
  KEY ind_sex_country_city_birthday (sex,province,city,birthday,name) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=500000 DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;


--   ---------------------------------------------------
-- 主键定值查询最快，查询类型const
explain select * from user t where t.id = 10002;

-- 主键范围查询，效率也比较高，查询类型为range
explain select * from user t where t.id>1000;
--   ---------------------------------------------------
-- 使用索引匹配固定值与匹配in效果一致
-- 使用索引匹配固定值
explain select * from user t where t.sex='M';
-- 使用索引匹配in
explain select * from user t where t.sex in ('F');
--   ---------------------------------------------------
-- 覆盖索引
-- 查询所有列需要回表
explain select * from user t where t.name like 'Ann%';
-- 查询的列索引可覆盖到，不需回表更高效
explain select name from user t where t.name like 'Ann%';
--   ---------------------------------------------------
-- 排序，当排序的单列或多列满足最做前缀或者where条件与order列组合形成最左前缀排序将使用索引排序
-- 不匹配最左前缀使用外部排序
explain select * from user t where t.sex='F' order by t.birthday; -- 排序字段非最左前缀
explain select * from user t where t.sex='F' order by t.birthday,t.province,t.city; -- 排序顺序与索引顺序不一致

-- 使用索引排序
explain select * from user t where t.sex='F' and t.province is null and t.city is null order by t.birthday;
explain select * from user t where t.sex='F' order by t.province,t.city,t.birthday;
explain select * from user t where t.sex='F' order by t.province desc,t.city desc,t.birthday desc; -- 整体降序

-- 使用外部排序
explain select * from user t where t.sex='F' order by t.province,t.city,t.birthday desc; -- 部分升序部分降序使用外部排序
--   ---------------------------------------------------

索引总结参考

一	二	三	四	五	六	日
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31