MySQL

MySQL組成

通常意義上來講，MySQL是由三部分組成：

1. MySQL client - 我們使用的mysql工具。在此工具中連接server，輸入sql語句，輸出查詢結果。

2. MySQL server - MySQL服務器端，用于編譯sql語句 ，優(yōu)化sql，生成執(zhí)行計劃，執(zhí)行，使用CPU在內存中對結果進行計算（比如排序），對結果進行緩存，最終將結果傳輸給客戶端。

3. 數據存儲引擎 - MySQL服務器是一個計算器，數據庫真正的數據是存儲于存儲引擎中。MySQL服務器使用存儲引擎存儲，掃描，獲取數據。常用的存儲引擎有InnoDB, MyISAM, Memory。

借助《高性能MySQL》書中的一張圖片，展示MySQL查詢的工作流程：

存儲引擎

最常用的存儲引擎為InnoDB，MyISAM和Memory。還有其它引擎以及第三方提供的引擎。選擇合適的。

90%的案例應該使用InnoDB。除非你需要特殊的功能而InnoDB無法滿足。InnoDB是一個支持事務，行級鎖，崩潰后可自行恢復的存儲引擎。

MyISAM是MySQL最早使用的存儲引擎，它不支持事務，使用表級鎖，崩潰無法恢復。因為MyISAM可以壓縮表，所以讀取速度快。

Memory引擎要求數據都存放于內存中，只有表結構存放于硬盤，一旦重啟，數據清空。它支持hash index，所以它的取速度極快。但它不支持Blob和Text。并且它要求column類型必須是定長，varchar會自動轉為char。它也只支持表鎖，所以并發(fā)寫性能不好。

InnoDB存儲結構

InnoDB數據存儲結構由大到小分Tablespace, segment, extend, page(block), row。借用網上一張圖片：

每一個table對應一個tablespace，在tablespace中，index，數據，trasaction信息又各自存放于不同的segment中。其中數據比較特殊，innoDB的數據是跟cluster index一起的，cluster index會在后面詳述。

每一個segment又分64個連續(xù)page，每個page是固定大小16K。存儲引擎是按葉讀取的，它會把需要的內容的所在頁一并load到內存中。一個page上可以存儲的是index，也可以是row，還可以是其它信息。

從開發(fā)者角度看

編寫SQL

會設計Schema，編寫SQL語句，是一個后臺程序員必須的技能。 create database, create table, create index,  create trigger, create event, grant, alter, insert, select, update, delete, join, union，subquery , 這些都是必須掌握的基礎。

其中最復雜的部分還是index，join，union與subquery，這經常牽扯到查詢性能。

事務

無論使用任何事務型數據庫都應該了解事物的4大隔離級別： Serializable, Repeatible read, read commited, read uncommited.

事物的隔離是依靠鎖來實現的。MySQL支持讀鎖，寫鎖，MVCC。MVCC稱為多版本并發(fā)控制，使用快照和時間戳來維護同一時間一條數據所產生的多個版本。不同的事務可以讀取不同的快照。

Seriablizable隔離級別需要MySQL嚴格使用讀寫鎖，而后面三種級別則不使用讀鎖，而是用寫鎖+MVCC來實現。

臨時表和內存表

臨時表存在兩類：

1. mysql內部臨時表。這種表是由系統產生，用于保存中間結果的臨時表。這種表會使用memory存儲引擎，但如果中間結果太多，超出限制，則會轉為MyISAM存儲。如果表中含有text，blob，也會轉為MyISAM。

2. 用戶使用create temporary table創(chuàng)建的臨時表，這種表可以使用任何存儲引擎。且在用戶連接斷開的時候銷毀。

以上兩類臨時表不要混淆。

用戶還會使用Momery存儲引擎創(chuàng)建內存表。內存表支持hash index，隨機訪問的速度占優(yōu)，適合做緩存。內存表在服務器重啟以后，數據清空。內存表不支持blob和text類型。

create table table_name (definition) engine=memory;

索引index

常用的MySQL存儲引擎支持B+樹索引和Hash索引。InnoDB和MyISAM支持B+樹索引，而Memory引擎支持B+樹和Hash索引。在創(chuàng)建index的時候，可以聲明使用哪種算法：

create index ix1 on table_name(columns) using {BTREE|HASH}

