MySQL06-性能调优

日志分析工具

mysqldumpslow

查看慢查询日志中的前 10 条慢查询：
mysqldumpslow -t 10 /path/to/slow_query.log

#按照查询时间排序显示慢查询：
mysqldumpslow -s t /path/to/slow_query.log

#按照查询次数排序显示慢查询：
mysqldumpslow -s c /path/to/slow_query.log

#查看慢查询的详细信息（查询语句、执行时间、锁等待时间等）
mysqldumpslow -v /path/to/slow_query.log

pt-query-digest：Percona Toolkit 提供的一个强大的日志分析工具，用于分析 MySQL 慢查询日志。它可以从慢查询日志中提取信息，并生成有用的报告，包括查询的摘要、性能统计、索引使用情况等。

mysqlsla：这是一个 Perl 脚本，用于分析 MySQL 的慢查询日志。它可以统计查询的执行时间、频率等信息，并生成报告和图表，帮助你了解数据库的性能瓶颈。

pt-query-advisor：Percona Toolkit 提供的另一个工具，用于分析查询日志，并提供有关查询性能的建议。它可以识别潜在的性能问题，并提供优化建议，帮助你改进查询性能。

mysqlslap：MySQL 提供的一个基准测试工具，用于模拟并评估 MySQL 数据库服务器的性能。它可以生成各种负载并测量数据库服务器的响应时间，以便进行性能优化和调整。

Neor Profile SQL：这是一个基于 Web 的 SQL 查询性能分析工具，可以帮助你轻松地分析和优化 SQL 查询。它提供了可视化的界面，包括查询的执行计划、索引使用情况等信息。

mysql_slow_log_filter

过滤出执行时间超过 0.5 秒的前 1000 条慢查询
tail -f mysql-slow.log | mysql_slow_log_filter -T 0.5 -R 1000

这个命令将从标准输入中读取慢查询日志，并将分析结果输出到指定的文件 output.txt 中
mysql_slow_log_filter < slow_query.log > output.txt

过滤指定时间范围内的慢查询：
mysql_slow_log_filter --start-time=2023-01-01T00:00:00 --end-time=2023-12-31T23:59:59 < slow_query.log

只显示执行时间超过指定阈值的慢查询：
mysql_slow_log_filter --min-query-time=5 < slow_query.log
这个命令将从慢查询日志中过滤出执行时间超过 5 秒的所有慢查询。

只显示特定用户的慢查询：
mysql_slow_log_filter --user=username < slow_query.log

mysql_slow_log_parser

查询优化

create index index_name on test_table(column_name)     # 创建索引
show index from test_table                             # 查看索引

索引的最左前缀法则

当使用复合索引进行查询时，查询条件必须包含索引的最左侧列（即索引的第一个列），否则索引将不会被充分使用。

假设有一个复合索引 (a, b, c)：
当查询条件为 WHERE a = 1 时，索引会被充分使用。
当查询条件为 WHERE a = 1 AND b = 2 时，索引也会被充分使用。
当查询条件为 WHERE b = 2 时，索引不会被使用，因为查询条件没有包含索引的最左侧列 a。
当查询条件为 WHERE a = 1 AND c = 3 时，虽然使用了索引的最左侧列 a，但跳过了中间的列 b ,直接使用 c，
这通常会导致索引使用不完全（可能只会使用到 a 的部分）

覆盖索引

sql查询的返回的所有列都包含在索引中

索引失效的情况

查询字符串类型的数据不使用引号
select * from tb_user where phone = 12312342345;

使用前部模糊查询，后部模糊查询不会使索引失效
select * from tb_user where profession = "%工程"；

or连接的查询条件没有都使用索引
select * from tb_user where id = 10 or age = 30;  # id有索引，age没有索引，会导致id的索引失效

数据分布影响，如果mysql评估使用索引比全表更慢，则不使用索引

SQL提示

使用某个索引，不使用某个索引

select * from tb_user use index(idx_name) where age = 30;
select * from tb_user ignore index(idx_name) where age = 30;
select * from tb_user force index(idx_name) where age = 30;

SQL优化

insert优化

批量插入：一次插入多条数据，而不是一条条插入

手动提交事务：在插入数据前start transaction； 插入完毕后 commit；

主键顺序插入

使用load插入大批量数据，而不是用insert
mysql --local-infile -u root -p
set global local_infile=1;
load data local infile '/root/sql.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

SQL性能分析

--当前数据库insert update delete select的次数。7个字符
show global status like "Com_______"   # 查看全局的sql执行频率
show session status like "Com_______"  # 查看当前会话的sql执行频率

--profile
select @@have_profiling;    # 查看是否支持profiling
select @@profiling;         # 查看profiling功能是否打开
set @@profiling = 1;        # 打开profiling
show profiles;              # 查看sql的执行时间（先执行sql，再执行该条）
show profiles for query 11  # 查看对应sql的具体执行过程
show profiles cpu for query 11

--explain执行计划
explain select * from test_table;

