mysql - 如何在 MySql 中使用 where 和 group by

Question

我的数据库中有一个表:

CREATE TABLE `yapial` (
  `user_id` int(11) NOT NULL,
  `username` varchar(255) DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `status` tinyint(10) DEFAULT NULL,
  `date` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

状态 和日期 列索引。我运行 sql 代码:

select status, count(*) from user_details group by status

并成功运行 0.34 毫秒。但我想添加一个日期限制=>

select date, status, count(*) from user_details 
where date between '2017-01-25 18:13:50' and '2017-01-29 18:13:50'
group by status

成功运行 6.52 秒。时间限制太高了。我想减少运行时间。我该如何改进？

Answer 1

耗时 0.34 毫秒的查询可能是从 MySQL query cache 获取结果的.查询不太可能这么快。

我认为最好的优化是如果你有一个两列索引。有两种可能:

alter table user_details
  add key (status, date),
  add key (date, status);

如果使用第一个索引，它会进行索引扫描但会避开临时表:

explain select sql_no_cache date, status, count(*) 
  from user_details use index (status) 
  where date between '2017-01-25 18:13:50' and '2017-01-29 18:13:50'
  group by status\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_details
   partitions: NULL
         type: index
possible_keys: date,status
          key: status
      key_len: 7
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where; Using index

如果使用第二个索引，它将使用该索引进行范围查找，按日期范围匹配行，但会产生一个临时表。

root@localhost [test] > explain select sql_no_cache date, status, count(*) 
    from user_details use index (date) 
    where date between '2017-01-25 18:13:50' and '2017-01-29 18:13:50' 
    group by status\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_details
   partitions: NULL
         type: range
possible_keys: date,status
          key: date
      key_len: 5
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index; Using temporary; Using filesort

哪个索引最好取决于有多少行符合条件。