????????本文是我自己在秋招復(fù)習(xí)時(shí)的讀書(shū)筆記逃魄，整理的知識(shí)點(diǎn)荤西，也是為了防止忘記，尊重勞動(dòng)成果伍俘，轉(zhuǎn)載注明出處哦邪锌！如果你也喜歡，那就點(diǎn)個(gè)小心心癌瘾，文末贊賞一杯豆奶吧觅丰，嘻嘻。 讓我們共同成長(zhǎng)吧……

MySQL數(shù)據(jù)庫(kù)知識(shí)點(diǎn)整理

????1.常用基礎(chǔ)SQL

????????數(shù)據(jù)庫(kù)常用語(yǔ)句

????2.優(yōu)化事項(xiàng)

????????1.　對(duì)查詢(xún)進(jìn)行優(yōu)化妨退，應(yīng)盡量避免全表掃描妇萄，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。

????????2.　應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷咬荷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描嚣伐，如：select id from t where num is null可以在num上設(shè)置默認(rèn)值0，確保表中num列沒(méi)有null值萍丐，然后這樣查詢(xún)：select id from t where num=0

????????3.　應(yīng)盡量避免在 where 子句中使用!=或<>操作符，否則引擎將放棄使用索引而進(jìn)行全表掃描放典。

????????4.　應(yīng)盡量避免在 where 子句中使用or 來(lái)連接條件逝变，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：select id from t where num=10 or num=20可以這樣查詢(xún)：select id from t where num=10 union all select id from t where num=20

????????5.　in 和 not in 也要慎用奋构，否則會(huì)導(dǎo)致全表掃描壳影，如：select id from t where num in(1,2,3) 對(duì)于連續(xù)的數(shù)值，能用 between 就不要用 in 了：select id from t where num between 1 and 3

????????6.　下面的查詢(xún)也將導(dǎo)致全表掃描：select id from t where name like ‘%李%'若要提高效率弥臼，可以考慮全文檢索宴咧。

????????7．　如果在 where 子句中使用參數(shù)，也會(huì)導(dǎo)致全表掃描径缅。因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量掺栅，但優(yōu)化程序不能將訪問(wèn)計(jì)劃的選擇推遲到運(yùn)行時(shí)；它必須在編譯時(shí)進(jìn)行選擇纳猪。然 而氧卧，如果在編譯時(shí)建立訪問(wèn)計(jì)劃，變量的值還是未知的氏堤，因而無(wú)法作為索引選擇的輸入項(xiàng)沙绝。如下面語(yǔ)句將進(jìn)行全表掃描：select id from t where num=@num可以改為強(qiáng)制查詢(xún)使用索引：select id from t with(index(索引名)) where num=@num

????????8.　應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：select id from t where num/2=100應(yīng)改為:select id from t where num=100*2

????????9.　應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作闪檬，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描星著。如：select id from t where substring(name,1,3)='abc' ，name以abc開(kāi)頭的id應(yīng)改為:select id from t where name like ‘a(chǎn)bc%'

????????10.　不要在 where 子句中的“=”左邊進(jìn)行函數(shù)粗悯、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算虚循，否則系統(tǒng)將可能無(wú)法正確使用索引。

????????11.　在使用索引字段作為條件時(shí)为黎，如果該索引是復(fù)合索引邮丰，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引，否則該索引將不會(huì)被使用铭乾，并且應(yīng)盡可能的讓字段順序與索引順序相一致剪廉。

????????12.　不要寫(xiě)一些沒(méi)有意義的查詢(xún)，如需要生成一個(gè)空表結(jié)構(gòu)：select col1,col2 into #t from t where 1=0　這類(lèi)代碼不會(huì)返回任何結(jié)果集炕檩，但是會(huì)消耗系統(tǒng)資源的斗蒋，應(yīng)改成這樣：create table #t(…)

????????13.　很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇：select num from a where num in(select num from b)　用下面的語(yǔ)句替換：

select num from a where exists(select 1 from b where num=a.num)

???????14.　并不是所有索引對(duì)查詢(xún)都有效，SQL是根據(jù)表中數(shù)據(jù)來(lái)進(jìn)行查詢(xún)優(yōu)化的笛质，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)泉沾，SQL查詢(xún)可能不會(huì)去利用索引，如一表中有字段sex妇押，male跷究、female幾乎各一半，那么即使在sex上建了索引也對(duì)查詢(xún)效率起不了作用敲霍。

????????15.　索引并不是越多越好俊马，索引固然可 以提高相應(yīng)的 select 的效率，但同時(shí)也降低了 insert 及 update 的效率肩杈，因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引柴我，所以怎樣建索引需要慎重考慮，視具體情況而定扩然。一個(gè)表的索引數(shù)最好不要超過(guò)6個(gè)艘儒，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有 必要。

????????16.　應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列夫偶，因?yàn)?clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲(chǔ)順序界睁，一旦該列值改變將導(dǎo)致整個(gè)表記錄的順序的調(diào)整，會(huì)耗費(fèi)相當(dāng)大的資源兵拢。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列晕窑，那么需要考慮是否應(yīng)將該索引建為 clustered 索引。

????????17.　盡量使用數(shù)字型字段卵佛，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型杨赤，這會(huì)降低查詢(xún)和連接的性能敞斋，并會(huì)增加存儲(chǔ)開(kāi)銷(xiāo)。這是因?yàn)橐嬖谔幚聿樵?xún)和連接時(shí)會(huì)逐個(gè)比較字符串中每一個(gè)字符疾牲，而對(duì)于數(shù)字型而言只需要比較一次就夠了植捎。

????????18.　盡可能的使用 varchar/nvarchar 代替 char/nchar ，因?yàn)槭紫茸冮L(zhǎng)字段存儲(chǔ)空間小阳柔，可以節(jié)省存儲(chǔ)空間焰枢，其次對(duì)于查詢(xún)來(lái)說(shuō)，在一個(gè)相對(duì)較小的字段內(nèi)搜索效率顯然要高些舌剂。

????????19.　任何地方都不要使用 select * from t 济锄，用具體的字段列表代替“*”，不要返回用不到的任何字段霍转。

????????20.　盡量使用表變量來(lái)代替臨時(shí)表荐绝。如果表變量包含大量數(shù)據(jù)，請(qǐng)注意索引非常有限（只有主鍵索引）避消。

????????21.　避免頻繁創(chuàng)建和刪除臨時(shí)表低滩，以減少系統(tǒng)表資源的消耗。

????????22.　臨時(shí)表并不是不可使用岩喷，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行∧纾?dāng)需要重復(fù)引用大型表或常用表中的某個(gè)數(shù)據(jù)集時(shí)纱意。但是婶溯，對(duì)于一次性事件，最好使用導(dǎo)出表偷霉。

????????23.　在新建臨時(shí)表時(shí)爬虱，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table腾它，避免造成大量 log ，以提高速度死讹；如果數(shù)據(jù)量不大瞒滴，為了緩和系統(tǒng)表的資源，應(yīng)先create table赞警，然后insert妓忍。

????????24.　如果使用到了臨時(shí)表，在存儲(chǔ)過(guò)程的最后務(wù)必將所有的臨時(shí)表顯式刪除愧旦，先 truncate table 世剖，然后 drop table ，這樣可以避免系統(tǒng)表的較長(zhǎng)時(shí)間鎖定笤虫。

????????25.　盡量避免使用游標(biāo)旁瘫，因?yàn)橛螛?biāo)的效率較差祖凫，如果游標(biāo)操作的數(shù)據(jù)超過(guò)1萬(wàn)行，那么就應(yīng)該考慮改寫(xiě)酬凳。

????????26.　使用基于游標(biāo)的方法或臨時(shí)表方法之前惠况，應(yīng)先尋找基于集的解決方案來(lái)解決問(wèn)題，基于集的方法通常更有效宁仔。

????????27.　與臨時(shí)表一樣稠屠，游標(biāo)并不是不可使 用。對(duì)小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法翎苫，尤其是在必須引用幾個(gè)表才能獲得所需的數(shù)據(jù)時(shí)权埠。在結(jié)果集中包括“合計(jì)”的例程通常要比使用游標(biāo)執(zhí)行的速度快。如果開(kāi)發(fā)時(shí) 間允許煎谍，基于游標(biāo)的方法和基于集的方法都可以嘗試一下攘蔽，看哪一種方法的效果更好。

????????28.　在所有的存儲(chǔ)過(guò)程和觸發(fā)器的開(kāi)始處設(shè)置 SET NOCOUNT ON 粱快，在結(jié)束時(shí)設(shè)置 SET NOCOUNT OFF 秩彤。無(wú)需在執(zhí)行存儲(chǔ)過(guò)程和觸發(fā)器的每個(gè)語(yǔ)句后向客戶(hù)端發(fā)送DONE_IN_PROC 消息。

