高性能MySQL学习笔记——第一章

最新推荐文章于 2024-06-05 17:23:30 发布

毕东

最新推荐文章于 2024-06-05 17:23:30 发布

阅读量246

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/weixin_39408986/article/details/105696366

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

高性能MySQL第一章

MySQL服务器逻辑架构图

一个MySQL服务器可以有多个存储引擎，存储引擎负责数据的存储和读取，MySQL服务器通过API与存储引擎通信，存储引擎有几十个底层函数。

除了InnoDB之外，其他的存储引擎不会解析SQL，不同存储引擎间不会通信，只是简单响应上层服务器的请求。可以通过show engines语句来查看MySQL数据库支持的存储引擎类型。

连接管理与安全性

每个客户端连接，都会在服务器进程中有用一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心活CPU中运行，服务器负责缓存线程。不需要为每一个新建连接创建或销毁线程。

MySQL5.5或者更新的版本提供了一个API，支持线程池插件，可以使用池中少量的线程来服务大量的连接。

优化与执行

MySQL解析查询，创建解析树，然后对其进行各种优化，包括重写查询、决定表的读取顺序以及选择合适的索引。用户可以使用hint关键字提示优化器影响优化器决策。可以使用explain亲贵优化器解释优化过程的各个因素，知道服务器是如何进行优化决策的。

优化器不关心表使用的是什么存储引擎，优化器会请求存储引擎提供容量或某个具体操作的开销信息，表数据的统计信息等。

对于SELECT语句，在解析查询之前，先会检查缓存，如果能找到对应查询，就不再执行查询解析、优化和执行过程。

并发控制

读写锁

共享锁share lock和排他锁exclusive lock
锁颗粒度

存储引擎行锁表锁页锁
MyISAM √
BDB √ √
InnoDB √ √
锁的级别不同，带来的开销，性能，并发程度也都不相同。
- 表锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突概率高，并发程度最低，服务层也会使用表锁；
- 行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高，存储引擎实现，服务层没有实现；
- 页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

存储引擎	行锁	表锁	页锁
MyISAM		√
BDB		√	√
InnoDB	√	√

事务

InnoDB支持事务，InnoDB之前都不支持事务，MyISAM不支持事务。

ACID
- 原子性（Atomic）
  
  一个事务必须被视为一个不可分割的最小工作单元，整个事务中所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作。
- 一致性（Consistency）
  
  数据库总是从一个一致状态转换到另一个一致性的状态。
- 隔离性（Isolation）
  
  一个事务所作的修改在最终提交以前，对其他事务是不可见的。
- 持久性（Durability）
  
  一旦事务提交，则其所做的修改就会永久保存到数据库中。
  - 事务SQL样本如下

```
START TRANSACTION;
SELECT ...
UPDATE ...
COMMIT;
``` 

* 一个实现了ACID的数据库，相比没有实现ACID的数据库，通常会需要更强的CPU处理能力、更大的内存和更多的磁盘存储空间。用户可以根据自身的业务需求，自主选择合适的存储引擎，对于一些不需要事务的查询类应用，选择一个非事务型的存储引擎，来选择合适的存储引擎。即使存储引擎不支持事务，也可以通过LOCK TABLES语句为应用提供一定程度的保护。

隔离级别
- READ UNCOMMITTED（未提交读），事务中的修改，即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，也被称为脏读Dirty Read，这个级别会导致很多问题。
- READ COMMITTED（提交读），大多数数据库系统的默认隔离级别，一个事务开始时，只能”看见”已经提交的事务所作的修改，一个事务从开始直到提交之前，所作的任何修改对其他事务都是不可见的，也叫不可重复读（nonrepeatable read），有可能出现幻读，指的是当某个事物在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。
- REPEATABLE READ（可重复读），通过多版本并发控制MVCC，Multiversion concurrency Control，理论上无法解决幻读问题，通过InnoDB和XtraDB存储引擎，是MySQL默认事务隔离级别。
- SERIALIZABLE（可串行化），最高级别，通过强制事务串行执行，避免了幻读问题，会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题。
死锁

指两个或多个事务在统一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象，InnoDB处理方法：将持有最少行级排他锁的事务回滚。
事务日志

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

事务日志是追加写入，是磁盘上一小块区域的顺序I/O，所以速度快很多。

事务日志持久化以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘，称为预写式日志。
MySQL的checkpoint机制

