我有一个MySQL请求,我每两分钟运行一次,我在parralel中执行4次,这意味着每两分钟这个请求被执行4次(参数中的值不同(,一段时间后,我的数据库过载,我试图做一堆更新来优化它,但我的SQL知识到了最后,所以我指的是你, 我该怎么做来优化这个请求?
SELECT bill.* FROM billing bill
INNER JOIN subscriber s ON (s.subscriber_id = bill.subscriber_id)
INNER JOIN subscription sub ON(s.subscriber_id = sub.subscriber_id)
WHERE s.status = 'C'
AND bill.subscription_id = sub.subscription_id
AND sub.package_name = 598
AND sub.renewable = 1
AND bill.billing_value IN ('not_ok BILLTOBE','not_ok BILL010 2','not_ok BILL010','not_ok BILL010 3')
AND sub.store = 'MTargetStore'
AND (
SELECT bill2.billing_date
FROM billing bill2
WHERE bill2.subscriber_id = bill.subscriber_id
AND bill2.subscription_id = bill.subscription_id
AND bill2.billing_value NOT IN('not_ok BILL010','not_ok BILL010 2','not_ok BILL010 3')
ORDER BY bill2.billing_date DESC LIMIT 1
) = bill.billing_date LIMIT 300
显示订阅订阅者和计费的创建表(没有不必要的参数(
订阅
CREATE TABLE `subscription` (
`subscription_id` int(11) NOT NULL AUTO_INCREMENT,
`subscriber_id` int(11) NOT NULL,
`renewable` tinyint(1) DEFAULT '0',
`store` varchar(127) DEFAULT 'GooglePlay',
`package_name` text,
PRIMARY KEY (`subscription_id`),
KEY `subscription_subscriber_id_subscriber_subscriber_id_idx` (`subscriber_id`),
KEY `renewableindex` (`renewable`),
KEY `subscription_index1` (`renewable`,`subscriber_id`,`subscription_id`),
KEY `subscription_index2` (`renewable`,`subscription_id`,`subscriber_id`),
CONSTRAINT `subscription_subscriber_id_subscriber_subscriber_id`
FOREIGN KEY (`subscriber_id`)
REFERENCES `subscriber` (`subscriber_id`)
ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2282403 DEFAULT CHARSET=utf8
订户
CREATE TABLE `subscriber` (
`udid` varchar(100) NOT NULL,
`subscriber_id` int(11) NOT NULL AUTO_INCREMENT,
`status` varchar(3) DEFAULT 'CE',
`consumer_id` varchar(250) DEFAULT NULL,
PRIMARY KEY (`subscriber_id`),
UNIQUE KEY `udid_UNIQUE` (`udid`),
UNIQUE KEY `subscriber_os_email_key` (`email`,`os`),
UNIQUE KEY `consumer_id_UNIQUE` (`consumer_id`),
KEY `sub_id_status` (`status`,`subscriber_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3655055 DEFAULT CHARSET=utf8
计费
CREATE TABLE `billing` (
`billing_id` int(11) NOT NULL AUTO_INCREMENT,
`billing_date` timestamp NULL DEFAULT NULL,
`subscriber_id` int(11) NOT NULL,
`billing_value` varchar(40) DEFAULT NULL,
`subscription_id` int(11) DEFAULT NULL,
PRIMARY KEY (`billing_id`),
KEY `billing_subscriber_id_subscriber_subscriber_id_idx` (`subscriber_id`),
KEY `subscriptionid` (`subscription_id`),
CONSTRAINT `billing_subscriber_id_subscriber_subscriber_id`
FOREIGN KEY (`subscriber_id`) REFERENCES `subscriber` (`subscriber_id`)
ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=9013743 DEFAULT CHARSET=utf8
最后是初始请求的解释:
1 PRIMARY sub ref PRIMARY,subscription_subscriber_id_subscriber_subscriber_id_idx,renewableindex,subscription_index1,subscription_index2 renewableindex 2 const 171836 Using where
1 PRIMARY s eq_ref PRIMARY,sub_id_status PRIMARY 4 Infinitgame.sub.subscriber_id 1 Using where
1 PRIMARY bill ref billing_subscriber_id_subscriber_subscriber_id_idx,subscriptionid billing_subscriber_id_subscriber_subscriber_id_idx 4 Infinitgame.sub.subscriber_id 14 Using where
2 DEPENDENT SUBQUERY bill2 ref billing_subscriber_id_subscriber_subscriber_id_idx,subscriptionid billing_subscriber_id_subscriber_subscriber_id_idx 4 Infinitgame.bill.subscriber_id 14 Using where; Using filesort
(或(
1 PRIMARY sub ref -- renewableindex 2 const 171836 Using where
1 PRIMARY s eq_ref -- PRIMARY 4 Infinitgame.sub.subscriber_id 1 Using where
1 PRIMARY bill ref -- bsss 4 Infinitgame.sub.subscriber_id 14 Using where
2 DEPENDENT SUBQUERY bill2 ref -- bsss 4 Infinitgame.bill.subscriber_id 14 Using where; Using filesort
而不是
SELECT bill2.billing_date
FROM billing bill2
WHERE ...
ORDER BY bill2.billing_date DESC
LIMIT 1
说
SELECT MAX(bill2.billing_date)
FROM billing bill2
WHERE ...
你真的需要 300 行吗? 我怀疑这会导致对该子查询进行 300 次评估。
INNER JOIN subscriber s ON (s.subscriber_id = bill.subscriber_id)
INNER JOIN subscription sub ON(s.subscriber_id = sub.subscriber_id)
WHERE s.status = 'C'
AND bill.subscription_id IN ( ... )
AND bill.subscription_id = sub.subscription_id
AND sub.package_name = 598
AND sub.renewable = 1
AND sub.store = 'MTargetStore'
有这些索引(不清楚哪个最有用(:
s: INDEX(status, subscriber_id) -- (indexing flags is rarely useful)
bill: INDEX(subscription_id)
sub: INDEX(package_name, store, renewable, subscriber_id, subscription_id)
sub: INDEX(package_name, store, renewable, subscription_id, subscriber_id)
sub
的两个索引 -- 取决于优化程序决定访问表的顺序。
AND bill.subscription_id = sub.subscription_id
这不应该是ON
的一部分吗? 它似乎定义了这种关系。
一个没有和ORDER BY
的LIMIT
-- 你会得到一组不可预测的行;可以吗?
要进一步讨论这个问题,请提供每个表的SHOW CREATE TABLE
,并EXPLAIN SELECT ...
。
另外,价值是什么
SELECT COUNT(DISTINCT subscriber_id, subscription_id)
FROM billing
WHERE billing_value NOT IN('not_ok BILL010','not_ok BILL010 2',
'not_ok BILL010 3')
答案可能会导致执行子查询的不同方法。
另外,这可以代替那个NOT IN
吗?
WHERE billing_value NOT LIKE 'not_ok BILL010%'
将字符串(或TEXT
(与数字字符串进行比较时,请引用字符串:
AND sub.package_name = "598"
没有引号,使用package_name
的索引就无法有效使用。