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業務優化