## 避免使用select * (外包項目 select * 很好用) ### 缺點 1. 會導致覆蓋索引失效 2. 當表字段數量多時, 會查詢出很多無效字段, 如果我們只需要其中 3、5個字段時，會浪費數據庫資源，增加數據傳輸時間 ## 優點 簡單方便 對于缺點一， 確定本表或者本條sql用不到覆蓋索引就無所謂了， 特別是外包項目，很多表都不帶加索引的。 對于缺點二， 如果像分頁查詢， 這點數據的資源消耗可忽略了 最重要的是，某些外包項目，需求更改頻繁，極其頻繁，select*的開發效率遠遠大于上面倆缺點 ## 最左前綴原則 最左前列開始并且不跳過索引中的列， 如：你家是三層樓，你得按照 1、2、3層的方式去第三層，不能跳過1去2、3，也不能跳過2直接從1到3 聯合索引在底層 B+樹 上是先從左列開始排序，比如 a、b、c 三列， 先排序a，當a相同時排序b，依次排序。索引查詢時，跳過a列去查詢，這時候b實際是相當于無序的，沒法去使用此聯合索引查詢。 ## like 左邊加% 不走索引 like "%wang"， 只要左邊有% 就不會走索引 字符串當索引列時，會比較字符串的每個字符進行排序，第一個相同再比較第二個，依此類推。也就是字符串的索引列都是根據每個字符排好序的，跳過前邊的索引字符時，索引列就沒法排序，無法使用索引。 like "%wang" 這種就相當于違反聯合索引中最左前綴原則。 ## 小表驅動大表 小表驅動大表，也就是說用小表的數據集驅動大表的數據集。 假如有order和user兩張表，其中order表有10000條數據，而user表有100條數據。 這時如果想查一下，所有有效的用戶下過的訂單列表。 可以使用in關鍵字實現： select * from order where user_id in (select id from user where status=1) 也可以使用 exists 關鍵字實現： select * from order where exists (select 1 from user where order.user_id = user.id and status=1) 前面提到的這種業務場景，使用in關鍵字去實現業務需求，更加合適。 為什么呢？ 因為如果sql語句中包含了in關鍵字，則它會優先執行in里面的子查詢語句，然后再執行in外面的語句。如果in里面的數據量很少，作為條件查詢速度更快。 而如果sql語句中包含了exists關鍵字，它優先執行exists左邊的語句（即主查詢語句）。然后把它作為條件，去跟右邊的語句匹配。如果匹配上，則可以查詢出數據。如果匹配不上，數據就被過濾掉了。 這個需求中，order表有10000條數據，而user表有100條數據。order表是大表，user表是小表。如果order表在左邊，則用in關鍵字性能更好。 總結一下： * in 適用于左邊大表，右邊小表。（oracle in 限制不能超過1000個， MySQL 是4M） * exists 適用于左邊小表，右邊大表。 不管是用in，還是exists關鍵字，其核心思想都是用小表驅動大表。 ## limit 如查詢用戶首單時間時，可以增加 limit 1 表示只返回一條 如果是深度分頁, 可以分頁條件查詢的sql寫在子查詢中 只返回id, 在外邊根據id在手動查詢數據，這樣可以大量減少回表，提高些性能。 mysql分頁是：把當前分頁前的數據也查詢出來，然后得到當前分頁的數據，前邊的再拋棄掉。如：查詢第100頁的數據，那么mysql會把前邊的99頁和當前100頁的數據都查詢出來，然后再放棄前邊的99頁，只留下需要的第100頁面數據。 ``` 請使用5.7以上版本, 5.5 會非常慢 120萬數據情況下 使用子查詢, 先查詢到數據的id, 再查詢其他字段, 在都有索引的情況下快了 0.359 秒 SELECT * FROM sys_user WHERE id IN ( SELECT t.id FROM ( SELECT id FROM sys_user WHERE `status` = 0 ORDER BY create_time DESC, id DESC LIMIT 826163, 10 ) AS t ) ORDER BY create_time DESC, id DESC; 直接分頁 2.009秒 SELECT * FROM sys_user WHERE `status` = 0 ORDER BY create_time DESC, id DESC LIMIT 826163, 10 ``` ## union all 和 union **Union**：對兩個結果集進行并集操作，**不包括****重復行**，同時進行默認規則的排序； **Union All**：對兩個結果集進行并集操作，**包括****重復行**，不進行排序； 因為沒有去重和排序，union all 效率比union高， 如果確定并集中不包含重復數據可用 union all ## 覆蓋索引 如果返回的字段不在索引中, 會進行回表(去主鍵索引中獲取其他要返回的字段) 覆蓋索引就是所有需要的字段在索引里(聯合索引需要注意字段順序)都包含了, 則不需要回表了 extra 有 Using index 表示使用了覆蓋索引 ## group by 先使用 where 把數據縮小范圍在分組，能在where用的條件不要放在having中 Where是在結果返回**之前**起作用的，Where中**不能使用聚合函數。** Having是在結果返回**之后**對查詢結果進行的過濾操作，在Having中**可以使用聚合函數** 注意當有聯合索引時, group by 的順序要和 聯合索引一至, 否則也用不上聯合索引, 而且像這樣的條件 date_format(create_time,'%Y-%m-%d') 也可能會導致查詢速度慢。都用到了聯合索引, 但是 date_format extra 是 ``` Using where; Using index; Using temporary; Using filesort ``` 不加 date_format extra 是 ``` Using where; Using index ``` 速度影響也很大, 導致出現了臨時表和排序.(索引是有序的了, 不應該再而外排序) ## 盡量避免在索引列使用 or 、 in、!= 并不是用了 or in 等就不用索引了，而是**MySQL會預估查詢結果數量，判斷全表查詢和使用索引那種方式更加高效**，當 or in 等查詢的結果集在總數據中占比過大，就有可能使用全表查詢了。 ## Join 表的數量限制 阿里的Java規范手冊里強制要求 join 不超過三張表 ## 設置合理的字段類型 像賬號狀態，審核狀態，性別，這種枚舉類可以使用 tinyint（） 長度固定的字符串字段，用char類型 長度可變的字符串字段，用varchar類型。 金額字段用decimal，避免精度丟失問題。 ## 索引創建規則 * 經常與其他表進行連接的表，在連接字段上應該建立索引 * 不要對有大量重復的值的字段建立索引（像性別這種字段） * 索引應該建在小字段上，對于大的文本字段，text類型字段，不要建索引 * 經常出現在Where子句中的字段，特別是大表的字段，應該建立索引 * 如果復合索引中的字段經常單獨出現在Where子句中，則分解為多個單字段索引 * 頻繁進行數據操作的表，不要建立太多的索引 * 不要在索引上進行任何操作，例如對索引進行（計算、函數、類型轉換），索引失效 * SQL傳參類型要和表字段類型一致, 防止MySQL隱式轉換導致索引失效 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件。 注：如果是做外包，那種小企業后臺管理，工期緊，任務重，上面的內容可以當做不知道，**干完要緊**。干完了才有時間學習或者玩耍。