MySQL索引及其结构(B+树)

索引：

是为提高查询效率的数据结构；

索引分类

1. 应用字段层次

   • 单列索引=主键索引(非二级索引) + 唯一索引 + 普通索引
   • 组合(联合)索引：多个字段组合上创建的索引
   • 全文索引：类型为FULLTEXT，在varchar与text类型列上创建

2. 数据储存方式

   • 聚簇索引：Innodb中，即存了主键值，也存了行数据的主键索引；
   • 非聚簇索引：MyISAM中索引和数据文件分开存储，所有都是非聚簇索引;当然Innodb的非主键索引也都是；

3. 索引数据结构划分

   1. hash索引：MySQL不显式支持，只是作为内部优化；在Innodb存储引擎里，会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，就为之建立hash索引。又叫自适应Hash索引；
      • 其次，经常访问的二级索引数据会自动被生成到hash索引里，是对innodb的缓冲池的B+树页进行创建，因此快
      • 缺点：会占用innodb buffer pool，只适用等值查询(=， <=>，in)
      • 建议：默认开启，建议可关闭set global innodb_adaptive_hash_index=off/on(Redis、MyBatis二级缓存足矣)
   2. B+树索引：除 全文索引、hash结构索引外，都是此B+树结构；

索引不适用场景

1. 全表扫描更快：数据少
2. 数据重复且分布平均的字段(如：性别-男女)
3. 频繁修改字段
4. where条件中不用的字段(order by等)----如非必要，不创建；

索引失效情况

expain + SQL;判断是否走索引；

1. 使用!=与<>，会全表扫描ALL
2. 类型不一致–>隐式转换(可能索引失效–不能确定下面的结论)
   • 常见：int column = varchar a走索引，反过来索引失效；（转为浮点数比较-但我总结为强制转换会值失真索引失效？）
   • 优先级？：char/varchar---十进制/浮点数---时间 注意，其它情况转 **浮点数(实数)**比较；
3. 索引列上运算(单列) （函数DATE(create_time)运算符age - 1）
4. or与like %x not null 与 null(索引无法存储null值)
5. 多列索引 违法 最左匹配原则：
   • 一个联合索引(col1,col2,col3) = (col1),(col1,col2),(col1,col2,col3)三个索引
   • (a,c,b)(b,a)都走索引，server层的查询优化器会优化SQL;
   • 从左到右，出现范围比较(!=)索引便不匹配下去了
6. in 与 exist
   • in走索引，但in数据量大一点就不走了(大约四五个)，讨论这个也没意义；not in不走索引；

B-树与B+树详解

原由：

1. 可以大大减少树的高度，减少对磁盘的IO操作
2. 读取时会将B树的缓存命中率更高，加载进内存最小单位为page页(16k),相邻关键字随后很有可能用上；

为什么MySQL选B+树？

1. IO次数减少：非叶子节点不存整行数据，一个page能存更多行数据，减少IO；
2. B树范围查找麻烦，B+树因为叶子节点是双向循环链表，容易查询；
3. hash结构不支持 范围查找等

相关概念：

1. 阶数m：一个节点最多有多少个子节点（B树>=2）
2. 关键字：节点上的数值就是关键字(非键)
3. 度：节点的子节点的数量

B-树简介

性质：

1. 根结点至少有两个子女;
2. 非根节点关键字个数j满足: （m/2) - 1 <= j <= m - 1
3. 非叶节点：有K个关键字就有K+1个子节点
4. 相邻叶子节点是通过单向指针相连
5. 储存的是键值对:每个节点有含有键(索引字段值)与对应的值（MySQL中没有用，所有别说储存一行值）

插入：查找是否存在节点，不存在(已到叶子节点):插入节点，此时若叶子节点元素为m，节点分裂(取中间元素向父节点插入)

删除：删除节点元素D；f父亲节点、b兄弟节点

• D所在节点无子节点，直接删除；有子节点，向子节点借用一元素(前驱后继元素)
• 判断被借用元素节点(或删除元素节点)是否是(m/2)-2, 是则
• 判断兄弟节点不为(m/2)-1,节点借用父元素，父节点借B元素；否则，与兄弟节点合并；

