MySQL性能分析与调整

MySQL优化概述与优化方案

系统响应时间、并发性

性能分类：事前、事后

当出现问题的时候：

• 尽早开始
• 设立优化目标
• 编优化边调整边监控
• 人员协调

调优方向：

1. 应用程序（业务分析人员、设计人员、应用开发人员）
   • SQL语句优化
   • 架构优化
   • 应用代码优化
2. 数据库层面的（数据库管理员）
   • 内存
   • 数据存储结构
   • 参数配置
3. 操作系统层面（数据库和系统运维人员）
   • I/O
   • 内存
   • CPU
   • 参数
   • 网络

MySQL数据库优化过程中

1、如何快速发现性能瓶颈

• 查看系统状态，如：top、vmstat、sar、iostat

• 查看MySQL连接数及相应的SQL语句：

  show processlist;
  

• show engine innodb status 取出数据分析

• show global status，至少2次，5~10秒

• 查看慢日志，分析SQL，优化SQL

2、收集更多信息

• 业务情况，业务的数据
• QPS是多少
• 读写比例如何？
• 数据是否快速增长？
• 数据库的服务时间，与架构有关

3、如何解决呢？

• 升级硬件
• 修改MySQL配置参数
• 优化索引，优化查询
• MySQL版本升级
• 优化架构

导入测试数据：1000w+的数据

#创建测试数据库
mysql -uroot -prootroot -e "create database itpuxdb"
mysql -uroot -prootroot itpuxdb < /home/itpux20180222.sql

操作系统优化

操作系统优化调整之交换页面

交换页面

使用swap分区有两个重要的原因

其一，当物理内存不足以支撑系统和应用程序（进程）的运作时，这个swap空间可以用作临时存放使用率不高的内存分页，把腾出的内存交给急需的应用程序（进程）使用。系统总是在屋里内存不够时，才进行swap交换

其二，即使你的机器拥有足够多的物理内存，也有一些程序会在他们初始化时残留的极少再用到的内存分页内容转移到swap空间，以此让出物理内存空间。对于有发生内存泄漏几率的应用程序（进程），swap空间更是重要，因为谁也不想看到物理内存不足导致系统崩溃

swap配置对性能的影响

分配太多的swap空间会浪费磁盘空间，而swap空间太少，则系统会发生错误。如果系统的物理内存用光了，系统就会跑的很慢，但仍能运行，如果swap空间用光了，那么系统就会发生错误。例如，服务器能根据不同的请求数量衍生出多个服务进程（或线程），如果swap空间用完，则服务进程无法启动，通常会出现"out of memory" 的错误，严重时会造成服务进程的死锁。因此swap空间的分配是很重要的。

swap配置大小

红帽官方的建议：

物理内存大小	推荐的swap设置
<=2G	物理内存的2倍
>2-8G	物理内存的1倍
>8-64G	物理内存的0.5倍
>64G	4G

结合Oracle经验个人建议：

物理内存大小	推荐的swap设置
<=8G	物理内存的2倍
>8G，<=16G	物理内存的1倍
>16G,<=64G	物理内存的0.5倍
>64G	8G

建议在操作系统安装的时候设置好

swap分区的优化

1. 首先，做到尽量使用分区而非文件，除非万不得已

2. 当然也可以是空间太小，那么就自己添加swap分区

3. 特别注意的是使用分区号较小的分区

4. 分布到不同设备上可以实现轮询

5. 若真的有多个swap分区，也可以指定优先级，意思也就是优先使用性能较好的分区

   注意在配置文件/etc/fstab的书写：（数字越大，优先级越高，也可以使用swapon -p 来指定）
   /dev/sda1 swap swap defaults,pri=10 0 0
   /dev/sda5 swap swap defaults,pri=5 0 0
   

6. 优化虚拟内存，并保证Linux有足够的物理内存

   一般在Oracle rac的配置环境中配置这个三个参数，可以防止换页引起节点短暂无响应，导致节点重启

   • vm.swappiness

     减少这个参数使系统尽快通过swapout不使用的进程资源来释放更多的物理内存

     swappiness建议设置为10，默认是60

     具体这样做：

     1）查看你的系统里面的swappiness

     cat /proc/sys/vm/swappiness
     

     2）修改swappiness值为10

     [root@testos ~]# grep swappiness /etc/sysctl.conf   
     vm.swappiness=10
     
     #修改好后执行以下命令生效
     sysctl -p
     

   • vm.min_free_kbytes

     保证物理内存有足够空闲空间，防止突发性换页（强制Linux虚拟内存保留最小值的空闲）

     32位系统不要设置，设置了就完蛋了

     64位系统如下设置：（8G以下，别设置，没有空闲2G的内存，也不要设置）

     建议值：524288_{1048576（512mb}1G）

     [root@testos ~]# grep vm.min_free_kbytes /etc/sysctl.conf 
     vm.min_free_kbytes=51200
     #修改好后执行以下命令生效
     sysctl -p
     

   • vm.vfs_cache_pressure

     默认是100，增大这个参数设置了虚拟内存回收directory和i-node缓冲的倾向，这个值越大，越易回收

     [root@testos ~]# grep vm.vfs_cache_pressure /etc/sysctl.conf
     vm.vfs_cache_pressure=200
     #修改好后执行以下命令生效
     sysctl -p
     

