MYSQL计数(Distinct)分组依据

Question

此选择保持超时：

SELECT COUNT(DISTINCT `invoices`) 
FROM `data` 
WHERE date BETWEEN '2013-07-01' AND '2014-06-30' 
GROUP BY `store`

我对发票、日期、商店和发票、商店、日期进行了索引。感谢您的帮助。

Answer 1

尝试

SELECT `store`, COUNT(*) FROM 
(SELECT `store`, `invoices`
FROM `data` 
WHERE date BETWEEN '2013-07-01' AND '2014-06-30' 
GROUP BY `store`, `invoices`
) tbl
GROUP BY `store`

这是一把小提琴，没有date列，请检查。。。

http://sqlfiddle.com/#！9/fb83c/5/0

Answer 2

我也遇到了同样的问题。我想你在用一张大桌子工作。我通过更改配置文件中的一些值来解决这个问题：

key_buffer = 128M
thread_stack = 128K
thread_cache_size = 8
table_cache = 8192
max_heap_table_size = 256M
query_cache_limit = 4M
query_cache_size = 512M
innodb_file_per_table
innodb_buffer_pool_size = 4G 
innodb_log_file_size = 512M
skip_name_resolve
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M 
key_buffer_size         = 64M
sort_buffer_size        = 1M
tmp_table_size          = 256M
max_heap_table_size     = 512M
read_buffer_size        = 64K
read_rnd_buffer_size    = 64K
myisam_sort_buffer_size = 256M

但这些价值观会随着你的电脑和工作而变化。我建议你搜索一下"大数据的mysql配置"。你会发现一些关于它们含义的信息。顺便说一下，您的表中也应该有一个主键。我认为这也会影响表现。最后，您应该检查mysql中的首选项。您可能需要在sql编辑器部分增加mysql会话设置。我希望它能有所帮助。

Answer 3

试试这个

SELECT store, COUNT(DISTINCT invoices) 
FROM data 
WHERE date BETWEEN '2013-07-01' AND '2014-06-30' 
GROUP BY store