抱歉,從印象筆記拷貝出來,格式亂了,但知識(shí)點(diǎn)還是很全面的,抱拳!
邏輯架構(gòu)
連接器：管理連接，權(quán)限驗(yàn)證
分析器：語法分析氛琢，
優(yōu)化器：執(zhí)行計(jì)劃生成，索引選擇
執(zhí)行器：操作引擎，返回結(jié)果
查詢緩存：命中則直接返回結(jié)果
存儲(chǔ)引擎：存儲(chǔ)數(shù)據(jù)白热，提供讀寫接口
服務(wù)層:
order by:全字段排序(Using filesort);
rowid排序(只有需要排序的字段和id)
order by rand():使用了內(nèi)存臨時(shí)表,內(nèi)存臨時(shí)表排序的時(shí)候使用了rowid排序方法;
order buffer;join buffer;
redo log（重做日志）
位置：InnoDB引擎特有的日志
作用: 保證在即使數(shù)據(jù)庫發(fā)生異常重啟，之前提交的記錄不會(huì)丟掉粗卜，這種能力叫做crash-safe;
物理日志,記錄是"在某個(gè)數(shù)據(jù)頁做了什么修改";
循環(huán)寫屋确，空間固定會(huì)用完；
主要節(jié)省的是隨機(jī)寫磁盤的io消耗(轉(zhuǎn)成順序?qū)?,而change buffer減少隨機(jī)讀盤的io消耗;
WAL技術(shù)：Write-Ahead-Logging,先寫日志续扔，再寫(flush)磁盤(刷臟頁);
臟頁:當(dāng)內(nèi)存數(shù)據(jù)頁跟磁盤數(shù)據(jù)頁內(nèi)容不一致的時(shí)候,我們稱這個(gè)為"臟頁";
干凈頁:內(nèi)存數(shù)據(jù)寫入到磁盤后,內(nèi)存和磁盤上的數(shù)據(jù)頁的內(nèi)容就一致了,稱為"干凈頁";
bin log（歸檔日志）
位置：Server層自有的日志
作用：保證在即使數(shù)據(jù)庫發(fā)生異常重啟攻臀，之前提交的記錄不會(huì)丟掉，這種能力叫做crash-safe;
邏輯日志,記錄的是這個(gè)語句的原始邏輯,比如"給 ID=2 這一行的 c 字段加 1";
追加寫,binlog文件寫到一定大小后會(huì)切換到下個(gè),不會(huì)覆蓋以前的日志';
binlog_format: row 或 statement(記錄的是sql語句);
更新操作:先寫入redolog,prepare,再寫入binlog,commit,這就是所謂的"兩階段提交";
事務(wù)
在實(shí)現(xiàn)上,數(shù)據(jù)庫會(huì)創(chuàng)建一個(gè)視圖,訪問的時(shí)候已視圖的邏輯結(jié)果為主;
隔離級(jí)別:
可重復(fù)讀(mysql默認(rèn)級(jí)別),視圖在事務(wù)啟動(dòng)的時(shí)候創(chuàng)建
讀提交,視圖是在每個(gè)sql語句開始執(zhí)行的時(shí)候創(chuàng)建
讀未提交,直接返回記錄上的最新值,沒有視圖概念
串行化,直接用加鎖的方式來避免并行訪問
可重復(fù)讀-實(shí)現(xiàn):
在mysql中,實(shí)際上每條記錄更新的時(shí)候會(huì)同時(shí)記錄一條回滾操作;記錄上的最新值,可以通過回滾操作,都可以得到前一個(gè)狀態(tài)的值;
當(dāng)沒有事務(wù)再需要到這些回滾日志(undo log)時(shí),回滾日志會(huì)刪除;存放位置:5.5之前,ibdata;
可重復(fù)讀-開啟:
顯式啟動(dòng)事務(wù)語句纱昧， begin 或 start transaction刨啸。配套的提交語句是 commit，回滾語句是 rollback识脆。
set autocommit=0设联，這個(gè)命令會(huì)將這個(gè)線程的自動(dòng)提交關(guān)掉。意味著如果你只執(zhí)行一個(gè) select 語句灼捂，這個(gè)事務(wù)就啟動(dòng)了离例，而且并不會(huì)自動(dòng)提交。這個(gè)事務(wù)持續(xù)存在直到你主動(dòng)執(zhí)行 commit 或 rollback 語句纵东，或者斷開連接粘招。
set autocommit=1的情況下,用 begin 顯式啟動(dòng)的事務(wù)，如果執(zhí)行 commit 則提交事務(wù)偎球。如果執(zhí)行 commit work and chain洒扎，則是提交事務(wù)并自動(dòng)啟動(dòng)下一個(gè)事務(wù)辑甜，這樣也省去了再次執(zhí)行 begin 語句的開銷。同時(shí)帶來的好處是從程序開發(fā)的角度明確地知道每個(gè)語句是否處于事務(wù)中袍冷。
幻讀:
原因:行鎖只能鎖住行,但新插入記錄這個(gè)動(dòng)作,要更新的是記錄之間的"間隙";為了解決幻讀問題,InnoDB只好引入新的鎖;也就是間隙鎖(GapLock);
鎖
數(shù)據(jù)庫鎖設(shè)計(jì)的初衷是處理并發(fā)問題;作為多用戶共享資源,當(dāng)出現(xiàn)并發(fā)訪問的時(shí)候,需要合理地控制資源的訪問規(guī)則;而鎖就是用來實(shí)現(xiàn)這些訪問規(guī)則的重要數(shù)據(jù)結(jié)構(gòu);
加鎖原則:
原則 1：加鎖的基本單位是 next-key lock磷醋。希望你還記得，next-key lock 是前開后閉區(qū)間胡诗。
原則 2：查找過程中訪問到的對(duì)象才會(huì)加鎖邓线。
優(yōu)化 1：索引上的等值查詢，給唯一索引加鎖的時(shí)候煌恢，next-key lock 退化為行鎖骇陈。
優(yōu)化 2：索引上的等值查詢，向右遍歷時(shí)且最后一個(gè)值不滿足等值條件的時(shí)候瑰抵，next-key lock 退化為間隙鎖你雌。
一個(gè) bug：唯一索引上的范圍查詢會(huì)訪問到不滿足條件的第一個(gè)值為止。
根據(jù)加鎖的范圍,mysql里面的鎖大致可以分為:
全局鎖:對(duì)整個(gè)數(shù)據(jù)庫實(shí)例加鎖;
1.全局讀鎖:命令(Flush tables with read lock);會(huì)讓整個(gè)庫處于只讀狀態(tài),以下語句會(huì)被阻塞:數(shù)據(jù)更新語句(數(shù)據(jù)的增刪改),數(shù)據(jù)定義語句(建表,修 改表結(jié)構(gòu))和更新類事務(wù)提交語句;使用場(chǎng)景:全庫邏輯備份;
表級(jí)鎖
1.表鎖:命令(lock tables ... read/write;unlock tables;)
2.元數(shù)據(jù)鎖:metadata lock(MDL);不需要顯式使用,在訪問一個(gè)表的時(shí)候會(huì)被自動(dòng)加上,作用是為了保證寫的正確性;
當(dāng)對(duì)一個(gè)表做增刪改查操作的時(shí)候,加MDL讀鎖;
當(dāng)對(duì)一個(gè)表做結(jié)構(gòu)更變操作的時(shí)候,加入MDL寫鎖;
讀鎖之間不互斥,可以多個(gè)線程同時(shí)對(duì)一張表增刪改查;
讀寫鎖,寫鎖之間是互斥的,用來保證變更表結(jié)構(gòu)操作的安全性;
行鎖:在引擎層,由各個(gè)引擎自己實(shí)現(xiàn)的,InnoDB支持行鎖;不支持行鎖意味著只能使用表級(jí)鎖,意味著同一張表上任意時(shí)刻只能有一個(gè)更新在執(zhí)行,會(huì)影響業(yè)務(wù)并發(fā)度;
1.在InnoDB事務(wù)中,行鎖是在需要的時(shí)候才加上的,但并不是不需要了就立即釋放,而是等到事務(wù)結(jié)束時(shí)才釋放;這就是兩階段鎖協(xié)議;
2.死鎖和死鎖檢測(cè):提交通過參數(shù) innodb_lock_wait_timeout 來設(shè)置超時(shí)時(shí)間,默認(rèn)是50s;提交發(fā)起死鎖檢測(cè)二汛，發(fā)現(xiàn)死鎖后婿崭，主動(dòng)回滾死鎖鏈條中的某一個(gè)事務(wù)，讓其他事務(wù)得以繼續(xù)執(zhí)行肴颊。將參數(shù) innodb_deadlock_detect 設(shè)置為 on氓栈，表示開啟這個(gè)邏輯;
3.即使把所有記錄都加上鎖,還是阻止不了新插入進(jìn)來的記錄,這也是為什么"幻讀"被單拿出來解決的原因;
間隙鎖:行鎖只能鎖住行,但新插入記錄這個(gè)動(dòng)作,要更新的是記錄之間的"間隙";為了解決幻讀問題,InnoDB只好引入新的鎖;也就是間隙鎖(GapLock) ;
作用:跟間隙鎖存在沖突關(guān)系的,是"往這個(gè)間隙中插入一個(gè)記錄"這個(gè)操作;間隙鎖之間不會(huì)存在沖突關(guān)系;
間隙鎖和行鎖合稱next-key-lock;前開后閉區(qū)間;lock in share mode,for update,insert...select;都會(huì)對(duì)一個(gè)區(qū)間段的數(shù)據(jù)加上間隙鎖;
作用:間隙鎖和next-key-lock的引入,幫我們解決了幻讀的問題;但是,間隙鎖的引入,可能會(huì)導(dǎo)致同樣對(duì)的語句鎖住更大的范圍,這其實(shí)影響了必發(fā)度的;
索引
目的:提高查詢效率;InnoDB的數(shù)據(jù)是按數(shù)據(jù)頁為單位來讀寫的,也就是說,當(dāng)需要讀一條記錄的時(shí)候,并不是將這個(gè)記錄本身從磁盤讀出來,而是以頁為單位,將 其整體讀入內(nèi)存,默認(rèn)每個(gè)數(shù)據(jù)頁大小為16KB;
數(shù)據(jù)結(jié)構(gòu):
哈希表,適用于等值查詢的場(chǎng)景,做區(qū)間查詢的速度很慢;
有序數(shù)組,適用于靜態(tài)存儲(chǔ)引擎,在等值查詢和區(qū)間查詢的性能非常優(yōu)秀,但在更新數(shù)據(jù)的時(shí)候就非常麻煩,插入一條記錄必須挪動(dòng)后面所有的記錄;
搜索樹:B+樹,時(shí)間復(fù)雜度O(log(N)),為了維持 O(log(N)) 的查詢復(fù)雜度，你就需要保持這棵樹是平衡二叉樹;為了減少讀取磁盤的數(shù)據(jù),降低樹的高度,引入N叉樹;
InnoDB引擎把數(shù)據(jù)放在主鍵索引上,其他索引上保存的是主鍵id;這種方式,我們稱之為"索引組織表";
Memory引擎采用的是把數(shù)據(jù)單獨(dú)存放,索引上保存數(shù)據(jù)位置的數(shù)據(jù)組織形式,我們稱之為"堆組織表";
類型:
主鍵索引:葉子節(jié)點(diǎn)是整行數(shù)據(jù);主鍵的長(zhǎng)度越小,普通索引的葉子節(jié)點(diǎn)就越小,普通索引占用的空間也就越小;
普通索引:葉子節(jié)點(diǎn)是主鍵的值;
覆蓋索引:索引上的信息足夠滿足查詢請(qǐng)求婿着，不需要再回到主鍵索引上去取數(shù)據(jù);
前綴索引:可以定義字符串的一部分作為索引;定義好長(zhǎng)度,既可以節(jié)省空間,又不用額外增加太多的查詢成本;
提交回表查詢:回到主鍵索引樹搜索的過程,稱之為回表;
提交最左前綴原則:B+樹這種索引結(jié)構(gòu),可以利用索引的"最左前綴",來定位記錄;
1. 索引的復(fù)用能力:如果可以調(diào)整順序,可以少維護(hù)一個(gè)索引,那么這個(gè)順序往往就是需要優(yōu)先考慮采用的;
2.索引占用的空間;
索引維護(hù):
數(shù)據(jù)頁分裂;
數(shù)據(jù)頁合并:相領(lǐng)兩個(gè)數(shù)據(jù)頁由于刪除數(shù)據(jù),利用率很低之后,會(huì)將兩個(gè)數(shù)據(jù)頁合并;
更新操作緩存在change buffer,定期merge;用的是buffer pool里的內(nèi)存;
刪除數(shù)據(jù)會(huì)造成空洞,插入數(shù)據(jù)也會(huì);
如果你創(chuàng)建的表沒有主鍵,或者把一個(gè)表的主鍵刪除了,InnoDB會(huì)生成一個(gè)長(zhǎng)度為6字節(jié)的rowid來作為主鍵;
索引使用:
對(duì)索引字段做函數(shù)操作,可能破壞索引值的有序性,因此優(yōu)化器就決定放棄走樹搜索功能;
隱身類型轉(zhuǎn)換:提交數(shù)據(jù)類型轉(zhuǎn)換:在mysql中,字符串和數(shù)字比較的話,是將字符串轉(zhuǎn)換成數(shù)字;多表查詢,關(guān)聯(lián)字段類型不一致;提交隱式字符編碼轉(zhuǎn)換:兩個(gè)表編碼集不一致,做多表查詢,連接過程中要求在被驅(qū)動(dòng)表的索引字段上加函數(shù)操作;
常見問題
長(zhǎng)事務(wù):
長(zhǎng)事務(wù)意味著系統(tǒng)里面會(huì)存在很老的事務(wù)視圖;由于這些事務(wù)隨時(shí)可能訪問數(shù)據(jù)庫里面的任何數(shù)據(jù),所以在事務(wù)提交前,數(shù)據(jù)庫里面它可能用到的回滾記錄都必須保留,這就會(huì)導(dǎo)致大量占用存儲(chǔ)空間;
常用sql
show variables like 'transaction_isolation'; 查看數(shù)據(jù)事務(wù)級(jí)別
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60; 查看持續(xù)時(shí)間超過60秒的事務(wù)
alter table tbl_name nowait add column ...;
alter table tbl_name wait n add column ...;更新表結(jié)構(gòu),設(shè)置拿到MDL鎖的時(shí)間,時(shí)間內(nèi)拿不到鎖,也不會(huì)阻塞其他業(yè)務(wù)語句;
start transaction with consistent snapshot; 想要馬上啟動(dòng)一個(gè)事務(wù);
begin/start transaction; 命令并不是一個(gè)事務(wù)的起點(diǎn)授瘦，在執(zhí)行到它們之后的第一個(gè)操作 InnoDB 表的語句（第一個(gè)快照讀語句），事務(wù)才真正啟動(dòng)祟身。
innodb_stats_persistent;存儲(chǔ)索引統(tǒng)計(jì)的方式;on表示統(tǒng)計(jì)信息會(huì)持久化存儲(chǔ);off表示統(tǒng)計(jì)信息只存儲(chǔ)在內(nèi)存中;
select * from t where id=5 for update;查詢后會(huì)加上一個(gè)寫鎖,在事務(wù)提交的時(shí)候釋放;是當(dāng)前讀;
create temporary table temp_t;創(chuàng)建內(nèi)存臨時(shí)表
tmp_table_size;配置內(nèi)存表的大小,默認(rèn)16M;如果臨時(shí)表大小超過這個(gè)配置,那么內(nèi)存臨時(shí)表就會(huì)轉(zhuǎn)成磁盤臨時(shí)表;
insert into … on duplicate key update;這個(gè)語義的邏輯是奥务，插入一行數(shù)據(jù)，如果碰到唯一鍵約束袜硫，就執(zhí)行后面的更新語句
select SQL_BIG_RESULT id%100 as m, count() as c from t1 group by m;告訴優(yōu)化器,這個(gè)語句涉及的數(shù)據(jù)量很大,可以直接使用磁盤臨時(shí)表;
select * from where id = 1 lock in share mode;是當(dāng)前讀;select * from where id = 1 是一致性讀;(第19篇的問題沒有理解)
analyze table t;重新統(tǒng)計(jì)索引信息;
optimize table t;重建表,重新統(tǒng)計(jì)索引信息;
force index(xxx) ;強(qiáng)制選擇xxx索引;
innodb_file_per_table;OFF表示表的數(shù)據(jù)放在系統(tǒng)共享表空間,和數(shù)據(jù)字典放在一起;ON表示每個(gè)InnoDB表數(shù)據(jù)存儲(chǔ)在一個(gè)以.ibd為后綴的文件中;推薦設(shè)置為ON;
innodb_flush_neighbors;控制刷臟頁連帶"鄰居"一起刷的行為;值為1表示會(huì)有上述"連坐"機(jī)制;值為0時(shí)表示不找鄰居,自己刷自己;8.0版本以后,默認(rèn)為0;
innodb_io_capacity;告訴InnoDB你的磁盤能力,建議設(shè)置成磁盤的IOPS;磁盤的IOPS可以通過工具fio來測(cè)試;
sort_buffer_size;mysql排序開辟的內(nèi)存大小;如果排序的數(shù)據(jù)量小于該值,排序就在內(nèi)存中完后;如果排序數(shù)據(jù)量太大,內(nèi)存放不下,則不得不利用磁盤臨時(shí)文件排序;
max_length_for_sort_data;控制排序行數(shù)據(jù)長(zhǎng)度的一個(gè)參數(shù);如果超過這個(gè)值,則換排序算法;
innodb_max_dirty_pages_pct;臟頁比例上限,默認(rèn)是0.75,來控制刷臟頁的速度;臟頁比例是通過Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的;
create user 'ua'@'%' identified by 'pa’;創(chuàng)建一個(gè)用戶ua;密碼是pa;這時(shí)候用戶沒有任何權(quán)限;
grant all privileges on . to 'ua'@'%' with grant option;給ua用戶所有權(quán)限;生產(chǎn)上為了安全我們應(yīng)該限制用戶的ip;
revoke all privileges on . from 'ua'@'%';回收用戶的所有權(quán)限;
grant all privileges on db1. to 'ua'@'%' with grant option;讓用戶擁有庫db1的所有權(quán)限;
flush privileges;刷新緩存權(quán)限;當(dāng)表和換成權(quán)限不一致的時(shí)候;不要直接操作用戶權(quán)限表;
show warnings;
創(chuàng)建分區(qū)表
CREATE TABLE t (
ftime datetime NOT NULL,
c int(11) DEFAULT NULL,
KEY (ftime)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
insert into t values('2017-4-1',1),('2018-4-1',1);
備份
官方自帶邏輯備份工具:mysqldump;使用參數(shù)–single-transaction,使導(dǎo)數(shù)據(jù)前只開啟一個(gè)事務(wù),確保拿到一致性視圖;(引擎必須支持該隔離級(jí)別)
全局讀鎖
架構(gòu)
M(master);S(slave);
M-S架構(gòu)(主--->從)
雙MM架構(gòu)
問題
全局讀鎖和set global readonly=true;怎么選擇?
答:兩者都可以讓全庫進(jìn)入只讀狀態(tài);readonly在某些系統(tǒng)被用來做其他邏輯,比如判斷一個(gè)庫是主庫還是備庫,修改全局變量的方式影響面更大;在異常處理機(jī)制方面,全局讀鎖在客戶端異常斷開后,會(huì)自動(dòng)釋放這個(gè)全局鎖,整個(gè)庫可以正常更新的狀態(tài),而readonly狀態(tài),會(huì)導(dǎo)致整個(gè)庫長(zhǎng)時(shí)間處于不可寫狀態(tài),風(fēng)險(xiǎn)較高;
在可重復(fù)讀隔離級(jí)別下,一致性視圖(一致性讀)和兩階段鎖協(xié)議到底沖突嗎?
答:事務(wù)在開啟時(shí),會(huì)基于庫"拍個(gè)快照",也就是版本號(hào);每行數(shù)據(jù)都有多個(gè)版本號(hào);能夠看到所有提交的事務(wù)結(jié)果,但之后,這個(gè)事務(wù)執(zhí)行期間,其他事務(wù)的更新對(duì)它不可見;InnoDB 利用了“所有數(shù)據(jù)都有多個(gè)版本”的這個(gè)特性，實(shí)現(xiàn)了“秒級(jí)創(chuàng)建快照”的能力;但:更新數(shù)據(jù)都是先讀后寫的挡篓，而這個(gè)讀婉陷，只能讀當(dāng)前的值，稱為“當(dāng)前讀”（current read）官研。若"當(dāng)前讀"的數(shù)據(jù),有其他事務(wù)的寫鎖還沒有釋放,這時(shí)候"兩階段鎖協(xié)議"上場(chǎng)了,必須等其他事務(wù)釋放了這個(gè)鎖,才能繼續(xù)當(dāng)前讀;
總結(jié):可重復(fù)讀的核心就是一致性讀（consistent read）秽澳；而事務(wù)更新數(shù)據(jù)的時(shí)候，只能用當(dāng)前讀戏羽。如果當(dāng)前的記錄的行鎖被其他事務(wù)占用的話担神，就需要進(jìn)入鎖等待。
- 在 MySQL 里始花，有兩個(gè)“視圖”的概念：
一個(gè)是 view妄讯。它是一個(gè)用查詢語句定義的虛擬表孩锡，在調(diào)用的時(shí)候執(zhí)行查詢語句并生成結(jié)果。創(chuàng)建視圖的語法是 create view ... 亥贸，而它的查詢方法與表一樣躬窜。
另一個(gè)是 InnoDB 在實(shí)現(xiàn) MVCC 時(shí)用到的一致性讀視圖，即 consistent read view炕置，用于支持 RC（Read Committed荣挨，讀提交）和 RR（Repeatable Read，可重復(fù)讀）隔離級(jí)別的實(shí)現(xiàn)朴摊。
4.怎么收縮表空間?
答:重建表;Online DDL;
5.select * from where id +1 = 10000;
select * from where id = 10000 -1; 兩者一個(gè)不會(huì)走id索引,一個(gè)會(huì)走;
6.MySQL 什么時(shí)候會(huì)使用內(nèi)部臨時(shí)表默垄？
答:如果語句執(zhí)行過程可以一邊讀數(shù)據(jù)，一邊直接得到結(jié)果甚纲，是不需要額外內(nèi)存的口锭，否則就需要額外的內(nèi)存，來保存中間結(jié)果贩疙；
join_buffer 是無序數(shù)組讹弯，sort_buffer 是有序數(shù)組，臨時(shí)表是二維表結(jié)構(gòu)这溅；
如果執(zhí)行邏輯需要用到二維表特性组民，就會(huì)優(yōu)先考慮使用臨時(shí)表。比如我們的例子中悲靴，union 需要用到唯一索引約束臭胜， group by 還需要用到另外一個(gè)字段來存累積計(jì)數(shù)。
7.group by使用知道原則:
如果對(duì) group by 語句的結(jié)果沒有排序要求癞尚，要在語句后面加 order by null耸三；
盡量讓 group by 過程用上表的索引，確認(rèn)方法是 explain 結(jié)果里沒有 Using temporary 和 Using filesort浇揩；
如果 group by 需要統(tǒng)計(jì)的數(shù)據(jù)量不大仪壮，盡量只使用內(nèi)存臨時(shí)表；也可以通過適當(dāng)調(diào)大 tmp_table_size 參數(shù)胳徽，來避免用到磁盤臨時(shí)表积锅；
如果數(shù)據(jù)量實(shí)在太大，使用 SQL_BIG_RESULT 這個(gè)提示养盗，來告訴優(yōu)化器直接使用排序算法得到 group by 的結(jié)果
8.如何快速?gòu)?fù)制一張表?
1.mysqldump
mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
這條命令中缚陷，主要參數(shù)含義如下：
–single-transaction 的作用是，在導(dǎo)出數(shù)據(jù)的時(shí)候不需要對(duì)表 db1.t 加表鎖往核，而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法箫爷；
–add-locks 設(shè)置為 0，表示在輸出的文件結(jié)果里，不增加" LOCK TABLES t WRITE;" 虎锚；
–no-create-info 的意思是硫痰，不需要導(dǎo)出表結(jié)構(gòu)；
–set-gtid-purged=off 表示的是翁都，不輸出跟 GTID 相關(guān)的信息碍论；
–result-file 指定了輸出文件的路徑，其中 client 表示生成的文件是在客戶端機(jī)器上的柄慰。
mysqldump -hport -u
secure_file_priv
mysqldump 提供了一個(gè)–tab 參數(shù)鳍悠，可以同時(shí)導(dǎo)出表結(jié)構(gòu)定義文件和 csv 數(shù)據(jù)文件;這條命令會(huì)在 $secure_file_priv 定義的目錄下，創(chuàng)建一個(gè) t.sql 文件保存建表語句坐搔，同時(shí)創(chuàng)建一個(gè) t.txt 文件保存 CSV 數(shù)據(jù)
2.csv
select * from db1.t where a>900 into outfile '/server_tmp/t.csv’;
需要注意如下幾點(diǎn)藏研。
1.這條語句會(huì)將結(jié)果保存在服務(wù)端。如果你執(zhí)行命令的客戶端和 MySQL 服務(wù)端不在同一個(gè)機(jī)器上概行，客戶端機(jī)器的臨時(shí)目錄下是不會(huì)生成 t.csv 文件的蠢挡。
2,into outfile 指定了文件的生成位置（/server_tmp/），這個(gè)位置必須受參數(shù) secure_file_priv 的限制凳忙。參數(shù) secure_file_priv 的可選值和作用分別是：
3,如果設(shè)置為 empty业踏，表示不限制文件生成的位置，這是不安全的設(shè)置涧卵；
4.如果設(shè)置為一個(gè)表示路徑的字符串勤家，就要求生成的文件只能放在這個(gè)指定的目錄，或者它的子目錄柳恐；
5.如果設(shè)置為 NULL伐脖，就表示禁止在這個(gè) MySQL 實(shí)例上執(zhí)行 select … into outfile 操作。
6.這條命令不會(huì)幫你覆蓋文件乐设，因此你需要確保 /server_tmp/t.csv 這個(gè)文件不存在讼庇，否則執(zhí)行語句時(shí)就會(huì)因?yàn)橛型募拇嬖诙鴪?bào)錯(cuò)。
7.這條命令生成的文本文件中近尚，原則上一個(gè)數(shù)據(jù)行對(duì)應(yīng)文本文件的一行蠕啄。但是，如果字段中包含換行符戈锻，在生成的文本中也會(huì)有換行符介汹。不過類似換行符、制表符這類符號(hào)舶沛，前面都會(huì)跟上“\”這個(gè)轉(zhuǎn)義符， 這樣就可以跟字段之間窗价、數(shù)據(jù)行之間的分隔符區(qū)分開如庭。
8.select …into outfile 方法不會(huì)生成表結(jié)構(gòu)文件
得到.csv 導(dǎo)出文件后，你就可以用下面的 load data 命令將數(shù)據(jù)導(dǎo)入到目標(biāo)表 db2.t 中
load data infile '/server_tmp/t.csv' into table db2.t;//讀取mysql服務(wù)端目錄
load data local infile ‘/tmp/SQL_LOAD_MB-1-0’ INTO TABLE `db2`.`t`。//讀取mysql客戶端目錄
3.物理拷貝
在 MySQL 5.6 版本引入了可傳輸表空間(transportable tablespace) 的方法
假設(shè)我們現(xiàn)在的目標(biāo)是在 db1 庫下坪它，復(fù)制一個(gè)跟表 t 相同的表 r骤竹，具體的執(zhí)行步驟如下：
執(zhí)行 create table r like t，創(chuàng)建一個(gè)相同表結(jié)構(gòu)的空表往毡；
執(zhí)行 alter table r discard tablespace蒙揣，這時(shí)候 r.ibd 文件會(huì)被刪除；
執(zhí)行 flush table t for export开瞭，這時(shí)候 db1 目錄下會(huì)生成一個(gè) t.cfg 文件懒震；
在 db1 目錄下執(zhí)行 cp t.cfg r.cfg; cp t.ibd r.ibd；這兩個(gè)命令（這里需要注意的是嗤详，拷貝得到的兩個(gè)文件个扰，MySQL 進(jìn)程要有讀寫權(quán)限）；
執(zhí)行 unlock tables葱色，這時(shí)候 t.cfg 文件會(huì)被刪除递宅；
執(zhí)行 alter table r import tablespace，將這個(gè) r.ibd 文件作為表 r 的新的表空間苍狰，由于這個(gè)文件的數(shù)據(jù)內(nèi)容和 t.ibd 是相同的办龄，所以表 r 中就有了和表 t 相同的數(shù)據(jù)
關(guān)于拷貝表的這個(gè)流程，有以下幾個(gè)注意點(diǎn)：
在第 3 步執(zhí)行完 flsuh table 命令之后淋昭，db1.t 整個(gè)表處于只讀狀態(tài)俐填，直到執(zhí)行 unlock tables 命令后才釋放讀鎖；
在執(zhí)行 import tablespace 的時(shí)候响牛，為了讓文件里的表空間 id 和數(shù)據(jù)字典中的一致玷禽，會(huì)修改 r.ibd 的表空間 id。而這個(gè)表空間 id 存在于每一個(gè)數(shù)據(jù)頁中呀打。因此矢赁，如果是一個(gè)很大的文件（比如 TB 級(jí)別），每個(gè)數(shù)據(jù)頁都需要修改贬丛，所以你會(huì)看到這個(gè) import 語句的執(zhí)行是需要一些時(shí)間的撩银。當(dāng)然，如果是相比于邏輯導(dǎo)入的方法豺憔，import 語句的耗時(shí)是非常短的额获。
比一下這三種方法的優(yōu)缺點(diǎn):
1.物理拷貝的方式速度最快，尤其對(duì)于大表拷貝來說是最快的方法恭应。如果出現(xiàn)誤刪表的情況抄邀，用備份恢復(fù)出誤刪之前的臨時(shí)庫，然后再把臨時(shí)庫中的表拷貝到生產(chǎn)庫上昼榛，是恢復(fù)數(shù)據(jù)最快的方法境肾。但是， 這種方法的使用也有一定的局限性：必須是全表拷貝，不能只拷貝部分?jǐn)?shù)據(jù)奥喻；需要到服務(wù)器上拷貝數(shù)據(jù)偶宫，在用戶無法登錄數(shù)據(jù)庫主機(jī)的場(chǎng)景下無法使用；由于是通過拷貝物理文件實(shí)現(xiàn)的环鲤，源表和目標(biāo)表都是使用 InnoDB 引擎時(shí)才能使用纯趋。
2.用 mysqldump 生成包含 INSERT 語句文件的方法，可以在 where 參數(shù)增加過濾條件冷离，來實(shí)現(xiàn)只導(dǎo)出部分?jǐn)?shù)據(jù)吵冒。這個(gè)方式的不足之一是，不能使用 join 這種比較復(fù)雜的 where 條件寫法酒朵。
3.用 select … into outfile 的方法是最靈活的桦锄，支持所有的 SQL 寫法。但蔫耽，這個(gè)方法的缺點(diǎn)之一就是结耀，每次只能導(dǎo)出一張表的數(shù)據(jù)，而且表結(jié)構(gòu)也需要另外的語句單獨(dú)備份匙铡。
后兩種方式都是邏輯備份方式图甜，是可以跨引擎使用的。
9.join 語句的兩種算法鳖眼，分別是 Index Nested-Loop Join(NLJ) 和 Block Nested-Loop Join(BNL)黑毅。
這些優(yōu)化方法中：
BKA 優(yōu)化是 MySQL 已經(jīng)內(nèi)置支持的，建議你默認(rèn)使用钦讳；
BNL 算法效率低矿瘦，建議你都盡量轉(zhuǎn)成 BKA 算法。優(yōu)化的方向就是給被驅(qū)動(dòng)表的關(guān)聯(lián)字段加上索引愿卒；
基于臨時(shí)表的改進(jìn)方案缚去，對(duì)于能夠提前過濾出小數(shù)據(jù)的 join 語句來說，效果還是很好的琼开；
MySQL 目前的版本還不支持 hash join易结，但你可以配合應(yīng)用端自己模擬出來，理論上效果要好于臨時(shí)表的方案柜候。
— 如果一條語句extra字段什么都沒寫的話,表示用的是NLJ算法;在使用left join時(shí)候,左邊表不一定是驅(qū)動(dòng)表;
