一、索引
通过观察有无索引查询的速度来了解索引
SQL语句
-- Active: 1714203732007@@127.0.0.1@3306@db_index
--1、创建测试表
create table test1(
id int,num int,pass varchar(50)
);
create table test2(
id int,num int,pass varchar(50),
index idIdx (id)
);
create table test3(
id int,num int,pass varchar(50)
);
--2、向表test1里插入300000条数据
CREATE PROCEDURE InsertTestData()
BEGIN
DECLARE v_counter INT DEFAULT 1;
WHILE v_counter <= 300000 DO
INSERT INTO test1 (id, num, pass) VALUES (v_counter, FLOOR(RAND() * 1000000), CONCAT('password', v_counter));
SET v_counter = v_counter + 1;
END WHILE;
END
-- 调用存储过程来插入数据
CALL InsertTestData();
--为test1添加索引
ALTER TABLE test1 ADD PRIMARY KEY (id);
-- 3、在有索引和没有索引的情况下执行查询
-- 1)没有创建索引时查询
-- 2)创建索引后再次查询
-- mysql>explain select * from test1 where id>=1 and id<=10000;
-- 4、在有索引和没有索引的情况下新增数据
-- 1)没有创建索引时插入数据
-- mysql> insert into test2 select * from test1;
-- 2)创建索引后再次插入数据
-- mysql> insert into test3 select * from test1;
测试截图
1,2.创建表后插入数据
没有创建索引时查询
创建索引后查询
对比两表后发现创建索引后,使用了优化查询。
二、索引与视图练习
SQL语句
-- Active: 1714203732007@@127.0.0.1@3306@db_stu_2
-- 学生表:Student (Sno, Sname, Ssex , Sage, Sdept)
-- 学号,姓名,性别,年龄,所在系 Sno为主键
-- 课程表:Course (Cno, Cname,)
-- 课程号,课程名 Cno为主键
-- 学生选课表:SC (Sno, Cno, Score)
-- 学号,课程号,成绩 Sno,Cno为主键
-- 1.用SQL语句创建学生表student,定义主键,姓名不能重名,性别只能输入男或女,所在系的默认值是 “计算机”。
CREATE TABLE Student (
Sno INT PRIMARY KEY,
Sname VARCHAR(50) UNIQUE NOT NULL,
Ssex CHAR(1) CHECK (Ssex IN ('男', '女')),
Sage SMALLINT,
Sdept VARCHAR(50) DEFAULT '计算机'
);
CREATE TABLE Course (
Cno INT PRIMARY KEY,
Cname VARCHAR(100) NOT NULL
);
CREATE TABLE SC (
Sno INT,
Cno INT,
Score DECIMAL(5,2), -- 假设成绩为带两位小数的十进制数
PRIMARY KEY (Sno, Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
-- 2.修改student 表中年龄(age)字段属性,数据类型由int 改变为smallint。
ALTER TABLE Student MODIFY COLUMN Sage SMALLINT;
-- 3.为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引,索引名为SC_INDEX 。
CREATE INDEX SC_INDEX ON SC (Sno ASC, Cno ASC);
-- 4.创建一视图 stu_info,查询全体学生的姓名,性别,课程名,成绩。
CREATE VIEW stu_info AS
SELECT
S.Sname,
S.Ssex,
C.Cname,
SC.Score
FROM
Student S
JOIN
SC ON S.Sno = SC.Sno
JOIN
Course C ON SC.Cno = C.Cno;
测试截图
创建student表
创建course表
创建sc表
查看视图