MySQL索引详解及优化策略

1. 索引介绍

索引是存储引擎用于快速查找记录的一种数据结构。索引优化是对查询性能优化最有效的手段。
索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。

1.1 索引优点

索引可以让服务器快速定位到表的指定位置:最常见的B-Tree索引，按照顺序存储数据，所以MySQL可以用来做ORDER BY 和 GROUP BY操作.
总结下来，索引有如下三个优点：

1. 索引大大减少了服务器需要扫描的数据量；
2. 索引可以帮助服务器避免排序和临时表；
3. 索引可以将随机I/O变为顺序I/O。

1.2 适合建立索引的字段

1. 经常搜索
2. 经常排序
3. 经常跟在Where语句后面
4. 数据类型小
5. 简单数据类型
6. 列中尽量避免null

2. 索引类型

2.1 B-Tree索引

使用B-Tree数据结构来存储数据，大多MySQL引擎都支持该索引。B-Tree索引可以加快访问数据的速度，因为B-Tree对索引列顺序组织存储，范围查找快.

1. 主键和唯一性约束字段的B树索引，效率几乎和海量数据没有关系。
2. 键值重复率低的字段比较适合使用B树索引。

• 唯一索引
  索引列的所有值都只能出现一次，即必须唯一，值可以为空,一对一关系。
  普通索引(一对多关系)是允许数据重复的，如果确定了某列数据不会重复，则可创建唯一索引，唯一索引有两个好处，索引更有效：插入新数据，如果重复，MySQL拒绝插入。
• 主键索引
  主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键
  主键本身默认创建索引

2.1.1 可以使用B-Tree索引的查询类型

1. 全值匹配：全值匹配指的是和索引中的所有列进行匹配。
2. 匹配最左前缀：即只使用索引的第一列。
3. 匹配列前缀：也可以只匹配某一列的值的开头部分。
4. 匹配范围值
5. 精确匹配某一列并范围匹配另外一列
6. 只访问索引的查询：覆盖索引

2.1.2 索引顺序查询

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作(按顺序查找)。
一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。

2.1.3 B-Tree索引的限制

1. 如果不是按索引的最左列开始查找，则无法使用索引。
2. 不能跳过索引中的列。
3. 如果查询中有某个列的范围查询，则最右边的所有列都无法使用索引优化查询。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围查找。

2.2 Hash索引(Memory引擎支持)

哈希索引基本哈希表实现，只有精确匹配索引所有列的查询才有效。结构十分紧凑，查询速度非常快。
对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码值较小。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。MySQL中只有Memory引擎显示支持哈希索引。哈希表只能在内存实现，没法在磁盘实现.
索引列会被存储在匹配到的hash bucket里面的表里，这个表里会有实际的数据行指针，再根据实际的数据行指针查找对应的数据行。
hash索引适用于等值(=)查询

2.2.1 Hash索引流程

概括来说，要查找一行数据或者处理一个where子句：

1. 根据where条件里面的参数生成合适的哈希函数
2. 索引列进行匹配，匹配到对应hash bucket，找到对应hash bucket意味着也找到了对应的数据行指针（row pointer）
3. 读取数据
   哈希索引比起B树索引简单，因为它不需要遍历B树，所以访问速度会更快
   

2.2.2 Hash索引的缺点

1. 因为Hash索引比较的是经过Hash计算的值，所以只能进行等式比较，不能用于范围查询
2. 由于哈希值是按照顺序排列的，但是哈希值映射的真正数据在哈希表中就不一定按照顺序排列，所以无法利用Hash索引来加速任何排序操作
3. 不能用部分索引键来搜索，因为组合索引在计算哈希值的时候是一起计算的。
4. 当哈希值大量重复且数据量非常大时，其检索效率并没有B-tree索引高。

2.3 空间索引(R-Tree)

MyIsam表支持空间索引，Mysql本身对GIS的支持并不完善
开源关系数据库中对GIS的解决方案做的比较好的是PostgreSQL的PostGIS

2.4 全文索引（MyISAM引擎支持）

全文索引是一种特殊的索引，它查找的是文本中的关键词而不是直接比较索引中的值。
全文索引更加类似于搜索引擎做的事情，而不是简单的WHERE匹配。在同一列上同时创建全文索引和基于值的B-Tree索引不会有冲突。
全文索引主要用于词汇的快速搜索，比较适合对列中的字段进行模糊查询（如SQL语句中包含%)或者语言类的查询，其缺点是占用空间太大（会创建很多中间表）

应用场景
文本字段上的普通索引只能加快对出现在字段内最前面的字符串进行的检索操作，如果字段里存放的是由几个或者多个单词构成的大段文字，普通索引就不行了，这种场合用全文索引比较合适
效率
查询效率：唯一索引>自增主键>主键
插入效率：主键>自增主键>唯一索引
创建全文索引

• 创建arti