高性能MySql設計之查詢優化(limit優化)

1        介紹

1.1     處理流程

當MYSQL 收到一條查詢請求時，會首先通過關鍵字對SQL語句進行解析，生成一顆“解析樹”，然後預處理器會校驗“解析樹”是否合法(主要校驗資料列和表明是否存在，别名是否有歧義等)，當“解析樹”被認為合法後，查詢優化器會對這顆“解析樹”進行優化，并确定它認為最完美的執行計劃。

1.2     衡量标準

MYSQL查詢優化器衡量某個執行計劃是否完美的标準是“使用該執行計劃時的成本”，該成本的最小機關是讀取一個4K資料頁的成本。

下面圖中的資料說明，當使用id為條件查詢時，查詢的成本隻有一個資料頁，而使用mean(非索引)查詢時，成本将近是22334個資料頁

1.3     優化手段

下面列出了一些優化器常用的優化手段，但實際上遠遠不止這些

1.       重新定義關聯表的順序

對于多表關聯的查詢（INTER JOIN），優化器會根據資料的選擇性來重新決定關聯的順序，選擇性高的會被置前。

如果關聯設計到N張表，優化器會嘗試N！種的關聯順序，從中選出一種最優的排列順序，如果有10張表進行關聯，就有3628800種排序的可能，優化器可能需要經過3628800次的嘗試才能得到一個最優的順序。面對這種數量很大的排序任務，優化器并沒有老老實實的嘗試3628800次，而是當達到optimizer_search_depth指定的次數後，就會采用“貪婪模式”進行處理。這也表明關聯表的數量不能太多

此功能可以通過STRAIGHT_JOIN關鍵字來進行屏蔽

2.       排序優化

當不能使用索引進行排序時，MYSQL會對結果集進行排序，這時候會采取兩種政策：（1）如果結果集的容量小于“排序緩沖區”的容量，在記憶體中進行排序（2）如果查詢的結果大于“排序緩沖區”，則先将結果集拆分成多個“排序緩沖區”可以容納的子集，然後把各個子集排序的結果存放在磁盤上，最後對各個子集進行合并

在排序的過程中使用臨時表的存儲空間可能要比實際的存儲空間大很多，主要是因為在排序的時候都會為每個字段保留最大的存儲空間

當進行關聯查詢排序時，如果order by的字段全部來自第一張表，則在對第一張表進行關聯處理時，就會進行排序動作（Extra 中會包含Using filesort），否則會對多表關聯後的結果進行排序（Extra 中包含，Using temporary, Using filesort）。

在MYSQL5.6 之前的版本中， LIMIT關鍵字的作用隻會在排序完之後才生效，是以即使在查詢中包含了LIMIT，查詢還是會對大量的資料進行處理

3.       等價規則

例如 出現 where 5=5 and a>5 會轉化成where a>5

4.       COUNT(),MIN(),MAX()

對于B-Tree索引而言，Max()/Min()的結果分别傳回的是二叉樹中最左邊以及最右邊的值，是以不需要進行表的通路就可以直接取到對應的值。

對于Count()函數而言，在MYISAM引擎中維護了一個對應的常量值，也不需要對表進行通路就可以直接取到Count的值。

經過這種優化過的SQL,在EXTRA中會出現 “Selecttables optimized away”的字樣

5.       轉化為常數表達式

首先要說明的是，在資料庫對查詢進行處理的時候， 以常數（Constant）的方式進行處理的速度是最快的。查詢優化器在優化的過程中，如果發現一個表達式可以轉換為常數，就會将表達式轉換為常數進行處理。

在優化階段，一個查詢也可以轉換為常數，例如 在索引列上執行Min(),在where中對主鍵或者是唯一鍵進行條件限制等。

6.       覆寫索引掃描

7.       提前終止

在下列幾種情況中，查詢會提前終止，并不再對表進行掃描

·        當優化器發現查詢的結果已經滿足查詢需求的時候。比如查詢中用到了LIMIT

·        Where的條件不成立的時候。例如 where id>100 and id <10

8.       等值傳播

對于通過列關聯的查詢，某列的where條件可以自動的從一張表傳遞到另外一張表，例如

Select film.filmid from film

Inter join film_actor using (filmid)

Where film.filmid>50;

上面的查詢隻是顯示的指出film.filmid>50，但是優化器在優化的工程中會将其轉化為

Where film.filmid>50 and film_actor.filmid>50.

9.       清單IN()的比較

…..where id in(2,4,1,3,8,6) 這種類型的限制條件在很多的RDBMS中等同于

where id=2 or id=4 or id=3 or id=8 or id=6. 這種算法的複雜度是O(n).

而在MYSQL中,首先會對In清單進行排序，然後通過二分查找的方式進行比較，該方式的算法複雜度是O(log n).如果IN清單中的資料量非常的大，則效果會非常的明顯

在“查詢優化器常用的方式”一文中列出了一些優化器常用的優化手段。查詢優化器在提供這些特性的同時，也存在一定的局限性，這些局限性往往會随着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條