swap添加案例

已有的OS，把swap设置下了，现在需要调整

1）有硬盘分区

场景1：原来是4G的swap，要设置为8G的swap，用8G换4G

#1、新建磁盘分区（8G）
#2、停止所有的swap分区
#3、fdisk分区
#4、格式化分区
#5、启动新的swap分区
#6、设置开机自动启动

free -m
fdisk /dev/sdb > n > ；p 检查; t 修改> id: 82；w 保存
swapoff -a
mkswap /dev/sdb1
swapon /dev/sdb1
free -m

vi /etc/fstab：
/dev/sdb1 swap swap defaults 0 0
or
UUID=01b0db03-f54f-43d8-a633-29de84bdb928
swap swap defaults 0 0

场景2：原来是4G的swap，要设置为8G的swap，用4G+4G

#1、新建磁盘分区（4G）
#2、fdisk分区
#3、格式化分区
#4、启动新的swap分区
#5、设置开机自动启动


fdisk /dev/sdb > n > ；p 检查; t 修改> id: 82；w 保存
free -m
mkswap /dev/sdb1
swapon /dev/sdb1
free -m

vi /etc/fstab：
/dev/sdf1 swap swap defaults 0 0
or
UUID=81439932-2916-411e-8f5a-08464ea0fc4f
swap swap defaults 0 0

2）没有硬盘分区

场景1：原来是4G的swap，要设置为8G的swap，用8G换4G

#1、新建文件分区（8G）
#2、停止所有的swap分区
#3、启动新的swap分区
#4、设置开机自动启动


dd if=/dev/zero of=/soft/swap8g bs=1M count=8192
swapoff -a
free -m
mkswap -f /soft/swap8g
swapon /soft/swap8g
free -m

vi /etc/fstab：
/soft/swap8g swap swap defaults 0 0

场景2：原来是4G的swap，要设置为8G的swap，用4G+4G

#1、新建文件分区（4G）
#2、fdisk分区
#3、格式化分区
#4、启动新的swap分区
#5、设置开机自动启动



dd if=/dev/zero of=/soft/swap4g bs=1M count=4096
free -m
mkswap -f /soft/swap4g
swapon /soft/swap4g
free -m

vi /etc/fstab：
/soft/swap4g swap swap defaults 0 0

操作系统优化调整之大内存页HugePages

MySQL数据库是非常耗内存的，可以使用hugepages提高内存管理效率，减少swap的使用，保障数据库性能

但是注意：在MySQL中，大页内存可以被innodb使用，来分配buffer pool和别的内存

hugepages介绍

hugepages是kernel 2.6引入以便适应越来越大的物理内存

在Linux下、page size默认是4K，如果使用hugepages，默认是2M

2个术语：

Page table 映射表：物理内存和swap的对应关系、访问内存是先读Page table，根据表里的映射关系操作

TLB：CPU cache组件、缓存部分Page table以提高转换速度

在Linux下如果没有hugepage或没有配置合适的hugepage，那么可能遇到以下的问题：

1. 影响数据库性能
2. 内存不足或者经常需要进行swap
3. 某些数据库实例不能启动
4. 严重的系统故障

配置hugepages有如下好处：

1. 不需要内存页交换
2. 减轻快表压力
3. 减轻换页表的负载
4. 提高内存的性能，降低CPU负载

hugepages配置

1.设置 memlock：

vim /etc/security/limits.conf
@mysql soft memlock unlimited
@mysql hard memlock unlimited

2.修改/etc/sysctl.conf

vi /etc/sysctl.conf
kernel.shmmax = 17179869184
kernel.shmall = 4194304
kernel.shmmax = 314572800
kernel.shmall = 76800

#使设置生效
sysctl -p

shmmax是 最 大 的 共 享 内 存 段 的 大 小 ， 单 位 是 字 节 、 本 例 12g, 这 个 应 该 比innodb_buffer_pool 要大。也就是说大于DB 内存，小于物理内存
shmall 是共享内存的总大小，单位是页、每页 4K

