概述

01.应用程序:SQL语句优化，架构优化，应用代码优化。

02.数据库层面，内存，数据存储结构，参数配置。

03.操作系统层面，I/0，内存，CPU，参数，网络。

一 MySQL优化概述与优化方案

1.1 MySQL数据库优化过程中如何快速发现性能瓶颈

1查看系统状态，如: top , vmstat,sar ,iostat. . .

2查看MYSQL连接数及相应的SQL语句，show processlist

3 show engine innodb status取出数据分析

4 show global status,至少2次+，5~10秒

5查看慢日志,分析SQL，优化SQL

1.2 收集信息

1你的业务情况，业务的数据

2QPS是多少。

3读写比例如何?

4数据是否快速增长?

5数据库的服务时间，与架构有关

1.3 如何解决

1修改MYSQL配置

2优化索引，优化查询

3升级硬件

4 MYSQL版本升级

5 优化架构

二操作系统调整优化

2.1 交换页面swap

目前内存已经很便宜了，可以买大内存，所有swap内存设置不需要很大，内存不够了，直接买就OK了。内存和硬盘的性能差距是千倍以上，尽量热数据都在内存中。

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

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

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

2.1.2 Swap配置对性能的影响

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

2.1.3交换配置大小

红帽官方的建议:

小于等于2G:2倍

2-8G:1倍

8-64G :0.5倍

64G : 4G

个人建议:

小于等于8G:2倍

8G-16G:1倍

16G-64G :0.5倍

64G以上 : 8G

2.1.4 SWAP设置- vm.swappiness

内核参数vm.swappiness控制换出运行时内存的相对权重，参数值大小对如何使用swap分区有很大联系。值越大，表示越积极使用swap分区，越小表示越积极使用物理内存。默认值swappiness=60，表示内存使用率超过100-60=40%时开始使用交换分区。swappiness=0的时候表示最大限度使用物理内存，然后才是 swap空间；swappiness＝100的时候表示积极使用swap分区，并把内存上的数据及时搬运到swap空间。（网上有的说，对于3.5以后的内核和RedHat 2.6.32之后的内核，设置为0会禁止使用swap，从而引发out of memory，这种情况可以设置为1。）

需要根据服务器运行的程序类型，来设置不同的参数值。例如，对于Oracle一般设置为10；对于MySQL一般设置为1，尽可能不用swap分区。

vim /etc/sysctl. conf

vm.swappiness=10

vm.min_free_kbytes=51200

#vm.min_free_kbytes= 512000 -- 生产环境

01.首先，做到尽量使用分区而非文件，记住除非万不得已/dev/sda5 OK，/oracle/swap.file NO02当然也可能是空间太小，那么就自己添加swap分区

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

04.分布到不同设备上可以实现轮循

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

2.1.5 SWAP设置- vm.min_free_kbytes

谨慎调整内核参数，16G以上设置,16G以下不用设置。

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

对于线上128G的内存的机器，可以考虑将min设置为512M左右。因为，太大了，可能会导致内存的浪费；当然如果只有40G的物理机，更不要考虑把min设置超过1G了，这样会导致频繁的触发内存回收；具体优化也要根据业务来看。

关键是在于调整内存的内核参数的时候！ 调大的风险远大于调小的风险！ 如果有人想将vm.min_free_kbytes 调大，千万要注意当前的水位，如果一旦调大vm.min_free_kbytes 立刻触发direct reclaim，可能会导致机器hang住，ping的通，ssh不上，影响业务！hang住的原因是当vm.min_free_kbytes 是512M的时候，此时 free只有1G，此时正常运行，此时如果调大vm.min_free_kbytes 到5G，将会direct reclaim失败。

32位系统不要设置，设置了你就哭了。

64位系统按如下设置:

vim /etc/sysctl. conf

vm.swappiness=10

vm.min_free_kbytes=51200

KBytes 就是KB 的简写而已。51200/1024=50M.

16G以上设置,16G以下不用设置。

默认值是45056，。保证物理内存有足够空闲空间，防止突发性换页（强制linux虚拟内存保留最小值的空闲)

建议值:524288~1048576

vi /etc/sysctl.conf

