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

首先，推薦一個(gè)鏈接為萬物之始： 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)單條插入，代價(jià)很昂貴！在IBATIS中一次插入多條語句配置：

<insert id="insertUserList" parameterClass="java.util.List">

?<![CDATA[

?????insert into user(

?????????id,

?????????userName,

?????????passWord

?????) values

?]]]]>

?<iterate conjunction=",">

?<![CDATA[

?????(

?????????#list[].id#,

?????????#list[].userName#,

?????????#list[].passWord#

?????)

?]]]]>

?</iterate>

?</insert>

?

2、有效利用索引

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

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

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

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

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

3、一定要使用LIMIT 1

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

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

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`='18961751810'

vs.

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

UNION

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

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

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

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

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

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

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

－ORDER BY，僅對(duì) WHERE中 同個(gè) 組合索引 內(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，因?yàn)榭梢灾苯幼哧P(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(chǎn)%’，NOT ‘%a%’

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

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

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

11、禁用SELECT FOR UPDATE

FOR UPDATE屬于悲觀鎖（Pessimistic Locking），在整個(gè)數(shù)據(jù)處理過程中將處于鎖定狀態(tài)。樂觀鎖（Optimistic Locking）則采用更加寬松的鎖機(jī)制。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ù)時(shí)可利用行寫鎖保證唯一性：

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

Hibernate在框架支持樂觀鎖機(jī)制，IBATIS中暫時(shí)沒有相應(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)驗(yàn)而言，一般情景下是沒有太多效率提升，可以將查詢頻煩、固定表長(zhǎng)的部分作為一部分。 ？啥時(shí)候該做這件事？

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

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

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

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

14、使用Prepared Statements（JDBC）

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

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

http://www.mysqlperformanceblog.com/

MySQL數(shù)據(jù)庫SQL層級(jí)優(yōu)化