Reference

MySQL實戰45講

為什麼 MySQL 偶爾會選錯索引

原因： 選擇索引是最佳化器的工作， 最佳化器會結合預計掃描行數， 是否使用臨時表， 是否排序因素進行綜合判斷

解決： 掃描行數不準確可以使用

analyze table

解決， 可以使用

force index

強制使用指定索引， 或者修改 SQL 語句， 或者刪除影響最佳化器判斷的索引。

怎麼給字串加索引

字串相比 int， 索引佔用空間巨大， 影響 io 效能， 可以使用字首索引， 比如欄位 name 儲存了 abc111， abc222， abc333 這類字串， 建立 name（3） 的字首索引

計算字首索引長度

select count（distinct name） as L from User；

為什麼 MySQL 會"抖"

原因： “抖” 意味著MySQL 後臺在刷髒頁。

MySQL 更新資料採用 WAL 技術（記錄redo log， 再非同步寫入磁碟）， 將隨機寫轉化為順序寫， 提高系統性能， 代價就是會產生資料髒頁， 髒頁想變成乾淨頁就需要在某個時候（mysql 空閒或髒頁無被填滿） flush 進磁碟， 這時候會佔用系統資源影響正常的 SQL 執行， 稱之為 “抖”

解決： 正確設定

innodb_io_capacity

引數告訴 InnoDB 磁碟的 io 能力。 關注

innodb_io_capacity

引數髒頁比例不能超過 75%

磁碟的 IOPS 可以透過 fio 這個工具來測試

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

count(*) count(1) count(id) count(列) 原理

MyISAM 維護了總行數字段， 可以直接獲取， 但是帶 where 條件就不好使了

InnoDB 需要遍歷每一行， count（

） 有最佳化過， 會分析佔用空間比較小的索引來執行遍歷， 因為一次 io 可以獲取更多資料 count（1） count（id） count（列） 會多一次比對 null 值再統計， 理論上 count（

） 最優

為什麼刪除資料表大小不變

原因： delete 命令只會將資料標記為可複用，並不會刪除物理空間，但是插入資料也不會使用這些可複用的空間， 除非顯示指定 id = 你刪除的 id 才能使用， 所以會造成資料空洞， 影響 mysql 效能

解決： 重建表

不走索引的情況

索引上使用函式

SELECT `sname` FROM `stu` WHERE concat（`sname`，‘abc’） =‘Jaskeyabc’；

索引列參與計算

SELECT `sname` FROM `t_stu` WHERE `age`+10=30；

模糊查詢 Like %xxx

SELECT * FROM `houdunwang` WHERE `uname` LIKE ‘%字尾’

隱式型別轉換

SELECT * FROM `t2` WHERE `a`=1； ### a 是 varchar

幻讀

產生： 思考下面兩個 mysql 連線 m1 和 m2

1 m1： BEGIN；

2 m1：SELECT * FROM test WHERE id = 6； ### 結果是空

3 m2： INSERT INTO test（id， k） VALUES （6， 6） ### 返回插入成功一行

4 m1：INSERT INTO test（id， k） VALUES （6， 6）； ### 返回主鍵重複

如果是按照 InnDB 行鎖的機制， 第 4 步是會插入衝突的，因為 2，3 兩步提前插入了相同的資料， 對 T1 事務來說， 先查詢再插入， 再符合邏輯不過了， 結果居然失敗了， 所以產生了幻讀（幻行）

解決： 可以使用 InnoDB 的間隙鎖， 或者給 第二步 m1 的查詢語句加一個排他鎖

SELECT * FROM test WHERE id = 6 FOR UPDATE

特別注意一點： 加鎖， 只會加在索引上， 這也是為什麼， 資料庫不存在的記錄也可以加上鎖， 因為鎖，不對應任何實際資料。

MySQL 如何保證資料不丟失

眾所周知， MYSQL 中每條語句都是以一次事務來執行的

當資料寫入或更新時， 先寫 redo log buffer， redo log 準備好之後， 再寫 binlog cache， 這些都是在記憶體中執行的， redo log buffer 和 bin log cache 寫入完畢之後， flush 到各自的 redo log file 和 bin log file， 這兩個檔案由於是順序寫， 所以雖然是持久化的， 但是也很快， 當 file 也都寫入完畢之後， 事務就可以提交了， 如果意外 carsh， 這兩個檔案也能幫助 mysql 恢復到事務提交後的樣子。

