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

一.每项的基本思路步骤

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

2.索引及查询优化

3.配置优化

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

使用另外两个查看

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

1.查询和索引优化

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

show命令：

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

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

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

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

慢查询日志

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

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

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

查看慢查询设置信息

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

脚本内容

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

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

explain分析查询

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

EXPLAIN字段：

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

type：这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL

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

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

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

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

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

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

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

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

profiling分析查询

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

开启profiling并且查看是否开启

执行语句测试

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

还可以测试更多的信息

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

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

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

2.配置优化

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

连接请求的变量

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

查看最大连接数

查看当前的响应的连接数

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

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

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

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

查看back_log的连接数

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

然后重启服务

(3)wait_timeout和interactive_time

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

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

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

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

查看wait_timeout、interactive_timeout

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

wait_timeout = 秒数

interactive-timeout = 秒数

(4)key-buff_size

key_buff_size：是指定索引缓冲区的大，决定索引的处理速度，通过查看key_read_requests和key_reads可以知道key_buffer_size设置是否合理

查看状态值

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

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

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

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

重启mysqld，就能生效

（5）query_cache_size简称QC

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

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

qcache_free_memory：query  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的配置

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

query_cache_min_res_unit：缓存块的最小大小 

query_cache_size：查询缓存大小

query_cache_type：缓存类型

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

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