数据的准备
#1. 数据的准备
CREATE DATABASE atguigudb1;
USE atguigudb1;
#1.创建学生表和课程表
CREATE TABLE `student_info` (
`id` INT(11) AUTO_INCREMENT,
`student_id` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`course_id` INT NOT NULL ,
`class_id` INT(11) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `course` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`course_id` INT NOT NULL ,
`course_name` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
SET GLOBAL log_bin_trust_function_creators = 1;
#函数1：创建随机产生字符串函数
DELIMITER //
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;
//SELECT @@log_bin_trust_function_creators;
#函数2：创建随机数函数
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;
# 存储过程1：创建插入课程表存储过程
DELIMITER //
CREATE PROCEDURE insert_course( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO course (course_id, course_name ) VALUES (rand_num(10000,10100),rand_string(6));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
# 存储过程2：创建插入学生信息表存储过程
DELIMITER //
CREATE PROCEDURE insert_stu( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student_info (course_id, class_id ,student_id ,NAME ) VALUES (rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
#调用存储过程：
CALL insert_course(100);
SELECT COUNT(*) FROM course;
CALL insert_stu(1000000);
SELECT COUNT(*) FROM student_info;
适合创建索引
情况1：字段数值具有唯一性
索引本身就具有约束的作用，比如唯一索引、主键索引都可以起到唯一约束作用，如果表里的某个字段每个值都是唯一的，可以直接创建唯一索引，快速确定这条记录位置.
阿里开发规范：业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引.
情况2：频繁用WHERE查询的字段
如果某个字段在WHERE条件中经常使用，可添加索引，尤其是数据量非常大的情况，可大幅提升效率.
举例：
有索引比没有索引只单次操作就快了几百ms
学生表中 student_id没有索引的情况下进行查询：
用了380ms
mysql> SELECT course_id, class_id, NAME, create_time, student_id
-> FROM student_info
-> WHERE student_id = 123110;
+-----------+----------+--------+---------------------+------------+
| course_id | class_id | NAME | create_time | student_id |
+-----------+----------+--------+---------------------+------------+
| 10035 | 10107 | yAfcWz | 2022-05-08 18:35:17 | 123110 |
| 10053 | 10168 | DXgLkU | 2022-05-08 18:35:47 | 123110 |
| 10015 | 10003 | bpvgwN | 2022-05-08 18:36:16 | 123110 |
| 10027 | 10178 | vkOphP | 2022-05-08 18:36:38 | 123110 |
+-----------+----------+--------+---------------------+------------+
4 rows in set (0.38 sec)
添加索引
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);
有索引的情况下进行查询，用了1ms:
+-----------+----------+--------+---------------------+------------+
| course_id | class_id | NAME | create_time | student_id |
+-----------+----------+--------+---------------------+------------+
| 10035 | 10107 | yAfcWz | 2022-05-08 18:35:17 | 123110 |
| 10053 | 10168 | DXgLkU | 2022-05-08 18:35:47 | 123110 |
| 10015 | 10003 | bpvgwN | 2022-05-08 18:36:16 | 123110 |
| 10027 | 10178 | vkOphP | 2022-05-08 18:36:38 | 123110 |
+-----------+----------+--------+---------------------+------------+
4 rows in set (0.01 sec)
情况3：经常GROUP BY 和 ORDER BY的列
索引就是将数据按照某种顺序进行存储，当我们使用分组（GROUP BY）或者排序（ORDER BY）查询时，如果添加了索引，等于已经提前分完组拍完序，直接拿数据即可
举例：
比如根据学生id对学生选修课程进行分组，相同课程的学生一组，最后返回数量
不创建索引，执行下面的语句：
SELECT student_id, COUNT(*) AS num FROM student_info GROUP BY student_id LIMIT 100;
用掉了710毫秒
100 rows in set (0.71 sec)
添加索引，再次调用相同的分组语句：
SELECT student_id, COUNT(*) AS num FROM student_info GROUP BY student_id LIMIT 100;
用掉了100毫秒
100 rows in set (0.00 sec)
情况4：UPDATE、DELETE的WHERER条件列
更新或删除记录时，需要先根据where找到这条记录,然后进行操作。如果对where条件中的字段添加了索引，会提高查询效率。
并且如果更新的字段是非索引字段，那么效率会再次提升， 因为更新非索引字段不需要对索引进行维护.
比如 update student set age=18 where id=2; 对id设置索引快速查询，对age不设置索引会快速更新.
没有索引的情况下： 1.98s
mysql> UPDATE student_info SET student_id = 10002
-> WHERE NAME = '462eed7ac6e791292a79';
Query OK, 0 rows affected (1.98 sec)
Rows matched: 0 Changed: 0 Warnings: 0
创建索引，并进行相同操作：0.04s 4ms
ALTER TABLE student_info
ADD INDEX idx_name(NAME);
mysql> UPDATE student_info SET student_id = 10001
-> WHERE NAME = '462eed7ac6e791292a79';
Query OK, 0 rows affected (0.04 sec)
Rows matched: 0 Changed: 0 Warnings: 0
情况5：DISTINCT去重时可添加索引
- 使用DISTINCT去除重复值时，添加索引可提高效率
- 索引会对数据进行排序，去重时重复数据可能会出现在一起，所以快了很多.
select distinct(student_id) from 'student_info';
运行0.683s;
添加索引后再次运行：
select distinct(student_id) from 'student_info';
运行时间0.01s
情况6：多表 JOIN时，添加索引
- 表的数量不要超过3个（多一个等于套一个循环，太多会非常低效）
- where字段添加索引，过滤数据
- 连接字段添加索引，并且类型一致
从0.227到0.001s
SELECT s.course_id, NAME, s.student_id, c.course_name
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE NAME = '462eed7ac6e791292a79'; #0.227s
SELECT s.course_id, NAME, s.student_id, c.course_name
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE NAME = '462eed7ac6e791292a79'; #0.001s
情况7：类型小的列适合创建索引
- 类型指数据范围的大小
- 创建索引时尽量使用类型小的列
- 类型越小，查询越快，占用的空间就少，单个数据页中能放下的记录就越多，磁盘IO损耗越低
- 主键必须选择类型小的列，因为聚簇和其他二级索引都会存一份主键值，主键越小，就会省出更多空间，更高效
情况8：字符串前缀创建索引
- 如果字符串很长，在储存时就是一个问题。
- 如果这个字符串被设为索引，在B+树中会占据太多的空间，不便于存储，也不便于比较
- 解决方法是截取字符串前一部分建立索引，也叫前缀索引。
- 定位到前缀相同的几个数据，然后根据主键回表查询数据，非常节省时间
ali规范：varchar上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
情况9：区分度高（散列性高）的列适合做索引
- 列的基数是指某列中不重复数据的个数。
- 当记录行数一定时，列的基数越大，列中的值越分散，列的基数越小，列中的值越集中
- 基数越大越分散，建立索引效果越好
情况10：使用最频繁的列放到联合索引的左侧
- 最左前缀原则，增加联合索引使用率
情况11：多个字段都需要建立索引时，联合优于单列
不适合创建索引
- 在where中用不到的字段（包括分组、排序这种）
- 数据量小的表最好不要使用索引
- 有大量重复数据的列上不要建立索引，当数据重复量过大，比如“高于10%”的时候，也不需要使用索引
- 避免对经常更新的表创建过多的索引
- 不建议用无序的值作为索引
- 删除不再使用或者很少使用的索引
- 不要定义冗余或者重复的索引
3267
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
