MYSQL資料庫------SQL優化

sql優化

1. 對查詢進行優化，應儘量避免全表掃描，首先應考慮在where及order by，GROUP BY涉及的列上建立索引。
2. 對查詢進行優化，應儘量避免全表掃描，首先應考慮在where及order by涉及的列上建立索引。
3. 應儘量避免在where子句中使用!=或<>操作符，MySQL只有對以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些時候的LIKE。
4. 應儘量避免在where子句中使用or來連線條件，否則將導致引擎放棄使用索引而進行全表掃描，可以使用UNION合併查詢：select id from t where num=10 union all select id from t where num=20。
5. in和not in也要慎用，否則會導致全表掃描，對於連續的數值，能用between就不要用in了：Select id from t where num between 1 and 3
6. 下面的查詢也將導致全表掃描：select id from t where name like‘%abc%’或者select id from t where name like‘%abc’若要提高效率，可以考慮全文檢索。而select id from t where name like‘abc%’才用到索引。
7. 如果在where子句中使用引數，也會導致全表掃描。
8. 很多時候用exists代替in是一個好的選擇：select num from a where num in(select num from b)。用下面的語句替換：select num from a where exists(select 1 from b where num=a.num)。
9. 索引固然可以提高相應的select的效率，但同時也降低了insert及update的效率，因為insert或update時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。
10. 最好不要使用”“返回所有：select from t ，用具體的欄位列表代替“*”，不要返回用不到的任何欄位。
11. 最好不要使用”“返回所有：select from t ，用具體的欄位列表代替“*”，不要返回用不到的任何欄位。
12. 在IN後面值的列表中，將出現最頻繁的值放在最前面，出現得最少的放在最後面，減少判斷的次數。
13. 儘量使用exists代替select count(1)來判斷是否存在記錄，count函式只有在統計表中所有行數時使用，而且count(1)比count(*)更有效率。 count(*)它可能鎖住整張表
14. 當有一批處理的插入或更新時，用批量插入或批量更新，絕不會一條條記錄的去更新。
15. 當只要一行資料時使用LIMIT 1，這樣查詢到以後就不會繼續查詢了
16. 在適當的情形下使用GROUP BY而不是DISTINCT
17. 提高GROUP BY語句的效率，可以通過將不需要的記錄在GROUP BY之前過濾掉。下面兩個查詢返回相同結果，但第二個明顯就快了許多。

SELECT JOB , AVG(SAL) 
FROM EMP 
GROUP BY JOB 
HAVING JOB =’PRESIDENT’ 
OR JOB =’MANAGER’ 

SELECT JOB , AVG(SAL) 
FROM EMP 
WHERE JOB =’PRESIDENT’ 
OR JOB =’MANAGER’ 
GROUP BY JOB

儘量避免使用子查詢

以下查詢返回月薪大於部門平均月薪的員工資訊
EXPLAIN ANALYZE
 SELECT emp_id, emp_name
   FROM employee e
   WHERE salary > (
     SELECT AVG(salary)
       FROM employee
       WHERE dept_id = e.dept_id);

1. 從執行計劃可以看出，MySQL 中採用的是類似 Nested Loop Join 實現方式；子查詢迴圈了 25 次，而實際上可以通過一次掃描計算並快取每個部門的平均月薪。以下語句將該子查詢替換為等價的 JOIN 語句，實現了子查詢的展開（Subquery Unnest）：

EXPLAIN ANALYZE
 SELECT e.emp_id, e.emp_name
   FROM employee e
   JOIN (SELECT dept_id, AVG(salary) AS dept_average
           FROM employee
          GROUP BY dept_id) t
     ON e.dept_id = t.dept_id
  WHERE e.salary > t.dept_average;

1. 改寫之後的查詢利用了物化（Materialization）技術，將子查詢的結果生成一個記憶體臨時表；然後與 employee 表進行連線。通過實際執行時間可以看出這種方式更快。
   們應該儘量避免使用子查詢，考慮使用 JOIN 進行重寫。

瞭解 SQL 子句的邏輯執行順序

以下是 SQL 中各個子句的語法順序，前面括號內的數字代表了它們的邏輯執行順序：

(6)SELECT [DISTINCT | ALL] col1, col2, agg_func(col3) AS alias
(1) FROM t1 JOIN t2
(2) ON (join_conditions)
(3) WHERE where_conditions
(4) GROUP BY col1, col2
(5)HAVING having_condition
(7) UNION [ALL]
   ...
(8) ORDER BY col1 ASC,col2 DESC
(9)OFFSET m ROWS FETCH NEXT num_rows ROWS ONLY;

