MySQL的运行模式及一些特性，引擎、事务、并发控制、优化总结

一 MySQL总体架构

 

　　上图是《高性能MySQL》中对MySQL总体架构的描述，客户端对服务端的连接有很多条，有一个专门的处理组件，类似tomcat使用线程池处理请求。解析器负责解析sql语句，在这同时会访问缓存如果缓存有目标数据就直接返回。如果需要执行sql语句，还会先经过优化器重新编排执行过程（重写查询，重排查询表的顺序，选择合适的索引、优化min（）max() in()、重排where的顺序以适应左前缀原则等），优化的原则根本上只有一个：从磁盘读取的数据页（一页16K,IO的基本单位）越少越好。例如：

　　使用where语句想走索引查询，但是如果优化器认为查到的数据基本是全表就会直接走全表扫描，不走索引减少数据页的读取，也无需回表，虽然这也是性能上的优化，但是会让MySQL执行的操作在我们的意料之外，例如不走索引的查询不会对受影响的行加锁，这有时会导致一些问题。因此，有explain这个指令让我们可以知道MySQL的具体执行过程。

　　以上说的都是服务层面的，一些通用的功能，还包括了用户权限验证啊等等。最下面的则是存储引擎，负责对磁盘数据的存取。看似对磁盘的数据存取只需调用API就行，实际上MySQL在存储引擎这做了很多工作，例如事务控制啊、并发控制啊。

 二 引擎

　　目前最常用的是InnoDB引擎，据说95 %的情况下使用它就行了。主要有点有：

　　　　1. 采用聚簇索引，数据本体存在主键索引的叶子结点下，查询速度非常快。

　　　　2.还会视情况建立自适应的hash索引（根据数据的值散列运算得到地址,O(1)复杂度），hash索引虽然查询速度非常快但地址随机不适合范围查找，而且冲突多的时候表现不佳。

　　　　3. 相比MyISAM额外支持事务、支持行锁。虽然行锁不是任何情况优于表锁，表锁虽然并发量很低但是加锁开销小适合死锁率很高的情况。InnoDB二者都有，可以灵活选择。

　　　　4. InnoDB有完善的事务日志及热备份机制，可用性很强，崩溃后恢复很方便

　    MyISAM实现非常简单，功能非常有限：非聚簇索引（索引文件与数据文件单独保存，通过索引查询时总是需要回行）、不支持事务、锁只支持表锁。只适合小型项目、读操作占大都数写操作非常少的场景。

三 锁与并发控制

　　锁：MySQL的锁从模式上来说有 共享锁S（读锁）和 排他锁X（写锁），从粒度上或加锁策略上分又分为行锁与表锁。一个引擎支持行锁说明它可以一次给若干行加锁，只支持表锁的话就说明一次加锁过程要么不锁要么把全表锁住。两个事务获取同一目标（若干行或整个表）的不同模式的锁时，冲突情况如下：

　　

 

上图的√表示两个事务获取这两种锁时不会冲突，×表示这两种锁不能被两个事务同时获取，会冲突。上图内容其实总结就是：两个锁同时有‘S’，或同时有‘I’是可以同时被两个事务获取的，不会冲突。

　　MVCC多版本控制并发：由于加锁的开销比较大，导致并发量的下降，因此很多数据库都会有MVCC这个机制。这个机制简单的说是通过给每个事务分配一个版本号，这个事务修改删除等操作影响的行将被打上事务版本号存起来作为一个快照版本，通过这种做法可以在并发环境下避免很多的加锁操作同时也能保证数据库的正确性，从而大幅提高并发量。可以说MVCC是一种变种的行级锁（不是简单无脑给行加锁），当然只有支持行级锁的引擎支持。

　　具体是MySQL有一个系统版本号，每创建一个事务后就递增，并把这个版本号给新建事务，可以作为这个事务的标识。每个行有隐藏的两列，分别为创建时间、删除时间，实际中我们把这两列存放创建这一行的事务的版本号、删除这一行的版本号。进行各个操作的具体实现是：

　　　　insert： 把插入行的创建标识置为当前版本号（即当前事务版本号）。

　　　　delete： 把删除行的删除标识置为当前版本号。

　　　　update: 先新建一行，把当前版本号作为新建行的创建标识，把当前版本号作为原先行的删除标识。

