一.简介

Mysql数据库的优化包括硬件层面与操作系统优化、性能层面优化和安全层面优化。随着业务数据量越来越大，任何数据库最终都会达到硬件的极限，如存储空间、磁盘IO、CPU、内存和网络带宽等；性能优化包括表、查询和配置设置等多个因素，表结构是否合理？是否有正确的索引来提高查询效率？事务性存储InnoDB或非事务性存储引擎MyISAM选择是否正取？如每个表是否使用了适当的存储引擎，压缩表时有没有正取使用存储引擎？共享访问并行处理与锁定策略制定的是否合适？用于缓存的所有内存区域的大小是否正确，如内存大页（Transparent Huge Pages, THP）和 NUMA（Non-Uniform Memory Access）设置等。优化是多方面需要考虑的问题，不同的业务优化参数可能需要重新设置，对任何一个参数的修改都要进过测试，常用的测试工具mysqldumpshow、pt-query-digest、pt-variable-advisor、mysqltuner和tuning-primer等。

Mysql8.0官方优化参考文档

二.硬件与操作系统层面优化关注点

1.硬件选配

#CPU 多少U多少核的配置，与业务系统每秒并发密切相关，需要多少线程
innodb_thread_concurrency = 64   #如32核，CPU核心的2倍

#硬盘选择具有高IOPS的SSD硬盘 阿里云SSD 200-300Mbps
#RAID卡 RAID0 RAID10 最高的存储性能
#网卡选择速度为1Gbps或更高，支持网卡绑定

#内存选择大小，热点数据与缓存数据等决定
innodb_buffer_pool_size = 64G    #物理内存的50%-80%,另外innodb_buffer_pool_size要是下面这两个参数相乘的倍数
innodb_buffer_pool_instances = 32G 
innodb_buffer_pool_chunk_size = 1G

#关闭numa 禁用swap  
#BIOS关闭numa,推荐，等于1是关闭
numactl --hardware
#操作系统GRUB关闭numa
/boot/grub/grub.cfg 添加numa = off
#数据库级别关闭numa
innodb_numa_interleave = OFF

2.操作系统

#关闭防火墙禁用SELINUX
systemctl disable firewalld
sed -i 's/^SELINUX=enforcing$/SELINUX=disabled/' /etc/selinux/config

#内核优化-更改文件句柄和最大进程数
vi /etc/sysctl.conf
vm.swappiness = 0  #禁用SWAP
net.ipv6.conf.all.disable_ipv6 = 1  #禁用IPV6
vm.dirty_ratio = 20
vm.dirty_background_ratio = 10
net.ipv4.tcp_max_syn_backlog = 819200
net.core.netdev_max_backlog = 400000
net.core.somaxconn = 4096
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 0

#最大可打开的文件描述符
vi /etc/security/limits.conf 
mysql hard nofile 65535
mysql soft nofile 65535

#文件系统优，推荐使用XFS文件格式挂载
#不使用LVM，LVM有一个软件层，并未直接使用物理磁盘，尽量前期规划好磁盘空间
#关闭内存 透明大页THP，原因是影响内存访问性能
echo 'echo never > /sys/kernel/mm/transparent_hugepage/enabled' >> /etc/rc.local
echo 'echo never > /sys/kernel/mm/transparent_hugepage/defrag' >> /etc/rc.local
chmod +x /etc/rc.local

#io调度
#临时修改，重启可能无效
echo noop > /sys/block/sda/queue/scheduler 或 echo deadline >/sys/block/sda/queue/scheduler 
#内核修改io调度
vi /etc/grub.conf
kernel /vmlinuz-2.6.32-220.el6.x86_64 ... elevator=deadline

三.数据库版本选择

mysql开源版无论测试还是生产尽量选择GA版（正式发行版），GA版的小版本尽量选择双数版。

四.三层结构及部分参数优化

