高性能MySql進化論(十):查詢優化器的局限性

在“查詢優化器常用的方式”一文中列出了一些優化器常用的優化手段。查詢優化器在提供這些特性的同時，也存在一定的局限性，這些局限性往往會隨著MYSQL版本的升級而得到改善，所以本文會列出一些常見的局限性，且不包含所有的。 

1.1 關聯子查詢

描述:

因為select …from table1 t1 where t1.id in(select t2.fk from table2 t2 wheret2.id=’…’) 類型的語句往往會被優化成 select …. From table1 t1 where exists (select* from table2 t2 where t2.id=’…’ and t2.fk=t1.id), 由於在進行tabl2查詢時, table1的值還無法確定, 所以會對table1進行全表掃描

解決方案:

盡量用 INNER JOIN 替代 IN(),重寫成 select * from table1 t1 inner jointable2 t2 using (id) where t2.id=’…’

1.2 UNION的限制

描述:

UNION操作不會把UNION外的操作推送到每個子集

解決方案:

為每個子操作單獨的添加限制條件

例如 學生表有10000條記錄,會員表有10000表記錄,如果想按照姓名排序取兩個表的前20條記錄,如果在各個子查詢中添加limit的話,則最外層的limit操作將會從40條記錄中取20條,否則是從20000條中取20條 

(select name from student order by name limit 20) union all (select name from memberorder by member limit 20) limit 20 

1.3 等值傳遞

在進行查詢操作的時候 IN,ON,Using,等操作往往會把一個列表的值在多個表之間共享,而優化器為了優化的方便會把列表裡的值為每個相關表都拷貝一份,如果這個列表非常的大,會對性能造成一定的影響.

目前為止還沒有好的策略應對這個問題

1.4 並行執行

目前為止,MYSQL不支持

1.5 哈希關聯

目前MYSQL唯一支持的是循環嵌套關聯,不支持HASH關聯

1.6 松散索引掃描

描述:所謂的松散索引就是當對表進行掃描是,可以智能的跳過一些記錄,以此來減少需要掃描的記錄行數.為了更清楚的說明這個問題,舉個例子來說明松散索引掃描的好處,例如table1表上有索引(a,b),執行 select * from tabl1 where b between2 and 3時,支持/不支持松散掃描的表掃描方式分別如下

由於B列是按照順序排列的,所以只需要在固定的區間內查找就可以了,其余的記錄可以跳過

B不是索引的第一字段,所以只能從第一條找到最後一條

上面兩個圖可以很明顯的說明松散索引的好處,但是Mysql對這個特性的支持不是很好,只針對某些特殊的查詢才提供此優化,具體的要看各個版本的手冊 

1.7 Max()/MIN()

問題描述:

當執行 select max(id) from table1 where name=’sun’ 時,如果name沒有建立相應的索引,MYSQL會進行全表掃描

解決方案:

將SQL等同的轉化為

select id from table1 use index(PRIMARY) wherename=’sun’ limit 1.

1.8 同一個表的查詢以及更新

問題描述:

不能在查詢某個表的同時對表進行更新 

Update table1t1 set  t1.cnt=(select count(*) fromtable1) 

否則會拋出異常: ERROR 1093 (HY000): You can'tspecify target table 'ftsexchangerate' for update in FROM clause

解決辦法: 轉化成關聯表的形式

update ftsexchangerate 

inner join(

select currency,count(*) as cnt from ftsexchangerate group by (currency) ) as innusing(currency)

set ftsexchangerate.description=inn.cnt ;