sql语句具体在虚拟机里面的执行过程

作者: 5dplay | 来源:发表于2021-07-26 10:46 被阅读0次

前提准备

编译sqlite3：gcc -g -O0 -DSQLITE_DEBUG -DSQLITE_ENABLE_EXPLAIN_COMMENTS -DSQLITE_ENABLE_SELECTTRACE -DSQLITE_ENABLE_WHERETRACE -o sqlite3 shell.c sqlite3.c

这里单开DEBUG十分之有用，源码里面，还有一些输出调试控制。SQLITE_ENABLE_EXPLAIN_COMMENTS这个非常有用，可以explain某个sql语句的时候，查看具体要执行什么操作。

PRAGMA parser_trace=boolean：控制是否显示parser规约过程
PRAGMA vdbe_debug=boolean：显示sql对应的操作码，显示执行过程。

以下是官网的介绍：

具体例子

select 主键并且 without rowid

sqlite> select * from department where id = 33;
SQL: [select * from department where id = 33;]
VDBE Program Listing:
   0 Init             0   11    0               00 Start at 11
   1 OpenRead         1    2    0 k(1,)         02 root=2 iDb=0; sqlite_autoindex_department_1
   2 Explain          2    0    0 SEARCH TABLE department USING PRIMARY KEY (id=?) 00
   3 Integer         33    1    0               00 r[1]=33
   4 SeekGE           1   10    1 1             00 key=r[1]
   5 IdxGT            1   10    1 1             00 key=r[1]
   6 Column           1    0    2               00 r[2]=department.id
   7 Column           1    1    3               00 r[3]=department.dept
   8 Column           1    2    4               00 r[4]=department.emp_id
   9 ResultRow        2    3    0               00 output=r[2..4]
  10 Halt             0    0    0               00
  11 Transaction      0    0    2 0             01 usesStmtJournal=0
  12 TableLock        0    2    0 department    00 iDb=0 root=2 write=0
  13 Goto             0    1    0               00
VDBE Trace:
   0 Init             0   11    0               00 Start at 11
SQL-trace: select * from department where id = 33;
  11 Transaction      0    0    2 0             01 usesStmtJournal=0
  12 TableLock        0    2    0 department    00 iDb=0 root=2 write=0
  13 Goto             0    1    0               00
   1 OpenRead         1    2    0 k(1,)         02 root=2 iDb=0; sqlite_autoindex_department_1
   2 Explain          2    0    0 SEARCH TABLE department USING PRIMARY KEY (id=?) 00
   3 Integer         33    1    0               00 r[1]=33
REG[1] =  i:33
   4 SeekGE           1   10    1 1             00 key=r[1]
REG[1] =  i:33
   6 Column           1    0    2               00 r[2]=department.id
REG[2] =  i:33
   7 Column           1    1    3               00 r[3]=department.dept
REG[3] =   s6[test33](8)
   8 Column           1    2    4               00 r[4]=department.emp_id
REG[4] =  i:3
   9 ResultRow        2    3    0               00 output=r[2..4]
REG[2] =  i:33
REG[3] =   s6[test33](8)
REG[4] =  i:3
33|test33|3
  10 Halt             0    0    0               00

熟悉汇编的，或者别的虚拟机字节码的对此应该不陌生。我们可以直接看最终的执行顺序，也就是VDBE Trace开始的字节码：

