MySQL高階學習筆記(二)

一、MySQL邏輯架構簡介

1.1 連線層

最上層是一些客戶端和連線服務，包含本地sock通訊和大多數基於客戶端/服務端工具實現的類似於tcp/ip的通訊。主要完成一些類似於連線處理、授權認證、及相關的安全方案。在該層上引入了執行緒池的概念，為通過認證安全接入的客戶端提供執行緒。同樣在該層上可以實現基於SSL的安全連結。伺服器也會為安全接入的每個客戶端驗證它所具有的操作許可權。

1.2 服務層

Management Serveices & Utilities：

系統管理和控制工具  

SQL Interface: SQL介面

接受使用者的SQL命令，並且返回使用者需要查詢的結果。比如select from就是呼叫SQL Interface

Parser: 解析器

SQL命令傳遞到解析器的時候會被解析器驗證和解析。 

Optimizer: 查詢優化器。

SQL語句在查詢之前會使用查詢優化器對查詢進行優化。 
用一個例子就可以理解： select uid,name from user where  gender= 1;
優化器來決定先投影還是先過濾。

Cache和Buffer： 查詢快取。

如果查詢快取有命中的查詢結果，查詢語句就可以直接去查詢快取中取資料。
這個快取機制是由一系列小快取組成的。比如表快取，記錄快取，key快取，許可權快取等
快取是負責讀，緩衝負責寫。

1.3 引擎層

儲存引擎層，儲存引擎真正的負責了MySQL中資料的儲存和提取，伺服器通過API與儲存引擎進行通訊。不同的儲存引擎具有的功能不同，這樣我們可以根據自己的實際需要進行選取。後面介紹MyISAM和InnoDB

1.4 儲存層

資料儲存層，主要是將資料儲存在執行於裸裝置的檔案系統之上，並完成與儲存引擎的互動。

二、查詢流程

mysql客戶端通過協議與mysql伺服器建連線,傳送查詢語句,先檢查查詢快取,如果命中(一模一樣的sql才能命中),直接返回結果,否則進行語句解析,也就是說,在解析查詢之前,伺服器會先訪問查詢快取--它儲存select語句以及相應的查詢結果集,如果某個查詢結果已經位於快取中,伺服器就不會再對查詢進行解析優化執行。它僅僅將快取中的結果返回給使用者即可,這將大大提高系統的效能。
語法解析器和預處理:首先mysql通過關鍵字將SQL語句進行解析,並生成一顆對應的'解析樹'。mysql解析器將使用mysql語法規則驗證和解析查詢;前處理器根據一些mysql規則進一步檢查解析樹是否合法。
查詢優化器:當解析樹被認為是合法的了,並且由優化器將其轉化成執行計劃。一條查詢可以有很多種執行方式,最後都返回了相同的結果。優化器的作用就是找到最好的執行計劃。

三、Mysql儲存引擎

3.1 檢視命令

mysql> show engines;
mysql> show variables like '%storage_engine%';

3.2各個引擎簡介

InnoDB儲存引擎

InnoDB是MySQL的預設事務型引擎，它被設計用來處理大量的短期(short-lived)事務。除非有非常特別的原因需要使用其他的儲存引擎，否則應該優先考慮InnoDB引擎。行級鎖，適合高併發情況

MyISAM儲存引擎

MyISAM提供了大量的特性，包括全文索引、壓縮、空間函式(GIS)等，但MyISAM不支援事務和行級鎖(myisam改表時會將整個表全鎖住)，有一個毫無疑問的缺陷就是崩潰後無法安全恢復。

Archive引擎

Archive儲存引擎只支援INSERT和SELECT操作，在MySQL5.1之前不支援索引。
Archive表適合日誌和資料採集類應用。適合低訪問量大資料等情況。
根據英文的測試結論來看，Archive表比MyISAM表要小大約75%，比支援事務處理的InnoDB表小大約83%。

Blackhole引擎

Blackhole引擎沒有實現任何儲存機制，它會丟棄所有插入的資料，不做任何儲存。但伺服器會記錄Blackhole表的日誌，所以可以用於複製資料到備庫，或者簡單地記錄到日誌。但這種應用方式會碰到很多問題，因此並不推薦。

CSV引擎

CSV引擎可以將普通的CSV檔案作為MySQL的表來處理，但不支援索引。
CSV引擎可以作為一種資料交換的機制，非常有用。
CSV儲存的資料直接可以在作業系統裡，用文字編輯器，或者excel讀取。

Memory引擎

如果需要快速地訪問資料，並且這些資料不會被修改，重啟以後丟失也沒有關係，那麼使用Memory表是非常有用。Memory表至少比MyISAM表要快一個數量級。(使用專業的記憶體資料庫更快，如redis)

Federated引擎

Federated引擎是訪問其他MySQL伺服器的一個代理，儘管該引擎看起來提供了一種很好的跨伺服器的靈活性，但也經常帶來問題，因此預設是禁用的。

3.3 MyISAM和InnoDB

MyISAM	InnoDB
不支援主外來鍵	支援主外來鍵
不支援事務	支援事務
支援表鎖	支援行鎖[適合高併發]
只快取索引	還快取真實資料
表空間小	表空間大
關注點是效能	關注點是事務

四、索引

4.1 機器執行SQL語句順序

4.2 索引是什麼

索引是一種資料結構

可以簡單理解為“排好序的快速查詢資料結構”。

索引的目的在於提高查詢效率,可以類比字典,如果要查'mysql'這個單詞,先定位到m字母,然後從上往下找到y字母,再找到剩下的sql;
如果沒有索引,那麼你可能需要從a--z進行查詢;
在資料之外,資料庫系統還維護著滿足特定查詢演算法的資料結構,這些資料結構以某種方式指向資料,這樣就可以在這些資料結構上實現高階查詢演算法。這種資料結構,就是索引。

