MySQL數據庫SQL層級優化_MySQL

本篇主涉及MySQL SQL Statements層面的優化。

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

其次，Explain作為分析SQL的優化利器，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多條語句

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

insert into user(

id,

userName,

passWord

) values

]]]]>

(

#list[].id#,

#list[].userName#,

#list[].passWord#

)

]]]]>

2、有效利用索引

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

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

－考慮聯合索引，但存在”first hit”問題。

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

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

3、一定要使用LIMIT 1

大數據集，會占用內存、帶寬等資源。使用LIMIT，強迫分頁，減少服務器壓力。

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

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是要優于index_merge，雖然該條語句可能OR的性能略高于UNION（約1ms)，但UNION可以保證一定走索引，而MySQL的OR執行計劃不走index_merge的概率也蠻高。OR的每個條件列都必須使用索引，OR才使用索引。

6、使用合適確數據類型、縮減存儲空間

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

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

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

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

－ORDER BY，僅對WHERE中同個組合索引內的key采用統一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，因為可以直接走關聯子句的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%’導致全表查詢。

10、不要以字符形式聲明數字

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

11、禁用SELECT FOR UPDATE

FOR UPDATE屬于悲觀鎖（Pessimistic Locking），在整個數據處理過程中將處于鎖定狀態。樂觀鎖（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，防止數據不一致問題。修改數據時可利用行寫鎖保證唯一性：

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

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

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

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

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

12、垂直分割

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

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

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

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

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

14、使用Prepared Statements（JDBC）

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

再送一個SQL優化的網站：

http://www.mysqlperformanceblog.com/

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