mysql性能优化、慢查询分析、优化索引和配置

.每项的基本思路步骤

1.性能瓶颈定位:show命令、慢查询日志、explain分析查询、profiling分析查询、

2.索引及查询优化

3.配置优化

.mysql是常见的两个瓶颈是cpui/o的瓶颈,cup在内存和磁盘上读取数据,磁盘i/o发生在装入数据远大于内存容量的时候,当查询量过大的时候瓶颈会暴露在网络中,可以通过命令查看系统性能:mpstatiostatsarvmstat

wKioL1lDQyuDNtLZAAA7zdTALxI693.png 

使用另外两个查看

wKiom1lDQ1PCnMZtAABCidFhTLI504.png 

优化数据库通常有三种:使用索引、explain分析查询、调整mysql内部配置

1.查询和索引优化

优化mysql需要对数据库分析,有慢查询、explainprofiling分析、show命令查看系统状态和系统变量。

show命令:

使用variables查看数据库配置信息

wKiom1lDQ2eQYvpQAAADokWYWVM778.png 

查看数据库运行的各种状态值

wKioL1lDQ3OQtLvMAAAXksYooV0923.png 

在命令行中使用mysqladmin查看系统变量及状态信息

wKiom1lDQ4riR2UMAAAF6DJfUbM088.png 

wKioL1lDRK2gMoi2AAApSGbSi9c620.png 在数据库中使用help  show可以查看所有参数参考

慢查询日志

开启慢查询日志:在my.cnf配置文件中添加3个参数,然后重启mysql数据库服务

wKioL1lDRPzQiz9QAABAvtHG3Ag762.png重启服务并且查看是否存在目录

wKiom1lDRMfxIvkYAAAtM9urNt0525.png 

也可以通过在数据库中设置

set global slow_query_log=on;    set long_query_time=时间;   set global  slow_query_log_file="目录"

查看慢查询设置信息

wKioL1lDRSfwt0CwAAAmnEoImwA289.png 

为了测试可以建个脚本执行创建库、表建多少行,次测试建了20000

wKiom1lDRXvBsQXHAAAUQiGK-Oo505.png 

脚本内容

wKioL1lDRYfSybA6AACA_gY_Yqs420.png 

执行一条命令并且查看慢查询日志

wKiom1lDRZXjWTu4AAAF8kn6lUU021.png 

在日志文件中查看 如果在日志中查询不到在/etc/my.cnf中把time时间改小点

wKioL1lDRZ7wrmO_AABwzRMMn8w484.png 

explain分析查询

使用explain关键字是可以模拟优化器执行sql查询语句

wKiom1lDRajjlOZhAAAqlOoMrNc029.png 

EXPLAIN字段:

Table:显示这一行的数据是关于哪张表的

type:这是最重要的字段之一,显示查询使用了何种类型。从最好到最差的连接类型为systemconsteq_regrefrangeindexALL

possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。

key:实际使用的索引。如果为NULL,则没有使用索引。

key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好

ref:显示索引的哪一列被使用了,如果可能的话,是一个常数

rowsMySQL认为必须检索的用来返回请求数据的行数

Extra:关于MYSQL如何解析查询的额外信息 

没有索引不是单个查找而是全表扫描

优化方法:创建索引、然后在进行查找执行explain

wKiom1lDRcSg5x4dAAAqv0xCP7w383.png 

profiling分析查询

通过profiling命令可以更精准的查到sql执行消耗资源的信息(profiling默认是关闭的)

开启profiling并且查看是否开启

wKiom1lDRdHxacICAAAYIPgoWmI587.png 

执行语句测试

wKiom1lDRdvBNTclAAAxUfpoqE4377.png 

使用profile查看sql语句执行的详细信息   status:是profile里的状态,duration:是status状态下的所耗的时间

wKioL1lDRefR92AuAAAoGcZ32WQ482.png 

还可以测试更多的信息

SHOW PROFILE [type [, type] ... ][FOR QUERY n]

type:
       ALL:显示所有的开销信息
      BLOCK IO:显示块IO相关开销
      CPU:显示用户CPU时间、系统CPU时间
      IPC:显示发送和接收相关开销信息
       PAGE FAULTS:显示页面错误相关开销信息
      SWAPS:显示交换次数相关开销的信息

测试完之后,要关闭profiling以免影响数据库的正常使用

wKiom1lDRfWStlu2AAAHjqLQ5OE373.png 

