數(shù)據(jù)庫的三范式

     第一范式：1NF是對屬性的原子性約束,，要求屬性具有原子性,，不可再分解；

     第二范式：2NF是對記錄的惟一性約束，要求記錄有惟一標識,，即實體的惟一性,；  

     第三范式：3NF是對字段冗余性的約束，即任何字段不能由其他字段派生出來，它要求字段沒有冗余,。

SQL執(zhí)行順序

     關(guān)鍵字：select,、from,、join、on、where,、group by,、having,、order by、distinct執(zhí)行順序

     from > join > on > where > group by > having > select > distinct > order by

UNION/ALL,、EXCEPT/ALL和INTERSECT/ALL

     UNION/ALL：組合多個結(jié)果表，并消去表中重復(fù)行,，和ALL一起使用時,，不消除重復(fù)行。

     EXCEPT/ALL：在table1中但不在table2中的行并消除重復(fù)行,，和ALL一起使用時,，不消除重復(fù)行。

     INTERSECT/ALL：包括table1和table2中都有的行并消除重復(fù)行,，和ALL一起使用時,，不消除重復(fù)行。

內(nèi)連接,、外連接,、笛卡兒積(交叉連接)

     內(nèi)連接：結(jié)果僅包含符合連接條件的兩表中的行

     外連接：結(jié)果包含符合條件的行，同時包含不符合條件的行（分為左外連接,、右外連接和全外連接）

     左外連接：左表全部行+右表匹配的行,，如果左表中某行在右表中沒有匹配的行，則顯示NULL,。

     右外連接：右表全部行+左表匹配的行,。

     全外連接：全外連接：不管匹配不匹配，全部顯示出來,。

     交叉連接：返回左表中所有行與右表中所有行的組合,，也稱笛卡爾積。

VARCHAE和CHAR的區(qū)別

     CHAR：指定長度,，如果一個數(shù)據(jù)實際長度比設(shè)定長度短,，那么它將按照設(shè)定（最長）長度儲存，不足部分,，填補空格,。適用于固定長度的字段，如性別,、手機號等,。

     VARCHAE：指定最大長度，但該長度可變,，即如果數(shù)據(jù)的實際長度比設(shè)定長度短,，那么它將按照實際長度儲存，而不占用剩余的空間。 適用于非固定長度的字段,，如姓名,、城市名等。

DROP,，DELETE與TRUNCATE的區(qū)別

     DROP直接刪掉表 ,。 

     TRUNCATE刪除表中數(shù)據(jù)，再插入時自增長id又從1開始 ,。 

     DELETE刪除表中數(shù)據(jù),，可以加WHERE字句。

數(shù)據(jù)庫事務(wù)(ACID)

     原子性（Atomicity）一個事務(wù)必須被視為一個不可分割的最小工作單元,，整個事務(wù)中的所有操作要么全部提交成功，要么全部失敗回滾,，對于一個事務(wù)來說,，不可能只執(zhí)行其中的一部分操作。

     一致性（Consistency）數(shù)據(jù)庫總是從一個一致性的狀態(tài)轉(zhuǎn)換到另一個一致性的狀態(tài),。

     隔離性（Isolation）一個事務(wù)所做的修改在最終提交以前,，對其他事務(wù)是不可見的。

     持久性（Durability）一旦事務(wù)提交,，則其所做的修改不會永久保存到數(shù)據(jù)庫,。

數(shù)據(jù)庫的樂觀鎖和悲觀鎖

     悲觀鎖：對數(shù)據(jù)被外界修改持保守態(tài)度，因此,，在整個數(shù)據(jù)處理過程中,，將數(shù)據(jù)處于鎖定狀態(tài)。（悲觀鎖的實現(xiàn),，往往依靠數(shù)據(jù)庫提供的鎖機制）

     樂觀鎖：大多是基于數(shù)據(jù)版本 （ Version ）記錄機制實現(xiàn),。

     即為數(shù)據(jù)增加一個版本標識，在基于數(shù)據(jù)庫表的版本解決方案中,，一般是通過為數(shù)據(jù)庫表增加一個 “version” 字段來 實現(xiàn),。 讀取出數(shù)據(jù)時，將此版本號一同讀出,，之后更新時,，對此版本號加一。此時,，將提 交數(shù)據(jù)的版本數(shù)據(jù)與數(shù)據(jù)庫表對應(yīng)記錄的當前版本信息進行比對,，如果提交的數(shù)據(jù) 版本號大于數(shù)據(jù)庫表當前版本號，則予以更新,，否則認為是過期數(shù)據(jù)