a) Mater Thread Checkpoint
以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘，这个过程是异步的。此时InnoDB存储引擎可以进行其他的操作，用户查询线程不会阻塞。
b）FLUSH_LRU_LIST Checkpoint
因为InnoDB存储引擎需要保证LRU（Least Recently Used）列表中需要有差不多1024个空闲页可供使用。
倘若没有1024个可用空闲页，那么InnoDB存储引擎会将LRU列表尾端的页一处。如果这些页中有脏页，那么需要进行Checkpoint，而这些页是来自LRU列表的，因此称为FLUSH_LRU_LIST Checkpoint。
c）Async/Sync Flush Checkpoint
指的是重做日志文件不可用的情况，这是需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的。

MySQL中的事务
- 自动提交AUTOCOMMIT
  
  MySQL默认使用自动提交模式，如果不是显式开始一个事务，则每个查询都被当做一个事务执行提交操作。 SHOW VARIABLES LIKE 'AUTOCOMMIT可以查看当前数据库是否开启自动提交。
在事务中混合使用存储引擎 MySQL服务器部管理事务，事务是由存储引擎进行管理的，所以在同一事务中，使用多种存储引擎是不可靠的。如果事务中混合使用了事务型和非事务型的表，在正常提交的情况下不会有什么问题。但是如果该事务需要回滚，非事务型的表上的操作无法撤销，这会使数据库处于不一致的状态。
隐式锁定和显式锁定除了事务中禁用了AUTOCOMMIT，可以使用LOCK TABLES之外，其他任何时候都不要显式执行LOCK TABLES，不管使用的是什么存储引擎。

多版本并发控制

MySQL的大多数事务型存储引擎实现的都不是简单的行级锁，基于提升并发性能的考虑，他们一般都同时实现了多版本并发控制MVCC，但是各自实现机制不同，没有一个统一的标准。

MVCC的实现，是通过保存数据在某个时间点的快照来实现的，也就是说，不管需要执行多长时间，每个事务看到的数据都是一致的。根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

不同的MVCC实现是不同的，典型的有乐观并发控制和悲观并发控制。

下面以InnoDB举例以说明MVCC机制。

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

具体InnoDB的增删改查是如下实现的：
- 插入数据
  
  记录的版本号就是当前事务的版本号。
id name create version delete version
1 test 1
- 更新数据
  
  更新数据实际上是执行两个操作，insert+delete，先添加一条新纪录，将版本号设置为当前事务id=2，然后将旧数据删除版本号设置为当前事务id=2。
id name create version delete version
1 test 1 2
1 new_value 2
- 删除数据
  
  将删除版本号设置为事务版本号。
id name create version delete version
1 new_value 2 3
- 查询操作
  
  从上面的描述可以看到，只有符合下面两个条件的记录才能被查询出来：
  - 删除版本号未指定（即未被其他事务删除）或者删除版本号大于当前事务版本号（即，假设当前事务版本号为2，但是删除版本号为3，代表数据已经被其他事务删除掉了，但是为了保证当前事务id下的数据的一致性，会读取之前的数据）
  - 创建版本号小于或者等于当前事务版本号，就是说记录创建是在当前事务中或者在当前事务启动之前的其他事务进行的insert

id	name	create version	delete version
1	test	1

id	name	create version	delete version
1	test	1	2
1	new_value	2

id	name	create version	delete version
1	new_value	2	3

MySQL的存储引擎

MySQL的存储引擎有9个，分别代表了不同的存储方式，实际使用过程中需要根据具体的业务需求选择不同的存储引擎。

关于表的相关信息，可以通过SHOW TABLE STATUS LIKE 'TABLENAME' \G进行查询。

其中包含有表明，引擎，版本，行数等信息。

InnoDB引擎

InnoDB引擎，是MySQL默认的事务型引擎，也是最重要、使用最广泛的存储引擎，它被设计用来处理大量的短期事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
InnoDB的数据存储在表空间中，表空间是由InnoDB管理的一个黑盒子，由一系列的数据文件组成，InnoDB可以将每个表的数据和索引都放在单独的文件中。
InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别，其默认的隔离级别为REPEATED READ（可重复读）
InnoDB表是基于聚簇索引建立的。聚簇索引对主键查询有很高的性能，不过其二级索引中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此，若表上的索引较多的话，主键应当尽可能的小。

MyISAM引擎

在InnoDB之前，MySQL默认引擎都是MyISAM引擎。MyISAM提供了一些特性，包括全文索引、压缩、空间函数等。但是MyISAM不支持事务，且不支持行级锁，只支持表级锁，其并发性受到巨大的制约。
如果表在创建并导入数据之后，不会再进行修改操作，那么这样的表或许适合采用MyISAM压缩表。可以使用myisampack对MyISAM表进行压缩。
还有很多其他种类的引擎，各有其特点。
引擎的选择

除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎。

如果想使用全文索引，可以考虑InnoDB+Sphinx的组合，而不是使用支持全文索引的MyISAM。