#物理内存：24G
innodb_buffer_pool：物理内存的 50%-80%：15G
kernel.shmmax=16G（15+1）*1024*1024*1024

3.查看 mysql 用户的组信息

id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)

4.配置使用大页内存的用户组

more /proc/sys/vm/hugetlb_shm_group
sysctl -w vm.hugetlb_shm_group=27

5.计算 vm.nr_hugepages 的建议值

cat /proc/meminfo
Hugepagesize: 2048 kB

vi /etc/sysctl.conf
vm.nr_hugepages = 24576

sysctl -p

说明：

• nr_hugepages的值2M应大于（innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + tmp_table_size）

  因为以上谈到innodb可以使用large Pages来分配buffer pool跟additional mem pool

• memlock的设置，在启动MySQL时，一定要先查看ulimit -a来查看 max locked memory设置是否合理，可以尝试用以上两种方法来设置该值。还有一点 /etc/security/limits.conf配置文件的修改

经验：不超过物理内存85%，不能大于memlock

推荐设置：比物理内存低一点，直接除2，比如物理内存24G，设置为： 16G（kernel.shmmax）/ 2M + 250

6.修改参数停止实例并重启生效

vi /mysql/data/3306/my.cnf
[mysqld]
large-pages

systemctl restart mysqld

7.验证 HugePages 配置

cat /proc/meminfo

8.使用hugepages的注意事项

下面的三种情形应当重新配置hugepages

1. 物理内存的增加或者减少
2. 在当前服务器上新增或移除instance
3. instance的buffer pool大小增加或减少

如果未能调整hugepages，可能会引发下面的问题

1. 数据库性能底下
2. 出现内存不足或者过度使用交换空间
3. 数据库实例不能被启动
4. 关键性系统服务故障

关闭 Linux 透明大页功能（OEL/RHEL6.x）

从redhat6，oel 6，sles 11 and UEK2 kernels开始（包含RHEL7，OL7）,系统默认会启动 transparent hugepages：

用来提高内存管理的性能，透明大页（transparent hugepages）和之前版本中的大页功能上类型

主要的区别是：transparent hugepages可以实时配置，不需要重启就能生效

Oracle官方不建议在使用redhat6，oel 6，sles 11 and UEK2 kernels时开启透明大页

1. transparent hugepages会导致节点异常重启和性能问题
2. transparent hugepages也会导致一些异常的性能问题

Oracle强烈建议开启hugepages需要关闭transparent hugepages

红帽：transparent hugepages在RHEL Linux 6.5+中已经去除

transparent hugepages是在运行时由khugepageed进程动态的分配，而传统的hugepages是在系统启动时预先分配的

transparent hugepages是在Linux 6中被引入，目的是为了提高内存管理效率，在Linux 6中默认被启用，可以通过下面的命令来查看transparent hugepages是否启用

more /sys/kernel/mm/transparent_hugepage/enabled
or
cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never

#always 表示启用了 Transparent HugePages

可以通过下面的方法关闭 Transparent HugePages：

方法1：在/etc/grub.conf 文件中添加如下内容并重启操作系统（官方）-- 建议设置：

title Oracle Linux Server Red Hat Compatible Kernel (2.6.32-696.el6.x86_64)
        root (hd0,0)
        kernel /boot/vmlinuz-2.6.32-696.el6.x86_64 ro root=UUID=0eecb335-0cfb-4ad9-9136-b6d58797f570 rd_NO_LUKS  transparent_hugepage=never KEYBOARDTYPE=pc KEYTABLE=us rd_NO_MD crashkernel=auto LANG=zh_CN.UTF-8 rd_NO_LVM rd_NO_DM rhgb quiet numa=off
        initrd /boot/initramfs-2.6.32-696.el6.x86_64.img

#RHEL6.x 重启检查 是 0 就 OK：
[root@testos ~]# grep AnonHugePages /proc/meminfo
AnonHugePages: 0 kB

方法2：在/etc/rc.local 文件中加入如下内容并重启操作系统（官方）

if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi

if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi

禁止操作系统更新文件的 atime 属性

atime是Linux/UNIX系统下的一个文件属性，每当读取文件时，操作系统都会将读操作时间写回到磁盘上。对于读写频繁的数据库文件来说，记录文件的访问时间一般没有任何用处，却会增加磁盘系统的负担，影响I/O性能，因此，可以通过设置文件系统的mount属性，阻止操作系统写atime信息，减轻磁盘I/O负担

#（1）修改文件系统配置文件/etc/fstab，指定 noatime 选项：
/dev/sdb1 /data ext4 noatime 0 1
#（2）重新 mount 文件系统使其修改生效：
# mount -o remount /data

