一、索引優化：

1、like語句的前導模糊查詢不使用索引：

select * from doc where title like '%XX'； --不能使用索引
select * from doc where title like 'XX%'； --非前導模糊查詢，可以使用索引

2、負向條件查詢不能使用索引：

負向條件有：!=、<>、not in、not exists、not like 等

例如下面SQL語句：（假設status的取值為0、1、2、3、4）

select * from doc where status != 1 and status != 2; --不能使用索引
select * from doc where status in (0,3,4); --優化為 in 查詢，可以使用索引

3、範圍條件右邊的列不能使用索引（範圍列可以用到索引）：

範圍條件有：<、<=、>、>=、between等。

索引最多用於一個範圍列，如果查詢條件中有兩個範圍列則無法全用到索引。

假如有聯合索引 (emp_no 、title、from_date )，那麼下面的 SQL 中 emp_no 可以用到索引，而title 和 from_date 則使用不到索引。

select * from employees.titles where emp_no < 10010' and title='SenIOr Engineer'and from_date between '1986-01-01' and '1986-12-31'

4、在索引列做任何操作（計算、函數、表達式）會導致索引失效而轉向全表掃描：

select * from doc where YEAR(create_time) <= '2016'; -- 不能使用索引
select * from doc where create_time<= '2016-01-01'; -- 可以使用索引
select * from order where date < = CURDATE()； -- 不能使用索引
select * from order where date < = '2018-01-2412:00:00'; -- 可以使用索引
select id from t where substring(name,1,3)=』abc』 -- 不能使用索引
select id from t where name like 『abc%』 -- 可以使用索引
select id from t where num/2=100 -- 不能使用索引
select id from t where num=100*2 -- 可以使用索引

5、where 子句中索引列使用參數，也會導致索引失效：

因為SQL只有在運行時才會解析局部變量，但優化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變量的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：

select id from t where num=@num -- 不能使用索引
select id from t with(index(索引名)) where num=@num --可以改為強制查詢使用索引：

6、強制類型轉換會導致全表掃描：

字符串類型不加單引號會導致索引失效，因為MySQL會自己做類型轉換,相當於在索引列上進行了操作。

如果 phone 欄位是 varchar 類型，則下面的 SQL 不能命中索引，因為內部發生的類型轉換。

select * from user where phone=13800001234; -- 不能使用索引
select * from user where phone='13800001234'; -- 可以使用索引

7、is null, is not null 在無法使用索引，不過在mysql的高版本已經做了優化，允許使用索引

select id from t where num is null; -- mysql低版本不能使用索引
select id from t where num=0; -- 可以在num上設置默認值0，確保表中num列沒有null值，然後這樣查詢

8、使用組合索引時，要符合最左前綴原則：

組合索引的欄位數不允許超過5個。如果在a,b,c三個欄位上建立聯合索引 index(a,b,c)，那麼他會自動建立 a、(a,b)、(a,b,c) 三組索引。

（1）建立聯合索引的時候，區分度最高的欄位在最左邊：

（2）存在等號和非等號混合判斷條件時，在建立索引時，把等號條件的列前置，如 where a > ? and b= ?，那麼即使 a 的區分度更高，也必須把 b 放在索引的最前列。

（3）最左前綴查詢時，並不是指SQL語句的where順序要和聯合索引一致，但還是建議 where 條件的順序和聯合索引一致。

（4）假如index(a,b,c), where a=3 and b like 'abc%' and c=4，a能用，b能用，c不能用。

9、利用覆蓋索引來進行查詢操作，避免回表，減少select * 的使用：

覆蓋索引：被查詢列要被所建的索引覆蓋，被查詢列的數據能從索引中直接取得，不用通過行定位符再到 row 上獲取，加速查詢速度。

例如登錄業務需求，SQL語句如下。

Select uid, login_time from user where login_name=? and passwd=?

可以建立(login_name, passwd, login_time)的聯合索引，由於 login_time 已經建立在索引中了，被查詢的 uid 和 login_time 就不用去 row 上獲取數據了，從而加速查詢。

10、利用索引下推減少回表的次數：

索引下推是Mysql5.6版本推出的功能，用於優化查詢。

