本文為mysql11-15講總結

如何加索引

MySQL是支持前綴索引的，也就是說，你可以定義字符串的一部分作為索引。默認地，如果你創建索引的語句不指定前綴長度，那么索引就會包含整個字符串。

mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));

第一個語句代表著以整個字符串作為索引，第二個則是只取前6個字節

第二種由于只取前6個字節所以占用空間更少，但是會增加額外的記錄掃描次數

如果使用的是index2（即email(6)索引結構），執行順序是這樣的：

1. 從index2索引樹找到滿足索引值是’zhangs’的記錄，找到的第一個是ID1；

2. 到主鍵上查到主鍵值是ID1的行，判斷出email的值不是’zhangssxyz@xxx.com，這行記錄丟棄；

3. 取index2上剛剛查到的位置的下一條記錄，發現仍然是’zhangs’，取出ID2，再到ID索引上取整行然后判斷，這次值對了，將這行記錄加入結果集；

4. 重復上一步，直到在index2上取到的值不是’zhangs’時，循環結束。

使用前綴索引時要定義好長度才能做到節省空間的同時盡可能減少增加的額外查詢成本

首先，你可以使用下面這個語句，算出這個列上有多少個不同的值：

mysql> select count(distinct email) as L from SUser;

然后，依次選取不同長度的前綴來看這個值，比如我們要看一下4~7個字節的前綴索引，可以用這個語句：

