读书笔记《MySQL技术内幕InnoDB存储引擎》_MySQL

缘由

在微博上看到李嘉诚自述的视频中有这么一句话，大意是：我很喜欢读书，我通常读完一本书，把它记到脑子里，再去换另一本书。当时我突有感想，这些年工作，买过的书也不少，有80余本，基本上每本都是经典的好书，也算是有点收藏的味道吧。但是很多书我都是翻一翻，满足自己对某一方面知识的渴望，但自己真的能记在脑力里的却不多，于是在2012年的年尾，伴随着自己的失业，我也打算好好的选择一些书继续阅读，争取读完了，能记住一些，再换下一本。

状态

首读 —— 《MySQL技术内幕 InnoDB存储引擎》 At 2012/12/20

前言

我不是DBA，我是一名开发者，所以站在开发者的角度来读这本书对自己还是有不少收获的，至少以后在项目中设计和使用数据库的过程中，可以考虑到如何更好的和DBA进行有效的沟通。

获取的知识

InnoDB存储引擎 master thread 的问题？

InnoDB的主线程的代码，在每秒执行的任务中：存在固定的只刷新100个脏页到磁盘、合并20个插入缓冲。在写密集的App中，每秒中可以能产生大于100个的脏页，或是产生大于20个插入缓冲，此时的master thread似乎会忙不过来，或者说它总是做得很慢。即使磁盘能在1秒内处理多于100个页的写入和20个插入缓冲的合并，由于hard coding（硬编码）master thread也只会选择刷新100个脏页和合并20个插入缓冲。同时，当发生宕机需要恢复时，由于很多数据还没有刷新回磁盘，所以可能会导致恢复需要很快的时间，尤其是对于insert buffer。

解决办法

InnoDB Plugin提供了一个参数，用来表示磁盘IO的吞吐量，参数为 innodb_io_capacity，默认值为200。对于刷新到磁盘的数量，会按照 innodb_io_capacity的百分比来刷新相对数量的页。规则如下：

　　* 在合并插入缓冲时，合并插入缓冲的数量为 innodb_io_capacity 数值的5%。

　　* 在从缓冲区刷新脏页时，刷新脏页的数量为 innodb_io_capacity。

如果你使用了SSD类的磁盘，或者将几块磁盘做了RAID，当你的存储拥有更高的IO速度时，完全可以将 innodb_io_capacity 的值调得再高点，知道符合你的磁盘IO的吞吐量为止。

慢查询日志

MySQL允许用户通过 long_query_time 参数来设置，默认值是10，代表10秒。默认情况下，MySQL数据库并不启动慢查询日志，需要我们手工将这个参数（log_slow_queries）设为ON，然后启动。

* 注意1

当设置了long_query_time后，MySQL数据库会记录运行时间超过该值的所有SQL语句，但对于运行时间正好等于long_query_time的情况，并不会被记录下。

* 注意2

从MySQL5.1开始，long_query_time开始以微秒记录SQL语句运行时间。

另一个和慢查询日志有关的参数是 log_queries_not_using_indexes，如果运行的SQL语句没有使用索引，则MySQL数据库同样会将这条SQL语句记录到慢查询日志文件。

使用 mysqldumpslow 命令可以分析慢查询日志文件

mysqldumpslow nh122-190-slow.log

MySQL5.1开始可以将慢查询的日志记录放入一张表中，这使我们的查询更加直观。慢查询表在MySQL数据库中，名为slow_log。

参数log_output指定了慢查询输出的格式，默认为FILE，你可以将它设为TABLE，然后就可以查询mysql数据库的slow_log表了。

set global log_output='TABLE';

分区表

MySQL 5.1 后添加对表分区的支持，当然支持的分区类型为水平分区（一表中不同行的记录分配到不同的物理文件中）。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。

show variables like '%partition%'/G;

MySQL目前支持的分区类型有：

　　* RANGE分区：行数据基于属于一个给定连续区间的列值放入分区。MySQL5.5开始支持RANGE COLUMNS的分区。

　　* LIST分区：和RANGE分区类似，只是LIST分区面向的是离散的值。MySQL5.5开始支持LIST COLUMNS的分区。

　　* HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。

　　* KEY分区：根据MySQL数据库提供的哈希函数来进行分区。

* 不论创建何种类型的分区，如果表中存在主键或者是唯一索引时，分区别必须是唯一索引的一个组成部分。唯一索引可以是允许NULL值的，并且分区列只要是唯一索引的一个组成部分，不需要整个唯一索引列都是分区列。

* 当建表时没有指定主键，唯一索引时，可以指定任何一个列为分区列。

B+树索引

B+树索引其本质就是B+树在数据库中的实现，但是B+的索引在数据库中有一个特定就是高扇出性，因此在数据库中，B+树的高度一般都在2-3层，也就是对于查询某一键值的行记录，最多只需要2到3次IO，而对于当前的硬盘速度，2-3次IO也就意味着查询时间只需要0.02-0.03秒。

什么时候使用B+树索引

* 访问高选择性字段并从表中取出很少一部分行时，对这个字段添加B+树索引是非常有必要的。

聚集索引和辅助索引

InnoDB存储引擎是索引组织表，即表中数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一颗B+树，并且叶节点中存放着整张表的行记录数据，因此也让聚集索引的叶节点成为数据页。

每张表只能拥有一个聚集索引。

辅助索引（非聚集索引），叶级别不包含行的全部数据。叶节点除了包含键值以外，每个叶级别中的索引行还包含了一个书签，该书签用来告诉InnoDB存储引擎。

事务的隐式提交

不好的事务习惯

* 在循环中提交

create procedure load1(count int unsigned)begindeclare s int unsigned default 1;declare c char(80) default repreat('a',80);while s <= count doinsert into t1 select NULL,c;commit;set s = s+1;end while;end;

* 使用自动提交

自动提交并不是好习惯，因为这对于初级DBA容易犯错，另外对于一些开发人员可能产生错误的理解，如我们在上面提到的循环提交问题。MySQL数据库默认设置使用自动提交。可以使用如下语句来改变当然自动提交的方式

set autocommit=0;

* 使用自动回滚

create procedure sp_auto_rollback_demo()begindeclare exit handler for sqlexception rollback;start transaction;insert into b select 1;insert into c select 2;insert into b select 1;insert into b select 3;commit;end;