vm.min_free_kbytes=524288

524288/1024=512M

2.2 大内存页HugePages

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

在mysql中，大页内存可以被InnoDB使用，来分配buffer pool和别的内存。

2.2.1 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负载。

2.2.2 HugePages配置:

01.设置memlock:

vim /etc/security/limits.conf

@mysql soft memlock unlimited

@mysql hard memlock unlimited

2.修改/etc/sysct1.conf vi /etc/sysctl.conf

kernel.shmmax = 12884901888

#12G*1024*1024*1024>m>km>G

kernel.shmall = 3145728

shmmax是最大的共享内存段的大小，单位是字节、本例12G，85%这个应该比innodb_buffer_pool要大。>DB内存 <物理内存

shmall是共享内存的总大小，单位是页、每页4K,shmall大于shmmax

设备内存大小除了4096，12884901888/4096=3145728

在/etclsysctl.conf中设置。

sysctl -p使其设置生效。

2.2.3配置使用大页内存的用户组

查看mysql用户的组信息id mysql

id mysql

uid=496(mysql) gid=500(mysql) 组=500(mysql)

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

vm.hugetlb_shm_group =500

在/etclsysctl.conf中设置。

2.2.4 vm.nr_hugepages的建议值.

cat /proc/meminfo

Hugepagesize:

2048 kB

vi /etc/sysctl.conf

vm.nr_hugepages = 24576

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 memory pool.

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

经验不计算:不超过物理85%，不能大于femlock.

我的设置方法:比物理内存低一点，直接除2，比如48G/2M=24576页

+500MB

因为上面已经排至了无限制了，这个就不需要配置了

@mysql soft memlock unlimited

@mysql hard memlock unlimited

注意保存内存的问题。

sysctl -p

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

修改

vi /mysql/data/3306/my. cnf

[mysqld]

large-pages

2.2.6使用HugePages的注意事项

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

a、物理内存的增减或减少

b、在当前服务器上新增或移出Instancec、Instance的SGA大小增加或减少

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

a、数据库性能地下

b、出现内存不足或者过度使用交换空间

c、数据库实例不能被启动

d、关键性系统服务故障

2.2.7 严重大内存是否启用

如果改小大页，启动会报错(先停，再改，再启) :

2018-08-19T12:39:33.5434874+08:00 0[Warning] InnoDB: Failed to allocate 138412032 bytes. errno 122018-08-19T12:39:33.543492+08:00 [Warning] InnoDB: Using conventional memory pool

2018-08-19T12:39:33.555133+08:00 0[Warning] InnoDB: Failed to allocate 138412032 bytes. errno 122018-08-19T12:39:33.555158+08:00 0[Warning] InnoDB: Using conventional memory pool

cat /proc/meminfo

#### 没有启用

HugePages_Total: 800

HugePages_Free: 800

HugePages_Rsvd: 0

HugePages_Surp: 0

Hugepagesize: 2048 kB

使用

HugePages_Total: 800

HugePages_Free: 780

HugePages_Rsvd: 522

HugePages_Surp: 0

Hugepagesize: 2048 kB

2.2.8 关闭Linux透明大页功能

从RedHat 6，OEL 6，SLES 11 and UEK2 kernels开始(包括RHEL7,OL7)，系统缺省会启用Transparent HugePages :用来提高内存管理的性能透明大页(Transparent HugePages ）和之前版本中的大页功能上类似。

主要的区别是:Transparent HugePages可以实时配置，不需要重启才能生效配置;

ORACLE官方不建议我们使用RedHat 6，OEL 6，SLES 11 and UEK2 kernels时的开启透明大页（Transparent HugePages )

1.Transparent HugePages )会导致异常节点重启，和性能问题;

⒉.透明大页(Transparent HugePages ) 也会导致一些异常的性能问题;

ORACLE强烈建议开启HugePages需要关闭Transparent HugePages.

红帽:Transparent HugePages在RHEL Linux 6.5+中已经去除。

Transparent HugePages是在运行时由khugepaged进程动态的分配，而传统的HugePages是在系统启动时预先分配的，Transparent HugePages在Linux 6中被引入。

Linux引入Transparent HugePages是为了提高内存管理效率，

在Linux 6中默认被启用，可以通过下面的命令查看Transparent HugePages是否启用。我们上面讲的就是传统的HugePages.

more /sys/kernel/mm/transparent_hugepage/enabled

cat lsys/kernel/mm/transparent_hugepage/enabled

[always] madvise never

always表示启用了Transparent HugePages。

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

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

transparent_hugepage=never

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

atime是Linux/UNIX系统下的一个文件属性，每当读取文件时，操作系统都会将读操作时间回写到磁盘上。

对于读写频繁的数据库文件来说，记录文件的访问时间一般没有任何用处，却会增加磁盘系统的负担，影响I/0性能!因此，可以通过设置文件系统的mount属性，阻止操作系统写atime信息，减轻磁盘I/0负担。方法如下:

(1)修改文件系统配置文件/etc/fstab，指定noatime选项:

/ dev/sdb1 /data ext4 noatime 0 1

(2)重新mount文件系统使其修改生效:

mount -o remount /data

2.4 I0调度算法与I0优化

Linux I/O 调度器是Linux内核中的一个组成部分，用户可以通过调整这个调度器来优化系统性能。本文首先介绍Linux I/O 调度器的结构，然后介绍如何根据不同的存储器来设置Linux I/O 调度器从而达到优化系统性能。

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

一共有三种算法

2.4.1 CFQ完全公平队列，比较适合于交互式场景

工作原理:

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

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

2.4.2 Deadline (截止时间调度程序)

特点:

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

Deadline确保了在一个截止时间内服务请求,这个截止时间是可调整的,而默认读期限短于写期限.这样就防止了写操作因为不能被读取而饿死的现象．

Deadline对数据库环境(ORACLE RAC,MYSQL等)是最好的选择．

2.4.3Noop（电梯式调度程序）固态硬盘推荐使用

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

NOOP倾向饿死读而利于写.

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

2.4.4 查看当前系统支持的I/O调度器

dmesg | grep -i scheduler

2.4.5 查看当前设备(l dev/sda)使用的I/O调度算法:

cat /sys/block/sdb/queue/scheduler

noop [deadline] cfq

2.4.6 --I0调度算法的调整

echo 'noop' > /sys/block/sdb/queue/scheduler

永久修改I/O调度算法，可以通过修改内核引导参数，增加elevator=调度算法名

RHEL 7.x

vi /etc/default/grub

GRUB_CMDLINE_LINUX="crashkernel=auto rhgb quiet numa=off elevator=deadline"

RHEL 6.x

Vim /etc/grub.conf

2.5 关闭numa

非统一内存访问（NUMA），numa把一台服务器的CPU分成多个节点(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?

MySQL是单进程多线程架构数据库，当numa采用默认内存分配策略时，MySQL进程会被并且仅仅会被分配到numa的一个节点上去。假设这个节点的本地内存为10GB,而MySQL配置20GB内存，超出节点本地内存部分(20GB-10GB)Linux会使用swap而不是使用其他节点的物理内存。在这种情况下，能观察到虽然系统总的可用内存还未用完，但是MySQL进程已经开始在使用swap了。

MySQL在NUMA架构上会出现的问题:

1).CPU规模因摩尔定律指数级发展，而总线发展缓慢，导致多核CPU通过一条总线共享内存成为瓶颈

2).于是NUMA出现了，CPU平均划分为若干个Chip（不多于4个），每个Chip有自己的内存控制器及内存插槽

3).CPU访问自己Chip上所插的内存时速度快，而访问其他CPU所关联的内存（下文称Remote Access）的速度相较慢三倍左右

4).当被换出页被访问时问题就以数据库响应时间飙高甚至阻塞的形式出现了

在/etc/grub.conf的kernel一行后添加了numa=off，重启服务器后果然问题被解决。

查看系统是否参数numa，如下64个CPU，被分成了4个NODE节点。

cat /proc/cpuinfo| grep "processor"| wc -l

cat /proc/cpuinfo

processor : 63

BogoMIPS : 100.00

Features : fp asimd evtstrm aes pmull sha1 sha2 crc32 cpuid

