?

簡介

1. 在項目上線初期，業(yè)務(wù)數(shù)據(jù)量相對較少，SQL的執(zhí)行效率對程序運行效率的影響可能不太明顯，因此開發(fā)和運維人員可能無法判斷SQL對程序的運行效率有多大。但隨著時間的積累，業(yè)務(wù)數(shù)據(jù)量的增多，SQL的執(zhí)行效率對程序的運行效率的影響逐漸增大，此時對SQL的優(yōu)化就很有必要。
2. 優(yōu)化SQL可以使其更有效地使用索引、減少硬盤I/O等，從而提高程序的運行效率。
3. 優(yōu)化SQL可以使其更好地利用緩存，從而降低程序的響應(yīng)時間。
4. 優(yōu)化SQL可以使其更好地處理大數(shù)據(jù)量，從而減少程序的運行時間。
5. 優(yōu)化SQL可以使其更符合程序的業(yè)務(wù)需求，從而提高程序的性能和用戶體驗。

一、查詢SQL盡量不要使用select *，而是具體字段。

1、反例

select * from emp

2、正例

select id,sex,user_name,salary from emp

3、理由

• 使用 * 號是因為使用 * 號會查詢出表中所有列的數(shù)據(jù)，
• 查詢所有列可能會導(dǎo)致性能問題，特別是當(dāng)表中包含大量列時，查詢所有列可能會導(dǎo)致查詢速度變慢。
• 查詢所有列可能會導(dǎo)致數(shù)據(jù)冗余，特別是當(dāng)表中包含多個關(guān)聯(lián)表時，查詢所有列可能會導(dǎo)致返回大量重復(fù)數(shù)據(jù)。
• 指定需要查詢的列可以使查詢結(jié)果更易于理解和維護，特別是當(dāng)表中包含大量列時，指定需要查詢的列可以使查詢結(jié)果更加清晰和易于理解。

?二、where中使用默認(rèn)值代替NULL

說明:

? ? ? ? 為了方便 我就使用* 代替了。

1、反例

select * from emp where  salary is not null

2、正例

select * from emp  where  salary > 0

3、理由

• 提高查詢效率：在 WHERE 子句中使用 NULL 值需要進行 IS NULL 或 IS NOT NULL 的判斷，這需要額外的計算成本。而使用默認(rèn)值 0 代替 NULL 值可以使查詢條件更加簡單明確，避免進行額外的判斷，從而提高查詢效率。
• 避免 NULL 值帶來的問題：NULL 值具有特殊的語義，即無法進行任何比較。因此，在 WHERE 子句中使用 NULL 值可能會導(dǎo)致查詢結(jié)果不準(zhǔn)確或無法執(zhí)行。使用默認(rèn)值 0 代替 NULL 值可以避免這種情況，確保查詢結(jié)果的準(zhǔn)確性和可執(zhí)行性。
• 提高代碼可讀性和可維護性：使用默認(rèn)值 0 代替 NULL 值可以使代碼更加清晰易懂，提高可讀性。同時，在修改查詢條件時，使用默認(rèn)值 0 也可以避免忘記處理 NULL 值的情況，提高代碼的可維護性。

三、避免在where子句中使用 or 來連接條件

1、反例

select * from emp  where id = 1 or salary = 2500.10

• 使用 or?可能會引起索引失效，從而進行全表掃描
• or??操作符連接的兩個條件只要有一個條件成立，就會返回相應(yīng)的結(jié)果集。因此，當(dāng)使用 where id=1 or salary=2500.10?進行查詢時，如果 id 字段上有索引，但 salary 字段上沒有索引，那么查詢引擎會優(yōu)先使用 id 索引進行查詢，因為 id 字段上的條件一定成立。但如果要查詢的 salary 值不在 id 索引的范圍內(nèi)，那么查詢引擎可能需要進行全表掃描或放棄使用索引，導(dǎo)致 id 索引失效。

2、正例

select * from emp  where id = 1
union all
select * from emp  where salary = 2500.10

