写在最前
这是读书笔记，Mysql，innodb系列一共3篇。
- Mysql-innodb-B+索引（本篇）
- Mysql-innodb-锁（预计20200523）
- Mysql-innodb-事务预计20200530）
概述
下面是常见的建表语句：
CREATE TABLE `aid_***_detail` (
//省略所有字段
PRIMARY KEY (`id`),
KEY `range_idx` (`range_id`,`is_delete`,`range_detail_num`,`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4复制代码
其中的Key和PRIMARY就是 B+树索引， 即常用的索引，大概率是B+树索引
注：mysql还有全文索引和hash索引。
Innodb的2种B+树索引
聚集索引
- 以 主键构建B+树，叶子节点存放一整行数据，聚集索引的叶子节点称为数据页
- 每张表只有一个聚集索引
- 逻辑连续，页通过双向链表连接，页中数据通过双向链表维护
主键排序查找和范围查找速度快。若高度为3，需要3次IO找到数据
辅助索引
- 也称非聚集索引，不影响聚集索引， 一张表上可以有多个辅助索引
- 叶子节点，存储键值和书签。书签：聚集索引的键值
- 辅助索引找主键索引，主键索引找完整记录
辅助索引B+树高度为3，聚集索引B+树高度为3，需要6次IO
Innodb创建索引的方式
聚集索引
新建表，将数据拷贝到新表，删除老表，将新表重命名
辅助索引（FIC机制）
表上加S锁，不用重建表，标记删除
允许读，阻塞写
注：关于锁的部分见下一篇blog：Mysql-innodb-锁复制代码
Cardinality 一个参数看索引好坏
索引中唯一项的估计值 ，Cardinality/n_rows_in_table 越接近1越好。
n_rows_in_table 表中总记录数。
可以使用 show index from table 查看Cardinality的值。
Innodb对Cardinality的更新策略
- 表中1/16 数据发生了变化
- stat_modified_counter＞2 000 000 000（20亿）
- ANALYZE TABLE table_name 可以改变Cardinality，耗时慎用。
采集方式
Cardinality=（P1+P2+…+P8）*A/8
A索引中叶子节点的数量
P1..P8, Pn表示当前页不同记录的个数
联合索引-使用技巧
(a,b,c)联合索引的B+树，简图如下：
从上图可以看出：
例如a,b,c形式的排序了
1,2,1 在1,2,2前。
但是a,c这种形式没有排序。
例如：2,1,4 ，2,2,3 都是 2,*，X的形式，X位没有排序。
下面进行测试：
创建测试表：
CREATE TABLE `aid_test` (
`id` varchar(32) NOT NULL COMMENT '主键',
`a` varchar(32) NOT NULL,
`b` varchar(32) NOT NULL,
`c` int(11) NOT NULL,
`is_delete` int(11) NOT NULL COMMENT '0未删除1已删除',
`create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
`update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `range_idx` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;复制代码
开始测试
若联合索引是（a，b，c）如下情况可以使用索引
select * from t where a=XX and b=xx and c=XXX
select * from t where a=XX and b=xx
select * from t where a=XX
通过执行计划，都可以看到，都走了索引。
测试排序
explain select * from t where a =XX order by b
或
explain select * from t where a =XX and b=XX order by c
可以看到，Extra项，只使用索引条件。
但是
explain select * from t where a =XX order by c
Extra项 有Using filesort！！！。
如果待排序的内容不能由所使用的索引直接完成排序的话，那么mysql有可能就要进行文件排序
经过测试证明了，聚合索引的排序方式。
尽量利用聚合索引的排序方式，优化查询。
补充-B+数据结构
为磁盘或其他直接存取辅助设备设计的一种平衡查找树
定义
1.数据存储在叶子节点上,所有数据按照键值排序，各个叶子节点指针相互连接
2.非叶子节点存储直到M-1个关键字以指示搜素的方向；关键字i代表子树i+1中最小的关键字。
3.树的根要么无子节点，要么其儿子数在2到M之间
4.除根外，所有非树叶节点的儿子数在[M/2]和M之间
5.所有的树叶都在相同的深度上并有[L/2]和L之间个数据项
6.M,L根据磁盘区块大小确定
B+数据结构操作
插入-裂项
| 叶子节点是否已经满了 | 索引节点是否已经满了 | 操作 |
| 否 | 否 | 记录直接插入到叶子节点 |
| 是 | 否 | 拆分叶子节点 叶子节点的中间值上升到索引节点 小于中间值的，放在新的左叶 大于等于中间值的，放在右叶 |
| 是 | 是 | 拆分叶子节点 叶子节点的中间值上升到索引节点 小于中间值的，放在新的左叶 大于等于中间值的，放在右叶 拆分索引节点 索引节点的中间值上升到上层索引节点 小于中间值的，放到左边索引节点 大于中间值的，放到右边新索引节点 |
删除合项
| 叶子节点小于填充因子 | 索引节点小于填充因子 | 操作 |
| 否 | 否 | 直接删除， 如果该节点，还在索引节点中，用该节点的右节点替代 |
| 是 | 否 | 合并叶子节点，同时更新索引节点 |
| 是 | 是 | 合并叶子节点， 合并索引节点和它的兄弟节点 |
InnoDB的裂项
总是从中间进行分裂，会造成空间浪费。
Innodb的方式为：
- 随机插入时，从中间进行裂项
- 同一方向插入5条，已定位的数据后还有3个数据。从已定位后的3个记录进行裂项
Page Header中决定的参数Page Last Insert，Page DIRECTION，Page_N_DIRECTION