臟讀,、虛讀和不可重復(fù)讀

     臟讀：一個事務(wù)讀取到了另外一個事務(wù)沒有提交的數(shù)據(jù)；(修改的數(shù)據(jù)還未提交就被另一個事務(wù)使用這個數(shù)據(jù))

     不可重復(fù)讀：在同一事務(wù)中，兩次讀取同一數(shù)據(jù),，得到內(nèi)容不同,；（兩次讀取同一數(shù)據(jù)之間，另一個事務(wù)對數(shù)據(jù)進行了修改）

     虛度：同一事務(wù)中,，用同樣的操作讀取兩次,，得到的記錄數(shù)不相同；(兩次相同的操作之間,，另一個事務(wù)對數(shù)據(jù)進行了新增或刪除)

MySQL中的四種事務(wù)隔離級別

     序列化 (Serializable)：可避免臟讀,、不可重復(fù)讀、幻讀的發(fā)生,。

     可重復(fù)讀 (Repeatable read)：可避免臟讀,、不可重復(fù)讀的發(fā)生。

     讀已提交(Read committed)：可避免臟讀的發(fā)生,。

     讀未提交 (Read uncommitted)：最低級別,，任何情況都無法保證。

MVCC(多版本并發(fā)控制)

     MVCC最大的好處：讀不加鎖,，讀寫不沖突,。

     1.多版本并發(fā)控制（MVCC）是一種用來解決讀-寫沖突的無鎖并發(fā)控制，也就是為事務(wù)分配單向增長的時間戳,，為每個修改保存一個版本,，版本與事務(wù)時間戳關(guān)聯(lián)，讀操作只讀該事務(wù)開始前的數(shù)據(jù)庫的快照,。 這樣在讀操作不用阻塞寫操作,，寫操作不用阻塞讀操作的同時，避免了臟讀和不可重復(fù)讀

     2.當MVCC數(shù)據(jù)庫更新一條數(shù)據(jù)時,，不會直接重寫原始的數(shù)據(jù),，而是修改新創(chuàng)建的數(shù)據(jù)副本。因此會有多個版本的數(shù)據(jù)被保存下來,。每個事務(wù)看到的數(shù)據(jù)版本依賴于隔離級別的實現(xiàn),。MVCC里最通用的隔離級別的實現(xiàn)就是快照隔離。在快照隔離的情況下,，事務(wù)只會獲取到數(shù)據(jù)的在事務(wù)開始前的狀態(tài),。

MYSQL主從復(fù)制

概念解釋     MySQL的Replication（英文為復(fù)制）是一個多MySQL數(shù)據(jù)庫做主從同步的方案，特點是異步復(fù)制,。     MySQL Replication 就是從服務(wù)器拉取主服務(wù)器上的二進制日志文件,，然后再將日志文件解析成相應(yīng)的SQL語句在從服務(wù)器上重新執(zhí)行一遍主服務(wù)器的操作，通過這種方式來保證數(shù)據(jù)的一致性,。主從復(fù)制原理     1.master(主)在執(zhí)行sql之后,，記錄二進制log文件（bin-log）,。     2.slave(從)連接master，并從master獲取binlog,，存于本地relay-log中,，然后從上次記住的位置起執(zhí)行SQL語句，一旦遇到錯誤則停止同步,。        簡記：MySQL的主從復(fù)制,，實際上就是Master記錄自己的執(zhí)行日志binlog，然后發(fā)送給Slave,，Slave解析日志并執(zhí)行,，來實現(xiàn)數(shù)據(jù)復(fù)制。主服務(wù)器流程分析     首先bin-log日志文件加鎖,，然后讀取更新的操作,，讀取完畢以后將鎖釋放掉，最后將讀取的記錄發(fā)送給從服務(wù)器,。從服務(wù)器流程分析     在一次主從復(fù)制過程中需要用到三個線程：Binlog dump 線程,、Slave I/O 線程和Slave SQL線程，其中Binlog dump 線程在主服務(wù)器上面,，剩下的兩個線程是在從服務(wù)器上面工作的。     這兩個線程在從服務(wù)器上面的工作流程如下圖所示：如何提高Mysql主從復(fù)制的效率,？    1.master 端       master端有2個參數(shù)可以控制,。        Binlog_Do_DB : 設(shè)定哪些數(shù)據(jù)庫需要記錄Binlog。        Binlog_Ignore_DB : 設(shè)定哪些數(shù)據(jù)庫不要記錄Binlog,。    2.slave 端       slave端有6個參數(shù)可以控制,。        Replicate_Do_DB : 設(shè)定須要復(fù)制的數(shù)據(jù)庫，多個DB用逗號分隔,。        Replicate_Ignore_DB : 設(shè)定可以忽略的數(shù)據(jù)庫,。        Replicate_Do_Table : 設(shè)定須要復(fù)制的Table。        Replicate_Ignore_Table : 設(shè)定可以忽略的Table,。        Replicate_Wild_Do_Table : 功能同Replicate_Do_Table,，但可以帶通配符來進行設(shè)置。        Replicate_Wild_Ignore_Table : 功能同Replicate_Ig-nore_Table,，可帶通配符設(shè)置,。