索引本身也很大,不可能全部儲存在內容中,因為索引往往以索引檔案形式儲存在磁碟上;
我們平常說的索引,如果沒有特別指明,都是指B樹(多路搜尋樹,並不一定是二叉的)結構組織的索引。其中聚集索引、次要索引、複合索引、字首索引、唯一索引預設都是使用B+樹索引。統稱索引。當然,除了B+樹這種型別的索引之外,還有雜湊索引等。

4.3 索引優勢

1. 提高資料檢索的效率,降低資料庫的IO成本
2. 通過索引對資料進行排序,降低資料排序的成本,降低了CPU的消耗

4.4 索引劣勢

1. 索引也是一張表,也佔記憶體
2. 對資料進行更新時候,也需要維護索引

4.5 索引分類

 1. 單指索引
 2. 唯一索引
 3. 複合索引
 4. 基本語法

#建立索引
create [unique] index indexName on mytable(columnname);
alter mytable add [unique] index indexName on (columnname) 
#刪除索引
drop index indexName on mytable;
#檢視索引
show index from table_name\G

#有四種方式來新增資料表的索引
alter table table_name add primary key(column_name);#mysql會自動為主鍵欄位新增索引
alter table table_name add unique index_name(column_name);#建立唯一索引
alter table table_name add index index_name(column_name);#建立普通索引,索引值可出現多次
alter table table_name add fulltsxt index_name(column_name);

4.6 索引結構

索引是在MySQL的儲存引擎層中實現的，而不是在伺服器層實現的。所以每種儲存引擎的索引都不一定完全相同,也不是所有的儲存引擎都支援所有的索引型別的。

1. BTree索引
   

   【初始化介紹】 
   一顆b樹，淺藍色的塊我們稱之為一個磁碟塊，可以看到每個磁碟塊包含幾個資料項（深藍色所示）和指標（黃色所示），如磁碟塊1包含資料項17和35，包含指標P1、P2、P3，P1表示小於17的磁碟塊，P2表示在17和35之間的磁碟塊，P3表示大於35的磁碟塊。真實的資料存在於葉子節點即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
   非葉子節點不儲存真實的資料，只儲存指引搜尋方向的資料項，如17、35並不真實存在於資料表中。
   
    【查詢過程】
   如果要查詢資料項29，那麼首先會把磁碟塊1由磁碟載入到記憶體，此時發生一次IO，在記憶體中用二分查詢確定29在17和35之間，鎖定磁碟塊1的P2指標，記憶體時間因為非常短（相比磁碟的IO）可以忽略不計，通過磁碟塊1的P2指標的磁碟地址把磁碟塊3由磁碟載入到記憶體，發生第二次IO，29在26和30之間，鎖定磁碟塊3的P2指標，通過指標載入磁碟塊8到記憶體，發生第三次IO，同時記憶體中做二分查詢找到29，結束查詢，總計三次IO。
   
    真實的情況是，3層的b+樹可以表示上百萬的資料，如果上百萬的資料查詢只需要三次IO，效能提高將是巨大的，如果沒有索引，每個資料項都要發生一次IO，那麼總共需要百萬次的IO，顯然成本非常非常高。
   

2. Hash索引

3. full-text全文索引

4. R-Tree索引

4.7 哪些情況需要建索引

 1. 主鍵自動建立唯一索引
 2. 頻繁作為查詢條件的欄位應該建立索引
 3. 查詢中與其他表關聯的欄位,外來鍵關係建立索引
 4. 頻繁更新的欄位不適合建立索引(每次更新還需要更新索引)
 5. where條件裡用不到的欄位不建立索引
 6. 單鍵/組合索引  (在高併發情況下傾向建立組合索引)
 7. 查詢中排序的欄位,排序欄位若通過索引去訪問將大大提高排序速度
 8. 查詢中統計或者分組的欄位

4.8 哪些情況不用建索引

 1. 表記錄太少
 2. 經常增刪改的表(不僅僅需要維護表,還需要維護索引)
 3. 資料重複且分佈平均的表欄位

五、Explain效能分析

5.1 MySql常見瓶頸

 1. CPU:CPU在飽和時的時候一般發生在資料裝入記憶體或從磁碟上讀取資料時候
 2. IO:磁碟IO瓶頸發生在裝入資料遠大於記憶體容量的時候
 3. 伺服器硬體的效能瓶頸:top,free,lostat和vmstat來檢視系統的效能瓶頸

5.2 Explain

5.2.1 是什麼

使用explain關鍵字可以模擬優化器執行SQL查詢語句,從而知道mysql是如何處理你的sql語句的,分析你的sql語句或者是表結構的效能瓶頸

5.2.1 能幹嗎

 1. 表的讀取順序
 2. 哪些索引可以使用
 3. 資料讀取操作的操作型別
 4. 哪些索引被實際使用
 5. 表之間的引用
 6. 每張表有多少行被優化器查詢

5.2.2 怎麼玩

explain+sql語句

5.2.3 各欄位解釋

1. id
表的讀取順序
select查詢的序列號,包含一組順序,表事查詢中執行select子句或操作表的順序
id相同,執行順序從上到下
如果是子查詢,id的序號會遞增,id值越大優先順序越高,越先被執行

2. select_type
資料讀取操作的操作型別
取值有simple、primary、subquery、derived、union、union result
查詢的型別,主要是用於區別普通查詢、聯合查詢、子查詢等的複雜查詢
simple:簡單的select查詢,查詢中不包含子查詢或者union
primary:查詢中若包含任何複雜的子部分,最外層查詢被標記為primary
subquery:查詢中若包含任何複雜的子部分,子查詢被標記為subquery
derived:在from列表中包含的子查詢表標記為derived,MYSQL會遞迴執行這些子查詢,把結果放在臨時表裡
union:若第二個select出現在union之後,則被標記為union,若union包含在from子句的子查詢中,外層select被標記為deriver
union result:從union表獲取結果的select

3. table
顯示這一行的資料是關於那張表的

