mysql - 针对 max、count 优化 MySQL InnoDB 查询

Question

我有一个 570 万行和 1.9GB 大小的 MySQL InnoDB 表:

+-------------------+---------+------+-----+---------+----------------+
|       Field       |  Type   | Null | Key | Default |     Extra      |
+-------------------+---------+------+-----+---------+----------------+
| id                | int(20) | NO   | PRI | NULL    | auto_increment |
| listing_id        | int(20) | YES  |     | NULL    |                |
| listing_link      | text    | YES  |     | NULL    |                |
| transaction_title | text    | YES  |     | NULL    |                |
| image_thumb       | text    | YES  |     | NULL    |                |
| seller_link       | text    | YES  |     | NULL    |                |
| seller_name       | text    | YES  |     | NULL    |                |
| sale_date         | date    | YES  |     | NULL    |                |
+-------------------+---------+------+-----+---------+----------------+

这是我的 3GB RAM 服务器的 my.ini 设置:

key_buffer = 16M
max_allowed_packet = 16M
sort_buffer_size = 8M
net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 8M
log_error = "mysql_error.log"
innodb_autoinc_lock_mode=0
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
thread_stack = 192K
tmp_table_size = 64M

innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 120
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90

当我运行下一个查询时，需要 20 多分钟才能返回结果:

SELECT transaction_title, 
       listing_id, 
       seller_name, 
       Max(sale_date) AS sale_date, 
       Count(*)       AS count 
FROM   sales_meta 
WHERE `sale_date` BETWEEN '2017-06-06' AND '2017-06-06' 
GROUP  BY listing_id 
HAVING Count(*) > 1 
ORDER  BY count DESC, 
          seller_name;

我做了一些研究，看来我需要添加一些索引来加快处理速度，但我对如何去做感到困惑。有的是单列索引，有的是多列索引，我应该做哪个？

为了让事情变得更复杂，我需要定期对该表执行一些其他查询:

SELECT * 
FROM   sales_meta 
WHERE ` sale_date `= '2017-06-06'; 

和

SELECT DISTINCT `seller_name` 
FROM   `sales_meta`; 

这两个可能不那么费力，但我仍然需要尽可能地优化它们，尽管三个中的第一个查询是目前的重中之重。

Answer 1

如果您只想要一天的值并且数据类型是日期，那么您可以避免使用 between 子句并使用 =

    SELECT transaction_title, 
           listing_id, 
           seller_name, 
           Max(sale_date) AS max_sale_date, 
           Count(*)       AS count 
    FROM   sales_meta 
    WHERE sale_date =  str_to_date('2017-06-06', '%Y-%m-%d')  
    GROUP  BY listing_id 
    HAVING Count(*) > 1 
    ORDER  BY count DESC, seller_name;

并确保您在 sale_date 上有一个索引