索引(Index)是帮助MySQL高效获取数据的数据结构,它的存在形式是文件。索引能够帮助我们快速定位数据,它可以让mysql高效运行,大大提高mysql的查询(包括排序,分组)效率。
Mysql目前主要有以下几种索引类型(按存储结构划分):FULLTEXT,HASH,BTREE,RTREE。
对应存储引擎支持如下:
MyISAM | BTREE,FULLTEXT,RTREE |
Innodb | BTREE,RTREE |
Memory | HASH,BTREE |
NDB | BTREE,HASH,RTREE |
注:前面索引为存储引擎默认索引。
MySQL索引在使用上一般有如下几种种类(按使用功能划分):
普通索引(INDEX),唯一索引(UNIQUE INDEX),主键索引(PRIMARY KEY),全文索引(FULLTEXT INDEX),外键索引(FOREIGN KEY),组合索引。
2.1 索引的优点
大大加快数据的检索速度,这也是创建索引的最主要的原因。
创建唯一性索引,保证数据库表中每一行数据的唯一性。
加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
通过使用索引,可以在查询的过程中使用优化隐藏器。
2.2 索引的缺点
创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度。
建立索引会占用磁盘空间的索引文件,尤其大表上创建了多种组合索引,索引文件的会膨胀很快。
3.1 建表时创建索引
3.1.1 创建普通索引
CREATE TABLE index_normal (
c1 int default NULL,
c2 varchar(30) default NULL,
c3 date default NULL,
INDEX normal (c3)
) engine=innodb;
查看表结构
mysql> show create table index_normal \G
*************************** 1. row ***************************
Table: index_normal
Create Table: CREATE TABLE `index_normal` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(30) COLLATE utf8_bin DEFAULT NULL,
`c3` date DEFAULT NULL,
KEY `normal` (`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
注:若不显示指定normal为关键字,key会指定c3为关键字。
插入语句并查看执行计划
mysql> insert into index_normal values (1,'testing partitions','1995-07-17');
mysql> explain select * from index_normal where c3 = 1995-07-17 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: index_normal
type: ref
possible_keys: normal
key: normal
key_len: 4
ref: const
rows: 1
Extra: Using where
3.1.2 创建唯一索引