CPU implementer : 0x41

CPU architecture: 8

CPU variant : 0x0

CPU part : 0xd08

CPU revision : 2

[root@subcenter-117-129 ~]# numactl --hardware

available: 4 nodes (0-3)

node 0 cpus: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

node 0 size: 64794 MB

node 0 free: 5493 MB

node 1 cpus: 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

node 1 size: 65404 MB

node 1 free: 5510 MB

node 2 cpus: 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

node 2 size: 65404 MB

node 2 free: 4073 MB

node 3 cpus: 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

node 3 size: 65402 MB

node 3 free: 4948 MB

node distances:

node 0 1 2 3

0: 10 15 20 20

1: 15 10 20 20

2: 20 20 10 15

3: 20 20 15 10

关闭 numa

kernel /boot/vmlinuz-2.6.18-128.1.16.0.1.el5 root=LABEL=DBSYS ro bootarea=dbsys rhgb quiet console=ttyS0,115200n8 console=tty1 crashkernel=128M@16M numa=off

确认os层关闭numa

cat /proc/cmdline

root=LABEL=DBSYS ro bootarea=dbsys rhgb quiet console=ttyS0,115200n8 console=tty1 crashkernel=128M@16M numa=off

# numactl --hardware

2.6 关闭CPU节能

在BIOS开启做大性能

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

[root@itpuxdb01 ~]# grep -E 'Amodel namel Acpu MHz’ /proc/cpuinfomodel name

: Intel(R) Xeon(R) CPU E3-1231 v3 3.40GHz

cpu MHz

: 3390.397

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

节能模式:

操作系统和CPU硬件配合，系统不繁忙的时候，为了节约电能和降低温度，它会将CPU降频。对MySQL来说,可能是一个灾难。

为了保证MySQL能够充分利用CPU的资源，建议设置CPU为最大性能模式。这个设置可以在BIOS和操作系统中设置，当然，在BIOS中设置该选项更好。

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

3.1 无外接存储

服务器本地硬盘,不建议用STAT，用SAS/SSD，要做RAID1+0/RAID5。

3.2 存储设备

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

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

有钱的选择infiniBand,互联转输数据光纤至少快10X，至少是40GB/S。

1 硬盘选择

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

2 RAID配置

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

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

3数据库配置

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

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

不要什么数据都丢一个文件系统。

/mysql/app : 200G

/mysql/data:1T

/msyql/log: 300G

/mysql/backup:1T

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

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

顺序:

1)看执行计划优化SQL和索引

2)前端加缓存memcached , redis

3)主从复制-读写分离

4)MYSQL表分区

5) MySQL分表分库-垂直拆分

6)MySQL分表分库-水平拆分

4.2 慢查询

my.cnf的[mysqld]下面加上如下代码:

slow_query_log = ON

slow_query_log_file=/mysql/log/3306/itpuxdb-query.errlong_query_time=10

log_queries_not_using_indexes = 1

-- 记录所有没有使用到索引的查询语句

min_examined_row_limit=100

--记录那些由于查找了多余100次而引发的慢查询

log_ slow_admin_statements = 1

--记录那些慢的optimize table，analyze table和alter table语句log_slow_slave_statements = 1 --记录由Slave所产生的慢查询

long_query_time 10

慢查询是大于10秒

show variables like '%slow%';

show processlist;

4.3 连接数

经常会遇见"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服务器连接数上限设置的过高了。

4.4 关于内存设置

1 myisam:

key_buffer_size 对MyISAM表来说非常重要。

key_buffer_size = 2000M

或者

key_buffer_size = 1G

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

2 innodb:

innodb_buffer_pool_size = 8000M或者

innodb_buffer_pool_size = 15G

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

4.5 临时表-没有弄明白

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');

4.6 Open Table情况

open_tables表示当前打开表的数量,

opened_tables表示打开过的表总数量如果

Opened_tables数量过大,

说明配置中table_open_cache值可能太小，我们查询一下服务器table_cache值:table_open_cache

show global status where Variable_name in ( 'Opened_tables','Open_tables');

show variables like 'table_open_cache' ;

比较合适的值为:

open_tables / 0pened_tables * 100% >= 85%