B+樹的算法建議大家還是去稍微了解一下，明白其原理。

如果羅列MySQL innoDB的索引種類的話，我會將其分為如下：

1. Primary key， 系統會自動將primary key創(chuàng)建為cluster index（聚簇索引)。

3. 唯一索引 Unique index

4. 二級索引 Secondary index

5. 組合索引或多列索引

6. 覆蓋索引

7. 聚簇索引 cluster index

這有些混亂，讓我們一個一個的看。

聚簇索引

首先看聚簇索引，它和其它索引不一樣，聚簇索引本身就是一種數據存儲方式。聚簇索引和數據在物理磁盤上是存放在一起，索引的葉子節(jié)點本身就是數據。這使得插入的數據會按照聚簇索引的順序排列。一張表只能有一個聚簇索引。InnoDB會使用primary key做聚簇索引，如果沒有primary key，則選擇一個unique index做聚簇索引，如果沒有unique index, 則自動生成row id，使用row id做聚簇索引。

如果primary key是連續(xù)增長的整數，那么插入的效率會高些，因為數據存放的物理位置永遠是一個最末端的page。但寫都集中在最末端的page，鎖的激烈競爭導致寫的并發(fā)性下降。

如果primary key是一個非連續(xù)的uuid的話，那么插入會導致隨機IO，并可能會發(fā)生分裂page，產生碎片。在并發(fā)情況下，寫的page比較分散，不會導致鎖的激烈競爭。

由于聚簇索引的數據是按照索引在物理位置上排列的，創(chuàng)建好的聚簇索引，可以使相關的數據在物理上存放在一起，在查詢的時候，連續(xù)幾個page的讀取便能獲取所有相關的數據。比如在論壇里面，一個topic頁面總是伴隨著所有的評論。

二級索引

二級索引與聚簇索引相對。二級索引的葉子節(jié)點指向的并不是數據地址，而是數據所對應的聚簇索引的key。所以一個二級索引，默認的包含了primary key在最后面。使用二級索引查找數據，需要訪問兩次索引。

使用二級索引查找數據，首先從二級索引中得到葉子節(jié)點所指向的key，然后再通過聚簇索引找到key指向的數據。

組合索引和覆蓋索引

組合索引和覆蓋索引在語法上沒有任何區(qū)別，它們都是在一個索引中包含了多個列。它們只是在使用意義上有所不同。

組合索引是為了where右邊的條件所做的優(yōu)化，可以快速的定位符合條件的數據。 組合索引必須從索引的左邊開始計算，如果左邊缺失，或者左邊是一個range，那么右邊全部失效 。（這句寫的實在是爛，但懂得人自然懂。）

覆蓋索引是為了消除回表掃描而將索引范圍擴大到查詢語句中所有涉及到的列。這樣，查詢語句中所有用到的列均可以在覆蓋索引中得到，而不用再到表中獲取。

優(yōu)化查詢性能

1. 使用緩存。像Hibernate，MyBatis都支持二級緩存。MySQL也支持查詢緩存。

2. 創(chuàng)建合適的index。這是最常用的方法。

4. 使用explain，查看執(zhí)行計劃，了解MySQL的執(zhí)行計劃可以幫助我們調整index，SQL結構來優(yōu)化查詢效率。

5. 盡量定義長度短的列，沒用的列不要出現在select中。size太大的列占用內存和傳輸帶寬。SQL在執(zhí)行過程中，會將中間結果存放在內存臨時表中，如果中間結果占用的內存超過限制，會把臨時表轉存到硬盤上，操作效率會變低。

6. 使用not null，用特殊值替代null。允許null值，會使得column后面需要額外的空間來控制是否為null。此外，null值無法使用索引。

7. 盡量避免子查詢。MySQL對子查詢的優(yōu)化非常糟?？梢允褂肑oin來代替子查詢。

8. in（）比 or 的速度快，MySQL會對in中的值先排序，再訪問，這樣可以順序IO訪問。

9. MySQL不會將外層條件推入Union中，在使用union時，可以在union里面使用充足的條件，來減小結果集。

10. 對大偏移量的分頁，可以使用延遲關聯，避免對表中大量數據的掃描。

對以上的最后一點給一個例子：

