連接(JOIN)之所以更有效率一些，是因為MySQL不需要在內(nèi)存中創(chuàng)建臨時表來完成這個邏輯上 需要兩個步驟的查詢工作。

另外，如果你的應用程序有很多JOIN查詢，你應該確認兩個表中JOIN的字段是被建立過索引的。這樣MySQL內(nèi)部 會啟動為你優(yōu)化JOIN的SQL語句的機制。而且這些被用來JOIN的字段，應該是相同的類型的。

例如：如果你要把DECIMAL字段和一個INT字段JOIN在一起，MySQL就無法使用他們的索引。對于那些STRING類型，還需要有相同的字符集才行。(兩個表的字符集可能不相同)。

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)過多方面的證實inner join性能比較快，因為inner join是等值連接，或許返回的行數(shù)比較少。但是我們要記得有些語句隱形的用到了等值連接，如：

SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;

推薦：能用inner join連接誒就用inner join連接。

sql中的連接查詢有inner join(內(nèi)連接）、left join(左連接)、right join（右連接）、full join（全連接）四種方式，它們之間其實并沒有太大區(qū)別，僅僅是查詢出來的結(jié)果有所不同。

例如我們有兩張表：

Orders表通過外鍵Id_P和Persons表進行關(guān)聯(lián)。

inner join（內(nèi)連接），在兩張表進行連接查詢時，只保留兩張表中完全匹配的結(jié)果集。

我們使用inner join對兩張表進行連接查詢，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連接多個條件
ORDER BY p.LastName

查詢結(jié)果集：

此種連接方式Orders表中Id_P字段在Persons表中找不到匹配的，則不會列出來。

注意：單純的select * from a,b是笛卡爾乘積。比如a表有5條數(shù)據(jù)，b表有3條數(shù)據(jù)，那么最后的結(jié)果有5*3=15條數(shù)據(jù)。

但是如果對兩個表進行關(guān)聯(lián):select * from a,b where a.id = b.id 意思就變了，此時就等價于：

select * from a inner join b on a.id = b.id。 -- 即就是內(nèi)連接。

但是這種寫法并不符合規(guī)范，可能只對某些數(shù)據(jù)庫管用，如sqlserver。推薦最好不要這樣寫。最好寫成inner join的寫法。

內(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,在兩張表進行連接查詢時，會返回左表所有的行，即使在右表中沒有匹配的記錄。

我們使用left join對兩張表進行連接查詢，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

查詢結(jié)果如下：

可以看到，左表（Persons表）中LastName為Bush的行的Id_P字段在右表（Orders表）中沒有匹配，但查詢結(jié)果仍然保留該行。

right join,在兩張表進行連接查詢時，會返回右表所有的行，即使在左表中沒有匹配的記錄。

我們使用right join對兩張表進行連接查詢，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

查詢結(jié)果如下：

Orders表中最后一條記錄Id_P字段值為65，在左表中沒有記錄與之匹配，但依然保留。

full join,在兩張表進行連接查詢時，返回左表和右表中所有沒有匹配的行。

我們使用full join對兩張表進行連接查詢，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é)果如下：

查詢結(jié)果是left join和right join的并集。

4、使用聯(lián)合(UNION)來代替手動創(chuàng)建的臨時表

MySQL從4.0版本開始支持union查詢，他可以把需要使用臨時表的兩條或更多的select查詢合在一個查詢中。在客戶端查詢會話結(jié)束的時候，臨時表會被自動刪除，從而保證數(shù)據(jù)庫整齊、高效。使用union來創(chuàng)建查詢的時候，我們只需要用union作為關(guān)鍵字把多個select語句連接起來就可以了，要注意的是所有select語句中的字段數(shù)目要相同。下面一個例子就演示了一個使用union額查詢。

當我們可以確認不可能出現(xiàn)重復結(jié)果集或者不在乎重復結(jié)果集的時候盡量使用union all而不是union，因為union和union all的差異主要是前者需要將兩個或者多個結(jié)果集合并后再進行唯一性過濾操作，這就會涉及到排序，增加大量的CPU運算，增大資源消耗及延遲。

5、事務

盡管我們可以使用子查詢(Sub-Queries)、連接(JOIN)和聯(lián)合(UNION)來創(chuàng)建各種各樣的查詢，但不是所有的數(shù)據(jù)庫操作，都可以只用一條或少數(shù)幾條就可以完成的。更多的時候是需要用一系列的語句來完成某種工作。但是在這種情況下，當這個語句塊中的某一條語句運行出錯的時候，整個語句塊的操作就會變得不確定起來。

設想一下，要把某個數(shù)據(jù)同時插入兩個相關(guān)聯(lián)的表中，可能會出現(xiàn)這樣的情況：第一個表中成功更新后，數(shù)據(jù)庫突然出現(xiàn)意外狀況，造成第二個表中的操作沒有完成，這樣就會造成數(shù)據(jù)的不完整，甚至會破壞數(shù)據(jù)庫中的數(shù)據(jù)。要避免這種情況，就應該使用事務，它的作用是要么語句塊中每條語句都操作成功，要么都失敗。

換句話說，就是可以保持數(shù)據(jù)庫中的數(shù)據(jù)的一致性和完整性。事務以BEGIN關(guān)鍵字開始，COMMIT關(guān)鍵字結(jié)束。在這之間的一條SQL語句操作失敗，那么Rollback命令就可以把數(shù)據(jù)庫恢復到begin開始之前的狀態(tài)。

BEGIN; 
INSERTINTOsalesinfoSETCustomerID=14;
UPDATEinventorySETQuantity=11WHEREitem='book';
COMMIT;

事務的另一個作用是當多個用戶同時使用相同的數(shù)據(jù)源時，他可以使用鎖定數(shù)據(jù)庫的方式來為用戶提供一種安全的訪問機制，這樣可以保證用戶的操作不被其它的用戶所干擾。

一般來說，事務必須滿足四個條件(ACID)：原子性(Atomicity,或稱不可分割性)、一致性(Consistency)、隔離性(Isolation,又稱獨立性)、持久性(Durability).

• 原子性：一個事物(transaction)中的所有操作，要么全部完成，要么全部不完成，不會結(jié)束在中間某個環(huán)節(jié)。事務在執(zhí)行過程中發(fā)生錯誤，會被回滾(Rollback)到事務開始的狀態(tài)，就像這個事務從來沒有執(zhí)行過一樣。
• 一致性：在事務開始之前和事務結(jié)束之后，數(shù)據(jù)庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規(guī)則，這包含資料的精確度、串聯(lián)性以及后續(xù)數(shù)據(jù)庫可以自發(fā)性地完成預定的工作。
• 隔離性：數(shù)據(jù)庫允許多個事務同時對其數(shù)據(jù)進行讀寫和修改的能力，隔離性可以防止多個事務并發(fā)執(zhí)行時由于交叉執(zhí)行而導致數(shù)據(jù)的不一致。事務隔離分為不同的級別，包括讀未提交(Read uncommitted)、讀已提交(Read committed)、可重復讀(repeateable read)和串行化(Serializable).
• 持久性：事務處理結(jié)束后，對數(shù)據(jù)的修改就是永久的，即便系統(tǒng)故障也不會丟失。

事務的并發(fā)問題：

• 臟讀：事務A讀取了事務B更新的數(shù)據(jù)，然后B回滾操作，那么A讀取到的數(shù)據(jù)就是臟數(shù)據(jù)
• 不可重復讀：事務A多次讀取同一事物，事務B在事務A多次讀取的過程中，對數(shù)據(jù)做了更新并提交，導致事務A多次讀取同一數(shù)據(jù)時，結(jié)果不一致。
• 幻讀：系統(tǒng)管理員A將數(shù)據(jù)庫中的所有學生的成績從具體分數(shù)改為ABCDE等級，但是系統(tǒng)管理員B就在這個時候插入了一條具體分數(shù)的記錄，當系統(tǒng)管理員A改結(jié)束后發(fā)現(xiàn)還有一條記錄沒有改過來，就好像發(fā)生了幻覺一樣，這就叫幻讀。
• 小結(jié)：不可重復讀的和幻讀很容易混淆，不可重復讀側(cè)重于修改，幻讀側(cè)重于新增或刪除。解決不可重復讀的問題只需鎖住滿足條件的行，解決幻讀需要鎖表

MySQL事務隔離級別

事務控制語句：

BEGIN或START TRANSACTION：顯式的開啟一個事物。

• COMMIT：也可以使用COMMIT WORK，不過二者是等價的。COMMIT會提交事務，并使已對數(shù)據(jù)庫進行的所有修改成為永久性的。
• Rollback：也可以使用Rollback work，不過二者是等價的?；貪L會結(jié)束用戶的事務，并撤銷正在進行的所有未提交的修改。
• SAVEPOINT identifier：SAVEPOINT允許在事務中創(chuàng)建一個保存點，一個事務中可以有很多個SAVEPOINT；
• RELEASE SAVEPOINT identifier：刪除一個事物的保存點,當沒有指定的保存點時，執(zhí)行該語句會拋出一個異常。
• ROLLBACK TO inditifier：把事務回滾到標記點。
• SET TRANSACTION: 用來設置事務的隔離級別。InnoDB存儲引擎提供事務的隔離級別有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERLALIZABLE。

6、使用外鍵

鎖定表的方法可以維護數(shù)據(jù)的完整性，但是他卻不能保證數(shù)據(jù)的關(guān)聯(lián)性。這個時候我們可以使用外鍵。例如：外鍵可以保證每一條銷售記錄都指向某一個存在的客戶。

在這里，外鍵可以把customerinfo表中的customerid映射到salesinfo表中customerid，任何一條沒有辦法合法customerid的記錄都不會被跟新或插入到salesinfo中.

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ù)保證當customerinfo表中的一條客戶記錄也會被自動刪除。如果要在mysql中使用外鍵，一定要記住在創(chuàng)建表的時候?qū)⒈淼念愋投x為事務安全表InnoDB類型。該類型不是mysql表的默認類型。定義的方法是在CREATE TABLE語句中加上engine=innoDB。

