掌握MYSQL进阶

php入门到就业线上直播课：进入学习

免费学习推荐：mysql视频教程

文章目录

• 1 前言
• • 1.1 数据库架构
  • 1.2 监控信息
• 2 影响数据库的因素
• • 2.1 超高的QPS和TPS
  • 2.2 大量的并发和超高的CPU使用率
  • 2.3 磁盘IO
  • 2.4 网卡流量
  • 2.5 大表
  • • 2.5.1 大表对查询的影响
    • 2.5.2 大表对DDL操作的影响
    • 2.5.3 如何处理数据库中的大表
  • 2.6 大事务
  • • 2.6.1 什么是事务
    • 2.6.2 事务的原子性（ATOMICITY）
    • 2.6.3 事务的一致性（CONSISTENCY）
    • 2.6.4 事务的隔离性（ISOLATION）
    • 2.6.5 事务的持久性（DURABILITY）
    • 2.6.7 什么是大事务

1 前言

服务器的压力来很大一部分压力来自于数据库的性能，如果没有稳定的数据库及服务器环境，那么服务器很容易出现一些故障甚至是宕机，造成的后果也是不可估量的， 因此数据库的性能优化必不可少。

1.1 数据库架构

一般的数据库架构都是一台主服务器，下面搭载着几个或十几个从服务器进行主从同步，当主服务器宕机之后，需要程序员手动选出一台数据最新的从服务器接替主服务器，然后对新的主服务器进行同步。然而有时候因为从服务器较多，导致这个过程是相当耗时的，并且在这个过程也是对网卡的容量的一个挑战。

1.2 监控信息

QPS & TPS：数值越高越好。
并发量：同一时间处理的请求的数量。
CPU使用率：越低越好。
磁盘IO：读写性能越高越好。
注意：一般公司在大促活动前后，最好不要在主库上进行数据库备份，或者在大型活动前取消这类计划，因为这会严重损耗服务器的性能。

2 影响数据库的因素

影响数据库的因素有很多，比如有：sql查询速度，服务器硬件，网卡流量，磁盘IO等等，后面我们会细说，下面介绍一下一些监控信息中反馈给我们的信息，以及我们应该如何优化它。

2.1 超高的QPS和TPS

由于效率低下的SQL，往往会造成超高的QPS和TPS的风险。在一般的大促期间，网站的访问量会大大的提高，也会提高数据库的QPS和TPS。
什么是QPS：每秒钟处理的查询量。比如我们有一个cpu的情况下，10ms可以处理一个sql，那么1s就可以处理100个sql，QPS<=100，但当我们100ms才处理一个sql，那么我们1s钟才能处理10个sql，QPS<=10，这两种情况是相差很大的，因此尽量优化sql性能。

2.2 大量的并发和超高的CPU使用率

那在这种情况下会导致什么风险呢？
在大量的并发下，可能会导致数据库连接数被占满，这种情况下，尽量将数据库参数 max_connections设置的大一点（默认值为100），如果超过了这个值的时候会出现报500错误的情况。
在超高的CPU使用率下，会因CPU资源耗尽而出现宕机。

2.3 磁盘IO

数据库的瓶颈之一就是磁盘IO，那么它会带来一下几点风险：

1. 磁盘IO性能突然下降
   这往往会发生在热数据大于服务器可用内存的情况下。 通常这种情况我们只能使用更快的磁盘设备来解决。
2. 其他大量消耗磁盘性能的计划任务
   这一点我们上面也提到了，最好避免在大促之前在主数据库上进行备份，或在从服务器上进行，调整计划任务，做好磁盘维护。

2.4 网卡流量

显而易见，网卡流量过大造成网卡IO被占满的风险。
一般的网卡是千兆网卡（1000Mb/8 ≈ 100MB）
如果连接数超过了网卡最大容量的时候，就会出现的服务无法连接的情况，那么我们应该如何避免无法连接数据库的情况：

1. 减少从服务器的数量
   因为每个从服务器都要从主服务器上面复制日志，因此从服务器越多，网卡流量就越大。
