非常干的sql書寫規(guī)范建議超實(shí)用sql優(yōu)化技巧

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

1、干貨關(guān)于SQL書寫建議 &索引優(yōu)化的總結(jié)，你值得擁有前言平時(shí)寫sql寫的比較多，一直沒把優(yōu)化相關(guān)的知識(shí)整理記錄下來，本文章記錄對(duì)SQL優(yōu)化的一些技巧；我將結(jié)合demo（一個(gè)百萬級(jí)數(shù)據(jù)表），去實(shí)踐驗(yàn)證這些優(yōu)化技巧。測試用例接下來，我們創(chuàng)建一個(gè)測試表并生成100w條測試數(shù)據(jù)，有助演示或驗(yàn)證接下來的知識(shí)- 創(chuàng)建一個(gè)測試表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)建生成隨機(jī)數(shù)據(jù)的存儲(chǔ)過程

4、DROP PROCEDURE IF EXISTS create_test_data;DELIMITER ;CREATE PROCEDURE create_test_data(n INT) COMMENT 生成若干隨機(jī)數(shù)據(jù)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;- 返回隨機(jī)字符串的函數(shù)DROP

5、 FUNCTION IF EXISTS get_rand_str;DELIMITER ;CREATE FUNCTION get_rand_str(n INT) RETURNS varchar(100) COMMENT 返回隨機(jī)數(shù)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作為關(guān)聯(lián)條件ref 非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值得所有行，本質(zhì)上也是一種索引訪問，它返回所有匹配某個(gè)單獨(dú)值得行，然而它可能會(huì)找到多個(gè)符合條件的行，所以他應(yīng)該屬于查找和掃描的混合體range 只檢索給定范圍的行，一般就是where語句中出現(xiàn)了between,in等范圍的查詢。這種范圍掃描索引掃描比全表掃描要好index 遍歷全表，ALL區(qū)別為index類型只遍歷索

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

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

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，不管檢索結(jié)果有沒有重復(fù)，都會(huì)嘗試進(jìn)行合并，然后在輸出最終結(jié)果前進(jìn)行排序。如果已知檢

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

11、中使用in (mysql5.6及以上支持索引) like %關(guān)鍵字% where中對(duì)字段進(jìn)行運(yùn)算或使用函數(shù) 使用復(fù)合索引但沒有使用引導(dǎo)列我們知道測試表中b字段是有索引，c沒有索引，接下來逐一測試一下where條件中沒有匹配字段類型- b是字符串類型,where且寫了整數(shù)，雖然可以正常執(zhí)行sql，但是不會(huì)走索引EXPLAIN SELECT * from users where b=1;* NOT、!=*- 均會(huì)使索引失效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連接沒有索引的字段這種情況，假設(shè)它走了b的索引，但是走到c查詢條件時(shí)，它還得全表掃描- 也就是需要三步過程：全表掃描+索引掃描+合并。所以O(shè)R會(huì)導(dǎo)致索引失效- 注意，測試表中c是沒索引的，如果c也有索引，用or其實(shí)是OK的EXPLAIN SELECT * from users where b=a or c=a- 優(yōu)化方式1.改用 inSELECT * from users where b in (b,bbb)2.UNION - 對(duì)于or，我們可以這樣優(yōu)化我們的sql,雖然第二條沒有走索引,但

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

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

15、 正例 只要出現(xiàn)a即可EXPLAIN SELECT * from users where a=a and b=b ;EXPLAIN SELECT * from users where a=a and c=c ;limit分頁優(yōu)化我們?nèi)粘Ｗ龇猪撔枨髸r(shí)，一般會(huì)用 limit 實(shí)現(xiàn)- 常用做法SELECT * from users LIMIT 1000000,10當(dāng)偏移量最大的時(shí)候，查詢效率就會(huì)越低，因?yàn)镸ysql并非是跳過偏移量直接去取后面的數(shù)據(jù)，而是先把偏移量+要取的條數(shù)，然后再把前面偏移量這一段的數(shù)據(jù)拋棄掉再返回的。優(yōu)化分頁是需要跟業(yè)務(wù)結(jié)合，這里提供幾種解決方案，沒有最好只有最合適where加

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

17、 TEXT列，一般比較少用，因?yàn)橄翊笪谋镜臋z索都會(huì)采用一些全文檢索框架如elasticsearch，而不是在數(shù)據(jù)庫里檢索。- 單列索引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)化索引與字段選擇性如下兩個(gè)字段，郵箱、用戶名

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

19、sers (email,age);- 反例CREATE INDEX index_name ON users (age,email);聯(lián)合索引能為前綴單列，復(fù)列提供幫助- 聯(lián)合索引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=?(只要三者都出現(xiàn),順序打亂都沒問題，mysql會(huì)自動(dòng)給你排成上一句的順序)- 無效where b=? and c=? where b=? 根據(jù)上面的規(guī)律，其實(shí)可以發(fā)現(xiàn)

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

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