1.索引問(wèn)題

索引是數(shù)據(jù)庫(kù)優(yōu)化中最常用也是最重要的手段之一，通過(guò)索引通?？梢詭椭脩?hù)解決大多數(shù) 的SQL性能問(wèn)題。本章節(jié)將對(duì)MySQL中的索引的分類(lèi)、存儲(chǔ)、使用方法做詳細(xì)的介紹。

2.索引的存儲(chǔ)分類(lèi)

MyISAM存儲(chǔ)引擎的表數(shù)據(jù)和索引是自動(dòng)分開(kāi)存儲(chǔ)的，各自是獨(dú)立的一個(gè)文件；InnoDB存儲(chǔ)引擎的表數(shù)據(jù)和索引是存儲(chǔ)在同一個(gè)表空間里面，但可以有多個(gè)文件組成。MySQL中索引的存儲(chǔ)類(lèi)型目前只有兩種（BTREE和HASH），具體和表的存儲(chǔ)引擎相關(guān)：MyISAM和InnoDB存儲(chǔ)引擎都只支持BTREE索引；MEMORY/HEAP存儲(chǔ)引擎可以支持HASH和BTREE索引。MySQL目前不支持函數(shù)索引，但是能對(duì)列的前面某一部分進(jìn)索引，例如上章節(jié)庫(kù)存表goods_stock.LotNO批次字段，可以只取Model的前4個(gè)字符進(jìn)行索引，這個(gè)特性可以大大縮小索引文件的大小，我們?cè)谠O(shè)計(jì)表結(jié)構(gòu)的時(shí)候也可以對(duì)文本列根據(jù)此特性進(jìn)行靈活設(shè)計(jì)。下面是創(chuàng)建前綴索引的一個(gè)例子：

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

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

3.如何使用索引

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

3.1使用索引

在MySQL中，下列幾種情況下有可能使用到索引。

對(duì)于創(chuàng)建的多列索引，只要查詢(xún)的條件中用到了最左邊的列，索引一般就會(huì)被使用， 舉例說(shuō)明如下：

-- 首先在庫(kù)存表goods_stock按Model，Brand的順序創(chuàng)建一個(gè)復(fù)合索引
CREATE INDEX idx_stock_1 ON goods_stock (Model,Brand);

然后按Model進(jìn)行表查詢(xún)，具體命令如下：

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

可以發(fā)現(xiàn)即便where條件中不是用Model與Brand字段的組合條件，索引仍然能用到，這就是索引的前綴特性（按照索引列順序查詢(xún)）。但是如果只按Brand條件查詢(xún)表，那么索引就不會(huì)被用到，具體如下：

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

對(duì)于使用like的查詢(xún)，后面如果是常量并且只有％號(hào)不在第一個(gè)字符，索引才可能會(huì)被使用，來(lái)看下面兩個(gè)執(zhí)行計(jì)劃：

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

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

可以發(fā)現(xiàn)第一個(gè)SQL沒(méi)有使用索引，而第二個(gè)SQL就能夠使用索引，區(qū)別就在于“%”的位置不同，前者把“%”放到第一位就不能用到索引，而后者沒(méi)有放到第一位就使用了索引。另外，如果如果like后面跟的是一個(gè)列的名字，那么索引也不會(huì)被使用。如果對(duì)大的文本進(jìn)行搜索，使用全文索引而不要使用like ‘%...%’。

如果列名是索引，使用column_name is null時(shí)候?qū)?huì)使用索引。如下例中查詢(xún)LotNO為null的記錄時(shí)候就會(huì)用到索引：

EXPLAIN SELECT * FROM goods_stock WHERE LotNO IS NULL;

3.2存在索引但不使用索引

在下列情況下，雖然存在索引，但是MySQL并不會(huì)使用相應(yīng)的索引。

如果MySQL估計(jì)使用索引比全表掃描更慢，則不使用索引。例如如果列 key_part1 均勻分布在 1 和 100 之間，下列查詢(xún)中使用索引就不是很好：

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

