文本

大家好，我是戰(zhàn)哥。

1.查詢SQL。 盡量不要使用*，而是使用特定字段。

1. 反例

SELECT?*?FROM?user

2、正面例子

SELECT?id,username,tel?FROM?user

3、原因

節(jié)省資源并減少網(wǎng)絡(luò)開銷。

覆蓋索引可以用來減少表返回，提高查詢效率。

注意：為了節(jié)省時間，下面的示例字段已替換為 *。

2.避免在where子句中使用or來連接條件

1. 反例

SELECT?*?FROM?user?WHERE?id=1?OR?salary=5000

2、正面例子

(1)使用union all

SELECT?*?FROM?user?WHERE?id=1?
UNION?ALL
SELECT?*?FROM?user?WHERE?salary=5000

(2)分開兩條sql寫入

SELECT?*?FROM?user?WHERE?id=1

SELECT?*?FROM?user?WHERE?salary=5000

3、原因

使用或者可能會使索引失效，導(dǎo)致全表掃描；

對于where or沒有索引的情況，假設(shè)它使用的是id的索引，但是當(dāng)達到查詢條件時，仍然要掃描全表；

也就是說，整個過程需要三步：全表掃描+索引掃描+合并。 如果一開始就進行全表掃描，則可以一次掃描完成；

雖然MySQL有優(yōu)化器，但出于效率和成本的考慮，索引在遇到OR條件時仍然可能會失敗；

3.盡量使用數(shù)值而不是字符串類型。

1、正面例子

主鍵（id）：鍵優(yōu)先使用數(shù)字類型int。

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

2. 原因

因為引擎在處理查詢和連接時會將字符串中的每個字符一一比較；

對于數(shù)字類型，只需比較一次就足夠了；

字符會降低查詢和連接性能并增加存儲開銷；

4.使用代替char

1. 反例

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

2、正面例子

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

3、原因

變長字段按照數(shù)據(jù)內(nèi)容的實際長度存儲，存儲空間小，可以節(jié)省存儲空間；

char按照聲明的大小存儲，不足時不加空格；

其次，對于查詢來說，在相對較小的字段內(nèi)進行搜索效率更高；

5.技術(shù)延伸，char和char有什么區(qū)別？

1、char的長度是固定的，但是char的長度是可以改變的。

例如，存儲字符串“101”時，對于char(10)來說，意味著你存儲的字符將占用10個字節(jié)（包括7個空字符）。 在數(shù)據(jù)庫中，是被空格占用的，同樣的（10）只占用3個字節(jié)的長度，10只是最大值。 當(dāng)你存儲的字符少于10個時，按照實際長度存儲。

2、char的效率比char略高。

3、什么時候用char，什么時候用？

Char 和 是一個矛盾的統(tǒng)一體。 兩者是互補的。 它們比 char 節(jié)省空間，但效率略低于 char。 如果你想獲得效率，就必須犧牲一些空間。 這就是我們在數(shù)據(jù)庫設(shè)計中所做的。 人們常說“以空間換效率”。

雖然比char節(jié)省空間，但是如果頻繁修改某一列，并且每次修改的數(shù)據(jù)長度不同，就會造成“行遷移”現(xiàn)象，從而造成冗余I/O，這是數(shù)據(jù)庫中應(yīng)該避免的設(shè)計。 ，在這種情況下最好使用 char 代替。 char 會自動填充空格，因為輸入 char 字段時會自動添加空格，但后面的空格不會被刪除。 因此，查詢char類型時一定要記得使用trim。 這就是寫這篇文章的原因。

如果開發(fā)人員詳細說明使用 rpad() 技巧將綁定變量轉(zhuǎn)換為某種與 char 字段相當(dāng)?shù)念愋停ó?dāng)然，填充綁定變量比截斷修剪數(shù)據(jù)庫列要好，因為應(yīng)用修剪函數(shù)的列可以很容易導(dǎo)致無法使用列上現(xiàn)有的索引），并且可能必須考慮列長度隨時間的變化。 如果字段的大小發(fā)生變化，應(yīng)用程序就會受到影響，因為它必須修改字段寬度。

正是由于上述原因，定寬存儲空間可能會導(dǎo)致表和相關(guān)索引比平時大很多，而且還伴隨著綁定變量問題，所以無論什么場合都要避免使用char類型。

6.在where中使用默認值而不是null

1. 反例

SELECT?*?FROM?user?WHERE?age?IS?NOT?NULL

2、正面例子

SELECT?*?FROM?user?WHERE?age>0

3、原因

并不意味著如果使用is null或者is not null，索引就不會被使用。 這與MySQL版本和查詢成本有關(guān)；

