mysql4

最新推荐文章于 2024-08-19 22:23:29 发布

Markland_l

最新推荐文章于 2024-08-19 22:23:29 发布

阅读量92

点赞数 1

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/Markland_l/article/details/120446808

版权

mysql 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

mysql4

磁盘和B+树

磁盘物理构造

柱面
- 半径相同的磁道沿着主轴方向（z轴方向）发射，形成一个空间柱面
磁头
- 所有磁头的移动都是一起移动的
- 磁头数量与盘片数量相同
扇区
- 把磁道再切割成一段段的弧，这一段弧就是扇区
- 磁盘里最小读取单位，扇区的大小普遍是512字节，有些操作系统则规定为4KB
主轴
- 沿z轴方向把盘片串联起来
磁道
- 盘片可以划分成很多个同心圆，一个同心圆就是一个磁道
盘片

怎么定位到数据在磁盘哪个位置

读取数据时，所有的磁头都是移动不同盘片的同一磁道上面
比如，要读取第一个盘片，第五个柱面，第六个扇区的位置，于是盘片移动，磁头即可读取到相应位置的数据
磁盘上对某一个数据的唯一标识是什么？
- 盘片号+柱面号+扇区号，一个三维坐标
读取数据具体过程？整个过程9ms时间
- 1.寻道，寻找数据在哪一个柱面或者哪一个磁道上面
- 2.旋转，把盘片旋转到当前的磁头之下
  - 取决于磁盘的转速
- 3.读取，读取盘片上面的内容
  - 所花的时间是纳秒级
读取磁盘数据时间主要花在寻道和旋转之上，为了提高磁盘的读取效率，减少磁盘的io，比如要读某一个扇区的数据，可能不仅是读这一个扇区的数据，可能连着把这个扇区后面的几个扇区的数据都一起读了，这个过程就称为预读
- 预读的来源是计算机科学中的局部性原理

预读和局部性原理

当一个数据被用到时，其附近的数据也通常会马上被使用。
程序运行期间所需要的数据通常比较集中。
不光是在磁盘数据读取中应用，在cpu读取内存也是
局部性分为空间局部性和时间局部性
- 空间局部性：当一个数据被用到时，其附近的数据也通常会马上被使用。
- 时间局部性：一个数据被用到后不久，可能会被再次用到

为什么要用局部性原理？

假如读取的第一个数据在第4磁道，下一个数据又在第8磁道，此时需要选择磁头移动到第8磁道，这种情况和顺序读取第4磁道、第5磁道相比，明显是后者更快，后者称为顺序读取，前者称为随机io
顺序读效率和随机io相比，前者是后者的40_{400倍，顺序写的效率是随机写的10}100倍

SSD盘

SSD盘是没有磁头的移动和盘片的旋转的，其物理实现形式和芯片相似，内部是一块一块的01二进制位，在ssd盘下，随机io和顺序io的差距要小于硬盘
顺序读和随机读的差距在7~10倍，而且机械硬盘的顺序写要稍微优于SSD盘（写放大的问题）

预读

预读常用单位是页的整数倍，页一般是4k，但是也有16k的
- 页的拓展？
  - 当需要把数据从硬盘读取到内存时，发送了这种数据交换，就是以页为单位的，如果当读取的数据不在内存中时，就会发生缺页中断，操作系统就会向磁盘发出一个读盘信号，从磁盘读取到数据后，就会写入内存，程序继续运行
- mmap?
  - 内核空间和用户空间，当使用系统调用read()方法去读的时候，操作系统一般情况下首先把数据读取内核空间，再拷贝到用户空间，
  - 而mmap是直接把磁盘上的数据映射到用户空间去，当需要使用数据时，直接把磁盘中的数据拷贝到用户空间中去

页里存储B+树节点

一页里面放一个B+树的节点，InnoDB中一个节点是16K，也就是4页，假设一个key是8字节，对下级节点的指针引用是6个字节，则一个节点下面可以放1000多个数据元素，意味着这个B+树可以达到1000叉树，而不是很简单的二叉树
同时由于局部性原理，这一个节点，16k数据完全有可能是顺序存放的，读的时候也有可能把这16k数据顺序读出来

由磁盘物理性质、预读、B+树特性得来的mysql优化方向

尽可能用顺序读写，减少随机io
- 主键数据尽可能有序，因为B+数也是天然有序的，等下计算出来在不同的节点上，因此也就会在不同的页上，也就会在不同的磁道上，用自增主键，不要用uuid这种