7、鎖定表

盡管事務是維護數(shù)據(jù)庫完整性的一個非常好的方法，但卻因為他的獨占性，有時會影響數(shù)據(jù)庫的性能，尤其是很大的應用系統(tǒng)中。由于在事務執(zhí)行的過程中，數(shù)據(jù)庫將會被鎖定，因此其他的用戶請求只能暫時等待直到該事務結(jié)束。

如果一個數(shù)據(jù)庫系統(tǒng)只有少數(shù)幾個用戶來使用，事務造成的影響不會成為太大的問題；但假設有成千上萬的用戶同時訪問一個數(shù)據(jù)庫系統(tǒng)，例如訪問一個電子商務網(wǎng)站，就會產(chǎn)生比較嚴重的響應延遲。

其實，有些情況下我們可以通過鎖定表的方式來獲得更好的性能。下面的例子就是鎖定表的方法來完成前面一個例子中事務的功能。

這里，我們用一個select語句取出初始數(shù)據(jù)，通過一些計算，用update語句將新值更新到表中。包含有WRITE關(guān)鍵字的LOCKTABLE語句可以保證在UNLOCKTABLES命令被執(zhí)行之前，不會有其他訪問來對inventory進行插入、更新或者刪除的操作。

8、使用索引

索引是提高數(shù)據(jù)庫性能的常用方法，他可以令數(shù)據(jù)庫服務器比沒有索引快得多的速度檢索特定的行，尤其是在查詢語句當中包含有MAX(),MIN()和ORDERBY這些命令的時候，性能提高更為明顯。