如果MySQL優(yōu)化器發(fā)現(xiàn)建立索引的成本高于不建立索引的成本，就會放棄索引。 這些條件！=、、is null、is not null常常被認為使索引無效；

其實是因為一般情況下查詢成本高，優(yōu)化器自動放棄索引；

如果將null值替換為默認值，往往是可以索引的，同時含義也比較清晰；

7.避免在where子句中使用!=或運算符

1. 反例

SELECT?*?FROM?user?WHERE?salary!=5000

SELECT?*?FROM?user?WHERE?salary<>5000

2. 原因

使用 != and 可能會使索引無效

應(yīng)盡量避免在where子句中使用!=或運算符，否則引擎將放棄使用索引并執(zhí)行全表掃描

要實現(xiàn)業(yè)務(wù)優(yōu)先，實在沒有辦法的話，就只能用它了。 這并不意味著您不能使用它。

8.內(nèi)連接、左連接、右連接、內(nèi)連接優(yōu)先

如果三個連接的結(jié)果相同，則優(yōu)先選擇內(nèi)連接。 如果使用左連接，則左表應(yīng)盡可能小。

為什么？

9.提高group by語句的效率

1. 反例

先分組，再過濾

select?job,?avg（salary）?from?employee?
group?by?job
having?job?='develop'?or?job?=?'test';

2、正面例子

先過濾，再分組

select?job，avg（salary）?from?employee?
where?job?='develop'?or?job?=?'test'?
group?by?job;

3、原因

您可以在執(zhí)行語句之前過濾掉不需要的記錄。

10. 清表時使用的優(yōu)??先級

table 在功能上與不帶 where 子句的語句相同：都刪除表中的所有行。 但表速度更快，并且使用更少的系統(tǒng)和事務(wù)日志資源。

該語句一次刪除一行，并在事務(wù)日志中為刪除的每一行記錄一個條目。 表刪除數(shù)據(jù)是通過釋放存儲表數(shù)據(jù)的數(shù)據(jù)頁來實現(xiàn)的，只在事務(wù)日志中記錄該頁的釋放。 另外，搜索公眾號頂級技術(shù)背景，回復(fù)“API接口”即可獲得驚喜大禮包。

table 刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。 用于標識新行的計數(shù)將重置為該列的種子。 如果您想保留身份計數(shù)值，請改用。 如果要刪除表定義及其數(shù)據(jù)，請使用 drop table 語句。

對于鍵約束引用的表intellij idea 重置默認視圖，不能使用table，而應(yīng)使用不帶where子句的語句。 由于該表未記錄，因此無法激活觸發(fā)器。

table 不能用于參與索引視圖的表。

11、操作或語句，循環(huán)中批量添加限制或刪除

1.減少寫錯SQL的成本

清除表數(shù)據(jù)并不是一件小事。 如果手一抖，一切都沒有了，刪除數(shù)據(jù)庫然后逃跑？ 如果加上限制，誤刪除只會丟失部分數(shù)據(jù)，并且可以通過日志快速恢復(fù)。

2. SQL可能更高效

SQL 中添加了限制 1。 如果第一項命中目標，如果沒有限制，則繼續(xù)執(zhí)行掃描表。

3.避免長時間交易

執(zhí)行過程中，如果age被索引，MySQL會為所有相關(guān)行添加寫鎖和間隙鎖，所有與執(zhí)行相關(guān)的行都會被鎖定。 如果刪除次數(shù)較多，會直接影響相關(guān)業(yè)務(wù)，變得無法使用。

4、數(shù)據(jù)量大的話，很容易占滿CPU。

如果刪除大量數(shù)據(jù)，又不加限制限制記錄數(shù)，很容易占滿CPU，導(dǎo)致刪除越來越慢。

5. 鎖表

一次刪除過多的數(shù)據(jù)可能會導(dǎo)致鎖表和鎖等待錯誤，所以建議批量操作。

12. 聯(lián)合運算符

UNION 聯(lián)表后會過濾掉重復(fù)記錄，因此聯(lián)表后會對生成的結(jié)果集進行排序，刪除重復(fù)記錄，然后返回結(jié)果。 在大多數(shù)實際應(yīng)用中，不會產(chǎn)生重復(fù)記錄。 最常見的是進程表和歷史表之間的 UNION。 喜歡：

select?username,tel?from?user
union
select?departmentname?from?department

這條SQL在運行時首先檢索兩個表的結(jié)果，然后使用排序空間進行排序并刪除重復(fù)記錄，最后返回結(jié)果集。 如果表數(shù)據(jù)較大，可以使用磁盤進行排序。 推薦解決方案：使用 UNION ALL 運算符而不是 UNION，因為 UNION ALL 運算只是將兩個結(jié)果合并后返回。