• 使用 UNION ALL 用于將兩個SELECT語句的結(jié)果合并在一起，形成一個結(jié)果集。
• 與UNION不同的是，UNION ALL不會去除重復(fù)的數(shù)據(jù)行，而是將所有符合條件的數(shù)據(jù)行都列出來。
• 在 salary 字段上創(chuàng)建索引，這樣查詢引擎就可以使用兩個字段上的索引來優(yōu)化查詢，提高查詢性能。
• 使用 UNION ALL 操作符將兩個查詢結(jié)果合并，這樣可以避免使用 OR 操作符，從而避免 id 索引失效的問題。

四、盡量使用數(shù)值替代字符串類型

1、正例

主鍵(id)：primary key優(yōu)先使用數(shù)值類型int，tinyint。

性別(sex)?：0代表女，1代表男，數(shù)據(jù)庫沒有布爾類型，mysql推薦使用tinyint。

2、理由

• 引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對于數(shù)字型而言只需要比較一次就夠了，因此數(shù)值類型可以大大提高查詢和連接的性能。
• 字符串和數(shù)字之間的比較需要進行隱式的類型轉(zhuǎn)換，這會導(dǎo)致查詢性能的降低。
• 索引的優(yōu)缺點之一是可以加速表與表之間的連接，特別是在實現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。通過使用數(shù)值類型，可以在創(chuàng)建索引時提高查詢性能和連接性能。
• 在使用分組和排序字句進行數(shù)據(jù)檢索時，數(shù)值類型可以顯著減少查詢中分組和排序的時間。

五、使用varchar代替char

1、反例

`address` char(100) DEFAULT NULL COMMENT '地址'

• sql中使用char(100)作為數(shù)據(jù)類型有一些缺點。首先，char(100)會占用更多的磁盤空間，因為每個字符都需要固定長度的存儲空間。其次，使用char(100)時，如果存儲的字符串長度小于100個字符，會出現(xiàn)浪費存儲空間的情況。
• 另外，如果某些字段涉及到文件排序或者基于磁盤的臨時表時，使用char(100)可能會消耗更多的內(nèi)存，因為固定長度的字符型數(shù)據(jù)在內(nèi)存中是連續(xù)的空間。因此，在使用char(100)時需要考慮這些缺點，并根據(jù)實際需求選擇更適合的數(shù)據(jù)類型。

2、正例

`address` varchar(100) DEFAULT NULL COMMENT '地址'

• 存儲靈活：相比于固定長度的數(shù)據(jù)類型，varchar(100)可以存儲長度為100的字符串，也可以存儲長度小于100的字符串，或者不存儲任何字符，因此可以更加靈活地處理數(shù)據(jù)。
• 內(nèi)存消耗較?。簩τ谳^短的字符串，使用varchar(100)可以減少內(nèi)存消耗。例如，如果需要存儲一個長度為10的字符串，使用固定長度的數(shù)據(jù)類型需要11個字節(jié)的內(nèi)存空間，而使用varchar(100)只需要2個字節(jié)的內(nèi)存空間。
• 可以避免浪費磁盤空間：如果使用固定長度的數(shù)據(jù)類型存儲長度不足的值，會浪費一些磁盤空間。而使用varchar(100)可以根據(jù)實際需要使用或浪費較少的磁盤空間。
• 可以提高查詢性能：在某些情況下，使用varchar(100)可以提高查詢性能。例如，如果一個表中的某些列經(jīng)常需要被查詢，而這些列使用固定長度的數(shù)據(jù)類型可能會浪費一些內(nèi)存空間，從而導(dǎo)致查詢性能下降。而使用varchar(100)可以根據(jù)實際情況使用更少的內(nèi)存空間，從而提高查詢性能。

六、char與varchar2的區(qū)別

1、CHAR的長度是固定的，VARCHAR2的長度是可以變化的。

