【Mysql优化分析】之InnoDB索引类型及原理、字段范式/逆

Mysql优化的部分有很多，本文主要介绍Mysql执行过程、索引类型及原理、字段范式/逆范式设计、索引设计及使用。

1.Mysql语句执行过程

了解mysql优化前，需要了解sql语句的执行过程，其大致过程为：客户端请求，服务端检查缓存并处理，服务端解析解析语法，预处理，优化器确定执行计划，存储引擎执行，结果返回至客户端。
其中，Mysql的存储引擎主要有MyISAM、InnoDB、Memory；MyISAM不支持事务，主要用于经常插入和查询的表格，Memory是基于内存的，所以对表格大小有限制。大部分场景用的是InnoDB，它支持事务。

2.InnodDB索引类型及原理
索引类型可分为聚簇索引(clustered index)、辅助索引（secondary index）。

聚簇索引：一个表只有一个，通常为主键索引，主键索引不存在则取首个唯一索引，都不存在则隐式地取行记录row_id。其实就是行记录的唯一标识，聚簇索引的b+tree中节点值为完整的行记录数据。

非聚簇索引：除了聚簇索引外的所有索引，称为辅助索引/二级索引，主要区别在于非聚簇索引的b+tree中节点值为聚簇索引，所以辅助索引可能需要进行回表操作，即再查询一次聚集索引树。

InnoDB索引的数据结构是B+tree，它是由Btree改进而来的。现在主要分析一下Btree以及B+tree的数据结构。

2.1. Btree
Btree是由平衡二叉树改进而来，平衡二叉树是通过二分查找实现的有序二路树，且树的左右两边层级差小于等于1。Btree不同的地方在于它是多路的，即父节点有多个子节点。

原因：树的子节点越多层级就越低，则查找的次数就越少，查询性能得以提升。
节点结构：包含关键字(索引)、节点值(聚簇索引/行记录)、节点指针。

2.2. B+tree
B+tree是由Btree改进而来，与Btree的主要区别在于：
A. 它的非叶子节点不存在节点值，即不保存聚簇索引/行记录，而是采取冗余的方式存放在叶子节点。
B. 叶子节点还通过单向链表结构连接起来。

分析：b+tree的数据结构带来的优势是B+tree的查询性能稳定，因为节点的查询次数都一致。同时，由于叶子节点还通过链表有序存储，遍历索引也有性能优势；劣势则是：如果索引数据离根节点比较近，则Btree查找速度更快。

3.字段范式及索引设计
索引的设计对于程序的开发及维护有着非常大的影响，因此索引的设计也需要重点掌握。索引本身是一个字段列，索引设计前先考虑字段的设计，下面将依次介绍字段设计的范式/逆范式原则、索引设计原则。

3.1. 范式及逆范式
教科书里有对表结构设计要求的三大范式，分别是：
第一范式：表字段列需要保证原子性，不可分割。
第二范式：表需要保证唯一性，即需要有主键。
第三范式：除主键依赖外，字段不能依赖其他非主键字段。

But，实际开发中不能严格按照这三大范式来设计表结构，因为它不能保证数据库的性能，于是就需要根据具体业务情况做一些逆范式的设计。

常见的逆方式设计有：
A. 冗余存储：经常关联的字段进行冗余，减少关联查询，提升查询性能。
B. 拆分存储：表格垂直拆分，把大存储空间的字段、不常用的字段拆分到其他表中，以提升查询性能。
C. 重复存储：在分布式数据库情况中，为避免跨实例关联，可考虑重复存储表，方便查询。

另外需要注意，有并发和性能要求的，不要使用外键约束，尽量在应用端保证数据的一致，减少数据库的压力。

3.2. 索引设计
从索引设计的角度，索引可分为主键索引、唯一索引、复合索引、普通索引。
主键索引：一个表只能有一个，一般是int型自增ID，不要用字符型，效率低。
唯一索引：除了是索引，还增加了唯一性的约束，可用于去重插入。
复合索引：索引字段由多个组成，使用索引时需要满足最左前缀原则。
普通索引：没啥好说。

其中，复合索引的最左前缀原则可以展开分析。
3.2.1.定义：在复合索引中，查找会按照最左前缀原则进行命中索引。
举栗子：
建立复合索引idx_a_b_c(a,b,c)，相当于建立了三个索引，idx1(a),indx2(a,b),indx(a,b,c)；
如果按照a、c顺序进行查找，则只会命中a字段，而不会命中c字段。

3.2.2.作用：通过上述栗子可以看到，复合索引可以减少索引的建立，避免多个单独索引树的建立，从而提高数据库的操作性能。

3.2.3.原理：根据前面索引的B+tree数据结构，可知索引的节点会存储关键字(索引)且根据关键字进行有序连接。这个有序是单个索引时好理解，当它是复合索引时，实际上是先从左边字段开始排序，其余字段在左边的基础上再进行排序，因此存在最左前缀原则。
举栗子：
复合索引(a,b,c)某个节点的子节点排序分别为(1,3,5)、(1,4,1)、(2,1,3)。可以看到，如果筛选字段是b（结果是3、4、1），则不能保证该索引字段是有序的，也就无法命中索引。

3.2.4.索引设计原则

A. 必须建立主键索引，尽量建立唯一索引（约束，区分度高）。
B. 区分度高的字段添加索引。
C. 尽量使用复合索引，设计时需要把最常用于筛选、区分度最高的放在前面。

3.3 索引使用/命中
索引建立好了也要合理使用，才能发挥它的作用。索引使用原则：

A.复合索引必须满足最左前缀原则
B.分组group by和排序order by尽量利用索引的有序性。
C.查询时尽量select索引字段，避免回表操作。
D.索引字段不要进行函数操作，否则索引会失效。
E.筛选索引字段时，不要进行类型转化，保持字段类型一致，否则索引会失效。
F.join连表查询的关联字段必须加索引，且保持字段类型一致，否则索引会失效。
G.模糊搜索时，尽量不要使用%xx，而使用xx%，否则索引会失效，有需求可考虑使用搜索引擎实现，或者主键回表查。
H.尽量不要使用否定操作，索引会失效。