MySQL优化实施方案(持续更新中)

首先性能优化不是一簇而就的,需要不断观察调试,才能最终确定是否达到最优.
平台Centos7,mariadb10
(本文必须参考多个高人的文章,取之精华,结合自身实践,从宏观和微观不同角度详细的阐述,对如何优化MySQL豁然开朗,三人行必有我师)

优化可能带来的问题
优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统。
优化手段本来就有很大的风险，只不过你没能力意识到和预见到！
任何的技术可以解决一个问题，但必然存在带来一个问题的风险！
对于优化来说解决问题而带来的问题,控制在可接受的范围内才是有成果。
保持现状或出现更差的情况都是失败！

优化的执行者
在进行数据库优化时，应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等，业务相关人员共同参与。

优化的目标
在数据库优化上有两个主要方面：即安全与性能。

一 操作系统及硬件
详细命令可参考系统性能瓶颈分析
https://blog.csdn.net/CSDN1887/article/details/81633504

cpu方面
sar,top, htop、nmon、mpstat
memory方面
vmstat、free 、ps -aux 、
IO设备（磁盘、网络）
iostat,netstat,lsof,iftop,iptraf

当然不排除下面这些特殊情况：
问题一：cpu负载高，IO负载低
内存不够
磁盘性能差
SQL问题 ——>去数据库层，进一步排查sql问题
IO出问题了（磁盘到临界了、raid设计不好、raid降级、锁、在单位时间内tps过高）
tps过高: 大量的小数据IO、大量的全表扫描
问题二：IO负载高，cpu负载低
大量小的IO 写操作：
　　autocommit ，产生大量小IO
　　IO/PS,磁盘的一个定值，硬件出厂的时候，厂家定义的一个每秒最大的IO次数。
大量大的IO 写操作
　　SQL问题的几率比较大
问题三：IO和cpu负载都很高
硬件不够了或sql存在问题

主机方面：
根据数据库类型，主机CPU选择、内存容量选择、磁盘选择
平衡内存和磁盘资源
随机的I/O和顺序的I/O
主机 RAID卡的BBU(Battery Backup Unit)关闭
cpu的选择：

    cpu的两个关键因素：核数、主频
    根据不同的业务类型进行选择：
    cpu密集型：计算比较多，OLTP     主频很高的cpu、核数还要多
    IO密集型：查询比较，OLAP         核数要多，主频不一定高的

内存的选择：

    OLAP类型数据库，需要更多内存，和数据获取量级有关。
    OLTP类型数据一般内存是cpu核心数量的2倍到4倍，没有最佳实践。

存储方面：

根据存储数据种类的不同，选择不同的存储设备
配置合理的RAID级别(raid5、raid10、热备盘)
对与操作系统来讲，不需要太特殊的选择，最好做好冗余（raid1）（ssd、sas 、sata）
raid卡：主机raid卡选择：
    　　实现操作系统磁盘的冗余（raid1）
　　　平衡内存和磁盘资源
　　　随机的I/O和顺序的I/O
　　　主机 RAID卡的BBU(Battery Backup Unit)要关闭。

网络设备方面：

使用流量支持更高的网络设备（交换机、路由器、网线、网卡、HBA卡）

SWAP：

MySQL尽量避免使用swap。
阿里云的服务器中默认swap为0

/proc/sys/vm/swappiness的内容改成0（临时），/etc/sysctl.conf上添加vm.swappiness=0（永久）
　　这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。
　　当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。
修改MySQL的配置参数innodb_flush_method，开启O_DIRECT模式。
　　这种情况下，InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘，但是redo log依旧会使用文件系统cache。
　　值得注意的是，Redo log是覆写模式的，即使使用了文件系统的cache，也不会占用太多

IO ：

raid、no lvm、 ext4或xfs、ssd、IO调度策略
#echo deadline>/sys/block/sda/queue/scheduler   临时修改为deadline
   永久修改
vi /boot/grub/grub.conf
更改到如下内容:
kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet

Linux系统内核参数优化

vim /etc/sysctl.conf
    net.ipv4.ip_local_port_range = 1024 65535   # 用户端口范围
    net.ipv4.tcp_max_syn_backlog = 4096 
    net.ipv4.tcp_fin_timeout = 30 
    fs.file-max=65535          # 系统最大文件句柄，控制的是能打开文件最大数量  

用户限制参数（mysql可以不设置以下配置）

vim    /etc/security/limits.conf 
    * soft nproc 65535
    * hard nproc 65535
    * soft nofile 65535
    * hard nofile 65535

业务应用和数据库应用独立,
　

防火墙：iptables、selinux等其他无用服务(关闭)：
    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

　　安装图形界面的服务器不要启动图形界面 runlevel 3

