mysql性能

浅浅的花香味﹌ 2023-07-12 09:22 16阅读 0赞

**目录**

一、有哪些因素影响mysql性能

二、性能优化的大致思路

三、MySQL性能优化细节

四、数据库优化具体思路

--------------------

### 一、有哪些因素影响mysql性能 ###

![format_png][]

在一个类似此结构的服务器架构是哪些方面影响该服务器性能：

QPS：每秒钟处理的查询量；sql查询速度，效率低下的sql会随着访问量来严重影响效率；比如10ms处理1个sql，那么QPS<=100

TPS：

并发量&CPU使用率：并发量是指同一时间处理请求的数量,大并发导致数据库连接被占满；超高的cpu资源耗尽而出现宕机；

磁盘IO：磁盘吞吐量；风险磁盘IO性能突然下降或者大量消耗磁盘性能的计划任务（调整计划）

网卡流量：网卡io被占满，（减少从服务器的数量，进行分级缓存，避免selet \* 操作，）

###  二、性能优化的大致思路 ###

1、首先需要使用慢查询功能，去获取所有查询时间比较长的SQL语句。  
MySQL——慢查询  
2、其次使用explain命令去查看有问题的SQL的执行计划。  
MySQL——执行计划EXPLAIN  
3、最后可以使用show profile\[s\] 查看有问题的SQL的性能使用情况。  
MySQL高级：show profile

### 三、MySQL性能优化细节 ###

1、合理的创建及使用索引（考虑数据的增删情况）。  
2、合理的冗余字段（尽量建一些大表，考虑数据库的三范式和业务设计的取舍）。  
3、使用SQL要注意一些细节：  
select语句中尽量不要使用\*、count(\*)，  
WHERE语句中尽量不要使用1=1、in语句（建议使用exists）、  
注意组合索引的创建顺序按照顺序组着查询条件、  
尽量查询粒度大的SQL放到最左边、  
尽量建立组合索引。  
4、合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况。

5、表关联查询时务必遵循小表驱动大表原则。  
6、使用查询语 where 条件时，不允许出现函数，否则索引会失效；  
7、使用单表查询时，相同字段尽量不要用 OR，因为可能导致索引失效，比如：SELECT \* FROM table WHERE name = '手机' OR name = '电脑'，可以使用 UNION 替代；  
8、LIKE 语句不允许使用 % 开头，否则索引会失效；  
9、组合索引一定要遵循 从左到右 原则，否则索引会失效；比如：SELECT \* FROM table WHERE name = '张三' AND age = 18，那么该组合索引必须是 name,age 形式；  
10、索引不宜过多，根据实际情况决定，尽量不要超过 10 个；  
11、每张表都必须有 主键，达到加快查询效率的目的；  
12、分表，可根据业务字段尾数中的个位或十位或百位（以此类推）做表名达到分表的目的；  
13、分库，可根据业务字段尾数中的个位或十位或百位（以此类推）做库名达到分库的目的；  
14、表分区，类似于硬盘分区，可以将某个时间段的数据放在分区里，加快查询速度，可以配合 分表 + 表分区 结合使用；

### 四、数据库优化具体思路 ###

1、优化什么？

在数据库优化上有两个主要方面：即安全与性能

安全 ---------------------- 可持续性

性能 ---------------------- 数据的高性能访问

2、优化的范围有哪些？

存储、主机和操作系统方面:

*      主机架构稳定性
 *      I/O规划及配置
 *      swap交换分区
 *      OS内核参数和网络问题

应用程序方面：

*      应用程序稳定性
 *      SQL语句性能
 *      串行访问资源
 *      性能欠佳会话管理
 *      这个应用适不适合用MySQL

数据库优化方面：

*       内存
 *       数据库结构（物理&逻辑）
 *       实例配置

说明：不管是在，设计系统，定位问题还是优化，都可以按照这个顺序执行。

3、优化维度

数据库优化维度有四个:

硬件、系统配置、数据库表结构、SQL及索引

![format_png 1][]

优化选择

![format_png 2][]![format_png 2][]

4、优化工具有啥？

4.1数据库层面 ：

检查问题常用工具

![format_png 3][]

不常用但好用的工具

![format_png 4][]

4.2 数据库层面问题解决思路

一般应急调优的思路：

针对突然的业务办理卡顿，无法进行正常的业务处理！需要立马解决的场景！

![format_png 5][]

![format_png 4][]

常规调优思路：

针对业务周期性的卡顿，例如在每天10-11点业务特别慢，但是还能够使用，过了这段时间就好了。

![format_png 6][]

4.3 系统层面

cpu方面

vmstat、sar top、htop、nmon、mpstat

内存

free、ps -aux 、

IO设备（磁盘、网络）

iostat、 ss 、 netstat 、 iptraf、iftop、lsof、

vmstat 命令说明：

![format_png 7][]

iostat命令说明

实例命令： iostat -dk 1 5

iostat -d -k -x 5 （查看设备使用率（%util）和响应时间（await））

![format_png 8][]

4.4 系统层面问题解决办法

你认为到底负载高好，还是低好呢？

在实际的生产中，一般认为 cpu只要不超过90%都没什么问题 。

当然不排除下面这些特殊情况：

问题一：cpu负载高，IO负载低

![format_png 9][]

问题二：IO负载高，cpu负载低

![format_png 10][]

问题三：IO和cpu负载都很高

硬件不够了或sql存在问题

5 基础优化

1.5.1 优化思路

定位问题点:

硬件 --> 系统 --> 应用 --> 数据库 --> 架构（高可用、读写分离、分库分表）

处理方向

明确优化目标、性能和安全的折中、防患未然

5.2 硬件优化

主机方面：

![format_png 11][]