Open_tables / table_cache *100% <= 95%

4.7进程使用情况

show global status like 'Thread%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| Threads_cached | 0 |

| Threads_connected | 1 |

| Threads_created | 1 |

| Threads_running | 1 |

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

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

show variables like 'Thread%';

+-------------------+---------------------------+

| Variable_name | Value |

+-------------------+---------------------------+

| thread_cache_size | 768 |

| thread_handling | one-thread-per-connection |

| thread_stack | 262144 |

4.8.查询缓存(query cache)

8.0已经废掉

show global status like 'qcache%';

show variables like 'query_cache%' ;

1 MySQL查询缓存变量解释:

Qcache_free_blocks:缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE会对缓存中的碎片进行整

Qcache_free_memory:缓存中的空闲内存。

Qcache_hits:每次查询在缓存中命中时就增大

Qcache_inserts:每次插入一个查询时就增大。命中次数除以插入次数就是不中比率。

Qcache_lowmem_prunes:缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来

Qcache_not_cached:不适合进行缓存的查询的数量，通常是由于这些查询不是SELECT语句或者用了now()之类的函数。

Qcache_queries_in_cache:当前缓存的查询(和响应)的数量。

cache_total_blocks:缓存中块的数量。

2 各字段的解释:

query_cache_limit:超过此大小的查询将不缓存

query_cache_min_res_unit:缓存块的最小大小

query_cache_size:查询缓存大小

query_cache_type:缓存类型，决定缓存什么样的查询，示例中表示不缓存 select sql_no_cache查询

query_cache_wlock_invalidate:当有其他客户端正在对MISAM表进行写操作时，如果查询在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) l 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%,命中率很差,可能写操作比较频繁吧，而且可能有些碎片。

4.9 排序的情况

show global status like 'sort%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| Sort_merge_passes | 0 |

| Sort_range | 0 |

| Sort_rows | 0 |

| Sort_scan | 0 |

show variables like 'sort%';

+------------------+----------+

| Variable_name | Value |

+------------------+----------+

| sort_buffer_size | 33554432 |

Sort_merge_passes包括两步。

MySQL首先会尝试在内存中做排序，使用的内存大小由系统变量Sort_buffer_size决定，

如果它的大小不够把所有的记录都读到内存中，MySQL就会把每次在内存中排序的结果存到临时文件中，等MySQL找到所有记录之后，再把临时文件中的记录做一次排序。

但盲目的增加Sort_buffer_size并不一定能提高速度

4.10 文件打开数

show global status like 'open_files';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Open_files | 18 |

show variables like 'open_files%';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| open_files_limit | 65536 |

4.11表锁情况

mysql> show global status like 'table_locks%' ;

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Table_locks_immediate | 98 |

| Table_locks_waited | 0 |

Table_locks_immediate表示立即释放表锁数，

Table_locks_waited表示需要等待的表锁数，如果Table_locks_immediate / Table_locks_waited> 5000,

Table_locks_waited 如果特别大，说明锁表特别多

4.12 表扫描情况

show global status like 'handler_read%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Handler_read_first | 13 |

| Handler_read_key | 11 |

| Handler_read_last | 0 |

| Handler_read_next | 4 |

| Handler_read_prev | 0 |

| Handler_read_rnd | 0 |

| Handler_read_rnd_next | 281 |

show global status like 'com_select%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Com_select | 2 |

计算表扫描率:

表扫描率= Handler_read_rnd_next / Com_select

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

show variables like 'read_buffer_size';

+------------------+---------+

| Variable_name | Value |

+------------------+---------+

| read_buffer_size | 8388608 |

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

4.13.查询吞吐量QPS与TPS

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

show global status like 'Question%' ;

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Questions | 52 |

show global status like 'uptime%' ;

+---------------------------+-------+

| Variable_name | Value |

+---------------------------+-------+

| Uptime | 6948 |

| Uptime_since_flush_status | 6948 |

QPs Uptime=6427771/28257=227/s

4.14 SQL分析与优化

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

SQL优化又称SQL调节，其步骤一般包括:

对SQL调优基本步骤

a)捕获SQL语句

b)产生SQL语句的执行计划;

