MySQL 性能優(yōu)化的 9 種姿勢(shì)，面試再也不怕了！
1、選擇最合適的字段屬性
例如：在定義郵政編碼這個(gè)字段時(shí)，如果將其設(shè)置為char(255)，顯然給數(shù)據(jù)庫增加了不必要的空間，甚至使用varchar這種類型也是多余的，因?yàn)閏har(6)就可以很好地完成了任務(wù)。同樣的如果可以的話，我們應(yīng)該是用MEDIUMINT而不是BIGINT來定義整形字段。
在可能的情況下，盡量把字段設(shè)置為NOT NULL,這樣在將來執(zhí)行查詢的時(shí)候，數(shù)據(jù)庫不用去比較NULL值。
例如：我們要將客戶基本信息表中沒有任何訂單的客戶刪除掉，就可以利用子查詢先從銷售信息表中將所有發(fā)出訂單的客戶id取出來，然后將結(jié)果傳遞給主查詢，如下圖所示：
inner join內(nèi)連接也叫做等值連接，left/right join是外鏈接。
SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id=B.id;
SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;
SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;
經(jīng)過多方面的證實(shí)inner join性能比較快，因?yàn)閕nner join是等值連接，或許返回的行數(shù)比較少。但是我們要記得有些語句隱形的用到了等值連接，如：
SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;
sql中的連接查詢有inner join(內(nèi)連接）、left join(左連接)、right join（右連接）、full join（全連接）四種方式，它們之間其實(shí)并沒有太大區(qū)別，僅僅是查詢出來的結(jié)果有所不同。
inner join（內(nèi)連接），在兩張表進(jìn)行連接查詢時(shí)，只保留兩張表中完全匹配的結(jié)果集。
我們使用inner join對(duì)兩張表進(jìn)行連接查詢，sql如下：
SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
INNER JOIN Orders o
ON p.Id_P=o.Id_P and 1=1 --用and連接多個(gè)條件
ORDER BY p.LastName
此種連接方式Orders表中Id_P字段在Persons表中找不到匹配的，則不會(huì)列出來。
注意：?jiǎn)渭兊?code style="margin: 3px;padding: 3px;outline: 0px;max-width: 100%;box-sizing: border-box !important;overflow-wrap: break-word !important;font-size: 14px;border-radius: 4px;font-family: "Operator Mono", Consolas, Monaco, Menlo, monospace;word-break: break-all;color: rgb(155, 110, 35);background-color: rgb(255, 245, 227);">select * from a,b是笛卡爾乘積。比如a表有5條數(shù)據(jù)，b表有3條數(shù)據(jù)，那么最后的結(jié)果有5*3=15條數(shù)據(jù)。
但是如果對(duì)兩個(gè)表進(jìn)行關(guān)聯(lián):select * from a,b where a.id = b.id 意思就變了，此時(shí)就等價(jià)于：
select * from a inner join b on a.id = b.id。 -- 即就是內(nèi)連接。
內(nèi)連接查詢 (select * from a join b on a.id = b.id) 與 關(guān)聯(lián)查詢 (select * from a , b where a.id = b.id)的區(qū)別
left join,在兩張表進(jìn)行連接查詢時(shí)，會(huì)返回左表所有的行，即使在右表中沒有匹配的記錄。
我們使用left join對(duì)兩張表進(jìn)行連接查詢，sql如下：
SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
LEFT JOIN Orders o
ON p.Id_P=o.Id_P
ORDER BY p.LastName
right join,在兩張表進(jìn)行連接查詢時(shí)，會(huì)返回右表所有的行，即使在左表中沒有匹配的記錄。
我們使用right join對(duì)兩張表進(jìn)行連接查詢，sql如下：
SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
RIGHT JOIN Orders o
ON p.Id_P=o.Id_P
ORDER BY p.LastName
full join,在兩張表進(jìn)行連接查詢時(shí)，返回左表和右表中所有沒有匹配的行。
我們使用full join對(duì)兩張表進(jìn)行連接查詢，sql如下：
SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
FULL JOIN Orders o
ON p.Id_P=o.Id_P
ORDER BY p.LastName
查詢結(jié)果是left join和right join的并集。
下面一個(gè)例子就演示了一個(gè)使用union額查詢。
當(dāng)我們可以確認(rèn)不可能出現(xiàn)重復(fù)結(jié)果集或者不在乎重復(fù)結(jié)果集的時(shí)候盡量使用union all而不是union，因?yàn)閡nion和union all的差異主要是前者需要將兩個(gè)或者多個(gè)結(jié)果集合并后再進(jìn)行唯一性過濾操作，這就會(huì)涉及到排序，增加大量的CPU運(yùn)算，增大資源消耗及延遲。
BEGIN;
INSERTINTOsalesinfoSETCustomerID=14;
UPDATEinventorySETQuantity=11WHEREitem='book';
COMMIT;
原子性：一個(gè)事物(transaction)中的所有操作，要么全部完成，要么全部不完成，不會(huì)結(jié)束在中間某個(gè)環(huán)節(jié)。事務(wù)在執(zhí)行過程中發(fā)生錯(cuò)誤，會(huì)被回滾(Rollback)到事務(wù)開始的狀態(tài)，就像這個(gè)事務(wù)從來沒有執(zhí)行過一樣。一致性：在事務(wù)開始之前和事務(wù)結(jié)束之后，數(shù)據(jù)庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預(yù)設(shè)規(guī)則，這包含資料的精確度、串聯(lián)性以及后續(xù)數(shù)據(jù)庫可以自發(fā)性地完成預(yù)定的工作。隔離性：數(shù)據(jù)庫允許多個(gè)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫和修改的能力，隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。事務(wù)隔離分為不同的級(jí)別，包括讀未提交(Read uncommitted)、讀已提交(Read committed)、可重復(fù)讀(repeateable read)和串行化(Serializable).持久性：事務(wù)處理結(jié)束后，對(duì)數(shù)據(jù)的修改就是永久的，即便系統(tǒng)故障也不會(huì)丟失。
1、臟讀：事務(wù)A讀取了事務(wù)B更新的數(shù)據(jù)，然后B回滾操作，那么A讀取到的數(shù)據(jù)就是臟數(shù)據(jù)
2、不可重復(fù)讀：事務(wù)A多次讀取同一事物，事務(wù)B在事務(wù)A多次讀取的過程中，對(duì)數(shù)據(jù)做了更新并提交，導(dǎo)致事務(wù)A多次讀取同一數(shù)據(jù)時(shí)，結(jié)果不一致。
3、幻讀：系統(tǒng)管理員A將數(shù)據(jù)庫中的所有學(xué)生的成績(jī)從具體分?jǐn)?shù)改為ABCDE等級(jí)，但是系統(tǒng)管理員B就在這個(gè)時(shí)候插入了一條具體分?jǐn)?shù)的記錄，當(dāng)系統(tǒng)管理員A改結(jié)束后發(fā)現(xiàn)還有一條記錄沒有改過來，就好像發(fā)生了幻覺一樣，這就叫幻讀。
小結(jié)：不可重復(fù)讀的和幻讀很容易混淆，不可重復(fù)讀側(cè)重于修改，幻讀側(cè)重于新增或刪除。解決不可重復(fù)讀的問題只需鎖住滿足條件的行，解決幻讀需要鎖表
| 事務(wù)隔離級(jí)別 | 臟讀 | 不可重復(fù)讀 | 幻讀 |
|---|---|---|---|
| 讀未提交（read-uncommitted） | 是 | 是 | 是 |
| 不可重復(fù)讀（read-committed） | 否 | 是 | 是 |
| 可重復(fù)讀（repeatable-read） | 否 | 否 | 是 |
| 串行化（serializable） | 否 | 否 | 否 |
事務(wù)控制語句：
BEGIN或START TRANSACTION：顯式的開啟一個(gè)事物。COMMIT：也可以使用COMMIT WORK，不過二者是等價(jià)的。COMMIT會(huì)提交事務(wù)，并使已對(duì)數(shù)據(jù)庫進(jìn)行的所有修改成為永久性的。Rollback：也可以使用Rollback work，不過二者是等價(jià)的?；貪L會(huì)結(jié)束用戶的事務(wù)，并撤銷正在進(jìn)行的所有未提交的修改。SAVEPOINT identifier：SAVEPOINT允許在事務(wù)中創(chuàng)建一個(gè)保存點(diǎn)，一個(gè)事務(wù)中可以有很多個(gè)SAVEPOINT；RELEASE SAVEPOINT identifier：刪除一個(gè)事物的保存點(diǎn),當(dāng)沒有指定的保存點(diǎn)時(shí)，執(zhí)行該語句會(huì)拋出一個(gè)異常。ROLLBACK TO inditifier：把事務(wù)回滾到標(biāo)記點(diǎn)。SET TRANSACTION:用來設(shè)置事務(wù)的隔離級(jí)別。InnoDB存儲(chǔ)引擎提供事務(wù)的隔離級(jí)別有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERLALIZABLE。
CREATE TABLE customerinfo(customerid int primary key) engine = innodb;
CREATE TABLE salesinfo( salesid int not null,customerid int not null, primary key(customerid,salesid),foreign key(customerid) references customerinfo(customerid) on delete cascade)engine = innodb;
注意例子中的參數(shù)“on delete cascade”.該參數(shù)保證當(dāng)customerinfo表中的一條客戶記錄也會(huì)被自動(dòng)刪除。如果要在mysql中使用外鍵，一定要記住在創(chuàng)建表的時(shí)候?qū)⒈淼念愋投x為事務(wù)安全表InnoDB類型。該類型不是mysql表的默認(rèn)類型。定義的方法是在CREATE TABLE語句中加上engine=innoDB。
其實(shí)，有些情況下我們可以通過鎖定表的方式來獲得更好的性能。下面的例子就是鎖定表的方法來完成前面一個(gè)例子中事務(wù)的功能。
8、使用索引
索引是提高數(shù)據(jù)庫性能的常用方法，他可以令數(shù)據(jù)庫服務(wù)器比沒有索引快得多的速度檢索特定的行，尤其是在查詢語句當(dāng)中包含有MAX(),MIN()和ORDERBY這些命令的時(shí)候，性能提高更為明顯。
此外，MySQL從版本3.23.23開始支持全文索引和搜索。全文索引在MySQL中是一個(gè)FULLTEXT類型索引，但僅能用于MyISAM類型的表。對(duì)于一個(gè)大的數(shù)據(jù)庫，將數(shù)據(jù)裝載到一個(gè)沒有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX創(chuàng)建索引，將是非?？斓摹５绻麑?shù)據(jù)裝載到一個(gè)已經(jīng)有FULLTEXT索引的表中，執(zhí)行過程將會(huì)非常慢。
1 不使用子查詢
例：SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’);
在MariaDB10/MySQL5.6版本里，采用join關(guān)聯(lián)方式對(duì)其進(jìn)行了優(yōu)化，這條SQL會(huì)自動(dòng)轉(zhuǎn)換為
SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;
2 避免函數(shù)索引
例：
SELECT * FROM t WHERE YEAR(d) >= 2016;
由于MySQL不像Oracle那樣支持函數(shù)索引，即使d字段有索引，也會(huì)直接全表掃描。
應(yīng)改為—–>
SELECT * FROM t WHERE d >= ‘2016-01-01’;
3 用IN來替換OR
低效查詢
SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
—–> 高效查詢
SELECT * FROM t WHERE LOC_IN IN (10,20,30);
4 LIKE雙百分號(hào)無法使用到索引
SELECT * FROM t WHERE name LIKE ‘%de%’;
—–>
SELECT * FROM t WHERE name LIKE ‘de%’;
5 讀取適當(dāng)?shù)挠涗汱IMIT M,N
SELECT * FROM t WHERE 1;
—–>
SELECT * FROM t WHERE 1 LIMIT 10;
6 避免數(shù)據(jù)類型不一致
SELECT * FROM t WHERE id = ’19’;
—–>
SELECT * FROM t WHERE id = 19;
7 分組統(tǒng)計(jì)可以禁止排序
SELECT goods_id,count(*) FROM t GROUP BY goods_id;
—–>
SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;
8 避免隨機(jī)取記錄
SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;
MySQL不支持函數(shù)索引，會(huì)導(dǎo)致全表掃描 —–>
SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;
9 禁止不必要的ORDER BY排序
SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;
—–>
SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;
10 批量INSERT插入
INSERT INTO t (id, name) VALUES(1,’Bea’);
INSERT INTO t (id, name) VALUES(2,’Belle’);
INSERT INTO t (id, name) VALUES(3,’Bernice’);
—–>
INSERT INTO t (id, name) VALUES(1,’Bea’), (2,’Belle’),(3,’Bernice’);
原文鏈接：https://blog.csdn.net/weixin_42047611/article/details/81772149
感謝您的閱讀，也歡迎您發(fā)表關(guān)于這篇文章的任何建議，關(guān)注我，技術(shù)不迷茫！小編到你上高速。
正文結(jié)束
1.不認(rèn)命，從10年流水線工人，到谷歌上班的程序媛，一位湖南妹子的勵(lì)志故事
3.從零開始搭建創(chuàng)業(yè)公司后臺(tái)技術(shù)棧
5.37歲程序員被裁，120天沒找到工作，無奈去小公司，結(jié)果懵了...
6.IntelliJ IDEA 2019.3 首個(gè)最新訪問版本發(fā)布，新特性搶先看
