mysql技术内幕innodb存储引擎（笔记）

mysql技术内幕innodb存储引擎（笔记）

### 1. mysql 体系结构和存储引擎
mysql 是按 /etc/my.cnf->/etc/mysql/my.cnf->/usr/local/mysql/etc/my.cnf->~/.my.cnf
mysql 组成:
连接池组件/管理服务和工具组件/sql接口组件/查询分析器组件/优化器组件/缓冲组件/插件式存储引擎/物理文件

同一个表,使用不用的存储引擎,所占的空间大小有所不同.

常用的进程通信方式有管道、命名管道、命名字、TCP/IP套接字、Unix域名套接字
TCP/IP套接字方式是mysql在任何平台下都提供的连接方式,也是网络中使用得最多的一种方式.
命名管道和共享内存
在mysql,使用命名管道需在配置文件中启用--enable-named-pipe选项.
共享内存的,需在配置文件中添加--shared-memory; 在连接时,mysql客户端还必须使用-protocol=memory选项.


### 2. innodb存储引擎
*后台线程
默认情况下,innodb存储引擎的后台线程有7个--4个IO thread,1个master thread, 1个锁监控线程,1个错误监控线程
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)

*内存
innodb 存储引擎内存由一下几个部分组成:缓冲池 buffer pool / 重做日志缓冲池 redo log buffer 以及 额外的内存池 additional memory pool

mysql> show variables like 'innodb%size';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| innodb_additional_mem_pool_size | 67108864  |
| innodb_buffer_pool_size         | 281018368 |
| innodb_log_buffer_size          | 8388608   |
+---------------------------------+-----------+


Buffer pool size   17152
Free buffers       17088
Database pages     64
Modified db pages  0
buffer pool size 表明了一共有多少个缓冲帧(buffer frame) 每个buffer frame 为16K
free buffers 表示当前空闲的缓冲帧,database pages 表示已经使用的缓冲帧,modified db pages 表示脏页的数量


* master thread
master thread的线程优先级别最高.其内部由几个循环loop组成: 主循环 (loop)、后台循环(background loop)、刷新循环(flush loop)、暂停循环(suspend loop)。

关键特性：插入缓冲、两次写、自适应哈希索引
*插入缓冲
两个条件：索引是辅助索引、索引不是唯一的



### 3. 文件
参数文件
如果mysql在默认的数据库目录下找不到mysql架构，则启动失败，在日志中如下内容：
[error] fatal error: can't open and lock pribilege tables : table 'mysql.host' doesn't exist

日志文件
错误日志、二进制日志、慢查询日志、查询日志
用table记录慢查询日志：（查询日志也一样）general_log
mysql> show create table mysql.slow_log;
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
mysql> set global log_output='TABLE';
mysql> select count(*) from mysql.slow_log;
+----------+
| count(*) |
+----------+
|        3 |
+----------+

二进制文件的作用：恢复、复制

套接字文件
pid文件
表结构定义文件
innodb存储引擎文件


### 4. 表
* innodb 存储引擎表的类型
在innodb存储引擎中，每张表都有个主键
首先表中是否有非空的唯一索引(unique not null),如果有,则该列为主键
不符合上述条件，innodb存储引擎自动创建一个6个字节大小的之争。

* innodb 行记录格式
compact 和 redundant 两种格式
redundant 是为兼容之前的版本而保留的
show table status like ***
如：create table testtable ( *** ) engine=innodb charset=latin1 row_format=compact;

* 约束
建表时定义也可以使用alter table命令创建
如：
mysql> create table u ( id int, name varchar(20), id_card char(18), primary key(id), unique key(name));
mysql> select constraint_name,constraint_type from information_schema.table_constraints where table_name='u'\G;
*************************** 1. row ***************************
constraint_name: PRIMARY
constraint_type: PRIMARY KEY
*************************** 2. row ***************************
constraint_name: name
constraint_type: UNIQUE
2 rows in set (0.00 sec)

alter table u add unique key uk_id_card(id_card);

外键约束：
create table p ( id int, u_id int, primary key (id), foreign key (u_id) references p (id));

select * from information_schema.referential_constraints where constraint_schema='wjlcn';