c）验证统计信息(SQL语句涉及到的表是否做过分析)，表信息(结果集的记录数，索引)，字段上面数据分布特点

d)如果统计信息有问题，需要对表进行分析，从而获得合适的执行计划。

e)再检查SQL语法，业务逻辑,进行相应的优化与调整

1 分析SQL涉及到的表及其索引的相关信息。

01.这条SQL有哪些表?

02.每个表有多少数据量,就是多少行?

03.每个表和索引的统计信息是否准确?

04.要了解表和索引类型,了解表中的各个索引字段,分析SQL语句中索引是否使用合理

05.如果有表分区,了解分区类型及每个分区的数据量

06.了解SQL语句中的表关联方式

07.SQL语句执行后的结果集数量08.分析SQL语句是否编写合理

五 MySQL性能优化之执行计划Explain

MySQL的EXPLAIN命令用于SQL语句的查询执行计划(QEP).

这条命令的输出结果能够让我们了解MySQL优化器是如何执行SQL语句的。这条命令并没有提供任何调整建议，但它能够提供重要的信息帮助你做出调优决策。

MySQL的SQL特别差，超过3个表的关联就很差劲。

explain select * from m1db where region='北京';

5.1 explain输出解释

id:选择标识符：执行顺序,id相同为一组，从上往下顺序执行﹔在所有组中，id值越大，优先级越高，越先执行。

select_type:表示查询的类型。查询中每个select子句的类型table :

table:输出结果集的表

partitions:匹配的分区, 表分区名字

type:表示表的连接类型, 表示要对表进行表扫描类型。

possible_keys:表示查询时，可能使用的索引

key:表示实际使用的索引，如果没有使用为null。

key_len:索引字段的长度，表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（不损失精确性的情况下，长度越短越好)

ref:列与索引的比较, 表示使用哪个列和key—起从表中选择行。表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows:扫描出的行数(估算的行数)，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

filtered:按表条件过滤的行百分比, 指返回结果的行占需要读到的行(rows列的值)的百分比，在使用explain extended时出现。它显示的是针对表里符合条件的记录数的百分比所做的一个悲观估算值。

Extra:执行情况的描述和说明, 表示查询的详情信息，用到where，临时表，排序。

5.2 select_type列

(1)SIMPLE:简单SELECT,不使用UNION或子查询等

(2)PRIMARY:查询中若包含任何复杂的子部分,最外层的Select

(3) UNION : UNION中的第二个或后面的SELECT语句

(4) DEPENDENT UNION : UNION中的第二个或后面的SELECT语句，取决于外面的查询

(5) UNION RESULT:从UNION表获取结果的SELECT

(6) SUBQUERY:在SELECT或WHERE列表中包含了子查询

(7)DEPENDENT SUBQUERY:子查询中的第一个SELECT，取决于外面的查询

(8) DERIVED:派生表的SELECT, FROM子句的子查询，用来表示包含在from子句中的子查询的select , mysql会递归执行并将结果放到一个临时表中。

(9) UNCACHEABLE SUBQUERY∶子查询结果不能被缓存，必须重新评估外链接的第一行

1 simple：简单查询。查询不包含子查询和union

2 primary- subquery- derived

2）primary：复杂查询中最外层的 select

3）subquery：包含在 select 中的子查询（不在 from 子句中）

4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

用这个例子来了解 primary、subquery 和 derived 类型

3 union- union result

5）union：在 union 中的第二个和随后的 select

6）union result：从 union 临时表检索结果的 select

用这个例子来了解 union 和 union result 类型：

5.3 type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行。

依次从最优到最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行

index :Full Index Scan，索引全扫描，MYSQL遍历整个索引来查找匹配的行

range:索引范围扫描，常见于<、<=、>、>=、between等操作符

ref :使用非唯一性索引或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。

eq_ref :使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配。

const、system:单表中最多只有一条匹配行，查询起来非常迅速，所以这个匹配行中的其他列中的值可以被优化器在当前查询中当做常量来处理

NULL:MYSQL不用访问表或者索引就直接能到结果。

1 NULL：

mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

2 const, system

const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。

3 ref：相比 eq_ref

ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

