索引及练习

1.索引

📖什么是索引？

1. 索引是对数据库一列或者多列的值进行排序的一种结构。

2. 索引的建立会大大提高 mysql 的检索速度。

3. 如果想高效的使用 mysql, 而且数据量大时，需要花费事件去设计索引，建立优秀的索引规

则，或优化 sql 语句

📖优缺点:

优点 :

1. 索引减少了服务器需要扫描的数据量，加快了检索的速度，这就是使用索引的主要原因

2. 通过创建唯一索引，可以保证数据表中每一行数据的唯一性

3. 索引可以帮助服务器避免排序和创建临时表

缺点 :

创建索引和维护索引，需要成本，随着数据量的增加成本需要增加。

它会占用物理空间，除了数据表占用的空间外，每一个索引都需要占用一定的物理空间，特

别是聚簇索引，更会占用大量空间

对数据表数据操作 ( 删除、修改、增加 ) 时，索引需要动态维护，降低了整个维护速度

如果具备大量重复数据时，没有建立索引的必要了。

对于非常小的表，没有必要

！！ mysql 5.5 及之后的版本，默认使用 innodb 引擎，因此默认会添加主键索引，底层为 B+Tree

📘分类:

1. 根据底层 : 聚簇索引 ( 主键 ) 和非聚簇索引 ( 其他索引 )---- 后续面试时，一定要冲刺这部分

聚簇索引按照数据存放的物理位置为顺序 --- 多行检索快

非聚簇索引对单行的检索特别快

2. 根据作用点 : 主键索引、唯一索引、单列索引、多列索引、全文索引等等

📖 索引的设计原则

1. 选择惟一性索引

2. 为经常需要排序、分组和联合操作的字段建立索引

3. 为常作为查询条件的字段建立索引

4. 限制索引的数目

5. 尽量使用数据量少的索引

6. 尽量使用前缀来索引

7. 删除不再使用或者很少使用的索引

📖 索引的使用

1. 查询索引

show index from 表名
show index from 表名\G --- 竖着显示详细信息

2. 创建索引

create index 索引名 on 表名(字段名) -- 1.创建普通索引 -单列索引
create unique index 索引名 on 表名(字段名) -- 2.创建唯一索引
create index 索引名 on 表名(字段1,字段n) -- 3.多列索引
create fulltext index 索引名 on 表名(字段名(只能为char\varchar\text)) -- 4.全
文索引
Create table index6( -- 5.空间索引
Id int,
Space geometry not null,
Spatial index index6_sp(space)
)engine=myisam;
建空间索引时，表的存储引擎必须是myisam类型，而且索引字段必须有非空约束。空间数据类型包
括
geometry,point,linestring和polygon类型等。平时很少用到。

3. 删除索引

drop index 索引名 on 表名

4. 执行计划 -- 看索引是否命中

explain 查询sql
1
EXPLAIN分析结果的含义:
table：这是表的名字。
type：连接操作的类型，ALL、index、range、 ref、eq_ref、const、system、NULL（从左到
右，性能从差到好）
possible_keys：可能可以利用的索引的名字
Key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。
key_len：索引中被使用部分的长度，以字节计。
ref：它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行
rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1
Extra：这里可能出现许多不同的选项，其中大多数将对查询产生负面影响

索引练习

题目 

学生表:Student(Sno，Sname，Ssex，Sage，Sdept) 学号，姓名，性别，年龄，所在系 Sno为主键

课程表:Course(Cno，Cname，) 课程号，课程名 Cno为主键

学生选课表:SC(Sno，Cno，Score) 学号，课程号，成绩 Sno,Cno为主键

1.用SQL语句创建学生表student，定义主键，姓名不能重名，性别只能输入男或女，所在系的默认值是“计算机”

2.修改student 表中年龄(age)字段属性，数据类型int 改变为smallint 的主键索引，索引名为SC_INDEX

3.为SC表建立按学号(sno)和课程号(cno)组合的升

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

实现过程

1. 创建 Student 表

CREATE TABLE Student (Sno CHAR(10) NOT NULL PRIMARY KEY, -- 学号为主键Sname VARCHAR(50) NOT NULL UNIQUE, -- 姓名不能重复Ssex ENUM('男', '女') NOT NULL, -- 性别限制为男或女Sage INT, -- 年龄Sdept VARCHAR(50) DEFAULT '计算机' -- 所在系默认值为计算机
);

2. 修改 Student 表中 Sage 字段的数据类型为 SMALLINT

ALTER TABLE Student
MODIFY COLUMN Sage SMALLINT;

3. 创建 Course 表

CREATE TABLE Course (Cno CHAR(10) NOT NULL PRIMARY KEY, -- 课程号为主键Cname VARCHAR(100) NOT NULL -- 课程名
);

4. 创建 SC 表并设置联合主键与索引

CREATE TABLE SC (Sno CHAR(10) NOT NULL, -- 学号Cno CHAR(10) NOT NULL, -- 课程号Score FLOAT, -- 成绩PRIMARY KEY (Sno, Cno), -- 联合主键INDEX SC_INDEX (Sno ASC, Cno ASC) -- 联合升序索引，索引名为 SC_INDEX
);

5. 创建视图 stu_info

CREATE VIEW stu_info AS
SELECT Student.Sname AS 姓名,Student.Ssex AS 性别,Course.Cname AS 课程名,SC.Score AS 成绩
FROM SC
JOIN Student ON SC.Sno = Student.Sno
JOIN Course ON SC.Cno = Course.Cno;