免费注册 查看新帖 |

Chinaunix

  平台 论坛 博客 文库
最近访问板块 发新帖
查看: 1356 | 回复: 0

mysql技术内幕innodb存储引擎(笔记) [复制链接]

论坛徽章:
0
发表于 2011-12-23 03:30 |显示全部楼层
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 存储引擎源代码的编译和调试




您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-6 北京市公安局海淀分局网监中心备案编号:11010802020122 niuxiaotong@pcpop.com 17352615567
未成年举报专区
中国互联网协会会员  联系我们:huangweiwei@itpub.net
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP