优化mysql in () order by

mysql(SHOW STATUS)性能的检查和调优方法

记录慢查询:
在my.cnf中配置
[mysqld]
; enable the slow query log, default 10 seconds
log-slow-queries
; log queries taking longer than 5 seconds
long_query_time = 5
; log queries that don’t use indexes even if they take less than long_query_time
; MySQL 4.1 and newer only
log-queries-not-using-indexes

慢速查询日志都保存在 MySQL 数据目录中，名为 hostname-slow.log。如果希望使用一个不同的名字或路径，可以在 my.cnf 中使用 log-slow-queries = /new/path/to/file 实现此目的。
阅读慢速查询日志最好是通过 mysqldumpslow 命令进行。指定日志文件的路径，就可以看到一个慢速查询的排序后的列表，并且还显示了它们在日志文件中出现的次数。一个非常有用的特性是 mysqldumpslow 在比较结果之前，会删除任何用户指定的数据，因此对同一个查询的不同调用被计为一次；这可以帮助找出需要工作量最多的查询。

对查询进行缓存:
默认不启用
启用方法 将 query_cache_size = 32M 添加到 /etc/my.conf 中可以启用 32MB 的查询缓存。

监视查询缓存的统计信息
mysql> SHOW STATUS LIKE ‘qcache%’;
+————————-+————+
| Variable_name | Value |
+————————-+————+
| Qcache_free_blocks | 5216 |
| Qcache_free_memory | 14640664 |
| Qcache_hits | 2581646882 |
| Qcache_inserts | 360210964 |
| Qcache_lowmem_prunes | 281680433 |
| Qcache_not_cached | 79740667 |
| Qcache_queries_in_cache | 16927 |
| Qcache_total_blocks | 47042 |
+————————-+————+
8 rows in set (0.00 sec)

Qcache_free_blocks 缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE 会对缓存中的碎片进行整理，从而得到一个空闲块。
Qcache_free_memory 缓存中的空闲内存。
Qcache_hits 每次查询在缓存中命中时就增大。
Qcache_inserts 每次插入一个查询时就增大。命中次数除以插入次数就是不中比率；用 1 减去这个值就是命中率。在上面这个例子中，大约有 87% 的查询都在缓存中命中。
Qcache_lowmem_prunes 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存 很少。（上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况）。
Qcache_not_cached 不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句。
Qcache_queries_in_cache 当前缓存的查询（和响应）的数量。
Qcache_total_blocks 缓存中块的数量。

强制限制:
在my.cnf中设置
set-variable=max_connections=500
set-variable=wait_timeout=10 #终止空闲超过10秒的连接
max_connect_errors = 100 #主机连接到mysql 100次无法建立连接,就锁定直到flush hosts 之后.

查看当前最大连接数
mysql> show status like ‘max_used_connections’;

缓冲区和缓存:
设置打开文件缓存
/etc/mysqld.conf
table_cache=5000

显示打开表的活动
mysql> SHOW STATUS LIKE ‘open%tables’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_tables | 5000 |
| Opened_tables | 195 |
+—————+——-+
2 rows in set (0.00 sec)
如果 Open_tables 比 table_cache 设置小很多，就说明该值太大了.

设置线程缓存
在 my.cnf中
thread_cache = 40
显示线程使用统计信息
mysql> SHOW STATUS LIKE ‘threads%’;
+——————-+——–+
| Variable_name | Value |
+——————-+——–+
| Threads_cached | 27 |
| Threads_connected | 15 |
| Threads_created | 838610 |
| Threads_running | 3 |
+——————-+——–+
4 rows in set (0.00 sec)

如果连续执行show status命令 Threads_created 都快速增加 就应该增加线程缓存.

设置关键字缓存
key_buffer = 384M
确定关键字效率
mysql> show status like ‘%key_read%’;
+——————-+———–+
| Variable_name | Value |
+——————-+———–+
| Key_read_requests | 163554268 |
| Key_reads | 98247 |
+——————-+———–+
2 rows in set (0.00 sec)

key_reads 命中磁盘的请求个数
key_read_requests 是请求总数
如果1000个请求中有超过1个命中磁盘,就应该考虑增大关键字缓存.

设置临时表
临时表太大就会写入磁盘
在my.cnf中设置
tmp_table_size
max_heap_table_size
显示临时表
mysql> SHOW STATUS LIKE ‘created_tmp%’;
+————————-+——-+
| Variable_name | Value |
+————————-+——-+
| Created_tmp_disk_tables | 30660 |
| Created_tmp_files | 2 |
| Created_tmp_tables | 32912 |
+————————-+——-+
3 rows in set (0.00 sec)

设置每个会话
my.cnf中修改 或通过代码修改
sort_buffer_size = 4M 将排序缓冲区设置为 4MB
mysql> SHOW STATUS LIKE “sort%”;
+——————-+———+
| Variable_name | Value |
+——————-+———+
| Sort_merge_passes | 1 |
| Sort_range | 79192 |
| Sort_rows | 2066532 |
| Sort_scan | 44006 |
+——————-+———+
4 rows in set (0.00 sec)
如果 sort_merge_passes 很大，就表示需要注意 sort_buffer_size

确定表的扫描比率
mysql> SHOW STATUS LIKE “com_select”;
+—————+——–+
| Variable_name | Value |
+—————+——–+
| Com_select | 318243 |
+—————+——–+
1 row in set (0.00 sec)

mysql> SHOW STATUS LIKE “handler_read_rnd_next”;
+———————–+———–+
| Variable_name | Value |
+———————–+———–+
| Handler_read_rnd_next | 165959471 |
+———————–+———–+
1 row in set (0.00 sec)

Handler_read_rnd_next / Com_select 得出了表扫描比率 —— 在本例中是 521:1。如果该值超过 4000，就应该查看 read_buffer_size，例如 read_buffer_size = 4M。如果这个数字超过了 8M，就应该与开发人员讨论一下对这些查询进行调优了！

3个工具
mytop
mysqlard
mysqlreport

flush show

函数介绍
benchmark

key_reads/Key_read_requests<0.01 OK
key_writes/Key_write_requests<1 OK

Optimize table table_name
对表自动优化

Explain select statement

Flush flush_option [Privileges,tables,hosts,logs]