優(yōu)化Group By語句

默認(rèn)情況下,MySQL 排序所有GROUP BY col1,col2,.....查詢的辦法如同在查詢中指定ORDER BY col1,col2,....如果顯式包括一個包含相同的列的ORDER BY子句,MySQL 可以毫不減速地對它進(jìn)行優(yōu)化,盡管仍然進(jìn)行排序.如果查詢包括GROUP BY 但你想要避免排序結(jié)果的消耗,你可以指定ORDER BY NULL禁止排序.

優(yōu)化Order by語句

在某些情況中,MySQL 可以使用一個索引來滿足ORDER BY 子句,而不必要額外的排序.where 條件和order by 使用相同的索引,并且order by 的順序和索引順序相同,并且order by 的字段都是升序或者都是降序.

優(yōu)化insert語句

如果你同時從同一客戶插入很多行,使用多個值表的INSERT 語句.這比使用分開 INSERT 語句快(在一些情況中幾倍).Insert into test values(1,2),(1,3),(1,4)…

如果你從不同客戶插入很多行,能通過使用INSERT DELAYED 語句得到更高的速度.Delayed 的含義是讓insert 語句馬上執(zhí)行,其實數(shù)據(jù)都被放在內(nèi)存的隊列中,并沒有真正的寫入磁盤；這比每條語句都分別插入要快的多；LOW_PRIORITY剛好相反,在所有其他用戶對表的讀寫完成后才進(jìn)行插入.

將索引文件和數(shù)據(jù)文件分在不同的磁盤上存放(利用建表中的選項)；

如果進(jìn)行批量插入,可以增加bulk_insert_buffer_size 變量值的辦法來提高速度,但是,這只能對myisam表使用

當(dāng)從一個文本文件裝載一個表時,使用LOAD DATA INFILE.這通常比使用很多INSERT語句快20倍；

根據(jù)應(yīng)用情況使用replace 語句代替insert；

根據(jù)應(yīng)用情況使用ignore 關(guān)鍵字忽略重復(fù)記錄.

大批量插入數(shù)據(jù)

1. 對于Myisam 類型的表,可以通過以下方式快速的導(dǎo)入大量的數(shù)據(jù).

ALTER TABLE tblname DISABLE KEYS;

這兩個命令用來打開或者關(guān)閉Myisam 表非唯一索引的更新.在導(dǎo)入大量的數(shù)據(jù)到一個非空的Myisam 表時,通過設(shè)置這兩個命令,可以提高導(dǎo)入的效率.對于導(dǎo)入大量數(shù)據(jù)到一個空的Myisam 表,默認(rèn)就是先導(dǎo)入數(shù)據(jù)然后才創(chuàng)建索引的,所以不用進(jìn)行設(shè)置.

2. 而對于Innodb 類型的表,這種方式并不能提高導(dǎo)入數(shù)據(jù)的效率.對于Innodb 類型的表,我們有以下幾種方式可以提高導(dǎo)入的效率：

a. 因為Innodb 類型的表是依照主鍵的順序保存的,所以將導(dǎo)入的數(shù)據(jù)依照主鍵的順序排列,可以有效的提高導(dǎo)入數(shù)據(jù)的效率.如果Innodb 表沒有主鍵,那么系統(tǒng)會默認(rèn)創(chuàng)建一個內(nèi)部列作為主鍵,所以如果可以給表創(chuàng)建一個主鍵,將可以利用這個優(yōu)勢提高導(dǎo)入數(shù)據(jù)的效率.

b. 在導(dǎo)入數(shù)據(jù)前執(zhí)行SET UNIQUE_CHECKS=0,關(guān)閉唯一性校驗,在導(dǎo)入結(jié)束后執(zhí)行SETUNIQUE_CHECKS=1,恢復(fù)唯一性校驗,可以提高導(dǎo)入的效率.

c. 如果應(yīng)用使用自動提交的方式,建議在導(dǎo)入前執(zhí)行SET AUTOCOMMIT=0,關(guān)閉自動提交,導(dǎo)入結(jié)束后再執(zhí)行SET AUTOCOMMIT=1,打開自動提交,也可以提高導(dǎo)入的效率.

查詢的優(yōu)化

讀為主可以設(shè)置low_priority_updates=1,寫的優(yōu)先級調(diào)低,告訴MYSQL盡量先處理讀求

