数据库索引 ( 二 ) MySQL索引基本操作

春哥的魔法书

已于 2022-10-26 22:28:29 修改

阅读量656

点赞数

分类专栏： SQL与数据库文章标签：数据库 mysql 索引

于 2022-10-25 21:53:26 首次发布

本文链接：https://blog.csdn.net/yuanchun05/article/details/127522670

版权

SQL与数据库专栏收录该内容

25 篇文章 5 订阅

订阅专栏

2.数据库索引

2.1.索引概念

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构.

索引的实现通常使用BTree和变种的B+Tree（MySQL常用的索引就是B+Tree）。除了数据之外，数据库系统还维护为满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这种数据结构就是索引。

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

所以索引是快速查询，更新数据库中表数据的工具.

2.1.1.索引作用

通过创建索引，可以在查询的过程中，提高系统的性能
通过创建唯一性索引，可以保持数据库表中每一行数据的唯一性
在使用分组和排序子句进行数据检索时，可以减少查询中分组和排序的时间

2.1.2.缺点

创建索引和维护索引要耗费时间，而且时间随着数据量的增加而增大
索引需要占用物理空间，如果要建立聚簇索引，所需要的空间会更大
在对表中的数据进行增删改时需要耗费较多的时间，因为索引也要动态地维护

2.1.3.应该创建索引的场景

经常需要搜索的列上
作为主键的列上
经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度
经常需要根据范围进行搜索的列上
经常需要排序的列上
经常使用在where子句上面的列上

2.1.4.不应该创建索引的场景

查询中很少用到的列
对于那些具有很少数据值的列，比如数据表中的性别列，bit数据类型的列
对于那些定义为text，image的列，因为这些列的数据量相当大
当对修改性能的要求远远大于搜索性能时，因为当增加索引时，会提高搜索性能，但是会降低修改性能

2.2.索引类型

MySQL索引类型可以按不同纬度分为如下几种：

从应用层次划分：普通索引、唯一索引、主键索引、复合索引
从数据存储和索引键值逻辑关系划分：聚集索引和非聚集索引
从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引( 空间数据索引, 只支持geometry数据类型 )
从索引键值类型划分：主键索引、辅助索引（二级索引）

2.3.索引使用

2.3.1.删除索引

drop index <索引的名字> on 表名;

2.3.2.查看索引

show index from 表名;

2.3.2.1.实例

student 学生表 , 有 2000020 条记录

字段名称	字段类型	长度	小数点	字段说明	字段特征
student_id	int	11	0	主键	主键(主键)
student_name	varchar	10	0	名称	(全文索引)
student_enrollmenttime	datetime	0	0	入学时间	(函数索引)
student_tel	char	11	0	电话	(唯一索引)
education_id	int	11	0	学历	学历外键(外键索引)
student_weight	double	19	1	体重	(普通索引, 复合索引)
student_bloodtype	varchar	10	0	血型
student_sex	int	11	0	性别
student_height	int	11	0	身高	(复合索引)

执行索引查询

show index  from student;

显示的结果

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fL83nbIy-1666705796647)(数据库索引.assets/image-20221024143846121.png)]

结果说明:

当前表有两条索引, 分别是主键索引和外键索引

Table : 表的名称。

Non_unique : 如果索引不能包括重复词，则为0。如果可以，则为1。

Key_name : 索引的名称。

Seq_in_index : 索引中的列序列号，从1开始。

Column_name : 列名称。

Collation : 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。

Cardinality : 索引中唯一值的数目的估计值,通过运行ANALYZE TABLE或myisamchk -a可以更新,基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的,基数越大,当进行联合时,MySQL使用该索引的机会就越大.

Sub_part : 如果列只是被部分地编入索引,则为被编入索引的字符的数目,如果整列被编入索引,则为NULL.

Packed : 指示关键字如何被压缩,如果没有被压缩,则为NULL.

Null : 如果列含有NULL,则含有YES,如果没有,则该列含有NO.

Index_type : 索引的结构（BTREE / FULLTEXT / HASH / RTREE）.

Comment : 更多评注.

Index_comment : 索引描述

Visible : 是否显示

Expression : 函数表达式, 使用函数索引时用到

2.3.3.主键索引

它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。

创建主键索引的方法如下：

