网站后台 模板,wordpress语音插件,网站跳出率太高,wordpress 域名解析前言
本文主要调试一下 mysql 的如下两种查询语句
我们也来深入的看一下, 究竟如下两个普通的查询, mysql 做了什么事情
1. select * from user where id 991;
2. select * from user;
3. select * from user where name jerry991; 环境介绍
测试表 user schema 如下…前言
本文主要调试一下 mysql 的如下两种查询语句
我们也来深入的看一下, 究竟如下两个普通的查询, mysql 做了什么事情
1. select * from user where id 991;
2. select * from user;
3. select * from user where name jerry991; 环境介绍
测试表 user schema 如下
CREATE TABLE user (id int(11) NOT NULL AUTO_INCREMENT,age int(11) DEFAULT 0,name varchar(64) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT3 DEFAULT CHARSETutf8;user 表中的测试数据如下 select * from user where id 991
我们这里会着重介绍这个 case, 这里面会详细的看一下 index page, data page 的情况, 以及其具体的执行的流程
另外这个过程中我们会忽略掉一些细节, 尽量只查看这个业务查询的核心业务流程 获取 index page
index - page 为 3, 表示第三个 page 为索引页面, pageNo 初始化为 index-page, 该 page 的地址为 block-frame : 0x1217f4000 我们 inspect 一下 0x1217f4000
可以看出 offset 为 3, 对应于上面的 pageNo
pagePrev, pageNext 为 0xffffffff, 表示只有一个索引页面
pageNDirSlots 表示当前 page 存在两个 pageDirectory
pageHeapTop 表示当前页面的 FreeSpace 位于 0xa2
pageNHeap 表示有 5 个元素, 三个索引元素 infimum supremum
pageNRecords 表示有三个索引元素
pageLevel 为 1 表示下面一级才是叶子节点[存储数据的节点]
再看下面的索引信息 [1, 302) - page4, [302, 719) - page5, [719, supremum] - page6
(lldb) x 0x1217f4000 -c 0x100
0x1217f4000: c2 b7 03 ad 00 00 00 03 ff ff ff ff ff ff ff ff ·.....
0x1217f4010: 00 00 00 00 00 1d 34 e5 45 bf 00 00 00 00 00 00 ......4E......
0x1217f4020: 00 00 00 00 00 06 00 02 00 a2 80 05 00 00 00 00 ...............
0x1217f4030: 00 9a 00 02 00 02 00 03 00 00 00 00 00 00 00 00 ................
0x1217f4040: 00 01 00 00 00 00 00 00 00 16 00 00 00 06 00 00 ................
0x1217f4050: 00 02 00 f2 00 00 00 06 00 00 00 02 00 32 01 00 ............2..
0x1217f4060: 02 00 1b 69 6e 66 69 6d 75 6d 00 04 00 0b 00 00 ...infimum......
0x1217f4070: 73 75 70 72 65 6d 75 6d 00 10 00 11 00 0e 80 00 supremum........
0x1217f4080: 00 01 00 00 00 04 00 00 00 19 00 0e 80 00 01 2e ................
0x1217f4090: 00 00 00 05 00 00 00 21 ff d6 80 00 02 cf 00 00 .......!.....
0x1217f40a0: 00 06 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x1217f40b0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x1217f40c0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x1217f40d0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x1217f40e0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x1217f40f0: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................# FileHeader
FIL_PAGE_SPACE c2 b7 03 ad
FIL_PAGE_OFFSET 00 00 00 03
FIL_PAGE_PREV ff ff ff ff
FIL_PAGE_NEXT ff ff ff ff
FIL_PAGE_LSN 00 00 00 00 00 1d 34 e5
FIL_PAGE_TYPE 45 bf
FIL_PAGE_FILE_FLUSH_LSN 00 00 00 00 00 00 00 00
FIL_PAGE_ARCH_LOG_NO 00 00 00 06 # PageHeader
PAGE_N_DIR_SLOTS 00 02
PAGE_HEAP_TOP 00 a2
PAGE_N_HEAP 80 05
PAGE_FREE 00 00
PAGE_GARBAGE 00 00
PAGE_LAST_INSERT 00 9a
PAGE_DIRECTION 00 02
PAGE_N_DIRECTION 00 02
PAGE_N_RECS 00 03
PAGE_MAX_TRX_ID 00 00 00 00 00 00 00 00
PAGE_LEVEL 00 01
PAGE_INDEX_ID 00 00 00 00 00 00 00 16
PAGE_BTR_SEG_LEAF 00 00 00 06 00 00 00 02 00 f2
PAGE_BTR_SEG_TOP 00 00 00 06 00 00 00 02 00 32 # page4
0x 00 nulls
0x 1 delete flag
0x 0 number of records owned by the record
0b 0000 0000 0001 0 2 order number of this record
0b 001 node pointer (inside B-tree)
0x 00 0e next record offset - page05
minId 0x 80 00 00 01 1
pageNo 0x 00 00 00 04# page5
0x 00 nulls
0x 0 delete flag
0x 0 number of records owned by the record
0b 0000 0000 0001 1 3 order number of this record
0b 001 node pointer (inside B-tree)
0x 00 0e next record offset - page06
minId 0x 80 00 01 2e 302
pageNo 0x 00 00 00 05# page6
0x 00 nulls
0x 0 delete flag
0x 0 number of records owned by the record
0b 0000 0000 0010 0 4 order number of this record
0b 001 node pointer (inside B-tree)
0x ff d6 next record offset - supremum
minId 0x 80 00 02 cf 719
pageNo 0x 00 00 00 06根据 index page 确定数据所在 page
通过上面 index page 里面的索引信息, 以及我们这里查询的 991 限定来确定数据属于哪一个 page
这里的查询会有两层搜索, 通过 pageDirectory 的搜索, 通过最近的 pageDirectory 线性查询目标索引, 这里 991 属于 page6
看这里的 low_rec, 指向的记录就是上面的 [719, supremum] - page6 获取数据 page
pageNo 为 6, 获取 page6, 该 page 的地址为 block-frame : 0x121804000 我们来 inspect 一下 0x121804000
(lldb) x 0x121804000 -c 0x100
0x121804000: e5 86 89 21 00 00 00 06 00 00 00 05 ff ff ff ff ..!........
0x121804010: 00 00 00 00 00 1d a6 f8 45 bf 00 00 00 00 00 00 ......E......
0x121804020: 00 00 00 00 00 06 00 47 27 fc 81 1b 00 00 00 00 .......G......
0x121804030: 27 df 00 02 01 18 01 19 00 00 00 00 00 00 00 00 ..............
0x121804040: 00 00 00 00 00 00 00 00 00 16 00 00 00 00 00 00 ................
0x121804050: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 00 ................
0x121804060: 02 00 1c 69 6e 66 69 6d 75 6d 00 06 00 0b 00 00 ...infimum......
0x121804070: 73 75 70 72 65 6d 75 6d 08 00 00 00 10 00 24 80 supremum......$.
0x121804080: 00 02 cf 00 00 00 00 41 75 f1 00 00 01 e7 01 10 ......Au.....
0x121804090: 80 00 00 16 6a 65 72 72 79 37 31 39 08 00 00 00 ....jerry719....
0x1218040a0: 18 00 24 80 00 02 d0 00 00 00 00 41 76 f2 00 00 ..$.......Av..
0x1218040b0: 01 e8 01 10 80 00 00 16 6a 65 72 72 79 37 32 30 .......jerry720
0x1218040c0: 08 00 00 00 20 00 24 80 00 02 d1 00 00 00 00 41 .... .$.......A
0x1218040d0: 77 f3 00 00 01 e9 01 10 80 00 00 16 6a 65 72 72 w.........jerr
0x1218040e0: 79 37 32 31 08 00 04 00 28 00 24 80 00 02 d2 00 y721....(.$....
0x1218040f0: 00 00 00 41 78 f4 00 00 01 ea 01 10 80 00 00 16 ...Ax.........# FileHeader
FIL_PAGE_SPACE e5 86 89 21
FIL_PAGE_OFFSET 00 00 00 06
FIL_PAGE_PREV 00 00 00 05
FIL_PAGE_NEXT ff ff ff ff
FIL_PAGE_LSN 00 00 00 00 00 1d a6 f8
FIL_PAGE_TYPE 45 bf
FIL_PAGE_FILE_FLUSH_LSN 00 00 00 00 00 00 00 00
FIL_PAGE_ARCH_LOG_NO 00 00 00 06 # PageHeader
PAGE_N_DIR_SLOTS 00 47
PAGE_HEAP_TOP 27 fc
PAGE_N_HEAP 81 1b
PAGE_FREE 00 00
PAGE_GARBAGE 00 00
PAGE_LAST_INSERT 27 df
PAGE_DIRECTION 00 02
PAGE_N_DIRECTION 01 18
PAGE_N_RECS 01 19
PAGE_MAX_TRX_ID 00 00 00 00 00 00 00 00
PAGE_LEVEL 00 00
PAGE_INDEX_ID 00 00 00 00 00 00 00 16
PAGE_BTR_SEG_LEAF 00 00 00 00 00 00 00 00 00 00
PAGE_BTR_SEG_TOP 00 00 00 00 00 00 00 00 00 00 # UserRecords
jerry719 - jerry720 - jerry721 ... - jerry999我们来 inspect 一下 0x121807f00
从上面元数据可以知道, pageDirectory 总共有 71 个, 合计 142 字节, 加上末尾的 8 字节 FileTailer 合计 150 字节
0x121807fff - 0x121807f6a 1 0x96 150 字节
可以看到这里的 pageDirectory 也是逆序排列的 infimum - 0xeb - 0x017b - ... - supremum [根据 id 排序]
(lldb) x 0x121807f00 -c 0x100
0x121807f00: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x121807f10: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x121807f20: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x121807f30: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x121807f40: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x121807f50: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................
0x121807f60: 00 00 00 00 00 00 00 00 00 00 00 70 27 2b 26 9b ...........p.
0x121807f70: 26 0b 25 7b 24 eb 24 5b 23 cb 23 3b 22 ab 22 1b .%{$$[##;.
0x121807f80: 21 8b 20 fb 20 6b 1f db 1f 4b 1e bb 1e 2b 1d 9b !. k..K....
0x121807f90: 1d 0b 1c 7b 1b eb 1b 5b 1a cb 1a 3b 19 ab 19 1b ...{..[..;...
0x121807fa0: 18 8b 17 fb 17 6b 16 db 16 4b 15 bb 15 2b 14 9b ....k..K....
0x121807fb0: 14 0b 13 7b 12 eb 12 5b 11 cb 11 3b 10 ab 10 1b ...{..[..;...
0x121807fc0: 0f 8b 0e fb 0e 6b 0d db 0d 4b 0c bb 0c 2b 0b 9b ....k..K....
0x121807fd0: 0b 0b 0a 7b 09 eb 09 5b 08 cb 08 3b 07 ab 07 1b ...{..[..;...
0x121807fe0: 06 8b 05 fb 05 6b 04 db 04 4b 03 bb 03 2b 02 9b ....k..K....
0x121807ff0: 02 0b 01 7b 00 eb 00 63 14 23 33 ed 00 1d a6 f8 ...{..c.#3.. 根据数据page 定位 id 991
这里的查询会有两层搜索, 通过 pageDirectory 的搜索, 通过最近的 pageDirectory 线性查询目标索引, 这里 991 对应于 up_rec
看这里的 up_rec, 指向的记录就是上面的 (991, 22, jerry991) 我们来 inspect 一下 up_rec, 正是我们这里期望查询的 id 991 对应的记录
(lldb) x 0x1218066bf
0x1218066bf: 80 00 03 df 00 00 00 00 42 85 81 00 00 01 32 01 .......B.....2.
0x1218066cf: 10 80 00 00 16 6a 65 72 72 79 39 39 31 08 00 00 .....jerry991...外部的业务函数 row_search_for_mysql
上面这里查询到了 id 991 对应的记录, 封装在了 pcur-btr_cur-page_cur-rec 里面
上面的流程是在 row_search_for_mysql 的 btr_pcur_open_with_no_init 这里, 这里的作用就是初始化 pcur, 为后面的业务迭代做准备
接下来走的便是根据 pcur-btr_cur-page_cur-rec 进行迭代, 比较是否符合查询条件, 输出记录信息到客户端等等流程 所以这里会做的事情是, 当前 rec 是在 id 991, 然后 比较是否符合查询条件, 这里是符合, 然后将记录输出到客户端
然后会进入 prebuilt-n_fetch_cached MYSQL_FETCH_CACHE_SIZE 的条件, 继续下一个元素的迭代
迭代到 rec 是在 id 992, 然后 比较是否符合查询条件, 这里是不匹配, 走的时候 normal_return, 退出查询 我们来 inspect 一下 0x1218066bf
(lldb) x 0x1218066bf
0x1218066bf: 80 00 03 df 00 00 00 00 42 85 81 00 00 01 32 01 .......B.....2.
0x1218066cf: 10 80 00 00 16 6a 65 72 72 79 39 39 31 08 00 00 .....jerry991... 迭代元素 id 992 我们来 inspect 一下 0x1218066e3
(lldb) x 0x1218066e3
0x1218066e3: 80 00 03 e0 00 00 00 00 42 86 82 00 00 01 35 01 .......B.....5.
0x1218066f3: 10 80 00 00 16 6a 65 72 72 79 39 39 32 08 00 00 .....jerry992...explain 的结果
我们来看一下 explain 的结果, 会使用 primary key 来索引, 大约会扫描 1 行记录 select * from user
初始化 pcur-btr_cur-page_cur-rec
这个查询相对来说 走的流程比较简单, 查询的最小的 数据page的 infimum, 然后不断往后迭代, 直到所有元素迭代完成
这里是初始化 pcur-btr_cur-page_cur-rec 为最小的 数据page的 infimum
这里的迭代是从 index page 开始的, 从 level 最高的 index page 开始向下迭代, 如果是 fromLeft 表示最终取最小的数据页的 infimum, 如果是 !fromLeft 表示最终取最大的数据页的 supremum 根据 pcur-btr_cur-page_cur-rec 进行迭代
然后接下来就是不断地向后迭代, 输出查询的元素信息到客户端 当前 page 到达了末尾, 切换到下一个 page
btr_pcur_is_after_last_in_tree 判断当前 page 是否是最后一个 page, 判断的方式是 pageNext 是否为 0xffffffff explain 的结果
我们来看一下 explain 的结果, 全表扫描, 大约会扫描 906 行记录 select * from user where name jerry991
初始化 pcur-btr_cur-page_cur-rec
这里的初始化过程 和上面的 select * from user 的初始化是一样的, 初始化 pcur-btr_cur-page_cur-rec 为 page4-infimum
然后不断向后迭代数据, 直到全表的数据迭代完成 我们来 inspect 一下 这一页的数据
(lldb) x 0x11cf1c000 -c 0x100
0x11cf1c000: 94 8c b5 2e 00 00 00 04 ff ff ff ff 00 00 00 05 ...........
0x11cf1c010: 00 00 00 00 00 1d 34 e5 45 bf 00 00 00 00 00 00 ......4E......
0x11cf1c020: 00 00 00 00 00 06 00 36 3b 0d 81 a3 1d b0 1d 64 .......6;....d
0x11cf1c030: 00 00 00 05 00 00 00 d0 00 00 00 00 00 00 00 00 ...............
0x11cf1c040: 00 00 00 00 00 00 00 00 00 16 00 00 00 00 00 00 ................
0x11cf1c050: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 00 ................
0x11cf1c060: 02 00 1c 69 6e 66 69 6d 75 6d 00 01 00 0b 00 00 ...infimum......
0x11cf1c070: 73 75 70 72 65 6d 75 6d 05 00 00 00 10 00 21 80 supremum......!.
0x11cf1c080: 00 00 01 00 00 00 00 2b 07 04 00 00 01 56 04 7b ............V.{
0x11cf1c090: 80 00 00 1c 6a 65 72 72 79 04 00 00 00 18 00 20 ....jerry......
0x11cf1c0a0: 80 00 00 02 00 00 00 00 35 04 83 00 00 01 36 01 ........5.....6.
0x11cf1c0b0: 10 80 00 00 16 6c 75 63 79 06 00 00 00 20 00 22 .....lucy.... .
0x11cf1c0c0: 80 00 00 05 00 00 00 00 3d 14 8a 00 00 01 91 01 ...............
0x11cf1c0d0: 10 80 00 00 16 6a 65 72 72 79 35 06 00 04 00 28 .....jerry5....(
0x11cf1c0e0: 00 22 80 00 00 07 00 00 00 00 3d 0b 84 00 00 01 ..............
0x11cf1c0f0: 37 01 10 80 00 00 16 6a 65 72 72 79 37 06 00 00 7......jerry7...根据 pcur-btr_cur-page_cur-rec 进行迭代
然后接下来就是不断地向后迭代, 输出查询的元素信息到客户端
todo, 这里根据条件过滤, 以及数据传递, 还需要再找找 完