如果使用MEMORY/HEAP表并且where條件中不使用“=”進(jìn)行索引列，那么不會(huì)用到索引。HEAP表只有在“=”的條件下才會(huì)使用索引。
用or分割開(kāi)的條件，如果or前的條件中的列有索引，而后面的列中沒(méi)有索引，那么涉及到的索引都不會(huì)被用到，例如：

SHOW INDEX FROM goods_stock;

通過(guò)命令可以看到goods_stock庫(kù)存表有兩個(gè)索引，然后我們?cè)賵?zhí)行如下語(yǔ)句看是否使用索引：

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

可見(jiàn)雖然在LotNO這個(gè)列上存在索引idx_stock_2，但是這個(gè)SQL語(yǔ)句并沒(méi)有用到這個(gè)索引，原因就是or中有一個(gè)條件中的列沒(méi)有索引。

如果列類(lèi)型是字符串，那么一定記得在where條件中把字符常量值用引號(hào)引起來(lái)，否則即便這個(gè)列上有索引，MySQL也不會(huì)用到的，因?yàn)镸ySQL默認(rèn)把輸入的常量值進(jìn)行轉(zhuǎn)換以后才進(jìn)行檢索，請(qǐng)看如下例子：

DESC goods_stock;

通過(guò)DESC命令我們可以看到goods_stock庫(kù)存表中的LotNO字段是字符型，如果我們?cè)赟QL語(yǔ)句中的LotNO字段加入一個(gè)數(shù)值型為20200821的條件值，因此即便在LotNO上有索引，MySQL也不能正確地用上索引，而是繼續(xù)進(jìn)行全表掃描，具體如下：

EXPLAIN SELECT * FROM goods_stock WHERE LotNO=20200821;

4.查看索引使用情況

如果索引正在工作，Handler_read_key的值將很高，這個(gè)值代表了一個(gè)行被索引值讀的次數(shù)，很低的值表明增加索引得到的性能改善不高，因?yàn)樗饕⒉唤?jīng)常使用。Handler_read_rnd_next的值高則意味著查詢(xún)運(yùn)行低效，并且應(yīng)該建立索引補(bǔ)救。這個(gè)值的含義是在數(shù)據(jù)文件中讀下一行的請(qǐng)求數(shù)。如果正進(jìn)行大量的表掃描，Handler_read_rnd_next的值較高，則通常說(shuō)明表索引不正確或?qū)懭氲牟樵?xún)沒(méi)有利用索引。可以先刷新?tīng)顟B(tài)再查詢(xún)，具體如下：

FLUSH STATUS;
SHOW STATUS LIKE 'Handler_read%';

參數(shù)解釋如下：

• Handler_read_first：此選項(xiàng)表明SQL是在做一個(gè)全索引掃描，注意是全部，而不是部分，所以說(shuō)如果存在WHERE語(yǔ)句，這個(gè)選項(xiàng)是不會(huì)變的。
• Handler_read_key：此選項(xiàng)數(shù)值如果很高，MySQL高效的使用了索引，一切運(yùn)轉(zhuǎn)良好。
• Handler_read_next：此選項(xiàng)表明在進(jìn)行索引掃描時(shí)，按照索引從數(shù)據(jù)文件里取數(shù)據(jù)的次數(shù)。
• Handler_read_prev：此選項(xiàng)表明在進(jìn)行索引掃描時(shí)，按照索引倒序從數(shù)據(jù)文件里取數(shù)據(jù)的次數(shù)，一般就是ORDER BY … DESC。
• Handler_read_rnd：就是查詢(xún)直接操作了數(shù)據(jù)文件，很多時(shí)候表現(xiàn)為沒(méi)有使用索引或者文件排序。
• Handler_read_rnd_next：此選項(xiàng)值較高時(shí)候，則通常說(shuō)明表索引不正確或?qū)懭氲牟樵?xún)沒(méi)有利用索引。

