mysql - 为什么 MySQL COUNT 函数有时会进行全表扫描,而有时会使用索引?又该如何避免呢?
问题描述
在我当前的一个网络项目中,我们正在实现一个复杂的搜索功能。作为该搜索功能的一部分,我们使用 MySQLCOUNT
函数来返回匹配结果的数量。
结果,我们遇到了性能问题。在获取实际结果列表时,MySQL 正确使用了我们设置的索引并很快返回结果。但是,在使用COUNT
查询时,有时返回的结果非常缓慢。在检查各种搜索查询的执行计划时,我们发现有时COUNT
查询会进行全表扫描。其他时候,尽管查询逻辑实际上相同,但查询使用的是索引。我们似乎没有注意到任何区分两者的特定模式。
下面是一个不进行全表扫描的查询示例:
select COUNT(DISTINCT text.name) AS count
from `text_epigraphy`
inner join `text` ON `text`.`uuid` = `text_epigraphy`.`text_uuid`
inner join `hierarchy` ON `hierarchy`.`uuid` = `text_epigraphy`.`text_uuid`
inner join `text_epigraphy` as `t1` ON `t1`.`text_uuid` = `text_epigraphy`.`text_uuid`
and `t1`.`reading_uuid` in ('01f1e805-1278-ec9b-9f69-fced97bc923e',
'07a120bc-02ec-c1ac-e0ba-532de39766ed', '126f978b-bd99-40f0-8f3b-d2bcec1ed3fe',
'44ec304e-71f4-4995-a30d-0ca6d3bec95a', '4a1d8673-9e30-2d1e-7b87-453dec2886db',
'bce40e36-d6eb-c44a-d114-8c7653a0e68c', 'c9083b77-6122-7933-ea21-63d3777749f3' )
and t1.char_on_tablet=text_epigraphy.char_on_tablet + 1
and t1.line=text_epigraphy.line
inner join `text_epigraphy` as `t2` ON `t2`.`text_uuid` = `text_epigraphy`.`text_uuid`
and `t2`.`reading_uuid` in ('3fc156dc-e831-493e-5dc1-84a547aeb4fa',
'70f9be19-62b6-3fe8-ddda-32bd50a8d36e' )
and t2.char_on_tablet=text_epigraphy.char_on_tablet + 2
and t2.line=text_epigraphy.line
inner join `text_epigraphy` as `t3` ON `t3`.`text_uuid` = `text_epigraphy`.`text_uuid`
and `t3`.`reading_uuid` in ('1ee91402-ebb0-3be9-cc38-9d4187816031',
'25a44259-fe7a-2b73-6e2c-02171c924805', 'a23fd531-c796-353e-4a53-54680248438a',
'd55fa6ad-c523-2e33-6378-b4f2e2a020f1' )
and t3.char_on_tablet=text_epigraphy.char_on_tablet + 3
and t3.line=text_epigraphy.line
where `text_epigraphy`.`reading_uuid` in ('6c0e47d0-00aa-26fb-e184-07038ca64323',
'd8904652-f049-11f9-3f7a-038f1e3b6055', 'eca27c41-d3ca-417c-15e0-db5353ddaefb' )
and 1 = 1
and (1 = 1
or 1 = 0)
limit 1
然而这个查询正在进行全表扫描:
select COUNT(DISTINCT text.name) AS count
from `text_epigraphy`
inner join `text` ON `text`.`uuid` = `text_epigraphy`.`text_uuid`
inner join `hierarchy` ON `hierarchy`.`uuid` = `text_epigraphy`.`text_uuid`
inner join `text_epigraphy` as `t1` ON `t1`.`text_uuid` = `text_epigraphy`.`text_uuid`
and `t1`.`reading_uuid` in ('3fc156dc-e831-493e-5dc1-84a547aeb4fa')
and t1.char_on_tablet=text_epigraphy.char_on_tablet + 1
and t1.line=text_epigraphy.line
inner join `text_epigraphy` as `t2` ON `t2`.`text_uuid` = `text_epigraphy`.`text_uuid`
and `t2`.`reading_uuid` in ('1ee91402-ebb0-3be9-cc38-9d4187816031',
'25a44259-fe7a-2b73-6e2c-02171c924805', 'a23fd531-c796-353e-4a53-54680248438a',
'd55fa6ad-c523-2e33-6378-b4f2e2a020f1' )
and t2.char_on_tablet=text_epigraphy.char_on_tablet + 2
and t2.line=text_epigraphy.line
where `text_epigraphy`.`reading_uuid` in ('c9083b77-6122-7933-ea21-63d3777749f3')
and 1 = 1
and (1 = 1
or 1 = 0)
limit 1
就像我说的,我们不能完全弄清楚为什么某些搜索在使用时会进行全表扫描,COUNT
但这会导致搜索速度明显变慢。如果有人可以帮助我们找出导致差异的原因以及我们如何能够避免全表扫描或至少优化查询。
解决方案
你不能删除hierarchy
吗?
存在哪些索引text_epigraphy
?这看起来很有用:
INDEX(line, char_on_tablet, reading_uuid, text_uuid)
开text
: INDEX(uuid, name)
之后,请提供EXPLAIN SELECT
;然后我会看看你的问题。
推荐阅读
- python-3.x - 在给定条件的情况下增加python中的for循环
- r - pivot_longer 与单独的
- python - Python:从 type 0 转换为 type 1 midi
- c - 即使字符串不包含任何空白字符,isspace() 函数也会返回 true
- javascript - 使用 Jest 进行反应测试 - onChange 从未调用过 Checkbox
- javascript - 用于查找特定列中最后一个单元格总和的脚本
- revit-api - Revit API - 如何检查打开的事务、子事务或组事务
- php - 如何在 PHP 中安全地将购物篮订单和付款信息发送到 PayPal?
- python - 熊猫数据框 isin() 系列
- python - OSError:异常:访问冲突读取 0x000002369EF14000