向左左提问：MYSQL参数设定

Question

昨天看到一个什么show query_cache_size的帖子，
结果我在我的MYSQL这边一看，啥都是0呀。SHOW STATUS LIKE 'Qcache%'：
'Qcache_free_blocks', '0'
'Qcache_free_memory', '0'
'Qcache_hits', '0'
'Qcache_inserts', '0'
'Qcache_lowmem_prunes', '0'
'Qcache_not_cached', '0'
'Qcache_queries_in_cache', '0'
'Qcache_total_blocks', '0'SHOW VARIABLES LIKE '%query_cache%''have_query_cache', 'YES'
'query_cache_limit', '1048576'
'query_cache_min_res_unit', '4096'
'query_cache_size', '138412032'
'query_cache_type', 'ON'
'query_cache_wlock_invalidate', 'OFF'帮我优化一下。200分..

Answer 1

你的MYSQL估计刚开出来吧。 什么都没查过。

Answer 2

status; 命令贴出来看一下。

Answer 3

show status:
'Aborted_clients', '0'
'Aborted_connects', '0'
'Binlog_cache_disk_use', '0'
'Binlog_cache_use', '0'
'Bytes_received', '241'
'Bytes_sent', '366'
'Com_admin_commands', '0'
'Com_alter_db', '0'
'Com_alter_table', '0'
'Com_analyze', '0'
'Com_backup_table', '0'
'Com_begin', '0'
'Com_call_procedure', '0'
'Com_change_db', '1'
'Com_change_master', '0'
'Com_check', '0'
'Com_checksum', '0'
'Com_commit', '0'
'Com_create_db', '0'
'Com_create_function', '0'
'Com_create_index', '0'
'Com_create_table', '0'
'Com_create_user', '0'
'Com_dealloc_sql', '0'
'Com_delete', '0'
'Com_delete_multi', '0'
'Com_do', '0'
'Com_drop_db', '0'
'Com_drop_function', '0'
'Com_drop_index', '0'
'Com_drop_table', '0'
'Com_drop_user', '0'
'Com_execute_sql', '0'
'Com_flush', '0'
'Com_grant', '0'
'Com_ha_close', '0'
'Com_ha_open', '0'
'Com_ha_read', '0'
'Com_help', '0'
'Com_insert', '0'
'Com_insert_select', '0'
'Com_kill', '0'
'Com_load', '0'
'Com_load_master_data', '0'
'Com_load_master_table', '0'
'Com_lock_tables', '0'
'Com_optimize', '0'
'Com_preload_keys', '0'
'Com_prepare_sql', '0'
'Com_purge', '0'
'Com_purge_before_date', '0'
'Com_rename_table', '0'
'Com_repair', '0'
'Com_replace', '0'
'Com_replace_select', '0'
'Com_reset', '0'
'Com_restore_table', '0'
'Com_revoke', '0'
'Com_revoke_all', '0'
'Com_rollback', '0'
'Com_savepoint', '0'
'Com_select', '2'
'Com_set_option', '2'
'Com_show_binlog_events', '0'
'Com_show_binlogs', '0'
'Com_show_charsets', '0'
'Com_show_collations', '0'
'Com_show_column_types', '0'
'Com_show_create_db', '0'
'Com_show_create_table', '0'
'Com_show_databases', '0'
'Com_show_errors', '0'
'Com_show_fields', '0'
'Com_show_grants', '0'
'Com_show_innodb_status', '0'
'Com_show_keys', '0'
'Com_show_logs', '0'
'Com_show_master_status', '0'
'Com_show_ndb_status', '0'
'Com_show_new_master', '0'
'Com_show_open_tables', '0'
'Com_show_privileges', '0'
'Com_show_processlist', '0'
'Com_show_slave_hosts', '0'
'Com_show_slave_status', '0'
'Com_show_status', '1'
'Com_show_storage_engines', '0'
'Com_show_tables', '0'
'Com_show_triggers', '0'
'Com_show_variables', '0'
'Com_show_warnings', '0'
'Com_slave_start', '0'
'Com_slave_stop', '0'
'Com_stmt_close', '0'
'Com_stmt_execute', '0'
'Com_stmt_fetch', '0'
'Com_stmt_prepare', '0'
'Com_stmt_reset', '0'
'Com_stmt_send_long_data', '0'
'Com_truncate', '0'
'Com_unlock_tables', '0'
'Com_update', '0'
'Com_update_multi', '0'
'Com_xa_commit', '0'
'Com_xa_end', '0'
'Com_xa_prepare', '0'
'Com_xa_recover', '0'
'Com_xa_rollback', '0'
'Com_xa_start', '0'
'Compression', 'OFF'
'Connections', '131'
'Created_tmp_disk_tables', '0'
'Created_tmp_files', '5'
'Created_tmp_tables', '1'
'Delayed_errors', '0'
'Delayed_insert_threads', '0'
'Delayed_writes', '0'
'Flush_commands', '1'
'Handler_commit', '0'
'Handler_delete', '0'
'Handler_discover', '0'
'Handler_prepare', '0'
'Handler_read_first', '0'
'Handler_read_key', '0'
'Handler_read_next', '0'
'Handler_read_prev', '0'
'Handler_read_rnd', '0'
'Handler_read_rnd_next', '0'
'Handler_rollback', '0'
'Handler_savepoint', '0'
'Handler_savepoint_rollback', '0'
'Handler_update', '0'
'Handler_write', '132'
'Innodb_buffer_pool_pages_data', '891'
'Innodb_buffer_pool_pages_dirty', '0'
'Innodb_buffer_pool_pages_flushed', '131'
'Innodb_buffer_pool_pages_free', '2115'
'Innodb_buffer_pool_pages_latched', '0'
'Innodb_buffer_pool_pages_misc', '2'
'Innodb_buffer_pool_pages_total', '3008'
'Innodb_buffer_pool_read_ahead_rnd', '7'
'Innodb_buffer_pool_read_ahead_seq', '0'
'Innodb_buffer_pool_read_requests', '68995'
'Innodb_buffer_pool_reads', '531'
'Innodb_buffer_pool_wait_free', '0'
'Innodb_buffer_pool_write_requests', '489'
'Innodb_data_fsyncs', '136'
'Innodb_data_pending_fsyncs', '0'
'Innodb_data_pending_reads', '0'
'Innodb_data_pending_writes', '0'
'Innodb_data_read', '16732160'
'Innodb_data_reads', '898'
'Innodb_data_writes', '240'
'Innodb_data_written', '4379648'
'Innodb_dblwr_pages_written', '131'
'Innodb_dblwr_writes', '25'
'Innodb_log_waits', '0'
'Innodb_log_write_requests', '136'
'Innodb_log_writes', '59'
'Innodb_os_log_fsyncs', '84'
'Innodb_os_log_pending_fsyncs', '0'
'Innodb_os_log_pending_writes', '0'
'Innodb_os_log_written', '74240'
'Innodb_page_size', '16384'
'Innodb_pages_created', '3'
'Innodb_pages_read', '888'
'Innodb_pages_written', '131'
'Innodb_row_lock_current_waits', '0'
'Innodb_row_lock_time', '0'
'Innodb_row_lock_time_avg', '0'
'Innodb_row_lock_time_max', '0'
'Innodb_row_lock_waits', '0'
'Innodb_rows_deleted', '13'
'Innodb_rows_inserted', '35'
'Innodb_rows_read', '19418'
'Innodb_rows_updated', '20'
'Key_blocks_not_flushed', '0'
'Key_blocks_unused', '22484'
'Key_blocks_used', '0'
'Key_read_requests', '0'
'Key_reads', '0'
'Key_write_requests', '0'
'Key_writes', '0'
'Last_query_cost', '0.000000'
'Max_used_connections', '10'
'Not_flushed_delayed_rows', '0'
'Open_files', '21'
'Open_streams', '0'
'Open_tables', '230'
'Opened_tables', '0'
'Prepared_stmt_count', '0'
'Qcache_free_blocks', '0'
'Qcache_free_memory', '0'
'Qcache_hits', '0'
'Qcache_inserts', '0'
'Qcache_lowmem_prunes', '0'
'Qcache_not_cached', '0'
'Qcache_queries_in_cache', '0'
'Qcache_total_blocks', '0'
'Questions', '9186'
'Rpl_status', 'NULL'
'Select_full_join', '0'
'Select_full_range_join', '0'
'Select_range', '0'
'Select_range_check', '0'
'Select_scan', '1'
'Slave_open_temp_tables', '0'
'Slave_retried_transactions', '0'
'Slave_running', 'OFF'
'Slow_launch_threads', '0'
'Slow_queries', '0'
'Sort_merge_passes', '0'
'Sort_range', '0'
'Sort_rows', '0'
'Sort_scan', '0'
'Ssl_accept_renegotiates', '0'
'Ssl_accepts', '0'
'Ssl_callback_cache_hits', '0'
'Ssl_cipher', ''
'Ssl_cipher_list', ''
'Ssl_client_connects', '0'
'Ssl_connect_renegotiates', '0'
'Ssl_ctx_verify_depth', '0'
'Ssl_ctx_verify_mode', '0'
'Ssl_default_timeout', '0'
'Ssl_finished_accepts', '0'
'Ssl_finished_connects', '0'
'Ssl_session_cache_hits', '0'
'Ssl_session_cache_misses', '0'
'Ssl_session_cache_mode', 'NONE'
'Ssl_session_cache_overflows', '0'
'Ssl_session_cache_size', '0'
'Ssl_session_cache_timeouts', '0'
'Ssl_sessions_reused', '0'
'Ssl_used_session_cache_entries', '0'
'Ssl_verify_depth', '0'
'Ssl_verify_mode', '0'
'Ssl_version', ''
'Table_locks_immediate', '7997'
'Table_locks_waited', '0'
'Tc_log_max_pages_used', '0'
'Tc_log_page_size', '0'
'Tc_log_page_waits', '0'
'Threads_cached', '7'
'Threads_connected', '3'
'Threads_created', '12'
'Threads_running', '1'
'Uptime', '12147'
'Uptime_since_flush_status', '12147'

