mysql优化

Mysql优化思路：

优化思路：

1、硬盘优化

2、磁盘IO优化

3、操作系统优化

4、数据库设计与规划

5、my.cnf参数优化

6、mysql查询优化

7、mysql存储引擎

1、硬盘优化：

CPU：64位、高主频、高缓存、高并行处理能力

内存：大内存、主频高、尽量不要使用SWAP

硬盘：15000转或更大的转速、使用RAID10、RAID5磁盘阵列或SSD固态硬盘

网卡：服务器的网卡建议是10G网卡，使用网卡 bond 技术

Mysql服务器尽可能和使用它的web服务器在同一局域网内，避免服务器策略等不必要的开销

注：bond就是将2个或多个物理网卡绑成一个虚拟网卡，实现本地网卡的冗余，带宽扩容和负载均衡

2、磁盘IO规划，IO技术：

raid技术：raid10 或 raid5

建议是15000转或更大转，或直接使用SSD固态磁盘

swap分区：最好使用raid0 或 SSD

磁盘分区：将数据目录放到一个磁盘或分区，存储数据的硬盘或分区和系统所在的硬盘分开。

把binlog日志放到单独的磁盘分区上

3、操作系统优化：

操作系统优化-内核、tcp连接数量：

3.1、修改/etc/sysctl.conf 文件：

将系统对本地端口范围限制设置为 1024~65000之间

注：本地端口范围最小值必须大于或等于1024，端口范围最大值是65535不能超过这个值

在/etc/sysctl.conf文件里添加该行，保存退出

net.ipv4.ip_local_port_range = 1024 65000

执行sysctl命令使修改生效

sysctl -p

3.2、设置并发数：

3.2.1、查看当前服务器的并发连接数：

查看使用了多少连接数：

cat /proc/sys/net/ipv4/netfilter/ip_conntrack_count     centos7以前的版本

cat /proc/sys/net/netfilter/nf_conntrack_count          centos7新版本用这个查询

查看总数多少：

cat /proc/sys/net/ipv4/ip_conntrack_max        centos7以前的版本

cat /proc/sys/net/netfilter/nf_conntrack_max   centos7新版本用这个查询

注：确保模块nf_conntrack_max被加载

3.2.2、设置并发数：

临时设置增加并发数：

echo 524288 > /proc/sys/net/ipv4/ip_conntrack_max        centos7以前的版本

echo 524288 > /proc/sys/net/netfilter/nf_conntrack_max    centos7新版本用这个查询

永久设置增加并发数：

在/etc/sysctl.conf 文件中加入：👇

net.ipv4.ip_conntract_max =102400         centos7以前的版本

net.netfilter.nf_conntrack_max = 102400   centos7新版本用这个

执行systcl -p使修改生效

3.3、在服务器修改或增加配置：

编辑文件/etc/sysctl.conf文件，加入或修改以下内容

net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 30

使参数生效：sysctl -p

参数详解：

• net.ipv4.tcp_syncookies = 1 表示开启SYN Cookies。当出现SYN等待队列溢出时，启用cookies来处理，可防范少量SYN攻击，默认为0，表示关闭；

• net.ipv4.tcp_tw_reuse = 1 表示开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接，默认为0，表示关闭；

• net.ipv4.tcp_tw_recycle = 1 表示开启TCP连接中TIME-WAIT sockets的快速回收，默认为0，表示关闭。

• net.ipv4.tcp_fin_timeout 修改系默认的 TIMEOUT 时间

3.4、修改linux系统打开文件限制：

查看linux系统用户最大打开文件限制：

ulimit -n

修改打开文件限制：

vi /etc/security/limits.conf 加入以下参数👇

mysql soft nofile 102400
mysql hard nofile 102400
mysql soft nproc 102400
mysql hard nproc 102400

参数详解：

• mysql指定要修改哪个用户的打开文件数限制，用”*”号表示修改所有用户的限制，

• soft或hard指定要修改软限制还是硬限制

• 102400指定要修改的新限制值，即最大打开文件数(软限制要小于或等于硬限制)

3.4.1、修改/etc/pam.d/login：

vi /etc/pam.d/login 加入以下参数

session    required     /usr/lib64/securit/pam_limits.so

这是告诉linux在用户完成系统登录后，调用pam_limits.so模块来设置系统对该用户可使用的各种资源数量的最大限制，(包括用户可打开的最大文件数限制)，pam_limits.so模块从/etc/security/limits.conf文件中读取配置来设置这些限制值。

3.4.2、Linux系统级别：

查看linux系统对同时打开文件数的硬限制：

sysctl -a | grep file-max

fs.file-max = 181056         返回参数

