MySQL-性能优化_大表和大事务的常用处理方案

小小工匠

发布于 2021-08-17 11:08:06

1.5K0

文章被收录于专栏：小工匠聊架构小工匠聊架构

生猛干货

带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试

官方文档

https://dev.mysql.com/doc/

如果英文不好的话，可以参考 searchdoc 翻译的中文版本

http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/index.com.coder114.cn.html

影响MySQL性能的几大因素

通常来说，有以下几点

慢SQL（重点）、主机的硬件资源（CPU、内存、磁盘I/O等）、网卡流量等等

高并发和高CPU使用率高并发–>数据库连接池被用光的几率大增（max_connections默认100），超过的话，就会看到500子类的错误了高CPU使用率—>响应慢，甚至导致宕机

磁盘I/O 磁盘I/O的性能突然下降—>使用更快的磁盘设备其他大量消耗磁盘性能的计划任务等 —> 可预期的高峰期，调整计划任务的执行时间

网卡流量比如我们常说的千兆网卡，这里的千兆其实是小b , 1Byte = 8 bit . bit 小b Byte 大B 1000Mb / 8 约等于 100MB (我们熟悉的带宽) 网卡被占满的风险增加，被占满的话，肯定访问不到数据库了，如何避免呢？通常来说， 1. 减少slave节点的数量，避免大量的复制，占用带宽 2. 合理的使用多级缓存，避免大量缓存失效，请求到DB 3. 避免使用 select * 查询，占用带宽传输 3. 分离业务网络和服务器网络等等

大表带来的风险

大表的定义

啥叫大表？粗略的定义，可以从两个维度去考虑，仅供参考

记录超过1千万
表数据文件巨大，超过10G

大表带来的风险

对查询的影响举个例子：从超巨数据中，查找区分度不高的数据，将导致大量的磁盘I/O,有可能导致数据库hang死，从而产生大量的慢查询，需要特别关注解决。
对DDL的影响建立索引耗时特别长，风险： MySQL 5.5 以前的版本，建立索引会锁表。 5.5以后的版本虽然不会引起锁表，但会引起主从延迟。 修改表结构的话，需要长时间锁表 ，风险：1. 主从延迟 2. 影响正常的数据操作

如何应对大表？

1. 分库分表（分表主键如何选择，分表后跨分区的查询和统计如何解决）慎重！！！
2. 对历史数据进行归档（归档时间点的选择、如何高效的归档）

大事务带来的风险

基本特性：ACID

事务的4个特性： ACID

原子性 atomicity | 一致性 consistency | 隔离性 isolation | 持久性 durability

原子性（atomicity）一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。举个例子：转账 A 转给B ， A账户扣减 2千， B账户增加两千。这两个必须在一个事务中，有任何一步出现问题，都不会提交，需要回滚，否则的话，A扣了2000成功，B增加2000没成功，钱就莫名其妙的少了，谁能受得了？