Answer 4

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.1.52, for Win32 (ia32)Connection id:          1
Current database:       csdn
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.1.52-community MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 2 hours 7 min 23 secThreads: 1  Questions: 50  Slow queries: 0  Opens: 20  Flush tables: 1  Open tab
les: 1  Queries per second avg: 0.6
--------------mysql>

Answer 5

mysql>  SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 15966368 |
| Qcache_hits             | 18       |
| Qcache_inserts          | 284      |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 141      |
| Qcache_queries_in_cache | 152      |
| Qcache_total_blocks     | 381      |
+-------------------------+----------+
8 rows in set (0.00 sec)
 
mysql> use cpc;
Database changed
mysql> select * from area where area_id=100;
+---------+-----------+-------------+----------------+----------+-------+------------+
| area_id | name      | area_number | parent_area_id | fullname | state | area_level |
+---------+-----------+-------------+----------------+----------+-------+------------+
|     100 | 鍖椾含甯?   |         100 |              1 |          | 1     |          2 |
+---------+-----------+-------------+----------------+----------+-------+------------+
1 row in set (0.00 sec)
 
mysql>  SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 15965344 |
| Qcache_hits             | 18       |
| Qcache_inserts          | 285      |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 142      |
| Qcache_queries_in_cache | 153      |
| Qcache_total_blocks     | 383      |
+-------------------------+----------+
8 rows in set (0.00 sec)
 
