如何获取有性能问题的 SQL
通过用户反馈获取存在性能问题的 SQL
通过慢查询日志获取性能问题的 SQL
实时获取存在性能问题的 SQL
使用慢查询日志获取有性能问题的 SQL
首先介绍下慢查询相关的参数
1slow_query_log 启动定制记录慢查询日志
设置的方法, 可以通过 MySQL 命令行设置 set global slow_query_log=on
或者修改 / etc/my.cnf 文件, 添加 slow_query_log=on
2slow_query_log_file 指定慢查询日志的存储路径及文件
建议日志存储和数据存储分开存储
3long_query_time 指定记录慢查询日志 SQL 执行时间的阈值
记录所有符合条件的 SQL
数据修改语句
包括查询语句
已经回滚的 SQL
注意:
时间可以精确到微秒, 存储的单位是秒, 默认值为 10 秒, 例如我们想查询 1 微秒的值, 这里就要设置成 0.001 秒
4log_queries_not_using_indexes 是否记录未使用索引的 SQL
5log_output 设置慢日志查询的保存格式 (如果需要保存为文件请修改成 FILE)
慢查询使用日志中记录的信息
第一行记录的信息为使用 sbtest 做的测试
第二行记录的信息为慢查询日志的时间
第三行记录的信息为所使用锁的时间
第四行记录的信息为返回的数据行数
第五行记录的信息为扫描数据的行数
第六行记录的信息为时间戳
第七行记录的信息为查询的 SQL 语句
使用慢查询获取有性能问题的 SQL
常使用的慢查询日志分析工具 (mysqldumpslow)
介绍: 汇总除查询条件外其他完全相同的 SQL, 并将分析结果按照参数中所指定的顺序输出
慢查询日志实例
慢查询的相关配置设置
命令行执行参数查看分析的结果
- ]# cd /var/lib/mysql/log
- ]# mysqldumpslow -s r -t 10 slow-mysql
常使用的慢查询日志分析工具 (pt-query-digest)
使用工具前, 需要先安装该工具, 如果已有, 可略过下面的安装步骤
1perl 模块
]# yum install -y perl-CPAN perl-Time-HiRes perl-IO-Socket-SSL perl-DBD-mysql perl-Digest-MD5
2 切换至 src 目录下载 rpm 包
- ]# cd /usr/local/src
- ]# wget https://www.percona.com/downloads/percona-toolkit/3.0.7/binary/redhat/7/x86_64/percona-toolkit-3.0.7-1.el7.x86_64.rpm
3 安装工具包
]# rpm -ivh percona-toolkit-3.0.7-1.el7.x86_64.rpm
执行命令分析慢查询日志
]# pt-query-digest --user=root --password=redhat --host=127.0.0.1 slow-mysql > slow.rep
分析的结果如下
MySQL 服务器处理查询请求的整个过程
客户端发送 SQL 请求给服务器
服务器检查是否存在在缓存服务器中命中该 SQL
服务器端进行 SQL 解析, 预处理, 再由优化器对应执行计划
根据执行计划, 调用存储引擎 API 来查询数据
将结果返回给客户端
查询缓存对 SQL 性能的影响
优先检查整个查询是否命中查询缓存中的数据
通过一个对大小写敏感的哈希查找实现的
查询缓存的优化参数
query_cache_type 设置查询缓存是否可用
ON,OFF,DEMAND
注意: DEMAND 表示只有在查询语句中使用 SQLCACHE 和 SQL_NO_CACHE 来控制是否需要缓存
query_cache_size 设置查询缓存的内存大小
query_cache_limit 设置查询缓存可用存储的最大值
query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据 (默认是关闭的, 建议也是关闭的此选项)
query_cache_min_res_unit 设置查询缓存分配的内存块最小的值
会造成 MySQL 生成错误的执行计划的原因
统计信息不准确
执行计划中的成本估算不等同于实际的执行计划的成本
MySQL 优化器所认为的最优可能与你所认为的最优不一样
MySQL 从不考虑其他并发的查询, 这可能会影响当前查询数据
MySQL 有时候也会基于一些固定的规则来生成执行计划
MySQL 不会考虑不受其控制的成本
MySQL 优化器可优化的 SQL 类型
1 重新定义表的关联顺序
优化器会根据统计信息来决定表的关联顺序
2 将外链接转换成内连接
where 条件和库表结构等
3 使用等价变换规则
(5=5 and a > 5) 将会被改写成 a > 5
4 优化 count(), min() 和 max()
select tables optimized away
优化器已经从执行计划中移除了该表, 并以一个常数取而代之
5 将一个表达式转换为常数表达式
6 使用等价变换规则
7 子查询优化
8 对 in() 条件进行优化
如何确定查询处理各个阶段所消耗的时间
使用 profile
set profiling = 1;
执行查询:
- show profiles;
- show profile for query N;
查询的每个阶段所消耗的时间
使用 profile 查看语句所消耗的时间
特定的 SQL 查询优化
利用主从切换的原理进行大表的表结构修改, 例如, 现在从服务器上修改, 修改完毕以后, 进行主从切换, 再在原来老的主上进行大表的修改, 存在一定的风险
在主服务器上创建于一个新的表, 表结构就是将要修改大表后表结构, 再把老表的数据重新导入到新表中, 并在老表中建立一系列的触发器, 把老表的数据同步更新到新表中, 当老表中的数据全部同步到新表以后, 再对老表加排它锁, 把新表改成老表的名称, 删除重命名的老表, 如下图所示
使用 pt-online-schema-change 命令来修改大表, 具体操作如下图所示
上图的参数解释
--alter 所使用的 sql 语句
--user 数据库的登录用户
--password 登录用户的密码
D 指定所有修改表的数据库名称
t 表的名称
--charset 指定数据库的字符串
--excute 执行
来源: https://www.cnblogs.com/demon89/p/8508433.html