不使用索引下推的情況下，在使用非主鍵索引進行查詢時，存儲引擎通過索引檢索到數據，然後返回給MySQL服務層，服務層然後判斷數據是否符合條件。
使用索引下推的情況下，如果存在某些被索引的列的判斷條件時，MySQL服務層將這一部分判斷條件傳遞給存儲引擎，然後由存儲引擎通過判斷索引是否符合MySQL服務層傳遞的條件，只有當索引符合條件時才會將數據檢索出來返回給MySQL服務層。

所以，索引下推就是存儲引擎查詢數據時，根據查詢條件過濾掉一些記錄，減少回表的次數，也可以減少MySQL服務層從存儲引擎接收數據量。

11、使用前綴索引：

短索引不僅可以提高查詢性能而且可以節省磁碟空間和I/O操作，減少索引文件的維護開銷，但缺點是不能用於 ORDER BY 和 Group BY 操作，也不能用於覆蓋索引。比如有一個varchar(255)的列，如果該列在前10個或20個字符內，可以做到既使前綴索引的區分度接近全列索引，那麼就不要對整個列進行索引。為了減少key_len，可以考慮創建前綴索引，即指定一個前綴長度，可以使用count(DISTINCT leftIndex(列名, 索引長度))/count(*) 來計算前綴索引的區分度（計算前綴索引的區分度在文章第三部分會介紹）。

12、order by、group by後面的列如果有索引，可以利用索引的有序性可以消除排序帶來的CPU開銷。

（1）order by 最後的欄位是組合索引的一部分，並且放在索引組合順序的最後，避免出現file_sort 的情況，影響查詢性能。例如對於語句 where a= ? and b= ? order by c，可以建立聯合索引(a,b,c)。

（2）如果索引中有範圍查找，那麼索引有序性無法利用，如 WHERE a > 10 ORDER BY b; 索引(a,b)無法排序。

（3）如果是前綴索引，是不能消除排序的

（4）order by排序欄位順序，即asc/desc升降要跟索引保持一致，充分利用索引的有序性來消除排序帶來的CPU開銷

12、進行join聯表查詢的欄位需要建立索引，join最好不要超過三個表，需要 join 的欄位，數據類型必須一致：

多表關聯查詢時，保證被關聯的欄位需要有索引。left join是由左邊決定的，左邊的數據一定都有，所以右邊是我們的關鍵點，建立索引要建右邊的。當然如果索引在左邊，可以用right join。

13、單表索引建議控制在5個以內。

索引不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，同時也會暫用空間。一個表的索引數較好不要超過5個。

14、SQL 性能優化 explain 中的 type：至少要達到 range 級別，要求是 ref 級別，如果可以是 consts 最好。

consts：單表中最多只有一個匹配行（主鍵或者唯一索引），在優化階段即可讀取到數據。

ref：使用普通的索引

range：對索引進行範圍檢索。

當 type=index 時，索引物理文件全掃，速度非常慢。

15、業務上具有唯一特性的欄位，即使是多個欄位的組合，也必須建成唯一索引，防止髒數據產生：

不要以為唯一索引影響了 insert 速度，這個速度損耗可以忽略，但提高查找速度是明顯的。另外，即使在應用層做了非常完善的校驗控制，只要沒有唯一索引，根據墨菲定律，必然。

16、更新十分頻繁、數據區分度不高的列不宜建立索引：

數據更新會變更 B+ 樹，在更新頻繁的欄位建立索引會大大降低資料庫性能。類似於「性別」這種區分度不大的屬性，建立索引是沒有什麼意義的，不能有效過濾數據，性能與全表掃描類似。一般區分度在80%以上的時候就可以建立索引，區分度可以使用 count(distinct(列名))/count(*) 來計算。

二、SQL語句優化：

1、減少請求的數據量：

（1）只返回必要的列，用具體的欄位列表代替 select * 語句

MySQL資料庫是按照行的方式存儲，而數據存取操作都是以一個頁大小進行IO操作的，每個IO單元中存儲了多行，每行都是存儲了該行的所有欄位。所以無論取一個欄位還是多個欄位，實際上資料庫在表中需要訪問的數據量其實是一樣的。但是如果查詢的欄位都在索引中，也就是覆蓋索引，那麼可以直接從索引中獲取對應的內容直接返回，不需要進行回表，減少IO操作。除此之外，當存在 order by 操作的時候，select 子句中的欄位多少會在很大程度上影響到我們的排序效率。

