mysql索引建立和優化
建立索引的幾大原則
最左前綴匹配原則，mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 ，如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整（參考原則2）。但是mysql查詢優化器可能通過優化調整順序從而使用索引，但是寫sql語句時還是按照此原則；= 和 in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識別的形式；盡量選擇區分度高的列作為索引,區分度的公式是count(distinct col)/count(*)，表示字段不重復的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別字段可能在大數據面前區分度就是0，那可能有人會問，這個比例有什麼經驗值嗎？使用場景不同，這個值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄索引列不能參與計算，保持列“干淨”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數據表中的字段值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’);盡量的擴展索引，不要新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那麼只需要修改原來的索引即可
注意：
前綴索引在Oder by 和 Group by操作的時候無法使用；
hash索引不適用於范圍查詢，例如<，>，<=，>=等操作。如果使用Memory/Heap引擎並且where條件中不使用"="進行索引列，那麼不會用到索引。Memory/Heap引擎只有在"="條件下才會使用索引；
mysql> show create table rental\G *************************** 1. row *************************** Table: rental Create Table: CREATE TABLE `rental` ( `rental_id` int(11) NOT NULL AUTO_INCREMENT, `rental_date` datetime NOT NULL, `inventory_id` mediumint(8) unsigned NOT NULL, `customer_id` smallint(5) unsigned NOT NULL, `return_date` datetime DEFAULT NULL, `staff_id` tinyint(3) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`rental_id`), UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`), KEY `idx_fk_inventory_id` (`inventory_id`), KEY `idx_fk_customer_id` (`customer_id`), KEY `idx_fk_staff_id` (`staff_id`), CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE, CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE, CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
mysql> alter table rental drop index rental_date; Query OK, 16044 rows affected (0.92 sec) Records: 16044 Duplicates: 0 Warnings: 0
ql> alter table rental add index idx_rental_date(rental_date ,inventory_id,customer_id); Query OK, 16044 rows affected (0.48 sec) Records: 16044 Duplicates: 0 Warnings: 0
//匹配全值，對索引中的所有列都執行具體值，即是對索引中的所有列都有等值匹配的條件。 mysql> explain select * from rental where rental_date='2005-05-25 17:22:10' and inventory_id=373 and customer_id=343\G（等值查詢的話查詢條件可以亂序） *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: ref //使用一般索引，所以此時是ref，使用唯一性索引或者primary key進行查詢時是const possible_keys: idx_fk_inventory_id,idx_fk_customer_id,idx_rental_date key: idx_rental_date key_len: 13 ref: const,const,const //顯示哪些字段或者常量用來和key配合從表中查詢記錄出來 rows: 1 Extra: 1 row in set (0.00 sec)
mysql> alter table rental drop index idx_rental_date; Query OK, 16044 rows affected (0.65 sec) Records: 16044 Duplicates: 0 Warnings: 0
mysql> alter table rental add unique index idx_rental_date(rental_date ,inventory_id,customer_id); Query OK, 16044 rows affected (0.56 sec) Records: 16044 Duplicates: 0 Warnings: 0
mysql> explain select * from rental where rental_date='2005-05-25 17:22:10' and inventory_id=373 and customer_id=343\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: const //使用唯一性索引或者primary key進行查詢時是const possible_keys: idx_rental_date,idx_fk_inventory_id,idx_fk_customer_id key: idx_rental_date key_len: 13 ref: const,const,const rows: 1 Extra: 1 row in set (0.00 sec)
注意：等號或者in可以亂序 mysql> explain select * from rental where customer_id = 5 and rental_date = '2006-05-30 10:00:00'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: ref possible_keys: idx_fk_customer_id,idx_rental_date key: idx_rental_date key_len: 10 ref: const,const rows: 1 Extra: 1 row in set (0.01 sec) mysql> explain select * from rental where rental_date = '2006-05-30 10:00:00' and customer_id = 5\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: ref possible_keys: idx_fk_customer_id,idx_rental_date key: idx_rental_date key_len: 10 ref: const,const rows: 1 Extra: 1 row in set (0.00 sec)
//匹配值的范圍查詢，對索引的值能夠進行范圍查詢 mysql> explain select * from rental where customer_id >=373 and customer_id <= 400\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: range possible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 745 Extra: Using where //Using where表示優化器除了根據索引來加速訪問之外，還根據索引回表查詢數據。 1 row in set (0.00 sec)
Using where
A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.
//匹配最左匹配，僅僅使用索引中的最左邊列進行查找。
mysql> show create table payment\G *************************** 1. row *************************** Table: payment Create Table: CREATE TABLE `payment` ( `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `customer_id` smallint(5) unsigned NOT NULL, `staff_id` tinyint(3) unsigned NOT NULL, `rental_id` int(11) DEFAULT NULL, `amount` decimal(5,2) NOT NULL, `payment_date` datetime NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`payment_id`), KEY `idx_fk_staff_id` (`staff_id`), KEY `idx_fk_customer_id` (`customer_id`), KEY `fk_payment_rental` (`rental_id`), CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE, CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
mysql> alter table payment add index idx_payment_date(payment_date,amount,last_update); Query OK, 16049 rows affected (2.85 sec) Records: 16049 Duplicates: 0 Warnings: 0
mysql> explain select * from payment where payment_date = '2006-02-14 15:16:03' and last_update='2006-02-15 22:12:32'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: ref possible_keys: idx_payment_date key: idx_payment_date key_len: 8 ref: const rows: 182 Extra: Using where 1 row in set (0.00 sec)
mysql> explain select * from payment where amount = 3 and last_update='2006-02-15 22:12:32'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: ALL //不是使用最左匹配，全表掃描 possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 16470 Extra: Using where 1 row in set (0.00 sec)
//僅僅對索引進行查詢，當查詢的字段在索引的字段中時，查詢的效率更高。不必回表數據。 mysql> explain select last_update from payment where payment_date = '2006-02-14 15:16:03' and amount=3.98\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: ref possible_keys: idx_payment_date key: idx_payment_date key_len: 11 ref: const,const rows: 8 Extra: Using index //using index表示直接通過訪問索引就足夠獲取所需要的數據，無需通過索引回表，using index也就是常說的覆蓋索引掃描。只訪問必須訪問的數據，在一般情況下，減少不必要的數據訪問能夠提高效率。 1 row in set (0.04 sec)
//匹配列前綴 mysql> show create table film_text\G *************************** 1. row *************************** Table: film_text Create Table: CREATE TABLE `film_text` ( `film_id` smallint(6) NOT NULL, `title` varchar(255) NOT NULL, `description` text, PRIMARY KEY (`film_id`), FULLTEXT KEY `idx_title_description` (`title`,`description`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> create index idx_title_desc_part on film_text(title(10),description(20)); Query OK, 1000 rows affected (0.40 sec) Records: 1000 Duplicates: 0 Warnings: 0
mysql> explain select title from film_text where title like 'AFRICAN%'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film_text type: range possible_keys: idx_title_desc_part,idx_title_description key: idx_title_desc_part //使用列前綴匹配，不適用全局索引idx_title_description key_len: 32 ref: NULL rows: 1 Extra: Using where 1 row in set (0.16 sec)
//實現索引匹配是部分精確，而其他部分進行范圍匹配。 mysql> alter table rental drop index idx_rental_date; Query OK, 16044 rows affected (0.86 sec) Records: 16044 Duplicates: 0 Warnings: 0 mysql> alter table rental add index idx_rental_date(rental_date,customer_id,inventory_id); Query OK, 16044 rows affected (1.43 sec) Records: 16044 Duplicates: 0 Warnings: 0 mysql> explain select inventory_id from rental where rental_date = '2006-02-14 15:16:03' and customer_id >=300 and customer_id <=400\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: range possible_keys: idx_fk_customer_id,idx_rental_date key: idx_rental_date key_len: 10 ref: NULL rows: 24 Extra: Using where; Using index 1 row in set (0.00 sec) 執行過程是先使用索引的首字段rental_date將符合rental_date = '2006-02-14 15:16:03'的索引過濾，通過IO取出數據（回表，因為還要進行customer_id條件進行過濾），然後通過customer_id>=300 <=400過濾記錄。
注意：將range放在前面mysql查詢優化器也會將語句優化為可以使用索引的查詢。 mysql> explain select * from rental where customer_id > 5 and rental_date = '2006-05-30 10:00:00'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: range possible_keys: idx_fk_customer_id,idx_rental_date key: idx_rental_date key_len: 10 ref: NULL rows: 1 Extra: Using where 1 row in set (0.00 sec)
//如果列名是索引，那麼使用column_name is null 就會使用索引。 mysql> show create table payment\G *************************** 1. row *************************** Table: payment Create Table: CREATE TABLE `payment` ( `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `customer_id` smallint(5) unsigned NOT NULL, `staff_id` tinyint(3) unsigned NOT NULL, `rental_id` int(11) DEFAULT NULL, `amount` decimal(5,2) NOT NULL, `payment_date` datetime NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`payment_id`), KEY `idx_fk_staff_id` (`staff_id`), KEY `idx_fk_customer_id` (`customer_id`), KEY `fk_payment_rental` (`rental_id`), KEY `idx_payment_date` (`payment_date`,`amount`,`last_update`), CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE, CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> explain select * from payment where rental_id is null\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: ref possible_keys: fk_payment_rental key: fk_payment_rental key_len: 5 ref: const rows: 5 Extra: Using where 1 row in set (0.00 sec)
不能使用索引的情況：
以%開頭的like查詢不能夠利用B-TREE索引，一般推薦使用全文索引來解決全文檢索問題（模糊查詢）；
數據類型出現隱式轉換的時候也不會使用索引；特別是字符串常量一定要使用引號引起來，這樣才會使用索引。mysql默認會把輸入的常量進行轉換之後才會進行檢索。 mysql> show create table actor\G *************************** 1. row *************************** Table: actor Create Table: CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`) ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> explain select * from actor where last_name = 1\G //沒有使用引號將常量引起來 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: ALL possible_keys: idx_actor_last_name key: NULL key_len: NULL ref: NULL rows: 200 Extra: Using where 1 row in set (0.00 sec) mysql> explain select * from actor where last_name = '1'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: ref possible_keys: idx_actor_last_name key: idx_actor_last_name key_len: 137 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec)
3 .不滿足最左條件不會使用索引。 4.用or分割的條件，如果or前的列有索引，而後面的列中沒有索引，那麼涉及的索引都不會被用到。因為後面的列的查詢要使用全表掃描，沒有必要再使用前面的列進行一次索引掃描。 mysql> explain select * from payment where customer_id = 203 or amount = 3.96\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: ALL possible_keys: idx_fk_customer_id key: NULL key_len: NULL ref: NULL rows: 16470 Extra: Using where 1 row in set (0.00 sec)
mysql> show status like 'Handler_read%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Handler_read_first | 2 | | Handler_read_key | 2 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 33123 | +-----------------------+-------+ 6 rows in set (0.00 sec)
如果索引正在工作，Handler_read_key的值將很高，這個值代表了一個行被索引值讀的次數，很低的值表明增加索引得到的性能改善不高，因為索引不經常使用。Handler_read_rnd_next表示在數據文件中讀下一行的請求數。如果正在進行大量的表掃描，Handler_read_rnd_next的值較高，則通常說明表索引不正確或者寫入查詢沒有用到索引。
慢查詢優化基本步驟
先運行看看是否真的很慢，注意設置SQL_NO_CACHE；
where條件單表查，鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應用到表中返回的記錄數最小的表開始查起，單表每個字段分別查詢，看哪個字段的區分度最高；
explain查看執行計劃，是否與1預期一致（從鎖定記錄較少的表開始查詢）；
order by limit 形式的sql語句讓排序的表優先查；
了解業務方使用場景；
加索引時參照建索引的幾大原則；
觀察結果，不符合預期繼續從1分析；
