Mysql 索引与优化 加锁处理分析

Mysql  索引与优化及加锁处理分析

1 Mysql介绍

1.1 Mysql简介

定义：MySQL 是一款优秀的开源小型关系数据库系统。

历史：

1.       1979年，始于一款报表工具、数据引擎。

2.       1996年，发布 MySQL1.0 版本。

3.       2000年，成立 MySQL AB公司。

4.       2008年，Sun 以 10 亿元收购了 MySQLAB 公司。

5.       2009年，Oracle 以 76 亿元收购了 Sun 公司。

特点（从DB产品对比角度）：

开源，基于通用公共许可证协议（GPL）可以免费使用修改。引擎，采用开放式存储引擎架构，有多种免费或付费引擎可选择。可用度高，通过优秀的架构设计及相关手段能实现企业级高可用性、高扩展性。免费，有效降低企业投资运营成本。趋势，产品成熟度逐渐提高，占行业应用比重增大，相关产业链丰富，从业人员逐步形成规模效应。

特点（从开发管理角度）：不简单，MySQL由于引擎众多，且各自内部机制和实现技术差别很大，完全掌握需要花很多精力，并不像很多人想象中的那样简单。

版本：企业版本（MySQLEnterprise）：收费，更稳定，完整的配套监控工具，官方技术支持。

社区版本（MySQL）：免费，没有企业版稳定，没有完整监控工具及官方支持。

下载：

http://dev.mysql.com/downloads/mysql/

1.2 MySQL体系结构

图1-1 Mysql体系结构

1.3 MySQL存储引擎

1.3.1 InnoDB

1.       支持事务

行锁设计，提供并发性，同时支持MVCC

MVCC——多版本并发控制

支持外键，以确保数据完整性

独立的ibd文件存储表结构和数据

其他特性：插入缓冲、二次写、自适应哈希索引、预读等高性能和高可用功能

适用场合：强调可靠性和需要支持事务处理

1.3.2 MyISAM

1.       不支持事务

2.       表锁设计

3.       全文索引

4.       存储表由MYD和MYI组成，分别存放数据和索引

5.       使用数据压缩和索引优化

6.       特点：可靠性高、适用范围广、检索速度快

7.       适用场合：强调数据检索速度

1.3.3 NDB

1.       Network database，一种集群存储引擎

2.       采用share nothing 集群架构，每个节点都拥有自己的内存和磁盘

3.       特点：数据全部放在内存中，主键查询速度快，增加NDB存储节点，可线性提高性能,Join连接的网络开销大,对RAM需求大

4.       适用场合：冗余、高可用性、负载均衡

1.3.4 Memory

1.       将表中数据存放在内存中

2.       默认使用哈希索引

3.       由一个.frm的扩展名来指明它存储的表定义

4.       特点：服务器重启时，Memory表里的数据全部丢失, 只支持表锁，并发性能差, 不支持TEXT和BLOB类型, 按照定常方式存储变长字段(varchar)数据高速度，低延迟，不适合频繁写

5.       适用场合：存储临时数据、数据仓库的纬度表

1.3.5 Archive

1.       目标：提供高速的插入和压缩功能

2.       特点：只支持Insert和Select操作, 支持行锁来实现高并发的插入操作,     不支持事务, 采用zlib算法压缩存储，压缩比例达1:10

3.       适用场合：存储归档数据，如日志信息

1.3.6 Maria

1.     目标：取代原有的MyISAM存储引擎

2.     特点：缓存数据和索引文件, 行锁设计, 提供MVCC功能, 支持事务和非事务安全

1.3.7 综合比较

图1-2 存储引擎综合比较

1.4 InnoDB存储引擎

1.       由Innobase Oy公司开发（2006被Oracle收购）

2.       第一个完整支持ACID事务的MySQL存储引擎(ACID:原子性(Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）)

3.       行锁设计

4.       支持MVCC

5.       支持外键

6.       一致性非锁定读

7.       应用广泛：Yahoo、Facebook、Youtube、Flickr、Wow、   SecondLife …

1.5 InnoDB存储引擎——体系架构

图1-3体系架构

 

图1-4 内存结构

缓冲池：存放各种数据缓存，将数据库文件按页读取，LRU算法维护缓存，定时flush脏页到文件

日志缓冲：将重做日志信息先放入这个缓冲区，然后按一定频率将其刷新到重做日志文件

额外内存池：对一些数据结构本身信息的内存分配

2 MySQL加锁处理分析

2.1 背景

2.1.1 MVCC Snapshot Read vs Current Read

MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control)(注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。在读多些少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的RDBMS，都支持了MVCC。

在MVCC并发控制中，读操作可以分成两类：快照读(snapshot read)与当前读(currentread)。快照读，读取的是记录的可见版本(有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例：

 

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)

n select * from table where ?;

 

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。

n select * from table where ? lock in share mode;

n select * from table where ? for update;

n insert into table values (…);

n update table set ? where ?;

n deletefrom table where ?;

所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁(共享锁)外，其他的操作，都加的是X锁(排它锁)。

为什么将插入/更新/删除操作，都归为当前读？可以看看下面这个更新操作，在数据库中的执行流程：

从图中，可以看到，一个Update操作的具体流程。当Update SQL被发给MySQL后，MySQL Server会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁(current read)。待MySQLServer收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。因此，Update操作内部，就包含了一个当前读。同理，Delete操作也一样。Insert操作会稍微有些不同，简单来说，就是Insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读。

注：根据上图的交互，针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。先对一条满足条件的记录加锁，返回给MySQL Server，做一些DML操作；然后在读取下一条加锁，直至读取完毕。

2.1.2 Cluster Index：聚簇索引

InnoDB存储引擎的数据组织方式，是聚簇索引表：完整的记录，存储在主键索引中，通过主键索引，就可以获取记录所有的列。关于聚簇索引表的组织方式，可以参考MySQL的官方文档：Clustered and Secondary Indexes。本文假设读者对这个，已经有了一定的认识，就不再做具体的介绍。接下来的部分，主键索引/聚簇索引两个名称，会有一些混用，望读者知晓。

2.1.3 Clustered and Secondary Indexes

Every InnoDB table has a special indexcalled the clustered index where the data forthe rows is stored. Typically, the clustered index is synonymous(同义词) with theprimary key. To get the best performance from queries,inserts, and other database operations, you must understand how InnoDB uses theclustered index to optimize the most common lookup and DML operations for eachtable.

If you define a PRIMARY KEY on your table, InnoDB uses it as the clusteredindex.

If you do notdefine a PRIMARY KEY for your table, MySQL picks(采用) the firstUNIQUE index that has onlyNOT NULL columns as the primary keyandInnoDB uses itas the clustered index.

If the table hasno PRIMARY KEY orsuitable UNIQUEindex,InnoDBinternally generates a hidden clustered index on a synthetic column containingrow ID values. The rows are ordered by the ID thatInnoDB assigns to the rows in such atable. The row ID is a 6-byte field that increases monotonically as new rowsare inserted. Thus, the rows ordered by the row ID are physically in inser