我有 3 張桌子，
testdata1: id (pri) -> 1000000 rows
testdata2: id (pri), channel (indexed), -> 10000 rows
testdata3: id (pri) -> 1000 rows
在執行以下查詢時，我對 testdata2 進行了掃描。
explain format=tree
select *
from testdata1
inner join testdata2 on testdata1.id = testdata2.channel
inner join testdata3 on testdata2.channel = testdata3.id
where testdata1.id < 100;
EXPLAIN: -> Nested loop inner join (cost=8014.20 rows=9984)
-> Nested loop inner join (cost=4519.80 rows=9984)
-> Table scan on testdata2 (cost=1025.40 rows=9984)
-> Filter: ((testdata1.id < 100) and (testdata1.id = testdata2.`channel`)) (cost=0.25 rows=1)
-> Single-row index lookup on testdata1 using PRIMARY (id=testdata2.`channel`) (cost=0.25 rows=1)
-> Filter: (testdata2.`channel` = testdata3.id) (cost=0.25 rows=1)
-> Single-row index lookup on testdata3 using PRIMARY (id=testdata2.`channel`) (cost=0.25 rows=1)
為什么 mysql 不使用testdata2(channel)列的索引？
*更新
運行后analyze table testdata2，mysql使用了索引。
創建索引后是否需要使用analyze table命令？
EXPLAIN: -> Nested loop inner join (cost=241.34 rows=156)
-> Nested loop inner join (cost=186.85 rows=156)
-> Filter: (testdata1.id < 100) (cost=20.09 rows=99)
-> Index range scan on testdata1 using PRIMARY over (id < 100) (cost=20.09 rows=99)
-> Index lookup on testdata2 using a_temp_index (channel=testdata1.id), with index condition: (testdata1.id = testdata2.`channel`) (cost=1.53 rows=2)
-> Filter: (testdata2.`channel` = testdata3.id) (cost=0.25 rows=1)
-> Single-row index lookup on testdata3 using PRIMARY (id=testdata2.`channel`) (cost=0.25 rows=1)
uj5u.com熱心網友回復：
https://dev.mysql.com/doc/refman/8.0/en/create-index.html說：
啟用該
innodb_stats_persistent設定ANALYZE TABLE后，在該表上創建索引后運行 InnoDB 表的陳述句。
此設定默認開啟，所以是的，建議ANALYZE TABLE在創建索引后運行。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/517925.html
標籤：mysqlsql查询优化
上一篇：無法執行塊