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

1、「干貨」關(guān)于SQL書(shū)寫(xiě)建議 &索引優(yōu)化的總結(jié)，你值得擁有 前言 平時(shí)寫(xiě)sql寫(xiě)的比較多，一直沒(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

2、 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 -- ----------

3、------------------ 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', 'wq1

4、2', '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

5、_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

6、(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

7、 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(ret

8、urn_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); Explain Explain是確定一個(gè)SQL是否走索引最簡(jiǎn)單的辦法，我們用此方法可以對(duì)SQL進(jìn)行調(diào)優(yōu)，本文章只需關(guān)注以下項(xiàng)目，關(guān)于Exolain的具

9、體說(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等范圍

10、的查詢。這種范圍掃描索引掃描比全表掃描要好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.001s SELECT * from us

11、ers where c='隨便啦，測(cè)試而已'; -- 花費(fèi)0.306s SQL優(yōu)化建議 少用select * 老生常談，大家都懂。 合理使用limit 1 如果知道查詢結(jié)果只有一條或者只要一條記錄，建議用limit 1，當(dāng)然，如果已存在唯一索引就沒(méi)必要用。 合理使用join Inner join 內(nèi)連接，在兩張表進(jìn)行連接查詢時(shí)，只保留兩張表中完全匹配的結(jié)果集 left join 在兩張表進(jìn)行連接查詢時(shí)，會(huì)返回左表所有的行，即使在右表中沒(méi)有匹配的記錄 right join 在兩張表進(jìn)行連接查詢時(shí)，會(huì)返回右表所有的行，即使在左表中沒(méi)有匹配的記錄 都滿足SQL需求的前提下

12、，推薦優(yōu)先使用Inner join（內(nèi)連接），如果要使用left join，左邊表數(shù)據(jù)結(jié)果盡量小，如果有條件的盡量放到左邊處理。 批量插入數(shù)據(jù) 數(shù)量不大的情況下，一條一條插入問(wèn)題不大。如果數(shù)據(jù)量?jī)桑褂门坎迦胝Z(yǔ)句效率更高 for(){ INSERT INTO`test`.`users`(`a`,`b`,`c`) VALUES ('hLQK51GcL6','1DXIzvIS3t','4LsQGKva6U') } 更優(yōu)： INSERT INTO `test`.`users` (`a`, `b`, `c`) VALUES -- 此處可自行拼接語(yǔ)句，如使用mybatis

13、等 ( '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.06s SELEC

14、T * FROM users LIMIT 0, 10000 UNION ALL SELECT * FROM users LIMIT 10000, 20000 -- 執(zhí)行時(shí)間0.2s SELECT * FROM users LIMIT 0, 10000 UNION SELECT * FROM users LIMIT 10000, 20000 會(huì)使索引失效的幾種情況 · where條件中沒(méi)有匹配字段類型 · where中使用NOT、!=、IN ("IN" Mysql5.

15、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且寫(xiě)了整數(shù)，雖然可以正常執(zhí)行sql，但是不會(huì)走索引 EXPLAIN SELECT * from users where b=1; ** NOT、!=** -- 均會(huì)使索引失效 EXPLAIN SELECT *

16、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

17、='a' -- 優(yōu)化方式 1.改用 in SELECT * 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)鍵字%"是可以

18、使用索引提高查詢效率,類似前綴索引 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就不要

19、用>< -- 可以自行按時(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

20、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í)間篩選

21、 比如只獲取最近一年的數(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索引和僅

22、適用于 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_na

23、me 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í)候往往也需要考慮索引的順序，以emai

24、l與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=?

25、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è)索引，減少

26、不要要的冗余。 索引覆蓋掃描 索引覆蓋掃描是指根據(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)銷(xiāo) 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)有存在的必要