mysql> selectcount(distinct left(email,4)）as L4,count(distinct left(email,5)）as L5,count(distinct left(email,6)）as L6,count(distinct left(email,7)）as L7,
from SUser;

當然，使用前綴索引很可能會損失區分度，所以你需要預先設定一個可以接受的損失比例，比如5%。然后，在返回的L4~L7中，找出不小于 L * 95%的值，假設這里L6、L7都滿足，你就可以選擇前綴長度為6。

使用前綴索引比不上覆蓋索引對查詢性能的優化，就算將前綴索引的長度設置為字段長度，innodb也是會回到索引再查一遍

如何給身份證號添加索引

1. 很多網頁比如說校園卡，會跟你說默認密碼為身份證后六位，所以我們也可以把身份證倒過來存儲，取六位為前綴索引
2. 使用hash字段：在表上創建一個整數字段，保存身份證的校驗碼，同時創建索引

mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);

然后每次插入新記錄的時候，都同時用crc32()這個函數得到校驗碼填到這個新字段。由于校驗碼可能存在沖突，也就是說兩個不同的身份證號通過crc32()函數得到的結果可能是相同的，所以你的查詢語句where部分要判斷id_card的值是否精確相同。

mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

這樣，索引的長度變成了4個字節，比原來小了很多。

接下來，我們再一起看看 使用倒序存儲和使用hash字段這兩種方法的異同點。

首先，它們的相同點是，都不支持范圍查詢。倒序存儲的字段上創建的索引是按照倒序字符串的方式排序的，已經沒有辦法利用索引方式查出身份證號碼在[ID_X, ID_Y]的所有市民了。同樣地，hash字段的方式也只能支持等值查詢。

它們的區別，主要體現在以下三個方面：

1. 從占用的額外空間來看，倒序存儲方式在主鍵索引上，不會消耗額外的存儲空間，而hash字段方法需要增加一個字段。當然，倒序存儲方式使用4個字節的前綴長度應該是不夠的，如果再長一點，這個消耗跟額外這個hash字段也差不多抵消了。

2. 在CPU消耗方面，倒序方式每次寫和讀的時候，都需要額外調用一次reverse函數，而hash字段的方式需要額外調用一次crc32()函數。如果只從這兩個函數的計算復雜度來看的話，reverse函數額外消耗的CPU資源會更小些。

3. 從查詢效率上看，使用hash字段方式的查詢性能相對更穩定一些。因為crc32算出來的值雖然有沖突的概率，但是概率非常小，可以認為每次查詢的平均掃描行數接近1。而倒序存儲方式畢竟還是用的前綴索引的方式，也就是說還是會增加掃描行數。

SQL語句變慢

臟頁 (Dirty Pages)

• 定義：內存中已被修改但尚未寫回磁盤的數據頁。
• 重要性：允許數據庫延遲磁盤寫操作，提高性能。
• 風險：系統崩潰時可能丟失未寫回的數據。

干凈頁 (Clean Pages)

• 定義：內存中的數據頁內容與磁盤上的內容完全一致。
• 優點：可以隨時從內存中移除而不影響數據完整性。
• 場景：系統無需擔心數據丟失即可釋放這些數據頁。

為何區分臟頁和干凈頁

1. 性能優化：允許數據頁在內存中保持臟狀態可以顯著提高數據庫的性能。這是因為磁盤I/O操作通常比內存操作要慢得多。通過減少必須執行的磁盤寫操作的次數，系統的整體響應時間和吞吐量可以得到提升。
2. 事務的一致性和恢復：在發生系統崩潰或其他故障時，臟頁的存在是關鍵的，因為它們包含了重要的未持久化的數據。數據庫恢復機制（如日志重做）依賴于這些臟頁的信息來確保數據的一致性和完整性。
3. 緩沖管理：數據庫系統通常包含一個緩沖池管理器，它負責決定何時讀取或寫入磁盤數據頁。通過智能地管理臟頁和干凈頁，緩沖池管理器可以優化數據訪問和寫回策略，例如使用LRU（最近最少使用）算法來決定哪些頁應當被保留在緩存中，哪些應當被寫回磁盤。

處理臟頁

數據庫通常通過一種稱為“檢查點”（checkpoint）的機制定期將所有臟頁寫回磁盤。這不僅有助于減少系統崩潰后恢復所需的時間，還可以確保數據的持久性和一致性。

總之，臟頁和干凈頁的概念是數據庫緩存管理的核心部分，對于理解和優化數據庫的性能和可靠性至關重要。

管理策略

• 檢查點：定期將所有臟頁數據同步到磁盤，幫助減少數據恢復時間，確保數據一致性。
• 緩沖池管理：有效管理臟頁和干凈頁，優化數據訪問和持久化策略。

flush

平時執行很快的更新操作，其實就是在寫內存和日志，而MySQL偶爾“抖”一下的那個瞬間，可能就是在刷臟頁（flush）。

flush：將臟頁寫入磁盤，確保持久性和一致性

引發flush的情況：

1. 檢查點 (Checkpoint)：數據庫定期執行檢查點操作，這是一個系統過程，旨在將所有當前的臟頁寫入磁盤。這樣做可以在系統崩潰后加快恢復速度，因為只需重新處理從最后一個檢查點之后的事務日志。
2. 事務提交：當事務提交時，為保證數據的持久性，會觸發臟頁的寫入。這確保了一旦事務被標記為成功，它的更改就永久保存了。
3. 臟頁的限制：如果臟頁的數量達到了數據庫系統設定的閾值，系統可能會自動觸發flush操作，以防內存中積累過多未寫入磁盤的更改。
4. 內存壓力：當系統內存不足時，數據庫可能需要清空緩沖池中的頁，以為新的數據頁騰出空間。這種情況下，臟頁需要先被flush到磁盤，才能被清出內存。
5. 系統關閉：在數據庫正常關閉過程中，為了確保所有暫存于內存中的更改都被持久化，會進行一次全面的flush操作。
6. 手動觸發：數據庫管理員可以手動觸發flush操作，例如通過特定的SQL命令或數據庫管理工具，來管理數據庫的物理存儲情況。

如何控制

需要明確告訴innodb所在主機的io能力：

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

關注臟頁比例，不要讓他經常接近75%，臟頁比例則是通過Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的，具體的命令參考下面的代碼：

select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

innodb_flush_neighbors在MySQL8.0中已經默認為0，這樣可以在如果鄰數據頁同為臟頁的情況下不被刷掉，也可以減少sql語句的響應時間

為什么刪除表數據后表文件大小不變

問題背景

在MySQL數據庫中，尤其是使用InnoDB存儲引擎的情況下，用戶經常遇到刪除表中大量數據后，表文件的物理大小沒有相應減小的問題。

核心原因

數據存儲方式

• InnoDB存儲結構： InnoDB使用B+樹索引結構存儲數據，數據按頁（通常大小為16KB）組織。
• 刪除操作的實際效果： 在InnoDB中，刪除操作僅標記數據行為刪除狀態，實際數據并未從磁盤中移除。這意味著物理空間不會立即釋放，而是留待將來重用。

參數影響

• innodb_file_per_table:
  • 設置為OFF: 所有表數據存放在共享表空間（ibdata1文件）中，刪除數據后空間不會回收到操作系統，而是留在共享空間中待后續使用。
  • 設置為ON: 每個表數據存儲在單獨的.ibd文件中。雖然便于管理，但刪除表內數據或整表后，空間仍然不會自動縮減。

解決方案

1. 調整innodb_file_per_table設置

推薦將innodb_file_per_table設置為ON，以便每個表使用獨立的文件，便于管理和優化。

2. 使用表重建來回收空間

• 重建單個表：

  ALTER TABLE your_table_name ENGINE=InnoDB;
  

  這個命令會重建表，期間移除了所有標記為刪除的記錄的空間，壓縮表中未使用的空間。

• 創建新表并復制數據：

  CREATE TABLE new_table LIKE original_table;
  INSERT INTO new_table SELECT * FROM original_table;
  DROP TABLE original_table;
  RENAME TABLE new_table TO original_table;
  

  這種方法通過完全重建數據來優化存儲布局和頁的利用率。

3. 定期優化表

對于頻繁更新的表，定期執行表優化可以提高性能和空間利用率。

OPTIMIZE TABLE your_table_name;

注意事項

• 操作風險： 重建表或優化表是資源密集型操作，可能會導致服務性能下降，特別是在高峰期進行這些操作時。建議在低峰時段進行，并確保有完整的備份。
• 數據安全： 在進行任何結構修改前，確保備份所有重要數據，防止操作失誤導致數據丟失。

online和inplace

Inplace DDL

• 定義： Inplace操作指的是在進行表結構變更時，數據不需要被移動到外部結構，而是在原有的表結構上直接進行修改。
• 實現細節： 在InnoDB中，例如執行 ALTER TABLE 操作時，可能會創建一個臨時文件（tmp_file），這個文件用于處理數據重組過程。盡管操作在物理上看起來像是在原地進行（即不需要額外的臨時表），但實際上仍然需要額外的磁盤空間來存儲臨時數據。
• 空間要求： 如果服務器的可用磁盤空間不足以創建所需的臨時文件，即使是inplace操作也無法完成。

Online DDL

• 定義： Online DDL 允許在表結構變更過程中，表繼續對外提供服務，即允許對表進行讀寫操作。

• 關系與區別： 所有Online DDL操作均是Inplace的，但并非所有Inplace操作都是Online的。例如，添加全文索引的過程雖然是inplace的，但它會阻塞表的寫操作，因此不是Online的。

• Inplace but not Online: 例如，添加全文索引（FULLTEXT）或空間索引（SPATIAL），這些操作雖然在物理上不需要移動數據到新表，但會阻塞表的某些操作。

• Online and Inplace: 某些表結構變更如增加新列或者修改某些不影響表數據排列的屬性，可以在線進行，同時在物理存儲上也是在原地完成。

以下是對您提供的文檔內容的總結和筆記：

COUNT(*) 討論

• 問題討論：為什么在 MySQL 中使用 SELECT COUNT(*) FROM t 查詢表行數會隨著數據增多而變慢。
• 焦點：分析 COUNT(*) 的執行機制，特別是在不同的存儲引擎（MyISAM 和 InnoDB）中的表現，并探討應對策略。

COUNT(*) 的實現方式

• MyISAM：存儲行數在磁盤上，使得 COUNT(*) 很快，因為直接返回已存儲的數值。
• InnoDB：需要逐行讀取數據并判斷行的可見性（MVCC），因此隨著數據量的增加，COUNT(*) 的性能會顯著下降。

InnoDB 的設計和挑戰

• 多版本并發控制（MVCC）：InnoDB 使用 MVCC 支持高并發，但這使得即使是簡單的 COUNT(*) 查詢也需要逐行檢視，以確定每行是否對查詢事務可見。
• 索引優化：雖然 InnoDB 會嘗試通過遍歷最小的索引樹來優化 COUNT(*) 查詢，但這仍可能涉及大量數據處理。

解決策略

1. 緩存系統（如 Redis）:
   • 優點：快速讀寫。
   • 缺點：可能丟失更新，需要定期從數據庫同步來確保準確性。
2. 定期同步:
   • 方法：定期從數據庫執行 COUNT(*) 并更新緩存。
   • 考量：平衡更新頻率和性能需求。

建議

• 對于需要頻繁訪問數據行數的應用，推薦使用緩存系統維護計數，并結合定期同步策略以防數據丟失。
• 需要權衡實時性和性能，選擇適合自己業務場景的數據同步頻率。

總結

• 雖然 COUNT(*) 在 MyISAM 中非常快速，但不支持事務和并發控制，而 InnoDB 雖然支持高級功能，卻因為其數據一致性和并發控制設計，使得 COUNT(*) 變得較慢。
• 在面對實際業務需求時，特別是在數據量大且更新頻繁的情況下，推薦采用外部緩存系統來處理計數，以提高性能。