操作系统优化调整之I/O算法

I/O调度算法：

I/O调度程序负责维护（当向设备写入数据块或是从设备读出数据块时，请求都被安置在一个队列中等待完成）的顺序，以更有效地利用介质，I/O调度程序将无序的I/O操作变为有序的I/O操作

一共有3种算法：

1、CFQ（completely Fair Queuing，完全公平排队）

工作原理：

CFQ为每个进程/线程单独创建一个队列来管理该进程所产生的请求，也就是说每个进程一个队列，各队列之间的调度使用时间片来调度，以此来保证每个进程都能被很好的分配到I/O带宽。

I/O调度器每次执行一个进程的4次请求

2、NOOP（电梯式调度程序）

NOOP它像电梯的工作一样对I/O请求进行组织，当有一个新的请求的到来时，它将请求合并到最近的请求之后，以此来保证请求同一介质。

NOOP倾向饿死读而利于写

NOOP对于闪存设备，SSD，RAM，嵌入式系统是最好的选择

3、DeadLine（截止时间调度程序）

特点：

通过时间以及硬盘区域进行分类，这个分类和合并要求类似于NOOP的调度程序。

DeadLine确保了在一个截止时间内服务请求，这个截止时间是可调整的，而默认读期限短于写期限

这样就防止了写操作因为不能读取而饿死的现象

DeadLine对数据库环境（Oracle、RAC、MySQL）是最好的选择

#（1）查看当前系统支持的 I/O 调度算法
[root@itpuxdb ~]# dmesg | grep -i scheduler
[    0.825671] io scheduler noop registered
[    0.825674] io scheduler deadline registered (default)
[    0.825715] io scheduler cfq registered
[root@itpuxdb ~]# 

