您现在的位置是：首页 > 数据库 > MySQLMySQL

性能优化

第十三双眼睛2020-02-03【MySQL】人已围观

简介MySQL性能优化就是通过合理安排资源，调整系统参数使MySQL运行更快，更节省资源，MySQL性能优化包括查询速度优化，数据库结构优化，MySQL服务器优化。

优化简介
优化MySQL数据库使数据库管理员和开发人员必不可少的技能。

自然主键与代理主键
自然主键是指事物属性中的自然唯一标识，比如订单表中的order_id字段
代理主键是指与业务无关的，无意义的数字序列值，比如订单表中自动生成的id字段
在进行表设计时，优先推荐代理主键，不推荐自然主键

字段类型优先级
数字类型
日期类型与二进制类型
字符串类型
如：存储手机号时，用数字类型比字符串类型好，查询效率高，而且占用空间少

数据类型选择
varchar：使用最小的符合要求的长度，不要上来就varchar(255)，这是不负责任的做法
varchar 255长度以下，会用额外的一个字节用来保存字符串的实际长度，255以上会用2个额外的自己来保存字符串的实际长度
varchar(5)与varchar(200)在内存中占用的空间是不一样的。
varchar类型进行变更长度时，会进行锁表
varchar适合用来存储长度波动大的数据，比如博客文章。
varchar使用用在字符串很少被更新的场景，如果频繁更新，会重新计算
varchar适合保存多字节的字符
char
char最大长度255
char类型会自动删除末尾的空格
char类型的检索效率比varchar高
char适合存储长度波动不大的数据，如MD5摘要
char适合存储短字符串
char适合存储经常变化的字符串

日期时间类型datetime
固定占用8个字节来保存数据
与时区无关，可以保存到毫秒
不要使用字符串存储日期类型，因为无法使用mysql的日期函数来操作，而且占用的空间更大

timestamp类型
使用4个字节来保存数据
只能存储时间范围为1970-01-01 到2038-01-19 的时间，
并且只精确到了秒，存储的是整型时间，依赖时区。

执行计划
id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。
id=1的表是驱动表

select type 查询类型,说明查询的种类
1.simple 简单查询。查询不包含子查询和union
2.primary：复杂查询中最外层的 select
3.derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
4.union：在 union 中的第二个和随后的 select
5.union result：从 union 临时表检索结果的 select
6.subquery：包含在 select 中的子查询（不在 from 子句中）

table 这一列表示 explain 的一行正在访问哪个表。
partitions 说明查询作用在哪个分区表上

type 这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行。
执行效率排序
1.system
2.const * mysql能对查询的某部分进行优化并将其转化成一个常量。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。
explain select * from (select * from film where id = 1) tmp;
3.eq_ref * primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。
explain select * from film_actor left join film on film_actor.film_id = film.id;
4.ref * 相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
explain select * from film where name = "film1";
5.fulltext
6.ref_or_null * 类似ref，但是可以搜索值为NULL的行。
explain select * from film where name = "film1" or name is null;
7.index_merge
8.unique_subquery
9.index_subquery
10.range * 范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
explain select * from actor where id > 1;
11.index * 和ALL一样，不同就是mysql只需扫描索引树，这通常比ALL快一些。
explain select count(*) from film;
12.ALL * 即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了
explain select * from actor;

possible_keys 这一列显示查询可能使用哪些索引来查找。
key 这一列显示mysql实际采用哪个索引来优化对该表的访问。
key_len 这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
ref 这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film.id）

row 这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
filtered 列,是一个百分比的值,代表 (rows * filtered) / 100 ,这个结果将于前表产生交互

Extra 这一列展示的是额外信息.
distinct: 一旦mysql找到了与行相联合匹配的行，就不再搜索了
Using index:这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。是性能高的表现。
using index也叫索引覆盖
Using where:mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。
using temporary:mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
using filesort:采用文件扫描对结果进行计算排序,效率很差
对于排序,只有select 字段与order by 字段都被索引覆盖是才允许使用Using Index

设置mysql应用参数的三种方式
set session 代表在当前会话(窗口/连接)才有效,关闭会话后自动失效
set Global在Mysql服务器运行过程中会一直生效，直到mysql关闭
在配置文件中进行设置

数据库连接参数
max_connections 代表数据库同时允许的最大允许连接数
连接有两种常见状态:sleep / query
sleep 代表连接处于闲置状态
query 代表连接正处于处理任务的状态
sleep + query 连接的总量不能超过max_connections的设置值
否则会出现经典错误:"ERROR 1040：Too many connetcions"

