索引

索引 : 是幫助MySql高效查詢數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)

優(yōu)勢(shì)&劣勢(shì)

• 優(yōu)勢(shì):
  1. 提高數(shù)據(jù)檢索的效率, 降低數(shù)據(jù)庫(kù)的IO成本
  2. 通過(guò)索引列隊(duì)數(shù)據(jù)進(jìn)行排序, 降低數(shù)據(jù)的排序成本, 降低CPU的消耗
• 劣勢(shì):
  1. 索引維護(hù)了主鍵信息, 并指向表中數(shù)據(jù)記錄, 也是占用磁盤(pán)空間的
  2. 索引提高了查詢效率, 但索引也不是越多越好, 增刪改的時(shí)候會(huì)對(duì)索引進(jìn)行維護(hù), 會(huì)降低更新表速度

索引結(jié)構(gòu)

索引是MySql的存儲(chǔ)引擎層實(shí)現(xiàn)的, 而不是在服務(wù)器層實(shí)現(xiàn)的

MySql 目前提供了以下4種規(guī)則引擎:

• BTREE(btree)索引: 常見(jiàn)的索引類(lèi)型, 大部分索引都支持B樹(shù)索引
• HASH(hash)索引 : 只有Memory引擎支持, 使用場(chǎng)景簡(jiǎn)單
• R-tree索引(空間索引) : 空間索引是MyISAM引擎的一個(gè)特殊索引類(lèi)型, 主要用于地理空間數(shù)據(jù)類(lèi)型, 通常使用較少
• Full-text(全文索引) : 全文索引也是MyISAM引擎的一個(gè)特殊索引類(lèi)型, 主要用于全文索引, InnoDB從MySql5.6版本開(kāi)始也支持全文索引

MyISAM、InnoDB、Memory三種存儲(chǔ)引擎對(duì)各種索引類(lèi)型的支持

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH索引	不支持	不支持	支持
R-tree索引	不支持	支持	不支持
Full-text索引	5.6版本之后支持	支持	不支持

平常說(shuō)的索引, 如果沒(méi)有特別指明, 都是指B+樹(shù)(多路搜索樹(shù), 并不一定是二叉)結(jié)構(gòu)組織的索引. 其中聚集索引、復(fù)合索引、前綴索引、唯一索引默認(rèn)都是使用B+tree樹(shù)索引, 統(tǒng)稱(chēng)為索引.

BTREE結(jié)構(gòu)

BTree又叫多路平衡搜索樹(shù), 一顆M叉的BTree特性如下:

• 樹(shù)中每個(gè)節(jié)點(diǎn)最多包含m個(gè)孩子
• 除根節(jié)點(diǎn)與葉子節(jié)點(diǎn)外, 每個(gè)節(jié)點(diǎn)至少有[ceil(m/2)]個(gè)孩子
• 若根節(jié)點(diǎn)不是葉子節(jié)點(diǎn), 至少有倆個(gè)孩子
• 所有的葉子節(jié)點(diǎn)都在同一層
• 每個(gè)非葉子節(jié)點(diǎn)由n個(gè)key與n+1個(gè)指針組成, 其中[ceil(m/2)-1] <= n <= m-1

以5叉BTree樹(shù)為例, key的數(shù)量 : 公式推導(dǎo)[ceil(m/2)-1] <= n <= m-1. 所以 2 <= n <=4. 當(dāng)n>4時(shí),中間節(jié)點(diǎn)分裂到父節(jié)點(diǎn), 倆邊的節(jié)點(diǎn)分裂.

B+TREE結(jié)構(gòu)

B+Tree為BTree的變種, B+Tree與BTree的區(qū)別為:

1. n叉B+Tree最多含有n個(gè)key. 而B(niǎo)Tree最多含有n-1個(gè)key.
2. B+Tree的葉子節(jié)點(diǎn)保存所有key信息, 依key大小順序排列.
3. 所有的非葉子節(jié)點(diǎn)多可以看作是key的索引部分.
   
   由于B+Tree只有葉子節(jié)點(diǎn)保存key信息, 查詢?nèi)魏蝛ey都要從root走到葉子節(jié)點(diǎn). 所以B+Tree的查詢效率更加穩(wěn)定.

MySql中的B+Tree

MySql索引數(shù)據(jù)結(jié)構(gòu)對(duì)經(jīng)典的B+Tree進(jìn)行了優(yōu)化. 在原B+Tree的基礎(chǔ)上, 增加了一個(gè)指向相鄰葉子節(jié)點(diǎn)的鏈表指針, 就形成了帶有順序指針的B+Tree, 提高區(qū)間訪問(wèn)的性能.

索引分類(lèi)

1. 單值索引 : 即一個(gè)索引只包含單個(gè)列, 一個(gè)表可以有多個(gè)單列索引
2. 唯一索引 : 索引列的值必須唯一, 但允許有空值(NULL可以出現(xiàn)多次)
3. 復(fù)合索引 : 即一個(gè)索引包含多個(gè)列

創(chuàng)建索引

-- 字段名為多個(gè)時(shí),創(chuàng)建為復(fù)合索引
create [UNIQUE|FULLTEXT|SPATIAL] index 索引名稱(chēng) [USING index_type] on 表名(字段名...);

示例: create index inx_name on student(name);

查看索引

show index from 表名;

刪除索引

drop index 索引名稱(chēng) on 表名;

ALTER(alter)命令

-- 添加主鍵
alter table 表名 add primary key(字段名);
-- 添加唯一主鍵(NULL可以出現(xiàn)多次)
alter table 表名 add unique 索引名稱(chēng)(字段名);
-- 添加普通索引
alter table 表名 add index 索引名稱(chēng)(字段名);
-- 指定索引為FULLTEXT,用于全文索引
alter table 表名 add fulltext 索引名稱(chēng)(字段名);

索引設(shè)計(jì)原則

索引的設(shè)計(jì)可以遵循一些已有的規(guī)則, 創(chuàng)建索引的時(shí)候請(qǐng)盡量考慮符合這些原則, 便于提升索引的使用效率, 更高效的使用索引.

• 對(duì)查詢頻次較高, 且數(shù)據(jù)量比較大的表建立索引.
• 索引字段的選擇, 最佳候選列應(yīng)當(dāng)從where子句(查詢條件)中提取, 如果where子句的組合比較多, 那么應(yīng)當(dāng)挑選最常用, 過(guò)濾效果最好的列的組合.
• 使用唯一索引, 區(qū)分度越高, 使用索引的效率越高.
• 索引可以有效的提升查詢數(shù)據(jù)的效率, 但索引的數(shù)量不是越多越好, 索引越多, 增刪時(shí)維護(hù)索引難度也水漲船高
• 使用短索引, 索引創(chuàng)建之后也是使用硬盤(pán)來(lái)存儲(chǔ)的, 因此提升索引訪問(wèn)的I/O效率, 也可以提升總體的訪問(wèn)效率. 假如構(gòu)成索引的字段總長(zhǎng)度比較短, name在給定大小的存儲(chǔ)塊內(nèi)可以存儲(chǔ)更多的索引值, 相應(yīng)的可以有效的提升MySql訪問(wèn)索引的I/O效率.
• 利用最左前綴, N個(gè)列組合而成的組合索引, name相當(dāng)于創(chuàng)建了N個(gè)索引, 如果查詢時(shí)where子句中使用了組合索引的前幾個(gè)字段, name這條查詢SQL可以利用組合索引來(lái)提升查詢效率.

視圖

視圖(View) 是一種虛擬存在的表. 視圖在數(shù)據(jù)庫(kù)中并不是真實(shí)存在的, 行和列的數(shù)據(jù)來(lái)源自定義查詢中的表, 并在使用視圖時(shí)動(dòng)態(tài)生成. 通俗的講, 視圖就是一條SELECT語(yǔ)句執(zhí)行返回的結(jié)果集. 所以創(chuàng)建視圖時(shí), 重點(diǎn)是創(chuàng)建這條SQL的查詢語(yǔ)句上.

• 簡(jiǎn)單 : 不需要關(guān)注視圖背后對(duì)應(yīng)的表結(jié)構(gòu)、關(guān)聯(lián)條件和篩查條件, 對(duì)使用視圖來(lái)說(shuō)視圖已經(jīng)是過(guò)濾好的復(fù)合條件的結(jié)果集.
• 安全 : 使用視圖只允許訪問(wèn)視圖返回的結(jié)果集, 對(duì)表的權(quán)限管理可以限制到某行某列.
• 數(shù)據(jù)獨(dú)立 : 視圖一旦查詢語(yǔ)句確定, 源表結(jié)構(gòu)變化對(duì)視圖沒(méi)有影響, 源表修改列名, 修改視圖便好, 不會(huì)對(duì)源表有影響.

創(chuàng)建視圖

create [or replace] [algorithm = {undefined | merge | temptable}]
view 視圖名稱(chēng)[(column_list)]
as 查詢語(yǔ)句
[with [cascaded | local] check option]

修改視圖

alter [algorithm = {undefined | merge | temptable}]
view 視圖名稱(chēng)[(column_list)]
as 查詢語(yǔ)句
[with [cascaded | local] check option]

選項(xiàng)說(shuō)明

with [cascaded | local] check option 決定了是否允許更新數(shù)據(jù)使記錄不再滿足視圖的條件
local : 只要滿足視圖條件就可以更新
cascaded(默認(rèn)值) : 必須滿足所有針對(duì)該視圖的所有視圖條件才可以更新.

刪除視圖

drop view [if exists] 視圖名稱(chēng)...[restrict | cascade]

存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)

概述

存儲(chǔ)過(guò)程和函數(shù)是事先經(jīng)過(guò)編譯并存儲(chǔ)在數(shù)據(jù)庫(kù)中的一段SQL語(yǔ)句集合, 調(diào)用存儲(chǔ)過(guò)程和函數(shù)可以簡(jiǎn)化開(kāi)發(fā), 減少數(shù)據(jù)庫(kù)和應(yīng)用之間的傳輸, 對(duì)于提高數(shù)據(jù)處理效率有一定的好處.
存儲(chǔ)過(guò)程和函數(shù)的區(qū)別在于函數(shù)必須喲返回值,而存儲(chǔ)過(guò)程沒(méi)有.
函數(shù) : 是一個(gè)有返回值得過(guò)程
過(guò)程 : 是一個(gè)沒(méi)有返回值的函數(shù)

創(chuàng)建存儲(chǔ)過(guò)程

create procedure 存儲(chǔ)過(guò)程名稱(chēng)([proc_parameter(...)])
begin-- SQL語(yǔ)句
end;

創(chuàng)建存儲(chǔ)過(guò)程中使用SQL語(yǔ)句分隔符,可能會(huì)報(bào)錯(cuò),所以使用delimiter關(guān)鍵字修改SQL分隔符

調(diào)用存儲(chǔ)過(guò)程

call 存儲(chǔ)過(guò)程名稱(chēng)();

查看存儲(chǔ)過(guò)程

-- 查詢數(shù)據(jù)庫(kù)中的所有存儲(chǔ)過(guò)程
select name from mysql.proc where db='數(shù)據(jù)庫(kù)名稱(chēng)'
-- 查詢存儲(chǔ)過(guò)程的狀態(tài)信息
show procedure status;
-- 查詢某個(gè)存儲(chǔ)過(guò)程的定義
show create procedure 數(shù)據(jù)庫(kù)名.存儲(chǔ)過(guò)程名稱(chēng);

刪除存儲(chǔ)過(guò)程

drop procedure [if exists] 存儲(chǔ)過(guò)程名稱(chēng);

語(yǔ)法

存儲(chǔ)過(guò)程是可以編程的, 意味著可以使用變量、表達(dá)式、控制結(jié)構(gòu)來(lái)完成比較復(fù)雜的功能.

變量DECLARE

• DELARE
  通過(guò)declare可以定義一個(gè)局部變量, 該變量作用范圍只能在begin…end塊中.

declare 變量名稱(chēng)[...] 變量類(lèi)型 [default 默認(rèn)值]

示例 :

create 存儲(chǔ)名稱(chēng)()
begindeclare a int default 3;select a + 10;
end;

• SET

直接賦值用SET, 可以賦常量活表達(dá)式

