MySQL性能优化6 - 索引类型

MyISAM 存储引擎的 表的数据和索引是自动分开存储的，各自是独立的一个文件； InnoDB 存储引擎 的表的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。

MySQL 中索引的存储类型目前只有两种（ btree 和 hash ），具体和表的存储引擎相关： MyISAM 和 InnoDB 存储引擎都只支持 btree 索引； MEMORY/HEAP 存储引擎可以支持 hash 和 btree 索引。

MEMORY 存储引擎的表可以选择使用 BTREE 索引或者 HASH 索引，两种不同类型的索引各有其不同的适用范围。 HASH 索引有一些重要的特征需要在使用的时候特别注意，如下所示。

• 只用于使用[cci lang='mysql' ] =[/cci] 或[cci lang='mysql' ] <=> [/cci]操作符的等式比较。


• 优化器不能使用 HASH 索引来加速 [cci lang='mysql' ]ORDER BY[/cci] 操作。


• MySQL 不能确定在两个值之间大约有多少行。如果将一个 MyISAM 表改为 HASH 索引的 MEMORY 表，会影响一些查询的执行效率。


• 只能使用整个关键字来搜索一行。

而对于 BTREE 索引，当使用 [cci lang='mysql' ]>[/cci]、 [cci lang='mysql' ]<[/cci] 、 [cci lang='mysql' ]>= [/cci]、 [cci lang='mysql' ]<=[/cci] 、 [cci lang='mysql' ]BETWEEN [/cci]、[cci lang='mysql' ] !=[/cci] 或者[cci lang='mysql' ] <> [/cci]，或者[cci lang='mysql' ] LIKE 'pattern'[/cci] （其中 'pattern' 不以通配符开始）操作符时，都可以使用相关列上的索引。

下列范围查询适用于 BTREE 索引和 HASH 索引：

[cc lang='mysql' ]SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);[/cc]

下列范围查询只适用于 BTREE 索引：

[cc lang='mysql' ]SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;[/cc]

[cc lang='mysql' ]SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'lisa' AND 'simon';[/cc]

例如，创建一个和 city 表完全相同的 MEMORY 存储引擎的表 city_memory ：

[cc lang="mysql"]mysql> CREATE TABLE city_memory (
-> city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> city VARCHAR(50) NOT NULL,
-> country_id SMALLINT UNSIGNED NOT NULL,
-> last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (city_id),
-> KEY idx_fk_country_id (country_id)
-> )ENGINE=Memory DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into city_memory select * from city;

Query OK, 600 rows affected (0.00 sec)
Records: 600 Duplicates: 0 Warnings: 0[/cc]

当对索引字段进行范围查询的时候，只有 BTREE 索引可以通过索引访问：
[cc lang="mysql"]
mysql> explain SELECT * FROM city WHERE country_id > 1 and country_id < 10 \G

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: city
type: range
possible_keys: idx_fk_country_id
key: idx_fk_country_id
key_len: 2
ref: NULL
rows: 24
Extra: Using where

1 row in set (0.00 sec)[/cc]
而 HASH 索引实际上是全表扫描的：
[cc lang="mysql"]
mysql> explain SELECT * FROM city_memory WHERE country_id > 1 and country_id < 10 \G

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: city_memory
type: ALL
possible_keys: idx_fk_country_id
key: NULL
key_len: NULL
ref: NULL
rows: 600
Extra: Using where

1 row in set (0.00 sec)[/cc]
了解了 BTREE 索引和 HASH 索引不同后，当使用 MEMORY 表的时候，如果是默认创建的 HASH 索引，就要注意 SQL 语句的编写，确保可以使用上索引，如果一定要使用范围查询，那么在创建索引的时候，就应该选择创建成 BTREE 索引。