MySQL innodb碎碎念

以下内容是在看书（《深入MySQL innodb存储引擎》）的过程中随手敲的，很混乱，做个记录

InnoDB
后台线程：
IO线程（由 innodb_file_io_threads参数控制）、1个master thread、一个锁监控线程、1个错误监控线程
show engine innodb status\G


I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)


mysql> show variables like 'innodb_%io_threads'\G
*************************** 1. row ***************************
Variable_name: innodb_read_io_threads
Value: 4
*************************** 2. row ***************************
Variable_name: innodb_write_io_threads
Value: 4
2 rows in set (0.00 sec)


——————————————————————————
内存：
buffer pool、redo log buffer 以及 额外的内存池
参数：innodb_buffer_pool_size、 innodb_log_buffer_size


数据库文件按页读取（16K）到缓冲池，LRU替换
索引页、数据页、undo页、插入缓冲……
对脏页，以一定的频率修改。


-----------------------------------------------
1.master thread：定时进行loop
删除无用undo页、合并插入缓冲、刷新脏页……


2.插入缓冲：和数据页一样，是物理页的一个组成部分
需要满足两个条件：索引是辅助索引、索引不是唯一的
当非聚集索引页不在缓冲池中时，则先放入到一个插入缓冲区中
写密集的情况下，插入缓冲会占用过多的缓冲池内存，默认最大为1/2
在ibuf0ibuf.c文件中进行了定义：
/* Buffer pool size per the maximum insert buffer size */
#define IBUF_POOL_SIZE_PER_MAX_SIZE 2
我们可以对上述值进行修改，来调整插入缓冲的大小


3.两次写
redo log 记录的是对页的物理操作，比如偏移量800.写入'aaa'操作。。


double write由两部分组成：内存中的double write buffer(2MB)和物理磁盘上的共享表空间中连续的128个页（2MB）


需要刷新脏页时
->memcpy到doublewrite buffer
->分两次，每次写1MB到共享表空间的物理磁盘上，并马上调用fsync，同步磁盘
->再将double write buffer中的页写入到各个表空间文件中
参数skip_innodb_doublewrite禁止两次写功能


4.自适应哈希索引：
通过缓冲池的B+树构造而来，建造速度快，不需要将整个表建哈希索引
只能搜索等值查询，即 select....where a = 'xx'
参数：innodb_adaptive_hash_index 禁用或启动此特性，默认为开启


5.
mysql> select @@innodb_fast_shutdown
-> ;
+------------------------+
| @@innodb_fast_shutdown |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)
值可以为0、1、2




innodb_force_recovery


6. innodb plugin
可以看做一个新版本的存储引擎，如果想使用，仅需要在配置文件中做设置
--------------------------------------
SET global|session
autocommit只能在session改


错误日志
慢查询日志：设置一个阈值,将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中，
默认为关闭：
mysql> show variables like 'log_slow_%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| log_slow_queries | OFF |
+------------------+-------+
1 row in set (0.00 sec)




如果运行的SQL语句没有使用索引，也会记录到慢查询日志文件中。
mysql> show variables like 'log_que_%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set (0.01 sec)


--------------------
binlog
事务未提交时，二进制日志被记录到一个缓存中
提交时，写入文件
sync_binlog=[N],每写缓冲N次就同步到磁盘


>
问题：当sync_binlog=1时，提交前写入了二进制，但事务commit失败。二进制日志无法回滚
解决：设置innodb_support_xa=1,能够确保二进制日志和INNODB存储引擎数据文件的同步
<
slave同时记录二进制文件 参数：log-slave-update


rand、uuid、时间戳、触发器等可能会导致主从不一致


innodb 默认隔离级别：repeatable read
ROW模式：记录修改的行
STATEMENT：SQL
MIXED：默认为STATEMENT,但当
1.含有不确定函数时，如UUID、CURRENT_USER等函数时，
2.INSERT DELAY
3.UDF
4.临时表
使用ROW模式