B+树

性质：1.2.3

不同点：

1. 叶子节点储存单位是16k的page，每页有两指针-previous page与next page指针；即叶子节点是双向循环链表结构；
   • 根据储存特性可知：建议索引字段不能过长，会使索引树的页数过多，索引层次多，效率不佳；
2. 非叶节点，记录只包含索引的键和指向下一层节点的指针
3. 叶子节点所有关键字(包含非关键字)：其中，二级(辅助)索引储存的值都是主键(常为id)

删除：删除节点，节点元素太少，向兄弟节点借用，兄弟节点也不够，合并兄弟节点;(再查看父节点有没有删除-同理)

添加：页分裂，当父节点未满元素时(无父节点)，分成兄弟节点，父节点添加元素；

特点：

1. B+树高度一般为2-4层（减少IO次数：磁盘IO时间 = 寻道 + 磁盘旋转 + 数据传输时间）
2. 回表查询：二级索引查询到id还缺其它列数据，需要再从聚簇索引查找其它列数据叫回表查询(组合索引不一定回表)
3. 索引覆盖：命中组合索引且不需要回表查询(组合索引字段满足所需查询列)
4. 一般是三层高度：三层一般能存2千多万行数据：一节点最少一页,查看命令show variables like 'innodb_page_size'
   • 非叶子节点最少存：16*1024/(8+6) = 1170关键字(MySQL指针6字节，假设key为bigint有8Byte)；
   • 其二，假设一行数据1k(常见)，一叶子节点16行数据，即2层：1170*16=18720，三层：1170*1170*16约2千多万
5. innodb的一张表最多1070列，64个二级索引，16列组成的联合索引，默认阶数(参考：至少一半16k的page储存数据)

相关问题

为什么要自增id(升序的id)

自增id：auto_increment计数器(保存最大id)存储在内存中-MySQL8.0开始(innodb)会持久化计数器—使用时注意MySQL版本

auto-increment-increment = 1 auto-increment-offset = 3设置起始id与增量；

1. 业务主键(可能是联合主键-与char)存在的问题：
   • 一，与其它表有关联时，增加了表与表之间关联的复杂性与存储空间（自增主键最大bigint）
   • 二，业务主键可能被修改(邮箱唯一主键)，与其它表关联-需保证数据一致性，修改多表麻烦；
   • 一，非聚集索引叶子节点存储主键key，key一般比较大，使非聚集索引变得膨胀和占用较大的存储空间-page页存储数据更少-io增加；
   • 二，索引碎片问题，同3，默认情况下主键就是聚集索引，如果聚集索引是一个无规律的字段，新填充进来的数据必然会根据排序规则随机存放， 一旦随机存放，极有可能导致不断的页拆分与伴随着碎片的产生；性能差；
2. 分布式id解决方案；全局唯一、高性能、高可用、易接入、递增
   • uuid：32长度的无序字符串，影响插入存储性能与查询性能（不推荐）
   • 自增id：对于不同数据库起点不一样，增量为一致
     • 优势：实现简单
     • 劣势：扩展数据库难-水平扩展难；DB单点存在宕机风险，不适合高并发；
   • 基于redis的incr命令 set seq_id 1; incr seq_id;
     • 考虑到redis持久化-问题：AOF会对每条写命令进行持久化，会导致Redis重启恢复的数据时间过长；不使用RDB；
   • 基于DB的号段模式：主流实现方法之一 参考
     • 从DB批量的获取自增ID(号段范围)，如 (1,1000] ，具体的业务使用此号段内的id加载到内存；
   • 基于Snowflake： 1+41(时间戳)+10(工作机器)+12(序列号)=64位=bigint
     • 明显：时间戳是差值：可使用69年，工作机器最多1024台，序列号一毫秒有4096个；
     • 劣势：要处理时钟回拨+机器id分配与回收+机器id上限？ 解决
     • 优势：在业务生成-高可用、索引效率高、容量大
   • 美团leaf–百度uid-generator–滴滴（Tinyid） 暂时不考虑-后面再补充