一致性 consistency 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。说实话，这个定义太难懂了。。。。 [1]Transactions are not a law of nature; they were created with a purpose, namely to simplify the programming model for applications accessing a database. By using transactions, the application is free to ignore certain potential error scenarios and concurrency issues, because the database takes care of them instead (we call these safety guarantees). 上述说出了为什么会出现事务：事务的产生,其实是为了当应用程序访问数据库的时候,事务能够简化我们的编程模型,不需要我们去考虑各种各样的潜在错误和并发问题。因此事务本质上是为了应用层服务的。 ACID里的AID都是数据库的特征,也就是依赖数据库的具体实现.而唯独这个C,实际上它依赖于应用层,也就是依赖于开发者. 一致性是指系统从一个正确的状态,迁移到另一个正确的状态.什么叫正确的状态呢?就是当前的状态满足预定的约束就叫做正确的状态.而事务具备ACID里C的特性是说通过事务的AID来保证我们的一致性. CASE1: A要向B转2000元,而A的账户中只有1800元,并且我们给定账户余额这一列的约束是,不能小于0.那么很明显这条事务执行会失败,因为1800-2000=-200,小于我们给定的约束了. -----------------> 这个例子里,支付之前我们数据库里的数据都是符合约束的,但是如果事务执行成功了,我们的数据库数据就破坏约束了,因此事务不能成功,这里我们说事务提供了一致性的保证 . CASE2: A要向B支付2000元,而A的账户中只有1800元,我们的账户余额列没有任何约束.但是我们业务上不允许账户余额小于0.因此支付完成后我们会检查A的账户余额,发现余额小于0了,于是我们进行了事务的回滚. -------------------> 这个例子里,如果事务执行成功,虽然没有破坏数据库的约束,但是破坏了我们应用层的约束.而事务的回滚保证了我们的约束,因此也可以说事务提供了一致性保证. (事实上,是我们应用层利用事务回滚保证了我们的约束不被破坏) CASE3: A要向B支付2000元,而A的账户中只有1800元,我们的账户余额列没有任何约束.然后支付成功了.-------------------->这里,如果按照很多人的理解,事务不是保证一致性么?直观上账户余额为什么能为负呢.但这里事务执行前和执行后,我们的系统没有任何的约束被破坏.一直都是保持正确的状态.

所以,综上.我们可以理解一致性就是:应用系统从一个正确的状态到另一个正确的状态.而ACID就是说事务能够通过AID来保证这个C的过程.C是目的,AID都是手段.

隔离性 isolation 一个事务所做的修改在最终提交以前，对其他事务是不可见的。还是转账的例子，A转账给B，A扣减，B增加。假设在A扣减完成，B未增加时，有另外一个事务，统计A账户的余额，这个时候看到的应该是A扣减前的金额。

持久性 durability

事务的操作，一旦提交，对于数据库中数据的改变是永久性的，即使数据库发生故障也不能丢失已提交事务所完成的改变。

SQL标准中的4个隔离级别

Read UnCommitted 读取未提交内容在这个隔离级别，所有事务都可以“看到”未提交事务的执行结果。在这种级别上，可能会产生很多问题，除非用户真的知道自己在做什么，并有很好的理由选择这样做。本隔离级别很少用于实际应用，因为它的性能也不必其他性能好多少，而别的级别还有其他更多的优点。读取未提交数据，也被称为“脏读”

Read Committed 读取提交内容 （oracle等大部分数据库的隔离级别） 大多数数据库系统的默认隔离级别（但是不是MySQL的默认隔离级别），满足了隔离的早先简单定义：一个事务开始时，只能“看见”已经提交事务所做的改变，一个事务从开始到提交前，所做的任何数据改变都是不可见的，除非已经提交。这种隔离级别被称为“不可重复读”。这意味着用户运行同一个语句两次，看到的结果是不同的。

Repeatable Read 可重复读（MySQL默认的隔离级别） MySQL数据库默认的隔离级别。该级别解决了READ UNCOMMITTED隔离级别导致的问题。它保证同一事务的多个实例在并发读取事务时，会“看到同样的”数据行。不过，这会导致另外一个棘手问题“幻读”。InnoDB和Falcon存储引擎通过多版本并发控制机制解决了幻读问题。
Serializable 可串行化该级别是最高级别的隔离级。它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简而言之，SERIALIZABLE是在每个读的数据行上加锁。在这个级别，可能导致大量的超时Timeout和锁竞争Lock Contention现象，实际应用中很少使用到这个级别，但如果用户的应用为了数据的稳定性，需要强制减少并发的话，也可以选择这种隔离级

Read Committed （不可重复读） VS Repeatable Read （可重复读） VS Read UnCommitted(脏读)

我们以MySQL数据库为例子，对比下这两种事务隔离级别对查询数据的影响

打开两个会话

会话一：

# 连接mysql

[root@artisan ~]# mysql -u root -p
Enter password:
 ....
 ....
 ....
 ....
 
# 切到artisan数据库
mysql> use artisan;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> desc t_test;  # 查看t_test表结构
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from t_test; # 查询数据
+----+
| id |
+----+
|  1 |
|  3 |
|  5 |
|  7 |
|  9 |
+----+
5 rows in set (0.00 sec)