create table test (
  id int auto_increment,
  name varchar(20) not null,
  description varchar(255) not null,
  primary key(id)
);
create index ix on test(name);

select * from test order by name limit 10000, 20;

MySQL優(yōu)化器并沒有聰明到像人腦，上面的查詢會先將test按照name排序，從表中取出前10020名的數據，然后丟掉前10000名，返回后20名。因為description并不在ix的index中，所以需要回表掃面。使用延遲關聯可以優(yōu)化查詢：

select t.* from test t
  inner join (
    select id from test order by name limit 10000, 20
  ) as s on t.id=s.id;

上面的子查詢會先執(zhí)行，子查詢中使用二級索引ix，ix中包含name和id，所以不需要掃描原表，只需要再index上找出排名10000到10020之間的name的id。然后在使用這20個id與原表去join，只需要回表掃描20條數據就足夠了。

Explain

Explain是一個后端開發(fā)人員最常用的查詢優(yōu)化工具。Explain可以給出MySQL優(yōu)化器優(yōu)化過后的執(zhí)行計劃，通過執(zhí)行計劃我們可以了解一條SQL在數據庫服務器中是如何被執(zhí)行的。MySQL中的多表查詢都是使用NLP（嵌套循環(huán)查詢），所以每一層嵌套，均是對一個table的查詢。當使用explain工具時，MySQL會列出一張表，表中每一行均是對一個table的訪問計劃。而嵌套的順序則可以通過id，select_type以及table這三列判斷出來。

除去前三列,后面幾個列則關系到查詢的性能:

案例分析

以上面的兩條limit查詢語句為例。一條是未優(yōu)化前的語句：

執(zhí)行計劃給出，查詢的類型是ALL，會做全表掃描，然后使用排序。

而改進后的sql如下：

解釋一下這段執(zhí)行計劃。最外層兩個相互做join的表，一個為<derived2>,另一個為t。MySQL都是使用嵌套循環(huán)查詢（NLP），<derived2>在前面，則<derived2>為循環(huán)最外層。

<derived2>是一張派生表，這張派生表是有id=2的子查詢產生的，通過名字<derived2>可以判斷的出。id=2的查詢便是

#id=2
select id from test order by name limit 10000, 20；

這段子查詢沒有使用全表掃描，完全使用index掃描，索引ix上存有name和id的值，所以可以快速的拿到按照name順序排列的id，而不用filesort。子查詢會只返回20條數據。

執(zhí)行計劃中第一行對<derived2>中rows的描述不正確，這是MySQL的一個不足。<derived2>的rows數量應該為20.

使用NLP，最外層只需循環(huán)為20次，內層根據外層傳下來的id，獲取t中的數據行，可以通過primary key index快速定位，然后回表查詢。

優(yōu)化后的sql只需要回表查詢20次即可。對比優(yōu)化前的回表查詢上萬次，速度會有很多提升。

使用以下命令，可以得到MySQL優(yōu)化引擎所產生的執(zhí)行偽代碼：

mysql> explain extended select...
mysql> show warnings;

warnings中會顯示偽代碼，上面優(yōu)化后的查詢偽代碼是：

/* select#1 */ 
select `test`.`t`.`id` AS `id`,`test`.`t`.`name` AS `name`,`test`.`t`.`description` AS `description` from `test`.`test` `t` 
        join (/* select#2 */ select `test`.`test`.`id` AS `id` from `test`.`test` order by `test`.`test`.`name` limit 10000,20) `s` 
        where (`test`.`t`.`id` = `s`.`id`)

Trigger

Trigger是一個很常見的功能,眾多數據庫都支持trigger. MySQL的trigger有幾個地方需要注意.

Trigger語法如下:

mysql>delimiter |
mysql>create trigger trigger_name before insert on table_name for each row
     >  begin
     >     <trigger body>
     >  end|
mysql>delimiter ;

在trigger body中,既可以使用sql語句,也可以調用stored procedure, 還可以調用MySQL的UDF. 由于MySQL的UDF可以做任何事情, 這使得trigger可以調用MySQL以外的系統資源.

在MySQL主從復制的環(huán)境中,trigger的工作方式需要注意. Trigger在主從節(jié)點中均存在. 如果replication是statement based, 主服務器的變動出發(fā)了trigger, 從服務器也會觸發(fā)trigger. 如果replication是row based, 則從服務器不會觸發(fā)trigger. 但主服務器trigger更新的內容被被同步到從服務器.

