1. MySQL 索引使用有哪些注意事項呢？可以從兩個維度回答這個問題：索引哪些情況會失效，索引不適合哪些場景 索引哪些情況會失效
索引不適合哪些場景
2. MySQL 遇到過死鎖問題嗎，你是如何解決的？排查死鎖的步驟：
3. 日常工作中你是怎么優(yōu)化 SQL 的？可以從這幾個維度回答這個問題：
4. 分庫分表的設計分庫分表方案，分庫分表中間件，分庫分表可能遇到的問題 分庫分表方案
常用的分庫分表中間件
分庫分表可能遇到的問題
5. InnoDB 與 MyISAM 的區(qū)別
6. 數(shù)據(jù)庫索引的原理，為什么要用 B + 樹，為什么不用二叉樹？可以從幾個維度去看這個問題，查詢是否夠快，效率是否穩(wěn)定，存儲數(shù)據(jù)多少，以及查找磁盤次數(shù)，為什么不是二叉樹，為什么不是平衡二叉樹，為什么不是 B 樹，而偏偏是 B + 樹呢？ 為什么不是一般二叉樹？1）當數(shù)據(jù)量大時，樹的高度會比較高（樹的高度決定著它的 IO 操作次數(shù)，IO 操作耗時大），查詢會比較慢。 2）每個磁盤塊（節(jié)點 / 頁）保存的數(shù)據(jù)太?。↖O 本來是耗時操作，每次 IO 只能讀取到一個關鍵字，顯然不合適），沒有很好的利用操作磁盤 IO 的數(shù)據(jù)交換特性，也沒有利用好磁盤 IO 的預讀能力（空間局部性原理），從而帶來頻繁的 IO 操作。 為什么不是平衡二叉樹呢？我們知道，在內(nèi)存比在磁盤的數(shù)據(jù)，查詢效率快得多。如果樹這種數(shù)據(jù)結構作為索引，那我們每查找一次數(shù)據(jù)就需要從磁盤中讀取一個節(jié)點，也就是我們說的一個磁盤塊，但是平衡二叉樹可是每個節(jié)點只存儲一個鍵值和數(shù)據(jù)的，如果是 B 樹，可以存儲更多的節(jié)點數(shù)據(jù)，樹的高度也會降低，因此讀取磁盤的次數(shù)就降下來啦，查詢效率就快啦。 那為什么不是 B 樹而是 B + 樹呢？1）B+Tree 范圍查找，定位 min 與 max 之后，中間葉子節(jié)點，就是結果集，不用中序回溯 2）B+Tree 磁盤讀寫能力更強（葉子節(jié)點不保存真實數(shù)據(jù)，因此一個磁盤塊能保存的關鍵字更多，因此每次加載的關鍵字越多） 3）B+Tree 掃表和掃庫能力更強（B-Tree 樹需要掃描整顆樹，B+Tree 樹只需要掃描葉子節(jié)點） 詳細參考：索引原理 7. 聚集索引與非聚集索引的區(qū)別
何時使用聚集索引或非聚集索引？ 8. limit 1000000 加載很慢的話，你是怎么解決的呢？方案一：如果 id 是連續(xù)的，可以這樣，返回上次查詢的最大記錄 (偏移量)，再往下 limit select id，name from employee where id>1000000 limit 10. 復制代碼 方案二：在業(yè)務允許的情況下限制頁數(shù)： 建議跟業(yè)務討論，有沒有必要查這么后的分頁啦。因為絕大多數(shù)用戶都不會往后翻太多頁。 方案三：order by + 索引（id 為索引） select id，name from employee order by id limit 1000000，10 SELECT a.* FROM employee a, (select id from employee where 條件 LIMIT 1000000,10 ) b where a.id=b.id 復制代碼 方案四：利用延遲關聯(lián)或者子查詢優(yōu)化超多分頁場景。（先快速定位需要獲取的 id 段，然后再關聯(lián)） 9. 如何選擇合適的分布式主鍵方案呢？
10. 事務的隔離級別有哪些？MySQL 的默認隔離級別是什么？什么是事務的隔離性？ 隔離性是指，多個用戶的并發(fā)事務訪問同一個數(shù)據(jù)庫時，一個用戶的事務不應該被其他用戶的事務干擾，多個并發(fā)事務之間要相互隔離。 咱們舉例子來說明： 建表語句： CREATE TABLE `T` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = INNODB; 復制代碼 數(shù)據(jù)列表：
案例一： 事務 A，先執(zhí)行，處于未提交的狀態(tài)： insert into T values(4, wangwu); 復制代碼 事務 B，后執(zhí)行，也未提交： select * from T; 復制代碼 如果事務 B 能夠讀取到 (4, wangwu) 這條記錄，事務 A 就對事務 B 產(chǎn)生了影響，這個影響叫做 “讀臟”，讀到了未提交事務操作的記錄。 案例二： 事務 A，先執(zhí)行： select * from T where id=1; 復制代碼 結果集為：1, xiaohong 事務 B，后執(zhí)行，并且提交： update T set name=hzy where id=1; 復制代碼 commit; 事務 A，再次執(zhí)行相同的查詢： select * from T where id=1; 復制代碼 結果集為：1, hzy 這次是已提交事務 B 對事務 A 產(chǎn)生的影響，這個影響叫做 “不可重復讀”，一個事務內(nèi)相同的查詢，得到了不同的結果。 案例三： 事務 A，先執(zhí)行： select * from T where id>3; 復制代碼 結果集為： NULL 事務 B，后執(zhí)行，并且提交： insert into T values(4, wangwu); commit; 復制代碼 事務 A，首次查詢了 id>3 的結果為 NULL，于是想插入一條為 4 的記錄： insert into T values(4, hzy); 復制代碼 結果集為： Error : duplicate key! 這次是已提交事務 B 對事務 A 產(chǎn)生的影響，這個影響叫做 “幻讀”。 可以看到，并發(fā)的事務可能導致其他事務：
InnoDB 實現(xiàn)了四種不同事務的隔離級別：
不同事務的隔離級別，實際上是一致性與并發(fā)性的一個權衡與折衷。 InnoDB 的四種事務的隔離級別，分別是怎么實現(xiàn)的？ InnoDB 使用不同的鎖策略 (Locking Strategy) 來實現(xiàn)不同的隔離級別。 讀未提交 (Read Uncommitted)這種事務隔離級別下，select 語句不加鎖。 此時，可能讀取到不一致的數(shù)據(jù)，即 “讀臟”。這是并發(fā)最高，一致性最差的隔離級別。 串行化 (Serializable)這種事務的隔離級別下，所有 select 語句都會被隱式的轉化為 select … in share mode. 這可能導致，如果有未提交的事務正在修改某些行，所有讀取這些行的 select 都會被阻塞住。 這是一致性最好的，但并發(fā)性最差的隔離級別。 在互聯(lián)網(wǎng)大數(shù)據(jù)量，高并發(fā)量的場景下，幾乎不會使用上述兩種隔離級別。 可重復讀 (Repeated Read, RR) 這是 InnoDB 默認的隔離級別，在 RR 下：①普通的 select 使用快照讀 (snapshot read)，這是一種不加鎖的一致性讀 (Consistent Nonlocking Read)，底層使用 MVCC 來實現(xiàn)； ②加鎖的 select (select … in share mode /select … for update), update, delete 等語句，它們的鎖，依賴于它們是否在唯一索引 (unique index) 上使用了唯一的查詢條件 (unique search condition)，或者范圍查詢條件 (range-type search condition)：
讀提交 (Read Committed, RC) 這是互聯(lián)網(wǎng)最常用的隔離級別，在 RC 下：①普通讀是快照讀； ②加鎖的 select, update, delete 等語句，除了在外鍵約束檢查 (foreign-key constraint checking) 以及重復鍵檢查 (duplicate-key checking) 時會封鎖區(qū)間，其他時刻都只使用記錄鎖； 此時，其他事務的插入依然可以執(zhí)行，就可能導致，讀取到幻影記錄。 11. 在高并發(fā)情況下，如何做到安全的修改同一行數(shù)據(jù)？要安全的修改同一行數(shù)據(jù)，就要保證一個線程在修改時其它線程無法更新這行記錄。一般有悲觀鎖和樂觀鎖兩種方案 使用悲觀鎖悲觀鎖思想就是，當前線程要進來修改數(shù)據(jù)時，別的線程都得拒之門外～比如，可以使用 select…for update select * from User where name='jay’ for update 復制代碼 以上這條 sql 語句會鎖定了 User 表中所有符合檢索條件（name='jay’）的記錄。本次事務提交之前，別的線程都無法修改這些記錄。 使用樂觀鎖樂觀鎖思想就是，有線程過來，先放過去修改，如果看到別的線程沒修改過，就可以修改成功，如果別的線程修改過，就修改失敗或者重試。實現(xiàn)方式：樂觀鎖一般會使用版本號機制或 CAS 算法實現(xiàn)。 12. 數(shù)據(jù)庫的樂觀鎖和悲觀鎖悲觀鎖悲觀鎖她專一且缺乏安全感了，她的心只屬于當前事務，每時每刻都擔心著它心愛的數(shù)據(jù)可能被別的事務修改，所以一個事務擁有（獲得）悲觀鎖后，其他任何事務都不能對數(shù)據(jù)進行修改啦，只能等待鎖被釋放才可以執(zhí)行。 樂觀鎖樂觀鎖的 “樂觀情緒” 體現(xiàn)在，它認為數(shù)據(jù)的變動不會太頻繁。因此，它允許多個事務同時對數(shù)據(jù)進行變動。實現(xiàn)方式：樂觀鎖一般會使用版本號機制或 CAS 算法實現(xiàn)。 13. SQL 優(yōu)化的一般步驟是什么，怎么看執(zhí)行計劃（explain），如何理解其中各個字段的含義？
14. select for update 有什么含義，會鎖表還是鎖行還是其他？select for update 含義 select 查詢語句是不會加鎖的，但是 select for update 除了有查詢的作用外，還會加鎖呢，而且它是悲觀鎖哦。至于加了是行鎖還是表鎖，這就要看是不是用了索引 / 主鍵啦。 沒用索引 / 主鍵的話就是表鎖，否則就是是行鎖。 15. MySQL 事務得四大特性以及實現(xiàn)原理
事務 ACID 特性的實現(xiàn)思想
16. 如果某個表有近千萬數(shù)據(jù)，CRUD 比較慢，如何優(yōu)化？分庫分表某個表有近千萬數(shù)據(jù)，可以考慮優(yōu)化表結構，分表（水平分表，垂直分表），當然，你這樣回答，需要準備好面試官問你的分庫分表相關問題呀，如
索引優(yōu)化除了分庫分表，優(yōu)化表結構，當然還有所以索引優(yōu)化等方案～ 17. 如何寫 sql 能夠有效的使用到復合索引？復合索引，也叫組合索引，用戶可以在多個列上建立索引，這種索引叫做復合索引。 當我們創(chuàng)建一個組合索引的時候，如 (k1,k2,k3)，相當于創(chuàng)建了（k1）、(k1,k2) 和 (k1,k2,k3) 三個索引，這就是最左匹配原則。 select * from table where k1=A AND k2=B AND k3=D 復制代碼 有關于復合索引，我們需要關注查詢 Sql 條件的順序，確保最左匹配原則有效，同時可以刪除不必要的冗余索引。 18. mysql 中 in 和 exists 的區(qū)別假設表 A 表示某企業(yè)的員工表，表 B 表示部門表，查詢所有部門的所有員工，很容易有以下 SQL: select * from A where deptId in (select deptId from B); 復制代碼 這樣寫等價于： 先查詢部門表 B select deptId from B 再由部門 deptId，查詢 A 的員工 select * from A where A.deptId = B.deptId 可以抽象成這樣的一個循環(huán)： List<> resultSet ;
for(int i=0;i<B.length;i++) {
for(int j=0;j<A.length;j++) {
if(A[i].id==B[j].id) {
resultSet.add(A[i]);
break;
}
}
}
復制代碼顯然，除了使用 in，我們也可以用 exists 實現(xiàn)一樣的查詢功能，如下： select * from A where exists (select 1 from B where A.deptId = B.deptId); 復制代碼 因為 exists 查詢的理解就是，先執(zhí)行主查詢，獲得數(shù)據(jù)后，再放到子查詢中做條件驗證，根據(jù)驗證結果（true 或者 false），來決定主查詢的數(shù)據(jù)結果是否得意保留。 那么，這樣寫就等價于： select * from A, 先從 A 表做循環(huán) select * from B where A.deptId = B.deptId, 再從 B 表做循環(huán). 同理，可以抽象成這樣一個循環(huán)： List<> resultSet ;
for(int i=0;i<A.length;i++) {
for(int j=0;j<B.length;j++) {
if(A[i].deptId==B[j].deptId) {
resultSet.add(A[i]);
break;
}
}
}
復制代碼數(shù)據(jù)庫最費勁的就是跟程序鏈接釋放。假設鏈接了兩次，每次做上百萬次的數(shù)據(jù)集查詢，查完就走，這樣就只做了兩次；相反建立了上百萬次鏈接，申請鏈接釋放反復重復，這樣系統(tǒng)就受不了了。即 mysql 優(yōu)化原則，就是小表驅動大表，小的數(shù)據(jù)集驅動大的數(shù)據(jù)集，從而讓性能更優(yōu)。 因此，我們要選擇最外層循環(huán)小的，也就是，如果 B 的數(shù)據(jù)量小于 A，適合使用 in，如果 B 的數(shù)據(jù)量大于 A，即適合選擇 exists，這就是 in 和 exists 的區(qū)別。 19. 數(shù)據(jù)庫自增主鍵可能遇到什么問題？使用自增主鍵對數(shù)據(jù)庫做分庫分表，可能出現(xiàn)諸如主鍵重復等的問題。解決方案的話，簡單點的話可以考慮使用 UUID 哈 自增主鍵會產(chǎn)生表鎖，從而引發(fā)問題 自增主鍵可能用完問題。 20. MVCC 底層原理我們聊下 MySQL 是如何實現(xiàn) Read Repeatable 的吧，因為一般我們都不修改這個隔離級別，但是你得清楚是怎么回事兒，MySQL 是通過 MVCC 機制來實現(xiàn)的，就是多版本并發(fā)控制，multi-version concurrency control。 innodb 存儲引擎，會在每行數(shù)據(jù)的最后加兩個隱藏列，一個保存行的創(chuàng)建時間，一個保存行的刪除時間，但是這兒存放的不是時間，而是事務 id，事務 id 是 mysql 自己維護的自增的，全局唯一。 事務 id，在 mysql 內(nèi)部是全局唯一遞增的，事務 id=1，事務 id=2，事務 id=3
事務 ID=121 的事務，查詢 ID=1 的這一行數(shù)據(jù)，一定會找到創(chuàng)建事務 ID<= 當前事務 ID 的那一行，select * from table where id = 1，就可以查到上面那一行。 事務 ID=122 的事務，將 ID=1 的這一行刪除了，此時就會將 ID=1 的行的刪除事務 ID 設置成 122
事務 ID=121 的事務，再次查詢 ID=1 的那一行，能查到，創(chuàng)建事務 ID<= 當前事務 ID，當前事務 ID < 刪除事務 ID
事務 id=121 的事務，查詢 id=2 的那一行，查到 name = 李四
事務 id=122 的事務，將 id=2 的那一行的 name 修改成 name = 小李四 Innodb 存儲引擎，對于同一個 ID，不同的事務創(chuàng)建或修改，每個事務都有自己的快照（會插入一條記錄） 事務 id=121 的事務，查詢 id=2 的那一行，答案是：李四，創(chuàng)建事務 id <= 當前事務 id，當前事務 id < 刪除事務 id. 在一個事務內(nèi)查詢的時候，mysql 只會查詢創(chuàng)建事務 id <= 當前事務 id 的行，這樣可以確保這個行是在當前事務中創(chuàng)建，或者是之前創(chuàng)建的；同時一個行的刪除事務 id 要么沒有定義（就是沒刪除），要么是比當前事務 id 大（在事務開啟之后才被刪除）；滿足這兩個條件的數(shù)據(jù)都會被查出來。 那么如果某個事務執(zhí)行期間，別的事務更新了一條數(shù)據(jù)呢？這個很關鍵的一個實現(xiàn)，其實就是在 innodb 中，是插入了一行記錄，然后將新插入的記錄的創(chuàng)建時間設置為新的事務的 id，同時將這條記錄之前的那個版本的刪除時間設置為新的事務的 id。 現(xiàn)在 get 到這個點了吧？這樣的話，你的這個事務其實對某行記錄的查詢，始終都是查找的之前的那個快照，因為之前的那個快照的創(chuàng)建時間小于等于自己事務 id，然后刪除時間的事務 id 比自己事務 id 大，所以這個事務運行期間，會一直讀取到這條數(shù)據(jù)的同一個版本。 21. 數(shù)據(jù)庫中間件了解過嗎，sharding jdbc，mycat？sharding-jdbc 目前是基于 jdbc 驅動，無需額外的 proxy，因此也無需關注 proxy 本身的高可用。 Mycat 是基于 Proxy，它復寫了 MySQL 協(xié)議，將 Mycat Server 偽裝成一個 MySQL 數(shù)據(jù)庫，而 Sharding-JDBC 是基于 JDBC 接口的擴展，是以 jar 包的形式提供輕量級服務的。 22. MySQL 的主從延遲，你怎么解決？ 主從復制分了五個步驟進行：
主從同步延遲的原因一個服務器開放Ｎ個鏈接給客戶端來連接的，這樣有會有大并發(fā)的更新操作，但是從服務器的里面讀取 binlog 的線程僅有一個，當某個 SQL 在從服務器上執(zhí)行的時間稍長 或者由于某個 SQL 要進行鎖表就會導致，主服務器的 SQL 大量積壓，未被同步到從服務器里。這就導致了主從不一致， 也就是主從延遲。 主從同步延遲的解決辦法可以參考沈劍老師的文章：數(shù)據(jù)庫主從不一致怎么解決？ 23. 說一下大表查詢的優(yōu)化方案
24. 什么是數(shù)據(jù)庫連接池？為什么需要數(shù)據(jù)庫連接池呢？連接池基本原理： 數(shù)據(jù)庫連接池原理：在內(nèi)部對象池中，維護一定數(shù)量的數(shù)據(jù)庫連接，并對外暴露數(shù)據(jù)庫連接的獲取和返回方法。 應用程序和數(shù)據(jù)庫建立連接的過程
數(shù)據(jù)庫連接池好處
25. 一條 SQL 語句在 MySQL 中如何執(zhí)行的？MySQL 邏輯架構圖MySQL 分為 Server 層和存儲引擎層兩個部分，不同的存儲引擎共用一個 Server 層。  Server 層：大多數(shù) MySQL 的核心服務功能都在這一層，包括連接處理、授權認證、查詢解析、分析、優(yōu)化、緩存以及所有的內(nèi)置函數(shù)（例如，日期、時間、數(shù)學和加密函數(shù)），所有跨存儲引擎的功能都在這一層實現(xiàn)，比如存儲過程、觸發(fā)器、視圖等。 存儲引擎層：存儲引擎負責 MySQL 中數(shù)據(jù)的存儲和提取。服務器通過 API 與存儲引擎進行通信。這些接口屏蔽了不同存儲引擎之間的差異，使得這些差異對上層的查詢過程透明。 MySQL 客戶端與服務端的通信方式是 “半雙工”，客戶端一旦開始發(fā)送消息另一端要接收完整這個消息才能響應，客戶端一旦開始接收數(shù)據(jù)就沒法停下來發(fā)送指令，一請求一響應。 連接器第一步，先連接到數(shù)據(jù)庫上，當客戶端（應用）連接到 MySQL 服務器時，服務器需要對其進行認證，認證基于用戶名、原始主機信息和密碼，一旦客戶端連接成功，服務器會繼續(xù)驗證客戶端是否具有執(zhí)行某個特定查詢的權限（例如，是否允許客戶端對某一數(shù)據(jù)庫的某一表執(zhí)行 SELECT 語句） 連接命令： mysql -h$ip -p$port -u$user -p 復制代碼 輸完命令之后，需要在交互對話里面輸入密碼，密碼不建議在 - p 后面直接輸入，這樣會導致密碼泄露。 查詢緩存第二步，查詢緩存，每次 MySQL 執(zhí)行過的語句及其結果會以 key-value 形式緩存在內(nèi)存中，key 是查詢語句，value 是查詢結果。如果查詢能夠在緩存中找到 key，那么這個 value 就會被直接返回客戶端。 但是大多數(shù)情況下我會建議不要使用緩存，因為查詢緩存的失效非常頻繁，只要對一個表的更新，即便是更新一些與緩存無關的字段，這個表所有的緩存都會被清空，因此很可能會費勁地把結果存起來，還沒使用就被一個更新全部清空，對于更新壓力的數(shù)據(jù)庫來說，查詢緩存的命中率會非常低，除非業(yè)務就一張靜態(tài)表，很長時間才會更新一次。（例如系統(tǒng)配置表） MySQL 提供了按需使用的方式，可以將參數(shù) query_cache_type 設置為 DEMAND，這對于默認的 SQL 不使用查詢緩存，而對于確定要使用查詢緩存的語句，可以使用 SQL_CACHE 顯示指定。（SELECT SQL_CACHE * FROM TAB） 通過查詢語句做哈希算法得到一個哈希值，因此這里要想命中緩存，查詢 SQL 和緩存 SQL 必須完全一致，每次檢查緩存是否命中時都會對緩存加鎖，對于一個讀寫頻繁的系統(tǒng)使用查詢緩存很有可能降低查詢 注意：MySQL8.0 版本直接將緩存的整個功能模塊刪掉了 分析器第三步，分析器，如果沒有命中緩存，就會執(zhí)行 SQL 語句，首先讓 MySQL 知道我們需要做什么，因此需要對 SQL 語句解析，MySQL 從輸入的 “select” 關鍵字識別出來，這是一條查詢語句，把字符串 “TAB” 識別成表名 TAB，檢查查詢中涉及的表和數(shù)據(jù)列是否存在或別名是否有歧義 解析器的工作：語法分析（生成句子），語義分析（確保這些句子講得通），以及代碼生成（為編譯準備） 注意：分析器和解析器是一個東西，有些書叫分析器，有些書叫解析器，就是不同的叫法而已 優(yōu)化器第四步，優(yōu)化器，經(jīng)過分析器 MySQL 知道我們需要什么了，在開始執(zhí)行前，還要經(jīng)過優(yōu)化器進行處理，優(yōu)化器是在表里面有多個索引時，決定使用哪個索引，或者在一個語句有多表關聯(lián)（join）時，決定各個表的連接順序。 優(yōu)化器會生成執(zhí)行計劃 執(zhí)行器第五步，執(zhí)行器，MySQL 通過分析器知道要做什么，通過優(yōu)化器知道怎么做，開始執(zhí)行前，要先判斷一下是否有表 TABLE 查詢權限，如果有打開表，根據(jù)表的引擎定義，去使用這個引擎提供的接口。 根據(jù)執(zhí)行計劃，調(diào)用存儲引擎 API 來查詢數(shù)據(jù) 26. InnoDB 引擎中的索引策略，了解過嗎？只有當索引幫助存儲引擎快速查找到記錄帶來的好處大于其帶來的額外工作時，索引才是有效的。對于非常小的表，大部分情況下簡單的全表掃描更高效，對于中到大型的表，索引就非常有效。 正確地創(chuàng)建和使用索引是實現(xiàn)高性能查詢的基礎。 獨立的列如果查詢中的列不是獨立的，則 MySQL 就不會使用索引，’獨立的列’是指索引列不能是表達式的一部分，也不是函數(shù)的參數(shù)。 select actor_id from skill.actor where actor_id + 1 = 5, 這個查詢無法使用 actor_id 列的索引；select actor_id from skill.actor where to_days (current_date) - to_days (date_col) <= 10, 這個也不會使用索引。 前綴索引和索引選擇性有時候需要索引很長的字符列，這會讓索引變得大且慢，通常可以索引開始的部分字符，這樣可以大大節(jié)約索引空間，從而提高索引效率，但這樣也會降低索引的選擇性，索引的選擇性是指，不重復的索引值和數(shù)據(jù)表的記錄總數(shù)（#T）的比值，范圍從 1/#T 到 1 之間，索引的選擇性越高則查詢效率越高，因為選擇性高的索引可以讓 MySQL 在查找時過濾掉更多的行，唯一索引的選擇性是 1，這是最好的索引選擇性，性能也是最好的。 一般情況下某個列前綴的選擇性也是足夠高的，足以滿足查詢性能，對于 BLOB、TEXT 或者很長的 VARCHAR 類型的列，必須使用前綴索引，因為 MySQL 不允許索引這些列的完整程度。 訣竅在于要選擇足夠長的前綴以保證較高的選擇性，同時又不能太長（以便節(jié)約空間）。 如何選擇合適的前綴？ ①我們可以通過 left 函數(shù) ②計算完整列的選擇性，并使前綴的選擇性接近于完整列的選擇性； select count (distinct city)/count (*) from skill.city_demo，選擇性 0.0312 select count (distinct left (city,6))/count (*) from skill.city_demo，選擇性 0.0309 select count (distinct left (city,7))/count (*) from skill.city_demo，選擇性 0.0310 前綴索引是一種能使索引更小、更快的有效方法，但另一方面也有其缺點：MySQL 無法使用前綴索引做 ORDER BY 和 GROUP BY，也無法使用前綴索引做覆蓋掃描。 有時候后綴索引也有用途，MySQL 原聲不支持反向索引，但是可以把字符串反轉后存儲，并基于此建立前綴索引。 多列索引一個常見的錯誤就是，為每個列創(chuàng)建一個獨立的索引或者按照錯誤的順序創(chuàng)建多列索引。 當出現(xiàn)服務器對多個索引做相交操作時（通常有多個 AND 條件），通常意味著需要一個包含所有相關列的多列索引，而不是多個獨立的單列索引。 當服務器需要對多個索引做聯(lián)合操作時（通常有多個 OR 條件），通常需要耗費大量 CPU 和內(nèi)存資源在算法的緩存、排序合并操作上，特別是當其中有些索引的選擇性不高，需要合并掃描返回的大量數(shù)據(jù)的時候，導致該執(zhí)行計劃還不如直接走全表掃描，這樣做不但會消耗更多的 CPU 和內(nèi)存資源，還可能會影響查詢的并發(fā)性。 單列索引：節(jié)點中關鍵字【name】 聯(lián)合索引：節(jié)點中關鍵字【name，phoneNum】 聯(lián)合索引列選擇原則： ①經(jīng)常用的列優(yōu)先【最左匹配原則】 ②選擇性（離散性）高的優(yōu)先【離散度高原則】 ③寬度小的列優(yōu)先【最少空間原則】 優(yōu)先級 1>2>3 select * from t_user where name = ? select * from t_user where name = ? and phoneNum = ? create index index_name on t_user(name) create index index_name_phoneNum on t_user(name,phoneNum) 這種做法是錯誤的，根據(jù)最左匹配原則，兩條查詢都可以走 index_name_phoneNum 索引，index_name 索引就是冗余索引。 選擇合適的索引列順序當不需要考慮排序和分組時，將選擇性最高的列放在前面通常是很好的，這時索引的作用只是用于優(yōu)化 WHERE 條件的查找，在這種情況下，這種設計的索引確實能夠最快地過濾出需要的行，對于在 WHERE 字句中只使用了索引部分前綴列的查詢來說選擇性也更高，然而，性能不只是依賴于所有索引列的選擇性，也和查詢條件的具體值有關，也就是和值的分布有關，可能需要根據(jù)那些運行頻率最高的查詢來調(diào)整索引列的順序，讓這種情況下索引的選擇性最高。 聚簇索引聚簇索引并不是一種單獨的索引類型，而是一種數(shù)據(jù)存儲方式，具體的細節(jié)依賴于其實現(xiàn)方式，但 InnoDB 的聚簇索引實際上在同一個結構中保存了 B+Tree 索引和數(shù)據(jù)行。 當表有聚簇索引時，它的數(shù)據(jù)行實際上存放在索引的葉子頁中，術語 “聚簇” 表示數(shù)據(jù)行和相鄰的鍵值緊湊地存儲在一起（這并非總成立），因為無法同時把數(shù)據(jù)行存在兩個不同的地方，所以一個表只能有一個聚簇索引（不過，覆蓋索引可以模擬多個聚簇索引的情況）。 因為是存儲引擎負責實現(xiàn)索引，因此不是所有的存儲引擎都支持聚簇索引，這里只關注 InnoDB。 列的離散性找出離散性好的列，離散性越高，可選擇性就越好。  27. 數(shù)據(jù)庫存儲日期格式時，如何考慮時區(qū)轉換問題？
28. 一條 sql 執(zhí)行過長的時間，你如何優(yōu)化，從哪些方面入手？
29. Blob 和 text 有什么區(qū)別？
30. MySQL 里記錄貨幣用什么字段類型比較好？
31. InnoDB 有哪幾種鎖？如何使用普通鎖保證一致性？ ①操作數(shù)據(jù)前，加鎖，實施互斥，不允許其他的并發(fā)任務操作； ②操作完成后，釋放鎖，讓其他任務執(zhí)行；如此這般，來保證一致性。 普通鎖存在什么問題？ 簡單的鎖住太過粗暴，連 “讀任務” 也無法并行，任務執(zhí)行過程本質上是串行的。 共享 / 排它鎖 (Shared and Exclusive Locks)簡單的鎖住太過粗暴，連 “讀任務” 也無法并行，任務執(zhí)行過程本質上是串行的。于是出現(xiàn)了共享鎖與排他鎖：
共享鎖與排他鎖：
可以看到，一旦寫數(shù)據(jù)的任務沒有完成，數(shù)據(jù)是不能被其他任務讀取的，這對并發(fā)度有較大的影響。 有沒有可能，進一步提高并發(fā)呢？ 即使寫任務沒有完成，其他讀任務也可能并發(fā)，MySQL 通過多版本控制解決此問題。（快照讀） 意向鎖 (Intention Locks)InnoDB 支持多粒度鎖 (multiple granularity locking)，它允許行級鎖與表級鎖共存，實際應用中，InnoDB 使用的是意向鎖。 意向鎖是指，未來的某個時刻，事務可能要加共享 / 排它鎖了，先提前聲明一個意向。 意向鎖的特點： ①首先，意向鎖，是一個表級別的鎖 (table-level locking)； ②意向鎖分為：
舉個例子： select … lock in share mode，要設置 IS 鎖； select … for update，要設置 IX 鎖； ③意向鎖協(xié)議 (intention locking protocol) 并不復雜： 事務要獲得某些行的 S 鎖，必須先獲得表的 IS 鎖 事務要獲得某些行的 X 鎖，必須先獲得表的 IX 鎖 ④由于意向鎖僅僅表明意向，它其實是比較弱的鎖，意向鎖之間并不相互互斥，而是可以并行，其兼容互斥表如下：
⑤既然意向鎖之間都相互兼容，那其意義在哪里呢？它會與共享鎖 / 排它鎖互斥，其兼容互斥表如下：
補充：排它鎖是很強的鎖，不與其他類型的鎖兼容。這也很好理解，修改和刪除某一行的時候，必須獲得強鎖，禁止這一行上的其他并發(fā)，以保障數(shù)據(jù)的一致性。 意向鎖解決什么問題？ 事務 A 獲取了某一行的排它鎖，并未提交： select * from table where id = 6 from update 事務 B 想要獲取 table 表的表鎖： LOCK TABLES table READ; 因為共享鎖與排它鎖互斥，所以事務 B 在視圖對 table 表加共享鎖的時候，必須保證： ①當前沒有其他事務持有 table 表的排它鎖。 ②當前沒有其他事務持有 table 表中任意一行的排它鎖 。 為了檢測是否滿足第二個條件，事務 B 必須在確保 table 表不存在任何排它鎖的前提下，去檢測表中的每一行是否存在排它鎖。很明顯這是一個效率很差的做法，但是有了意向鎖之后，事務 A 持有了 table 表的意向排它鎖，就可得知事務 A 必然持有該表中某些數(shù)據(jù)行的排它鎖，而無需去檢測表中每一行是否存在排它鎖 意向鎖之間為什么互相兼容？ 事務 A 先獲取了某一行的排他鎖，并未提交： select * from users where id = 6 for update ①事務 A 獲取了 users 表上的意向排他鎖。 ②事務 A 獲取了 id 為 6 的數(shù)據(jù)行上的排他鎖。 之后事務 B 想要獲取 users 表的共享鎖： LOCK TABLES users READ; 事務 B 檢測到事務 A 持有 users 表的意向排他鎖。 事務 B 對 users 表的加鎖請求被阻塞（排斥）。 最后事務 C 也想獲取 users 表中某一行的排他鎖： select * from users where id = 5 for update; ①事務 C 申請 users 表的意向排他鎖。 ②事務 C 檢測到事務 A 持有 users 表的意向排他鎖。 ③因為意向鎖之間并不互斥，所以事務 C 獲取到了 users 表的意向排他鎖。 ④因為 id 為 5 的數(shù)據(jù)行上不存在任何排他鎖，最終事務 C 成功獲取到了該數(shù)據(jù)行上的排他鎖。 如果意向鎖之間互斥，行級鎖的意義將會失去 記錄鎖 (Record Locks)記錄鎖，它封鎖索引記錄，例如： select * from t where id=1 for update; 它會在 id=1 的索引記錄上加鎖，以阻止其他事務插入，更新，刪除 id=1 的這一行。 需要說明的是： select * from t where id=1; 則是快照讀 (SnapShot Read)，它并不加鎖，具體在《17. 什么是快照讀？》中做了詳細闡述。 間隙鎖 (Gap Locks)間隙鎖，它封鎖索引記錄中的間隔，或者第一條索引記錄之前的范圍，又或者最后一條索引記錄之后的范圍。 存儲引擎：InnoDB 隔離級別：可重復讀隔離級別 建表語句： mysql> CREATE TABLE `T` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = INNODB; 復制代碼 數(shù)據(jù)列表：
這個 SQL 語句 select * from T where id between 8 and 15 for update; 會封鎖區(qū)間，以阻止其他事務 id=10 的記錄插入。 為什么要阻止 id=10 的記錄插入？ 如果能夠插入成功，頭一個事務執(zhí)行相同的 SQL 語句，會發(fā)現(xiàn)結果集多出了一條記錄，即幻影數(shù)據(jù)。 間隙鎖的主要目的，就是為了防止其他事務在間隔中插入數(shù)據(jù)，以導致 “不可重復讀”。 如果把事務的隔離級別降級為讀提交 (Read Committed, RC)，間隙鎖則會自動失效。 臨鍵鎖 (Next-key Locks)臨鍵鎖，是記錄鎖與間隙鎖的組合，它的封鎖范圍，既包含索引記錄，又包含索引區(qū)間。 更具體的，臨鍵鎖會封鎖索引記錄本身，以及索引記錄之前的區(qū)間。 如果一個會話占有了索引記錄 R 的共享 / 排他鎖，其他會話不能立刻在 R 之前的區(qū)間插入新的索引記錄。 存儲引擎：InnoDB 隔離級別：可重復讀隔離級別 建表語句： mysql> CREATE TABLE `T` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = INNODB; 復制代碼 數(shù)據(jù)列表：
PK 上潛在的臨鍵鎖為： (-infinity, 1] (1, 3] (3, 5] (5, 9] (9, +infinity] 臨鍵鎖的主要目的，也是 為了避免幻讀 (Phantom Read) 。如果把事務的隔離級別降級為 RC，臨鍵鎖則也會失效。 插入意向鎖 (Insert Intention Locks)對已有數(shù)據(jù)行的修改與刪除，必須加強互斥鎖 X 鎖，那對于數(shù)據(jù)的插入，是否還需要加這么強的鎖，來實施互斥呢？插入意向鎖，孕育而生。 插入意向鎖，是間隙鎖 (Gap Locks) 的一種（所以，也是實施在索引上的），它是專門針對 insert 操作的。 多個事務，在同一個索引，同一個范圍區(qū)間插入記錄時，如果插入的位置不沖突，不會阻塞彼此。 存儲引擎：InnoDB 隔離級別：可重復讀隔離級別 建表語句： mysql> CREATE TABLE `T` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = INNODB; 復制代碼 數(shù)據(jù)列表：
事務 A 先執(zhí)行，在 10 與 20 兩條記錄中插入了一行，還未提交： insert into t values(11, xxx); 事務 B 后執(zhí)行，也在 10 與 20 兩條記錄中插入了一行： insert into t values(12, ooo); 會使用什么鎖？事務 B 會不會被阻塞呢？ 回答：雖然事務隔離級別是 RR，雖然是同一個索引，雖然是同一個區(qū)間，但插入的記錄并不沖突，故這里： 使用的是插入意向鎖，并不會阻塞事務 B 自增鎖 (Auto-inc Locks)案例說明： 存儲引擎：InnoDB 隔離級別：可重復讀隔離級別 建表語句： mysql> CREATE TABLE `T` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = INNODB; 復制代碼 數(shù)據(jù)列表：
事務 A 先執(zhí)行，還未提交： insert into t (name) values (xxx); 事務 B 后執(zhí)行： insert into t (name) values (ooo); 事務 B 會不會被阻塞？ 案例分析： InnoDB 在 RR 隔離級別下，能解決幻讀問題，上面這個案例中： ①事務 A 先執(zhí)行 insert，會得到一條 (4, xxx) 的記錄，由于是自增列，故不用顯示指定 id 為 4，InnoDB 會自動增長，注意此時事務并未提交； ②事務 B 后執(zhí)行 insert，假設不會被阻塞，那會得到一條 (5, ooo) 的記錄； 此時，并未有什么不妥，但如果， ③事務 A 繼續(xù) insert： insert into t(name) values(xxoo); 會得到一條 (6, xxoo) 的記錄。 ④事務 A 再 select： select * from t where id>3; 得到的結果是： 4, xxx 6, xxoo 補充：不可能查詢到 5 的記錄，再 RR 的隔離級別下，不可能讀取到還未提交事務生成的數(shù)據(jù)。 這對于事務 A 來說，就很奇怪了，對于 AUTO_INCREMENT 的列，連續(xù)插入了兩條記錄，一條是 4，接下來一條變成了 6，就像莫名其妙的幻影。 自增鎖是一種特殊的表級別鎖（table-level lock），專門針對事務插入 AUTO_INCREMENT 類型的列。最簡單的情況，如果一個事務正在往表中插入記錄，所有其他事務的插入必須等待，以便第一個事務插入的行，是連續(xù)的主鍵值。 與此同時，InnoDB 提供了 innodb_autoinc_lock_mode 配置，可以調(diào)節(jié)與改變該鎖的模式與行為。 32. Hash 索引和 B + 樹區(qū)別是什么？你在設計索引是怎么抉擇的？
33. mysql 的內(nèi)連接、左連接、右連接有什么區(qū)別？
34. 什么是內(nèi)連接、外連接、交叉連接、笛卡爾積呢？
35. 說一下數(shù)據(jù)庫的三大范式
36. mysql 有關權限的表有哪幾個呢？MySQL 服務器通過權限表來控制用戶對數(shù)據(jù)庫的訪問，權限表存放在 mysql 數(shù)據(jù)庫里，由 mysql_install_db 腳本初始化。這些權限表分別 user，db，table_priv，columns_priv 和 host。
主從復制 binlog 格式有哪幾種？有什么區(qū)別？①STATEMENT，基于語句的日志記錄，把所有寫操作的 sql 語句寫入 binlog （默認） 例如 update xxx set update_time = now () where pk_id = 1，這時，主從的 update_time 不一致 優(yōu)點： 成熟的技術。 更少的數(shù)據(jù)寫入日志文件。當更新或刪除影響許多行時，這將導致 日志文件所需的存儲空間大大減少。這也意味著從備份中獲取和還原可以更快地完成。 日志文件包含所有進行了任何更改的語句，因此它們可用于審核數(shù)據(jù)庫。 缺點： 有很多函數(shù)不能復制，例如 now ()、random ()、uuid () 等 ②ROW，基于行的日志記錄，把每一行的改變寫入 binlog，假設一條 sql 語句影響 100 萬行，從節(jié)點需要執(zhí)行 100 萬次，效率低。 優(yōu)點：可以復制所有更改，這是最安全的復制形式 缺點：如果該 SQL 語句更改了許多行，則基于行的復制可能會向二進制日志中寫入更多的數(shù)據(jù)。即使對于回滾的語句也是如此。這也意味著制作和還原備份可能需要更多時間。此外，二進制日志被鎖定更長的時間以寫入數(shù)據(jù)，這可能會導致并發(fā)問題。 ③MIXED，混合模式，如果 sql 里有函數(shù)，自動切換到 ROW 模式，如果 sql 里沒有會造成主從復制不一致的函數(shù)，那么就使用 STATEMENT 模式。（存在問題：解決不了系統(tǒng)變量問題，例如 @@host name，主從的主機名不一致） 38. Mysql 主從復制方式？有什么區(qū)別？①異步復制 網(wǎng)絡或機器故障時，會造成數(shù)據(jù)不一致 數(shù)據(jù)不一致緩解方案：半同步，插入主庫時，不會及時返回給我們的 web 端，他會進行等待，等待從庫的 I/OThread 從主節(jié)點 Binary log 讀取二進制文件并拷貝到從節(jié)點的 relaybinlog 之后，在進行返回。（不是等待所有，一個從節(jié)點復制過去就行了） 數(shù)據(jù)強一致性了但是性能低：可以設置超時時間（多個 Slave，或者 Slave 非常卡，會導致響應非常慢？不會，有保護機制，超過時間就直接返回，一般情況下設置 1 秒） 注意：不是等待所有從節(jié)點同步從主節(jié)點 Binary log 讀取二進制文件并拷貝到從節(jié)點的 relaybinlog 之后才返回，而是只要有一個節(jié)點拷貝成功就返回 根據(jù)業(yè)務場景選擇同步和半同步 注意：半同步只會緩解數(shù)據(jù)不一致問題，并不能完全解決 ②半同步復制（MySQL 8.0 還支持通過插件實現(xiàn)的半同步復制接口） 默認情況下，MySQL 復制是異步的。Master 將事件寫入其二進制日志，Slave 將在事件就緒時請求它們。Master 不知道 Slave 是否或何時檢索和處理了事務，并且不能保證任何事件都會到達 Slave。使用異步復制，如果 Master 崩潰，則它提交的事務可能不會傳輸?shù)饺魏?Slave。在這種情況下，從 Master 到 Slave 的故障轉移可能會導致故障轉移到缺少相對于 Master 的事務的服務器。 在完全同步復制的情況下，當 Master 提交事務時，所有 Slave 也都已提交事務，然后 Master 才返回執(zhí)行該事務的會話。完全同步復制意味著可以隨時從 Master 故障轉移到任何 Slave。完全同步復制的缺點是完成事務可能會有很多延遲。 半同步復制介于異步復制和完全同步復制之間。Master 等待直到至少一個 Slave 接收并記錄了事件（所需數(shù)量的 Slave 是可配置的），然后提交事務。Master 不等待所有 Slave 都確認收到，它僅需要 Slave 的確認，而不是事件已在 Slave 端完全執(zhí)行并提交。因此，半同步復制可確保如果 Master 崩潰，則它已提交的所有事務都已傳輸?shù)街辽僖粋€ Slave。 與異步復制相比，半同步復制提供了改進的數(shù)據(jù)完整性，因為眾所周知，當提交成功返回時，數(shù)據(jù)至少存在兩個位置。在半同步 Master 收到所需數(shù)量的 Slave 的確認之前，該事務處于暫掛狀態(tài)且未提交。 與完全同步復制相比，半同步復制更快，因為半同步復制可以配置為平衡對數(shù)據(jù)完整性（確認已收到事務的 Slave 數(shù)）與提交速度的需求，提交速度較慢，因為需要等待 Slave。 與異步復制相比，半同步復制對性能的影響是增加數(shù)據(jù)完整性的權衡。減慢量至少是將提交發(fā)送到 Slave 并等待 Slave 確認接收的 TCP / IP 往返時間。這意味著半同步復制最適合通過快速網(wǎng)絡通信的關閉服務器，而最不適合通過慢速網(wǎng)絡通信的遠程服務器。半同步復制還通過限制二進制日志事件從 Master 發(fā)送到 Slave 的速度，對繁忙的會話設置了速率限制。當一個用戶太忙時，這會減慢速度，這在某些部署情況下很有用。 Master 及其 Slave 之間的半同步復制操作如下： Slave 表示連接到 Master 時是否具有半同步功能。 如果在 Master 端啟用了半同步復制，并且至少有一個半同步 Slave，則在 Master 塊上執(zhí)行事務提交的線程將等待直到至少一個半同步 Slave 確認已接收到該事務的所有事件，或者直到發(fā)生超時。 僅在事件已被寫入其中繼日志并刷新到磁盤之后，Slave 才確認接收到事務事件。 如果在沒有任何 Slave 確認事務的情況下發(fā)生超時，則 Master 將恢復為異步復制。趕上至少一個半同步 Slave 時，Master 將返回到半同步復制。 必須在 Master 端和 Slave 端都啟用半同步復制。如果在 Master 上禁用了半同步復制，或者在 Master 上啟用了半同步復制但沒有任何 Slave，則 Master 使用異步復制。 ③延遲復制 MySQL 8.0 還支持延遲復制，以使副本故意在源之后至少指定的時間量 39. InnoDB 內(nèi)存結構包含四大核心組件
](mp.weixin.qq.com/s?__biz=MjM…)
](mp.weixin.qq.com/s?__biz=MjM…)
](mp.weixin.qq.com/s?__biz=MjM…) 40. 索引有哪些優(yōu)缺點？優(yōu)點
缺點
41. 索引有哪幾種類型？
42. 創(chuàng)建索引的三種方式在執(zhí)行 CREATE TABLE 時創(chuàng)建索引CREATE TABLE `employee` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, `date` datetime DEFAULT NULL, `sex` int(1) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 復制代碼 使用 ALTER TABLE 命令添加索引ALTER TABLE table_name ADD INDEX index_name (column); 復制代碼 使用 CREATE INDEX 命令創(chuàng)建CREATE INDEX index_name ON table_name (column); 復制代碼 43. 百萬級別或以上的數(shù)據(jù)，你是如何刪除的？
44. 覆蓋索引、回表等這些，了解過嗎？
45. B + 樹在滿足聚簇索引和覆蓋索引的時候不需要回表查詢數(shù)據(jù)？
46. 何時使用聚簇索引與非聚簇索引 47. 非聚簇索引一定會回表查詢嗎？不一定，如果查詢語句的字段全部命中了索引，那么就不必再進行回表查詢（哈哈，覆蓋索引就是這么回事）。 舉個簡單的例子，假設我們在學生表的上建立了索引，那么當進行 select age from student where age < 20 的查詢時，在索引的葉子節(jié)點上，已經(jīng)包含了 age 信息，不會再次進行回表查詢。 48. 組合索引是什么？為什么需要注意組合索引中的順序？組合索引，用戶可以在多個列上建立索引，這種索引叫做組合索引。 因為 InnoDB 引擎中的索引策略的最左原則，所以需要注意組合索引中的順序。 49. 什么是死鎖？怎么解決？死鎖是指兩個或多個事務在同一資源上相互占用，并請求鎖定對方的資源，從而導致惡性循環(huán)的現(xiàn)象?？磮D形象一點，如下：  死鎖有四個必要條件：互斥條件，請求和保持條件，環(huán)路等待條件，不剝奪條件。 解決死鎖思路，一般就是切斷環(huán)路，盡量避免并發(fā)形成環(huán)路。
50. 你是如何監(jiān)控你們的數(shù)據(jù)庫的？你們的慢日志都是怎么查詢的？監(jiān)控的工具有很多，例如 zabbix，lepus，我這里用的是 lepus 作者：Java 程序魚 本作品采用《CC 協(xié)議》，轉載必須注明作者和本文鏈接 |
|
|