set 變量名 = 值[...]

示例 :

create procedure 存儲(chǔ)過(guò)程名稱(chēng)()
begindeclare a int;set a = 10;select a;
end;

• select … into 賦值

create procedure 存儲(chǔ)過(guò)程名稱(chēng)()
begindeclare a int;select count(id) into a from 數(shù)據(jù)庫(kù)表;select a;
end;

if條件判斷

if 條件語(yǔ)句 then 業(yè)務(wù)邏輯
[elseif 判斷語(yǔ)句 then 業(yè)務(wù)邏輯] ...
[else 業(yè)務(wù)邏輯]
end if;

示例 :

create procedure 存儲(chǔ)過(guò)程名稱(chēng)()
begindeclare a int default 175;declare b varchar(32) default '';if a >= 180 thenset b = '高';elseif a >= 170 and a < 180 thenset b = '中'elseset b = '低'end if;
end; 

傳遞參數(shù)

create procedure 存儲(chǔ)過(guò)程名稱(chēng)([in/out/inout] 參數(shù)名 參數(shù)類(lèi)型)
...
in : 該參數(shù)作為輸入?yún)?shù),默認(rèn)
out: 該參數(shù)作為輸出參數(shù)
inout: 既可以作為輸入?yún)?shù),也可以作為輸出參數(shù)

in-輸入?yún)?shù)

示例 :

create procedure 存儲(chǔ)過(guò)程名稱(chēng)(in a int)
begindeclare b varchar(10) default '';if a >= 180 thenset b = '高';elseif a >= 170 and a < 180 thenset b = '中';elseset b = '低';end if;
end;

out-輸出參數(shù)

示例 :

create procedure 存儲(chǔ)過(guò)程名稱(chēng)(in a int,out b varchar(8))
beginif a >= 180 thenset b = '高';elseif a >=170 and a< 180 thenset b = '中';elseset b = '低';end if;
end;

調(diào)用:

call 存儲(chǔ)過(guò)程名稱(chēng)(168,@b);
select @b;

@變量名稱(chēng) : 這種變量前面加上"@" 符,叫用戶會(huì)話變量, 代表整個(gè)會(huì)話過(guò)程都有作用,類(lèi)似全局變量
@@變量名稱(chēng) : 這種變量前加"@@"符, 叫系統(tǒng)變量

case結(jié)構(gòu)

-- 方式一:
case 變量when 判斷值的判斷語(yǔ)句 then 為true執(zhí)行業(yè)務(wù)邏輯[when 判斷值的判斷語(yǔ)句 then 為true執(zhí)行業(yè)務(wù)邏輯] ...[else 上面條件全部不滿足執(zhí)行的業(yè)務(wù)邏輯]
end case;
-- 方式二:
casewhen 條件判斷語(yǔ)句 then 為true的業(yè)務(wù)邏輯[when 條件判斷語(yǔ)句 then 為true的業(yè)務(wù)邏輯]...[else 上面條件不滿足所執(zhí)行的業(yè)務(wù)邏輯]
end case;

示例 :

create procedure 存儲(chǔ)過(guò)程名稱(chēng)(mon int)
begindeclare result varchar(8);casewhen mon > 0 and mon <= 3 thenset result = '一';when mon > 3 and mon <= 6 thenset result = '二';when mon > 6 and mon <= 9 thenset reuslt = '三';else set result = '四';end case;
end;

while循環(huán)

while 判斷條件 do循環(huán)體
end while;

示例:

create procedure 存儲(chǔ)過(guò)程名稱(chēng)(n int)
begindeclare total int default 0;declare num int default 1;while num <= n doset total = total + num;set num = num + 1;end while;
end;

repeat循環(huán)

有條件的循環(huán)控制語(yǔ)句, 當(dāng)滿足條件時(shí)退出循環(huán). while滿足條件才執(zhí)行, repeat是滿足條件才退出

repeat循環(huán)體
unitl 退出循環(huán)條件(這里不加sql語(yǔ)句結(jié)束符)
end repeat;

示例 :

create procedure 存儲(chǔ)過(guò)程名稱(chēng)(n int)
begindeclare total int default 0;repeatset total = total + n;set n = n - 1;until n = 0end repeat;
end;

loop循環(huán)

loop實(shí)現(xiàn)簡(jiǎn)單的循環(huán), 退出循環(huán)的條件需要其他的語(yǔ)句定義, 通常使用leave語(yǔ)句實(shí)現(xiàn)

[loop別名:]loop循環(huán)體
end loop [loop別名]

leave語(yǔ)句

示例:

create procedure 存儲(chǔ)過(guò)程名稱(chēng)(n int)
begindeclare total int default 0;c:loopset total = total + n;set n = n - 1;if n <= 0 thenleave c;end if;end loop c;
end;

游標(biāo)/光標(biāo)

游標(biāo)是用來(lái)存儲(chǔ)查詢結(jié)果集的數(shù)據(jù)類(lèi)型, 再存儲(chǔ)過(guò)程和函數(shù)可以使用光標(biāo)對(duì)結(jié)果集進(jìn)行循環(huán)處理.
聲明光標(biāo):

declare 光標(biāo)名稱(chēng) cursor for 查詢語(yǔ)句;

open(打開(kāi))光標(biāo):

open 光標(biāo)名稱(chēng);

fetch(遍歷)光標(biāo):

fetch 光標(biāo)名稱(chēng) into var_name [,var_name]...

close(關(guān)閉)光標(biāo):

close 光標(biāo)名稱(chēng);

示例:

create procedure 存儲(chǔ)過(guò)程名稱(chēng)()
begindeclare e_id int(11);declare e_name varchar(32);declare e_age int(11);declare e_salary int(11);declare has_data int default 1;declare 光標(biāo)名稱(chēng) cursor for select * from a;declare exit handler for not found set has_data = 0;open 光標(biāo)名稱(chēng);repeatfetch 光標(biāo)名稱(chēng) into e_id,e_name,e_age,e_salary;select concat(e_id,e_name,e_age,e_salary);until has_data = 0end repeat;close 光標(biāo)名稱(chēng);
end;

存儲(chǔ)函數(shù)

create function 存儲(chǔ)函數(shù)名稱(chēng)([變量名 變量類(lèi)型...])
returns 返回值類(lèi)型
begin...
end;

示例:

create function 函數(shù)名稱(chēng)(a int)
returns int
begindeclare num int;select count(id) into num from city where country_id = a;return num;
end;

觸發(fā)器

觸發(fā)器是與表有關(guān)的數(shù)據(jù)庫(kù)對(duì)象, 指在insert/update/delete之前或之后, 觸發(fā)并執(zhí)行觸發(fā)器中定義的sql語(yǔ)句集合.

使用別名OLD和NEW來(lái)引用觸發(fā)器中發(fā)生變化的記錄內(nèi)容.

觸發(fā)器類(lèi)型	NEW和OLD的使用
INSERT型觸發(fā)器	NEW表示將要或者已經(jīng)新增的數(shù)據(jù)
UPDATE型觸發(fā)器	OLD表示修改之前的數(shù)據(jù), NEW表示將要或已經(jīng)修改后的數(shù)據(jù)
DELETE型觸發(fā)器	OLD表示將要或者已經(jīng)刪除的數(shù)據(jù)

創(chuàng)建觸發(fā)器

create trigger 觸發(fā)器名稱(chēng)
before/after insert/update/delete
on 表名
[for each row]
begin... -- 獲取新數(shù)據(jù) new.id 獲取舊數(shù)據(jù)lod.id
end;

查看觸發(fā)器

show triggers;

刪除觸發(fā)器

drop trigger [數(shù)據(jù)庫(kù)名.]觸發(fā)器名稱(chēng);

MySQL體系結(jié)構(gòu)

整個(gè)MySql Server由以下組成:

• Connection Pool : 連接池組件(認(rèn)證、連接最大數(shù)、緩存的一些設(shè)置)
• Management Services & Utilities: 管理服務(wù)和工具組件(數(shù)據(jù)備份恢復(fù)、數(shù)據(jù)安全、系統(tǒng)配置等)
• SQL Interface : Sql接口組件(sql語(yǔ)句CRUD、視圖、存儲(chǔ)過(guò)程、函數(shù)等)
• Parser : 查詢分析器組件(分析過(guò)濾Sql語(yǔ)句)
• Optimizer : 優(yōu)化器組件(MySql內(nèi)部對(duì)解析后的sql進(jìn)行優(yōu)化)
• Caches & Buffers : 緩沖池組件(看當(dāng)前sql語(yǔ)句是否有緩存數(shù)據(jù),有直接返回緩存數(shù)據(jù),沒(méi)有繼續(xù)往下走)
• Pluggable Storage Engines : 存儲(chǔ)引擎(根據(jù)不同業(yè)務(wù)設(shè)置不同存儲(chǔ)引擎)
• File System : 文件系統(tǒng)(具體數(shù)據(jù)存放位置)

1. 連接層
   最上層是一些客戶端和鏈接服務(wù)，包含本地sock通信和大多數(shù)基于客戶端/服務(wù)端工具實(shí)現(xiàn)的類(lèi)似于TCP/IP的通信。主要完成一些類(lèi)似與連接處理、授權(quán)認(rèn)證及相關(guān)的安全方案。在該層上引入線程池的概念，為通過(guò)認(rèn)證安全接入的客戶端提供線程。同樣再該層上可以實(shí)現(xiàn)基于SSL的安全鏈接。服務(wù)器也會(huì)為安全接入的每個(gè)客戶端驗(yàn)證它所具有的操作權(quán)限。
2. 服務(wù)層
   第二層架構(gòu)主要完成大多數(shù)的核心服務(wù)功能，如Sql接口，完成緩存的查詢，Sql分析和優(yōu)化，部分內(nèi)置函數(shù)的執(zhí)行。所有跨存儲(chǔ)引擎的功能也在這層實(shí)現(xiàn)（如過(guò)程、函數(shù)等）。服務(wù)器會(huì)解析查詢并創(chuàng)建相應(yīng)的內(nèi)部解析樹(shù)，并對(duì)其完成相應(yīng)的優(yōu)化（如確定表的查詢順序、是否利用索引等）。最后生成相應(yīng)的執(zhí)行操作
3. 引擎層
   存儲(chǔ)引擎負(fù)責(zé)了MySql中數(shù)據(jù)的存儲(chǔ)和提取，服務(wù)器通過(guò)API和存儲(chǔ)引擎進(jìn)行通信。
4. 存儲(chǔ)層
   主要將數(shù)據(jù)存儲(chǔ)到文件系統(tǒng)上，并完成與存儲(chǔ)引擎的交互

和其他數(shù)據(jù)庫(kù)相比,MySql有點(diǎn)與眾不同,MySql插件式存儲(chǔ)引擎架構(gòu)可以在多種不同場(chǎng)景中應(yīng)用并發(fā)揮良好作用. 將查詢處理和其他的系統(tǒng)任務(wù)及數(shù)據(jù)的存儲(chǔ)提取分離. 這種架構(gòu)可以根據(jù)業(yè)務(wù)的需求和實(shí)際需要選擇合適的存儲(chǔ)引擎

存儲(chǔ)引擎

概述

存儲(chǔ)引擎就是存儲(chǔ)數(shù)據(jù), 建立索引, 更新查詢數(shù)據(jù)等技術(shù)的實(shí)現(xiàn)方式. 存儲(chǔ)引擎是基于 表 的,而不是基于庫(kù)的.所以存儲(chǔ)引擎也可被稱(chēng)為表類(lèi)型

Oracle，SqlServer等數(shù)據(jù)庫(kù)只有一種存儲(chǔ)引擎。MySql提供了插件式的存儲(chǔ)引擎架構(gòu)。

MySql5.0支持的存儲(chǔ)引擎包含 : InnoDB、MyISAM、BDB、MEMORY、MERGE、EXAMPLE、NDBCluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事務(wù)安全表，其他存儲(chǔ)引擎是非事務(wù)安全表
可以通過(guò)指定show engines,查詢當(dāng)前數(shù)據(jù)庫(kù)支持的存儲(chǔ)引擎:

通過(guò)指令show variables like '%storage_engine%', 查看當(dāng)前服務(wù)器默認(rèn)存儲(chǔ)引擎

