一:背景
1.讲故事
最近给一位朋友做 SQL 慢语句 优化,花了些时间调优,遗憾的是 SQLSERVER 非源码公开,玩起来不是那么顺利,不过从这次经历中我觉得明年的一个重大任务就是好好研究一下它,争取在 SQLSERVER 性能优化上做一些成绩,哈哈! 个人觉得要想深入研究 SQLSERVER,得从它的存储引擎说起,说到存储引擎又得从核心的 数据页 说起,毕竟 mdf 就是由 数据页 拼出来的,当然理解的不对大家可以指出来。
二:理解数据页
1. 什么是数据页
一般来说,对大块资源或者数据进行高效管理都会按照一定粒度来划分的,比如说 Windows 对内存的管理就是按照 内存页 (4k) 来进行划分,言外之意就是 SQLSERVER 对 mdf 的管理也是按照 数据页 (8k) 来划分的,画个图大概就是这样的。
那如何来验证这个结论呢?刚才也说了数据都在数据页上,我们弄点数据然后在指定的数据页上提取出来就好了,这里用的是 SQLServer 2019 。- CREATE DATABASE MyTestDB
- GO
- USE MyTestDB;
- GO
- IF OBJECT_ID('person') IS NOT NULL
- DROP TABLE person;
- CREATE TABLE person
- (
- id INT IDENTITY,
- name CHAR(5)
- );
- GO
- INSERT INTO dbo.person( name ) VALUES ('john');
- INSERT INTO dbo.person( name ) VALUES ('mary');
复制代码 2. 寻找数据所在的数据页
刚才图中也说了 mdf 是由无数个 数据页 拼出来的,那如何找到 person 表所在的数据页呢?其实微软提供了一个 dbcc ind 命令可以帮我们洞察出来,同时记得开始 3604 标记,让输出显示在控制台上,而不是默认的错误日志中,这个命令具体怎么用,大家可以查看官方文档。- DBCC TRACEON(3604)
- DBCC IND(MyTestDB,person, -1)
复制代码
从输出看有两条记录,第一个是 PagePID=41 是 IAM 数据页,而PagePID=280 就是我们 person 表记录所在的数据页编号,也就是说 person 表的记录在 mdf 文件偏移为 0n280 * 0n8192 的位置,用 WinDbg 算一下就是 0x00230000 。- 0:090> ? 0n280 * 0n8192
- Evaluate expression: 2293760 = 00000000`00230000
复制代码 那是不是呢?可以用 WinHex 验证一下,为了不出现进程占用,先把 MyTestDB 下线了,最后记得再上线即可。- ALTER DATABASE MyTestDB SET OFFLINE
- ALTER DATABASE MyTestDB SET ONLINE
复制代码
从 WinHex 上看,果然是在偏移为 0x00230000 这个数据页上。
3. 如何从内存中看到数据页
刚才我们看到的数据页只是物理硬盘上的,但数据页和数据页之间的逻辑关系肯定是在内存中用一定的数据结构来承载的,接下来看下这个 数据页 映射到 SQLSERVER 进程内存的哪里呢?微软提供了 DBCC PAGE 命令可以查看指定 数据页 的详细信息。- DBCC TRACEON(3604)
- DBCC PAGE(MyTestDB,1,280,2)
复制代码 输出结果如下:- DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
- PAGE: (1:280)
- BUFFER:
- BUF @0x000002B41104F480
- bpage = 0x000002B3F0632000 bPmmpage = 0x0000000000000000 bsort_r_nextbP = 0x000002B41104F3D0
- bsort_r_prevbP = 0x0000000000000000 bhash = 0x0000000000000000 bpageno = (1:280)
- bpart = 1 ckptGen = 0x0000000000000000 bDirtyRefCount = 0
- bstat = 0x9 breferences = 0 berrcode = 0
- bUse1 = 12454 bstat2 = 0x0 blog = 0x15ab215a
- bsampleCount = 0 bIoCount = 0 resPoolId = 0
- bcputicks = 0 bReadMicroSec = 182 bDirtyContext = 0x0000000000000000
- bDbPageBroker = 0x0000000000000000 bdbid = 10 bpru = 0x000002B3FA708040
- PAGE HEADER:
- Page @0x000002B3F0632000
- m_pageId = (1:280) m_headerVersion = 1 m_type = 1
- m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8200
- m_objId (AllocUnitId.idObj) = 179 m_indexId (AllocUnitId.idInd) = 256
- Metadata: AllocUnitId = 72057594049658880
- Metadata: PartitionId = 72057594043170816 Metadata: IndexId = 0
- Metadata: ObjectId = 581577110 m_prevPage = (0:0) m_nextPage = (0:0)
- pminlen = 13 m_slotCnt = 2 m_freeCnt = 8060
- m_freeData = 128 m_reservedCnt = 0 m_lsn = (37:584:3)
- m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
- m_tornBits = -116446693 DB Frag ID = 1
- Allocation Status
- GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x41 ALLOCATED 50_PCT_FULL
- DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED
- DATA:
- Memory Dump @0x000000F840DF8000
- 000000F840DF8000: 01010000 00820001 00000000 00000d00 00000000 ....................
- 000000F840DF8014: 00000200 b3000000 7c1f8000 18010000 01000000 ........|...........
- 000000F840DF8028: 25000000 48020000 03000000 00000000 00000000 %...H...............
- 000000F840DF803C: 1b2a0ff9 00000000 00000000 00000000 00000000 .*..................
- 000000F840DF8050: 00000000 00000000 00000000 00000000 10000d00 ....................
- 000000F840DF8064: 01000000 6a6f686e 20020000 10000d00 02000000 ....john ...........
- 000000F840DF8078: 6d617279 20020000 00002121 21212121 21212121 mary .....!!!!!!!!!!
- 000000F840DF808C: 21212121 21212121 21212121 21212121 21212121 !!!!!!!!!!!!!!!!!!!!
- 000000F840DF80A0: 21212121 21212121 21212121 21212121 21212121 !!!!!!!!!!!!!!!!!!!!
- ...
- 000000F840DF9FF4: 21212121 21212121 70006000 !!!!!!!!p.`.
- OFFSET TABLE:
- Row - Offset
- 1 (0x1) - 112 (0x70)
- 0 (0x0) - 96 (0x60)
- DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。
- Completion time: 2022-12-30T17:48:20.5466040+08:00
复制代码 从上面的 Memory Dump 区节中可以看到,数据在进程内存的 000000F840DF8064 ~ 000000F840DF8078 范围内,这里要吐槽的是内存地址按照 大端布局 的,看起来很不习惯,可以用 windbg 用 小端布局 来显示。- 0:116> dp 000000F840DF8064
- 000000f8`40df8064 6e686f6a`00000001 000d0010`00000220
- 000000f8`40df8074 7972616d`00000002 21210000`00000220
- 000000f8`40df8084 21212121`21212121 21212121`21212121
- 000000f8`40df8094 21212121`21212121 21212121`21212121
- 000000f8`40df80a4 21212121`21212121 21212121`21212121
- 000000f8`40df80b4 21212121`21212121 21212121`21212121
- 000000f8`40df80c4 21212121`21212121 21212121`21212121
- 000000f8`40df80d4 21212121`21212121 21212121`21212121
复制代码 4. sql 请求源码研究
喜欢玩 windbg 的朋友肯定想对 sqlserver 进行汇编级洞察,比如研究下 sql 请求在 sqlserver 里面的执行流是什么样的? 其实很简单,我们可以这样处理,使用 ba 对 john 的内存地址下一个硬件断点,即 ba r4 000000f840df8064+0x4 ,然后在 SSMS 上执行一条 SELECT * FROM person 语句,因为要提取 john 自然就会命中。- 0:104> ba r4 000000f840df8064+0x4
- 0:104> g
- Breakpoint 0 hit
- sqlmin!BTreeMgr::GetHPageIdWithKey+0x4a:
- 00007ff8`d4ea121a 48894c2478 mov qword ptr [rsp+78h],rcx ss:000000f8`45278028=0000024800000025
- 0:102> k
- # Child-SP RetAddr Call Site
- 00 000000f8`45277fb0 00007ff8`d4ea0b59 sqlmin!BTreeMgr::GetHPageIdWithKey+0x4a
- 01 000000f8`45278450 00007ff8`d4ea08b7 sqlmin!IndexPageManager::GetPageWithKey+0x119
- 02 000000f8`45278d20 00007ff8`d4eb22d1 sqlmin!GetRowForKeyValue+0x203
- 03 000000f8`45279880 00007ff8`d4eb2a39 sqlmin!IndexDataSetSession::GetRowByKeyValue+0x141
- 04 000000f8`45279a70 00007ff8`d4eb279b sqlmin!IndexDataSetSession::FetchRowByKeyValueInternal+0x230
- 05 000000f8`45279ed0 00007ff8`d4eb2883 sqlmin!RowsetNewSS::FetchRowByKeyValueInternal+0x437
- 06 000000f8`4527a000 00007ff8`d4eaadab sqlmin!RowsetNewSS::FetchRowByKeyValue+0x96
- 07 000000f8`4527a050 00007ff8`d4f93d60 sqlmin!CMEDScan::StartSearch+0x4f8
- 08 000000f8`4527a170 00007ff8`d4f93f3a sqlmin!CMEDCatYukonObject::GetTemporalCurrentTableId+0x10e
- 09 000000f8`4527a380 00007ff8`d801f0d1 sqlmin!CMEDProxyRelation::GetTemporalCurrentTableId+0x7a
- 0a 000000f8`4527a3c0 00007ff8`d801dfb8 sqllang!CAlgTableMetadata::FPartialBind+0xb58
- 0b 000000f8`4527a580 00007ff8`d80394b3 sqllang!CAlgTableMetadata::Bind+0x317
- 0c 000000f8`4527a620 00007ff8`d800415d sqllang!CRelOp_Get::BindTree+0x78f
- 0d 000000f8`4527a890 00007ff8`d80418a1 sqllang!COptExpr::BindTree+0x85
- 0e 000000f8`4527a8c0 00007ff8`d800415d sqllang!CRelOp_FromList::BindTree+0x31
- 0f 000000f8`4527a920 00007ff8`d802c2a3 sqllang!COptExpr::BindTree+0x85
- 10 000000f8`4527a950 00007ff8`d800415d sqllang!CRelOp_QuerySpec::BindTree+0x2e8
- 11 000000f8`4527aa60 00007ff8`d80042dd sqllang!COptExpr::BindTree+0x85
- 12 000000f8`4527aa90 00007ff8`d800415d sqllang!CRelOp_SelectQuery::BindTree+0x489
- 13 000000f8`4527ab80 00007ff8`d8003f23 sqllang!COptExpr::BindTree+0x85
- 14 000000f8`4527abb0 00007ff8`d8004e47 sqllang!CRelOp_Query::FAlgebrizeQuery+0x4bd
- 15 000000f8`4527ae30 00007ff8`d7ff5576 sqllang!CProchdr::FNormQuery+0x8f
- 16 000000f8`4527ae70 00007ff8`d7ff4a79 sqllang!CProchdr::FNormalizeStep+0x5bd
- 17 000000f8`4527b4b0 00007ff8`d7ff5124 sqllang!CSQLSource::FCompile+0xea5
- 18 000000f8`4527e1b0 00007ff8`d7e659c3 sqllang!CSQLSource::FCompWrapper+0xcb
- 19 000000f8`4527e280 00007ff8`d7e6387a sqllang!CSQLSource::Transform+0x721
- 1a 000000f8`4527e3e0 00007ff8`d7e6e67b sqllang!CSQLSource::Execute+0x4fa
- 1b 000000f8`4527e8c0 00007ff8`d7e6d815 sqllang!process_request+0xca6
- 1c 000000f8`4527efc0 00007ff8`d7e6d5ef sqllang!process_commands_internal+0x4b7
- 1d 000000f8`4527f0f0 00007ff8`d4096523 sqllang!process_messages+0x1d6
- 1e 000000f8`4527f2d0 00007ff8`d4096e6d sqldk!SOS_Task::Param::Execute+0x232
- 1f 000000f8`4527f8d0 00007ff8`d4096c75 sqldk!SOS_Scheduler::RunTask+0xa5
- 20 000000f8`4527f940 00007ff8`d40bb160 sqldk!SOS_Scheduler::ProcessTasks+0x39d
- 21 000000f8`4527fa60 00007ff8`d40baa5b sqldk!SchedulerManager::WorkerEntryPoint+0x2a1
- 22 000000f8`4527fb30 00007ff8`d40bafa4 sqldk!SystemThreadDispatcher::ProcessWorker+0x3ed
- 23 000000f8`4527fe30 00007ff8`f6d86fd4 sqldk!SchedulerManager::ThreadEntryPoint+0x3b5
- 24 000000f8`4527ff20 00007ff8`f865cec1 KERNEL32!BaseThreadInitThunk+0x14
- 25 000000f8`4527ff50 00000000`00000000 ntdll!RtlUserThreadStart+0x21
复制代码 从线程栈上看,有 SQLSERVER 核心的 Scheduler ,Task 以及 命令分析器,查询优化器,查询执行器 等各种核心元素,后续再慢慢研究吧。
三: 总结
深入的理解数据,索引在数据页上的布局,可以从根本上帮助我们理解如何减少请求在数据页上的流转,减少逻辑读,从而提升 sql 的查询性能。
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作! |