原书:《mysql技术内幕:InnoDB引擎》
源码:https://github.com/mysql/mysql-server/tree/8.0/storage/innobase
======================================================
查看mysql使用配置:mysql --help | grep cnf 以最后一个为准
查看mysql数据文件目录: show variables like '%datadir%'; //也可以查看*.cnf中的datadir字段的值
查看状态:show session/global status like '%uptime%';
查看表状态:show table status; // 包含row_format;
<=> select * from information_schema.tables;
// <=> select * from GLOBAL_VARIABLES where variable_name like '%dir%';
// select @@session.autocommit ;
// 修改变量 set [ global | session ] autocommit = true;
// 修改变量 set @@global.autocommit = true;
查看log路径:show variables like '%log_err%';
查看base路径:show variables like '%dir%'; --datadir/basedir
查看binlog: mysqlbinlog /usr/local/var/mysql/binlog.000001
查看Innodb状态(包括innodb线程数等): show engine innodb status;
binlog有三种:STATMENT/ROW/MIXED
innodb存储文件:.ibd
myisam存储文件 .MYI, .MYD
redo log: ib_logfile0 ib_logfile1
=======================================================
表空间数据文件 /usr/local/var/mysql/ibdata1 存放了各个表的信息。如果启用了innodb_file_per_table将会为每个表生成一个表空间。但是每个表空间里只存储了索引、数据和插入缓冲,但是撤销、系统事务、二次写缓冲还是存在共享的表空间。
表数据文件: xxx.ibd 存放了数据,key等信息
show engines; <=> select * from information_schema.engines;查看mysql支持的引擎信息
delimiter |
create procedure recreate(num int) begin declare idx int default 1; truncate table inc; while idx <= num do insert into inc values(idx, now()); set idx = idx + 1; end while; end;|
delimiter ;
约束:
select * from information_schema.table_constraints;
select * from information_schema.referential_constraints;
分区:
alter table p partition by hash(k) partitions 4;
锁:
select 也可以加锁:
select * from t where k < 3 for update; --对k小于3的行加上排它锁;
select * from t where k < 3 lock in share mode; --对k<3的行加上共享锁;
select * from information_schema.innodb_locks; --查看正在使用的锁;
事务:
select * from information_schema.innodb_trx; --正在执行的事务
代码解析:
1. Innodb每个页有16K(0x4000)(可以使用show variables like 'innodb_page_size' ;查看),每个页类型定义参见:https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/include/fil0fil.h#L964 .页的结构如下:
mysql没有定义页这个结构,是直接通过offset覆盖bytes,一些创建的函数:
https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/page/page0page.cc#L375 page_create()
https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/include/mach0data.ic#L53 mach_write_to_2()
file header offset: https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/include/fil0types.h#L42
page header offset: https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/include/page0types.h#L42