????????29.　盡量避免大事務(wù)操作事哭，提高系統(tǒng)并發(fā)能力漫雷。

????????30.　盡量避免向客戶(hù)端返回大數(shù)據(jù)量，若數(shù)據(jù)量過(guò)大鳍咱，應(yīng)該考慮相應(yīng)需求是否合理降盹。

1、事務(wù)四大特性（ACID）

????原子性(Atomicity):原子性是指事務(wù)是一個(gè)不可分割的工作單位谤辜，事務(wù)中的操作要么都發(fā)生蓄坏，要么都不發(fā)生。

????一致性(Consistency):如果事務(wù)執(zhí)行之前數(shù)據(jù)庫(kù)是一個(gè)完整性的狀態(tài),那么事務(wù)結(jié)束后,無(wú)論事務(wù)是否執(zhí)行成功,數(shù)據(jù)庫(kù)仍然是一個(gè)完整性狀態(tài)丑念。 (數(shù)據(jù)庫(kù)的完整性狀態(tài):當(dāng)一個(gè)數(shù)據(jù)庫(kù)中的所有的數(shù)據(jù)都符合數(shù)據(jù)庫(kù)中所定義的所有的約束,此時(shí)可以稱(chēng)數(shù)據(jù)庫(kù)是一個(gè)完整性狀態(tài)涡戳。)

????隔離性(Isolation):事務(wù)的隔離性是指多個(gè)用戶(hù)并發(fā)訪問(wèn)數(shù)據(jù)庫(kù)時(shí)，一個(gè)用戶(hù)的事務(wù)不能被其它用戶(hù)的事務(wù)所干擾脯倚，多個(gè)并發(fā)事務(wù)之間數(shù)據(jù)要相互隔離渔彰。

????持久性(durability):持久性是指一個(gè)事務(wù)一旦被提交，它對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變就是永久性的推正，接下來(lái)即使數(shù)據(jù)庫(kù)發(fā)生故障也不應(yīng)該對(duì)其有任何影響恍涂。

????引申：NOSQL CAP BASE

????1.關(guān)系型數(shù)據(jù)庫(kù)和非關(guān)系型數(shù)據(jù)庫(kù)區(qū)別？

????優(yōu)點(diǎn)

????成本：nosql數(shù)據(jù)庫(kù)簡(jiǎn)單易部署植榕，基本都是開(kāi)源軟件再沧，不需要像使用oracle那樣花費(fèi)大量成本購(gòu)買(mǎi)使用，相比關(guān)系型數(shù)據(jù)庫(kù)價(jià)格便宜尊残。當(dāng)然還有免費(fèi)的

????查詢(xún)速度：nosql數(shù)據(jù)庫(kù)將數(shù)據(jù)存儲(chǔ)于緩存之中炒瘸，關(guān)系型數(shù)據(jù)庫(kù)將數(shù)據(jù)存儲(chǔ)在硬盤(pán)中淤堵，自然查詢(xún)速度遠(yuǎn)不及nosql數(shù)據(jù)庫(kù)。

????存儲(chǔ)數(shù)據(jù)的格式：nosql的存儲(chǔ)格式是key,value形式什燕、文檔形式粘勒、圖片形式等等，所以可以存儲(chǔ)基礎(chǔ)類(lèi)型以及對(duì)象或者是集合等各種格式屎即，而數(shù)據(jù)庫(kù)則只支持基礎(chǔ)類(lèi)型庙睡。

????擴(kuò)展性：關(guān)系型數(shù)據(jù)庫(kù)有類(lèi)似join這樣的多表查詢(xún)機(jī)制的限制導(dǎo)致擴(kuò)展很艱難。

????缺點(diǎn)

????維護(hù)的工具和資料有限技俐，因?yàn)閚osql是屬于新的技術(shù)乘陪，不能和關(guān)系型數(shù)據(jù)庫(kù)10幾年的技術(shù)同日而語(yǔ)。

????不提供對(duì)sql的支持雕擂，如果不支持sql這樣的工業(yè)標(biāo)準(zhǔn)啡邑，將產(chǎn)生一定用戶(hù)的學(xué)習(xí)和使用成本。

????不提供關(guān)系型數(shù)據(jù)庫(kù)對(duì)事物的處理井赌。

????非關(guān)系型數(shù)據(jù)庫(kù)的優(yōu)勢(shì)

????性能NOSQL是基于鍵值對(duì)的谤逼，可以想象成表中的主鍵和值的對(duì)應(yīng)關(guān)系捣炬，而且不需要經(jīng)過(guò)SQL層的解析黔龟，所以性能非常高绳瘟。

????可擴(kuò)展性同樣也是因?yàn)榛阪I值對(duì)敬惦，數(shù)據(jù)之間沒(méi)有耦合性，所以非常容易水平擴(kuò)展轧飞。

關(guān)系型數(shù)據(jù)庫(kù)的優(yōu)勢(shì)

????復(fù)雜查詢(xún)可以用SQL語(yǔ)句方便的在一個(gè)表以及多個(gè)表之間做非常復(fù)雜的數(shù)據(jù)查詢(xún)司训。

????事務(wù)支持使得對(duì)于安全性能很高的數(shù)據(jù)訪問(wèn)要求得以實(shí)現(xiàn)刁岸。對(duì)于這兩類(lèi)數(shù)據(jù)庫(kù)耘子，對(duì)方的優(yōu)勢(shì)就是自己的弱勢(shì)果漾，反之亦然。

2.CAP 分布式系統(tǒng)不可能同時(shí)滿(mǎn)足一致性（C：Consistency）谷誓、可用性（A：Availability）和分區(qū)容忍性（P：Partition Tolerance）绒障，最多只能同時(shí)滿(mǎn)足其中兩項(xiàng)

dubbo+zookeeper 主要實(shí)現(xiàn)CP

springcloud eureka [hystrix] 主要實(shí)現(xiàn)AP

以上與服務(wù)注冊(cè)細(xì)節(jié)相關(guān)

3.BASE 是基本可用（Basically Available）、軟狀態(tài)（Soft State）和最終一致性（Eventually Consistent）三個(gè)短語(yǔ)的縮寫(xiě)捍歪。 BASE 理論是對(duì) CAP 中一致性和可用性權(quán)衡的結(jié)果户辱，它的理論的核心思想是：即使無(wú)法做到強(qiáng)一致性，但每個(gè)應(yīng)用都可以根據(jù)自身業(yè)務(wù)特點(diǎn)费封，采用適當(dāng)?shù)姆绞絹?lái)使系統(tǒng)達(dá)到最終一致性。

2蒋伦、數(shù)據(jù)庫(kù)隔離級(jí)別弓摘，每個(gè)級(jí)別會(huì)引發(fā)什么問(wèn)題，mysql默認(rèn)是哪個(gè)級(jí)別痕届？

????SQL標(biāo)準(zhǔn)定義了4類(lèi)隔離級(jí)別韧献，包括了一些具體規(guī)則末患，用來(lái)限定事務(wù)內(nèi)外的哪些改變是可見(jiàn)的，哪些是不可見(jiàn)的锤窑。低級(jí)別的隔離級(jí)一般支持更高的并發(fā)處理璧针，并擁有更低的系統(tǒng)開(kāi)銷(xiāo)。

????Read Uncommitted（讀取未提交內(nèi)容）

????在該隔離級(jí)別渊啰，所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果探橱。本隔離級(jí)別很少用于實(shí)際應(yīng)用，因?yàn)樗男阅芤膊槐绕渌?jí)別好多少绘证。讀取未提交的數(shù)據(jù)隧膏，也被稱(chēng)之為臟讀（Dirty Read）。

????Read Committed（讀取提交內(nèi)容）

????這是大多數(shù)數(shù)據(jù)庫(kù)系統(tǒng)的默認(rèn)隔離級(jí)別（但不是MySQL默認(rèn)的）嚷那。它滿(mǎn)足了隔離的簡(jiǎn)單定義：一個(gè)事務(wù)只能看見(jiàn)已經(jīng)提交事務(wù)所做的改變胞枕。 這種隔離級(jí)別也支持所謂的不可重復(fù)讀（Nonrepeatable Read），因?yàn)橥皇聞?wù)的其他實(shí)例在該實(shí)例處理其間可能會(huì)有新的commit魏宽，所以同一select可能返回不同結(jié)果腐泻。

????Repeatable Read（可重讀）

????這是MySQL的默認(rèn)事務(wù)隔離級(jí)別，它確保同一事務(wù)的多個(gè)實(shí)例在并發(fā)讀取數(shù)據(jù)時(shí)队询，會(huì)看到同樣的數(shù)據(jù)行派桩。不過(guò)理論上，這會(huì)導(dǎo)致另一個(gè)棘手的問(wèn)題：幻讀 （Phantom Read）娘摔。 簡(jiǎn)單的說(shuō)窄坦，幻讀指當(dāng)用戶(hù)讀取某一范圍的數(shù)據(jù)行時(shí)，另一個(gè)事務(wù)又在該范圍內(nèi)插入了新行凳寺，當(dāng)用戶(hù)再讀取該范圍的數(shù)據(jù)行時(shí)鸭津，會(huì)發(fā)現(xiàn)有新的“幻影” 行。InnoDB和Falcon存儲(chǔ)引擎通過(guò)多版本并發(fā)控制（MVCC肠缨，Multiversion Concurrency Control）機(jī)制解決了該問(wèn)題逆趋。