表明这台linux系统最多允许同时打开(包含所有用户打开文件数总和)181056个文件，是linux系统的硬限制，所有用户级的打开文件数限制都不会超过这个数值。

系统级硬限制是linux系统在启动时根据系统硬件资源状况计算出来的最佳同时打开文件数限制

修改系统硬限制 file-max：

vi /etc/sysctl.conf

fs.file-max = 1000000  把限制数改成1000000

sysctl -p 使其生效

3.5、禁用不必要启动的服务：

3.5.1、文件系统调优：

推荐给数据库一个单独的文件系统XFS，效率高、可靠

在挂载分区时考虑启动noatime选项。noatime不记录访问时间

vi /etc/fstab

UUID=10b60c57-a2a1-441a-abd8-c79d7ad2389f /data      xfs    defaults,noatime        0 0

mount -o remount /data/ 使其生效

这样以后系统在读此分区下的文件时，将不会修改atime属性

3.5.2、最小化原则：

1、安装系统最小化

2、开启程序服务最小化原则

3、登录最小化原则

4、权限最小化

3.5.3、纵向拆解、横向拆解：

• 纵向拆解：转机专用

纵向拆解内容分成多个内容，数据库服务器转机专用，避免额外的服务可能导致的性能下降和不稳定性

• 横向拆解：

主从同步、负载均衡、高可用性集群，列如：当单个mysql数据库无法满足日益增加的需求时，

考虑在这个数据库逻辑层面增加多台服务器，以达到稳定、高效的效果。

4、数据库设计与规划：

1、尽量把字段设置为 NOT NULL，避免null值出现，或设置默认数字0代替null，因为这样在进行查询时数据库不会去比较 NULL 值。

2、创建表时、可以将表中字段的宽厚设得尽可能小，如：

定义什么类型的编码设置多少宽厚，定义邮政编码：就应该设置成CHAR(6)就可以很好的匹配不会浪费不必要的空间，应该尽量使用INT或MEDIUMINT而不是BIGIN来定义整型字段。

3、使用枚举或整数代替字符串类型，列如："省份"或"性别"可以将它定义为ENUM类型，

因为在mysql中ENUM类型被当作数值型数据来处理，数值型数据处理速度比文本类型快得多。

4、尽量使用 TIMESTAMP而非DATETIME

5、单表不要有太多字段，建议在20以内

5、my.cnf参数优化：

优化原则：40%资源给os，60%-70%给mysql内存和cpu。

5.1、对查询进行缓存：

查询流程：PHP发出请求>数据库收到指令对查询语句进行分析>确定如何查询>从磁盘中加载信息>返回结果。

如果反复查询就反复执行这些操作。

Mysql有一个特性就是查询缓存，它可以将查询的结果保存在内存缓存区中，在有同样的select查询语句，将直接从缓冲区读取结果。缓冲区默认是禁止的

注：两条sql的查询语句必须完全一致

5.2、启动查询缓存：

vi /etc/my.cnf 对my.cnf文件进行编辑

[mysqld] #在此字段中添加 
query_cache_size = 256M  #设置缓存为256M
query_cache_type=1    #1是开启mysql查询缓存，0是不缓存
注：通常设置为32-512Mb。设置完之后最好跟踪一段时间，查看是否运行良好。

systemctl restart mysqld 重启mysql服务

5.3、查看缓存：

mysql> show status like 'qcache%';

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1         |
| Qcache_free_memory      | 268417912 |
| Qcache_hits             | 0         |
| Qcache_inserts          | 0         |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 1         |
| Qcache_queries_in_cache | 0         |
| Qcache_total_blocks     | 1         |
+-------------------------+-----------+
8 rows in set (0.01 sec)

参数说明：

1、 Qcache_free_blocks：缓存中相邻内存块的个数。数目大说明可能有碎片。

​ 如果数目比较大，可以执行：

​ mysql> flush query cache; #对缓存中的碎片进行整理，从而得到一个空闲块。

2、 Qcache_free_memory：缓存中的空闲内存大小，通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是减少。

3、 Qcache_hist：表示有多少次命中缓存。每次查询在缓存中命中时就增大。数字越大，缓存效果越理想。

4、 Qcache_inserts：表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。

5、 Qcache_lowmem_prunes： 因内存不足删除缓存次数，缓存出现内存不足并且必须要进行清理,以便为更多查询提供空间的次数。返个数字最好长时间来看；如果返个数字在不断增长，就表示可能碎片非常严重，或者缓存内存很少。

如果Qcache_free_blocks比较大，说明碎片严重。 如果 free_memory 很小，说明缓存不够用了。

6、 Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是SELECT语句或者用了now()之类的函数。

7、 Qcache_queries_in_cache：在当前缓存的查询（和响应）的数量。

8、 Qcache_total_blocks：缓存中块的数量。