二 Mysql系统参数调优
Max_connections 并发需求高的需修改
back_log mysql能暂存的连接数量
wait_timeout, mysql在关闭一个非交互的连接之前所要等待的秒数
interactive_timeout 关闭一个交互的连接之前所需要等待的秒数
默认的是28800，可调优为100
key_buffer_size 索引缓冲区的大小,根据系统内存情况修改
query_cache_size 查询缓存大小QC
query_cache_type 默认on
query_cache_limit 超限则不缓存
query_cache_min_res_unit 最小缓存块的大小
max_connect_errors 超过指定次数，mysql服务器将禁止host的连接请求
sort_buffer_size 每个需要进行排序的线程分配该大小的一个缓冲区,根据设计的连 接数来定
max_allowed_packet server接受的数据包大小
join_buffer_size sort_buffer_size一样，该参数对应的分配内存也是每个连接独享
thread_cache_size 服务器线程缓存,提高参数值有利于,客户连接
1GB 内存配置为8，2GB配置为16 etc,
thread_pool_size 线程池大小
thread_pool_max_thread 线程池数量,超过被缓存
innodb_buffer_pool_size 指定大小的内存来缓冲数据和索引,最大可以把该值设置成物理内存的80%
innodb_flush_log_at_trx_commit log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；1，则在每秒钟或是每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；设置为2，每次事务提交引起写入日志文件的动作，但每秒钟完成一次flush磁盘操作
MySQL手册，在允许丢失最近部分事务的危险的前提下，可以把该值设为0或2。
innodb_thread_concurrency 设置innodb线程的并发数量，默认值为0表示不限制，若要设置则与服务器的CPU核数相同或是cpu的核数的2倍，建议用默认设置，一般为8.
innodb_log_buffer_size 确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小
innodb_log_file_size 更大的设置可以提高性能
innodb_log_files_in_group 可以以循环方式将日志文件写到多个文件。推荐设置为3
read_buffer_size 可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享
read_rnd_buffer_size 但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
bulk_insert_buffer_size 批量插入数据缓存大小，可以有效提高插入效率
binlog_cache_size 没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点
max_binlog_cache_size
max_binlog_size 不能将该变量设置为大于1GB或小于4096字节
expire_logs_days

一些参数汇总

slow_query_log=1
slow_query_log_file = /usr/local/mysql/data/slow_query_log
long_query_time=1
log-queries-not-using-indexes
max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=256M
query_cache_size=256M
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=2048M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
log-bin=mysql-bin
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=1M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=64M
server-id=1

检查问题常用工具

mysql
msyqladmin                                 mysql客户端，可进行管理操作
mysqlshow                                  功能强大的查看shell命令
show [SESSION | GLOBAL] variables          查看数据库参数信息
SHOW [SESSION | GLOBAL] STATUS             查看数据库的状态信息
information_schema                         获取元数据的方法
SHOW ENGINE INNODB STATUS                  Innodb引擎的所有状态
SHOW PROCESSLIST                           查看当前所有连接session状态
explain                                    获取查询语句的执行计划
show index                                 查看表的索引信息
slow-log                                   记录慢查询语句
mysqldumpslow                              分析slowlog文件的

其他工具

zabbix                  监控主机、系统、数据库（部署zabbix监控平台）
pt-query-digest         分析慢日志
mysqlslap               分析慢日志
sysbench                压力测试工具
mysql profiling         统计数据库整体状态工具    
Performance Schema      mysql性能状态统计的数据
workbench               管理、备份、监控、分析、优化工具（比较费资源）

三 MySQL数据库设计调优
https://blog.csdn.net/sinat_23080035/article/details/52802569
数据结构优化

①优化insert语句 
·如果从同一客户端插入很多行，应该尽量使用多个值表一次性插入； 
·如果从不同客户端插入很多行，可以使用insert delayed语句先把数据放在内存的队列中，并不真正写入磁盘，比每条语句分别插入快得多； 
·当从一个文本文件装载一个表时，使用load data infile，这通常比使用很多insert 语句快20倍； 
·如果在MyISAM表中进行批量插入，可以通过增加bulk_insert_buffer_size变量值的方法来提高速度。

②优化 order by语句 
MySQL中有两种排序方式，第一种通过有序索引顺序扫描直接返回有效数据，不需要额外的排序，操作效率较高；第二种对返回的数据进行排序，也就是常说的Filesort排序，所有不是通过索引直接返回排序结果的排序都是filesort排序。 
优化目标：尽量通过索引直接返回有序数据，减少额外的排序。 
通过创建合适的索引能减少filesort出现，但是某些情况下，条件限制不能让filesort消失，那就需要想办法加快filesort的操作。filesort有两种排序算法，一种是一次扫描算法（较快），二种是两次扫描算法。适当加大系统变量max_length_for_sort_data的值，能够让MySQL选择更优化的filesort排序算法；适当加大sort_buffer_size排序区，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行。尽量只使用必要的字段，select具体的字段名称，而不是select * 选择所有字段，这样可以减少排序区的使用，提高SQL性能。

