這篇文章主要介紹了幾個常見的MySQL的可優化點歸納總結,包括在編程時處理索引、分頁以及數據類型時可用到的地方,需要的朋友可以參考下
索引相關
1. 查詢(或更新,刪除,可以轉換為查詢)沒有用到索引
這是最基礎的步驟,需要對sql執行explain查看執行計劃中是否用到了索引,需要重點關注type=ALL, key=NULL的字段。
2. 在索引字段上施加函數
- to_char(gmt_created, ‘mmdd') = '0101′
正確的寫法
- gmt_created between to_date(“20090101″, “yyyymmdd”) and to_date(“20090102″, “yyyymmdd”)
3. 在索引字段上使用全模糊
- 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)
- select subject from offer where member_id=234
在member_id=234記錄數很多的情況下,會優于
- 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表中是否存在記錄:
- select count(*) from offer where member_id=234 limit 1
優于
- select count(*) from offer where member_id=234
原因是第一條sql會在得到第一條符合條件的記錄后停止。
高效分頁 1. 高效的分頁
使用join技術,利用索引查找到符合條件的id,構造成臨時表,用這個小的臨時表與原表做join
- select *
- from
- (
- select t.*, rownum AS rn
- from
- (select * from blog.blog_article
- where domain_id=1
- and draft=0
- order by domain_id, draft, gmt_created desc) t
- where rownum >= 2
- ) a
- where a.rn <= 3
應該改寫成
- select blog_article.*
- from
- (
- select rid, rownum as rn
- from
- (
- select rowid as id from blog.blog_article
- where domain_id=1
- and draft=0
- order by domain_id, draft, gmt_created desc
- ) t
- where rownum >= 2
- ) a, blog_article
- where a.rn >= 3
- and a.rid = blog_article.rowid
2. order by沒有用到索引
有索引(a, b,c )
混合排序規則
- ORDER BY a ASC, b DESC, c DESC /* mixed sort direction */
缺失了前導列
- WHERE g = const ORDER BY b, c /* a prefix is missing */
缺失了中間列
- WHERE a = const ORDER BY c /* b is missing */
使用了不在索引中的列進行排序
- WHERE a = const ORDER BY a, d /* d is not part of index */
高效地利用primary key
隨機查詢
一個錯誤的做法:
- select * from title where kind_id=1 order by rand() limit 1;
- create index k on title(kind_id);
這個sql執行過程中需要全表掃描,并且將數據保存到臨時表,這是一個非常耗時的操作。
改進的做法,利用偏移量。
- select round(rand() * count(*)) from title where kind_id=1;
- select * from title where kind_id=1 limit 1 offset $random;
- create index k on title(kind_id);
相比上面的做法,這種寫法能夠利用到kind_id上的索引,減少了需要掃描的數據塊。但是,如果offset非常大,那么需要掃描的數據塊也非常大,極端情況是掃描索引k的所有數據塊。
最優的做法,利用主鍵進行范圍查找
- select round(rand() * count(*)) from title where kind_id=1;
- select * from title where kind_id = and id > $random limit 1;
這個sql利用primary key進行范圍查詢,完全走索引,并且只讀取一條記錄,速度非??臁5?,這種用法的限制是primary key必須是int型,并且是連續自增長的。
高效join 1. 小表驅動大表進行join 2. 避免子查詢
子查詢是一個影響性能的隱患。應該使用join改寫sql。
數據類型 1. 避免隱式轉換
- CREATE TABLE `user` (
- `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
- `account` char(11) NOT NULL COMMENT ”,
- `email` varchar(128),
- PRIMARY KEY (`id`),
- UNIQUE KEY `username` (`account`)
- ) ENGINE=InnoDB CHARSET=utf8;
- mysql> explain select * from user where account=123 /G
- *************************** 1. row ***************************
- id: 1
- select_type: SIMPLE
- table: user
- type: ALL
- possible_keys: username
- key: NULL
- key_len: NULL
- ref: NULL
- rows: 2
- Extra: Using where
- 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。
新聞熱點
疑難解答