程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 幾個罕見的MySQL的可優化點歸結總結

幾個罕見的MySQL的可優化點歸結總結

編輯：MySQL綜合教程

幾個罕見的MySQL的可優化點歸結總結。本站提示廣大學習愛好者：（幾個罕見的MySQL的可優化點歸結總結）文章只能為提供參考，不一定能成為您想要的結果。以下是幾個罕見的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(*)慢

count(id) === count(1) where id is not null

假如沒有(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停止規模查詢，完整走索引，而且只讀取一筆記錄，速度異常快。然則，這類用法的限制是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. 主鍵不是自增列
自增列的主鍵有多個利益：