show status like 'Threads%';
Threads_connected 代表当前已经有多少连接(Sleep+Query)
Threads_created 代表历史总共创建过多少个数据库连接
Threads_running 代表有几个连接正处于"工作"状态,也是目前的并发数
Threads_cached 共缓存过多少连接.如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)。
set global thread_cache_size=80;
MySQL历史运行过程中最大连接数的数量及时点
show status like 'Max_used_connections%';

back_log 设置保存多少数据库请求到堆栈(缓冲区)中.
也就是说，如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。将会报：
unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时.

wait_timeout和interactive_timeout
这两个参数都是至超过一段时间后,数据库连接自动关闭(默认28800秒,即8小时)
interactive_timeout针对交互式连接，wait_timeout针对非交互式连接。
说得直白一点，通过mysql客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接。
查看当前数据库连接详细状况
show processlist;

查询缓存参数设置
show status like 'Qcache%';
Qcache_free_memory:Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察当前系统中的Query Cache内存大小是否足够，是需要增多还是过多了。
Qcache_lowmen_prunes：多少条Query因为内存不足而被清除出Query Cache，通过Qcache_lowmem_prunes和Qcache_free_memory 相互结合，能够更清楚的了解到我们系统中Query Cache的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query被换出。这个数字最好是长时间来看，如果这个数字在不断增长，就表示可能碎片化非常严重，或者内存很少。
Qcache_total_blocks：当前Query Cache中block的数量
Qcache_free_blocks：缓存中相邻内存块的个数。如果该值显示过大，则说明Query Cache中的内存碎片较多了。
查询缓存碎片率：Qcache_free_block/Qcache_total_block*100%
如果查询缓存碎片率超过20%，可以用flush query cache整理缓存碎片
Block默认是 4KB ，设置值大对大数据查询有好处，但是如果你查询的都是小数据查询，就容易造成内存碎片和浪费。
Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询能缓存的效果。数字越大缓存效果越理想。
Qcache_inserts：表示多少次未命中而插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert带查询缓存中。这样的情况次数越多，表示查询缓存应用到的比较少，效果也就不理想。
Qcache_queries_in_cache:当前缓存中缓存的查询数量。
Qcache_not_cached 未进入查询缓存的select个数
query_cache_size：查询缓存大小（注：QC存储的单位最小是1024byte，所以如果你设定的一个不是1024的倍数的值。这个值会被四舍五入到最接近当前值的等于1024的倍数的值。）
query_cache_limit：超出此大小的查询将不被缓存
1mb,超过1mb的结果将不缓存
query_cache_type：缓存类型，决定缓存什么样子的查询，注意这个值不能随便设置必须设置为数字，可选值以及说明如下：
0：OFF 相当于禁用了
1：ON 将缓存所有结果，除非你的select语句使用了SQL_NO_CACHE禁用了查询缓存
2：DENAND 则只缓存select语句中通过SQL_CACHE指定需要缓存的查询。
5.7中默认禁用QC,需要在my.ini中进行开启
query_cache_min_res_unit：缓存块的最小大小，query_cache_min_res_unit的配置是一柄双刃剑，默认是 4KB ，设置值大对大数据查询有好处，但是如果你查询的都是小数据查询，就容易造成内存碎片和浪费。
每个查询占用缓存的平均值=（query_cache_size - Qcache_free_memory）/ Qcache_queries_in_cache
查询缓存利用率：（query_cache_size-Qcache_free_memory)/query_cache_size*100%
查询缓存利用率在25%以下的话说明query_cache_size设置过大，可以适当减小：查询缓存利用率在80%以上而且Qcache_lowmem_prunes>50
的话说明query_cache_size可能有点小，要不就是碎片太多
查询缓存命中率：Qcache_hits/(Qcache_hits+Qcache_inserts)*100%
sort_buffer_size
每个需要排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY 或 GROUP BY操作
sort_buffer_size是一个connection级的参数，在每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存。
sort_buffer_size：并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统的内存资源。例如：500个连接将会消耗500*sort_buffer_size(2M)=1G

InnoDb参数设置
1. innodb_buffer_pool_size
show GLOBAL VARIABLES like 'innodb_buffer_pool_size'
Innodb已使用的缓存"页Page"数量
show global status like 'Innodb_buffer_pool_pages_data';
Innodb全部缓存页数量
show global status like 'Innodb_buffer_pool_pages_total';
Innodb每页的长度
show global status like 'Innodb_page_size';
页面使用率
result = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
val > 95% 则考虑增大 innodb_buffer_pool_size，建议使用物理内存的75%
val < 95% 则考虑减小 innodb_buffer_pool_size，建议设置为：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