????Serializable（可串行化）

????這是最高的隔離級(jí)別，它通過(guò)強(qiáng)制事務(wù)排序晒奕，使之不可能相互沖突闻书，從而解決幻讀問(wèn)題。簡(jiǎn)言之脑慧，它是在每個(gè)讀的數(shù)據(jù)行上加上共享鎖魄眉。在這個(gè)級(jí)別，可能導(dǎo)致大量的超時(shí)現(xiàn)象和鎖競(jìng)爭(zhēng)闷袒。 這四種隔離級(jí)別采取不同的鎖類(lèi)型來(lái)實(shí)現(xiàn)坑律，若讀取的是同一個(gè)數(shù)據(jù)的話，就容易發(fā)生問(wèn)題囊骤。

????臟讀(Drity Read)：某個(gè)事務(wù)已更新一份數(shù)據(jù)晃择，另一個(gè)事務(wù)在此時(shí)讀取了同一份數(shù)據(jù)冀值，由于某些原因，前一個(gè)RollBack了操作宫屠，則后一個(gè)事務(wù)所讀取的數(shù)據(jù)就會(huì)是不正確的列疗。

????不可重復(fù)讀(Non-repeatable read):在一個(gè)事務(wù)的兩次查詢(xún)之中數(shù)據(jù)不一致，這可能是兩次查詢(xún)過(guò)程中間插入了一個(gè)事務(wù)更新的原有的數(shù)據(jù)浪蹂。

????幻讀(Phantom Read):在一個(gè)事務(wù)的兩次查詢(xún)中數(shù)據(jù)筆數(shù)不一致抵栈，例如有一個(gè)事務(wù)查詢(xún)了幾列(Row)數(shù)據(jù)，而另一個(gè)事務(wù)卻在此時(shí)插入了新的幾列數(shù)據(jù)乌逐，先前的事務(wù)在接下來(lái)的查詢(xún)中竭讳，就會(huì)發(fā)現(xiàn)有幾列數(shù)據(jù)是它先前所沒(méi)有的。

????讀不影響寫(xiě)：事務(wù)以排他鎖的形式修改原始數(shù)據(jù)浙踢，讀時(shí)不加鎖绢慢，因?yàn)?MySQL 在事務(wù)隔離級(jí)別Read committed 、Repeatable Read下洛波，InnoDB 存儲(chǔ)引擎采用非鎖定性一致讀－－即讀取不占用和等待表上的鎖胰舆。即采用的是MVCC中一致性非鎖定讀模式。 因讀時(shí)不加鎖蹬挤，所以不會(huì)阻塞其他事物在相同記錄上加 X鎖來(lái)更改這行記錄缚窿。

????寫(xiě)不影響讀：事務(wù)以排他鎖的形式修改原始數(shù)據(jù)，當(dāng)讀取的行正在執(zhí)行 delete 或者 update 操作焰扳，這時(shí)讀取操作不會(huì)因此去等待行上鎖的釋放倦零。相反地，InnoDB 存儲(chǔ)引擎會(huì)去讀取行的一個(gè)快照數(shù)據(jù)吨悍。

????間隙鎖：間隙鎖主要用來(lái)防止幻讀扫茅，用在repeatable-read隔離級(jí)別下，指的是當(dāng)對(duì)數(shù)據(jù)進(jìn)行條件育瓜，范圍檢索時(shí)葫隙，對(duì)其范圍內(nèi)也許并存在的值進(jìn)行加鎖！ 當(dāng)查詢(xún)的索引含有唯一屬性（唯一索引躏仇，主鍵索引）時(shí)恋脚，Innodb存儲(chǔ)引擎會(huì)對(duì)next-key lock進(jìn)行優(yōu)化，將其降為record lock,即僅鎖住索引本身焰手，而不是范圍糟描！若是普通輔助索引，則會(huì)使用傳統(tǒng)的next-key lock進(jìn)行范圍鎖定书妻！

3船响、MySQL的鎖算法

????Record Lock：?jiǎn)蝹€(gè)行記錄上的鎖。

????Gap Lock：間隙鎖，鎖定一個(gè)范圍灿意，但不包括記錄本身。GAP鎖的目的崇呵，是為了防止同一事務(wù)的兩次當(dāng)前讀缤剧，出現(xiàn)幻讀的情況。

????Next-Key Lock：1+2域慷，鎖定一個(gè)范圍荒辕，并且鎖定記錄本身。對(duì)于行的查詢(xún)犹褒，都是采用該方法抵窒，主要目的是解決幻讀的問(wèn)題。

4叠骑、MySQL的MVCC

????MVCC的全稱(chēng)是“多版本并發(fā)控制”李皇。這項(xiàng)技術(shù)使得InnoDB的事務(wù)隔離級(jí)別下執(zhí)行一致性讀操作有了保證，換言之宙枷，就是為了查詢(xún)一些正在被另一個(gè)事務(wù)更新的行掉房，并且可以看到它們被更新之前的值。 這是一個(gè)可以用來(lái)增強(qiáng)并發(fā)性的強(qiáng)大的技術(shù)慰丛，因?yàn)檫@樣的一來(lái)的話查詢(xún)就不用等待另一個(gè)事務(wù)釋放鎖卓囚。這項(xiàng)技術(shù)在數(shù)據(jù)庫(kù)領(lǐng)域并不是普遍使用的。一些其它的數(shù)據(jù)庫(kù)產(chǎn)品诅病，以及mysql其它的存儲(chǔ)引擎并不支持它哪亿。

????mysql的innodb采用的是行鎖，而且采用了多版本并發(fā)控制來(lái)提高讀操作的性能贤笆。

1.什么是多版本并發(fā)控制呢MVCC 蝇棉？

????其實(shí)就是在每一行記錄的后面增加兩個(gè)隱藏列，記錄創(chuàng)建版本號(hào)和刪除版本號(hào)苏潜，而每一個(gè)事務(wù)在啟動(dòng)的時(shí)候银萍，都有一個(gè)唯一的遞增的版本號(hào)。 在InnoDB中恤左，給每行增加兩個(gè)隱藏字段來(lái)實(shí)現(xiàn)MVCC贴唇，兩個(gè)列都用來(lái)存儲(chǔ)事務(wù)的版本號(hào)，每開(kāi)啟一個(gè)新事務(wù)飞袋，事務(wù)的版本號(hào)就會(huì)遞增戳气。

2.默認(rèn)的隔離級(jí)別（REPEATABLE READ）下，增刪查改巧鸭？

????SELECT

????讀取創(chuàng)建版本小于或等于當(dāng)前事務(wù)版本號(hào)瓶您，并且刪除版本為空或大于當(dāng)前事務(wù)版本號(hào)的記錄。這樣可以保證在讀取之前記錄是存在的

????INSERT

????將當(dāng)前事務(wù)的版本號(hào)保存至行的創(chuàng)建版本號(hào)

????UPDATE

????新插入一行，并以當(dāng)前事務(wù)的版本號(hào)作為新行的創(chuàng)建版本號(hào)呀袱，同時(shí)將原記錄行的刪除版本號(hào)設(shè)置為當(dāng)前事務(wù)版本號(hào)

????DELETE

????將當(dāng)前事務(wù)的版本號(hào)保存至行的刪除版本號(hào)

3.什么是快照讀和當(dāng)前讀贸毕？

????快照讀：讀取的是快照版本，也就是歷史版本

????當(dāng)前讀：讀取的是最新版本

????普通的SELECT就是快照讀夜赵，而UPDATE明棍、DELETE、INSERT寇僧、SELECT …? LOCK IN SHARE MODE摊腋、SELECT … FOR UPDATE是當(dāng)前讀。

4.什么是鎖定讀嘁傀？

????在一個(gè)事務(wù)中兴蒸，標(biāo)準(zhǔn)的SELECT語(yǔ)句是不會(huì)加鎖，但是有兩種情況例外细办。

????SELECT ... LOCK IN SHARE MODE 給記錄假設(shè)共享鎖橙凳，這樣一來(lái)的話，其它事務(wù)只能讀不能修改笑撞，直到當(dāng)前事務(wù)提交

????SELECT ... FOR UPDATE 給索引記錄加鎖痕惋，這種情況下跟UPDATE的加鎖情況是一樣的

5.什么是一致性非鎖定讀？