mysql> select * from area where area_id=100;
+---------+-----------+-------------+----------------+----------+-------+------------+
| area_id | name      | area_number | parent_area_id | fullname | state | area_level |
+---------+-----------+-------------+----------------+----------+-------+------------+
|     100 | 鍖椾含甯?   |         100 |              1 |          | 1     |          2 |
+---------+-----------+-------------+----------------+----------+-------+------------+
1 row in set (0.00 sec)
 
mysql>  SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 15965344 |
| Qcache_hits             | 19       |
| Qcache_inserts          | 285      |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 142      |
| Qcache_queries_in_cache | 153      |
| Qcache_total_blocks     | 383      |
+-------------------------+----------+
8 rows in set (0.00 sec)
你看下我测试是值，第一次查100的时候，加入缓存了，第二次再查，就没再增加进去。
show variables like '%query%';
看看这个是什么结果

Answer 6

mysql> status                                                                   --------------                                                                  C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe  Ver 14.12 Distrib 5.0.51b, for Win32 (ia32)                                                                                                                                              Connection id:          151                                                     Current database:                                                               Current user:           root@localhost                                          SSL:                    Not in use                                              Using delimiter:        ;                                                       Server version:         5.0.51b-community-nt MySQL Community Edition (GPL)      Protocol version:       10                                                      Connection:             localhost via TCP/IP                                    Server characterset:    latin1                                                  Db     characterset:    latin1                                                  Client characterset:    latin1                                                  Conn.  characterset:    latin1                                                  
TCP port:               3306                                                    Uptime:                 3 hours 37 min 36sec                                                                                                                   
Threads: 3  Questions: 9951  Slow queries: 0  Opens: 578  Flush tables: 1  
Open tables: 0  Queries per second avg:0.762                                        --------------                                                                  