數(shù)據(jù)庫的分庫分表

分表      對于訪問極為頻繁且數(shù)據(jù)量巨大的單表來說，我們首先要做的就是減少單表的記錄條數(shù),，以便減少數(shù)據(jù)查詢所需要的時間,，提高數(shù)據(jù)庫的吞吐，這就是所謂的分表,！      將原有的單表分為256個表  算法 ：user_id%256      拆分后表的數(shù)量一般為2的n次方,，就是上面拆分成256張表的由來！分庫     分表能夠解決單表數(shù)據(jù)量過大帶來的查詢效率下降的問題，但是,，卻無法給數(shù)據(jù)庫的并發(fā)處理能力帶來質(zhì)的提升,。面對高并發(fā)的讀寫訪問，對數(shù)據(jù)庫進行拆分,，從而提高數(shù)據(jù)庫寫入能力,，這就是所謂的分庫!     將原有的單庫分為256個庫 算法 ：user_id%256分庫分表     有時數(shù)據(jù)庫可能既面臨著高并發(fā)訪問的壓力，又需要面對海量數(shù)據(jù)的存儲問題,，這時需要對數(shù)據(jù)庫既采用分表策略,，又采用分庫策略，以便同時擴展系統(tǒng)的并發(fā)處理能力,，以及提升單表的查詢性能,，這就是所謂的分庫分表。     一種分庫分表的路由策略如下：    1. 中間變量 = user_id % (分庫數(shù)量 * 每個庫的表數(shù)量)    2. 庫 = 取整數(shù) (中間變量 / 每個庫的表數(shù)量)    3. 表 = 中間變量 % 每個庫的表數(shù)量舉例：    假設(shè)將原來的單庫單表order拆分成256個庫,，每個庫包含1024個表,，那么按照前面所提到的路由策略，對于user_id=262145 的訪問,，路由的計算過程如下：   1.  中間變量 = 262145 % (256 * 1024) = 1   2.  庫 = 取整 (1/1024) = 0   3.  表 = 1 % 1024 = 1   這就意味著,，對于user_id=262145 的訂單記錄的查詢和修改，將被路由到第0個庫的第1個order_1表中執(zhí)行?。,。?/span>

數(shù)據(jù)庫SQL優(yōu)化

