笔记-6：mysql索引

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

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);