mysql innodb内幕_MySQL技术内幕InnoDB存储引擎笔记

1.mysql配置文件的默认加载顺序

/etc/my.cnf  >  /etc/mysql/my.cnf  >  mysql安装目录/etc/my.cnf  >  ~/.my.cnf

后面读到的文件会覆盖前面的配置。

2.mysql参数修改

mysql参数分为静态参数和动态参数  在mysql运行中可以对动态参数进行修改

SET |[global|session] system_var_name = expr

SET |[@@global.|@@session.]system_var_name = expr

3.表

1)在innodb中，表都是根据主键顺序组织存放的，称为索引组织表。

2)分区表

a.range分区 行数据基于一个给定的连续区间的列值放入分区。

b.list分区 和range分区一样，只是list分区面向的是离散的值

c.hash分区 根据用户自定义表达式的返回值来进行分区，返回值不能为负数

d.key分区 根据MySQL数据库提供的散列函数进行分区

4.索引

1)InnoDB中常见的几种索引

B+树索引

全文索引

哈希索引

2)B+树索引

a.B+树索引 分为聚集索引和辅助索引

b.聚集索引是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的行记录数据

c.辅助索引 叶子节点除了包含键值外还包含一个书签，即相应行数据的聚集索引建。

通过辅助索引查找数据时，innodb引擎会遍历辅助索引 找到指向主键索引的主键(书签)，然后查找主键索引查找到完整的行数据。

3)可以通过show index from tablename 来查看索引状况

4)通过Cardinality可以看到不重复数据的预估值

5)索引的使用

联合索引 是指对表上的多个列进行索引。

覆盖索引 可以从辅助索引中直接获取到要查询的数据，不需要再查询聚集索引。

索引提示 index hint，显示告诉优化器使用哪个索引。

eg:select * from t USE INDEX(a) where a=1 and b=2; 建议使用索引

select * from t FORCE INDEX(a) where a=1 and b=2; 强制使用索引

5.锁

1)InnoDB中锁的类型

共享锁(S)

排它锁(X)

意向共享锁(IS)

意向排它锁(IX)

由于InnoDB存储引擎支持的是行级锁，因此意向锁实际上不会阻塞除全表扫描外的任何请求。

意向锁的存在是为了协调行锁和表锁的关系，支持多粒度(表锁与行锁)的锁并存。

1)意向共享锁(IS锁)：事务在请求S锁前，要先获得IS锁

2)意向排他锁(IX锁)：事务在请求X锁前，要先获得IX锁

例子：事务A修改user表的记录r，会给记录r上一把行级的排他锁(X)，同时会给user表上一把意向排他锁(IX)，这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。

2)非一致性锁定读

InnoDB通过多版本控制MVCC的方法来读取当前执行时间的数据。

即实际读取的是undo段中快照数据。

3)一致性锁定读

可以通过SELECT...FOR UPDATE来获取X锁

通过SELECT...LOCK IN SHARE MODE来获取S锁

4)行锁的3种算法

Record Lock：单个行记录上的锁 [a]

Gap Lock：间隙锁 包含下范围 不包括记录本身 (a,b)

Next-Key Lock 包含上范围 包含记录本身(c,a]

6.事物

1)ACID

原子性Atomicity

一致性Consistency

隔离性Isolation

持久性Durability

2)InnoDB支持扁平事务、带有保存点的事务、链事务、分布式事务。

3)事务日志

通过redo(重做日志)保证事务的原子性和持久性

undo保证事务的一致性 实现MVCC和事务回滚

binlog用来进行POINT-IN-TIME的恢复和主从复制replication环境的建立。

4)隐式提交的SQL语句

a.DDL语句 alert create drop truncate

b.用户权限相关 create/rename/drop user、grant、revoke、set password

c.管理语句 ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、

OPTIMIZE TABLE、REPAIR TABLE.

5)分布式事务

使用分布式事务时、mysql的隔离级别必须设置为serializable。

XA事务通常有一个或多个资源管理器(数据库)、一个事务管理器和一个应用程序组成。

使用两阶段提交方式(2PC:Two-Phrase Commit)。

第一阶段所有节点都开始准备、告诉事务管理器事务等待提交。

第二阶段 事务管理器告诉资源管理器ROLLBACK或者COMMIT

7.其他

缓存命中率计算

正常情况下命中率不能低于99%、命中率过低应该考虑内存不足问题。

Show global status like 'innodb%read%'\G;

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:发起读取请求的次数，每次读取可能需要读取多少页。

缓冲池命中率=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;