mysql 多表连接查询速度超级慢

问题

我们有一个 SQL，用于找到没有主键 / 唯一键的表，但是在 MySQL 57 上运行特别慢，怎么办？

实验

我们搭建一个 MySQL 57 的环境，此处省略搭建步骤。

写个简单的脚本，制造一批带主键和不带主键的表：

执行一下脚本：

现在执行以下 SQL 看看效果：

执行了 1680s，感觉是非常慢了。

现在用一下 DBA 三板斧，看看执行计划：

感觉有点惨，由于 information_schemacolumns 是元数据表，没有必要的统计信息。

那我们来 show warnings 看看 MySQL 改写后的 SQL：

我们格式化一下 SQL：

可以看到 MySQL 将

select from A where Ax not in (select x from B) //非关联子查询

转换成了

select from A where not exists (select 1 from B where Bx = ax) //关联子查询

如果我们自己是 MySQL，在执行非关联子查询时，可以使用很简单的策略：

select from A where Ax not in (select x from B where ) //非关联子查询:1 扫描 B 表中的所有记录，找到满足条件的记录，存放在临时表 C 中，建好索引2 扫描 A 表中的记录，与临时表 C 中的记录进行比对，直接在索引里比对，

而关联子查询就需要循环迭代：

select from A where not exists (select 1 from B where Bx = ax and ) //关联子查询扫描 A 表的每一条记录 rA：     扫描 B 表，找到其中的第一条满足 rA 条件的记录。

显然，关联子查询的扫描成本会高于非关联子查询。

我们希望 MySQL 能先"缓存"子查询的结果（缓存这一步叫物化，MATERIALIZATION），但MySQL 认为不缓存更快，我们就需要给予 MySQL 一定指导。

可以看到执行时间变成了 067s。

整理

我们诊断的关键点如下：

\1 对于 information_schema 中的元数据表，执行计划不能提供有效信息。

\2 通过查看 MySQL 改写后的 SQL，我们猜测了优化器发生了误判。

\3 我们增加了 hint，指导 MySQL 正确进行优化判断。

但目前我们的实验仅限于猜测，猜中了万事大吉，猜不中就无法做出好的诊断。

对比下面两个脚本的速度：

Windows

7

用

localhost

连接本地

MySQL，速度会很慢。

Windows

7

用

127001

连接本地

MySQL，速度则是正常的。

myini里配置了

bind-address=127001

用localhost连接本地MySQL:

速度慢

<php

$start

=

microtime(true);

$mysqli

=

new

mysqli('127001',

'root',

'',

'mysql');

//连接耗时仅为00025秒

//$mysqli

=

new

mysqli('localhost',

'root',

'',

'mysql');

//连接耗时超过1秒,比正常慢了400倍

echo

microtime(true)

-

$start;

分析:

1myini里配置

bind-address=127001

时,Win7及其以上版本系统PHP用localhost连接MySQL耗时超过1秒,比用127001连接慢了400倍

2myini里配置

bind-address=::1

时,Win7及其以上版本系统PHP用localhost连接MySQL速度是正常的,但用127001连接又不正常了

3去掉bind-address配置,则用localhost或127001连接MySQL速度都是正常的

所以：在配置了

bind-address=127001

的情况下，应该使用

127001

连接本地

MySQL

数据库

。

安装

WordPress、phpMyAdmin

等

PHP

程序的时候默认使用

localhost

连接本地

MySQL

数据库，这时注意把默认的

localhost

改为

127001。

另外，Windows

2008、2012

跟

Windows

7

存在同样的问题。

1 找到你mysql的安装目录，打开myini，如果是Linux服务器,那就是myconf;

2然后在[mysqld]选项下面添加：

skip-name-resolve

这个选项的意思是：禁用DNS解析，连接速度会快很多。

然后保存，重启mysql服务器，希望能帮你解决问题！

我们先来看第一个阶段，MySQL慢的诊断思路，一般我们会从三个方向来做：

第一个方向是MySQL内部的观测

第二个方向是外部资源的观测

第三个方向是外部需求的改造

11 MySQL 内部观测

