1.索引概述
建立索引的目的:加快数据库检索的速度。
mysql中索引主要分为:
- 普通索引:使用index或key关键字创建,其索引列值可以取空值或重复值。
- 唯一性索引:使用关键字UNIQUE创建,其索引列值不能重复,且必须唯一,但可以是空值。
- 主键索引:使用关键字primary key创建,主键索引是一种唯一性索引,但是其列值不能为空。
- 聚簇索引:聚簇索引的索引顺序就是数据存储的物理顺序。一个表只能有一个聚簇索引,且只有solidDB和InnoDB存储引擎才支持。
- 全文索引:只能创建在varchar或text的列上。且只支持MyISAM存储引擎。
- 单列索引:在某一个列上创建一个索引,一个表可以创建多个单列索引。
- 组合索引:在表的多个列上创建一个索引。比如在班级表tb_class的‘所属学院’和‘年级’上建立一个索引,此索引称为组合索引。索引遵循最前缀法则。即先按照第一列进行排序,当第一列值相同时再对第二列排序。
2.查看数据表上所建立的索引
# 语法格式: SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tb_name [{FROM | IN} db_name] # { | }:多选 # [ ]:可选
# 显示tb_score表上所有索引的信息 show index from tb_score \G; # \G:简化
索引结果:
- Table:指明索引所在表的名称。
- Non_name:该索引是否不是唯一性索引,1:不是,0:是
- Key_name:索引的名称。如果在创建索引时没有指明索引名,则系统会自动指定一个索引名。
- Column_name:建立索引的列名称。
- Collation:说明以何种顺序(升序或降序)索引。A:升序,NULL:无分类。
3.创建索引
3.1 索引create table语句创建索引
# 语法格式: create table tb_name[col_name data_type] [CONSTRAINT index_name] [UNIQUE] [INDEX | KEY] [index_name](index_col_name[length]) [ASC | DESC] # tb_name:建立索引的表名 # index_name:指定所建立的索引名称。 # UNIQUE:创建唯一性索引。 # index_col_name:指定要创建索引的列名。 # length:指定使用列的前length个字符创建索引。 # ASC | DESC:指定索引是按ASC还是DESC排序,默认为ASC。
# 创建新表的同时创建普通索引、唯一性索引 create table tb_score( studentNo char(10) not null UNIQUE, # studentNo字段建立唯一性索引 studentName varchar(20) not null, sex char(2) not null, birthday date, native varchar(20), nation varchar(10) default '汉', classNo char(6), index(studentName) # studentName 字段建立普通索引 );
# 创建新表的同时创建主键索引 create table tb_score( studentNo char(10), courseNo char(5), score float, constraint PK_score primary key (studentNo,courseNo), # studentNo、courseNo字段分别创建主键索引 constraint FK_score1 foreign key (studentNo) references tb_student (studentNo), constraint FK_score2 foreign key (courseNo) references tb_course (courseNo) );
3.2 使用create index语句创建索引
# 语法格式: create table [UNIQUE] INDEX index_name ON tb_name (col_name [(length)] [ASC | DESC]);
# 在tb_student表上创建一个普通索引,索引字段是studentNo create index index_stu ON tb_student(studentNo); # index_stu:索引名
在tb_course表上创建一个索引,要求按课程名称courseName字段值前三个字符建立降序索引。 create index index_course ON tb_course(courseName(3) DESC); # 对于字符类型排序,英文按照字母排序;中文在mysql中按照汉语拼音对应的英文字母顺序进行排序。
# 在tb_book上建立图书类别(ASC)和书名(DESC)的组合索引,索引名为index_book。 create index index_book ON tb_book(bclassNo ASC,bookName DESC);
3.3 使用alter table语句创建索引
# 语法格式: alter table tb_name add [UNIQUE | FULLTEXT] [INDEX | KEY] [index_name] (col_name[length] [ASC | DESC])
# 使用alter table 语句创建普通索引 alter table tb_student ADD INDEX idx_studentName(studentName);
4.删除索引
4.1 使用drop index语句删除索引
# 语法格式: drop index index_name ON tb_name # index_name:要删除的索引名 # tb_name:索引所在的表
# 删除tb_student表上的索引idx_studentName。 drop index idx_studentName ON tb_student;
4.2 使用alter table语句删除索引
# 语法格式: alter table tb_name drop index index_name
# 删除tb_student表上的索引index_stu alter table tb_student drop index index_stu;
5.使用索引的建议
- 索引可以提高数据查询的效率,但是过多的使用索引会影响数据更新的速度,不恰当的使用索引会降低系统性能。因此在操作频繁的数据表上避免过多的建立索引。
- 数据量小的表最好不要建立索引。
- 使用组合索引时,严格遵循最左前缀法则。
- 避免在不同值比较少的字段上建立索引,比如:性别。