1. 對查詢進行優(yōu)化，要盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引,。2. 應(yīng)盡量避免在 where 子句中對字段進行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進行全表掃描,。如：select id from t where num is null

   最好不要給數(shù)據(jù)庫留NULL，盡可能的使用 NOT NULL填充數(shù)據(jù)庫,。備注,、描述、評論之類的可以設(shè)置為 NULL,，其他的,，最好不要使用NULL。

   不要以為 NULL 不需要空間,，比如：char(100) 型,，在字段建立時，空間就固定了,， 不管是否插入值（NULL也包含在內(nèi)）,，都是占用 100個字符的空間的,，如果是           varchar這樣的變長字段， null 不占用空間,。

   可以在num上設(shè)置默認值0,，確保表中num列沒有null值，然后這樣查詢：select id from t where num = 03. 應(yīng)盡量避免在 where 子句中使用 != 或 <> 操作符,，否則將引擎放棄使用索引而進行全表掃描,。4. 應(yīng)盡量避免在 where 子句中使用 or 來連接條件，如果一個字段有索引,，一個字段沒有索引,，將導(dǎo)致引擎放棄使用索引而進行全表掃描。如：select id from t where num=10 or Name = 'admin'可以這樣查詢：select id from t where num = 10union allselect id from t where Name = 'admin'5. in 和 not in 也要慎用,，否則會導(dǎo)致全表掃描,。如：select id from t where num in(1,2,3)對于連續(xù)的數(shù)值，能用 between 就不要用 in 了：select id from t where num between 1 and 3很多時候用 exists 代替 in 是一個好的選擇：select num from a where num in(select num from b)用下面的語句替換：select num from a where exists(select 1 from b where num=a.num)6. 下面的查詢也將導(dǎo)致全表掃描select id from t where name like ‘%abc%’   若要提高效率,，可以考慮全文檢索,。7. 如果在 where 子句中使用參數(shù)，也會導(dǎo)致全表掃描,。因為SQL只有在運行時才會解析局部變量,，但優(yōu)化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇,。然 而,，如果在編譯時建立訪問計劃，變量的值還是未知的,，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：select id from t where num = @num可以改為強制查詢使用索引：select id from t with(index(索引名)) where num = @num 應(yīng)盡量避免在 where 子句中對字段進行表達式操作,，這將導(dǎo)致引擎放棄使用索引而進行全表掃描,。如：select id from t where num/2 = 100應(yīng)改為：select id from t where num = 100*29. 應(yīng)盡量避免在where子句中對字段進行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進行全表掃描,。如：select id from t where substring(name,1,3) = ’abc’       -–name以abc開頭的idselect id from t where datediff(day,createdate,’2005-11-30′) = 0     -–‘2005-11-30’    --生成的id應(yīng)改為:select id from t where name like 'abc%'select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'10. 不要在 where 子句中的“=”左邊進行函數(shù),、算術(shù)運算或其他表達式運算，否則系統(tǒng)將可能無法正確使用索引,。11. 在使用索引字段作為條件時,，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統(tǒng)使用該索引,，否則該索引將不會被使用,，并且應(yīng)盡可能的讓字段順序與索引順序相一致。12. 不要寫一些沒有意義的查詢,，如需要生成一個空表結(jié)構(gòu)：select col1,col2 into #t from t where 1=0這類代碼不會返回任何結(jié)果集,，但是會消耗系統(tǒng)資源的,，應(yīng)改成這樣：create table #t(…)13. Update 語句，如果只更改1,、2個字段,，不要Update全部字段，否則頻繁調(diào)用會引起明顯的性能消耗,，同時帶來大量日志,。14. 對于多張大數(shù)據(jù)量（這里幾百條就算大了）的表JOIN，要先分頁再JOIN,，否則邏輯讀會很高,，性能很差。15. select count(*) from table,；這樣不帶任何條件的count會引起全表掃描,，并且沒有任何業(yè)務(wù)意義，是一定要杜絕的,。16. 索引并不是越多越好,，索引固然可以提高相應(yīng)的 select 的效率，但同時也降低了 insert 及 update 的效率,，因為 insert 或 update 時有可能會重建索引,，所以怎樣建索引需要慎重考慮，視具體情況而定,。一個表的索引數(shù)最好不要超過6個,，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有 必要。17. 應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列,，因為 clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲順序,，一旦該列值改變將導(dǎo)致整個表記錄的順序的調(diào)整，會耗費相當大的資源,。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列,，那么需要考慮是否應(yīng)將該索引建為 clustered 索引。18. 盡量使用數(shù)字型字段,，若只含數(shù)值信息的字段盡量不要設(shè)計為字符型,，這會降低查詢和連接的性能，并會增加存儲開銷,。這是因為引擎在處理查詢和連 接時會逐個比較字符串中每一個字符,，而對于數(shù)字型而言只需要比較一次就夠了。19. 盡可能的使用 varchar/nvarchar 代替 char/nchar ,，因為首先變長字段存儲空間小,，可以節(jié)省存儲空間，其次對于查詢來說,，在一個相對較小的字段內(nèi)搜索效率顯然要高些,。20. 任何地方都不要使用 select * from t ,，用具體的字段列表代替“*”，不要返回用不到的任何字段,。21. 盡量使用表變量來代替臨時表,。如果表變量包含大量數(shù)據(jù)，請注意索引非常有限（只有主鍵索引）,。22. 避免頻繁創(chuàng)建和刪除臨時表,，以減少系統(tǒng)表資源的消耗。臨時表并不是不可使用,，適當?shù)厥褂盟鼈兛梢允鼓承├谈行?，例如，當需要重?fù)引用大型表或常用表中的某個數(shù)據(jù)集時,。但是,，對于一次性事件， 最好使用導(dǎo)出表,。23. 在新建臨時表時,，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table,，避免造成大量 log ,，以提高速度；如果數(shù)據(jù)量不大,，為了緩和系統(tǒng)表的資源,，應(yīng)先create table，然后insert,。24. 如果使用到了臨時表,，在存儲過程的最后務(wù)必將所有的臨時表顯式刪除，先 truncate table ,，然后 drop table ,，這樣可以避免系統(tǒng)表的較長時間鎖定。25. 盡量避免使用游標,，因為游標的效率較差，如果游標操作的數(shù)據(jù)超過1萬行,，那么就應(yīng)該考慮改寫,。26. 使用基于游標的方法或臨時表方法之前，應(yīng)先尋找基于集的解決方案來解決問題,，基于集的方法通常更有效,。27. 與臨時表一樣，游標并不是不可使用,。對小型數(shù)據(jù)集使用 FAST_FORWARD 游標通常要優(yōu)于其他逐行處理方法,，尤其是在必須引用幾個表才能獲得所需的數(shù)據(jù)時,。在結(jié)果集中包括“合計”的例程通常要比使用游標執(zhí)行的速度快。如果開發(fā)時 間允許,，基于游標的方法和基于集的方法都可以嘗試一下,，看哪一種方法的效果更好。28. 在所有的存儲過程和觸發(fā)器的開始處設(shè)置 SET NOCOUNT ON ,，在結(jié)束時設(shè)置 SET NOCOUNT OFF ,。無需在執(zhí)行存儲過程和觸發(fā)器的每個語句后向客戶端發(fā)送 DONE_IN_PROC 消息。29. 盡量避免大事務(wù)操作,，提高系統(tǒng)并發(fā)能力,。

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

