- 论坛徽章:
- 0
|
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 存储引擎源代码的编译和调试
|
|