4 ref_or_null

ref_or_null：类似ref，但是可以搜索值为NULL的行。

5 index_merge

index_merge：表示使用了索引合并的优化方法。例如下表：id是主键，tenant_id是普通索引。or 的时候没有用 primary key，而是使用了 primary key(id) 和 tenant_id 索引

6 range

range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

7 index：和ALL

index：和ALL一样，不同就是mysql只需扫描索引树，这通常比ALL快一些。

ALL：即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了

5.4 Extra 扩展条件

Using index:该值表示相应的select操作中使用了覆盖索引（ Covering Index)

Using where:表示mysql服务器将在存储引擎检索行后再进行过滤。许多where条件里涉及索引中的列，当它读取索引时，就能被存储引擎检验.

Using temporary:表示MySQL需要使用临时表(基于内存的)来存储结果集，

常见于排序和分组查询

Using filesort: MySQL中无法利用索引完成的排序操作称为“文件排序”

Using join buffer:该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，可能需要添加索引来改进能。

Impossible where:这个值强调了where语句会导致没有符合条件的行。

Select tables optimized away :这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。

Index merges :当MySQL决定要在一个给定的表上使用超过一个索引的时候，就会现以下格式中的一个，详细说明使用的索引以及合并的类型。

1 distinct:

一旦mysql找到了与行相联合匹配的行，就不再搜索了

2 Using index：

Using index：这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。是性能高的表现

4 Using where

Using where：mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。

不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

5 Using temporary

Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

6 Using filesort：

Using filesort mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。

六 MySQL性能优化之Profile

https:/ldev.mysql.com/doc/refman/5.7/en/show-profile.html

https://dev.mysql.com/doc/refman/5.7/en/performance-schema-query-profiling.html

https://dev.mysql.com/doc/refman/8.0/en/show-profile.html

分析SQL执行带来的开销是优化SQL的重要手段。在MySQL数据库中，可以通过配置profiling参数来启用SQL剖析。

该参数可以在全局和session级别来设置。对于全局级别则作用于整个NySQL实例，而session级别紧影响当前session.该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等.

根据这些开销进一步分析当前SQL瓶颈从而进行优化与调整

6.1 使用profile分析SQL执行带来的开销

默认是不开启的

show profiles;

Empty set, 1 warning (0.00 sec)

show variables like '%profiling%';

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| have_profiling | YES |

| profiling | OFF |

| profiling_history_size | 15 |

set profiling=1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

type:

ALL--显示所有的开销信息

BLOCK IO --显示块IO相关开销

CONTEXT SWITCHES -- 上下文切换相关开销

CPU --显示CPU相关开销信息

IPC --显示发送和接收相关开销信息

MEMORY --显示内存相关开销信息

PAGE FAULTS --显示页面错误相关开销信息

SOURCE --显示和Source_function，Source_i

SWAPS --显示交换次数相关开销的信息

停止profile,可以设置profiling参数，或者在session退出之后,profiling会被自动关闭

七 MySQLTuner-perl

MySQL性能建议者，非常非常好用。

unzip MySQLTuner-perl-master.zip

ln -s MySQLTuner-perl-master mysqltuner

perl mysqltuner.pl --user root --pass root

Variables to adjust:

*** MySQL's maximum memory usage is dangerously high ***

*** Add RAM before increasing MySQL buffer variables ***

query_cache_size (=0)

query_cache_type (=0)

query_cache_limit (> 1M, or use smaller result sets)

tmp_table_size (> 72M)

max_heap_table_size (> 16M)