11 Transaction 这个格式有点多寄存器，Transaction p1 p2 p3 p4 p5
- p1 对应这打开的数据库序号，0 是主数据库文件， 1 是临时表用的文件，2及以上都是attach的数据库文件。
- p2 对应是读事务还是写事务，非0则是写事务，0为读事务。如果p2 >= 2 的话，则是排他性的事务
- p3 schema cookie，用来校验schema是否过期了
- p4 schema generation counter，尚不明确
- p5 控制是否检验schema cookie和schema generation counter。如果不一致就会返回SQLITE_SCHEMA ，此时sqlite3_step会reprepare statement
12 TableLock p1 p2 p3 p4:
- p1 表对应的数据库文件下标
- p2 该表的根页号
- p3 0 for 读锁，1 for 写锁
1 OpenRead p1 p2 p3 p4 p5：打开一个只读的游标
- p1 游标的句柄
- p2 表所在的根页
- p3 表所在数据库文件
- p4 依据是索引树还是普通b-tree。待查明
- p5 设置该游标的属性：0x02 代表它只用来等值搜索
Integer p1 p2：32位整型的p1写入寄存器p2中，本例中是将r[1] = 33。
SeekGE p1 p2 p3 p4：寻找关键值大于等于key的。游标p1，p2是失败的时候goto的地方。
IdxGT p1 p2 p3 p4：判断找到的关键字是否大于key，是的话跳转到p2。
Column p1 p2 p3：将游标p1看做一行数据，p2是第p2项数据，将其结果储存在寄存器p3中。
ResultRow p1 p2 ：将从寄存器p1开始的p2个寄存器作为返回值，通知sqlite3_step() 返回SQLITEROW。

select 索引

sqlite> select * from department where dept == 'test33';
SQL: [select * from department where dept == 'test33';]
VDBE Program Listing:
   0 Init             0   15    0               00 Start at 15
   1 OpenRead         0    2    0 k(1,)         00 root=2 iDb=0; department
   2 OpenRead         1    3    0 k(2,,)        02 root=3 iDb=0; depName_index
   3 Explain          3    0    0 SEARCH TABLE department USING INDEX depName_index (dept=?) 00
   4 String8          0    1    0 test33        00 r[1]='test33'
   5 SeekGE           1   14    1 1             00 key=r[1]
   6 IdxGT            1   14    1 1             00 key=r[1]
   7 Column           1    1    2               00 r[2]=
   8 NotFound         0   13    2 1             00 key=r[2]
   9 Column           1    1    3               00 r[3]=department.id
  10 Column           1    0    4               00 r[4]=department.dept
  11 Column           0    2    5               00 r[5]=department.emp_id
  12 ResultRow        3    3    0               00 output=r[3..5]
  13 Next             1    6    1               00
  14 Halt             0    0    0               00
  15 Transaction      0    0    2 0             01 usesStmtJournal=0
  16 TableLock        0    2    0 department    00 iDb=0 root=2 write=0
  17 Goto             0    1    0               00
VDBE Trace:
   0 Init             0   15    0               00 Start at 15
SQL-trace: select * from department where dept == 'test33';
  15 Transaction      0    0    2 0             01 usesStmtJournal=0
  16 TableLock        0    2    0 department    00 iDb=0 root=2 write=0
  17 Goto             0    1    0               00
   1 OpenRead         0    2    0 k(1,)         00 root=2 iDb=0; department
   2 OpenRead         1    3    0 k(2,,)        02 root=3 iDb=0; depName_index
   3 Explain          3    0    0 SEARCH TABLE department USING INDEX depName_index (dept=?) 00
   4 String8          0    1    0 test33        00 r[1]='test33'
REG[1] =   t6[test33](8)
   5 SeekGE           1   14    1 1             00 key=r[1]
REG[1] =   t6[test33](8)
   7 Column           1    1    2               00 r[2]=
REG[2] =  i:33
   8 NotFound         0   13    2 1             00 key=r[2]
REG[2] =  i:33
   9 Column           1    1    3               00 r[3]=department.id
REG[3] =  i:33
  10 Column           1    0    4               00 r[4]=department.dept
REG[4] =   s6[test33](8)
  11 Column           0    2    5               00 r[5]=department.emp_id
REG[5] =  i:3
  12 ResultRow        3    3    0               00 output=r[3..5]
REG[3] =  i:33
REG[4] =   s6[test33](8)
REG[5] =  i:3
33|test33|3
  13 Next             1    6    1               00
   6 IdxGT            1   14    1 1             00 key=r[1]
REG[1] =   t6[test33](8)
  14 Halt             0    0    0               00
sqlite>

这个过程就比较复杂，首先明确一定，dept这个索引，存储着的是dept与id的对应关系，sql遇到带索引的查询是先通过索引找与主键（一般情况下是rowid，但without rowid则是primary key）的映射关系。然后再通过主键搜索到对应的记录。
所以开始分析下这条sql语句