聚集索引和非聚集索引

聚集索引     聚集索引就是存放的物理順序和列中的順序一樣。一般設(shè)置主鍵索引就為聚集索引,。     一個沒加主鍵的表,，它的數(shù)據(jù)無序的放置在磁盤存儲器上，一行一行的排列的很整齊,。如果給表上了主鍵,，那么表在磁盤上的存儲結(jié)構(gòu)就由整齊排列的結(jié)構(gòu)轉(zhuǎn)變成了樹狀結(jié)構(gòu)，也就是平衡樹結(jié)構(gòu),，換句話說,，就是整個表就變成了一個索引，也就是所謂的聚集索引,。 這就是為什么一個表只能有一個主鍵,， 一個表只能有一個聚集索引，因為主鍵的作用就是把表的數(shù)據(jù)格式轉(zhuǎn)換成索引（平衡樹）的格式放置,。     上圖就是帶有主鍵的表（聚集索引）的結(jié)構(gòu)圖,。其中樹的所有結(jié)點（底部除外）的數(shù)據(jù)都是由主鍵字段中的數(shù)據(jù)構(gòu)成，也就是通常我們指定主鍵的id字段,。最下面部分是真正表中的數(shù)據(jù),。 假如我們執(zhí)行一個SQL語句：select * from table where id = 1256     首先根據(jù)索引定位到1256這個值所在的葉結(jié)點，然后再通過葉結(jié)點取到id等于1256的數(shù)據(jù)行,。 這里不講解平衡樹的運行細節(jié),， 但是從上圖能看出，樹一共有三層,， 從根節(jié)點至葉節(jié)點只需要經(jīng)過三次查找就能得到結(jié)果,。如下圖     然而， 事物都是有兩面的,， 索引能讓數(shù)據(jù)庫查詢數(shù)據(jù)的速度上升,， 而使寫入數(shù)據(jù)的速度下降,，原因很簡單的， 因為平衡樹這個結(jié)構(gòu)必須一直維持在一個正確的狀態(tài),， 增刪改數(shù)據(jù)都會改變平衡樹各節(jié)點中的索引數(shù)據(jù)內(nèi)容,，破壞樹結(jié)構(gòu)， 因此,，在每次數(shù)據(jù)改變時,， DBMS必須去重新梳理樹（索引）的結(jié)構(gòu)以確保它的正確，這會帶來不小的性能開銷,，也就是為什么索引會給查詢以外的操作帶來副作用的原因,。非聚集索引    講完聚集索引 ， 接下來聊一下非聚集索引,， 也就是我們平時經(jīng)常提起和使用的常規(guī)索引,。    非聚集索引和聚集索引一樣， 同樣是采用平衡樹作為索引的數(shù)據(jù)結(jié)構(gòu),。索引樹結(jié)構(gòu)中各節(jié)點的值來自于表中的索引字段,， 假如給user表的name字段加上索引 ， 那么索引就是由name字段中的值構(gòu)成,，在數(shù)據(jù)改變時,， DBMS需要一直維護索引結(jié)構(gòu)的正確性。如果給表中多個字段加上索引 ,， 那么就會出現(xiàn)多個獨立的索引結(jié)構(gòu),，每個索引（非聚集索引）互相之間不存在關(guān)聯(lián)。 如下圖     每次給字段建一個新索引,， 字段中的數(shù)據(jù)就會被復(fù)制一份出來,， 用于生成索引。 因此,， 給表添加索引,，會增加表的體積， 占用磁盤存儲空間,。  非聚集索引和聚集索引的區(qū)別在于：     通過聚集索引可以一次查到需要查找的數(shù)據(jù),， 而通過非聚集索引第一次只能查到記錄對應(yīng)的主鍵值 ， 再使用主鍵的值通過聚集索引查找到需要的數(shù)據(jù),。     聚集索引一張表只能有一個,，而非聚集索引一張表可以有多個。

索引是什么,？有什么作用以及優(yōu)缺點？

索引是什么,？

     數(shù)據(jù)庫索引是數(shù)據(jù)庫管理系統(tǒng)中一個排序的數(shù)據(jù)結(jié)構(gòu),，以協(xié)助快速查詢,、更新數(shù)據(jù)庫表中數(shù)據(jù)。索引的實現(xiàn)通常使用B樹及其變種B+樹,。

索引原理

     索引的目的在于提高查詢效率,，與我們查閱圖書所用的目錄是一個道理：先定位到章，然后定位到該章下的一個小節(jié),，然后找到頁數(shù),。相似的例子還有：查字典，查火車車次,，飛機航班等,。

