mysql的select
· 4 min read
为什么想看select的代码
有一个场景,遇到一个表只有十多万,但是表大小有几十g,为什么呢?因为有个字段是longtext.
放了很多很长的文本.发现select * from table limit 1000就已经读不出来了
一个简单的select语句
select id from test wher id < 100 ;
这个流程究竟发生了什么?
第一步其实和php差不多,先是编译原理的前端几步lex和parse 第二步就是逻辑优化和物理优化, 其实可以想成是常用的编程语言的常量折叠或者数据流图的分析,就是编译时的优化 第三步也就是语义动作了,也就是真正的执行过程也可以想做是运行时: 但是条件对于读来说是不可见的,条件是作用于索引上 , 然后返回所有行 , 再根据列筛选出来,然后再join和排序,对于这个sql来说,他唯一作用就是通过索引读出内容,内存和硬盘对于他来说是不存在的
首先是读表,这个表是从ibd文件来的.所以终究是需要调用系统调用读文件,那么linux用系统调用是pread
索引在哪保存? 保存在表空间里面 内容保存到哪里? 保存到表空间里面 关联是在哪里发生? 发生在从索引从表空间读出来 关联是整整一行关联吗? 是的. 二级索引怎么读的? 通过二级索引读一级索引,一级索引读内容.
所以实际上是有一个语义上的层是: sql -> 语义动作 作用于索引 -> 索引访问表空间(有点像交换空间或者物理内存和虚拟内存的关系一样一样, 需要的时候才从硬盘硬盘加载)
下面是相关代码
(gdb) bt
#0 srv_start (create_new_db=create_new_db@entry=false) at /home/ubuntu/mysql-8.0.23/storage/innobase/srv/srv0start.cc:1857
#1 0x00005555577275b6 in innobase_init_files (tablespaces=0x7fffea1f1380, dict_init_mode=DICT_INIT_CHECK_FILES) at /home/ubuntu/mysql-8.0.23/storage/innobase/handler/ha_innodb.cc:5042
#2 innobase_ddse_dict_init (dict_init_mode=DICT_INIT_CHECK_FILES, version=<optimized out>, tables=0x7fffea1f1360, tablespaces=0x7fffea1f1380)
at /home/ubuntu/mysql-8.0.23/storage/innobase/handler/ha_innodb.cc:12323
#3 0x00005555573d2aef in dd::bootstrap::DDSE_dict_init (thd=thd@entry=0x55555b899410, dict_init_mode=dict_init_mode@entry=DICT_INIT_CHECK_FILES, version=80023)
at /home/ubuntu/mysql-8.0.23/sql/dd/impl/bootstrap/bootstrapper.cc:737
#4 0x00005555575f92e4 in dd::upgrade_57::do_pre_checks_and_initialize_dd (thd=0x55555b899410) at /home/ubuntu/mysql-8.0.23/sql/dd/upgrade_57/upgrade.cc:911
#5 0x0000555556697ec5 in bootstrap::handle_bootstrap (arg=arg@entry=0x7fffffffda10) at /home/ubuntu/mysql-8.0.23/sql/bootstrap.cc:323
#6 0x0000555557b934a1 in pfs_spawn_thread (arg=0x55555b834c80) at /home/ubuntu/mysql-8.0.23/storage/perfschema/pfs.cc:2900
#7 0x00007ffff7bbb6db in start_thread (arg=0x7fffea1f2700) at pthread_create.c:463
#8 0x00007ffff61b571f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
(gdb) info threads
Id Target Id Frame
1 Thread 0x7ffff7fe7880 (LWP 17988) "mysqld" 0x00007ffff7bbcd2d in __GI___pthread_timedjoin_ex (threadid=140737121298176, thread_return=thread_return@entry=0x0, abstime=abstime@entry=0x0,
block=block@entry=true) at pthread_join_common.c:89
* 2 Thread 0x7fffea1f2700 (LWP 18000) "mysqld" __libc_pread64 (fd=4, buf=0x7fffe81d0000, count=65536, offset=0) at ../sysdeps/unix/sysv/linux/pread64.c:29
4 Thread 0x7fffe8f49700 (LWP 18269) "mysqld" 0x00007ffff7bc1ad3 in futex_wait_cancelable (private=<optimized out>, expected=0, futex_word=0x7fffe42eda10)
at ../sysdeps/unix/sysv/linux/futex-internal.h:88
5 Thread 0x7fffe3b32700 (LWP 18270) "mysqld" 0x00007ffff7bc1ad3 in futex_wait_cancelable (private=<optimized out>, expected=0, futex_word=0x7fffe42edab0)
at ../sysdeps/unix/sysv/linux/futex-internal.h:88
6 Thread 0x7fffe3331700 (LWP 18271) "mysqld" 0x00007ffff7bc1ad3 in futex_wait_cancelable (private=<optimized out>, expected=0, futex_word=0x7fffe42edb50)
at ../sysdeps/unix/sysv/linux/futex-internal.h:88
7 Thread 0x7fffe2b30700 (LWP 18272) "mysqld" 0x00007ffff7bc1ad3 in futex_wait_cancelable (private=<optimized out>, expected=0, futex_word=0x7fffe42edbf0)
at ../sysdeps/unix/sysv/linux/futex-internal.h:88
8 Thread 0x7fffe232f700 (LWP 18273) "mysqld" 0x00007ffff7bc1ad3 in futex_wait_cancelable (private=<optimized out>, expected=0, futex_word=0x7fffe42edc90)
at ../sysdeps/unix/sysv/linux/futex-internal.h:88
9 Thread 0x7fffe1b2e700 (LWP 18274) "mysqld" 0x00007ffff7bc1ad3 in futex_wait_cancelable (private=<optimized out>, expected=0, futex_word=0x7fffe42edd30)
at ../sysdeps/unix/sysv/linux/futex-internal.h:88
10 Thread 0x7fffe132d700 (LWP 18275) "mysqld" 0x00007ffff7bc1ad3 in futex_wait_cancelable (private=<optimized out>, expected=0, futex_word=0x7fffe42eddd0)
at ../sysdeps/unix/sysv/linux/futex-internal.h:88
11 Thread 0x7fffe0b2c700 (LWP 18276) "mysqld" 0x00007ffff7bc1ad3 in futex_wait_cancelable (private=<optimized out>, expected=0, futex_word=0x7fffe42ede70)
at ../sysdeps/unix/sysv/linux/futex-internal.h:88
12 Thread 0x7fffd3d5f700 (LWP 18277) "mysqld" 0x00007ffff7bc1ad3 in futex_wait_cancelable (private=<optimized out>, expected=0, futex_word=0x7fffe42edf10)
at ../sysdeps/unix/sysv/linux/futex-internal.h:88
13 Thread 0x7fffd355e700 (LWP 18278) "mysqld" 0x00007ffff7bc1ad3 in futex_wait_cancelable (private=<optimized out>, expected=0, futex_word=0x7fffe42edfb0)
at ../sysdeps/unix/sysv/linux/futex-internal.h:88
14 Thread 0x7fffd2d5d700 (LWP 18279) "mysqld" 0x00007ffff6ace280 in operator new(unsigned long) () from /usr/lib/x86_64-linux-gnu/libstdc++.so.6