问题
实验
写个简单的脚本,制造一批带主键和不带主键的表：
执行一下脚本：
现在执行以下 SQL 看看效果：
...
现在用一下 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 not exists (select 1 from B where B.x = a.x and ...) //关联子查询扫描 A 表的每一条记录 rA： ? ? 扫描 B 表,找到其中的第一条满足 rA 条件的记录.
显然,关联子查询的扫描成本会高于非关联子查询.
我们希望 MySQL 能先"缓存"子查询的结果（缓存这一步叫物化,MATERIALIZATION）,但MySQL 认为不缓存更快,我们就需要给予 MySQL 一定指导.
整理
我们诊断的关键点如下：
\1. 对于 information_schema 中的元数据表,执行计划不能提供有效信息.
但目前我们的实验仅限于猜测,猜中了万事大吉,猜不中就无法做出好的诊断.
第一段：检查系统的状态
通过操作系统的一些工具检查系统的状态,比如CPU、内存、交换、磁盘的利用率,根据经验或与系统正常时的状态相比对,有时系统表面上看起来看空闲,这也可能不是一个正常的状态,因为cpu可能正等待IO的完成.除此之外,还应观注那些占用系统资源(cpu、内存)的进程.
①使用sar来检查操作系统是否存在IO问题
结果示例：
注：在redhat下,%system就是所谓的%wio.
其中：
%usr指的是用户进程使用的cpu资源的百分比;
%sys指的是系统资源使用cpu资源的百分比;
%wio指的是等待io完成的百分比,这是值得观注的一项;
%idle即空闲的百分比.
cpu资源
~]#
vmstat
procs
———–memory———-—swap–
—–io—-–system–
—–cpu——
r
b
swpd
free
buff
cache
si
so
bi
bo
in
cs
us
sy
id
wa
st
的输出那些信息值得关注?
io
bo:
①
CPU问题
下面几列需要被察看,以确定cpu是否有问题
Processesinthe
run
queue
(procs
r)
Usertime
(cpu
us)
System
time
sy)
Idle
id)
问题情况:
如果processes
r)的数量远大于系统中cpu的数量,将会使系统便慢.
如果cpu的idle时间经常为0的话,或者系统占用时间(cpu
sy)是用户占用时间(cpu
us)两辈的话,系统面临缺少cpu资源
解决方案
:
解决这些情况,涉及到调整应用程序,使其能更有效的使用cpu,同时增加cpu的能力或数量
②内存问题
主要查看页导入的数值(swap中的si),如果该值比较大就要考虑内存,大概方法如下：
最简单的,加大RAM
减少RAM的需求
处理方式：做raid10提高性能
telnet一下MySQL对外开放的端口,如果不通的话,看看防火墙是否正确设置了.另外,看看MySQL是不是开启了skip-networking的选项,如果开启请关闭.
临时冒出另外一种解决想法,即用 GDB 调试崩溃恢复,通过临时修改 validate 变量值让 MySQL 跳过表空间验证过程,然后让 MySQL 正常关闭,重新启动就可以正常启动了.但是实际测试发现,如果以 debug 模式运行,确实可以临时修改 validate 变量,跳过表空间验证过程,但是 debug 模式下代码运行效率大打折扣,反而耗时更长.而以非 debug 模式运行,则无法修改 validate 变量,想法破灭.
①当我们请求mysql服务器的时候,MySQL前端会有一个监听,请求到了之后,服务器得到相关的SQL语句,执行之前(虚线部分为执行),还会做权限的判断
我们先来看第一个阶段,MySQL慢的诊断思路,一般我们会从三个方向来做：
第一个方向是MySQL内部的观测
第二个方向是外部资源的观测
第三个方向是外部需求的改造
①1 MySQL 内部观测
除了这些手段以外,大家还提出了一些乱七八糟的手段,我就不列在这了,这些是常规的一个MySQL的内部的状态观测的思路.除了这些以外,MySQL还陆陆续续提供了一些暴露自己状态的方案,但是这些方案并没有在实践中形成套路,原因是学习成本比较高.
①uptime,uptime告诉我们这个机器活了多久,以及它的平均的负载是多少.
我们来看一下后五条：
首先是iostat-xz 1,查看IO的问题,然后是free-m内存使用率,之后两个sar,按设备网卡设备的维度,看一下网络的消耗状态,以及总体看TCP的使用率和错误率是多少.最后一条命令top,看一下大概的进程和线程的问题.
这个就是对于外部资源的诊断,这十条命令揭示了应该去诊断哪些外部资源.
第三个诊断思路是外部的需求改造,我今天这一节引用了一篇文档,这篇文档是MySQL的官方文档中的一章,这一章叫Examples of Common Queries,文档中介绍了常规的SQL怎么写, 给出了一些例子.文章的链接二维码在slide上.
我们来看一下它其中提到的一个例子.
关联子查询成本是很贵的,所以上面的文档会教你快速地把它转成一个非关联子查询,大家可以看到中间的子查询和外边的查询之间是没有关联性的.
第三步,会教大家直接把子查询拿掉,然后转成这样一个SQL,这个就叫业务改造,前后三个SQL的成本都不一样,把关联子查询拆掉的成本,拆掉以后SQL会跑得非常好,但这个SQL已经不能良好表义了,只有在诊断到SQL成本比较高的情况下才建议大家使用这种方式.
为什么它能够把一个关联子查询拆掉呢?
这背后的原理是关系代数,所有的SQL都可以被表达成等价的关系代数式,关系代数式之间有等价关系,这个等价关系通过变换可以把关联子查询拆掉.
上面的这篇文档是一个大学的教材,它从头教了关于代数和SQL之间的关系.然后一步步推导怎么去简化这句SQL.
第一,MySQL本身提供了很多命令来观察MySQL自身的各类状态,大家从上往下检一般能检到SQL的问题或者服务器的问题.
第二,从服务器的角度,我们从巡检的脚本角度入手,服务器的资源就这几种,观测手法也就那么几种,我们把服务器的资源全部都观察一圈就可以了.
第三,如果实在搞不定,需求方一定要按照数据库容易接受的方式去写SQL,这个成本会下降的非常快,这个是常规的MySQL慢的诊断思路.