一個面試題

我曾被人問及, 如果數據庫中的數據發(fā)生了變化, 如何能實時的通知應用服務器. Oracle的JDBC中有一個feature,叫做Database Change Notification. 但MySQL卻并沒有這種功能. MySQL可以使用trigger來做到這一點.

第三方提供了很多MySQL的UDF, 比如mysql-udf-http, 可以直接在sql語句中調用其提供的方法,如 get, post等. 我們也可以自己開發(fā)UDF. 如果使用mysql-udf-http, 當trigger被觸發(fā)時,我們可以調用post方法,向應用服務器發(fā)送一條更新記錄. HTTP性能不好,還可以使用其它的udf,使mysql和應用服務器之間的逆向通信更快速.

Event

Event提供了scheduler的功能. MySQL可以創(chuàng)建一個一次性固定時間點的scheduler, 也可以創(chuàng)建一個周期性的scheduler. 周期可以是自己定義的, 也可以是MySQL提供的Hour, Day, Week, Month, Year等等等等.

event的語法類似于

create event e_name
  on schedule every 1 day
  do
    begin
      body
    end;

body中既可以是簡單的sql,也可以是復雜的stored procudure.

分區(qū)

分區(qū)是一個非常重要的優(yōu)化方法. MySQL只支持水平分區(qū),不支持垂直分區(qū). 為什么分區(qū)? 當一個表里的數據量變得無比龐大的時候, index B-Tree的深度會過深, 導致讀寫的性能變的都非常差. 并且過多的數據存放在一個storage上, 過度的并發(fā)也使得storage成為瓶頸. 如果使用分區(qū), 把相關的數據分在一個區(qū), 則可以客服上面的困難.

分區(qū)和分片不同,分片是向外擴展,而分區(qū)是向內擴展. 而分區(qū)和分表相類似, 分片和分庫相類似. 分片會在后續(xù)詳述.

舉一個例子, 大眾點評網上的店家數據, 可以按照城市進行分區(qū). 因為點評上還從沒見過跨城市綜合查詢或排序的. 再一個例子,比如logEvents, 我們可以按照時間段進行分區(qū).

MySQL分區(qū)有4中方式:

1. by range - 根據某一個字段的取值范圍進行分區(qū), 比如 createDate, 每年分為一個區(qū).

2. by list - 跟range類似, 根據某一個字段的取值list進行分區(qū), 比如city in ('shanghai', 'hangzhou', 'nanjing')為一個長三角分區(qū), city in ('beijing', 'tianjing', 'tanshan')為一個京津唐分區(qū).

3. by hash - 根據某一個字段的hash值進行分區(qū), 這種分區(qū)方法可以使數據分布比較均勻.

4. by key - 跟hash類似,根據某個地段進行分區(qū). 此字段會被MySQL默認算法進行hash.

range分區(qū)方法還可以接受多個column進行分段.

range和list分區(qū)計算的時候只接受整形, 但如果在分區(qū)的時候, 聲明columns, 它們則可以支持string, date, datetime.

每個range和list分區(qū),還支持子分區(qū), 子分區(qū)必須為hash或key分區(qū)方式.

每個分區(qū)就相當于一張表, 所有的分區(qū)合在一起則可以展現一張完整的表. 每個分區(qū)擁有自己的index和數據, index并不跨越所有分區(qū). 在創(chuàng)建分區(qū)的時候,用戶可以自己指定每個分區(qū)的數據和index所存儲的位置.

例子:

create table test (id int, created date)
 partition by range(YEAR(created)) (
   partition p0 values less than (2000)
     data directory = '/disk0/data'
     index directory = '/disk0/index',
   partition p1 less than MAXVALUE
     data directory = '/disk1/data'
     index directory = '/disk1/index'
  );

分區(qū)減輕了IO的并發(fā)負擔, 把大表分成小表, 優(yōu)化了查詢速度.

然而,當跨區(qū)排序的時候,則會是一種災難. 所以要避免大數據量的跨區(qū)排序.

主從讀寫分離

借網上一張圖,說明MySQL主從備份的實現方式. 

上文先寫到這里吧, 主從備份將在下文詳述.