• 長度固定 vs 長度可變：CHAR的長度是固定的，而VARCHAR2的長度是可以變化的。例如，存儲字符串“101”，對于CHAR(10)，表示你存儲的字符將占10個字節(jié)（包括7個空字符），而同樣的VARCHAR2(10)則只占用3個字節(jié)的長度。
• 存儲空間：CHAR比VARCHAR2更節(jié)省存儲空間，因為CHAR類型在數(shù)據(jù)庫中以空格填充，而VARCHAR2則不會。但是，VARCHAR2在效率上比CHAR稍差一點。
• 性能：CHAR類型在某些方面比VARCHAR2類型稍快，因為它具有固定的長度，因此在處理和搜索數(shù)據(jù)時，它可以更快地定位和讀取。

2、什么時候選擇CHAR什么時候選擇VARCHAR2

• 存儲需求：如果需要存儲的字符數(shù)據(jù)長度固定且長度較小，則使用CHAR更為節(jié)省空間。如果數(shù)據(jù)長度變化范圍較大，或者長度較小，則使用VARCHAR2更為節(jié)省空間。
• 性能需求：如果應(yīng)用程序需要快速高效的查詢和搜索數(shù)據(jù)，并且對數(shù)據(jù)的長度有要求，則使用CHAR可能更合適。如果對性能要求不高，而對數(shù)據(jù)長度和變化范圍有要求，則更適合使用VARCHAR2。

注意: 在使用VARCHAR2時，如果一個列經(jīng)常被修改，并且每次修改的數(shù)據(jù)長度不同，這可能會導(dǎo)致Row Migration(行遷移)現(xiàn)象，這可能會影響數(shù)據(jù)庫性能并增加I/O負(fù)擔(dān)。在這種情況下，使用CHAR可能更好。

CHAR中還會自動補齊空格，因為你INSERT到一個CHAR字段自動補充了空格的,但是SELECT后空格沒有刪除，因此CHAR類型查詢的時候一定要記得使用TRIM函數(shù)去除字符串兩端的空格。

3、如何使用TRIM

在查詢CHAR類型的列時，如果要使用TRIM函數(shù)去除字符串兩端的空格，可以使用以下語法：

select * from emp  where TRIM(text) = 'csdn';

• emp是要查詢的表名，text是要查詢的CHAR類型的列名，csdn是要匹配的字符串。TRIM函數(shù)將去除csdn中字符串兩端的空格，并返回與csdn相等的唯一值。
• 需要注意：使用TRIM函數(shù)可能會影響查詢性能。如果查詢速度非常重要，則可以考慮將CHAR類型的列轉(zhuǎn)換為VARCHAR2類型，因為VARCHAR2類型會自動去除字符串兩端的空格。但是，這種方法可能會導(dǎo)致存儲空間的浪費。

七、避免在where子句中使用!=或＜＞ 等操作符

1、反例

select * from emp where salary !=2500.10

select * from emp where salary <>2500.10

2、理由

• 當(dāng)查詢語句中使用 IS NULL 或 IS NOT NULL 運算符進行空值判斷時，數(shù)據(jù)庫無法利用索引進行查詢，因為索引只是一種用于快速查找數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，無法判斷數(shù)據(jù)是否為空。此時，數(shù)據(jù)庫需要遍歷整個表來查找符合條件的行，從而導(dǎo)致索引失效。
• 即使在查詢語句中使用了 = 或 > 等運算符進行等值或范圍查詢，但如果查詢條件中包含了一個未使用索引的列，也會導(dǎo)致索引失效。

? 3、舉例

select * from emp  where user_name ='choudidi'  and  salary is not null;

• 在這個查詢語句中，user_name 列使用了索引，但 salary 列沒有使用索引。因此，即使 user_name 列的條件滿足，數(shù)據(jù)庫仍然需要遍歷整個表來查找符合條件的行，從而導(dǎo)致索引失效。

八、inner join 、left join、right join，優(yōu)先使用inner join

