(2)mysql最佳化之使用索引最佳化查詢

標籤：mysql   索引最佳化   索引   

概述

??索引是資料庫最佳化中最常用的也是最重要的手段之一。該篇看看索引的分類，使用情境，不能使用的情境等。

索引分類

??索引是在mysql儲存引擎中實現的，而不是在伺服器層實現的。索引每種儲存引擎的索引不一定相同。mysql支援4種索引：

• B-tree索引：最常見索引，大部分引擎都支援B樹索引。
• full-text索引：全文索引。
• hash索引：只有memory引擎支援。
• R-Tree索引：空間索引是myisam的一種特殊索引，使用比較少。

衍生索引：主鍵，唯一索引，複合式索引，首碼索引等。hash索引適合key-value查詢（等值查詢），不適合範圍查詢。

索引	MYISAM引擎	InnoDB引擎	Memory引擎
B-tree索引	支援	支援	支援
full-text索引	支援	-	-
hash索引	-	-	支援
R-tree索引	支援	-	-

使用索引的典型情境 1.匹配全值(match the full value)

對索引中所有列都指定具體值，即對索引中的所有列都有等值匹配的條件。

#設定複合式索引（rental_date,inventory_id,customer_id）為唯一索引。EXPLAIN SELECT * FROM rental WHERE rental_date=‘2005-05-25 17:22:10‘   AND inventory_id=373   AND customer_id=343;

2.匹配值的範圍查詢(match a range of values)

對索引值進行範圍尋找。

#設定索引idx_fk_customer_id(customer_id)EXPLAIN SELECT * FROM rental WHERE customer_id >= 373 AND customer_id < 400;

3.匹配最左首碼(match a leftmost prefix)

僅僅使用索引中的最左邊列進行查詢。比如複合式索引（col1,col2,col3）能夠被col1,col1+col2,col1+col2+col3的等值查詢利用到的。

#建立索引idx_payment_date(payment_date,amount,last_update);EXPLAIN SELECT * FROM payment WHERE payment_date=‘2006-02-14 15:16:03‘ AND last_update = ‘2006-02-15 22:12:32‘;

從結果可以看出利用了索引，但又row為182行，所有只使用了部分索引。

EXPLAIN SELECT * FROM payment WHERE amount=3.98 AND last_update=‘2006-02-15‘;

從結果看出，這次查詢沒有利用索引，進行了全表尋找。

4.僅對索引查詢進行查詢（index only query）

當查詢列都在索引欄位中。即select中的列都在索引中。

EXPLAIN SELECT last_update FROM payment WHERE payment_date=‘2005-08-19 21:21:47‘ AND amount=4.99;

extra部分Using index,說明不需要通過索引回表，Using index就是平時說的覆蓋索引掃描（即找到索引，就找到了要查詢的結果，不用再回表尋找了）。

5.匹配列首碼（match a column prefix）

僅僅使用索引的第一列，並且只包含索引第1列的開頭部分進行尋找。

#建立索引idx_title_desc_part(title(10)，description(20));EXPLAIN SELECT title FROM film_text WHERE title LIKE ‘AFRICAN%‘;

6.索引部分等值匹配，部分範圍匹配

EXPLAIN SELECT inventory_id FROM rental WHERE rental_date=‘2006-02-14 15:16:03‘ AND customer_id >= 300 AND customer_id <=400;

type=ref，說明使用了索引。

7.列名是索引，column_name is null，使用索引

EXPLAIN SELECT * FROM payment WHERE rental_id IS NULL;

索引存在但不能使用索引的典型情境 1.以%開頭的like查詢

EXPLAIN SELECT * FROM actor WHERE last_name LIKE ‘%NI%‘;

#最佳化EXPLAIN SELECT * FROM (SELECT actor_id FROM actor WHERE last_name LIKE ‘%NI%‘)a,actor b WHERE a.actor_id=b.actor_id;

2.資料類型出現隱式轉化，不會使用索引

EXPLAIN SELECT * FROM actor WHERE last_name=1;

#使用索引EXPLAIN SELECT * FROM actor WHERE last_name=‘1‘;

3.複合式索引，不滿足最左原則，不使用符合索引

EXPLAIN SELECT * FROM payment WHERE amount=3.98 AND last_update=‘2006-02-15 22:12:32‘;

4.估計使用索引比全表掃描還慢，則不要使用索引

如查詢以“S”開頭的標題的電影，返回記錄比例比較大，mysql預估索引掃描還不如全表掃描。

EXPLAIN SELECT * FROM film_text WHERE title LIKE ‘S%‘;

5.用or分割條件，若or前後只要有一個列沒有索引，就都不會用索引

EXPLAIN SELECT * FROM payment WHERE customer_id=203 OR amount=3.96;

(2)mysql最佳化之使用索引最佳化查詢