高性能MySQL阅读笔记

MySQL的特点：它的架构可以在多种不同的场景中应用，足够的灵活，能适应高要求的环境。既可以嵌入到应用程序中，也可以支持数据仓库、内容索引和部署软件、高可用的冗余系统，在线事务处理系统等。

 

MySQL逻辑架构图:

 

 

 

第一层并不是MySQL独有的，大部分基于网络的服务都有类似的架构

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

第三层包含存储引擎。复杂MySQL中数据的存储和提取。每个存储引擎都有其优势和劣势。服务器通过API与存储引擎进行通信。这些API屏蔽了不同存储引擎之间的差异，使得这些差异对上层查询过程透明。，存储引擎包含几十个底层函数，用于执行诸如“开始一个事务”或者“根据主键提取一行记录”等操作。但是存储引擎不会解析SQL，不同存储引擎之间也不会互相通信，而是简单的响应上层服务器的请求。

 

SQL的执行

MySQL会解析查询并创建内部数据结构，然会对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示优化器，影响它的决策过程，也可以请求优化器解释优化过程的各个因素。

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

 

并发控制

锁

共享锁（读锁）与排他锁（写锁）

锁粒度 给系统加锁也是需要消耗资源的。锁的各种操作，例如获得锁，检查锁是否已经解除、释放锁等，都会增加系统的开销。如果系统花费大量的时间来管理锁而不是存取数据，那么系统的性能也会收到影响。

所谓的锁策略，就是在锁定的开销和数据的安全性之间寻求平衡，例如表级锁与行级锁。将锁粒度固定在某个级别，可以为某些特定的应用场景提供更好的性能。

 

MySQL中表级锁是最基本的锁策略，并且是开销最小的策略。写锁优先级高于读锁，因此一个写锁请求可能会被插入到读锁的前面。

行级锁可以最大程度的支持并发处理（同时带来了最大的锁开销）。例如InnoDB就支持行级锁。行级锁只在存储引擎层实现，（注意与表锁的区别）。服务处完全不了解存储引擎中的锁实现。

 

事务

事务就是一组原子性的SQL查询，或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应该该组查询的全部语句，那么就执行该组查询。如果其中有任何一条语句因为崩溃或者其他原因无法执行，那么所有的语句都不会执行。

ACID

atomicity

一个事务必须被是为一个不可分割的最小工作单元，整个事务中所有的操作要么全部提较成功要么全部失败回滚，不可能只执行其中的一部分操作。

consistency

数据库总是从一个一致性的状态转换到另一个一致性状态，即事务发生的前后，数据是一致的。

isolation

一个事务所做的修改在最终提交以前，对其他事务是不可见的。

durabilty

一旦事务提交，所作的修改就会永久的保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

一个兼任ACID的数据库系统，需要做很多复杂但是可能用户没有察觉到的工作，才能确保ACID的实现。就像锁粒度的升级会增加系统开销一样，事务处理过程中额外的安全性，也会需要数据库系统做更多的额外工作。

 

隔离级别

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低几倍的隔离通常可以执行更高的并发。系统开销也更低。

 

READ UNCOMMITED（未提交读）

在READ UNCOMMITED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，也称脏读（Dirty Read）这个级别会导致很多问题。在实际应用中一般很少使用。

 

READ COMMITED（提交读）

大多数数据库系统的默认隔离级别都是READ COMMITED（MySQL不是）。READ COMMITED满足了隔离性的简单定义：一个事务开始时，只能看见已经提交的事务所做的修改。这个级别有时也叫做不可重复读。因为两次执行同样的查询，可能会得到不一样的结果。

 

REPEATABLE REDA(可重复读)

解决了脏读的问题。该级别保证了在同i个事务中多次读取同样的记录结果时一致的。但是理论上，可重复读隔离级别别还是无法解决幻读的问题。即当某个事务在读取谬个范围内的记录时，另一个事务又在该范围内插入了新的记录，当前的事务再次读取该范围的记录时，会产生幻行。InnoDB通过多版本并发控制MVCC解决了幻读的问题。

 

SERIALIZABLE（可串行化）

最高的隔离级别，通过强制事务的串行执行避免了幻读的问题。SERIALIZABLE会在读取的每一行数据上都加锁，可能导致大量的超时和锁征用问题。实际应用中很少使用。

 

MySQL可以通过执行SET TRANSACTION ISOLATION LEVEL命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。可以在配置文件中设置整个数据库的隔离级别。

 

在事务中使用混合存储引擎

MySQL服务器层，使用混合引擎在正常的提价情况下不会发生说明问题。但是如果事务需要回滚，非事务型的表的变更就无法撤销。会导致数据库处于不一致的状态。所有为每张表选择合适的存储引擎非常重要。

 

