>[info] 訂單、庫存兩個表 如何保證數據的一致性？ 在一個電子商務系統中，正常的應該是訂單生成成功后，相應的庫存進行減少必須要保證兩者的一致性，但有時候因 為某些原因，比如程序邏輯問題，并發等問題，導致下單成功而庫存沒有減少的情況。這種情況我們是不允許發生 的，MySQL的中的事務剛好可以解決這一問題，首先得選擇數據庫的存儲引擎為InnoDB的，事務規定了只有下訂單 完成了，并且相應的庫存減少了才允許提交事務，否則就事務回滾，確保數據一致性。 >[info] 外鍵的作用 保持數據一致性，完整性，主要目的是控制存儲在外鍵表中的數據 >[info] 什么是讀鎖，什么是寫鎖？ * **讀鎖：** 也叫共享鎖、S鎖，若事務T對數據對象A加上S鎖，則事務T可以讀A但不能修改A，其他事務只能再對A加S 鎖，而不能加X鎖，直到T釋放A上的S 鎖。這保證了其他事務可以讀A，但在T釋放A上的S鎖之前不能對A做任何修 改。 * **寫鎖：** 又稱排他鎖、X鎖。若事務T對數據對象A加上X鎖，事務T可以讀A也可以修改A，其他事務不能再對A加 任何鎖，直到T釋放A上的鎖。這保證了其他事務在T釋放A上的鎖之前不能再讀取和修改A。 >[info] 數據庫中，什么是鎖？ 數據庫是一個多用戶使用的共享資源。當多個用戶并發地存取數據時，在數據庫中就會產生多個事務同時存取同一數 據的情況。若對并發操作不加控制就可能會讀取和存儲不正確的數據，破壞數據庫的一致性。 ***** **MyISAM和InnoDB存儲引擎使用的鎖：** * MyISAM采用表級鎖(table-level locking)。 * InnoDB支持行級鎖(row-level locking)和表級鎖,默認為行級鎖 ***** **表級鎖和行級鎖對比**： * **表級鎖：** Mysql中鎖定 粒度最大 的一種鎖，對當前操作的整張表加鎖，實現簡單，資源消耗也比較少，加鎖 快，不會出現死鎖。其鎖定粒度最大，觸發鎖沖突的概率最高，并發度最低，MyISAM和 InnoDB引擎都支持表 級鎖。 **行級鎖：** Mysql中鎖定 粒度最小 的一種鎖，只針對當前操作的行進行加鎖。 行級鎖能大大減少數據庫操作的沖 突。其加鎖粒度最小，并發度高，但加鎖的開銷也最大，加鎖慢，會出現死鎖。 ***** **InnoDB存儲引擎的鎖的算法有三種：** * Record lock：單個行記錄上的鎖 * Gap lock：間隙鎖，鎖定一個范圍，不包括記錄本身 * Next-key lock：record+gap 鎖定一個范圍，包含記錄本身 >[info] mysql事務隔離級別，說下你對事務的隔離性的理解？ **** **為了達到上述事務特性，數據庫定義了幾種不同的事務隔離級別：** * **READ_UNCOMMITTED（RU 未授權讀取）**: 最低的隔離級別，允許讀取尚未提交的數據變更，**可能會導致臟讀、 幻讀或不可重復讀** * **READ_COMMITTED（RC 授權讀取）**: 允許讀取并發事務已經提交的數據，**可以阻止臟讀，但是幻讀或不可重復讀 仍有可能發生** * **REPEATABLE_READ（RR 可重復讀）**: 對同一字段的多次讀取結果都是一致的，除非數據是被本身事務自己所修 改，**可以阻止臟讀和不可重復讀，但幻讀仍有可能發生。** * **SERIALIZABLE（串行）**: 最高的隔離級別，完全服從ACID的隔離級別。所有的事務依次逐個執行，這樣事務之 間就完全不可能產生干擾，也就是說，**該級別可以防止臟讀、不可重復讀以及幻讀。** 但是這將嚴重影響程序的 性能。通常情況下也不會用到該級別。 ***** **這里需要注意的是：** Mysql 默認采用的 REPEATABLE\_READ隔離級別 Oracle 默認采用的 READ\_COMMITTED 隔離級別. ***** 事務隔離機制的實現基于鎖機制和并發調度。其中并發調度使用的是MVVC（多版本并發控制），通過保存修改 的舊版本信息來支持并發一致性讀和回滾等特性。 >[info] 什么是事務(ACID)？及其特性？ **事務特性：（ACID） :** 1. **原子性（Atomicity）：** 即不可分割性，事務要么全部被執行，要么就全部不被執行。 2. **一致性（Consistency）：** 事務開始前和結束后，數據庫的完整性約束沒有被破壞 。比如A向B轉賬，不可能A扣 了錢，B卻沒收到。 3. **隔離性（Isolation）：** 同一時間，只允許一個事務請求同一數據，不同的事務之間彼此沒有任何干擾。比如A正 在從一張銀行卡中取錢，在A取錢的過程結束前，B不能向這張卡轉賬。 4. **持久性（Durability）：** 事務完成后，事務對數據庫的所有更新將被保存到數據庫，不能回滾。 >[info] 請問 varchar 和 char有什么區別？ char是固定長度的字符類型，分配多少空間，就占用多長空間。Varchar是可變長度的字符類型，內容有多大就占用 多大的空間，能有效節省空間。由于varchar類型是可變的，所以在數據長度改變的時，服務器要進行額外的操作， 所以效率比char類型低。 >[info] Mysql的存儲引擎,myisam和innodb的區別 1. InnoDB支持事務，MyISAM不支持 對于InnoDB每一條SQL語言都默認封裝成事務，自動提交，這樣會影響速度，所以最好把多條SQL語言放在 begin和commit之間，組成一個事務； 2. . InnoDB支持外鍵，而MyISAM不支持。對一個包含外鍵的InnoDB表轉為MYISAM會失敗； 3. . InnoDB是聚集索引，數據文件是和索引綁在一起的，必須要有主鍵，通過主鍵索引效率很高。 但是輔助索引需要兩次查詢，先查詢到主鍵，然后再通過主鍵查詢到數據。因此主鍵不應該過大，因為主鍵太 大，其他索引也都會很大。 而MyISAM是非聚集索引，數據文件是分離的，索引保存的是數據文件的指針。主鍵索引和輔助索引是獨立的。 4. InnoDB不保存表的具體行數，執行select count(*) from table時需要全表掃描。而MyISAM用一個變量保存了 整個表的行數，執行上述語句時只需要讀出該變量即可，速度很快； 5. Innodb不支持全文索引，而MyISAM支持全文索引，查詢效率上MyISAM要高； >[info] 組合索引 (a,b,c)，支持哪些基于索引的查找 **最左原則:** a = 省 b = 市 c = 區 對于聯合索引:Mysql從左到右的使用索引中的字段，一個查詢可以只使用索引中的一部份，但只能是最左側部分。 ***** 例如索引是key index (a,b,c). 可以支持a 、 a,b 、ac、 a,b,c 4種組合進行查找，但不支持 b,c進行查找 .當最左側字段 是常量引用時，索引就十分有效。 >[info] 什么情況下應不建或少建索引 (sex 字段) 1. 表記錄太少 2. 經常插入、刪除、修改的表 3. 數據重復且分布平均的表字段，假如一個表有10萬行記錄，有一個字段A只有T和F兩種值，且每個值的分布概率 大約為50%，那么對這種表A字段建索引一般不會提高數據庫的查詢速度。 4. 經常和主字段一塊查詢但主字段索引值比較多的表字段 >[info] 什么是表分區？ 表分區，是指根據一定規則，將數據庫中的一張表分解成多個更小的，容易管理的部分。從邏輯上看，只有一張表， 但是底層卻是由多個物理分區組成。 **分表：** 指的是通過一定規則，將一張表分解成多張不同的表。比如將用戶訂單記錄根據時間成多個表。 **分表與分區的區別在于：** 分區從邏輯上來講只有一張表，而分表則是將一張表分解成多張表。 >[info] MySQL支持的分區類型有哪些？ 1. RANGE分區： 這種模式允許將數據劃分不同范圍。例如可以將一個表通過年份劃分成若干個分區 2. LIST分區： 這種模式允許系統通過預定義的列表的值來對數據進行分割。按照List中的值分區，與RANGE的區 別是，range分區的區間范圍值是連續的。 3. HASH分區 ：這中模式允許通過對表的一個或多個列的Hash Key進行計算，最后通過這個Hash碼不同數值對應 的數據區域進行分區。例如可以建立一個對表主鍵進行分區的表。 4. KEY分區 ：上面Hash模式的一種延伸，這里的Hash Key是MySQL系統產生的。 >[info] 關于MVCC的了解 MySQL InnoDB存儲引擎，實現的是基于多版本的并發控制協議——MVCC (Multi-Version Concurrency Control) **注：** 與MVCC相對的，是基于鎖的并發控制，Lock-Based Concurrency Control **MVCC最大的好處：** 讀不加鎖，讀寫不沖突。在讀多寫少的OLTP應用中，讀寫不沖突是非常重要的，極大的增加了 系統的并發性能，現階段幾乎所有的RDBMS，都支持了MVCC。 >[info] MySQL優化 1. 業務優化，數據庫設計 2. 數據庫索引 3. 分表分庫(水平分割,垂直分割) 4. 讀寫分離 5. 存儲過程(模塊化編程,可以提 高速度) 6. 對MySQL配置優化(配置最大并發數my.ini,調整緩存大小) 7. SQL調優 8. 選擇正確的存儲引擎 9. 定時清除 不需要的數據,定時進行碎片整理 10. 熱點數據采用Nosql等替代品 11. 模糊查詢采用es等替代品 12. mysql配置優化 13. 服務器優化（操作系統和硬件） >[info] 大表優化，以及談談你對mysql分庫分表的見解 當MySQL單表記錄數過大時，數據庫的CRUD性能會明顯下降，一些常見的優化措施如下： 1. **限定數據的范圍：** 務必禁止不帶任何限制數據范圍條件的查詢語句。比如：我們當用戶在查詢訂單歷史的時候，我們可以控制在一個月的范圍內。 2. **讀/寫分離：** 經典的數據庫拆分方案，主庫負責寫，從庫負責讀； 3. **緩存：** 使用MySQL的緩存，另外對重量級、更新少的數據可以考慮使用應用級別的緩存； 4. **垂直分區:** 根據數據庫里面數據表的相關性進行拆分。 例如，用戶表中既有用戶的登錄信息又有用戶的基本信息，可以將 用戶表拆分成兩個單獨的表，甚至放到單獨的庫做分庫。 簡單來說垂直拆分是指數據表列的拆分，把一張列比較多的表拆分為多張表。 如下圖所示，這樣來說大家應該 就更容易理解了。 **垂直拆分的優點：** 可以使得行數據變小，在查詢時減少讀取的Block數，減少I/O次數。此外，垂直分區可以簡化表 的結構，易于維護。 **垂直拆分的缺點：** 主鍵會出現冗余，需要管理冗余列，并會引起Join操作，可以通過在應用層進行Join來解決。此 外，垂直分區會讓事務變得更加復雜；  5. **水平分區:** **保持數據表結構不變，通過某種策略存儲數據分片。這樣每一片數據分散到不同的表或者庫中，達到了分布式的目 的。 水平拆分可以支撐非常大的數據量。** 水平拆分是指數據表行的拆分，表的行數超過200萬行時，就會變慢，這時可以把一張的表的數據拆成多張表來存 放。舉個例子：我們可以將用戶信息表拆分成多個用戶信息表，這樣就可以避免單一表數據量過大對性能造成影響。 水品拆分可以支持非常大的數據量。需要注意的一點是:分表僅僅是解決了單一表數據過大的問題，但由于表的數據 還是在同一臺機器上，其實對于提升MySQL 并發能力沒有什么意義，所以 **水品拆分最好分庫 。** 水平拆分能夠 **支持非常大的數據量存儲**，**應用端改造也少**，但 **分片事務難以解決** ，跨界點Join 性能較差，邏輯復雜  >[info] 【索引失效】在哪些情況下會發生針對該列創建了索引但是在查詢的時候并沒有使用呢? 1. **沒有遵循索引最左原則。** 建立幾個復合索引字段，最好就用上幾個字段。 ***** 2. **最佳左前綴法則** 如果索引了多列，要遵守最左前綴法則，指的是查詢從索引的最左前列開始，可以跳過但是盡量不跳過索引中 間的列。 ***** 3. 不再索引列上做任何操作(計算、函數、（自動or手動）類型轉換)，會導致索引失效而轉向全表掃描 ***** 4. 存儲引擎不能使用索引中范圍條件右邊的列。（范圍之后全失效） 若中間索引列用到了范圍（>、<、like等），則后面的索引全失效 ***** 5. Mysql在使用不等于(!=、<>)或like的左模糊的時候無法試用索引會導致全表掃描 ***** 6. IS NULL和IS NOT NULL也無法使用索引 ***** 7. 字符串不加單引號索引失效，因為這里有一個隱式的類型轉換操作，更嚴重會導致行鎖變表鎖，降低SQL效率 以上情況,MySQL無法使用索引. >[info] mysql的水平拆分和垂直拆分 1. **水平分割：** 例：QQ的登錄表。假設QQ的用戶有100億，如果只有一張表，每個用戶登錄的時候數據庫都要從這100億中查找， 會很慢很慢。如果將這一張表分成100份，每張表有1億條，就小了很多，比如qq0,qq1,qq1...qq99表。 ***** 用戶登錄的時候，可以將用戶的id%100，那么會得到0-99的數，查詢表的時候，將表名qq跟取模的數連接起來，就 構建了表名。比如123456789用戶，取模的89，那么就到qq89表查詢，查詢的時間將會大大縮短。 ***** 2. **垂直分割:** 垂直分割指的是：表的記錄并不多，但是字段卻很長，表占用空間很大，檢索表的時候需要執行大量的IO，嚴重降低 了性能。這時需要把大的字段拆分到另一個表，并且該表與原表是一對一的關系。 ***** 例如學生答題表tt：有如下字段： Id name 分數 題目 回答 其中題目和回答是比較大的字段，id name 分數比較小。 ***** 如果我們只想查詢id為8的學生的分數：select 分數 from tt where id = 8;雖然知識查詢分數，但是題目和回答這兩個 大字段也是要被掃描的，很消耗性能。但是我們只關心分數，并不想查詢題目和回答。這就可以使用垂直分割。我們 可以把題目單獨放到一張表中，通過id與tt表建立一對一的關系，同樣將回答單獨放到一張表中。這樣我們插敘tt中 的分數的時候就不會掃描題目和回答了。 >[info] 你用什么軟件實現數據庫高可用？ MHA >[info] mysql執行一條sql語句的完整過程，sql語句在mysql中的執行過程 MySQL 主要分為 Server 層和引擎層，Server 層主要包括連接器、查詢緩存、分析器、優化器、執行器，同時還有 一個日志模塊（binlog），這個日志模塊所有執行引擎都可以共用,redolog 只有 InnoDB 有。 引擎層是插件式的， 目前主要包括，MyISAM,InnoDB,Memory 等。 SQL 等執行過程分為兩類，一類對于查詢等過程如下：**權限校驗 —》查詢緩存—》分析器—》優化器—》權限校驗—》執行器—》引擎** 對于更新等語句執行流程如下：**分析器----》 權限校驗----》執行器—》引擎—redo log prepare—》binlog—》redo log commit** >[info] 說說mysql主從同步怎么做的？ **原理:** * master提交完事務后，寫入binlog * slave連接到master，獲取binlog * master創建dump線程，推送binglog到slave * slave啟動一個IO線程讀取同步過來的master的binlog，記錄到relay log中繼日志中 * slave再開啟一個sql線程讀取relay log事件并在slave執行，完成同步 * slave記錄自己的binglog >[info] 知道什么是間隙鎖嗎？(鎖了1頁數據) 間隙鎖是(RR)可重復讀級別下才會有的鎖，間隙鎖可以解決幻讀的問題。我們還是以user舉例，假設現在user表有幾條記錄: ``` 當我們執行： begin; select * from user where age=20 for update; begin; insert into user(age) values(10); #成功 insert into user(age) values(11); #失敗 insert into user(age) values(20); #失敗 insert into user(age) values(21); #失敗 insert into user(age) values(30); #失敗 只有10可以插入成功，那么因為表的間隙mysql自動幫我們生成了區間(左開右閉) (negative infinity，10],(10,20],(20,30],(30,positive infinity) 由于20存在記錄，所以(10,20]，(20,30]區間都被鎖定了無法插入、刪除。 如果查詢21呢？就會根據21定位到(20,30)的區間(都是開區間)。 ``` >[info] 什么是覆蓋索引和回表？ **MySQL innodb的主鍵索引是簇集索引，也就是索引的葉子節點存的是整個單條記錄的所有字段值，不是主鍵索引的 就是非簇集索引，非簇集索引的葉子節點存的是主鍵字段的值。回表是什么意思？就是你執行一條sql語句，需要從 兩個b+索引中去取數據。舉個例子：** ***** ``` 表tbl有a,b,c三個字段，其中a是主鍵，b上建了索引，然后編寫sql語句 SELECT * FROM tbl WHERE a=1 這樣不會產生回表，因為所有的數據在a的索引樹中均能找到 SELECT * FROM tbl WHERE b=1 ``` 這樣就會產生回表，因為where條件是b字段，那么會去b的索引樹里查找數據，但b的索引里面只有a,b兩個字段的 值，沒有c，那么這個查詢為了取到c字段，就要取出主鍵a的值，然后去a的索引樹去找c字段的數據。查了兩個索引 樹，這就叫回表。 ***** 索引覆蓋就是查這個索引能查到你所需要的所有數據，不需要去另外的數據結構去查。其實就是不用回表。 ***** 怎么避免？不是必須的字段就不要出現在SELECT里面。或者b,c建聯合索引。但具體情況要具體分析，索引字段多 了，存儲和插入數據時的消耗會更大。這是個平衡問題。 >[info] 如何預防死鎖？ * 盡量使用主鍵更新數據，防止使用非聚簇索引回表時和使用聚簇索引沖突造成死鎖。 * 在允許幻讀和不可重復度的情況下，盡量使用RC的隔離級別，避免gap lock造成的死鎖。 * 避免長事務，將事務拆解 * 設置鎖超時等待`innodb_lock_wait_timeout` >[info] 如何預防間隙鎖？ * 通過修改數據庫的參數innodb_locaks_unsafe_for_binlog來取消間隙鎖從而達到避免 * 刪除之前,先查詢是否存在. 如果并發不大,內部系統,可以不考慮 * 不做物理刪除, 而做邏輯刪除 或者 就 修改. >[info] 超大分頁怎么處理? **超大的分頁一般從兩個方向上來解決:** * 數據庫層面分頁,每次獲取一定的數據. * 從需求的角度減少這種請求….主要是不做類似的需求(直接跳轉到幾百萬頁之后的具體某一頁.只允許逐頁查看或 者按照給定的路線走,這樣可預測,可緩存)以及防止ID泄漏且連續被人惡意攻擊.  >[info] 對于關系型數據庫而言，索引是相當重要的概念，請回答有關索引的幾個問題： 1. 索引的目的是什么？ 快速訪問數據表中的特定信息，提高檢索速度 創建唯一性索引，保證數據庫表中每一行數據 的唯一性。 加速表和表之間的連接,使用分組和排序子句進行數據檢索時，可以顯著減少查詢中分組和排序的時間. ***** 2. 索引對數據庫系統的負面影響是什么？ 創建索引和維護索引需要耗費時間，這個時間隨著數據量的增 加而增加；索引需要占用物理空間，不光是表需要占用數據空間，每個索引也需要占用物理空間；當對表進行增、 刪、改、的時候索引也要動態維護，這樣就降低了數據的維護速度。 ***** 3. 為數據表建立索引的原則有哪些？ 在最頻繁使用的、用以縮小查詢范圍的字段上建立索引。 ***** 4. 什么情況下不宜建立索引？ 對于查詢中很少涉及的列或者重復值比較多的列，不宜建立索引。 對于一些特殊的數據類型，不宜建立索引，比如文本字段（text）等 >[info] 什么是存儲過程？用什么來調用？ 存儲過程是一個預編譯的SQL語句，優點是允許模塊化的設計，就是說只需創建一次，以后在該程序中就可以調 用多次。如果某次操作需要執行多次SQL，使用存儲過程比單純SQL語句執行要快。可以用一個命令對象來調用存儲 過程。 >[info] 主鍵、外鍵和索引的區別？ **主鍵、外鍵和索引的區別:** **定義：** * 主鍵–唯一標識一條記錄，不能有重復的，不允許為空 * 外鍵–表的外鍵是另一表的主鍵, 外鍵可以有重復的, 可以是空值 * 索引–該字段沒有重復值，但可以有一個空值 ***** **作用：** * 主鍵–用來保證數據完整性 * 外鍵–用來和其他表建立聯系用的 * 索引–是提高查詢排序的速度 ***** **個數：** * 主鍵–主鍵只能有一個 * 外鍵–一個表可以有多個外鍵 * 索引–一個表可以有多個唯一索引 >[info] 談談對mysql視圖的理解 **什么是基本表？什么是視圖？** 基本表是本身獨立存在的表，在 SQL 中一個關系就對應一個表。 視圖是從一個或幾個基本表導出的表。視圖本 身不獨立存儲在數據庫中，是一個虛擬表. ***** **試述視圖的優點？** (1) 視圖能夠簡化用戶的操作 (2) 視圖使用戶能以多種角度看待同一數據； (3) 視圖為數據庫提供了一定程度的邏 輯獨立性； (4) 視圖能夠對機密數據提供安全保護。