存儲(chǔ)引擎特性

特點(diǎn)	InnoDB	MyISAM	MEMORY	MERGE	NDB
存儲(chǔ)限制	64TB	有	有	沒(méi)有	有
事務(wù)安全	支持
鎖機(jī)制	行鎖(支持高并發(fā))	表鎖	表鎖	表鎖	表鎖
B樹(shù)索引	支持	支持	支持	支持	支持
哈希索引			支持
全文索引	支持(5.6版本之后)	支持
集群索引	支持
數(shù)據(jù)索引	支持		支持		支持
索引緩存	支持	支持	支持	支持
數(shù)據(jù)可壓縮		支持
空間使用	高	低	N/A	低	低
內(nèi)存使用	高	低	中等	低	高
批量插入速度	低	高	高	高	高
支持外鍵	支持

InnoDB

InnoDB存儲(chǔ)引擎是MySql的默認(rèn)存儲(chǔ)引擎。InnoDB存儲(chǔ)引擎提供了具有提交、回滾、奔潰恢復(fù)的事務(wù)安全。但對(duì)比MyISAM的存儲(chǔ)引擎，InnoDB寫(xiě)的處理效率差些，并會(huì)占用更多的磁盤(pán)空間以保留數(shù)據(jù)和索引。

InnoDB存儲(chǔ)引擎不同于其他存儲(chǔ)引擎的特點(diǎn) ：
事務(wù)控制

外鍵約束
MySql支持外鍵的存儲(chǔ)引擎只有InnoDB，在創(chuàng)建外鍵的時(shí)候，要求父表必須有對(duì)應(yīng)的索引，指標(biāo)在創(chuàng)建外鍵的時(shí)候，也會(huì)自動(dòng)創(chuàng)建對(duì)應(yīng)的索引。
存儲(chǔ)方式
InnoDB 存儲(chǔ)表和索引有倆種方式：

1. 使用共享表空間存儲(chǔ)，這種方式創(chuàng)建的表的表結(jié)構(gòu)保存再.frm文件中，數(shù)據(jù)和索引保存再innodb_data_home_dir和innodb_data_file_path定義的表空間中，可以是多個(gè)文件
2. 使用多表空間存儲(chǔ)，這種創(chuàng)建的表的表結(jié)構(gòu)存在.frm文件中，但表的數(shù)據(jù)和索引單獨(dú)保存再.ibd文件中。

MyISAM

MyISAM不支持事務(wù)，也不支持外鍵，優(yōu)勢(shì)就是訪問(wèn)速度快，對(duì)事務(wù)的完整性沒(méi)有要求或者以SELECT、INSERT為主的應(yīng)用基本上可以使用這個(gè)引擎來(lái)創(chuàng)建表。

不支持事務(wù)
文件存儲(chǔ)方式
每個(gè)MyISAM再磁盤(pán)上存儲(chǔ)成3個(gè)文件，其文件名和表名相同，擴(kuò)展名分別為：

• .frm（表結(jié)構(gòu)）
• .MYD（MYData，存儲(chǔ)數(shù)據(jù)）
• .MYI（MYIndex，存儲(chǔ)索引）

MEMOEY（Memory）

 Memory存儲(chǔ)引擎將表的數(shù)據(jù)存放到內(nèi)存中。每個(gè)Memory表實(shí)際對(duì)應(yīng)一個(gè)磁盤(pán)文件（.frm），該文件只存儲(chǔ)表結(jié)構(gòu)，數(shù)據(jù)文件存儲(chǔ)到內(nèi)存中，有利于數(shù)據(jù)的快速處理，提高整個(gè)表的效率。Memory類(lèi)型的表默認(rèn)使用HASH索引，因?yàn)閿?shù)據(jù)存放在內(nèi)存中，所以服務(wù)器一旦關(guān)閉，數(shù)據(jù)就會(huì)丟失。

MERGE（Merge）

Merge存儲(chǔ)引擎是一組MyISAM表的組合，這些MyISAM表必須結(jié)構(gòu)完全相同，Merge表本身并沒(méi)有存儲(chǔ)數(shù)據(jù)，對(duì)Merge類(lèi)型的表可以進(jìn)行查詢、更新、刪除操作，這些操作實(shí)際都是對(duì)內(nèi)部的MyISAM表進(jìn)行的。
對(duì)于Merge類(lèi)型表的插入操作，是通過(guò)INSERT_METHOD子句定義插入的表，可以有3個(gè)不同的值，使用FIRST或LAST值使插入操作被相應(yīng)的作用在第一或最后一個(gè)表上，不定義這個(gè)子句或者定義為NO，表示不能對(duì)這個(gè)Merge表執(zhí)行插入操作。
可以對(duì)Merge表進(jìn)行drop操作，但這個(gè)操作只是刪除Merge表的定義，對(duì)內(nèi)部的表是沒(méi)有任何影響的。

存儲(chǔ)引擎的選擇

在選擇存儲(chǔ)引擎時(shí)，根據(jù)應(yīng)用系統(tǒng)的特點(diǎn)選擇合適的存儲(chǔ)引擎。對(duì)于復(fù)雜的應(yīng)用系統(tǒng)，根據(jù)實(shí)際情況選擇多種存儲(chǔ)引擎進(jìn)行組合。

• InnoDB：是MySql的默認(rèn)存儲(chǔ)引擎，用于事務(wù)處理應(yīng)用程序，支持外鍵。如果應(yīng)用多事務(wù)的完整性有較高要求，再并發(fā)調(diào)價(jià)下要求數(shù)據(jù)一致性，數(shù)據(jù)操作出來(lái)插入和查詢以外，還有很多更新、刪除操作，那么InnoDB存儲(chǔ)引擎是比較合適的選擇。InnoDB存儲(chǔ)引擎除了有效降低由于刪除和更新導(dǎo)致的鎖定，還可以確保事務(wù)的完整提交和回滾，對(duì)于類(lèi)似于計(jì)費(fèi)系統(tǒng)或者財(cái)務(wù)系統(tǒng)等對(duì)數(shù)據(jù)準(zhǔn)確性要求比較高的系統(tǒng)，InnoDB是最合適的選擇。
• MyISAM：如果應(yīng)用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并對(duì)事務(wù)的完整性，并發(fā)要求不是很高，選擇這個(gè)存儲(chǔ)引擎是非常合適的。
• MEMORY（Memory）：將所有數(shù)據(jù)保存在RAM中，在需要快速定位記錄和其他類(lèi)似數(shù)據(jù)環(huán)境下，可以提供快速的訪問(wèn)。Memory的缺陷是對(duì)表的大小有限制，太大的表無(wú)法緩存到內(nèi)存中，其次要確保表的數(shù)據(jù)可以恢復(fù)，數(shù)據(jù)庫(kù)異常終止后表中數(shù)據(jù)是可以恢復(fù)的。Memory表通常用于更新不太頻繁的小表，用以快速得到訪問(wèn)結(jié)果。
• MERGE（Merge）：用于將一系列等同的MyISAM表已邏輯方式組合在一起，并作為一個(gè)對(duì)象引用他們。Merge表的優(yōu)點(diǎn)在于突破對(duì)單個(gè)MyISAM表的大小限制，將不同的表分布在多個(gè)磁盤(pán)上，有效改善Merge表的訪問(wèn)效率。對(duì)于存儲(chǔ)如數(shù)據(jù)倉(cāng)儲(chǔ)等VLDB環(huán)境十分合適。

SQL優(yōu)化

查看SQL執(zhí)行頻率

MySql客戶端連接成功后，通過(guò)show [session|global] status 命令提供服務(wù)器狀態(tài)信息。show [session|global] status 可以根據(jù)需要加上參數(shù)session或global來(lái)顯示session級(jí)(當(dāng)前連接)的統(tǒng)計(jì)結(jié)果和global級(jí)(自數(shù)據(jù)庫(kù)上次啟動(dòng)至今)的統(tǒng)計(jì)結(jié)果。默認(rèn)使用session。
顯示當(dāng)前session中所有統(tǒng)計(jì)參數(shù)的值：

show status like 'Com_______'; -- 下劃線7個(gè)

show status like 'Innodb_rows_%';

定位低效率執(zhí)行SQL

• 慢查詢?nèi)罩?#xff1a;通過(guò)慢查詢?nèi)罩径ㄎ荒切﹫?zhí)行效率低的SQL語(yǔ)句，用--log-slow-queries[=file_name]選項(xiàng)啟動(dòng)時(shí)，mySqld寫(xiě)一個(gè)包含所有執(zhí)行時(shí)間超過(guò)long_query_time秒的Sql語(yǔ)句的日志文件
• show processlist ：慢查詢?nèi)罩驹诓樵兘Y(jié)束之后才記錄，所以在應(yīng)用反映執(zhí)行效率出現(xiàn)問(wèn)題的時(shí)候查詢?nèi)罩静荒芏ㄎ粏?wèn)題，可以使用show processlist命令查看當(dāng)前MySql在進(jìn)行的線程，包括線程狀態(tài)、是否鎖表等，可以實(shí)時(shí)查看Sql的執(zhí)行情況，同時(shí)對(duì)鎖表操作進(jìn)行優(yōu)化。
  

列	說(shuō)明
ID	用戶登錄MySql時(shí)，系統(tǒng)分配的‘connection_id’，可以使用函數(shù)connecton_id()查看
User	顯示當(dāng)前用戶，如果不是root，這個(gè)命令只顯示用戶權(quán)限范圍的sql語(yǔ)句
Host	顯示ip地址和端口，可以用來(lái)跟蹤出現(xiàn)問(wèn)題語(yǔ)句的客戶
db	顯示連接的那個(gè)數(shù)據(jù)庫(kù)
Command	顯示當(dāng)前連接執(zhí)行的命令，一般值為休眠(sleep)、查詢(query)、連接(connect)等
Time	狀態(tài)持續(xù)時(shí)間(單位秒)
State	顯示當(dāng)前連接的Sql語(yǔ)句狀態(tài),已查詢?yōu)槔?可能需要copyiing to tpm table、sorting result、sending data等才能完成)
info	顯示Sql語(yǔ)句，判斷問(wèn)題的重要依據(jù)

EXPLAIN(explain)分析執(zhí)行計(jì)劃

通過(guò)EXPLAIN或者DESC命令獲取MySql如何執(zhí)行的select語(yǔ)句信息，包括select語(yǔ)句執(zhí)行過(guò)程中表如何連接及連接順序。

字段	含義
id	select查詢的序列號(hào)，是一組數(shù)字，表示的是查詢中執(zhí)行select子句或者操作表的順序
select_type	表示select類(lèi)型，常見(jiàn)的取值有SLMPLE(簡(jiǎn)單表，即不使用表連接或子查詢)、PRIMARY(主查詢，即外層查詢)、UNION(UNION中第二個(gè)或者后面的查詢語(yǔ)句)、SUBUERY(子查詢中第一個(gè)select)等
table	輸出結(jié)果集的表
type	表的連接類(lèi)型，性能由好到差的連接類(lèi)型為(system -> const -> eq_ref ->ref_or_null -> index_merge -> index_subquery -> range -> index -> all)
possible_keys	查詢時(shí), 可能使用的索引
key	實(shí)際使用的索引
key_len	索引字段的長(zhǎng)度
rows	掃描行的數(shù)量
extra	執(zhí)行情況的說(shuō)明和描述

ID列

id字段是select查詢的序列號(hào)，是一組數(shù)字，表示的是查詢中執(zhí)行select子句或者操作表的順序。

• id相同表示加載表的順序是從上到下
• id不同，值越大，優(yōu)先級(jí)越高，越優(yōu)先被執(zhí)行
• id有相同，也有不同，同時(shí)存在。id相同的可以看成一組，從上到下順序執(zhí)行；所有組中，值越大，優(yōu)先級(jí)越高，越先執(zhí)行

select_type列