1. 首先，FROM 和 JOIN 是 SQL 語句執行的第一步。它們的邏輯結果是一個笛卡爾積，決定了接下來要操作的資料集。注意邏輯執行順序並不代表物理執行順序，實際上資料庫在獲取表中的資料之前會使用 ON 和 WHERE 過濾條件進行優化訪問；
2. 其次，應用 ON 條件對上一步的結果進行過濾並生成新的資料集；
3. 然後，執行 WHERE 子句對上一步的資料集再次進行過濾。WHERE 和 ON 大多數情況下的效果相同，但是外連線查詢有所區別，我們將會在下文給出示例；
4. 接著，基於 GROUP BY 子句指定的表示式進行分組；同時，對於每個分組計算聚合函式 agg_func 的結果。經過 GROUP BY 處理之後，資料集的結構就發生了變化，只保留了分組欄位和聚合函式的結果；
5. 如果存在 GROUP BY 子句，可以利用 HAVING 針對分組後的結果進一步進行過濾，通常是針對聚合函式的結果進行過濾；
6. 接下來，SELECT 可以指定要返回的列；如果指定了 DISTINCT 關鍵字，需要對結果集進行去重操作。另外還會為指定了 AS 的欄位生成別名；
7. 如果還有集合操作符（UNION、INTERSECT、EXCEPT）和其他的 SELECT 語句，執行該查詢並且合併兩個結果集。對於集合操作中的多個 SELECT 語句，資料庫通常可以支援併發執行；
8. 然後，應用 ORDER BY 子句對結果進行排序。如果存在 GROUP BY 子句或者 DISTINCT 關鍵字，只能使用分組欄位和聚合函式進行排序；否則，可以使用 FROM 和 JOIN 表中的任何欄位排序；
9. 最後，OFFSET 和 FETCH（LIMIT、TOP）限定了最終返回的行數。

優化表的資料型別，選擇合適的資料型別：

1. 原則：更小通常更好，簡單就好，所有欄位都得有預設值，儘量避免null。
2. 資料庫表設計時候更小的佔磁碟空間儘可能使用更小的整數型別。(mediumint就比int更合適)
3. 時間欄位：datetime和timestamp，datetime佔用8個位元組，而timestamp佔用4個位元組，只用了一半，而timestamp表示的範圍是1970—2037適合做更新時間
4. MySQL可以很好的支援大資料量的存取，但是一般說來，資料庫中的表越小，在它上面執行的查詢也就會越快。
   因此，在建立表的時候，為了獲得更好的效能，我們可以將表中欄位的寬度設得儘可能小。
   例如：在定義郵政編碼這個欄位時，如果將其設定為CHAR(255)，顯然給資料庫增加了不必要的空間。甚至使用VARCHAR這種型別也是多餘的，因為CHAR(6)就可以很好的完成任務了。
   如果可以的話，我們應該使用MEDIUMINT而不是BIGIN來定義整型欄位，應該儘量把欄位設定為NOT NULL，這樣在將來執行查詢的時候，資料庫不用去比較NULL值。
   對於某些文字欄位，例如“省份”或者“性別”，我們可以將它們定義為ENUM型別。因為在MySQL中，ENUM型別被當作數值型資料來處理，而數值型資料被處理起來的速度要比文字型別快得多。這樣，我們又可以提高資料庫的效能。

varchar,char,text的區別

1. char是定長的，也就是當你輸入的字元小於你指定的數目時，char(8)，你輸入的字元小於8時，它會再後面補空值。當你輸入的字元大於指定的數時，它會擷取超出的字元。
2. varchar長度為 n 個位元組的可變長度且非 Unicode 的字元資料。n 必須是一個介於 1 和 8,000 之間的數值。儲存大小為輸入資料的位元組的實際長度，而不是 n 個位元組。所輸入的資料字元長度可以為零。
3. text儲存可變長度的非Unicode資料，最大長度為2^31-1(2,147,483,647)個字元。
   如果能確定字元長度（比如身份證號）用char型別，反之用varchar

一定要用explain測試自己的sql

主要關注Select_type
Select_type 說明查詢中使用到的索引型別，如果沒有用有用到索引則為all

常用的型別有： ALL, index, range, ref, eq_ref, const, system, NULL（從左到右，效能從差到好）

ALL：Full Table Scan， MySQL將遍歷全表以找到匹配的行

index: Full Index Scan，index與ALL區別為index型別只遍歷索引樹（建立索引，但是如果沒有規範的使用索引，就是index型別 比如（%param%）查詢的就是index）

range:只檢索給定範圍的行，使用一個索引來選擇行

ref: 表示上述表的連線匹配條件，即哪些列或常量被用於查詢索引列上的值

eq_ref: 類似ref，區別就在使用的索引是唯一索引，對於每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連線中使用primary key或者 unique key作為關聯條件

const、system: 當MySQL對查詢某部分進行優化，並轉換為一個常量時，使用這些型別訪問。如將主鍵置於where列表中，MySQL就能將該查詢轉換為一個常量,system是const型別的特例，當查詢的表只有一行的情況下，使用system

NULL: MySQL在優化過程中分解語句，執行時甚至不用訪問表或索引，例如從一個索引列裡選取最小值可以通過單獨索引查詢完成。