#（2）查看当前设备（/dev/sda）使用的 I/O 调度算法：
[root@itpuxdb ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq 
[root@itpuxdb ~]# 

#（3）修改当前设备使用的 I/O 调度算法，普通磁盘可以选择 Deadline，SSD 我们可以选择使用 NOOP 或者 Deadline
[root@itpuxdb ~]# echo "noop" >> /sys/block/sda/queue/scheduler
[root@itpuxdb ~]# cat /sys/block/sda/queue/scheduler
[noop] deadline cfq 
[root@itpuxdb ~]# 

#（4）永久修改 I/O 调度算法，可以通过修改内核引导参数，增加 elevator=调度算法名
RHEL 7.x
vi /etc/default/grub
GRUB_CMDLINE_LINUX="crashkernel=auto rhgb quiet numa=off elevator=noop"
grub2-mkconfig -o /etc/grub2.cfg
reboot
cat /sys/block/sda/queue/scheduler

NUMA架构优化

NUMA把一台计算机分成多个节点（node），每个节点内部拥有多个CPU，节点内部使用共有的内存控制器，节点之间是通过互联模块进行连接和信息交互，因此节点的所有内存对于本节点所有的CPU是等同的，而对于其他节点中的所有CPU都是不同的。因此每个CPU可以访问整个系统内存，但是访问本地节点的内存速度最快（不需要经过互联模块），访问非本地节点的内存速度较慢（需要经过互联模块），即CPU访问内存的速度与节点的距离有关，距离成为node distance

NUMA默认的内存分配策略是优先在进程所在CPU的本地内存中分配，会导致CPU节点之间内存分配不均衡，当某个CPU节点内存不足时，会导致Swap发生，而不是从远程节点分配内存，这就是Swap Insanity现象

MySQL是单进程多线程架构的数据库，当NUMA采用默认的内存分配策略时，MySQL进程会被并且仅仅会被分配到NUMA的一个节点上去。假设MySQL进程被分配到node 1运行，这个节点的本地内存是4GB，而MySQL配置了6GB内存，MySQL分配的6GB内存中，超过本地节点内存部分（6GB-4GB=2GB）Linux系统宁愿使用Swap也不会使用其他节点的物理内存。在这种情况下，能观察到系统虽然总共可用的物理内存还很多，但是MySQL进程已经使用swap了。

MySQL对NUMA的特性支持不好，可以选择关闭NUMA，关闭的方式有两种

（1）硬件层，在 BIOS 中设置关闭
（2）OS 内核层，启动时设置 numa=off

#RHEL 6.X:
vim /etc/grub.conf
numa=off

#RHEL 7.x:
vi /etc/default/grub
GRUB_CMDLINE_LINUX="crashkernel=auto rhgb quiet numa=off"

grub2-mkconfig -o /etc/grub2.cfg

CPU优化

检查CPU是否开启了节能选项

[root@itpuxdb ~]# grep -E '^model name|^cpu MHz' /proc/cpuinfo
model name      : Intel(R) Xeon(R) CPU E5-2678 v3 @ 2.50GHz
cpu MHz         : 2494.224

#如上如果  2.50GHz 与 2494.224 相差太大就是开启了节能模式

如果发现CPU的频率跟它标称的频率不一样，那么就是开启了节能模式

节能模式：

操作系统和CPU硬件配合，系统不繁忙的时候，为了节约电能和降低温度，它会将CPU降频

对MySQL来说，可能是一个灾难。

为了保证MySQL能够充分利用CPU的资源，建议设置CPU为最大性能模式

这个设置可以在BIOS和操作系统中设置，当然，在BIOS中设置该选项更好

MySQL存储系统层优化调整与建议

操作系统层面

硬盘使用：

服务器本地硬盘，不建议使用stat，用sas/ssd，要做raid1/raid5，不建议做raid0，

4块盘*300G
raid0 1.2t
raid1 4/2=600G
raid5 (n-1)/n  75%的使用率=900G

文件系统使用：

要用LVM，可以在线扩展文件系统

ext4/xfs

空间分配：

#示例
8块sas 600G的盘 raid1或者raid5
/ :至少50G，空间大，可以100G，不建议用LVM做根
swap： 大于16G，设置为16G
其他先不分，留到后面规划数据库使用

数据库层面

用存储设备

不要用垃圾存储跑DB，比如，不要用几万块的存储，至少要用知名厂家。IBM/HP/EMC

不要用IP存储（ISCSI）跑DB，跑网络，千兆网络，IO慢，就算万兆网也没用

建议用光纤存储跑DB，目前16GB/S，并且还是多路径，安全+速度

有钱的选择infiniband，互联传送数据光纤至少快10倍，至少是40GB/S

硬盘选择

跑业务的数据库，用15K/S转，最差也要用10K，不要用STAT盘，有钱吗SSD

跑备份，用STAT盘，7200就行了

RAID配置

8个硬盘做一个RAID比较常用，一个RAID建议用一个热备盘

如果是存储设备一个柜子用一个热备盘，至少是RAID5/6，最好建议采用RAID10，性能好，空间换性能

数据库配置

分过来的存储LUN，最好不超过2T

如果是TB级数据，最好是100G、200G一个的LUN，分过来过LVM

不要什么数据都存放到一个文件系统

/mysql/app: 50G
/mysql/data: 500G
/mysql/log: 300G
/mysql/backup: 1T

数据库方面的参数

详见第四阶段

redo、undo、binlog、datadir

MySQL数据库层面常用优化指标分析

问题：

对于MySQL千万级/亿级的大表如何优化？

优化思路：

1. 看执行计划优化SQL和索引
2. 前端加缓存memcached，Redis
3. 主从复制-读写分离
4. MySQL表分区
5. MySQL分表分库-垂直拆分
6. MySQL分表分库-水平拆分

慢查询

#my.cnf 的[mysqld]下面加上如下代码：
slow_query_log = ON
slow_query_log_file=/mysql/log/3306/itpuxdb-query.err
long_query_time=10
log_queries_not_using_indexes = 1   -- 记录所有没有使用到索引的查询语句
min_examined_row_limit=1000         -- 记录那些由于查找了多余 1000 次而引发的慢查询
log_slow_admin_statements = 1       -- 记录那些慢的 optimize table，analyze table 和 alter table 语句
log_slow_slave_statements = 1       -- 记录由 Slave 所产生的慢查询


OR：
set global slow_query_log = on;
mysql> show variables like '%slow%';
mysql> show global status like '%slow%';


#关闭慢查询日志
set global log_slow_queries = OFF;
set global slow_query_log = OFF;

打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主从结构，可以考虑打开其中一台服务器的慢查询日志，这样既可以监控慢查询，对性能影响又小

超过 10 秒的查询将会被记录，测试如下:
#找个客户端执行
select sleep(11);
#再看 show status 及日志
show global status like '%slow%';
show processlist;

连接数

经常会遇见”MySQL: ERROR 1040: Too many connections”的情况，一种是访问量确实很高，MySQL 服务器抗不住，这 个 时 候 就 要 考 虑 增 加 从 服 务 器 分 散 读 压 力 ， 另 外 一 种 情 况 是 MySQL 配 置 文 件 中
max_connections 值过小：

show variables like 'max_connections';
show global status like 'Max_used_connections';

Max_used_connections / max_connections * 100% ≈ 85%
最大连接数占上限连接数的 85%左右，如果发现比例在 10%以下，MySQL 服务器连接数上限设置的过高了。

关于内存缓存设置

myisam:

key_buffer_size 对 MyISAM 表来说非常重要。

key_buffer_size = 2000M
或者
key_buffer_size = 1G

索引缓冲，可用内存的 30-40%,MyISAM 表会使用操作系统的缓存来缓存数据，因此需要留出部分内存给它们，很多情况下数据比索引大多了。

innodb:

innodb_buffer_pool_size = 8000M
或者
innodb_buffer_pool_size = 15G

用于缓存数据、索引、锁、插入缓冲、数据字典等,不超过物理内存的 80%

innodb_buffer_pool_size 对 Innodb 表来说非常重要。

临时表

show global status like 'created_tmp%';

每 次 创 建 临 时 表 ， Created_tmp_tables 增 加 ， 如 果 是 在 磁 盘 上 创 建 临 时 表 ，Created_tmp_disk_tables 也增加,Created_tmp_files 表示 MySQL 服务创建的临时文件文件数，比较理想的配置是：

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%

MySQL 服务器对临时表的配置：

只有 256MB 以下的临时表才能全部放内存，超过的就会用到硬盘临时表。

show variables where Variable_name
in
('tmp_table_size','max_heap_table_size');

Open Table 情况

Open_tables 表示当前打开表的数量，
Opened_tables 表示打开过的表总数量

如果 Opened_tables 数量过大，
说明配置中 table_open_cache 值可能太小，我们查询一下服务器 table_cache 值：

比较合适的值为：

Open_tables / Opened_tables * 100% >= 85%
Open_tables / table_cache * 100% <= 95%

进程使用情况

show global status like 'Thread%';

如果我们在 MySQL 服务器配置文件中设置了 thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)。

