一.硬件层面优化1 硬件选配2.关闭NUMA3 阵列卡配置建议4.关闭THP5.网卡绑定6.存储多路径二.系统层面优化1.内核优化-更改文件句柄和进程数2.防火墙3.文件系统优化4.不使用LVM 5.io调度三. 数据库版本选择四.数据库三层结构及核心参数优化1.连接层2.Server层3.存储引擎层4.复制5.其它五.开发规范1.字段规范2.SQL语句规范六.索引优化七.锁优化1.全局锁 Global Read lock2.row lock wait八.架构优化九.安全优化
一.硬件层面优化
1 硬件选配
DELL、HP、IBM、华为、浪潮。 CPU：I系列（适合CPU密集型，适合在线分析的系统）、E系列(适合IO密集型，核心数偏多，并发性较高) 内存：带有ECC功能的内存。 IO : SAS 、 pci-e SSD 、 Nvme flash（非易失性内存快速） raid卡：Raid10 网卡： 单卡单口 bonding + 交换机堆叠 云服务器： ECS 、RDS 、TDSQL、PolarxDB
2.关闭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(推荐) SSD或者PCI-E或者Flash 强制回写（Force WriteBack） BBU 电池 ： 如果没电会有较大性能影响、定期充放电。 如果有UPS、多路电源、发电机。可以关闭。 关闭预读: 有可能的话开启Cache(如果UPS、多路电源、发电机。)
4.关闭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]
5.网卡绑定
bonding技术，业务数据库服务器都要配置bonding继续。建议是主备模式。交换机一定要堆叠。
6.存储多路径
使用独立存储设备的话，需要配置多路径。 linux 自带 : multipath 厂商提供 :
二.系统层面优化
1.内核优化-更改文件句柄和进程数
(1)/etc/sysctl.conf vm.swappiness = 5 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 63000
2.防火墙
禁用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 2
4.不使用LVM
因为LVM会需要创建一个软件层，并不会直接使用磁盘设备，会造成一定性能的影响。
5.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= 32M #*****
2.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 =ROW
3.存储引擎层
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
4.复制
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 =TABLE
5.其它
客户端配置： [mysql] no-auto-rehash
五.开发规范
1.字段规范
(1)每个表建议在30个字段以内。 (2)需要存储emoji字符的，则选择utf8mb4字符集。 (3)机密数据，加密后存储。 (4)整型数据，默认加上UNSIGNED。 (5)存储IPV4地址建议用bigINT UNSIGNE，查询时再利用INET_ATON()、INET_NTOA()函数转换。 (6)如果遇到BLOB、TEXT大字段单独存储表或者附件形式存储。 (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注入。
扫一扫
1306
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