mysql> show variables like '%iso%'  # 查看隔离级别 
    -> ;
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.00 sec)

mysql> begin;   # 开启事务 
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_test where id < 7;
+----+
| id |
+----+
|  1 |
|  3 |
|  5 |
+----+
3 rows in set (0.00 sec)

mysql>

然后切到会话二 ，插入几条数据

[root@artisan ~]# mysql -u root -p
Enter password: 
.....
.....
.....
mysql> use artisan;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from t_test;
+----+
| id |
+----+
|  1 |
|  3 |
|  5 |
|  7 |
|  9 |
+----+
5 rows in set (0.00 sec)

mysql> begin ;  # 开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_test values(2);  #插入数据 
Query OK, 1 row affected (0.00 sec)

mysql> commit;   #提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_test; 
+----+
| id |
+----+
|  1 |
|  3 |
|  5 |
|  7 |
|  9 |
|  2 |
+----+
6 rows in set (0.00 sec)

mysql>

重新切回到会话一，重复执行刚才的SQL （此时，会话一这个事务还未提交，还在事务中）

可以看到，在 **REPEATABLE-READ （可重复读）**这种隔离级别下， 事务一在事务内，每次查询到的数据都是一样的，而且也无法读取到事务二已经提交的数据。

这也就理解了为啥叫 “可重复读” ：因为它保证同一事务的多个实例在并发读取事务时，会“看到同样的”数据行 。

那这种事务级别潜在的问题是啥呢？ --------> 会导致另外一个棘手问题“幻读”。InnoDB和Falcon存储引擎通过多版本并发控制机制解决了幻读问题。

幻读是事务非独立执行时发生的一种现象，例如事务T1批量对一个表中某一列列值为1的数据修改为2的变更，但是在这时，事务T2对这张表插入了一条列值为1的数据，并完成提交。此时，如果事务T1查看刚刚完成操作的数据，发现还有一条列值为1的数据没有进行修改，而这条数据其实是T2刚刚提交插入的，这就是幻读

我们修改下 MySQL的隔离级别为 read-committed

我们把会话一的事务先提交了，然后修改下隔离级别

mysql> commit ;
Query OK, 0 rows affected (0.00 sec)

mysql> set session tx_isolation='read-committed';   # 设置隔离级别
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like '%iso%'   
    -> ;   #查看隔离级别
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation          | READ-COMMITTED |
+-----------------------+----------------+
2 rows in set (0.01 sec)

mysql>

然后切到会话二

回切到会话一，重新查询

所以 READ-COMMITTED 又被称为不可重复读，因为对于数据库中的某个数据，一个事务执行过程中多次查询返回不同查询结果，这就是在事务执行过程中，数据被其他事务提交修改了。每次查询都有可能查询到其他事务修改过的数据，所以称为不可重复读。

不可重复读 VS 脏读 VS 可重复读

不可重复读同脏读的区别在于，脏读是一个事务读取了另一未完成的事务执行过程中的数据，而不可重复读是一个事务执行过程中，另一事务提交并修改了当前事务正在读取的数据。
幻读和不可重复读都是读取了另一条已经提交的事务（这点同脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

总结下不同隔离级别的缺点

隔离行由低到高： Read UnCommitted —> Read Committed —> Repeatable Read -----> Serializable

并发性由高到低： Read UnCommitted —> Read Committed —> Repeatable Read -----> Serializable

MySQL修改隔离级别的方法

全局修改需要修改MySql的全局文件my.cnf (linux操作系统)

#可选参数有：READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
[mysqld]
transaction-isolation = REPEATABLE-READ

修改当前会话Session的隔离级

mysql> set session tx_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like '%iso%'
    -> ;
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.00 sec)

mysql>

MySql 的autoCommit设置

另外MySql中有autoCommit参数，默认为on，也就是开启状态

mysql>  show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

如果需要关闭autocommit，我们可以使用下面语句设置