Threads_created 表示创建过的线程数，如果发现 Threads_created 值过大的话，表明MySQL 服务器一直在创建线程，这 也 是 比 较 耗 资 源 ， 可 以 适 当 增 加 配 置 文 件 中 thread_cache_size 值 ， 查 询 服 务 器thread_cache_size 配置：

show variables like 'thread_cache_size';

查询缓存(query cache)

MySQL 8.0 已经废掉

show global status like 'qcache%';
show variables like 'query_cache%';


mysql> show global status like 'qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 13      |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+
8 rows in set (0.00 sec)

mysql> show variables like 'query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
5 rows in set (0.00 sec)

MySQL 查询缓存变量解释：

• Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY
• CACHE 会对缓存中的碎片进行整理，从而得到一个空闲块。
• Qcache_free_memory：缓存中的空闲内存。
• Qcache_hits：每次查询在缓存中命中时就增大
• Qcache_inserts：每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。
• Qcache_lowmem_prunes：缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看;如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。(上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况)
• Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT语句或者用了 now()之类的函数。
• Qcache_queries_in_cache：当前缓存的查询(和响应)的数量。
• Qcache_total_blocks：缓存中块的数量。

各字段的解释：

• query_cache_limit：超过此大小的查询将不缓存
• query_cache_min_res_unit：缓存块的最小大小
• query_cache_size：查询缓存大小
• query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache 查询
• query_cache_wlock_invalidate：当有其他客户端正在对 MyISAM 表进行写操作时，如果查询在 query cache 中，是否返回 cache 结果还是等写操作完成再读表获取结果。
• query_cache_min_res_unit 的配置是一柄”双刃剑”，默认是 4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
• 查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
• 如果查询缓存碎片率超过 20%，可以用 FLUSH QUERY CACHE 整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。
• 查 询 缓 存 利 用 率=(query_cache_size-Qcache_free_memory)/query_cache_size * 100%
• 查询缓存利用率在 25%以下的话说明 query_cache_size 设置的过大，可适当减小;查 询 缓 存 利 用 率 在 80% 以 上 而 且 Qcache_lowmem_prunes>50 的 话 说 明query_cache_size 可能有点小，要不就是碎片太多。
• 查询缓存命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%
• 示例服务器 查询缓存碎片率 = 20.46%，查询缓存利用率 = 62.26%，查询缓存命中率 =1.94%，命中率很差，可能写操作比较频繁吧，而且可能有些碎片。

排序使用情况

mysql> show global status like 'sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 0     |
| Sort_scan         | 0     |
+-------------------+-------+
4 rows in set (0.00 sec)

mysql> show variables like 'Sort%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| sort_buffer_size | 33554432 |
+------------------+----------+
1 row in set (0.00 sec)

Sort_merge_passes 包括两步。
MySQL 首先会尝试在内存中做排序，使用的内存大小由系统变量 Sort_buffer_size 决定，
如果它的大小不够把所有的记录都读到内存中，MySQL 就会把每次在内存中排序的结果存到临时文件中，
等 MySQL 找到所有记录之后，再把临时文件中的记录做一次排序。
但盲目的增加 Sort_buffer_size 并不一定能提高速度

