索引在数据库中的作用是快速找出某个列中一个特定值的行,不使用索引的话,MySQL 必须从第一条记录遍历到相关行,表数据越大,花费的时间越多,但是如果有索引,就能快速的到达某个位置去搜索数据文件。
一、索引的优缺点
(一)、索引的优点:
通过创建唯一索引,可以保证数据库每一行数据的唯一性
可以大大提高查询速度
可以加速表与表的连接
可以显著的减少查询中分组和排序的时间。
(二)、索引的缺点
创建索引和维护索引需要时间,而且数据量越大时间越长
创建索引需要占据磁盘的空间,如果有大量的索引,可能比数据文件更快达到最大文件尺寸
当对表中的数据进行增加,修改,删除的时候,索引也要同时进行维护,降低了数据的维护速度
二、索引的分类及设计原则
(一)、索引的分类
普通索引(Normal):基本索引类型,允许在定义索引的列里插入空值或重复值。
唯一索引(Unique):索引列值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
主键索引:是一种特殊的唯一索引,不允许有空值。
单列索引:只包含一个列的索引,一个表中可以有多个。
组合索引:包含多个列的索引,查询条件包含这些列的最左边的字段的时候,索引就会被引用,遵循最左缀原则。
全文索引(Full Text):在定义的值中支持全文查找,允许空值和重复值,可以在 CHAR,VARCHAR 或者 TEXT 字段类型上创建,仅支持 MyISAM 存储引擎。
空间索引:针对空间数据做的索引,支持的数据类型有 4 种,分别是 GEOMETRY,POINT,LINESTRING 和 POLYGON。创建空间索引的列必须声明为非空值(NOT NULL),仅支持 MyISAM 存储引擎。
(二)、索引的设计原则
不是越多越好
常更新的表越少越好
数据量小的表最好不要建立索引
不同的值比较多的列才需要建立索引
某种数据本身具备唯一性的时候,建立唯一性索引,可以保证定义的列的数据完整性,以提高查询速度
频繁进行排序或分组的列(group by 或者是 order by)可以建立索引,提高搜索速度
经常用于查询条件的字段应该建立索引
三、索引的创建
(一)、在创建表的时候创建索引
在创建数据表时创建索引的基本语法结构:
CREATE TABLE table_name(
属性名 数据类型[约束条件],
……
属性名 数据类型
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[别名] (属性名 1 [(长度)] [ASC | DESC] , 属性名 2 [(长度)] [ASC | DESC] ,
...)
);
属性值的含义如下:
UNIQUE: 可选参数,表示索引为唯一索引。
FULLTEXT: 可选参数,表示索引为全文索引。
SPATIAL: 可选参数,表示索引为空间索引。
INDEX 和 KEY 参数用于指定字段为索引的,用户在选择时,只需要选择其中的一种即可。
“别名” : 为可选参数,其作用是给创建的索引取新名称。
属性名 1: 指索引对应的字段名称,该字段必须被预先定义。
长度: 可选参数,其指索引的长度,必须是字符串类型才可以使用。
ASC/DESC: 可选参数,ASC 表示升序排列,DESC 表示降序排列。
1、创建普通索引
创建普通索引,即不添加 UNIQUE、FULLTEXT 等任何参数。
【例】创建表名为 score 的数据表,并在该表的 id 字段上建立索引,SQL 语句如下:
mysql> CREATE table score(
-> id int(11) AUTO_INCREMENT primary key not null,
-> name varchar(50) not null,
-> math int(5) not null,
-> English int (5) not null,
-> Chinese int (5) not null,
-> index(id)
-> );
2、创建唯一索引
创建唯一索引时,使用 UNIQUE 参数进行约束。
【例】创建表名为 address 的数据表,并在该表的 id 字段上建立唯一索引,SQL 语句如下:
mysql> CREATE table address(
-> id int(11) auto_increment primary key not null,
-> name varchar(50),
-> address varchar(200),
-> UNIQUE INDEX address(id ASC)
-> );
3、创建全文索引
全文索引只能作用在 CHAR、VARCHAR、TEXT、类型的字段上。创建全文索引需要使用 FULLTEXT 参数进行约束。
【例】创建表名为 cards 的数据表,并在该表的 name 字段上建立全文索引,SQL 语句如下:
mysql> create table cards(
-> id int(11) auto_increment primary key not null,
-> name varchar(50),
-> number bigint(11),
-> info varchar(50),
-> FULLTEXT KEY cards_number(name)
-> );
4、创建单列索引
创建单列索引,即在数据表的单个字段上创建索引。创建该类型索引不需要引入约束参数,用户在建立时只需要指定单列字段名,即可创建单列索引。
【例】创建名称为 telephone 的数据表,并指定在 tel 字段上建立名称为 tel_num 的单列索引,SQL 语句如下:
mysql> create table telephone(
-> id int(11) primary key auto_increment not null,
-> name varchar(50) not null,
-> tel varchar(50) not null,
-> index (tel)
-> );
5、创建多列索引
创建多列索引即指定表的多个字段即可实现。
【例】创建名称为 information 的数据表,并指定 name 和 sex 为 多列索引,SQL 语句如下:
mysql> create table information(
-> inf_id int(11) auto_increment primary key not null,
-> name varchar(50) not null,
-> sex varchar(5) not null,
-> birthday varchar(50) not null,
-> index info(name,sex)
-> );
需要注意的是,在多列索引中,只有查询条件中使用了这些字段中的第一个字段(即上面示例中的 name 字段),索引才会被使用。
触发多列索引的条件是用户必须使用索引的第一字段,如果没有用到第一字段,则索引不起任何作用,用户想要优化查询速度,可以应用该类索引形式。
6、创建空间索引
创建空间索引时,需要设置 SPATIAL 参数。同样,必须说明的是,只有 MyISAM 类型表支持该类型索引。而且,索引字段必须有非空约束。
【例】创建一个名称为 list 的数据表,并创建一个名为 listinfo 的空间索引,SQL 语句如下:
mysql> create table list(
-> id int(11) primary key auto_increment not null,
-> goods geometry not null,
-> SPATIAL INDEX listinfo(goods)
-> )engine=MyISAM;
goods 字段上已经建立名称为 listinfo 的空间索引,其中 goods 字段必须不能为空,且数据类型是 GEOMETRY,该类型是空间数据类型。空间类型不能用其他类型代替,否则在生成空间素引时会产生错误且不能正常创建该类型索引。
空间类型除了上述示例中提到的 GEOMETRY 类型外,还包括如 POINT、LINESTRING、POLYGON 等类型,这些空间教据类型在平常的操作中很少被用到。
(二)、已存在表上创建索引
1、添加 PRIMARY KEY(主键索引):
ALTER TABLE table_name ADD INDEX index_name (column)
2、添加 UNIQUE(唯一索引) :
ALTER TABLE table_name ADD UNIQUE (column)
3、添加 INDEX(普通索引) :
ALTER TABLE table_name ADD INDEX index_name (column)
4、添加 FULLTEXT(全文索引) :
ALTER TABLE table_name ADD FULLTEXT (column)
5、添加多列索引:
ALTER TABLE table_name ADD INDEX index_name (column1,column2,column3)
四、查看索引是否引用
使用 EXPLAIN 查看索引是否使用(注意要先往表中插入相应的数据,至少两条)
EXPLAIN SELECT * FROM table_name WHERE column='value';
查询结果的字段详解:
select_type: 查询类型,可能的值:SIMPLE,PRIMARY,UNION,SUNQUERY 等
table: 表名,按被读取的先后顺序查询
partitions: 如果查询是基于分区表的话,会显示查询将访问的分区
type: 与其他表的关系,可能的值:ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右,性能从差到好)
possible_key: 搜索记录可选用的各个索引
key: 实际选用的索引
key_len: 索引按字节计算的长度,
ref: 行给出关联关系中另一个数据表里的数据列的名称
rows: 预计读出的行数
filtered: 按条件过滤的行的百分比
extra: 与关联操作相关的信息
五、总结
需要明白索引的优缺点,再选择需要用的索引
最好在建表前就确定每张表的索引字段