一、MySQL架构

1. MySQL逻辑架构图

第一层：连接/线程处理：连接处理、授权认证、安全等

第二层：查询缓存、解析器、优化器：MySQL的核心服务都在这层，如查询解析、分析、优化、缓存、内置函数（日期、时间、数据、加密），所有跨引擎的功能也都在这层实现，如存储过程、触发器、视图等。

第三层：存储引擎：存储引擎负责MySQL中数据的存储和提取。MySQL服务器通过API与存储引擎进行通信，这些API屏蔽了不同引擎之间的差异，使得这些差异对上层的查询过程透明。

1）连接：MySQL实现了类似连接池的机制，客户端与服务器通信不必每次都新建和销毁线程。

2）优化：当查询时，MySQL首先解析查询语句，然后进行优化（包括重写查询、决定表的读取顺序、选择合适的索引等）。

用户可以通过hint修改MySQL的优化过程
也可以通过explain查看优化过程，根据结果用户可以修改查询、schema等来优化查询性能。

对于select语句，在解析查询之前，MySQL会先检查查询缓存（Query Cache）中是否有，找到的话就直接返回结果。

1.2 并发控制

并发控制场景：多个语句要在同一时刻修改同一数据。
并发控制涉及两个场景：服务器层、存储引擎层

1.2.1 读写锁

可通过读写锁来解决并发控制存在的问题。

1）读锁（read lock/shared lock）: 读锁是共享的，或者说是互不阻塞的，多个用户可以同时读取同一个资源。

2）写锁（write lock/exclusive lock）：写锁是排他的，写锁会阻塞其他的写锁和读锁。

注：MySQL的读写锁和Java一样，一句话总结：读锁时其他线程可读不可写；写锁其他线程不可读也不可写。

1.2.2 锁粒度：表锁和行锁

锁的粒度越大，对性能的影响也越大。

MySQL的锁策略：就是在锁的开销与数据的安全性之间寻求取舍。大多数商业数据库都在表上加行级锁（row-level lock），而MySQL则提供了多种选择，每种MySQL存储引擎都有自己的锁策略和锁粒度。

1）表锁（table lock）：MySQL中开销最小的锁策略
当用户对表进行insert, delete, update，alter table等操作时，要先获得写锁来锁定整张表，之后其他用户对该表既不可写，也不可读。

写锁比读锁有更高的执行优先级。

2）行级锁（row lock）：可最大程度支持高并发，但却是MySQL中最大的锁开销。
在InnoDB和XtraDB的存储引擎中实现了行级锁。
行级锁只在存储引擎层实现，而MySQL服务器层没有实现。

例1: (明确指定主键，并且能查到，row lock)
SELECT * FROM products WHERE id=‘3’ FOR UPDATE;
SELECT * FROM products WHERE id=‘3’ and type=1 FOR UPDATE;

例2: (明确指定主键，若查不到，则无lock)
SELECT * FROM products WHERE id=’-1’ FOR UPDATE;

例3: (无主键，table lock)
SELECT * FROM products WHERE name=‘Mouse’ FOR UPDATE;

例4: (主键不明确，table lock)
SELECT * FROM products WHERE id<>‘3’ FOR UPDATE;

例5: (主键不明确，table lock)
SELECT * FROM products WHERE id LIKE ‘3’ FOR UPDATE;

注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。
注2: 要测试锁定的状况，可以利用MySQL的Command Mode ，开二个视窗来做测试。

1.3 事务

事务就是一组原子性的SQL。即事务内的SQL语句，要么全部执行成功，要么全部执行失败。

事务的经典案例：转账：A给B转账200元。
语句1.从A账户减去200元;
语句2.从B账户中增加200元。
如果语句2失败，那么语句1也要回滚。

要支持事务，必须确保ACID特性。ACID表示：
1）atomicity原子性：事务中的全部操作要么全部成功，要么全部回滚。
2）consistency一致性：上面例子中，若语句2失败，A也不会损失200元，因为事务最终没有commit。
3）isolation隔离性：一个事务所有的修改在没有commit前，对其他事务是不可见的。上面例子中，若语句2没有执行成功，那么查询时，看到A中的钱并没有减少200元。
4）durabiliity持久性：一旦事务commit, 其所作的修改就会永久保存到数据库中。

