面試官：怎么優化 SQL？

在面試的環節中，面試官問到：你是如何設計你的表結構的，畫一下E-R圖？接著又繼續深挖，如果有慢查詢，你是如何優化你的sql的？

今天，我就來和大家講講要怎么回答這道問題。首先，我們要穩住不要慌，自己是自己親手做的項目，第一個問題應該都不大，第二個問題就需要在面試之前做好充分的準備啦…

在回答問題之前先要了解查詢的流程：查詢是由一系列的子任務組成的，包括從客戶端，到服務器，然后在服務器上進行解析，生成執行計劃，執行，并返回結果給客戶端。其中“執行”可以認為是整個生命周期中最重要的階段，這其中包括了大量為了檢索數據到存儲引擎的調用以及調用后的數據處理，包括排序、分組。為了完成這些任務，查詢需要在不同的地方花費時間，包括網絡，CPU計算，生成統計信息和執行計劃、鎖等待操作。進行一些不必要的額外操作時或者某些重復執行某些額外操作會消耗大量的時間。

查詢性能低下最基本的原因是訪問的數據太多。某些查詢可能不可避免地需要篩選大量的數據，大部分性能低下的查詢都可以通過減少訪問的數據量的方式進行優化。對于低效的查詢，可以通過以下兩個步驟來分析：

確認應用程序是否在檢索大量超過需要的數據。

確認MySQL服務器是否在分析大量超過需要的數據行。

上面的都是理論，在實踐中，MySQL的優化主要涉及SQL語句及索引的優化、數據表結構的優化這三個方面。

SQL語句的優化：
1、少用子查詢

盡量少用子查詢，因為子查詢會產生臨時表；除非像count(*)臨時表很小的。

2、少用SELECT *

每次看到SELECT *都需要用懷疑的眼光審視，是否真的需要返回全部的列？取出全部的列，會讓優化器無法完成索引覆蓋掃描這類優化，還會為服務器帶來額外的I/O、內存和CPU的消耗。

3、查詢必要的記錄

一個常見的錯誤是常常會誤以為MySQL只會返回需要的數據，實際上MySQL卻是先返回全部結果集再進行計算，建議在查詢后面加上LIMIT。

4、不要重復查詢相同的數據

不斷執行相同的查詢，然后每次都會返回完全相同的數據。可以采用的方案是初次查詢的時候將這個數據緩存起來，需要的時候從緩存中取出，這樣性能顯然會更好。

5、COUNT查詢優化

COUNT()聚合函數的作用：統計某一個列值的數量，也可以統計行數。需要注意的是統計列值時要求列值是非空的(不統計NULL)，COUNT()查詢盡可能少的行。

舉個例子：如果我們直接查 id>100 的記錄，涉及到的有兩千多萬行記錄掃描。但是由于COUNT()特性，我們可以用 count() - (id<100)的做法，這樣掃描的行就只有100行了。

6、Where子句中，where表之間的連接必須寫在其他Where條件之前，那些可以過濾掉最大數量記錄的條件必須寫在Where子句的末尾.HAVING最后。

7、用EXISTS替代IN、用NOT EXISTS替代NOT IN。

8、避免在索引列上使用計算。

9、避免在索引列上使用IS NULL和IS NOT NULL。

10、對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。

11、應盡量避免在 where 子句中對字段進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描。

12、應盡量避免在 where 子句中對字段進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。

索引優化
1、關聯查詢優化

確保ON 或則USING 子句的列上有索引。創建索引時就要考慮關聯的順序，當表A和表B用列c關聯的時候，如果優化器關聯順序是B、A，就只需要在表A上建立索引，沒用的索引會占用存儲。

2、GROUP BY 和 DISTINCT優化

GROUP BY 和 DISTINCT的優化最有效的就是使用索引。所有對于分組的列一定要建立索引。比如：

select product, count(*) from orders group by product;

這樣的一個查詢，對product要建立索引。

3、LIMIT分頁優化

進行分頁操作時，通常都會通過偏移量來查詢某些數據。然后再加上解釋的order by，性能一般都不錯。對于order by的列 一定要加上索引。但是對于limit 10000，10 這樣檢索目標10條記錄必須先先查詢前面的10000條記錄。代價很高，這種時候優化最簡單辦法就是使用覆蓋索引。

注意索引失效的情況，

1)以“%”開頭的LIKE語句，模糊匹配

2)OR語句前后沒有同時使用索引

3)數據類型出現隱式轉化（如varchar不加單引號的話可能會自動轉換為int型）

數據庫優化
選擇優化數據類型的幾條建議：

更小的通常更好，盡量使用可以正確存儲數據的最小數據類型，因為占用更少的磁盤、內存和CPU緩存。

簡單最好，選擇整數而不是字符串，選擇MySQL內建的類型而不是字符串來存儲時間和日期，使用整數來存儲IP地址。

盡量避免NULL，很多表都包含可為NULL的列，這是因為NULL是列的默認值，需要指定列為NOT NULL。

整數類型數據一般用int，對于布爾類型的數據用tinyint，但是整數計算一般是使用64位的BIGINT整數。

在需要對小數進行精確計算時，比如說存儲財務數據才使用DECIMAL（浮點存儲的float和double類型計算不精確），但是DECIMAL計算的代價很高，可以考慮使用BIGINT代替DECIMAL，將小數的位數乘以相應的倍數即可。

varchar和char

當需要存儲可變長的字符串用varchar，比使用char存儲更節省空間，varchar使用1或者2個額外的字節來記錄長度。至于用char來存儲適用于下列幾種情況，一是需要存儲很短的字符串時（存儲只有Y和N的值時），二是所有的值接近固定長度（存儲MD5值），三是經常需要變更的值。

BIT

在MySQL5.0之前，BIT是TINYINT的同義詞，在MySQL5.0以及更新的版本，是一個完全不同的數據類型。BIT類型的新行為：（1）可以使用BIT列在一列中存儲一個或者多個true/false值。MySQL把BIT當做字符串類型，而不是數字類型。當檢索BIT(1)的值時，結果是一個包含二進制0或者1的字符串，而不是ASCII的“0”或“1”。

SET

如果需要保存很多的true/false值，可以考慮合并這些列到一個SET數據類型，它在MySQL內部是一系列打包的位的集合來表示的。

使用枚舉代替常用的字符串類型，因為MySQL在存儲枚舉時非常緊湊，MySQL把每個枚舉的值保存為整數，并且在表的.firm文件中保存“數字-字符串”映射關系的“查找表”。

DATATIME存儲的范圍更廣，保存的值從1001年到9999年，精確到秒，與時區無關，使用8個字節的存儲空間，使用一種可排序、無歧義的格式顯示時間，TIMESTAMP類型保存了從1970年1月1日午夜以來的秒數，使用4個字節的存儲空間，只能表示從1970年到2038年，依賴于時區，空間效率更高，推薦使用TIMESTAMP

對于BOLB和TEXT類型他們都是為了存儲很大的數據而設計的字符串，分別采用二進制和字符串方式存儲。

不能有太多的列

單個查詢最好在12個表以內做關聯

當遇到未知值的時候不要害怕使用NULL

在實際的應用中需要混用范式和反范式，使用部分范式化的schema、緩存表、以及其他的技巧，最常見的反范式化數據的方法是復制或者緩存，在不同的表中存儲相同的特定列。

修改.frm文件來加快ALTER TABLE 操作的速度

選取最適用的字段屬性，盡可能減少定義字段寬度，盡量把字段設置NOTNULL，例如’省份’、’性別’最好適用ENUM

使用連接(JOIN)來代替子查詢

用聯合(UNION)來代替手動創建的臨時表

鎖定表、優化事務處理