MySQL-03-索引

       索引是提高MySQL查询性能的一个重要途径，但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用，从而影响应用程序的整体性能。应当尽量避免事后才想起添加索引，因为事后可能需要监控大量的SQL才能定位到问题所在，而且添加索引的时间肯定是远大于初始添加索引所需要的时间，可见索引的添加也是非常有技术含量的。

1-为什么要使用索引

在数据库中，我们为什么需要使用索引呢？一般有以下几点原因：
（1）索引大大减少存储引擎需要扫描的数据量；
（2）索引可以帮助我们进行排序避免使用临时表；
（3）索引可以把随机IO变为顺序IO；

2-索引的数据结构

       通常我们所说的索引是指B-Tree索引，它是目前关系型数据库中查找数据最为常用和有效的索引，大多数存储引擎都支持这种索引。使用B-Tree这个术语，是因为MySQL在CREATE TABLE或其它语句中使用了这个关键字，但实际上不同的存储引擎可能使用不同的数据结构，比如InnoDB就是使用的B+Tree。

      B+Tree中的B是指balance，意为平衡。需要注意的是，B+树索引并不能找到一个给定键值的具体行，它找到的只是被查找数据行所在的页，接着数据库会把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

       理解B+Tree时，只需要理解其最重要的两个特征即可：第一，所有的关键字（可以理解为数据）都存储在叶子节点（Leaf Page），非叶子节点（Index Page）并不存储真正的数据，所有记录节点都是按键值大小顺序存放在同一层叶子节点上。其次，所有的叶子节点由指针连接。

3-高性能索引策略

前缀索引：如果列很长，通常可以索引开始的部分字符，这样可以有效节约索引空间，从而提高索引效率。

联合索引

（1）一个联合索引可以相当于几个索引，例如（a,b）建立联合索引 
select * from tablename where a=’xxx’ and b=’xxx’  (可以使用索引)
select * from tablename where a=’xxx’  (可以使用索引)
select * from tablename where b=’xxx’  (不可以使用索引)
（2）联合索引已经对第二个键值进行了排序处理，例如(userid,buy_date)联合索引，前提是已经先按照userid条件查询。
查询用户userid=1的最近3次购买记录
select * from buy_log where userid=1 order by buy_date desc limit 3
根据该联合索引取出的数据，无须再对buy_date做一次额外的排序操作。如果extra选项中出现using filesort，就是需要额外的一次排序才能完成查询。
对于联合索引（a,b,c）来说，下列语句同样可以直接通过联合索引得到结果：
select * from tablename where a=’xxx’ order by b;
select * from tablename where a=’xxx’ and b=’xxx’ order by c

联合索引的顺序选择：（1）经常被使用列优先；（2）选择性高的列优先；（3）宽度小的列优先

覆盖索引

InnoDB存储引擎支持覆盖索引（covering index），即从辅助索引中就可以查询的记录，而不需要查询聚集索引中的记录。
覆盖索引优点：
（1）辅助索引不包含整行记录的所有信息，故其大小远小于聚集索引，因此可以减少大量的IO操作。
查看执行计划Extra列的Using index就是表示优化器进行了覆盖索引操作。

（2）对某些统计问题而言。
此外，在通常情况下，诸如(a,b)的联合索引，一般是不可以选择b中的所谓的查询条件。但是如果是统计操作，并且是覆盖索引，则优化器会进行选择。
例如：select count(*) from buy_log where buy_date >=’2011-11-01’ and buy_date<=’2013-12-02’
表buy_log有（userid,buy_date）联合索引，这里只根据buy_date进行查询，一般情况下是不能使用该联合索引的，但是这句sql是查询统计操作，并且可以利用到覆盖索引的信息，因此优化器会选择联合索引。

MRR优化Multi-Range Read

MRR从mysql5.6版本开始支持，MRR优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound类型的sql查询可带来性能极大的提升。MRR优化可适用于rang，ref，eq_ref类型查询。
MRR优化的好处：
（1）MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并且按照主键排序的顺序进行书签查找。
（2）减少缓冲池中页被替换的次数
（3）批量处理对键值的查询操作。

例如：
Select * from salaries where salary>10000 and salary<40000  (salarys上有辅助索引idx_s)
不启用MRR特性，Extra列显示 using index condition
如果启用MRR特性，Extra除了显示 using index condition，还会显示using MRR

此外，MRR还可以将某些范围查询，拆分为键值对，以此来进行批量的数据查询。这样做的好处是在拆分的过程中，直接过滤一些不符合查询条件的数据。
例如：select * from t where key_part1>1000 and key_part1<2000 and key_part2=10000
表t有（key_part1, key_part2）的联合索引，因此索引根据key_part1，key_part2的位置关系进行排序。若没有MRR，此时查询类型为range，sql优化器会先讲将key_part1大于1000并且key_part1小于2000的数据都取出，即使key_part2不等于10000，取出数据后再根据key_part2进行过滤，这会导致无用数据被取出，尤其是有大量的数据并且key_part2不等于10000。 
如果启用了MRR优化，优化器会先将查询条件进行拆分，然后再进行数据查询。

启用MRR：
show variables like 'optimizer_switch'

 
Mysql5.7默认mrr_cost_base默认标记是on
mrr_cost_base标记表示是否通过cost_base的方式来选择是否启用mrr。如果将mrr设为on，mrr_cost_base设为off，则总是启用MRR优化。

index_condition_pushdown（ICP）

       ICP是mysql5.6版本开始支持的，之前版本的mysql数据库不支持ICP，当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录。在支持ICP后，mysql数据库在取出索引的同时，判断是否可以进行where条件过滤，也就是将where的部分过滤操作放在了存储引擎层（前提是where的过滤条件是要改索引可以覆盖到的范围）。在某些查询下，可以大大减少上层sql层对记录的索取（fetch），从而提高数据库整体性能。
ICP优化支持range、ref、eq_ref、ref_or_null类型的查询，支持myisam和InnoDB，当优化器选择ICP优化时，可在执行计划列Extra看到Using index condition 提示。