select_type	含義
SIMPLE	簡(jiǎn)單的select查詢，查詢中不包含子查詢或者UNION
PRIMARY	查詢中若包含任何復(fù)雜的子查詢，最外層查詢標(biāo)記為該標(biāo)識(shí)
SUBQUERY	在select或where列表中包含子查詢
EDRIVED	在from列表中包含的子查詢，被標(biāo)記為DERIVED(衍生)MySql會(huì)遞歸執(zhí)行子查詢，吧結(jié)果放到臨時(shí)表中
UNION	若第二個(gè)select出現(xiàn)在UNION之后，則標(biāo)記為UNION；若UNION包含from子句的子查詢中，外層select將標(biāo)記為：DERIVED
UNION RESILT	從UNION表獲取結(jié)果的select

table列

展示這行的數(shù)據(jù)關(guān)于哪張表的

type列

type顯示的是訪問(wèn)類(lèi)型，是最重要的一個(gè)指標(biāo)

type	含義
NULL	MySql不訪問(wèn)任何表，索引，直接返回結(jié)果
system	表只有一行記錄(等于系統(tǒng)表)，這是const類(lèi)型的特例，一般不會(huì)出現(xiàn)
const	表示通過(guò)索引一次就能找到，const用于比較primary key或unique索引。因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快。如：將主鍵置于where列表中，MySql就能將該查詢轉(zhuǎn)換為一個(gè)常量。const將“主鍵”或“唯一”索引的所有部分與常量進(jìn)行比較
eq_ref	類(lèi)似ref，區(qū)別在于使用的是唯一索引，使用主鍵的關(guān)聯(lián)查詢，關(guān)聯(lián)查詢出的記錄只有一條。常見(jiàn)于主鍵或唯一索引掃描
ref	非唯一性索引掃描，返回匹配某個(gè)單獨(dú)的所有行。本質(zhì)上也是一種索引訪問(wèn)，返回所有匹配某個(gè)單獨(dú)值得所有行
range（MySql查詢的最低標(biāo)準(zhǔn)）	只檢索給定返回的行，使用一個(gè)索引來(lái)選擇行。where之后出現(xiàn)的between、<、>、in等操作
index	index與all的區(qū)別為 index類(lèi)型只遍歷索引，比all快，all是遍歷所有數(shù)據(jù)
all	將遍歷全表以找到匹配的行

結(jié)果值從最好到最壞依次是：

NUll > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > Allsystem > const > eq_ref > ref > range > index > all

多個(gè)key列

possible_keys：顯示可能應(yīng)用這張表的索引，一個(gè)或多個(gè)
key：實(shí)際使用的索引，如果為null，則沒(méi)用使用索引
key_len：表示索引中使用的字節(jié)數(shù)，該值為索引字段最大可能長(zhǎng)度，并非實(shí)際使用長(zhǎng)度，在不損失精確性的前提下，長(zhǎng)度越短越好

rows列

掃描行的數(shù)量

extra列

其他的額外執(zhí)行計(jì)劃信息

extra	含義
using filesort	mysql會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序讀取，稱(chēng)為“文件排序”；效率低
using temporary	使用了臨時(shí)表保存中間結(jié)果，mysql對(duì)查詢結(jié)果排序時(shí)使用臨時(shí)表。常見(jiàn)order by和group by；效率低
using index	表示相應(yīng)的select操作使用了覆蓋索引，避免訪問(wèn)表的數(shù)據(jù)行；效率一般
using where	在查找是哦用索引的情況下, 需要回表去查詢所需數(shù)據(jù)
using index condition	查找使用了索引, 需要回表查詢數(shù)據(jù)
using index; using where	查詢使用了索引,需要的數(shù)據(jù)再索引列中, 無(wú)需回表查詢數(shù)據(jù)

show profile 分析sql

MySql 從5.0.37版本開(kāi)始增加了對(duì)show profiles和show profile語(yǔ)句支持。

通過(guò)have_profiling參數(shù),看到當(dāng)前MySql是否支持profile
select @@have_profiling

默認(rèn)profiling是關(guān)閉的，可以通過(guò)set語(yǔ)句在session級(jí)別開(kāi)啟profling
select @@profiling

set profiling = 1; -- 開(kāi)啟profiling開(kāi)關(guān)

執(zhí)行show profiles命令,查看sql語(yǔ)句執(zhí)行耗時(shí)

通過(guò)show profile for query query_id語(yǔ)句查看該sql執(zhí)行過(guò)程中每個(gè)線程的狀態(tài)和消耗時(shí)間

TIP : Sending data 狀態(tài)表示MySql線程開(kāi)始訪問(wèn)數(shù)據(jù)并把數(shù)據(jù)返回客戶端，而不是僅僅是返回到客戶端。由于在Sending data狀態(tài)下，MySql線程往往需要大量的磁盤(pán)讀取操作，所以經(jīng)常是整個(gè)查詢中最耗時(shí)的狀態(tài)。

在獲取到最消耗時(shí)間的線程狀態(tài)后，MySql支持進(jìn)一步選擇all、cpu、block io、context weitch、page faults等明細(xì)類(lèi)型查看MySql在使用什么資源上耗費(fèi)了過(guò)高的時(shí)間。

trace分析優(yōu)化器執(zhí)行計(jì)劃

MySql5.6提供了對(duì)sql的跟蹤trace, 通過(guò)trace文件能夠進(jìn)一步了解為什么優(yōu)化器選擇A計(jì)劃,而不是B計(jì)劃.
打卡trace, 設(shè)置格式為JSON,并設(shè)置trace最大能夠使用的內(nèi)存大小,避免解析過(guò)程中因?yàn)槟J(rèn)內(nèi)存過(guò)小而不能夠完整展示.

 set optimizer_trace = 'enabled=on',end_markers_in_json = on;set optimizer_trace_max_mem_size = 1000;

執(zhí)行Sql語(yǔ)句
檢查information_schema.optimizer_trace就可以知道MySql是如何執(zhí)行sql的

select * from information_schema.optimizer_trace\G;

索引的使用

索引是數(shù)據(jù)庫(kù)最常用也是最重要的手段之一, 通過(guò)索引可以解決大多數(shù)MySql的性能優(yōu)化問(wèn)題.

• 最左前綴法則
  多列建索引,要遵守最左前綴法則(查詢從索引的最左前列開(kāi)始,并不跳過(guò)索引中的列).
• 范圍查詢右邊的列, 不能使用索引.
• 不要唉索引列上進(jìn)行運(yùn)算操作, 索引將失效.
• varchar類(lèi)型字符串不加單引號(hào), 索引將失效.
• 盡量使用覆蓋索引(只訪問(wèn)索引的查詢(索引列完全包含查詢列)), 避免select *
  explain工具 sql語(yǔ)句 extra 列具體說(shuō)明
• 用or分隔開(kāi)的條件,如果or前的條件中的列有索引, 后面的列沒(méi)索引, 那么涉及的索引列中的索引不會(huì)被用到(有一列沒(méi)建索引,那么用or連接是不走索引的)
• 以%開(kāi)頭的like模糊查詢,索引失效(以%結(jié)尾的模糊查詢,索引不會(huì)失效)
  通過(guò)覆蓋索引來(lái)解決(返回列為索引列)
• 如果MySql評(píng)估全表比使用索引更快,則不使用索引(當(dāng)某一列創(chuàng)建了索引,且某值占表的比例很大,走全表掃描比走索引查詢更快)
• is null 、is not null 有時(shí)索引失效(看表中數(shù)據(jù)某列null值占比例偏大,is null時(shí)不走索引,is not null 走索引。反之亦然)
• in走索引，not in索引失效
• 單列索引(當(dāng)查詢條件多個(gè)時(shí),每列都創(chuàng)建一個(gè)索引,數(shù)據(jù)庫(kù)會(huì)根據(jù)辨識(shí)度最高的索引來(lái)使用,而不是使用全部索引)和復(fù)合索引(遵守最左前綴法則,多個(gè)列同時(shí)在索引范圍內(nèi)會(huì)使用復(fù)合索引查詢效率更高)

查看索引的使用情況

show status like 'Handler_read%';show global status like 'Handler_read%';

列名	說(shuō)明
Handler_read_first(這個(gè)值越低越好)	索引中第一條被讀的次數(shù), 如果較高,表示服務(wù)器正執(zhí)行大量全索引掃描
Handler_read_key(這個(gè)值越高越好)	如果這個(gè)索引正在工作,這個(gè)值代表一個(gè)行被索引值讀的次數(shù). 如果值越低,表示索引得到的性能改善不高, 因?yàn)樗饕唤?jīng)常使用
Handler_read_last
Handler_read_next	按照鍵順序讀下一行的請(qǐng)求數(shù). 如果你用范圍約束或如果執(zhí)行索引掃描查詢索引列, 該值增加.
Handler_read_prev	按照順序讀前一行的請(qǐng)求數(shù). 該方法主要用于優(yōu)化order by … desc
Handler_read_rnd	按照鍵順序讀前一行的請(qǐng)求數(shù). 如果你正執(zhí)行大量查詢并需要對(duì)結(jié)果進(jìn)行排序該值較高. 你可能使用了大量需要MySql掃描整個(gè)表的查詢或你的連接沒(méi)有正確使用鍵. 這個(gè)值較高,意味著運(yùn)行效率低, 應(yīng)該建立索引補(bǔ)救
Handler_read_rnd_next	在數(shù)據(jù)文件中讀下一行的請(qǐng)求數(shù). 如果你正在進(jìn)行大量的表掃描…該值越高, 說(shuō)明表索引不正確或?qū)懭氲牟樵?/td>

SQL優(yōu)化

大批量插入數(shù)據(jù)

使用load命令導(dǎo)入數(shù)據(jù)時(shí),適當(dāng)?shù)脑O(shè)置可以提高導(dǎo)入效率

load data local infile '文件路徑/文件名.文件格式' into table `表名` fields terminated by ',' lines terminated by '\n';
-- local infile 本地文件系統(tǒng)
-- terminated by ','  字段分隔符
-- terminated by '\n' 行分隔符

對(duì)于InnoDB類(lèi)型的表, 有以下幾種方式可以提高導(dǎo)入效率

1. 主鍵順序插入
   因?yàn)镮nnoDB表是按照主鍵的順序保存的, 所以將導(dǎo)入的數(shù)據(jù)按照主鍵的順序排列, 可以有效的提高導(dǎo)入數(shù)據(jù)的效率. 如果InnoDB表沒(méi)有主鍵,那么系統(tǒng)會(huì)自動(dòng)默認(rèn)創(chuàng)建一個(gè)內(nèi)部列作為主鍵, 所以如果可以給表創(chuàng)建一個(gè)主鍵,將可以利用這點(diǎn), 來(lái)提高導(dǎo)入數(shù)據(jù)的效率.
2. 關(guān)閉唯一性校驗(yàn)
   在導(dǎo)入數(shù)據(jù)前執(zhí)行set unique_checks = 0, 關(guān)閉唯一性校驗(yàn), 在導(dǎo)入結(jié)束后執(zhí)行set unique_checks = 1, 恢復(fù)唯一性校驗(yàn), 可提高導(dǎo)入效率
3. 手動(dòng)提交事務(wù)
   如果應(yīng)用使用自動(dòng)提交的方式, 建議在導(dǎo)入前執(zhí)行set autocommit = 0,關(guān)閉自動(dòng)提交, 導(dǎo)入結(jié)束后在執(zhí)行set autocommit = 1,打開(kāi)自動(dòng)提交,也可以提高導(dǎo)入效率

優(yōu)化insert語(yǔ)句

當(dāng)進(jìn)行數(shù)據(jù)insert操作時(shí), 可以考慮采用以下幾種優(yōu)化方案

1. 同時(shí)對(duì)一張表插入多行數(shù)據(jù)時(shí), 盡量使用多個(gè)值表的insert語(yǔ)句, 可以減少客戶端與數(shù)據(jù)庫(kù)之間的連接/關(guān)閉等消耗. 效率比分開(kāi)單個(gè)執(zhí)行insert語(yǔ)句快
   例:

insert into 表名 values(1,'tom');
insert into 表名 values(2,'cat');
-- 優(yōu)化為:
insert into 表名 values(1,'tom'),(2,'cat');

2. 在事務(wù)中進(jìn)行數(shù)據(jù)插入

start transaction;
insert into 表名 values(1,'tom');
insert into 表名 values(2,'cat');
commit;

3. 數(shù)據(jù)主鍵有序插入

order by優(yōu)化

