mysql性能优化工具--tuner-primer使用介绍(mysql语句性能优化)

推荐整理分享mysql性能优化工具--tuner-primer使用介绍(mysql语句性能优化)，希望有所帮助，仅作参考，欢迎阅读内容。

文章相关热门搜索词:mysql5.7性能优化,mysql性能优化和高可用性实践,mysql性能优化和高可用性实践,mysql性能优化工具,mysql8性能优化,mysql性能优化和高可用架构实践 pdf,mysql性能优化和高可用架构实践 pdf,mysql5.7性能优化,内容如对您有帮助，希望把文章链接给更多的朋友！

下载并改变执行权限：wget +x tuning-primer.sh./tuning-primer.sh结果报告：会用几种颜色标记：蓝色：总指标绿色：表示此参数还可以红色：表示此参数有严重问题深红色：表示有问题参数黄色：一些信息提示而且还有警告：Note! This script will still suggest raising the join_buffer_size whenANY joins not using indexes are found.下面是一个报告的结果 xx@xxxxxx:~$ . ./tuning-primer.sh mysqld is alive-- MYSQL PERFORMANCE TUNING PRIMER --- By: Matthew Montgomery -MySQL Version 5.1.-enterprise-gpl-log x_Uptime = 4 days 0 hrs min 3 secAvg. qps = Total Questions = Threads Connected = 4Server has been running for over hrs.It should be safe to follow these recommendationsTo find out more information on how each of theseruntime variables effects performance visit: info about MySQL's Enterprise Monitoring and Advisory ServiceSLOW QUERIESThe slow query log is NOT enabled.Current long_query_time = . sec.You have out of that take longer than . sec. to completeYour long_query_time seems to be fineBINARY UPDATE LOGThe binary update log is enabledThe expire_logs_days is not set.The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manuallySetting expire_logs_days will allow you to remove old binary logs automaticallySee THREADSCurrent thread_cache_size = Current threads_cached = Current threads_per_sec = 0Historic threads_per_sec = 0Your thread_cache_size is fineMAX CONNECTIONSCurrent max_connections = Current threads_connected = 4Historic max_used_connections = The number of used connections is % of the configured maximum.Your max_connections variable seems to be fine.MEMORY USAGEMax Memory Ever Allocated : . GConfigured Max Per-thread Buffers : . GConfigured Max Global Buffers : 6. GConfigured Max Memory Limit : . GPhysical Memory : 8. GMax memory limit exceeds % of physical memory（内存设置严重有问题！--By Me）KEY BUFFER * / * Current MyISAM index space = 8 KCurrent key_buffer_size = 2. GKey cache miss rate is 1 : Key buffer free ratio = 0 %Your key_buffer_size seems to be too high.Perhaps you can use these resources elsewhere（错误参数设置：query_cache_size > query_cache_limit --By Me）SORT OPERATIONSCurrent sort_buffer_size = 4 MCurrent read_rnd_buffer_size = MSort buffer seems to be fineJOINSCurrent join_buffer_size = . KYou have had 1 queries where a join could not use an index properlyYou should enable "log-queries-not-using-indexes"Then look for non indexed joins in the slow query log.If you are unable to optimize your queries you may want to increase yourjoin_buffer_size to accommodate larger joins in one pass.Note! This script will still suggest raising the join_buffer_size whenANY joins not using indexes are found.（ join_buffer_size设置过小，另外存在一个查询使用了join但是没有走索引 --By Me）OPEN FILES LIMITCurrent open_files_limit = filesThe open_files_limit should typically be set to at least 2x-3xthat of table_cache if you have heavy MyISAM usage.Your open_files_limit value seems to be fineTABLE CACHECurrent table_open_cache = tablesCurrent table_definition_cache = tablesYou have a total of 0 tablesYou have open tables.

TABLE SCANSCurrent read_buffer_size = 2 MCurrent table scan ratio = : 1You have a high ratio of sequential access requests to SELECTsYou may benefit from raising read_buffer_size and/or improving your use of indexes.TABLE LOCKINGCurrent Lock Wait ratio = 1 : Your table locking seems to be fine

MySQL分组查询Group By实现原理详解 由于GROUPBY实际上也同样会进行排序操作，而且与ORDERBY相比，GROUPBY主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚

mysql 松散的索引扫描(Loose index scan) 优化GroupBy最有效的办法是当可以直接使用索引来完全获取需要group的字段。使用这个访问方法时，MySQL使用对关键字排序的索引的类型（比如BTREE索引）

MySQL学习笔记小结 慢速SQL：执行时间超过给定时间范围的查询就称为慢速查询。在MySQL中如何记录慢速SQL？答：可以在my.cnf中设置如下信息：[mysqld];enabletheslowquerylog,default1