MySQL常用的SQL调优手段或工具有哪些

jiezi

5 年前

MySQL binlog_format=mixed，可行吗，为什么
MySQL 误删除 frm 文件该怎么办？
你遇到过 PHP 连接 MySQL 的性能问题吗，如何解决的？
MySQL 常用的 SQL 调优手段或工具有哪些
在一个 2c4g 的服务器上如何用 python 操作 8GB 的超大文件
MySQL 反应慢的排查思路

一、MySQL binlog_format=mixed，可行吗，为什么

不可行，因为会导致主从数据不一致

Mixed 格式相当于 Row 和 Statement 模式的融合。遇到表结构变更的时候就会以 statement 模式来记录。像 update 或者 delete 等修改数据的语句，还是会记录所有行的变更。

但某些情况就会产生主从数据不一致例如：

1、当带有自增主键的更新多个列的表，并调用触发器或存储函数时

2、当 SQL 使用 LOAD_FILE()功能时。（Bug#39701）

3、当 SQL 语句引用一个或多个系统变量时。（Bug#331168）

更多请参考：https://dev.mysql.com/doc/ref…

二、MySQL 误删除 frm 文件该怎么办？

情况一：误删后还未重启 MySQL
1、从 proc 中恢复.frm 文件
cp /proc/pidof mysqld/fd/ 误删除的.frm /datadir/db/ 对应库的目录 /

情况二：误删后也重启 MySQL 了
2、从备份中获取表结构

物理备份。从物理备份中直接把.frm 文件拷贝回来。
逻辑备份。找到该表的 DDL，在备用实例创建该表，再把.frm 文件拷贝回来。

注意事项：
1、无论是情况一还是情况二，都需要重新设置属主和属组。
2、若恢复期间对该表执行了新的 DDL，则上述方法可能都无效。
3、本案例在 MySQL 5.7.18 版本（开启表独立空间模式）下亲测通过。

三、你遇到过 PHP 连接 MySQL 的性能问题吗，如何解决的？

PHP 连接 MySQL 用得比较多的有原生 mysql 扩展、mysqli 和 pdo_mysql 等，其与 MySQL 的连接可以分为三类

1、短连接，每次都需要建立新连接，数据库开销较大。尤其是高并发环境下，有可能会把数据库连接数直接打满并造成 CPU 很高。

2、长连接，但无法像连接池那样做到连接复用，控制不好的话，更容易导致数据库连接数爆表。

3、利用第三方应用提供的连接池功能，如 swoole、ProxySQL 等，基本上就可以解决连接性能瓶颈了。尤其像 ProxySQL 这样的还能顺便解决读写分离、高可用切换等问题。
另建议设置 net.ipv4.tcp_tw_reuse= 1 打开 tcp 重用，提高 tcp 连接性能。

四、MySQL 常用的 SQL 调优手段或工具有哪些

1、根据执行计划优化
通常使用 desc 或 explain，另外可以添加 format=json 来输出更详细的 json 格式的执行计划，主要注意点如下：

type：显示关联类型。重点关注 ALL（全表扫描）、index（全索引扫描）；
key_len：使用到索引的长度。通常该值大于 30 就要注意被选中的索引是否字符串类型，可否进一步优化；
rows：预估扫描的行数。通常该值大于 1 万就要注意可否选择更合适的索引减少扫描的行数；
extra：显示额外信息。重点关注 Using temporary，Using filesort，尽量通过添加或调整来消除。

2、利用 profiling 优化
通过探针的方式详细记录 sql 执行过程详细代价，可以很清楚地了解到 sql 到底慢在哪个环节。
重点关注下列几种情况是否耗时较大：

sending data
creating sort index
sorting result
query end
Waiting … lock
Creating tmp table
Copying to tmp table

3、利用 optimizer_trace 优化
可以输出优化器评估 SQL 执行计划的详细过程，尤其是每个可能的索引选择的代价。
利用它可以明白优化器为什么选中索引 A，而不选中索引 B。

4、利用 session status 优化
通过 flush status 重置 session 级别的状态值后，执行 sql 查看相应的状态变化量。
可重点关注几个信息：

    Created_tmp_tables，创建内存临时表
    Created_tmp_disk_tables，创建磁盘临时表，尤其注意
    Handler_read_rnd，随机读
    Handler_read_rnd_next，全表扫描或者排序或者读下一行
    Select_scan，全表扫描
    Select_full_join，全表 join
    Sort_merge_passes，多次归并排序

5、其他优化工具

MySQL workbench、pt-query-digest 等

五、在一个 2c4g 的服务器上如何用 python 操作 8GB 的超大文件

1、使用 with open 的方式，for line in f 文件对象 f 视为一个迭代器，会自动的采用缓冲 IO 和内存管理，并且能够自动关闭文件，推荐该方式
举例：

with open('filename') as f:
    for line in f:
        do_things(line)

2、open file 的方式，可以通过 read(size)指定每次读取的大小，将大文件切割成小文件来读取，每次处理完小块即释放内存
举例：

f = open(filePath)
while True:
 content = f.read(chunk_size)
 do_things(content)

3、linecache 模块，可以指定读取文件某一行
举例：

content = linecache.getline('filename', linenum)
do_things(content)

六、MySQL 反应慢的排查思路

（一）导致 MySQL 慢可能的因素有
1、计算资源不足
2、系统层面未进行基本的优化，或不同进程间资源抢占
3、MySQL 配置不科学(附神器：http://imysql.com/my-cnf-wiza…
4、垃圾 SQL 满天飞

（二）查看系统层面负载手段
1、top 查看整体负载情况，快速确认哪个进程系负载高
2、free 查看内存情况，是否有内存泄露和用了 swap 等风险
3、vmstat/sar 查看当前系统瓶颈到底在哪，如 CPU、IO、网络等
4、终极神器 perf top 查看 cpu 消耗在哪些系统调用函数

（三）查看 MySQL 的整体情况
1、观察 show processlist 输出中是否有临时表、排序、大量逻辑读、锁等待等状态
2、观察 show engine innodb status 输出中是否有大事务、长事务、锁等待等状态

（四）干掉垃圾 SQL，常用手段
1、用 explain、desc 观察执行计划
2、用 profiling 定位 sql 执行的瓶颈
3、用 pt-query-digest 分析慢 sql

（五）几个窍门
1、mysqld 进程消耗 CPU 长时间超过 90% 的话，99.9% 是因为没用好索引
2、cpu 的 %sys 高的话，大概率是 swap 或中断不均衡导致，也可能是有多个索引且超高并发写入(更新)，或者有很严重的锁等待事件
3、最⼤的瓶颈通常是在磁盘 I / O 上，因此尽量用高速磁盘设备
4、如果物理磁盘无法再升级，则通过增加内存提升性能容量
5、遇到无法诊断的问题时，试试⽤ perf top 来观测跟踪
6、SQL 执行慢，有时未必是效率低，也可能是因为锁等待，甚⾄是磁盘满了

详情戳：精彩回顾┃《MySQL 为什么慢》视频上线

公众号：知数堂，更多 MySQL 干货知识，关注公众号获取。

原文链接：https://zhishutang.com/p7W
推荐阅读：https://zhishutang.com/xdI