MySQL索引
索引是一种特殊的数据库结构,是提高数据库性能的重要方式,可以用来快速查询数据库表中的特定记录,MySQL中所有的数据类型都可以被索引。
我们来看一个数据表有索引和无索引时检索相同数据的时间。
首先看一下表的结构:
![运行结果](/assets/image-20221020232807036-OOyQ9Pig.png)
我们为num1添加了索引,num2没有。
然后我们查看里面的数据。
![运行结果](/assets/image-20221021131243865-5qisfKlB.png)
在此表中添加了两个字段在这两个字段中num1添加了索引,num2没有添加索引,还添加了2432358条数据行,0~2432357,我们分别使用where语句设置查询条件为字段1和字段2的某个值,分别看一下查询速度。
![运行结果](/assets/image-20221021132111603-XkwSvpbg.png)
可以看到使用索引后查询几乎可以秒出,但是不使用索引就会遍历整个表,非常耗费时间。
索引概述
索引分类
- 普通索引
普通索引不应用任何限制条件的索引,该索引可以在任何数据类型中创建。字段本身的约束条件可以判断其值是否为空或唯一。在某数据表的某一字段中创建该类型索引后,用户便可通过索引进行查询。
- 唯一索引
使用UNIQUE
参数可以设置索引为唯一索引。创建该索引时,索引的值必须唯一,通过唯一索引,用户可以快速定位某条记录,主键是一种特殊的唯一索引。
- 全文索引
使用FULLTEXT
参数可以设置索引为全文索引。全文索引只能在CHAR、VARCHAR、TEXT类型的字段上创建。查询数据量较大的字符串类型的字段时,使用全文索引可以提高查询速度。如,查询带有回复内容的字段,可以应用全文索引方式。需要注意的是,在默认情况下,应用全文搜索大小写不敏感。如果索引的列使用二进制排序,可以使用对大小写敏感的全文索引。
- 单列索引
顾名思义,单列索引即只对一个字段生效的索引,其可以包含上述3种索引方式。应用该索引时必须保证该索引的值对应一个字段。在添加单列索引时,若被索引的列为varchar、text等类型,则还可以为索引添加长度。
- 多列索引
多列索引实在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段,用户可以通过这几个字段进行查询。要想应用该索引,用户必须使用这些字段的第一个字段。多列索引在使用联合查询时会起到优化作用,但是它不能优化单独使用被索引的列满足特定条件的查询。多列建索引比对每个列分别建索引更有优势,因为索引建立得越多就越占磁盘空间,在更新数据的时候速度会更慢。另外建立多列索引时,顺序也是需要注意的,应该将严格的索引放在前面,这样筛选的力度会更大,效率更高。
- 空间索引
使用SPATIAL参数可以设置该索引为空间索引。空间索引只能建立在空间数据类型上,这样可以提高系统获取空间型数据的速度。MySQL中只有MyISAM存储引擎支持空间检索,而且索引的字段不能为空值。
创建索引
在创建数据表时创建索引
语法如下:
create table table_name(
属性名 数据类型[约束条件],
...,
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY
[别名] (属性1[(长度)][ASC|DESC]
);
INDEX和KEY参数用于指定字段索引,选择其中一个即可。
创建普通索引
create table score(
id int(11) not null auto_increment primary key,
name varchar(50) not null ,
math int(5) not null ,
chinese int(5) not null ,
english int(5) not null ,
index(id)
);
![运行结果 image-20221020190902387](/assets/image-20221020190902387-Mqx0RM6Q.png)
在最后一行可以看到,id已经被索引。
创建唯一索引
create table address(
id int(11) auto_increment primary key not null,
name varchar(50),
detail_address varchar(200),
unique index address(id)
);
![运行结果 image-20221020191251199](/assets/image-20221020191251199-WF954oJm.png)
在数据完整性约束里,若为字段设置主键约束,则MySQL会自动为该字段添加主键索引;若为字段设置候选键约束,则MySQL会自动为该字段设置唯一索引。
设置主键约束的部分语法为
constraint index_col_name primary key (col_name)
,其中constaint关键字表示下面的语句代表添加完整性约束,index_col_name代表主键的名称,col_name代表字段,如下面为表a中的字段a添加主键约束,由于MySQL会自动为该字段添加主键索引,因而运行结果如下图。下面是为字段b添加候选键约束的演示。
运行结果
创建全文索引
create table cards(
id int(11) auto_increment primary key not null,
name varchar(50),
number bigint(11),
info varchar(50),
fulltext key card_info(info)
)
![运行结果](/assets/image-20221020221129751-WVQDmKlv.png)
创建单列索引
create table telephone(
id int(11) primary key auto_increment not null,
name varchar(50) not null,
tel varchar(50) not null,
index tel_num(tel(20))
);
![运行结果](/assets/image-20221020221925247-XG3hOUtW.png)
创建多列索引
create table information(
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)
);
![运行结果](/assets/image-20221020222237665-JOyzDa1Y.png)
创建空间索引
create table list(
id int(11) primary key auto_increment not null,
goods geometry not null,
spatial index listinfo(goods)
);
![运行结果](/assets/image-20221020222437693-zOT6FGi3.png)
在已建立的数据表上创建索引
语法结构如下:
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(属性 [(length)])[ASC|DESC]);
创建普通索引
在创建普通索引之前,先来看一下student的表的结构。
![运行结果](/assets/image-20221020225502988-Cq3JO5mU.png)
create index id_index on student(id);
![运行结果](/assets/image-20221020225719879-PM5A7KaW.png)
创建唯一索引
语法:
CREATE UNIQUE INDEX 索引名 ON 数据表名称(字段名称);
我们在删除前面创建的索引之后为id添加一个唯一索引(删除索引的语句没有包含在下面的截图中):
create unique index id_uindex on student(id);
![运行结果](/assets/image-20221020230055729-XsjFwsrK.png)
创建全文索引
语法如下:
CREATE FULLTEXT INDEX 索引名 ON 数据表名称(字段名称);
我们在演示之前先创建一个如下的数据表:
![运行结果](/assets/image-20221020230409975-O9TVF78M.png)
create fulltext index info_index on information(info);
![运行结果](/assets/image-20221020230517526-XixPicmi.png)
创建单列索引
语法如下:
CREATE INDEX 索引名 ON 数据表名称(字段名称(长度));
设置字段名称长度,可以优化查询,提高查询效率。
我们为info中的information添加单列索引(先删除):
create index info_index on information(info(10));
![运行结果](/assets/image-20221020231246880-cGVYpwfs.png)
创建多列索引
语法如下:
CREATE INDEX 索引名 ON 数据表名称(字段1,...);
如我们为数据表student添加一个多列索引:
create index multi_index on student(name,age);
![运行结果](/assets/image-20221020230728059-BFHd4OjC.png)
创建空间索引
语法如下:
CREATE SPATIAL INDEX 索引名 ON 数据表名称(字段名称);
删除索引
若索引不使用也不删除,会既占用系统资源也降低表更新的速度。删除索引的语法如下:
DROP INDEX 索引名 ON 数据表名;
我们可以看到demo表中的字段num1存在我们之前创建过的索引,我们删除此索引。
![运行结果](/assets/image-20221021224831585-C_nQpe5W.png)
drop index num on demo;
![运行结果](/assets/image-20221021224951549-J6xqZrG6.png)
可以看到成功删除了索引。