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

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

概述

??索引是數據庫優化中最常用的也是最重要的手段之一。該篇看看索引的分類，使用場景，不能使用的場景等。

索引分類

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

衍生索引：主鍵，唯一索引，組合索引，前綴索引等。hash索引適合key-value查詢（等值查詢），不適合范圍查詢。

使用索引的典型場景

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;

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

#創建索引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';

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

#創建索引idx_title_desc_part(title(10)，description(20));

EXPLAIN SELECT title FROM film_text WHERE title LIKE 'AFRICAN%';

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

EXPLAIN SELECT * FROM payment WHERE rental_id IS NULL;

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;

EXPLAIN SELECT * FROM actor WHERE last_name=1;

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

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

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

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