1. 通過(guò)對(duì)返回?cái)?shù)據(jù)進(jìn)行排序, 也就是通常說(shuō)的filesort排序, 所有不是通過(guò)索引直接返回排序結(jié)果的排序都叫fileSort排序(返回不含索引字段)
2. 通過(guò)有序索引順序掃描直接返回有序數(shù)據(jù), 這種情況即為using index(explain工具中extra列), 不需要額外排序, 操作效率高.(反回字段全部為索引字段)

FileSort的優(yōu)化

通過(guò)創(chuàng)建合適的索引, 能夠減少fileSort的出現(xiàn), 但在某些情況下, 條件限制不能讓fileSort消失, 那就需要加快fileSort的排序操作.

1. 倆次掃描算法 : MySql4.1之前, 使用該方式排序. 首先根據(jù)條件取出排序字段和指針信息, 然后再排序區(qū)sort buffer中排序, 如果sort buffer不夠, 則再臨時(shí)表temporary table 中存儲(chǔ)排序結(jié)果. 完成排序后, 再根據(jù)行指針回表讀取記錄, 該操作可能會(huì)導(dǎo)致大量隨機(jī)I/O操作
2. 一次掃描算法 : 一次性取出滿足條件的所有字段, 然后再排序區(qū)sort buffer 中排序后直接輸出結(jié)果集. 排序時(shí)內(nèi)存開(kāi)銷(xiāo)較大, 但是排序效率比倆次掃描算法要高.

MySql通過(guò)比較系統(tǒng)變量 max_length_for_sort_data的大小和Query語(yǔ)句取出字段總大小, 來(lái)判斷使用那種排序算法,如果max_length_sort_data更大, 那么使用第二種優(yōu)化后的算法, 否則使用第一種
可以適當(dāng)?shù)奶岣?code>sort_buffer_size和max_length_for_sort_data系統(tǒng)變量, 來(lái)增大排序區(qū)的大小, 提高排序的效率.

盡量減少額外的排序, 通過(guò)索引直接返回有序數(shù)據(jù), where條件和order by 使用相同的索引, 且order by的順序和索引順序相同, 且order by的字段都是升序,或降序,否則可能需要額外的操作, 這樣就會(huì)出現(xiàn)fileSort.

group by語(yǔ)句優(yōu)化

由于group by實(shí)際上也同樣會(huì)進(jìn)行排序操作, 且與order by相比, group by主要多了排序后的分組操作.
查詢分組且要避免排序結(jié)果的消耗, 可以執(zhí)行order by null禁止排序

select id,count(id) from 表名 group by age order by null;

也可以對(duì)分組的字段添加索引,提高效率

優(yōu)化OR條件

對(duì)于包含OR的查詢語(yǔ)句, 如果要利用索引, 則OR之間的每個(gè)條件列都必須用到索引, 而且不能使用到復(fù)合索引, 如果沒(méi)有索引, 則考慮增加索引
建議使用union替換or

優(yōu)化分頁(yè)查詢

一般分頁(yè)查詢時(shí), 通過(guò)創(chuàng)建覆蓋索引能夠提高性能. 當(dāng)查詢幾百萬(wàn)頁(yè)后的數(shù)據(jù)時(shí), 分頁(yè)查詢limit查詢的效率就會(huì)下降

優(yōu)化思路一

在索引上完成排序、分頁(yè)操作，最后根據(jù)索引列關(guān)聯(lián)回原表查詢所需要的其他列內(nèi)容。

優(yōu)化思路二

該方案只適用于主鍵自增的表，可以吧limit查詢轉(zhuǎn)換成某個(gè)位置的查詢(對(duì)數(shù)據(jù)要求比較高, 主鍵自增且連續(xù), 避免出現(xiàn)斷數(shù)據(jù)的情況)
例：

select * from 表名 where id > 200000 limit 10;

使用SQL提示

SQL提示, 是優(yōu)化數(shù)據(jù)庫(kù)的一個(gè)重要手段, 簡(jiǎn)單來(lái)說(shuō), 就是在sql語(yǔ)句中加入一些人為的提示來(lái)達(dá)到優(yōu)化操作的目的.

use index

在查詢語(yǔ)句中表名的后面, 添加user index來(lái)提供希望MySql去參考的索引列表, 就可以讓MySql不再考慮其他可用的索引.

select * from 表名 use index(索引名) where 查詢條件;

ignore index

在查詢語(yǔ)句中表名后面, 添加ignore idnex來(lái)讓MySql忽略一個(gè)或多個(gè)索引

select * from 表名 ignore index(索引名) where 查詢條件;

force index

在查詢語(yǔ)句中表名后面, 添加force index ,強(qiáng)制使MySql使用一個(gè)特定的索引

select * from 表名 force index(索引名) where 查詢條件;

緩存

概述及流程

開(kāi)啟MySql的查詢緩存, 當(dāng)執(zhí)行完全相同的Sql語(yǔ)句時(shí), 服務(wù)器會(huì)直接從緩存中讀取結(jié)果, 當(dāng)數(shù)據(jù)被修改, 之前的緩存會(huì)失效, 修改頻繁的表不適合做查詢緩存.

1. 客戶端發(fā)送一條查詢給Sql服務(wù)器
2. 服務(wù)器會(huì)檢查查詢緩存, 如果命中了緩存, 立即返回存儲(chǔ)在緩存中的結(jié)果, 否則進(jìn)入下一階段
3. 服務(wù)器進(jìn)行sql解析, 預(yù)處理, 再由路由器生成對(duì)應(yīng)的執(zhí)行計(jì)劃
4. 查詢對(duì)應(yīng)的數(shù)據(jù)文件,查詢對(duì)應(yīng)的結(jié)果
5. 將結(jié)果返回客戶端,并將查詢出的結(jié)果緩存到查詢緩存中

查詢緩存配置

• 查看當(dāng)前MySql數(shù)據(jù)庫(kù)是否支持查詢緩存

  show variables like 'have_query_cache';
  

• 查看當(dāng)前MySql是否開(kāi)啟了查詢緩存

  show variables like 'query_cache_type';
  

• 查看查詢緩存的占用大小(單位字節(jié))

  show variables like 'query_cache_size';
  

• 查看查詢緩存的狀態(tài)變量

show status like 'Qcache%';

參數(shù)	含義
Qcache_free_blocks	查詢緩存中的可用內(nèi)存塊數(shù)
Qcache_free_memory	查詢緩存的可用內(nèi)存量
Qcache_hits	查詢緩存命中數(shù)
Qcache_inserts	添加到查詢緩存的查詢數(shù)
Qcache_lowmem_prunes	由于內(nèi)存不足而從查詢緩存中刪除的查詢數(shù)
Qcache_not_cached	非緩存查詢的數(shù)量(由于query_chche_type設(shè)置而無(wú)法緩存或未緩存)
Qcache_queries_in_cache	查詢緩存中注冊(cè)的查詢數(shù)
Qcache_total_blocks	查詢緩存中的塊總數(shù)

開(kāi)啟查詢緩存

MySql的查詢緩存默認(rèn)是關(guān)閉的, 需要手動(dòng)配置參數(shù)query cache type, 來(lái)查詢緩存.

值	含義
off/0	查詢緩存功能關(guān)閉
on/1	查詢緩存功能打開(kāi), select的結(jié)果符合緩存條件即會(huì)緩存, 否則不予緩存, 顯示指定sql_no_cache, 不予緩存
demand/2	查詢緩存功能按需進(jìn)行, 顯示指定sql_cache的select語(yǔ)句才會(huì)緩存, 其它不予緩存

在/usr/my.cnf配置中, 增加一些配置

# 開(kāi)啟MySql的查詢緩存
query_cache_type=1

配置完畢后,重啟服務(wù)即可生效
然后在命令執(zhí)行sql語(yǔ)句進(jìn)行驗(yàn)證, 執(zhí)行一條比較耗時(shí)的sql語(yǔ)句, 然后再執(zhí)行幾次, 查看后面幾次的執(zhí)行時(shí)間, 獲取通過(guò)查看緩存的緩存命中數(shù), 來(lái)判定是否走查詢緩存.

查詢緩存select選項(xiàng)

可以再select語(yǔ)句中指定倆個(gè)與查詢緩存相關(guān)的選項(xiàng)
sql_cache : 如果查詢結(jié)果是可緩存的, 且query_cache_type系統(tǒng)變量的值為on或demand, 則緩存查詢結(jié)果.
sql_no_cache : 服務(wù)器不使用查詢緩存. 既不檢查查詢緩存, 也不檢查是否已緩存, 也不緩存查詢結(jié)果.
例:

select sql_cache * from 表名 where 條件;select sql_no_cache * from 表名 where 條件;

查詢緩存失效的情況

1. sql語(yǔ)句不一致時(shí)(要想命中緩存,sql要完全一致)

select count(*) from 表名;Select count(*) from 表名;

2. 當(dāng)查詢條件不確定時(shí), 不會(huì)緩存.例: now()、current_date()、curdate()、curttime()、rand()、uuid()、user()、database()。

select * from 表名 where update_time < now();

3. 不查任何表

select 'A';

4. 查詢系統(tǒng)庫(kù)表時(shí)(mysql、information_schema或performance_schema)

select * from information_schema.engines;

5. 在存儲(chǔ)的函數(shù), 觸發(fā)器或事件的主體內(nèi)執(zhí)行的查詢
6. 緩存的表有更改, 該表的所有高速緩存查詢都將變?yōu)闊o(wú)效并從高速緩存中刪除(包括使用MERGE映射到已更改的表的查詢). 一個(gè)表可以被許多類(lèi)型的語(yǔ)句更改(insert、update、delete、truncate table、alter table、drop table、drop database)。

MySql內(nèi)存管理及優(yōu)化

內(nèi)存優(yōu)化原則

1. 盡量將多的內(nèi)存分配給MySql做緩存, 但要給操作系統(tǒng)和其他程序預(yù)留足夠的內(nèi)存
2. MyISAM存儲(chǔ)引擎的數(shù)據(jù)文件讀取依賴于操作系統(tǒng)自身的IO緩存, 所以有MyISAM表, 就要預(yù)留更多的內(nèi)存給操作系統(tǒng)做IO緩存
3. 排序區(qū), 連接區(qū)等緩存是分配給每個(gè)數(shù)據(jù)庫(kù)回話(session)專(zhuān)用的, 其默認(rèn)值的設(shè)置要根據(jù)最大連接數(shù)合理分配, 如果設(shè)置太大, 不僅浪費(fèi)資源, 還有可能再并發(fā)高時(shí)導(dǎo)致物理內(nèi)存耗盡.

MyISAM內(nèi)存優(yōu)化

MyISAM存儲(chǔ)引擎使用key_buffer存儲(chǔ)索引塊, 加速M(fèi)yISAM索引的讀寫(xiě)速度. 對(duì)于MyISAM表的數(shù)據(jù)塊, MySql沒(méi)有特別的緩存機(jī)制, 完全依賴于操作系統(tǒng)的IO緩存.

key_buffer_size

key_buffer_size決定MyISAM索引塊緩存區(qū)的大小, 直接影響到MyISAM表的存取效率. 可以在MySql參數(shù)文件中設(shè)置key_buffer_size的值, 對(duì)于一般MyISAM數(shù)據(jù)庫(kù), 建議至少將1/4可用內(nèi)存分配給key_buffer_size.
在/usr/my.cnf中做以下配置

key_buffer_size=512M

read_buffer_size

如果需要經(jīng)常順序掃描MyISAM表, 可以通過(guò)增大read_buffer_size的值來(lái)改善性能, 但需要注意的是read_buffer_size是每個(gè)session獨(dú)占的, 如果默認(rèn)值設(shè)置太大, 就會(huì)造成內(nèi)存浪費(fèi).

read_rnd_buffer_size

對(duì)于需要做排序的MySIAM表的查詢, 如帶有order by子句的sql. 適當(dāng)增加read_rnd_buffer_size的值, 可以改善此類(lèi)的sql性能, 但需要注意的是read_rnd_buffer_size是每個(gè)session獨(dú)占的, 如果默認(rèn)值設(shè)置太大, 就會(huì)造成內(nèi)存浪費(fèi).

InnoDB內(nèi)存優(yōu)化

