如何優(yōu)化索引？

yonghedawang

SQL1：SELECT COUNT(*) FROM table1 WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954'
SQL2：select * from table1 WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954' order by lastpost desc limit 0,36

SELECT COUNT(*) FROM table1 有32萬條數(shù)據(jù)
SELECT COUNT(*) FROM table1 WHERE  zplb0='954'  有10萬條數(shù)據(jù)
SELECT COUNT(*) FROM table1 WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954'  有10萬條數(shù)據(jù)

索引：KEY `zplb0` (`zplb0`,`displayorder`,`lastposter`)   

explain SELECT COUNT(*) FROM cdb_threads WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954'  
結(jié)果 Using where; Using index

explain select * from cdb_threads WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954' order by lastpost desc limit 0,36

結(jié)果 type = all ; extra  = Using where; Using filesort


sql2執(zhí)行要1秒多，非常慢。

請問這樣的sql如何優(yōu)化好。謝謝！

飛鴻無痕

回復(fù) 1# yonghedawang

對于這個(gè)，可以考慮建立索引+其他解決辦法共同來解決。
關(guān)于建立索引，可以考慮在zplb0和lastpost建立索引，這樣能兼顧到SQL1和SQL2
index idx_zplb0_lastpost(`zplb0`,`lastpost `) 。
關(guān)于其他解決辦法，比如SQL1類型的統(tǒng)計(jì)可以使用其他方式實(shí)現(xiàn)統(tǒng)計(jì)，而不需要每次都統(tǒng)計(jì)10萬多的數(shù)據(jù)，可以使用一個(gè)額外的表進(jìn)行統(tǒng)計(jì)。比如凌晨使用事件定期統(tǒng)計(jì)一次，然后使用類似觸發(fā)器的功能進(jìn)行累加。對于這種統(tǒng)計(jì)類型的語句性能會(huì)好很多。尤其是統(tǒng)計(jì)結(jié)果不需要非常精確的時(shí)候。

yonghedawang

謝謝你的回復(fù)，sql1的效率本身不低。sql2要一秒多，我現(xiàn)在還沒有想到好的辦法。

wangjj20

or條件可以使用類似
select * from table1 WHERE displayorder>='0'  and zplb0='954'
union all
select * from table1  where displayorder='-2' and zplb0='954'
這樣的語句。
索引可以嘗試displayorder+zplb0+lastpost

另外看下profile，看下慢在哪個(gè)部分。

Jack_boo

mark下關(guān)注下結(jié)果

ruochen

count(*)和*，而且后面的選擇性很差

第二個(gè)sql用index idx_zplb0_lastpost(`zplb0`,`lastpost `) 做一年應(yīng)該比較好

G8bao7

回復(fù) 1# yonghedawang


1、由于滿足where條件的記錄數(shù)占全表的比重比較大(10W/32W)，而且sql2是select *，也不能使用索引覆蓋掃描。
    優(yōu)化器認(rèn)為索引還沒有全表掃描快
2、由于where 條件中displayorder是范圍查找，排序并不會(huì)使用`zplb0`索引(`zplb0`,`displayorder`都是等值查找才會(huì)使用)，
    所以order by lastpost會(huì)引起Using filesort

sql2優(yōu)化建議：
select t2.* from (select 主鍵ID from table1 WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954' order by lastpost desc limit 0,36 ) as t1
,table1 AS t2
where t1.主鍵ID=t2.主鍵ID;