MySQL：MySQL 在从表中搜索记录时关闭错误"mysql server gone away"的连接

Question

我使用下面的select查询从表中进行搜索。

"Select * from authentication_codes where client_id=6"

列client_id是integer类型，我也为该列编制了索引。表authentication_codes中总共有36751694条记录，其中20200000属于client_id 6。

每当我同时点击上述SQL查询4次时，mysql就会崩溃，出现错误"mysql server gone away"，CPU利用率达到95%。

对于相同的查询，带有解释命令的输出如下

mysql> explain Select * from authentication_codes where client_id=6 G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: authentication_codes
         type: ref
possible_keys: index_authentication_codes_on_client_id
          key: index_authentication_codes_on_client_id
      key_len: 5
          ref: const
         rows: 18475849
        Extra: Using where
1 row in set (0.00 sec)

在用top命令同时命中3个SQL请求语句后，CPU利用率如下

top - 09:13:43 up  2:28,  4 users,  load average: 1.94, 0.67, 0.52
Tasks: 123 total,   2 running, 121 sleeping,   0 stopped,   0 zombie
%Cpu(s): 91.0 us,  2.8 sy,  0.0 ni,  5.9 id,  0.0 wa,  0.0 hi,  0.0 si,  0.4 st
KiB Mem:   4046840 total,  2439948 used,  1606892 free,     6836 buffers
KiB Swap:        0 total,        0 used,        0 free.  1929160 cached Mem
  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 4543 mysql     20   0  886304 176544   7068 S 188.0  4.4   2:53.83 mysqld

我的服务器有4GB的内存和2个CPU核心。Mysql表使用的是数据库引擎Innodb。

谢谢，

Answer 1

服务器没有"崩溃"；客户端放弃了，因为服务器占用了太长时间。

去掉client_id上的索引；扫描整个表以发现其中大多数都有"6"，这比更快。

客户对2000万（2亿）行到底要做什么？可能会耗尽客户端中的内存。如果你需要看所有的表格，以1000块为一块来阅读表格。如果您需要COUNT()或SUM()之类的东西，请在SQL中执行。

你有一台很小的机器（只有4GB）？如果表是InnoDB，则应该将innodb_buffer_pool_size设置为1500M。

至于max_allowed_packet，请考虑您的连接是否在返回任何内容之前等待所有行，然后一次返回所有20M。也许1G今天会起作用；也许明天不会。您可以设置连接以"随到随到"地传递行吗。这将需要更长的时间，但不会达到数据包限制。