慢 SQL 的優(yōu)化，主要從兩個方面考慮，SQL 語句本身的優(yōu)化，以及數據庫設計的優(yōu)化。

避免不必要的列

SQL 查詢的時候，應該只查詢需要的列，而不是包含額外的列，像select *這種寫法應該盡量避免。

分頁優(yōu)化

在數據量比較大，分頁比較深的情況下，需要考慮分頁的優(yōu)化。

 select * from tabel where type = 2 and level = 9 order by id asc limit 100000,10;

延遲關聯(lián)

先通過where條件提取出主鍵，在將該表與原數據表關聯(lián)，通過主鍵 id 提取數據行，而不是通過原來的二級索引提取數據行

 select a.* from table a,
 (select id from table where type = 2 and level = 9 order by id asc limit 100000,10) b
 where a.id = b.id;

id 偏移量

偏移量就是找到 limit 第一個參數對應的主鍵值，根據這個主鍵值再去過濾并 limit

 select * from table where id >
 (select id from table where type = 2 and level = 9 order by id asc limit 190 );

索引優(yōu)化

合理的設計和使用索引，是優(yōu)化慢 SQL 的利器。

1.利用覆蓋索引

InnoDB 使用二級索引查詢數據時會回表，但是如果索引的葉節(jié)點中已經包含要查詢的字段，那它沒有必要再回表查詢了，這就叫覆蓋索引，還有一個簡單的理解查詢列都是索引列。

 select b from test where a = "wanna";

 alter table test add index idx_a_b (a,b);

2.避免使用 or 查詢

在 MySQL 5.0之前的版本要盡量避免使用 or 查詢，可以使用 union 或者子查詢來替代，因為早期的 MySQL 版本使用 or 查詢可能會導致索引失效，高版本引入了索引合并，解決了這個問題，不過建議大家在實際使用中還是規(guī)范寫法，能不用就少用。

3.避免使用 != 或者 <> 操作符

SQL 中，不等于操作符會導致查詢引擎放棄查詢索引，引起全表掃描，即時比較的字段上有索引

解決方法：通過把不等于操作符改成 or，可以使用索引，避免全表掃描

 id <> 'aaa' ===> id > 'aaa' or id < 'aaa'

4.適當使用前綴索引

適當的使用前綴索引，可以降低索引的控件占用，提高索引的查詢效率。

比如，郵箱的后綴都是固定的@xxx.com，那么類似這種后面幾位為固定值的字段就非常適合定義為前綴索引

 alter table test add index dix_emaile_prefix (email(6));

需要注意的是，前綴索引也存在缺點，MySQL 無法利用前綴索引做 order by和group by操作，也無法作為覆蓋索引。

5.避免列上函數運算

要避免在列字段上進行算術運算符或其他表達式運算，否則可能會導致存儲引擎無法正確的使用索引，從而影響了查詢的效率。

 select * from test where id + 1 = 50;
 select * from test where month(updateTime) = 7;

6.正確的使用聯(lián)合索引

使用聯(lián)合索引的時候，注意最左匹配原則。

JOIN 的優(yōu)化

優(yōu)化子查詢

盡量使用 join 語句來替代子查詢，因為子查詢是嵌套查詢，而嵌套查詢會新建創(chuàng)建一張臨時表，而臨時表的創(chuàng)建與銷毀會占用一定的系統(tǒng)資源以及花費一定的時間，同時對于返回結果集比較大的子查詢，其對查詢性能的影響更大。

小表驅動大表

關聯(lián)查詢的時候要拿小表去驅動大表，因為關聯(lián)的時候，MySQL 內部會遍歷驅動表，再去連接被驅動表。

select name from 小表 left join 大表;

適當增加冗余字段

增加冗余字段可以減少大量的連表查詢，因為多張表的連表查詢性能很低，所有可以適當的增加冗余字段，以減少多張表的關聯(lián)查詢，這是以空間換時間的優(yōu)化策略。

避免使用 JOIN 關聯(lián)太多表

《阿里巴巴 Java 開發(fā)手冊》規(guī)定不要 join 超過三張表，第一 join 太多降低查詢的速度，第二 join 的 buffer 會占用更多的內存。

排序優(yōu)化

利用索引掃描做排序

MySQL 有兩種方式生成有序結果：一是對結果集進行排序的操作，而是按照索引順序掃描得出的結果，索引是排好序的數據結果，自然是有序的。

但是如果索引不能覆蓋查詢所需列（覆蓋索引），就會沒掃描一條記錄回表查詢一次（逐個獲取），這個讀操作是隨機 IO，通常會比順序全表掃描還慢，有時會直接放棄使用索引轉為全表掃描。

因此，在設計索引時，盡可能使用同一個索引既滿足排序又用于查找行。

-- 索引(a,b,c)
select b,c from test where a like 'aa%' order by b,c;

只有當索引的列順序和order by子句的順序完全一致，并且所有列的排序方向都一樣時，才能夠使用索引來對結果做排序。

UNION 優(yōu)化

條件下推

MySQL 處理 union 的策略是先創(chuàng)建臨時表，然后將各個查詢結果填充到臨時表中最后再來做查詢，很多優(yōu)化策略在 union 查詢中都會失效，因為它無法利用索引。

所以需要將where、limit等子句下推到 union 的各個子查詢中，以便優(yōu)化器可以充分利用這些條件進行優(yōu)化。

此外，除非確實需要服務器去推，一定要試用union all，如果不加all關鍵字，MySQL 會給臨時表加上 distinct選項，這會導致對整個臨時表做唯一性檢查，代價很高。

到此這篇關于MySQL中慢SQL優(yōu)化的不同方式介紹的文章就介紹到這了,更多相關MySQL慢SQL優(yōu)化內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論