4. type
顯示查詢使用了哪種型別
取值有ALL、Index、range、ref、eq_ref、const,system、null等
從最好到最差的依次是:system>const>eq_ref>ref>range>index>all
一般來說,得保證查詢到達range級別,最好是ref
system:表只有一行記錄,這是const型別的特例,平時不會出現
const:表示通過索引一次就找到了,const用於比較primary key或者unique索引,因為只匹配一行資料,所以很快;如將主鍵置於where列表中,MYSQL就能將該查詢轉換為一個常量
eq_ref:唯一性索引掃描,對於每個索引鍵,表中只有一條記錄與之匹配,常見於主鍵或唯一索引掃描
ref:非唯一性索引掃描,返回匹配某個單獨值的所有行;本質上也是一種索引訪問,它返回所有匹配某個單獨值的行,然而,它可能會找到多個符合條件的行,所以他應該屬於查詢和掃描的混合體
range:只檢索給定範圍的行,使用一個索引來選擇行;key 列顯示使用了哪個索引,一般就是在你的where語句中出現了between and等的查詢,這種範圍掃描索引比全表掃描要好,因為只掃描部分索引,不用掃描全部
index:只遍歷索引樹(雖然all和Index都是讀全表,但是Index是從索引中讀取,而all是從硬碟中讀取的)
all:全表掃描

5. possible_keys
顯示可能應用在這張表中的索引,一個或多個
查詢涉及到的欄位上若存在索引,則該索引被列出,但不一定被查詢實際使用

6. key
實際使用的索引,如果為null,則沒有使用索引
查詢中若使用了覆蓋索引,則該索引僅僅出現在key列表中

7. key_len
表示索引中使用的位元組數,可通過該列計算查詢中使用的索引的長度,在不損失精確性的情況下越小越好
值為索引欄位的最大可能長度,並非實際使用長度,既是根據表定義計算而得,不是通過表內檢索出的

8. ref
顯示索引的哪一列被使用了,如果可能的話,是一個常數,那些列或常量用於查詢索引列上的值

9. rows
每張表有多少行被優化器查詢 
根據表統計資訊及索引選用情況,大致估算出找到所需記錄所需要讀取的行數,越小越好

10. extra
10.1 using filesort
說明mysql會對資料使用一個外部的索引排序,而不是按照表內的索引順序進行讀取。
mysql中無法利用索引完成的排序操作稱為"檔案排序";
10.2 using temporary
使用了臨時表儲存中間結果,mysql在對查詢結果排序時使用臨時表。常見於排序和分組查詢。
10.3 using index
表示相應的selec操作中使用了覆蓋索引,避免訪問了表的資料行,效率不錯
如果同時出現了using where ,表面索引被用來執行索引鍵值的查詢
如果沒有同時出現using where,表面索引只是用來讀取資料而非利用索引來執行查詢
10.4 using where 
表明使用了where過濾
10.5 using join buffer
使用了連線快取
10.6 impossible where
where子句的值總是false
10.7 select tables optimized away
在沒有groupby子句的情況下,基於索引優化min/max操作或者對於myisan儲存殷勤優化count(*)操作,不必等到執行階段在進行計算,查詢執行階段即完成優化。

六、索引優化

6.1 索引分析

#總結 
#1.where後面的欄位建立索引
#2.儘量建立複合索引
#3.對複合索引的 不是第一個欄位 的其餘欄位進行了範圍查詢 會使後面欄位索引失效
#4.對左外連線的表建立索引,應該對右邊的表的外來鍵欄位建立索引
#5.保證被驅動表的join欄位以及建立索引(join後的表為被驅動表)
#6.left join時,選擇小表作為驅動表,大表作為被驅動表
#7.inner join時,mysql會自動幫你把小結果集的表作為驅動表
#8.子查詢儘量不要放在被驅動表,可能會不走索引

CREATE TABLE IF NOT EXISTS `article` (
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL,
`views` INT(10) UNSIGNED NOT NULL,
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);

INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');

SELECT * FROM article;

#1.查詢 category_id 為1 且  comments 大於 1 的情況下,views 最多的 article_id。 
 
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

#結論：很顯然,type 是 ALL,即最壞的情況。Extra 裡還出現了 Using filesort,也是最壞的情況。優化是必須的。
 
#開始優化：
# 1.1 新建索引+刪除索引
#ALTER TABLE `article` ADD INDEX idx_article_ccv ( `category_id` , `comments`, `views` );
create index idx_article_ccv on article(category_id,comments,views);
DROP INDEX idx_article_ccv ON article
  
# 1.2 第2次EXPLAIN
EXPLAIN SELECT id,author_id FROM `article` WHERE category_id = 1 AND comments >1 ORDER BY views DESC LIMIT 1;
 
#結論：
#type 變成了 range,這是可以忍受的。但是 extra 裡使用 Using filesort 仍是無法接受的。
#但是我們已經建立了索引,為啥沒用呢?
#這是因為按照 BTree 索引的工作原理,
# 先排序 category_id,
# 如果遇到相同的 category_id 則再排序 comments,如果遇到相同的 comments 則再排序 views。
#當 comments 欄位在聯合索引裡處於中間位置時,
#因comments > 1 條件是一個範圍值(所謂 range),
#MySQL 無法利用索引再對後面的 views 部分進行檢索,即 range 型別查詢欄位後面的索引無效。
 
# 1.3 刪除第一次建立的索引
DROP INDEX idx_article_ccv ON article;
 
# 1.4 第2次新建索引
#ALTER TABLE `article` ADD INDEX idx_article_cv ( `category_id` , `views` ) ;
create index idx_article_cv on article(category_id,views);
 
# 1.5 第3次EXPLAIN
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
#結論：可以看到,type 變為了 ref,Extra 中的 Using filesort 也消失了,結果非常理想。
DROP INDEX idx_article_cv ON article;

CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);


INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));


INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

# 下面開始explain分析
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
#結論：type 有All
 
# 新增索引優化
ALTER TABLE `book` ADD INDEX Y ( `card`);
 