• 優(yōu)先使用INNER JOIN的原因是其性能相對更好。INNER JOIN是等值連接，只保留兩張表中完全匹配的結(jié)果集，而LEFT JOIN和RIGHT JOIN會返回左表或右表的所有行，即使在另一張表中沒有匹配的記錄。
• 因此，如果使用LEFT JOIN或RIGHT JOIN，可能會導(dǎo)致返回的數(shù)據(jù)量較大，從而影響查詢性能。
• INNER JOIN將數(shù)據(jù)集的較小數(shù)據(jù)驅(qū)動，而LEFT JOIN和RIGHT JOIN則將較大的數(shù)據(jù)集驅(qū)動，這也是MySQL優(yōu)化原則之一。
• 小表驅(qū)動大表，小的數(shù)據(jù)集驅(qū)動大的數(shù)據(jù)集，從而讓性能更優(yōu)。

九、提高group by語句的效率

1、反例

select department, avg(salary) from emp 
group by department
having department ='1' or department = '3';

• ?先分組，再過濾

2、正例

select department,avg(salary) from emp 
where department ='1' or department = '3' 
group by department;

• 先過濾，后分組

• 篩選出符合條件的記錄：where子句中的條件篩選出了部門為'1'或'3'的員工記錄，只對符合條件的記錄進行后續(xù)計算。

• 按部門分組：group by子句將結(jié)果集按照部門進行分組，這樣每個部門的數(shù)據(jù)都會單獨計算平均值，避免了將所有記錄混合在一起計算平均值的情況。

十、清空表時優(yōu)先使用truncate?

• 速度更快：truncate比delete更快，因為它不會記錄在日志中，也不需要在事務(wù)日志中為所刪除的每行記錄一項。
• 占用的資源更少：truncate通過釋放存儲表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)，而不是在事務(wù)日志中記錄頁的釋放，而delete語句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項。
• 保持表結(jié)構(gòu)及其列、約束、索引等不變：truncate刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。新行標(biāo)識所用的計數(shù)值重置為該列的種子。如果要保留標(biāo)識計數(shù)值，請改用delete。如果要刪除表定義及其數(shù)據(jù)，請使用drop table語句。
• 不適用于參與了索引視圖的表：truncate不能用于參與了索引視圖的表，而delete可以。
• 不激活觸發(fā)器：truncate不會激活觸發(fā)器，而delete會。truncate不會激活觸發(fā)器的原因是它不會記錄各行的日志刪除操作。由于truncate操作不會記錄在事務(wù)日志中，所以它不會激活delete觸發(fā)器。

十一、操作delete或者update語句，加limit或使用循環(huán)分批次刪除

• 降低數(shù)據(jù)庫負(fù)載：一次性刪除(或更新)大量數(shù)據(jù)會對數(shù)據(jù)庫性能造成較大的壓力，導(dǎo)致其他操作變慢或阻塞。分批次刪除可以降低數(shù)據(jù)庫負(fù)載，保證其他操作的正常運行。
• 提高數(shù)據(jù)安全性：刪除或更新大量數(shù)據(jù)時，如果不小心出現(xiàn)錯誤操作，可能會導(dǎo)致數(shù)據(jù)丟失或不可恢復(fù)。分批次刪除可以降低操作風(fēng)險，即使不小心刪除也可以通過binlog日志恢復(fù)進行回滾操作。
• 便于代碼實現(xiàn)：一次性刪除大量數(shù)據(jù)可能會導(dǎo)致內(nèi)存溢出或其他異常情況，使用循環(huán)分批次刪除可以避免這些問題。同時，使用循環(huán)分批次刪除也可以降低代碼復(fù)雜度，提高代碼可讀性和可維護性。
• 鎖表：一次性大量刪除太多數(shù)據(jù)，可能造成鎖表，會有l(wèi)ock wait timeout exceed的錯誤，所以建議分批操作。
• 減少鎖定時間：刪除或更新數(shù)據(jù)時，數(shù)據(jù)庫會對相關(guān)行進行鎖定，確保數(shù)據(jù)一致性。如果一次性刪除大量數(shù)據(jù)，鎖定時間會較長，影響其他操作。而分批次刪除可以縮短鎖定時間，提高并發(fā)性能。

1、正例

1、使用limit限制刪除數(shù)量