innodb_log_file_size should be (=256M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

innodb_buffer_pool_instances(=2)

八 tuning-primer

show status看mysql的状态，会感觉很难读懂，事实上，你可以用tuing-primer.sh脚本输出可读性的报表，它除了提供报表以外,还进一步提供了修改建议。

unzip tuning-primer.sh-master.zip

ln -s tuning-primer.sh-master mytuning

MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS

Current sort_buffer_size = 32 M

Current read_rnd_buffer_size = 4 M

Sort buffer seems to be fine

JOINS

Current join_buffer_size = 128.00 M

You have had 0 queries where a join could not use an index properly

Your joins seem to be using indexes properly

join_buffer_size >= 4 M

This is not advised

OPEN FILES LIMIT

Current open_files_limit = 65536 files

The open_files_limit should typically be set to at least 2x-3x

that of table_cache if you have heavy MyISAM usage.

Your open_files_limit value seems to be fine

TABLE CACHE

Current table_open_cache = 2000 tables

Current table_definition_cache = 1400 tables

You have a total of 146 tables

You have 1128 open tables.

The table_cache value seems to be fine

TEMP TABLES

Current max_heap_table_size = 16 M

Current tmp_table_size = 72 M

Of 2547 temp tables, 5% were created on disk

Effective in-memory tmp_table_size is limited to max_heap_table_size.

Created disk tmp tables ratio seems fine

TABLE SCANS

Current read_buffer_size = 8 M

Current table scan ratio = 6 : 1

read_buffer_size seems to be fine

TABLE LOCKING

Current Lock Wait ratio = 0 : 2631

Your table locking seems to be fine

会用几种颜色标记:

蓝色:总指标

绿色:表示此参数还可以

红色:表示此参数有严重问题

深红色:表示有问题参数

黄色:一些信息提示

转载地址：http://mabai.baihongyu.com/

你可能感兴趣的文章

就在昨天，全球 42 亿 IPv4 地址宣告耗尽！

查看>>

Jackson Tree Model Example

Xcode 11 报错，提示libstdc++.6 缺失，解决方案

图文介绍openLDAP在windows上的安装配置

查看>>

概述

一 MySQL优化概述与优化方案

1.1 MySQL数据库优化过程中如何快速发现性能瓶颈

1.2 收集信息

1.3 如何解决

二 操作系统调整优化

2.1 交换页面swap

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

2.1.2 Swap配置对性能的影响

2.1.3交换配置大小

2.1.4 SWAP设置- vm.swappiness

2.1.5 SWAP设置- vm.min_free_kbytes

2.2 大内存页HugePages

2.2.1 HugePages介绍

2.2.2 HugePages配置:

2.2.3配置使用大页内存的用户组

2.2.4 vm.nr_hugepages的建议值.

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

2.2.6使用HugePages的注意事项

2.2.7 严重大内存是否启用

2.2.8 关闭Linux透明大页功能

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

2.4 I0调度算法与I0优化

2.4.1 CFQ完全公平队列，比较适合于交互式场景

2.4.2 Deadline (截止时间调度程序)

2.4.3Noop（电梯式调度程序） 固态硬盘 推荐使用

2.4.4 查看当前系统支持的I/O调度器

2.4.5 查看当前设备(l dev/sda)使用的I/O调度算法:

2.4.6 --I0调度算法的调整

2.5 关闭numa

2.6 关闭CPU节能

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

3.1 无外接存储

3.2 存储设备

1 硬盘选择

2 RAID配置

3数据库配置

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

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

4.2 慢查询

4.3 连接数

4.4 关于内存设置

1 myisam:

2 innodb:

4.5 临时表-没有弄明白

4.6 Open Table情况

4.7进程使用情况

4.8.查询缓存(query cache)

1 MySQL查询缓存变量解释:

2 各字段的解释:

4.9 排序的情况

4.10 文件打开数

4.11表锁情况

4.12 表扫描情况

4.13.查询吞吐量QPS与TPS

4.14 SQL分析与优化

1 分析SQL涉及到的表及其索引的相关信息。

五 MySQL性能优化之执行计划Explain

5.1 explain输出解释

5.2 select_type列

1 simple：简单查询。查询不包含子查询和union

2 primary- subquery- derived

3 union- union result

5.3 type列

1 NULL：

2 const, system

3 ref：相比 eq_ref

4 ref_or_null

5 index_merge

6 range

7 index：和ALL

5.4 Extra 扩展条件

1 distinct:

2 Using index：

4 Using where

5 Using temporary

6 Using filesort：

六 MySQL性能优化之Profile

6.1 使用profile分析SQL执行带来的开销

七 MySQLTuner-perl

二操作系统调整优化

2.4.3Noop（电梯式调度程序）固态硬盘推荐使用