5.兩個(gè)簡(jiǎn)單實(shí)用的優(yōu)化方法

對(duì)于大多數(shù)開(kāi)發(fā)人員來(lái)說(shuō)，可能只希望掌握一些簡(jiǎn)單實(shí)用的優(yōu)化方法，對(duì)于更多更復(fù)雜的優(yōu)化，更傾向于交給專(zhuān)業(yè)DBA來(lái)做。本小節(jié)將向大家介紹兩個(gè)簡(jiǎn)單適用的優(yōu)化方法。

5.1定期分析表和檢查表

分析表的語(yǔ)法如下：

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

本語(yǔ)句用于分析和存儲(chǔ)表的關(guān)鍵字分布，分析的結(jié)果將可以使得系統(tǒng)得到準(zhǔn)確的統(tǒng)計(jì)信息，使得SQL能夠生成正確的執(zhí)行計(jì)劃。如果用戶(hù)感覺(jué)實(shí)際執(zhí)行計(jì)劃并不是預(yù)期的執(zhí)行計(jì)劃，執(zhí)行一次分析表可能會(huì)解決問(wèn)題。在分析期間，使用一個(gè)讀取鎖定對(duì)表進(jìn)行鎖定。這對(duì)于MyISAM, BDB和InnoDB表有作用。對(duì)于MyISAM表，本語(yǔ)句與使用myisamchk -a相當(dāng)，下例中對(duì)goods_stock表做了表分析：

ANALYZE TABLE goods_stock;

檢查表的語(yǔ)法如下：

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

檢查表的作用是檢查一個(gè)或多個(gè)表是否有錯(cuò)誤。CHECK TABLE對(duì)MyISAM和InnoDB表有作用。對(duì)于MyISAM表，關(guān)鍵字統(tǒng)計(jì)數(shù)據(jù)被更新，例如：

CHECK TABLE goods_stock;

CHECK TABLE也可以檢查視圖是否有錯(cuò)誤，比如在視圖定義中被引用的表已不存在，舉例如下：

（1）首先我們創(chuàng)建一個(gè)表。

CREATE TABLE test
(
  ID INT(11)
);

（2）再創(chuàng)建一個(gè)視圖。

CREATE VIEW test_view AS SELECT * FROM test;

（3）然后CHECK一下該視圖，發(fā)現(xiàn)沒(méi)有問(wèn)題。

CHECK TABLE test_view;

（4）現(xiàn)在刪除掉視圖依賴(lài)的表。

DROP TABLE test;

（5）再來(lái)CHECK一下剛才的視圖，發(fā)現(xiàn)報(bào)錯(cuò)了。

CHECK TABLE test_view;

5.2定期優(yōu)化表

優(yōu)化表的語(yǔ)法如下：

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

如果已經(jīng)刪除了表的一大部分，或者如果已經(jīng)對(duì)含有可變長(zhǎng)度行的表（含有VARCHAR、BLOB或TEXT列的表）進(jìn)行了很多更改，則應(yīng)使用OPTIMIZE TABLE 命令來(lái)進(jìn)行表優(yōu)化。這個(gè)命令可以將表中的空間碎片進(jìn)行合并，并且可以消除由于刪除或者更新造成的空間浪費(fèi)，但OPTIMIZE TABLE命令只對(duì)MyISAM、BDB和InnoDB表起作用。以下例子顯示了優(yōu)化goods_stock庫(kù)存表的過(guò)程：

-- 先查看下goods_stock庫(kù)存表是什么表類(lèi)型
SHOW TABLE STATUS LIKE 'goods_stock%';

OPTIMIZE TABLE goods_stock;

注意：ANALYZE、CHECK、OPTIMIZE執(zhí)行期間將對(duì)表進(jìn)行鎖定，因此一定注意要在數(shù)據(jù)庫(kù)不繁忙的情況下執(zhí)行相關(guān)的操作。

到此這篇關(guān)于MySQL使用索引優(yōu)化性能的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評(píng)論