（3）MySQL進階篇SQL優化（索引）

1.索引問題

索引是資料庫優化中最常用也是最重要的手段之一，通過索引通常可以幫助用戶解決大多數 的SQL性能問題，本章節將對MySQL中的索引的分類、存盤、使用方法做詳細的介紹，

2.索引的存盤分類

MyISAM存盤引擎的表資料和索引是自動分開存盤的，各自是獨立的一個檔案；InnoDB存盤引擎的表資料和索引是存盤在同一個表空間里面，但可以有多個檔案組成，MySQL中索引的存盤型別目前只有兩種（BTREE和HASH），具體和表的存盤引擎相關：MyISAM和InnoDB存盤引擎都只支持BTREE索引；MEMORY/HEAP存盤引擎可以支持HASH和BTREE索引，MySQL目前不支持函式索引，但是能對列的前面某一部分進索引，例如上章節庫存表goods_stock.LotNO批次欄位，可以只取Model的前4個字符進行索引，這個特性可以大大縮小索引檔案的大小，我們在設計表結構的時候也可以對文本列根據此特性進行靈活設計，下面是創建前綴索引的一個例子：

EXPLAIN SELECT * FROM goods_stock WHERE LotNO LIKE '2021%';

-- 創建前綴索引
CREATE INDEX idx_stock_2 ON goods_stock (LotNO(4));

3.如何使用索引

索參考于快速找出在某個列中有一特定值的行，對相關列使用索引是提高SELECT操作性能的最佳途徑，查詢要使用索引最主要的條件是查詢條件中需要使用索引關鍵字，如果是多列索引，那么只有查詢條件使用了多列關鍵字最左邊的前綴時，才可以使用索引，否則將不能使用索引，

3.1使用索引

在MySQL中，下列幾種情況下有可能使用到索引，
●對于創建的多列索引，只要查詢的條件中用到了最左邊的列，索引一般就會被使用， 舉例說明如下：

-- 首先在庫存表goods_stock按Model，Brand的順序創建一個復合索引
CREATE INDEX idx_stock_1 ON goods_stock (Model,Brand);

然后按Model進行表查詢，具體命令如下：

EXPLAIN SELECT * FROM goods_stock WHERE Model='LM358DT';

可以發現即便where條件中不是用Model與Brand欄位的組合條件，索引仍然能用到，這就是索引的前綴特性（按照索引列順序查詢），但是如果只按Brand條件查詢表，那么索引就不會被用到，具體如下：

EXPLAIN SELECT * FROM goods_stock WHERE Brand='TI';

●對于使用like的查詢，后面如果是常量并且只有％號不在第一個字符，索引才可能會被使用，來看下面兩個執行計劃：

EXPLAIN SELECT * FROM goods_stock WHERE Model LIKE '%LM358';

EXPLAIN SELECT * FROM goods_stock WHERE Model LIKE 'LM358%';

可以發現第一個SQL沒有使用索引，而第二個SQL就能夠使用索引，區別就在于“%”的位置不同，前者把“%”放到第一位就不能用到索引，而后者沒有放到第一位就使用了索引，另外，如果如果like后面跟的是一個列的名字，那么索引也不會被使用，如果對大的文本進行搜索，使用全文索引而不要使用like ’%…%’，
●如果列名是索引，使用column_name is null時候將會使用索引，如下例中查詢LotNO為null的記錄時候就會用到索引：

EXPLAIN SELECT * FROM goods_stock WHERE LotNO IS NULL;

3.2存在索引但不使用索引

在下列情況下，雖然存在索引，但是MySQL并不會使用相應的索引，
●如果MySQL估計使用索引比全表掃描更慢，則不使用索引，例如如果列 key_part1 均勻分布在 1 和 100 之間，下列查詢中使用索引就不是很好：

SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;

●如果使用MEMORY/HEAP表并且where條件中不使用“=”進行索引列，那么不會用到索引，HEAP表只有在“=”的條件下才會使用索引，
●用or分割開的條件，如果or前的條件中的列有索引，而后面的列中沒有索引，那么涉及到的索引都不會被用到，例如：

SHOW INDEX FROM goods_stock;

通過命令可以看到goods_stock庫存表有兩個索引，然后我們再執行如下陳述句看是否使用索引：

EXPLAIN SELECT * FROM goods_stock WHERE LotNO='20200821' OR PackageUnit='包';

可見雖然在LotNO這個列上存在索引idx_stock_2，但是這個SQL陳述句并沒有用到這個索引，原因就是or中有一個條件中的列沒有索引，
●如果列型別是字串，那么一定記得在where條件中把字符常量值用引號引起來，否則即便這個列上有索引，MySQL也不會用到的，因為MySQL默認把輸入的常量值進行轉換以后才進行檢索，請看如下例子：