Answer 7

show variables like '%query%';
'ft_query_expansion_limit', '20'
'have_query_cache', 'YES'
'long_query_time', '10'
'query_alloc_block_size', '8192'
'query_cache_limit', '1048576'
'query_cache_min_res_unit', '4096'
'query_cache_size', '0'
'query_cache_type', 'ON'
'query_cache_wlock_invalidate', 'OFF'
'query_prealloc_size', '8192'

Answer 8

Qcache_hits             | 19       另外你看这个命中率。由于我查询的语句完全一样，所以就命中了。命中次数+1.

Answer 9

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 151
Server version: 5.0.51b-community-nt MySQL Community Edition (GPL)Type 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> status
--------------
C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe  Ver 14.12 Distrib 5.0.51b
, for Win32 (ia32)Connection id:          151
Current database:
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.0.51b-community-nt MySQL Community Edition (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 3 hours 37 min 36 secThreads: 3  Questions: 9951  Slow queries: 0  Opens: 578  Flush tables: 1  Open
tables: 0  Queries per second avg: 0.762
--------------mysql> ^A^X

Answer 10

'query_cache_size', '0'怎么是0 啊set global query_cache_size = 600000
再查询几次。

Answer 11

现在查了3次： 
mysql> Show STATUS LIKE 'Qcache%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 1      |
| Qcache_free_memory      | 581344 |
| Qcache_hits             | 0      |
| Qcache_inserts          | 3      |
| Qcache_lowmem_prunes    | 0      |
| Qcache_not_cached       | 19     |
| Qcache_queries_in_cache | 3      |
| Qcache_total_blocks     | 8      |
+-------------------------+--------+
8 rows in set (0.02 sec)mysql>

Answer 12

我查询的时候HIT没有加上，只有INSERT有加上。

Answer 13

1：你查询的表记录不能有任何改变，如果你做了INSERT,UPDATE等，那么缓存失效
2：你的查询语句要完全一样，多一个空格都认为是2个不同的查询，命中缓存失败。

Answer 14

是先在insert加上,
再运行同一个sql语句 才能在hit上加.以为已经在缓存中存在了