mysql性能优化（一）

缘来路过

　　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服务   查看是否启动成功