3

MySQL group by 优化 - V2EX

 2 years ago
source link: https://www.v2ex.com/t/831164
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

V2EX  ›  MySQL

MySQL group by 优化

  guangzhouwuyanzu · 1 天前 · 1431 次点击

目的取一段时间内 uid 对应消耗，然后划分区间

发现 group by uid返回的数据越多越慢，这个从 SQL 下手有优化空间吗

MySQL 5.7

select
  elt(interval(total, null, 300), '-INF~300', '300~INF') as section,
  count(*) AS total
from (
  select
    uid,
    SUM(gold) as total
  from `table_name`
  where `time` > 1640966400 and `time` <= 1642176000 group by `uid`
) as `tmp` group by `section`;

-- 执行了 3-4 秒

返回结果：
-INF~300	46319
300~INF		15060

EXPLAIN 结果:

select_type table type possible_keys rows rows

PRIMARY <derived2> ALL

217073 Using temporary; Using filesort

DERIVED table_name index time,uid 434146 Using where

第 1 条附言  ·  1 天前

附上表结构

CREATE TABLE `table_name` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `time` int(11) unsigned NOT NULL DEFAULT '0',
  `uid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `gold` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `time` (`time`),
  KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK