Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions are that indexes on spatial data types use R-trees, and that MEMORY tables also support hash indexes.
mysql> create table t7(id int) type=myisam; Query OK, 0 rows affected, 1 warning (0.08 sec)mysql> create index idx_t7 using hash on t7(id); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0mysql> create index idx_t7_2 using btree on t7(id); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 不知道这个怎么解释
see http://dev.mysql.com/doc/refman/5.1/en/create-index.html Storage Engine Allowable Index Types MyISAM BTREE, RTREE InnoDB BTREE MEMORY/HEAP HASH, BTREE NDB HASH, BTREE (see note in text) mysql> show index from t7; +-------+------------+----------+--------------+-------------+-----------+------ -------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi nality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+----------+--------------+-------------+-----------+------ -------+----------+--------+------+------------+---------+ | t7 | 1 | idx_t7 | 1 | id | A | NULL | NULL | NULL | YES | BTREE | | | t7 | 1 | idx_t7_2 | 1 | id | A | NULL | NULL | NULL | YES | BTREE | | +-------+------------+----------+--------------+-------------+-----------+------ -------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec)
fulltext、index索引 这三种都是。B-TREE,B+-TREE,R-TREE,T-TREE,HASH.
http://blog.haohtml.com/index.php/archives/3311看看这样就明白了,呵
在《深入浅出MySQL——数据库开发、优化与管理维护 》中有讲到primary key、unique、index和fulltext索引都是存储在B树结构中的。
Query OK, 0 rows affected, 1 warning (0.08 sec)mysql> create index idx_t7 using hash on t7(id);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> create index idx_t7_2 using btree on t7(id);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
不知道这个怎么解释
Storage Engine Allowable Index Types
MyISAM BTREE, RTREE
InnoDB BTREE
MEMORY/HEAP HASH, BTREE
NDB HASH, BTREE (see note in text)
mysql> show index from t7;
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi
nality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
| t7 | 1 | idx_t7 | 1 | id | A |
NULL | NULL | NULL | YES | BTREE | |
| t7 | 1 | idx_t7_2 | 1 | id | A |
NULL | NULL | NULL | YES | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
看来using hash on t7(id)没什么效果。等同于btree.