隐式和显式锁定

InnoDB采用的是两阶段锁定协议。在事务执行过程中，随时都可以执行锁定。锁只有在执行COMMIT或者ROLLBACK的时候才会释放，并且所有的锁都是在同一时刻释放。InnoDB会根据隔离级别在需要的时候自动加锁。

 

另外，InnoDB也支持通过特定的语句进行显示锁定，这些语句不属于SQL规范。

 

多版本并发控制

MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，一般都同时实现了多版本并发控制（MVCC）。MVCC是行级锁的一个变种，但是很多情况下避免了加锁操作，因此开销更低。一般都实现了非阻塞的读操作，写操作也只锁定必要的行,写操作也只锁定必要的行。

 

MVCC主要通过保存数据在某个时间点的快照来实现。即不管需要执行多长时间，每个事务看到的数据都是一致的。不同的存储引擎实现MVCC的方式不同，典型的有乐观（optimistic）并发控制和悲观（pessimistic）并发控制。

 

InnoDB的MVCC通过在每行记录的后面保存两个隐藏列（1.创建时间，2.删除时间）不是实际时间，而是系统版本号。没开始一个新的事务，系统版本号会自增。事务开始时刻的版本号会作为事务的版本号，用来查询每行记录的版本号进行比较。

 

 

例如：

SELECT

1.查找的每一条记录只找小于当前事务版本号的记录（保证读到的数据是事务开始前就存在，或本事务自身插入或修改的）

2.行的删除有两种情况，（版本未定义）， （大于当前事务的版本号），可以保证事务读到的行在事务开始前未被删除。

 

INSERT

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

 

DELECT

InnoDB为删除的每一行保存当前系统版本号作为行删除时间

 

UPDATE

保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识

 

InnoDB通过MVCC来支持高并发，实现了四个隔离级别（未提交读， 提交读， 可重复读， 串行化）默认级别是可重复读，并且通过间隙锁策略防止幻读的出现。间隙锁使InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，防止幻行的插入。

INNODB基于聚簇索引建立，主键查询性能很高，但是二级索引中必须包含主键列。

 

MySQL的性能优化

Mysql支持的数据类型非常多，正确的数据类型对性能至关重要。以下几个简单的原则有助于做出更好的选择。

1.更小的通常更好：

尽量选择可以正确存储数据的最小数据类型。更小的数据类型通常更快。需要更少的磁盘，内存和cpu缓存，并且处理时需要的cpu周期也更短

2.简单就好

简单数据类型的操作通常需要更少的cpu周期，整型比字符型操作代价更低。例如应该使用MySQL内建的类型而不是字符串来存储日期和时间，，使用整型存储IP地址。

3.尽量避免null

最好指定列为not null， 除非真的要存储null， 因为查询中包含可以为null的列，优化更难，会使得索引，索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，MySQL需要特殊处理，当可为NULL的列被索引时，每个索引记录需要一个额外的字节。

 

一些应该避免的设计：

1.太多的列，MySQL的存储引擎API工作时需要在服务器层和存储引擎之间通过行缓存格式copy数据，然后在服务器层将缓冲内容解码成各个列。而从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。（MyISAM的定长结构与服务器层正好匹配， 但是变长结构需要转换）

2.太多的关联：所谓的实体-属性-值（EAV）设计模式是一个常见的糟糕设计，尤其是在MySQL下不能靠谱的工作。MySQL限制了每个关联操作最大只能有61张表。如果希望查询执行的快速并且并发性好，单个查询最好在12个表以内做关联。

3.过渡使用枚举

 

范式的优点

1.范式化的更新操作通常比反范式化要快

2.当数据较好的范式化时，就只有很少的重复数据，修改数据更容易

3.范式化的表通常更小，可以更好的存放在内存里，执行会更快

4.很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。

 

范式的缺点

范式化的设计通常需要关联。不仅使查询代价更高，也可能使一些索引策略失效。例如一些列存在不同的表里，如果不范式化，就存在一张表里，使用同一个索引。

 

高性能索引

索引优化使对查询优化最有效的手段，索引能够轻易的将查询性能提升几个数量级，“最优的索引‘有时比一个”好的“索引性能要好两个数量级。创建一个真正优化的索引经常需要重写查询。

索引可以包含一个或多个列的值，如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效的使用索引的最左前缀列。一个包含两个列的索引和两个包含一个列的索引是大不相同的。

 

B-Tree索引

B-Tree，即每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点叶子节点的范围遍历。不过存储引擎也可能使用不同的存储引擎，例如NDB集群存储引擎内部实际上使用了T-Tree结构存储这种索引。InnoDB使用的是B+Tree。

 

为什么有了二叉查找树还需要B树和B+树？

