本篇主涉及MySQL SQL Statements層面的優(yōu)化。

首先，推薦一個鏈接為萬物之始：http://dev.mysql.com/doc/refman/5.0/en/optimization.html

其次，Explain作為分析SQL的優(yōu)化利器，SHOW STATUS 和 PROCEDURE ANALYSE(16, 256)也蠻有用。推薦兩篇MySQL Explain：

http://www.khankennels.com/presentations/pdf/explain.pdf

http://dev.mysql.com/doc/refman/5.0/en/explain-output.html

1、一次INSERT多條語句

避免循環(huán)單條插入，代價很昂貴！在IBATIS中一次插入多條語句配置：

insert into user(

id,

userName,

passWord

) values

]]]]>

(

#list[].id#,

#list[].userName#,

#list[].passWord#

)

]]]]>

2、有效利用索引

－Index Unique Column。在MySQL中使用唯一索引會提升效率，僅當(dāng)作為Search目的、才有必要設(shè)置。

－在WHERE條件中盡量使用索引。

－考慮聯(lián)合索引，但存在”first hit”問題。

－FORCE INDEX強制使用指定索引列表，SELECT SQL_BUFFER_RESULTS強制使用MySQL生成臨時結(jié)果集（使得好臨時結(jié)果集、將大大提升性能，還有SQL_SMALL_RESULT、SQL_BIG_RESULT），USE INDEX給定參考索引列表，IGNORE INDEX給定忽略索引列表。

－避免在索引列使用IS NULL或NOT IS NULL。

3、一定要使用LIMIT 1

大數(shù)據(jù)集，會占用內(nèi)存、帶寬等資源。使用LIMIT，強迫分頁，減少服務(wù)器壓力。

4、盡可能地使用NOT NULL，無論是在WHERE查詢還是表字段設(shè)計中使用默認值。

5、Utilize Union instead of OR

Indexes lose their speed advantage when using them in OR-situations in MySQL at least. Hence, this will not be useful although indexes is being applied. 例：

SELECT * FROM EventPrizeUser A WHERE A.`UserID`=39235750 OR A.`UserMobile`='161751810'

vs.

(SELECT * FROM EventPrizeUser WHERE `UserID`=39235750)

UNION

(SELECT * FROM EventPrizeUser WHERE `UserMobile`='161751810')

第一條走index_merge，第二條走ref(const)。ref是要優(yōu)于index_merge，雖然該條語句可能OR的性能略高于UNION（約1ms)，但UNION可以保證一定走索引，而MySQL的OR執(zhí)行計劃不走index_merge的概率也蠻高。OR的每個條件列都必須使用索引，OR才使用索引。

6、使用合適確數(shù)據(jù)類型、縮減存儲空間

-使用ENUM、而不是VARCHAR。ENUM利用TINYINT、類型緊湊、比較快，但卻可以有字符串的“華麗外表”。如果是預(yù)定義好的類型，可以嘗試SET類型。?ENUM新增類型。使用PROCEDURE ANALYSE分析出表的ENUM建議。

-使用DATE、TIMESTAMP，避免DATETIME。TIMESTAMP的存儲空間是DATETIME的一半。

7、避免不必要排序，如DISTINCT等都會觸發(fā)排序

－GROUP BY A ORDER BY NULL。GROUP BY默認會使用排序，所以如果結(jié)果集比較大、可以采用ORDER BY NULL去掉。

－ORDER BY，僅對WHERE中同個組合索引內(nèi)的key采用統(tǒng)一ASC/DESC方式

例：SELECT * FROM WHERE part_key1 ORDER BY part_key1 DESC, part_key2 DESC

8、慎用NOT，避免使用IN、 NOT IN、<>、OR或HAVING等

用EXIST、NOT EXISTS代替IN、NOT EXISTS，因為可以直接走關(guān)聯(lián)子句的WHERE。<>可以用 “> & <”代替。如：

SELECT MemberCardID FROM `MC_MemberCard` WHERE MemberCardID <> 1247

vs.

SELECT MemberCardID FROM `MC_MemberCard` WHERE MemberCardID < 1247 OR MemberCardID > 1247

faster 1ms

9、Wildcard，LIKE ‘a%’，NOT ‘%a%’

’a%’為前綴匹配、走索引，但’%a%’導(dǎo)致全表查詢。

10、不要以字符形式聲明數(shù)字

a=1、NOT a = ‘1’，因為會使索引失效、導(dǎo)致全表掃描。?會么?

11、禁用SELECT FOR UPDATE

FOR UPDATE屬于悲觀鎖（Pessimistic Locking），在整個數(shù)據(jù)處理過程中將處于鎖定狀態(tài)。樂觀鎖（Optimistic Locking）則采用更加寬松的鎖機制。wiki定義如下：

Optimistic concurrency control (OCC) is a concurrency control method for relational database management systems that assumes that multiple transactions can complete without affecting each other, and that therefore transactions can proceed without locking the data resources that they affect. Before committing, each transaction verifies that no other transaction has modified its data. If the check reveals conflicting modifications, the committing transaction rolls back。

樂觀鎖最常用方式是通過version或TIMESTAMP，防止數(shù)據(jù)不一致問題。修改數(shù)據(jù)時可利用行寫鎖保證唯一性：

UPDATE T SET VERSION+1=VERSION WHERE ID=xxx

Hibernate在框架支持樂觀鎖機制，IBATIS中暫時沒有相應(yīng)支持，但可參考下文：

http://matejtymes.blogspot.hk/2010/11/optimistic-locking-on-ibatis.html

還可使用前置條件解決并發(fā)問題，如：

UPDATE STATUS=’BUY_SUCC’ WHERE OrderId=xxx AND Status=’WAITING_PAY’

12、垂直分割

水平分割、SQL太復(fù)雜不好處理，但經(jīng)驗而言，一般情景下是沒有太多效率提升，可以將查詢頻煩、固定表長的部分作為一部分。？啥時候該做這件事？

13、高并發(fā)寫操作的表，不建議使用自增ID

使用自增ID、會引起寫鎖保護，也不能使用MySQL的UUID()，因為會導(dǎo)致主備數(shù)據(jù)不一致。并發(fā)應(yīng)用程序中生成ID，保證唯一性、推薦兩種方式：

－經(jīng)典的combined guid/timestamp方式：占32字節(jié)，效率太慢。利用BitConverter.ToInt()轉(zhuǎn)換成8個字節(jié)，可以接受的友好；

－根據(jù)業(yè)務(wù)規(guī)則自定義方案。如：12位年月日時分秒+3位服務(wù)器編碼+3位表編碼+5位隨機碼/流水碼。?啥級別自增會防礙讀寫?

14、使用Prepared Statements（JDBC）

次少SQL解析、生成執(zhí)行計劃次數(shù)，順帶過濾注入。在IBATIS中，#{id}表示PreparedStatement parameter，在XML語句配制中有statementType參數(shù)，默認為PREPARED。

再送一個SQL優(yōu)化的網(wǎng)站：

http://www.mysqlperformanceblog.com/

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com