实际上，数据库要实现ACID非常难，一个兼容ACID的系统，要做很多复杂但用户并不会觉察的工作，才能确保ACID实现。
实现了ACID的数据库，比没有实现ACID的数据库，需要更多的CPU、内存、磁盘空间。这也正是MySQL强大的地方，用户可以根据业务特点，来选择合适的存储引擎，对于不需要事务性操作的应用，可以选择非事务型的存储引擎。

1.3.1 隔离级别

隔离性比想象的复杂。在SQL标准中定义了四种隔离级别，每种都规定了一个事务中所做的修改，在事务内和事务间，哪些是不可见的。较低级别的隔离可用于执行更高的并发，系统开销也更小。

MySQL每种存储引擎对隔离性的级别的实现也不尽相同。

隔离性的四种级别：

1） read uncommitted （读未提交）
事务中的修改，即使没有commit, 其他事务也都是可见的。
其他事务读取的未提交的数据，被称为脏读（Dirty Read）。
该级别性能上非常好，但缺乏其他级别的好处。

2）read commited（读提交）
一个事务提交之前，其所作的修改在其他事务中都是不可见的。
大多数数据库系统的默认级别都是read commited（但MySQL不是）。
该级别也叫不可重复读，即提交前和提交后读取到的数据不同。

3）repeatable read（可重复读）
可重复读解决了脏读的问题，保证了同一个事务多次读取同样的记录，结果是一样的。
可重复读是MySQL的默认事务隔离级别。

4）serializable（可串行化）
他是最高的隔离级别。serializable会在读取的每一行数据上加锁，但可能导致大量的超时和锁争用问题，只有在非常需要确保数据一致性且没有并发时，才考虑用该级别。

1.3.2 死锁

死锁是指两个事务在同一资源上相互占用，并请求锁定对方占用的资源。

为了避免死锁，数据库系统实现了各种死锁检测机制和死锁超时机制。比如InnoDB，对越复杂的系统，越能检测到死锁的循环依赖，并立即返回一个错误。否则死锁会导致一个非常慢的查询。InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。（这是相对简单额死锁回滚算法）

造成死锁的例子：

1.3.3 事务日志

事务日志的原理：存储引擎在修改表时，只需要修改内存，然后把该修改行为记录到硬盘上的事务日志文件中，而不必每次都将修改真正的数据。然后事务日志会通过异步追加的方式来修改真正的数据。因为写日志是对磁盘的顺序IO，而不必以随机IO的方式查找真实的数据，所以要快的多。目前，大多数存储引擎都是这样实现的，这种方式被称为预写式日志（Write-Ahead Logging），修改数据需要写两次磁盘（一次写事务日志，一次修改真实数据）

除了性能外，这种事务日志的另一好处是，如果真实数据还没被修改，系统崩溃，存储引擎可在重启时根据事务日志，自动恢复这部分修改的数据。

1.3.4 MySQL中的事务

MySQL提供了两种事务型的存储引擎：InnoDB和NDB Cluster.

1）自动提交（AutoCommit）
MySQL默认采用自动提交的模式。

2）隐式和显式锁定
InnoDB采用两阶段锁协议（two-phase locking protocol）。在事务执行过程中，随时都可以执行锁定，所有锁只有在执行commit或者rollback之后才会释放，InnoDB会根据隔离级别在需要的时候加锁。

另外，InnoDB也支持通过语句进行显式锁，这些语句不属于SQL规范。例如：
select … lock in share mode
select … for update

1.4 多版本并发控制

MySQL的大多数事务型存储引擎的实现都不是简单的行级锁。基于并发性能的考虑，他们都实现了多版本并发控制（MVCC）。不单MySQL, Oracle，PostgreSQL也都实现了MVCC, 但各自的实现机制不同。

MVCC的实现原理：通过保存数据在某个时间点的快照来实现的。即每个事务由于开始时间不同都有自己的版本，这样每个事务对同一张表，不管需要执行多长时间，每个事务看到的数据是一致的。而同一时刻，不同事务对同一张表看到的数据可能不一样。

MVCC是行级锁的一个变种，他很多情况下避免了加锁操作，从而开销更低。虽然不同存储引擎的MVCC实现机制不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行。

不同存储引擎对MVCC的实现不同，典型的有乐观并发控制和悲观并发控制。

