有一张表,数据量很大,大概5G左右
现在有一个很简单的sql,类似下面的语句SELECT
col_name, SUM(count) AS value
FROM
table_name
WHERE
sys_date BETWEEN '2017-10-01' AND '2017-10-31'
GROUP BY col_name
ORDER BY value DESC是不是很简单,执行时间大概50秒
但是我换一种写法,先通过where得到一个结果集,然后在这结果集上做一次group bySELECT col_name, SUM(count) AS value
FROM
(
SELECT
col_name, count
FROM
table_name
WHERE
sys_date BETWEEN '2017-10-01' AND '2017-10-31'
) A
GROUP BY col_name
ORDER BY value DESC这样的执行结果只有4秒
为什么会是这样?Group BY 不是作用在where的结果集上的吗?但是我感觉第一个sql的执行顺序并不是这样,不然,两个类似的SQL为什么差别这么大?PS:
explain的结果没什么太大的差别,索引都没有用到
现在有一个很简单的sql,类似下面的语句SELECT
col_name, SUM(count) AS value
FROM
table_name
WHERE
sys_date BETWEEN '2017-10-01' AND '2017-10-31'
GROUP BY col_name
ORDER BY value DESC是不是很简单,执行时间大概50秒
但是我换一种写法,先通过where得到一个结果集,然后在这结果集上做一次group bySELECT col_name, SUM(count) AS value
FROM
(
SELECT
col_name, count
FROM
table_name
WHERE
sys_date BETWEEN '2017-10-01' AND '2017-10-31'
) A
GROUP BY col_name
ORDER BY value DESC这样的执行结果只有4秒
为什么会是这样?Group BY 不是作用在where的结果集上的吗?但是我感觉第一个sql的执行顺序并不是这样,不然,两个类似的SQL为什么差别这么大?PS:
explain的结果没什么太大的差别,索引都没有用到
解决方案 »
- source命令在dos下执行,而用C#中调用执行时报语法错误
- group by与group_concat结合使用总会造成结果的排序,怎么可以避免这个
- mysql if语句怎么写
- 求助:帮忙看一下下面的这个存储过程问题出在哪里?
- mysql C API中怎样判断table是否存在
- pgsql 登录是 connecting to database …… failed
- jsp連結資料庫更新問題
- 关于内存减少的问题
- 商品ID如何设计才能方便的扩展,比如从1亿的数据到10亿的数据
- 数据库安装报错
- 初学SQL求助语句,随机取出数据库一条 table 为 2018 的数据
- 数据库重启后的回滚异常,造成这张表删除操作一直拿不到锁,求解决办法!
这是第一个的explain"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "PRIMARY" "<derived2>" "ALL" \N \N \N \N "2576336" "Using temporary; Using filesort"
"1" "PRIMARY" "category_mapping" "ALL" \N \N \N \N "148" "Using where; Using join buffer (Block Nested Loop)"
"2" "DERIVED" "path_rule" "index" "INDEX_APPS,INDEX_APPS_WEEK" "INDEX_APPS" "306" \N "5152673" "Using where; Using temporary; Using filesort"
这是第二个的"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "PRIMARY" "<derived2>" "ALL" \N \N \N \N "2576336" \N
"1" "PRIMARY" "category_mapping" "ALL" \N \N \N \N "148" "Using where; Using join buffer (Block Nested Loop)"
"2" "DERIVED" "<derived3>" "ALL" \N \N \N \N "2576336" "Using temporary; Using filesort"
"3" "DERIVED" "path_rule" "ALL" "INDEX_APPS,INDEX_APPS_WEEK" \N \N \N "5152673" "Using where"
如果 table_name 是表, 理论上这两个的执行计划应该是一样的, 如果是查询 VIEW,那另当别论
如果对比执行计划确定是一样的话,那么可能是缓存,你可以多次执行对比下效率
按照你写的查询,只有一个条件,但里面 Extra 里面出现了两次 WHERE , 第一个查询还出现了两次 sort
正常的单表应该是一次 WHERE + 一次 SORT
(我在 5.7 版本上,童瑶查询模拟验证了一下,两种写法执行计划一样,只会有一条执行计划)
SELECT
A.execute_app AS name, IFNULL(category_mapping.view_name, execute_app) AS mappingName
FROM
(
SELECT
sys_date,
month_number,
execute_app, SUM(COUNT) AS value
FROM path_rule
WHERE month_number=10 AND YEAR = 2017)) AND execute_app IS NOT NULL AND LENGTH(execute_app) > 0
GROUP BY execute_app
ORDER BY value DESC)A
LEFT JOIN
category_mapping ON
A.execute_app = category_mapping.log_name AND category_mapping.kpi_name = 'path_rule_app'
ORDER BY A.value DESC
LIMIT 10 第二个SQL, group by 放外面的(快)
EXPLAIN
SELECT
A.execute_app AS name, IFNULL(category_mapping.view_name, execute_app) AS mappingName
FROM
(
SELECT execute_app, SUM(COUNT) AS value
FROM(
SELECT execute_app, COUNT
FROM
path_rule
WHERE month_number = 10 AND YEAR = 2017 AND execute_app IS NOT NULL AND LENGTH(execute_app) > 0 ) r
GROUP BY execute_app
ORDER BY value DESC)A
LEFT JOIN
category_mapping ON
A.execute_app = category_mapping.log_name AND category_mapping.kpi_name = 'path_rule_app'
LIMIT 10
Explain的结果上面贴过了
ORDER BY A.value DESC你把第2个查询加上这个再测试一下