????consistent read （一致性讀）娃殖，InnoDB用多版本來(lái)提供查詢(xún)數(shù)據(jù)庫(kù)在某個(gè)時(shí)間點(diǎn)的快照值戳。如果隔離級(jí)別是REPEATABLE READ，那么在同一個(gè)事務(wù)中的所有一致性讀都讀的是事務(wù)中第一個(gè)這樣的讀讀到的快照炉爆； 如果是READ COMMITTED堕虹，那么一個(gè)事務(wù)中的每一個(gè)一致性讀都會(huì)讀到它自己刷新的快照版本。Consistent read（一致性讀）是READ COMMITTED和REPEATABLE READ隔離級(jí)別下普通SELECT語(yǔ)句默認(rèn)的模式芬首。 一致性讀不會(huì)給它所訪問(wèn)的表加任何形式的鎖赴捞，因此其它事務(wù)可以同時(shí)并發(fā)的修改它們。

????MVCC實(shí)現(xiàn)一致性非鎖定讀郁稍，這就有保證在同一個(gè)事務(wù)中多次讀取相同的數(shù)據(jù)返回的結(jié)果是一樣的赦政，解決了不可重復(fù)讀的問(wèn)題。

6.什么是悲觀鎖和樂(lè)觀鎖耀怜？

????????悲觀鎖：正如它的名字那樣恢着，數(shù)據(jù)庫(kù)總是認(rèn)為別人會(huì)去修改它所要操作的數(shù)據(jù)，因此在數(shù)據(jù)庫(kù)處理過(guò)程中將數(shù)據(jù)加鎖财破。其實(shí)現(xiàn)依靠數(shù)據(jù)庫(kù)底層掰派。

????????樂(lè)觀鎖：如它的名字那樣，總是認(rèn)為別人不會(huì)去修改左痢，只有在提交更新的時(shí)候去檢查數(shù)據(jù)的狀態(tài)靡羡。通常是給數(shù)據(jù)增加一個(gè)字段來(lái)標(biāo)識(shí)數(shù)據(jù)的版本系洛。

7.select時(shí)怎么加排它鎖？

????使用鎖定讀略步，普通select不會(huì)引起加鎖描扯，而是去讀取最新的快照。同上4

????事務(wù)以排他鎖的形式修改原始數(shù)據(jù)趟薄，當(dāng)讀取的數(shù)據(jù)正在進(jìn)行更新等操作荆烈，則直接去讀取快照，而不是等鎖釋放

5竟趾、MYSQL的兩種存儲(chǔ)引擎區(qū)別（事務(wù)、鎖級(jí)別等等）宫峦，各自的適用場(chǎng)景

MyISAM

????不支持事務(wù)岔帽，但是每次查詢(xún)都是原子的；

????支持表級(jí)鎖导绷，即每次操作是對(duì)整個(gè)表加鎖犀勒；

????存儲(chǔ)表的總行數(shù)；

????一個(gè)MYISAM表有三個(gè)文件：索引文件妥曲、表結(jié)構(gòu)文件贾费、數(shù)據(jù)文件；

????采用非聚集索引檐盟，索引文件的數(shù)據(jù)域存儲(chǔ)指向數(shù)據(jù)文件的指針褂萧。輔索引與主索引基本一致，但是輔索引不用保證唯一性葵萎。

????適用OLAP

InnoDb

????支持ACID的事務(wù)导犹，支持事務(wù)的四種隔離級(jí)別；

????支持行級(jí)鎖及外鍵約束：因此可以支持寫(xiě)并發(fā)羡忘；

不存儲(chǔ)總行數(shù)谎痢；

????一個(gè)InnoDb引擎存儲(chǔ)在一個(gè)文件空間（共享表空間，表大小不受操作系統(tǒng)控制卷雕，一個(gè)表可能分布在多個(gè)文件里）节猿，也有可能為多個(gè)（設(shè)置為獨(dú)立表空，表大小受操作系統(tǒng)文件大小限制漫雕，一般為2G）滨嘱，受操作系統(tǒng)文件大小的限制；

????主鍵索引采用聚集索引（索引的數(shù)據(jù)域存儲(chǔ)數(shù)據(jù)文件本身）浸间，輔索引的數(shù)據(jù)域存儲(chǔ)主鍵的值九孩；因此從輔索引查找數(shù)據(jù)，需要先通過(guò)輔索引找到主鍵值发框，再訪問(wèn)輔索引躺彬；

????最好使用自增主鍵煤墙，防止插入數(shù)據(jù)時(shí)，為維持B+樹(shù)結(jié)構(gòu)宪拥，文件的大調(diào)整仿野。

????適用OLTP

InnoDB主要特性

????主要包括：插入緩存（insert buffer）、兩次寫(xiě)(double write)她君、自適應(yīng)哈希(Adaptive Hash index)脚作、異步IO(Async IO)、刷新鄰接頁(yè)(Flush Neighbor Page)

感興趣可以參考書(shū)籍《MySQL技術(shù)內(nèi)幕:innodb存儲(chǔ)引擎》 網(wǎng)上找了一個(gè)博客InnoDB關(guān)鍵特性

6缔刹、索引有B+索引和hash索引球涛，各自的區(qū)別？

主要區(qū)別

????如果是等值查詢(xún)校镐，那么哈希索引明顯有絕對(duì)優(yōu)勢(shì)亿扁，因?yàn)橹恍枰?jīng)過(guò)一次算法即可找到相應(yīng)的鍵值；當(dāng)然了鸟廓，這個(gè)前提是从祝，鍵值都是唯一的。如果鍵值不是唯一的引谜，就需要先找到該鍵所在位置牍陌，然后再根據(jù)鏈表往后掃描，直到找到相應(yīng)的數(shù)據(jù)员咽；

????如果是范圍查詢(xún)檢索毒涧，這時(shí)候哈希索引就毫無(wú)用武之地了，因?yàn)樵仁怯行虻逆I值贝室，經(jīng)過(guò)哈希算法后链嘀，有可能變成不連續(xù)的了，就沒(méi)辦法再利用索引完成范圍查詢(xún)檢索档玻；

????同理怀泊，哈希索引也沒(méi)辦法利用索引完成排序，以及l(fā)ike ‘xxx%’ 這樣的部分模糊查詢(xún)（這種部分模糊查詢(xún)误趴，其實(shí)本質(zhì)上也是范圍查詢(xún)）霹琼；

????哈希索引也不支持多列聯(lián)合索引的最左匹配規(guī)則；

????+樹(shù)索引的關(guān)鍵字檢索效率比較平均凉当，不像B樹(shù)那樣波動(dòng)幅度大枣申，在有大量重復(fù)鍵值情況下，哈希索引的效率也是極低的看杭，因?yàn)榇嬖谒^的哈希碰撞問(wèn)題忠藤。

7、為什么B+樹(shù)適合作為索引的結(jié)構(gòu)楼雹？

????B樹(shù)：有序數(shù)組+平衡多叉樹(shù)

????B+樹(shù)：有序數(shù)組鏈表+平衡多叉樹(shù) 葉子存儲(chǔ)數(shù)據(jù)模孩，空間占用小尖阔，且是雙鏈表，修改效率快

????不同于B樹(shù)只適合隨機(jī)檢索榨咐，B+樹(shù)同時(shí)支持隨機(jī)檢索和順序檢索

????數(shù)據(jù)庫(kù)索引采用B+樹(shù)的主要原因是B樹(shù)在提高了磁盤(pán)IO性能的同時(shí)并沒(méi)有解決元素遍歷的效率低下的問(wèn)題介却。 正是為了解決這個(gè)問(wèn)題，B+樹(shù)應(yīng)運(yùn)而生块茁。B+樹(shù)只要遍歷葉子節(jié)點(diǎn)就可以實(shí)現(xiàn)整棵樹(shù)的遍歷齿坷。而且在數(shù)據(jù)庫(kù)中基于范圍的查詢(xún)是非常頻繁的，而B(niǎo)樹(shù)不支持這樣的操作（或者說(shuō)效率太低）数焊。

????平衡二叉樹(shù)沒(méi)能充分利用磁盤(pán)預(yù)讀功能永淌，而B(niǎo)樹(shù)是為了充分利用磁盤(pán)預(yù)讀功能來(lái)而創(chuàng)建的一種數(shù)據(jù)結(jié)構(gòu)，也就是說(shuō)B樹(shù)就是為了作為索引才被發(fā)明出來(lái)的的佩耳。

1.局部性原理與磁盤(pán)預(yù)讀

????由于存儲(chǔ)介質(zhì)的特性遂蛀，磁盤(pán)本身存取就比主存慢很多，再加上機(jī)械運(yùn)動(dòng)耗費(fèi)蚕愤，磁盤(pán)的存取速度往往是主存的幾百分分之一，因此為了提高效率饺蚊，要盡量減少磁盤(pán)I/O萍诱。 為了達(dá)到這個(gè)目的，磁盤(pán)往往不是嚴(yán)格按需讀取污呼，而是每次都會(huì)預(yù)讀裕坊，即使只需要一個(gè)字節(jié)，磁盤(pán)也會(huì)從這個(gè)位置開(kāi)始燕酷，順序向后讀取一定長(zhǎng)度的數(shù)據(jù)放入內(nèi)存籍凝。這樣做的理論依據(jù)是計(jì)算機(jī)科學(xué)中著名的局部性原理：