InnoDB的MVCC是这样实现的：
是通过记录每行记录后面保存的两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的过期时间（或删除时间），当然存储的并不是实际的时间值，而是系统版本号。
每当开始一个新的事务，系统版本号会自动加1，系统最开始的版本号会作为系统版本号，用来和查询到的每行记录的版本号作比较。

下面以repreatable read隔离级别下为例，看MVCC是如何操作的：
1）select：InnoDB会根据以下两个条件检查每行记录：
《1》InnoDB只查找早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在，要么是事务自身插入或者修改过的。
《2》行的删除版本要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行，在事务开始之前未被删除。
只有符合上述两个条件的记录，才能返回查询结果。

2）insert: InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

3）delete: InnoDB为删除每一行保存当前系统版本号作为行删除标识。

4）update: InnoDB为插入一条新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

通过保存这两个额外的系统版本号，使大多数读操作都可以不用加锁。不足之处是每行记录都需要额外的空间，需要做更多的行检查工作，以及一些额外的维护工作。

MVCC只在repeatable read和read committed两个隔离级别下工作，其他两个隔离级别对MVCC不支持。

1.5 MySQL的存储引擎

在MySQL文件系统中，将每个数据库（也称为schema）保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。

可使用show table status命令显示表的相关信息。例如：

可以看出，这是一个MyISAM表。

1.5.1 InnoDB存储引擎

InnoDB是MySQL默认的事务型引擎。InnoDB的数据存储在表空间（tablespace）中，表空间是由一系列数据文件组成，InnoDB将每个表的数据和索引存放在单独的文件中。

InnoDB采用MVCC来支持高并发，且实现了四个标准的隔离级别，其默认级别是repeatable read（可重复读），并且通过间隙锁（next-key locking）策略来防止幻读的出现。

所谓幻读，是指当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围内的记录时，就会产生幻行（phantom row）。

InnoDB是基于聚簇索引（聚簇索引是顺序结构与数据存储物理结构一致的一种索引，并且一个表的聚簇索引只能有唯一的一条）建立的。InnoDB的索引结构和MySQL其他存储引擎有很大不同。聚簇索引对主键查询有很高的性能。不过他的二级索引（secondary index, 非主键索引）中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此，若表上的索引较多的话，主键应当尽可能的小。

InnoDB内部做了很多优化（加速读、写），包括从磁盘读取数据时，采用的可预测性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引（adaptive hash index），以及能够加速插入操作的插入缓冲区（insert buffer）等。

作为事务型存储引擎，InnoDB支持热备份，MySQL的其他存储引擎不支持热备份。

1.5.2 MyISAM存储引擎

在MySQL 5.1版本之前，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，如全文索引、压缩、空间函数等。当MyISAM不支持事务和行级锁。MyISAM最大缺陷是崩溃后无法安全恢复。

MyISAM的优势在于：对只读的数据，或者表比较小，可以忍受修复（repair）操作，依然可以使用MyISAM引擎。

MyISAM会将表存储在两个文件中：数据文件和索引文件，分别以.MYD和.MYI为扩展名。

MyISAM的特性：

1）加锁与并发：MyISAM只能对全表加锁，而不支持行级锁。

2）修复：对于MyISAM表，MySQL可以手工或自动执行检查和修复操作。表的修复可能导致一些数据丢失，且修复操作是非常慢的。

3）索引特性：MyISAM支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询。

MyISAM的性能：MyISAM引擎设计简单，数据以紧密格式存储，所以在某些场景下性能很好。但MyISAM最典型的性能问题还是表锁的问题，高并发下会拖累性能。

1.5.3 选择合适的引擎

在大部分情况下，InnoDB都是正确的选择，除非需要用到某些InnoDB不具备的特性。例如，要用到全文索引，建议优先考虑InnoDB加上Sphinx的组合，而不是使用支持全文索引的MyISAM。
如不在乎并发的能力，也不在乎崩溃后的数据丢失问题，且对InnoDB的占用多余空间敏感，可使用MyISAM.

选择InnoDB or MyISAM?
1）日志型应用：把业务日志写入到MySQL中，这类需求对insert性能有很高的要求，可采用MyISAM, 因为开销低，插入速度快。

2）只读或者大部分情况下的只读：即读多写少的操作。如果不介意MyISAM崩溃恢复的问题，可采用MyISAM。

3）订单处理：要求支持事务，要选择InnoDB

4）海量数据存储：采用InnoDB，否则灾难恢复就是个噩梦。

1.5.4 转换表的引擎

