這篇文章主要介紹了幾個常見的MySQL的可優化點歸納總結,包括在編程時處理索引、分頁以及數據類型時可用到的地方,需要的朋友可以參考下

索引相關

1. 查詢（或更新，刪除，可以轉換為查詢）沒有用到索引

這是最基礎的步驟，需要對sql執行explain查看執行計劃中是否用到了索引，需要重點關注type=ALL, key=NULL的字段。

2. 在索引字段上施加函數

1. to_char(gmt_created, ‘mmdd') = '0101′ 

正確的寫法

1. gmt_created between to_date(“20090101″, “yyyymmdd”) and to_date(“20090102″, “yyyymmdd”) 

3. 在索引字段上使用全模糊

1. member_id like ‘%alibab%' 

B樹無法解決此類問題，可以考慮搜索引擎。

但是member_id like ‘alibab%'可以用到索引。

其實，對任何一個字段使用 like ‘%xxxx%'都是一種不規范的做法，需要能檢查到這種錯誤用法。

4. 多列字段的索引，沒有用到前導索引

索引：(memeber_id, group_id)

where group_id=9234，實際上，這個條件是沒有辦法用到上面的索引的。這是一個很常見的誤用。要理解為什么不能用到這個索引，需要理解mysql如何構造多列索引的。

索引是一棵B樹，問題是，對于多列索引，mysql將索引字段按照索引建立的順序進行拼裝，組成一個新的字符串，這個字符串被用來做為構建B樹的鍵。所以，在查詢條件里，如果沒有用到前導列，就沒辦法訪問多列索引的B樹。

應該建立索引：(group_id, member_id)

5. 訪問到了索引之外的字段

索引(member_id, subject)

1. select subject from offer where member_id=234 

在member_id=234記錄數很多的情況下，會優于

1. select subject, gmt_created from offer where member_id=234 

原因是第二條sql會根據索引查找到的rowid訪問表里的記錄。第一條sql使用索引范圍掃描就可以得到結果。

如果某個sql執行次數很多，但是讀取的字段沒有被索引覆蓋，那么，可能需要建立覆蓋性索引。

6. 計數count(id)有時比count(*)慢

如果沒有(id)索引，那么會用全表掃描，而count(*)會使用最優的索引進行用索引快速全掃描

計數統一使用count(*)

7. 正確使用stop機制

判斷member_id在offer表中是否存在記錄：

1. select count(*) from offer where member_id=234 limit 1 

優于

1. select count(*) from offer where member_id=234 

原因是第一條sql會在得到第一條符合條件的記錄后停止。

高效分頁 1. 高效的分頁

使用join技術，利用索引查找到符合條件的id，構造成臨時表，用這個小的臨時表與原表做join

1. select * 
2. from 
3. ( 
4. select t.*, rownum AS rn 
5. from 
6. (select * from blog.blog_article 
7. where domain_id=1 
8. and draft=0 
9. order by domain_id, draft, gmt_created desc) t 
10. where rownum >= 2 
11. ) a 
12. where a.rn <= 3 

應該改寫成

1. select blog_article.* 
2. from 
3. ( 
4. select rid, rownum as rn 
5. from 
6. ( 
7. select rowid as id from blog.blog_article 
8. where domain_id=1 
9. and draft=0 
10. order by domain_id, draft, gmt_created desc 
11. ) t 
12. where rownum >= 2 
13. ) a, blog_article 
14. where a.rn >= 3 
15. and a.rid = blog_article.rowid 

2. order by沒有用到索引

有索引(a, b,c )

混合排序規則

1. ORDER BY a ASC, b DESC, c DESC /* mixed sort direction */ 

缺失了前導列

1. WHERE g = const ORDER BY b, c /* a prefix is missing */ 

缺失了中間列

1. WHERE a = const ORDER BY c /* b is missing */ 

使用了不在索引中的列進行排序

1. WHERE a = const ORDER BY a, d /* d is not part of index */ 

高效地利用primary key

隨機查詢

一個錯誤的做法：

1. select * from title where kind_id=1 order by rand() limit 1; 
2. create index k on title(kind_id); 

這個sql執行過程中需要全表掃描，并且將數據保存到臨時表，這是一個非常耗時的操作。

改進的做法，利用偏移量。

1. select round(rand() * count(*)) from title where kind_id=1; 
2. select * from title where kind_id=1 limit 1 offset $random; 
3. create index k on title(kind_id); 

相比上面的做法，這種寫法能夠利用到kind_id上的索引，減少了需要掃描的數據塊。但是，如果offset非常大，那么需要掃描的數據塊也非常大，極端情況是掃描索引k的所有數據塊。

最優的做法，利用主鍵進行范圍查找

1. select round(rand() * count(*)) from title where kind_id=1; 
2. select * from title where kind_id = and id > $random limit 1; 

這個sql利用primary key進行范圍查詢，完全走索引，并且只讀取一條記錄，速度非常快。但是，這種用法的限制是primary key必須是int型，并且是連續自增長的。

高效join 1. 小表驅動大表進行join 2. 避免子查詢

子查詢是一個影響性能的隱患。應該使用join改寫sql。

數據類型 1. 避免隱式轉換

1. CREATE TABLE `user` ( 
2. `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, 
3. `account` char(11) NOT NULL COMMENT ”, 
4. `email` varchar(128), 
5. PRIMARY KEY (`id`), 
6. UNIQUE KEY `username` (`account`) 
7. ) ENGINE=InnoDB CHARSET=utf8; 
8. mysql> explain select * from user where account=123 /G 
9. *************************** 1. row *************************** 
10. id: 1 
11. select_type: SIMPLE 
12. table: user 
13. type: ALL 
14. possible_keys: username 
15. key: NULL 
16. key_len: NULL 
17. ref: NULL 
18. rows: 2 
19. Extra: Using where 
20. 1 row in set (0.00 sec) 

可以看到，account=123的條件并沒有用到唯一索引`username`。mysql的server從storage engine中讀取所有的記錄，使用to_number()函數，將記錄中的account轉換成數字，被轉換后的數字用來和參數比較。我們的測試表里有2條記錄，而執行計劃中rows的值也是2，并且type的值為ALL，這也說明索引`username`并沒有被用到。

復制代碼代碼如下:

mysql> explain select * from user where account='123′ /G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: user

type: const

possible_keys: username

key: username

key_len: 33

ref: const

rows: 1

Extra:

1 row in set (0.00 sec)

參數為字符串類型，我們可以看到索引`username`，被使用到了。

這是一個經常被誤用的做法。

2. 主鍵不是自增列

自增列的主鍵有多個好處：

插入性能高。

減小page的碎片。

提供二級索引的性能，降低二級索引的空間，因為二級索引存儲的是主鍵的值，并不是page中的行id。