由于查询而导致性能下降？
由于查询而导致性能下降？
提问于 2014-01-27 06:50:59
请帮助我重写以下查询。
由于下面的query.in，我们正在使用子查询，因此我们会出现性能问题。请帮助我重新写没有子查询。
查询是：
select
sum(a.order_count)
from
(select
count(cart_id) as order_count, user_id
from
carts_archive
where
order_date > '2013-01-21 00:00:01'
and user_id is not null
group by user_id
order by order_count desc) a
where
a.order_count > 1;我们也无法检索解释计划。
解释计划：
解释计划：
+----+-------------+---------------+-------+--------------------------------------------+------------------------+---------+------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+--------------------------------------------+------------------------+---------+------+---------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1436430 | Using where |
| 2 | DERIVED | carts_archive | range | nk_cart_ach_user_id,pk_cart_ach_order_date | pk_cart_ach_order_date | 9 | NULL | 3552006 | Using where; Using temporary; Using filesort |
+----+-------------+---------------+-------+--------------------------------------------+------------------------+---------+------+---------+----------------------------------------------+
2 rows in set (2 min 50.33 sec)表结构：
mysql> show create table carts_archive\G
*************************** 1. row ***************************
Table: carts_archive
Create Table: CREATE TABLE `carts_archive` (
`row_mod` datetime DEFAULT NULL,
`row_create` datetime DEFAULT NULL,
`order_date` datetime DEFAULT NULL,
`billing_zip` varchar(10) COLLATE latin1_bin DEFAULT NULL,
`billing_address` varchar(200) COLLATE latin1_bin DEFAULT NULL,
`billing_home_phone` varchar(50) COLLATE latin1_bin DEFAULT NULL,
`billing_email` varchar(100) COLLATE latin1_bin DEFAULT NULL,
`status` varchar(30) COLLATE latin1_bin DEFAULT NULL,
`website_id` varchar(50) COLLATE latin1_bin DEFAULT NULL,
`discount_program` varchar(20) COLLATE latin1_bin DEFAULT NULL,
`credit_card_exp_year` varchar(4) COLLATE latin1_bin DEFAULT NULL,
`billing_country` varchar(50) COLLATE latin1_bin DEFAULT NULL,
`add_client_flag` varchar(1) COLLATE latin1_bin DEFAULT NULL,
`billing_last_name` varchar(50) COLLATE latin1_bin DEFAULT NULL,
`billing_work_phone` varchar(50) COLLATE latin1_bin DEFAULT NULL,
`total_charge` float(10,2) DEFAULT NULL,
`add_newsletter_flag` varchar(1) COLLATE latin1_bin DEFAULT NULL,
`cart_id` int(11) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`discount_first_name` varchar(20) COLLATE latin1_bin DEFAULT NULL,
`markcode` int(11) DEFAULT NULL,
`discount_account_junk` varchar(20) COLLATE latin1_bin DEFAULT NULL,
`gift_cert_junk` varchar(30) COLLATE latin1_bin DEFAULT NULL,
`credit_card_name` varchar(50) COLLATE latin1_bin DEFAULT NULL,
`billing_work_phone_ext` varchar(10) COLLATE latin1_bin DEFAULT NULL,
`billing_state` varchar(50) COLLATE latin1_bin DEFAULT NULL,
`billing_first_name` varchar(50) COLLATE latin1_bin DEFAULT NULL,
`order_id` varchar(30) COLLATE latin1_bin DEFAULT NULL,
`discount_last_name` varchar(20) COLLATE latin1_bin DEFAULT NULL,
`credit_card_exp_month` varchar(2) COLLATE latin1_bin DEFAULT NULL,
`credit_card_number` varchar(20) COLLATE latin1_bin DEFAULT NULL,
`billing_city` varchar(100) COLLATE latin1_bin DEFAULT NULL,
`credit_card_type` varchar(20) COLLATE latin1_bin DEFAULT NULL,
`discount_account` varchar(50) COLLATE latin1_bin DEFAULT NULL,
`asnbuyer` varchar(70) COLLATE latin1_bin DEFAULT NULL,
`buyercookie` varchar(70) COLLATE latin1_bin DEFAULT NULL,
`expire_date` datetime DEFAULT NULL,
`security_string` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`user_information` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`track_id` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`gift_cert` varchar(255) COLLATE latin1_bin DEFAULT NULL,
`billing_address2` varchar(100) COLLATE latin1_bin DEFAULT NULL,
`visited_signup` varchar(1) COLLATE latin1_bin DEFAULT NULL,
`paypal_email_junk` varchar(127) COLLATE latin1_bin DEFAULT NULL,
`used_saved_cc_flag` varchar(1) COLLATE latin1_bin DEFAULT NULL,
`paypal_auth_amount_junk` float DEFAULT NULL,
`bml_auth_code_junk` varchar(20) COLLATE latin1_bin DEFAULT NULL,
`bml_approve_amount_junk` float DEFAULT NULL,
`bml_account_id_junk` varchar(30) COLLATE latin1_bin DEFAULT NULL,
`bml_order_number_junk` varchar(22) COLLATE latin1_bin DEFAULT NULL,
`alt_reference_num` varchar(50) COLLATE latin1_bin DEFAULT NULL,
`alt_account_id` varchar(200) COLLATE latin1_bin DEFAULT NULL,
`alt_approval_amount` float DEFAULT NULL,
`alt_points_used` float DEFAULT NULL,
`alt_auth_id` varchar(30) COLLATE latin1_bin DEFAULT NULL,
`alt_redemption_rate` float DEFAULT NULL,
`cvv_response_code` varchar(50) COLLATE latin1_bin DEFAULT NULL,
`cvv_indicator_value` varchar(1) COLLATE latin1_bin DEFAULT NULL,
`language_id` varchar(10) COLLATE latin1_bin DEFAULT 'ENUS',
`gc_trans_id` varchar(20) COLLATE latin1_bin DEFAULT NULL,
`gc_auth_date` datetime DEFAULT NULL,
`gc_pin` varchar(20) COLLATE latin1_bin DEFAULT NULL,
`gift_cert_value` float DEFAULT NULL,
UNIQUE KEY `pk_cart_ach_cart_id` (`cart_id`),
KEY `nk_cart_ach_user_id` (`user_id`),
KEY `pk_cart_ach_order_date` (`order_date`),
KEY `carts_archive_n1` (`row_mod`),
KEY `order_id_n1` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)回答 3
Stack Overflow用户
回答已采纳
发布于 2014-01-27 06:57:33
尝试按下面的方式创建索引，然后查看EXPLAIN计划
CREATE INDEX idx_order_date_user_id ON carts_archive(order_date,user_id);Stack Overflow用户
发布于 2014-01-27 09:13:43
我不认为在子查询中使用order子句。有关表中包含索引的记录数量的信息将有所帮助，或者解释计划将有所帮助。
也尝试使用下面稍微修改过的sql。
选择sum(a.order_count) from (选择count(cart_id)作为order_count，从carts_archive选择user_id，其中order_date > '2013-01-21 00:00:01‘和user_id是非空组，由有计数(Card_id)>1)a
Stack Overflow用户
发布于 2014-01-27 07:21:37
我对这个问题的了解是有限的，但是oce当我们遇到类似的问题时，我发现SQL SERVER 2012 dosent有自动索引或在它未指定时与索引相关的一些问题，因此添加主键或任何类型的索引，如果没有适当的索引顺序，可能会出现性能问题。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/21374627
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号