DESC goods_stock;

通過DESC命令我們可以看到goods_stock庫存表中的LotNO欄位是字符型，如果我們在SQL陳述句中的LotNO欄位加入一個數值型為20200821的條件值，因此即便在LotNO上有索引，MySQL也不能正確地用上索引，而是繼續進行全表掃描，具體如下：

EXPLAIN SELECT * FROM goods_stock WHERE LotNO=20200821;

4.查看索引使用情況

如果索引正在作業，Handler_read_key的值將很高，這個值代表了一個行被索引值讀的次數，很低的值表明增加索引得到的性能改善不高，因為索引并不經常使用，Handler_read_rnd_next的值高則意味著查詢運行低效，并且應該建立索引補救，這個值的含義是在資料檔案中讀下一行的請求數，如果正進行大量的表掃描，Handler_read_rnd_next的值較高，則通常說明表索引不正確或寫入的查詢沒有利用索引，可以先重繪狀態再查詢，具體如下：

FLUSH STATUS;
SHOW STATUS LIKE 'Handler_read%';

引數解釋如下：
Handler_read_first：此選項表明SQL是在做一個全索引掃描，注意是全部，而不是部分，所以說如果存在WHERE陳述句，這個選項是不會變的，
Handler_read_key：此選項數值如果很高，MySQL高效的使用了索引，一切運轉良好，
Handler_read_next：此選項表明在進行索引掃描時，按照索引從資料檔案里取資料的次數，
Handler_read_prev：此選項表明在進行索引掃描時，按照索引倒序從資料檔案里取資料的次數，一般就是ORDER BY … DESC，
Handler_read_rnd：就是查詢直接操作了資料檔案，很多時候表現為沒有使用索引或者檔案排序，
Handler_read_rnd_next：此選項值較高時候，則通常說明表索引不正確或寫入的查詢沒有利用索引，

5.兩個簡單實用的優化方法

對于大多數開發人員來說，可能只希望掌握一些簡單實用的優化方法，對于更多更復雜的優 化，更傾向于交給專業DBA來做，本小節將向大家介紹兩個簡單適用的優化方法，

5.1定期分析表和檢查表

●分析表的語法如下：

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

本陳述句用于分析和存盤表的關鍵字分布，分析的結果將可以使得系統得到準確的統計資訊，使得SQL能夠生成正確的執行計劃，如果用戶感覺實際執行計劃并不是預期的執行計劃，執行一次分析表可能會解決問題，在分析期間，使用一個讀取鎖定對表進行鎖定，這對于MyISAM, BDB和InnoDB表有作用，對于MyISAM表，本陳述句與使用myisamchk -a相當，下例中對goods_stock表做了表分析：

ANALYZE TABLE goods_stock;

●檢查表的語法如下：

CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED} 

檢查表的作用是檢查一個或多個表是否有錯誤，CHECK TABLE對MyISAM和InnoDB表有作用，對于MyISAM表，關鍵字統計資料被更新，例如：

CHECK TABLE goods_stock;

CHECK TABLE也可以檢查視圖是否有錯誤，比如在視圖定義中被參考的表已不存在，舉例如下：
（1）首先我們創建一個表，

CREATE TABLE test
(
  ID INT(11)
);

（2）再創建一個視圖，

CREATE VIEW test_view AS SELECT * FROM test;

（3）然后CHECK一下該視圖，發現沒有問題，

CHECK TABLE test_view;

（4）現在洗掉掉視圖依賴的表，

DROP TABLE test;

（5）再來CHECK一下剛才的視圖，發現報錯了，

CHECK TABLE test_view;

5.2定期優化表

優化表的語法如下：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

如果已經洗掉了表的一大部分，或者如果已經對含有可變長度行的表（含有VARCHAR、BLOB或TEXT列的表）進行了很多更改，則應使用OPTIMIZE TABLE 命令來進行表優化，這個命令可以將表中的空間碎片進行合并，并且可以消除由于洗掉或者更新造成的空間浪費，但OPTIMIZE TABLE命令只對MyISAM、BDB和InnoDB表起作用，以下例子顯示了優化goods_stock庫存表的程序：

-- 先查看下goods_stock庫存表是什么表型別
SHOW TABLE STATUS LIKE 'goods_stock%';

OPTIMIZE TABLE goods_stock;

注意：ANALYZE、CHECK、OPTIMIZE執行期間將對表進行鎖定，因此一定注意要在資料庫不繁忙的情況下執行相關的操作，

參考文獻：
深入淺出MySQL大全

標籤：MySQL

上一篇：教程：linux系統—mysql常用備份命令

下一篇：認識資料庫