mysql技术innodb分区_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 存储引擎源代码的编译和调试

阅读(4151) | 评论(0) | 转发(0) |