需要根据服务器配置和业务情况，以及维护数据库时遇到的问题等进行不断优化改进。

1.连接层

max_connections = 2000         #最大连接数，并发请求
max_connect_errors = 10        #最大操作连接数，防止爆破
wait_timeout = 600             #非交互连接超时，10分钟  api jdbc
interactive_timeout = 600      #交互式连接超时,10分钟  连接工具
net_read_timeout = 120         #读超时2分钟
net_write_timeout = 120        #写超时2分钟
max_allowed_packet = 64M       #允许最大的数据包，64G推荐64M

2.Server层

slow_query_log = ON                         #开启慢sql
slow_query_log_file = /data/3306/slow.log   #慢sql存储路径
long_query_time = 1                         #记录≥1s的sql
log_queries_not_using_indexes = ON          #开启
log_throttle_queries_not_using_indexes = 10 #未走Index记录10条
sort_buffer_size = 1M
join_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
tmp_table_size = 16M
max_heap_table_size = 16M
max_execution_time = 28800                   #select最大执行时间
innodb_lock_wait_timeout = 60                
lower_case_table_names = 1                             
thread_cache_size = 64                       #cache中最大连接线程数                  
log_timestamps = SYSTEM             
init_connect = "set names utf8"              #C连S的字符集
event_scheduler = OFF                        #事件调度 关闭
secure-file-priv = /tmp                      #导入导出限制为/tmp目录        
binlog_expire_logs_seconds = 2592000         #自动删除过期binlogs时间        
sync_binlog = 1                  
log-bin = /data/3306/mysql-bin
log-bin-index = /data/3306/mysql-bin.index
max_binlog_size = 500M
binlog_format = ROW                          #默认主从复制binlog日志格式，任何情况都可以被复制

3.存储引擎层

default-storage-engine = InnoDB
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 20M
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 64G    #物理内存的50%-80%
innodb_buffer_pool_instances = 32G 
innodb_buffer_pool_chunk_size = 1G
innodb_buffer_pool_load_at_startup = 0
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 60
innodb_io_capacity_max = 8000
......

五.sql语句优化和索引优化

#sql语句优化
避免使用SELECT *，减少语句查询范围，精确查询条件
避免使用not in、not exists 、<>、like %%
多表连接，小表驱动大表
多条件，符合联合索引最左原则
减少临时表应用，优化order by 、group by、union、distinct、join等
查询条件减少使用函数、拼接字符等条件、条件隐式转换
union all 替代 union
减少having子句使用
如非必须不使用for update语句
update和delete，开启安全更新参数
减少inset  ... select语句应用
使用load替代insert录入大数据
导入大量数据时，可以禁用索引、增大缓冲区、增大redo文件和buffer、关闭autocommit、RC级别可以提高效率
优化limit，最好业务逻辑中先获取主键ID，再基于ID进行查询

#索引优化
避免不必要的索引
尽可能使用覆盖索引
选择合适的索引列顺序，最常查询的列放在索引的最前面
唯一键由3个以下字段组成，并且字段都是整形时，使用唯一键作为主键
没有唯一键或者唯一键不符合5中的条件时，使用自增id作为主键
唯一键不和主键重复
单张表的索引数量控制在5个以内，若单张表多个字段在查询需求上都要单独用到索引，需要经过DBA评估。查询性能问题无法解决的，应从产品设计上进行重构。
使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary
UPDATE、DELETE语句需要根据WHERE条件添加索引
对长度大于50的VARCHAR字段建立索引时，按需求恰当的使用前缀索引，或使用其他方法
减少冗余索引和使用率较低的索引

六.安全优化

影响安全性的一般因素：
合理授权用户、口令复杂度及最小权限、系统表保证只有管理员用户可访问
使用加密连接，使用ssl连接
删除数据库匿名用户，锁定非活动过期用户
替换MySQL默认端口号，设置访问控制
优化业务代码，防止注入
使用安全的组件和插件