MySQL数据结构与性能优化课堂小结
索引
1.索引是什么？
索引是帮助MySQL高效获取数据的排好序的数据结构
2.索引数据结构
- 二叉树（不适合单边增长趋势的字段）
- 红黑树（二叉平衡树）（不适合场景：数据量大，深度也大，然而需要查找叶子节点上的数据）
- Hash表（不适合进行范围查找）
- B+Tree
3.B-Tree
- 叶节点具有相同的深度，叶节点的指针为空
- 所有索引元素不重复
- 节点中的数据索引从左到右递增排列
4.B+Tree（B-Tree变种）
- 非叶子节点不存储data，只存储索引（冗余），可以放更多的索引
- 叶子节点包含所有索引字段
- 叶子节点用指针（双向）连接，提高区间访问性能
估算h=3的B+Tree的存储能力 - 假设索引存放的数据是BigInt类型，大小占8B
- 后面跟的指针大小为6B
- 每个节点大小有16KB（MySQL每个B+树节点最大存储容量：16KB （指针+数据+索引））
- 那么每个节点可以存放16*1024/(6+8)=1170个数据
- 假设B+Tree树高度为3
- 估算每个叶子节点可存放1KB的一行数据
- 那么每个叶子节点就可以存放16个1KB的一行数据数据
- 结果：1170117016=21902400，约等于2000万个的1KB的行数据量
- 因此，B+树存储大数据量的表也可以非常高效的获取数据，MySQL使用B+树作为索引的数据结构。
MyISAM与InnoDB
- MyISAM与InnoDB存储引擎是来形容表的
- MyISAM表文件：frm（表结构）、MYD（表数据）、MYI（表索引）
- InnoDB表文件：frm（表数据）、ibd（表数据+表索引）
1.MyISAM存储引擎索引实现
- MyISAM索引文件和数据文件是分离的（非聚集）
2.InnoDB存储引擎实现
- 表数据文件本身就是按B+Tree组织的一个索引结构文件
- 聚集索引：叶节点包含了完整的数据记录
3.索引最佳左前缀原理
假设联合索引是state/city/zipCode
那么state就是第一关，city是第二关，zipCode就是第三关
必须匹配了第一关，才能匹配第二关，匹配了第一关和第二关，才能匹配第三关
你不能直接到第二关的
索引的格式就是第一层是state，第二层才是city
多列索引是先按照第一列进行排序，然后在第一列排好序的基础上再对第二列排序，如果没有第一列的话，直接访问第二列，那第二列肯定是无序的，直接访问后面的列就用不到索引了
4.联合索引
联合索引的底层存储结构长什么样?
定义联合索引(员工级别，员工姓名，员工出生年月)，将联合索引按照索引顺序放入节点中，新插入节点时，先按照联合索引中的员工级别比较，如果相同会按照是员工姓名比较，如果员工级别和员工姓名都相同 最后是员工的出生年月比较。可以从图中从上到下，从左到右看，第一个B+树的节点 是通过联合索引的员工级别比较的，第二个节点是 员工级别相同，会按照员工姓名比较，第三个节点是 员工级别和员工姓名都相同，会按照员工出生年月比较。
MySQL面试题
1.为什么InnoDB必须有主键（最好自己设置主键），并且推荐使用整型的自增主键?
InnoDB表数据文件本身就是按B+Tree组织的一个索引结构文件，表在创建时，即使没有组件，mysql也会选取一个字段作为索引建立B+树索引结构数据文件，所以，最好自己创建主键，从而可以由主键建立索引。
使用整型的自增主键，原因：整型相比UUID生成的字符串类型，在进行B+树建立过程中进行索引排序，不用查对应的ASCII码，直接可以进行比较排序，排序速度快；其次，整型占用空间小，可以存放更多的索引，从而可以存放更对的数据。
- 首先，为了满足MySQL的索引数据结构B+树的特性，必须要有索引作为主键，可以有效提高查询效率，因此InnoDB必须要有主键。如果不手动指定主键，InnoDB会从插入的数据中找出不重复的一列作为主键索引，如果没找到不重复的一列，InnoDB会在后台增加一列rowId做为主键索引。
- 其次，索引的数据类型是整型，一方面整型占有的磁盘空间或内存空间相比字符串更少，另一方面整型比较比字符串比较更快速，字符串比较是先转换为ASCII码，然后再比较的。
- 最后，B+树本质是多路多叉树，如果主键索引不是自增的，那么后续插入的索引就会引起B+树的其他节点的分裂和重新平衡，影响数据插入的效率，如果是自增主键，只用在尾节点做增加就可以。
2.为什么非主键索引结构叶子节点存储的是主键值？
- 主键索引和非主键索引维护各自的B+树结构，当插入的数据的时候，由于数据只有一份，通过非主键索引获取到主键值，然后再去主键索引的B+树数据结构中找到对应的行数据，节省了内存空间；
- 如果非主键索引的叶子节点也存储一份数据，如果通过非主键索引插入数据，那么要向主键索引对应的行数据进行同步，那么会带来数据一致性问题。可以通过事务的方式解决，我们都知道使用事务后，就会对性能有所消耗。