目录
1.绪论
2.索引原理
2.1 索引采用的数据结构
2.1.1 B树
1.什么是B树
2.B树的优缺点
2.1.2 B+树
1.什么是B+树
3.B+树的优缺点
2.2.3 hash
2.2.4 总结
2.2 InnoDB数据存储结构(聚簇索引)
2.2.1 自底向上探寻索引
3.2.2 查询一条数据的完整流程
3.2.3 聚簇索引
2.4 MyIsam数据存储结构(非聚簇索引)
2.3 二级索引
2.4 联合索引
2.6 索引的注意点
2.7 索引的代价
3.索引的使用
3.1 索引生效失效场景分析
3.1.1 数据准备
3.1.2 等值匹配
1. 索引列和where条件后面列相等
2. 索引列和where条件后面列部分相等且遵循最左匹配原则
3. 索引列和where条件后面列部分相等但不遵循最左匹配原则
3.1.3 模糊匹配
1. 匹配列前缀
2. 非列前缀模糊匹配
3.1.4. 范围查询
1. 索引最左列进行范围查询
2. 索引非左列进行范围查询
3. 索引列范围查询，并且左侧列等值查询
3.1.5 排序
1. 按照索引列顺序排序
2. 排序列在索引里，但未按照索引顺序
3. 索引列排序，左侧列等值比较
4. ASC和DESC混用
3.1.5 分组
3.2 回表与索引覆盖
3.2.1 回表
1.什么是回表
2.回表的代价
3.2.2 索引覆盖
3.3. 怎么建立索引
3.3.1 尽量只为搜索、排序、分组字段建立索引
3.3.2 尽量为基数较大的列建立索引
3.3.3 索引列的类型尽量足够小
3.3.4 对于字符串，使用字符串前缀建立索引
3.3.5 不要让索引列出现在表达式中
3.3.5 主键尽量递增
3.3.6 减少冗余索引和重复索引
3.4 is null和使用or会走索引吗？
3.4.1 null值在索引中的处理
3.4.2 扫描区间
3.总结
1.绪论
索引可以说是Mysql或者关系型数据库中最重要的部分之一，而通过建立合适的索引来提升查询效率也是我们在日常开发过程中优化的最常用的手段。本文主要讲解索引的原理，以及如何使用索引来提升查询效率。
2.索引原理
2.1 索引采用的数据结构
2.1.1 B树
1.什么是B树
如图所示，B树满足如下特征:
1.B树种的每条数据为一个[key,value]的形式，并且每个节点可以存储多条数据;
2.B树每个节点内元素按照key从小到大排列；
3.对于B树的分支节点，每个节点的key值构成多个范围，比如上图父节点将区间分为<6,[6,20),[20,108),>=108，而他们的所对应的子节点key的范围应该介于上述区间内。
2.B树的优缺点
1）优点:可以将热点数据存储在靠近根节点的位置，加快索引速度。
2）缺点:B树每个节点不仅要存储的key还要存储数据，导致每个节点存储key值变少，让整棵B树变高。
4）使用场景:B树主要用于文件系统或者部分数据索引，比如MogoDB，但是大部分关系型数据库还是采用B+树作为索引。
2.1.2 B+树
1.什么是B+树
如图所示，B+树有如下特点：
1.B+树有叶子节点和非叶子节点之分，非叶子节点值存储key值，非叶子节点存储key值和value值。
2.和B树一样，key值大小从左到右按照从小到大排列，并且子节点的key值一定在父节点的区间范围内。
3.叶子节点之间采用指针连接，构成一个双向链表。
3.B+树的优缺点
1.B+树的非叶子节点值存储key值，每个节点能够存储更多的key值，整棵树的高度更低；
2.B树的数据都存储在非叶子节点上面，所以查询时间为O(H),其中H为B+树的高度，查询时间很稳定；
3.B+树每个叶子节点采用指针连接成双端链表。针对表扫描，只用读取叶子节点，扫描能力更强。
2.2.3 hash
InnoDB中还会有一种数据结构，就是hash，它底层采用hash表，同时采用链地址法来解决hash冲突。查询速度为O(1)，但是它不支持排序和范围查询。
2.2.4 总结
InnoDB默认采用B+树作为索引的数据结构，但是如果某些数据页被频繁访问，InnoDB会将这些索引结构存储到hash表中，提升访问速度。这一过程是InnoDB自己完成的，我们并不能干预。这一过程称为自适应哈希，可以通过innodb_adaptive_hash_index这一参数来启停这一功能。
2.2 InnoDB数据存储结构(聚簇索引)
2.2.1 自底向上探寻索引
在索引的数据结构中，我们知道索引其实是一棵B+树，但是我们结合前面的InnoDB的页结构，来进行分析，也会得到这一索引结构。
在前面我们知道，Innodb是以一个16kb的数据页来进行磁盘IO的，每个数据页的页头有一个前向指针和后向指针，而每个数据页里面有多条记录，每个条记录按照主键从小到大排列，并且采用单向指针串成一个单向链表。这个时候其实就已经形成了B+树种的叶子节点。
在前面讲页目录的时候，为了提升查询性能，建立了页目录，目的就是为了采用二分查找查询数据。这里我们也可以采用相同的思想，可以将每页的最小的key值提取出来，将页号设置为value，将其放到一个节点上去，形成一个目录。
可以看出，这个时候变已经得形成了一个B+树。
前面讲页目录结构的时候，说过record_type表示不同的记录，record_type=0表示普通数据页记录，record_type=1表示分支节点存储索引的记录，record_type=2表示每页的最小记录，record_type=1表示每页的最大记录。
3.2.2 查询一条数据的完整流程
1.InnoDB聚簇索引根节点位置是固定的，所以先在固定位置查询到根节点；
2.在分支节点上通过主键值在分支节点内进行范围查找，找到存储数据的叶子节点的页号；
3.在页中通过页中的页目录进行二分查找得到该条数据所处的分组；
4.在分组内进行匹配，得到对应数据。
3.2.3 聚簇索引
在上面构建数据目录的结构中，主键作为key，value为数据记录内容，构建的索引就叫聚簇索引。InnoDB就采用的聚簇索引，并且它的数据也是这样存储在磁盘中的。
2.4 MyIsam数据存储结构(非聚簇索引)
MyIsam中，数据进入的时候是按照到达顺序存储在磁盘中的，和每个叶子节点存储的是主键id和数据内容的地址，所以在查询数据的时候，就算是走主键索引，也会进行回表。这就是非聚簇索引。
2.3 二级索引
上面说了InnoDB数据其实是以主键索引的形式存储的，但是如果我们想要根据某个自定义的列建立索引，该怎么办。这就需要建立二级索引。二级索引其实就是以自定义列作为key，主键id作为value。
2.4 联合索引
我们也可以根据多列建立索引，比如key(a,b)建立联合索引，首先按照a列进行排序，如果a列相当在按照b列进行排序。
2.6 索引的注意点
1.聚簇索引根节点的存储位置是固定的；
2.在建立二级索引时，为了保证二级索引，目录项的唯一性，会将id冗余到二级索引的非叶子节点上。并为了进行回表，也会将主键id冗余到叶子节点上。
3.为了保证B+树的扁平化，每个非叶子节点至少存储两条记录。
2.7 索引的代价
1.索引会浪费空间，因为需要额外的空间来存储索引结构；
2.索引会浪费时间，在我们在插入数据的时候，需要多维护一种数据结构；
综上，索引适合于查多写少的场景。
3.索引的使用
3.1 索引生效失效场景分析
3.1.1 数据准备
CREATE TABLE person_info(id INT NOT NULL auto_increment,name VARCHAR(100) NOT NULL,birthday DATE NOT NULL,phone_number CHAR(11) NOT NULL,country varchar(100) NOT NULL,PRIMARY KEY (id),KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);本文将以上述结构建立联合索引，所以这个索引的列，是按照先按照name进行排序，然后按照birthday顺序排序，最后按照phonenumber进行排序的。
3.1.2 等值匹配
1. 索引列和where条件后面列相等
where条件后面的列刚好是在索引列
select * from person_info where `name`='ello' AND birthday ='1998-07-01' and phone_number = 12321312321对于联合索引的等值匹配,这个时候会直接走索引，先匹配name匹配到过后再在b+树种查找birthday ='1998-07-01'然后再查找country = 'china'的内容。等值匹配的顺序是可以变的，不管是他们顺序是什么样的，都会被优化成索引的顺序，先匹配name，再匹配birthday，再匹配phonenumber。
2. 索引列和where条件后面列部分相等且遵循最左匹配原则
where条件后面的列，有name和birthday这两列，是按照索引的建立顺序从左能够匹配上的，这称之为最左匹配原则。
SELECT * FROM person_info WHERE `name`='ello' AND birthday ='1998-07-01' AND conutry = 'China'这个时候，name和birthday会走二级索引获取到主键id，然后通过主键索引回表后过滤出conutry = 'China'的数据。
3. 索引列和where条件后面列部分相等但不遵循最左匹配原则
这个时候，where条件后面没有根据name进行判断，但是索引建立是name相当，才根据birthday进行排序，所以需要向把name定下来，才能对birthday采用二分查找。所以这种情况不能走索引。
SELECT * FROM person_info WHERE birthday ='1998-07-01' AND conutry = 'China'3.1.3 模糊匹配
1. 匹配列前缀
SELECT * FROM person_info WHERE `name` LIKE 'zhang%'上面name排在第一列，并且对于字符串建立的索引，可以认为也是一个聚簇索引，每次比较的时候比如'zhang'和'zhba'这两个字符串，也是从左到右一个字符一个字符的进行比较，页应该满足最左匹配原则。所以上述会走索引。
2. 非列前缀模糊匹配
SELECT * FROM person_info WHERE `name` like '%zhang'上述sql不满足最左匹配原则，则不走索引。
3.1.4. 范围查询
1. 索引最左列进行范围查询
SELECT * FROM person_info WHERE `name` > 'aaa' and 'name' < 'nnn'最左列式有序的，所以直接查询出name='aaa'的数据，然后再叶子节点上通过后向指针遍历即可。所以会走索引。
2. 索引非左列进行范围查询
SELECT * FROM person_info WHERE phone_number > 1213123 and phone_number < 5555555左侧列没有固定，所以在全局来看，该列是随机的，不能走索引。
3. 索引列范围查询，并且左侧列等值查询
SELECT * FROM person_info WHERE `name`='ello' AND birthday ='1998-07-01' and phone_number > 1213123 and phone_number < 5555555name和birthday等值查询后就已经固定下来，然后再通过phone进行范围查询，可以采用二分查找，会走索引。
3.1.5 排序
1. 按照索引列顺序排序
SELECT * FROM person_info ORDER BY `name` ASC, birthday ASC索引会先按照name升序排序，在按照birthday排序，最后按照phone升序排序，刚好满足sql，所以会走索引。
2. 排序列在索引里，但未按照索引顺序
SELECT * FROM person_info ORDER BY birthday ASC左侧name未固定，所以birthday对于整棵索引树来说是乱序，不会走索引。
3. 索引列排序，左侧列等值比较
SELECT * FROM person_info WHERE `name`='ello' ORDER BY birthday ASCname字段已经被固定了，所以索引树是哪找birthday进行排序的，所以会走索引。
4. ASC和DESC混用
SELECT * FROM person_info ORDER BY `name` ASC, birthday DESC这种情况，name可以通过索引进行排序，然后将数据读取到sort buffer中，最后通过文件排序对birthday进行排序。
3.1.5 分组
索引其实天然就带有分组的功能，比如上面的联合索引可以理解为先对name进行分组，然后再对brthday进行分组，最后对phone_number进行分组。
SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number
所以针对上述sql，也会走索引。
3.2 回表与索引覆盖
3.2.1 回表
1.什么是回表
在二级索引中，如果查询的数据在二级索引中并没有存储，就需要通过二级索引得到主键id，然后通过主键id到主键索引中回表得到完整的记录。
比如
SELECT * FROM person_info WHERE `name`='ello' AND birthday ='1998-07-01' AND phone_number = 12321312321我们需要根据idx_name_birthday_phone_number 这个索引查询出满足条件的id值，然后根据主键id到主键索引中获取到完整的记录。
这就是我们为什么不建议使用select *的原因，因为这样会加大回表的概率。
2.回表的代价
1.我们从索引中获取主键id的时候，因为索引是按照顺序存储的，所以这个时候基本上可以认为是顺序IO，性能较好。
2.拿到主键id过后，由于主键id可能是乱序的，然后到主键索引中查询，这个时候是随机IO，性能较差。
3.2.2 索引覆盖
我们如果让二级索引刚好包含需要查询的列，这个时候结果就只需要从二级索引中获取，不用回表到主键索引中再次获取，就称之为索引覆盖。
3.3. 怎么建立索引
3.3.1 尽量只为搜索、排序、分组字段建立索引
尽量只为搜索、排序、分组字段建立索引。当然，如果某个sql特别重要，为了减少回表，也可以为sql的将该sql的查询字段冗余到索引中，减少回表。这些都需要结合实际而定。
3.3.2 尽量为基数较大的列建立索引
比如状态类型这种列值只有几个选择的列时，如果要建立索引，由于有大量重复值，这个索引也基本上不会生效。这个时候我们可以考虑从业务角度优化，比如查询是带上时间，建立一个时间和状态的联合索引，增大索引列的基数。
3.3.3 索引列的类型尽量足够小
索引列尽量使用tinyint，int这种类型，这样b+树的每个节点会存储更多内容，减少树的高度。
3.3.4 对于字符串，使用字符串前缀建立索引
字符串比较的时候，是按照一个字符一个字符进行比较的。如果将整个字符串都作为索引的话，首先整棵索引树会特别大，比较的时候，如果索引树比较深，或者用or连接多个字符串模糊匹配的场景，优化器可能会选择全表扫描。
SELECT * FROM person_info WHERE `name` like 'aaa%' or `name` like 'bbb%' or `name` like 'ccc%' or `name` like 'ddd%'所以，我们可以截取字符串的部分前缀建立索引，这个时候可以减少索引树的大小。但是这样不能将其用于排序。
3.3.5 不要让索引列出现在表达式中
尽量不要在where条件后面的索引列中，进行表达式运算，这样会导致索引失效。
3.3.5 主键尽量递增
主键递增，可以减少主键索引的页分裂。
3.3.6 减少冗余索引和重复索引
维护冗余索引和重复索引会降低性能。
3.4 is null和使用or会走索引吗？
3.4.1 null值在索引中的处理
Mysql默认null值是索引中的最小值。
3.4.2 扫描区间
对于is null或者or，Mysql的优化器首先会根据写的sql得出一个扫描区间：
比如is null的扫描区间就是[null,null],is not null的扫描区间(null,+∞]，而对于下面这个用sql连接的语句器扫描区间为[11111,22222] U [33333,44444] U [55555,66666]。
SELECT * FROM person_info WHERE phone_number > 11111 and phone_number < 22222 or phone_number > 33333 AND phone_number < 44444or phone_number > 55555 AND phone_number < 66666Mysql的优化器会根据索引对扫描区间进行扫描的话估算出执行代价，然后对比全表扫描的代价，判断哪种方式代价更低，然后选择索引或者全表扫描。
网上说的is null不走索引，大概率是因为表的数据量不够或者如果运行字段为空，大概率这个字段会出现大量为空的记录。所以对于null这个值，基数会很小，执行器在计算代价的时候，跟倾向于选择全表扫描。
3.总结
索引是sql优化的最主要的手段，只有掌握原理才能知道哪些场景索引会失效或者生效。对于索引失效或者生效，是由执行器根据走不同的索引或者全表扫描得到执行计划，然后估算出不同的代价比较后选择代价最小的执行计划。执行计划的选择会受很多条件影响，比如数据量，或者索引树的存储排列结构。我们只有根据索引的原理去分析是否走索引，但是因为其他因素影响，可能真正的执行结果和我们预想的不一致。这些都是要结合实际数据场景去分析的。而且优化器选择的执行计划也不一定是最优的，有时通过强制索引来使得某个索引生效，也是一种优化手段。
