MySQL 查詢優(yōu)化

查詢優(yōu)化常用策略

  1、優(yōu)化數(shù)據(jù)訪問：應用程序應該減少對數(shù)據(jù)庫的數(shù)據(jù)訪問，數(shù)據(jù)庫應該減少實際掃描的記錄數(shù)

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

  2、重寫SQL

     對于需要進行大量數(shù)據(jù)的操作，可以分批執(zhí)行，以減少對生產(chǎn)系統(tǒng)的影響，從而緩解復制超時

MySQL join 嚴重降低了并發(fā)性，應該盡量連接太多的表，建議在應用層實現(xiàn)部分的連接功能

  3、重新設計庫表

      在沒有其他的優(yōu)化辦法下，可以考慮更改表結(jié)構(gòu)設計，增加緩存表，暫存統(tǒng)計數(shù)據(jù)，或者增加冗余列，以減少連接

  4、索引

     索引能解決80%的問題

優(yōu)化器介紹

  優(yōu)化器的不足

   1、數(shù)據(jù)的統(tǒng)計信息可能是錯誤的

   2、CPU，內(nèi)存、數(shù)據(jù)是否在緩存，都會影響優(yōu)化器

   3、優(yōu)化器不會考慮并發(fā)的情況，資源的鄭永可能會導致性能問題

   提示：

   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優(yōu)化器一般會選擇小表來做驅(qū)動表（外部表）

各種語句的優(yōu)化

連接的優(yōu)化

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

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

 3、最好能轉(zhuǎn)換為inner join，left join的成本比inner join高很多

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

 5、反范式設計

group by、distinct、order by語句優(yōu)化

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

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

3、 利用索引排序

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

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

6、增加sort_rnd_buffer_size

7、改變tempdir變量指向基于內(nèi)存的文件系統(tǒng)或者其他更快的磁盤

8、指定Order by null

  默認情況下MySQL將排序所有的Group by的查詢，如果要避免排序結(jié)果，可以指定Order by null;

9、優(yōu)化Group by with rollup

   考慮在應用層實現(xiàn)

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

   比如 group by x,y，如果group by z 能到到相同的結(jié)果，則盡量少出現(xiàn)group by

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

優(yōu)化子查詢

   大多數(shù)情況下，連接會比子查詢快，子查詢生成的臨時表沒有索引

   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;

把子句從子查詢的外部轉(zhuǎn)移到內(nèi)部

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;

優(yōu)化limit字句

優(yōu)化 IN

優(yōu)化Union

優(yōu)化帶有BLOB、Text類型字段的查詢

filesort的優(yōu)化

優(yōu)化SQL_CALC_FOUND_ROWS

優(yōu)化臨時表

OLAP業(yè)務優(yōu)化