（2）只返回必要的行，使用 Limit 語句來限制返回的數據。如果不使用 Limit 的話，MySQL將會一行一行的將全部結果按照順序查找，最後返回結果，藉助 Limit 可以實現當找到指定行數時，直接返回查詢結果，提高效率

2、優化深度分頁的場景：利用延遲關聯或者子查詢

對於 limit m, n 的分頁查詢，越往後面翻頁（即m越大的情況下）SQL的耗時會越來越長，對於這種應該先取出主鍵id，然後通過主鍵id跟原表進行Join關聯查詢。因為MySQL 並不是跳過 offset 行，而是取 offset+N 行，然後放棄前 offset 行，返回 N 行，那當 offset 特別大的時候，效率就非常的低下，要麼控制返回的總頁數，要麼對超過特定閾值的頁數進行 SQL 改寫。

延遲關聯示例如下，先快速定位需要獲取的 id 段，然後再關聯：

# 延遲關聯：通過使用覆蓋索引查詢返回需要的主鍵,再根據主鍵關聯原表獲得需要的數據
# 覆蓋索引：select的數據列只用從索引中就能夠得到，不用回表查詢
select a.* from 表1 a,(select id from 表1 where 條件 limit 100000,20) b where a.id=b.id；

但對於深度分頁的情況，最好還是將上次遍歷到的最末尾的數據ID傳給資料庫，然後直接定位到該ID處再往後面遍歷數據

3、分解大連接查詢：

將一個大連接查詢分解成對每一個表進行一次單表查詢，然後在應用程式中進行關聯，這樣做的好處有：

（1）減少鎖競爭；
（2）讓緩存更高效。對於連接查詢，如果其中一個表發生變化，那麼整個查詢緩存就無法使用。而分解後的多個查詢，即使其中一個表發生變化，對其它表的查詢緩存依然可以使用。
（3）分解成多個單表查詢，這些單表查詢的緩存結果更可能被其它查詢使用到，從而減少冗餘記錄的查詢。
（4）在應用層進行連接，可以更容易對資料庫進行拆分，從而更容易做到高性能和可伸縮。
（5）查詢本身效率也可能會有所提升。比如使用 IN() 代替連接查詢，可以讓 MySQL 按照 ID 順序進行查詢，這可能比隨機的連接要更高效。

4、避免使用select的內聯子查詢：

在select後面有子查詢的情況稱為內聯子查詢，SQL返回多少行，子查詢就需要執行過多少次，嚴重影響SQL性能。

5、儘量使用Join代替子查詢：

由於MySQL的優化器對於子查詢的處理能力比較弱，所以不建議使用子查詢，可以改寫成Inner Join，之所以 join 連接效率更高，是因為 MySQL不需要在內存中創建臨時表

select 
    b.member_id,b.member_type, a.create_time,a.device_model 
from 
    member_operation_log a 
inner join 
    (select member_id,member_type from member_base_info where `status` = 1) as b 
on 
    a.member_id = b.member_id;

6、多張大數據量的表進行JOIN連接查詢，最好先過濾在JOIN:

在多個表進行 join 連接查詢的時候，最好先在一個表上先過濾好數據，然後再用過濾好的結果集與另外的表 Join，這樣可以儘可能多的減少不必要的 IO 操作，大大節省 IO 操作所消耗的時間

7、避免在使用or來連接查詢條件：

如果一個欄位有索引，一個欄位沒有索引，將導致引擎放棄使用索引而進行全表掃描。

8、union、in、or 都能夠命中索引，但推薦使用 in：

（1）union：能夠命中索引，並且MySQL 耗費的 CPU 最少

select * from doc where status=1
union all
select * from doc where status=2;

（2）in：能夠命中索引，查詢優化耗費的 CPU 比 union all 多，但可以忽略不計

select * from doc where status in (1, 2);

（3）or：新版的 MySQL 能夠命中索引，但是如果一個欄位有建立索引、一個欄位沒有建立索引，那麼將導致索引失效而進行全表掃描，or 查詢優化耗費的 CPU 比 in 多

