问题
我们有一个 SQL,用于找到没有主键 / 唯一键的表,但是在 MySQL 5.7 上运行特别慢,怎么办?
实验
我们搭建一个 MySQL 5.7 的环境,此处省略搭建步骤。
写个简单的脚本,制造一批带主键和不带主键的表:
执行一下脚本:
现在执行以下 SQL 看看效果:
...
执行了 16.80s,感觉是非常慢了。
现在用一下 DBA 三板斧,看看执行计划:
感觉有点惨,由于 information_schema.columns 是元数据表,没有必要的统计信息。
那我们来 show warnings 看看 MySQL 改写后的 SQL:
我们格式化一下 SQL:
可以看到 MySQL 将
select from A where A.x not in (select x from B) //非关联子查询
转换成了
select from A where not exists (select 1 from B where B.x = a.x) //关联子查询
如果我们自己是 MySQL,在执行非关联子查询时,可以使用很简单的策略:
select from A where A.x not in (select x from B where ...) //非关联子查询:1. 扫描 B 表中的所有记录,找到满足条件的记录,存放在临时表 C 中,建好索引2. 扫描 A 表中的记录,与临时表 C 中的记录进行比对,直接在索引里比对,
而关联子查询就需要循环迭代:
select from A where not exists (select 1 from B where B.x = a.x and ...) //关联子查询扫描 A 表的每一条记录 rA: 扫描 B 表,找到其中的第一条满足 rA 条件的记录。
显然,关联子查询的扫描成本会高于非关联子查询。
我们希望 MySQL 能先"缓存"子查询的结果(缓存这一步叫物化,MATERIALIZATION),但MySQL 认为不缓存更快,我们就需要给予 MySQL 一定指导。
...
可以看到执行时间变成了 0.67s。
整理
我们诊断的关键点如下:
\1. 对于 information_schema 中的元数据表,执行计划不能提供有效信息。
\2. 通过查看 MySQL 改写后的 SQL,我们猜测了优化器发生了误判。
\3. 我们增加了 hint,指导 MySQL 正确进行优化判断。
但目前我们的实验仅限于猜测,猜中了万事大吉,猜不中就无法做出好的诊断。
您好,很高兴为您解答。第一先限制Innodb的并发处理.如果innodb_thread_concurrency = 0 可以先改成 16或是64 看机器压力,如果
非常大,先改成16让机器的压力下来,然后慢慢增达,适应自已的业务.
处理方法: set global innodb_thread_concurrency=16
第二: 对于连接数已经超过600或是更多的情况,可以考虑适当的限制一下连接数,让前端报一下错,也别让DB挂了.
DB在了,总是可以用来加载一下数据,当数据加载到了nosql里了,慢慢的DB压力也会降下来的.
限制单用户连接数在500以下. 如:
set global max_user_connections=500
(MySQL随着连接数的增加性能会是下降的,这也是thread_pool出现的原因)
另外对于有的监控程序会读取information_schema下面的表的程序可以考虑关闭下面的参数
innodb_stats_on_metadata=0
set global innodb_stats_on_metadata=0
这个参数主要防止对读取information_schema时造成大量读取磁盘进行信息统计(如果慢查询中出现关于information_schema中表时,也可以考虑禁用该参数)
处理依据:
当学校的一个食堂一分钟只能为两个打饭, 忽然来了100个时人来打饭,又没排队, 不出会现了打饭的师傅要用点时间
去选择为那个用户服务了, 人越多,场面就越乱, 难免出现用户大吼该他的场面, 最后有可能就出现不是打饭了,而时之间相互
打架了,打饭的师傅也将收到同时有90个以上的Server too busy. 如果能排一下队.最多也就50分钟能处理完了.
以前办法,应该可以让MySQLD不会挂掉.如果业务支撑受到限制,还是想办法处理一下.
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)