磁盤IO與預(yù)讀

     考慮到磁盤IO是非常高昂的操作，計算機操作系統(tǒng)做了一些優(yōu)化,，當一次IO時,，不光把當前磁盤地址的數(shù)據(jù)，而是把相鄰的數(shù)據(jù)也都讀取到內(nèi)存緩沖區(qū)內(nèi),，因為局部預(yù)讀性原理告訴我們,，當計算機訪問一個地址的數(shù)據(jù)的時候，與其相鄰的數(shù)據(jù)也會很快被訪問到,。每一次IO讀取的數(shù)據(jù)我們稱之為一頁(page),。具體一頁有多大數(shù)據(jù)跟操作系統(tǒng)有關(guān)，一般為4k或8k,，也就是我們讀取一頁內(nèi)的數(shù)據(jù)時候,，實際上才發(fā)生了一次IO，這個理論對于索引的數(shù)據(jù)結(jié)構(gòu)設(shè)計非常有幫助,。

索引的兩大類型Hash索引和B樹索引

     Hash類型的索引：查詢單條快,，范圍查詢慢。

     B樹類型的索引：b+樹,，層數(shù)越多,，數(shù)據(jù)量指數(shù)級增長（我們就用它，因為innodb默認支持它）,。

  不同的存儲引擎支持的索引類型也不一樣

     InnoDB 支持事務(wù),，支持行級別鎖定，支持 B-tree,、Full-text 等索引,，不支持 Hash 索引。

     MyISAM 不支持事務(wù),，支持表級別鎖定,，支持 B-tree、Full-text 等索引，不支持 Hash 索引,。

     Memory 不支持事務(wù),，支持表級別鎖定，支持 B-tree,、Hash 等索引,，不支持 Full-text 索引。

Hash索引的限制

     1.由于索引僅包含hash code和記錄指針,，所以,，MySQL不能通過使用索引避免讀取記錄。但是訪問內(nèi)存中的記錄是非常迅速的,，不會對性造成太大的影響,。

     2.不能使用hash索引排序。

     3.Hash索引不支持鍵的部分匹配,，因為是通過整個索引值來計算hash值的,。

     4.Hash索引只支持等值比較，例如使用=,，IN( )和<=>,。對于WHERE price>100并不能加速查詢。

索引數(shù)據(jù)結(jié)構(gòu)為什么采用B+樹

     為了盡量減少I/O操作,，磁盤讀取每次都會預(yù)讀,，大小通常為頁的整數(shù)倍。即使只需要讀取一個字節(jié),，磁盤也會讀取一頁的數(shù)據(jù)(通常為4K)放入內(nèi)存,，內(nèi)存與磁盤以頁為單位交換數(shù)據(jù)。因為局部性原理認為,，通常一個數(shù)據(jù)被用到,，其附近的數(shù)據(jù)也會立馬被用到。

     B樹：如果一次檢索需要訪問4個節(jié)點,，數(shù)據(jù)庫系統(tǒng)設(shè)計者利用磁盤預(yù)讀原理,，把節(jié)點的大小設(shè)計為一個頁，那讀取一個節(jié)點只需要一次I/O操作,，完成這次檢索操作,，最多需要3次I/O(根節(jié)點常駐內(nèi)存)。數(shù)據(jù)記錄越小,，每個節(jié)點存放的數(shù)據(jù)就越多,，樹的高度也就越小，I/O操作就少了,，檢索效率也就上去了,。

     B+樹：非葉子節(jié)點只存key,，大大滴減少了非葉子節(jié)點的大小，那么每個節(jié)點就可以存放更多的記錄,，樹更矮了,，I/O操作更少了。所以B+Tree擁有更好的性能,。

     由于B+樹在內(nèi)部節(jié)點上不包含數(shù)據(jù)信息，因此在內(nèi)存頁中能夠存放更多的key,。 數(shù)據(jù)存放的更加緊密,，具有更好的空間局部性。因此訪問葉子節(jié)點上關(guān)聯(lián)的數(shù)據(jù)也具有更好的緩存命中率,。

     B+樹的葉子結(jié)點都是相鏈的,，因此對整棵樹的便利只需要一次線性遍歷葉子結(jié)點即可。而且由于數(shù)據(jù)順序排列并且相連,，所以便于區(qū)間查找和搜索,。而B樹則需要進行每一層的遞歸遍歷。相鄰的元素可能在內(nèi)存中不相鄰,，所以緩存命中性沒有B+樹好,。

索引的優(yōu)缺點

   優(yōu)點：

     1.通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性,。

     2.可以大大加快數(shù)據(jù)的檢索速度,，這也是創(chuàng)建索引的最主要的原因。

     3.可以加速表和表之間的連接,，特別是在實現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義,。

     4.在使用分組和排序子句進行數(shù)據(jù)檢索時，同樣可以顯著減少查詢中分組和排序的時間,。

     5.通過使用索引,，可以在查詢的過程中，使用優(yōu)化隱藏器,，提高系統(tǒng)的性能,。

   缺點：

     1.創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加,。

     2.索引需要占物理空間,，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間,，如果要建立聚簇索引,，那么需要的空間就會更大。

     3.當對表中的數(shù)據(jù)進行增加,、刪除和修改的時候,，索引也要動態(tài)的維護,，這樣就降低了數(shù)據(jù)的維護速度。

