mysql数据库性能优化参考

原文链接：https://blog.csdn.net/qq_34777982/article/details/125788079

硬件和操作系统层面的优化
硬件：cpu、内存、磁盘io、网络带宽
操作系统：
应用文件句柄（ulimit -a open files）
网络配置

架构设计层面的优化
集群方式（主从集群或者主主集群）：避免单点故障
读写分离：读写分开，将压力分担，避免读写冲突，提高性能
分库分表：分库降低单一服务器的IO压力；分表降低单表数据量，提升sql查询效率
分布式数据库：针对热点数据，使用redis、mongodb等降低直接查询mysql的压力，提升数据检索性能

数据库配置优化
存储引擎的选择：http://c.biancheng.net/view/7185.html
最大连接数：max_connections 默认151 上限16384 建议值：实际连接数是最大连接数的85%较为合适
binlog日志开启：log_bin
binlog日志格式：binlog_format=row
连接超时时间：wait-timeout=600 默认8小时
并发线程数：innodb_thread_concurrency 默认0无限制 建议值：设置为cpu核心数的两倍
请求堆栈：back_log 默认50 建议值：设置为最大并发连接数的20%～30%
缓存池配置：innodb_buffer_pool_size 建议值：主机内存的70%～80%

参考资料：
https://wenku.baidu.com/view/ebaefb0513a6f524ccbff121dd36a32d7375c733.html

关于配置项的修改，需要关注两个层面，第一个是配置的作用域，它可以分为会话级别和全局范围。第二个是是否支持热加载，针对这两个点，我们需要注意的是，全局参数的设定，对于已经存在的会话是无法生效的，会话参数的设定，随着会话的销毁而失效。第三个是全局类的统一配置，建议配置在默认配置文件中，否则重启服务会导致配置失效。

SQL执行优化
慢sql定位和排查：慢查询日志、慢查询日志分析工具 >> 获取到慢sql
执行计划分析：关键字explain，重点关注type，key，rows，filterd等字段，从而去定位该SQL执行慢的根本原因，再去有的放矢的进行优化。
show profile工具：MySQL提供的可以用来分析当前会话中SQL语句资源消耗情况的工具，可以用于SQL调优的测量，在当前会话中，默认情况下，show profile是关闭状态，打开以后会保存，最近15次的运行结果，针对运行慢的SQL通过profile工具进行详细分析，可以得到SQL执行过程中所有资源的开销情况，比如io开销，cpu开销，内存开销。
示例：

-- 查看当前是否开启此功能
show variables like '%profiling%';
-- 开启
set profiling=1;
-- 查看具体执行时间
show profiles;
-- 查看详细的每个步骤时间
show profile for query 1;
-- 显示所有性能信息
show profile all for query n
--  block io：显示块IO操作次数
show  profile block io for query n
-- context switches：显示上下文切换次数，被动和主动
show profile context switches for query n
-- cpu：显示用户cpu时间、系统cpu时间
show profile cpu for query n
-- IPC：显示发送和接受的消息数量
show profile ipc for query n
-- page faults：显示页错误数量
show profile page faults for query n
-- source：显示源码中的函数名称与位置
show profile source for query n
-- swaps：显示swap的次数
show profile swaps for query n

sql优化规则：

SQL的查询一定要基于索引来进行数据扫描。
避免索引列上使用函数或者运算符。
Where字句中like%号尽量放置在右边。
使用索引扫描，联合索引中的列从左往后，命中越多越好
尽可能使用SQL语句用到的索引完成排序
查询有效的列信息即可，少用*代替列信息
永远要用小的结果集驱动大的结果集
数据库性能测试
（1）sysbench
sysbench支持以下几种测试模式：

CPU运算性能
磁盘IO性能
调度程序性能
内存分配及传输速度
POSIX线程性能
数据库性能(OLTP基准测试)
(2) mysql自带基准测试工具mysqlslap

(3) ab性能压测

说明：
性能测试中关注的指标：

吞吐率（Requests per second）
服务器并发处理能力的量化描述，单位是reqs/s，指的是在某个并发用户数下单位时间内处理的请求 数。某个并发用户数下单位时间内能处理的最大请求数，称之为最大吞吐率。吞吐率是基于并发用户数的。
计算公式：总请求数/处理完成这些请求数所花费的时间，即 Request per second=Complete requests/Time taken for tests 必须要说明的是，这个数值表示当前机器的整体性能，值越大越好。

并发连接数（The number of concurrent connections）
并发连接数指的是某个时刻服务器所接受的请求数目

并发用户数（Concurrency Level）
要注意区分这个概念和并发连接数之间的区别，一个用户可能同时会产生多个会话，也即连接数。

用户平均请求等待时间（Time per request）
计算公式：处理完成所有请求数所花费的时间/（总请求数/并发用户数），即： editor Time per request=Time taken for tests/（Complete requests/Concurrency Level）

服务器平均请求等待时间（Time per request:across all concurrent requests）
计算公式：处理完成所有请求数所花费的时间/总请求数，即： Tim taken for/testsComplete requests 可以看到，它是吞吐率的倒数。同时，它也等于用户平均请求等待时间/并发用户数，即 Time per request/Concurrency Level