InnoDB用一塊內(nèi)存區(qū)做IO緩存池, 不僅用來(lái)緩存InnoDB的索引塊, 也用來(lái)緩存InnoDB的數(shù)據(jù)塊

innodb_buffer_pool_size

該變量決定了innodb存儲(chǔ)引擎表數(shù)據(jù)的索引數(shù)據(jù)的最大緩存區(qū)大小. 在保證操作系統(tǒng)及其他程序有足夠內(nèi)存可用的情況下,innodb_buffer_pool_size的值越大, 緩存命中率越高, 訪問(wèn)InnoDB表需要的磁盤(pán)I/O就越少, 性能也就越高

innodb_buffer_pool_size=512M

innodb_logbuffer_zise

決定了innodb重做日志緩存的大小, 對(duì)于可能產(chǎn)生最大更新記錄的大事務(wù), 增加innodb_log_buffer_size的大小, 可以避免innodb在事務(wù)提交前就執(zhí)行不必要的日志寫(xiě)入磁盤(pán)操作

innodb_log_buffer_size=10M

MySql并發(fā)參數(shù)調(diào)整

MySql Server是多線程結(jié)構(gòu), 包括后臺(tái)線程和客戶服務(wù)線程. 多線程可以有效的利用服務(wù)資源, 提高數(shù)據(jù)庫(kù)的并發(fā)性能. 控制并發(fā)連接和線程的主要參數(shù)包括 max_connectons、back_log、thread_cache_size、table_open_cahce.

max_connections

采用max_connections控制允許連接到MySql數(shù)據(jù)庫(kù)的最大數(shù)量, 默認(rèn)值151. 如果狀態(tài)變量connection_errors_max_connections不為零, 并且一直增長(zhǎng), 說(shuō)明不斷有連接請(qǐng)求因數(shù)據(jù)庫(kù)連接數(shù)已達(dá)到最大值而失敗, 可以考慮增大max_connections的值
MySql最大可支持的連接數(shù), 取決于很多因素(操作系統(tǒng)平臺(tái)的線程庫(kù)的質(zhì)量、內(nèi)存大小、每個(gè)連接的負(fù)荷、CPU的處理速度、期望的響應(yīng)時(shí)間等),在Linux平臺(tái)下,性能好的服務(wù)器, 支持500-1000個(gè)連接,具體根據(jù)服務(wù)器性能進(jìn)行設(shè)定

back_log

back_log參數(shù)控制MySql監(jiān)聽(tīng)TCP端口時(shí)設(shè)置的積壓請(qǐng)求棧大小.
當(dāng)MySql的連接數(shù)達(dá)到max_connectons時(shí), 新來(lái)的請(qǐng)求將會(huì)被存到堆棧中, 以等待某一連接釋放資源, 該堆棧的數(shù)量即back_log, 如果等待的數(shù)量超過(guò)back_log, 將不被授予連接資源, 將會(huì)報(bào)錯(cuò).
5.6.6版本之前默認(rèn)值為50, 之后的版本默認(rèn)為50+(max_connections/5), 但最大不會(huì)超過(guò)900
如果需要數(shù)據(jù)庫(kù)在較短時(shí)間內(nèi)處理大量連接請(qǐng)求, 可以考慮適當(dāng)增大back_log的值

table_open_cache

該參數(shù)用來(lái)控制所有Sql語(yǔ)句執(zhí)行線程可以打開(kāi)表緩存的數(shù)量, 而在執(zhí)行Sql語(yǔ)句時(shí), 每個(gè)sql執(zhí)行線程至少打開(kāi)1個(gè)表緩存. 該參數(shù)的值應(yīng)該根據(jù)設(shè)置最大連接數(shù)max_connections以及每個(gè)連接執(zhí)行關(guān)聯(lián)查詢中涉及的表的最大數(shù)量來(lái)設(shè)定.
max_connections * N

thread_cache_size

為了加快連接數(shù)據(jù)庫(kù)的速度, MySql會(huì)緩存一定數(shù)量的客戶服務(wù)線程以備重用, 通過(guò)參數(shù)tread_cache_size可控制MySql緩存客戶服務(wù)線程的數(shù)量

innodb_lock_wait_timeout

該參數(shù)是用來(lái)設(shè)置InnoDB事務(wù)等待行鎖時(shí)間,默認(rèn)50ms.
根據(jù)需要進(jìn)行動(dòng)態(tài)設(shè)置, 對(duì)于需要快速反饋的業(yè)務(wù)系統(tǒng)來(lái)說(shuō), 可以將行鎖的等待時(shí)間調(diào)小, 以避免事務(wù)長(zhǎng)時(shí)間掛起, 對(duì)于后臺(tái)運(yùn)行的批量處理程序來(lái)說(shuō), 可以將行鎖的等待時(shí)間調(diào)大, 已避免發(fā)生大的回滾操作.

MySql鎖

概述

鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問(wèn)某一資源的機(jī)制
在數(shù)據(jù)庫(kù)中, 除傳統(tǒng)的計(jì)算資源(CPU、RAM、I/O等)的爭(zhēng)用外, 數(shù)據(jù)也是一種供許多用戶共享的資源. 如何保證數(shù)據(jù)并發(fā)訪問(wèn)的一致性、有效性、是所有數(shù)據(jù)庫(kù)必須解決的問(wèn)題，鎖沖突也是影響數(shù)據(jù)庫(kù)并發(fā)訪問(wèn)性能的一個(gè)重要因素。鎖對(duì)數(shù)據(jù)庫(kù)而言顯得尤為重要，也更加復(fù)雜。

鎖分類(lèi)

• 數(shù)據(jù)操作的顆粒度：
  1. 表鎖：操作時(shí)，會(huì)鎖定整個(gè)表
  2. 行鎖：操作時(shí)，會(huì)鎖定當(dāng)前操作行
• 數(shù)據(jù)操作的類(lèi)型：
  1. 讀鎖(共享鎖)：針對(duì)同一份數(shù)據(jù)，多個(gè)讀操作可以同時(shí)進(jìn)行而不會(huì)互相影響
  2. 寫(xiě)鎖(排它鎖)：當(dāng)前操作沒(méi)有完成之前，會(huì)阻斷其他寫(xiě)鎖和讀鎖

MySql鎖

針對(duì)其他數(shù)據(jù)庫(kù)而言，MySql的鎖機(jī)制比較簡(jiǎn)單，特點(diǎn)就是不同的存儲(chǔ)引擎支持不同的鎖機(jī)制

存儲(chǔ)引擎	表級(jí)鎖	行級(jí)鎖	頁(yè)面鎖
MyISAM	支持	不支持	不支持
InnoDB	支持	支持	不支持
MEMORY	支持	不支持	不支持
BDB	支持	不支持	支持

鎖的特點(diǎn)

鎖類(lèi)型	特點(diǎn)
表級(jí)鎖	偏向MyISAM存儲(chǔ)引擎, 開(kāi)銷(xiāo)小, 加鎖快, 不會(huì)出現(xiàn)死鎖, 鎖定粒度大,發(fā)生鎖沖突的概率最高, 并發(fā)最低
行級(jí)鎖	偏向InnoDB存儲(chǔ)引擎, 開(kāi)銷(xiāo)大, 加鎖慢, 會(huì)出現(xiàn)死鎖, 鎖定粒度最小, 發(fā)生鎖沖突的概率最低, 并發(fā)度最高
頁(yè)面鎖	開(kāi)始和加鎖時(shí)間介于表鎖和行鎖之間, 會(huì)出現(xiàn)死鎖, 鎖定粒度介于表鎖和行鎖之間,并發(fā)度一般

表級(jí)鎖更適合查詢?yōu)橹?只有少量按索引條件更新數(shù)據(jù)的應(yīng)用 如Web應(yīng)用
行級(jí)鎖更適合有大量按索引條件并發(fā)更新少量不同數(shù)據(jù), 同時(shí)又有查詢 如一些在線事務(wù)處理(OLTP)系統(tǒng)

MyISAM表鎖

MyISAM存儲(chǔ)引擎只支持表鎖, 也是MySql開(kāi)始版本中唯一支持的鎖類(lèi)型

加表鎖

MyISAM在執(zhí)行查詢語(yǔ)句(select)前, 會(huì)自動(dòng)給涉及的所有表加讀鎖, 在執(zhí)行更新操作(update、delete、insert)前，會(huì)自動(dòng)給涉及的表加寫(xiě)鎖，這個(gè)過(guò)程不需要用戶干預(yù)，因此，用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯示加鎖

   lock table 表名 read; -- 加讀鎖lock table 表名 write; -- 加寫(xiě)鎖unlock tables; -- 解鎖

鎖類(lèi)型	當(dāng)前操作界面	其他操作界面
讀鎖	可讀, 不可寫(xiě)	可讀, 不可寫(xiě)
寫(xiě)鎖	可讀, 可寫(xiě)	不可讀, 不可寫(xiě)

由上表可見(jiàn):

1. 對(duì)MyISAM表的讀操作, 不會(huì)阻塞其他用戶對(duì)用一張表的讀請(qǐng)求, 但會(huì)阻塞對(duì)同一張表的寫(xiě)請(qǐng)求
2. 對(duì)MyISAM表的寫(xiě)操作, 則會(huì)阻塞其他用戶對(duì)同一張表的讀和寫(xiě)操作

簡(jiǎn)而言之, 就是讀鎖會(huì)阻塞寫(xiě), 但不會(huì)阻塞讀, 而寫(xiě)鎖, 即會(huì)阻塞讀, 又會(huì)阻塞寫(xiě)
此外, MyISAM的讀寫(xiě)鎖調(diào)度是寫(xiě)優(yōu)先, 這也是MyISAM不適合做寫(xiě)為主的表的存儲(chǔ)引擎的原因, 因?yàn)閷?xiě)鎖后, 其他線程不能做任何操作, 大量的更新會(huì)使查詢很難得到鎖, 從而造成永久阻塞

查看鎖爭(zhēng)用情況

show open tables;

• In_user: 當(dāng)前表被查詢使用的次數(shù), 如果該數(shù)為0, 則表是打開(kāi)的, 但當(dāng)前沒(méi)有被使用.
• Name_locked: 表名稱(chēng)是否被鎖定. 名稱(chēng)鎖定用于取消表或?qū)Ρ磉M(jìn)行重命名等操作

show status like 'Table_locks%';

• Table_locks_immediate: 指的是能夠立即獲的表級(jí)鎖的次數(shù), 每立即獲取表, 值+1
• Table_locks_waited: 指的是不能立即獲取表級(jí)鎖而需要等待的次數(shù), 每等待一次, 值+1, 此值高說(shuō)明存在較為嚴(yán)重的表級(jí)鎖爭(zhēng)用情況

InnoDB行鎖

行鎖介紹

行鎖特點(diǎn): 偏向InnoDB存儲(chǔ)引擎, 開(kāi)銷(xiāo)大, 加鎖慢; 會(huì)出現(xiàn)死鎖; 鎖定粒度小, 發(fā)生鎖沖突的概率最低, 并發(fā)度也最高
InnoDB與MyISAM的最大不同有倆點(diǎn): 一是支持事務(wù), 二是采用行級(jí)鎖

show variables like 'tx_isloation';-- 查看數(shù)據(jù)庫(kù)默認(rèn)隔離級(jí)別

InnoDB的行鎖模式

InnoDB實(shí)現(xiàn)了一下倆種類(lèi)型的行鎖:

• 共享鎖(S) : 又稱(chēng)讀鎖, 簡(jiǎn)稱(chēng)S鎖, 共享鎖就是多個(gè)事務(wù)對(duì)同一數(shù)據(jù)可以共享一把鎖, 都能訪問(wèn)到數(shù)據(jù), 但只能讀不能改
• 排他鎖(X) : 又稱(chēng)寫(xiě)鎖, 簡(jiǎn)稱(chēng)X鎖, 排他鎖就是不能與其他鎖并存, 如一個(gè)事務(wù)獲取了一個(gè)數(shù)據(jù)行的排他鎖, 其他事務(wù)就不能再獲取該行的其他鎖, 包括共享鎖和排他鎖, 但獲取排他鎖的事務(wù)是可以對(duì)數(shù)據(jù)行讀取和修改

