索引及练习
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;