掌握MYSQL进阶

数据运维 2023-04-19 大树手机阅读

免费学习推荐： mysql视频教程文章目录 1 前言 1.1 数据库架构 1.2 监控信息 2 影响数据库的因素 2.1 超高的QPS和TPS 2.2 大量的并发和超高的CPU使用率 2.3 磁盘IO 2.4 网卡流量 2.5 大表 2.5.1 大

    <img src="https://img.mryunwei.com/uploads/2023/04/20230419021924215.jpg"><p>免费学习推荐：mysql视频教程</p>

文章目录

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

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，那么它会带来一下几点风险：

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

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

2.5.1 大表对查询的影响

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

2.5.2 大表对DDL操作的影响

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

2.6.1 什么是事务

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

定义：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败，对于一个事务来说，不可能只执行其中的一部分操作。例子： 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）

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

2.6.7 什么是大事务

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