《非常干的sql書寫規(guī)范建議超實用sql優(yōu)化技巧》由會員分享，可在線閱讀，更多相關《非常干的sql書寫規(guī)范建議超實用sql優(yōu)化技巧（11頁珍藏版）》請在裝配圖網上搜索。

1、干貨關于SQL書寫建議 &索引優(yōu)化的總結，你值得擁有前言平時寫sql寫的比較多，一直沒把優(yōu)化相關的知識整理記錄下來，本文章記錄對SQL優(yōu)化的一些技巧；我將結合demo（一個百萬級數據表），去實踐驗證這些優(yōu)化技巧。測試用例接下來，我們創(chuàng)建一個測試表并生成100w條測試數據，有助演示或驗證接下來的知識- 創(chuàng)建一個測試表CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, a varchar(255) DEFAULT NULL, b varchar(255) DEFAULT NULL, c varchar(11) DEFAULT NULL,

2、 d int(2) DEFAULT NULL, PRIMARY KEY (id), KEY index_name (a,b,c) USING BTREE, KEY d (d), KEY b (b) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;- - Records of users- -INSERT INTO users VALUES (1, a, b, c, 1);INSERT INTO users VALUES (2, asd, 785qwe, 2, 2);INSERT INTO users VALUES (3, wer, 12

3、3, 1, 3);INSERT INTO users VALUES (4, 左先生, 123, 1, 4);INSERT INTO users VALUES (5, qwe1, wq12, 2, 5);INSERT INTO users VALUES (6, qwe, 123, 2, null);INSERT INTO users VALUES (7, 1, 1, 1, null);INSERT INTO users VALUES (8, w, 1, 1, null);INSERT INTO users VALUES (9, aa, 1, null, null);- 創(chuàng)建生成隨機數據的存儲過程

4、DROP PROCEDURE IF EXISTS create_test_data;DELIMITER ;CREATE PROCEDURE create_test_data(n INT) COMMENT 生成若干隨機數據BEGIN DECLARE i INT DEFAULT 1; WHILE i n DO INSERT INTO test.users (a, b, c) VALUES ( get_rand_str (10), get_rand_str (10), get_rand_str (10) ); SET i = i + 1; END WHILE;END;- 返回隨機字符串的函數DROP

5、 FUNCTION IF EXISTS get_rand_str;DELIMITER ;CREATE FUNCTION get_rand_str(n INT) RETURNS varchar(100) COMMENT 返回隨機數BEGIN DECLARE char_str varchar(100) DEFAULT abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789; DECLARE return_str varchar(255) DEFAULT ; DECLARE i INT DEFAULT 0; WHILE i eq_

6、ref ref range index all ）const 表示通過索引一次就找到了，const用于比較primary key 或者 unique索引eq_ref 多表連接中使用primary key或者 unique key作為關聯條件ref 非唯一性索引掃描，返回匹配某個單獨值得所有行，本質上也是一種索引訪問，它返回所有匹配某個單獨值得行，然而它可能會找到多個符合條件的行，所以他應該屬于查找和掃描的混合體range 只檢索給定范圍的行，一般就是where語句中出現了between,in等范圍的查詢。這種范圍掃描索引掃描比全表掃描要好index 遍歷全表，ALL區(qū)別為index類型只遍歷索

7、引樹 ( select索引列或order by 主鍵 兩種情況，但是where沒用到索引 )all 遍歷全表以找到匹配的行一般保證查詢至少達到range級別，最好能達到ref。 key 本次查詢最終用到哪個索引 key_len 索引使用的前綴長度或整個長度 row 掃描過的記錄行數- 測試一下,其中b字段有索引，c字段沒有索引SELECT * from users where b=隨便啦，測試而已; - 花費0.001sSELECT * from users where c=隨便啦，測試而已; - 花費0.306sSQL優(yōu)化建議少用select *老生常談，大家都懂。合理使用limit 1如果

8、知道查詢結果只有一條或者只要一條記錄，建議用limit 1，當然，如果已存在唯一索引就沒必要用。合理使用joinInner join 內連接，在兩張表進行連接查詢時，只保留兩張表中完全匹配的結果集left join 在兩張表進行連接查詢時，會返回左表所有的行，即使在右表中沒有匹配的記錄right join 在兩張表進行連接查詢時，會返回右表所有的行，即使在左表中沒有匹配的記錄都滿足SQL需求的前提下，推薦優(yōu)先使用Inner join（內連接），如果要使用left join，左邊表數據結果盡量小，如果有條件的盡量放到左邊處理。批量插入數據數量不大的情況下，一條一條插入問題不大。如果數據量兩，使用

