MySQL

MySQL組成

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

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

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

3. 數(shù)據(jù)存儲(chǔ)引擎 - MySQL服務(wù)器是一個(gè)計(jì)算器，數(shù)據(jù)庫(kù)真正的數(shù)據(jù)是存儲(chǔ)于存儲(chǔ)引擎中,。MySQL服務(wù)器使用存儲(chǔ)引擎存儲(chǔ)，掃描,，獲取數(shù)據(jù),。常用的存儲(chǔ)引擎有InnoDB, MyISAM, Memory。

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

存儲(chǔ)引擎

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

90%的案例應(yīng)該使用InnoDB。除非你需要特殊的功能而InnoDB無法滿足,。InnoDB是一個(gè)支持事務(wù),，行級(jí)鎖，崩潰后可自行恢復(fù)的存儲(chǔ)引擎,。

MyISAM是MySQL最早使用的存儲(chǔ)引擎,，它不支持事務(wù)，使用表級(jí)鎖,，崩潰無法恢復(fù),。因?yàn)镸yISAM可以壓縮表,，所以讀取速度快。

Memory引擎要求數(shù)據(jù)都存放于內(nèi)存中,，只有表結(jié)構(gòu)存放于硬盤,，一旦重啟，數(shù)據(jù)清空,。它支持hash index,，所以它的取速度極快。但它不支持Blob和Text,。并且它要求column類型必須是定長(zhǎng),，varchar會(huì)自動(dòng)轉(zhuǎn)為char。它也只支持表鎖,，所以并發(fā)寫性能不好,。

InnoDB存儲(chǔ)結(jié)構(gòu)

InnoDB數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)由大到小分Tablespace, segment, extend, page(block), row。借用網(wǎng)上一張圖片：

每一個(gè)table對(duì)應(yīng)一個(gè)tablespace,，在tablespace中,，index，數(shù)據(jù),，trasaction信息又各自存放于不同的segment中,。其中數(shù)據(jù)比較特殊，innoDB的數(shù)據(jù)是跟cluster index一起的,，cluster index會(huì)在后面詳述,。

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

從開發(fā)者角度看

編寫SQL

會(huì)設(shè)計(jì)Schema,，編寫SQL語句,，是一個(gè)后臺(tái)程序員必須的技能。 create database, create table, create index,  create trigger, create event, grant, alter, insert, select, update, delete, join, union,，subquery , 這些都是必須掌握的基礎(chǔ),。

其中最復(fù)雜的部分還是index，join,，union與subquery,，這經(jīng)常牽扯到查詢性能。

事務(wù)

無論使用任何事務(wù)型數(shù)據(jù)庫(kù)都應(yīng)該了解事物的4大隔離級(jí)別： Serializable, Repeatible read, read commited, read uncommited.

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

Seriablizable隔離級(jí)別需要MySQL嚴(yán)格使用讀寫鎖,，而后面三種級(jí)別則不使用讀鎖,，而是用寫鎖+MVCC來實(shí)現(xiàn)。

臨時(shí)表和內(nèi)存表

臨時(shí)表存在兩類：

1. mysql內(nèi)部臨時(shí)表,。這種表是由系統(tǒng)產(chǎn)生,，用于保存中間結(jié)果的臨時(shí)表。這種表會(huì)使用memory存儲(chǔ)引擎,，但如果中間結(jié)果太多,，超出限制，則會(huì)轉(zhuǎn)為MyISAM存儲(chǔ),。如果表中含有text,，blob，也會(huì)轉(zhuǎn)為MyISAM,。

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

以上兩類臨時(shí)表不要混淆,。

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

create table table_name (definition) engine=memory;

索引index

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

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

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

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

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

3. 唯一索引 Unique index

4. 二級(jí)索引 Secondary index

5. 組合索引或多列索引

6. 覆蓋索引

7. 聚簇索引 cluster index

這有些混亂,，讓我們一個(gè)一個(gè)的看,。

聚簇索引

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

