我有以下疑问
SELECT `users`.`id`, `users`.`vPlatform`, `users`.`vLanguage`
FROM `users` FORCE INDEX (test1)
WHERE
EXISTS (
SELECT `orders`.* FROM `orders` WHERE `orders`.`is_deleted` = FALSE
AND `orders`.`iCourierId` = `users`.`id`
AND `orders`.`vStatus` = 'delivered'
AND `orders`.`updated_at` >= '2022-12-06 09:05:27'
)
AND `users`.`login_status` != FALSE
AND `users`.`vPlatform` IN ('300', '301')
AND NOT ((`users`.`vDeviceToken` = '' OR `users`.`vDeviceToken` IS NULL))
AND `users`.`vCurrency` = 'EGP'
AND `users`.`is_deleted` = FALSE
AND `users`.`id` BETWEEN 17395001 AND 17400000 ORDER BY `users`.`id` ASC;
它的explain analyze
显示以下内容
- 用于订单表的索引为
CourierId_idx (iCourierId,vStatus,updated_at,created_at)
-> Nested loop semijoin (cost=11633.55 rows=254) (actual time=0.743..8.440 rows=8 loops=1)
-> Filter: ((users.vPlatform in ('300','301')) and (users.vDeviceToken <> '') and (users.vDeviceToken is not null)) (cost=3636.72 rows=73) (actual time=0.059..4.504 rows=904 loops=1)
-> Index range scan on users using test1 over (17395001 <= id <= 17400000 AND NULL < login_status), with index condition: ((users.is_deleted = false) and (users.login_status <> false) and (users.vCurrency = 'EGP') and (users.id between 17395001 and 17400000)) (cost=3636.72 rows=5000) (actual time=0.057..4.257 rows=914 loops=1)
-> Filter: (orders.is_deleted = false) (cost=346.09 rows=3) (actual time=0.004..0.004 rows=0 loops=904)
-> Index lookup on orders using CourierId_idx (iCourierId=users.id, vStatus='delivered'), with index condition: (orders.updated_at >= TIMESTAMP'2022-12-06 09:05:27') (cost=346.09 rows=105) (actual time=0.004..0.004 rows=0 loops=904)
这表明orders.is_deleted
被过滤了,因为它不是使用的索引的一部分,将其添加到索引中会使下面的explain analyze
变慢
- 用于订单表的索引为
test3(iCourierId,vStatus,created_at,updated_at,is_deleted)
-> Nested loop semijoin (cost=4184.56 rows=92) (actual time=0.854..9.845 rows=8 loops=1)
-> Filter: ((users.vPlatform in ('300','301')) and (users.vDeviceToken <> '') and (users.vDeviceToken is not null)) (cost=3636.72 rows=73) (actual time=0.045..6.321 rows=904 loops=1)
-> Index range scan on users using test1 over (17395001 <= id <= 17400000 AND NULL < login_status), with index condition: ((users.is_deleted = false) and (users.login_status <> false) and (users.vCurrency = 'EGP') and (users.id between 17395001 and 17400000)) (cost=3636.72 rows=5000) (actual time=0.043..6.071 rows=914 loops=1)
-> Filter: ((orders.is_deleted = false) and (orders.updated_at >= TIMESTAMP'2022-12-06 09:05:27')) (cost=1.46 rows=1) (actual time=0.004..0.004 rows=0 loops=904)
-> Covering index lookup on orders using test3 (iCourierId=users.id, vStatus='delivered') (cost=1.46 rows=64) (actual time=0.003..0.004 rows=0 loops=904)
使用的索引包括orders.is_deleted
和orders.updated_at
,但它被过滤了,而orders.updated_at
从with index condition
移动到filter
为什么?
1条答案
按热度按时间guykilcj1#
除非您需要
vDeviceToken = ''
来表示不同的含义,否则请选择其中一个。使用OR
会降低性能。这些措施应有助于:
去掉
FORCE INDEX
和这些,如果你碰巧有它们:INDEX(login_status)
和INDEX(is_deleted)
。为了提高效率,索引中的列应该从用
=
测试的列开始。更多:Index Cookbook