③优化group by 语句 
MySQL默认对所有group by col1,col2…的字段进行排序，可以指定order by null禁止排序。

④优化嵌套查询 
MySQL5.5及以下版本，子查询的效率不如连接查询（join），因为MySQL不需要在内存中创建临时表来完成这个在逻辑上需要两个步骤的查询工作。

⑤优化or 查询 
对于含有or的查询子句，如果要利用索引，则or之间的每个条件列都必须使用索引；如果没有索引，可以考虑增加索引。 
MySQL在处理含有or的查询时，实际上对or的各个字段分别查询后的结果进行了union操作。

⑥优化分页查询 
·第一种 在索引上完成排序分页操作，然后根据主键关联回原表查询所需要的其他列的内容； 
·第二种 在排序字段不会出现重复值的情况下，新增一个参数记录上次查询的最后一条记录，将limit m,n转化成limit n.

⑦使用SQL提示 
就是在SQL语句中加入一些认为提示，让MySQL按照特定方案执行，以达到优化操作的目的。 
·use index 指定MySQL参考的索引而忽略别的索引 
·ignore index 让MySQL忽略某个或某些索引 
·force index 强制MySQL使用某个特定的索引

⑧其他 
·使用REGEXP，比如代替like. 
·使用rand()提取随机行 
·表的字段尽量不使用自增长变量，在高并发的情况下可能会对MySQL的效率有较大影响。

字段选择合适的数据类型

procedure analyse() 可以对当前应用的表进行分析，对数据表中列的数据类型提出优化建议。

三范式和反三范式

对表进行水平或者垂直拆分
   这点很重要,尤其是对历史数据不能删除的情况下

数据索引优化
MySQL的索引在存储引擎层实现，而不是在服务器层。
可以通过show status like ‘Handler_read%’命令来查看索引使用情况。

1.2.1MySQL 中索引的存储类型目前有四种（B-Tree、Hash、空间索引R-Tree、全文索引Full-text），具体和表的存储引擎相关；MyISAM 和 InnoDB 存储引擎都支持 B-Tree 和全文索引（Full-text，InnoDB 5.6 +）；MyISAM还支持空间索引（R-Tree）；Memory/Heap存储引擎可以支持 HASH和 B-Tree 索引，不过只有Memory/Heap支持 Hash索引。

MySQL如何使用索引

（1）MySQL使用索引的典型情景
①匹配全值（match the full value）
②匹配值的范围（match a range of values）
③匹配最左前缀（match a leftmost prefix）最左匹配原则是MySQL中B-Tree索引使用的首要原则。
④只查询索引（index only query）当然where子句中要满足最左匹配原则
⑤匹配列前缀（match a column prefix）使用复合索引的第一列的开头一部分
⑥复合索引中，一部分匹配精确内容 and 其他部分匹配一个范围（match one part exactly and match a range on another part）
⑦列名是索引，那么column_name is null就会使用索引,比如where column_name is null
（2）MySQL不使用索引的典型情景
①like “%query”不使用B-Tree索引，但like “query%”会使用B-Tree索引。
②数据类型出现隐式转换的时候也不会使用索引。尤其当列类型是字符串时，一定记得在where条件中把字符串常量值用引号引起来，比如where last_name = ‘1’;
③使用复合索引时，查询条件不包含索引的最左边部分
④用or 分割的条件，如果其中一个列中没有索引，则涉及的另一个索引也不会被用到。
⑤如果MySQL估计使用索引比全表扫描更慢，则不使用索引。

索引参数调整

default-storage-engine
innodb_buffer_pool_size       # 没有固定大小，50%测试值，看看情况再微调。但是尽量设置不要超过物理内存70%
innodb_file_per_table=(1,0)
innodb_flush_log_at_trx_commit=(0,1,2) # 1是最安全的，0是性能最高，2折中
binlog_sync
Innodb_flush_method=(O_DIRECT, fdatasync)
innodb_log_buffer_size        # 100M以下
innodb_log_file_size          # 100M 以下
innodb_log_files_in_group     # 5个成员以下,一般2-3个够用（iblogfile0-N）
innodb_max_dirty_pages_pct   # 达到百分之75的时候刷写 内存脏页到磁盘。
log_bin
max_binlog_cache_size         # 可以不设置
max_binlog_size               # 可以不设置
innodb_additional_mem_pool_size    #小于2G内存的机器，推荐值是20M。32G内存以上100M

一般应急调优的思路：
需要立马解决的场景！

