MySQL创建索引原则

索引是一种特殊的数据结构，可以用来快速查询数据库的特定记录，建立是一中提高数据库性能的重要方式。

 

内容：索引的意义，索引的设计，创建和删除

 

可参考的资料：http://www.51cto.com/specbook/52/12911.htm

 

索引简介

索引是建立在表上的，有一列或者多列组成，并对这一列或者多列进行排序的一种结构。

 

所有存储引擎对每个表至少支持16个索引，总索引长度至少为256字节，索引有两种存储类型，包括B型树索引和哈希索引。

 

索引的优点是可以提高检索的速度，但是创建和维护索引需要耗费时间，这个时间随着数据量的增加而增加。

 

索引可以提高查询的速度，但是会影响插入的速度，当要插入大量的数据时，最好的办法是先删除索引，插入数据后再建立索引。

 

MySQL的索引分为：普通索引，唯一性索引，全文索引，单列索引，多列索引和空间索引。

 

目前只有MyISAM存储引擎支持全文索引，InnoDB引擎5.6以下版本还不支持全文索引。则需要升级mysql。例如：从5.1升级到5.5，再升级到5.6版本。详细见mysql手册。http://dev.mysql.com/doc/refman/5.6/en/upgrading.html

 

索引的设计原则

1. 选择唯一性索引。
2. 为经常需要排序，分组和联合操作的字段建立索引。
3. 为常作为查询条件的字段建立索引。
4. 限制索引的数目。
5. 尽量使用数据量少的索引。
6. 尽量使用前缀来索引。如果字段的值很长，最好使用值的前缀来索引，如果只检索子酸的前面的若干字符，可以提高检索的速度。
7. 删除不再使用或者很少使用的索引。

原则只是参考而不能拘泥。

 

创建索引

三种方式：在创建表是创建索引，在已存在的表上创建索引和使用alter table语句创建索引。

 

mysql> show tables;
+----------------+
| Tables_in_kiwi |
+----------------+
| stu |
+----------------+
1 row in set (0.00 sec)

mysql> create table indexTest(id int, name varchar(20), sex boolean, index index_id(id));
Query OK, 0 rows affected (0.08 sec)

mysql> desc indextest;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> explain select * from indextest where id = 1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: indextest
type: ref
possible_keys: index_id
key: index_id
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)

 

创建单列索引，subject(10)是为了不查询全部信息而提高检索的速度。

mysql> create table singleRow(id int,name varchar(20),subject varchar(30),index index_st(subject(10)));
Query OK, 0 rows affected (0.17 sec)

mysql> show create table singlerow\G;
*************************** 1. row ***************************
Table: singlerow
Create Table: CREATE TABLE `singlerow` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`subject` varchar(30) DEFAULT NULL,
KEY `index_st` (`subject`(10))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

多列索引，空间索引类似。

 

在已存在的表上建立索引

语法为： create [unique|fulltext|spatial] index index_name on table_name (property_name[length] [asc|desc]);

mysql> desc stu;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| s_num | int(10) | YES | MUL | NULL | |
| course | varchar(20) | YES | | NULL | |
| score | varchar(4) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.05 sec)

mysql> show create table stu \G;
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`s_num` int(10) DEFAULT NULL,
`course` varchar(20) DEFAULT NULL,
`score` varchar(4) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `index_id` (`id`),
KEY `grade_fk` (`s_num`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

 

使用alter table创建索引

语法为：

alter table table_name add [unique|fulltext|spatial] index index_name(property_name[length] [asc|desc]);

 

mysql> create table index_1(id int, name varchar(20), class int);
Query OK, 0 rows affected (0.11 sec)

mysql> show tables;
+----------------+
| Tables_in_kiwi |
+----------------+
| index_1 |
| singlerow |
| stu |
+----------------+
3 rows in set (0.00 sec)

mysql> show create table index_1 \G;
*************************** 1. row ***************************
Table: index_1
Create Table: CREATE TABLE `index_1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`class` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table index_1 add fulltext index index_alter (name desc);
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
mysql> alter table index_1 engine=myisam;
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table index_1 add fulltext index index_alter (name desc);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table index_1 \G;
*************************** 1. row ***************************
Table: index_1
Create Table: CREATE TABLE `index_1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`class` int(11) DEFAULT NULL,
FULLTEXT KEY `index_alter` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

 

删除索引

语法：

drop index index_name on table_name;

mysql> show create table index_1 \G;
*************************** 1. row ***************************
Table: index_1
Create Table: CREATE TABLE `index_1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`class` int(11) DEFAULT NULL,
FULLTEXT KEY `index_alter` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> drop index index_alter on index_1;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table index_1 \G;
*************************** 1. row ***************************
Table: index_1
Create Table: CREATE TABLE `index_1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`class` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)