什么影响了MySQL性能

一时失言乱红尘 2022-05-28 09:13 312阅读 0赞

# 一、sql查询速度（后期补充） #

1、效率低下的sql

# 二、服务器硬件 #

## 1、CPU资源 ##

1、我们的应用是否CPU密集型的  
密集型则考虑需要用CPU好一点的，对目前mysql版本而言，还不支持多CPU对同一sql并发处理  
2、我们的应用并发是怎样的  
并发高的话可以看来采用多核心的CPU来提高数据库性能，但是要用最新版的mysql，因为老版本的mysql对多核CPU的支持并不好，但是最近的mysql5.6 5.7对多核CPU的支持已经有了很大的改善  
3、CPU是32位还是64位（目前这个不要考虑了，现在基本都是64位了）  
4、系统是32位还是64位，32位的系统任何进程都不能选址到4g的内存，mysql本身是一个单线程的服务，所以最好使用64位的操作系统

## 2、内存 ##

**常用的mysql引擎**  
MyISAM会把索引缓存到内存中，数据通过操作系统进行缓存  
InnoDB会同时在内存中缓存数据和索引，从而提高数据库的运行效率

**内存的大小**  
有时一味的增加内存也不能提高数据库性能，如果数据库数据就100g，而内存以达到256g那么在增加也只能提高操作系统其他的应用性能

对于内存有些人有一些误区，以后内存只对读会有作用，其实对写也有作用，数据库会对写进行一些处理，它会把多次写的操作记录，在统一写入磁盘，进行性能优化

**内存频率**  
其实内存的频率和CPU的频率是一样的，频率高的对内存的读写还是有一些提高的，而现在内存条的时候我们应该尽可能的选择主板支持的最大内存频率，当然也要考虑数据库的大小问题，数据库小的话就没必要选择特别大的内存  
![这里写图片描述][70]

# 三、网卡流量 #

![这里写图片描述][70 1]  
![这里写图片描述][70 2]

# 四、磁盘IO #

![这里写图片描述][70 3]

## 1、传统磁盘 ##

![这里写图片描述][70 4]  
![这里写图片描述][70 5]  
![这里写图片描述][70 6]  
![这里写图片描述][70 7]

## RAID模式 ##

**1、RAID 0**  
![这里写图片描述][70 8]  
![这里写图片描述][70 9]  
![这里写图片描述][70 10]  
![这里写图片描述][70 11]

**2、RAID 1**  
![这里写图片描述][70 12]  
![这里写图片描述][70 13]

**3、RAID 5**  
![这里写图片描述][70 14]  
![这里写图片描述][70 15]

**4、RAID 10**  
![这里写图片描述][70 16]  
![这里写图片描述][70 17]

![这里写图片描述][70 18]

## 2、固态存储 ##

**固态硬盘与传统硬盘相比有那些优缺点**  
![这里写图片描述][70 19]

**固态硬盘的特点**  
1、使用SATA接口可以替换传统磁盘而不需要任何的改变，但也受限于SATA接口的传输速度  
![这里写图片描述][70 20]

2、SATA接口的SSD同样支持RAID技术

**固态存储PCIe卡的特点**  
1、无法使用SATA接口，需要独特的驱动和配置  
2、价格相当于SSD要贵，但是性能比SSD更好  
3、PCI-E SSD还需消耗我们的内存，有时提高我们的IO性能还需要牺牲我们服务器的内存

![这里写图片描述][70 21]  
这说明如果我们只有一块固态硬盘的话最好是放在从数据库上，因为从数据库是单线程的，而且没有大量的写，对磁盘的损耗也会小点

## 3、使用网络存储SAN和NAS ##

## SAN ##

![这里写图片描述][70 22]

## NAS ##

![这里写图片描述][70 23]  
因为是通过网络使用文件传输协议来进行访问的，所以有一定的网络延迟

网络存储一般用作于数据备份

# 五、大表 #

## 1、什么样的表可以称之为大表 ##

1、记录行数巨大，单表超过千万行；  
2、表数据文件巨大，表数据文件超过10G

## 2、大表对查询的影响 ##

1、慢查询：很难在一定的时间内过滤出所需的数据

## 3、大表对DDL操作的影响 ##