索引是建立在數(shù)據(jù)庫表中的某些列的上面,。在創(chuàng)建索引的時候,，應(yīng)該考慮在哪些列上可以創(chuàng)建索引，在哪些列上不能創(chuàng)建索引,。

哪些字段適合建索引

     1.在經(jīng)常需要搜索的列上,，可以加快搜索的速度；

     2.在作為主鍵的列上,，強制該列的唯一性和組織表中數(shù)據(jù)的排列結(jié)構(gòu),；

     3.在經(jīng)常用在連接的列上，這些列主要是一些外鍵,，可以加快連接的速度,；

     4.在經(jīng)常需要根據(jù)范圍進行搜索的列上創(chuàng)建索引，因為索引已經(jīng)排序,，其指定的范圍是連續(xù)的,；

     5.在經(jīng)常需要排序的列上創(chuàng)建索引，因為索引已經(jīng)排序,，這樣查詢可以利用索引的排序,，加快排序查詢時間；

     6.在經(jīng)常使用在WHERE子句中的列上面創(chuàng)建索引,，加快條件的判斷速度,。

哪些字段不適合建索引

     1.對于那些在查詢中很少使用或者參考的列不應(yīng)該創(chuàng)建索引。這是因為,，既然這些列很少使用到,，因此有索引或者無索引，并不能提高查詢速度,。相反,，由于增加了索引，反而降低了系統(tǒng)的維護速度和增大了空間需求,。

     2.對于那些只有很少數(shù)據(jù)值的列也不應(yīng)該增加索引,。這是因為，由于這些列的取值很少,，例如人事表的性別列,，在查詢的結(jié)果中，結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例,，即需要在表中搜索的數(shù)據(jù)行的比例很大,。增加索引，并不能明顯加快檢索速度,。

     3.對于那些定義為text, image和bit數(shù)據(jù)類型的列不應(yīng)該增加索引,。這是因為,，這些列的數(shù)據(jù)量要么相當大，要么取值很少,。

     4.當修改性能遠遠大于檢索性能時,，不應(yīng)該創(chuàng)建索引。這是因為,，修改性能和檢索性能是互相矛盾的,。當增加索引時，會提高檢索性能,，但是會降低修改性能,。當減少索引時，會提高修改性能,，降低檢索性能。因此,，當修改性能遠遠大于檢索性能時,，不應(yīng)該創(chuàng)建索引。

數(shù)據(jù)庫引擎MyISAM和InnoDB
MyISAMInnoDB事務(wù)支持   不支持支持鎖的粒度   表鎖行鎖存儲容量   沒有上限64TB哈希索引   不支持支持全文索引   支持以前不支持,，現(xiàn)在支持外鍵   不支持支持

     InnoDB是非事務(wù)的存儲引擎,；適合用于頻繁查詢的應(yīng)用；表鎖,，不會出現(xiàn)死鎖,；適合小數(shù)據(jù)，小并發(fā)

     InnoDB是支持事務(wù)的存儲引擎,；合于插入和更新操作比較多的應(yīng)用,；設(shè)計合理的話是行鎖（最大區(qū)別就在鎖的級別上）；適合大數(shù)據(jù),，大并發(fā),。

     MyISAM表的數(shù)據(jù)文件和索引文件是自動分開的；InnoDB的數(shù)據(jù)和索引是存儲在同一個表空間里面,，但可以有多個文件組成,。

為什么MyISAM會比InnoDB的查詢速度快

     1.InnoDB要緩存數(shù)據(jù)塊，而MyISAM只緩存索引塊

     2.InnoDB尋址要映射到塊,，再到行,，MyISAM記錄的直接是文件的OFFSET，定位比InnoDB要快

     3.InnoDB還需要維護MVCC一致

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

