三范式? * 第一范式：確保每列保持原子性，數據表中的所有字段值都是不可分解的原子值。 * 第二范式：確保表中的每列都和主鍵相關 * 第三范式：確保每列都和主鍵列直接相關而不是間接相關 什么是索引？ 官方: 本質是一種數據結構, 是幫助MySQL高效獲取數據的數據結構 是排好序的快速查找數據結構 索引的優劣？ 1. 優勢 1. 提高數據檢索效率，降低數據庫的IO成本 2. 降低數據排序成本，降低了CPU的消耗 3. 劣勢 * 實際上索引也是一張表，該表保存了主鍵和索引字段，并指向實體表的記錄,所以索引列也是要占用空間的 * 因為更新表時，MySQL不僅要不存數據，還要保存一下索引文件每次更新添加了索引列的字段，都會調整因為更新所帶來的鍵值變化后的索引信息 * 索引只是提高效率的一個因素，如果你的MySQL有大數據量的表，就需要花時間研究建立優秀的索引，或優化查詢語句 索引分類? 1. 普通索引 基本的索引類型，可以為NULL 2. 主鍵索引 數據列不允許重復，不能為NULL，一個表只能有一個主鍵索引 3. 唯一索引 數據列必須唯一，但允許有空值 4. 復合索引 即一個索引包含多個列 覆蓋索引+回表+索引下推？ * 覆蓋索引：索引字段覆蓋了查詢語句涉及的字段，直接通過索引文件就可以返回查詢所需的數據，不必通過回表操作。 * 回表：通過索引找到主鍵，再根據主鍵id去主鍵索引查數據。 * 索引下推：在根據索引查詢過程中就根據查詢條件過濾掉一些記錄，減少最后的回表操作 什么是聚簇索引，什么是非聚簇索引？ 聚集索引（主鍵索引） 聚簇索引和非聚簇索引最主要的區別是數據和索引是否分開存儲。 * 聚簇索引(主鍵索引)：**將數據和索引放到一起存儲**，索引結構的葉子節點保留了數據行。每個表一定會有一個聚集索引，整個表的數據存儲以b+樹的方式存在文件中，**b+樹葉子節點中的key為主鍵值，data為完整記錄的信息；非葉子節點存儲主鍵的值。** 通過聚集索引檢索數據只需要按照b+樹的搜索過程，即可以檢索到對應的記錄。 * 非聚簇索引：將數據進和索引分開存儲，索引葉子節點存儲的是指向數據行的地址。每個表可以有多個非聚集索引，b+樹結構，葉子節點的key為索引字段字段的值，data為主鍵的值；非葉子節點只存儲索引字段的值。 通過非聚集索引檢索記錄的時候，需要2次操作，先在**非聚集索引中檢索出主鍵**，**然后再到聚集索引中檢索出主鍵對應的記錄**，該過程比聚集索引多了一次操作。 在InnoDB存儲引擎中，默認的索引為B+樹索引，利用主鍵創建的索引為主索引，也是聚簇索引，在主索引之上創建的索引為輔助索引，也是非聚簇索引。 非聚簇索引一定會進行回表查詢嗎？ 不一定，當遇到**覆蓋索引**時，如果查詢的數據再輔助索引上完全能獲取到便不需要回表查詢。 如：返回值是 id 和 name， name上有索引，這時候name的索引列就同上有 name和id了，不需要再回表查詢了。 非聚簇索引的葉子節點存儲的是主鍵，也就是說要先通過非聚簇索引找到主鍵，再通過聚簇索引找到主鍵所對應的數據，后面這個再通過聚簇索引找到主鍵對應的數據的過程就是回表查詢 什么是前綴索引？ 前綴索引是指對文本或者字符串的前幾個字符建立索引，這樣索引的長度更短，查詢速度更快。 使用場景：前綴的區分度比較高的情況下。 ALTER TABLE table_name ADD KEY(column_name(prefix_length)); 哪些情況需要創建索引? 1. 主鍵自動建立唯一索引 2. 頻繁作為查詢的條件的字段應該創建索引 3. 查詢中與其他表關聯的字段，外鍵關系建立索引 4. 頻繁更新的字段不適合創建索引 因為每次更新不單單是更新了記錄還會更新索引，加重IO負擔 5. Where條件里用不到的字段不創建索引 6. 單間/組合索引的選擇問題，who？（在高并發下傾向創建組合索引） 7. 查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序的速度 8. 查詢中統計或者分組字段 哪些情況不要創建索引? 1. 表記錄太少 2. 經常增刪改的表 3. 數據重復且分布平均的表字段，因此應該只為經常查詢和經常排序的數據列建立索引。 注意，如果某個數據列包含許多重復的內容，為它建立索引就沒有太大的實際效果。 索引的設計原則？ * 最適合索引的列是在where后面出現的列或者連接句子中指定的列，而不是出現在SELECT關鍵字后面的選擇列表中的列。 * 索引列的基數越大，索引的效果越好，換句話說就是索引列的區分度越高，索引的效果越好。比如使用性別這種區分度很低的列作為索引，效果就會很差，因為列的基數最多也就是三種，大多不是男性就是女性。 * 盡量使用短索引，對于較長的字符串進行索引時應該指定一個較短的前綴長度，因為較小的索引涉及到的磁盤I/O較少，并且索引高速緩存中的塊可以容納更多的鍵值，會使得查詢速度更快。 * 盡量利用最左前綴。 * 不要過度索引，每個索引都需要額外的物理空間，維護也需要花費時間，所以索引不是越多越好。 索引優化? 1. 全值匹配我最愛 2. 最佳左前綴法則 如果索引了多例，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。帶頭大哥不能死,中間兄弟不能斷 3. 不在索引列上做任何操作（計算、函數、（自動or手動）類型轉換），會導致索引失效而轉向全表掃描 4. 存儲引擎不能使用索引中范圍條件右邊的列 5. 盡量使用覆蓋索引（只訪問索引的查詢（索引列和查詢列一致）），減少select* 6. mysql在使用不等于（！=或者<>）的時候無法使用索引會導致全表掃描 7. is null,is not null 也無法使用索引 8. like以通配符開頭（'$abc...'）mysql索引失效會變成全表掃描操作 解決方式, 使用覆蓋索引, 9. 字符串不加單引號索引失效 10. 少用or,用它連接時會索引失效 什么是事務？ 事務：一組邏輯操作單元，使數據從一種狀態變換到另一種狀態。 事務處理的原則：保證所有事務都作為 一個工作單元 來執行，即使出現了故障，都不能改變這種執行方 式。當在一個事務中執行多個操作時，要么所有的事務都被提交( commit )，那么這些修改就 永久 地保 存下來；要么數據庫管理系統將 放棄 所作的所有 修改 ，整個事務回滾( rollback )到最初狀態。 事務的四大特性是什么？ * 原子性（atomicity）：原子性是指事務是一個不可分割的工作單位，要么全部提交，要么全部失敗回滾。 * 一致性（consistency）：一致性是指事務執行前后，數據從一個 合法性狀態 變換到另外一個 合法性狀態 。這種狀態 是 語義上 的而不是語法上的，跟具體的業務有關。 * 隔離型（isolation）：一個事務的執行 不能被其他事務干擾 ，即一個事務內部的操作及使用的數據對 并發 的 其他事務是隔離的，并發執行的各個事務之間不能互相干擾。 * 持久性（durability）：持久性是指一個事務一旦被提交，它對數據庫中數據的改變就是 永久性的 ，接下來的其他操作和數據庫 故障不應該對其有任何影響。 數據庫的并發一致性問題？ 1. 臟寫（ Dirty Write ） 事務A修改了事務B未提交的的數據。這個不用管，數據本身就解決了。 2. 臟讀（ Dirty Read ） 事務A讀取了事務B修改但未提交的的數據。 3. 不可重復讀（ Non-Repeatable Read ） 事務A讀取了一個字段，事務B修改了此字段，當事務A再次讀取此字段時，值不同了，那就意味著發生了不可重復讀。 4. 幻讀（ Phantom ） 事務A讀取了一個字段，然后事務B在該表中插入了一些新的行，之后事務A再次讀取同一個表，就會多出幾行。 事務的四種隔離級別？ * READ UNCOMMITTED ：讀未提交，在該隔離級別，所有事務都可以看到其他未提交事務的執行結 果。不能避免臟讀、不可重復讀、幻讀。 * READ COMMITTED ：讀已提交，它滿足了隔離的簡單定義：一個事務只能看見已經提交事務所做 的改變。這是大多數數據庫系統的默認隔離級別（但不是MySQL默認的）。可以避免臟讀，但不可 重復讀、幻讀問題仍然存在。 * REPEATABLE READ ：可重復讀，事務A在讀到一條數據之后，此時事務B對該數據進行了修改并提 交，那么事務A再讀該數據，讀到的還是原來的內容。可以避免臟讀、不可重復讀，但幻讀問題仍 然存在。這是MySQL的默認隔離級別。 * SERIALIZABLE ：可串行化，確保事務可以從一個表中讀取相同的行。在這個事務持續期間，禁止 其他事務對該表執行插入、更新和刪除操作。所有的并發問題都可以避免，但性能十分低下。能避 免臟讀、不可重復讀和幻讀。  什么是MVCC？ MVCC(multiple version concurrent control)是一種控制并發的方法，主要用來提高數據庫的并發性能。 通過數據行的多個版 本管理來實現數據庫的 并發控制 。 主要是為了提高數據庫并發性能，用更好的方式去處理 讀-寫沖突 ，做到 即使有讀寫沖突時，也能做到 不加鎖 ， 非阻塞并發讀 ，而這個讀指的就是 快照讀。 快照讀 快照讀又叫一致性讀，讀取的是快照數據。不加鎖的簡單的 SELECT 都屬于快照讀，即不加鎖的非阻塞 讀； mysql中in和exists的區別？ in和exists一般用于子查詢。 * 使用exists時會先進行外表查詢，將查詢到的每行數據帶入到內表查詢中看是否滿足條件；使用in一般會先進行內表查詢獲取結果集，然后對外表查詢匹配結果集，返回數據。 * in在內表查詢或者外表查詢過程中都會用到索引。 * exists僅在內表查詢時會用到索引 * 一般來說，當子查詢的結果集比較大，外表較小使用exist效率更高；當子查詢尋得結果集較小，外表較大時，使用in效率更高。 * 對于not in和not exists，not exists效率比not in的效率高，與子查詢的結果集無關，因為not in對于內外表都進行了全表掃描，沒有使用到索引。not exists的子查詢中可以用到表上的索引。 UNION和UNION ALL的區別？ union和union all的作用都是將兩個結果集合并到一起。 union會對結果去重并排序，union all直接直接返回合并后的結果，不去重也不進行排序。 union all的性能比union性能好。 MySQL的復制原理及流程？如何實現主從復制？ 主從部署必要條件： * 主庫開啟binlog日志（設置log-bin參數） * 主從server-id不同 * 從庫服務器能連通主庫 MySQL復制：為保證主服務器和從服務器的數據一致性，在向主服務器插入數據后，從服務器會自動將主服務器中修改的數據同步過來。 主從復制的原理： 主從復制主要有三個線程：binlog線程，I/O線程，SQL線程。 * binlog線程：負責將主服務器上的數據更改寫入到二進制日志（Binary log）中。 * I/O線程：負責從主服務器上讀取二進制日志（Binary log），并寫入從服務器的中繼日志（Relay log）中。 * SQL線程：負責讀取中繼日志，解析出主服務器中已經執行的數據更改并在從服務器中重放 1. Master在每個事務更新數據完成之前，將操作記錄寫入到binlog中。 2. Slave從庫連接Master主庫，并且Master有多少個Slave就會創建多少個binlog dump線程。當Master節點的binlog發生變化時，binlog dump會通知所有的Slave，并將相應的binlog發送給Slave。 3. I/O線程接收到binlog內容后，將其寫入到中繼日志（Relay log）中。 4. SQL線程讀取中繼日志，并在從服務器中重放  主從復制的作用？ * 高可用和故障轉移 * 負載均衡 * 數據備份 * 升級測試 讀寫分離？ 讀寫分離主要依賴于主從復制，主從復制為讀寫分離服務。 讀寫分離的優勢： * 主服務器負責寫，從服務器負責讀，緩解了鎖的競爭 * 從服務器可以使用MyISAM，提升查詢性能及節約系統開銷 * 增加冗余，提高可用性