**建立索引需要很长时间**  
风险：  
1、MySql版本<5.5建立索引会锁表  
2、MySql版本>=5.5虽然不会锁表但会引起主从延迟  
**修改表结构需要长时间锁表**  
风险：  
1、会造成长时间的主从延迟  
2、影响正常的数据操作

## 4、如何处理数据库中的大表 ##

**分库分表把一张大表分成多个小表**  
难点：  
1、分库主键的选择  
2、分表后跨分区数据的查询和统计

**大表的历史数据归档减少对前后端业务的影响**  
难点：  
1、归档时间点的选择  
2、如何进行归档操作

# 六、大事务 #

## 1、什么是事务 ##

1、事务是数据库系统区别于其他一切文件系统的重要特性之一  
2、事务是一组具有原子性的sql语句，或是一个独立的工作单元

## 2、事务的特性 ##

**1、事务的原子性**  
定义：  
一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败，对于一个事务来说，不可能只执行其中的一部分操作  
![这里写图片描述][70 24]

**2、事务的一致性**  
定义：  
一致性是指事务将数据库从一种一致性状态转换到另外一种一致性状态，在事务开始之前和事务结束后数据库中的数据的完整性没有被破坏

**3、事务的隔离性**  
定义：  
隔离性要求一个事务对数据库中的数据修改，在未提交完成对于其他事务是不可见的  
**sql标准中定义的四种隔离级别（隔离级别由低到高）**  
1、未提交读  
2、已提交读  
3、可重复读  
4、可串行化

**4、事务的持久性**  
定义：  
一旦事务提交，则其所做的修改就会永久保存到数据库中。  
此时即使系统崩溃，已经提交的修改数据也不会丢失

## 3、什么是大事务 ##

**定义：**运行时间比较长，操作的数据比较多的事务  
风险：  
1、锁定太多的数据，造成大量的阻塞和锁超时  
2、回滚时所需时间比较长  
3、执行时间长，容易造成主从延迟

## 4、如何处理大事务 ##

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

# 七、操作系統 #

1、windows下mysql数据库大小写不敏感，而linux下mysql数据库大小写敏感  
![这里写图片描述][70 25]

相信大多数MySQL数据库都会部署在Linux系统之中，尤其是生产数据库，我所用到的MySQL数据库都是部署在CentO64位操作系统上的，对操作系统默认参数的一些修改配置，可以让数据库的性能得到进一步的提升。我们最好不要把数据库装好之后马上就运行使用，而是应该将数据库的操作系统，以及数据库本身的一些参数配置好后再使用。

1.网络参数配置
    
     网络参数配置需要修改配置文件/etc/sysctl.conf，可以在配置文件后面添加一下内容：
    
    net.core.somaxconn=65536 #每个监听端口允许的最大的监听队列长度，这个值应该适当增大。
    
    net.core.netdev_max_backlog=65536 #当网络端口接收数据包的速率比内核处理数据速度快时，允许发送到对列中的最大包数
    
    net.ipv4.tcp_max_syn_backlog=65536 #还未获得链接的请求可保持在队列中的数量，当超过这个数目是，链接请求就可能被抛弃
    
    net.ipv4.tcp_fin_timeout=10 #tcp链接等待时间，调整这个参数目的是为了加快TCP链接回收的速度
    
    net.ipv4.tcp_tw_reuse=1 #加快TCP链接回收的速度
    
    net.ipv4.tcp_tw_recycle=1 #加快TCP链接回收的速度
    
    以下四个参数设置tcp链接接收和发送数据时缓冲区大小的最大值和和默认值，应该将其调整的更大一些。
    
    net.core.wmem_default=87380
    
    net.core.wmem_max=16777216
    
    net.core.rmem_default=87380
    
    net.core.rmem_max=16777216
    
    以下三个参数用于减少时效的TCP链接所占用的资源，提升资源回收效率，改的较小一些
    
    net.ipv4.tcp_keepalive_time=100 #代表TCP发送keepalive探测消息的时间间隔
    
    net.ipv4.tcp_keepalive_intvl=10 #代表当TCp发送keepalive探测消息未获得确认是重发探测消息的时间间隔
    
    net.ipv4.tcp_keepalive_probes=3 #代表TCP链接失效之前，最多发送多少keepalive探测消息
    
    2.内存相关参数
    
    内存的相关参数同样修改/etc/sysctl.conf文件
    
    kernel.shmmax=2147483648用于定义单个共享内存段的最大值，这个参数应该设置的足够大，以便共享内存段能容纳下整个InnoDB缓冲池的大小。建议设置成物理内存的一半。
    
    vm.swappiness=0 表示除非虚拟内存使用完了，否则不要是要swap分区。
    
    3.特定资源限制的配置
    
    这个配置需要修改配置文件/etc/security/limits.conf，主要的就是对打开文件数量的配置，在MySQL的InnoDB存储引擎中，每一张表就对应着2-3个文件，对于允许打开文件的数量适当的增大，可以是MySQL的数据库性能更好。
    
    打开文件数的配置
    
    * soft nofile 65535
    
    * hard nofile 65535
    
    * 表示对所有用户生效
    
    soft 代表是当前系统生效的设置
    
    hard 代表系统中所能设定的最大值
    
    nofile 表示所限制的资源是打开文件的数量
    
    4.修改磁盘调度策略 
    
    一般默认情况下，CentOS系统的磁盘调度策略是完全公平队列模式（cfq），这种调度策略对于数据库服务器来说不太适合，而应该使用deadline调度策略。通过以下命令可以修改调度策略：
    
    echo deadline>/sys/block/devname/queue/scheduler
    
    以上就是一些比较重要的配置MySQL数据库服务器系统参数的调整，调整这些系统参数后，会使MySQL服务器的性能更好，但这并不是全部的系统参数调整。可以参看Linux系统参数调优的相关数据了解更多系统参数调优。

