InnoDB 的索引分为聚集索引和辅助聚集索引。
InnoDB 的表是索引组织表,即表中的数据按照主键顺序排放的。
聚集索引其实就是表的主键,根据主键存储信息(正行)。
mysql> create table t(a nout int chnot null, b char(3) noyt null ,primary key(a))engine =innodb;
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> show index from t; \G
*************************** 1. row ***************************
Table: t
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: a
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
辅助索引就是自己创建的索引,可以使用alter table table_name add key 或者create index 的语法来创建。
mysql> alter table t add key(b);
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t | 0 | PRIMARY | 1 | a | A | 0 | NULL | NULL | | BTREE | | |
| t | 1 | b | 1 | b | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t | 0 | PRIMARY | 1 | a | A | 0 | NULL | NULL | | BTREE | | |
| t | 1 | b | 1 | b | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
辅助索引中包含了键值和一个书签(相应聚集索引的键),所以使用辅助索引其实就是先根据辅助索引的键值查找相应的行,再根据索引键中包含的主键信息再去查找索引组织表。
当然如果在辅助索引就能查找到所需的值,是不需要查找索引组织表的。
mysql> desc t;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | NO | PRI | NULL | |
| b | char(3) | NO | MUL | NULL | |
| c | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | NO | PRI | NULL | |
| b | char(3) | NO | MUL | NULL | |
| c | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> explain for select a,b from t;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | index | NULL | b | 3 | NULL | 3 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | index | NULL | b | 3 | NULL | 3 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25956376/viewspace-711680/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25956376/viewspace-711680/