mysql全面优化

mysql优化思路

1硬件层优化

2系统优化

3mysql软件及版本选主

4mysql三层结构及参数优化

5mysql开发规范

6mysql索引优化

7mysql的事务及锁优化

8mysql架构优化

9mysql的安全优化

1硬件优化

1纯硬件服务器

华为 浪潮

cpu

i系列是计算密集型

e系列io类型

io sas

raid卡 建议 raid 10

网卡 单卡单口

2云服务器 ecs rds

3关闭 numa

会把你的内存绑定到cpu上

numa是控制cpu分配内存的控制手段，比如8核cpu 64G内存，每个核心分为8个核心的内存大家就不会争抢资源了，那为什么要关闭numa呢？

对于mysql来讲呢用不上这个机制，myslq有独立的内存管理机制，他怎么分内存，线程如何使用内存是mysql自己独立控制的不希望别人来控制这个内存的使用，完全公平去分配的话，有的时候某一个线程需要的内存大一些会出现阻塞 ，最后关闭numa。

4关闭thp

在centos6之后才加入的一个分配内存的机制，为什么要关闭系统新加入的内存机制THP。

因为应用在申请内存的时候呢，因为mysql不需要他来管理内存机制，mysql有自己的内存管理机制

THP分配的内存可能会比mysql实际需要的多的多，因为mysql内部机制申请内存是16kb，THP分配大页内存机制是1M这个时候就会出现内存碎片，内存碎片就是THP分配给mysql多余的内存，不会进行释放(还是一直在占用资源)，这个时候会产生OOM，内存溢出，会造成资源的浪费，操作系统没有内存可以使用了，就会kill掉mysql这个线程， 生产建议关闭(推荐)。

2系统优化

内核优化 /etc/sysctl.conf

更改文件句柄和进程数

vm.swappiness = 5（也可以设置为0） #物理内存剩余百分之5启动交换分区swap,推荐是不要设置0防止OOM

vm.dirty_ratio = 20 ## 脏页比例达到百分之多少进行刷新，脏页文件系统缓存中一些被修改完的数据需要落入到磁盘中的数据

vm.dirty_background_ratio = 10 ##脏页异步刷新，上面是同步刷新

##vm dirty参数要根据自己硬盘的性能调整参数，磁盘性能高就调低，磁盘性能差就调高，上面是ssd磁盘的配置，性能差就让他刷新的频率不要那么频繁

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

* soft nofile 204800

* hard nofile 204800

* soft nproc 204800

* hard nproc 204800

查看是否生效

ulimit -a

内存显示95%

vm.dirty_ratio = 20 调整刷脏页

数据库 ckpt

innodb flush method =o_direct

mysql建议分区独立

不使用lvm

io调度

sas ：deadline

ssd noop

cat/sys/block/sda/queue/scheduler

top补充

关注第一项是cpu

按一下1就可以看到各个cpu状态

关注点

1us

2sy 内核

3id 空闲的

us很高是正常现象

任何程序需要往磁盘写什么东西

程序要跟内核交互 内核刷磁盘

并发太多了会导致us高sy高

归根揭底找它干活的太多了

wa状态

把内存数据刷磁盘的时候等待的状态

等待也会耗费cpu

mysql占用cpu非常高
ps -ef |grep mysqld 看一下mysql的进程号
top -Hp  进程号  看mysql的那个线程使用cpu过高
去mysql 
select  * from performance_schema.threads;
去找os的线程
看是什么线程
内存太小了

mysql锁相关

latch 栓锁

并发应用的时候保护内存的数据结构对象针对线程

show engine innodb mutex

需要看源码

mdl锁类型

ftwrl： flush tables with read lock

解锁方法 unlock tables

排查方法

select * from performance_schema.metadata_locks\G

看是什么类型的锁，是对什么对象 看是不是持有的

5.6是需要打开的

5.7可以看

show processlist;

select * from sys.schema_table_lock_waits;

可以配合找到

select * from performance_schema.threads;

找到后台线程

监控

show status like 'innodb_row——lock%'

优化方向

1索引优化

2减少事务的更新范围

3rc级别

4拆分语句

死锁

show engine innodb status \G

innodb_print_all_deadlocks =1 记录日志

rc级别有外键的话可能会有间隙

rr级别

间隙锁，索引扫描是区间

如果大于5

会扫描第一个 先找到5 往下遍历 记录加锁

5前面的间隙不会加锁，直到最后一个不满足条件为止

加锁规则

1加锁的基本单位是 next-key lock 并且 next-key lock 是左开右闭的区间，直到最后一个不满足条件为止
2 查找过程中访问到的索引才会加锁
3 索引上的等值查询，给唯一索引加锁的时候next-key lock 会退化行锁
id列
4 索引的等值查询，向右遍历向又扫描最后一个值不满足等值条件的时候 next-key lock 退化为间隙
a=5
5555，10]12
10之后可以插入
插入6就不行
8019之前的bug 唯一索引上的查询范围会访问到不满足条件的第一个值为止

如果找a=5

会找到5,10的范围