為查詢緩存優(yōu)化你的查詢

大多數(shù)的MySQL服務(wù)器都開啟了查詢緩存.這是提高性最有效的方法之一,而且這是被MySQL的數(shù)據(jù)庫引擎處理的.當(dāng)有很多相同的查詢被執(zhí)行了多次的時候,這些查詢結(jié)果會被放到一個緩存中,這樣,后續(xù)的相同的查詢就不用操作表而直接拜訪緩存結(jié)果了.

這里最主要的問題是,對于程序員來說,這個事情是很容易被忽略的.因為,我們某些查詢語句會讓MySQL不使用緩存.請看下面的示例：

// 查詢緩存不開啟

拆分大的 DELETE 或 INSERT 語句

如果你必要在一個在線的網(wǎng)站上去執(zhí)行一個大的 DELETE 或 INSERT 查詢,你必要非常小心,要避免你的操作讓你的整個網(wǎng)站停止相應(yīng).因為這兩個操作是會鎖表的,表一鎖住了,別的操作都進(jìn)不來了.

Apache 會有很多的子進(jìn)程或線程.所以,其工作起來相當(dāng)有效率,而我們的服務(wù)器也不希望有太多的子進(jìn)程,線程和數(shù)據(jù)庫鏈接,這是極大的占服務(wù)器資源的事情,尤其是內(nèi)存.

如果你把你的表鎖上一段時間,比如30秒鐘,那么對于一個有很高拜訪量的站點(diǎn)來說,這30秒所積累的拜訪進(jìn)程/線程,數(shù)據(jù)庫鏈接,打開的文件數(shù),可能不僅僅會讓你泊WEB服務(wù)Crash,還可能會讓你的整臺服務(wù)器馬上掛了.

所以,如果你有一個大的處理,你定你一定把其拆分,使用 LIMIT 條件是一個好的辦法.下面是一個示例：

