mysql优化思维引导二

   今天得重点是第二方面，如何从数据库得状态，发现数据库得瓶颈，或者可能出现瓶颈的地方。主要针对myisam存储引擎

1、慢查询日志

打开、并观察是否有慢查询日志，具体花费多少时间算慢查询，可以根据数据量，和并发量来综合衡量，如果要做sql语句优化的话，一般1s以上的都算慢查询，通过满查询日志找出1s以上的查询，使用explain和prifile分析，在这里提一点，mysql的优化工具没有oracle这种大型数据库好用，没有现成统计某条语句执行的次数的工具，所以只有我们自己开发脚本，通过bin-log或者查询日志来提取某条语句的执行频率，然后从执行频率最高的语句开始逐条分析，看看是否可以优化，比如一条语句，每天执行100W次，你优化后，就算每次提升0.1秒，但是对于总的性能提升效果还是很明显的。

 

2、最重要的状态参数key_read_requests和key_reads.
key_read_requests表示从缓存读索引的请求数，key_reads表示没有在缓存中找到索引，从硬盘上读取索引的次数，这里就是为什么使用索引作为where子句的关键字，速度快的原因，如果key_reads过高，就需要调大Key_buffer_size。

 

3、关于索引使用的情况

Handler_read_first \\索引中第一条被读的次数。如果较高，它建议服务器正执行大量全索引扫描；例如，SELECT col1 FROM foo，假定col1有索引。
Handler_read_key   \\根据索引读第一行的请求数，该值高，说明索引设置正确
Handler_read_rnd    \\根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键
Handler_read_rnd_next \\在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引，这个值
要注意一下，不是做一次全表扫描纪录一次，比如做一次全表扫描有多少行，这里就会增加多少数字，表连接和子查询有多条结果，也会累计，所以不一定是全表扫描，具体问题具体分析。

 

4、创建临时表和临时文件的次数
Created_tmp_disk_tables  \\如果此值较大，证明tmp_table_size较少，需要调大。 
Created_tmp_files
Created_tmp_tables

 

5、opened table的数量
open_table_definitions \\缓存的.frm文件的数量
open_tables \\当前打开了多少表
opened_tables \\一共打开了多少表
opened_table_definitions \\总共缓存过多少.frm文件
如果opened_tables和opened_table_definitions的数量过大，那么证明需要优化table_open_cache
table_open_cache \\该值设置缓存.frm表定义的数量

 

6、创建的连接线程数
threads_cached \\已经缓存的连接线程数
threads_connected \\当前正处于连接的线程数，和 show processlist数量相等
threads_created  \\一共创建过多少个连接线程，如果远远大于threads_connected，并且threads_cached=thread_cache_size的值，那么需要调大thread_cache_size的值

 

7、表锁，确定是否有锁资源的争抢
Table_locks_immediate \\立即获得表锁
Table_locks_waited \\等待表锁，如果太高，就要考虑优化程序结构，语句，或者替换存储引擎

 

8、排序的状态
Sort_merge_passes \\排序算法已经执行的合并的数量。如果这个变量值较大，应考虑增加sort_buffer_size系统变量的值。
Sort_range \\在范围内执行的排序的数量。
Sort_rows \\已经排序的行数。
Sort_scan  \\通过扫描表完成的排序的数量

 

9、查询缓存

这个值对性能影响很重要，但是比较费内存，所以放在最后
query_cache_type是查询缓存的开关，此参数有三个值，分别的含义如下：
 0也就是off //此值设定服务器不支持查询缓存，select不会从缓存中检索结果也不会把结果写到缓存中。
 1也就是on //如果要启用，通常设置这个值，select语句默认会从缓存中检索结果页会把查询的结果放到缓存中，除非select语句包含select sql_no_cache
 2 //该值告诉服务器只对使用了 select sql_cache的语句使用查询缓存功能。
以下这些是查询缓存的相关设置值。
mysql> show global variables like 'query%';         
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_alloc_block_size       | 8192      |
| query_cache_limit            | 1048576   |  \\查过这个值不缓存
| query_cache_min_res_unit     | 4096      |   
| query_cache_size             | 102400000 |  \\查询缓存可以使用的内存大小
| query_cache_type             | ON        |  \\是否启用查询缓存
| query_cache_wlock_invalidate | OFF       |
| query_prealloc_size          | 8192      |
+------------------------------+-----------+
以下这些是查询缓存的状态值。
mysql> show global status like 'qcache%';         
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 1153      |  \\空闲的query cache块
| Qcache_free_memory      | 100094608 |  \\空闲的query cache内存
| Qcache_hits             | 259503    |  \\查询缓存的命中数量--重要
| Qcache_inserts          | 357422    |  \\从缓存查询结果没命中的次数
| Qcache_lowmem_prunes    | 0         |  \\由于cache_size过小，结果从内存中被删                 除的次数
| Qcache_not_cached       | 5187      | \\没有被缓存的次数
| Qcache_queries_in_cache | 1474      | \\缓存中包含的结果数量，如果此值和Qcache_lowmem_prunes值较小，qcache_inserts很大，则可能是结果变坏太快，被cache的结果一旦变化就会从此值中删除
| Qcache_total_blocks     | 4129      |
+-------------------------+-----------+
缓存的使用率=Qcache_hits/（Qcache_hits+com_select）

有新的想法的时候再继续更新