可以基于session模式进行修改
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT |
+-----------------+
1 row in set (0.00 sec)


mysql> set session binlog_format='ROW';
Query OK, 0 rows affected (0.00 sec)


mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW |
+-----------------+
1 row in set (0.00 sec)


——————————————————
mysql pid文件：记录自己的进程ID
——————————————————
每个存储引擎都有一个以frm为后缀的文件：记录表结构定义（包括视图的定义，为文本文件）


innodb默认存储文件：
ibdata1--默认表空间文件 （innodb_data_file_path来设置）,所有给予innodb存储引擎的表的数据都会记录到该文件内。
innodb_file_per_table参数：为每个表单独产生一个表空间，以ibd作为后缀，仅存储表的数据、索引和插入缓冲等信息，其他
undo信息、系统事务信息、二次写缓冲等信息还是村在共享表空间中。


>
mysql中调用shell命令：system ....
<
_________________________________
redo log文件
默认：ib_logfile0、ib_logfile1 ，可以有多个日志组，并被部署到不同的磁盘上
每个组中的日志被循环使用
记录的内容：
每个页更改的物理情况
与binlog不同，是在事务进行的过程中，不断有redo entry写入到redo log中
redo log结构：
spaceid、pageno、opcode、data
先写入日志缓冲中，
1.再由master thread定时将日志缓冲写入到磁盘的redo log中。
2.若设置了innodb_flush_log_at_trx_commit参数（0、1、2）
--0:由主线程刷新
--1:commit时将日志缓冲同步写入到磁盘
--2:将redo log异步写入到磁盘（不能完全保证commit时肯定会写入到redo log文件中）


表空间由分散的页和段组成：


区：64个页
页：16KB，磁盘管理的最小单位，每页存放的行记录范围 16B/2 - 200
-------------------
Compact行记录：
变长字段长度列表，按照列的顺序逆序放置，小于255字节，用1字节表示，大于255个字节，用2个字节表示，因此变长字段的长度最大不超过2个字节
NULL标志位：例如NULL值为06->00000110，则第2列和第3列为NULL，后续数据不会记录值；
记录头信息，5个字节
1
1
1 --deleted_flag，该行是否已被删除
1 --min_rec_flag，如果该记录是预先被定义为最小的记录，则为1
4 --n_owned，该记录拥有的记录数
13--heap_no,索引堆中该条记录的排序记录
3 --record_type,000=普通，001=B+树节点指针，010=Infimum 011=Superemum 1XX=保留
16--next_recorder，页中下一条记录的相对位置
列1数据、列2数据…… NULL值是不占空间的


当一行各个列的长度总和超过65535时将无法创建成功表


>
linux下打开二进制：hexdump -C -v mytest.ibd > mytest.txt
<
----------------------
Redundant行记录格式：5.0版本之前
………………………………………………………
行溢出数据，
Innodb的表是以B+组织的，每个页需要保证至少能够存放两条记录，长度阈值为8098（？？）
溢出时，实际数据保存在BLOB页中，数据页仅保存前768个字节
__________________
innodb plugin的新文件格式:Barracuda （旧的被称为Antelope）
两种新的记录格式：
Compressed：zlib算法进行压缩
Dynamic
对存放BLOB的数据，在数据页仅存放20字节（之前为768），实际数据都存放在BLOB页中
char类型 CHAR(N)，N实际上是字符的长度，而非字节数，因此对不同的编码可能是变长的。
因此。对于多字节编码的CHAR类型，在内部被视为变长字符
------------------------------------------
InnoDB数据页：B-TREE NODE，存放行的实际数据
数据页的结构：
FILE HEADER：38字节
PAGE HEADER：56字节，页的状态信息


Infimum records:记录比该页中任何主键都要小的值
Supremum Records：指比任何可能大的值还要大的值，
以上两个值在页创建时被建立，并且不会删除，用于限定记录的边界。
User Records:用户记录
Free Space
Page Directory：页目录，存放页的相对位置，slots，按照键顺序存放，能够利用二叉查找迅速找到记录的指针
即我们可以由B+树索引找到页，再由页内的Page directory进行二叉查找


