MySQL 性能调优——数据库性能影响因素

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/smartbetter/article/details/91418494

数据库性能影响因素包括 sql 查询速度、大表和大事务、数据库存储引擎、数据库参数配置、服务器硬件、网卡流量、磁盘 IO 等因素。

需要注意的是 MySQL 5.7 是单线程的服务,并不支持多 cpu 并发运算,意味着每条 sql 只能用到 1 个 cpu。

这里 MySQL 选择 5.7,Linux 选择 CentOS 7.2。

1.数据库性能影响因素

1.sql查询速度

最常见的影响因素是在超高 QPS 和 TPS 的服务中 ,存在效率低下的 sql。

https://blog.csdn.net/smartbetter/article/details/93502395

2.大表和大事务

1、大表带来的问题:

一般 MySQL 单表行数超过千万行 或者 单表数据文件超过 10GB(如果使用的是Flash IO则这个限制会再大一些)就会对数据库性能造成影响。

大表对 DDL(数据定义语言)操作的影响 :

  • 建立索引需要很长时间。MySQL 版本 < 5.5 建立索引会锁表,MySQL 版本 >= 5.5 虽然不会锁表但会引起主从延迟。
  • 修改表结构需要长时间锁表。会造成长时间的主从延迟,因为目前MySQL的主从复制都是使用单线程,所以一旦有大表的修改在从服务器上没有完成修改操作之前,其他所有数据库的修改操作都会无法执行;在主库上进行表结构修改,这个表的所有操作都会被阻塞,影响正常的数据操作。

如何处理数据库的大表?

  • 分库分表把一张大表分成多个小表(难点:分表主键的选择;分表后跨分区数据的查询和统计);
  • 另外一种处理方式就是对大表的历史数据归档,减少对前后端业务的影响(难点:归档时间点的选择;如何进行归档操作);

2、大事务给我们带来的问题:

事务是一组具有原子性的 SQL 语句,或是一个独立的工作单元。事务要符合原子性、一致性、隔离性、持久性。

事务的特性 说明
原子性 一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败。
一致性 事务将数据库从一种一致性状态转换到另外一种一致性状态,在事务开始之前和事务结束后数据库中数据的完整性没有被破坏。
隔离性 隔离性要求一个事务对数据库中数据的修改,在未提交完成前对于其它事务是不可见的。
SQL 标准中定义了四种隔离级别(隔离性由低到高,并发性由高到低):
未提交读(READ UNCOMMITED);已提交读(READ COMMITED);可重复读(REPEATABLE READ);可串行化(SERIALIZABLE)。
持久性 一旦事务提交,则其所做的修改就会永久保存到数据库中。此时即使系统崩溃,已经提交的修改数据也不会丢失。

大事务是指运行时间比较长,操作的数据比较多的事务。

大事务带来的问题 :

  • 锁定太多的数据,造成大量的阻塞和锁超时;
  • 回滚时所需时间比较长;
  • 执行时间长,容易造成主从延迟。

如何处理数据库的大事务?

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

3.数据库存储引擎

数据库存储引擎的选择会对性能产生直接的影响。

https://blog.csdn.net/smartbetter/article/details/91492332

4.数据库参数配置

前面所有因素加起来对性能的影响可能也没有数据库参数配置对性能的影响大:

数据库参数配置对性能的影响巨大

数据库参数配置一般都是由专业运维人员进行优化。

5.服务器硬件

大量的并发会导致数据库连接数被占满,超高的 cpu 使用率会因 cpu 资源耗尽而出现宕机,通常连接量往往要比并发量多很多,建立众多连接中,通常只有几个是在请求数据库处理的,其他的大多数连接都会处于 sleep 状态,数据库所能最多建立的连接数由 max_connections 参数来决定,默认 100。

优化方案:

  1. 选择最合适的服务器硬件。

常见的问题:

1、选择 32 位还是 64 位的 CPU?

建议选择 64 位 CPU。

2、64 位 CPU 运行 32 位的操作系统会有影响吗?

MySQL 是单线程的服务,会对 MySQL 性能有一个极大的限制,64 位 CPU 一定要工作在 64 位操作系统下。

3、CPU核数重要还是频率重要?

对于并发比较高的场景,CPU的数量比频率重要;对于CPU密集型场景和复杂SQL则频率越高越好。

4、内存是不是越大越好?

内存对性能的影响是有限的,并不能无限的增加性能,一般当缓存的数据和磁盘存储的数据一样时(也就是所有的数据都缓存到内存中),再增加内存的大小就没有什么意义了。但是这也不是绝对的,多余的内存也可以增加操作系统等其他服务的性能。

5、如何选择内存的主频?

内存的主频越高速度越快,选择内存时最好选择主板所能使用的最高频率的内存。

6、多通道内存需要注意什么?

每个通道的内存选择相同的品牌、颗粒、频率、电压、校验技术和型号,单条容量要尽可能大。

7、比如目前数据库热数据的大小可能有 100 GB,那选择多大内存?

一般选择 128GB 内存就足够了。如果考虑到数据的增长幅度,选择 256GB 可能会更好,避免了短时间内升级内存的问题。

8、磁盘的配置与选择?

按需选择。

传统机械硬盘:可以使用 RAID(磁盘冗余队列,可以把多个容量较小的磁盘组成一组容量更大的磁盘,并提供数据冗余来保证数据完整性的技术)增加传统机械硬盘的性能。

固态存储:相比机械磁盘,固态磁盘有更好的随机读写性能,能更好的支持并发,但是也更容易损坏。常见的固态存储有 SSD 和 PCI-E SSD(性能更好,但是PCI-E还会使用CPU和内存资源,提升了I/O性能,但是牺牲了服务器资源)。固态存储适用于存在大量随机I/O的场景和解决单线程负载的I/O瓶颈。

9、数据库备份?

通常使用网络存储 SAN 和 NAS 解决。

SAN 和 NAS 是两种外部文件存储设备加载到服务器上的方法。这两种设备的不同点是 SAN 设备通过光纤连接到服务器,设备通过块接口访问,服务器可以将其当做硬盘使用,可以承受大量顺序读写操作。NAS 设备使用网络连接,通过基于文件的协议如 NFS 或 SMB 来访问,通常会有一定的网络延迟。

6.网卡流量

网卡 IO 被占满,例如千兆网卡 1000Mb/8 = 125MB,这时如果有新的连接连接数据库,就会出现无法连接数据库的情况。

优化方案:

  1. 减少从服务器的数量;
  2. 进行分级缓存;
  3. 避免使用 select * 进行查询;
  4. 分离业务网络和服务器网络。

7.磁盘IO

磁盘 IO 性能突然下降。

优化方案:

  1. 可以考虑使用使用更快的磁盘设备,比如 RAID 卡、SSD、Fusion-IO 等;
  2. 检查是否存在其他大量消耗磁盘性能的计划任务,如果存在,则调整计划任务,做好磁盘维护。
展开阅读全文

没有更多推荐了,返回首页