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