我们来看MySQL内部的观测，常用的观测手段是这样的，从上往下看，第一部分是Processlist，看一下哪个SQL压力不太正常，第二步是explain，解释一下它的执行计划，第三步我们要做Profilling，如果这个SQL能再执行一次的话, 就做一个Profilling，然后高级的DBA会直接动用performance_schema ，MySQL 57 以后直接动用sys_schema，sys_schema是一个视图，里面有便捷的各类信息，帮助大家来诊断性能。再高级一点，我们会动用innodb_metrics进行一个对引擎的诊断。

除了这些手段以外，大家还提出了一些乱七八糟的手段，我就不列在这了，这些是常规的一个MySQL的内部的状态观测的思路。除了这些以外，MySQL还陆陆续续提供了一些暴露自己状态的方案，但是这些方案并没有在实践中形成套路，原因是学习成本比较高。

12 外部资源观测

外部资源观测这部分，我引用了一篇文章，这篇文章的二维码我贴在上面了。这篇文章是国外的一个神写的，标题是：60秒的快速巡检，我们来看一下它在60秒之内对服务器到底做了一个什么样的巡检。一共十条命令，这是前五条，我们一条一条来看。

1uptime，uptime告诉我们这个机器活了多久，以及它的平均的负载是多少。

2dmesg -T | tail，告诉我们系统日志里边有没有什么报错。

3vmstat 1，告诉我们虚拟内存的状态，页的换进换出有没有问题，swap有没有使用。

4 mpstat -P ALL，告诉我们CPU压力在各个核上是不是均匀的。

5pidstat 1，告诉我们各个进程的对资源的占用大概是什么样子。

我们来看一下后五条：

首先是iostat-xz 1，查看IO的问题，然后是free-m内存使用率，之后两个sar，按设备网卡设备的维度，看一下网络的消耗状态，以及总体看TCP的使用率和错误率是多少。最后一条命令top，看一下大概的进程和线程的问题。

这个就是对于外部资源的诊断，这十条命令揭示了应该去诊断哪些外部资源。

13 外部需求改造

第三个诊断思路是外部的需求改造，我在这里引用了一篇文档，这篇文档是MySQL的官方文档中的一章，这一章叫Examples of Common Queries，文档中介绍了常规的SQL怎么写, 给出了一些例子。文章的链接二维码在slide上。

我们来看一下它其中提到的一个例子。

它做的事情是从一个表里边去选取，这张表有三列，article、dealer、price，选取每个作者的最贵的商品列在结果集中，这是它的最原始的SQL，非常符合业务的写法，但是它是个关联子查询。

关联子查询成本是很贵的，所以上面的文档会教你快速地把它转成一个非关联子查询，大家可以看到中间的子查询和外边的查询之间是没有关联性的。

第三步，会教大家直接把子查询拿掉，然后转成这样一个SQL，这个就叫业务改造，前后三个SQL的成本都不一样，把关联子查询拆掉的成本，拆掉以后SQL会跑得非常好，但这个SQL已经不能良好表义了，只有在诊断到SQL成本比较高的情况下才建议大家使用这种方式。

为什么它能够把一个关联子查询拆掉呢？

这背后的原理是关系代数，所有的SQL都可以被表达成等价的关系代数式，关系代数式之间有等价关系，这个等价关系通过变换可以把关联子查询拆掉。

上面的这篇文档是一个大学的教材，它从头教了关于代数和SQL之间的关系。然后一步步推导怎么去简化这句SQL。

第一，MySQL本身提供了很多命令来观察MySQL自身的各类状态，大家从上往下检一般能检到SQL的问题或者服务器的问题。

第二，从服务器的角度，我们从巡检的脚本角度入手，服务器的资源就这几种，观测手法也就那么几种，我们把服务器的资源全部都观察一圈就可以了。

第三，如果实在搞不定，需求方一定要按照数据库容易接受的方式去写SQL，这个成本会下降的非常快，这个是常规的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不会挂掉如果业务支撑受到限制,还是想办法处理一下

以上就是关于mysql 多表连接查询速度超级慢全部的内容，包括:mysql 多表连接查询速度超级慢、Windows Server 2008 R2和2012中PHP连接MySQL过慢的解决方法、PHP访问mysql数据库巨慢，请求帮助等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！