????當(dāng)一個(gè)數(shù)據(jù)被用到時(shí)，其附近的數(shù)據(jù)也通常會(huì)馬上被使用苗缩。 程序運(yùn)行期間所需要的數(shù)據(jù)通常比較集中饵蒂。 由于磁盤(pán)順序讀取的效率很高（不需要尋道時(shí)間，只需很少的旋轉(zhuǎn)時(shí)間）酱讶，因此對(duì)于具有局部性的程序來(lái)說(shuō)退盯，預(yù)讀可以提高I/O效率。

2.為什么說(shuō)紅黑樹(shù)沒(méi)能充分利用磁盤(pán)預(yù)讀功能泻肯？

????紅黑樹(shù)這種結(jié)構(gòu)渊迁，h明顯要深的多。由于邏輯上很近的節(jié)點(diǎn)（父子）物理上可能很遠(yuǎn)灶挟，無(wú)法利用局部性琉朽，所以紅黑樹(shù)的I/O漸進(jìn)復(fù)雜度也為O(h)，效率明顯比B-Tree差很多稚铣。

????也就是說(shuō)箱叁，使用紅黑樹(shù)（平衡二叉樹(shù)）結(jié)構(gòu)的話墅垮，每次磁盤(pán)預(yù)讀中的很多數(shù)據(jù)是用不上的數(shù)據(jù)。因此蝌蹂，它沒(méi)能利用好磁盤(pán)預(yù)讀的提供的數(shù)據(jù)噩斟。然后又由于深度大（較B樹(shù)而言），所以進(jìn)行的磁盤(pán)IO操作更多孤个。

8剃允、B+索引數(shù)據(jù)結(jié)構(gòu)，和B樹(shù)的區(qū)別 齐鲤？

????除了以上的斥废，主要區(qū)別，其他請(qǐng)看B- B+ B*樹(shù)?實(shí)際上B-就是B樹(shù)给郊，二叉樹(shù)不叫B樹(shù)牡肉，像這種寫(xiě)法B-Tree，可以是B-樹(shù)也可以說(shuō)是B樹(shù)[-可能是連接符淆九，可能是翻譯問(wèn)題]统锤，B+ B* 是改善的B樹(shù)

9、索引的分類(lèi)（主鍵索引炭庙、唯一索引）饲窿，最左前綴原則，哪些情況索引會(huì)失效焕蹄？

????????1.關(guān)于索引優(yōu)化最前面已有了逾雄，不再羅列

????????2.各種索引區(qū)別

????????普通索引：最基本的索引，沒(méi)有任何限制腻脏。

????????唯一索引：與"普通索引"類(lèi)似鸦泳，不同的就是：索引列的值必須唯一，但允許有空值永品。

????????主鍵索引：它 是一種特殊的唯一索引做鹰，不允許有空值。

????????全文索引：僅可用于 MyISAM 表鼎姐，針對(duì)較大的數(shù)據(jù)誊垢，生成全文索引很耗時(shí)耗空間。

????????組合索引：為了更多的提高mysql效率可建立組合索引症见，遵循”最左前綴“原則喂走。

10、聚集索引和非聚集索引區(qū)別是什么谋作？

????聚集（clustered）索引芋肠，也叫聚簇索引。

????????定義：數(shù)據(jù)行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同遵蚜，一個(gè)表中只能擁有一個(gè)聚集索引帖池。

????非聚集（unclustered）索引奈惑。

????????定義：該索引中索引的邏輯順序與磁盤(pán)上行的物理存儲(chǔ)順序不同，一個(gè)表中可以擁有多個(gè)非聚集索引睡汹。

????總結(jié)

????????使用聚集索引的查詢(xún)效率要比非聚集索引的效率要高肴甸，但是如果需要頻繁去改變聚集索引的值，寫(xiě)入性能并不高囚巴，因?yàn)樾枰苿?dòng)對(duì)應(yīng)數(shù)據(jù)的物理位置原在。

????非聚集索引在查詢(xún)的時(shí)候可以的話就避免二次查詢(xún)，這樣性能會(huì)大幅提升彤叉。

????????不是所有的表都適合建立索引庶柿，只有數(shù)據(jù)量大表才適合建立索引，且建立在選擇性高的列上面性能會(huì)更好秽浇。

????????重復(fù)度高的可能使得索引失效

????????具體最前面有更詳細(xì)的????

11浮庐、schema(表結(jié)構(gòu))對(duì)性能的影響？

1.冗余數(shù)據(jù)的處理

????適當(dāng)?shù)臄?shù)據(jù)冗余可以提高系統(tǒng)的整體查詢(xún)性能(在P2P中,在userinfo對(duì)象中有realname和idnumber);

關(guān)系數(shù)據(jù)庫(kù)的三范式:

????第一范式（1NF）是對(duì)關(guān)系模式的基本要求柬焕，不滿(mǎn)足第一范式（1NF）的數(shù)據(jù)庫(kù)就不是關(guān)系數(shù)據(jù)庫(kù)审残，是指數(shù)據(jù)庫(kù)表的每一列都是不可分割的基本數(shù)據(jù)項(xiàng)，同一列中不能有多個(gè)值斑举；

????第二范式（2NF）要求數(shù)據(jù)庫(kù)表中的每個(gè)實(shí)例或行必須可以被惟一地區(qū)分搅轿。 即各字段和主鍵之間不存在部分依賴(lài)

????第三范式（3NF）要求一個(gè)數(shù)據(jù)庫(kù)表中不包含已在其它表中已包含的非主關(guān)鍵字信息。即在第二范式的基礎(chǔ)上懂昂，不存在傳遞依賴(lài) (不允許有冗余數(shù)據(jù))

2.大表拆小表介时，有大數(shù)據(jù)的列單獨(dú)拆成小表

????在一個(gè)數(shù)據(jù)庫(kù)中,一般不會(huì)設(shè)計(jì)屬性過(guò)多的表;

????在一個(gè)數(shù)據(jù)庫(kù)中,一般不會(huì)有超過(guò)500/1000萬(wàn)數(shù)據(jù)的表(拆表,按照邏輯拆分,按照業(yè)務(wù)拆分);

????有大數(shù)據(jù)的列單獨(dú)拆成小表(富文本編輯器,CKeditor);

3.根據(jù)需求的展示設(shè)置更合理的表結(jié)構(gòu)

4.把常用屬性分離成小表

????在P2P項(xiàng)目中,我們把logininfo和userinfo和account表拆成了三張表;

????減少查詢(xún)常用屬性需要查詢(xún)的列;

????便于常用屬性的集中緩存;

12没宾、數(shù)據(jù)庫(kù)的主從復(fù)制 凌彬？

????就算MYSQL拆成了多個(gè),也必須分出主和從,所有的寫(xiě)操作都必須要在主MYSQL 上完成;

????所有的從MYSQL的數(shù)據(jù)都來(lái)自于(同步于)主MYSQL;

????既然涉及到同步,那一定有延遲;有延遲,就一定可能在讀的時(shí)候產(chǎn)生臟數(shù)據(jù);所以,能夠在從MYSQL上進(jìn)行的讀操作,一定對(duì)實(shí)時(shí)性和臟數(shù)據(jù)有一定容忍度的數(shù)據(jù);比如,登陸日志,后臺(tái)報(bào)表,首頁(yè)統(tǒng)計(jì)信息來(lái)源;文章;資訊;SNS消息;

????在我們的P2P中,做主從,絕大部分的讀操作,都必須在主MYSQL上執(zhí)行;只有(登陸日志,報(bào)表,滿(mǎn)標(biāo)一審列表,滿(mǎn)標(biāo)二審列表,用戶(hù)的流水信息,充值明細(xì),投標(biāo)明細(xì)查詢(xún)類(lèi)的業(yè)務(wù)可以定位到從MYSQL);

????【一定注意】:在MYSQL主從時(shí),如果一個(gè)業(yè)務(wù)(service中的一個(gè)方法)中,如果既有R操作,又有W操作,因?yàn)閃操作一定要在主MYSQL上,所以在一個(gè)事務(wù)中所有的數(shù)據(jù)來(lái)源都只能來(lái)自于一個(gè)MYSQL

????要完成主從同步,就必須讓在Master上執(zhí)行的所有的DML和DDL能夠正確的在Salve上再執(zhí)行一遍;MYSQL選擇使用文件來(lái)記錄SQL;

