簡介：以上文章講述的是【線上系統(tǒng)打日志你了解多少】接下來我總結(jié)一下【SQL調(diào)優(yōu)與設(shè)計(jì)規(guī)范】。覺得我還可以的可以加群一起督促學(xué)習(xí)探討技術(shù)。QQ群：1076570504 個(gè)人學(xué)習(xí)資料庫http://www.aolanghs.com/微信公眾號(hào)搜索【歡少的成長之路】

一、SQL調(diào)優(yōu)

優(yōu)化select*

1.盡量避免使用select *，返回?zé)o用的字段會(huì)降低查詢效率。如下：

SELECT * FROM Student

優(yōu)化方式：使用具體的字段代替*，只返回使用到的字段。

2.明知查詢一條數(shù)據(jù)集，后面一定要加上limit 1

SELECT * FROM Student where username=root and password=123456 limit 1

優(yōu)化in和not in

盡量避免使用in 和not in，會(huì)導(dǎo)致數(shù)據(jù)庫引擎放棄索引進(jìn)行全表掃描。如下：

SELECT * FROM t WHERE id IN (2,3)
SELECT * FROM t1 WHERE username IN (SELECT username FROM t2)

優(yōu)化方式：如果是連續(xù)數(shù)值，可以用between代替。如下：

SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查詢，可以用exists代替。如下：

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)0

優(yōu)化or

盡量避免使用or，會(huì)導(dǎo)致數(shù)據(jù)庫引擎放棄索引進(jìn)行全表掃描

SELECT * FROM t WHERE id = 1 OR id = 3

優(yōu)化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3

tip：如果or兩邊的字段是同一個(gè)，如例子中這樣。貌似兩種方式效率差不多，即使union掃描的是索引，or掃描的是全表

優(yōu)化運(yùn)算符

運(yùn)算能用 = 就不要用 <> ，增加索引使用幾率。

SELECT * FROM t WHERE id = 1

SELECT * FROM t WHERE id <> 1

優(yōu)化insert，update，delete

超過100萬以上的數(shù)據(jù)要把一個(gè)大的語句處理分解成小的處理語句

insert進(jìn)行插入操作的時(shí)候一定要指定列名，不允許直接數(shù)值

優(yōu)化where

where后面的列盡量被索引，這樣查詢效率是非常高的

SELECT * FROM t WHERE id = 1

優(yōu)化group by

group by后面的列有索引，索引可以消除排序帶來的CPU開銷，如果是前綴索引，是不能消除排序的。

優(yōu)化order by

order by后面的列有索引，索引可以消除排序帶來的CPU開銷，如果是前綴索引，是不能消除排序的。

排序字段順序，asc/desc升降要跟索引保持一致，充分利用索引的有序性來消除排序帶來的CPU開銷。

優(yōu)化limit

對(duì)于limit m, n分頁查詢，越往后面翻頁即m越大的情況下SQL的耗時(shí)會(huì)越來越長，對(duì)于這種應(yīng)該先取出主鍵id，然后通過主鍵id跟原表進(jìn)行Join關(guān)聯(lián)查詢。

二、設(shè)計(jì)規(guī)范

UDF用戶自定義函數(shù)

SQL語句的select后面使用了自定義函數(shù)UDF，SQL返回多少行，那么UDF函數(shù)就會(huì)被調(diào)用多少次，這是非常影響性能的

-- getSum是用戶自定義函數(shù) 根據(jù)getSum求成本的總和
select id, payment_id, cos_sn, getSum(cost) from tsransaction where status = 1 and create_time between '2021-01-01 10:00:00' and '2021-01-12 12:00:00';

AUTO_INCREMENT 自增

建表的時(shí)候主鍵id帶有AUTO_INCREMENT屬性，而且AUTO_INCREMENT=1，在InnoDB內(nèi)部是通過一個(gè)系統(tǒng)全局變量dict_sys.row_id來計(jì)數(shù)，row_id是一個(gè)8字節(jié)的bigint unsigned，InnoDB在設(shè)計(jì)時(shí)只給row_id保留了6個(gè)字節(jié)的長度，這樣row_id取值范圍就是0到2^48 - 1，如果id的值達(dá)到了最大值，下一個(gè)值就從0開始繼續(xù)循環(huán)遞增，在代碼中禁止指定主鍵id值插入。

NOT NULL不等于空

根據(jù)業(yè)務(wù)含義，盡量將字段都添加上NOT NULL DEFAULT VALUE屬性，如果列值存儲(chǔ)了大量的NULL，會(huì)影響索引的穩(wěn)定性

DEFAULT默認(rèn)值

在創(chuàng)建表的時(shí)候，建議每個(gè)字段盡量都有默認(rèn)值，禁止DEFAULT NULL

TEXT/BLOB 少用

創(chuàng)建表時(shí)如果有圖片什么的請(qǐng)采用本地存儲(chǔ)（對(duì)象存儲(chǔ)OSS），當(dāng)前的路徑存在數(shù)據(jù)庫字段中，杜絕直接存圖片的二進(jìn)制數(shù)據(jù)，如果真有特定的需求請(qǐng)分離出去單獨(dú)一個(gè)表存儲(chǔ)。而且操作那個(gè)表的時(shí)候不要用select*

text類型也是一樣的是一個(gè)大類型少用，能采用其他方案就其他方案！

知道的越多，不知道的就越多。找準(zhǔn)方向，堅(jiān)持自己的定位！加油向前不斷前行，終會(huì)有柳暗花明的一天！
創(chuàng)作不易，你們的支持就是對(duì)我最大認(rèn)可！
文章將持續(xù)更新，我們下期見！QQ群：1076570504 微信公眾號(hào)搜索【歡少的成長之路】請(qǐng)多多支持！