先说为什么要使用索引:
索引用于快速找出在某个列中有一特定值的行。不使用索引,MySQL必须从第一行数据开始找,知道找到对应的数据;如果表中查询 的列有一个索引,MySQL能快速到达一个位置搜索数据文件,而不必查看所有数据。
一、索引简介
1、索引的含义和特点
索引是一个单独的、存储在磁盘上的数据库结构,包含着对数据表里所有记录的引用指针。
索引的优点:
- 通过创建唯一索引,可以保证数据库表中每一行数据的唯一性
- 可以大大加快数据的查询速度
- 在实现数据的参考完整性方面,可以加速表和表之间的连接
- 在使用分组和排序子句进行数据查询时,也可以显著减少查询中分组和排序的时间
索引的缺点:
- 创建索引和维护索引需要耗费时间,并且随着数据量的增加所耗费的时间也会增加
- 索引需要占磁盘空间,如果创建大量的索引,索引文件可能比数据文件更快达到最大文件尺寸
- 对表中的数据进行增删改时,索引也要动态维护,降低了数据的维护速度
2、索引的分类
- 普通索引和唯一索引:普通索引允许在定义索引的列中插入重复值和空值;唯一索引列的值必须唯一,允许有空值,如果是组合索引,列值得组合必须唯一,主键是一种特殊的唯一索引,不允许有空值。
- 单列索引和组合索引:单列索引就是一个索引只包含一个列,一个表中可以有多个单列索引。组合索引是指在表的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用。使用组合索引时遵循最左前缀集合。
- 全文索引:FULLTEXT,在定义索引的列上支持全文查找。列值可空柯重复,必须建立在char、varchar、text类型的列上。MySQL中只有MyISAM存储引擎支持全文索引。
- 空间索引:空间索引是对空间数据类型的字段创建的索引,MySQL空间数据类型有GEOMETRY、POINT、LINESTRING、POLYGON四种,创建空间索引的列必须申明为NOT NULL,MySQL中只有MyISAM存储引擎支持空间索引。
3、索引的设计原则
- 索引并不是越多越好。索引过多,不仅占磁盘空间,还会影响增删改的性能
- 避免对经常更新的表创建过多的索引,并且索引中的列尽量少。对经常查询的列创建索引,但避免添加不必要的字段
- 数据量小的表最好不要使用索引
- 在条件表达式中经常用到的不同值较多的列上创建索引,在不同值较少的列上不要创建索引。例如性别只有“男”和“女”,就不要创建索引了
- 当唯一性是某种数据的本身特征时,指定唯一索引
- 在频繁进行排序或分组(即进行group by或order by操作)的列上创建索引。如果排序列有多个,可以创建组合索引
二、创建索引
1、创建表时创建索引
基本语法:
create table table_name [col_name data_type] [unique|fulltext|spatial] [index|key] [index_name] (col_name[length]) [asc|desc]
其中unique、fulltext、spatial时可选参数,分别代表唯一索引、全文索引和空间索引。
创建普通索引:
-- 创建一个表并创建普通索引
mysql> create table book(
-> id int not null,
-> name varchar(255) not null,
-> info varchar(255) not null,
-> year_publication year not null,
-> index(year_publication)
-> );
Query OK, 0 rows affected (0.09 sec)
-- 查看索引是否创建成功
mysql> show create table book\G
*************************** 1. row ***************************
Table: book
Create Table: CREATE TABLE `book` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`info` varchar(255) NOT NULL,
`year_publication` year(4) NOT NULL,
KEY `year_publication` (`year_publication`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.02 sec)
-- 查看索引是否正常使用
mysql> explain select * from book where year_publication=1990\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: book
type: ref
possible_keys: year_publication
key: year_publication
key_len: 1
ref: const
rows: 1
Extra:
1 row in set (0.08 sec)
这里对explain语句输出结果的各个行结束一下:
1、select_type行指定所使用的select查询类型,这里的值是simple,表示简单的select,不使用union或子查询。其他可能取值有:primary、union、subquery等。
2、table行指定数据库读取的表名字
3、type行指定了本数据表与其他数据表之间的关联关系,可能的取值有:system、const、eq_ref、ref、range、index和All。
4、possible_keys行给出了Mysql在搜索数据记录时可选用的各个索引。
5、key行是mysql实际选用的索引
6、key_len行给出索引按照字节计算的长度,key_len越小表示越快。
7、ref行给出了关联关系中另一个数据表里的数据列的名字
8、rows行是mysql在执行这个查询时预计会从这个数据表里读出的数据行的个数
9、extra行提供了与关联操作有关的信息
创建唯一索引:
mysql> create table t1(
-> id int not null,
-> name varchar(50) not null,
-> unique index uniqidx(id)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
UNIQUE KEY `uniqidx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
创建单列索引:
普通索引和唯一索引都是单列索引。
mysql> create table t2(
-> id int not null,
-> name varchar(50) not null,
-> index singleidx(name(20))
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
KEY `singleidx` (`name`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
创建组合索引:
-- 创建一个表并创建组合索引
mysql> create table t3(
-> id int not null,
-> name varchar(50) not null,
-> age int not null,
-> info varchar(255),
-> index multiidx(id,name,age)
-> );
Query OK, 0 rows affected (0.04 sec)
-- 查看表结构,看索引是否创建成功
mysql> show create table t3\G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`age` int(11) NOT NULL,
`info` varchar(255) DEFAULT NULL,
KEY `multiidx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> explain select * from t3 where id=1 and name='jon' and age=23\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t3
type: ref
possible_keys: multiidx
key: multiidx
key_len: 160
ref: const,const,const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from t3 where name='jon' and age=23\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t3
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)
这里解释一下上面的两个explain:
组合索引遵从“最左前缀”:利用组合索引最左边的列集来匹配行,例如这里是使用的id、name和age三个字段构成的索引,索引行中按照id/name/age的顺序存放,索引可以搜索下面的字段组合:(id,name,age)、(id,name)、(id)。否则mysql将不会使用索引。
创建全文索引:
mysql> create table t4(
-> id int not null,
-> name varchar(50) not null,
-> age int not null,
-> info varchar(255),
-> fulltext index fulltxtidx(info)
-> )engine=myisam;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table t4\g
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------+
| t4 | CREATE TABLE `t4` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`age` int(11) NOT NULL,
`info` varchar(255) DEFAULT NULL,
FULLTEXT KEY `fulltxtidx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------+
1 row in set (0.01 sec)
mysql5.6默认用的是innoDB存储引擎,在这里需要改为myisam存储引擎,因为只有myisam引擎支持全文索引。全文索引非常适合大型数据集
创建空间索引:
mysql> create table t5(
-> g geometry not null,
-> spatial index spatidx(g)
-> )engine=myisam;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table t5\g
+-------+-----------------------------------------------------------------------
------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
------------------------------------------+
| t5 | CREATE TABLE `t5` (
`g` geometry NOT NULL,
SPATIAL KEY `spatidx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
------------------------------------------+
1 row in set (0.01 sec)
2、在已经存在的表上创建索引
在已经存在的表上面创建索引可以用alter table和create index语句。
使用alter table语句创建索引:
基本语法:
alter table table_name add [unique|fulltext|spatial] [index|key] [index_name](col_name[length],...) [asc|desc]
查看表索引:
show index from table_name\G
例:
查看表中索引:
mysql> show index from book\G
*************************** 1. row ***************************
Table: book
Non_unique: 1
Key_name: year_publication
Seq_in_index: 1
Column_name: year_publication
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
1、Table表示创建索引的表
2、Non_unique表示索引非唯一,1代表非唯一索引,0代表唯一索引
3、Key_name表示索引的名称
4、Seq_in_index表示字段在索引中的位置,单列索引该值为1,组合索引为每个字段在索引定义中的顺序
5、Column_name表示定义索引的列字段
6、Sub_part表示索引长度
7、Null表示该字段是否能为空值
8、Index_type表示索引类型
创建索引例子:
-- 创建一个索引(这里以创建普通索引为例,创建唯一索引、单列索引、组合索引也是类似)
mysql> alter table book add index nameidx(name(30));
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 查看表中索引
mysql> show index from book\G
*************************** 1. row ***************************
Table: book
Non_unique: 1
Key_name: year_publication
Seq_in_index: 1
Column_name: year_publication
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: book
Non_unique: 1
Key_name: nameidx
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: 30
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
创建全文索引
mysql> create table t6(
-> id int not null,
-> info varchar(255)
-> )engine=myisam;
Query OK, 0 rows affected (0.05 sec)
mysql> alter table t6 add fulltext index infoIdx(info);
Query OK, 0 rows affected (0.17 sec)
创建空间索引
mysql> create table t7(
-> g geometry not null
-> )engine=myisam;
Query OK, 0 rows affected (0.05 sec)
mysql> alter table t7 add spatial index spatIdx(g);
Query OK, 0 rows affected (0.05 sec)
使用create index创建索引:
基本语法:
create [unique|fulltext|spatial] index index_name on table_name(col_name[length],...)[asc|desc]
先创建一张新表
mysql> create table book1(
-> bookid int not null,
-> bookname varchar(255) not null,
-> authors varchar(255) not null,
-> info varchar(255) null,
-> comment varchar(255) null,
-> year_publication year not null
-> );
Query OK, 0 rows affected (0.01 sec)
创建普通索引
mysql> create index bknameidx on book1(bookname);
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建唯一索引
mysql> create unique index uniqidIdx on book1(bookid);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建单列索引
mysql> create index bkcmIdx on book1(comment(50));
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建组合索引
mysql> create index bkAuAndInfoIdx on book1(authors(20),info(50));
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建全文索引
mysql> create table t8(
-> id int not null,
-> info varchar(255)
-> )engine=myisam;
Query OK, 0 rows affected (0.02 sec)
mysql> create fulltext index infoIdx on t8(info);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建空间索引
mysql> create table t9(
-> g geometry not null
-> )engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> create spatial index gIdx on t9(g);
Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
三、删除索引
1、使用alter table删除索引
基本语法:ALTER TABLE table_name DROP INDEX index_name;
2、使用drop index删除索引
基本语法:DROP INDEX index_name ON table_name;