cpu的选择：

![format_png 12][]

内存的选择：

![format_png 13][]

存储方面：

![format_png 14][]

raid卡：主机raid卡选择：

![format_png 15][]

网络设备方面：

使用流量支持更高的网络设备（交换机、路由器、网线、网卡、HBA卡）

注意：以上这些规划应该在初始设计系统时就应该考虑好。

5.3 服务器硬件优化

![format_png 16][]

5.4 系统优化

Cpu：

基本不需要调整，在硬件选择方面下功夫即可。

内存：

基本不需要调整，在硬件选择方面下功夫即可。

SWAP：

MySQL尽量避免使用swap。阿里云的服务器中默认swap为0

IO ：

![format_png 17][]  
![format_png 18][]

这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。

修改MySQL的配置参数innodb\_flush\_method，开启O\_DIRECT模式。这种情况下，InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘，但是redo log依旧会使用文件系统cache。值得注意的是，Redo log是覆写模式的，即使使用了文件系统的cache，也不会占用太多

IO调度策略

![format_png 19][]

5.5 系统参数调整

Linux系统内核参数优化

![format_png 20][]

用户限制参数（mysql可以不设置以下配置）

![format_png 21][]

5.6 应用优化

业务应用和数据库应用独立,防火墙：iptables、selinux等其他无用服务(关闭)：

![format_png 22][]

安装图形界面的服务器不要启动图形界面 runlevel 3,另外，思考将来我们的业务是否真的需要MySQL，还是使用其他种类的数据库。用数据库的最高境界就是不用数据库。

6、数据库优化

SQL优化方向：

执行计划、索引、SQL改写

架构优化方向：

高可用架构、高性能架构、分库分表

6.1 数据库参数优化

调整：

实例整体（高级优化，扩展）

![format_png 23][]

连接层（基础优化）

设置合理的连接客户和连接方式

![format_png 24][]

SQL层（基础优化）

query\_cache\_size： 查询缓存

OLAP类型数据库,需要重点加大此内存缓存.

但是一般不会超过GB.

对于经常被修改的数据，缓存会立马失效。

我们可以实用内存数据库（redis、memecache），替代他的功能。

6.2 存储引擎层（innodb基础优化参数）  
![format_png 25][]

[format_png]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9pbWcyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvMTE5MTQyMi8yMDE4MTIvMTE5MTQyMi0yMDE4MTIxMTIzMDUxMDcwNS0xMDU2MzIzMTM2LnBuZw?x-oss-process=image/format,png
[format_png 1]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLWEzOWZlZGFjNDdlOTFjNGQ?x-oss-process=image/format,png
[format_png 2]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLWIxYmRkODU0YjQ0NDc2OGUucG5n?x-oss-process=image/format,png
[format_png 3]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLWU2YjI0MmE3ZDIyZGEwMDYucG5n?x-oss-process=image/format,png
[format_png 4]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLTNlYzIxYWVjMDZmNWI4M2MucG5n?x-oss-process=image/format,png
[format_png 5]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLWIxMTIwYzhiOWI5M2E3YmEucG5n?x-oss-process=image/format,png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLTIxZjcxMTRkYmYyNDI3ZjEucG5n?x-oss-process=image/format,png
[format_png 7]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLTM2ODg1N2MxNTI0M2M3ODAucG5n?x-oss-process=image/format,png
[format_png 8]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLWI0NWFlNWQxZTg2MmU5M2EucG5n?x-oss-process=image/format,png
[format_png 9]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLTIxZGVkYTZhNmQ3YjAyZjIucG5n?x-oss-process=image/format,png
[format_png 10]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLTBmMzZmZTQ2OGZlOWNkZTEucG5n?x-oss-process=image/format,png
[format_png 11]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLTY5NGZiMGU4MzhlM2U3YjYucG5n?x-oss-process=image/format,png
[format_png 12]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLTE4ZDJmMmJhMzk2YzliNDcucG5n?x-oss-process=image/format,png
[format_png 13]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLTgzMmRkNjRkNDBhZjA3MTMucG5n?x-oss-process=image/format,png
[format_png 14]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLWM4ZDhmMjIwMTUzZGU4ZmIucG5n?x-oss-process=image/format,png
[format_png 15]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLTVlMjFkNDdjOWIxYjA4OGUucG5n?x-oss-process=image/format,png
[format_png 16]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLTQ2MDhjMWVmZmUzNzU0MWMucG5n?x-oss-process=image/format,png
[format_png 17]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLWJhNWRlYzllYzA5NzJmYTIucG5n?x-oss-process=image/format,png
[format_png 18]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLTdlMjM3NTA2NWJlOTBhZmQucG5n?x-oss-process=image/format,png
[format_png 19]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLTRjZjdhZWY1ODIxYzQ5ZmQucG5n?x-oss-process=image/format,png
[format_png 20]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLWYxNWY1YzM0OWFjN2NmODkucG5n?x-oss-process=image/format,png
[format_png 21]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLTJlMzk1NWY2YmU1MTRhZDUucG5n?x-oss-process=image/format,png
[format_png 22]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLTJlNWZmNjAzZTc2Njk2ODQucG5n?x-oss-process=image/format,png
[format_png 23]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLTk5Y2IxZGNmOTkzOThjNDgucG5n?x-oss-process=image/format,png
[format_png 24]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLTVmYWRkOWYxZjAzMDI0ZjYucG5n?x-oss-process=image/format,png
[format_png 25]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91cGxvYWQtaW1hZ2VzLmppYW5zaHUuaW8vdXBsb2FkX2ltYWdlcy83NTY0NTAxLThlODM0ZDkzMTFkMTI2NjUucG5n?x-oss-process=image/format,png