????完成主從同步,第一個(gè)事情就是把在主服務(wù)器上的bin-log(二進(jìn)制文件)打開(kāi),bin-log文件就可以記錄在MYSQL上執(zhí)行的所有的DML+DDL+TCL;

????MYSQL使用被動(dòng)注冊(cè)的方式來(lái)讓從MYSQL請(qǐng)求同步主MYSQL的binlog;原因:被動(dòng)請(qǐng)求的方式,主的MYSQL不需要知道有哪些從的MYSQL,我額外添加/去掉從MYSQL服務(wù)器,對(duì)主MYSQL服務(wù)器的正常運(yùn)行沒(méi)有任何影響;

????第二步,從MYSQL后臺(tái)一個(gè)線程發(fā)送一個(gè)請(qǐng)求,到主服務(wù)器請(qǐng)求更新數(shù)據(jù);最重要的數(shù)據(jù)(我這次請(qǐng)求,請(qǐng)求你bin-log的哪一行數(shù)據(jù)之后的數(shù)據(jù))

????第三步,主MYSQL后臺(tái)一個(gè)線程接收到從MYSQL發(fā)送的請(qǐng)求,然后讀取bin-log文件中指定的內(nèi)容,并放在從MYSQL的請(qǐng)求響應(yīng)中;

????第四步,從MYSQL的請(qǐng)求帶回同步的數(shù)據(jù),然后寫(xiě)在從MYSQL中的relay-log(重做日志)中;relay-log中記錄的就是從主MYSQL中請(qǐng)求回來(lái)的哪些SQL數(shù)據(jù);

????第五步,從MYSQL后臺(tái)一個(gè)線程專(zhuān)門(mén)用于從relay-log中讀取同步回來(lái)的SQL,并寫(xiě)入到從MYSQL中,完成同步;

????MYSQL的主從同步是經(jīng)過(guò)高度優(yōu)化的,性能非常高;

這里東西太多，更多請(qǐng)參考mysql優(yōu)化的課程筆記

13循衰、explain和join

EXPLAIN:

使用方式： explain SQL;

返回結(jié)果：

ID：執(zhí)行查詢(xún)的序列號(hào)铲敛；

select_type：使用的查詢(xún)類(lèi)型

DEPENDENT SUBQUERY：子查詢(xún)中內(nèi)層的第一個(gè)SELECT，依賴(lài)于外部查詢(xún)的結(jié)果集会钝；

DEPENDENT UNION：子查詢(xún)中的UNION伐蒋，且為UNION 中從第二個(gè)SELECT 開(kāi)始的后面所有SELECT，同樣依賴(lài)于外部查詢(xún)的結(jié)果集迁酸；

PRIMARY：子查詢(xún)中的最外層查詢(xún)先鱼，注意并不是主鍵查詢(xún)；

SIMPLE：除子查詢(xún)或者UNION 之外的其他查詢(xún)奸鬓；

SUBQUERY：子查詢(xún)內(nèi)層查詢(xún)的第一個(gè)SELECT焙畔，結(jié)果不依賴(lài)于外部查詢(xún)結(jié)果集；

UNCACHEABLE SUBQUERY：結(jié)果集無(wú)法緩存的子查詢(xún)串远；

UNION：UNION 語(yǔ)句中第二個(gè)SELECT 開(kāi)始的后面所有SELECT宏多，第一個(gè)SELECT 為PRIMARY

UNION RESULT：UNION 中的合并結(jié)果儿惫；

table：這次查詢(xún)?cè)L問(wèn)的數(shù)據(jù)表；

type：對(duì)表所使用的訪問(wèn)方式：

all：全表掃描

const：讀常量伸但，且最多只會(huì)有一條記錄匹配肾请，由于是常量，所以實(shí)際上只需要讀一次更胖；

eq_ref：最多只會(huì)有一條匹配結(jié)果铛铁，一般是通過(guò)主鍵或者唯一鍵索引來(lái)訪問(wèn)；

fulltext：全文檢索函喉，針對(duì)full text索引列避归；

index：全索引掃描；

index_merge：查詢(xún)中同時(shí)使用兩個(gè)（或更多）索引管呵，然后對(duì)索引結(jié)果進(jìn)行merge 之后再讀取表數(shù)據(jù)梳毙；

index_subquery：子查詢(xún)中的返回結(jié)果字段組合是一個(gè)索引（或索引組合），但不是一個(gè)主鍵或者唯一索引捐下；

rang：索引范圍掃描账锹；

ref：Join 語(yǔ)句中被驅(qū)動(dòng)表索引引用查詢(xún)；

ref_or_null：與ref 的唯一區(qū)別就是在使用索引引用查詢(xún)之外再增加一個(gè)空值的查詢(xún)坷襟；

system：系統(tǒng)表奸柬，表中只有一行數(shù)據(jù)；

unique_subquery：子查詢(xún)中的返回結(jié)果字段組合是主鍵或者唯一約束婴程；

possible_keys：可選的索引廓奕；如果沒(méi)有使用索引，為null档叔；

key：最終選擇的索引桌粉；

key_len：被選擇的索引長(zhǎng)度；

ref：過(guò)濾的方式衙四，比如const（常量）铃肯，column（join），func（某個(gè)函數(shù)）传蹈；

rows：查詢(xún)優(yōu)化器通過(guò)收集到的統(tǒng)計(jì)信息估算出的查詢(xún)條數(shù)押逼；

Extra：查詢(xún)中每一步實(shí)現(xiàn)的額外細(xì)節(jié)信息

Distinct：查找distinct 值，所以當(dāng)mysql 找到了第一條匹配的結(jié)果后惦界，將停止該值的查詢(xún)而轉(zhuǎn)為后面其他值的查詢(xún)挑格；

Full scan on NULL key：子查詢(xún)中的一種優(yōu)化方式，主要在遇到無(wú)法通過(guò)索引訪問(wèn)null值的使用使用沾歪；

Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通過(guò)收集到的統(tǒng)計(jì)信息判斷出不可能存在結(jié)果漂彤；

No tables：Query 語(yǔ)句中使用FROM DUAL 或者不包含任何FROM 子句；

Not exists：在某些左連接中MySQL Query Optimizer 所通過(guò)改變?cè)蠶uery 的組成而使用的優(yōu)化方法，可以部分減少數(shù)據(jù)訪問(wèn)次數(shù)显歧；

Select tables optimized away：當(dāng)我們使用某些聚合函數(shù)來(lái)訪問(wèn)存在索引的某個(gè)字段的時(shí)候仪或，MySQL Query Optimizer 會(huì)通過(guò)索引而直接一次定位到所需的數(shù)據(jù)行完成整個(gè)查詢(xún)。當(dāng)然士骤，前提是在Query 中不能有GROUP BY 操作范删。如使用MIN()或者M(jìn)AX（）的時(shí)候；

Using filesort：當(dāng)我們的Query 中包含ORDER BY 操作拷肌，而且無(wú)法利用索引完成排序操作的時(shí)候到旦，MySQL Query Optimizer 不得不選擇相應(yīng)的排序算法來(lái)實(shí)現(xiàn)。

Using index：所需要的數(shù)據(jù)只需要在Index 即可全部獲得而不需要再到表中取數(shù)據(jù)巨缘；

Using index for group-by：數(shù)據(jù)訪問(wèn)和Using index 一樣添忘，所需數(shù)據(jù)只需要讀取索引即可，而當(dāng)Query 中使用了GROUP BY 或者DISTINCT 子句的時(shí)候若锁，如果分組字段也在索引中搁骑，Extra 中的信息就會(huì)是Using index for group-by；

Using temporary：當(dāng)MySQL 在某些操作中必須使用臨時(shí)表的時(shí)候又固，在Extra 信息中就會(huì)出現(xiàn)Using temporary 仲器。主要常見(jiàn)于GROUP BY 和ORDER BY 等操作中。

Using where：如果我們不是讀取表的所有數(shù)據(jù)仰冠，或者不是僅僅通過(guò)索引就可以獲取所有需要的數(shù)據(jù)乏冀，則會(huì)出現(xiàn)Using where 信息；

Using where with pushed condition：這是一個(gè)僅僅在NDBCluster 存儲(chǔ)引擎中才會(huì)出現(xiàn)的信息洋只，而且還需要通過(guò)打開(kāi)Condition Pushdown 優(yōu)化功能才可能會(huì)被使用说庭≈叫梗控制參數(shù)為engine_condition_pushdown 眯亦。

profiling: Query Profiler是MYSQL5.1之后提供的一個(gè)很方便的用于診斷Query執(zhí)行的工具误算，能夠準(zhǔn)確的獲取一條查詢(xún)執(zhí)行過(guò)程中的CPU，IO等情況舷礼；

開(kāi)啟profiling：set profiling=1

執(zhí)行QUERY鹃彻，在profiling過(guò)程中所有的query都可以記錄下來(lái)

查看記錄的query：show profiles

選擇要查看的profile：show profile cpu, block io for query 6

status是執(zhí)行SQL的詳細(xì)過(guò)程

Duration：執(zhí)行的具體時(shí)間

CPU_user：用戶(hù)CPU時(shí)間

CPU_system：系統(tǒng)CPU時(shí)間

Block_ops_in：IO輸入次數(shù)

Block_ops_out：IO輸出次數(shù)

profiling只對(duì)本次會(huì)話有效

JOIN的原理

????????在mysql中使用Nested Loop Join來(lái)實(shí)現(xiàn)join郊闯； A JOIN B：通過(guò)A表的結(jié)果集作為循環(huán)基礎(chǔ)妻献，一條一條的通過(guò)結(jié)果集中的數(shù)據(jù)作為過(guò)濾條件到下一個(gè)表中查詢(xún)數(shù)據(jù)，然后合并結(jié)果团赁；

JOIN的優(yōu)化原則

????盡可能減少Join 語(yǔ)句中的Nested Loop 的循環(huán)總次數(shù)育拨，用小結(jié)果集驅(qū)動(dòng)大結(jié)果集；

????優(yōu)先優(yōu)化Nested Loop 的內(nèi)層循環(huán)欢摄；

????保證Join 語(yǔ)句中被驅(qū)動(dòng)表上Join 條件字段已經(jīng)被索引熬丧；

????擴(kuò)大join buffer的大小怀挠；

14析蝴、內(nèi)連接害捕、外連接、交叉連接闷畸、笛卡兒積等

內(nèi)連接?

????????只有兩個(gè)表相匹配的行才能在結(jié)果集中出現(xiàn) 分為三種：等值連接尝盼、自然連接、不等連接?

外連接?

????????左外連接(LEFT OUTER JOIN或LEFT JOIN) 以左邊為準(zhǔn)佑菩，右邊沒(méi)用則為空

????????右外連接(RIGHT OUTER JOIN或RIGHT JOIN) 以右邊為準(zhǔn)盾沫，左邊沒(méi)有則為空

????????全外連接(FULL OUTER JOIN或FULL JOIN) 左右均可能為空?

交叉連接

????????沒(méi)有WHERE 子句，它返回連接表中所有數(shù)據(jù)行的笛卡爾積?

笛卡兒積

15殿漠、死鎖怎么解決赴精？

產(chǎn)生死鎖的原因主要是

????????系統(tǒng)資源不足。

????????進(jìn)程運(yùn)行推進(jìn)的順序不合適绞幌。

????????資源分配不當(dāng)?shù)取?/p>

????????如果系統(tǒng)資源充足蕾哟，進(jìn)程的資源請(qǐng)求都能夠得到滿(mǎn)足，死鎖出現(xiàn)的可能性就很低莲蜘，否則就會(huì)因爭(zhēng)奪有限的資源而陷入死鎖渐苏。其次，進(jìn)程運(yùn)行推進(jìn)順序與速度不同菇夸，也可能產(chǎn)生死鎖琼富。

產(chǎn)生死鎖的四個(gè)必要條件

????????1. 互斥條件：一個(gè)資源每次只能被一個(gè)進(jìn)程使用。

????????2. 請(qǐng)求與保持條件：一個(gè)進(jìn)程因請(qǐng)求資源而阻塞時(shí)庄新，對(duì)已獲得的資源保持不放鞠眉。

????????3. 不剝奪條件:進(jìn)程已獲得的資源，在末使用完之前择诈，不能強(qiáng)行剝奪械蹋。

????????4. 循環(huán)等待條件:若干進(jìn)程之間形成一種頭尾相接的循環(huán)等待資源關(guān)系。

這四個(gè)條件是死鎖的必要條件羞芍，只要系統(tǒng)發(fā)生死鎖哗戈，這些條件必然成立，而只要上述條件之一不滿(mǎn)足荷科，就不會(huì)發(fā)生死鎖唯咬。

死鎖的預(yù)防和解除

????????理解了死鎖的原因，尤其是產(chǎn)生死鎖的四個(gè)必要條件畏浆，就可以最大可能地避免胆胰、預(yù)防和解除死鎖。所以刻获，在系統(tǒng)設(shè)計(jì)蜀涨、進(jìn)程調(diào)度等方面注意如何不讓這四個(gè)必要條件成立，如何確定資源的合理分配算法，避免進(jìn)程永久占據(jù)系統(tǒng)資源厚柳。此外氧枣，也要防止進(jìn)程在處于等待狀態(tài)的情況下占用資源,在系統(tǒng)運(yùn)行過(guò)程中，對(duì)進(jìn)程發(fā)出的每一個(gè)系統(tǒng)能夠滿(mǎn)足的資源申請(qǐng)進(jìn)行動(dòng)態(tài)檢查别垮，并根據(jù)檢查結(jié)果決定是否分配資源挑胸，若分配后系統(tǒng)可能發(fā)生死鎖，則不予分配宰闰，否則予以分配 茬贵。因此，對(duì)資源的分配要給予合理的規(guī)劃移袍。 如何將死鎖減至最少

????????雖然不能完全避免死鎖解藻，但可以使死鎖的數(shù)量減至最少。將死鎖減至最少可以增加事務(wù)的吞吐量并減少系統(tǒng)開(kāi)銷(xiāo)葡盗，因?yàn)橹挥泻苌俚氖聞?wù)回滾螟左，而回滾會(huì)取消事務(wù)執(zhí)行的所有工作。由于死鎖時(shí)回滾而由應(yīng)用程序重新提交觅够。

下列方法有助于最大限度地降低死鎖

????按同一順序訪問(wèn)對(duì)象胶背。

????避免事務(wù)中的用戶(hù)交互。

????保持事務(wù)簡(jiǎn)短并在一個(gè)批處理中喘先。

????使用低隔離級(jí)別钳吟。

????使用綁定連接。

具體

????????按同一順序訪問(wèn)對(duì)象 -- 如果所有并發(fā)事務(wù)按同一順序訪問(wèn)對(duì)象窘拯，則發(fā)生死鎖的可能性會(huì)降低红且。例如，如果兩個(gè)并發(fā)事務(wù)獲得 Supplier 表上的鎖涤姊，然后獲得 Part 表上的鎖暇番，則在其中一個(gè)事務(wù)完成之前，另一個(gè)事務(wù)被阻塞在 Supplier 表上思喊。第一個(gè)事務(wù)提交或回滾后壁酬，第二個(gè)事務(wù)繼續(xù)進(jìn)行。不發(fā)生死鎖恨课。將存儲(chǔ)過(guò)程用于所有的數(shù)據(jù)修改可以標(biāo)準(zhǔn)化訪問(wèn)對(duì)象的順序舆乔。

????????避免事務(wù)中的用戶(hù)交互 -- 避免編寫(xiě)包含用戶(hù)交互的事務(wù)，因?yàn)檫\(yùn)行沒(méi)有用戶(hù)交互的批處理的速度要遠(yuǎn)遠(yuǎn)快于用戶(hù)手動(dòng)響應(yīng)查詢(xún)的速度庄呈，例如答復(fù)應(yīng)用程序請(qǐng)求參數(shù)的提示蜕煌。例如派阱，如果事務(wù)正在等待用戶(hù)輸入诬留，而用戶(hù)去吃午餐了或者甚至回家過(guò)周末了，則用戶(hù)將此事務(wù)掛起使之不能完成。這樣將降低系統(tǒng)的吞吐量文兑，因?yàn)槭聞?wù)持有的任何鎖只有在事務(wù)提交或回滾時(shí)才會(huì)釋放盒刚。即使不出現(xiàn)死鎖的情況，訪問(wèn)同一資源的其它事務(wù)也會(huì)被阻塞绿贞，等待該事務(wù)完成因块。

????????保持事務(wù)簡(jiǎn)短并在一個(gè)批處理中 -- 在同一數(shù)據(jù)庫(kù)中并發(fā)執(zhí)行多個(gè)需要長(zhǎng)時(shí)間運(yùn)行的事務(wù)時(shí)通常發(fā)生死鎖。事務(wù)運(yùn)行時(shí)間越長(zhǎng)籍铁，其持有排它鎖或更新鎖的時(shí)間也就越長(zhǎng)涡上，從而堵塞了其它活動(dòng)并可能導(dǎo)致死鎖。 保持事務(wù)在一個(gè)批處理中拒名，可以最小化事務(wù)的網(wǎng)絡(luò)通信往返量吩愧，減少完成事務(wù)可能的延遲并釋放鎖。

????????使用低隔離級(jí)別 -- 確定事務(wù)是否能在更低的隔離級(jí)別上運(yùn)行增显。執(zhí)行提交讀允許事務(wù)讀取另一個(gè)事務(wù)已讀妊慵选（未修改）的數(shù)據(jù)，而不必等待第一個(gè)事務(wù)完成同云。使用較低的隔離級(jí)別（例如提交讀）而不使用較高的隔離級(jí)別（例如可串行讀）可以縮短持有共享鎖的時(shí)間糖权，從而降低了鎖定爭(zhēng)奪。