delete from emp limit 1000;
-- 或
delete from emp where 條件 limit 1000;  # 每次刪除 1000 行數(shù)據(jù)
-- 或者
UPDATE emp SET column1 = value1, column2 = value2, ... WHERE condition LIMIT 1000;  # 每次更新 1000 行數(shù)據(jù)

• ?可以在delete或update語句中添加limit關(guān)鍵字，指定每次刪除的數(shù)據(jù)量。列如以上SQL每次刪除1000條數(shù)據(jù)：

2、使用循環(huán)分批次刪除

CREATE DEFINER = `root` @`localhost` PROCEDURE `P_xiao_jian` () BEGIN
	DECLARE
		i INT DEFAULT 1;#Routine body goes here...
	START TRANSACTION;
	
	SET @counter = 1;
	WHILE
			@counter <= 1000 DO DELETE FROM order_for_goods WHERE order_id >= @counter;
		
		SET @counter = @counter + 1;
		
	END WHILE;
	COMMIT;

END

1. 定義一個名為 P_xiao_jian 的存儲過程，使用 CREATE PROCEDURE 語句創(chuàng)建。
2. 定義一個名為 @counter 的整型變量，并初始化為 1。
3. 使用 START TRANSACTION 語句開始一個事務(wù)。
4. 使用 WHILE 循環(huán)，循環(huán)條件為 @counter 小于等于 1000。
5. 在循環(huán)體內(nèi)，使用 DELETE FROM 語句刪除 order_for_goods 表中 order_id 大于等于當(dāng)前循環(huán)變量 @counter 的所有記錄。
6. 循環(huán)結(jié)束后，使用 COMMIT 語句提交事務(wù)。
7. 使用 END 語句結(jié)束存儲過程的定義。
   ?

十二、UNION操作符

• UNION操作符用于將兩個或多個SELECT語句的結(jié)果集合并成一個結(jié)果集。

select user_name,salary from emp 
union
select departmentname from department

• union在進行表連接后會篩選掉重復(fù)的記錄，所以在表連接后會對所產(chǎn)生的結(jié)果集進行排序運算，刪除重復(fù)的記錄再返回結(jié)果。
• 在運行時先取出兩個表的結(jié)果，再用排序空間進行排序刪除重復(fù)的記錄，最后返回結(jié)果集，如果表數(shù)據(jù)量大的話可能會導(dǎo)致用磁盤進行排序。
  ?

推薦：采用union all操作符替代union，因為union all操作只是簡單的將兩個結(jié)果合并后就返回。

十三、批量插入性能提升

1、反例

• 多條提交

insert into emp (id,user_name) values(1,'提交1條');

insert into emp (id,user_name) values(2,'提交2條');

2、正例

• 批量提交

insert into emp (id,user_name) values(1,'提交1條'),(2,'提交2條');

• 當(dāng)執(zhí)行INSERT、UPDATE或DELETE語句默認(rèn)SQL有事務(wù)控制，導(dǎo)致每條都需要事務(wù)開啟和事務(wù)提交，而批量處理是一次事務(wù)開啟和提交，效率提升明顯，達到一定量級，效果顯著。

十四、表連接不宜太多，索引不宜太多，建議5個以內(nèi)

1、表連接不宜太多原因

• 關(guān)聯(lián)的表個數(shù)越多，編譯的時間和開銷也就越大。
• 每次關(guān)聯(lián)內(nèi)存中都生成一個臨時表，應(yīng)該把連接表拆開成較小的幾個執(zhí)行，可讀性更高。
• 如果一定需要連接很多表才能得到數(shù)據(jù)，那么意味著這是個不理想的的設(shè)計。
• 可以查看阿里規(guī)范手冊，其中包含一條建議多表聯(lián)查三張表以下
• 通常情況下，建議將表連接的數(shù)量控制在5個以內(nèi)，這樣可以提高查詢的效率和性能。如果需要連接更多的表，可以考慮對查詢進行拆分或重構(gòu)，以減少連接的數(shù)量。此外，還可以使用索引、臨時表、視圖等技術(shù)來優(yōu)化查詢性能。