锁住数据库文件
打开两个游标cursor0，cursor1，分别指向主键id和dept索引的根页。
通过cursor1找到dept == 'test33'的映射关系。并且将主键id赋值到r[2]上
通过cursor0找到主键==r[2]的记录
这里比较有意思，主键id和dept在cursor1上有，故这两个直接由cursor1给出。emp_id只有cursor0有，故这个要从cursor0上获取。

非主键/索引 select

sqlite> select * from department where emp_id == 3;
SQL: [select * from department where emp_id == 3;]
VDBE Program Listing:
   0 Init             0   12    0               00 Start at 12
   1 OpenRead         1    2    0 k(1,)         00 root=2 iDb=0; sqlite_autoindex_department_1
   2 Explain          2    0    0 SCAN TABLE department 00
   3 Rewind           1   11    1 0             00
   4 Column           1    2    1               00 r[1]=department.emp_id
   5 Ne               2   10    1 (BINARY)      54 if r[1]!=r[2] goto 10
   6 Column           1    0    3               00 r[3]=department.id
   7 Column           1    1    4               00 r[4]=department.dept
   8 Column           1    2    5               00 r[5]=department.emp_id
   9 ResultRow        3    3    0               00 output=r[3..5]
  10 Next             1    4    0               01
  11 Halt             0    0    0               00
  12 Transaction      0    0    2 0             01 usesStmtJournal=0
  13 TableLock        0    2    0 department    00 iDb=0 root=2 write=0
  14 Integer          3    2    0               00 r[2]=3
  15 Goto             0    1    0               00
VDBE Trace:
   0 Init             0   12    0               00 Start at 12
SQL-trace: select * from department where emp_id == 3;
  12 Transaction      0    0    2 0             01 usesStmtJournal=0
  13 TableLock        0    2    0 department    00 iDb=0 root=2 write=0
  14 Integer          3    2    0               00 r[2]=3
REG[2] =  i:3
  15 Goto             0    1    0               00
   1 OpenRead         1    2    0 k(1,)         00 root=2 iDb=0; sqlite_autoindex_department_1
   2 Explain          2    0    0 SCAN TABLE department 00
   3 Rewind           1   11    1 0             00
   4 Column           1    2    1               00 r[1]=department.emp_id
REG[1] =  i:33333
   5 Ne               2   10    1 (BINARY)      54 if r[1]!=r[2] goto 10
REG[2] =  i:3
REG[1] =  i:33333
  10 Next             1    4    0               01
   4 Column           1    2    1               00 r[1]=department.emp_id
REG[1] =  i:3
   5 Ne               2   10    1 (BINARY)      54 if r[1]!=r[2] goto 10
REG[2] =  i:3
REG[1] =  i:3
   6 Column           1    0    3               00 r[3]=department.id
REG[3] =  i:33
   7 Column           1    1    4               00 r[4]=department.dept
REG[4] =   s6[test33](8)
   8 Column           1    2    5               00 r[5]=department.emp_id
REG[5] =  i:3
   9 ResultRow        3    3    0               00 output=r[3..5]
REG[3] =  i:33
REG[4] =   s6[test33](8)
REG[5] =  i:3
33|test33|3
  10 Next             1    4    0               01
   4 Column           1    2    1               00 r[1]=department.emp_id
REG[1] =  i:4
   5 Ne               2   10    1 (BINARY)      54 if r[1]!=r[2] goto 10
REG[2] =  i:3
REG[1] =  i:4
  10 Next             1    4    0               01
   4 Column           1    2    1               00 r[1]=department.emp_id
REG[1] =  i:5
   5 Ne               2   10    1 (BINARY)      54 if r[1]!=r[2] goto 10
REG[2] =  i:3
REG[1] =  i:5
  10 Next             1    4    0               01
   4 Column           1    2    1               00 r[1]=department.emp_id
REG[1] =  i:6
   5 Ne               2   10    1 (BINARY)      54 if r[1]!=r[2] goto 10