　　　　select：读取数据时有两个条件  a：行的创建标识要早于或等于本事务版本号（保证不会读到后到的事务修改的数据，即解决不可重复读）；  b:行的删除标识要晚于或未定义本事务版本号（保证不会读到被之前事务执行删除操作的数据，即数据不失效）。

　　MVCC总结就是：变种的行级锁，增删改时维护行的创建标识和删除标识，读取时对这两个标识加点限制条件。实现了不加锁的情况下读取到正确的数据，少加了这么多锁，大幅提高了并发量。

四 事务与实际加锁策略

　　事务：事务的概念及ACID特性都是老生常谈了，这里总结一下‘I’隔离性的每个隔离级别下的加锁策略以及解决的问题。

 

RU级别脏读原因：此级别事务可以读到其他事务修改的且未提交的数据，如事务A将x = 1，事务B此时读到了x = 1，但是A回滚了，数据库中x肯定也不为1了，所以B的数据是脏数据。

RC级别： 此隔离级别规定只有事务提交了，数据才能被其他事务读到，自然解决了脏读。但是却没解决不可重复读的问题：事务A先读到x = 1,然后事务B修改x = 2并提交,这时事务A再读就会发现x = 2，与之前不一样了。

RR级别: 不可重复读使一个事务可能会读到后来的事务修改的数据，为了避免这种情况，有了MVCC机制，读数据时对每一行的版本做一些限制（MVCC在上一部分已总结原理）。

S级别： 这个级别下，操作表时会锁住整个表，所以事务A操作此表时，其他事务不能对这张表做任何操作，包括了插入操作，自然也没了幻读的事情。

间隙锁：间隙锁会锁住额外的行（即不止受影响的行），让其他事务没法删和改后面的行。间隙锁的作用具体例子：当事务A在修改删除 id>10的数据时，还没执行完事务B插了进来添加了10条数据id都大于10，这时事务A再恢复执行就会把事务B插进来的数据也给改/删咯。总之就是间隙锁只有在当前读（select ...for update、update、delete、insert）才会触发，锁住其他行，防止中途产生新的符合条件的数据，这样被无辜污染。

上述都是理论上各个隔离级别解决的问题，在实际的MySQL中，RR隔离级别可以避免一部分避免幻读问题，举个例子：

　（1）一个事务内多次进行快照读：这种情况就算在这几次读中间插入了新行，几次读的结果也是一样的，因为有MVCC。

　（2）一个事务内，一个查询sql的结果用于后续写操作：可以在第一次查询时使用当前读，加上间隙锁，让其他符合条件的行差不进来，这样第一次查询结果在整个事务期间都是有效的。

当然，如果在一个事务内进行几次快照读和当前读都有的sql，那么这几次的结果可能就不一样了，要想这种情况下结果还一致只有在最高隔离级别“Serializable”才行。RR级别下快照读的一致性靠事务版本，当前读的一致性靠间隙锁。

 

　　MySQL的实际加锁策略：前面也说了，由于有了MVCC，RR及以下级别读操作无需加锁，增删改操作影响的行加X锁，删/改还会有有间隙锁，除非sql中显示指明select .... lock in share mode则为读操作影响的行加S锁。S隔离级别有点特殊，为了防止幻读，读操作时会对整个表加S锁，写操作时会给整个表加X锁。

五 优化总结

　　合理建表：

　　　　变长字段与定长字段尽量分离，每一行的大小固定方便跳跃计算

　　　　常用字段与非常用字段尽量分离，合理分配访问量

　　列的选择：

　　　　优先选用： int -> date time -> enum -> char -> vchar -> text

　　　　避免可为null的列，不适合索引的建立以及比较等计算，还会浪费额外的空间记录

　　建立合适的索引：

　　　　查询时应该用独立的列，像where id+1 = 5是用不上索引的

　　　　选择区分度大的列建立索引，像性别这种列就没必要 

　　　　使用频率高的联合查询 where 列1..and 列2...则需要考虑为这几个列建一个联合索引

　　　　由于最左原则，联合索引建立时应该把区分度高的放到左边建立，查询语句也要注意左前缀原则

　　　　用到索引覆盖最好，像select * 则几乎用不到索引覆盖

　　SQL语句的优化：

　　　　少用In查询

　　　　where ... or ...这类的查询可以拆分为几个select 再用union合并，性能提升明显

　　　　表在连接之前先用where筛选，也要避免过多表的连接

　　　　select ..时要几个列就写几个，不要多写，会增加数据传送量