9、批量插入語句效率更高for() INSERT INTOtest.users(a,b,c) VALUES (hLQK51GcL6,1DXIzvIS3t,4LsQGKva6U)更優(yōu)：INSERT INTO test.users (a, b, c)VALUES- 此處可自行拼接語句，如使用mybatis等 ( hLQK51GcL6, 1DXIzvIS3t, 4LsQGKva6U ), ( hLQK51GcL6, 1DXIzvIS3t, 4LsQGKva6U )盡量用union all替換 union如果使用union，不管檢索結果有沒有重復，都會嘗試進行合并，然后在輸出最終結果前進行排序。如果已知檢

10、索結果沒有重復記錄，使用union all 代替union，這樣會提高效率。- 執(zhí)行時間0.06sSELECT *FROM usersLIMIT 0, 10000UNION ALLSELECT *FROM usersLIMIT 10000,20000- 執(zhí)行時間0.2sSELECT *FROM usersLIMIT 0, 10000UNIONSELECT *FROM usersLIMIT 10000,20000會使索引失效的幾種情況 where條件中沒有匹配字段類型 where中使用NOT、!=、IN (IN Mysql5.6及以上支持索引) where中使用OR連接沒有索引的字段 where

11、中使用in (mysql5.6及以上支持索引) like %關鍵字% where中對字段進行運算或使用函數 使用復合索引但沒有使用引導列我們知道測試表中b字段是有索引，c沒有索引，接下來逐一測試一下where條件中沒有匹配字段類型- b是字符串類型,where且寫了整數，雖然可以正常執(zhí)行sql，但是不會走索引EXPLAIN SELECT * from users where b=1;* NOT、!=*- 均會使索引失效EXPLAIN SELECT * from users where b not in(a);EXPLAIN SELECT * from users where b is not

12、null; EXPLAIN SELECT * from users where b !=aOR- 用or連接沒有索引的字段這種情況，假設它走了b的索引，但是走到c查詢條件時，它還得全表掃描- 也就是需要三步過程：全表掃描+索引掃描+合并。所以OR會導致索引失效- 注意，測試表中c是沒索引的，如果c也有索引，用or其實是OK的EXPLAIN SELECT * from users where b=a or c=a- 優(yōu)化方式1.改用 inSELECT * from users where b in (b,bbb)2.UNION - 對于or，我們可以這樣優(yōu)化我們的sql,雖然第二條沒有走索引,但

13、是第一條sql就走了索引啦SELECT * from users where b = b UNION SELECT * from users where c = c LIKE- %關鍵字% 會讓索引失效SELECT * from users where a like %abc%- 正例,關鍵字%是可以使用索引提高查詢效率,類似前綴索引SELECT * from users where a like abc%where中對字段進行運算或使用函數- 均會使索引失效EXPLAIN SELECT * from users where YEAR(ctime) = 2020;EXPLAIN SELECT

14、* from users where d+1=2;大于號與小于號- 在mysql中大于號小于號是個神奇的東西，使用它有時候會走索引有時候不走，據說是和結果的數量有關的，當數量較少（網上查到是有一個比例）時時使用索引的- 建議能用BETWEEN就不要用2020-03-30 19:45:30使用復合索引但沒有使用引導列- 可知表中有復合索引idx_abc(a,b,c),還有一個idx_b索引，我們先把idx_b刪除- 以下sql 沒有用到引導列所以不會走idx_abc索引，引導列只指復合索引的第一個字段EXPLAIN SELECT * from users where c=c and b=b ;-

15、 正例 只要出現a即可EXPLAIN SELECT * from users where a=a and b=b ;EXPLAIN SELECT * from users where a=a and c=c ;limit分頁優(yōu)化我們日常做分頁需求時，一般會用 limit 實現- 常用做法SELECT * from users LIMIT 1000000,10當偏移量最大的時候，查詢效率就會越低，因為Mysql并非是跳過偏移量直接去取后面的數據，而是先把偏移量+要取的條數，然后再把前面偏移量這一段的數據拋棄掉再返回的。優(yōu)化分頁是需要跟業(yè)務結合，這里提供幾種解決方案，沒有最好只有最合適where加

