|
《High Performance MySQL》讀書筆記
1.MySQL基礎(chǔ)知識(shí) 1.1安裝包的選擇 1.1.1二進(jìn)制包安裝 vs. 源代碼編譯安裝 如沒(méi)有特殊定制的需求,，沒(méi)必要用源代碼編譯安裝,。 比如你想 MySQL被安裝在一個(gè)目錄,。
1.1.2.官方二進(jìn)制安裝包 vs. 發(fā)行版二進(jìn)制包 官方的二進(jìn)制包的文件布局更接近從源代碼安裝的。各個(gè)發(fā)行版的二進(jìn)制包安裝后其文件布局可能會(huì)很不一樣,。 如果要使用官方二進(jìn)制包安裝,，推薦使用tarball格式的，因?yàn)樗惭b后的文件結(jié)構(gòu)最接近最常見的結(jié)構(gòu),。
1.2.配置文件 1.2.1.文件位置 在類unix操作系統(tǒng)下,，搜索順序?yàn)椋?/font> /etc/my.cnf datadir/my.cnf ~/.my.cnf
在windows下，順序?yàn)椋?/font> %SystemRoot/my.ini c:\my.cnf
1.2.2.和配置文件相關(guān)的啟動(dòng)參數(shù) --no-defaults 不使用任何配置文件 --defaults-file=/path/to/file 指定配置文件的路徑 --defaults-extra-file=/path/to/file 在讀取my.cnf文件后額外讀取的配置文件
1.2.3.幾個(gè)示例文件的用處 my-small.cnf
my-medium.cnf 內(nèi)存為32M到64M的MySQL專用的服務(wù)器 內(nèi)存最多為128M的共享服務(wù)器（跑著MySQL,、Apache等服務(wù)）,。
my-large.cnf
my-huge.cnf
1.3.show命令 1.3.1.show variables 顯示運(yùn)行變量
1.3.2.show status 顯示狀態(tài)
1.3.3.show innodb status 顯示innodb的狀態(tài)
2.存儲(chǔ)引擎
2.1.MySQL架構(gòu)
2.2.鎖和并發(fā)
2.2.1.讀/寫鎖 解決多并發(fā)讀寫的這一經(jīng)典問(wèn)題的方法是實(shí)現(xiàn)一個(gè)包含讀鎖和寫鎖的鎖系統(tǒng)。 在資源上的讀鎖是共享的,，多個(gè)客戶端可以同時(shí)讀一個(gè)資源,。 寫鎖是獨(dú)占的。當(dāng)資源上有寫鎖的時(shí)候,，任何其它的讀和寫都是不允許的,。
2.2.2.鎖粒度 多數(shù)商業(yè)數(shù)據(jù)庫(kù)使用行鎖定。
2.2.2.1.表鎖定 因?yàn)?/font>MyISAM設(shè)計(jì)時(shí)假想的情況是90%的請(qǐng)求是讀,，所以MyISAM的表鎖定在這種情況下也能獲得很好的性能,。 換句話說(shuō)，對(duì)于讀大量多于寫的應(yīng)用,，適合選用MyISAM,。
2.2.2.2.頁(yè)鎖定 頁(yè)鎖定的開銷比表鎖定大。 頁(yè)鎖定中影響并發(fā)性的因素是頁(yè)的大小,。 BDB（Berkeley DB）使用頁(yè)鎖定,，頁(yè)大小是8KB,。
2.2.2.3.行鎖定 行鎖定提供最大的并行性,，當(dāng)然也需要最大的額外開銷。 InnoDB使用的是行鎖定,，但不是簡(jiǎn)單的行鎖定,，它使用的是和multiversioning schema接合的行鎖定。
2.2.3Multi-Version并行控制 Multi-Version Concurrency Control（MVCC）這種并行措施被Oracle,、PostgreSQL和MySQL的Innodb存儲(chǔ)引擎,。 MVCC是行鎖定的另一種手段。它允許不上鎖的讀同時(shí)鎖定必要的記錄以便來(lái)寫,。
MVCC中,，所有查詢面對(duì)的實(shí)際上是一個(gè)數(shù)據(jù)的快照。每行又2個(gè)額外的隱藏屬性,。它表示了這行創(chuàng)建和刪除的”時(shí)間“,。這里存儲(chǔ)的不是真實(shí)的時(shí)間,，而是一個(gè)由數(shù)據(jù)庫(kù)系統(tǒng)維護(hù)的版本號(hào)。每執(zhí)行一次查詢（如果支持事務(wù),，則不是查詢而是事務(wù)）,，這個(gè)版本號(hào)就增加一次。 在MCVV的機(jī)制下,，DBMS的主要任務(wù)就是跟蹤所有正在運(yùn)行的查詢以及它們的版本號(hào),。
SELECT 當(dāng)記錄被select時(shí)，DBMS必須檢查此行是否符合以下規(guī)則：
INSERT 當(dāng)行被添加時(shí),，DBMS同時(shí)將當(dāng)前版本號(hào)作為行的創(chuàng)建版本號(hào)一并插入。
DELETE DBMS把當(dāng)前版本號(hào)寫入記錄的刪除版本號(hào)
UPDATE 寫入一個(gè)新的原行的副本,，將當(dāng)前版本號(hào)作為創(chuàng)建版本號(hào),，將記錄的刪除版本號(hào)作為副本的刪除版本號(hào)。
這樣,，讀操作就不需要鎖表,。但附加信息增加了存儲(chǔ)空間，DBMS維護(hù)版本號(hào)又增加了系統(tǒng)開銷,。
MySQL的鎖模型和并行行
2.3.事務(wù)
2.4.選擇正確的引擎
2.4.1.考慮因素
2.4.1.1.事務(wù)和并發(fā)性 如果應(yīng)用需要事務(wù)和高讀/寫并發(fā),，InnoDB是你的最佳選擇,。 如果應(yīng)用需要事務(wù)但不要求高讀/寫并發(fā)，InnoDB和BDB都是不錯(cuò)的選擇,。 如果應(yīng)用不需要事務(wù)且查詢中讀大量大于寫或者寫大量大于讀,，則MyISAM是比較好的選擇。很多web應(yīng)用屬于這種情況,。
2.4.1.2.備份 如果服務(wù)器能定期停機(jī)做備份,，存儲(chǔ)引擎比較好選擇。如果需要聯(lián)機(jī)備份,，則比較麻煩,。 混合的存儲(chǔ)引擎架構(gòu)備份比較麻煩，考慮到復(fù)雜度，應(yīng)盡量選擇一致的存儲(chǔ)引擎,。
2.4.1.3.特殊功能 SELECT COUNT(*) FROM mytable 如果應(yīng)用中對(duì)count函數(shù)有性能要求,，MyISAM比InnoDB要好。MyISAM能時(shí)刻知道總的行數(shù),，但InnoDB需要每次都重新計(jì)算,。 如果應(yīng)用需要定義實(shí)體完整性，則需要InnoDB,。 如果應(yīng)用需要全文搜索,，則MyISAM。 如果發(fā)現(xiàn)需要的功能不能由一個(gè)存儲(chǔ)引擎滿足時(shí),，可能需要將一個(gè)表分割成多個(gè)表,。
2.5.存儲(chǔ)引擎
表的定義存儲(chǔ)在.frm文件中。
Show table status命令顯示表的屬性,。 例：SHOW TABLE STATUS LIKE 'user' 查看user表的屬性,。
2.5.1.MyISAM ISAM= Index Sequential Access Method
2.5.1.1.存儲(chǔ) 有2種文件：數(shù)據(jù)文件（.MYD）和索引文件（.MYI）。 MyISAM的文件可以自由復(fù)制,。 MyISAM的最大文件尺寸取決于文件系統(tǒng),，但是出于性能考慮，MyISAM默認(rèn)只操作4GB的數(shù)據(jù),。MyISAM的索引使用32位的指針,。如果要使用操作4GB的數(shù)據(jù)，需要設(shè)置MAX_ROWS和AVG_ROW_LENGTH,。 比如： 此表能存儲(chǔ)32GB,。 CREATE TABLE mytable ( a INTEGER NOT NULL PRIMARY KEY, b CHAR(18) NOT NULL ) MAX_ROWS = 1000000000 AVG_ROW_LENGTH = 32;
2.5.1.2.其它事宜
鎖與并行 MyISAM使用共享的讀鎖和獨(dú)占的寫鎖
自動(dòng)恢復(fù) 啟動(dòng)MySQL時(shí)使用—myisam-recover選項(xiàng)可啟動(dòng)對(duì)MyISAM表的進(jìn)行修復(fù)
手動(dòng)修復(fù) 用命令CHECK TABLE mytable和REPAIR TABLE mytable檢查和修復(fù)表。 或者使用命令行myisamchk在服務(wù)器離線的時(shí)候檢查,。
索引 MyISAM可對(duì)BLOB和TEXT類型進(jìn)行索引,。
延后的鍵寫入（Delayed key writes） 有DELAY_KEY_WRITE屬性的表。對(duì)于索引的寫入,，并不是馬上寫入磁盤,，而是先寫入內(nèi)存緩存，當(dāng)此內(nèi)容從緩存中除去或者表被關(guān)閉的時(shí)候,，鍵內(nèi)容才被寫入磁盤,。這對(duì)負(fù)載很重且經(jīng)常被修改的表有很好的性能提升,。
2.5.2.壓縮的MyISAM表 如果要把數(shù)據(jù)庫(kù)放到光盤或者嵌入式系統(tǒng)中,，且數(shù)據(jù)庫(kù)不會(huì)被修改，可以用使用myisampack工具將MyISAM表壓縮,，以節(jié)約磁盤空間,。解壓縮所帶來(lái)的額外開銷不會(huì)很大，可以接受。
2.5.3.RAID MyISAM表 要使用raid表,，需要自己編譯mysql以便使用MySQL-Max包,。raid表是把數(shù)據(jù)文件分割，而不一定是要放到不同的磁盤上,。 2種使用raid表的目的：1,、突破文件系統(tǒng)的單個(gè)文件尺寸限制 2、將分開的數(shù)據(jù)文件放到不同的物理磁盤上以提高性能
下面是創(chuàng)建 raid表的命令： CREATE TABLE mytable ( a INTEGER NOT NULL PRIMARY KEY, b CHAR(18) NOT NULL ) RAID_TYPE = STRIPED RAID_CHUNKS = 4 RAID_CHUNKSIZE = 16; raid_type表示raid的類型,，可以是striped或者raid0,。raid_chunks表示要將數(shù)據(jù)文件分割成幾個(gè)。raid_chunksize表示向一個(gè)分割文件中寫入多少個(gè)KB后再移動(dòng)到下一個(gè)分割文件,。
上面的例子中,，MySQL將創(chuàng)建4個(gè)子目錄：00,01,02,03。每個(gè)子目錄中存儲(chǔ)一個(gè)文件mytable.MYD,。 注意：如果系統(tǒng)中有RAID控制器并有軟RAID,，raid表就不是太需要了。另外需要注意的是,，僅僅是將數(shù)據(jù)文件分塊,，而不是索引文件。如果你是為了解決文件系統(tǒng)的尺寸限制,，還需要注意索引文件,。 2.5.4.MyISAM Merge 表 Merge表是MyISAM最新的功能。raid表是將文件分割,，而merge表是將多個(gè)相似的表合并成一個(gè)虛表,。 這對(duì)用MySQL來(lái)存儲(chǔ)日志文件非常有用。每個(gè)表存儲(chǔ)一段時(shí)間的記錄,，整體分析的時(shí)候把表merge成一個(gè)虛擬表,。 用于merge的子表要符合以下條件： 完全一樣的定義 是MyISAM表 在MySQL4.1.1后，支持不同數(shù)據(jù)庫(kù)中表的merge
如果要壓縮MyISAM表,，記得把它從merge中去掉,，壓縮后再加到merge里面去。
下面是創(chuàng)建merge的示例： CREATE TABLE mytable0 ( a INTEGER NOT NULL PRIMARY KEY, b CHAR(18) NOT NULL ); CREATE TABLE mytable1 ( a INTEGER NOT NULL PRIMARY KEY, b CHAR(18) NOT NULL ); CREATE TABLE mytable2 ( a INTEGER NOT NULL PRIMARY KEY, b CHAR(18) NOT NULL ); CREATE TABLE mytable ( a INTEGER NOT NULL PRIMARY KEY, b CHAR(18) NOT NULL ) TYPE = MERGE UNION = (mytable0, mytable1, mytable2) INSERT_METHOD = LAST;
INSERT_METHOD告訴MySQL怎樣處理對(duì)merge表的插入操作,，其值可以為NO,、FIRST和LAST。NO表示不允許插入,。
3.基準(zhǔn)測(cè)試
3.1.基準(zhǔn)測(cè)試的重要性
3.2.基準(zhǔn)測(cè)試的策略
每次只改變一樣?xùn)|西 迭代測(cè)試 重復(fù)測(cè)試 使用真實(shí)數(shù)據(jù) 不要使用太多的客戶端（除非是在進(jìn)行壓力測(cè)試,，不然不要使用超過(guò)50個(gè)并發(fā)客戶端） 不要在服務(wù)器上運(yùn)行測(cè)試客戶端
3.3 MySQL基準(zhǔn)測(cè)試的工具 MySQL Benchmark Suit MySQL super-smack 一個(gè)壓力測(cè)試工具 MyBench 一個(gè)用perl寫的壓力測(cè)試工具，比super-smack更容易擴(kuò)展和定制
4.索引 4.1.基礎(chǔ) 4.1.1.基本概念 索引會(huì)增加insert update delete的開銷,，但會(huì)提高select的速度,。
4.1.1.1.部分索引 對(duì)整個(gè)字段進(jìn)行索引 ALTER TABLE mytable1 ADD INDEX (name);
對(duì)字段的前4個(gè)字節(jié)索引 ALTER TABLE mytable ADD INDEX(name(4));
4.1.1.2.多欄索引 對(duì)于查詢：select * from mytable1 where fname='smith' and lname='john' 理論上創(chuàng)建2個(gè)查詢：ALTER TABLE mytable1 ADD INDEX (fname,lname) 用戶可以這樣定義,，但MySQL不會(huì)同時(shí)使用2個(gè)查詢。實(shí)際上,，MySQL在每個(gè)表每個(gè)查詢中只會(huì)使用一個(gè)索引（除了union）,。 MySQL處理union是將2個(gè)表分別處理，然后再歸并的,。
4.1.1.3.索引順序 MySQL沒(méi)有提供手動(dòng)指定索引順序的方法,。MySQL自己能優(yōu)化索引。
4.1.1.4.作為約束的索引 索引不是只能用于加快查詢,，還可以作為約束使用,。 唯一索引（unique index）可以用來(lái)保證在某些列中某一值只會(huì)出現(xiàn)一次（除了NULL，這始終是特殊的情況）,。
建立唯一索引： ALTER TABLE mytable1 ADD UNIQUE(name); 因?yàn)槭撬饕?，所以也可以指定部分索引?/font> 如： ALTER TABLE mytable1 ADD UNIQUE(name(2)); 這個(gè)語(yǔ)句規(guī)定name的前2個(gè)字母不能相同。
引申而言,，對(duì)規(guī)定unique約束的字段查詢可以提高速度,。 如果唯一索引僅僅是起到唯一性約束而不提高查詢性能的話，等于是浪費(fèi)了空間,，但目前 MySQL沒(méi)有這樣的優(yōu)化處理,。
4.1.1.5.Clustered和secondary索引 MyISAM的索引存儲(chǔ)在單獨(dú)的文件里。這樣保證了MySQL能快速地按照索引查詢,。
如果使用clustered索引,，主鍵和記錄是“捆綁”在一起的，并且是按照主鍵的順序排列的,。InnoDB用的是clustered索引,。在Oracle中，clustered索引被稱為“index-organized tables”,，這能幫助你理解主鍵和行順序之間的關(guān)系,。
如果你僅僅通過(guò)主鍵查找數(shù)據(jù)，clustered索引將極大地提高性能,。標(biāo)準(zhǔn)的MyISAM索引在一次對(duì)索引列的查詢中需要查找2次：先查找索引文件中的索引項(xiàng),，然后再去數(shù)據(jù)文件中查找。而clustered索引只需要查找一次,。
當(dāng)使用二級(jí)索引的時(shí)候,，clustered索引可能沒(méi)那么快。 比如： SELECT * FROM mytable1 WHERE phone='123'; 主鍵是name,，phone上也有索引,，這時(shí)，使用的實(shí)際是phone的索引,，而沒(méi)有使用name索引,。
但是在某些情況下，不當(dāng)?shù)厥褂?/font>clustered索引反而會(huì)降低性能,。當(dāng)同時(shí)使用clustered索引和二級(jí)索引時(shí),，需要特別考慮對(duì)存儲(chǔ)的影響。二級(jí)索引指向的是主鍵而不是具體列,。如果對(duì)很大的值做索引并且同時(shí)有很多二級(jí)索引,，會(huì)造成有很多重復(fù)的主鍵的副本。當(dāng)clustered索引的值很小的時(shí)候,，這不會(huì)是問(wèn)題,，但當(dāng)值很大時(shí)，這會(huì)造成很大存儲(chǔ)問(wèn)題,。
也就是說(shuō),，在InnoDB，Oracle中,，不要對(duì)擁有很大值的字段做索引,，即使有，也盡量不要使用二級(jí)索引,。這會(huì)非常浪費(fèi)存儲(chǔ)空間,。
在clustered索引中，對(duì)主鍵的修改的開銷比較大,。 在選擇主鍵的時(shí)候盡量選擇不會(huì)被修改,，且數(shù)值小的字段。
4.1.1.6.唯一索引（unique index）VS主鍵（primary key） 在myISAM,，這2個(gè)基本沒(méi)區(qū)別,，唯一的區(qū)別是主鍵不能含有NULL，主鍵就是定義了NOT NULL的唯一索引,。
InnoDB和BDB中的每個(gè)表都需要主鍵,。如果你不指定的話，它們會(huì)自己添加一個(gè)隱藏的主鍵,，這個(gè)主鍵為一個(gè)自增的數(shù)字,，和AUTO-INCREMENT字段相似。Heap引擎不需要主鍵,，但它會(huì)自己建立一個(gè),。實(shí)際上，你可以創(chuàng)建沒(méi)有任何索引的heap表,。
4.1.1.7.索引NULL值 在一般的數(shù)據(jù)庫(kù)中,，NULL值可以存在有索引的字段內(nèi)，但不能存在唯一索引中,。但MySQL允許在唯一索引中存NULL,。
4.2.索引結(jié)構(gòu) 4.2.1.B-Tree索引 B-Tree是最常見的索引結(jié)構(gòu),。
基于范圍的查詢?cè)?/font>B-Tree中速度很快，比如： SELECT * FROM phone_book WHERE last_name BETWEEN 'Marten' and 'Mason' 此外還包括min() max()等函數(shù) 還有 如下范圍查詢： SELECT COUNT(*) FROM phone_book WHERE last_name > 'Zawodny' MySQL只需要在B-Tree中找到Zawodny這個(gè)值,，然后僅僅數(shù)這個(gè)節(jié)點(diǎn)的后繼節(jié)點(diǎn)就可以了,。
4.2.2.Hash索引 hash索引使用hash表來(lái)存儲(chǔ)節(jié)點(diǎn)而不是一個(gè)平衡二叉樹。 Hash索引提供最快的key查找,，但是靈活性相比其它的索引類型要弱一些,。此時(shí)，基于范圍的查詢不能使用索引來(lái)提高速度,。 Hash索引對(duì)于文本（Text）和數(shù)值數(shù)據(jù)有比較好的效果,，因?yàn)樗苡行У販p少索引的尺寸。
4.2.3.R-Tree索引 R-Tree索引用在空間或者N維數(shù)據(jù)上,。這在地圖和地理學(xué)應(yīng)用上很有用,。當(dāng)然在基于坐標(biāo)或者維的查詢上很有用。 MySQL的R-Tree實(shí)現(xiàn)是基于OpenGIS的規(guī)范,，在www.opengis.org上可以找到,。
空間數(shù)據(jù)索引對(duì)于大多數(shù)人可能比較陌生。 創(chuàng)建一個(gè)空間數(shù)據(jù)表： mysql> create table map_test -> ( -> name varchar(100) not null primary key, -> loc geometry, -> spatial index(loc) -> );
4.3.索引和表類型 4.3.1.MyISAM表 MyISAM默認(rèn)的索引類型是B-Tree,。MyISAM還提供了兩種功能：前綴壓縮（prefix compression）和packed keys,。
前綴壓縮（prefix compression）將提取出字符中公共前綴。比如一個(gè)字段存儲(chǔ)了URL,，顯然存儲(chǔ)重復(fù)的前綴“http://”回使B-Tree的每個(gè)節(jié)點(diǎn)大大增加,。 Packed keys是前綴壓縮對(duì)于整數(shù)的的最好方法。數(shù)值的高位總是先被存儲(chǔ),，對(duì)于大規(guī)模的數(shù)字key,，最高位是很少變動(dòng)的。
要啟用packed keys,，需要在CREATE TABLE里面設(shè)置PACKED_KEY=1,。
4.3.1.1.Delayed key writes 有DELAY_KEY_WRITE屬性的表。對(duì)于索引的寫入,，并不是馬上寫入磁盤,，而是先寫入內(nèi)存緩存，當(dāng)此內(nèi)容從緩存中除去或者表被關(guān)閉的時(shí)候,，鍵內(nèi)容才被寫入磁盤,。這對(duì)負(fù)載很重且經(jīng)常被修改的表有很好的性能提升。
4.3.2.Heap表 將B-Tree和Heap表接合,，速度將很完美,。
4.3.3.BDB表 MySQL的BDB只提供B-Tree索引。 BDB的索引和MyISAM一樣提供前綴壓縮,。和InnoDB一樣,，BDB也提供 clustered索引,。BDB需要主鍵，如果你沒(méi)有指定,，系統(tǒng)會(huì)創(chuàng)建一個(gè)隱藏的,。這是因?yàn)?/font>BDB通過(guò)主鍵找到行。索引總是指向主鍵,，而不是指向行的物理位置,。這說(shuō)明,，按照二級(jí)索引查找比按照主鍵索引查找要慢,。
4.3.4.InnoDB表 InnoDB提供B-Tree索引。但沒(méi)有提供前綴壓縮和打包（packing）,。InnoDB需要主鍵,，如果你不指定，系統(tǒng)將自動(dòng)指定一個(gè)64位的值,。 索引文件存儲(chǔ)在InnoDB的表空間里面,。InnoDB使用clustered索引。即是說(shuō),，主鍵的值直接影響行和索引節(jié)點(diǎn)的物理位置,。所以，InnoDB中基于主鍵的查詢會(huì)非?？?/strong>,。當(dāng)記錄被找到的時(shí)候，也同時(shí)被緩存了到緩沖池里面了,。
4.3.5.全文索引 MyISAM支持全文索引,。全文索引能在文本字段（VARCHAR TEXT等）上做。全文索引是存儲(chǔ)在MYI文件中,。其具體實(shí)現(xiàn)是創(chuàng)建一個(gè)有2部分的B-Tree索引,，第一部分是varchar,，第二部分是float,。varchar存儲(chǔ)被索引的字符,，float存儲(chǔ)在其在行中的權(quán)重,。
全文索引會(huì)索引行中的每個(gè)單詞,，由于MySQL的B-Tree很高效,，全文索引帶來(lái)的性能提升絕對(duì)值得造成的存儲(chǔ)開銷,。
比如： select * from articles where body='%database%' 下面的語(yǔ)句會(huì)快很多很多,。 select * from articles (body) march against ('database')
4.3.6.索引限制 全文索引只是對(duì)單詞（英文是單詞,，中文是單字）做索引,。比如，搜索中間有“or”的單詞,，如word,lord,，用全文索引是不行的,。
4.3.6.2.正則表達(dá)式 比如： select last_name from phone_book where last_name rlike "(son|ith)$" rlike是MySQL中正則匹配的語(yǔ)句。 但這會(huì)很慢,。 MySQL的SQL優(yōu)化器不會(huì)優(yōu)化基于正則表達(dá)式的查詢,。
4.4.索引維護(hù) 4.4.1.獲得索引信息 獲得表的創(chuàng)建信息：SHOW CREATE TABLE mytable1; 獲得表的索引信息：SHOW INDEXES FROM mytable1;
4.4.2.刷新索引數(shù)據(jù) 整理碎片，優(yōu)化查詢 ：OPTIMIZE TABLE mytable1; MySQL將重新創(chuàng)建索引,。 當(dāng)數(shù)據(jù)庫(kù)下線時(shí),，可以使用命令行工具：myisamchk
InnoDB和 BDB不是很需要這樣的操作。
5.查詢性能 5.1.查詢處理基礎(chǔ) 5.1.1.查詢緩存 自MySQL4.0.1之后你可以在my.cnf中設(shè)置： query_cache_type = 1 開啟查詢緩存 query_cache_type= 2 按需模式
MySQL是將查詢hash處理后再存到緩存中的,。
基于這樣原因,，SELECT * FROM table1和select * from table1是不同的緩存項(xiàng)。MySQL不會(huì)修飾開始和結(jié)尾的空格,，所以對(duì)于的空格也會(huì)造成緩存項(xiàng)不同,。
MySQL只會(huì)對(duì)SELECT做緩存。它通過(guò)判斷SQL語(yǔ)句的前3個(gè)字母是否為sel來(lái)判斷是否為select查詢,。所以,，對(duì)于這樣的語(yǔ)句： /* This is a select */ select * from mytable1 這個(gè)語(yǔ)句加上注釋后，在執(zhí)行的時(shí)候,，可以通過(guò)show processlist命令來(lái)方便查看,。 直到MySQL5.0，都不能判斷出是一個(gè)select查詢,，導(dǎo)致不能進(jìn)行緩存,。
有時(shí)候我們不希望使用緩存?？梢栽?/font>sql語(yǔ)句里面加入SQL_NO_CACHE就可以,。如： select sql_no_cache * from mytable1 這對(duì)防止緩存污染很有用。 另,，有的查詢不需要緩存（如,，某查詢一天只會(huì)執(zhí)行一次），可以這樣來(lái)禁止MySQL緩存它,，以便為其它查詢留空間,。
當(dāng)my.cnf中的query_cache_type=2時(shí)候，只有在sql中加入SQL_CACHE時(shí)才啟用緩存,。 如： select sql_cache * from mytable1
5.1.2.解析,、分析 和 優(yōu)化 5.1.3.使用 EXPLAIN 首先我們可以用DESC命令查看表結(jié)構(gòu)：desc mytable1; 我們可以在sql語(yǔ)句前加上explain來(lái)查看語(yǔ)句的執(zhí)行情況，如： explain select * from mytable1 where name='tml';
5.1.4.執(zhí)行
5.2.優(yōu)化器的特色和怪脾氣 測(cè)試查詢時(shí),，一定記得使用真實(shí)數(shù)據(jù),。
MySQL會(huì)自己判斷是否使用查詢，當(dāng)需要遍歷用where表示的某個(gè)數(shù)量的數(shù)據(jù)時(shí)候，MySQL會(huì)不使用索引,。因?yàn)樽x取連續(xù)的數(shù)據(jù)比按索引一個(gè)一個(gè)讀要快,。
5.2.2.基于索引的排序 MySQL在一個(gè)表的一個(gè)查詢上只會(huì)使用一個(gè)索引。 對(duì)于含有order by的查詢,，若沒(méi)有使用到索引,，可以給排序字段添加b-tree索引來(lái)提速。 對(duì)于已經(jīng)有索引的情況,，則將排序字段添加到已有索引中去,。
如： SELECT * FROM weather WHERE city = 'Toledo' ORDER BY the_date DESC 可以這樣添加索引： ALTER TABLE weather DROP INDEX city, ADD INDEX (city, the_date) 注意：上面刪除了原有的(city)索引。 注意,，alter中字段的順序很重要,。根據(jù)最左前綴原則(leftmost prefix rule)。 我的理解,，：索引的選擇按照按照最左前綴,。如,，上面的查詢,，發(fā)現(xiàn)city時(shí)候，和索引(city,the_date)的最左前綴“city”匹配,，則使用,。又如，select * from where the_data='2008-01-01'就不能使用(city,the_city)索引,。
5.2.3.無(wú)效查詢
5.2.4.用全文檢索替代LIKE 目前的MySQL的查詢優(yōu)化器對(duì)于全文檢索的處理還不夠聰明,。當(dāng)有一個(gè)可用的全文索引時(shí)，它會(huì)使用它,，而不考慮索引能真正消除多少無(wú)關(guān)行,。
5.3.識(shí)別慢查詢 要找到慢查詢，可以啟用慢查詢?nèi)罩荆?/font>slow query log）,。該日志記錄的僅僅是耗時(shí)較長(zhǎng)的查詢,，而不一定是效率低的。
造成查詢有時(shí)緩慢的原因： 1,、表被鎖了,，日志中的 Lock_time顯示了查詢等待鎖釋放的時(shí)間。 2,、數(shù)據(jù)或索引沒(méi)有被緩存進(jìn)內(nèi)存,。這在MySQL剛啟動(dòng)的時(shí)候比較常見。 3,、備份進(jìn)程正在運(yùn)行 4,、CPU負(fù)載很大
可以使用mysql自帶的perl腳本mysqldumpslow去整理slow log。
5.4.用hint影響mysql mysql的hint一般出現(xiàn)在select語(yǔ)句的右邊,，如： SELECT SQL_CACHE * FROM mytable;
當(dāng)為多dbms寫查詢的時(shí)候,，可以用注釋語(yǔ)句來(lái)自動(dòng)屏蔽掉mysql特有的語(yǔ)句,，如： SELECT /*! SQL_CACHE */ * FROM mytable;
5.4.1.join的順序 如果你覺得mysql對(duì)join的順序處理不好，可以使用STRAIGHT_JOIN代替JOIN或者“.”,，如： SELECT * FROM table1 STRAIGHT_JOIN table2 ON .....; 這樣就強(qiáng)迫mysql按照表在查詢中出現(xiàn)的順序來(lái)使用表,。
5.4.2.索引使用 在表名后面使用USE INDEX(索引列表)讓mysql使用查詢，如： SELECT * FROM mytable USE INDEX(mod_time,name) ...;
如果你想讓mysql不使用查詢,，使用IGNORE INDEX(索引列表) ...
5.4.3.結(jié)果大小 當(dāng)結(jié)果很大的時(shí)候,，SQL_BUFFER_RESULT告訴mysql將結(jié)果存儲(chǔ)到一個(gè)臨時(shí)表里面去，這樣能更快地釋放鎖,。 SQL_BIG_RESULT告訴mysql結(jié)果集將會(huì)很大,，讓mysql在做決定的時(shí)候會(huì)更多地考慮基于磁盤的臨時(shí)表。這樣也不大可能為了排序而在臨時(shí)表上建立索引,。
5.4.4.查詢緩存 SQL_CACHE讓mysql使用緩存,。SQL_NO_CACHE讓mysql不使用緩存。 如果在配置中將query_cache_type設(shè)置為1,就是主動(dòng)模式,，則所有沒(méi)有指定SQL_NO_CACHE的查詢都默認(rèn)使用緩存,。 如果將query_cache_type設(shè)置為2，即是按需模式,，只有使用了SQL_CACHE的查詢才會(huì)使用緩存,。
5.5.愚蠢的查詢策略 5.5.1.兩個(gè)比一個(gè)好
|
|
|
