delete fd from konglong996.feast_done fd inner join konglong996.role r on fd.role_id=r.role_id where fd.kind=61 and r.today_pay=0
update konglong198.feast_done as fd join konglong198.role r on fd.role_id=r.role_id set fd.done=r.pay_all, fd.awarded = 0 where fd.role_id = 390198 and fd.kind = 61
./build -DCMAKE_BUILD_TYPE=Debug -DWITH_BOOST=E:\cpp\boost_1_77_0 -DCMAKE_INSTALL_PREFIX=E:\cpp\mysql-8.0.29\mysql-8.0.29\build #编译路径与安装路径需一致 -DMYSQL_DATADIR=E:\cpp\mysql-8.0.29\mysql-8.0.29\build\data #数据库初始化路径 -DSYSCONFDIR= E:\cpp\mysql-8.0.29\mysql-8.0.29\build -DMYSQL_UNIX_ADDR=E:\cpp\mysql-8.0.29\mysql-8.0.29\build\data\mysql.sock -DWITH_SSL=system
./build -DCMAKE_BUILD_TYPE=Debug -DWITH_BOOST=E:\cpp\boost_1_77_0 -DCMAKE_INSTALL_PREFIX=E:\cpp\mysql\mysql\build #编译路径与安装路径需一致 -DMYSQL_DATADIR=E:\cpp\mysql\mysql\build\data #数据库初始化路径 -DSYSCONFDIR= E:\cpp\mysql\mysql\build -DMYSQL_UNIX_ADDR=E:\cpp\mysql\mysql\build\data\mysql.sock -DWITH_SSL=system
178-195
https://blog.csdn.net/weixin_29166013/article/details/113115570
https://blog.csdn.net/yjidtliutao/article/details/121009101
https://www.bilibili.com/read/cv9906456/
ibd2sdi学习:
https://github.com/mysql/mysql-server/blob/8.0/utilities/ibd2sdi.cc
process_sdi_from_copy
https://segmentfault.com/a/1190000037778688?utm_source=tag-newest
cmake .. -DDOWNLOAD_BOOST=1 -DWITH_BOOST=. -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DENABLED_LOCAL_INFILE=ON -DWITH_SSL=system -DCMAKE_INSTALL_PREFIX=/usr/local/mysql/server -DMYSQL_DATADIR=/usr/local/mysql/data -DMYSQL_TCP_PORT=3306 -DCMAKE_BUILD_TYPE=Debug
https://blog.csdn.net/weixin_30036893/article/details/113265896
gcc -o sqlite3 -c sqlite3.c -lpthread -ldl sqlite3.o
DNDEBUG
TCC += -DSQLITE_DEBUG
TCC += -DSQLITE_TEST=1
TCC += -DSQLITE_ENABLE_MEMSYS5
TCC += -DSQLITE_ENABLE_MEMORY_MANAGEMENT
./build -DCMAKE_BUILD_TYPE=Debug -DWITH_BOOST=E:\cpp\boost_1_77_0 -DCMAKE_INSTALL_PREFIX=E:\cpp\mysql\build #编译路径与安装路径需一致 -DMYSQL_DATADIR=E:\cpp\mysql\build\data #数据库初始化路径 -DSYSCONFDIR= E:\cpp\mysql\build -DMYSQL_UNIX_ADDR=E:\cpp\mysql\build\data\mysql.sock -DWITH_SSL=system
https://blog.csdn.net/qq_44787898/article/details/112347449
合并
btr_compress
btr_can_merge_with_page
btr_cur_compress_if_useful 比例0.5
btr0btr.cc btr_compress
btr_cur_search_to_nth_level
page_cur_search_with_match
btr_page_split_and_insert
page_cur_insert_rec_low
page_cur_tuple_insert
row_ins_index_entry
row_ins_index_entry_low
/*将tuple内存中的逻辑记录转换成物理记录rec_t(在destination空间上)*/
rec_convert_dtuple_to_rec_low
page_header_get_ptr
page_rec_get_next
page_create page0page.cc
slot_no越大, 对应的rec越小
PAGE_HEADER
page_header_get_field(page, PAGE_N_DIR_SLOTS)
https://github.com/zhaiwx1987/innodb_ebook
老生常谈：如何在MySQL中查找数据
https://www.jianshu.com/p/adb7337a8fdc
分裂:
btr_page_split_and_insert
btr_page_get_split_rec_to_left
btr_page_get_split_rec_to_right
btr_page_get_split_rec_to_right
首先判断本次插入记录是否在上次插入记录的右边，如果是的话，则认为这是顺序的插入
//把本次插入记录之前(含本次插入记录)放在左页(即当前页),然后右边分裂出新页,即本次插入记录后面的tuple放在新的右页
btr_page_get_split_rec_to_left
如果上次插入的记录在当前插入记录的右边(则认为这是逆序的插入?),只要插入记录不是infimum或infimum的下一条记录,则分割点取当前插入记录
把本次插入记录之前(不含本次插入记录)的放在左页(向左分裂的新页),后面的元素放在右页(旧页),这样能保证本次插入记录
为新页的最后一个
为什么要有向左或向右分裂
无论是顺序(从小到大)或者逆序(从大到小)的插入,如果按照当前页面的数据的一半进行分裂,若是顺序插入,左侧的节点不可能有新的节点插入,因此,会浪费掉一半的空间,逆序亦同理。
https://blog.51cto.com/u_15352893/3746733
(!(((max_size >= rec_size) && (max_size >= BTR_CUR_PAGE_REORGANIZE_LIMIT))
|| page_get_max_insert_size(page, 1) >= rec_size || page_get_n_recs(page) <= 1))
btr_cur_pessimistic_update btr0cur.cc
做原地更新(若更新的每列存储空间没变的话)
row_upd_rec_in_place
rec_set_nth_field
先删除后插入(若更新的其中一列存储空间发送变化,如果原地更新由于存储空间发生变化,则会存在内存碎片),
page_cur_delete_rec(在页内删除)
btr_cur_insert_if_possible(在页内插入)
btr_cur_insert_if_possible在乐观更新或悲观更新时调用
page_cur_tuple_insert
page_cur_insert_rec_low
ibuf_insert_low ibuf0ibuf.cc
如果足够空间,则调用btr_cur_optimistic_insert直接插入即可
btr_cur_optimistic_insert
如果空间不足,则调用btr_cur_pessimistic_insert先分裂再插入
btr_cur_pessimistic_insert
https://www.jianshu.com/p/a41a1ba080bc
MySQL之数据页结构
https://www.modb.pro/db/139052
MySQL各种“Buffer”之Doublewrite Buffer
https://www.modb.pro/db/114783
mysql墨天轮
https://www.modb.pro/u/443175
mysql锁原理:
lock_rec_trx_wait
Innodb行锁源码学习(一)
https://www.cnblogs.com/zengkefu/p/5678355.html
Mysql锁内部实现机制之C源码解析
https://www.jb51.net/article/260262.htm
mysql中的各种锁把我搞糊涂啦~
https://jishuin.proginn.com/p/763bfbd3b469
MySQL InnoDB 事务锁源码分析
https://www.jb51.net/article/223558.htm#_lab2_2_1
InnoDB：lock manager (1)
https://zhuanlan.zhihu.com/p/451087966
MySQL InnoDB 事务锁源码分析
https://www.jb51.net/article/223558.htm#_lab2_2_1
【MySQL源码分析】浅谈Mysql的锁
http://www.javashuo.com/article/p-kfdhjffq-k.html
MySQL Innodb行锁剖析
https://zhuanlan.zhihu.com/p/139489272
MySQL 是怎么加锁的？ https://xiaolincoding.com/mysql/lock/how_to_lock.html#%E5%94%AF%E4%B8%80%E7%B4%A2%E5%BC%95%E7%AD%89%E5%80%BC%E6%9F%A5%E8%AF%A2
MySQL Innodb行锁剖析
https://zhuanlan.zhihu.com/p/139489272
InnoDB：row_search_mvcc源码简要分析
https://www.jianshu.com/p/6a6180825c0e
【Mysql原理与实践】第2阶段第11讲Next-key Lock加锁源码分析
https://www.bilibili.com/video/BV12S4y1G7YA/
【Mysql原理与实践】insert加锁过程的mysql源码分析
https://www.bilibili.com/video/av851429290/
读 MySQL 源码再看 INSERT 加锁流程
https://blog.csdn.net/u013256816/article/details/120231947
Mysql insert语句执行原理
https://www.ngui.cc/el/1547762.html?action=onClick
MySQL普通索引的加锁
https://www.pudn.com/news/6228ddfc9ddf223e1ad2a870.html
MySQL next-key lock 加锁范围是什么？
https://www.cnblogs.com/liuzhihang/p/14854944.html
MySQL next-key lock 加锁范围总结
https://blog.csdn.net/qq_36535538/article/details/117692855
Mysql performance_schema的data_locks 和 data_lock_waits说明
https://www.jianshu.com/p/cfaea55f5a28
为什么update语句where 条件没有带上索引会锁全表？
https://segmentfault.com/a/1190000042280196?utm_source=sf-similar-article
MySQL8.0优化 - 锁 - 锁的内存结构、锁监控
https://blog.csdn.net/ChinaYangJu/article/details/127939297
InnoDB数据字典（中）
https://www.modb.pro/db/58691
日志文件在/var/lib/mysql/
lock_rec_lock_fast lock0lock.cc
Lock_iter::for_each在lock0priv.h
lock_t *for_each
lock_t *first
lock_t *advance
RecID在lock0priv.h
matches(const lock_t *lock)在lock0priv.ic
创建锁
lock_rec_lock lock0lock.cc
RecLock::create
RecLock::lock_alloc
lock_rec_get_first_on_page
通过其所在的页得到space id、page number通过lock_rec_fold函数得出key值
通过key查找哈希表,然后遍历哈希链表,找到页id相同的第一个锁
只有当同一个事务锁住了页面的所有记录，并且锁模式相同，才可能保证一个页面只有一把锁
即通过key找到的锁都是同一页面的锁,但由于事务或锁模式的不同,导致有多个锁形成链表
/** Gets the first struct in a hash chain, NULL if none. */
#define HASH_GET_FIRST(TABLE, HASH_VAL) \
(hash_get_nth_cell(TABLE, HASH_VAL)->node)
/** Gets the next struct in a hash chain, NULL if none. */
#define HASH_GET_NEXT(NAME, DATA) ((DATA)->NAME)
lock_rec_lock_slow
lock_rec_other_has_conflicting
lock_rec_has_to_wait
lock_rec_get_rec_not_gap
lock_rec_add_to_queue
add_to_waitq
当上层函数接收到 DB_LOCK_WAIT 错误时通常会调用 lock_wait_suspend_thread 等待行锁被授予
ib_handle_errors
lock_wait_suspend_thread
os_event_wait
行锁加锁流程:
1.首先判断能否快速加锁,如果锁不存在则创建锁,否则看能否加锁,加锁的条件如下:
(1)确保只有一个事务锁
(2)确保该事务锁与申请的事务是同一个.
(3)确保该事务锁类型相同
(4)并且该事务锁的 bitmap 能够记录当前的 heap no
2.如不能快速加锁,则走慢速加锁
3.
4.判断是否有锁冲突,根据space_id,page_id,heap_no找到对应的行锁,然后判断锁是否需要等待(利用锁兼容数组判断)
5.如果需要等待,则放在等待的队列头尾
6.如果不需要等待,则放在队列头部,无论是否需要等待,只要锁类型不同就会创建一个新的锁结构
select加锁:
row_search_mvcc
btr_pcur_open_with_no_init
sel_set_rec_lock
/*------ sel_set_rec_lock begin -------/
lock_clust_rec_read_check_and_lock
lock_rec_lock
lock_rec_lock_fast or lock_rec_lock_slow
/*------ sel_set_rec_lock end -------/
/*------- sec_rec_lock begin ---------/
lock_sec_rec_read_check_and_lock
/*------- sec_rec_lock end ---------/
加锁:
row_search_mvcc
sel_set_rec_lock (row/row0sel.cc 5260)
lock_sec_rec_read_check_and_lock(row/row0sel.cc 1198)
lock_rec_lock(lock/lock0lock.cc 5687)
lock_rec_lock_slow(lock/lock0lock.cc 1759)
row_mysql_handle_errors (row/row0sel.cc 5967)
lock_wait_suspend_thread
lock_wait_table_reserve_slot
os_event_wait
m_set == false signal_count == reset_sig_count == 1
释放锁:
lock_trx_release_locks
lock_release /* 释放事务锁: trx->lock.trx_locks 记录当前事务所有持有的 lock. */
lock_rec_dequeue_from_page
lock_rec_grant
lock_rec_grant_by_heap_no
lock_grant
lock_reset_wait_and_release_thread_if_suspended
lock_wait_release_thread_if_suspended
os_event_set
条件变量：为什么要与互斥锁配套使用?
https://www.cnblogs.com/lxy-xf/p/11172912.html
https://www.cnblogs.com/zyb993963526/p/16154296.html
https://zhuanlan.zhihu.com/p/454841304
互斥锁:
innobase/os/os0event.cc
thr和trx关联
thr = que_fork_get_first_thr(prebuilt->sel_graph);
que_thr_move_to_run_state_for_mysql(thr, trx);
获取sel_graph:
row_prebuild_sel_graph(prebuilt) (row/row0sel.cc 4569);
pars_complete_graph_for_exec
que_fork_create
que_thr_create
que_node_get_parents
slot,thr相关
lock_rec_lock_slow
RecLock rec_lock(thr, index, block, heap_no, mode);
RecLock::add_to_waitq
RecLock::set_wait_state
que_thr_stop(que/que0que.cc)
trx_t *trx = thr_get_trx(thr);
trx->lock.wait_thr = thr;
lock_wait_suspend_thread
slot->thr = thr;
slot->thr->slot = slot;
slot->event = os_event_create();
os_event_reset(slot->event);
que_thr_t *thr = que_thr_end_lock_wait(lock->trx);
que_thr_t *const thr = trx->lock.wait_thr;
os_event_set(thr->slot->event);
m_set == false 释放锁
os_event包含互斥锁和条件变量
insert加锁:
mysql_execute_command
Sql_cmd_dml::execute
row_insert_for_mysql
/*--------对于主键索引----------/
row_ins_clust_index_entry
row_ins_clust_index_entry_low
btr_pcur_open
row_ins_duplicate_error_in_clust(加S LOCK或 X LOCK,判断是否有delete mark)
row_ins_clust_index_entry_by_modify(有delete mark,转为update)
/*--------对于主键索引----------/
btr_cur_optimistic_insert
btr_cur_ins_lock_and_undo
lock_rec_insert_check_and_lock
lock_rec_other_has_conflicting
Lock_iter::for_each
lock_rec_has_to_wait
RecLock::create
RecLock::lock_alloc
lock_rec_set_nth_bit
update加锁:
row_upd_clust_step
row_upd_clust_rec
聚集索引：row_upd->row_upd_clust_step->row_upd_del_mark_clust_rec->btr_cur_del_mark_set_clust_rec->lock_clust_rec_modify_check_and_lock
二级索引:
row_upd->row_upd_sec_step->row_upd_sec_index_entry->btr_cur_del_mark_set_sec_rec->lock_sec_rec_modify_check_and_lock
->lock_rec_lock
mysql行锁维护以页的粒度原因是,若每行都一个锁结构,如果锁住几千行,则创建锁的内存和CPU开销比较大,若每个表一个锁结构,
如表的数据有千万行,则bitmap占的内存比较大。但页的粒度也是时间换空间,因为修改锁结构的时候需要加锁, 且bitmap是以页为粒度进行管理,如rec_lock.create(trx),会先加锁然后申请获得lock结构,
select行锁加锁的流程:
select * from t where id >= 10 and id <= 20 for update;
row_search_mvcc
sel_set_rec_lock
lock_clust_rec_read_check_and_lock
lock_rec_lock
lock_rec_lock_fast
RecLock::create
/*通过btr cur获得page cursor*/
btr_cur_get_page_cur
将第一行记录转换为 MySQL 格式（Innodb 层）
row_sel_store_mysql_rec
auto offsets = rec_get_offsets();
ulint rec_f_len;
rec_get_nth_field(rec,offsets,1,&rec_f_len);
输出
rem0rec.cc
rec_print_comp
lock_rec_print
表锁:lock_table (lock0lock.cc 3549)
安装mysql:
Linux CentOS-8 源码编译安装MySQL-8.0.23 图文教程
https://blog.csdn.net/qq_43515054/article/details/116190750
在Linux使用GDB调式MySQL 5.7
https://www.jianshu.com/p/3e6a3b906682
Can't connect to local MySQL server through socket '/tmp/mysql.sock'
https://www.jb51.net/article/174244.htm
升级gcc
https://www.zyku.net/centos/12049.html
安装cmake
https://blog.csdn.net/qq_34935373/article/details/90266958
https://cmake.org/files/v3.5/
--gdb 参数允许你随时 Ctrl+C 的方式中断 mysqld 进程
https://www.xujun.org/note-143253.html
select * from performance_schema.data_locks\G;
A temporary password is generated for root@localhost: R6q5HlcWek-v
A temporary password is generated for root@localhost: !/iC*ttyq9ur
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'
./mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/lib/mysql/localhost.err --pid-file=/usr/local/mysql/data/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock
set args --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/lib/mysql/localhost.err --pid-file=/usr/local/mysql/data/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock
gdb --args ./mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/lib/mysql/localhost.err --pid-file=/usr/local/mysql/data/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock --gdb
./mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/lib/mysql/localhost.err --pid-file=/usr/local/mysql/data/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock --debug --console &
cmake .. -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_SSL=yes -DENABLED_LOCAL_INFILE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DWITH_DEBUG=1 -DWITH_BOOST=/home/xjh/boost_1_73_0/
select * from t where id >= 5 and id < 15 for update;
加锁的范围
5 行锁
10 行锁加间隙锁
15 间隙锁
select * from t where c >= 5 and c < 15 for update;
过程:
row_search_mvcc
加行锁:row_sel_get_clust_rec_for_mysql => Row_sel_get_clust_rec_for_mysql::operator()
=> lock_clust_rec_read_check_and_lock
row0sel.c:5500:row_sel_get_clust_rec_for_mysql
row0sel.c:3335:lock_clust_rec_read_check_and_lock
3829:row_search_idx_cond_check
innobase_index_cond
sel_set_rec_lock
lock_sec_rec_read_check_and_lock
btr_page_create
block = btr_pcur_get_block(pcur);
btr_pcur_get_block(prebuilt->clust_pcur) (lock_clust_rec_read_check_and_lock)
1552不能rec_print
std::ostream &os = std::cout;
rec_print(os,rec,rec_get_info_bits(rec,rec_offs_comp(offsets)),offsets);
rec_print_new(
file, rec,
rec_get_offsets(rec, index, offsets_, ULINT_UNDEFINED, &heap));
二进制转换:
https://tool.oschina.net/hexconvert/
插入:
LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION;
更改:
LOCK_X | LOCK_GAP
lock_get_mode_str
打印:
trx_get_id_for_print(trx)
lock_rec_print
数据格式:
rec_get_nth_field
rec_print_comp
/* Get the base address of offsets. The extra_size is stored at
this position, and following positions hold the end offsets of
the fields. */
#define rec_offs_base(offsets) (offsets + REC_OFFS_HEADER_SIZE)
rec_offs_base(offsets)[0] will be set
to the extra size (if REC_OFFS_COMPACT is set, the record is in the
new format; if REC_OFFS_EXTERNAL is set, the record contains externally
stored columns), and rec_offs_base(offsets)[1..n_fields] will be set to
offsets past the end of fields 0..n_fields, or to the beginning of
fields 1..n_fields+1. When the high-order bit of the offset at [i+1]
is set (REC_OFFS_SQL_NULL), the field i is NULL. When the second
high-order bit of the offset at [i+1] is set (REC_OFFS_EXTERNAL), the
field i is being stored externally.
rec_offs_nth_default
return (rec_offs_base(offsets)[1 + n] & REC_OFFS_DEFAULT);
#define rec_get_nth_field(rec, offsets, n, len) \
((rec) + rec_get_nth_field_offs(offsets, n, len))
dict0mem.h
dict_mem_index_create
dict_mem_fill_index_struct
加载index:
rec_offs_init
rec_init_offsets
rec_init_offsets_comp_ordinary
index->get_field
row_search_mvcc
btr_pcur_open_with_no_init
btr_cur_search_to_nth_level
row_sel_store_mysql_rec
row_sel_store_mysql_field
row_sel_store_mysql_field_func
rec_get_nth_field_instant
row_sel_field_store_in_mysql_format
rec_init_offsets_comp_ordinary
dict_table_t,dict_index_t在dict0mem.h
b rem/rec.cc:67
row0sel.cc row_search_mvcc 4459
handler/ha_innodb.cc index_read 9868
handler/ha_innodb.cc index_first
handler/ha_innodb.cc rnd_next
handler::ha_rnd_next
dict_index_t *index = m_prebuilt->index;
m_prebuilt->index->table_name
row_create_prebuilt
ha_innobase::open
row_create_prebuilt
dd_open_table_one
dd_fill_dict_index
dd_fill_one_dict_index
dict_index_add_col
dict_mem_index_create
b ha_innobase::change_active_index
b ha_innobase::ft_read
b ha_innobase::records
b ha_innobase::check
dict_index_t *innobase_index_lookup
ha_innobase::innobase_get_index
ha_innobase::change_active_index
ha_innobase::rnd_init
handler::ha_rnd_init
innobase_build_index_translation
handler::ha_open (table = table_arg)
ha_innobase::open
dd_open_table
dict_load_table_one
dict_load_table
m_form->s->fields
bool open_table
get_table_share_with_discover
get_table_share
alloc_table_share
Dictionary_client::acquire
Shared_dictionary_cache::get
Shared_dictionary_cache::get_uncache
Storage_adapter::get (cache/storage_adapter.cc)
trx.otx.open_tables()
Raw_table::find_record
ha_index_read_idx_map
open_table_def
// Fill the TABLE_SHARE with details.
select * from INFORMATION_SCHEMA.INNODB_TABLES where name='test/t';
select * from INFORMATION_SCHEMA.INNODB_COLUMNS where TABLE_ID=1065;
词法分析和语法分析:
TiDB 源码阅读系列文章（五）TiDB SQL Parser 的实现_tidb语法解析器_每天读点书学堂的博客-CSDN博客