select * from doc where status = 1 or status = 2

對於上面三種關鍵詞：union all 分兩步執行，而 in 和 or 只用了一步，效率高一點。用 or 的執行時間比 in 時間長。因為使用 or 條件查詢，會先判斷一個條件進行篩選，再判斷 or 中另外的條件再篩選，而 in 查詢直接一次在 in 的集合里篩選，並且or 查詢優化耗費的 CPU 比 in 多，所以推薦使用in

9、對於連續的數值，能用 between 就不要用 in：

10、小表驅動大表，即小的數據集驅動大的數據集：

in 和 exists 都可以用於子查詢，那麼 MySQL 中 in 和 exists 有什麼區別呢？

（1）使用exists時會先進行外表查詢，將查詢到的每行數據帶入到內表查詢中看是否滿足條件；使用in一般會先進行內表查詢獲取結果集，然後對外表查詢匹配結果集，返回數據。
（2）in在內表查詢或者外表查詢過程中都會用到索引；exists僅在內表查詢時會用到索引
（3）一般來說，當子查詢的結果集比較大，外表較小使用exist效率更高；當子查詢的結果集較小，外表較大時，使用in效率更高。
（4）對於 not in 和 not exists，not exists 效率比 not in 的效率高，與子查詢的結果集無關，因為 not in 對於內外表都進行了全表掃描，沒有使用到索引。not exists的子查詢中可以用到表上的索引。

11、使用union all 替換 union：

當SQL語句需要union兩個查詢結果集合時，這兩個結果集合會以union all的方式被合併，然後再輸出最終結果前進行排序。如果用union all替代union，這樣排序就不是不要了，效率就會因此得到提高.。需要注意的是，UNION ALL 將重複輸出兩個結果集合中相同記錄。

12、優化Group by，使用where子句替換Having子句：

避免使用having子句，having只會在檢索出所有記錄之後才會對結果集進行過濾，這個處理需要排序分組，如果能通過where子句提前過濾查詢的數目，就可以減少這方面的開銷。

on、where、having這三個都可以加條件的子句，on是最先執行，where次之，having最後。

提高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 =

13、儘量使用數字型欄位：

若只含數值信息的欄位儘量不要設計為字符型，這會降低查詢和連接的性能。引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對於數字型而言只需要比較一次就夠了。

14、寫出統一的SQL語句：

對於以下兩句SQL語句，很多人都認為是相同的。不過資料庫查詢優化器則認為是不同的，雖然只是大小寫不同，但必須進行兩次解析，生成2個執行計劃。所以應該保證相同的查詢語句在任何地方都一致，多一個空格都不行。

select * from dual
select * From dual

15、使用複合索引須遵守最左前綴原則：

複合索引必須使用到最左邊欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用，並且應儘可能的讓欄位順序與索引順序相一致。

16、當需要全表刪除且無需回滾時，使用Truncate替代delete

17、使用表的別名

當在SQL語句中連接多個表時, 使用表的別名並把別名前綴用於每個Column上，這樣可以減少解析的時間並減少那些由Column歧義引起的語法錯誤。

18、避免使用耗費資源的操作：

帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句，會啟動SQL引擎執行耗費資源的排序功能，DISTINCT需要一次排序操作，而其他的至少需要執行兩次排序。通常。帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫，如果你的資料庫的SORT_AREA_SIZE調配得好, 使用UNION , MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強。

19、Update 語句，如果只更改1、2個欄位，不要Update全部欄位，否則頻繁調用會引起明顯的性能消耗，同時帶來大量日誌。

20、應儘可能的避免更新聚簇索引數據列，因為聚簇索引數據列的順序就是表記錄的物理存儲順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。

21、儘量使用表變量來代替臨時表。

22、考慮使用「臨時表」暫存中間結果。臨時表並不是不可使用，適當地使用它們可以使某些查詢更有效，例如，當需要重複引用大型表或常用表中的某個數據集時。將臨時結果暫存在臨時表，後面的查詢就在臨時表中查詢了，這可以避免程序中多次掃描主表，也大大減少了程序執行中「共享鎖」阻塞「更新鎖」，減少了阻塞，提高了並發性能。但是，對於一次性事件，較好使用導出表。

