MySQL使用索引的幾種方式

之前的blog有介紹令人迷糊的extra中信息，本文我們重點(diǎn)來(lái)看看當(dāng)執(zhí)行計(jì)劃使用索引的時(shí)候

extra中幾種顯示的場(chǎng)景

版本:Mysql 5.6.14

測(cè)試表結(jié)構(gòu)如下:

CREATE TABLE `snapshot` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`snap_id` int(11) DEFAULT NULL,

`name` varchar(500) DEFAULT NULL,

`value` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `snap_id_ix` (`snap_id`)

) ENGINE=InnoDB? DEFAULT CHARSET=utf8

在snap_id上創(chuàng)建了索引，然后插入一些測(cè)試數(shù)據(jù)。

1，snap_id為等于的操作

mysql> explain extended select * from snapshot where snap_id=10;

+----+-------------+----------+------+---------------+------------+---------+-------+------+----------+-------+

| id | select_type | table??? | type | possible_keys | key??????? | key_len | ref?? | rows | filtered | Extra |

+----+-------------+----------+------+---------------+------------+---------+-------+------+----------+-------+

|? 1 | SIMPLE????? | snapshot | ref? | snap_id_ix??? | snap_id_ix | 5?????? | const |??? 1 |?? 100.00 | NULL? |

+----+-------------+----------+------+---------------+------------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec)

這個(gè)雖然使用了索引，但是extra中沒(méi)有任何信息，顯示為空

2,使用索引條件，先掃描索引，然后再通過(guò)索引掃描表

mysql>? explain extended select *? from snapshot where snap_id<100;

+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+-----------------------+

| id | select_type | table??? | type? | possible_keys | key??????? | key_len | ref? | rows | filtered | Extra???????????????? |

+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+-----------------------+

|? 1 | SIMPLE????? | snapshot | range | snap_id_ix??? | snap_id_ix | 5?????? | NULL |??? 1 |?? 100.00 | Using index condition |

+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+-----------------------+

1 row in set, 1 warning (0.00 sec)

通過(guò)索引條件，然后掃描表數(shù)據(jù)，extra是Using index condition

3,使用cover index，只查索引中的列，直接從索引中返回?cái)?shù)據(jù)就可以了

mysql>? explain extended select snap_id from snapshot;

+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+-------------+

| id | select_type | table??? | type? | possible_keys | key??????? | key_len | ref? | rows | filtered | Extra?????? |

+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+-------------+

|? 1 | SIMPLE????? | snapshot | index | NULL????????? | snap_id_ix | 5?????? | NULL | 6415 |?? 100.00 | Using index |

+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

mysql>? explain extended select snap_id from snapshot where snap_id=10;

+----+-------------+----------+------+---------------+------------+---------+-------+------+----------+-------------+

| id | select_type | table??? | type | possible_keys | key??????? | key_len | ref?? | rows | filtered | Extra?????? |

+----+-------------+----------+------+---------------+------------+---------+-------+------+----------+-------------+

|? 1 | SIMPLE????? | snapshot | ref? | snap_id_ix??? | snap_id_ix | 5?????? | const |??? 1 |?? 100.00 | Using index |

+----+-------------+----------+------+---------------+------------+---------+-------+------+----------+-------------+

1 row in set, 1 warning (0.01 sec)

mysql>? explain extended select snap_id from snapshot where snap_id>10;

+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+--------------------------+

| id | select_type | table??? | type? | possible_keys | key??????? | key_len | ref? | rows | filtered | Extra??????????????????? |

+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+--------------------------+

|? 1 | SIMPLE????? | snapshot | range | snap_id_ix??? | snap_id_ix | 5?????? | NULL | 6367 |?? 100.00 | Using where; Using index |

+----+-------------+----------+-------+---------------+------------+---------+------+------+----------+--------------------------+

1 row in set, 1 warning (0.00 sec)

在extra出現(xiàn)的是Using index提示，表明只使用索引即可，不需要對(duì)表進(jìn)行掃描操作.

4,?? 接下來(lái)我們看連接的操作，假設(shè)為snapshot作為連接的內(nèi)表

mysql> explain

-> select *

-> from t1 join snapshot

-> on (t1.a=snapshot.snap_id)

-> ;

+----+-------------+----------+------+---------------+------------+---------+----------+------+-------+

| id | select_type | table??? | type | possible_keys | key??????? | key_len | ref????? | rows | Extra |

+----+-------------+----------+------+---------------+------------+---------+----------+------+-------+

|? 1 | SIMPLE????? | t1?????? | ALL? | PRIMARY?????? | NULL?????? | NULL??? | NULL???? |??? 3 | NULL? |

|? 1 | SIMPLE????? | snapshot | ref? | snap_id_ix??? | snap_id_ix | 5?????? | db1.t1.a |? 200 | NULL? |

+----+-------------+----------+------+---------------+------------+---------+----------+------+-------+

2 rows in set (0.00 sec)

通過(guò)執(zhí)行計(jì)劃可以看出對(duì)snapshot也是索引來(lái)訪(fǎng)問(wèn)的，外表傳過(guò)來(lái)的值，在內(nèi)表很類(lèi)似于snap_id=XX,所以和第1中的執(zhí)行

計(jì)劃很類(lèi)似.除ref之外，一個(gè)是來(lái)自于常量(const),而本例中是來(lái)自表t1的索引db.t1.a.

總結(jié)

以上是生活随笔為你收集整理的oracle 5种索引方式,MySQL使用索引的几种方式的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。