高性能mysql读书笔记

1.mysql逻辑结构

第一层：连接/线程处理

作用：连接处理、授权认证、安全等

第二层：查询解析、分析、优化、缓存以及所有的内置函数

第三层：存储引擎。存储引擎负责mysql中数据的存储和提取，类似linux下的文件系统。server通过API与存储引擎进行通信

1.1连接管理和安全性

每个连接查询只能轮流在某个cpu核心或cpu中单独的线程中执行（并不需要为每个新建的连接创建或者销毁线程，服务器会缓存线程。即缓冲池插件）

1.2优化和执行

mysql会解析查询并创建内部数据结构，然后对其进行优化（包括重写查询，决定表的读取顺序，选择合适的索引）。用户可以通过hint关键字影响优化器的决策过程。

select语句，在解析查询之前，服务器会先检查查询缓存（query cache），如果能在其中找到对应的查询，服务器就不必执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集

2.并发控制

并发：多个查询需要在同一时刻修改同一数据

2.1读写锁

在处理并发读或者写时，可以通过实现共享锁(读锁)和排他锁(写锁)的组合来解决并发控制

读锁：共享的，相互不阻塞。多个客户端在同一时刻可以同时读取同一资源，而互不干扰

写锁：排他的，一个写锁会阻塞其他的写锁和读锁

2.2锁粒度

提高共享资源并发性的方式：只对修改的数据片进行精确的锁定，锁定的数据量越少，则系统的并发程度越高。

锁的各种操作(获得锁，检查锁是否解除，释放锁等)都会增加系统的开销

锁策略：在锁的开销和数据安全性之间寻求平衡。

表锁：会锁定整张表。一个用户在对表进行写操作前，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁后，其他读取的用户才能获得读锁，读锁之间是不能相互阻塞的。

写锁比读锁有更高的优先级，所以写锁请求可能被插入到读锁队列的前面

服务器会为alter table之类的语句使用表锁

行级锁：最大程度的支持并发处理

行级锁只在引擎层实现，而服务器层没有实现。

3.事务

事务就是一组原子性的SQL查询(或一个独立的工作单元)。即事务内的语句，要么全部执行成功，要么全部执行失败。

ACID：

原子性(atomicity)：一个事务是一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。

一致性(consistency):：总是从一个一致性状态转换到另一个一致性的状态

隔离性(isolation)：一个事务所做的修改在最终提交之前，对其他事务是不可见的

持久性(durability)：一旦事务提交，则其所做的修改就会永久保存到数据库中

3.1隔离级别

较低级别的隔离可以执行更高的并发，系统的开销也更低

read uncommitted(未提交读)：事务中的修改，即使没有提交，对其他事务也都是可见的。可以读取为提交的数据被称为脏读

read committed(提交读)：一个事务开始时，只能看见已经提交的事务所做的修改。一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的，这就叫不可重复读

repeatable read(可重复读)：在同一个事务中多次读取同样记录的结果是一致的。无法解决幻读：当某个事物在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该列范围的记录时，会产生幻行。innodb通过MVCC来解决幻读。

serializable(可串行化)：强制事务串行执行即在读取每一行数据都加锁，所以可能导致大量的超时和锁争用的问题。

3.2死锁

死锁：两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。

多个事务试图以不同的顺序锁定资源时，可能产生死锁。多个事务同时锁定同一个资源时，会产生死锁。

3.3事务日志

事务日志：存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。

事务日志采用追加的方式，在磁盘上是顺序IO

预写式日志(write-ahead logging)：事务日志持久化以后，内存中被修改的数据在后台刷回磁盘

3.4mysql中的事务

mysql有两种事务型的存储引擎：innodb和NDB cluster

自动提交(autocommit)：mysql默认采用自动提交模式即不是显示地开始一个事务，则每个查询都被当作一个事务执行提交操作。

事务是由引擎层实现的。

隐式和显式锁定：innodb采用的是两阶段锁定协议。

在事务执行过程中，锁只有在执行commit或rollback时才会释放，且所有的锁在同一时刻被释放。innodb会根据隔离级别在需要的时候自动加锁。

innodb通过特定的语句进行显示锁定：select ... lock in share mode ; select ... for update

mysql 也支持lock tables和unlock tables，这是在服务器层实现的，和存储引擎无关。

不管使用什么存储引擎和在任何时候都不要显式地执行lock tables；

4.多版本并发控制(MVCC)：mvcc可以理解为一个行锁的变种，但是在很多情况下避免了加锁操作，所以开销更低。实现了大都非阻塞的读操作，写操作也只锁定必要的行。

mvcc的实现是通过保存数据在某个时间点的快照来实现的。

innodb通过在每行记录后面保存两个隐藏的列来实现。一个列保存了行的创建时间，一个列保存了行过期时间(或删除时间)。存储的并不是实际的时间值，而是系统版本号。每开始一个新的事物，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询每行记录的版本号进行比较。

在RR下MVCC具体操作：

select

innodb会根据以下两个条件检查每行记录：a.innodb只查找版本早于当前事务版本的数据行(行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或修改过的。b.行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

insert

innodb为新插入的每一行保存当前系统版本号作为行版本号

delete

innodb为删除的每一行保存当前系统版本号作为行删除标识

update

innodb为i插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

MVCC只在RC和RR两个隔离级别下工作

mysql存储引擎

不同的存储引擎保存数据和索引方式不同，但表的定义则是在mysql服务层处理的。

innodb的数据存储在表空间中，表空间由一系列的数据文件组成。innodb将每张表的数据和索引单独放在文件中。

innodb采用MVCC来支持高并发，且实现了四个隔离级别，默认是RR，通过了next-gap locking防止了幻读的出现。（间隙锁不仅仅锁定了查询涉及的行，还会对索引中的间隙进行锁定）

innodb表是基于聚簇索引建立的。聚簇索引对主键查询有很高的性能。主键应该尽可能的小（因为二级索引中必须包含主键列）。innodb的存储格式是平台独立的，可以将数据和索引文件从interl平台复制到其他平台

innodb从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建hash索引来加速读操作的自适应哈希索引（adaptive hash index），以及加速插入操作的插入缓冲区（insert buffer）。

转换表的存储引擎的三种方法：

1.alter table

alter table table_name engine=; (适用于任何存储引擎)，执行时间较长，mysql会按行将数据从原表复制到一张新的表中，复制期间会消耗系统的所有IO，同时对原表加读锁。

注：转换表的存储引擎，将会失去和原引擎相关的所有特性

2.导入导出

使用mysqldump将数据导出到文件，修改文件中create table语句的存储引擎，

注意：同时修改表名(同一个数据库中不能存在相同的表名)，且mysqldump会默认在create table前加drop table语句。

3.创建与查询

创建一个新的存储引擎表，然后利用insert ... select 语句来导数据

create table table_name like sou_table_name;

alter table table_name engine=;

insert into table_name select * for sou_table_name;

数据量不大可直接处理；太大则分批处理，针对每一段数据执行事务提交操作，避免大事务产生过多的undo。

start transaction;

insert into table_name select * from sou_table_name where id between x and y;

commit;

如有需要，可以在执行过程中对原表加锁，来确保新表和旧表的数据一致。

译者介绍：家华，从事mysqlDBA的工作，记录自己对mysql的一些总结