2. 进行分级缓存
   一定要避免前端缓存突然失效而导致的后端访问量突然变大的情况。
3. 避免使用 select *进行查询
   这是一种最基本的数据库优化的方法，查询出没有必要的字段也会消耗大量的网络流量。
4. 分离业务网络和服务器网络
   这样可以避免主从同步或网络备份影响网络的性能。

2.5 大表

什么样的表可以称之为大表？其实都是相对而言，对于不同存储引擎会有不同的限制。像nosql的数据存储，并没有限制表的行数，理论上只要磁盘的容量允许，都可以进行存储。但当一张表的行数超过千万行的时候，就会对数据库的性能产生很大的影响。那么我们可以总结大表的特点：

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

但就算符合了以上的特点，它也可能对我们数据库性能不会产生很大的影响，因此这个说法是相对的，比如像一般数据库的日志表，即使记录行数很大，文件大小很大，但我们一般只对它进行增加和查询，不涉及大量的i修改和删除操作，因此不会对数据库性能产生很大的影响。
但当有一天因为业务发生变更，需要对这张10个G的日志表进行列增加的时候，那么这个工程量无疑是巨大的。

2.5.1 大表对查询的影响

大表往往代表着慢查询的产生，慢查询即是指很难在一定的时间内过滤出所需要的数据。
例如在一个上千万条数据的日志表上，有一个叫做订单来源的字段，它记录着订单是在哪一个平台上进行生成的。在一开始业务不需要的情况下，是不会对数据库性能造成影响的，但是后面由于业务需求，需要查看这上千万条数据的具体来自于哪一个平台的订单量，这一下就产生了很大的问题。
因为由于这些订单的来源渠道只有几个，区分度很低，所以在上千万的数据中查询某一些数据的话，这会消耗大量的磁盘IO，严重降低了磁盘的效率。在用户每一次查看订单的时候，都会从数据库查询一次订单的来源，这样会产生大量的慢查询。

2.5.2 大表对DDL操作的影响

大表对DDL操作的影响，这会给我们带来什么风险？

1. 在建立索引上需要很长的时间
   在MySQL的5.5版本之前，数据库在建立索引的时候会进行锁表，而在5.5版本之后，虽然不会锁表，但是由于MySQL的复制机制是在新的主机上执行，然后才能通过日志方式发送给从机，这样会引起长时间的主从延迟，影响正常的业务。
2. 修改表结构需要长时间锁表
   在修改表的结构过程中进行锁表，会给我们造成长时间主从延迟的风险。由于我们MySQL的主从复制机制，往往是所有的表结构操作是在主机上先执行完成再通过日志方式传给从机进行相同的操作，然后才完成表结构的主从复制。
   假设我们修改一个表的结构，在主服务器上修改的时间为480s，那么我们在从数据库上的修改时间也为480s。由于现在MySQL的主从复制都是使用单线程，所以一旦有大表修改，在从服务器上没有完成相关操作之前，其他的数据修改操作都无法执行，因此这会造成至少480s以上的主从延迟。
   同时会影响数据的正常操作，这会造成所有的插入操作被阻塞，连接数会大额提高并占满服务器，这时就会导致服务器出现500的连接错误。

2.5.3 如何处理数据库中的大表

1. 分库分表，把一张大表分成多个小表
   难点：
   1. 分表主键的选择
   2. 分表后跨分区数据的查询和统计
2. 大表的历史数据归档
   作用：减少对前后端业务的影响
   难点：
   1. 归档时间点的选择
   2. 如何进行归档操作

2.6 大事务

2.6.1 什么是事务

1. 事务是数据库系统区别于其它一切文件系统的重要特性之一。

2. 事务是一组具有原子性的SQL语句，或是一个独立的工作单元。+
   因此事务需要符合以下4个特性：原子性，一致性，隔离性，持久性。

2.6.2 事务的原子性（ATOMICITY）

定义：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败，对于一个事务来说，不可能只执行其中的一部分操作。
例子：
A要转给B1000元，在A账户中取出1000元时，数据库上A的余额减去1000，但是在加到B余额上的时候，服务器出现了故障，那A的1000元需要回退到A的账户中，保持事务原子性，要么一起成功，要么一起失败。