# 第2次explain
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
#可以看到第二行的 type 變為了 ref,rows 也變成了優化比較明顯。
#這是由左連線特性決定的。LEFT JOIN 條件用於確定如何從右表搜尋行,左邊一定都有,
#所以右邊是我們的關鍵點,一定需要建立索引。
 
# 刪除舊索引 + 新建 + 第3次explain
DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX X (card);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

6.2 索引失效(應該避免)

CREATE TABLE staffs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (24)  NULL DEFAULT '' ,
  age INT NOT NULL DEFAULT 0 ,
  pos VARCHAR (20) NOT NULL DEFAULT '' ,
  add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
) CHARSET utf8 ;
 
 
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());
SELECT * FROM staffs;
 
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

1. 全值匹配你最愛
2. 最左字首索引法則:指的是查詢從索引的最左前列開始並且不跳過索引中的列(and忽略左右關係)
3. 不在索引列上做任何操作
4. 儲存引擎不能使用索引中範圍條件右邊的列
5. 儘量使用select 索引欄位,減少select *
6. mysql在使用不等於的時候無法使用索引會導致全表掃描
7. is not null無法使用索引,但是is null可以
8. like以萬用字元開頭 索引會失效,如果想要兩邊都%,就使用覆蓋索引(建的索引和查的欄位順序和個數最好一致)
9. 字串不加單引號索引失效
10.少用or,用它來連線會導致索引失效 

6.3 面試題分析

create table test03(
    ->  id int primary key not null auto_increment,
    ->  c1 char(10),
    ->  c2 char(10),
    ->  c3 char(10),
    ->  c4 char(10),
    ->  c5 char(10)
    -> );

