mysql巧用query cache解决连接超时

应用日志中狂报错误，获取连接超时：

同时应用报错超出了数据库的最大连接数：max connections：

这种情况很有可能是有慢sql占用了连接池中的连接没有释放，导致后续进来的请求迟迟获取不到连接池中的连接，导致请求报错，登录数据库排查发现如下sql出现执行非常的慢：

mysql> select * from user where md5(nick)=’3f5950f59ddf2a0d14a44166040e348f’;
Empty set (1.32 sec)

一眼可以看出在nick上使用了md5函数，导致user表中的索引不能使用，而全表扫描：

mysql> explain select * from user where md5(nick)=’3f5950f59ddf2a0d14a44166040e348f?
+―-+――――-+――-+――+―――――+――+―――+――+――?+――――-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+―-+――――-+――-+――+―――――+――+―――+――+――?+――――-+
| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 256608 | Using where |
+―-+――――-+――-+――+―――――+――+―――+――+――?+――――-+
1 row in set (0.00 sec)

通常情况下需要用户修改应用，将应用中的md5函数去掉，但是修改业务的方法还需要一段时间，不能够立刻是业务恢复，所以看看数据库还有没有其他的方法，灵机一动，由于该应用的场景是读多写非常少的应用，想到了query cache：

mysql> show variables like ‘%query%’;
+――――――――――+――――――――――――――-+
| Variable_name | Value |
+――――――――――+――――――――――――――-+
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 1.000000 |
| 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 |
| slow_query_log | ON |
| slow_query_log_file | /home/mysql/data3003/mysql/slow_query.log |
+――――――――――+――――――――――――――-+
12 rows in set (0.00 sec)

mysql> set global query_cache_size=1024*1024*32;
Query OK, 0 rows affected (0.02 sec)

 

mysql> Ctrl-C ― exit!
Aborted

root@xxxx ~
# my 3003
Entry Port ==== 3003
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4601147
Server version: 5.1.61-Alibaba-3930-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> use cuxiaowang_db
Database changed
mysql> select * from user where md5(nick)=’3f5950f59ddf2a0d14a44166040e348f?’;
Empty set (1.32 sec)

mysql> select * from user where md5(nick)=’3f5950f59ddf2a0d14a44166040e348f?
Empty set (0.00 sec)

可以看到查询能够很快的返回，应用立刻恢复正常。

PS：query cache的打开是有一定场景的，由于query cache并不适合更新，插入，删除非常大的应用，所以打开query cache一定要慎重，笔者曾经看到由于其数据库有大量的插入和更新的数据库打开query cache而导致整个数据库都在等待query cache的故障: