MySQL索引

MySQL主要索引类型

B-Tree索引

大多数的存储引擎使用的是B-Tree（从技术上来说是B+Tree）索引，B-Tree索引通常所有的值都是按顺序存储的，每个叶子到的根的距离相同。InnoDB使用的是B+Tree。
B-Tree索引使用可以以如下数据表为例进行分析：
CREATE TABLE product (
id int primary key auto_increment,
name varchar(64) not null,
prud_time date not null,
num int not null,
type int not null,
unit varchar(16) not null,
key(name,prod_time,num)
);

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

1. 全值匹配
   和索引中所有的列进行匹配；
2. 匹配最左前缀
   如匹配所有name为手机的产品，即只使用索引的第一列；
3. 匹配列前缀
4. 只匹配某一列的值的开头部分；
5. 匹配范围值
6. 精确匹配某一列并范围匹配另外一列
7. 只访问索引的查询
   查询值需要访问索引，不需要访问数据行。查询效率高。

B-Tree索引的限制

1. 如果不是按照索引的最左开始查找，无法使用索引；
2. 不能跳过索引中的列
3. 如果查询中使用了某个列的范围查询，则其右边的所有列都无法使用索引；

哈希索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有索引类计算一个哈希码。索引结构紧凑，查找速度快。

哈希索引的限制

1. 哈希索引只包含哈希值和行指针，不存储段值，不能使用索引中的值来避免读取行；
2. 哈希索引数据不是按照索引值顺序存储，无法用于排序；
3. 哈希索引不支持部分索引列匹配查找；
4. 哈希索引值支持等值比较查询，包括=、IN()、<=>，不支持任何范围查询；
5. 当哈希索引出现哈希冲突时，存储引擎必须遍历链表中说有的行指针，逐行比较找到所有符合条件的行；
6. 如果哈希冲突很多的话，索引的维护操作的代价也很大。

索引的优点

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

高性能使用索引

独立的列

如果查询中的列不是独立的列，MySQL不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能使函数的参数；以下查询不能使用id列的索引。
SELECT id FROM product WHERE id+1=10；

前缀索引和索引选择性

如果索引是很长的字符列，这会让索引变得很大且慢。这种情况通常可以将列的前缀部分字符作为索引，这样可以大大节约索引空间，提高索引效率。但这样也会降低索引的选择性。索引的选择性，是指不重复的索引和数据表的总记录数的比值，索引选择性越高查询效率也越高。
前缀索引(取ciry名称前7个字符为索引)创建：
ALTER TABLE demo ADD KEY (city(7))；

多列索引

如果查询条件涉及多个列，应该建一个含有多列的索引，而不是为每个列创建独立的索引，或者按照错误的顺序创建多列索引。

选择合适的索引顺序

正确的顺序依赖于使用该索引的查询，同时需要考虑如何更好地满足排序和分组的需要。
在一个多列的B-Tree索引中，索引首先按照最左列进行排序，其次是第二列，索引可以按照升序或降序进行扫描，以满足精确符合列顺序的 ORDER BY 、GROUP BY 和DISTINCT等子句的查询。
如何选择多列索引顺序经验法则：将选择性最高的列放到索引最左。这在某些场景可能有帮助，但通常避免随机IO和排序更重要。当不需要考虑排序和分组时，将选择性最高的列放在索引最左通常是好的。

聚簇索引

覆盖索引

当一个索引包含所有需要查询的字段的值，称之为覆盖索引。覆盖索引极大提高性能，查询只需扫描索引不需要返回表查询数据行。

使用索引扫描来做排序

mysql有两种方式可以生成有序的结果：一排序操作；二按索引顺序扫描。如果EXPLAIN出来的type的值为“index”,则说明mysql使用了 索引扫描来排序。
扫描索引本身是很快的，只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就回表查询一次对应的行，这基本是随机IO，所以按索引顺序读取数据的 速度一般比顺序地全表扫描慢。
MySQL可以使用用一个索引既满足排序又用于查询，这样最好。
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（都是正序或倒序）都一样时，MySQL才能使用索引来对结果排序。
如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。
ORDER BY子句和查询的限制是一样的，需要满足索引的最左前缀的要求，否则MySQL都需要执行排序操作，无法使用索引排序。
有一种情况ORDER BY子句可以不满足索引的最左前缀要求，就是前导列为常量时。

压缩（前缀压缩）索引

MyISAM引擎使用前缀压缩来减少索引大小。

冗余和重复索引

重复索引是指在相同的列上按照相同的顺序创建相同类型的索引。MySQL需要单独维护重复的索引，优化器在优化查询时也需要逐个地考虑，会影响性能。应该避免创建重复索引。
冗余索引，如果创建了索引(a,b)，再创建索引(a)，那么索引(a)就是冗余索引（这种冗余只是对B-Tree索引来说的）。但如果再创建索引(b,a)则不是冗余索引。
大多数情况都不需要冗余索引

删除未使用的索引