2、索引不宜太多原因

• 增加額外的開銷：索引需要維護，包括更新、插入和刪除等操作，而這些操作都會增加額外的開銷。特別是對于頻繁更新的表，索引可能會成為性能瓶頸。
• 占用磁盤空間：索引需要存儲在磁盤上，因此會增加磁盤空間的占用。過多的索引可能會使查詢變得緩慢，因為數(shù)據(jù)量越大，查詢所花費的時間可能比表里索引的時間還要短，索引可能不會產(chǎn)生優(yōu)化效果。
• 降低查詢效率：索引設(shè)計不合理或者缺少索引都會對數(shù)據(jù)庫性能造成不良影響。過多的索引可能會使查詢變得復(fù)雜，降低查詢效率。

十五、避免在索引列上使用內(nèi)置函數(shù)

說明：

? ? ?在索引列上使用內(nèi)置函數(shù)可能會導(dǎo)致索引失效，從而降低查詢性能。這是因為索引是為了快速訪問表中數(shù)據(jù)而創(chuàng)建的，但是內(nèi)置函數(shù)可能會改變索引列中的數(shù)據(jù)，導(dǎo)致索引無法識別數(shù)據(jù)。

為了避免在索引列上使用內(nèi)置函數(shù)，可以考慮以下幾種方法：

1. 優(yōu)化查詢語句：如果必須使用內(nèi)置函數(shù)，可以嘗試優(yōu)化查詢語句，以減少函數(shù)的使用次數(shù)，或者使用其他方法來避免使用內(nèi)置函數(shù)。
2. 創(chuàng)建計算列：如果必須使用內(nèi)置函數(shù)，可以創(chuàng)建一個計算列，并在該列上創(chuàng)建索引。這樣，內(nèi)置函數(shù)的結(jié)果將存儲在新列中，而不是原始列中，從而避免影響索引。
3. 創(chuàng)建視圖：如果必須使用內(nèi)置函數(shù)，可以創(chuàng)建一個視圖，并在視圖中使用內(nèi)置函數(shù)。然后，在查詢中使用視圖而不是表，以避免在索引列上使用內(nèi)置函數(shù)。
4. 更改數(shù)據(jù)類型：如果可能，將索引列的數(shù)據(jù)類型更改為適合內(nèi)置函數(shù)的類型，以避免使用內(nèi)置函數(shù)。例如，如果要在索引列上使用日期函數(shù)，可以將數(shù)據(jù)類型更改為日期類型。

十六、組合索引

說明：

? ? ? ?如果查詢中排序的順序與組合索引的列順序不匹配，那么查詢效率可能會下降。這是因為數(shù)據(jù)庫無法利用索引進行排序，而只能使用其他算法進行排序，從而導(dǎo)致查詢效率下降。

create index idx_username_tel on employees (deptid,username,createtime);

select username,tel from employees  where deptid= 1 and username = 'CSDN' order by deptid,position,createtime desc; 

雖然創(chuàng)建了基于deptid、username和createtime列的索引，但是查詢條件中只使用了deptid=1和username=csdn列，沒有利用到createtime列。因此，這個查詢不能利用到基于createtime列的索引，查詢性能可能會受到影響。同時，如果表中的數(shù)據(jù)量非常大，創(chuàng)建索引可能會對系統(tǒng)的性能產(chǎn)生較大的影響。
?

十七、復(fù)合索引最左特性

說明：

? ? ? 復(fù)合索引（也稱為聯(lián)合索引）具有"最左前綴"特性。這意味著，當(dāng)查詢條件中使用了復(fù)合索引中的第一個列（最左邊的列），則該復(fù)合索引可以被使用。如果查詢條件中使用了復(fù)合索引中的多個列，則該復(fù)合索引也可以被使用，但是前提是這些列的順序必須與復(fù)合索引中的順序相同。?

1、創(chuàng)建復(fù)合索引

CREATE INDEX idx_name_age ON employees (name, age);