mysql 设置sql_mode 用来严格审核输入的参数
如：set sql_mode='STRICT_TRANS_TABLES';

* 分区表
mysql> show variables like '%partition%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

mysql> show plugins;
+------------+----------+----------------+---------+---------+
| Name       | Status   | Type           | Library | License |
+------------+----------+----------------+---------+---------+
| binlog     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| partition  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| ARCHIVE    | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| BLACKHOLE  | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| CSV        | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| FEDERATED  | DISABLED | STORAGE ENGINE | NULL    | GPL     |
| MEMORY     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| InnoDB     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MyISAM     | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
+------------+----------+----------------+---------+---------+

mysql 数据库支持 range、list、hash、key、columns分区，并且可以使用hash或者key来进行子分区。需注意的是，分区不是总适合于OLTP应用，你应该根据自己的应

用好好规划自己的分区设计？


### 5. 索引与算法
innodb 存储引擎支持两种常见的索引，一种是B+树索引，另一种是哈希索引。


### 6. 锁
锁的类型：
共享锁 S Lock 允许事务读一行数据。
排他锁 X Lock 允许事务删除或者更新一行数据。
意向锁是表级别的锁
意向共享锁 IS Lock 事务想要获得一个表中某几行的共享锁
意向排他锁 IX Lock 事务想要获得一个表中某几行的排他锁

### 7. 事务
原子性、一致性、隔离性、持久性

事务控制语句：
start transaction|begin 显式地开启一个事务
commit
rollback
savepoint identifier 允许你在事务中创建一个保存点，一个事务中可以有多个savepoint
rollback to [savepoint] identifier
set transaction 这个语句用来设置事务的隔离级别。innodb存储引擎提供的事务隔离级别有：
read uncommitted、read committed、repeatable read、serializable
completion_type 参数设置


### 8. 备份与恢复
mysql数据库提供的大多数工具(如mysqldump、ibbackup、replication)都能很好地完成备份的工作
通过第三方的一些工具来完成，如xtrabackup、LVM快照备份等

### 9. 性能调优
*选择适合的cpu
最好支持64位
cpu是多核，可以通过修改参数innodb_read_io_threads 和 innodb_write_io_threads来增大IO的线程，这样也能更充分利用cpu的多核性能。

*内存的重要性
如何判断当前数据库的内存是否已经达到瓶颈？可以通过查看当前服务器的状态，比较物理磁盘的读取和内存读取的比例来判断缓冲池中的命中率，通常innodb存储引

擎的缓冲池的命中率不应该小于99%

innodb_buffer_pool_reads 表示从物理磁盘读取页的次数；
innodb_buffer_pool_read_ahead 预读的次数；
innodb_buffer_pool_read_ahead_evicted 预读的页，但是没有被读取就从缓冲池中被替换的页的数量，一般用来判断预读的效率；
innodb_buffer_pool_read_requests 从缓冲池中读取页的次数；
innodb_data_read 总共读入的字节数；
innodb_data_reads  发起读取请求的次数，每次读取可能需要读取多个页。

如：mysql> show global status like 'innodb%read%';
+-----------------------------------+---------+
| Variable_name                     | Value   |
+-----------------------------------+---------+
| Innodb_buffer_pool_read_ahead_rnd | 1       |
| Innodb_buffer_pool_read_ahead_seq | 0       |
| Innodb_buffer_pool_read_requests  | 4149    |
| Innodb_buffer_pool_reads          | 33      |
| Innodb_data_pending_reads         | 0       |
| Innodb_data_read                  | 3559424 |
| Innodb_data_reads                 | 47      |
| Innodb_pages_read                 | 84      |
| Innodb_rows_read                  | 129     |
+-----------------------------------+---------+

缓冲池命中率=innodb_buffer_pool_read_requests/(innodb_buffer_pool_read_requests+innodb_buffer_pool_read_ahead+innodb_buffer_pool_reads)
平均每次读取的字节数=innodb_data_read/innodb_data_reads

*硬盘对数据库性能的影响
对于数据库应用来说，RAID10是最好的选择？
*合理地设置RAID
*操作系统的选择也很重要
*不同文件系统对数据库的影响
*选择合适的基准测试工具
sysbench 和 mysql-tpcc

### 10 innodb 存储引擎源代码的编译和调试