SELECT *
FROM (
SELECT temp.init_shop_name AS front_name, temp.relate_shop_name AS back_name
, COUNT(*) / vipNum.total * 100 AS support
, COUNT(*) / temp2.num * 100 AS confidence
FROM (
SELECT ssdr.vip_code, ssdr.initial_shop_code AS init_shop_code, shop.name AS init_shop_name, comm.code AS init_comm_code, comm.name AS init_comm_name
, cate.code AS init_comm_sub_code, cate.name AS init_comm_sub_name, shop2.code AS relate_shop_code, shop2.name AS relate_shop_name, comm2.code AS relate_comm_code
, comm2.name AS relate_comm_name, cate2.code AS relate_comm_sub_code, cate2.name AS relate_comm_sub_name
FROM sum_sale_daily_relation ssdr
INNER JOIN r_shop_category shop_cate ON shop_cate.shop_code = ssdr.initial_shop_code
INNER JOIN dict_category cate ON cate.code = shop_cate.category_code
INNER JOIN dict_commercial comm ON comm.code = cate.commercial_code
INNER JOIN origin_rent_shop shop ON shop.code = ssdr.initial_shop_code
INNER JOIN r_shop_category shop_cate2 ON shop_cate2.shop_code = ssdr.relation_shop_code
INNER JOIN dict_category cate2 ON cate2.code = shop_cate2.category_code
INNER JOIN dict_commercial comm2 ON comm2.code = cate2.commercial_code
INNER JOIN origin_rent_shop shop2 ON shop2.code = ssdr.relation_shop_code
WHERE '2017-09-19 00:00:00.0' <= ssdr.date
AND ssdr.date < '2017-12-19 00:00:00.0'
GROUP BY ssdr.vip_code, ssdr.initial_shop_code, ssdr.relation_shop_code
) temp
INNER JOIN (
SELECT COUNT(DISTINCT vip_code) AS total
FROM sum_vip_consume_daily
WHERE '2017-09-19 00:00:00.0' <= date
AND date < '2017-12-19 00:00:00.0'
) vipNum
INNER JOIN (
SELECT shop_cate.shop_code AS shopCode, COUNT(*) AS num
FROM sum_sale_daily_relation ssdr
INNER JOIN r_shop_category shop_cate ON shop_cate.shop_code = ssdr.initial_shop_code
INNER JOIN dict_category cate ON cate.code = shop_cate.category_code
WHERE '2017-09-19 00:00:00.0' <= ssdr.date
AND ssdr.date < '2017-12-19 00:00:00.0'
GROUP BY shop_cate.shop_code
) temp2
ON temp2.shopCode = temp.init_shop_code
GROUP BY temp.init_shop_code, temp.relate_shop_code
) temp3
ORDER BY confidence DESC
LIMIT 0, 40
這是樓主寫的sql，其中 sum_sale_daily_relation，sum_vip_consume_daily是大資料表，上百萬的資料。求如何優化，索引的如何建。
現在這兩個表的索引
CREATE TABLE `sum_sale_daily_relation` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`date` date NOT NULL COMMENT '日時間',
`vip_code` varchar(32) NOT NULL,
`initial_shop_code` varchar(32) NOT NULL COMMENT '初始商戶編號',
`relation_shop_code` varchar(32) NOT NULL COMMENT '關聯商戶編號',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`last_update` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`delete_flag` tinyint(1) unsigned DEFAULT '0',
`delete_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `key_sum_sale_daily_relation_date_vip_code` (`date`,`vip_code`),
KEY `idx_initialshopcode` (`initial_shop_code`)
) ENGINE=InnoDB AUTO_INCREMENT=3145681 DEFAULT CHARSET=utf8 COMMENT='銷售關聯日統計表';
CREATE TABLE `sum_vip_consume_daily` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`vip_code` varchar(32) NOT NULL COMMENT '會員號',
`date` date NOT NULL COMMENT '日時間',
`num` int(11) unsigned DEFAULT NULL COMMENT '消費筆數',
`amount` float(14,4) NOT NULL COMMENT '成交額，退貨為負值',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`last_update` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`delete_flag` tinyint(1) unsigned DEFAULT '0',
`delete_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=3999564 DEFAULT CHARSET=utf8 COMMENT='會員消費日統計';
uj5u.com熱心網友回復：
建議按照你的連表順序 一個個連上除錯 確定哪個地方最慢轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/101013.html
標籤：MySQL