mysql索引总结

mysql索引
InnoDB使用B+Tree
B-Tree索引
1.所有的值都是按顺序存储的，并且每一个叶子到根的距离相同
2.B-Tree对索引列是顺序组织存储的，很适合查找范围数据。
索引之所以能加快查询速度，是因为存储引擎不再需要全表扫描，而是从索引的根节点开始进行搜索。根节点中存放了指向子节点的指针，存储引擎根据指针向进入下层子节点。最终存储引擎要么找到对应的值，要么记录不存在。
3.索引对多个值进行排序的依据是create table 语句中定义索引时列的顺序。
4.B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。
全值匹配指的是和索引中所有的列进行匹配；
匹配最左前缀指的是只使用索引的第一列；
匹配列前缀指匹配某一列的值的开头部分，如：like 'J%',只适用索引的第一列；
匹配范围值，
5.B-Tree索引的限制：
如果不是按照索引的最左列开始查找，则无法使用索引；
不能跳过索引中的列；
如果查询中有某个列的范围查询，则其右边所有的列都无法使用索引优化查找；

哈希索引（hash index）
基于哈希表实现，只有精确匹配索引所有列的查询才有效。
mysql中，只有Memory引擎支持哈希索引
查找数据方式：计算出查找数据的hash code,根据hash code找到指向数据行的行指针，根据指针找到对应的数据行的数据
hash索引限制：
哈希索引值包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
哈希索引数据不是按照值顺序存储的，所以无法用于排序；
哈希索引不支持部分索引列的匹配，因为hash索引是根据索引列的全部内容来计算hash值的；
哈希索引只支持等值的比较，包含 = ,in(),<=> (<=>和<>是两个不同的操作)
当出现哈希冲突的时候（不同的索引值，却有相同的哈希值），必须遍历链表中的所有行指针，逐行比较
如果哈希冲突很多的话，维护索引的代价会很高

空间数据索引（R-Tree）
MyISAM表支持空间索引，可以用作地里数据存储。空间索引会从所有维护来维护数据，查询时可以用任意维护的组合来查询。
必须使用mysql的GIS相关函数如MBRCONTRINS()来维护数据，但Mysql的GIS支持并不完善。

全文索引
查找的是文本中的关键词，而不是索引中的值，适用于match against操作 

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

注意：

对于非常小的表，大部分情况下简单的表全表扫描效率更高；
对于中到大型的表，索引非常有效；
对于特大型的表，建立和使用索引的代价会随之增长。可以使用分区技术来实现数据的维护。

选择合适的索引顺序


在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次第二列。索引可以按照升序或降序进行扫描，以满足符合列顺序的order by,group by和distinct.
当不考虑排序和分组时，将选择性最高的列放到索引的最前列，可最快的过滤出需要的行，对于where条件中只使用了索引前缀列的查询来说选择性更高。


聚簇索引
聚簇索引是一种数据存储方式，具体的细节依赖于其实现方式。
聚集数据的优点：
1.可把相关数据保存在一起。
2.数据访问快
3.使用覆盖索引扫描的查询可直接用页节点中的主键值

缺点：
1.插入速度依赖于插入顺序；按照主键的顺序插入是加载数据到InnoDB表速度最快的方式。
2.更新聚簇索引的代价高；因为会强制将每一个被更新的行移动到新的位置。
3.聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或页分裂导致数据存储不连续的时候。

按主键顺序插入行
如果没有什么数据需要聚集，可定义一个代理键作为主键，设置为自增，这样可保证数据行是按顺序写入，对于根据主键做关联操作的性能会更好。
使用UUID做聚簇索引，会导致插入变得完全随机，性能很糟糕。

覆盖索引
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，称之为覆盖索引。


不是所有类型的索引都可称之为覆盖索引。覆盖索引必须存储索引列的值，而哈希索引，空间索引和全文索引等都不存储索引列的值，所以Mysql中只能使用B-Tree索引做覆盖索引。


如果索引不能覆盖查询所需的全部列，那需扫描一条索引记录就都回表查询一次对应的行，这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序的全表扫描慢。

只有当索引列的顺序和order by子句的顺序一致，并且所有的排序方向都一样，mysql才能使用索引对结果做排序。如果查询需要关联多张表，只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序。order by子句和查询的限制是一样的：需要满足索引的最左前缀的要求，否则mysql无法利用索引做排序。


如：在列（rental_date,inventory_id,customer_id）上建了一个索引rental_date;
create table rental(
...
primary key(rental_id),
unique key rental_date（rental_date,inventory_id,customer_id）,
key idx_fk_inventory_id (inventory_id),
key idx_fk_customer_id(customer_id）,
key idx_fk_staff_id(staff_id),
...
)

1.
where rental_date = '2015-11-22' order by inventory_id,customer_id
order by 不满足索引的最左前缀的要求，也可用于查询排序，因为索引的第一列被指定为一个常数。
2.
where rental_date = '2015-11-22' order by inventory_id desc
索引的第一列提供了常量条件，而使用第二列排序，两列合在一起，形成了索引的最左前缀。
3.
where rental_date = '2015-11-22' order by rental_date, inventory_id
order by的两列就是索引的最左前缀

下面不能使用索引做排序查询：
1.
where rental_date = '2015-11-22' order by inventory_id desc,customer_id asc
索引的列都是正序排序的，不能既有正序，又有倒序
2.
where rental_date = '2015-11-22' order by inventory_id ，staff_id
引用了一个不在索引列中的列
3.
where rental_date = '2015-11-22' order by customer_id 
无法构成最左前缀
4.
where rental_date > '2015-11-22' order by inventory_id ,customer_id 
第一列上是范围查询，无法使用索引的其余列
5.
where rental_date = '2015-11-22'and inventory_id in (1,3)  order by customer_id 
inventory_id列有多个等于条件，对于排序来说，是一种范围查询。

重复索引是指在相同的列上按照相同的顺序创建相同的索引类型。
如：
create table test(
id int not null primary key,
a int not null,
b int not null,
unique(id),
index(id)
) ENGINE=InnoDB;


在主键上先加了唯一限制，又加上索引。实际上唯一限制和主键限制都是通过索引实现的。

如果创建了索引(A,B)，再创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引，如果再创建索引（B,A），则不是冗余索引，索引（B）页不是，因为索引（B）不是索引（A,B）的前缀索引。

冗余索引通常会发生在为表添加索引的时候，如：表中已经存在了索引(A)，然后添加了一个(A,B)

大多数情况下不需要冗余索引，应该尽量扩展已有的索引，而不是新建索引。但有时候出于性能考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，影响其他使用该索引的查询性能。

表中的索引越多，插入速度回越慢。一般来说，增加索引会导致insert,update,delete等操作的速度变慢。

解决冗余索引的方式：找出，删除；可通过一些工具来定位。如：common_schema