1、show processlist
2、explain  select id ,name from stu where name='clsn'; # ALL  id name age  sex
            select id,name from stu  where id=2-1 函数 结果集>30;
　　　 show index from table;
3、通过执行计划判断，索引问题（有没有、合不合理）或者语句本身问题
4、show status  like '%lock%';    # 查询锁状态
　　kill SESSION_ID;   # 杀掉有问题的session

常规调优思路：
业务周期性的卡顿

1、查看slowlog，分析slowlog，分析出查询慢的语句。
2、按照一定优先级，进行一个一个的排查所有慢语句。
3、分析top sql，进行explain调试，查看语句执行时间。
4、调整索引或语句本身。

常用分析步骤

①通过show status 命令了解各种SQL的执行效率，

show [session | global] status;
1
可以根据需要加上参数来显示session级（当前连接，默认）和global级（自数据库上次启动至今）的统计结果。 
eg:

 show status like 'Com_%';
1
显示当前连接所有统计参数的值。 
Com_xxx表示每个xxx语句执行的次数，通常需要注意的是下面几个参数： 
Com_select/Com_insert/Com_update/Com_delte。

②定位执行效率较低的SQL语句 
·通过show processlist命令实时查看当前SQL的执行情况； 
·通过慢查询日志（结束以后记录）定位出现的问题。

③通过explain 或 desc分析低效SQL的执行计划 
select_type(simple/primary/union/subquery)/table/type/possible_keys/key/key_len/rows/extra

④通过show profile 分析SQL 
show profile 能帮我们了解时间都耗费到哪里去了。 
MySQL从5.0.37版本开始增加了show profile和show profiles语句的支持， 
通过secect @have_profiling命令能够看到当前MySQL是否支持profile， 
通过show profiles我们能够更清楚了解SQL执行的过程， 
通过show profile for query我们能看到执行过程中线程的每个状态和消耗的时间。

⑤通过trace分析优化器如何选择执行计划 
MySQL5.6提供了对SQL的跟踪trace,能帮我们了解为什么优化器选择执行A计划而不是B计划，进一步理解优化器的行为。

⑥确定问题并采取相应的优化措施

1.1.2两个简单实用的优化方法

①定期分析和检查表

analyze table tbl_name;
check table tbl_name;
1
2
②定期优化表
optimize table tbl_name;

四 应用程序调优
使用连接池

减少对MySQL的访问

理清应用逻辑，能一次取出的数据不用两次；
使用查询缓存
当只要一行数据时使用 LIMIT 1
在Join表的时候使用相当类型的例，并将其索引
千万不要 ORDER BY RAND()
避免 SELECT *
永远为每张表设置一个ID
使用 ENUM 而不是 VARCHAR
从 PROCEDURE ANALYSE() 取得建议

尽可能的使用 NOT NULL
Prepared Statements
无缓冲的查询  mysql_unbuffered_query()
把IP地址存成 UNSIGNED INT
固定长度的表会更快
垂直分割

拆分大的 DELETE 或 INSERT 语句
越小的列会越快
选择正确的存储引擎
使用一个对象关系映射器（Object Relational Mapper）
小心“永久链接”

MySQL的查询缓存（MySQL query cache）是4.1版本之后新增的功能，作用是存储select的查询文本和相应结果。如果随后收到一个相同的查询，服务器会从查询缓存中重新得到查询结果，而不再需要解析和执行查询。
查询缓存适用于更新不频繁的表，当表更改（包括表结构和数据）后，查询缓存会被清空。
③在应用端增加cache层
这点很重要,对大型数据设计尤为重要.
④负载均衡
负载均衡（Load Balance）是实际应用中使用非常普遍的一种优化方法，它的机制就是利用某种均衡算法，将固定的负载量分布到不同的服务器上，以此来减轻单台服务器的负载，达到优化的目的。负载均衡可以用在系统中的各个层面中，从前台的 Web 服务器到中间层的应用服务器，最后到数据层的数据库服务器，都可以使用。
·利用 MySQL 复制分流查询和更新操作
利用 MySQL 的主从复制可以有效地分流更新操作和查询操作，具体的实现是一个主服务器承担更新操作，而多台从服务器承担查询操作，主从之间通过复制实现数据的同步。通过复制来分流查询和更新是减少主数据库负载的一个常用方法，但是这种办法也存在一些问题，最主要的问题是当主数据库上更新频繁或者网络出现问题的时候，主从之间的数据可能存在比较大的延迟更新，从而造成查询结果和主数据库上有所差异。因此在设计应用的时候需要有所考虑。

参考
https://blog.csdn.net/sinat_23080035/article/details/52802569
https://www.cnblogs.com/jiaosq/p/5843437.html
https://www.cnblogs.com/clsn/p/8214048.html