REG[2] =  i:3
REG[1] =  i:6
  10 Next             1    4    0               01
   4 Column           1    2    1               00 r[1]=department.emp_id
REG[1] =  i:7
   5 Ne               2   10    1 (BINARY)      54 if r[1]!=r[2] goto 10
REG[2] =  i:3
REG[1] =  i:7
  10 Next             1    4    0               01
   4 Column           1    2    1               00 r[1]=department.emp_id
REG[1] =  i:4777
   5 Ne               2   10    1 (BINARY)      54 if r[1]!=r[2] goto 10
REG[2] =  i:3
REG[1] =  i:4777
  10 Next             1    4    0               01
   4 Column           1    2    1               00 r[1]=department.emp_id
REG[1] =  i:456520
   5 Ne               2   10    1 (BINARY)      54 if r[1]!=r[2] goto 10
REG[2] =  i:3
REG[1] =  i:456520
  10 Next             1    4    0               01
  11 Halt             0    0    0               00

这里看起来就复杂很多了，因为没有索引，只能逐个遍历确认emp_id == 3;

Rewind，重定位到该表第一项中。
将当前数据项的emp_id与r[2]作比较，同样则返回结果，否则获取下一项循环直至所有数据项被遍历完了。

总结一下：

如果是索引或者主键。rowid查找的时候，是直接由SeekXX系列的可以直接用的，并且支持多个字段的那种索引。例如SeekGE P1 P2 P3 P4，如果是index的话，就从p3开始的p4个寄存器都是索引的内容，依次序比较即可。
如果是非索引的查询，那么就是从第一项数据依次遍历一直往下找，直至结束。

insert 语句

最后让我们再来分析一个insert语句，其余的就不演示了。可以自行编译执行调试获知。

sqlite> insert into department values(100, 'test100', 90909);
SQL: [insert into department values(100, 'test100', 90909);]
VDBE Program Listing:
   0 Init             0   24    0               00 Start at 24
   1 OpenWrite        1    3    0 k(2,,)        00 root=3 iDb=0; depName_index
   2 OpenWrite        2    2    0 k(1,)         00 root=2 iDb=0; sqlite_autoindex_department_1
   3 Null             0    1    0               00 r[1]=NULL
   4 Integer        100    2    0               00 r[2]=100
   5 String8          0    3    0 test100       00 r[3]='test100'
   6 Integer       90909    4    0               00 r[4]=90909
   7 HaltIfNull    1299    2    2 department.id 01 if r[2]=null halt
   8 Affinity         2    3    0 DBD           00 affinity(r[2..4])
   9 Noop             0    0    0               00 uniqueness check for depName_index
  10 SCopy            3    6    0               00 r[6]=r[3]; dept
  11 SCopy            2    7    0               00 r[7]=r[2]; id
  12 MakeRecord       6    2    5               00 r[5]=mkrec(r[6..7]); for depName_index
  13 Noop             0    0    0               00 uniqueness check for sqlite_autoindex_department_1
  14 SCopy            2    9    0               00 r[9]=r[2]; id
  15 SCopy            3   10    0               00 r[10]=r[3]; dept
  16 SCopy            4   11    0               00 r[11]=r[4]; emp_id
  17 MakeRecord       9    3    8               00 r[8]=mkrec(r[9..11]); for sqlite_autoindex_department_1
  18 Abortable        0    0    0               00
  19 NoConflict       2   21    9 1             00 key=r[9]
  20 Halt          1555    2    0 department.id 02
  21 IdxInsert        1    5    6 2             10 key=r[5]
  22 IdxInsert        2    8    9 1             11 key=r[8]
  23 Halt             0    0    0               00
  24 Transaction      0    1    2 0             01 usesStmtJournal=0
  25 TableLock        0    2    1 department    00 iDb=0 root=2 write=1
  26 Goto             0    1    0               00

简单分析一下啊，因为这里有两个索引，所以要分别打开两个游标。而且，因为创建表的时候用的是without rowid，所以r[1]=null。

