MySQL 查詢最佳化

標籤：最佳化

查詢最佳化常用策略

  1、最佳化資料訪問：應用程式應該減少對資料庫的資料訪問，資料庫應該減少實際掃描的記錄數

     例如，Redis緩衝，避免"select * from table"

  2、重寫SQL

     對於需要進行大量資料的操作，可以分批執行，以減少對生產系統的影響，從而緩解複製逾時

MySQL join 嚴重降低了並發性，應該盡量串連太多的表，建議在應用程式層實現部分的串連功能

  3、重新設計庫表

      在沒有其他的最佳化辦法下，可以考慮更改表結構設計，增加緩衝表，暫存統計資料，或者增加冗餘列，以減少串連

  4、索引

     索引能解決80%的問題

 

最佳化器介紹

  最佳化器的不足

   1、資料的統計資訊可能是錯誤的

   2、CPU，記憶體、資料是否在緩衝，都會影響最佳化器

   3、最佳化器不會考慮並發的情況，資源的鄭永可能會導致效能問題

   提示：

   1、使用索引

   select * from table1 use index(col1_index,col2_index) where col1=1 and col2=2 and col3=3;

   2、不使用索引

      select * from table1 ignore index(col3_index) where col1=1 and col2=2 and col3=3;;

   3、強制使用索引

     select * from table1 force index(col3_index) where col1=1 and col2=2 and col3=3;;

    注意：use index,ignore index,force index 只會影響MySQL表中檢索記錄和串連要使用的索引，不影響order by或group by或group by

   4、不使用查詢快取

     SQL_NO_CACHE

   5、使用查詢快取  explicit_mode,query_cache_type=2 ，指明SQL需要緩衝，才緩衝

    SQL_CACHE

  6、Straight_join

     按照FROM字句描述的表的順序進行串連

 

MySQL的串連機制

   Nested Loop join

   MySQL最佳化器一般會選擇小表來做驅動表（外部表格）

   

各種語句的最佳化

串連的最佳化

 1、串連的表不要超過4個

 2、ON，using子句的列要有索引

 3、最好能轉換為inner join，left join的成本比inner join高很多

 4、explain檢查串連，如果輸出的rows列太高，考慮索引或串連表順序是否不當

 5、反範式設計

 

group by、distinct、order by語句最佳化

1、盡量對較少的行進行排序

2、串連了多張表，order by 的列應該屬於串連順序的第一張表

3、 利用索引排序

4、group by,order by的列盡量是第一表中的列，如果不是，考慮冗餘列

5、保證索引列和order by的列相同，而且按相同的方向進行排序

6、增加sort_rnd_buffer_size

7、改變tempdir變數指向基於記憶體的檔案系統或者其他更快的磁碟

8、指定Order by null

  預設情況下MySQL將排序所有的Group by的查詢，如果要避免排序結果，可以指定Order by null;

9、最佳化Group by with rollup

   考慮在應用程式層實現

10、使用非group by的列來替代group by的列

   比如 group by x,y，如果group by z 能到到相同的結果，則盡量少出現group by

11、考慮用Sphinx替代 group by語句

最佳化子查詢

   大多數情況下，串連會比子查詢快，子查詢產生的暫存資料表沒有索引

   

   select distinct col1 from t1 where col1 in (select col1 from t2);

   改寫為：

   select distinct t1.col1 from t1,t2 where t1.col1=t2.col1;

   

select * from t1 where id not in (select id from t2);

改寫：

select * from t1 where not exists (select id from t2 where t1.id=t2.id) 

也可以改寫為：

select table1.* from table1 left join table2 on table1.id=table2.id where table2.id is null;

把子句從子查詢的外部轉移到內部

select * from t1 where s1 in (select s1 from t1) or s1 in (select s1 from t2);

改寫

select * from t1 where s1 in (select s1 from t1 union all select s1 from s2);

select (select column1 from t1) +5 from t2;

改寫

select (select column1+5 from t1) from t2;

  

   

最佳化limit字句

最佳化 IN

最佳化Union

最佳化帶有BLOB、Text類型欄位的查詢

filesort的最佳化

最佳化SQL_CALC_FOUND_ROWS

最佳化暫存資料表

OLAP業務最佳化

MySQL 查詢最佳化