mysql优化之影响mysql性能因素一

不念不忘少年蓝@ 2022-06-01 12:54 232阅读 0赞

web服务器可以进行横向扩展，每台服务器只要配置相同就可以进行横向扩展，理论上可以无限制的增加web服务器。数据库服务器才是大数据量访问时延迟高效率低的主要问题，没有稳定的数据库就没有稳定的访问。

影响数据库因素主要因素总结如下：

1.  sql查询速度
2.  网卡流量
3.  服务器硬件
4.  磁盘IO

以上因素并不是时时刻刻都会影响我们的数据库性能，而就像木桶效应一样：如果其中一个因素严重影响性能，那么整个数据库性能就会严重受阻。另外，这些影响因素都是相对的，例如：当数据量并没有达到百万千万这样的级别，那么sql查询速度也许就不是个重要因素，换句话说，你的sql语句效率适当低下可能并不影响整个效率多少，反之，这种情况，无论如何怎么优化sql语句，可能都没有太明显的效果。 因此，知道哪些影响因素会直接导致哪些现象产生，是至关重要的经验，就像福尔摩斯一样，通过现象看本质。接下来我们对不同的现象与影响因素做一一对应的总结。

一主多从服务器集群的缺陷: 没有任何主从复制的高可用组件，一旦出现问题就只能手动进行主从复制的切换。耗时又费力。

#### 1.超高的QPS和TPS ####

> 什么是QPS：每秒钟查询量。如果每秒钟能处理100条查询sql语句，那么QPS就约等于100  
> 什么是TPS：每秒钟事务处理的数量。

风险：效率低下的SQL  
在大促的情况下，访问量暴增。这种情况下，sql语句的优化显得最直接最有效。由于现在的mysql不支持多cpu并发运算，即每条sql只能由一条cpu执行。这也就意味着，如果我们想提高单挑sql的执行速度，无法通过增加cpu的方式达到效果。

#### 2.大量的并发和超高的CPU使用率 ####

风险：大量的并发：同一时间处理请求的数量 大量的并发就会导致数据库连接数占满 。

对于数据库而言，所能建立的连接数是有限的，mysql中max\_connections参数默认值是100, 大量的并发就会导致数据库连接数占满 。

#### 3.超高的CPU使用率：因CPU资源耗尽而出现宕机 ####

1.磁盘IO,风险：磁盘IO性能突然下降。

> 数据库的主要瓶颈, fasionIO高速磁盘, SSD固态硬盘比较好  
> 其他大量消耗磁盘性能的计划任务  
> 不要再主数据库上进行数据库备份 可能会导致数据库性能急剧下降

#### 4.网卡流量 ####

风险：网卡IO被占满（100Mb/8=100MB）

如何避免无法连接数据库的情况：

1.  减少从服务器的数量。从服务器都要从主服务器上复制日志，所以，从服务器越多，网络流量越大。
2.  进行分级缓存。前方大量缓存突然失效会对数据库造成严重的冲击。
3.  避免使用“select \* ”进行查询 分
4.  离业务网络和服务器网络

#### 5.大表 ####

什么样的表可以称为大表  
1. innodb存储引擎时的那个表的数据量超过千万行  
2. 表的数据文件超过10G （数据是相对的）。  
3. 表数据文件巨大，表数据文件超过10G

造成的影响主要就是

1.  慢查询：
    
    > 很难在一定的时间内过滤出所需要的数据
2.  DDL操作
    
    > DDL(数据定义操作) 修改表就会长时间锁表,造成主从延迟,如建立索引 。  
    > 修改表的结构就会长时间的锁表 造成主从延迟  
    > （主库完成=> 日志=> 从库完成）

风险：

> MySQL版本<5.5建立索引会锁表，>=5.5虽然不会锁表但会引起主从延迟。

如何处理数据库中的大表分库分表：

1.  把一张大表分成多个小表

> 分表主键的选择。如订单号，订单地区等，好的分表主键选择，会对后期的扩展更有利  
> 分表后跨分区数据的查询和统计。  
> 分库分表需要消耗大量的人力物力，而且要冒着影响业务的风险，所以要慎重。

1.    
    
2.  历史数据归档  
    大表的历史数据归档，可以减少对前后端业务的影响。

> 归档时间点的选择。例如订单表，可以对一年前的数据进行归档；日志类的数据，对一个月前的数据归档即可。  
> 如何进行归档操作。

#### 6.大事务 ####

> 事务是数据库系统区别于其他一切文件系统的重要特性之一  
> 事务是一组具有原子性的SQL语句  
> 事务特性：原子性，一致性，隔离性，持久性

1.  原子性：  
    一个事务必须被视为一个不可分割的最小工作单元。整个事务要么全部提交成功，要么全部失败。  
    例如：银行转账，我向你汇钱，要么成功，我的账户减少1000元，你的账户增加1000元。要么失败，我不减，你也没有增加。不能出现：我的账户减少1000，这时候断电了，你没收到。
2.  一致性：  
    事务将数据库从一种一致性状态转换到另一种一致性状态，在事务开始之前和事务结束之后数据库中的数据的完整性没有被破坏例如：银行转账，转来转去，总和应该保持不变。在我看开，一致性其实就是宏观上强调了一下原子性。只要原子性原则没有被破坏，应该就总是一致的。
3.  隔离性：  
    一个事务对数据库中的数据进行修改，在未提交完成前对其他事务是否可见的。  
    隔离性有四种级别：
    
    > 未提交读（READ UNCOMMITED）事务可以读取未提交的数据 =>脏读。  
    > 已提交读（READ COMMITED）事务只能读取已提交的数据  
    > 可重复读（REPEATABLE READ）同一个事务中多次读取的数据是一致的  
    > 串行化（SERIALIZABLE）读取的每一行都加锁  
    > 隔离性由低到高 并发性由高到低  
    > 查看数据库的隔离级别:show variables like “%iso%”;
4.  持久性

> 一旦事务提交，则其所做的修改就会永久保存到数据库中。

什么是大事务?

> 运行时间比较长，操作数据比较多的事务

风险：

> 运行时间长 操作数据比较多的事务  
> 锁定大量的数据 造成大量的阻塞和锁超时  
> 回滚需要的时间比较长 回滚时数据也会被锁定也会造成主从延迟  
> 解决方法：避免一次性处理大量数据 可以分批处理数据  
> 移除不必要的select操作