寫SQL經驗，多次加班教訓總結

下面的是SQL要注意的問題，有些是我踩過的坑，有些是同事踩的，大家看的時候可能沒啥感覺，等到不小心搞出問題后會想起來的哈哈哈

select 子陳述句

(1) GROUP_CONCAT

謹慎考慮使用

這方法是分組的形式將相同的行進行字串聚合，字符之間默認以逗號間隔，有點耗時的，用了時候考慮一下要聚合的行數，如果行數多的話慎用，有同事之前SQL超時就因為這個

至于它的用處，舉個例子：

要把全校的同學以班級為單位分班

select class as 班級 , GROUP_CONCAT(studentName) as 學生名單 from student group by class

(2) 內查詢

我不知道怎么稱呼，我也沒見過有人這么寫過，長這樣子：

select studentName as 學生姓名, (select grade from student_grade where class = '1班') as 成績 from student_info where class = '1班'  

假設1班有50個人

根據SQL的執行順序：先from -> where ->select，我們每查一個同學的姓名，就去查一次全班的成績(即select grade就執行1次)；

一句話，外查詢的SQL回傳多少行(50個studentName 共50行)，內查詢就查幾次，共多查了50次，如果外查詢回傳了50萬行呢

建議內查詢少用

from子陳述句

(1) join盡量別連接資料量大的表

join需要用到臨時表記憶體，會生成一張臨時表，對于幾百萬資料量的大表，用 left join 就得謹慎點 了：

錯誤示范：

select .. from 600萬行的表  left join 1000行的表  on ...

對于 left join，不管on后面的條件是什么，都會生成一個600萬行的臨時表，你的資料庫的臨時表記憶體八成會爆掉的哦

因為left join導致左邊的大表幾乎是被全表掃描了，那么索引大概率也不會生效了，

left join + where 是說在生成臨時表后，用where的條件對臨時表進行過濾刪減

where 子陳述句要保證索引不失效

(1) 索引列運算(少點)

錯誤示范：

# (1) DATE_FORMAT會導致日期索引失效,假設create_time是索引

select record_info as 日志內容 from record_log where  DATE_FORMAT(create_time,'%Y-%m-%d')='2021-02-23'

# (2) age是int 型, 且作為索引列, 參與了算術運算, 導致索引失效

'age' int(11) Not NULL DEFAULT 0 COMMENT '年齡'
select studentName as 學生姓名 from student_info where age+1 = 18

第一條SQL的DATE_FORMAT會導致日期索引失效，因為資料庫會對create_time進行逐行計算后再比對，那基本是全表掃描

第二條SQL對索引列age進行運算，會導致索引失效

(2)避免自動資料型別轉換

表里的欄位是什么型別，傳入的引數就給什么型別的

錯誤示范：

# age是字符型, 且為索引
'age' varchar(6) Not NULL DEFAULT '0' COMMENT '年齡'

alter table student_info add index index_age('age')

select studentName as 學生姓名 from student_info where age = 18

age作為索引列

• 表定義的是字符型的age：傳入int型的18會 索引失效，萬一資料表的age有 “18.0”、"18"這兩種情況呢，這樣的話表里的字符型age就沒法和傳入的int型 18直接比較了，最終導致age被逐行轉換成int型別 后再與18比較

(3) 帶上分庫鍵

如果專案的資料庫有分庫分表的話，查詢的時候盡量帶上分庫鍵，它能將SQL陳述句分發到指定的庫表去執行，我們這的DRDS是這樣子的

如果不帶分庫鍵(拆分鍵)的話，會導致SQL陳述句在全庫都進行掃描執行，很慢的

(4)復合索引的順序

創建復合索引
alter table student_info add index index_collection('age','studentName','sex')

只有以下三種where陳述句的順序會走復合索引：

• age，studentName，sex
• age，studentName
• age

另外，第一索引列age涉及 “ >，<，between and ”，就停用索引了，

因為涉及范圍查詢 時，B+樹的索引查找是 “ 直接從左到右的方式遍歷葉子節點組成的鏈表 ”，而不是從根節點從上往下查找了

group by和order by帶索引很快的

group by和order by后面帶索引能降低開銷

表結構定義

(1) 少點Default NULL

特別是索引列的定義為Default NULL的話會很影響穩定性

列的定義最好有個默認值，即NOT NULL DEFAULT VALUE

(2) 欄位的定義給個注釋COMMENT

特別是表明 狀態型別 的欄位，要給出所有狀態的數值和對應的含義，例如：

`state` int(11) NOT NULL DEFAULT 0  COMMENT'0 進行中,  1 完成,   2 失效'

(3) 少用text型別

text是長文本的資料型別，mysql服務器將text資料傳回客戶端需要消耗大量的網路帶寬，而服務器查詢的時候將text資料從磁盤加載到記憶體又需要大量的IO帶寬，

因此，在定義欄位的資料型別時，text一般只適用于無法確定資料的長度的時候使用，其他情況慎用，

無法確定資料長度的情況一般是存盤json串的時候，但是我們也不是用text，而是用json資料型別，因為如果要改json串的內容的話，對json資料型別修改比text方便得多

索引設定

(1) 索引的區分度要高

什么是區分度高？就是索引列的欄位值最好都不一樣，重復的越少，區分度越高，主鍵就是區分度最好的，每一行都是不重復的值，B+樹很容易就將所有的行劃分開來，

那種 區分度低的列最好別上索引，比如性別sex，無非就是男或女，會有很多資料行重復，想象一下：

如果要查出 成績高于90分的男生是誰，一共50個人，有49個男生…

在成績表grade 將性別sex設定為索引
alter table grade add index index_sex('sex')

select studentName from grade where sex='男' and grade>90 

預想的執行情況是把49個男生拿出來，再一個個對比成績，這跟沒上索引差不多…

因為 索引sex=‘男’ 出現的頻率過高，估計MySQL的執行策略會變成全表掃描也說不定呢

然而我司就有這種問題，咱也不敢說什么，，，

(2) 作為索引的欄位的資料長度最好小一點

B+樹的結點要存盤索引列的每一個值，如果索引列的每個值都很大的話，MySQL會在把索引加載進入記憶體的程序中消耗大量的IO帶寬

另外，索引欄位的資料長度小的話，占用的記憶體就小，你知道的，innodb_buffer_pool_size設定的快取索引的空間是有限的， 索引欄位的資料長度越小，記憶體就能夠容納的更多的鍵值，提高1次就能查找到目標值的幾率，1次找不到，就得從磁盤再次加載其他索引去查

能用int型別，就盡量不要用BigInt

主鍵更應該讓資料長度小一點，每個二級索引(除聚簇索引外的索引)都會存放索引欄位(除了主鍵外的其他欄位)+對應的主鍵，主鍵太長，那每個二級索引占用的記憶體越大

其他的就不知道了，寫不動了，，，