目录
2.解决瓶颈：做索引 查询一些MySQL数据库的性能参数用show status
3.通过profiling命令得到更准确的SQL执行消耗系统资源的信息,profiling默认是关闭的。
1.MySQL瓶颈优化（业务从小到大）
假设一个网站从最开始访问量很小做到日PV千万，我们来推测一下它的mysql服务器架构演变过程。
第一阶段： 网站访问量日pv量级在1w以下。
单台机器跑web和db，不需要做架构层调优（比如，不需要增加memcached缓存）。此时，数据往往都是每日冷备份的，但是有时候如果考虑数据安全性，会搭建一个mysql主从。
第二阶段： 网站访问量日pv达到几万。
此时单台机器已经有点负载，需要我们把web和db分开，需要搭建memcached服务作为缓存。也就是说，在这个阶段，我们还可以使用单台机器跑mysql去承担整个网站的数据存储和查询。如果做mysql主从目的也是为了数据安全性。
第三阶段： 网站访问量日pv达到几十万。
单台机器虽然也可以支撑，但是需要的机器配置要比之前的机器多好多。如果经费允许，可以购买配置很高的机器来跑mysql服务，但是并不是说，配置翻倍，到了一定阶段配置增加已经不能带来性能的增加。所以，此阶段，我们会想到做mysql服务的集群，也就是说我们可以拿多台机器跑mysql。但mysql的集群和web集群是不一样的，我们需要考虑数据的一致性，所以不能简单套用做web集群的方式。可以做的架构是，mysql主从，一主多从。为了保证架构的强壮和数据完整，主只能是一个，从可以是多个。还有一个问题，我们需要想到，就是在前端web层，我们程序里面指定了mysql机器的ip，那么当mysql机器有多台时，程序里面如何去配置？我们可以拿多台机器跑mysql，其中一台写，其他多台是读，我们只需要把读写的ip分别配置到程序中，程序自动会去区分机器。
第四阶段： 网站访问量日pv到几百万。
之前的一主多从模式已经遇到瓶颈，因为当网站访问量变大，读数据库的量也会越来越大，我们需要多加一些从进来，但是从的数量增加到数十台时，由于主需要把bin-log全部分到所有从上，那么这个过程本身就是一件很繁琐读取，势必会造成从上同步过来的数据有很大延迟。所以，我们可以做一个优化，把mysql原来的一主多从为一主一从，然后作为其他从的主，而前面的主，只负责网站业务的写入，而后面的从不负责网站任何业务，只负责给其他从同步bin-log。这样还可以继续多叠加几个从库。
第五阶段： 网站访问量日pv到1千万的时候，我们发现，网站的写入量非常大，我们之前架构中只有一个主，这里的主已经成为瓶颈了。所以，需要再进一步作出调整，建议分布式
2.解决瓶颈：做索引 查询一些MySQL数据库的性能参数用show status
SHOW STATUS LIKE 'Connections'; //连接mysql服务器的次数 SHOW STATUS LIKE 'uptime'; //mysql服务器的上线时间 SHOW STATUS LIKE 'com_select'; //查询操作的次数 SHOW STATUS LIKE 'com_insert'; //插入操作的次数 SHOW STATUS LIKE 'com_update'; //更新操作的次数 SHOW STATUS LIKE 'com_delete'; //删除操作的次数 SHOW STATUS LIKE 'slow_queries'; //查询mysql服务器的慢查询次数 explain select * from city \G //分析查询语句,或desc
索引提高了查询效率: Create index 索引名 on 表名(字段)
3.通过profiling命令得到更准确的SQL执行消耗系统资源的信息,profiling默认是关闭的。
查看profiling状态 show variables like '%profiling%'; 或 select @@profiling; 打开profiling功能 set profiling=1; 执行要测试的sql语句 select * from city where countrycode='CHN'; 查看资源消耗 show profiles; show profile for query 2; 完成后关闭 set profiling=0;
4.优化表结构
将字段很多的表分解成多个表
增加中间表
合理增加冗余字段
优化插入记录的速度 ***
(1)禁用索引(mysql 8.0支持)
ALTER TABLE city DISABLE KEYS;
(2)禁用唯一性检查
set unique_checks=0;
(3)使用批量插入
insert into fruits values ('x8','101','mongo2','5.5'),('x7','101','mongo2','5.5');
(4)使用load data infile批量插入
SET unique_checks=0; SET foreign_key_checks=0; set autocommit=0; load data local infile 'data.txt' into table t1
5.分析表，检查表和优化表
(1)分析表：分析关键字的分布
ANALYZE TABLE city;
显示结果：
Table是表名
op执行的操作是什么
msg_type 信息级别（status是正常状态，info是信息，note注意，warning警告，error错误）
msg_text 是显示信息
(2)检查表：检查是否存在错误
CHECK TABLE city ;
(3)优化表：消除删除或更新造成的空间浪费
optimize table city \G
========================================================================
1. 优化哲学
1.1 优化有风险
复杂的生产环境
没有意识到优化的风险和后果
优化技术可能带来的后果，需要在控制范围内
稳定性和业务可持续性比性能更重要
优化变更有风险
性能变好，维持和变差是等概率事件
优化不能只是数据库管理员担当风险，所有的人分享优化成果
优化工作是由业务需要驱使的
1.2 优化的范围
存储、主机和操作系统:
主机架构稳定性
I/O规划及配置
Swap
OS内核参数
网络问题
应用 :（Index，lock，session）
应用程序稳定性和性能
SQL语句性能
串行访问资源
性能欠佳会话管理
数据库优化:（内存、数据库设计、参数）
内存
数据库结构(物理&逻辑)
实例配置
2. 优化工具介绍
2.1 系统层
CPU : 计算(主)和调度(次)
MEM : 缓存cache和缓冲buffer
IO : 输入和输出
(1) top命令
%Cpu(s): 0.0 us, 0.0 sy, 100.0 id, 0.0 wa
id 空闲的CPU时间片占比
us 用户程序工作所占用的时间片占比
sy 内核工作花费的cpu时间片占比
过高的原因:
内核本身bug,建议centos7.6以上
并发很高
锁
wa cpu用来等待的时间片占比
IO
等待大的处理事件
锁
KiB Mem : 4937752 total, 3988956 free, 476100 used, 472696 buff/cache 4193924 avail Mem
KiB Swap: 1048572 total, 1048572 free, 0 used.
(2) iostat
[root@db01 ~]# iostat -dk 1
一般情况下，CPU高，IO也应该高。
如果：CPU 高 ，IO 比较低
wait 高： 有可以能IO出问题了（Raid ，过度条带化）
SyS 高： 有可能是锁的问题，需要进一步去数据库中判断
(3) vmstat
[root@db01 ~]# vmstat 1
2.2 数据库层工具
show status show variables show index show processlist show slave status show engine innodb status desc /explain slowlog
扩展类深度优化:
pt系列（pt-query-digest pt-osc pt-index 等） mysqlslap sysbench information_schema （I_S） performance_schema (P_S) sys
3. 优化思路
3.0 未优化前的压力测试
create database hehe charset utf8mb4; use hehe; set sql_log_bin=0; source t100w.sql grant all on *.* to root@'localhost' identified by '123'; [root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf \ --concurrency=100 --iterations=1 --create-schema='world' \ --query="select * from world.t100w where k2='ABxy'" engine=innodb \ --number-of-queries=200 -uroot -p -verbose
3.1 主机,存储,网络
主机
真实的硬件（PC Server）: DELL R系列 ，华为，浪潮，HP，曙光,联想
云产品：ECS、数据库RDS、DRDS、PolarDB
IBM 小型机 P6 570 595 P7 720 750 780 P8
CPU根据数据库类型
OLTP 联机事务处理 增删改 ERP，CRM，OA
OLAP 联机分析处理 查询 数据统计分析
IO密集型：线上系统，OLTP主要是IO密集型的业务，高并发
CPU密集型：数据分析数据处理，OLAP，cpu密集型的，需要CPU高计算能力（i系列，IBM power系列）
CPU密集型： I 系列的，主频很高，核心少
IO密集型： E系列（至强），主频相对低，核心数量多
内存
建议2-3倍cpu核心数量 （ECC）
磁盘选择
SATA-III SAS Fc SSD（sata） pci-e ssd Flash
主机 RAID卡的BBU(Battery Backup Unit)关闭
存储
根据存储数据种类的不同，选择不同的存储设备
配置合理的RAID级别(raid5、raid10、热备盘)
r0 :条带化 ,性能高
r1 :镜像，安全
r5 :校验+条带化，安全较高+性能较高（读），写性能较低 （适合于读多写少）
r10：安全+性能都很高，最少四块盘，浪费一半的空间（高IO要求）
网络
1、硬件买好的（单卡单口）
2、网卡绑定（bonding），交换机堆叠
以上问题，提前规避掉。
3.2 系统
Swap调整
echo 0 >/proc/sys/vm/swappiness的内容改成0（临时），
vim /etc/sysctl.conf
上添加vm.swappiness=0（永久）
sysctl -p
IO调度策略
centos 7 默认是deadline
cat /sys/block/sda/queue/scheduler
#临时修改为deadline(centos6)
echo deadline >/sys/block/sda/queue/scheduler
vi /boot/grub/grub.conf
更改到如下内容:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet IO ： raid no lvm ext4或xfs ssd IO调度策略
提前规划好以上所有问题，减轻MySQL优化的难度。
3.3 数据库实例(参数)
3.3.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 | +----------------------+-------+
（3）修改方式举例
vim /etc/my.cnf
Max_connections=1024
补充:
1.开启数据库时,我们可以临时设置一个比较大的测试值
2.观察show status like 'Max_used_connections';变化
3.如果max_used_connections跟max_connections相同,
那么就是max_connections设置过低或者超过服务器的负载上限了
3.3.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
3.3.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=60
interactive_timeout=1200
长连接的应用，为了不去反复的回收和分配资源，降低额外的开销。
一般我们会将wait_timeout设定比较小，interactive_timeout要和应用开发人员沟通长链接的应用是否很多。如果他需要长链接，那么这个值可以不需要调整。
另外还可以使用类外的参数弥补。
3.3.4 key_buffer_size *****
(1) myisam 表的索引缓冲区
(2) 临时表的缓冲区
mysql> show status like "created_tmp%"; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | | Created_tmp_files | 6 | | Created_tmp_tables | 1 | Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables) 越高越好 Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables) Created_tmp_disk_tables/(Created_tmp_disk_tables + Created_tmp_tables)
控制在5%-10%以内
mysql> show variables like "key_buffer_size%";
3.3.5 query_cache_size
查询缓存简称QC,MySQL将查询结果存放在缓冲区
show variables like '%query_cache%';
query_cache_limit：超过此大小的查询将不缓存
query_cache_min_res_unit：缓存块的最小大小,默认是4KB，值大对大数据查询有好处，小数据查询容易造成内存碎片和浪费
query_cache_size：查询缓存大小,最小单位是1024byte
query_cache_type：缓存类型,值0关闭、值1缓存所有、值2仅缓存select语句中SQL_CACHE指定需要缓存的查询
vim /etc/my.cnf
query_cache_size=256M
query_cache_type=1
3.3.6 max_connect_errors
MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况;
当超过指定次数，MYSQL服务器将禁止host的连接请求。
3.3.7 sort_buffer_size
connection级参数，每个需要进行排序的线程分配该大小的一个缓冲区。
增加值加速ORDER BY或GROUPBY操作，值过大容易耗尽内存。
3.3.8 max_allowed_packet
MySQL根据配置文件限制Server接受的数据包大小
有时候大的插入和更新会受 max_allowed_packet 参数限制，导致写入或者更新失败。
最大值是1GB，必须设置1024的倍数。
3.3.9 join_buffer_size
用于表间关联缓存的大小
3.3.10 thread_cache_size
服务器线程缓存，处理客户的线程将会缓存起来以响应下一个客户而不是销毁；
设置规则如下：1GB 内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。
3.3.11 innodb_buffer_pool_size
InnoDB使用该参数指定大小的内存来缓冲数据和索引；
单独的MySQL数据库，最大可设置成物理内存的80%。
3.3.12 innodb_flush_log_at_trx_commit
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。
0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；
1，则在每秒钟或是每次事务的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；
2，每次事务提交引起写入日志文件的动作，但每秒钟完成一次flush磁盘操作。
注：1安全，0、2快速
3.3.13 innodb_thread_concurrency
设置innodb线程的并发数量，值为0表示不限制；
建议设置服务器的CPU核数或是cpu的核数的2倍，默认设置一般为8.
3.3.14 innodb_log_buffer_size
确定日志文件所用的内存大小，以M为单位。
缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。
3.3.15 innodb_log_file_size
确定数据日志文件的大小，以M为单位，更大的设置可以提高性能。
3.3.16 innodb_log_files_in_group
为提高性能，MySQL以循环方式将日志文件写到多个文件，推荐设置为3。
3.3.17 read_buffer_size
MySql读入缓冲区大小，connection级参数
3.3.18 read_rnd_buffer_size
MySql的随机读（查询操作）缓冲区大小，如果需要排序大量数据，可适当调高该值。
connection级参数
3.3.19 bulk_insert_buffer_size
批量插入数据缓存大小，可以有效提高插入效率，默认为8M
3.3.20 binary log
binlog_cache_size=2M
为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存,提高记录bin-log的效率。
没有什么大事务，dml也不是很频繁的情况下可以设置小一点；
如果事务大而且多，dml操作也频繁，则可以适当的调大一点。
max_binlog_cache_size = 8M 表示的是binlog 能够使用的最大cache内存大小
max_binlog_size = 512M 指定binlog日志文件的大小
expire_logs_days = 7 定义了mysql清除过期日志的时间
3.3.21 log_queries_not_using_indexes
开启记录所有没有经过索引的查询。
3.4 优化后的压力测试
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
