前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Mysql-innodb-B+索引

Mysql-innodb-B+索引

原创
作者头像
温安适
修改2020-05-18 11:05:18
2.3K0
修改2020-05-18 11:05:18
举报
文章被收录于专栏:温安适的blog

写在最前

这是读书笔记,Mysql,innodb系列一共3篇。

  • Mysql-innodb-B+索引(本篇)
  • Mysql-innodb-锁(预计20200523)
  • Mysql-innodb-事务预计20200530)

概述

下面是常见的建表语句:

代码语言:javascript
复制
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+树索引

聚集索引

  1. 主键构建B+树,叶子节点存放一整行数据,聚集索引的叶子节点称为数据页
  2. 每张表只有一个聚集索引
  3. 逻辑连续,页通过双向链表连接,页中数据通过双向链表维护

主键排序查找和范围查找速度快。若高度为3,需要3次IO找到数据

辅助索引

  1. 也称非聚集索引,不影响聚集索引,一张表上可以有多个辅助索引
  2. 叶子节点,存储键值和书签。书签:聚集索引的键值
  3. 辅助索引找主键索引,主键索引找完整记录

辅助索引B+树高度为3,聚集索引B+树高度为3,需要6次IO

Innodb创建索引的方式

聚集索引

新建表,将数据拷贝到新表,删除老表,将新表重命名

辅助索引(FIC机制)

表上加S锁,不用重建表,标记删除

允许读,阻塞写

代码语言:javascript
复制
注:关于锁的部分见下一篇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+树,简图如下:

B+对应的聚合索引
B+对应的聚合索引

从上图可以看出:

例如a,b,c形式的排序了

1,2,1 在1,2,2前。

但是a,c这种形式没有排序。

例如:2,1,4 ,2,2,3 都是 2,*,X的形式,X位没有排序。

下面进行测试:

创建测试表:

代码语言:javascript
复制
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有可能就要进行文件排序

Using filesort。

经过测试证明了,聚合索引的排序方式。

尽量利用聚合索引的排序方式,优化查询。

补充-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的方式为:

  1. 随机插入时,从中间进行裂项
  2. 同一方向插入5条,已定位的数据后还有3个数据。从已定位后的3个记录进行裂项

Page Header中决定的参数Page Last Insert,Page DIRECTION,Page_N_DIRECTION

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 写在最前
  • 概述
  • Innodb的2种B+树索引
    • 聚集索引
      • 辅助索引
        • Innodb创建索引的方式
        • Cardinality 一个参数看索引好坏
        • 联合索引-使用技巧
        • 补充-B+数据结构
          • 定义
            • B+数据结构操作
              • 插入-裂项
              • 删除合项
              • InnoDB的裂项
          相关产品与服务
          云数据库 SQL Server
          腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
          领券
          问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档