mysql:一个简单的组在一个表上的预期时间，2M条记录，一个用户？

Question

与My.cnf(大型表和特殊查询的MySQL调优)相关

我有一个很小的桌子，有2米的记录，在一台32 10的机器上，运行Windows10Pro时，除了MySql 5.7之外，什么都没有认真运行。我预计任何非索引列上的简单组都需要几秒钟，因为Mysql会缓存整个表。大约需要3分钟。我是不是漏掉了一些重要的调音提示？或者说，这是预期的时间？

查询

mysql> select specialty_code, count(*)
    -> from physicians
    -> group by specialty_code;
+----------------+----------+
| specialty_code | count(*) |
+----------------+----------+
| FM             |   199604 |
| GYN            |    14263 |
| OBG            |    78878 |
+----------------+----------+
3 rows in set (2 min 56.27 sec)

这张桌子

mysql> describe physicians;
+-----------------------+---------+------+-----+---------+-------+
| Field                 | Type    | Null | Key | Default | Extra |
+-----------------------+---------+------+-----+---------+-------+
| physician_id          | int(11) | YES  |     | NULL    |       |
| practitioner_id       | int(11) | YES  | MUL | NULL    |       |
| state                 | text    | YES  |     | NULL    |       |
| specialty_code        | text    | YES  |     | NULL    |       |
| specialty_description | text    | YES  |     | NULL    |       |
| CBSA                  | text    | YES  |     | NULL    |       |
+-----------------------+---------+------+-----+---------+-------+
6 rows in set (0.10 sec)    

mysql> show table status like '%physicians%';
+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name       | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| physicians | InnoDB |      10 | Dynamic    | 1963005 |             66 |   130711552 |               0 |            0 |   2097152 |           NULL | 2016-01-04 08:41:30 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+

规模：

mysql> select count(*) from physicians;
+----------+
| count(*) |
+----------+
|  1957997 |
+----------+
1 row in set (9.91 sec) 

解释：

mysql> explain select specialty_code, count(*)
    -> from physicians
    -> group by specialty_code;
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                           |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------+
|  1 | SIMPLE      | physicians | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1963005 |   100.00 | Using temporary; Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------+    

我调谐的帕拉姆：

join_buffer_size=2G 
tmp_table_size=5G
innodb_buffer_pool_size=20G
sort_buffer_size=2G

该表使用默认的InnoDB引擎。

注意:这只是我更大问题的一个简单的症状:我有~5-6个表，总容量为10 GB。我希望MySql会把它们全部放入内存中，并在一分钟内回答任何特别的查询.我应该在过滤/分组/加入每一列之前索引它吗？

如果这是在咆哮，谢谢和抱歉。我只是习惯于分布式数据库，在毫秒内为我处理数据:)

Answer 1

tmp_table_size=5G

不是的。回到默认状态。如果多个查询需要tmp表，则可能会耗尽RAM。

将TEXT字段更改为合理长度的VARCHARs。

第一个表扫描将执行I/O操作，以便将表放入RAM (如果可能的话)；随后对表的引用将显示缓存的数据。您运行了两次GROUP BY才看到这种效果吗？