在未启用查询缓存的工作负载中，如果启用了查询缓存会发生什么，此处讨论的是：
DB: MariaDB 10.0.11
操作系统：Ubuntu 14.04

桌子、数据、测试

CREATE TABLE event_test(
 id          BIGINT AUTO_INCREMENT,
 event_id    INT    NOT NULL    DEFAULT 0,
 user_id     INT    NOT NULL    DEFAULT 0,
 status      INT    NOT NULL    DEFAULT 0,
 create_time DATETIME   NOT NULL,
 update_time DATETIME   NOT NULL,
 delete_time DATETIME   NOT NULL,
 PRIMARY KEY (id),
 KEY (user_id,event_id)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8;

在上述的表格中，将大约3500万行数据随机进行多次搜索。

MariaDB [my_test]> select count(*) from event_test;
+----------+
| count(*) |
+----------+
| 34623542 |
+----------+
1 row in set (5.07 sec)

测试通过对sysbench的select.lua中的event函数稍作修改并执行。持续进行4个线程的轻量级搜索，时间为5分钟。

function event(thread_id)
   local eid
   eid = sb_rand(0, 1000000)%15
   rs = db_query("SELECT * FROM event_test WHERE event_id=" .. eid .. " AND user_id=" .. sb_rand(0,3000000))
end

./bin/sysbench --test=/tests/db/my_select_test.lua --db-driver=mysql --mysql-user=user --mysql-password=pass --mysql-host=127.0.0.1 --mysql-db=my_test --mysql-table-engine=innodb --num-threads=4 --max-requests=0 --max-time=300 run

There is only one option: 結果1摘录

在执行测试期间查看进程列表时，看起来进入了”等待查询缓存锁”的状态，可能会导致CPU使用率不高。以下是执行结果。

Running the test with following options:
Number of threads: 4
Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            3157862
        write:                           0
        other:                           0
        total:                           3157862
    transactions:                        0      (0.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 3157862 (10526.19 per sec.)
    other operations:                    0      (0.00 per sec.)

General statistics:
    total time:                          300.0004s
    total number of events:              3157862
    total time taken by event execution: 1195.9937s
    response time:
         min:                                  0.02ms
         avg:                                  0.38ms
         max:                                 11.40ms
         approx.  95 percentile:               0.59ms

Threads fairness:
    events (avg/stddev):           789465.5000/614.61
    execution time (avg/stddev):   298.9984/0.00

我试着将查询缓存关掉并再次尝试了一下。

如果将query_cache_size设为0，则可以关闭查询缓存。

MariaDB [my_test]> SET @@global.query_cache_size=0;
查询 OK, 受影响的行数：0，时间：0.01 秒。

2的結果

Running the test with following options:
Number of threads: 4
Random number generator seed is 0 and will be ignored


Threads started!

OLTP test statistics:
    queries performed:
        read:                            9999853
        write:                           0
        other:                           0
        total:                           9999853
    transactions:                        0      (0.00 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 9999853 (33332.83 per sec.)
    other operations:                    0      (0.00 per sec.)

General statistics:
    total time:                          300.0001s
    total number of events:              9999853
    total time taken by event execution: 1188.3766s
    response time:
         min:                                  0.07ms
         avg:                                  0.12ms
         max:                                 17.82ms
         approx.  95 percentile:               0.14ms

Threads fairness:
    events (avg/stddev):           2499963.2500/4806.77
    execution time (avg/stddev):   297.0942/0.02

请求处理数量增加了三倍。
如果主要是轻量级搜索或者更新频率极高的情况，可能最好关闭查询缓存。
在这种情况下，可能需要考虑将重量级搜索处理转移到内存缓存中。