MySQL之索引及简单运用

索引：

什么是索引

  索引是数据库中一种非常重要的数据结构，用于帮助快速查询数据库表中的数据。它就像一本书的目录，能够让你快速定位到书中的某个具体章节或内容，而不需要一页一页地翻阅整本书。

  在数据库管理系统中，索引是对数据库表中一列或多列的值进行排序的一种结构，可以极大地提高数据检索的效率，减少数据库的I/O成本。通过使用索引，数据库系统可以不必扫描整个表来定位所需的数据，而是可以直接通过索引快速定位到数据的物理存储位置。

索引的特点：

索引的作用

1. 提高数据检索效率：索引可以极大地加快数据检索的速度，特别是在处理大量数据时。
2. 保证数据的唯一性：通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
3. 实现数据的参照完整性：使用外键作为索引，可以保证数据的一致性和完整性。
4. 加速表与表之间的连接：在进行表的连接（JOIN）操作时，索引可以显著提高查询效率。
5. 在排序和分组时提供帮助：通过索引，数据库系统可以更快地对数据进行排序和分组。

 

索引的类型

• B树索引：最常见的索引类型，适用于全键值、键值范围或键值前缀查找。
• 哈希索引：基于哈希表实现，只有精确匹配索引所有列的查询才有效。
• 全文索引：用于在大量文本数据中搜索字符串。
• 空间索引：用于地理空间数据类型。
• 位图索引：适用于只有很少几种不同值的列，如性别、省份等。

索引的缺点

尽管索引可以显著提高查询效率，但它们也有一些缺点：

1. 占用额外空间：索引需要占用物理存储空间。
2. 降低写操作的性能：在表中插入、删除或更新数据时，索引也需要被相应地更新，这可能会降低写操作的性能。
3. 优化器负担：数据库优化器在生成查询计划时需要考虑索引，这可能会增加其负担。

 索引的创建查看与删除

 创建索引

方式1：创建表的时候创建索引

create table 表名 (               字段名1 数据类型 [完整性约束条件…],              

                                            字段名2 数据类型 [完整性约束条件…],              

                                                [unique | fulltext | spatial]   index | key              

                                                [索引名] (字段名[(长度)] [asc | desc])                 );

方式2：在已经存在的表上创建索引

create [unique | fulltext | spatial] index 索引名 ON 表名 ( 字段名[(长度)] [asc | desc] );  

方法3：使用alter table语句来创建索引

alter  table 表名 add [unique | fulltext | spatial] index                    

                                                 索引名 (字段名[(长度)] [asc | desc]);  

查看索引

# 查询索引 show create table 表名 \G

# 查询某张表中索引情况 show index from table_name;

删除索引

drop index 索引名 on 表名 

索引的小实例

 准备实验所需要的数据库和表

# 新建数据库

create database mydb15_indexstu;

# 使用该数据库

use mydb15_indexstu;

# 新建student表

create table student( sno int primary key auto_increment, sname varchar(30) not null unique, ssex varchar(2)check (ssex='男'or ssex='女')not null, sage int not null, sdept varchar(10)default'计算机'not nul1);

# 新建course表

create table course(cno int primary key not null, cname varchar(20) not null);

# 新建sc表

create table sc(sno int not null, cno varchar(10) primary key not null, score intnot null);

 处理该表

1.修改student 表中年龄(sage)字段属性，数据类型由int 改变为smallint

alter table student modify sage smallint not null;

2.为Course表中Cno 课程号字段设置索引,并查看索引

alter table course add index index_cno(cno);

show create table course\G

 

3.为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引，索引名为SC_INDEX 

CREATE UNIQUE INDEX SC_INDEX ON sc(sno, cno);

 4.创建一视图 stu info,查询全体学生的姓名，性别，课程名，成绩

create view stu_info as  select s.sname, s.ssex, c.cname, sc.score  from student s  join sc sc on s.sno = sc.sno  join course c on sc.cno = c.cno;

 5.删除所有索引

drop index index_cno on course;

drop index SC_INDEX on sc;

本次索引实验完成，索引更方便我们找到数据，可以大量节省运行时间，让我们更快速定位到想要的数据，本次索引不是很难，大家一定跟我一样都已经掌握了，敬请期待下一期吧！！！