2.配置优化

mysql参数优化对于不同的网站,及其在现量、访问量、帖子数量、网络情况、硬件配置都有关系,通过不断的调试才能达到最佳的效果

连接请求的变量

(1)max_connections:mysql的最大连接数,如果服务器的并发连接较大,必须调高连接数值,否则会宕机,mysql连接数越多会为每个连接提供连接缓冲区,就会开销越多的内存

查看最大连接数

wKiom1lDRf-T8M2kAAANWEXfwvI080.png 

查看当前的响应的连接数

wKioL1lDRhrynuJAAAAOSwLUv9I942.png 

调整最大连接数并测试 /etc/my.cnf中加入参数 max_connections = 连接数

wKioL1lDRi7AUHn9AAAe96kytck437.png 

到第四台连接的时候报错了 连接太多不让连接

(2)back_log:能暂存连接数量,当主要的mysql线程在一个短时间内有很多的连接请求,它会起作用

当观察主机进程列表时发现有很多大量的待连接时 用下面命令查看

wKiom1lDRlnRRFM4AAADs7JCyo8008.png 

查看back_log的连接数

wKiom1lDRmOjkVYjAAAYAysAlLs365.png 

修改back_log连接数 在/etc/my.cnf中添加参数

wKiom1lDRm3xbeawAAACj_9pvIg276.png 

然后重启服务

(3)wait_timeoutinteractive_time

wait_timeout:mysql在关闭一个非交互的连接之前所要等待的秒数

interactive_time:是mysql关闭一个交互的连接之前所要等待的秒数,如果超过了默认值的秒数就会自动断开,默认值为28800=8个小时,可调为7200

wait_timeout:如果设置太小,连接关闭的很快,从而时一些持久的连接不起作用

       如果设置太大,容易造成连接打开时间过长,从而造成too many  connections错误     一般建议wait_timeout尽可能降低

查看wait_timeoutinteractive_timeout

wKioL1lDRnnSVVptAAAgFXU0Dyg001.png 

修改/etc/my.cnf文件,在[mysqld]下面添加

wait_timeout = 秒数

interactive-timeout = 秒数

(4)key-buff_size

key_buff_size:是指定索引缓冲区的大,决定索引的处理速度,通过查看key_read_requestskey_reads可以知道key_buffer_size设置是否合理

查看状态值

wKiom1lDRomw4H87AAAcLnAwPtw547.png 

它们俩的比例最好是key_reads/key_buffer_size  1/100  1/1000

一共有6个索引读取请求,有3个请求在内存中没有找到直接从硬盘中读取索引

*key_buffer_size只对mylsam引擎表起作用   即使不使用mylsam表也能使用内部临时磁盘表这个表是mylsam引擎

wKioL1lDRpaCIdrwAAAQXGuNv5s033.png 

调整key_buffer_size/etc/my.cnf[mysqld]下添加参数key_buffer_size=多少MB

重启mysqld,就能生效

5query_cache_size简称QC

使用查询缓冲,mysql将查询结果存放在缓冲区中,对于同样的select语句,将直接从缓冲区读取结果。通过查看状态值qcache%,可以知道query_cache_size设置是否合理

wKioL1lDRqDzfA4ZAAAYDOjjmtw556.png 

qcache_free_blocks:缓存中相邻内存块的个数,如果多就证明碎片较多,通过flush query cache对缓存中的碎片进行清理,从而得到空闲的块

qcache_free_memoryquery  cache 中目前剩余的内存大小

qcache_hits表示有多少次命中缓存

qcache_inserts示多少次未命中然后插入

qcache_lowmem_prunes多少条query 因为内存不足而被清除出query cache

qcache_not_cached:不适合进行缓存的查询的数量

qcache_queries_in_cache当前query  cache cache query 数量

qcache_total_blocks当前query  cache 中的block 数量

查看query_cache的配置

wKiom1lDRqmAVUExAAAW-cNpUd8638.png 

query_cache_limit:超过此大小的查询将不缓存

query_cache_min_res_unit:缓存块的最小大小 

query_cache_size:查询缓存大小

query_cache_type:缓存类型

wKioL1lDRsKD-HNEAAByZrg7njk949.png 

修改/etc/my.cnf添加query_cache_size =多少M    query_cache_type=1(开启)

重启mysqld服务   查看是否启动成功

wKiom1lDRs7xHJwOAAAhSIIBGKA988.png