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

干貨關(guān)于SQL書寫建議 &索引優(yōu)化的總結(jié)，你值得擁有前言平時(shí)寫sql寫的比較多，一直沒(méi)把優(yōu)化相關(guān)的知識(shí)整理記錄下來(lái)，本文章記錄對(duì)SQL優(yōu)化的一些技巧；我將結(jié)合demo（一個(gè)百萬(wàn)級(jí)數(shù)據(jù)表），去實(shí)踐驗(yàn)證這些優(yōu)化技巧。測(cè)試用例接下來(lái)，我們創(chuàng)建一個(gè)測(cè)試表并生成100w條測(cè)試數(shù)據(jù)，有助演示或驗(yàn)證接下來(lái)的知識(shí)- 創(chuàng)建一個(gè)測(cè)試表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, 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', '123', '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ǔ)過(guò)程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 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 < n DO SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1); SET i = i+1; END WHILE; RETURN return_str;END;- 生成100w條隨機(jī)數(shù)據(jù)- 預(yù)計(jì)花費(fèi)半小時(shí)或更久，其實(shí)也可以生成1w條。主要是數(shù)據(jù)多一點(diǎn)，更能反映出索引的重要性call create_test_data(100*10000);ExplainExplain是確定一個(gè)SQL是否走索引最簡(jiǎn)單的辦法，我們用此方法可以對(duì)SQL進(jìn)行調(diào)優(yōu)，本文章只需關(guān)注以下項(xiàng)目，關(guān)于Exolain的具體說(shuō)明可查閱具體說(shuō)明· type（ 從最好到最差依次是 const > eq_ref > ref > range > index > all ）const 表示通過(guò)索引一次就找到了，const用于比較primary key 或者 unique索引eq_ref 多表連接中使用primary key或者 unique key作為關(guān)聯(lián)條件ref 非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值得所有行，本質(zhì)上也是一種索引訪問(wèn)，它返回所有匹配某個(gè)單獨(dú)值得行，然而它可能會(huì)找到多個(gè)符合條件的行，所以他應(yīng)該屬于查找和掃描的混合體range 只檢索給定范圍的行，一般就是where語(yǔ)句中出現(xiàn)了between,in等范圍的查詢。這種范圍掃描索引掃描比全表掃描要好index 遍歷全表，ALL區(qū)別為index類型只遍歷索引樹(shù) ( select索引列或order by 主鍵 兩種情況，但是where沒(méi)用到索引 )all 遍歷全表以找到匹配的行一般保證查詢至少達(dá)到range級(jí)別，最好能達(dá)到ref。· key 本次查詢最終用到哪個(gè)索引· key_len 索引使用的前綴長(zhǎng)度或整個(gè)長(zhǎng)度· row 掃描過(guò)的記錄行數(shù)- 測(cè)試一下,其中b字段有索引，c字段沒(méi)有索引SELECT * from users where b='隨便啦，測(cè)試而已' - 花費(fèi)0.001sSELECT * from users where c='隨便啦，測(cè)試而已' - 花費(fèi)0.306sSQL優(yōu)化建議少用select *老生常談，大家都懂。合理使用limit 1如果知道查詢結(jié)果只有一條或者只要一條記錄，建議用limit 1，當(dāng)然，如果已存在唯一索引就沒(méi)必要用。合理使用joinInner join 內(nèi)連接，在兩張表進(jìn)行連接查詢時(shí)，只保留兩張表中完全匹配的結(jié)果集left join 在兩張表進(jìn)行連接查詢時(shí)，會(huì)返回左表所有的行，即使在右表中沒(méi)有匹配的記錄right join 在兩張表進(jìn)行連接查詢時(shí)，會(huì)返回右表所有的行，即使在左表中沒(méi)有匹配的記錄都滿足SQL需求的前提下，推薦優(yōu)先使用Inner join（內(nèi)連接），如果要使用left join，左邊表數(shù)據(jù)結(jié)果盡量小，如果有條件的盡量放到左邊處理。批量插入數(shù)據(jù)數(shù)量不大的情況下，一條一條插入問(wèn)題不大。如果數(shù)據(jù)量?jī)?，使用批量插入語(yǔ)句效率更高for() INSERT INTOtest.users(a,b,c) VALUES ('hLQK51GcL6','1DXIzvIS3t','4LsQGKva6U')更優(yōu)：INSERT INTO test.users (a, b, c)VALUES- 此處可自行拼接語(yǔ)句，如使用mybatis等 ( 'hLQK51GcL6', '1DXIzvIS3t', '4LsQGKva6U' ), ( 'hLQK51GcL6', '1DXIzvIS3t', '4LsQGKva6U' )盡量用union all替換 union如果使用union，不管檢索結(jié)果有沒(méi)有重復(fù)，都會(huì)嘗試進(jìn)行合并，然后在輸出最終結(jié)果前進(jìn)行排序。如果已知檢索結(jié)果沒(méi)有重復(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條件中沒(méi)有匹配字段類型· where中使用NOT、!=、IN ("IN" Mysql5.6及以上支持索引)· where中使用OR連接沒(méi)有索引的字段· where中使用in (mysql5.6及以上支持索引)· like '%關(guān)鍵字%'· where中對(duì)字段進(jìn)行運(yùn)算或使用函數(shù)· 使用復(fù)合索引但沒(méi)有使用"引導(dǎo)列"我們知道測(cè)試表中b字段是有索引，c沒(méi)有索引，接下來(lái)逐一測(cè)試一下where條件中沒(méi)有匹配字段類型- 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 null; EXPLAIN SELECT * from users where b !='a'OR- 用or連接沒(méi)有索引的字段這種情況，假設(shè)它走了b的索引，但是走到c查詢條件時(shí)，它還得全表掃描- 也就是需要三步過(guò)程：全表掃描+索引掃描+合并。所以O(shè)R會(huì)導(dǎo)致索引失效- 注意，測(cè)試表中c是沒(méi)索引的，如果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,雖然第二條沒(méi)有走索引,但是第一條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 * from users where d+1=2;大于號(hào)與小于號(hào)- 在mysql中大于號(hào)小于號(hào)是個(gè)神奇的東西，使用它有時(shí)候會(huì)走索引有時(shí)候不走，據(jù)說(shuō)是和結(jié)果的數(shù)量有關(guān)的，當(dāng)數(shù)量較少（網(wǎng)上查到是有一個(gè)比例）時(shí)時(shí)使用索引的- 建議能用BETWEEN就不要用><- 可以自行按時(shí)間篩選出不同的數(shù)量測(cè)試SELECT id from users where ctime>'2020-03-30 19:45:30'使用復(fù)合索引但沒(méi)有使用"引導(dǎo)列"- 可知表中有復(fù)合索引idx_abc(a,b,c),還有一個(gè)idx_b索引，我們先把idx_b刪除- 以下sql 沒(méi)有用到"引導(dǎo)列"所以不會(huì)走idx_abc索引，"引導(dǎo)列"只指復(fù)合索引的第一個(gè)字段EXPLAIN SELECT * from users where c='c' and b='b' ;- 正例 只要出現(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è)優(yōu)化我們?nèi)粘Ｗ龇猪?yè)需求時(shí)，一般會(huì)用 limit 實(shí)現(xiàn)- 常用做法SELECT * from users LIMIT 1000000,10當(dāng)偏移量最大的時(shí)候，查詢效率就會(huì)越低，因?yàn)镸ysql并非是跳過(guò)偏移量直接去取后面的數(shù)據(jù)，而是先把偏移量+要取的條數(shù)，然后再把前面偏移量這一段的數(shù)據(jù)拋棄掉再返回的。優(yōu)化分頁(yè)是需要跟業(yè)務(wù)結(jié)合，這里提供幾種解決方案，沒(méi)有最好只有最合適where加上時(shí)間篩選比如只獲取最近一年的數(shù)據(jù)、只獲取今年的數(shù)據(jù) where createtime>'2020-01-01'放棄選頁(yè)，即只有上一頁(yè)下一頁(yè)1. 第一頁(yè)直接查2. 獲得第一頁(yè)max(id)，如123，一般是最后一條數(shù)據(jù)，3. 然后查詢帶上索引，這樣每次只要掃描10條數(shù)據(jù) where id>123 limit 10限制頁(yè)數(shù)如只允許獲取前100頁(yè)索引優(yōu)化建立索引mysql中索引一共分為主鍵索引、唯一索引、普通索引、全文索引。常用的都是前三種，第一種跟隨主鍵，無(wú)需手動(dòng)創(chuàng)建，而第四種全文索引用于全文搜索。只有InnoDB和 MyISAM存儲(chǔ)引擎支持 FULLTEXT索引和僅適用于 CHAR， VARCHAR和 TEXT列，一般比較少用，因?yàn)橄翊笪谋镜臋z索都會(huì)采用一些全文檢索框架如elasticsearch，而不是在數(shù)據(jù)庫(kù)里檢索。- 單列索引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è)字段，郵箱、用戶名這種選擇性較高的字符串是比較適合做索引，而性別這種比較單一的字段，建索引效率并不會(huì)提高太多，但如果存在男極多女極少的情況下，也可以考慮建索引。另外如果有一個(gè)CHAR(255)的列，如果在前10個(gè)或20個(gè)字符內(nèi)，多數(shù)值是唯一的，這種情況也屬于選擇性較低的字段，不適合做索引| email | age | username | asdasda | 男 | 小明 | 123basb1 | 女 | 小紅 |聯(lián)合索引的順序問(wèn)題建立聯(lián)合索引的時(shí)候往往也需要考慮索引的順序，以email與age為例，選擇性高的字段應(yīng)該排在age前面，如email。- 正確CREATE INDEX index_name ON users (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),順序打亂都沒(méi)問(wèn)題，mysql會(huì)自動(dòng)給你排成上一句的順序)- 無(wú)效where b=? and c=? where b=? 根據(jù)上面的規(guī)律，其實(shí)可以發(fā)現(xiàn)如果where里面如果沒(méi)有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í)際過(guò)程中不應(yīng)該建3個(gè)索引，減少不要要的冗余。索引覆蓋掃描索引覆蓋掃描是指根據(jù)字段A查詢字段B，建立索引idx(a,b)會(huì)比單一索引idx(a)效率更高，如現(xiàn)實(shí)場(chǎng)景中,系統(tǒng)經(jīng)常會(huì)根據(jù)用戶名查詢用戶密碼,進(jìn)行登錄操作,針對(duì)此操作我們對(duì)用戶名在前密碼在后建立聯(lián)合索,會(huì)比只建立單一索引查詢效率更好。- 根據(jù)用戶名查詢用戶密碼SELECT pwd from users where username='a'- 更優(yōu)做法,查詢時(shí)不需要回表查詢pwd字段，減少了IO開(kāi)銷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ù)最好不要超過(guò)5個(gè)，若太多需要考慮一些索引是否沒(méi)有存在的必要