先做约束性的校验，比如id != NULL。
再对两个索引打包，make record。depName_index 由 dept + id 组成，存放在r[5]中，id的由id + dept + emp_id组成，存放在r[8]中。
检测id是否重复，没重复就跳到插入那里去，否则退出，并且p1给出退出码。但是dept并没有检测，是因为index不一定要唯一性，只有创建的时候带上 unique 才会需要这玩意检测。

update 语句

sqlite> update department set emp_id=90910 where emp_id == 90909;
SQL: [update department set emp_id=90910 where emp_id == 90909;]
VDBE Program Listing:
   0 Init             0   23    0               00 Start at 23
   1 Null             0   10   10               00 r[10..10]=NULL
   2 Noop             3    1    0               00
   3 OpenWrite        2    2    0 k(1,)         00 root=2 iDb=0; sqlite_autoindex_department_1
   4 Explain          4    0    0 SCAN TABLE department 00
   5 Rewind           2   22   12 0             00
   6 Column           2    2   12               00 r[12]=department.emp_id
   7 Ne              13   21   12 (BINARY)      54 if r[12]!=r[13] goto 21
   8 Column           2    0   10               00 r[10]=department.id
   9 Once             0   10    0               00
  10 IsNull          10   22    0               00 if r[10]==NULL goto 22
  11 Column           2    0    7               00 r[7]=department.id
  12 Column           2    1    8               00 r[8]=department.dept
  13 Integer       90910    9    0               00 r[9]=90910
  14 Affinity         7    3    0 DBD           00 affinity(r[7..9])
  15 Noop             0    0    0               00 uniqueness check for sqlite_autoindex_department_1
  16 SCopy            7    2    0               00 r[2]=r[7]; id
  17 SCopy            8    3    0               00 r[3]=r[8]; dept
  18 SCopy            9    4    0               00 r[4]=r[9]; emp_id
  19 MakeRecord       2    3    1               00 r[1]=mkrec(r[2..4]); for sqlite_autoindex_department_1
  20 IdxInsert        2    1    2 1             03 key=r[1]
  21 Next             2    6    0               01
  22 Halt             0    0    0               00
  23 Transaction      0    1    2 0             01 usesStmtJournal=0
  24 TableLock        0    2    1 department    00 iDb=0 root=2 write=1
  25 Integer       90909   13    0               00 r[13]=90909
  26 Goto             0    1    0               00

update思路感觉和select -> modify -> insert的思路差不多，先查询得到对应的数据项，然后修改对应列项，最后写回b树中。这里留下一个疑问，在key相同的情况下，这个IdxInsert会做什么处理？感觉会是覆盖。

delete语句

sqlite> delete from department where emp_id == 90910;
SQL: [delete from department where emp_id == 90910;]
VDBE Program Listing:
   0 Init             0   16    0               00 Start at 16
   1 Noop             3    1    0               00
   2 OpenWrite        2    2    0 k(1,)         00 root=2 iDb=0; sqlite_autoindex_department_1
   3 Explain          3    0    0 SCAN TABLE department 00
   4 Rewind           2   15    2 0             00
   5 Column           2    2    2               00 r[2]=department.emp_id
   6 Ne               3   14    2 (BINARY)      54 if r[2]!=r[3] goto 14
   7 Column           2    0    1               00 r[1]=department.id
   8 Once             0   10    0               00
   9 OpenWrite        1    3    0 k(2,,)        08 root=3 iDb=0; depName_index
  10 Column           2    1    4               00 r[4]=department.dept
  11 Column           2    0    5               00 r[5]=department.id
  12 IdxDelete        1    4    2               00 key=r[4..5]
  13 Delete           2    1    0 department    02
  14 Next             2    5    0               01
  15 Halt             0    0    0               00
  16 Transaction      0    1    2 0             01 usesStmtJournal=0
  17 TableLock        0    2    1 department    00 iDb=0 root=2 write=1
  18 Integer       90910    3    0               00 r[3]=90910
  19 Goto             0    1    0               00

也是想查询到对应的数据项，然后删除索引，最后删表项。

sql语句具体在虚拟机里面的执行过程

前提准备

具体例子

select 主键并且 without rowid

select 索引

非主键/索引 select

insert 语句

update 语句

delete语句

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读