MySQL存儲引擎以及索引

文章目錄

• ​​一、資料庫引擎​​

• ​​1. 檢視資料庫引擎​​
• ​​2. 檢視表結構​​
• ​​3. 檢視表相關檔案​​
• ​​4. 各存儲引擎的差別​​

• ​​二、MySQL索引​​

• ​​1. 索引分類​​
• ​​2. 索引的建立和删除​​
• ​​3. 關于緩存問題​​
• ​​4. 過濾條件字段涉及類型轉換則無法使用索引​​
• ​​5. 删除索引​​
• ​​6. explain字段含義​​
• ​​7. 加索引優化原則​​

一、資料庫引擎

1. 檢視資料庫引擎

​

​show engines​

​

2. 檢視表結構

​

​show create table [student]​

​​：檢視表結構，其中表使用的資料庫引擎和字元集等可以在配置檔案中修改。windows下的配置檔案為安裝目錄下的​

​my.ini​

​​，linux則在​

​/etc/mysql/my.cnf​

​

3. 檢視表相關檔案

• 使用MyISAM存儲引擎的表對應的檔案有三個：​

  ​*.frm​

  ​​，​

  ​*.MYD​

  ​​，​

  ​*.MYI​

  ​，分别表示表結構、表資料、表索引
• 使用InnoDB存儲引擎的表對應的檔案有兩個：​

  ​*.frm​

  ​​，​

  ​*.ibd​

  ​，分别表示表結構、表資料和表索引，資料和索引放在一個檔案中

面試問題：為什麼使用InnoDB存儲引擎的表會自動生成主鍵，而使用MyISAM存儲引擎的表不會自動生成主鍵？

因為MyISAM的資料和索引是單獨存放的，手動加上主鍵會生成主鍵索引存放在​

​*.MYI​

​​，沒有主鍵的話​

​*.MYI​

​裡就不用存放索引。而InnoDB會預設生成一個整型類型的索引，因為Innodb的資料和索引放在一個檔案中，資料就是放在索引樹上的，沒有索引，資料也沒有地方存放。

4. 各存儲引擎的差別

種類	鎖機制	B樹索引	哈希索引	外鍵	事務	索引緩存	資料緩存
MyISAM	表鎖	支援	不支援	不支援	不支援	支援	不支援
InnoDB	行鎖	支援	不支援	支援	支援	支援	支援
Memory	表鎖	支援	支援	不支援	不支援	支援	支援

• 鎖機制：表示資料庫在并發請求通路的時候，多個事務在操作時，并發操作的力度
• B樹索引和哈希索引：主要是加速SQL的查詢速度
• 外鍵：子表的字段依賴父表的主鍵，設定兩張表的依賴關系
• 事務：多個SQL語句，保證它們共同執行的原子操作，要麼成功要麼失敗，不能隻成功一部分，失敗需要復原事務
• 索引緩存和資料緩存：和MySQL Server的查詢緩存相關，在沒有對資料和索引做修改之前，重複查詢可以不用進行磁盤I/O（資料庫的性能提升，目的是減少磁盤I/O提升通路效率），讀取上一次記憶體中查詢的緩存就可以了

二、MySQL索引

當表中的資料量達到上百萬的時候，SQL查詢花費的時間會很長，需要使用索引加速SQL查詢

由于索引也是需要存儲成索引檔案的，是以使用索引也會涉及磁盤I/O操作。如果索引過多，使用不當，SQL查詢時會造成大量無用的磁盤I/O操作，降低查詢效率。

此外，我們改動資料以後，不僅是資料檔案需要做修改，索引檔案也需要所修改，索引過多，修改的索引也會更多，是以索引并不是越多越好。

1. 索引分類

索引是建立在表上的，是對資料庫表中一列或多列的值進行排序的一種結果，其核心就是提高查詢的速度

• 實體上分為：聚集索引、非聚集索引
• 邏輯上分為：

1. 普通索引：沒有任何限制條件，可以給任何字段建立普通索引(一張表的一次SQL查詢隻能使用一個索引，比如​

   ​where age=1 and sex="man"​

   ​隻能使用一個索引)
2. 唯一性索引：使用​

   ​unique​

   ​修飾的字段，值不能重複，主鍵索引就是一種唯一性索引
3. 主鍵索引：使用​

   ​PRIMARY KEY​

   ​修飾的索引
4. 單列索引：在一個字段上建立索引
5. 多列索引：在表的多個字段上建立索引 (uid+cid，age+name等，先按第一個字段排序，再按第二個字段排序)，多列索引必須使用到第一個列，才能用到多列索引，否則索引用不上
6. 全文索引：使用​

   ​FULLTEXT​

   ​​參數可以設定全文索引，隻支援​

   ​CHAR​

   ​​，​

   ​VARCHAR​

   ​​和​

   ​TEXT​

   ​類型的字段上，常用于資料量較大的字元串類型上

• 索引的優點：提高查詢效率
• 索引的缺點：索引并不是越多越好，過多的索引會導緻CPU使用率居高不下，資料的改變也會造成索引檔案的改變，過多的磁盤I/O造成CPU負載太重

2. 索引的建立和删除

建立表的時候指定索引字段：

CREATE TABLE table1(id INT, 
  name VARCHAR(20), 
  sex ENUM('male', 'female'), 
  INDEX(id,name));      

在已經建立的表上添加索引：

CREATE [UNIQUE] INDEX 索引名 ON 表名（屬性名（length） [ASC | DESC]);      

删除索引：

DROP INDEX 索引名 ON 表名;      

此時表結構如下：

show create table      

使用具有主鍵索引的id進行過濾查找：

explain select * from student where uid=3;      

使用沒有索引的name屬性掃描

explain select * from student where name="zhangsan";      

給name添加索引

create index nameidx on student(name);      

用name索引加速搜尋

• type為ref，表示在掃描索引樹
• key_len這裡是152，對于給字元串類型資料建立索引的時候，一般會限制索引長度。若前面一部分字元區可以用于區分不同的資料，沒必要使用很長長度的索引（key_len很大）。因為索引長了，索引檔案會變大，就會使用更多的磁盤IO，應盡量避免

然而添加索引後，不一定就能使用到索引，因為MySQL server有優化，它會先進行分析，如果發現使用索引需要掃描的資料基本上是所有資料的大概百分之七八十左右，其實是不會使用索引的，因為如果花費差不多，讀索引檔案花費磁盤I/O，還要掃描索引樹，資料取不完，還要到整個資料表上取資料，還不如直接整張表搜尋取資料。

3. 關于緩存問題

對于相同的操作，若中間沒有更新資料（insert/delete/update），則第一次花費時間長，第二次花費時間短，這是因為存儲引擎對索引和資料進行了緩存。 第一次查詢後的結果會放在資料緩存或者索引緩存裡，第二次就不用花費磁盤I/O從磁盤讀取索引了。

4. 過濾條件字段涉及類型轉換則無法使用索引

檢視表結構後發現，password屬性是​

​varchar​

​​，然而查詢的時候使用的是​

​int​

​，這就涉及到了類型轉換，是以不會使用索引。

5. 删除索引

drop index pwdidx on t_user;      

6. explain字段含義

• select_type



• table



• type



• ref
• Extra

7. 加索引優化原則

• 若經常作為過濾條件（where）的屬性，需要加上索引
• 給字元串屬性添加索引的時候，需要限制索引的長度（key_len）
• 索引字段涉及類型強轉、mysql聚合函數調用、表達式計算等，不會使用索引