MySql单表优化选项

Question

我有一个大约有 600 万行的表，并且有很多包含大量数据的列。

我想在此表上实现一个主要基于创建行的日期范围和其他一些列的搜索功能。

不需要其他表，因此不存在查询中的连接问题。此外，我还在日期列和其他相关列上实现了索引。

考虑到表的行数，如果我搜索大范围，搜索仍然需要很长时间。我已将 key_buffer_size 增加到 1G，并将 mysql_query_cache 增加到 500M。

我想知道，

1. 我可以做任何其他事情，优化任何其他可能提高性能的变量。
2. 我是否应该考虑重新设计表格本身？

我想在增加 RAM 或 CPU 之前探索所有选项。

更新:

下面是更多的细节

CPU:英特尔四核内存:4GB操作系统:CentOS

+-----------------------------+---------------+------+-----+---------+----------------+
| Field                       | Type          | Null | Key | Default | Extra          |
+-----------------------------+---------------+------+-----+---------+----------------+
| deal_id                     | int(11)       | NO   | PRI | NULL    | auto_increment | 
| deal_group_id               | int(11)       | NO   | PRI | NULL    |                | 
| site_id                     | int(11)       | YES  | MUL | NULL    |                | 
| datetime                    | char(14)      | YES  | MUL | NULL    |                |  
| deal_category               | varchar(200)  | YES  | MUL | NULL    |                | 
| vendor_name                 | varchar(200)  | YES  |     | NULL    |                | 
| area                        | varchar(200)  | YES  | MUL | NULL    |                | 
| address                     | text          | YES  |     | NULL    |                |  
| country_code                | varchar(5)    | NO   | MUL | NULL    |                | 
| expired                     | int(1)        | NO   |     | 0       |                | 
| type                        | varchar(20)   | NO   |     | REGULAR |                | 
| pn_id                       | int(11)       | NO   |     | 0       |                | 
+-----------------------------+---------------+------+-----+---------+----------------+

以上是架构。在 where 子句中，使用了 datetime in range、deal_category、vendor_name in like 和 country_code 的组合。所有的索引。下面是一个示例查询


选择不同的 t.deal_id、t.deal_group_id、t.site_name、UNIX_TIMESTAMP(t.datetime) 作为日期时间，t.deal_category 作为 deal_category，t.vendor_name，t.area 作为区域，t.country_code FROM table t WHERE 1 AND t。 country_code = 'US' AND datetime BETWEEN '20110601' AND '20120303' limit 10;

我已经将 innodb_buffer_pool_size 增加到 75% 左右，它提高了一些查询的性能，但如果包括所有过滤器，仍然需要很多时间。

下面是my.cnf文件设置，

query_cache_size=500M

max_allowed_pa​​cket= 1G

innodb_buffer_pool_size = 3G

key_buffer_size = 1G

这张表很忙，每分钟有数百次写入。

希望这能提供更清晰的图片。

更多更新

下面是表的解释输出和索引，

+----+-------------+-------+------+-----------------------+--------------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys         | key          | key_len | ref   | rows   | Extra       |
+----+-------------+-------+------+-----------------------+--------------+---------+-------+--------+-------------+
|  1 | SIMPLE      | t     | ref  | country_code,datetime | country_code | 17      | const | 521556 | Using where | 
+----+-------------+-------+------+-----------------------+--------------+---------+-------+--------+-------------+

索引:

+-------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name          | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| table       |          0 | PRIMARY           |            1 | deal_id       | A         |     5810775 |     NULL | NULL   |      | BTREE      |         | 
| table       |          0 | PRIMARY           |            2 | deal_group_id | A         |     5810775 |     NULL | NULL   |      | BTREE      |         | 
| table       |          1 | site_id           |            1 | site_id       | A         |        2109 |     NULL | NULL   | YES  | BTREE      |        | 
| table       |          1 | area              |            1 | area          | A         |       18927 |     NULL | NULL   | YES  | BTREE      |         | 
| table       |          1 | country_code      |            1 | country_code  | A         |          20 |     NULL | NULL   |      | BTREE      |         | 
| table       |          1 | deal_group_id     |            1 | deal_group_id | A         |     1936925 |     NULL | NULL   |      | BTREE      |         | 
| table       |          1 | deal_category     |            1 | deal_category | A         |          20 |     NULL | NULL   | YES  | BTREE      |         | 
| table       |          1 | datetime          |            1 | datetime      | A         |     5810775 |     NULL | NULL   | YES  | BTREE      |         | 
+-------------+------------+-------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+

谢谢

Answer 1

1. 对于运行 Innodb 的数据库，您需要将 innodb_buffer_pool_size 的值增加到专用服务器上大约 70 - 80% 的可用 RAM，以便尽可能多的数据可以放入内存

   <

2. 创建包含您要搜索的字段的单个索引

3. LIKE %value% 搜索不使用索引

对于额外的调整，您需要提供更多关于您将执行的架构和搜索的信息

1. 移除 WHERE 子句中的“1 AND”比较

2. 将日期时间 BETWEEN '20110601' AND '20120303' 更改为 DATE(datetime) BETWEEN STR_TO_DATE('20110601', '%Y%m%d') AND STR_TO_DATE('20120303', '%Y%m% d').我假设日期时间字段包含 MySQL 格式的日期

3. 考虑将日期时间字段从 char(14) 更改为日期时间字段以加快计算速度

4. 如果 country_code 列只有 2 位国家代码，请考虑将其大小更改为 char(2)

5. 删除 deal_id 列之前的 DISTINCT，这是一个自动增量和主键