InnoDB中的索引

聚集索引/聚簇索引

将表的主键用来构造一棵B+树，并且将整张表的行记录数据存放在该B+树的叶子节点中
- primary key(id） USING BTREE
innoDB如果没有指定主键，就会有一个row_id的隐藏主键id，形成一个聚簇索引

辅助索引/二级索引

想以别的列作为搜索条件怎么办？我们一般会建立多个索引，这些索引被称为辅助索引/二级索引
同样是一个B+树，但是叶子节点只会存放索引列和主键元素列，不会存放所有的数据，同时索引列的数据也是排好序的

回表

select * from t where note = ‘a’;
- note是普通索引列
- 由于是查*，需要查所有的数据，先找到对应的主键，再去聚簇索引中查找所有的元素，这个过程就称为回表
性能问题
- 如果回表次数很多，mysql可能会做全表扫描，这个过程是由查询优化器做的，查询优化器首先会根据表中的数据做一个统计，再根据统计数据去估算是回表快，还是全表扫描快

联合索引/复合索引

将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引
虽然这个索引上有多个列，但是只会建立一个B+树

排序

最左匹配原则，按照字段定义顺序依次排序，是多重排序，当第一个字段相同时，再按第二个字段排序

覆盖索引

又叫索引覆盖，只是一种索引行为，不是一种新的索引
覆盖索引(covering index，或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

好处

只存放部分列数据，占用的空间比聚簇索引少，一个页可以可以存放更多数据，io次数会比在聚簇索引中更少，同时可以减少回表次数

自适应哈希索引

InnoDB存储引擎内部监控索引热数据，然后内部创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)，控制参数innodb_adaptive_hash_index和innodb_adaptive_hash_index_parts
通过哈希表的形式存储，mysql5.7中分了8个表（最大512个），通过不同的锁控制，不同版本，哈希表的个数不同
由数据库内部控制，我们无法干扰，我们只能控制用或者不用，默认开启

全文索引

innoDB中的索引，除了B+树索引、哈希索引外，还有全文索引

全文检索下常规数据库的缺陷

保存唐宋诗词，数据库中我们们会怎么设计？根据朝代或者作者寻找诗？怎么做？
- 如果我们现在有个需求：要求找到包含“望”字的诗词怎么办？
  - select 诗词全文 from 诗词表 where诗词全文 like’%望%’？
  - 这种就是全表扫描，基本没有优化空间
- 为了解决这个问题，提出倒排索引的概念

全文检索之倒排索引Inverted index

比如现在有：
- 1.蜀道难（唐）李白蜀道之难难于上青天，侧身西望长咨嗟。
- 2.静夜思（唐）李白举头望明月，低头思故乡。
- 3.春台望（唐）李隆基暇景属三春，高台聊四望。
- 4.鹤冲天(宋) 柳永黄金榜上，偶失龙头望。明代暂遗贤，如何向？未遂风云便，争不恣狂荡。何须论得丧？才子词人，自是白衣卿相。烟花巷陌，依约丹青屏障。幸有意中人，堪寻访。且恁偎红翠，风流事，平生畅。青春都一饷。忍把浮名，换了浅斟低唱！
序号 关键字 蜀道难 静夜思 春台望 鹤冲天
1 望 yes yes yes yes
2 上 yes no no yes
- 这就建立一个倒排索引，当需要去查望字有没有的时候，就知道这几首诗都有，就把这几首诗都检索出来
- 倒排索引，事先把文档中的关键字提取处理，把关键字和文档的对应关系保存起来，然后把关键字排序。当需要查某个关键字的时候，先对关键字进行检索，再通过关键字和文档的对应关系找到对应的文档。
- 关键字在倒排索引中称为Term（词项）
由于mysql是关系型数据库，虽然支持全文索引，但是一张表只支持一个全文索引，而且不支持没有单词限定符的词语
- 单词限定符，比如英语，this is a apple，用空格就分词了，但是中文里面怎么分词？起始这种分词有专门的中文分词器，但是mysql不支持

序号	关键字	蜀道难	静夜思	春台望	鹤冲天
1	望	yes	yes	yes	yes
2	上	yes	no	no	yes

深入思考索引在查询中的使用

索引在查询中的作用到底是什么？在我们的查询中发挥着什么样的作用呢？

1、让我们的查询可以快速定位和扫描到我们需要的数据记录上，加快查询的速度
2、一个select查询语句在执行过程中一般最多能使用一个二级索引，即使在where条件中用了多个二级索引。
- where order_no = ‘sss’ and expire_time = “xxx”；即使这两个字段都建立了二级索引，但是只会选择一个，具体选择哪一个，是根据mysql的查询优化器决定的

扫描区间

SELECT * FROM order_exp WHERE id >= 3 AND id<= 99;
- 通过where条件减少扫描区间，避免全表扫描
SELECT * FROM order_exp WHERE id in(3,9) OR (id>=23 AND id<= 99); 有几个扫描区间？
- 有3个扫描区间，（3,3）、（9,9）、（23,99）
SELECT * FROM order_exp WHERE order_no <‘DD00_10S’ AND expire_time> ‘2021-03-22 18:28:28’ AND order_note > ‘7排’; 有几个扫描区间？
- ```
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `u_idx_day_status`(`insert_time`, `order_status`, `expire_time`),
INDEX `idx_order_no`(`order_no`) USING BTREE,
INDEX `idx_expire_time`(`expire_time`) USING BTREE 
```
- 只有一个扫描区间，select语句只能使用一个二级索引
  - 假设使用order_no索引，从第一条数据扫描到’DD00_10S’，其他的where条件就变成了过滤条件
  - 假设使用expire_time索引，从该条数据’2021-03-22 18:28:28’ 扫描到最后
  - 具体查询过程，就是从索引范围找出扫描区间的所有主键key值，回表到聚簇索引中，找出所有的数据，然后按照其他的where条件过滤出符合条件的数据

范围区间扫描

对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写成<>）或者LIKE操作符连接起来，就可以产生一个区间。

IN操作符

SELECT * FROM order_exp WHERE insert_time IN (xxxxx, yyyy);
- 单点区间

!=产生的扫描区间

SELECT * FROM order_exp WHERE order_no != ‘DD00_9S’
- 排除指定值之外所有的区间

LIKE操作符

只有在匹配完整的字符串或者匹配字符串前缀时才产生合适的扫描区间
搜索条件是note LIKE’ b%’
- 因为B+数天然是排好序的，如果要like 'b%'的，就是要找b开头的，而b开头天然就是相连排列的，从第一条b开头的，一直扫描到第一条不以b开头的
- (a,c)的开区间
- 所有%只能写在后面才能利用索引，写在前面无法利用索引

所有搜索条件都可以使用某个索引的情况

SELECT * FROM order_exp WHERE order_no > ‘DD00_6S’ AND order_no > ‘DD00_9S’;
SELECT * FROM order_exp WHERE order_no > ‘DD00_6S’ OR order_no > ‘DD00_9S’;

有的搜索条件无法使用索引的情况

SELECT * FROM order_exp WHERE expire_time> ‘2021-03-22 18:35:09’ AND order_note = ‘abc’;
SELECT * FROM order_exp WHERE expire_time> ‘2021-03-22 18:35:09’ OR order_note = ‘abc’;

复杂搜索条件下找出范围匹配的区间

SELECT * FROM order_exp WHERE (order_no > ‘DD00_9S’ AND expire_time = ‘2021-03-22 18:35:09’ ) OR (order_no < ‘DD00_6S’ AND order_no > ‘DD00_10S’) OR (order_no LIKE ‘%0S’ AND order_no > ‘DD00_12S’ AND (expire_time < ‘2021-03-22 18:28:28’ OR order_note = ‘abc’)) ;
- ```
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `u_idx_day_status`(`insert_time`, `order_status`, `expire_time`),
INDEX `idx_order_no`(`order_no`) USING BTREE,
INDEX `idx_expire_time`(`expire_time`) USING BTREE 
```
- 使用idx_order_no执行查询
- 使用idx_expire_time执行查询

Markland_l

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql4

mysql4磁盘和B+树磁盘物理构造柱面半径相同的磁道沿着主轴方向（z轴方向）发射，形成一个空间柱面磁头所有磁头的移动都是一起移动的磁头数量与盘片数量相同扇区把磁道再切割成一段段的弧，这一段弧就是扇区磁盘里最小读取单位，扇区的大小普遍是512字节，有些操作系统则规定为4KB主轴沿z轴方向把盘片串联起来磁道盘片可以划分成很多个同心圆，一个同心圆就是一个磁道盘片怎么定位到数据在磁盘哪个位置读取数据时，所有的磁头都是移动不同盘片的
复制链接

扫一扫

专栏目录