2.6.3 事务的一致性（CONSISTENCY）

定义：一致性是指事务将数据库从一种一致性状态转换到另外一种一致性状态，在事务开始之前和事务结束后数据库中数据的完整性没有被破坏。
例子：
银行中A的1000块转给了B，A的余额为0，B的账户余额从0变为1000，但是从头到尾，A+B = 1000（A的余额） + 0（B的余额） = 0（A的余额） + 1000（B的余额），也就是说，A和B的总余额数是不变的，从头到尾还是1000元。

2.6.4 事务的隔离性（ISOLATION）

定义：隔离性要求一个事务对数据库中数据的修改，在未提交完成对于其他事务时不可见的。
例子：
银行中A从余额1000元中取款500元，在取款事务还没提交前，执行了一个查询A账户余额的事务，那查询出来的结果还是余额1000元，因为在取款事务还没提交之前，其他业务对它的事务过程是不可见的。

• SQL标准中定义的四种隔离级别

  • 未提交读（READ UNCOMMITED）

    • 未提交的事务对外可见，就是我们常说的脏读，查询到的数据称之为脏数据。

  • 已提交读（READ COMMITED）

    • 很多的数据中默认的隔离级别，在事务提交之后才能读出数据，也就是事务对外不可见。

  • 可重复读（REPEATABLE READ）

    • 比已提交读更高一层的级别，在可重复读的隔离级别事务中，一个未提交的事务中查询表中的数据，在另外一个事务中向这张表插入一条数据并提交，但当回到刚刚未提交的事务中再查询一次表的数据和上一次查询到的结果是相同的，并没有查询到刚刚插入的那一条数据。
    • 但在已提交读的隔离级别中是可以查到刚刚的那条数据的
    • 查看当前数据库的隔离级别语句：
      show variables like '%iso%'
    • 修改当前数据库隔离级别语句：
      set session tx_isolation='read-committed'

  • 可串行化（SERIALIZABLE）

    • 最高的隔离级别。简单来说就是会在读取的每一条数据上都加锁，所以可能会导致大量的锁超时和锁占用的问题，因此在实际业务中我们很少使用这个隔离级别。除非是严格要求数据一致性，并且可以接受在没有并发的情况下，我们才会考虑使用这个隔离级别。

  • 隔离性：

    • 未提交读 < 已提交读 < 可重复读 < 可串行化

  • 并发性：

    • 未提交读 > 已提交读 > 可重复读 > 可串行化

2.6.5 事务的持久性（DURABILITY）

定义：一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，已经提交的修改数据也不会丢失（不包括磁盘损坏等物理因素）。
例子：
银行中A用户存入账户1000元，事务提交后，即使银行系统崩溃，但在恢复回来后，除非A对余额进行了操作，否则A账户中的1000元是不会变的，这就是事务的持久性。

2.6.7 什么是大事务

讲了这么多，那什么是大事务？
大事务就是指运行时间比较长，操作的数据比较多的事务。举例来说，有一个理财产品每天都会统计每个用户前一天的收入所得，那如果需要统计所有用户的收入所得并更新到用户余额中，这时数以亿计的更新就需要数小时，如果中途出现的出现故障进行的回滚，事务需要进行的时间就更加不可估量，还不包括在更新过程中，会对用户的余额进行加锁，造成所有用户都无法使用余额这样的问题。

• 大事务会造成哪些风险：

1. 锁定太多的数据，造成大量的阻塞和锁超时
2. 回滚时所需的时间比较长
3. 执行时间长，容易造成主从延迟

• 如何避免大事务？

1. 避免一次处理太多的数据。
2. 移出不必要的事务中的SELECT操作。

能做到以上的两点基本可以避免大事务的产生。

更多相关免费学习推荐：mysql教程(视频)

以上就是掌握MYSQL进阶的详细内容，更多请关注php中文网其它相关文章！

千万级数据并发解决方案（理论+实战）：点击学习

Mysql单表千万级数据量的查询优化与性能分析

Mysql主从原理及其在高并发系统中的应用