因为磁盘IO，在大量数据存储中，查询时不能一下子将所有的数据都加载到内存中，只能逐一加载磁盘页，每个磁盘页对应树的一个节点。会造成大量的IO操作（最坏情况下为树的高度）,而平衡二叉树的深度过大，使得磁盘IO读写过于频繁。

 

B+树相比B树的优势

1.单一节点存储了更多的元素，使得查询IO次数更少

2.所有查询都要找到叶子节点，查询性能更稳定

3.所有叶子节点形成有序链表，便于范围查找

 

存储引擎以不同的方式使用B-Tree索引，性能也各不同。MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

B-Tree意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离都相同。

 

可以使用Btree索引的类型：全键值，键值范围，键前缀查找（只能根据最左前缀查找，即索引的第一列）

 

因为B-Tree索引时按照顺序存储数据，所以MySQL可以用来做ORDER BY和GROUP BY操作，因为数据是有序的，所以，B-TREE也就会将相关的列值都存储在一起，最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能完成全部查询。

索引优点如下：

1.索引大大减少了服务器需要扫描的数量

2.索引可以帮助服务器避免排序和临时表

3.索引可以将随机IO变为顺序IO

 

索引的优化

 

不当的使用索引(正确使用索引)

例如:查询中的列不是独立的（索引列是表达式的一部分），MySQL不会使用索引；

SELECT id FROM sakila WHERE id + 1 = 5;

应当简化WHERE的条件，始终将索引列单独放在比较符号的一侧。

 

前缀索引

当索引列字符特别长时，应该使用前缀索引，例如BLOB，TEXT或者特别长的varchar，注意前缀长度的选择。

前缀索引的缺点：MySQL无法使用前缀索引做ORDER BY 和GROUP BY ，也无法使用前缀索引做覆盖扫描。

 

聚簇索引

是一种数据存储方式，InnoDB的聚簇索引在同一个结构中保存了B-Tree索引和数据行。当表有聚簇索引时，其数据行实际上存放在所以你的叶子页中（聚簇），表示数据和相邻的键值紧凑的存储在一起。一个表只能有一个聚簇索引（主键）。如果没有定义主键，InnoDB会选择一个唯一的非空索引来替代，如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引。

 

聚簇的优点：

1.可以把相关数据保存在一起，例如实现电子邮箱时，可以根据用户ID老聚集数据，这样只需要从磁盘读取少量的数据也，就能获取某个用户的全部邮件。

2.数据访问更快，聚簇索引将数据和索引保存在同一个B-TREE中，隐藏从聚簇索引中获取数据更快

3.使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

 

缺点：

1.比不过redis这种数据放在内存中的数据库

2.插入速度依赖于插入顺序，按主键顺序插入最快。

3.更新聚簇索引的代价很高

4.插入新行，但已满时，可能也会有分页的操作。

5.二级索引可能会很大，因为二级索引的叶子节点里包含了引用行的主键列

6.全表扫描会比较慢

7.二级索引访问需要两次索引查找

 

随机的索引会使得使用聚簇索引的插入变得很慢，自增主键是一个良好的选择。但是顺序主键也不是万能的。在高并发工作负载时，主键顺序插入可能会造成明显的争用。并发插入可能导致间隙锁竞争。

 

 

 

查询优化

慢查询基础：优化数据访问

查询性能低下的最基本原因是访问的数据太多，一些查询不可避免的需要筛选大量的数据，但是大部分性能低下的查询都可以通过减少访问数据量的方式进行优化。

1.确认应用程序是否在检索大量超过需要的数据。通常是访问了太多的行，也有时候是访问了过多的列。

2.确认MySQL服务器层是否在分析大量超过需要的数据行。

 

一些常见问题：

1.查询不需要的记录，查询出全部结果集，再截取一部分数据。

2.多表关联时返回全部列，返回不需要的列尤其是多表关联时会产生大量不需要的数据。

3.总是取出全部的列。和上一条类似，增加了系统开销，而且让优化器无法完成索引覆盖扫描这样的优化

4.重复查询相同的数据，例如用户评论时需要查询用户头像url，最好是第一个访问，将其缓存起来。

 

 

一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏

·在索引中使用where条件来过滤不匹配记录，在存储引擎层完成

·使用索引覆盖扫描（Extra中出现Using index）来返回记录，在MySQL服务层完成

·从数据库表中返回数据，然后过滤不满足的条件记录（在Extra列中出现Using where）

 

某些情况下可以将一个复杂连接查询分解成多个简单查询，然后在应用程序里进行数据连接。

查询分解的好处：

1.缓存效率更高，可以方便的缓存单表查询对象

2.分解查询后，执行单个查询可以减少锁的竞争

3.应用层关联，可以更容易对数据库 进行拆分，更好左到高性能和可扩展

4.减少冗余查询