2、假設(shè)我們有一個查詢，需要按name和age進行排序，可以使用該復(fù)合索引(如下)

SELECT * FROM employees WHERE name = 'John' AND age = 25 ORDER BY name, age;

在這個查詢中，我們使用了復(fù)合索引中的第一個列name和第二個列age。由于這些列的順序與復(fù)合索引中的順序相同，因此該復(fù)合索引可以被使用。

3、然而，如果我們改變了查詢條件中列的順序，或者添加了其他函數(shù)或條件，可能會導(dǎo)致復(fù)合索引無法被使用(如下)

-- 復(fù)合索引無法被使用  
SELECT * FROM employees WHERE age = 25 AND name = 'John' ORDER BY name, age;  
  
-- 復(fù)合索引無法被使用  
SELECT * FROM employees WHERE SUBSTRING(name, 1, 1) = 'J' AND age = 25 ORDER BY name, age;

在第一個查詢中，我們改變了查詢條件中列的順序，因此復(fù)合索引無法被使用。在第二個查詢中，我們添加了一個函數(shù)SUBSTRING，這使得復(fù)合索引無法被使用。因此，在使用復(fù)合索引時，需要注意查詢條件中列的順序，以及是否添加了任何函數(shù)或條件。?

十八、優(yōu)化like語句

1、反例

select * from emp where user_name like '%王' (不使用索引)

select * from emp where user_name like '%王%' (不使用索引)

2、正例

select * from emp where user_name like '王%' (使用索引) 。

3、理由

• 使用前綴匹配，當(dāng)你知道要搜索的字符串具有某個特定的前綴時，可以使用前綴匹配來提高查詢效率。例如，如果你知道要搜索的名字都以 "張" 開頭，可以使用 LIKE '張%'。
• 首先盡量避免模糊查詢，如果必須使用，不采用全模糊查詢，也應(yīng)盡量采用右模糊查詢， 即like '...%'，是會使用索引的，左模糊like '%...'無法直接使用索引，但可以利用reverse + function index的形式，變化成 like '...%'。
• 全模糊查詢是無法優(yōu)化的，一定要使用的話建議使用搜索引擎。

reverse + function index的形式如下:

SELECT * FROM emp WHERE LEFT(user_name, 1) LIKE '肖%';

• 這將返回名字以 "肖" 開頭的所有名字。在這個語句中，LEFT(user_name, 1) 提取名字左側(cè)的第一個字符，并將其與 '肖%' 進行比較。注意，這里使用了 % 通配符來表示任意字符（包括 0 個字符），因此 '肖%' 表示以 "肖" 開頭的任意字符串。

十九、使用explain分析你SQL執(zhí)行計劃

1、type類型

• system：表僅有一行，基本用不到；
• const：表最多一行數(shù)據(jù)配合，主鍵查詢時觸發(fā)較多；
• eq_ref：對于每個來自于前面的表的行組合，從該表中讀取一行。這可能是最好的聯(lián)接類型，除了const類型；
• ref：對于每個來自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀??；
• range：只檢索給定范圍的行，使用一個索引來選擇行。當(dāng)使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關(guān)鍵字列時，可以使用range；
• index：該聯(lián)接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引文件通常比數(shù)據(jù)文件??；
• all：全表掃描；
• 性能排名：system > const > eq_ref > ref > range > index > all。
• 實際sql優(yōu)化中，最后達到ref或range級別。

2、Extra常用關(guān)鍵字

• Using index：只從索引樹中獲取信息，而不需要回表查詢；
• Using where：WHERE子句用于限制哪一個行匹配下一個表或發(fā)送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where并且表聯(lián)接類型為ALL或index，查詢可能會有一些錯誤。需要回表查詢。
• Using temporary：mysql常建一個臨時表來容納結(jié)果，典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時；
  ?

二十、其他SQL優(yōu)化方式

1、設(shè)計表的時候，所有表和字段都添加相應(yīng)的注釋。

2、SQL書寫格式，關(guān)鍵字大小保持一致，使用縮進。

3、修改或刪除重要數(shù)據(jù)前，要先備份。