File Trailer：记录checksum之和fil_page_lsn，8个字节
------------------------------------------------
NAME FILE FORMATS机制：解决不同版本下页结构的兼容性问题
-------------------------
如果想约束非法数据的插入或更新，不是warning而是报错，需要设置sql_mode
---------------------
视图：create view xx as ............... with check option ：只有当满足select时才被插入或更新
MySQL不支持物化视图
------------------------------------------------


分区表：
支持水平分区：即同一表中不同行的记录分配到不同的物理文件中


mysql> show variables like '%parti%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
1 row in set (0.00 sec)
分区类型：
RANGE分区：行数据基于一个给定的连续区间的列值放入分区
LIST：和RANGE类似，但面向的是离散的值
HASH：根据用户自定义的表达式的返回值来进行分区
KEY分区，根据MYSQL提交的哈嘻函数来进行分区


当表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分，若没有指定主键或唯一索引时，可以指定任何一个列作为分区列
mysql> create table t1 (col1 int not null,col2 date not null,col3 int not null, col4 int not null ,unique key (col1,col2)) partition by hash(col1) partitions 4;




mysql> create table t (id int) engine=innodb partition by range (id) ( partition p0 values less than (10), partition p1 values less than (20));
Query OK, 0 rows affected (0.01 sec)


mysql> show tables;
+-------------------+
| Tables_in_yinfeng |
+-------------------+
| mytest |
| t |
| t1 |
| test2 |
| v_x4 |
| x4 |
| x5 |
+-------------------+
7 rows in set (0.00 sec)


mysql> show create table t;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB) */ |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)


mysql> insert into t select 9;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0


mysql> insert into t select 10;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0


mysql> insert into t select 20;
ERROR 1526 (HY000): Table has no partition for value 20
mysql>
！！！
可见，当插入不在分区范围内的值时会报错
------------
当range分区为YEAR()、TO_DAYS()、TO_SECONDS()、UNIX——TIMESTAMP()时，优化器会进行优化选择


---------------------------
LIST分区：
create ........
partition by list(id)(
partition p0 values in(3,4,5)
……
)


HASH分区
hash()函数、 linear hash()


KEY分区
partition by key(id) mysql内部的hash函数，与PASSWORD类似的运算法则
以上分区皆需要整型


COLUMNS分区，支持的类型：
1.所有整型
2.日期类型DATE、DATETIME
3.字符串类型（除了BLOB和TEXT类型）
MySQL从5.5开始支持COLUMNS分区，对之前的RANGE和LIST可以用
RANGE|LIST COLUMNS 来代替
---------------------
子分区：允许在RANGE和LIST的分区上再进行HASH/KEY的子分区


——————————————————————
innodb存储引擎表中数据按照主键顺序存放，
而聚集索引则是按照主键构造B+树，聚集索引的叶节点包含行的全部数据
使用双向链表进行维护


----------------------------------------------------
redo：
redo log、innodb log buffer
LSN：日志序列号
预写日志方式（WAL），在写数据前，先写日志（innodb_flush_log_at_trx_commit=1）
事务执行时，会往日志缓冲里插入事务日志。


undo:
用于撤消事务。存放在数据库内部的一个特殊段中，称为undo段，位于共享表空间内
在提交前会产生，即使提交后，也需要等master thread来回收undo页




QPS：每秒请求数
TPS：每秒事务处理数


------------------------
MYSQL分布式事务XA
资源管理器：即数据库本身
事务管理器：连接到MYSQL的客户端，用来协调事务
分布式事务的两段式提交：
1.准备阶段
2.事务管理器告诉资源管理器执行提交或ROLLBACK
------------
在MySQL内部，也是通过XA事务来协调的，例如用START TRANSACTION开启一个本地事务，在一个事务中操作多个存储引擎，