B+树索引使用
- OLTP和OLAP
- 需要使用索引的情况
- 联合索引
- 1）联合索引树结构
- 2）联合索引使用分析
- 可以完整用到联合索引的情况
- 只能使用部分联合索引的情况
- 可以使用覆盖索引的情况
- 不能使用联合索引的情况
- 普通索引与唯一索引
- 1）insert buffer和change buffer
- 2）普通索引与唯一索引的区别
- 3）普通索引和唯一索引的选择
OLTP和OLAP
数据库存在两种类型的应用，OLTP和OLAP应用。
联机事务处理OLTP（on-line transaction processing） 主要是执行基本日常的事务处理，比如数据库记录的增删查改。比如在银行的一笔交易记录，就是一个典型的事务。
OLTP的特点一般有：
- 实时性要求高。银行异地汇款对实时处理能力有高要求。
- 数据量不是很大，生产库上的数据量一般不会太大，而且会及时做相应的数据处理与转移。
- 数据一般是确定的，比如银行存取款的金额肯定是确定的，所以OLTP是对确定性的数据进行存取
- 高并发，并且要求满足ACID原则。比如两人同时操作一个银行卡账户，比如大型的购物网站秒杀活动时上万的QPS请求。
**联机分析处理OLAP（On-Line Analytical Processing）**是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。典型的应用就是复杂的动态的报表系统。
OLAP的特点一般有：
- 实时性要求不是很高，比如最常见的应用就是日更新数据，然后出对应的数据报表。
- 数据量大，因为OLAP支持的是动态查询，所以用户也许要通过将很多数据的统计后才能得到想要知道的信息，例如时间序列分析等等，所以处理的数据量很大;
- OLAP系统的重点是通过数据提供决策支持，所以查询一般都是动态，自定义的。所以在OLAP中，维度的概念特别重要。一般会将用户所有关心的维度数据，存入对应数据平台。
OLTP应用中，查询操作只从数据库中取得一小部分数据。这种情况下，B+树索引的创建是有意义的；
OLAP应用中很少对少部分数据进行查询，所以对于微观信息列的索引时没有必要的，需要对宏观信息列添加索引才有意义，如OLAP应用中常常对时间字段添加索引。
需要使用索引的情况
使用索引可分为如下几种情况，
- 数据检索
在建立了索引的字段上进行检索能够有效加快检索速度，可以通过实验，分别在索引字段和非索引字段上进行检索，通过explain查看语句执行计划，在type项中，索引字段的值为"ref"，而非索引字段的值为"ALL"。同时查看row项的值，索引字段比非索引字段的值要小得多，故数据检索过程中索引能够加快检索速度。对于explain输出结果的解读参考博文。 - 使用聚合函数
一般情况下，聚合数据(总和，平均数，最大最小值等)并不总存储在表中。 但是，可以通过执行存储数据的计算来获取它。MySQL提供了许多聚合函数，包括AVG，COUNT，SUM，MIN，MAX等。
使用索引能够加速聚合函数的原因在博文的"5.7.18版本前后count(*)的区别"部分内容中有提及。聚合函数通过遍历最小可用二级索引来处理聚合函数，如果不存在二级索引，就扫描聚簇索引。这样运行的原因是辅助索引树比聚簇索引树小，优化器基于成本考虑，优先选择辅助索引。 - 排序
可回顾排序优化，具体总结如下，
1.对单个字段排序，可以在该排序字段上添加索引来优化排序
2.对多个字段排序，可以在多个字段上添加联合索引优化排序
3.如果是等值查询后在排序，可以在条件字段和排序字段上添加联合索引来优化排序 - 避免回表
查询数据过程中，如果通过辅助索引搜索数据，InnoDB引擎会先遍历辅助索引树，找到数据对应的主键索引的值，再从聚簇索引树中查找到数据。
这种查找方式有回表的过程，如果只通过遍历辅助索引就能够找到所需要的数据能够优化查询速度。次数对返回的字段添加索引，能够优化查询速度。这种索引又称为覆盖索引。 - 联合查询
见下面联合索引部分
联合索引
1）联合索引树结构
联合索引时对表上的多个列进行索引，创建方法与单个索引创建方法相同。如，
上表中，在字段a和b上创建了联合索引。联合索引创建后，B+索引树中会按照的顺序进行存放。如下面的B+树，
对于 a、b 两个字段都做为条件时，查询是可以走索引的；对于单独 a 字段查询也是可以走索引的。但是对于 b 字段单独查询就走不了索引了。
2）联合索引使用分析
可以完整用到联合索引的情况
是否完整被使用可以通过explain输出的KEY_LEN项得出结论，该项含义具体参考博文的内容。
假设表中存在联合索引KEY idx_a_b_c (a, b, c)，
- 联合索引各列字段作为条件字段时，能够使用完整的联合索引，如
select * from t where a=1 and b=1 and c=1;。
此时条件字段的顺序不影响联合索引的使用，即select * from t where c=1 and b=1 and a=1;同样可以使用联合索引 - 联合索引前面的字段使用范围查询，后面的字段作为条件时，依然可以使用完整的联合索引，如
select * from t where a=2 and b in (1,2) and c=2; - 联合索引前面字段作为条件，后面字段做排序时，可以使用完整的联合索引，如
select * from t where a=1 and b=2 order by c;和select * from t where a=1 order by b,c;同理，对联合索引同时做排序也可以使用完整联合索引，如select a,b,c from t order by a,b,c;但是此时三个字段的顺序要跟联合索引定义时的顺序相同。
只能使用部分联合索引的情况
- 当条件字段只包含联合索引的前面部分字段时，可用到部分联合索引，如
select * from t where a=1 and b=1;此时遵从联合索引的最左原则，联合索引idx_a_b_c (a, b, c)相当于（a）、（a，b）和（a，b，c）三种索引。所以语句select * from t where a=1 and c=1;只能使用到索引中的（a）。 - 联合索引前面的字段使用了范围查询，后面字段做排序（注意与使用完整索引的2和3进行区分）时，对后面的字段不能使用索引排序，如
select * from t where a=2 and b in (3,4) order by c;只能使用索引中的（a，b）
可以使用覆盖索引的情况
覆盖索引的优势在于不需要对聚簇索引进行扫描，可以减少SQL执行过程的IO次数。
返回的数据是联合索引字段的值或主键值时，可以使用到覆盖索引，如下，
select b,c from t where a=3;
select c from t where a=1 and b=1;
select id from t where a=1 and b=1 and c=1;不能使用联合索引的情况
- 只是用联合索引后面的字段时，违反了联合索引的最左原则，无法使用联合索引，如
select * from t where b=1;和select * from t order by b;无论是做条件还是做排序均无法使用联合索引，因为不存在相应的索引树。
普通索引与唯一索引
从命名可以推测普通索引字段可以写入重复的值，唯一索引的字段不能存在重复的值。实际上，MySQL中这两种索引的区别不只有这一种。
1）insert buffer和change buffer
- insert buffer
非聚簇索引在插入时，先判断插入的非聚簇索引页是否在缓冲池中。如果在，则直接插入；如果不在，则先放到insert buffer中，之后以一定频率进行insert buffer和辅助索引叶节点的merge操作。这样设计的目的是能够将多个插入合并到一个操作中，提升了数据插入的性能，减少了磁盘离散读取。
insert buffer的使用需要满足两个前提，索引是辅助索引且索引时非唯一索引 - change buffer
change buffer是对insert buffer的升级，可以对insert、delete和update都进行缓存。影响参数有两个，innodb_change_buffering：用于确定哪些场景使用change buffer，值包含none, inserts, deletes, updates, changes, purges和all。默认为all，表示启用所有。innodb_change_buffer_maax_size：控制change buffer占总buffer pool的百分比，默认25，表示最多可以占buffer pool的25%，最大值为50。
change buffer的使用前提与insert buffer相同，索引是辅助索引且索引时非唯一索引
唯一索引不使用change buffer进行更新的原因：唯一索引必须要将数据页读入内存才能判断是否违反唯一性约束。既然已经读入内存，直接更新内存会更快，没有使用change buffer的必要。
2）普通索引与唯一索引的区别
处理唯一性区别外，1）中的insert buffer和change buffer的提出可以看出另一个区别：如果对数据进行修改，普通索引可以使用change buffer，而唯一索引不行。
查询过程中，二者的区别如下，
- 普通索引查找到第一个满足条件的记录后，还需要查找下一个记录，直到不满足条件。
- 唯一索引查找到第一个满足条件的记录后直接返回结果即可。
InnoDB是按页从磁盘进行读取的，所以很大可能性是普通索引查询的数据都在一个数据页中，因此查找到第一个满足条件的记录后，再找之后的数据就是多次扫描内存的过程，这个速度很快。
对普通索引和唯一索引的区别进行如下总结，
- 普通索引可以有重复值，而唯一索引遵守唯一性约束。
- 数据修改时，普通索引可以用 Change Buffer，而唯一索引不行。
- 数据修改时，唯一索引在 RR 隔离级别下，更容易出现死锁。（事务相关笔记中说明）
- 查询数据是，普通索引查到满足条件的第一条记录还需要继续查找下一个记录，而唯一索引查找到第一个记录就可以直接返回结果了，但是普通索引多出的查找次数所消耗的资源多数情况可以忽略不计。
3）普通索引和唯一索引的选择
如果开发中要求某个字段唯一，但是代码不能保证写入的唯一性，此时添加唯一索引。一旦在唯一索引中添加重复数据，会报错
ERROR 1062 (23000): Duplicate entry '1' for key 'f1'如果代码能够保证写入的唯一性，则添加普通索引即可，普通索引能够使用change buffer，且出现死锁的概率比唯一索引低。
