mysql优化思维引导二

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql优化思维引导二

余二五 2017-11-09 20:57:00 浏览941
展开阅读全文
   今天得重点是第二方面,如何从数据库得状态,发现数据库得瓶颈,或者可能出现瓶颈的地方。主要针对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)
有新的想法的时候再继续更新




本文转自 fenghao.cn 51CTO博客,原文链接:http://blog.51cto.com/linuxguest/455269,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
余二五
+ 关注