23、在新建臨時表時，如果一次性插入數據量很大，那麼可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果數據量不大，為了緩和系統表的資源，應先create table，然後insert。

24、如果使用到了臨時表，在存儲過程的最後務必將所有的臨時表顯式刪除，先 truncate table ，然後 drop table ，這樣可以避免系統表的較長時間鎖定。

25、避免頻繁創建和刪除臨時表，以減少系統表資源的消耗。

26、儘量避免使用游標，因為游標的效率較差。與臨時表一樣，游標並不是不可使用。對小型數據集使用 FAST_FORWARD 游標通常要優於其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的數據時。在結果集中包括「合計」的例程通常要比使用游標執行的速度快。

27、在所有的存儲過程和觸發器的開始處設置 SET NOCOUNT ON ，在結束時設置 SET NOCOUNT OFF 。

28、儘量避免大事務操作，提高系統並發能力。

29、在運行代碼中，儘量使用PreparedStatement來查詢，不要用Statement。

三、索引的選擇性與前綴索引：

既然索引可以加快查詢速度，那麼是不是只要是查詢語句需要，就建上索引？答案是否定的。因為索引雖然加快了查詢速度，但索引也是有代價的：索引文件本身要消耗存儲空間，同時索引會加重插入、刪除和修改記錄時的負擔，另外，MySQL在運行時也要消耗資源維護索引，因此索引並不是越多越好。一般兩種情況下不建議建索引。

第一種情況是表記錄比較少，沒必要建索引，讓查詢做全表掃描就好了。

第二種情況是索引的選擇性較低。所謂索引的選擇性，是指不重複的索引值與表記錄數量的比值：

顯然選擇性的取值範圍為(0, 1]，選擇性越高的索引價值越大，這是由B+Tree的性質決定的。

例如，employees.titles表，如果title欄位經常被單獨查詢，是否需要建索引，我們看一下它的選擇性：

SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
+-------------+
| Selectivity |
+-------------+
|      0.0000 |
+-------------+

title的選擇性不足0.0001（精確值為0.00001579），所以實在沒有什麼必要為其單獨建索引。

有一種與索引選擇性有關的索引優化策略叫做前綴索引，就是用列的前綴代替整個列作為索引key，當前綴長度合適時，可以做到既使得前綴索引的選擇性接近全列索引，同時因為索引key變短而減少了索引文件的大小和維護開銷。下面以employees.employees表為例介紹前綴索引的選擇和使用。

假設employees表只有一個索引<emp_no>，那麼如果我們想按名字搜索一個人，就只能全表掃描了：

EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 300024 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

如果頻繁按名字搜索員工，這樣顯然效率很低，因此我們可以考慮建索引。有兩種選擇，建<first_name>或<first_name, last_name>，看下兩個索引的選擇性：

SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.0042 |
+-------------+
SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9313 |
+-------------+

<first_name>顯然選擇性太低，<first_name, last_name>選擇性很好，但是first_name和last_name加起來長度為30，有沒有兼顧長度和選擇性的辦法？可以考慮用first_name和last_name的前幾個字符建立索引，例如<first_name, left(last_name, 3)>，看看其選擇性：

SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.7879 |
+-------------+

選擇性還不錯，但離0.9313還是有點距離，那麼把last_name前綴加到4：

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
| 0.9007 |
+-------------+

這時選擇性已經很理想了，而這個索引的長度只有18，比<first_name, last_name>短了接近一半，我們把這個前綴索引建上：

ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

此時再執行一遍按名字查詢，比較分析一下與建索引前的結果：

SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                           |
+----------+------------+---------------------------------------------------------------------------------+
|       87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
|       90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
+----------+------------+---------------------------------------------------------------------------------+

性能的提升是顯著的，查詢速度提高了120多倍。

前綴索引兼顧索引大小和查詢速度，但是其缺點是不能用於ORDER BY和GROUP BY操作，也不能用於覆蓋索引

作者：張維鵬

原文連結：https://blog.csdn.net/a745233700/article/details/84455241

解析MySQL資料庫：「SQL優化」與「索引優化」

一、索引優化：

二、SQL語句優化：

三、索引的選擇性與前綴索引：