對(duì)于udate、delete、和insert語(yǔ)句，InnoDB會(huì)自動(dòng)給涉及的數(shù)據(jù)集加排他鎖（X）
對(duì)于普通的select語(yǔ)句，InnoDB不會(huì)加任何鎖

通過(guò)以下語(yǔ)句顯示給記錄集加共享鎖或排他鎖

select * from 表名 where ... lock in share mode; -- 共享鎖
select * from 表名 where ... for update; -- 排他鎖

無(wú)索引行鎖升級(jí)為表鎖

如果不通過(guò)索引條件檢索數(shù)據(jù), 那么InnoDB將對(duì)表中所有記錄加鎖, 實(shí)際效果跟表鎖一樣.

查看當(dāng)前表的索引 : show index from 表名;

間隙鎖危害

當(dāng)我們用范圍條件, 而不是使用相等條件檢索數(shù)據(jù), 并請(qǐng)求共享或排它鎖時(shí), InnoDB會(huì)給符合條件的已有數(shù)據(jù)進(jìn)行加鎖. 對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄, 叫做間隙(GAP).
InnoDB也會(huì)對(duì)這個(gè)"間隙"加鎖, 這種鎖機(jī)制就是所謂的間隙鎖(Next-Key鎖).

InnoDB行鎖爭(zhēng)用情況

show status like 'innodb_row_lock%';

Innodb_row_lock_current_waits : 當(dāng)前正在等待鎖定的數(shù)量
Innodb_row_lock_time : 從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定總時(shí)間長(zhǎng)度
Innodb_row_lock_time_avg : 每次等待所花平均時(shí)長(zhǎng)
Innodb_row_lock_time_max : 從系統(tǒng)啟動(dòng)到現(xiàn)在等待最長(zhǎng)一次所花的時(shí)間
Innodb_row_lock_waits : 系統(tǒng)啟動(dòng)到現(xiàn)在總共等待的次數(shù)當(dāng)?shù)却螖?shù)很高, 而且每次等待時(shí)間也不小時(shí), 就需要分析系統(tǒng)為什么會(huì)有如此多的等待, 然后根據(jù)分析結(jié)果著手制定優(yōu)化計(jì)劃.

SQL技巧

SQL執(zhí)行順序

SQL編寫(xiě)順序

select distinct查詢字段...
from表名
[left|right] join 表名 on 關(guān)聯(lián)條件
where 查詢條件
group by 分組字段...
having分組過(guò)濾條件
order by排序字段
limit分頁(yè)數(shù)量

SQL執(zhí)行順序

from 表名
on 關(guān)聯(lián)條件
[left|right] join表名
where 查詢條件
group by 分組字段...
having 分組過(guò)濾條件
select distinct查詢字段...order by 排序字段limit 分頁(yè)數(shù)量

正則表達(dá)式使用

正則表達(dá)式(Regular Expression) 是指一個(gè)用來(lái)描述或者匹配一系列符合某個(gè)句法規(guī)則的字符串的單個(gè)字符串

符號(hào)	含義
^	在字符串開(kāi)始處進(jìn)行匹配
$	在字符串末尾處進(jìn)行匹配
.	匹配單個(gè)字符, 包括換行符
[…]	匹配出括號(hào)內(nèi)的任意字符
[^…]	匹配不出括號(hào)內(nèi)的任意字符
a*	匹配零個(gè)或多個(gè)a(包括空串)
a+	匹配一個(gè)或多個(gè)a(不包括空串)
a?	匹配零個(gè)或一個(gè)a
a1|a2	匹配a1或a2
a(m)	匹配m個(gè)a
a(m,)	只是匹配m個(gè)a
a(m,n)	匹配m-n個(gè)a
a(,n)	匹配0-n個(gè)a
(…)	將模式元素組成單一元素

示例

select * from 表名 where 條件字段 regexp '^T'
select * from 表名 where 條件字段 regexp 'T$'
select * from 表名 where 條件字段 regexp '[awb]'

常用函數(shù)

數(shù)字函數(shù)

函數(shù)名稱(chēng)	作用
abs	求絕對(duì)值
sqrt	求二次方根
mod	求余數(shù)
ceil 和 ceiling	倆個(gè)函數(shù)功能相同, 都是返回不小于參數(shù)的最小整數(shù), 即向上取整
floor	向下取整,返回值轉(zhuǎn)化為一個(gè)bigint
rand	生成一個(gè)0-1之間的隨機(jī)數(shù),傳入整數(shù)參數(shù)是,用來(lái)產(chǎn)生重復(fù)序列
round	對(duì)所傳參數(shù)進(jìn)行四舍五入
sign	返回參數(shù)的符號(hào)
pow 和 power	倆個(gè)函數(shù)功能相同, 都是所傳參數(shù)的次方的結(jié)果值
sin	求正弦值
asin	求反弦值, 與函數(shù)sin互為反函數(shù)
cos	求余弦值
acos	求反余弦值, 與函數(shù)cos互為反函數(shù)
tan	求正切值
atan	求反正切值, 與函數(shù)tan互為反函數(shù)
cot	求余切值

字符串函數(shù)

函數(shù)名稱(chēng)	作用
length	計(jì)算字符串長(zhǎng)度的函數(shù), 返回字符串的字節(jié)長(zhǎng)度
concat	合并字符串, 返回結(jié)果為連接參數(shù)產(chǎn)生的字符串, 參數(shù)可以是一個(gè)或多個(gè)
insert	替換字符串函數(shù)
lower	將字符串中的字母轉(zhuǎn)換為小寫(xiě)
upper	將字符串中的字母轉(zhuǎn)換為大寫(xiě)
left	從左側(cè)截取字符串,返回字符串左邊的若干個(gè)字符
right	從右側(cè)截取字符串, 返回字符串右邊的若干個(gè)字符
trim	刪除字符串左右倆邊的空格
replace	字符串替換函數(shù), 返回替換后的新字符串
substring	截取字符串, 返回從指定位置開(kāi)始的指定長(zhǎng)度的字符串
reverse	字符串反轉(zhuǎn)(逆序)函數(shù), 返回與原字符串順序相反的字符串

日期函數(shù)

函數(shù)名稱(chēng)	作用
curdate 和 current_date	倆個(gè)函數(shù)的作用相同, 返回當(dāng)前系統(tǒng)的日期值
curtime 和 current_time	倆個(gè)函數(shù)的作用相同, 返回當(dāng)前系統(tǒng)的時(shí)間值
now 和 sysdate	倆個(gè)函數(shù)作用相同, 返回當(dāng)前系統(tǒng)的日期和時(shí)間值
month	獲取指定日期中月份
monthname	獲取指定日期中的月份的英文名稱(chēng)
dayname	獲取指定日期中對(duì)應(yīng)星期幾的英文名稱(chēng)
dayofweek	獲取指定日期對(duì)應(yīng)一周的索引位置值
week	獲取指定日期是一年中第幾周, 返回值范圍0~52或1-53
dayofyear	獲取指定日期是一年中第幾天, 返回值為1-366
dayofmonth	獲取一個(gè)日期是一個(gè)月中的第幾天, 返回值范圍是1~31
year	獲取年份, 返回值為1970~2069
time_to_sec	將時(shí)間參數(shù)轉(zhuǎn)換為秒數(shù)
sec_to_time	將秒數(shù)轉(zhuǎn)為時(shí)間, 與time_to_sec互為反函數(shù)
date_add 和 adddate	倆個(gè)函數(shù)功能相同, 都是向日期添加指定時(shí)間間隔
date_sub 和 subdate	倆個(gè)函數(shù)功能相同, 都是向日期減去指定的時(shí)間間隔
addtime	時(shí)間加法運(yùn)算, 在原始時(shí)間上添加指定時(shí)間
subtime	時(shí)間減法運(yùn)算, 在原始時(shí)間上減去指定的時(shí)間
datediff	獲取倆個(gè)日期之間的間隔, 返回參數(shù)1-參數(shù)2的值
date_format	格式化指定的日期, 根據(jù)參數(shù)返回指定格式的值
weekday	后去指定日期在一周內(nèi)對(duì)應(yīng)的工作日索引

MySql常用工具

MySql客戶端工具

mysql [options] [database]
參數(shù):-u , --user=name		指定用戶名-p , --password[=name]	指定密碼-h , --host=name		指定服務(wù)器ip或域名-P , --post=#			指定連接端口-e , --execute-name		執(zhí)行sql語(yǔ)句并退出
示例 :
mysql -h127.0.0.1 -P3306 -uroot -p1234
mysql -h 127.0.0.1 -P 3306 -u root -p
mysql -uroot -p1234 數(shù)據(jù)庫(kù)名稱(chēng) -e "執(zhí)行的sql語(yǔ)句";

mysqlbinlog

由于服務(wù)器生成的二進(jìn)制日志文件以二進(jìn)制格式保存, 所以如果想要檢查這些文本格式的文本, 就會(huì)使用到mysqlbinlog日志管理工具

mysqlbinlog [options] log-files ....
選項(xiàng):-d, --database=name : 指定數(shù)據(jù)庫(kù)名稱(chēng), 只列出指定的數(shù)據(jù)庫(kù)相關(guān)操作-o, --offset=# : 忽略調(diào)日志中的前N行命令-r, -- result-file=name : 將輸出文本格式日志輸出到指定文件-s, --short-form : 顯示簡(jiǎn)單格式, 省略掉一些信息--start-datatime=date --stop-datetime=date : 指定日期間隔內(nèi)的所有日志-- start-position=pos --stop-position=pos : 指定位置間隔內(nèi)的所有日志

mysqldump

mysqldump 客戶端工具用來(lái)備份數(shù)據(jù)庫(kù)或不同數(shù)據(jù)庫(kù)之間進(jìn)行數(shù)據(jù)遷移, 備份內(nèi)容包含創(chuàng)建表, 及插入表的sql語(yǔ)句

mysqldump [options] 數(shù)據(jù)庫(kù)名稱(chēng) [表名稱(chēng)]
mysqldump [options] --database/-B 數(shù)據(jù)庫(kù)名稱(chēng)..
mysqldump [options] -all-databases/-A
參數(shù):-u , --user=name		指定用戶名-p , --password[=name]	指定密碼-h , --host=name		指定服務(wù)器ip或域名-P , --post=#			指定連接端口
輸出內(nèi)容參數(shù)選項(xiàng):--add-drop-database		在每個(gè)數(shù)據(jù)庫(kù)創(chuàng)建語(yǔ)句前加上drop database語(yǔ)句--add-drop-table		在每個(gè)表創(chuàng)建語(yǔ)句前加上drop table語(yǔ)句,默認(rèn)開(kāi)啟;不開(kāi)啟(--skip-add-drop-table)-n, --no-create-db		不包含數(shù)據(jù)表的創(chuàng)建語(yǔ)句-t, --no-create-info	不包含數(shù)據(jù)表的創(chuàng)建語(yǔ)句-d, --no-data			不包含數(shù)據(jù)-T, --tab=name			自動(dòng)生成倆個(gè)文件: 一個(gè).sql文件,創(chuàng)建表結(jié)構(gòu)的語(yǔ)句;一個(gè).txt文件,數(shù)據(jù)文件, 相當(dāng)于select info outfile
示例:mysqldump -u登用戶名 -p登錄密碼 數(shù)據(jù)庫(kù)名 表名 [--add-drop-database --add-drop-table] > 文件名myslqdump -u登錄用戶名 -p登錄密碼 -T 目錄地址 數(shù)據(jù)庫(kù)名 表名

mysqlimport/source

mysqlimport是客戶端數(shù)據(jù)導(dǎo)入工具, 用來(lái)導(dǎo)入mysqldump加-T參數(shù)后導(dǎo)出的文本文件

mysqlimport [options] 表名 文件名...
示例:
mysqlimport -uroot -p1234 test /tmp/city.txt

如果需要導(dǎo)入sql文件, 可以使用mysql中的source指令:

source /root/tb_test.sql

mysqlshow

mysqlshow客戶端對(duì)象查找工具, 用來(lái)很快的查找存在哪些數(shù)據(jù)庫(kù), 數(shù)據(jù)庫(kù)中的表, 表中的列或索引