讀寫分離資料延遲問題

原因： binlog 備份是需要時間的， 雖然很快， 假如遇到長事務或者從庫查詢壓力過大影響了同步執行緒， 都會產生從庫資料延遲問題

解決： 1。 強制查主庫 （low） 2。 讀之前 sleep 一下 （low） 3。 從庫查之前先判斷

seconds_behind_master

是否等於 0 4。

Master_Log_File

和

Relay_Master_Log_File

、

Read_Master_Log_Pos

和

Exec_Master_Log_Pos

這兩組值完全相同，就表示接收到的日誌已經同步完成 5。 對比 GTID 集合確保主備無延遲

MySQL查詢出來資料是放在記憶體中一下返回客戶端的嗎?

是邊讀邊發的。

假如 Table 表有 100G 資料， 執行一個全表掃描

select * from Table

1。 獲取一行， 寫到

net_buffer

中， 這個 buffer 預設 16k 2。 重複獲取行， 直到

net_buffer

被寫滿， 呼叫網路介面發出 3。 傳送成功清空

net_buffer

， 繼續取下一行

能不能用 Join

不推薦用 Join， 一是 Join 會建立臨時表， 二是資料量變大後， 效能急劇下降， 三是系統分庫分表後， 所有的 Join 得重寫

業務查詢需要用到 Join ，完全可以用 where in 語句實現 如果使用的話， 讓小表做驅動表， Join 語句儘量使用到索引欄位

能不能用 分割槽表

儘量選擇手動分表， 如果有比較成熟的分庫分表中介軟體或者業務比較容易實現的話。 mysql 分割槽表優點是對業務透明， 但是 Mysql 在第一次開啟分割槽表時，需要訪問所有分割槽， 在 mysql server 層， 所有分割槽公用同一個 MDL 鎖

自增主鍵到達上限了會發生什麼

自增值達到上限後的邏輯是：再申請下一個 id 時，得到的值保持不變， 然後插入失敗

預設自增 id 是一個無符號4 位元組的整數， 那麼最大值是 2^32 -1 = 42 億左右， 一般是夠用了， 但是有極少數可能還是不夠用， 那麼可以定義無符號的 bigint，這個欄位可以存放 8 位元組， 妥妥夠用了

建表沒有建立主鍵會發生什麼

InnoDB 會自動建立一個不可見的 row_id 當自增主鍵，但是這個主鍵如果到達上限的話， 不會保持不變， 下一個值會變成 0（好像是無符號整形溢位？）， 從而覆蓋掉以前的資料

二階段提交時發生異常資料會一致嗎

1。 寫入 redolog 處於 prepare 階段， 寫 binlog 時異常， 此時事務還未提交， 理論上這條 update 語句應該不生效， 由於 binlog 沒有記錄， 此時 binlog 是 ok 的， redolog 已經記錄了，但是 redolog 沒有 commit， 所以會回滾。 2。 redolog 處於 prepare， binlog 寫完未提交， 此時異常， 重啟後會判斷 這個 update 事務的 binlog 和 redolog 是否一致， 一致則在恢復過程中提交這次事務。 不一致則回滾這次事務

redolog 和 binlog 如何關聯起來的

他們有一個共同的資料欄位 XID， 崩潰恢復時 1。 redolog 有 prepare 和 commit 標誌， 則直接提交事務 2。 redolog 只有 prepare， 沒有 commit， 就拿 XID 去 binlog 找對應的事務

單獨的 binlog 為什麼不能做異常恢復

歷史原因， InnoDB 在加入 MySQL 前就已經有 redolog， 而且 MySQL binlog 不具備崩潰恢復能力， 所以 InnoDB 用的就是原有的 redolog

實現上， InnoDB 使用 WAL 技術提升讀寫效能， 執行事務的時候寫完記憶體和日誌就算這次事務成功， binlog 無法修復日誌中以前記錄的 commit， 只會恢復最近一條 commit， 如果將 binlog 機制最佳化， 去判斷前幾頁的資料 commit 情況進行恢復， 這樣等於重新做了一個 redolog 出來。