mysql 在一个sql文件中引用另外一个sql文件中自定义函数_MySQL常用的SQL调优手段或工具有哪些

最新推荐文章于 2022-07-25 22:26:30 发布

weixin_39606638

最新推荐文章于 2022-07-25 22:26:30 发布

阅读量288

点赞数

文章标签： mysql 在一个sql文件中引用另外一个sql文件中自定义函数 mysql 添加索引慢 mysqlbinlog工具 mysqlworkbench导入sql文件 mysql可视化工具有哪些 mysql调优

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

weixin_39606638

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 在一个sql文件中引用另外一个sql文件中自定义函数_MySQL常用的SQL调优手段或工具有哪些

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

扫一扫