文件打开数(open_files)

mysql> show global status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 20    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 65536 |
+------------------+-------+
1 row in set (0.00 sec)

比较合适的设置：Open_files / open_files_limit * 100% <= 75%

表锁情况

mysql> show global status like 'table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 104   |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

mysql> 

Table_locks_immediate 表示立即释放表锁数，
Table_locks_waited 表 示 需 要 等 待 的 表 锁 数 ， 如 果 Table_locks_immediate/Table_locks_waited > 5000，最好采用 InnoDB 引擎。

表扫描情况

mysql> show global status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 13    |
| Handler_read_key      | 15    |
| Handler_read_last     | 0     |
| Handler_read_next     | 2     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 1332  |
+-----------------------+-------+
7 rows in set (0.00 sec)

mysql> show global status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 14    |
+---------------+-------+
1 row in set (0.00 sec)

计算表扫描率：

表扫描率 = Handler_read_rnd_next / Com_select
如 果 表 扫 描 率 超 过 4000 ， 说 明 进 行 了 太 多 表 扫 描 ， 很 有 可 能 索 引 没 有 建 好 ， 增 加read_buffer_size 值会有一些好处，但最好不要超过 8MB。

mysql> show variables like 'read_buffer_size';
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| read_buffer_size | 8388608 |
+------------------+---------+
1 row in set (0.00 sec)

这个时候就要分析相应的 SQL 语句了，比如：从慢查询日志中找到 SQL，然后进行优化与分析

查询吞吐量 QPS 与 TPS

如果计算 mysql 数据库中的 QPS（看 MYSQL 运行以来）:

mysql> show global status like 'Question%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Questions     | 21170805 |
+---------------+----------+
1 row in set (0.00 sec)

mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Uptime        | 196786 |
+---------------+--------+
1 row in set (0.00 sec)

Question/Uptime=QPS

SQL 分析与优化

SQL调优的本质就是调整执行计划

一般在应用中，糟糕的SQL语句是造成系统性能低下的最主要的原因，例如大小写的不统一、同样的SQL语句不同的写法等。而且，随着数据量的增加，情况会变得越来越严重。

SQL优化又称为SQL调整，其步骤一般包括：

1. 捕获SQL语句
2. 产生SQL语句的执行计划
3. 验证统计信息（SQL语句涉及到的表是否做过分析），表信息（结果集的记录数，索引），字段上面数据分析特点
4. 如果统计信息有问题，需要对表进行分析，从而获得合适的执行计划
5. 再检查SQL语法，业务逻辑，进行相应的优化与调整

分析SQL涉及的表及索引的相关信息

1. 这条SQL有哪些表？
2. 每个表有多少数据量，就是多少行？
3. 每个表和索引的统计信息是否准确？
4. 要了解表和索引类型，了解表中的各个索引字段，分析SQL语句中索引是否使用合理
5. 如果有分区表，了解分区类型及每个分区的数据量
6. 了解SQL语句中的表关联方式
7. SQL语句执行后的结果集据量
8. 分析SQL语句是否编写合理

MySQL 性能优化之执行计划 Explain

MySQL 的 EXPLAIN 语法可以运行在 SELECT 语句或者特定表上，如果作用在表上，那么此命令等同于 DESC 表命令,请看下面的示例：

create database itpuxdb;
mysql -uroot -proot itpuxdb < itpux20180222.sql
user itpuxdb;

select id,name from ITPUX_MEMBER where name='itpux12345';

explain select id,name from ITPUX_MEMBER where name='itpux12345';


mysql> explain select id,name from ITPUX_MEMBER where name='itpux12345';
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | ITPUX_MEMBER | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9623499 |    10.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)



explain select a.region
from ITPUX_M10 a
where exists (select *
from ITPUX_SALES b
where a.cardid = b.cardid
and a.recommend = '风哥');

+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | PRIMARY            | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  98590 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 199262 |    10.00 | Using where |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
2 rows in set, 4 warnings (0.00 sec)

id 列

use itpuxdb;
create table itpux1( id int primary key, name varchar(20), age int);
create table itpux2( id int primary key, name varchar(20), age int);
create table itpux3( id int primary key, name varchar(20), age int);
insert into itpux1 values (1,"itpux01",22);
insert into itpux2 values (1,"itpux01",22);
insert into itpux3 values (1,"itpux01",22);
insert into itpux1 values (2,"itpux02",22);
insert into itpux2 values (2,"itpux02",22);
insert into itpux3 values (2,"itpux02",22);
commit;

