前言
在数据库中创建索引可以提高数据检索的速度和效率。索引是一种数据结构,类似于书籍的目录,它可以帮助数据库系统快速定位和访问表中的特定数据行。
目录
一、索引相关介绍
1. 概述
索引就是一种帮助系统更快查找信息数据的集合表。
- 索引是一个排序的列表,储着索引的值和包含这个值的数据所在行的物理地址
- 无需全表扫描,访问相应的数据,能加快数据库的查询速度
- 索引是表中一列或者若干列值排序的方法
- 需要额外的磁盘空间
2. 作用
① 选择合适的索引,可以快速定位,加快查询
② 当表很大或多张表,可以成倍提高查询速度
③ 降低数据库的IO成本,还可以降低数据库的排序成本
④ 创建唯一(键)性索引,可以保证数据表中每一行数据的唯一性
⑤ 表之间连接加速
⑥ 使用分组和排序时,减少时间
3. 副作用
① 索引文件用于保存数据记录的地址需要占用磁盘空间
② 修改数据时更加费时,索引也要随之变动
4. 创建原则依据
索引虽可以提升数据库查询的速度,但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源,在有索引的情况下,数据库会先进行索引查询,然后定位到具体的数据行,如果索引使用不当,反而会增加数据库的负担。
① 表的主键、外键必须有索引
② 记录数超过300行的表应该有索引
③ 经常与其他表进行连接的表,在连接字段上应该建立索引
④ 经常出现在 where 子句中的字段,特别是大表的字段,应该建立索引
⑤ 索引应该建在选择性高的字段上
⑥ 索引应该建在小字段上
⑦ 唯一性太差的字段不适合建立索引
⑧ 更新太频繁地字段不适合创建索引
二、索引的分类与管理
这里准备一张表格和一些数据,以便进一步介绍索引的分类使用。
mysql> create table class (id int(6) not null,name char(20) not null,cardid varchar(10),phone varchar(11),address char(40),remark text);
mysql> insert into class values (1,'zhao',12345,11111,'nanjing','vip');
mysql> insert into class values (2,'qian',1234,22222,'nanjing','novip');
mysql> insert into class values (3,'sun',123,33333,'beijing','svip');
mysql> insert into class values (4,'li',12,44444,'shanghai','vip');
mysql> insert into class values (5,'zhou',321,55555,'huaian','ssvip');
mysql> insert into class values (6,'wu',222,666666,'yangzhou','ssvip');
mysql> insert into class values (7,'zhen',233,77777,'nanjing','vip');
mysql> select * from class;
+----+------+--------+--------+----------+--------+
| id | name | cardid | phone | address | remark |
+----+------+--------+--------+----------+--------+
| 1 | zhao | 12345 | 11111 | nanjing | vip |
| 2 | qian | 1234 | 22222 | nanjing | novip |
| 3 | sun | 123 | 33333 | beijing | svip |
| 4 | li | 12 | 44444 | shanghai | vip |
| 5 | zhou | 321 | 55555 | huaian | ssvip |
| 6 | wu | 222 | 666666 | yangzhou | ssvip |
| 7 | zhen | 233 | 77777 | nanjing | vip |
+----+------+--------+--------+----------+--------+
7 rows in set (0.00 sec)
1. 普通索引
1.1 简介
- 普通索引是最基本的索引类型,它没有唯一性限制,允许列中存在重复的值。
1.2 创建方法
① 直接创建索引
格式:
create index 索引名 on 表名 (列名[(length)]);
# (列名(length)):length是可选项。如果忽略 length 的值,则使用整个列的值作为索引。如果指定使用列前的 length 个字符来创建索引,这样有利于减小索引文件的大小。
# 索引名建议以“_index”结尾。
示例:
mysql> create index name_index on class (name);
mysql> select name from class; #实现索引
+------+
| name |
+------+
| li |
| qian |
| sun |
| wu |
| zhao |
| zhen |
| zhou |
+------+
7 rows in set (0.00 sec)
mysql> show create table class;
"id" int(6) NOT NULL,
"name" char(20) NOT NULL,
"cardid" varchar(10) DEFAULT NULL,
"phone" varchar(11) DEFAULT NULL,
"address" char(40) DEFAULT NULL,
"remark" text,
KEY "name_index" ("name") #已建立索引 name_index
② 修改表的方式创建
格式:
alter table 表名 add index 索引名 (列名);
示例:
mysql> alter table class add index cardid_index (cardid);
mysql> select cardid from class; #实现索引
+--------+
| cardid |
+--------+
| 12 |
| 123 |
| 1234 |
| 12345 |
| 222 |
| 233 |
| 321 |
+--------+
7 rows in set (0.00 sec)
mysql> show create table class;
KEY "name_index" ("name"),
KEY "cardid_index" ("cardid") #已建立索引 cardid_index
③ 创建表的时候指定
格式:
create table 表名 (字段1 数据类型,字段2 数据类型[,...],index 索引名 (列名));
示例:
mysql> create table test01 (id int(3) not null,name varchar(15),cardid char(20) not null,index id_index(id));
mysql> show create table test01; #实现索引
| test01 | CREATE TABLE "test01" (
"id" int(3) NOT NULL,
"name" varchar(15) DEFAULT NULL,
"cardid" char(20) NOT NULL,
KEY "id_index" ("id") #已建立索引 cardid_index
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
2. 唯一索引
2.1 简介
- 唯一索引要求索引列的值是唯一的,不允许重复值。
- 唯一索引允许有空值(注意和主键不同)。如果是用组合索引创建,则列值的组合必须唯一。添加唯一键将自动创建唯一索引。
2.2 创建方法
① 直接创建索引
格式:
create unique index 索引名 on 表名 (列名);
示例:
mysql> create unique index id_index on class (id);
mysql> show create table class;
UNIQUE KEY "id_index" ("id"), #已建立唯一键索引 id_index
KEY "name_index" ("name"),
KEY "cardid_index" ("cardid")
mysql> select id from class; #实现索引
+----+
| id |
+----+
| 4 |
| 3 |
| 2 |
| 1 |
| 6 |
| 7 |
| 5 |
+----+
7 rows in set (0.00 sec)
② 修改表方式创建
格式:
alter table 表名 add unique 索引名 (列名);
示例:
mysql> alter table class add unique phone_index (phone);
mysql> show create table class;
UNIQUE KEY "id_index" ("id"),
UNIQUE KEY "phone_index" ("phone"), #已建立唯一键索引 phone_index
KEY "name_index" ("name"),
KEY "cardid_index" ("cardid")
mysql> select phone from class; #实现索引
+--------+
| phone |
+--------+
| 11111 |
| 22222 |
| 33333 |
| 44444 |
| 55555 |
| 666666 |
| 77777 |
+--------+
7 rows in set (0.00 sec)
③ 创建表的时候指定
格式:
create table 表名 (字段1 数据类型[,...],unique 索引名 (列名));
示例:
mysql> create table test02 (id int,name char(6),unique id_index (id));
mysql> show create table test02;
UNIQUE KEY "id_index" ("id") #已建立唯一键索引 id_index
3. 主键索引
3.1 简介
- 主键索引是一种特殊的唯一索引,必须指定为“primary key”;不允许有 NULL 值,且每个表只能有一个主键索引。
3.2 创建方法
① 创建表的时候指定
格式:
create table 表名 ([...],primary key (列名));
示例:
mysql> create table test03 (id int,name char(6),primary key (id));
mysql> show create table test03;
PRIMARY KEY ("id") #已建立主键索引 id
② 修改表方式创建
格式:
alter table 表名 add primary key (列名));
示例:
mysql> alter table class add primary key (id);
mysql> show create table class;
PRIMARY KEY ("id"), #已建立主键索引 id
UNIQUE KEY "id_index" ("id"),
UNIQUE KEY "phone_index" ("phone"),
KEY "name_index" ("name"),
KEY "cardid_index" ("cardid")
mysql> select id from class; #实现索引
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+----+
7 rows in set (0.00 sec)
# 在 MySQL 中,一列可以创建多种类型的索引,但通常情况下,一列只能有一个主键索引,一个唯一索引,和多个普通索引或组合索引。
4. 组合索引
4.1 简介
- 组合索引是指将多个列组合起来创建索引(也可以单列),以提高多列条件查询的效率。
- 需要满足最左原则,因为select语句的 where条件是依次从左往右执行的,所以在使用select 语句查询时where条件使用的字段顺序必须和组合索引中的排序一致,否则索引将不会生效。
4.2 创建方法
创建格式:
create table 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,index 索引名 (列名1,列名2,列名3));
实现格式:
select 列名1,列名2,列名3 from 表名;
select * from 表名 where 列名1='...' and 列名2='...' and 列名3='...';
示例:
mysql> create table test04 (id int not null,name varchar(20),cardid varchar(20),index index_idname (id,name));
mysql> insert into test04 values (1,'zhangsan',111);
mysql> insert into test04 values (2,'lisi',222);
mysql> show create table test04;
"id" int(11) NOT NULL,
"name" varchar(20) DEFAULT NULL,
"cardid" varchar(20) DEFAULT NULL,
KEY "index_idname" ("id","name") #已建立组合索引 index_idname
mysql> select id,name from class; #实现索引,注意这里的字段顺序需要与创建格式顺序一致
+----+------+
| id | name |
+----+------+
| 4 | li |
| 2 | qian |
| 3 | sun |
| 6 | wu |
| 1 | zhao |
| 7 | zhen |
| 5 | zhou |
+----+------+
7 rows in set (0.01 sec)
5. 全文索引
5.1 简介
- 全文索引用于全文搜索,可以在文本数据中进行关键词内容搜索。
- 全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。
5.2 创建方法
① 直接创建索引
创建格式:
create fulltext index 索引名 on 表名 (列名);
实现全文索引格式:
select * from 表名 where 列名='查询内容';
select * from 表名 where match(列名) against('查询内容');
示例:
mysql> create fulltext index remark_index on class (remark);
mysql> show create table class;
PRIMARY KEY ("id"),
UNIQUE KEY "id_index" ("id"),
UNIQUE KEY "phone_index" ("phone"),
KEY "name_index" ("name"),
KEY "cardid_index" ("cardid"),
FULLTEXT KEY "remark_index" ("remark") #已建立全文索引 remark_index
mysql> select * from class where remark='vip'; #实现全文索引
或者
mysql> select * from class where match(remark) against('vip');
+----+------+--------+-------+----------+--------+
| id | name | cardid | phone | address | remark |
+----+------+--------+-------+----------+--------+
| 1 | zhao | 12345 | 11111 | nanjing | vip |
| 4 | li | 12 | 44444 | shanghai | vip |
| 7 | zhen | 233 | 77777 | nanjing | vip |
+----+------+--------+-------+----------+--------+
3 rows in set (0.00 sec)
② 修改表方式创建
格式:
alter table 表名 add fulltext 索引名 (列名);
示例:
mysql> alter table class add fulltext address_index (address);
mysql> show create table class;
PRIMARY KEY ("id"),
UNIQUE KEY "id_index" ("id"),
UNIQUE KEY "phone_index" ("phone"),
KEY "name_index" ("name"),
KEY "cardid_index" ("cardid"),
FULLTEXT KEY "remark_index" ("remark"),
FULLTEXT KEY "address_index" ("address") #已建立全文索引 address_index
mysql> select * from class where address='nanjing'; #实现全文索引
或者
mysql> select * from class where match(address) against('nanjing');
+----+------+--------+-------+---------+--------+
| id | name | cardid | phone | address | remark |
+----+------+--------+-------+---------+--------+
| 1 | zhao | 12345 | 11111 | nanjing | vip |
| 2 | qian | 1234 | 22222 | nanjing | novip |
| 7 | zhen | 233 | 77777 | nanjing | vip |
+----+------+--------+-------+---------+--------+
3 rows in set (0.00 sec)
③ 创建表的时候指定索引
格式:
create table 表名 (字段1 数据类型[,...],fulltext 索引名 (列名));
示例:
mysql> create table test05 (id int not null,name varchar(20),address varchar(20),fulltext address_index (address));
mysql> show create table test05;
"id" int(11) NOT NULL,
"name" varchar(20) DEFAULT NULL,
"address" varchar(20) DEFAULT NULL,
FULLTEXT KEY "address_index" ("address") #已建立全文索引 address_index
6. 查看索引与实现索引
查看索引
show index from 表名;
show index from 表名\G; 竖向显示表索引信息
show keys from 表名;
show keys from 表名\G;
show create table 表名;
实现索引:
select * from 表名;
select * from where 表达式;
select * from 表名 where match(列名) against('查询内容');
#全文索引,搜索的是内容
7. 删除索引
① 直接删除
格式:
drop index 索引名 on 表名;
示例:
mysql> show create table class; #查看 class 表索引
UNIQUE KEY "id_index" ("id"),
UNIQUE KEY "phone_index" ("phone"),
KEY "name_index" ("name"),
KEY "cardid_index" ("cardid"),
FULLTEXT KEY "remark_index" ("remark"),
FULLTEXT KEY "address_index" ("address")
mysql> drop index id_index on class; #删除唯一索引 id_index
mysql> show create table class;
PRIMARY KEY ("id"),
UNIQUE KEY "phone_index" ("phone"),
KEY "name_index" ("name"),
KEY "cardid_index" ("cardid"),
FULLTEXT KEY "remark_index" ("remark"),
FULLTEXT KEY "address_index" ("address")
② 修改方式删除
格式:
alter table 表名 drop index 索引名;
示例:
mysql> alter table class drop index name_index; #删除普通索引 name_index
PRIMARY KEY ("id"),
UNIQUE KEY "phone_index" ("phone"),
KEY "cardid_index" ("cardid"),
FULLTEXT KEY "remark_index" ("remark"),
FULLTEXT KEY "address_index" ("address")
③ 删除主键索引
格式;
alter table 表名 drop primary key;
示例:
mysql> alter table class drop primary key; #删除主键索引
mysql> show create table class;
UNIQUE KEY "phone_index" ("phone"),
KEY "cardid_index" ("cardid"),
FULLTEXT KEY "remark_index" ("remark"),
FULLTEXT KEY "address_index" ("address")
8. 字段含义
字段 | 含义 |
Table | 表的名称 |
Non_unique | 如果索引内容唯一,则为 0;如果可以不唯一,则为 1。 |
Key_name | 索引的名称。 |
Seq_in_index | 索引中的列序号,从 1 开始。 limit 2,3。 |
Column_name | 列名称。 |
Collation | 列以什么方式存储在索引中。在 MySQL 中,有值‘A’(升序)或 NULL(无分类)。 |
Cardinality | 索引中唯一值数目的估计值。 |
Sub_part | 如果列只是被部分地编入索引,则为被编入索引的字符的数目(zhangsan)。如果整列被编入索引,则为 NULL |
Packed | 指示关键字如何被压缩。如果没有被压缩,则为 NULL。 |
Null | 如果列含有 NULL,则含有 YES。如果没有,则该列含有 NO。 |
Index_type | 用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。 |
Comment | 备注 |
9. 总结
索引名称 | 键标识 | 说明 | 一张表索引数量 |
普通索引 | KEY | 针对所有字段,没有特殊的需求/规则 | 可多个 |
唯一(键)索引 | UNIQUE KEY | 针对唯一性的字段,仅允许出现一次空值 | 可多个 |
主键索引 | PRIMARY KEY | 针对唯一性字段、且不可为空,同时一张表只允许包含一个主键索引 | 一个 |
组合索引 | KEY | 多列/多字段组合形式的索引 | 可多个 |
全文索引 | FULLTEXT KEY | (varchar char text)全文搜索内容 | 可多个 |