mysqlshow [options] [數(shù)據(jù)庫(kù)名稱(chēng) [數(shù)據(jù)表名 [數(shù)據(jù)列名稱(chēng)]]]
參數(shù): --count 顯示數(shù)據(jù)庫(kù)及表的統(tǒng)計(jì)信息(數(shù)據(jù)庫(kù),表均可不指定)-i		顯示指定數(shù)據(jù)庫(kù)或指定表的狀態(tài)信息
示例:
mysqlshow -uroot -p134 庫(kù)名 表名 --count

MySql日志

任何一種數(shù)據(jù)庫(kù)中, 都會(huì)有各種各樣的日志, 記錄著數(shù)據(jù)庫(kù)工作的方方面面, 以記錄數(shù)據(jù)庫(kù)發(fā)生的各種事件,
MySql中,有4種不同的日志(錯(cuò)誤日志、二進(jìn)制日志、查詢?nèi)罩?、慢查詢?nèi)罩?

錯(cuò)誤日志

錯(cuò)誤日志是MySql中最重要的日志之一, 記錄了當(dāng)mysqld啟動(dòng)和停止時(shí), 以及服務(wù)器在運(yùn)行過(guò)程中發(fā)生任何嚴(yán)重錯(cuò)誤時(shí)的相關(guān)信息, 當(dāng)數(shù)據(jù)庫(kù)出現(xiàn)任何故障導(dǎo)致無(wú)法正常是使用時(shí), 可以先查看此日志.
該日志是默認(rèn)開(kāi)啟的, 默認(rèn)存放目錄是數(shù)據(jù)目錄(var/lib/mysql), 模式日志文件名為hostname.err(hostname是主機(jī)名)

show variables like 'log_error%';

查看日志內(nèi)容

tail -f .\Z-HR-Z.err;

二進(jìn)制日志

二進(jìn)制日志(binlog)記錄了所有的DDL(數(shù)據(jù)定義)語(yǔ)句和DML(數(shù)據(jù)超縱)語(yǔ)句, 但不包括數(shù)據(jù)查詢語(yǔ)句. 此日志對(duì)于災(zāi)難時(shí)的數(shù)據(jù)恢復(fù)起極其重要的作用.MySql的主從復(fù)制,就是通過(guò)binlog實(shí)現(xiàn)的
二進(jìn)制日志, 默認(rèn)是沒(méi)有開(kāi)啟的. 需要到MySql的配置文件中開(kāi)啟, 并配置MySql日志的格式
配置文件名稱(chēng): my.cnf
日志存放位置: 配置時(shí), 給定了文件名但沒(méi)有指定路徑, 日志默認(rèn)寫(xiě)入mysql的數(shù)據(jù)目錄

# 配置開(kāi)啟binlog日志, 日志的文件前綴為 mysqlbin --> 生成的文件名如: mysqlbing.000001
log_bin=mysqlbin# 配置二進(jìn)制日志的格式
binlog_format=STATEMENT

mysql’bin.index : 該文件是日志索引文件, 記錄日志的文件名
mysqlbing.000001: 日志文件
查看日志內(nèi)容:

## STATEMENT
mysqlbinlog mysqlbing.000001;
## ROW
mysqlbinlog -vv mysqlbing.000002;

日志格式

STATEMENT

該日志格式在日志文件中記錄的是sql語(yǔ)句,每一條對(duì)數(shù)據(jù)進(jìn)行修改的sql都會(huì)記錄在日志中, 通過(guò)mysql提供的mysqlbinlog工具, 可以清晰的查看到每條語(yǔ)句的文本, 主從復(fù)制時(shí), 從庫(kù)(slave) 會(huì)將日志解析為原文本, 并在從庫(kù)中執(zhí)行一次

ROW

該日志在日志文件中記錄的是每一行的數(shù)據(jù)變更, 而不是記錄sql語(yǔ)句,
比如:
update table set a = 1
如果是STATEMENT日志格式, 在日志中會(huì)記錄一行sql語(yǔ)句, 如果是ROW, 由于是全表進(jìn)行更新, 也就是每一行記錄都會(huì)變更, ROW格式的日志中會(huì)記錄每行的數(shù)據(jù)變更

MIXED

這是目前mysql默認(rèn)的日志格式, 即混合了 STATEMENT和ROW倆種格式. 默認(rèn)情況下采用STATEMENT, 但在一些特殊情況下采用ROW來(lái)進(jìn)行記錄, MIXED格式能盡量利用倆種模式的優(yōu)點(diǎn), 而避開(kāi)他們的缺點(diǎn)

日志刪除

對(duì)于比較繁忙的系統(tǒng), 由于每天生成日志量大, 日志文件就會(huì)占用大量的磁盤(pán)空間, 所以要對(duì)日志文件進(jìn)行清除

方式一

通過(guò)Reset Master 指令刪除全部的binlog日志, 刪除之后, 日志編號(hào)將從****.000001重新開(kāi)始
執(zhí)行刪除日志指令

Reset Master;

方式二

執(zhí)行指令purge master logs to 'mysqlbin.****'; , 該命令將刪除*****編號(hào)之前的所有日志.

方式三

執(zhí)行指令purge master logs before 'yyyy-mm-dd hh24:mi:ss';,該命令將刪除日志為yyyy-mm-dd hh24:mi:ss 之前產(chǎn)生的所有日志

方式四

設(shè)置參數(shù) --expire_logs_days=#, 此參數(shù)的含義是設(shè)置日志的過(guò)期天數(shù), 過(guò)了指定的天數(shù)后日志將自動(dòng)刪除, 這樣有利于減少DBA管理日志的工作量.
在mysql的配置文件中配置

log_bin=mysqlbin
binlog_format=ROW
--expire_logs_days=3

查詢?nèi)罩?/h3>

查詢?nèi)罩局杏涗浟丝蛻舳说乃胁僮髡Z(yǔ)句, 而二進(jìn)制日志不包含查詢數(shù)據(jù)的sql語(yǔ)句
默認(rèn)情況下, 查詢?nèi)罩臼俏撮_(kāi)啟的, 開(kāi)啟查詢?nèi)罩? 設(shè)置如下配置:
在mysql的配置文件中

# 開(kāi)啟查詢?nèi)罩?0-關(guān)閉 1-開(kāi)啟
general_log=1
# 配置查詢?nèi)罩镜奈募?如果沒(méi)有指定 默認(rèn)文件名為host_name.log
general_log_file=file_name.log

慢查詢?nèi)罩?/h3>

慢查詢?nèi)罩居涗浟怂袌?zhí)行時(shí)間超過(guò)參數(shù)long_query_time設(shè)置值,并掃描記錄數(shù)不小于min_examined_row_limit的所有sql語(yǔ)句的日志
log_query_time默認(rèn)為10秒, 最小為0, 可以精確到微秒

文件位置和格式

慢查詢?nèi)罩灸J(rèn)是關(guān)閉的, 可以通過(guò)倆個(gè)參數(shù)開(kāi)啟慢查詢?nèi)罩?/p>

# 該參數(shù)用來(lái)控制慢查詢?nèi)罩臼欠耖_(kāi)啟, 1-開(kāi)啟 0-關(guān)閉
slow_query_log=1
# 該參數(shù)用來(lái)指定慢查詢?nèi)罩镜奈募?/span>
slow_query_log_file=日志文件名.log
# 該選項(xiàng)用來(lái)配置查詢的時(shí)間限制, 超過(guò)這個(gè)時(shí)間將認(rèn)為是慢查詢, 將進(jìn)行日志記錄, 默認(rèn)10S
long_query_time=3

查看日志

查詢慢查詢?nèi)罩緯r(shí)間

show varialbes like 'long_query_time';

查看慢查詢?nèi)罩?/h5>

查看日志最后幾行記錄

tail -f 日志文件名

如果慢查詢?nèi)罩緝?nèi)容很多, 可以借助mysqldumpslow工具

mysqldumpslow 日志文件名

Msql復(fù)制

復(fù)制概述

復(fù)制是指將主數(shù)據(jù)庫(kù)的DDL和DML操作通過(guò)二進(jìn)制日志傳到從庫(kù)服務(wù)器中然后在從庫(kù)上對(duì)這些日志重新執(zhí)行(重做). 從而使從庫(kù)和主庫(kù)數(shù)據(jù)保持同步.

Mysql支持一臺(tái)主庫(kù)同時(shí)向多臺(tái)從庫(kù)復(fù)制, 從庫(kù)同時(shí)也可以作為其它從服務(wù)器的主庫(kù), 實(shí)現(xiàn)鏈狀復(fù)制

復(fù)制原理

從上圖來(lái)看, 復(fù)制分為三步:

• Master主庫(kù)在事務(wù)提交是, 會(huì)把數(shù)據(jù)變更為時(shí)間Events記錄在二進(jìn)制日志文件Binlog中
• 主庫(kù)推送二進(jìn)制文件Binlog中的日志事件到從庫(kù)的中繼日志Relay Log
• slave重做中繼日志中的事件, 將改變反映它自己的數(shù)據(jù)

復(fù)制優(yōu)勢(shì)

• 主庫(kù)出現(xiàn)問(wèn)題,可以快速切換到從庫(kù)提供服務(wù)
• 可以在從庫(kù)上執(zhí)行查詢操作, 在主庫(kù)中更新, 實(shí)現(xiàn)讀寫(xiě)分離, 降低主庫(kù)的訪問(wèn)壓力
• 可以在從庫(kù)中執(zhí)行備份, 以避免備份期間影響主庫(kù)的服務(wù)

搭建步驟

master

1. 在master的配置文件my.cnf(一般位于/usr/my.cnf)中, 配置如下內(nèi)容

   # mysql服務(wù)ID, 保證整個(gè)集群環(huán)境中唯一
   server-id=1
   # mysql binlog日志的存儲(chǔ)路徑和文件名
   lob-bin=/var/lib/mysql/mysqlbin
   # 錯(cuò)誤日志, 默認(rèn)開(kāi)啟
   # log-err
   # mysql的安裝目錄
   # basedir
   # mysql的臨時(shí)目錄
   # tmpdir
   # mysql的數(shù)據(jù)存放目錄
   # datadir
   # 是否只讀 1-只讀 0-讀寫(xiě)
   read-only=0
   #忽略的數(shù)據(jù), 指定不需要同步的數(shù)據(jù)庫(kù)
   binlog-ignore-db=mysql
   # 指定同步的數(shù)據(jù)庫(kù)
   # binlog-do-db=數(shù)據(jù)庫(kù)名
   

2. 執(zhí)行完畢后, 重啟mysql

   service mysql restart
   

3. 創(chuàng)建同步數(shù)據(jù)的賬戶, 并且進(jìn)行授權(quán)操作(Mysql執(zhí)行)

   grant replication slave on *.* to '賬戶名'@'目標(biāo)ip' identified by '賬戶密碼';
   

   刷新權(quán)限列表

   flush privileges;
   

4. 查看master狀態(tài)

   show master status;
   

   
   字段含義:

   File: 從那個(gè)日志文件開(kāi)始推送日志文件
   Positon: 從那個(gè)位置開(kāi)始推送日志
   Binlong_Ignore_DB: 指定不需要同步的數(shù)據(jù)庫(kù)
   

slave

1. 在slave端配置文件中, 配置如下內(nèi)容

   # mysql服務(wù)端ID, 唯一
   server-id=2
   # 指定binlog日志
   lob-bin=/var/lib/mysql/mysqlbin
   

2. 執(zhí)行完畢后, 重啟mysql

   service mysql restart
   

3. 執(zhí)行如下指令(mysql執(zhí)行)–指定當(dāng)前從庫(kù)對(duì)應(yīng)的主庫(kù)的ip地址,用戶名,密碼, 從那個(gè)日志文件開(kāi)始的那個(gè)位置開(kāi)始同步推送日志

   change msater to master_host='master服務(wù)器IP地址',master_user='master設(shè)置的用戶名',master_password='master設(shè)置的密碼',master_log_file='日志開(kāi)始文件名',master_log_pos=日志開(kāi)始行;
   

4. 開(kāi)啟同步操作

   start slave;
   

   查看是否啟動(dòng)成功

   show slave status\G;
   

   
5. 停止同步操作

   stop slave;