# 八、文件系统对性能的影响 #

## 文件系统的选择 ##

![这里写图片描述][70 26]  
![这里写图片描述][70 27]

[70]: /images/20220528/6ac9d1aeeddb4326ba856184263ad350.png
[70 1]: /images/20220528/b5b587483cfe4cb69a860f69cbb253cc.png
[70 2]: /images/20220528/41945648f9da416f8b82f16dce76f561.png
[70 3]: /images/20220528/142493b1528e4c238930cd623f70a997.png
[70 4]: /images/20220528/9dcdeeb636da4653847d4383ba81566e.png
[70 5]: /images/20220528/26b334fecf794f7e918103078f4b245f.png
[70 6]: /images/20220528/71ff073f33b644e38f6ad20ee43e5ec6.png
[70 7]: /images/20220528/6d19fda120e74ef3a6fd69b19cf95017.png
[70 8]: /images/20220528/4f2052848933422ab1e4f02a859a57a9.png
[70 9]: /images/20220528/c90ec704c8c34091962c73cf78fb86fc.png
[70 10]: /images/20220528/a54c2cd673e042ea9bf470531796e626.png
[70 11]: /images/20220528/ebdaf24621534718b80a5bda0e695802.png
[70 12]: /images/20220528/922400d2f6244ac8b3bf028861c0936f.png
[70 13]: /images/20220528/b5cf7593d33d436db9c5f6b123fbf93d.png
[70 14]: /images/20220528/0b6f7fa853f548198fe2bf5b182c3265.png
[70 15]: /images/20220528/4e57ce3507f7499fb4d95c3d965e2932.png
[70 16]: /images/20220528/715111c5940a48b79b944cf816dca92b.png
[70 17]: /images/20220528/a0d7b62876104a279b09b4f1cc840f74.png
[70 18]: /images/20220528/aace17bb755c46d3802f81ef131e6d14.png
[70 19]: /images/20220528/8a4df841e25142d8a8295799ae20e430.png
[70 20]: /images/20220528/f133f541e3014443b48e0bfa344ebed1.png
[70 21]: /images/20220528/c982be9bac3a498f8aa32dd78d69805f.png
[70 22]: /images/20220528/6183917707a94dc699dadf3b3f10983e.png
[70 23]: /images/20220528/1d2d02e7ffa940e9920aca387d16f691.png
[70 24]: /images/20220528/bb614ac953c341768ee302b7ea052e9a.png
[70 25]: /images/20220528/82e17d736f334645adb589e1f76fb7ab.png
[70 26]: /images/20220528/41146c9453ae4d94bcff99b9a613ec2c.png
[70 27]: /images/20220528/018f51e9227b4794a5f9632076690202.png