慢查詢?nèi)罩靖拍?/span>     MySQL的慢查詢?nèi)罩臼荕ySQL提供的一種日志記錄,，它用來記錄在MySQL中響應(yīng)時間超過閥值的語句,，具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢?nèi)罩局?。long_query_time的默認值為10,，意思是運行10S以上的語句,。默認情況下，Mysql數(shù)據(jù)庫并不啟動慢查詢?nèi)罩?，需要我們手動來設(shè)置這個參數(shù),，當然，如果不是調(diào)優(yōu)需要的話,，一般不建議啟動該參數(shù),，因為開啟慢查詢?nèi)罩緯蚨嗷蛏賻硪欢ǖ男阅苡绊憽Ｂ樵內(nèi)罩局С謱⑷罩居涗泴懭胛募?，也支持將日志記錄寫入?shù)據(jù)庫表,。慢查詢?nèi)罩鞠嚓P(guān)參數(shù)slow_query_log    ：是否開啟慢查詢?nèi)罩荆?表示開啟，0表示關(guān)閉,。log-slow-queries  ：舊版（5.6以下版本）MySQL數(shù)據(jù)庫慢查詢?nèi)罩敬鎯β窂?。可以不設(shè)置該參數(shù),，系統(tǒng)則會默認給一個缺省的文件host_name-slow.logslow-query-log-file：新版（5.6及以上版本）MySQL數(shù)據(jù)庫慢查詢?nèi)罩敬鎯β窂?。可以不設(shè)置該參數(shù),，系統(tǒng)則會默認給一個缺省的文件host_name-slow.loglong_query_time ：慢查詢閾值,，當查詢時間多于設(shè)定的閾值時，記錄日志,。log_queries_not_using_indexes：未使用索引的查詢也被記錄到慢查詢?nèi)罩局校蛇x項）,。log_output：日志存儲方式。log_output='FILE'表示將日志存入文件,，默認值是'FILE',。慢查詢?nèi)罩九渲?/span>     默認情況下slow_query_log的值為OFF，表示慢查詢?nèi)罩臼墙玫?，可以通過設(shè)置slow_query_log的值來開啟,。mysql> show variables like '%slow_query_log%';+---------------------+------------------------------------------+| Variable_name       | Value                                    |+---------------------+------------------------------------------+| slow_query_log      | OFF                                      || slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |+---------------------+------------------------------------------+2 rows in set (0.00 sec) mysql> set global slow_query_log=1;Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%slow_query_log%';+---------------------+------------------------------------------+| Variable_name       | Value                                    |+---------------------+------------------------------------------+| slow_query_log      | ON                                       || slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |+---------------------+------------------------------------------+2 rows in set (0.00 sec)#使用set global slow_query_log=1開啟了慢查詢?nèi)罩局粚Ξ斍皵?shù)據(jù)庫生效，MySQL重啟后則會失效,。如果要永久生效,，就必須修改配置文件my.cnf（其它系統(tǒng)變量也是如此）     修改my.cnf文件，增加或修改參數(shù)slow_query_log 和slow_query_log_file后,，然后重啟MySQL服務(wù)器,。slow_query_log =1slow_query_log_file=/usr/local/mysql/data/localhost-slow.logmysql> show variables like 'slow_query%';+---------------------+---------------------+| Variable_name       | Value               |+---------------------+---------------------+| slow_query_log      | ON                  || slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |+---------------------+---------------------+2 rows in set (0.00 sec) mysql> #慢查詢的參數(shù)slow_query_log_file ，它指定慢查詢?nèi)罩疚募拇娣怕窂?，系統(tǒng)默認會給一個缺省的文件host_name-slow.log日志分析工具mysqldumpslow     在實際生產(chǎn)環(huán)境中,，如果要手工分析日志，查找,、分析SQL,，顯然是個體力活,，MySQL提供了日志分析工具mysqldumpslow得到返回記錄集最多的10個SQL。mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log 得到訪問次數(shù)最多的10個SQLmysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log 得到按照時間排序的前10條里面含有左連接的查詢語句,。mysqldumpslow -s t -t 10 -g “l(fā)eft join” /database/mysql/mysql06_slow.log 另外建議在使用這些命令時結(jié)合 | 和more 使用 ,，否則有可能出現(xiàn)刷屏的情況。mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

XSS,、SQL和CSRF攻擊

     XSS（Cross Site Script,，跨站腳本攻擊）是向網(wǎng)頁中注入惡意腳本在用戶瀏覽網(wǎng)頁時在用戶瀏覽器中執(zhí)行惡意腳本的攻擊方式。

        防止方法：做數(shù)據(jù)校驗,、做標簽轉(zhuǎn)換,，如：空格 轉(zhuǎn)化為  。

     SQL注入攻擊是注入攻擊最常見的形式,，當服務(wù)器使用請求參數(shù)構(gòu)造SQL語句時,，惡意的SQL被嵌入到SQL中交給數(shù)據(jù)庫執(zhí)行；

        防止方法：輸入校驗,、sql不要動態(tài)拼接,，用參數(shù)化的sql。

     CSRF攻擊（Cross Site Request Forgery,，跨站請求偽造）是攻擊者通過跨站請求，以合法的用戶身份進行非法操作（如轉(zhuǎn)賬或發(fā)帖等）,。

     CSRF的原理是利用瀏覽器的Cookie或服務(wù)器的Session,，盜取用戶身份；

        防止方法：使用post代替get,，因為post不會被緩存,；每次清除掉cookie。