insert into test03(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

create index idx_test03_c1234 on test03(c1,c2,c3,c4);

#1.全值匹配你最愛
explain select * from test03 where c1='a1';
explain select * from test03 where c1='a1' and c2='a2';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
#1.1四個索引全用到
explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';
#1.2用到c1 c2 c3 (我以為用到c1 c2 ,但是範圍之後全失效)
explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
#1.3用到c1 c2 c3 c4
 explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
#1.4用到c1 c2 
explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;
#c3也用到了,但是它的作用是在排序而不是查詢
#1.5用到c1 c2 
explain select * from test03 where c1='a1' and c2='a2' order by c3;
#c3也用到了,但是它的作用是在排序而不是查詢
#1.6用到c1 c2 
explain select * from test03 where c1='a1' and c2='a2' order by c4; 
#using filesort
#1.7用到了c1 但是c2 c3用於排序,無filesort
 explain select * from test03 where c1='a1' and c5='a5' order by c2,c3; 
 #1.8用到了c1 ,有filesort
  explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;
 #1.9用到了c1 c2 無filesort
explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;
#1.10
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3; #用到了c1 c2 無filesort
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2; #用到了c1 c2 無filesort
explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;  #用到了c1 ,有filesort
#1.11 用到了c1 無filesort
explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;
#1.12  用到了c1 有filesort,還有了temporary
explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;          

定值、範圍還是排序,一般order by 是給個範圍
group by 基本上都需要進行排序,會有臨時表產生

1. 對於單鍵索引，儘量選擇針對當前query過濾性更好的索引
2. 在選擇組合索引的時候，當前Query中過濾性最好的欄位在索引欄位順序中，位置越靠前越好。(避免索引過濾性好的索引失效)
3. 在選擇組合索引的時候，儘量選擇可以能夠包含當前query中的where字句中更多欄位的索引
4. 儘可能通過分析統計資訊和調整query的寫法來達到選擇合適索引的目的

6.4 小總結

where語句	是否使用索引
where a=3	是,使用a
where a=3,b=3	是,使用a b
where a=3,b=3	是,使用a b c
where b=3或者where b=3 and c=4	否
where a=3 and c=5	是 ,使用a
where a=3 and b>4 and c=5	是,使用a b,b斷了,c不能用在範圍之後
where a=3 and b like ‘kk%’ and c=4	是,使用到a b c
where a=3 and b like ‘%kk’ and c=4	是 只用到a
where a=3 and b like ‘%kk%’ and c=4	是,只用到a
where a=3 and b like ‘k%kk%’ and c=4	是,使用到a b c

6.5 口訣

全值匹配我最愛,最左字首要遵守;
帶頭大哥不能死,中間兄弟不能斷;
索引列上少計算,範圍之後全失效;
like百分寫最右,覆蓋索引不寫星;
不等空值還有or,索引失效要少用

七、查詢擷取分析

7.1 查詢優化

小表驅動大表

for(int i=5;...){
	for(int j=1000)
	{
	
	}
}
*****************************
for(int i=1000;...){
	for(int j=5
	{
	
	}
}
******************************
儘量使用上面的,在計算看來這兩個結果一樣,但是當使用到資料庫時,使用上面的減少了連線的建立

select * from A where id in (select id from B)
等價於
for select id from B
for select * from A where A.id=B.id
當B表的資料集小於A表的資料集時,用in優於exists    //使用in的話,外面的第一個for迴圈是B

select * from A where exists (select 1 from B where B.id=A.id)
等價於
for select * from A
for select * from B where B.id=A.id
當A表的資料集小於B表的資料集時,用exists優於in    //使用exists的話,外面的第一個for迴圈是A
//exists可以理解為:將主查詢的資料,放到子查詢中做條件驗證,根據驗證結果來決定主查詢的資料結果是否得以保留

order by 優化

1.order by 子句,儘量使用index 避免產生filesort

order by 兩情況,會使用index排序:
1.order by 語句使用索引最左前列原則
2.使用where子句與order by子句條件列組合滿足索引最左前列
3.where子句中如果出現索引的範圍查詢(即explain中出現range)會導致order by 索引失效。

CREATE TABLE tblA(
  id int primary key not null auto_increment,
  age INT,
  birth TIMESTAMP NOT NULL,
  name varchar(200)
);
 
INSERT INTO tblA(age,birth,name) VALUES(22,NOW(),'abc');
INSERT INTO tblA(age,birth,name) VALUES(23,NOW(),'bcd');
INSERT INTO tblA(age,birth,name) VALUES(24,NOW(),'def');
 
CREATE INDEX idx_A_ageBirth ON tblA(age,birth,name);
 
SELECT * FROM tblA; 

2.儘可能在索引列上完成排序操作,遵照索引建的最佳左字首

3.如果不在索引列上完成排序操作,filesort有兩種演算法:mysql就要啟動雙路排序和單路排序

雙路排序:
MySQL 4.1之前是使用雙路排序,字面意思就是兩次掃描磁碟，最終得到資料，
讀取行指標和orderby列，對他們進行排序，然後掃描已經排序好的列表，按照列表中的值重新從列表中讀取對應的資料輸出
從磁碟取排序欄位，在buffer進行排序，再從磁碟取其他欄位。

單路排序:
取一批資料，要對磁碟進行了兩次掃描，眾所周知，I\O是很耗時的，所以在mysql4.1之後，出現了第二種改進的演算法，就是單路排序。
從磁碟讀取查詢需要的所有列，按照order by列在buffer對它們進行排序，然後掃描排序後的列表進行輸出，
它的效率更快一些，避免了第二次讀取資料。並且把隨機IO變成了順序IO,但是它會使用更多的空間，因為它把每一行都儲存在記憶體中了。

結論及引申出的問題:
由於單路是後出的，總體而言好過雙路
但是用單路有問題
在sort_buffer中，方法B比方法A要多佔用很多空間，因為方法B是把所有欄位都取出, 所以有可能取出的資料的總大小超出了sort_buffer的容量，導致每次只能取sort_buffer容量大小的資料，進行排序（建立tmp檔案，多路合併），排完再取取sort_buffer容量大小，再排……
從而多次I/O。
本來想省一次I/O操作，反而導致了大量的I/O操作，反而得不償失。

4.優化策略

增大sort_buffer_size引數的設定:用於單路排序的記憶體大小
增大max_length_for_sort_data引數的設定:單次排序欄位大小。(單次排序請求)
去掉select 後面不需要的欄位:select 後的多了，排序的時候也會帶著一起，很佔記憶體，所以去掉沒有用的

Why:
提高Order By的速度
1. Order by時select * 是一個大忌只Query需要的欄位， 這點非常重要。在這裡的影響是：
1.1 當Query的欄位大小總和小於max_length_for_sort_data 而且排序欄位不是 TEXT|BLOB 型別時，會用改進後的演算法——單路排序， 否則用老演算法——多路排序。
1.2 兩種演算法的資料都有可能超出sort_buffer的容量，超出之後，會建立tmp檔案進行合併排序，導致多次I/O，但是用單路排序演算法的風險會更大一些,所以要提高sort_buffer_size。

2. 嘗試提高 sort_buffer_size
不管用哪種演算法，提高這個引數都會提高效率，當然，要根據系統的能力去提高，因為這個引數是針對每個程式的

3. 嘗試提高 max_length_for_sort_data
提高這個引數， 會增加用改進演算法的概率。但是如果設的太高，資料總容量超出sort_buffer_size的概率就增大，明顯症狀是高的磁碟I/O活動和低的處理器使用率. 

5.小總結

為排序使用索引:
mysql兩種排序方式:檔案排序或掃描有序索引排序
mysql能為排序和查詢使用相同的索引

key a_b_c(a,b,c)
order by 使用索引最左字首:
order by a
order by a,b
order by a,b,c
order by a desc,b desc, c desc

如果where使用索引的最左字首定義為常量,則order by 能使用索引:
where a=const order by b,c
where a=const and b=const order by c
where a=const and b>const order by b,c 

不能使用索引進行排序:
order by a asc,b desc,c desc /*排序不一致*/
where g=const order by b,c /*丟失a*/
where a=const order by c /*丟失b索引*/
where a=const order by a,d /*d不是索引的一部分*/
where a in (...) order by a,c /*對於排序來說,多個想等條件也是範圍查詢*/

group by 優化

1.group by實質是先排序後進行分組，遵照索引建的最佳左字首
2.當無法使用索引列，增大max_length_for_sort_data引數的設定+增大sort_buffer_size引數的設定
3.where高於having，能寫在where限定的條件就不要去having限定了。

7.2 慢查詢日誌

是什麼

1.MySQL的慢查詢日誌是MySQL提供的一種日誌記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具體指執行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。

2.具體指執行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。long_query_time的預設值為10，意思是執行10秒以上的語句。

3.由他來檢視哪些SQL超出了我們的最大忍耐時間值，比如一條sql執行超過5秒鐘，我們就算慢SQL，希望能收集超過5秒的sql，結合之前explain進行全面分析。

怎麼玩

1.說明
預設情況下，MySQL資料庫沒有開啟慢查詢日誌，需要我們手動來設定這個引數。
當然，如果不是調優需要的話，一般不建議啟動該引數，因為開啟慢查詢日誌會或多或少帶來一定的效能影響。慢查詢日誌支援將日誌記錄寫入檔案
2.檢視是否開啟以及如何開啟
show variables like '%slow_query_log%';
set global slow_query_log=1;
3.那麼開啟了之後,什麼樣的sql語句會被記錄到日誌裡呢
show variables like 'long_query_time%';檢視預設值;
可以使用命令修改值的大小 set global long_query_time=3;
假如執行時間恰好等於,也不會被記錄,記錄的是大於這個執行時間的sql語句
4.查詢當前系統中有多少條慢查詢記錄
show global status like '%Slow_queries%'

7.3 批量資料指令碼

1.建表

# 新建庫
create database bigData;
use bigData;

#1 建表dept
CREATE TABLE dept(  
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,   
dname VARCHAR(20) NOT NULL DEFAULT "",  
loc VARCHAR(13) NOT NULL DEFAULT ""  
) ENGINE=INNODB DEFAULT CHARSET=UTF8 ;  
 
 
#2 建表emp
CREATE TABLE emp  
(  
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*編號*/  
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/  
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/  
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上級編號*/  
hiredate DATE NOT NULL,/*入職時間*/  
sal DECIMAL(7,2) NOT NULL,/*薪水*/  
comm DECIMAL(7,2) NOT NULL,/*紅利*/  
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部門編號*/  
)ENGINE=INNODB DEFAULT CHARSET=UTF8 ; 

2.設定引數log_bin_trust_function_creators

set global log_bin_trust_function_creators=1;

3.建立函式,保證每條資料不同

#隨機產生字串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    ##方法開始
 DECLARE chars_str VARCHAR(100) DEFAULT   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 ##宣告一個 字元竄長度為 100 的變數 chars_str ,預設值 
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0;
##迴圈開始
 WHILE i < n DO  
 SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
##concat 連線函式  ，substring(a,index,length) 從index處開始擷取
 SET i = i + 1;
 END WHILE;
 RETURN return_str;
END $$
 
#假如要刪除
#drop function rand_string;

#隨機產生部門編號
DELIMITER $$
CREATE FUNCTION rand_num( ) 
RETURNS INT(5)  
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = FLOOR(100+RAND()*10);  
RETURN i;  
 END $$
 
 
#假如要刪除
#drop function rand_num;

4.建立儲存過程

#建立往emp表中插入資料的儲存過程
DELIMITER $$
CREATE PROCEDURE insert_emp10000(IN START INT(10),IN max_num INT(10))  
BEGIN  
DECLARE i INT DEFAULT 0;   
#set autocommit =0 把autocommit設定成0  ；提高執行效率
 SET autocommit = 0;    
 REPEAT  ##重複
 SET i = i + 1;  
 INSERT INTO emp10000 (empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),FLOOR(1+RAND()*20000),FLOOR(1+RAND()*1000),rand_num());  
 UNTIL i = max_num   ##直到  上面也是一個迴圈
 END REPEAT;  ##滿足條件後結束迴圈
 COMMIT;   ##執行完成後一起提交
 END $$
 
#刪除
# DELIMITER ;
# drop PROCEDURE insert_emp;

#建立往dept表中插入資料的儲存過程
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))  
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO dept (deptno ,dname,loc ) VALUES (START +i ,rand_string(10),rand_string(8));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END $$ 
 