5.4、强制限制mysql资源设置：

在mysql中强制一些限制来，确保系统负载不会导致资源耗尽的情况出现。

vi /etc/my.cnf

max_connections=500
wait_timeout=10
max_connect_errors = 100

参数详解：

• max_connections：MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这是建立在服务器能支撑的情况下，因为如果连接数越多， MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。

可执行mysql> show variables like ‘max_connections’;查看mysql的最大连接数设置。

注：数值过小会经常出现ERROR 1040: Too many connections错误

• wait_timeout：指的是MySQL在关闭一个非交互的连接之前所要等待的秒数（空闲时间）。

可执行mysql> show variables like ‘wait_timeout’;查看wait_timeout的值。

• max_connect_errors：是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试连接失败的客户端，以防止暴力破解密码的情况, 当超过指定次数，MYSQL服务器将禁止该主机的连接请求。max_connect_errors的值与性能并无太大关系。

可执行mysql> show variables like ‘max_connect_errors’;查看该参数的设置值。

如果一个主机在连接到服务器时有问题，并重试很多次后放弃，那么这个主机就会被锁定，直到执行：mysql> FLUSH HOSTS;

5.5、表高速缓存：

数据库中的每个表存储在文件中，要读取内容必须先打开表，mysqld对这些文件进行缓存，在/etc/my.cnf中的table_cache指定

mysql> show variables like 'table_open_cache';      #查看太参数的设置值

vi /etc/my.cnf #添加如下内容👇

table_open_cache=512  #最多缓存512个表

systemctl restart mysqld #重启mysql服务

参数含义：

table_cache 的值在 2G 内存以下的机器中的值默认从 256 到 512个。

对于有 1G 内存的机器，推荐值是 128-256。

mysql> show global status like 'open%_tables';

Open_tables   | 33    
Opened_tables | 108  

参数含义：

Open_tables 表示打开表的数量

Opened_tables表示打开过的表数量，如果Opened_tables数量过大，说明配置中 table_open_cache值可能太小.

注：通常在设置table_open_cache参数的时候，在业务的高峰时期，检查open_Tables的值，如果open_Tables的值与table_open_cache的值相等，并且opened_tables的值在不断的增加，这个时候就需要对table_open_cache的值增加了。

5.6、关键字（索引）缓冲区：

key_buffer-size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度

mysql> show variables like 'key_buffer_size';  #查看该参数的值

vi /etc/my.cnf

key_buffer_size=512M   #关键字缓冲区大小

systemctl restart mysqld.service 重启mysql服务

mysql -uroot -p数据库密码 -e "show status like '%key_read%'"

Key_read_requests | 6     
Key_reads         | 3   

参数含义：

Key_reads：代表命中磁盘的请求个数

Key_read_requests：是总数，命中磁盘的读请求数除以读请求总数就是不中比率

6、mysql查询优化：

6.1、查看慢查询日志定义：

mysql> show global variables like ‘%slow_query_log%’;

| slow_query_log      | OFF                             |
| slow_query_log_file | /data/mysql/data/xiao1-slow.log |

mysql> show global variables like ‘%long%’;

long_query_time                                          | 10.000000 |

slow_query_log：off关闭状态 on开启状态

slow_query_log_file：慢查询日志存放地点

long_query_time：设置一个时间值(时间单位是秒)，如果查询时间超过了这个时间值(默认是10秒)

这个查询语句就会被记录到慢查询日志，设置为0的话表示记录所有的查询。

6.2、开启慢查询日志：

vi /etc/my.cnf #编辑my.cnf文件，添加如下内容

slow_query_log = 1                          #开启慢查询日志
slow_query-log-file=/data/mysql/log/slow.log    #这个路径对mysql具有写权限
long_query_time=1                         #查询时间超过1秒的语句会被记录下来
log-queries-not-using-indexes =1              #没有使用索引的查询

注：如果不指定存储路径，慢查询日志默认存储到mysql数据库的数据文件下，如不指定文件名默认为hostname-slow.log

systemctl restart mysqld.service 重启mysql服务

6.3、通过mysql服务器直接定义：

mysql> set global slow_query_log=1; #开启慢查询

mysql> set global long_query_time=0.001; #更改查询超过时间

通过explain来获取查询语句的执行计划，而不是真正执行该语句

mysql> explain select * from 表名 where price='100'\G

注：在mysql数据库操作时

只取自己需要的column，避免使用SELECT *

添加索引（主键索引/唯一索引/普通索引/复合索引）

不做列运算：SELECT id from tablename where age + 1 = 10，任何对列的操作都将导致表扫描，查询时要尽可能将操作移至等号右边

sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库。

OR改写成IN 查询符or可以改写成in

尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

7、mysql存储引擎：