16、上時間篩選比如只獲取最近一年的數據、只獲取今年的數據 where createtime2020-01-01放棄選頁，即只有上一頁下一頁1. 第一頁直接查2. 獲得第一頁max(id)，如123，一般是最后一條數據，3. 然后查詢帶上索引，這樣每次只要掃描10條數據 where id123 limit 10限制頁數如只允許獲取前100頁索引優(yōu)化建立索引mysql中索引一共分為主鍵索引、唯一索引、普通索引、全文索引。常用的都是前三種，第一種跟隨主鍵，無需手動創(chuàng)建，而第四種全文索引用于全文搜索。只有InnoDB和 MyISAM存儲引擎支持 FULLTEXT索引和僅適用于 CHAR， VARCHAR和

17、 TEXT列，一般比較少用，因為像大文本的檢索都會采用一些全文檢索框架如elasticsearch，而不是在數據庫里檢索。- 單列索引CREATE INDEX index_name ON users (name);- 多列索引CREATE INDEX index_name ON users (name,age);- 唯一索引，單列索引CREATE UNIQUE INDEX index_name ON users (name);- 唯一索引，多列索引CREATE UNIQUE INDEX index_name ON users (name,age);優(yōu)化索引與字段選擇性如下兩個字段，郵箱、用戶名

18、這種選擇性較高的字符串是比較適合做索引，而性別這種比較單一的字段，建索引效率并不會提高太多，但如果存在男極多女極少的情況下，也可以考慮建索引。另外如果有一個CHAR(255)的列，如果在前10個或20個字符內，多數值是唯一的，這種情況也屬于選擇性較低的字段，不適合做索引| email | age | username | asdasda | 男 | 小明 | 123basb1 | 女 | 小紅 |聯合索引的順序問題建立聯合索引的時候往往也需要考慮索引的順序，以email與age為例，選擇性高的字段應該排在age前面，如email。- 正確CREATE INDEX index_name ON u

19、sers (email,age);- 反例CREATE INDEX index_name ON users (age,email);聯合索引能為前綴單列，復列提供幫助- 聯合索引idx_1(a,b,c)- 有效where a=? where a=? and b=? where a=? and c=? (mysql5.6及以上才支持)where a=? and b=? and c=?where c=? and b=? and a=?(只要三者都出現,順序打亂都沒問題，mysql會自動給你排成上一句的順序)- 無效where b=? and c=? where b=? 根據上面的規(guī)律，其實可以發(fā)現

20、如果where里面如果沒有a，那么都不會走索引。這里引入一個概念叫“ 引導列 ”，在聯合索引中，排在第一位的就叫引導列，只有where條件中包含引導列，該查詢才會走索引。為了理解，其實當我們創(chuàng)建一個聯合索引的時候，如(idx1,idx2,idx3)，相當于創(chuàng)建了（idx1）、(idx1,idx2)和(idx1,idx2,idx3)三個索引，當然實際過程中不應該建3個索引，減少不要要的冗余。索引覆蓋掃描索引覆蓋掃描是指根據字段A查詢字段B，建立索引idx(a,b)會比單一索引idx(a)效率更高，如現實場景中,系統(tǒng)經常會根據用戶名查詢用戶密碼,進行登錄操作,針對此操作我們對用戶名在前密碼在后建立

21、聯合索,會比只建立單一索引查詢效率更好。- 根據用戶名查詢用戶密碼SELECT pwd from users where username=a;- 更優(yōu)做法,查詢時不需要回表查詢pwd字段，減少了IO開銷idx_1(username,pwd);- 一般的做法idx_1(username);避免冗余的索引重復的索引需要維護，并且優(yōu)化器在優(yōu)化查詢的時候也需要逐個地進行考慮，這會影響性能的反例：idx(a)idx(a,b)正例：組合索引（A，B）相當于創(chuàng)建了（A）和（A，B）索引idx(a,b)另外索引并不是越多越好，索引雖然提高了查詢的效率，但是也降低了插入和更新的效率 。 一個表的索引數最好不要超過5個，若太多需要考慮一些索引是否沒有存在的必要