MYSQL优化
一、优化工具：
1、系统优化工具(top)
(1)简介：
实时监控当前操作系统的负载情况的，每秒刷新一次状态,通常会关注三大指标（CPU、MEM、IO）
（2）评判标准
（2.1） 整体的负载情况，判断标准，如果值非常高，只能告诉我们操作系统很繁忙
load average: 0.00, 0.00, 0.00 （表示1分钟，5分钟，15分钟）
（2.2）CPU使用情况
cpu(s): 0.2%us, 0.2%sy, 99.7%id, 0.0%wa
%id: CPU空闲的百分比
问一个问题？你觉得在一个已投产的系统中ID值是高好还是低好呢？
一般情况下我们建议，95%以下都算是正常的，但是呢，我们去准备硬件的时候，一般都会预留一部分（3年）硬件配置
%us:用户程序，占用的CPU时间片的百分比。我们认为us%高是好事，但要在cpu正常能力范围内。
%sy:系统程序（和内核工作有关），资源调配，资源管理，内核其他功能的管理（system call）
对于比较成熟的操作系统，对sy%应该是占比很少的。我们认为越少越好。
如果飙升，可能说明两件事情，1，系统bug；2，中病毒了
%wa 这个参数越少越好。如果wait高说明了，
1，IO很慢（速度慢，全表扫描）
2、内存满了OOM（内存小，全表扫描）
（2.3）
Mem: 4040596k total, 1692536k used, 2348060k free, 152348k buffers
Swap: 786428k total, 0k used, 786428k free, 620256k cached
Mem：
total：总的内存量
used：已经被使用的内存量
free：空闲的内存空间
buffer：专门负责操作系统当中，与文件修改类操作有关的内存缓冲区（专门负责写操作的），可以被重复利用的内存区域
cached：专门负责操作系统当中，与文件读取有关的缓存区域（专门负责文件读取操作的），对于操作系统可用内存量=free+buffer+cached
used：used=RSS+anon+buffer+cached
补充：
1.Linux操作系统内存划分的三大区域：
RSS：常驻内存集，主要负责程序运行需要的内存区域
Page Cache：文件系统缓存，主要负责文件有关的缓冲和缓存，buffer+cached
anon page: 匿名页，主要负责程序之间交互时使用到内存区域
2.连续的地址位，定义为了page（页），并且进行了量化。
（1）基于固定大小page分配模式，他的一些不足的地方？
在申请内存时，需要整个内存进行遍历 会有大量的内存碎片，导致程序OOM（out of memory）
（2）SLAB Allocator内存管理子系统
1、将内存逻辑化成chain+chunk模式，内存区域会有多条链。每条chain下都“挂着”多个等同大小的chunk（2的幂）
2、在每条链的头部，都会有一个专门的chunk位图，来更快速的找到需要的空闲chunk，并且记录每个chunk最后被访问的时间戳。
（3）buddy system（伙伴系统）
1、提供了多种内存实现回收和整理内存碎片算法，最经典的就是LRU算法。
2、当内存free空间紧张时，会触发进行整理或释放，不再使用buffer和cached
通过以下命令，手工释放所有buffer和cached
echo 3 > /proc/sys/vm/drop_caches
SWAP：交换分区，当内存紧张的时候，会将内存区域当中的数据临时置换到SWAP中。
默认：在内存使用量达到60%
[root@db02 ~]# cat /proc/sys/vm/swappiness
60
对于MySQL环境，要尽量避免swap使用
sysctl.conf
临时修改：
[root@db02 ~]# echo 0 >/proc/sys/vm/swappiness
2、iostat
yum install sysstate -y
iostat 1 10(1s显示一次，一共显示10次)
iostat -dk 1 10(以kb为单位显示)
测试当前环境IO水平
mount /dev/sdb /data
iostat -dm 1 /dev/sdb
dd if=/dev/zero of=/data/bigfile
在优化过程中，我们一般会结合CPU和内存的使用情况看IO状态
CPU非常繁忙（MYSQL）：
1、user 很高
再看IO水平，正常情况下IO也会很高
不正常的情况，user很高，但是IO很低？
在做大量的计算（多表连接查询、排序、分组、子查询很复杂或者很频繁）
2、wait 很高
IO很少
（1）很有可能是全表扫描
（2）IO有问题（RAID规划或者磁盘IO本身问题）
3、vmstat
vmstat 1 10
综合查看mem,cpu,io,system,swap。
4、dstat
yum install dstat -y
磁盘，网络，cpu,paging,system
二、数据库层面优化工具
基础优化命令工具
（1）mysql
用法举例：
mysql -uroot -p123 -e "show status like ‘%lock%‘"
（2）SHOW ENGINE INNODB STATUS
mysql> show engine innodb status\G
一般关注比较多的：内存、锁相关
（3）show index
（4）Information_Schema (查看元数据)
（5）mysql库下的 innodb_table_stats innodb_index_stats
(6) SHOW [SESSION | GLOBAL] STATUS
(7) SHOW [FULL] PROCESSLIST(应急调优)
(8) explain
(9) mysqldumpslow （pt-query-diagest）
深度优化命令工具（扩展）
mysqlslap
sysbench
tpcc
Performance Schema(5.7默认开启)
三、硬件优化：
主机：
根据数据库类型
（1）主机CPU选择
IO密集型：可以处理多并发的CPU类型，特点是核心数量较多，主频中等
Intel E系列
CPU密集型：可以处理高性能计算的cpu类型，主频非常高，核心数量中等Intel I系列的
MySQL的线上业务，处理高并发访问的业务。属于IO密集型的业务，所以选择志强系列的CPU更好一些。
MySQL非线上的业务，数据处理数据分析，属于CPU密集型业务，所以选择I系列的CPU。
（2）内存容量选择
一般是选择cpu核心数量的2倍
（3）IO的选择
1、磁盘选择
SATAIII SAS FC SSD pci-e SSD Flash
主机 RAID卡的BBU(Battery Backup Unit)关闭
存储（有条件的公司会选择单独存储设备）：
根据存储数据种类的不同，选择不同的存储设备
配置合理的RAID级别(raid5、raid10、热备盘)
raid0 ：性能高（条带化），安全性和单盘一样
raid1 ：安全性高（条带化功能），性能和单盘一样
raid10 ：读写性能都很高（0级别条带化功能），安全性高（1级别，镜像功能），企业如果有条件推荐的一种raid级别
raid5 ：较好的安全性（校验），较好的性能（条带化功能，读性能比较高，写性能一般），对于读多写少的业务可以使用此级别
高端存储：IBM EMC HDS，一般都是raid1（就是raid10） ，自带条带化功能，而且只支持4块盘做一个raid
使用合适raid级别，避免过度条带化
IOPS峰值：对于每一块硬件磁盘来讲，都有一个固定参数IOPS，每秒最多能够进行的IO的次数。
网络设备：
使用流量支持更高的网络设备（交换机、路由器、网线、网卡、HBA卡）
网卡绑定：
bonding 0 1
交换机：堆叠（bonding 0:负载均衡，1表示主备）
四、系统层面优化：
1、Swap调整
/proc/sys/vm/swappiness的内容改成0（临时）
echo 0>/proc/sys/vm/swappiness
/etc/sysctl.conf上添加vm.swappiness=0（永久）
vim /etc/sysctl.conf
vm.swappiness=0
sysctl -p
这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。
当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。
2、IO调度策略
临时修改：
[root@db02 ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq]
[root@db02 ~]# cat /sys/block/sdb/queue/scheduler
noop anticipatory deadline [cfq]
[root@db02 ~]# echo deadline >/sys/block/sda/queue/scheduler
[root@db02 ~]# echo deadline >/sys/block/sdb/queue/scheduler
[root@db02 ~]# cat /sys/block/sdb/queue/scheduler
noop anticipatory [deadline] cfq
[root@db02 ~]#
永久修改：
更改到如下内容:
kernel /vmlinuz-2.6.32-696.el6.x86_64 ro root=UUID=40c9133f-6007-485c-be19-4082c8361df3 rd_NO_LUKS rd_NO_LVM LANG=en_US.UTF-8
rd_NO_MD SYSFONT=latarcyrheb-sun16 crashkernel=auto KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM elevator=deadline rhgb quiet
3、FS：
NO LVM
ext4或xfs
ssd（binlog relay）
4、关闭无用服务
chkconfig --level 23456 acpid off
chkconfig --level 23456 anacron off
chkconfig --level 23456 autofs off
chkconfig --level 23456 avahi-daemon off
chkconfig --level 23456 bluetooth off
chkconfig --level 23456 cups off
chkconfig --level 23456 firstboot off
chkconfig --level 23456 haldaemon off
chkconfig --level 23456 hplip off
chkconfig --level 23456 ip6tables off
chkconfig --level 23456 iptables off
chkconfig --level 23456 isdn off
chkconfig --level 23456 pcscd off
chkconfig --level 23456 sendmail off
chkconfig --level 23456 yum-updatesd off
?
以上：硬件优化建议，操作系统优化建议，应该在业务架构搭建初始应该做好。
?
五、数据库层面优化：
4.1 参数优化（见参数优化建议.txt）
4.2 数据库索引优化(见索引管理章节)
4.3 锁优化
4.4 数据库架构优化（扩展）
4.3 锁优化
MyIASM： 表级锁
优点：申请和释放时，需要更少系统资源，减少死锁产生。
缺点：不利于并发处理，在某个事务在对表进行修改操作时，会锁定整个表，其他事务只能等待完成之后，才能操作。
有非常严重的锁等待。
InnoDB：
支持行级锁，行级锁在索引锁。如果表中没有任何索引，那么我们做表数据处理的时候，依然会表级锁。
GAP锁：主要针对范围数据操作时
死锁的处理过程：
1、 show processlist
2、show engine innodb status\G;
LOCK WAIT 2 lock struct(s), heap size 1184, 1 row lock(s)
MySQL thread id 4, OS thread handle 0x7f37d66f0700, query id 44 localhost root Sending data
select * from t1 where id=3 for update
Trx read view will not see trx with id >= 2323, sees < 2323
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 14 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`t1` trx id 2322 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
3、kill 4;
避免死锁的方法：
（1）将index lock 转换为table lock
set autocommit=0;
lock table t1;
insert to
update
delete
commit
unlock table t1;
（2）将所有事务处理表数据的顺序尽量保证一致。
4.4架构优化扩展
高可用
读写分离
分布式架构（分库分表）
六、见参数优化建议：
MySQL参数优化测试建议
一、参数优化前压力测试
0、优化测试前提
MacBook：虚拟机vm12.5，OS centos 6.9（系统已优化），cpu2（I5 4288u 2.6GHZ）,MEM4GB ,HardDisk:Apple SSD(SM-0512F)
1、模拟数据库数据
为了测试我们创建一个test1的库创建一个tb1的表，然后导入20万行数据，脚本如下：
vim slap.sh
#!/bin/bash
HOSTNAME="localhost"
PORT="3306"
USERNAME="root"
PASSWORD="123"
DBNAME="oldboy"
TABLENAME="lufei"
#create database
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}"
create_db_sql="create database if not exists ${DBNAME}"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"
#create table
create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) not null,stusex char(1)
not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100) default null)"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}"
#insert data to table
i="1"
while [ $i -le 200000 ]
do
insert_sql="insert into ${TABLENAME} values($i,‘‘guojialei_$i,‘1‘,‘110011198809163418‘,‘1988-09-16‘,‘2017-09-13‘,‘oldboyedu_$i‘)"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}"
let i++
done
#select data
select_sql="select count(*) from ${TABLENAME}"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"
执行脚本：
sh slap.sh
2、检查数据可用性
mysql -uroot -p123
select count(*) from test1.tb1;
3、在没有优化之前我们使用mysqlslap来进行压力测试
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema=‘test1‘ --query=‘select * from test1.tb1‘ engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 31.463 seconds
Minimum number of seconds to run all queries: 31.463 seconds
Maximum number of seconds to run all queries: 31.463 seconds
Number of clients running queries: 100
Average number of queries per client: 20
--------------------------------mysqlslap使用说明----------------------------
mysqlslap工具介绍
mysqlslap来自于mariadb包，测试的过程默认生成一个mysqlslap的schema,生成测试表t1，查询和插入测试数据，mysqlslap库自动生成，如果已经存在则先删除。用--only-print来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹。
常用选项：
--auto-generate-sql, -a 自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力
--auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)
--auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持
--number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认1
--number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1
--number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)
--query=name,-q 使用自定义脚本执行测试，例如可以调用自定义的存储过程或者sql语句来执行测试
--create-schema 代表自定义的测试库名称，测试的schema，MySQL中schema也就是database
--commint=N 多少条DML后提交一次
--compress, -C 如服务器和客户端都支持压缩，则压缩信息
--concurrency=N, -c N 表示并发量，即模拟多少个客户端同时执行select；可指定多个值，以逗号或者--delimiter参数指定值做为分隔符
--engine=engine_name, -e engine_name 代表要测试的引擎，可以有多个，用分隔符隔开
--iterations=N, -i N 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次
--only-print 只打印测试语句而不实际执行
--detach=N 执行N条语句后断开重连
--debug-info, -T 打印内存和CPU的相关信息
测试示例：
1）单线程测试
[root@centos7 ~]# mysqlslap -a -uroot -p
Enter password:
Benchmark
Average number of seconds to run all queries: 0.004 seconds
Minimum number of seconds to run all queries: 0.004 seconds
Maximum number of seconds to run all queries: 0.004 seconds
Number of clients running queries: 1
Average number of queries per client: 0
2）多线程测试，使用--concurrency来模拟并发连接
[root@centos7 ~]# mysqlslap -uroot -p -a -c 500
Enter password:
Benchmark
Average number of seconds to run all queries: 3.384 seconds
Minimum number of seconds to run all queries: 3.384 seconds
Maximum number of seconds to run all queries: 3.384 seconds
Number of clients running queries: 500
Average number of queries per client: 0
3）同时测试不同的存储引擎的性能进行对比
[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info
Enter password:
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.192 seconds
Minimum number of seconds to run all queries: 0.187 seconds
Maximum number of seconds to run all queries: 0.202 seconds
Number of clients running queries: 500
Average number of queries per client: 2
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.355 seconds
Minimum number of seconds to run all queries: 0.350 seconds
Maximum number of seconds to run all queries: 0.364 seconds
Number of clients running queries: 500
Average number of queries per client: 2
User time 0.33, System time 0.58
Maximum resident set size 22892, Integral resident set size 0
Non-physical pagefaults 46012, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 31896, Involuntary context switches 0
4）执行一次测试，分别500和1000个并发，执行5000次总查询
[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500,1000 --number-of-queries 5000 --debug-info
Enter password:
Benchmark
Average number of seconds to run all queries: 3.378 seconds
Minimum number of seconds to run all queries: 3.378 seconds
Maximum number of seconds to run all queries: 3.378 seconds
Number of clients running queries: 500
Average number of queries per client: 10
Benchmark
Average number of seconds to run all queries: 3.101 seconds
Minimum number of seconds to run all queries: 3.101 seconds
Maximum number of seconds to run all queries: 3.101 seconds
Number of clients running queries: 1000
Average number of queries per client: 5
User time 0.84, System time 0.64
Maximum resident set size 83068, Integral resident set size 0
Non-physical pagefaults 139977, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 31524, Involuntary context switches 3
5）迭代测试
[root@centos7 ~]# mysqlslap -uroot -p -a --concurrency=500 --number-of-queries 5000 --iterations=5 --debug-info
Enter password:
Benchmark
Average number of seconds to run all queries: 3.307 seconds
Minimum number of seconds to run all queries: 3.184 seconds
Maximum number of seconds to run all queries: 3.421 seconds
Number of clients running queries: 500
Average number of queries per client: 10
User time 2.18, System time 1.58
Maximum resident set size 74872, Integral resident set size 0
Non-physical pagefaults 327732, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 73904, Involuntary context switches 3
二、优化细节：
1、参数优化
1.1 Max_connections
（1）简介
Mysql的最大连接数，如果服务器的并发请求量比较大，可以调高这个值，当然这是要建立在机器能够支撑的情况下，因为如果连接数越来越多，mysql会为每个连接提供缓冲区，就会开销的越多的内存，所以需要适当的调整该值，不能随便去提高设值。
（2）判断依据
show variables like ‘max_connections‘;
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
show status like ‘Max_used_connections‘;
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 101 |
+----------------------+-------+
如果max_used_connections跟max_connections相同,那么就是max_connections设置过低或者超过服务器的负载上限了，低于10%则设置过大.
（3）修改方式举例
vim /etc/my.cnf
Max_connections=1024
1.2 back_log
（1）简介
mysql能暂存的连接数量，当主要mysql线程在一个很短时间内得到非常多的连接请求时候它就会起作用，如果mysql的连接数据达到max_connections时候，新来的请求将会被存在堆栈中，等待某一连接释放资源，该推栈的数量及back_log,如果等待连接的数量超过back_log，将不被授予连接资源。
back_log值指出在mysql暂时停止回答新请求之前的短时间内有多少个请求可以被存在推栈中，只有如果期望在一个短时间内有很多连接的时候需要增加它
（2）判断依据
show full processlist
发现大量的待连接进程时，就需要加大back_log或者加大max_connections的值
（3）修改方式举例
vim /etc/my.cnf
back_log=1024
1.3 wait_timeout和interactive_timeout
（1）简介
wait_timeout：指的是mysql在关闭一个非交互的连接之前所要等待的秒数
interactive_timeout：指的是mysql在关闭一个交互的连接之前所需要等待的秒数，比如我们在终端上进行mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动的断开，默认的是28800，可调优为7200。
wait_timeout:如果设置太小，那么连接关闭的就很快，从而使一些持久的连接不起作用
（2）设置建议
如果设置太大，容易造成连接打开时间过长，在show processlist时候，能看到很多的连接 ，一般希望wait_timeout尽可能低
（3）修改方式举例
wait_timeout=1200
interactive_timeout=1200
1.4 key_buffer_size
（1）简介
key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度
（2）设置依据
通过key_read_requests和key_reads可以直到key_baffer_size设置是否合理。
mysql> show variables like "key_buffer_size%";
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.00 sec)
mysql>
mysql> show status like "key_read%";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Key_read_requests | 10 |
| Key_reads | 2 |
+-------------------+-------+
2 rows in set (0.00 sec)
mysql>
一共有10个索引读取请求，有2个请求在内存中没有找到直接从硬盘中读取索引
注：key_buffer_size只对myisam表起作用，即使不使用myisam表，但是内部的临时磁盘表是myisam表，也要使用该值。
可以使用检查状态值created_tmp_disk_tables得知：
mysql> show status like "created_tmp%";
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 6 |
| Created_tmp_tables | 1 |
+-------------------------+-------+
3 rows in set (0.00 sec)
mysql>
通常地，我们习惯以 Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables) 或者已各自的一个时段内的差额计算，来判断基于内存的临时表利用率。所以，我们会比较关注 Created_tmp_disk_tables 是否过多，从而认定当前服务器运行状况的优劣。
看以下例子：
在调用mysqldump备份数据时，大概执行步骤如下：
180322 17:39:33 7 Connect root@localhost on
7 Query /*!40100 SET @@SQL_MODE=‘‘ /
7 Init DB guo
7 Query SHOW TABLES LIKE ‘guo‘
7 Query LOCK TABLES guo READ /!32311 LOCAL /
7 Query SET OPTION SQL_QUOTE_SHOW_CREATE=1
7 Query show create table guo
7 Query show fields from guo
7 Query show table status like ‘guo‘
7 Query SELECT /!40001 SQL_NO_CACHE */ * FROM guo
7 Query UNLOCK TABLES
7 Quit
其中，有一步是：show fields from guo。从slow query记录的执行计划中，可以知道它也产生了 Tmp_table_on_disk。
所以说，以上公式并不能真正反映到mysql里临时表的利用率，有些情况下产生的 Tmp_table_on_disk 我们完全不用担心，因此没必要过分关注 Created_tmp_disk_tables，但如果它的值大的离谱的话，那就好好查一下，你的服务器到底都在执行什么查询了。
（3）配置方法
key_buffer_size=64M
1.5 query_cache_size
（1）简介：
查询缓存简称QC，使用查询缓冲，mysql将查询结果存放在缓冲区中，今后对于同样的select语句（区分大小写）,将直接从缓冲区中读取结果。
一个sql查询如果以select开头，那么mysql服务器将尝试对其使用查询缓存。
注：两个sql语句，只要想差哪怕是一个字符（列如大小写不一样；多一个空格等）,那么这两个sql将使用不同的一个cache。
（2）判断依据
mysql> show status like "%Qcache%";
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031360 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2002 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+
8 rows in set (0.00 sec)
---------------------状态说明--------------------
Qcache_free_blocks：缓存中相邻内存块的个数。如果该值显示较大，则说明Query Cache 中的内存碎片较多了，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
注：当一个表被更新之后，和它相关的cache blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用FLUSH QUERY CACHE语句来清空free blocks
Qcache_free_memory：Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是过多了。
Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
Qcache_inserts：表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
Qcache_lowmem_prunes：多少条Query 因为内存不足而被清除出Query Cache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query 被换出。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的free_blocks和free_memory可以告诉您属于哪种情况）
Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
Qcache_queries_in_cache：当前Query Cache 中cache 的Query 数量；
Qcache_total_blocks：当前Query Cache 中的block 数量；。
（3）配置示例
mysql> show variables like ‘%query_cache%‘ ;
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)
mysql>
-------------------配置说明-------------------------------
以上信息可以看出query_cache_type为off表示不缓存任何查询
各字段的解释：
query_cache_limit：超过此大小的查询将不缓存
query_cache_min_res_unit：缓存块的最小大小，query_cache_min_res_unit的配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
query_cache_size：查询缓存大小 (注：QC存储的最小单位是1024byte，所以如果你设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。)
query_cache_type：缓存类型，决定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：
如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。
如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。
如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。
修改/etc/my.cnf,配置完后的部分文件如下：
query_cache_size=256M
query_cache_type=1
1.6 max_connect_errors
max_connect_errors是一个mysql中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码等情况，当超过指定次数，mysql服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息 max_connect_errors的值与性能并无太大关系。
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容
max_connect_errors=2000
1.7 sort_buffer_size
（1）简介：
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY 或GROUP BY操作，
（2）配置依据
Sort_Buffer_Size并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。
列如：500个连接将会消耗500*sort_buffer_size（2M）=1G内存
（3）配置方法
修改/etc/my.cnf文件，在[mysqld]下面添加如下：
sort_buffer_size=1M
1.8 max_allowed_packet
（1）简介：
mysql根据配置文件会限制，server接受的数据包大小。
（2）配置依据：
有时候大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败，更大值是1GB，必须设置1024的倍数
（3）配置方法：
max_allowed_packet=32M
1.9 join_buffer_size
用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
1.10 thread_cache_size
(1)简介
服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.
（2）配置依据
通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。
设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。
服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)
试图连接到MySQL(不管是否连接成功)的连接数
mysql> show status like ‘threads_%‘;
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 8 |
| Threads_connected | 2 |
| Threads_created | 4783 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)
Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created:代表从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值。
Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。
(3)配置方法：
thread_cache_size=32
1.11 innodb_buffer_pool_size
（1）简介
对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。
（2）配置依据：
InnoDB使用该参数指定大小的内存来缓冲数据和索引。
对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%,一般我们建议不要超过物理内存的70%。
mysql> SHOW GLOBAL STATUS LIKE ‘Innodb_buffer_pool%‘
+---------------------------------------+-------------+
| Variable_name | Value |
+---------------------------------------+-------------+
| Innodb_buffer_pool_dump_status | not started |
| Innodb_buffer_pool_load_status | not started |
| Innodb_buffer_pool_pages_data | 1557 |
| Innodb_buffer_pool_bytes_data | 25509888 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 2305 |
| Innodb_buffer_pool_pages_free | 63977 |
| Innodb_buffer_pool_pages_misc | 2 |
| Innodb_buffer_pool_pages_total | 65536 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 64 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 32036288 |
| Innodb_buffer_pool_reads | 600 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 280891 |
+---------------------------------------+-------------+
17 rows in set (0.00 sec)
mysql>
Innodb_buffer_pool_pages_data
The number of pages in the InnoDB buffer pool containing data. The number includes both dirty and
clean pages.
Innodb_buffer_pool_pages_total
The total size of the InnoDB buffer pool, in pages.
Innodb_page_size
InnoDB page size (default 16KB). Many values are counted in pages; the page size enables them to be
easily converted to bytes
（3）配置方法
innodb_buffer_pool_size=1024M
1.12 innodb_flush_log_at_trx_commit
（1）简介
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。
0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；
1，则在每秒钟或是每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；
2，每次事务提交引起写入日志文件的动作，但每秒钟完成一次flush磁盘操作。
（2）配置依据
实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。
根据MySQL官方文档，在允许丢失最近部分事务的危险的前提下，可以把该值设为0或2。
（3）配置方法
innodb_flush_log_at_trx_commit=1
1.13 innodb_thread_concurrency
（1）简介
此参数用来设置innodb线程的并发数量，默认值为0表示不限制。
（2）配置依据
在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议，如下：
如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0；
如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128，
并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数，
例如，假设系统通常有40到50个用户，但定期的数量增加至60，70，甚至200。你会发现，
性能在80个并发用户设置时表现稳定，如果高于这个数，性能反而下降。在这种情况下，
建议设置innodb_thread_concurrency参数为80，以避免影响性能。
如果你不希望InnoDB使用的虚拟CPU数量比用户线程使用的虚拟CPU更多（比如20个虚拟CPU），
建议通过设置innodb_thread_concurrency 参数为这个值（也可能更低，这取决于性能体现），
如果你的目标是将MySQL与其他应用隔离，你可以考虑绑定mysqld进程到专有的虚拟CPU。
但是需 要注意的是，这种绑定，在myslqd进程一直不是很忙的情况下，可能会导致非最优的硬件使用率。在这种情况下，
你可能会设置mysqld进程绑定的虚拟 CPU，允许其他应用程序使用虚拟CPU的一部分或全部。
在某些情况下，最佳的innodb_thread_concurrency参数设置可以比虚拟CPU的数量小。
定期检测和分析系统，负载量、用户数或者工作环境的改变可能都需要对innodb_thread_concurrency参数的设置进行调整。
（3）配置方法：
innodb_thread_concurrency=8
1.14 innodb_log_buffer_size
此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。
innodb_log_buffer_size=8M
1.15. innodb_log_file_size = 50M
此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.
1.16. innodb_log_files_in_group = 3
为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3
1.17.read_buffer_size = 1M
MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享
18.read_rnd_buffer_size = 1M
MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。
19.bulk_insert_buffer_size = 8M
批量插入数据缓存大小，可以有效提高插入效率，默认为8M
1.20.binary log
log-bin=/data/mysql-bin
binlog_cache_size = 2M //为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存, 提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是--1M，后者建议是：即 2--4M
max_binlog_cache_size = 8M //表示的是binlog 能够使用的最大cache 内存大小
max_binlog_size= 512M //指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除。
expire_logs_days = 7 //定义了mysql清除过期日志的时间。
二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。
innodb_max_dirty_pages_pct ***********
innodb_additional_mem_pool_size (于2G内存的机器，推荐值是20M。32G内存的?100M)
transaction_isolation *********
1.21 安全参数
Innodb_flush_method=(O_DIRECT, fdatasync)
1、fdatasync ：
（1）在数据页需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘
2、 Innodb_flush_method=O_DIRECT
（1）在数据页需要持久化时，直接写入磁盘
（2）在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘
但，如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘
最高安全模式：
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
最高性能模式：
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fdatasync
一般情况下，我们更偏向于安全。
“双一标准”
innodb_flush_log_at_trx_commit=1 ***************
sync_binlog=1 ***************
innodb_flush_method=O_DIRECT?
三、参数优化结果
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log_bin=/data/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
relay_log_purge=0
max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
[client]
socket=/tmp/mysql.sock
再次压力测试 ：
[root@db02 ~]# mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema=‘test1‘
--query=‘select * from test1.tb1‘ engine=innodb
--number-of-queries=2000 -uroot -p123 -verbose
Warning: Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 7.271 seconds
Minimum number of seconds to run all queries: 7.271 seconds
Maximum number of seconds to run all queries: 7.271 seconds
Number of clients running queries: 100
Average number of queries per client: 20
对比之前：
mysqlslap --defaults-file=/etc/my.cnf
--concurrency=100 --iterations=1 --create-schema=‘test1‘
--query=‘select * from test1.tb1‘ engine=innodb
--number-of-queries=2000 -uroot -p123 -verbose
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 31.463 seconds
Minimum number of seconds to run all queries: 31.463 seconds
Maximum number of seconds to run all queries: 31.463 seconds
Number of clients running queries: 100
Average number of queries per client: 20
?
建立索引优化：
alter table oldboy.lufei add index idx_name(stuname);
mysqlslap --defaults-file=/etc/my.cnf
--concurrency=100 --iterations=1 --create-schema=‘test1‘
--query=‘select * from test1.tb1‘ engine=innodb
--number-of-queries=2000 -uroot -p123 -verbose