innodb_flush_log_at_trx_commit
#在事务控制中,存在"事务区"来保证事务完整性,在事务提交以后,这些事务区的数据会写入到硬盘上,同时事务操作日志(log)也需要向硬盘中写入.这个参数就是用来控制何时写日志数据的.
0：log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作。
1：每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去，该模式为系统默认。
2：每次事务提交时MySQL都会把log buffer的数据写入log file，但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。
三者比较
当设置为0，该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
当设置为1，该模式是最安全的，但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。。
当设置为2，该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。
实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要两秒，设置为0时只需要一秒，设置为1时，则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅度提高速度。

innodb_doublewrite 双写操作
同一份数据写入两次,保证数据存在一个副本,预防数据因为介质问题产生丢失
show global VARIABLES like 'innodb_doublewrite';
innodb_file_per_table=1
设置独立表空间文件 xxx.ibd

innodb_thread_concurrency
设置innodb线程的并发数，默认值为0表示不被限制，若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍。
show global VARIABLES like 'innodb_thread_concurrency';

Centos7参数调优
系统允许同时发起的TCP连接数。在许多的主流操作系统上这个值都默认是128。
net.core.somaxconn=65535
每个网络端口接收数据包的速率比内核处理这些包的速率快时，允许送到队列的数据包的最大数目
net.core.netdev_max_backlog=65536
系统允许的半连接(SYN)同步包上限
打个简单的比方：
某某发布公告要邀请四海之内若干客人到场参加酒席。客人参加酒席分为两个步骤：

1、到大厅；
2、找到座位(吃东西，比如糖果、饭菜、酒等)。
tcp_max_syn_backlog用于指定酒席现场面积允许容纳多少人进来；
somaxconn用于指定有多少个座位。
显然tcp_max_syn_backlog>=somaxconn。
如果要前来的客人数量超过tcp_max_syn_backlog，那么多出来的人虽然会跟主任见面握手，但是要在门外等候；
如果到大厅的客人数量大于somaxconn，那么多出来的客人就会没有位置坐(必须坐下才能吃东西)，只能等待有人吃完有空位了才能吃东西。
什么是半连接: 未完成三次握手的连接就是半连接,已完成的叫做完全连接
三次握手：
1、第一次握手：客户端发送syn包(syn=j)到服务器，并进入SYN_SEND状态，等待服务器确认；
2、第二次握手：服务器收到syn包，必须确认客户的SYN（ack=j+1），同时也发送一个SYN包（syn=k），即SYN+ACK包，此时服务器进入SYN_RECV状态；
3、第三次握手：客户端收到服务器的SYN＋ACK包，向服务器发送确认包ACK(ack=k+1)，此包发送完毕，客户端和服务器进入ESTABLISHED状态，完成三次握手。
net.ipv4.tcp_max_syn_backlog=65536
这个参数是用来设置保持在FIN_WAIT_2状态的时间。tcp4次挥手，正常的处理流程就是在FIN_WAIT_2情况下接收到FIN进入到TIME_WAIT的情况，tcp_fin_timeout参数对处于TIME_WAIT状态的时间没有任何影响。但是如果这个参数设的比较小，会缩短从FIN_WAIT_2到TIME_WAIT的时间，从而使连接更早地进入TIME_WAIT状态。状态开始的早，等待相同的时间，结束的也早，客观上也加速了TIME_WAIT状态套接字的清理速度。
net.ipv4.tcp_fin_timeout=10
TIME-WAIT套接字是否允许重用于新的TCP连接,了解网络建议开启=1,提高连接速度,不熟悉建议关闭=0
net.ipv4.tcp_tw_reuse=0
表示开启TCP连接中TIME-WAIT sockets的快速回收，默认为0，表示关闭。
net.ipv4.tcp_tw_recycle=0
发送与接收数据的缓存值
net.core.wmem_default=262144
net.core.wmem_max=524288
net.core.rmem_default=262144
net.core.rmem_max=524288
长连接的心跳包机制
设置心跳包开始时机为,发送数据包后半小时开始心跳检查
net.ipv4.tcp_keepalive_time=1800
心跳包发送间隔时间10秒
net.ipv4.tcp_keepalive_intvl=10
超过3次没有应答,则认为连接无效,将其丢弃
net.ipv4.tcp_keepalive_probes=3
=============================
Linux进程可以分配的单独共享内存的最大值,大于 INNODB_POOL_SIZE与QUERY_CACHE及其他MySQL占用内存总和
#建议总内存的60%左右
kernel.shmmax=2147483648
swappiness的值越大，表示越积极使用swap分区，越小表示越积极使用物理内存。默认值swappiness=60
vm.swappiness=0

Tags：MySQL 数据库优化

很赞哦！ ()

上一篇：MySQL日志

下一篇：MySQL Replication