13.批量插入性能提升

1. 多次提交

INSERT?INTO?user?(id,username)?VALUES(1,'哪吒編程');

INSERT?INTO?user?(id,username)?VALUES(2,'妲己');

2. 批量提交

INSERT?INTO?user?(id,username)?VALUES(1,'哪吒編程'),(2,'妲己');

3、原因

默認情況下intellij idea 重置默認視圖，新添加的SQL是有事務(wù)控制的，需要每個事務(wù)都打開并提交。 然而，批處理需要打開并提交一筆交易。 效率明顯提升，達到一定程度，效果顯著，平時是看不到的。

14、表連接不宜過多，索引也不宜過多，一般在5個以內(nèi)。

1、表連接數(shù)不宜過多，一般在5個以內(nèi)

關(guān)聯(lián)表的數(shù)量越多，編譯時間和開銷就越大。

內(nèi)存中為每個關(guān)聯(lián)生成一個臨時表

連接表應(yīng)拆分為較小的執(zhí)行，以使其更具可讀性。

如果您需要連接許多表來獲取數(shù)據(jù)，則意味著這是一個糟糕的設(shè)計。

根據(jù)阿里巴巴規(guī)范，建議在三表以內(nèi)查詢多表。

2、索引不宜過多，一般在5個以內(nèi)。

索引越多越好。 雖然它們提高了查詢的效率，但會降低插入和更新的效率；

索引可以理解為一張表，可以存儲數(shù)據(jù)，但是它的數(shù)據(jù)占用空間；

索引表中的數(shù)據(jù)是排序的，排序也需要時間；

索引可能需要不時地重建。 如果數(shù)據(jù)量很大，重建會導(dǎo)致記錄重新排序，所以建立索引需要慎重考慮，根據(jù)具體情況而定；

一個表中的索引最好不要超過5個。 如果太多，則需要考慮是否需要某些索引；

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

1. 反例

SELECT?*?FROM?user?WHERE?DATE_ADD(birthday,INTERVAL?7?DAY)?>=NOW();

2、正面例子

SELECT?*?FROM?user?WHERE??birthday?>=?DATE_ADD(NOW(),INTERVAL?7?DAY);

3、原因

當(dāng)對索引列使用內(nèi)置函數(shù)時，索引將變得無效。

16. 組合索引

排序時，應(yīng)該按照組合索引中每一列的順序進行排序，即使只對索引中的一列進行排序，否則排序性能會很差。

create?index?IDX_USERNAME_TEL?on?user(deptid,position,createtime);
select?username,tel?from?user?where?deptid=?1?and?position?=?'java開發(fā)'?order?by?deptid,position,createtime?desc;

其實只是查詢滿足=1和='java開發(fā)'條件的記錄，并按降序排序，但是按desc寫order性能較差。

17. 綜合索引最左特征

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

ALTER?TABLE?employee?ADD?INDEX?idx_name_salary?(name,salary)

2、如果滿足復(fù)合索引最左邊的特征，即使只是復(fù)合索引的一部分，復(fù)合索引也會生效。

SELECT?*?FROM?employee?WHERE?NAME='哪吒編程'

3、如果左邊的字段沒有出現(xiàn)，則說明最左邊的特征不滿足，索引失效。

SELECT?*?FROM?employee?WHERE?salary=5000

4、使用全部復(fù)合索引，name按左側(cè)順序出現(xiàn)，索引生效。

牛逼??！接私活必備的 N 個開源項目！趕快收藏

SELECT?*?FROM?employee?WHERE?NAME='哪吒編程'?AND?salary=5000

5、雖然違反了最左邊的特性，但是MySQL在執(zhí)行SQL的時候會進行優(yōu)化，底層會進行反向優(yōu)化。

SELECT?*?FROM?employee?WHERE?salary=5000?AND?NAME='哪吒編程'

六、原因

復(fù)合索引也稱為聯(lián)合索引。 當(dāng)我們創(chuàng)建一個聯(lián)合索引，如(k1,k2,k3)時，相當(dāng)于創(chuàng)建了三個索引(k1)、(k1,k2)和(k1,k2,k3)。 ，這就是最左匹配原則。

聯(lián)合索引不滿足最左原則，索引一般會失敗。

18.優(yōu)化like語句

對于模糊查詢，程序員最喜歡的是使用like，但是like很可能會讓你的索引失效。

1. 反例

select?*?from?citys?where?name?like?'%大連'?(不使用索引)