那該對那些字段進行索引呢？

一般來說，索引應該建立在那些將用于join，where判斷和orderby排序的字段上。盡量不要對數(shù)據(jù)庫中某個含有大量重復的值的字段建立索引，對于一個ENUM類型的字段來說，出現(xiàn)大量重復值是很有可能的情況。

例如customerinfo中的“province”..字段，在這樣的字段上建立索引將不會有什么幫助；相反，還有可能降低數(shù)據(jù)庫的性能。我們在創(chuàng)建表的時候可以同時創(chuàng)建合適的索引，也可以使用ALTERTABLE或CREATEINDEX在以后創(chuàng)建索引。

此外，MySQL從版本3.23.23開始支持全文索引和搜索。全文索引在MySQL中是一個FULLTEXT類型索引，但僅能用于MyISAM類型的表。對于一個大的數(shù)據(jù)庫，將數(shù)據(jù)裝載到一個沒有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX創(chuàng)建索引，將是非?？斓摹５绻麑?shù)據(jù)裝載到一個已經(jīng)有FULLTEXT索引的表中，執(zhí)行過程將會非常慢。

9、優(yōu)化de的查詢語句

9.1 不使用子查詢

例：SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name='hechunyang');

子查詢在MySQL5.5版本里，內(nèi)部執(zhí)行計劃器是這樣執(zhí)行的：先查外表再匹配內(nèi)表，而不是先查內(nèi)表t2，當外表的數(shù)據(jù)很大時，查詢速度會非常慢。

在MariaDB10/MySQL5.6版本里，采用join關(guān)聯(lián)方式對其進行了優(yōu)化，這條SQL會自動轉(zhuǎn)換為

SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;

但請注意的是：優(yōu)化只針對SELECT有效，對UPDATE/DELETE子查詢無效，固生產(chǎn)環(huán)境應避免使用子查詢

9.2 避免函數(shù)索引

例：

SELECT * FROM t WHERE YEAR(d) >= 2016;

由于MySQL不像Oracle那樣支持函數(shù)索引，即使d字段有索引，也會直接全表掃描。

應改為—–>

SELECT * FROM t WHERE d >= ‘2016-01-01';

9.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);

9.4 LIKE雙百分號無法使用到索引

SELECT * FROM t WHERE name LIKE ‘%de%';

—–>

SELECT * FROM t WHERE name LIKE ‘de%';

目前只有MySQL5.7支持全文索引（支持中文）

9.5 讀取適當?shù)挠涗汱IMIT M,N

SELECT * FROM t WHERE 1;

—–>

SELECT * FROM t WHERE 1 LIMIT 10;

9.6 避免數(shù)據(jù)類型不一致

SELECT * FROM t WHERE id = '19';

—–>

SELECT * FROM t WHERE id = 19;

9.7 分組統(tǒng)計可以禁止排序

SELECT goods_id,count(*) FROM t GROUP BY goods_id;

默認情況下，MySQL對所有GROUP BY col1，col2…的字段進行排序。如果查詢包括GROUP BY，想要避免排序結(jié)果的消耗，則可以指定ORDER BY NULL禁止排序。

—–>

SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;

9.8 避免隨機取記錄

SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;

MySQL不支持函數(shù)索引，會導致全表掃描 —–>

SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;

9.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;

9.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');

到此這篇關(guān)于9種 MySQL數(shù)據(jù)庫優(yōu)化的技巧的文章就介紹到這了,更多相關(guān) MySQL數(shù)據(jù)庫優(yōu)化的技巧內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！