mysql> explain select * from itpux1 join itpux2 on itpux1.id=itpux2.id where itpux2.id in (select itpux3.id from itpux3);
+----+-------------+--------+------------+--------+---------------+---------+---------+-------------------+------+----------+----------------------------------------------------+
| id | select_type | table  | partitions | type   | possible_keys | key     | key_len | ref               | rows | filtered | Extra                                              |
+----+-------------+--------+------------+--------+---------------+---------+---------+-------------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | itpux1 | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL              |    2 |   100.00 | NULL                                               |
|  1 | SIMPLE      | itpux2 | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL              |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | itpux3 | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | itpuxdb.itpux1.id |    1 |   100.00 | Using index                                        |
+----+-------------+--------+------------+--------+---------------+---------+---------+-------------------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)



mysql> explain select itpux2.* from itpux2 where id = (select id from itpux1 where
    -> id = (select itpux3.id from itpux3 where itpux3.name=''));
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | PRIMARY     | NULL   | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
|  2 | SUBQUERY    | NULL   | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
|  3 | SUBQUERY    | itpux3 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where                    |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
3 rows in set, 1 warning (0.00 sec)



mysql> explain select a.region
    -> from ITPUX_MEMBER a
    -> where exists (select *
    -> from ITPUX_SALES b
    -> where a.cardid = b.cardid
    -> and a.recommend = '');
+----+--------------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | PRIMARY            | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9623499 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  199262 |    10.00 | Using where |
+----+--------------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
2 rows in set, 4 warnings (0.00 sec)

select_type

mysql> explain select d1.name, (select id from itpux3) d2 from (select id,name
    -> from itpux1 where name='itpux01')d1 union (select name,id from itpux2);
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | itpux1     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    2 |    50.00 | Using where     |
|  2 | SUBQUERY     | itpux3     | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | Using index     |
|  4 | UNION        | itpux2     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    2 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,4> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

type

ALL：Full Table Scan

mysql> explain select id,name from ITPUX_MEMBER where name='itpux12345';
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | ITPUX_MEMBER | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9623499 |    10.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

index：Full Index Scan

mysql> explain select id from ITPUX_MEMBER;
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | ITPUX_MEMBER | NULL       | index | NULL          | PRIMARY | 9       | NULL | 9623499 |   100.00 | Using index |
+----+-------------+--------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

range

mysql> explain select * from itpux1 where id in (1,2);
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | itpux1 | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from itpux1 where id between 1 and 2;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | itpux1 | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from itpux1 where id=1 or id=2;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | itpux1 | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from itpux1 where id > 1;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | itpux1 | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

ref

mysql> explain select * from itpux1 where name='itpux01';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | itpux1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

eq_ref

explain select itpux1.name from itpux1, itpux2 where itpux1.id=itpux2.id;

const、system

mysql> explain select * from ( select * from itpux1 where id=1)itpux2;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | itpux1 | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Key

explain select id,age from itpux1;
explain select id,age from itpux1 force index(key_list 索引名);

rows

mysql> explain select a.region
    -> from ITPUX_M10 a
    -> where exists (select *
    -> from ITPUX_SALES b
    -> where a.cardid = b.cardid
    -> and a.recommend = '');
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | PRIMARY            | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  98590 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 199262 |    10.00 | Using where |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
2 rows in set, 4 warnings (0.00 sec)

Extra

Using index

mysql> explain select id from itpux1;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | itpux1 | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Using where

mysql> explain select id,name from itpux1 where id<4;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | itpux1 | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Using temporary

mysql> explain select id from itpux1 where id in (1,2) group by age,name;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                        |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | itpux1 | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where; Using temporary; Using filesort |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Using filesort

mysql> explain select id,age from itpux1 order by name;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | itpux1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

Using join buffer

mysql> explain select itpux1.name from itpux1
    -> inner join itpux2 on itpux1.name=itpux1.name;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | itpux1 | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    2 |    50.00 | Using where                                        |
|  1 | SIMPLE      | itpux2 | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | Using index; Using join buffer (Block Nested Loop) |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

Impossible where

mysql> EXPLAIN SELECT * FROM itpux1 WHERE 1=2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)

Select tables optimized away

mysql> explain select max(id) from itpux1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.01 sec)

总结：

• EXPLAIN 不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN 不考虑各种 Cache
• EXPLAIN 不能显示 MySQL 在执行查询时所作的优化工作
• EXPALIN 部分统计信息是估算的，并非精确值。
• EXPALIN 在 5.7 之前的版本只能解释 SELECT 操作，5.7 之后可以解析 DML 操作。