Mylsam存储引擎：mylsam存储引擎的表在数据库中，把一个整表分为三份以表命名的物理文件。分为：

.frm存放表结构定义信息

.MYD存放表的数据

.MYI存放牵引数据

7.1、Mylsam存储引擎的特点：

• **表级锁：**读取时对读到的所有表加锁(在读取表时会阻塞写入，但不会拒绝另外的读)

写入时则对表加排它锁(在写入时阻塞其他用户对数据的读)

表级锁开销小，影响范围大，适合读多写少的表

不支持事务

不支持外键

不支持崩溃后的安全恢复

• **Innodb存储引擎：**也有.frm文件来存放表结构定义相关的元数据，但表数据和索引数据是存放在一起的，每个表单独存放还是所有表存放在一起，完全由用户决定

7.2、Innodb存储引擎特点：

• **行级锁：**锁定的开销要比锁定全表要大，影响范围小，适合写操作比较频繁的数据表。但是全表扫描热然是表级锁定

支持事务，支持四个事务隔离级别

支持外键

支持崩溃后的安全恢复

Mylsam适合select查找较多的表，innodb适合insert、update修改较多的表

7.3、查看默认存储引擎：

mysql> show variables like '%storage_engine%';

7.4、查看支持的存储引擎：

mysql> show engines;

7.5、死锁概念：

• 锁的概念：当客户端操作表（记录）时，为了保证操作的隔离性(多个客户端同时操作不能互相影响)，通过锁来处理

• 读锁：读操作时增加的锁，也叫共享锁，S-lock。特征是阻塞其他客户端的写操作，不阻塞读操作(并发读)

• 写锁：写操作时增加的锁，也叫独占锁或排他锁，X-lock。特征是阻塞其它客户端的读、写操作。

• 锁定粒度(范围)：

行级：提升并发性，锁本身开销大

表级：不利于并发性，锁本身开销小

7.5.1、什么是死锁：

多个进程或线程抢占资源，抢占到了资源可以不放手并申请更多资源，其它进程或线程必须等待该资源被释放，从而陷入僵局

7.5.2、死锁的产生原因：

1、互斥条件：多进程或线程抢一个资源(一个资源只能被一个进程或线程占用)，

2、请求和持有：进程或线程占用当前资源时，不仅可以不释放资源，还可以申请更多资源。而其它资源有可能被其它进程或线程占用。

3、不可抢占条件：进程或线程在已获得的资源未使用完之前不能被抢占，只有资源持有者才有权力释放该资源

4、循环等待条件：若干进程或线程以不同的方式获取资源，而资源未被释放前，其他进程或线程在等待该资源的释放

7.5.3、避免，预防死锁：

• 避免死锁 使用银行家算法：每一个线程进入系统时，必须声明在运行过程中，所需每种资源类型最大数目，数目不能超过系统所拥有每种资源的总量，当线程申请资源时系统必须确定有足够资源分配给该进程，把资源分配给该进程后是否会使系统处于不安全状态，不会则分配 否则等待

• 预防死锁：破坏死锁的必要条件，不能破坏互斥条件，如：要求进程在创建时服从某种协议

7.5.4、临时处理死锁：

杀掉该进程：mysql数据库

mysql> show processlist;  #查看进程

mysql> kill id;       #杀死进程

mysql> select * from information_schema.innodb_trx;    #查看当前事务

mysql> select * from information_schema.innodb_locks;   #查看当前锁定的事务

mysql> select * from information_schema.innodb_lock_waits;   #查看当前等锁的事务

7.5.5、开启死锁到 error_log 中，错误日志在my.cnf配置为：

log-error=/data/mysql/log/mysqld.log     #生成死锁的日志
innodb_print_all_deadlocks = 1           #开启死锁

所需每种资源类型最大数目，数目不能超过系统所拥有每种资源的总量，当线程申请资源时系统必须确定有足够资源分配给该进程，把资源分配给该进程后是否会使系统处于不安全状态，不会则分配 否则等待

• 预防死锁：破坏死锁的必要条件，不能破坏互斥条件，如：要求进程在创建时服从某种协议

7.5.4、临时处理死锁：

杀掉该进程：mysql数据库

mysql> show processlist;  #查看进程

mysql> kill id;       #杀死进程

mysql> select * from information_schema.innodb_trx;    #查看当前事务

mysql> select * from information_schema.innodb_locks;   #查看当前锁定的事务

mysql> select * from information_schema.innodb_lock_waits;   #查看当前等锁的事务

7.5.5、开启死锁到 error_log 中，错误日志在my.cnf配置为：

log-error=/data/mysql/log/mysqld.log     #生成死锁的日志
innodb_print_all_deadlocks = 1           #开启死锁