4、很多時候用 exists 代替 in 是一個好的選擇

5、where后面的字段，留意其數(shù)據(jù)類型的隱式轉(zhuǎn)換。

• 在SQL中，當(dāng)在WHERE子句中指定條件時，如果條件中的字段類型與查詢中指定的字段類型不匹配，SQL可能會進行隱式的類型轉(zhuǎn)換，也稱為“隱式轉(zhuǎn)換”或“隱式類型轉(zhuǎn)換”。
• 例如，如果一個字段是一個日期類型，而條件中指定的值是一個字符串類型，SQL可能會嘗試將字符串值轉(zhuǎn)換為日期類型。同樣，如果一個字段是一個數(shù)值類型，而條件中指定的值是一個字符串類型，SQL可能會嘗試將字符串值轉(zhuǎn)換為數(shù)值類型。
• 這種隱式類型轉(zhuǎn)換在某些情況下可能是有用的，但在其他情況下可能會導(dǎo)致錯誤的結(jié)果。因此，建議在編寫查詢時始終顯式指定要比較的值和字段的類型，以避免出現(xiàn)意外的問題。

如：

SELECT * FROM emp WHERE NAME=12345

• 因為不加單引號時，是字符串跟數(shù)字的比較，它們類型不匹配
• MySQL會做隱式的類型轉(zhuǎn)換，把它們轉(zhuǎn)換為數(shù)值類型再做比較

6、盡量把所有列定義為NOT NULL

• NOT NULL列更節(jié)省空間，NULL列需要一個額外字節(jié)作為判斷是否為 NULL的標(biāo)志位。
• NULL列需要注意空指針問題，NULL列在計算和比較的時候，需要注意空指針問題。

7、偽刪除設(shè)計

8、數(shù)據(jù)庫和表的字符集盡量統(tǒng)一使用UTF8

• 可以避免亂碼問題。
• 可以避免，不同字符集比較轉(zhuǎn)換，導(dǎo)致的索引失效問題。

9、select count(*) from table；

• 這樣不帶任何條件的count會引起全表掃描，并且沒有任何業(yè)務(wù)意義，是一定要杜絕的。

10、避免在where中對字段進行表達式操作

• SQL解析時，如果字段相關(guān)的是表達式就進行全表掃描
• 字段干凈無表達式，索引生效

11、關(guān)于臨時表

• 避免頻繁創(chuàng)建和刪除臨時表，以減少系統(tǒng)表資源的消耗；
• 在新建臨時表時，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table，避免造成大量 log；
• 如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先create table，然后insert；
• 如果使用到了臨時表，在存儲過程的最后務(wù)必將所有的臨時表顯式刪除。先 truncate table ，然后 drop table ，這樣可以避免系統(tǒng)表的較長時間鎖定。

12、索引不適合建在有大量重復(fù)數(shù)據(jù)的字段上，比如性別，排序字段應(yīng)創(chuàng)建索引

13、去重distinct過濾字段要少

• 帶distinct的語句占用cpu時間高于不帶distinct的語句
• 當(dāng)查詢很多字段時，如果使用distinct，數(shù)據(jù)庫引擎就會對數(shù)據(jù)進行比較，過濾掉重復(fù)數(shù)據(jù)
• 然而這個比較、過濾的過程會占用系統(tǒng)資源，如cpu時間

14、盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力

15、所有表必須使用Innodb存儲引擎

• Innodb「支持事務(wù)，支持行級鎖，更好的恢復(fù)性」，高并發(fā)下性能更好，所以呢，沒有特殊要求（即Innodb無法滿足的功能如：列存儲，存儲空間數(shù)據(jù)等）的情況下，所有表必須使用Innodb存儲引擎。

16、盡量避免使用游標(biāo)文章來源地址http://www.zghlxwxcb.cn/news/detail-481007.html

• 因為游標(biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過1萬行，那么應(yīng)該考慮改寫。

到了這里，關(guān)于關(guān)于數(shù)據(jù)庫SQL優(yōu)化的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！