加索引后MySQL select count(1) 查询很慢

问题描述

问 题

SQL1：
SELECT count(1) from(
SELECT a.id AS "id"
FROM ims_customer a
left join ims_customer_bind_user cbu on cbu.customer_id=a.id
LEFT JOIN sys_user u ON u.id = cbu.user_id
LEFT JOIN sys_office o ON o.id = u.office_id
LEFT JOIN sys_user customerSalesServiceUser ON customerSalesServiceUser.id = a.customer_sales_service
WHERE a.del_flag = 0
and (cbu.del_flag = 0 or cbu.del_flag is null)
ORDER BY a.update_date
) as ww

SQL2：
SELECT a.id AS "id", a.mailing_contact AS "mailingContact", a.company_id AS "companyId", a.customer_code AS "customerCode",
a.customer_name AS "customerName", a.customer_simple_name AS "customerSimpleName",
a.customer_en_name AS "customerEnName", a.customer_property AS "customerProperty", a.country_area AS "countryArea",
a.customer_type AS "customerType", a.contact_person AS "contactPerson", a.contact_address AS "contactAddress",
a.zip_code AS "zipCode", a.tel_phone1 AS "telPhone1", a.tel_phone2 AS "telPhone2", a.fax AS "fax",
a.email AS "email", a.bank AS "bank", a.bank_account AS "bankAccount", a.film_ticket_type AS "filmTicketType",
a.is_zhongliang AS "isZhongliang", a.is_sale_achievement AS "isSaleAchievement",
a.sale_achievement_remarks AS "saleAchievementRemarks", a.is_account_limit AS "isAccountLimit",
a.customer_service AS "customerService", a.agree_on_receive_money_datetime AS "agreeOnReceiveMoneyDatetime",
a.receive_money_month AS "receiveMoneyMonth", a.is_import_client AS "isImportClient",
a.well_being_deal_with AS "wellBeingDealWith", a.is_physical_examination AS "isPhysicalExamination",
a.is_beforehand_voucher AS "isBeforehandVoucher", a.is_remittance_error AS "isRemittanceError",
a.is_fixed_pay_later AS "isFixedPayLater"
FROM ims_customer a
left join ims_customer_bind_user cbu on cbu.customer_id=a.id
LEFT JOIN sys_user u ON u.id = cbu.user_id
LEFT JOIN sys_office o ON o.id = u.office_id
LEFT JOIN sys_user customerSalesServiceUser ON customerSalesServiceUser.id = a.customer_sales_service
WHERE a.del_flag = 0
and (cbu.del_flag = 0 or cbu.del_flag is null)
ORDER BY a.update_date
DESC limit 10
当前只有2000数据。。。。
问题：
加索引前，SQL1查询大约需要600ms,但是SQL2查询需要2.2s;
给user_id、customer_id加索引后，SQL1查询大约需要2.8s,但是SQL2查询只要28ms就搞定了。
为什么索引前与索引后会有这样的差别？
为什么索引后select count(1)对查询性能影响这么大？
想请教下各位大牛对SQL1有什么优化建议？

补充
索引前：
sql1

sql2

索引后：
sql1

sql2

解决方案

count(1)的语句，如果在加索引后，查看执行计划确实使用了索引，因数据量小，出现不如全表扫描慢的情况是合理的。

建议把加索引前后的执行计划进行对比，确认查询方式的变化。

这篇关于加索引后MySQL select count(1) 查询很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！