#刪除
# DELIMITER ;
# drop PROCEDURE insert_dept;

5.呼叫儲存過程

#dept
DELIMITER ;
CALL insert_dept(100,10); 

#執行儲存過程，往emp表新增50萬條資料
DELIMITER ;    #將 結束標誌換回 ;
CALL insert_emp(100001,500000); 
CALL insert_emp10000(100001,10000); 

7.4 show profile

是什麼

是什麼：是mysql提供可以用來分析當前會話中語句執行的資源消耗情況。可以用於SQL的調優的測量
官網地址:http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
預設情況下，引數處於關閉狀態，並儲存最近15次的執行結果

分析步驟

1.檢視當前mysql版本是否支援:
Show  variables like 'profiling';

2.開啟功能 預設關閉  
set profiling=1;

3.執行sql 
select * from emp group by id%10 limit 150000;
select * from emp group by id%20  order by 5

4.檢視結果 show profiles;

5.診斷sql,show profile cpu,block io for query 上一步前面地問題sql數字號碼
type:  
 | ALL                        --顯示所有的開銷資訊  
 | BLOCK IO                --顯示塊IO相關開銷  
 | CONTEXT SWITCHES --上下文切換相關開銷  
 | CPU              --顯示CPU相關開銷資訊  
 | IPC              --顯示傳送和接收相關開銷資訊  
 | MEMORY           --顯示記憶體相關開銷資訊  
 | PAGE FAULTS      --顯示頁面錯誤相關開銷資訊  
 | SOURCE           --顯示和Source_function，Source_file，Source_line相關的開銷資訊  
 | SWAPS            --顯示交換次數相關開銷的資訊
 
6.日常開發需要注意的結論
converting HEAP to MyISAM 查詢結果太大，記憶體都不夠用了往磁碟上搬了。
Creating tmp table 建立臨時表
	拷貝資料到臨時表
	用完再刪除
Copying to tmp table on disk 把記憶體中臨時表複製到磁碟，危險！！！
locked

7.5 全域性查詢日誌

永遠不要在生產環境中開啟這個功能

命令
set global general_log=1;

#全域性日誌可以存放到日誌檔案中，也可以存放到Mysql系統表中。存放到日誌中效能更好一些，儲存到表中
set global log_output='TABLE';

此後 ，你所編寫的sql語句，將會記錄到mysql庫裡的general_log表，可以用下面的命令檢視

select * from mysql.general_log;

八、MySQL的鎖機制

8.1 概述

鎖是計算機協調多個程式或執行緒併發訪問某一資源的機制。
在資料庫中，除傳統的計算資源（如CPU、RAM、I/O等）的爭用以外，資料也是一種供許多使用者共享的資源。如何保證資料併發訪問的一致性、有效性是所有資料庫必須解決的一個問題，鎖衝突也是影響資料庫併發訪問效能的一個重要因素。從這個角度來說，鎖對資料庫而言顯得尤其重要，也更加複雜。

打個比方，我們到淘寶上買一件商品，商品只有一件庫存，這個時候如果還有另一個人買，
那麼如何解決是你買到還是另一個人買到的問題？
這裡肯定要用到事務，我們先從庫存表中取出物品數量，然後插入訂單，付款後插入付款表資訊，
然後更新商品數量。在這個過程中，使用鎖可以對有限的資源進行保護，解決隔離和併發的矛盾。

鎖的分類
從對資料操作的型別（讀\寫）分
	讀鎖(共享鎖)：針對同一份資料，多個讀操作可以同時進行而不會互相影響。可以讀自己,不能修改自己,不能讀別的表;別人可以讀,別人修改時發生阻塞;
	寫鎖（排它鎖）：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。
從對資料操作的粒度分
	表鎖
	行鎖

8.2 三鎖

表鎖(偏讀)