select?*?from?citys?where?name?like?'%大連%'?(不使用索引)

2、正面例子

select?*?from?citys?where?name?like?'大連%'?(使用索引)?。

3、原因

19. 用于分析你的SQL執(zhí)行計劃

1. 類型

：表只有一行，基本不用；

const：表最多可以匹配一行數(shù)據(jù)，在查詢主鍵時更容易被觸發(fā)；

：對于上一個表中的每個行組合，從該表中讀取一行。 這可能是除 const 類型之外最好的連接類型；

ref：對于前一個表中的行的每個組合，將從該表中讀取具有匹配索引值的所有行；

range：僅檢索給定范圍的行，使用索引來選擇行。 當(dāng)使用 =、 、 >、 >=、 > ref > range > index > all 時。

在實際的SQL優(yōu)化中，最終會達到ref或range級別。

2.額外常用關(guān)鍵詞

20.其他一些優(yōu)化方法

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

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

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

4.很多時候用“in”代替“in”是一個不錯的選擇

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

未使用索引

SELECT?*?FROM?user?WHERE?NAME=110

(1)因為不加單引號，比較的是字符串和數(shù)字，它們的類型不匹配；

（2）MySQL會做隱式類型轉(zhuǎn)換，轉(zhuǎn)換為數(shù)值類型然后進行比較；

6.嘗試將所有列定義為NOT NULL

NOT NULL 列可以節(jié)省空間。 NULL列需要一個額外的字節(jié)作為標志來確定它是否為NULL。 NULL列需要注意空指針問題。 在計算和比較NULL列時，需要注意空指針問題。

7. 偽刪除設(shè)計

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

(1)可以避免亂碼問題；

（2）可以避免不同字符集比較轉(zhuǎn)換導(dǎo)致的索引失敗問題；

9. 從表中計數(shù)（*）；

這樣不帶任何條件的計數(shù)會導(dǎo)致全表掃描，沒有業(yè)務(wù)意義，所以一定要避免。

10.避免對where中的字段執(zhí)行表達式操作

(1) SQL解析時，與表達式相關(guān)的字段是否進行全表掃描；

（2）字段干凈，沒有表達式，索引生效；

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

（1）避免頻繁創(chuàng)建和刪除臨時表，以減少系統(tǒng)表資源的消耗；

（2）創(chuàng)建臨時表時，如果一次性插入大量數(shù)據(jù)，可以使用into代替table，避免造成大量日志；

(3)如果數(shù)據(jù)量不大，為了緩解系統(tǒng)表的資源，應(yīng)該先創(chuàng)建表，然后；

(4) 如果使用臨時表，則必須在存儲過程結(jié)束時顯式刪除所有臨時表。 先建表，后刪除表，這樣可以避免系統(tǒng)表的長期鎖定；

12、索引不適合建立在重復(fù)數(shù)據(jù)較多的字段上，比如性別。 應(yīng)該創(chuàng)建索引來對字段進行排序。

13、去重過濾的字段應(yīng)該少一些。

使用它的語句比不使用它的語句占用更多的 CPU 時間。

查詢多個字段時，如果使用，數(shù)據(jù)庫引擎會比較數(shù)據(jù)，過濾掉重復(fù)數(shù)據(jù)

然而，這個比較和過濾的過程會占用系統(tǒng)資源，比如CPU時間。

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

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

“支持事務(wù)，支持行級鎖，有更好的恢復(fù)能力?！?高并發(fā)下性能更好。 因此，在沒有特殊要求（即不滿足功能如：列存儲、存儲空間數(shù)據(jù)等）的情況下，所有的表都必須使用存儲引擎。

16.盡量避免使用游標

因為游標的效率較差，如果游標操作的數(shù)據(jù)超過10000行，就應(yīng)該考慮重寫。

歡迎有需要的同學(xué)試試，如果本文對您有幫助，也請幫忙點個?贊 + 在看?啦！???擴展：快速開發(fā)平臺
在?GitHub猿?還有更多優(yōu)質(zhì)項目系統(tǒng)學(xué)習(xí)資源，歡迎分享給其他同學(xué)吧！

最后，我們整理了一份BAT各大公司的面試真題，供讀者參考。 如有需要，可掃描二維碼，添加微信備注：“面試”即可獲取。
◆◆◆ ◆◆
（放到你的圈子里，你的朋友會感謝你的）

PS：如果您覺得我的分享不錯，歡迎點贊、閱讀。
如有侵權(quán)請聯(lián)系刪除！
                             
TAGS：mysql創(chuàng)建索引   table   sql優(yōu)化   char函數(shù)   數(shù)據(jù)庫視圖