Mysql InnoDB笔记(3)——索引

最新推荐文章于 2023-02-01 08:00:00 发布

rice_88

最新推荐文章于 2023-02-01 08:00:00 发布

阅读量201

点赞数

分类专栏： Mysql 文章标签： mysql innodb 索引

本文链接：https://blog.csdn.net/liu_dou/article/details/90419890

版权

6 篇文章 0 订阅

订阅专栏

本文笔记摘自于《Mysql 技术内幕 InnoDB存储引擎》

B+树

先理解B+树的数据结构和算法https://www.jianshu.com/p/7411f7ff0978

B+树索引

聚集索引（clustered index）
聚集索引是使用表主键构建的一棵B+树，叶子结点就是数据页，存放用户数据。数据页之间通过双向链表相互连接。聚集索引并不是物理上连续，而是逻辑上连续。

按索引查找用户数据时，只能查到某一条记录在某一个页里，具体在页里的什么位置是不知道的。InnoDB会把整个页加载到内存里，然后使用二分法查找到具体的行记录。

通过辅助索引查询数据时，先在辅助索引遍历得到主键的值，然后再到聚集索引遍历得到目标数据所在的页，再在页内二分查找得到行记录。

了解一下B+树索引的分裂

关于B+树索引操作的一些技术

FIC（Fast Index Creation）
Mysql5.5版本之前，

对于聚集索引的添加和删除需要经历以下几个步骤：
创建一张临时表，表结构为通过命令ALTER TABLE新定义的结构；
把原表数据导到临时表；
删除原表；
把临时表重命名为原表。
这个过程，数据库服务是不可用的。

对于辅助索引，则会使用FIC的方式：
只对原表加S锁，不重建表，不影响其他线程读取数据，只会影响DML操作。因此，相对来说，性能有所提升。
OSC（Online Schema Change）
由Facebook开发的一种在线执行DDL的方式，过程比较复杂。简单来说，就是新建一张表，在新表执行DDL操作，将原表数据转移到新表，同时记录在转移过程中对原表的DML操作，转移完成后，在新表执行原表的DML操作，最后交换表名。
Online DDL
Mysql5.6版本开始支持Online DDL。执行过程就是：在创建或删除索引的时候，将对原表的DML操作日志写到缓存中，等索引操作完成后，将DML重做到原表上。

innodb_online_alter_log_max_size配置缓存的大小

了解一下SHOW INDEX FROM table_name;的Cardinality值

关于B+树索引的一些应用

对于联合索引idx_ab(a,b)
select * from tb_test where a=1;和 select * from tb_test where a=1 and b=2;都是走索引的，而select * from tb_test where b=2;却不走

对于联合索引idx_ab(a,b)，a相同的情况下，b是已经排好序的

覆盖索引
Mysql5.0及以下版本不支持。覆盖索引就是在辅助索引就可以直接查到所需要的列，而不需要去聚集索引再查一次。因为辅助索引叶子结点保存的就是所有的索引列。

比如tb_test有idx_a(a),idx_b(b),select count(*) from tb_test where a=1;和select a,b from tb_test where a=1;都是直接从辅助索引里拿到数据

索引提示
使用FORCE INDEX强制使用索引，如：select * from tb_test FORCE INDEX(a) where a=1;
MRR（Multi-Range Read）优化
Mysql5.6版本开始支持，MRR优化的目的是：减少磁盘的随机访问，并将随机访问转化为较为顺序的数据访问。可适用于range,ref,eq_ref类型的查询。
主要思路就是：从辅助索引查询到叶子结点的键值时，将它们放到缓存里，按RowID排序，然后再按RowID顺序读取数据。
或者，对于联合索引，拆分键值对，同时对键和值判断，减少拉取的数据。如idx_ab(a,b),select * from tb_test where a=1 and b=2;a和b的条件一起过滤，而不是先取出a=1的数据，再过滤b=2的数据。

通过参数optimizer_switch='mrr=on,mrr_cost_based=off'启用MRR

ICP（Index Condition Pushdown）优化
Mysql5.6版本开始支持，原理：进行索引查询时，先根据索引查找记录，再根据where后面的条件，过滤掉部分无效的数据，减少对上层sql的fetch。可适用于range,ref,eq_ref,ref_or_null类型的查询。
AHI（自适应哈希索引）