1. (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等同的轉化為 

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

這樣的語句會盡可能少的掃描表記錄

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

問題描述:

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

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

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

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

1. update ftsexchangerate   
2. inner join(  
3. select currency,count(*) as cnt from ftsexchangerate group by (currency) ) as innusing(currency)  
4. set ftsexchangerate.description=inn.cnt ;  

總結一下常見查詢語句的優化方式

1        COUNT

1.       COUNT的作用

·        COUNT(table.filed)統計的該字段非空值的記錄行數

·        COUNT(*)或者是COUNT(not nullable field) 統計的是全表的行數

如果要是統計全表記錄數，COUNT(*)效率會比COUNT(not nullable field)要高一點

2.       MYISAM的COUNT

一般執行COUNT操作時需要掃描大量的記錄，但是在MyISAM引擎的資料庫中，資料庫把表的記錄數儲存起來，是以COUN(*)會非常的快（前提是不包含where條件）

3.       當需要頻繁的使用COUNT時，可以考慮使用彙總表的政策

4.       優化小例子

在MYISAM中進行範圍查詢時，可以減少檢索行數的小技巧

原始的：select count(*) from dictionary where id>5.

優化後：select (select count(*) fromdictionary)-count(*) from dictionary where id<=5

減少查詢次數

優化前：需要兩條語句

                  Select count(*)from student where area=’SH’

                  Select count(*)from student where area=’BJ’

優化後：合并成一條

 select count(area='SH') as shcount, count(area='BJ') as bjcount from student;

2        優化關聯查詢

1.       確定ON或USING的字句上有索引

2.       一般情況下隻需要在第二個表上建立索引

3.       盡量使 Group by/Order by的表達式中隻包含一個表的字段

3        優化子查詢

盡量用關聯代替子查詢

4        優化Group by 以及Distinct

1.       當對關聯查詢執行group by操作時，使用查詢表的辨別列作為分組條件效率會比較高

2.       當需要查詢的非group by指定的字段時，正常情況下是無法執行的，可以通過inner join 的形式來彌補

1. select firstname, lastname  
2. from actor  
3. inner join(select  actor_id, count(*) as cnt from actor group by(actor_id))  
4. using (actor_id)  

3.       group by預設會對查詢的結果進行排序，資料量很大的時候可能會比較耗資源，如果你不關心查詢結果的順序，可以通過order by null 避免這種不必要的浪費

5        LIMIT分頁

在進行分頁查詢的時候往往是采用select * from table1 limit 100,20 的方式來提取資料，在處理的過程中會讀取120條資料，然後扔掉100條的offset記錄,最後傳回20條記錄給用戶端。如果offset的值非常大，效率上可能會有影響，可以嘗試

1.       可以通過覆寫索引+inner join的方式來重寫sql

1. select field1,field2,field3  
2. from table1  
3.     inner join  
4. (select id from table1 limit 100, 20) as temp  
5.    using(id)  

2.       如果可以計算出明确的開始點和結束點，可以轉換成 between and 的方式,這種方式隻會掃描指定的行數，效率比較高

Select * from table1 between 100 and 120.

3.       可以通過位置标簽的方式，來減少需要檢索的記錄數

例如 從某個位置開始。 Select * from table1 whereid>100 limit 20

下圖列出了三種方式的效率對比

在進行分頁處理的時候往往需要知道記錄的總數，然後用這些總數生成頁碼。擷取總數往往是使用count或是伴随一次全表查詢得到的，這個過程也是檢索所有的記錄，然後再丢掉。為了避免這種浪費可以采取兩種政策

·        把頁碼換成“下一頁”的方式，這樣就隻需要去取固定的條數

·        一次性讀取1000條，當一千條使用完後，采用“擷取更多記錄”的方式再擷取1000條

6        UNION

·        使用的時候要把每個優化手段下推到每個子集中

·        Union操作會對處理後的結果執行distinct操作，這在很多時候是沒有必要的。可以采用union all來避免這個問題

7        自定義變量

合理靈活的使用自定義變量往往會給程式的性能帶來意想不到的效果，但往往也會帶來與其他資料庫系統的相容性問題。

下面列出幾個自定義變量使用的小例子

·        行号

mysql> set @rownumber:=0;

mysql> select mean, @rownumber:[email protected]+1 from dictionary limit10;

·        避免重複查詢剛剛更新的資料

在更新完一條記錄後，往往需要再次執行select查詢剛剛更新過的記錄

通過變量可以避免這種問題

Mysql>set @updaterow:=null;

mysql> update dictionary set mean='update get variable' where id=100and @updaterow:=now();

·        統計更新和插入的數量

mysql> set @x:=0;    //define avariable

mysql> insert into dictionary (id,mean) values(3,'duplicate') onduplicate key update mean=values(mean)+(0*(@x:[email protected]+1)); //insert a duplicaterecord

mysql> select @x; //get x value, it’s indicator duplicate times

8        靜态分析工具

有時候可以借助專門的查詢分析工具來發現自己的問題，比如pt-query-advisor(http://www.percona.com/doc/percona-toolkit/2.1/pt-query-advisor.html)