CREATE TABLE 表名 ( [...], PRIMARY KEY (字段名) );
ALTER TABLE 表名 ADD PRIMARY KEY (字段名);

2.3.3.1.实例

CREATE TABLE `student` (
  `student_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `student_name` varchar(10) DEFAULT NULL COMMENT '姓名',
  `student_enrollmenttime` datetime DEFAULT NULL COMMENT '入学时间',
  `student_tel` char(11) DEFAULT NULL COMMENT '电话 唯一',
  `education_id` int(11) DEFAULT NULL COMMENT '学历',
  `student_weight` double(19,1) DEFAULT NULL COMMENT '体重',
  `student_bloodtype` varchar(10) DEFAULT NULL COMMENT '血型', 
  `student_sex` int(11) DEFAULT NULL  COMMENT '性别 0 女  1 男', 
  `student_height` int(11) DEFAULT NULL COMMENT '身高', 
  PRIMARY KEY (`student_id`),
  KEY `fk_student_eduid` (`education_id`),
  CONSTRAINT `fk_student_eduid` FOREIGN KEY (`education_id`) REFERENCES `education` (`education_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2000021 DEFAULT CHARSET=utf8;

2.3.4.普通索引

指基于普通字段建立的索引。

建立索引的方法如下：

CREATE INDEX <索引的名字> ON 表名 (字段名);
ALTER TABLE 表名 ADD INDEX [索引的名字] (字段名);
CREATE TABLE 表名 ( [...], INDEX [索引的名字] (字段名) );

2.3.4.1.实例

无索引查询 student_weight(体重)= 96.72 的记录, 用时 102 ms

 Consume Time：12 ms 2022-10-24 14:55:24
 Execute SQL：select student_id,student_name,student_enrollmenttime, student_tel,stu.education_id,student_weight, student_bloodtype,student_sex , edu.education_name , case student_sex when 1 then '男' when 0 then '女' else '不清楚' end student_sex_name from student stu left join education edu on stu.education_id = edu.education_id WHERE student_weight = 96.7 LIMIT 10

2.3.4.2.创建索引

CREATE INDEX weight_index ON student (student_weight);

成功 : 用时 15.959s

[SQL]CREATE INDEX weight_index ON student (student_weight);
受影响的行: 0
时间: 15.959s

在这里插入图片描述

再测试

查询 student_weight (体重) = 197.2 , 用进 1 ms

 Consume Time：1 ms 2022-10-24 15:07:22
 Execute SQL：select student_id,student_name,student_enrollmenttime, student_tel,stu.education_id,student_weight, student_bloodtype,student_sex , edu.education_name , case student_sex when 1 then '男' when 0 then '女' else '不清楚' end student_sex_name from student stu left join education edu on stu.education_id = edu.education_id WHERE student_weight = 197.2 LIMIT 10

2.3.5.唯一索引

与“普通索引”类似，不同的是：索引字段的值必须唯一，但允许有空值null (可以有多个null)。

在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。

创建唯一索引的方法如下：

CREATE UNIQUE INDEX <索引的名字> ON 表名 (字段名);
ALTER TABLE 表名 ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE 表名 ( [...], UNIQUE [索引的名字] (字段名) ;

2.3.5.1.实例

无索引查询 student_tel (电话) = ‘13801236065’ 的记录, 用时 1422 ms

Consume Time：1422 ms 2022-10-24 15:10:18
 Execute SQL：select student_id,student_name,student_enrollmenttime, student_tel,stu.education_id,student_weight, student_bloodtype,student_sex , edu.education_name , case student_sex when 1 then '男' when 0 then '女' else '不清楚' end student_sex_name from student stu left join education edu on stu.education_id = edu.education_id WHERE student_tel = '13801236065' LIMIT 10

2.3.5.2.创建索引

CREATE UNIQUE INDEX tel_index ON student (student_tel);

成功 : 用时 19.995s

[SQL]CREATE UNIQUE INDEX tel_index ON student (student_tel);
受影响的行: 0
时间: 19.995s

在这里插入图片描述

再测试

查询 student_tel (电话) = ‘15302362900’ , 用进 1 ms

Consume Time：1 ms 2022-10-24 15:14:16
 Execute SQL：select student_id,student_name,student_enrollmenttime, student_tel,stu.education_id,student_weight, student_bloodtype,student_sex , edu.education_name , case student_sex when 1 then '男' when 0 then '女' else '不清楚' end student_sex_name from student stu left join education edu on stu.education_id = edu.education_id WHERE student_tel = '15302362900' LIMIT 10

2.3.6.函数索引(MySQL8新特性)

create index <索引的名字> on 表名 ( (函数表达式) )
alter table 表名 add key 索引的名字 ((  函数表达式 ));

特别注意括号的问题

最好是数据量少时就创建, 如果数据量非常大时,创建函数索引会非常慢

2.3.6.1.实例

由于 student_enrollmenttime(入学时间) 是 datetime 类型 (包含年月日时分秒)

但查询时只传入年月日结构 , 所以 sql 中要使用时间函数

无索引查询 date_format(student_enrollmenttime,‘%Y-%m-%d’) = ‘1991-06-03’ 的记录, 用时 1422 ms

 Consume Time：310 ms 2022-10-24 16:00:25
 Execute SQL：select student_id,student_name,student_enrollmenttime, student_tel,stu.education_id,student_weight, student_bloodtype,student_sex , edu.education_name , case student_sex when 1 then '男' when 0 then '女' else '不清楚' end student_sex_name from student stu left join education edu on stu.education_id = edu.education_id WHERE date_format(student_enrollmenttime,'%Y-%m-%d') = '1991-06-03' LIMIT 10

2.3.6.2.创建索引

create index date_index on student ( ( date_format(student_enrollmenttime,'%Y-%m-%d') ) );

成功 : 用时 22.655s

[SQL]create index date_index on student ( ( date_format(student_enrollmenttime,'%Y-%m-%d') ) );
受影响的行: 0
时间: 22.655s

在这里插入图片描述

再测试

查询 date_format(student_enrollmenttime,‘%Y-%m-%d’) = ‘2008-07-26’ , 用进 1 ms

 Consume Time：73 ms 2022-10-24 16:10:52
 Execute SQL：select student_id,student_name,student_enrollmenttime, student_tel,stu.education_id,student_weight, student_bloodtype,student_sex , edu.education_name , case student_sex when 1 then '男' when 0 then '女' else '不清楚' end student_sex_name from student stu left join education edu on stu.education_id = edu.education_id WHERE date_format(student_enrollmenttime,'%Y-%m-%d') = '2008-07-26' LIMIT 10

2.3.7.复合索引

单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。

创建组合索引的方法如下：

CREATE INDEX <索引的名字> ON 表名 (字段名1, 字段名2...);
ALTER TABLE 表名 ADD INDEX [索引的名字] (字段名1，字段名2...);
CREATE TABLE 表名 ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

复合索引使用注意事项：

何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。
如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

2.3.7.1.实例

先执行删除索引操作, 将 weight_index 索引删除

drop index weight_index on student;

可以查询观察发现没有这个索引没有

再进行查询

查询 student_weight (体重) = 41.9 and student_height (身高) = 187 的记录, 用时 3232ms

 Consume Time：3232 ms 2022-10-25 20:56:38
 Execute SQL：select student_id,student_name,student_enrollmenttime, student_tel,stu.education_id,student_weight, student_bloodtype,student_sex , student_height , edu.education_name , case student_sex when 1 then '男' when 0 then '女' else '不清楚' end student_sex_name from student stu left join education edu on stu.education_id = edu.education_id WHERE student_weight = 41.9 and student_height = 187 LIMIT 10

2.3.7.2.创建索引

CREATE INDEX weight_height_index ON student ( student_weight,student_height );

成功 : 用时 11.070s

[SQL]CREATE INDEX weight_height_index ON student ( student_weight,student_height );
受影响的行: 0
时间: 11.070s

在这里插入图片描述

再测试

查询 student_weight (体重) = 222.7 and student_height (身高) = 172 , 用进 0 ms

 Consume Time：0 ms 2022-10-25 21:07:42
 Execute SQL：select student_id,student_name,student_enrollmenttime, student_tel,stu.education_id,student_weight, student_bloodtype,student_sex , student_height , edu.education_name , case student_sex when 1 then '男' when 0 then '女' else '不清楚' end student_sex_name from student stu left join education edu on stu.education_id = edu.education_id WHERE student_weight = 222.7 and student_height = 172 LIMIT 10