????????使用綁定連接 -- 使用綁定連接使同一應(yīng)用程序所打開(kāi)的兩個(gè)或多個(gè)連接可以相互合作炸站。次級(jí)連接所獲得的任何鎖可以象由主連接獲得的鎖那樣持有星澳，反之亦然，因此不會(huì)相互阻塞旱易。

16募判、varchar和char的使用場(chǎng)景？

1.varchar的特點(diǎn)

????????存儲(chǔ)變長(zhǎng)字符串咒唆，只占用必要的存儲(chǔ)空間

????????列的長(zhǎng)度小于255届垫，只用額外的1個(gè)字節(jié)來(lái)記錄長(zhǎng)度

????????列的長(zhǎng)度大于255，只用額外的2個(gè)字節(jié)來(lái)記錄長(zhǎng)度

2.char的特點(diǎn)

????????存儲(chǔ)定長(zhǎng)字符串全释，最大為255字節(jié)

????????要?jiǎng)h除字符串末尾的空格装处。

3.如何區(qū)別使用常場(chǎng)景

????????一、根據(jù)字符的長(zhǎng)度來(lái)判斷浸船。如某個(gè)字段妄迁，像人的名字，其最長(zhǎng)的長(zhǎng)度也是有限的李命。如我們給其分配18個(gè)字符長(zhǎng)度即可登淘。此時(shí)雖然每個(gè)人的名字長(zhǎng)度有可能 不同，但是即使為其分配了固定長(zhǎng)度的字符類(lèi)型封字，即18個(gè)字符長(zhǎng)度黔州，最后浪費(fèi)的空間也不是很大耍鬓。而如果采用NVARCHAR數(shù)據(jù)類(lèi)型時(shí)，萬(wàn)一以后需要改名流妻， 而原先的存儲(chǔ)空間不足用來(lái)容納新的值牲蜀，反而會(huì)造成一些額外的工作。在這種情況下绅这，進(jìn)行均衡時(shí)涣达，會(huì)認(rèn)為采用CHAR固定長(zhǎng)度的數(shù)據(jù)類(lèi)型更好。 在實(shí)際項(xiàng)目中证薇， 如果某個(gè)字段的字符長(zhǎng)度比較短此時(shí)一般是采用固定字符長(zhǎng)度度苔。

????????二、是考慮其長(zhǎng)度的是否相近浑度。如果某個(gè)字段其長(zhǎng)度雖然比較長(zhǎng)林螃，但是其長(zhǎng)度總是近似的，如一般在90個(gè)到100個(gè)字符之間俺泣，甚至是相同的長(zhǎng)度疗认。此時(shí)比較 適合采用CHAR字符類(lèi)型。比較典型的應(yīng)用就是MD5哈希值伏钠。當(dāng)利用MD5哈希值來(lái)存儲(chǔ)用戶(hù)密碼時(shí)横漏，就非常使用采用CHAR字符類(lèi)型。因?yàn)槠溟L(zhǎng)度是相同 的熟掂。另外缎浇，像用來(lái)存儲(chǔ)用戶(hù)的身份證號(hào)碼等等，一般也建議使用CHAR類(lèi)型的數(shù)據(jù)赴肚。 另外請(qǐng)大家考慮一個(gè)問(wèn)題素跺，CHAR（1）與VARCHAR（1）兩這個(gè)定義，會(huì)有什么區(qū)別呢誉券？雖然這兩個(gè)都只能夠用來(lái)保存單個(gè)的字符指厌，但是 VARCHAR要比CHAR多占用一個(gè)存儲(chǔ)位置。這主要是因?yàn)槭褂肰ARCHAR數(shù)據(jù)類(lèi)型時(shí)踊跟，會(huì)多用1個(gè)字節(jié)用來(lái)存儲(chǔ)長(zhǎng)度信息踩验。這個(gè)管理上的開(kāi)銷(xiāo)CHAR 字符類(lèi)型是沒(méi)有的。

????????三商玫、從碎片角度進(jìn)行考慮箕憾。使用CHAR字符型時(shí)，由于存儲(chǔ)空間都是一次性分配的拳昌。為此某個(gè)字段的內(nèi)容袭异，其都是存儲(chǔ)在一起的。單從這個(gè)角度來(lái)講炬藤，其不 存在碎片的困擾御铃。而可變長(zhǎng)度的字符數(shù)據(jù)類(lèi)型碴里，其存儲(chǔ)的長(zhǎng)度是可變的。當(dāng)其更改前后數(shù)據(jù)長(zhǎng)度不一致時(shí)畅买，就不可避免的會(huì)出現(xiàn)碎片的問(wèn)題并闲。故使用可變長(zhǎng)度的字符 型數(shù)據(jù)時(shí)细睡，數(shù)據(jù)庫(kù)管理員要時(shí)不時(shí)的對(duì)碎片進(jìn)行整理谷羞。如執(zhí)行數(shù)據(jù)庫(kù)導(dǎo)出導(dǎo)入作業(yè)，來(lái)消除碎片溜徙。

????????四湃缎、即使使用Varchar數(shù)據(jù)類(lèi)型，也不能夠太過(guò)于慷慨蠢壹。這是什么意思呢嗓违？如現(xiàn)在用戶(hù)需要存儲(chǔ)一個(gè)地址信息。根據(jù)評(píng)估图贸，只要使用100個(gè)字符就可 以了蹂季。但是有些數(shù)據(jù)庫(kù)管理員會(huì)認(rèn)為，反正Varchar數(shù)據(jù)類(lèi)型是根據(jù)實(shí)際的需要來(lái)分配長(zhǎng)度的疏日。還不如給其大一點(diǎn)的呢偿洁。為此他們可能會(huì)為這個(gè)字段一次性分 配200個(gè)字符的存儲(chǔ)空間。這VARCHAR（100）與VARCHAR（200）真的相同嗎沟优？結(jié)果是否定的涕滋。 雖然他們用來(lái)存儲(chǔ)90個(gè)字符的數(shù)據(jù)，其存儲(chǔ) 空間相同挠阁。但是對(duì)于內(nèi)存的消耗是不同的宾肺。對(duì)于VARCHAR數(shù)據(jù)類(lèi)型來(lái)說(shuō)，硬盤(pán)上的存儲(chǔ)空間雖然都是根據(jù)實(shí)際字符長(zhǎng)度來(lái)分配存儲(chǔ)空間的侵俗，但是對(duì)于內(nèi)存來(lái) 說(shuō)锨用，則不是。其時(shí)使用固定大小的內(nèi)存塊來(lái)保存值隘谣。簡(jiǎn)單的說(shuō)黔酥，就是使用字符類(lèi)型中定義的長(zhǎng)度，即200個(gè)字符空間洪橘。顯然跪者，這對(duì)于排序或者臨時(shí)表（這些內(nèi)容都 需要通過(guò)內(nèi)存來(lái)實(shí)現(xiàn)）作業(yè)會(huì)產(chǎn)生比較大的不利影響。 所以如果某些字段會(huì)涉及到文件排序或者基于磁盤(pán)的臨時(shí)表時(shí)熄求，分配VARCHAR數(shù)據(jù)類(lèi)型時(shí)仍然不能夠太 過(guò)于慷慨渣玲。還是要評(píng)估實(shí)際需要的長(zhǎng)度，然后選擇一個(gè)最長(zhǎng)的字段來(lái)設(shè)置字符長(zhǎng)度弟晚。如果為了考慮冗余忘衍，可以留10%左右的字符長(zhǎng)度逾苫。千萬(wàn)不能認(rèn)為其為根據(jù)實(shí)際 長(zhǎng)度來(lái)分配存儲(chǔ)空間，而隨意的分配長(zhǎng)度枚钓，或者說(shuō)干脆使用最大的字符長(zhǎng)度铅搓。

17、mysql并發(fā)情況下怎么解決搀捷？

????????代碼中sql語(yǔ)句優(yōu)化星掰； 數(shù)據(jù)庫(kù)字段優(yōu)化，索引優(yōu)化嫩舟；加緩存氢烘，redis/memcache等；主從家厌，讀寫(xiě)分離 集群 分流 橫向擴(kuò)展播玖；分區(qū)；垂直拆分饭于，解耦模塊蜀踏；水平切分 分片

18、引申 Redis | Memcached

????memcached全面剖析

????Redis

????redis數(shù)據(jù)結(jié)構(gòu)有哪些?

????redis隊(duì)列應(yīng)用場(chǎng)景掰吕？

????redis和Memcached（支持?jǐn)?shù)據(jù)持久化）果覆？

????分布式使用場(chǎng)景（儲(chǔ)存session等）

????發(fā)布/訂閱使用場(chǎng)景

以上參考《redis in action》?