--一些输出字段的含义
id            表明执行顺序，在多表查询中会有体现。id相同，表示从上到下执行；id不同，值越大，越先执行

select_type   表明查询类型，SIMPLE(简单表,不使用表连接或子查询)、PRIMARY(主查询，最外层的查询)
              UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含的子查询）

type          表明查询类型，性能从高到低NULL、system、const（使用索引）、eq_ref、ref（使用索引）、
              range、index、all（全表查询）

MySQL性能优化

手段	具体操作
升级硬件	CPU、内存、硬盘
网络带宽，网络架构	加大带宽，调整架构
调整mysql服务运行参数	并发连接数、连接超时时间、重复使用的线程.....
调整与查询相关的参数	查询缓存、索引缓存......
启用慢日志查询	Slow-query-log

并发及连接控制

max_connections
允许的最大并发连接数

connection_timeout
等待连接超时时间，默认10秒，仅登录有效

wait_timeout
等待关闭连接的不活动超市秒数。默认28800秒（8小时）

缓存参数控制

key_buffer-size
用于MyISAM引擎的关键索引缓存大小
缓存从服务器的物理内存中划分出来,表存放在硬盘里

sort_buffer_size
为每个要排序的线程分配此大小的缓存空间

read_buffer_size
为顺序读取表记录保留的缓存大小

thread_cache_size
允许保存在缓存中被重复使用的线程数量，加快访问速度

table_open_cache
为所有线程缓存的打开表的数量，加快访问速度
打开表的步骤：打开-->内存   cpu--->硬盘--->关闭

日志优化

log-error[=name]
错误日志。记录启动/运行/停止过程中的错误消息

general-log
general-log-file=文件名
查询日志。记录客户端连接和查询的操作

slow-query-log
slow-query-log-file=文件名
long-query-time=耗时时间
慢查询日志。记录耗时时间较长或不使用索引的查询操作，默认查询时间超过10s的就是耗时较长的

记录慢查询

vim /etc/my.cnf
slow_query_log=1                      -- 启用慢查询
slow_query_log_file=mysql-slow.log    -- 指定慢查询日志文件
long_query_time=5                     -- 超时时间(默认10秒)
log_queries_not_using_indexes=1       -- 记录未使用索引的查询
systemctl restart mysqld


show variables like "slow_query_log";


--查看慢查询日志的内容
mysqldumpslow  /var/lib/mysql/mysql-slow.log


--查看缓存的大小
show variables like "query_cache%";
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |  #字节，查询缓存的空间
| query_cache_type             | OFF     |  #查询缓存默认不开，占内存和硬盘，一般有专门的缓存服务器
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

--查看当前的查询缓存统计信息，看查询缓存的配置是否合理
show global status like "qcache%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |查询缓存中查找到数据的次数
| Qcache_inserts          | 0       |查询请求总量
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 40      |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
Qcache_hits/Qcache_inserts=缓存命中率

并发优化

重置统计信息
flush status;

查看当前已使用连接数
show global status like 'Max_used_connections'

查看默认最大连接数
show global status like 'max_connections'
理想比例为两者的比例不大于85%

查看服务运行时的参数配置
show variables\G;
show variables like "%innodb%";

查看曾经有过的最大连接数，处理过最多的连接数
flush status;  # 刷新状态信息的值，重新计数
show global status like "Max_used_connections";

查看默认的最大连接数
show variables like "max_connections%";

修改最大连接数,马上生效
set global max_connections=300;

或者修改配置文件，永久生效
Vim /etc/my.cnf
max_connections=300
Max_used_connections/max_connections=0.85,预留15%的空间，防止高并发

查看连接超时时间
flush status；
show variables like "%timeout%";
+--------------------------------+----------+
| Variable_name                  | Value    |
+--------------------------------+----------+
| connect_timeout                | 10       | 客户端与服务器连接的超时时间,太小损耗cpu,太大浪费cpu
| delayed_insert_timeout         | 300      |
| have_statement_timeout         | YES      |
| innodb_flush_log_at_timeout    | 1        |
| innodb_lock_wait_timeout       | 50       | 死锁，写锁等待超时时间
| innodb_rollback_on_timeout     | OFF      |
| interactive_timeout            | 28800    |
| lock_wait_timeout              | 3153600  |
| net_read_timeout               | 30       |
| net_write_timeout              | 60       |
| rpl_stop_slave_timeout         | 3153600  |
| slave_net_timeout              | 60       |
| wait_timeout                   | 28800    |  三次握手结束，等待执行命令的超时时间：8小时
+--------------------------------+------ ---+

允许保存在缓存中被重用的线程数量
mysql> show variables like "thread_cache_size";

用于MyISAM引擎的关键索引缓存大小
mysql> show variables like "key_buffer_size";

为每个要排序的线程分配此大小的缓存空间
mysql> show variables like "sort_buffer_size";

为顺序读取表记录保留的缓存大小
mysql> show variables like "read_buffer_size";

为所有线程缓存的打开的表的数量
mysql> show variables like "table_open_cache";