MySQL的引擎是可以切换的。不同的切换方法各有利弊。

二、MySQL数据类型优化

MySQL支持的数据类型众多，选择使用正确的数据类型对于高性能至关重要。

1）越小越好: 前提是使用最小数据类型，将来不会不够用，否则增加数据类型的范围是一个耗时和痛苦的过程。

2）简单就好：例如整型比字符类型操作代价更低。

3）尽量避免使用NULL: 最好指定为NOT NULL, 除非真的需要NULL值。因为NULL列会使索引计算变得更复杂，当可为NULL的列被索引时，每个索引记录都需要一个额外的字节。

1. 整数类型

tinyint：8位
smallint：16位
mediumit: 24位
int: 32位
bigint: 64位

可存储的范围为 -2的n-1次幂 ~ 2的n-1次幂-1，其中n是位数。

2. 字符串类型

1）varchar: 可变字符串，比定长的字符串更节省空间，varchar适合存储字符串的长度比平均长度大很多。

2）char: 定长的字符串，char适合存储很短的字符串，或者所有值都接近同一个长度。

3. 时间类型

1）datetime: 把时间封装到格式为YYYYMMDDHHMMSS的整数中，精度到秒，与时区无关。

2）timestamp: 从1970年1月1日午夜（格林尼治时间）到现在的秒数。timestamp只使用四个自己的存储空间，因此比datetime更节省空间。

4. 文本存储

1）blob: 二进制存储
2）text: 字符方式存储

三、MySQL的索引

在MySQL中，索引是由存储引擎层实现的，而不是服务器层实现的。所以，不同存储引擎对索引的实现方式不同。

1. 索引的类型

1.1 B-Tree索引：MySQL默认索引

大家谈论的索引如不特殊说明，都是BTree索引。对于InnoDB引擎，使用的是B+Tree.

BTree索引中所有的值都是按顺序存储的，并且每个叶子到根的距离相同，值都保存在叶子节点上。各个叶子节点之间通过链表连接。

B-Tree索引能加快访问数据的速度，因为存储引擎不需要进行全表扫描来查找需要的数据。

1.2 哈希索引：用于精确匹配

哈希索引基于哈希表实现，只有精确匹配索引所有的列查询才有效。

对于每一行数据，存储引擎都会对所有的索引列计算一个hashcode。哈希索引将所有的hashcode存储在索引中，同时在哈希表中保存指向每个数据行的指针。

2. 索引的优点

1）索引大大降低了服务器需要扫描的数据量（查询快）
2）索引可以帮助服务器避免排序和临时表（节约系统资源）
3）索引可以将随机IO变为顺序IO（写入快）

3. 正确地使用索引

1）索引必须是独立的列。— 即索引不能是表达式的一部分，也不能是函数的参数，否则MySQL的索引无法生效。

2）前缀索引
如果创建索引的列很长时，会让索引变得又大又慢。对于blob, text, 很长的varchar，可使用前缀索引。

那么，怎样选择前缀索引的长度呢？

可以看到，从6到7，这段长度的增加，其比例的增加已经很小了。所以，可这样创建前缀索引：
mysql> Alter table rongcloud.demo add key(city(7));

前缀索引的缺点：无法使用前缀索引做order by和group by.

3）多列索引

4）选择合适的索引列顺序

在B-Tree索引中，索引的顺序意味着按索引列的顺序从左到右排序，以满足order by, group by, distinct等子句的查询要求。

5）聚簇索引

6）覆盖索引

7）压缩索引

8）冗余和重复索引

四、查询性能优化

1. 慢查询的root cause

1）查询了不必要的数据
如select * from mytable;

2）避免多次查询同一数据
可使用缓存来解决此类问题

2. 优化查询的招

1）将一次慢的大查询分解成多次快的小查询

2）分解关联查询
将select * from tab
join tab_post on tappets.tab_id=tag.id
join post on tag_post.postid=post.id
where tag.tab = ‘mysql’
分解成下面的查询来代替：
select * from tab from tag=‘mysql’;
select * from tag_post where tag_id=1234;
select * from post where post.id in (123,456,789);

这样做的好处是：
(1) 让缓存效率更高
(2) 执行单个查询，可以降低锁竞争。
(3) 在应用层做关联，可以更容易对数据库进行拆分，可更好地做到高性能和可扩展。
(4) 可减少冗余记录的查询