如果primary key是連續(xù)增長(zhǎng)的整數(shù)，那么插入的效率會(huì)高些,，因?yàn)閿?shù)據(jù)存放的物理位置永遠(yuǎn)是一個(gè)最末端的page,。但寫都集中在最末端的page，鎖的激烈競(jìng)爭(zhēng)導(dǎo)致寫的并發(fā)性下降,。

如果primary key是一個(gè)非連續(xù)的uuid的話,，那么插入會(huì)導(dǎo)致隨機(jī)IO，并可能會(huì)發(fā)生分裂page,，產(chǎn)生碎片,。在并發(fā)情況下，寫的page比較分散,，不會(huì)導(dǎo)致鎖的激烈競(jìng)爭(zhēng),。

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

二級(jí)索引

二級(jí)索引與聚簇索引相對(duì),。二級(jí)索引的葉子節(jié)點(diǎn)指向的并不是數(shù)據(jù)地址,，而是數(shù)據(jù)所對(duì)應(yīng)的聚簇索引的key。所以一個(gè)二級(jí)索引,，默認(rèn)的包含了primary key在最后面,。使用二級(jí)索引查找數(shù)據(jù)，需要訪問兩次索引。

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

組合索引和覆蓋索引

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

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

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

優(yōu)化查詢性能

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

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

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

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

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

7. 盡量避免子查詢,。MySQL對(duì)子查詢的優(yōu)化非常糟,。可以使用Join來代替子查詢,。

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

9. MySQL不會(huì)將外層條件推入U(xiǎn)nion中，在使用union時(shí),，可以在union里面使用充足的條件,，來減小結(jié)果集。

10. 對(duì)大偏移量的分頁,，可以使用延遲關(guān)聯(lián),，避免對(duì)表中大量數(shù)據(jù)的掃描。

對(duì)以上的最后一點(diǎn)給一個(gè)例子：

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)化器并沒有聰明到像人腦,，上面的查詢會(huì)先將test按照name排序,，從表中取出前10020名的數(shù)據(jù)，然后丟掉前10000名,，返回后20名,。因?yàn)閐escription并不在ix的index中，所以需要回表掃面,。使用延遲關(guān)聯(lián)可以優(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;

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

Explain

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

除去前三列,后面幾個(gè)列則關(guān)系到查詢的性能:

案例分析

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

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

而改進(jìn)后的sql如下：

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

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

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

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

執(zhí)行計(jì)劃中第一行對(duì)<derived2>中rows的描述不正確,，這是MySQL的一個(gè)不足,。<derived2>的rows數(shù)量應(yīng)該為20.

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

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

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

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

warnings中會(huì)顯示偽代碼,，上面優(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是一個(gè)很常見的功能,眾多數(shù)據(jù)庫(kù)都支持trigger. MySQL的trigger有幾個(gè)地方需要注意.

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語句,也可以調(diào)用stored procedure, 還可以調(diào)用MySQL的UDF. 由于MySQL的UDF可以做任何事情, 這使得trigger可以調(diào)用MySQL以外的系統(tǒng)資源.

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

一個(gè)面試題

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

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

Event

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

event的語法類似于

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

body中既可以是簡(jiǎn)單的sql,也可以是復(fù)雜的stored procudure.

分區(qū)

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

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

舉一個(gè)例子, 大眾點(diǎn)評(píng)網(wǎng)上的店家數(shù)據(jù), 可以按照城市進(jìn)行分區(qū). 因?yàn)辄c(diǎn)評(píng)上還從沒見過跨城市綜合查詢或排序的. 再一個(gè)例子,比如logEvents, 我們可以按照時(shí)間段進(jìn)行分區(qū).

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

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

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

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

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

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

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

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

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

例子:

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ā)負(fù)擔(dān), 把大表分成小表, 優(yōu)化了查詢速度.

然而,當(dāng)跨區(qū)排序的時(shí)候,則會(huì)是一種災(zāi)難. 所以要避免大數(shù)據(jù)量的跨區(qū)排序.

主從讀寫分離

借網(wǎng)上一張圖,說明MySQL主從備份的實(shí)現(xiàn)方式. 

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