本文最后更新于 66 天前，其中的信息可能已经过时，如有错误请发送邮件到wuxianglongblog@163.com
MySQL的全面优化
一.硬件层面优化
1 硬件选配
DELL、HP、IBM、华为、浪潮。
CPU：I系列（适合CPU密集型，适合在线分析的系统）、E系列(适合IO密集型，核心数偏多，并发性较高)
内存：带有ECC功能的内存。
IO : SAS HHD、 pci-e SSD 、 Nvme flash（非易失性内存快速）
raid卡：Raid10, RAID 0
网卡： 单卡单口 bonding + 交换机堆叠
云服务器： ECS 、RDS 、TDSQL、PolarxDB2.关闭NUMA[了解即可]
(1)bios级别:
在bios层面numa关闭时，无论os层面的numa是否打开，都不会影响性能。
# numactl --hardware
available: 1 nodes (0) #如果是2或多个nodes就说明numa没关掉
(2)OS grub级别:
vi /boot/grub2/grub.cfg
#/* Copyright 2010, Oracle. All rights reserved. */
default=0
timeout=5
hiddenmenu
foreground=000000
background=ffffff
splashimage=(hd0,0)/boot/grub/oracle.xpm.gz
title Trying_C0D0_as_HD0
root (hd0,0)
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
initrd /boot/initrd-2.6.18-128.1.16.0.1.el5.img
温馨提示:
在os层numa关闭时,打开bios层的numa会影响性能，QPS会下降15-30%。
(3)数据库级别:
mysql> SHOW VARIABLES LIKE '%numa%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_numa_interleave | OFF |
+------------------------+-------+
或者：
vi /etc/init.d/mysqld
找到如下行
# Give extra arguments to mysqld with the my.cnf file. This script
# may be overwritten at next upgrade.
$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &
wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?
将$bindir/mysqld_safe --datadir="$datadir"这一行修改为：
/usr/bin/numactl --interleave all $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &
wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$?3.阵列卡配置建议
raid10(推荐),raid 0.
SSD或者PCI-E或者Flash
强制回写（Force WriteBack）
BBU 电池:
如果没电会有较大性能影响、定期充放电。
如果有UPS、多路电源、发电机。可以关闭。
关闭预读:
有可能的话开启Cache(如果UPS、多路电源、发电机。)4.网卡绑定
bonding技术，业务数据库服务器都要配置bonding继续。建议是主备模式。交换机一定要堆叠。5.存储多路径
使用独立存储设备的话，需要配置多路径。
linux 自带 : multipath
厂商提供 : 二.系统层面优化
1.内核优化-更改文件句柄和进程数
(1)/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
(2)limits.conf
nofile 630002.防火墙
禁用selinux ： /etc/sysconfig/selinux 更改SELINUX=disabled.
iptables如果不使用可以关闭。可是需要打开MySQL需要的端口号3.文件系统优化
(1)推荐使用XFS文件系统
(2)MySQL数据分区独立 ，例如挂载点为: /data
(3)mount参数 defaults, noatime, nodiratime, nobarrier
如/etc/fstab：
/dev/sdb /data xfs defaults,noatime,nodiratime,nobarrier 1 24.不使用LVM
因为LVM会需要创建一个软件层，并不会直接使用磁盘设备，会造成一定性能的影响。5.关闭THP
vi /etc/rc.local
在文件末尾添加如下指令：
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
[root@master ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
always madvise [never]
[root@master ~]# cat /sys/kernel/mm/transparent_hugepage/defrag
always madvise [never]6.io调度
SAS ： deadline
SSD&PCI-E： noop
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
三. 数据库版本选择
(1)稳定版：选择开源的社区版的稳定版GA版本。
(2)选择mysql数据库GA版本发布后6个月-12个月的GA双数版本，大约在15-20个小版本左右。
(3)要选择前后几个月没有大的BUG修复的版本，而不是大量修复BUG的集中版本。
(4)要考虑开发人员开发程序使用的版本是否兼容你选的版本。
(5)作为内部开发测试数据库环境，跑大概3-6个月的时间。
(6)优先企业非核心业务采用新版本的数据库GA版本软件。
(7)向DBA高手请教，或者在技术氛围好的群里和大家一起交流，使用真正的高手们用过的好用的GA版本产品。
最终建议： 8.0.20是一个不错的版本选择。向后可以选择双数版。四.数据库三层结构及核心参数优化
1.连接层
max_connections=1000 #*****
max_connect_errors=999999
wait_timeout=600 #*****
interactive_wait_timeout=3600
net_read_timeout = 120
net_write_timeout = 120
max_allowed_packet= 64M #*****
推荐阅读:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html2.Server层
sql_safe_updates =1 # *****
slow_query_log =ON
slow_query_log_file =/data/3307/slow.log # *****
long_query_time =1 # *****
log_queries_not_using_indexes =ON # *****
log_throttle_queries_not_using_indexes = 10 # *****
sort_buffer = 1M
join_buffer = 1M
read_buffer = 1M
read_rnd_buffer = 1M
tmp_table = 16M
heap_table = 16M
max_execution_time = 28800
lock_wait_timeout = 60 # *****
lower_case_table_names =1 # *****
thread_cache_size =64
log_timestamps =SYSTEM # *****
init_connect ="set names utf8" # *****
event_scheduler =OFF
secure-file-priv =/tmp # *****
binlog_expire_logs_seconds =2592000 # *****
sync_binlog =1 # *****
log-bin =/data/3307/mysql-bin
log-bin-index =/data/3307/mysql-bin.index
max_binlog_size =500M
binlog_format =ROW3.存储引擎层
transaction-isolation ="READ-COMMITTED" # *****
innodb_data_home_dir =/xxx
innodb_log_group_home_dir =/xxx
innodb_log_file_size =2048M
innodb_log_files_in_group =3
innodb_flush_log_at_trx_commit =2 # *****
innodb_flush_method =O_DIRECT # *****
innodb_io_capacity =1000 # *****
innodb_io_capacity_max =4000
innodb_buffer_pool_size =64G # *****
innodb_buffer_pool_instances =4 # *****
innodb_log_buffer_size =64M # *****
innodb_max_dirty_pages_pct =85 # *****
innodb_lock_wait_timeout =10 # *****
innodb_open_files =63000 # *****
innodb_page_cleaners =4
innodb_sort_buffer_size =64M
innodb_print_all_deadlocks =1 #
innodb_rollback_on_timeout =ON
innodb_deadlock_detect =ON
推荐阅读:
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html4.复制
relay_log =/opt/log/mysql/blog/relay
relay_log_index =/opt/log/mysql/blog/relay.index
max_relay_log_size =500M
relay_log_recovery =ON
rpl_semi_sync_master_enabled =ON
rpl_semi_sync_master_timeout =1000
rpl_semi_sync_master_trace_level =32
rpl_semi_sync_master_wait_for_slave_count =1
rpl_semi_sync_master_wait_no_slave =ON
rpl_semi_sync_master_wait_point =AFTER_SYNC
rpl_semi_sync_slave_enabled =ON
rpl_semi_sync_slave_trace_level =32
binlog_group_commit_sync_delay =1
binlog_group_commit_sync_no_delay_count =1000
gtid_mode =ON
enforce_gtid_consistency =ON
skip-slave-start =1
#read_only =ON
#super_read_only =ON
log_slave_updates =ON
server_id =2330602
report_host =xxxx
report_port =3306
slave_parallel_type =LOGICAL_CLOCK
slave_parallel_workers =4
master_info_repository =TABLE
relay_log_info_repository =TABLE5.其它
客户端配置：
[mysql]
no-auto-rehash
prompt=db106 [\d]>五.开发规范
1.字段规范
(1)每个表建议在30个字段以内。
(2)需要存储emoji字符的，则选择utf8mb4字符集。
(3)机密数据，加密后存储。
(4)整型数据，默认加上UNSIGNED。
(5)存储IPV4地址建议用bigINT UNSIGNE，查询时再利用INET_ATON()、INET_NTOA()函数转换。
(6)如果遇到BLOB、TEXT大字段单独存储表或者附件形式存储。如果可以的话,建议使用ES存储!
(7)选择尽可能小的数据类型，用于节省磁盘和内存空间。
(8)存储浮点数，可以放大倍数存储。
(9)每个表必须有主键，INT/BIGINT并且自增做为主键，分布式架构使用sequence序列生成器保存。
(10)每个列使用not null，或增加默认值。2.SQL语句规范
(1)去掉不必要的括号
如： ((a AND b) AND c OR (((a AND b) AND (c AND d))))
修改成 (a AND b AND c) OR (a AND b AND c AND d)
(3)去掉重叠条件
如： (a<b AND b=c) AND a=5
修改成 b>5 AND b=c AND a=5
如： (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
修改成 B=5 OR B=6
(3)避免使用not in、not exists 、<>、like %%
(4)多表连接，小表驱动大表
(5)减少临时表应用，优化order by 、group by、union、distinct、join等
(6)减少语句查询范围，精确查询条件
(7)多条件，符合联合索引最左原则
(8)查询条件减少使用函数、拼接字符等条件、条件隐式转换
(9)union all 替代 union
(10)减少having子句使用
(11)如非必须不使用for update语句
(12)update和delete，开启安全更新参数
(13)减少inset ... select语句应用
(14)使用load替代insert录入大数据
(15)导入大量数据时，可以禁用索引、增大缓冲区、增大redo文件和buffer、关闭autocommit、RC级别可以提高效率
(16)优化limit，最好业务逻辑中先获取主键ID，再基于ID进行查询
limit 5000000,10 limit 10 , 200
(17)DDL执行前要审核
(18)多表连接语句执行前要看执行计划六.索引优化
(1)非唯一索引按照“i_字段名称_字段名称[_字段名]”进行命名。
(2)唯一索引按照“u_字段名称_字段名称[_字段名]”进行命名。
(3)索引名称使用小写。
(4)索引中的字段数不超过5个。
(5)唯一键由3个以下字段组成，并且字段都是整形时，使用唯一键作为主键。
(6)没有唯一键或者唯一键不符合5中的条件时，使用自增id作为主键。
(7)唯一键不和主键重复。
(8)索引选择度高的列作为联合索引最左条件
(9)ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面。
(10)单张表的索引数量控制在5个以内，若单张表多个字段在查询需求上都要单独用到索引，需要经过DBA评估。
查询性能问题无法解决的，应从产品设计上进行重构。
(11)使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary。
(12)UPDATE、DELETE语句需要根据WHERE条件添加索引。
(13)对长度大于50的VARCHAR字段建立索引时，按需求恰当的使用前缀索引，或使用其他方法。
(14)下面的表增加一列url_crc32，然后对url_crc32建立索引，减少索引字段的长度，提高效率。
CREATE TABLE all_url(ID INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
url VARCHAR(255) NOT NULL DEFAULT 0,
url_crc32 INT UNSIGNED NOT NULL DEFAULT 0,
index idx_url(url_crc32));
(15)合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。
(16)合理利用覆盖索引，减少回表。
(17)减少冗余索引和使用率较低的索引
mysql> select * from schema_unused_indexes;
mysql> select * from schema_redundant_indexes\G七.锁优化
1.全局锁 Global Read lock
(1)介绍
全局读锁(FTWRL)的加锁方法：
flush tables with read lock.
解锁方法：
unlock tables;
出现场景：
mysqldump --master-data
xtrabackup（8.0之前早期版本）等备份时。
属于类型：
MDL（matedatalock）层面锁
影响情况：
加锁期间，阻塞所有事务写入，阻塞所有已有事务commit。
MDL，等待时间受 lock_wait_timeout=31536000
(2)检测方法
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME = 'wait/lock/metadata/sql/mdl';
mysql> select * from performance_schema.metadata_locks;
mysql> select OBJECT_SCHEMA ,OBJECT_NAME ,LOCK_TYPE,LOCK_DURATION,LOCK_STATUS ,OWNER_THREAD_ID,OWNER_EVENT_ID from performance_schema.metadata_locks;
mysql> show processlist;
mysql> select * from sys.schema_table_lock_waits;
(3).一个经典故障：5.7 xtrabackup/mysqldump备份时数据库出现hang状态，所有查询都不能进行
session1: 模拟一个大的查询或事务
mysql> select id,sleep(100) from city where id<100 for update ;
session2: 模拟备份时的FTWRL
mysql> flush tables with read lock;
-- 此时发现命令被阻塞
session3: 发起查询，发现被阻塞
mysql> select * from world.city where id=1 for update;
结论： 备份时，一定要选择业务不繁忙期间，否则有可能会阻塞正常业务。
案例2：
5.7版本 innobackupex备份全库，进程死了，mysql里就是全库读锁，后边insert 全阻塞了
show processlist ----> select * from performance_schema.metadata_locks; ---> pending ---->granted ----> OWNER_THREAD_ID: 66----> select * from threads \G ----->processlist_Id----> show processlist -----> kill processlist_Id 2.row lock wait
(1)介绍
record lock 、gap、next lock
都是基于索引加锁,与事务隔离级别有关。
(2)行锁监控及分析
# 确认有没有锁等待:
show status like 'innodb_row_lock%'
select * from information_schema.innodb_trx;
# 查询锁等待详细信息
select * from sys.innodb_lock_waits; ----> blocking_pid(锁源的连接线程)
# 通过连接线程找SQL线程
select * from performance_schema.threads;
# 通过SQL线程找到 SQL语句
select * from performance_schema.events_statements_history;
(3)优化方向
1. 优化索引
2. 减少事务的更新范围
3. RC
4. 拆分语句：
例如：
update t1 set num=num+10 where k1 <100; k1 是辅助索引,record lock gap next
改为:
select id from t1 where k1 <100; ---> id: 20,30,50
update t1 set num=num+10 where id in (20,30,50);八.架构优化
高可用架构：
MHA+ProxySQL+GTID
MGR\InnoDB Cluster
PXC
读写分离：
ProxySQL、MySQL-router
NoSQL:
Redis+sentinel,Redis Cluster
MongoDB RS/MongoDB SHARDING Cluster
ES九.安全优化
(1)使用普通nologin用户管理MySQL
(2)合理授权用户、密码复杂度及最小权限、系统表保证只有管理员用户可访问。
(3)删除数据库匿名用户
(4)锁定非活动用户
(5)MySQL尽量不暴露互联网,需要暴露互联网用户需要设置明确白名单、替换MySQL默认端口号、使用ssl连接
(6)优化业务代码，防止SQL注入。