while (1) {

where語句的優(yōu)化

1.盡量避免在 where 子句中對字段進(jìn)行表達(dá)式操作

select id from uinfo_jifen where jifen/60 > 10000;

優(yōu)化后:

Select id from uinfo_jifen where jifen>600000;

2.應(yīng)盡量避免在where子句中對字段進(jìn)行函數(shù)操作,這將導(dǎo)致mysql放棄使用索引

select uid from imid where datediff(create_time,'2011-11-22')=0

優(yōu)化后

select uid from imid where create_time> ='2011-11-21‘ and create_time<‘2011-11-23’;

索引的優(yōu)化

MySQL只有對以下操作符才使用索引：<,<=,=,>,>=,BETWEEN,IN,以及某些時候的LIKE.

盡量不要寫!=或者<>的sql,用between或> and <代替,否則可能用不到索引

Order by 、Group by 、Distinct 最好在必要這個列上建立索引,利于索引排序

盡量利用mysql索引排序

沒方法的情況下,使用強(qiáng)制索引Force index(index_name)

盡量避勉innodb用非常大尺寸的字段作為主鍵

較頻繁的作為查詢條件的字段應(yīng)該創(chuàng)建索引;

選擇性高的字段比擬適合創(chuàng)建索引;

作為表關(guān)聯(lián)字段一般都必要創(chuàng)索引.

更新非常頻繁的字段不適合創(chuàng)建索引;

不會出現(xiàn)在 WHERE 子句中的字段不該創(chuàng)建索引.

選擇性太低的字段不適合單獨(dú)創(chuàng)建索引

盡量不要用子查詢

mysql> explain select uid_,count(*) from smember_6 where uid_ in (select uid_ from alluid) group by uid_;| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+--------------------+-----------+-------+---------------+---------+---------+------+----------+--------------------------+| 1 | PRIMARY | smember_6 | index | NULL | PRIMARY | 8 | NULL | 53431264 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | alluid | ALL | NULL | NULL | NULL | NULL | 2448 | Using where |--優(yōu)化后| mysql> explain select a.uid_,count(*) from smember_6 a,alluid b where a.uid_=b.uid_ group by uid_;+----+-------------+-------+------+---------------+---------+---------+------------+------+---------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+---------+---------+------------+------+---------------------------------+| 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 2671 | Using temporary; Using filesort | | 1 | SIMPLE | a | ref | PRIMARY | PRIMARY | 4 | ssc.b.uid_ | 1 | Using index

Join的優(yōu)化

如果你的應(yīng)用程序有很多 JOIN 查詢,你應(yīng)該確認(rèn)兩個表中Join的字段是被建過索引的.這樣,MySQL內(nèi)部會啟動為你優(yōu)化Join的SQL語句的機(jī)制.

而且,這些被用來Join的字段,應(yīng)該是相同的類型的.例如：如果你要把 DECIMAL 字段和一個 INT 字段Join在一起,MySQL就無法使用它們的索引.對于那些STRING類型,還必要有相同的字符集才行.(兩個表的字符集有可能不一樣)

表的優(yōu)化

盡可能的使用 NOT NULL

除非你有一個很特別的原因去使用 NULL 值,你應(yīng)該總是讓你的字段堅持 NOT NULL.

不要以為 NULL 不需要空間,其需要額外的空間,并且,在你進(jìn)行比擬的時候,你的程序會更復(fù)雜.

當(dāng)然,這里并不是說你就不能使用NULL了,現(xiàn)實情況是很復(fù)雜的,依然會有些情況下,你必要使用NULL值.

下面摘自MySQL本身的文檔：

“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

固定長度的表會更快

如果表中的所有字段都是“固定長度”的,整個表會被認(rèn)為是 “static” 或 “fixed-length”. 例如,表中沒有如下類型的字段： VARCHAR,TEXT,BLOB.只要你包括了其中一個這些字段,那么這個表就不是“固定長度靜態(tài)表”了,這樣,MySQL 引擎會用另一種辦法來處理.

固定長度的表會提高性能,因為MySQL搜尋得會更快一些,因為這些固定的長度是很容易計算下一個數(shù)據(jù)的偏移量的,所以讀取的自然也會很快.而如果字段不是定長的,那么,每一次要找下一條的話,必要程序找到主鍵.

并且,固定長度的表也更容易被緩存和重建.不過,唯一的副作用是,固定長度的字段會浪費(fèi)一些空間,因為定長的字段無論你用不用,他都是要分配那么多的空間.

垂直分割

"垂直分割"是一種把數(shù)據(jù)庫中的表按列變成幾張表的辦法,這樣可以降低表的復(fù)雜度和字段的數(shù)目,從而達(dá)到優(yōu)化的目的.(以前,在銀行做過項目,見過一張表有100多個字段,很恐怖)

示例一：在Users表中有一個字段是家庭地址,這個字段是可選字段,相比起,而且你在數(shù)據(jù)庫操作的時候除了個人信息外,你并不必要經(jīng)常讀取或是改寫這個字段.那么,為什么不把他放到另外一張表中呢? 這樣會讓你的表有更好的性能,大家想想是不是,大量的時候,我對于用戶表來說,只有用戶ID,用戶名,口令,用戶角色等會被經(jīng)常使用.小一點(diǎn)的表總是會有好的性能.

示例二： 你有一個叫 “l(fā)ast_login” 的字段,它會在每次用戶登錄時被更新.但是,每次更新時會導(dǎo)致該表的查詢緩存被清空.所以,你可以把這個字段放到另一個表中,這樣就不會影響你對用戶ID,用戶名,用戶角色的不停地讀取了,因為查詢緩存會幫你增加很多性能.

另外,你必要注意的是,這些被分出去的字段所形成的表,你不會經(jīng)常性地去Join他們,不然的話,這樣的性能會比不分割時還要差,而且,會是極數(shù)級的下降.

越小的列會越快

對于大多數(shù)的數(shù)據(jù)庫引擎來說,硬盤操作可能是最重大的瓶頸.所以,把你的數(shù)據(jù)變得緊湊會對這種情況非常有幫助,因為這減少了對硬盤的拜訪.

參看 MySQL 的文檔 Storage Requirements 查看所有的數(shù)據(jù)類型.

如果一個表只會有幾列而已(比如說字典表,配置表),那么,我們就沒有理由使用 INT 來做主鍵,使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 會更經(jīng)濟(jì)一些.如果你不需要記錄時間,使用 DATE 要比 DATETIME 好得多.

當(dāng)然,你也必要留夠足夠的擴(kuò)展空間,不然,你日后來干這個事,你會死的很難看,參看Slashdot的例子(2009年11月06日),一個簡單的ALTER TABLE語句花了3個多小時,因為里面有一千六百萬條數(shù)據(jù).