1.特點:偏向myisan儲存引擎,開銷小,加鎖快;無死鎖;鎖定力度大,發生鎖衝突的概率最高,併發度最低;
2.案例分析:

【表級鎖分析--建表SQL】
 
create table mylock(
 id int not null primary key auto_increment,
 name varchar(20)
)engine myisam;
 
insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');
 
select * from mylock;
 
【手動增加表鎖】
 #lock table 表名字1 read(write)，表名字2 read(write)，其它;
 lock table mylock read,dept write;
【檢視錶上加過的鎖】
  show open tables;
【釋放表鎖】
unlock tables;

#1.lock table mylock read
當前session可以讀,其它session也可以讀
當前session不能查詢其它沒有鎖定的表,其它session可以查詢或者更新未鎖定的表
當前session中插入或者更新 鎖定的表 都會提示錯誤,其它session插入或更新鎖定表會一直等待獲得鎖
#2.lock table mylock write
當前session可以讀,其它session阻塞等待鎖釋放
當前session不能查詢其它沒有鎖定的表,其它session可以查詢或者更新未鎖定的表
當前session可以更新 鎖定的表,其它session插入或更新鎖定表會一直等待獲得鎖

3.案例結論:
MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行增刪改操作前，會自動給涉及的表加寫鎖。 
MySQL的表級鎖有兩種模式：
表共享讀鎖（Table Read Lock）
表獨佔寫鎖（Table Write Lock）鎖型別他人可讀他人可寫讀鎖是否寫鎖否否

鎖型別	可否相容	讀鎖	寫鎖
讀鎖	是	是	否
寫鎖	是	否	否

結論：
結合上表，所以對MyISAM表進行操作，會有以下情況： 
  1、對MyISAM表的讀操作（加讀鎖），不會阻塞其他程式對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放後，才會執行其它程式的寫操作。 
  2、對MyISAM表的寫操作（加寫鎖），會阻塞其他程式對同一表的讀和寫操作，只有當寫鎖釋放後，才會執行其它程式的讀寫操作。

簡而言之，就是讀鎖會阻塞寫，但是不會堵塞讀。而寫鎖則會把讀和寫都堵塞

行鎖(偏寫)

1.特點
偏向InnoDB儲存引擎，開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低,併發度也最高。
InnoDB與MyISAM的最大不同有兩點：一是支援事務（TRANSACTION）；二是採用了行級鎖

2.行鎖支援事務
事務是由一組SQL語句組成的邏輯處理單元，事務具有以下4個屬性，通常簡稱為事務的ACID屬性。 
l 原子性（Atomicity）：事務是一個原子操作單元，其對資料的修改，要麼全都執行，要麼全都不執行。 
l 一致性（Consistent）：在事務開始和完成時，資料都必須保持一致狀態。這意味著所有相關的資料規則都必須應用於事務的修改，以保持資料的完整性；事務結束時，所有的內部資料結構（如B樹索引或雙向連結串列）也都必須是正確的。 
l 隔離性（Isolation）：資料庫系統提供一定的隔離機制，保證事務在不受外部併發操作影響的“獨立”環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的，反之亦然。 
l 永續性（Durable）：事務完成之後，它對於資料的修改是永久性的，即使出現系統故障也能夠保持。 

併發事務處理帶來的問題:
更新丟失(Lost Update):
當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，由於每個事務都不知道其他事務的存在，就會發生丟失更新問題－－最後的更新覆蓋了由其他事務所做的更新。
例如，兩個程式設計師修改同一java檔案。每程式設計師獨立地更改其副本，然後儲存更改後的副本，這樣就覆蓋了原始文件。最後儲存其更改副本的編輯人員覆蓋前一個程式設計師所做的更改。
如果在一個程式設計師完成並提交事務之前，另一個程式設計師不能訪問同一檔案，則可避免此問題。 

髒讀(Dirty Reads):一句話：
事務A讀取到了事務B已修改但尚未提交的的資料，還在這個資料基礎上做了操作。此時，如果B事務回滾，A讀取的資料無效，不符合一致性要求。

不可重複讀(Non-Repeatable Reads):
在一個事務內，多次讀同一個資料。在這個事務還沒有結束時，另一個事務也訪問該同一資料。那麼，在第一個事務的兩次讀資料之間。由於第二個事務的修改，那麼第一個事務讀到的資料可能不一樣，這樣就發生了在一個事務內兩次讀到的資料是不一樣的，因此稱為不可重複讀，即原始讀取不可重複。
一句話：一個事務範圍內兩個相同的查詢卻返回了不同資料。
	
幻讀(Phantom Reads):
  一個事務按相同的查詢條件重新讀取以前檢索過的資料，卻發現其他事務插入了滿足其查詢條件的新資料，這種現象就稱為“幻讀”。
  一句話：事務A 讀取到了事務B提交的新增資料，不符合隔離性。
   
事務隔離級別:
髒讀”、“不可重複讀”和“幻讀”，其實都是資料庫讀一致性問題，必須由資料庫提供一定的事務隔離機制來解決。
資料庫的事務隔離越嚴格，併發副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使事務在一定程度上 “序列化”進行，這顯然與“併發”是矛盾的。同時，不同的應用對讀一致性和事務隔離程度的要求也是不同的，比如許多應用對“不可重複讀”和“幻讀”並不敏感，可能更關心資料併發訪問的能力。 
常看當前資料庫的事務隔離級別：show variables like 'tx_isolation';

3.案例分析

create table test_innodb_lock (a int(11),b varchar(16))engine=innodb;

insert into test_innodb_lock values(1,'b2');
insert into test_innodb_lock values(3,'3');
insert into test_innodb_lock values(4,'4000');
insert into test_innodb_lock values(5,'5000');
insert into test_innodb_lock values(6,'6000');
insert into test_innodb_lock values(7,'7000');
insert into test_innodb_lock values(8,'8000');
insert into test_innodb_lock values(9,'9000');
insert into test_innodb_lock values(1,'b1');

create index test_innodb_a_ind on test_innodb_lock(a);
create index test_innodb_lock_b_ind on test_innodb_lock(b);

select * from test_innodb_lock;
#關閉自動提交
set autocommit=0;


#1.如何鎖定一行
select xxx ... for update鎖定某一行後,其它的操作會被阻塞,直到鎖定行的會話提交commit;
#2.間隙鎖
因為Query執行過程中通過過範圍查詢的話，他會鎖定整個範圍內所有的索引鍵值，即使這個鍵值並不存在。即使session2操作的是另外一行不存在的資料,也會阻塞;
#3.索引失效行鎖變成表鎖
本來各自鎖定各自的行，互相不影響，如果沒有正常使用，會導致行鎖變表鎖
比如沒加單引號導致索引失效，行鎖變表鎖被阻塞，等待。只到Session_1提交後才阻塞解除，完成更新

4.案例結論
 Innodb儲存引擎由於實現了行級鎖定，雖然在鎖定機制的實現方面所帶來的效能損耗可能比表級鎖定會要更高一些，但是在整體併發處理能力方面要遠遠優於MyISAM的表級鎖定的。當系統併發量較高的時候，Innodb的整體效能和MyISAM相比就會有比較明顯的優勢了。
 但是，Innodb的行級鎖定同樣也有其脆弱的一面，當我們使用不當的時候，可能會讓Innodb的整體效能表現不僅不能比MyISAM高，甚至可能會更差。

5.行鎖分析
show status like 'innodb_row_lock%';
對各個狀態量的說明如下：

Innodb_row_lock_current_waits：當前正在等待鎖定的數量；
Innodb_row_lock_time：從系統啟動到現在鎖定總時間長度；
Innodb_row_lock_time_avg：每次等待所花平均時間；
Innodb_row_lock_time_max：從系統啟動到現在等待最常的一次所花的時間；
Innodb_row_lock_waits：系統啟動後到現在總共等待的次數；
對於這5個狀態變數，比較重要的主要是
Innodb_row_lock_time_avg（等待平均時長），
Innodb_row_lock_waits（等待總次數）
Innodb_row_lock_time（等待總時長）這三項。
尤其是當等待次數很高，而且每次等待時長也不小的時候，我們就需要分析系統中為什麼會有如此多的等待，然後根據分析結果著手指定優化計劃。
最後可以通過SELECT * FROM information_schema.INNODB_TRX\G;來查詢正在被鎖阻塞的sql語句。
	
6.優化建議
6.1儘可能讓所有資料檢索都通過索引來完成，避免無索引行鎖升級為表鎖。
6.2儘可能較少檢索條件，避免間隙鎖
6.3儘量控制事務大小，減少鎖定資源量和時間長度
6.4鎖住某行後，儘量不要去調別的行或表，趕緊處理被鎖住的行然後釋放掉鎖。
6.5涉及相同表的事務，對於呼叫表的順序儘量保持一致。
6.6在業務環境允許的情況下,儘可能低階別事務隔離

頁鎖

開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，併發度一般。

九、主從複製

9.1複製的基本原理

slave會從master讀取binlog來進行資料同步
MySQL複製過程分成三步：
1 master將改變記錄到二進位制日誌（binary log）。這些記錄過程叫做二進位制日誌事件，binary log events；
2 slave將master的binary log events拷貝到它的中繼日誌（relay log）；
3 slave重做中繼日誌中的事件，將改變應用到自己的資料庫中。 MySQL複製是非同步的且序列化的

9.2複製的基本原則

複製的基本原則:
每個slave只有一個master
每個slave只能有一個唯一的伺服器ID
每個master可以有多個salve

9.3複製的最大問題

延時

9.4一主一從常見配置

1.mysql版本一致且後臺以服務執行
2.主從都配置在[mysqld]結點下，都是小寫
3.主機修改my.ini配置檔案
	[必須]主伺服器唯一ID
		server-id=1
	[必須]啟用二進位制日誌
		log-bin=自己本地的路徑/data/mysqlbin
		log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin
	[可選]啟用錯誤日誌
		log-err=自己本地的路徑/data/mysqlerr
		log-err=D:/devSoft/MySQLServer5.5/data/mysqlerr
	[可選]根目錄
		basedir="自己本地路徑"
		basedir="D:/devSoft/MySQLServer5.5/"
	[可選]臨時目錄
		tmpdir="自己本地路徑"
		tmpdir="D:/devSoft/MySQLServer5.5/"
	[可選]資料目錄
		datadir="自己本地路徑/Data/"
		datadir="D:/devSoft/MySQLServer5.5/Data/"
	read-only=0
		主機，讀寫都可以
	[可選]設定不要複製的資料庫
		binlog-ignore-db=mysql
	[可選]設定需要複製的資料庫
		binlog-do-db=需要複製的主資料庫名字
4.從機修改my.cnf配置檔案
	[必須]從伺服器唯一ID
	[可選]啟用二進位制日誌
5.因修改過配置檔案，請主機+從機都重啟後臺mysql服務
6.主機從機都關閉防火牆
	windows手動關閉
	關閉虛擬機器linux防火牆    service iptables stop
7.在Windows主機上建立帳戶並授權slave
	GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'從機器資料庫IP' IDENTIFIED BY '123456';
	flush privileges;
	查詢master的狀態
		show master status;
		記錄下File和Position的值
	執行完此步驟後不要再操作主伺服器MYSQL，防止主伺服器狀態值變化
8.在Linux從機上配置需要複製的主機
	CHANGE MASTER TO MASTER_HOST='主機IP',MASTER_USER='zhangsan',MASTER_PASSWORD='123456',MASTER_LOG_FILE='File名字',MASTER_LOG_POS=Position數字;
	啟動從伺服器複製功能
		start slave;
	show slave status\G
		下面兩個引數都是Yes，則說明主從配置成功！
		Slave_IO_Running: Yes
		Slave_SQL_Running: Yes
9.主機新建庫、新建表、insert記錄，從機複製
10.如何停止從服務複製功能
	stop slave;