导航【mysql高级】【java提高】

日星月云

1838人浏览 · 2022-08-14 14:22:56

日星月云 · 2022-08-14 14:22:56 发布

前言

暑假持续学习ing

mysql高级

讲师：尚硅谷-宋红康（江湖人称：康师傅）
官网 atguigu

概览

资源

思维导图

请添加图片描述

sql文件

01-索引的创建.sql

# 01-索引的创建

# 第一种：CREATE TABLE

#隐式的方式创建索引。在声明有主键约束、唯一性约束、外键约束的字段上，会自动的添加相关的索引。
CREATE DATABASE dbtest2;

USE dbtest2;

CREATE TABLE dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);


CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20) UNIQUE,
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);

#显示的方式创建：
#①创建普通的索引

CREATE TABLE book(
	book_id INT ,
	book_name VARCHAR(100),
	AUTHORS VARCHAR(100),
	info VARCHAR(100) ,
	COMMENT VARCHAR(100),
	year_publication YEAR,
	# 声明索引
	INDEX idx_bane(book_name)
);

#图形化界面查看，或命令行输出

#通过命令查看索引
#方式一
SHOW CREATE TABLE book;

#方式二
SHOW INDEX FROM book;

#查询索引效率提高
#性能分析工具
EXPLAIN SELECT * FROM book WHERE year_publication ='...';

#②创建唯一索引
# 声明有唯一索引的字段，在添加数据时，要保证唯一性，但是可以添加null
CREATE TABLE book1(
	book_id INT ,
	book_name VARCHAR(100),
	AUTHORS VARCHAR(100),
	info VARCHAR(100) ,
	COMMENT VARCHAR(100),
	year_publication YEAR,
	# 声明索引
	UNIQUE INDEX uk_idx_cmt(COMMENT)
);

SHOW INDEX FROM book1;

INSERT INTO book1(book_id,book_name,COMMENT)
VALUES(1,'Mysql高级','适合有数据库开发经验的人员学习');

SELECT * FROM book1;

INSERT INTO book1(book_id,book_name,COMMENT)
VALUES(2,'Mysql高级',NULL);

#再去执行，会报错
#INSERT INTO book1(book_id,book_name,COMMENT)
#VALUES(1,'Mysql高级','适合有数据库开发经验的人员学习');


#③ 主键索引
#通过定义主键约束的方式定义主键索引
CREATE TABLE book2(
	book_id INT PRIMARY KEY,
	book_name VARCHAR(100),
	AUTHORS VARCHAR(100),
	info VARCHAR(100) ,
	COMMENT VARCHAR(100),
	year_publication YEAR
);

SHOW INDEX FROM book2;

#通过删除主键约束的方式删除主键索引
ALTER TABLE book2
DROP PRIMARY KEY;

# ④ 创建单列索引
CREATE TABLE book3(
	book_id INT ,
	book_name VARCHAR(100),
	AUTHORS VARCHAR(100),
	info VARCHAR(100) ,
	COMMENT VARCHAR(100),
	year_publication YEAR,
	# 声明索引
	UNIQUE INDEX idx_bname(book_name)
);

SHOW INDEX FROM book3;

# ⑤ 创建联合索引
CREATE TABLE book4(
	book_id INT ,
	book_name VARCHAR(100),
	AUTHORS VARCHAR(100),
	info VARCHAR(100) ,
	COMMENT VARCHAR(100),`book4`
	year_publication YEAR,
	# 声明索引
	INDEX mul_bid_bname_info(book_id,book_name,info)
);

SHOW INDEX FROM book4;


#分析  最左前缀原则
EXPLAIN SELECT * FROM book4 WHERE book_id=1001 AND book_name='mysql';

#左边要有索引
EXPLAIN SELECT * FROM book4 WHERE book_name='mysql';


#⑥ 创建全文索引
CREATE TABLE test4(
	id INT NOT NULL,
	NAME CHAR(30) NOT NULL,
	age INT NOT NULL,
	info VARCHAR(255),
	FULLTEXT INDEX futxt_idx_info(info(50))
) ENGINE=MYISAM;

SHOW INDEX FROM test4;



#第2种：表已经创建成功

#① ALTER TABLE ... ADD ...
CREATE TABLE book5(
	book_id INT ,
	book_name VARCHAR(100),
	AUTHORS VARCHAR(100),
	info VARCHAR(100) ,
	COMMENT VARCHAR(100),
	year_publication YEAR
);

SHOW INDEX FROM book5;


ALTER TABLE book5 ADD INDEX idx_cmt(COMMENT);#创建普通索引

ALTER TABLE book5 ADD UNIQUE uk_idx_bname(book_name);#创建唯一性索引

ALTER TABLE book5 ADD INDEX mul_bid_bname_info(book_id,book_name,info);#创建联合索引


#② CREATE INDEX ... ON ...
CREATE TABLE book6(
	book_id INT ,
	book_name VARCHAR(100),
	AUTHORS VARCHAR(100),
	info VARCHAR(100) ,
	COMMENT VARCHAR(100),
	year_publication YEAR
);

SHOW INDEX FROM book6;

CREATE INDEX idx_cmt ON book6(COMMENT);

CREATE UNIQUE INDEX  uk_idx_bname ON book6(book_name);

CREATE INDEX mul_bid_bname_info ON book6(book_id,book_name,info);

02-索引的删除.sql

# 02-索引的删除
USE dbtest2;

SHOW INDEX FROM book5;

#方式1：ALTER TABLE .... DROP INDEX ....
ALTER TABLE book5 
DROP INDEX idx_cmt;

#方式2：DROP INDEX ... ON ...
DROP INDEX uk_idx_bname ON book5;

#测试：删除联合索引中的相关字段，索引的变化
ALTER TABLE book5
DROP COLUMN book_name;

ALTER TABLE book5
DROP COLUMN book_id;

ALTER TABLE book5
DROP COLUMN info;

03-mysql8.0 新特性.sql

#03-mysql8.0 新特性
#1.支持降序索引
USE dbtest2;
CREATE TABLE ts1(a INT,b INT,INDEX idx_a_b(a ASC,b DESC));
SHOW CREATE TABLE ts1;

DELIMITER //
CREATE PROCEDURE ts_insert()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i < 800
DO
INSERT INTO ts1 SELECT RAND()*80000,RAND()*80000;
SET i = i + 1;
END WHILE;
COMMIT;
END //
DELIMITER ;

#调用
CALL ts_insert();

SELECT COUNT(*) FROM ts1;

#优化测试
EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;

#不推荐
EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b DESC LIMIT 5;




#2.隐藏索引
#创建表时，隐藏索引
CREATE TABLE book7(
	book_id INT ,
	book_name VARCHAR(100),
	AUTHORS VARCHAR(100),
	info VARCHAR(100) ,
	COMMENT VARCHAR(100),
	year_publication YEAR,
	#创建不可见的索引
	INDEX idx_cmt(COMMENT) invisible
);

SHOW INDEX FROM book7;

EXPLAIN SELECT * FROM book7 WHERE COMMENT = 'mysql....';

#② 创建表以后
ALTER TABLE book7
ADD UNIQUE INDEX uk_idx_bname(book_name) invisible;
SHOW INDEX FROM book7;

CREATE INDEX idx_year_pub ON book7(year_publication);
SHOW INDEX FROM book7;

#没有加invisible
EXPLAIN SELECT * FROM book7 WHERE year_publication = '2022';
SHOW INDEX FROM book7;

#修改索引的可见性
ALTER TABLE book7 ALTER INDEX idx_year_pub invisible; #可见--->不可见
SHOW INDEX FROM book7;

ALTER TABLE book7 ALTER INDEX idx_cmt visible; #不可见 ---> 可见
SHOW INDEX FROM book7;

#了解：使隐藏索引对查询优化器可见

SELECT @@optimizer_switch \G

SET SESSION optimizer_switch="use_invisible_indexes=on";

EXPLAIN SELECT * FROM book7 WHERE year_publication = '2022';

04-索引的设计原则.sql

# 04-索引的设计原则

#1.数据的准备
CREATE DATABASE atguigudb1;

USE atguigudb1;

#第1步：创建学生表和课程表
CREATE TABLE `student_info` (
`id` INT(11) NOT NULL 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;

#第2步：创建模拟数据必需的存储函数

# 函数报错 1418
SELECT @@log_bin_trust_function_creators;

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 ;


#函数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 ;


# 第3步：创建插入模拟数据的存储过程

# 存储过程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 ;

# 第4步：调用存储过程
CALL insert_course(100);
CALL insert_stu(1000000);


#测试
SELECT COUNT(*) FROM course;		#100
SELECT COUNT(*) FROM student_info;	#1000000

#2.哪些情况适合创建索引

#① 字段的数值有唯一性的限制

#② 频繁作为 WHERE 查询条件的字段
#查看当前stduent_info表中的索引
SHOW INDEX FROM student_info;
#student_id字段上没有索引的：
SELECT course_id, class_id, NAME, create_time, student_id 
FROM student_info
WHERE student_id = 123110; #276ms

#给student_id字段添加索引
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);

#student_id字段上有索引的：
SELECT course_id, class_id, NAME, create_time, student_id 
FROM student_info
WHERE student_id = 123110; #43ms
#将作为where查询条件的字段student_id设为索引后查询效率提高了



#③ 经常 GROUP BY 和 ORDER BY 的列
#student_id字段上有索引的：
SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id LIMIT 100; #41ms

#删除idx_sid索引
DROP INDEX idx_sid ON student_info;

#student_id字段上没有索引的：
SELECT student_id, COUNT(*) AS num 
FROM student_info 
GROUP BY student_id LIMIT 100; #866ms


#再测试：
SHOW INDEX FROM student_info;



#添加单列索引
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);

ALTER TABLE student_info
ADD INDEX idx_cre_time(create_time);


# 报错1055 sql_mode  性能分析explain 只使用idx_sid索引
SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  #5.212s

#解决1055

#修改sql_mode

SELECT @@sql_mode;

SET @@sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

#添加联合索引
ALTER TABLE student_info
ADD INDEX idx_sid_cre_time(student_id,create_time DESC);

# 性能分析explain 使用联合索引
SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  #0.257s

#再进一步：
ALTER TABLE student_info
ADD INDEX idx_cre_time_sid(create_time DESC,student_id);

#两个联合索引，删除一个
DROP INDEX idx_sid_cre_time ON student_info;

#possible_keys: idx_sid,idx_cre_time_sid key:idx_sid
#使用了idx_sid索引 没有使用idx_cre_time_sid
EXPLAIN SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  #3.790s

#④ UPDATE、DELETE 的 WHERE 条件列
#查看，没有关于name的索引
SHOW INDEX FROM student_info;

UPDATE student_info SET student_id = 10002 
WHERE NAME = '462eed7ac6e791292a79';  #0.633s

#添加索引
ALTER TABLE student_info
ADD INDEX idx_name(NAME);

UPDATE student_info SET student_id = 10001 
WHERE NAME = '462eed7ac6e791292a79'; #0.001s

# ⑤ DISTINCT 字段需要创建索引
# 略


# ⑥ 多表 JOIN 连接操作时，创建索引注意事项
#首先，`连接表的数量尽量不要超过 3 张`，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

#其次，`对 WHERE 条件创建索引`，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

#最后，`对用于连接的字段创建索引`，并且该字段在多张表中的`类型必须一致`。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

#有索引idx_name
SHOW INDEX FROM student_info;

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

DROP INDEX idx_name ON student_info;

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 *
FROM student_info
WHERE student_id=10013 AND course_id=100;
# 频繁放左边

# 补充：在多个字段都要创建索引的情况下，联合索引优于单值索引
# 略

# 3.哪些情况不适合创建索引

# ①在where中使用不到的字段，不要设置索引
# 略

# ②数据量小的表最好不要使用索引
# 略

# ③有大量重复数据的列上不要建立索引
# 结论：当数据重复度大，比如 `高于 10%` 的时候，也不需要对这个字段使用索引

EXPLIAN的使用.sql

#1. table：表名
#查询的每一行记录都对应着一个单表
EXPLAIN SELECT * FROM s1;

#s1:驱动表  s2:被驱动表
EXPLAIN SELECT * FROM s1 INNER JOIN s2;

#2. id：在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
#-->通常出现了几个select关键字就对应几个id 
 SELECT * FROM s1 WHERE key1 = 'a';#id值1


 SELECT * FROM s1 INNER JOIN s2
 ON s1.key1 = s2.key1
 WHERE s1.common_field = 'a';#id值1


 SELECT * FROM s1 
 WHERE key1 IN (SELECT key3 FROM s2);#id值1、2


 SELECT * FROM s1 UNION SELECT * FROM s2;


 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
 
 EXPLAIN SELECT * FROM s1 INNER JOIN s2;#多表查询
 
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
 
 ######查询优化器可能对涉及子查询的查询语句进行重写,转变为多表查询的操作########
 #两个select却只出现一个id-->查询优化器将该语句转成了多表查询
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');
 
 #Union去重-->UNION取并集并去重时产生一个临时表
 #两个select出现3行记录
 EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
 
 #UNION ALL不用去重，只有两个id/行
 EXPLAIN SELECT * FROM s1  UNION ALL SELECT * FROM s2;
 
 
 
 #3. select_type：SELECT关键字对应的那个查询的类型,确定小查询在整个大查询中扮演了一个什么角色

 # 查询语句中不包含`UNION`或者子查询的查询都算作是`SIMPLE`类型
 EXPLAIN SELECT * FROM s1;

 
#连接查询也算是`SIMPLE`类型
EXPLAIN SELECT * FROM s1 INNER JOIN s2;

 
 #对于包含`UNION`或者`UNION ALL`或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个
 #查询的`select_type`值就是`PRIMARY`
 
 
 #对于包含`UNION`或者`UNION ALL`的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询
 #以外，其余的小查询的`select_type`值就是`UNION`
 
 #`MySQL`选择使用临时表来完成`UNION`查询的去重工作，针对该临时表的查询的`select_type`就是
 #`UNION RESULT`
 EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
 #临时表的select_type-->UNION RESULT

 EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

 
 #子查询：
 #如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是不相关子查询。
 #该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`SUBQUERY`
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
 
 #如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是相关子查询，
 #则该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`DEPENDENT SUBQUERY`
 EXPLAIN SELECT * FROM s1 
 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';
 #注意的是，select_type为`DEPENDENT SUBQUERY`的查询可能会被执行多次。

 
 #在包含`UNION`或者`UNION ALL`的大查询中，如果各个小查询都依赖于外层查询的话，那除了
 #最左边的那个小查询之外，其余的小查询的`select_type`的值就是`DEPENDENT UNION`。
 EXPLAIN SELECT * FROM s1 
 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');

 
 #对于包含`派生表`的查询，该派生表对应的子查询的`select_type`就是`DERIVED`
 EXPLAIN SELECT * 
 FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1;

 
 #当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，
 #该子查询对应的`select_type`属性就是`MATERIALIZED`
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); #子查询被转为了物化表
 
  
 # 4. partitions (可略) :匹配的分区信息
 
 # 5. type：针对单表的访问方法
 
 #当表中`只有一条记录`并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，
 #那么对该表的访问方法就是`system`。
 CREATE TABLE t(i INT) ENGINE=MYISAM;
 INSERT INTO t VALUES(1);
 
 #system-->性能最高的场景
 EXPLAIN SELECT * FROM t;
 #再添加 INSERT INTO t VALUES(1);
 #EXPLAIN SELECT * FROM t; 就会变成all 
 
 
 
 #换成InnoDB
 CREATE TABLE tt(i INT) ENGINE=INNODB;
 INSERT INTO tt VALUES(1);
 EXPLAIN SELECT * FROM tt;
 #all
 
 
 #当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是`const`
 EXPLAIN SELECT * FROM s1 WHERE id = 10005;

 EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;

 #all
 EXPLAIN SELECT * FROM s1 WHERE key3 = 10066;
 
 #在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的
 #（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则
 # 驱动表 all 对该被驱动表的访问方法就是`eq_ref`
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;


 #当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是`ref`
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

 #为什么 EXPLAIN SELECT * FROM s1 WHERE key3 = 10066; 是all 因为key3是varchar类型 10066需要函数转换，涉及函数是不会用到索引的
 #加上''就是 ref
 EXPLAIN SELECT * FROM s1 WHERE key3 = '10066';
  

 #当对普通二级索引进行等值匹配查询，该索引列的值也可以是`NULL`值时，那么对该表的访问方法
 #就可能是`ref_or_null`
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;
 
 
 #单表访问方法时在某些场景下可以使用`Intersection`、`Union`、
 #`Sort-Union`这三种索引合并的方式来执行查询   OR的并集关系使两个索引都将用到 `index_merge`
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
 
 #`unique_subquery`是针对在一些包含`IN`子查询的查询语句中，如果查询优化器决定将`IN`子查询
 #转换为`EXISTS`子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的`type`
 #列的值就是`unique_subquery`
 EXPLAIN SELECT * FROM s1 
 WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a';

 #如果使用索引获取某些`范围区间`的记录，那么就可能使用到`range`访问方法
 EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

 #同上
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';
 
 
 #当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是`index`  
 #索引覆盖  用到了联合索引idx_key_part
 EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
 
 
 #最熟悉的全表扫描 `all`
 EXPLAIN SELECT * FROM s1;
 
 #6. possible_keys和key：可能用到的索引 和  实际上使用的索引
 
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';
 
 
 
#7.  key_len：实际使用到的索引长度(即：字节数)
# 帮你检查`是否充分的利用上了索引`，`值越大越好`,主要针对于联合索引，有一定的参考意义。

 EXPLAIN SELECT * FROM s1 WHERE id = 10005;
# int 主键 4个字节

 EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;
# int 唯一索引可能为null 4+1=5

 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
# varchar 100 utf8 100*3  +1(null) +2(实践长度）

 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';
#303 

 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
#606

 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
#909 
 
 EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a';
#没有使用索引，null 
 
#练习：
#key_len的长度计算公式：
#varchar(10)变长字段且允许NULL  = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

#varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

#char(10)固定字段且允许NULL    = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)

#char(10)固定字段且不允许NULL  = 10 * ( character set：utf8=3,gbk=2,latin1=1)

 # 8. ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。
 #比如只是一个常数或者是某个列。
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
 
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
 
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);

 # 9. rows：预估的需要读取的记录条数
 # `值越小越好`
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';


 # 10. filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比
 
 #如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用
 #到对应索引的搜索条件外的其他搜索条件的记录有多少条。
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';
#越高越好，说明查的数据都是有效的
 
 #对于单表查询来说，这个filtered列的值没什么意义，我们`更关注在连接查询
 #中驱动表对应的执行计划记录的filtered值`，它决定了被驱动表要执行的次数(即：rows * filtered)
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';


# 11.Extra:一些额外的信息
# 更准确的理解MySQL到底将如何执行给定的查询语句

 #当查询语句的没有`FROM`子句时将会提示该额外信息 No tables used
 EXPLAIN SELECT 1;


#查询语句的`WHERE`子句永远为`FALSE`时将会提示该额外信息  Impossible WHERE
 EXPLAIN SELECT * FROM s1 WHERE 1 != 1;


 #当使用全表扫描来执行对某个表的查询，并且该语句的`WHERE`
 #子句中有针对该表的搜索条件时，在`Extra`列中会提示上述额外信息。 Using where
 EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';



 #当使用索引访问来执行对某个表的查询，并且该语句的`WHERE`子句中
 #有除了该索引包含的列之外的其他搜索条件时，在`Extra`列中也会提示上述额外信息。  USING WHERE
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
 
  #当查询列表处有`MIN`或者`MAX`聚合函数，但是并没有符合`WHERE`子句中
 #的搜索条件的记录时，将会提示该额外信息  No matching min/max row
 EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';  

 SELECT * FROM s1 LIMIT 10;

 EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'znOhuD'; #Ftcfsz 是 s1表中key1字段真实存在的数据

 #当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以
 #使用覆盖索引的情况下，在`Extra`列将会提示该额外信息 Using index 。比方说下边这个查询中只
 #需要用到`idx_key1`而不需要回表操作：
 EXPLAIN SELECT key1,id FROM s1 WHERE key1 = 'a';
 
 
 #Using index condition：查找使用了索引,但是需要回表查询数据
#有些搜索条件中虽然出现了索引列，但却不能使用到索引
 #看课件理解索引条件下推    Using index condition
 #先找key1 > 'z' 再找key1 LIKE '%a'，最后回表 *， 优化 key1 > 'z'  再回表 * 最后 key1 LIKE '%a'
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
 
 #在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为
 #其分配一块名叫`join buffer`的内存块来加快查询速度，也就是我们所讲的`基于块的嵌套循环算法`
 #见课件说明  Using where; Using join buffer (hash join)
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
 
  #当我们使用左（外）连接时，如果`WHERE`子句中包含要求被驱动表的某个列等于`NULL`值的搜索条件，
 #而且那个列又是不允许存储`NULL`值的，那么在该表的执行计划的Extra列就会提示`Not exists`额外信息  Using where; Not exists
 EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
 
 
 #如果执行计划的`Extra`列出现了`Using intersect(...)`提示，说明准备使用`Intersect`索引
 #合并的方式执行查询，括号中的`...`表示需要进行索引合并的索引名称；
 #如果出现了`Using union(...)`提示，说明准备使用`Union`索引合并的方式执行查询；
 #出现了`Using sort_union(...)`提示，说明准备使用`Sort-Union`索引合并的方式执行查询。  Using union(idx_key1,idx_key3); Using where
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
 
 #当我们的`LIMIT`子句的参数为`0`时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息 Zero limit
 EXPLAIN SELECT * FROM s1 LIMIT 0; 

#有一些情况下对结果集中的记录进行排序是可以使用到索引的。  NULL
 #比如：
 EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;

#很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）
 #进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：`filesort`）。
 
 #如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的`Extra`列中显示`Using filesort`提示 
 EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
 
  #在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们
 #在执行许多包含`DISTINCT`、`GROUP BY`、`UNION`等子句的查询过程中，如果不能有效利用索引来完成
 #查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行
 #计划的`Extra`列将会显示`Using temporary`提示
 EXPLAIN SELECT DISTINCT common_field FROM s1;
 
 
  #同上。
 EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
 
  #执行计划中出现`Using temporary`并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以
 #我们`最好能使用索引来替代掉使用临时表`。比如：扫描指定的索引idx_key1即可  Using index
 EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;
 
 #JSON格式的EXPLAIN
 EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 
WHERE s1.common_field = 'a';

05-explain 使用1.sql

EXPLAIN SELECT * FROM student_info;

SELECT * FROM student_info LIMIT 10;

DESCRIBE DELETE FROM student_info WHERE id=2;


USE `atguigudb1`;

#创建表

#表一
CREATE TABLE s1 (
	id INT AUTO_INCREMENT,
	key1 VARCHAR(100),
	key2 INT,
	key3 VARCHAR(100),
	key_part1 VARCHAR(100),
	key_part2 VARCHAR(100),
	key_part3 VARCHAR(100),
	common_field VARCHAR(100),
	PRIMARY KEY (id),
	INDEX idx_key1 (key1),
	UNIQUE INDEX idx_key2 (key2),
	INDEX idx_key3 (key3),
	INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

#表二
CREATE TABLE s2 (
	id INT AUTO_INCREMENT,
	key1 VARCHAR(100),
	key2 INT,
	key3 VARCHAR(100),
	key_part1 VARCHAR(100),
	key_part2 VARCHAR(100),
	key_part3 VARCHAR(100),
	common_field VARCHAR(100),
	PRIMARY KEY (id),
	INDEX idx_key1 (key1),
	UNIQUE INDEX idx_key2 (key2),
	INDEX idx_key3 (key3),
	INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;


SET GLOBAL log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

#创建存储函数
DELIMITER //
CREATE FUNCTION rand_string1(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 ;


#创建存储过程
DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s1 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s2 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;

#调用存储过程
CALL insert_s1(10001,10000);

CALL insert_s2(10001,10000);


SELECT COUNT(*) FROM s1;#10000

SELECT COUNT(*) FROM s2;#10000

06-索引优化-查询优化.sql

# 06-索引优化-查询优化
# 1. 数据准备

CREATE DATABASE atguigudb2;
USE atguigudb2;

#建表
CREATE TABLE `class` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`className` VARCHAR(30) DEFAULT NULL,
	`address` VARCHAR(40) DEFAULT NULL,
	`monitor` INT NULL ,
	PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`stuno` INT NOT NULL ,
	`name` VARCHAR(20) DEFAULT NULL,
	`age` INT(3) DEFAULT NULL,
	`classId` INT(11) DEFAULT NULL,
	PRIMARY KEY (`id`)
	#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


#让数据库服务器信任函数的创建，否则会报错
SET GLOBAL log_bin_trust_function_creators=1; # 不加global只是当前窗口有效



#随机产生字符串
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 ;
#假如要删除
#drop function rand_string;


#用于随机产生多少到多少的编号
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 ;
#假如要删除
#drop function rand_num;


#创建往stu表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_stu( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, NAME ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
#假如要删除
#drop PROCEDURE insert_stu;

#执行存储过程，往class表添加随机数据
DELIMITER //
CREATE PROCEDURE `insert_class`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO class ( classname,address,monitor ) VALUES
(rand_string(8),rand_string(10),rand_num(1,100000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
#假如要删除
#drop PROCEDURE insert_class;


#执行存储过程，往class表添加1万条数据
CALL insert_class(10000);


#执行存储过程，往stu表添加50万条数据
CALL insert_stu(100000,500000);


SELECT COUNT(*) FROM class;	#10000
	
SELECT COUNT(*) FROM student;	#500000

DELIMITER //
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name FROM
information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND
seq_in_index=1 AND index_name <>'PRIMARY' ;
#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=2 ;
#若没有数据返回,程序继续,并将变量done设为2
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @str = CONCAT("drop index " , _index , " on " , tablename );
PREPARE sql_str FROM @str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END //
DELIMITER ;

# 2.索引失效案例
#1）全值匹配我最爱
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';
#Empty set, 1 warning (0.13 sec)

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;

#分别为student表创建三个索引：
CREATE INDEX idx_age ON student(age);#索引一

CREATE INDEX idx_age_classid ON student(age,classId);#索引二

CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);#索引三

#显示student表上的索引
SHOW INDEX FROM student;

#再次执行有索引的语句一：
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;

#再次执行有索引的语句二：
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;


#再次执行有索引的语句三：
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

#全值匹配表示索引列和查询条件的字段全部匹配，精度高，key_len长度大
#结论：
#当创建多个索引时，查询优化器通常会选取和查询字段匹配度最高的索引
#因为匹配度越高，查询效率越快
#此时除被选中的索引外，其它索引失效



#2）最佳左前缀法则
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd' ;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.name = 'abcd';

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE classid=4 AND student.age=30 AND student.name = 'abcd'; 

# 3)主键插入顺序
#略

#4)计算、函数、类型转换(自动或手动)导致索引失效
CREATE INDEX idx_name ON student(NAME);#创建索引(NAME)

#此语句比下一条要好！（能够使用上索引）
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

#LEFT(student.name,3) = 'abc'; 中left函数的使用导致索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc'; 

#再举例
CREATE INDEX idx_sno ON student(stuno);#创建索引(stuno)

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

#再举例
EXPLAIN SELECT id, stuno, NAME FROM student WHERE SUBSTRING(NAME, 1,3)='abc';


#5)类型转换导致索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123; 

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = '123'; 


#6)范围条件右边的列索引失效
SHOW INDEX FROM student;

CALL proc_drop_index('atguigudb2','student');#清空所有student表的索引

CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);#创建联合索引 idx_age_classId_name

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ; 
/*Using index condition表示：有些搜索条件中虽然出现了索引列，但却不能使用到索引
#使用了索引 idx_age_classId_name但是只用了联合索引的前两个字段
# 结合`age` INT(3)占5 、 `classId` INT(11)占5 以及key_len=10可知只使用了前两个字段 
*/

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.name = 'abc' AND student.classId>20; 
/*对于优化器来说AND连接的这几个条件可以任意颠倒，故此SQL语句和上一句执行效果一样  联合索引classId在中间
*/
#建一个新的索引
CREATE INDEX idx_age_name_cid ON student(age,NAME,classId);

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.name = 'abc' AND student.classId>20; 
/*在新索引下三个字段都用上了
*/
#补充说明：
#对于优化器来说AND连接的字段先写哪个后写哪个无所谓
#具体使用了哪几个字段只和索引中定义字段的位置以及哪个字段使用了范围查询有关
#“范围条件右边的列”中的右-->是左是右要看索引中定义字段的相对位置，而不是字段在where中的位置


#7)不等于(!= 或者<>)索引失效
#不等于时用不上B+树，只能一个一个查找
CREATE INDEX idx_name ON student(NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;
#或
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;
/*索引失效
*/

#8）is null可以使用索引，is not null无法使用索引
#is null可以使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL; 
#is not null无法使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL; 

#9)like以通配符%开头索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%'; 
/*使用了索引
*/
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';


#10)OR 前后存在非索引的列，索引失效
SHOW INDEX FROM student;

CALL proc_drop_index('atguigudb2','student');

CREATE INDEX idx_age ON student(age);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
/*没有使用索引
*/
#为前后两个索引都创建索引，则OR连接他们时就可以使用索引
CREATE INDEX idx_cid ON student(classid);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
/*因为age字段和classid字段上都有索引，所以查询中使用了索引
能看到这里使用到了index_merge，简单来说index_merge就是对age和classid分别进行了扫描，然后将这两个结果集进行了合并。这样做的好处就是避免了全表扫描
*/

# 11）不同的字符集进行比较前需要进行 转换、会造成索引失效  数据库和表的字符集统一使用utf8mb4
#略


#3. 关联查询优化（多表查询）
# 数据准备
CREATE TABLE IF NOT EXISTS `type` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
#图书
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);

#向分类表中添加20条记录
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));

#向图书表中添加20条记录
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));



# 情况1：左外连接
#连接的时候就和“嵌套循环”一样
#每次从驱动表里选取一条记录去被驱动表里整个遍历一遍
#将符合连接条件的放到结果集中
#驱动表和被驱动表-->EXPLAIN执行结果的记录中，上面的是驱动表，下面的是被驱动表
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;


#添加索引优化

#给被驱动表加了索引可以避免全表扫描
ALTER TABLE book ADD INDEX Y ( card); #【被驱动表】，可以避免全表扫描

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

#给驱动表加了索引也要全表扫描
ALTER TABLE `type` ADD INDEX X (card); #【驱动表】，无法避免全表扫描

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;


DROP INDEX Y ON book;

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;


#情况2：内连接

DROP INDEX X ON TYPE;
DROP INDEX Y ON book;#（如果已经删除了可以不用再执行该操作）


EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card=book.card;


#添加索引
ALTER TABLE book ADD INDEX Y ( card);

EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;

ALTER TABLE TYPE ADD INDEX X (card);

EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;
#如果发现type和book交换了 是因为INNER JOIN 的两表地位一样，选取cost小的执行
#结论：对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的

#删除索引
#删除被驱动表book的索引，被驱动表book有可能是type

#或 DROP INDEX X ON `type`;
DROP INDEX Y ON book;
#结论：对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card=book.card;

#或 ALTER TABLE `type` ADD INDEX X (card);
ALTER TABLE `book` ADD INDEX Y (card);

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card=book.card;

#再向分类表中添加20条记录
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card=book.card;



#JOIN 底层原理

CREATE TABLE a(f1 INT,f2 INT,INDEX(f1))ENGINE=INNODB;

CREATE TABLE b(f1 INT,f2 INT)ENGINE=INNODB;

INSERT INTO a VALUES(1,1),(2,2),(3,3 ),(4,4),(5,5),(6,6);

INSERT INTO b VALUES(3,3 ),(4,4),(5,5),(6,6),(7,7),(8,8);

SELECT * FROM b;

#测试1
EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) WHERE (a. f2=b.f2);
# 优化成内连接了 b驱动表 a被驱动表
EXPLAIN SELECT * FROM a JOIN b ON(a.f1=b.f1) WHERE (a. f2=b.f2);

#测试2
EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) AND (a.f2=b.f2);
# a驱动表 b被驱动表

EXPLAIN SELECT * FROM a JOIN b ON(a.f1=b.f1) WHERE (a. f2=b.f2);

#4. 子查询的优化

#创建班级表中班长的索引
CREATE INDEX idx_monitor ON class(monitor);

#子查询
#查询班长的信息
EXPLAIN SELECT * FROM student stu1
WHERE stu1.`stuno` IN (
SELECT monitor
FROM class c
WHERE monitor IS NOT NULL
);

#推荐使用多表查询
EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c 
ON stu1.`stuno` = c.`monitor`
WHERE c.`monitor` IS NOT NULL;



#查询不为班长的学生信息
#方式一
EXPLAIN SELECT SQL_NO_CACHE a.* 
FROM student a 
WHERE  a.stuno  NOT  IN (
			SELECT monitor FROM class b 
			WHERE monitor IS NOT NULL) 

#方式二
EXPLAIN SELECT SQL_NO_CACHE a.*
FROM  student a LEFT OUTER JOIN class b 
ON a.stuno =b.monitor
WHERE b.monitor IS NULL;

#5. 排序优化
#删除student和class表中的非主键索引
CALL proc_drop_index('atguigudb2','student');
CALL proc_drop_index('atguigudb2','class');

SHOW INDEX FROM student;
SHOW INDEX FROM class;

#过程一：
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid; 

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10; 



#过程二：order by时不limit，索引失效
#创建索引  
CREATE  INDEX idx_age_classid_name ON student (age,classid,NAME);

#不限制,索引失效
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid; 

#EXPLAIN  SELECT SQL_NO_CACHE age,classid,name,id FROM student ORDER BY age,classid; 
#不回表，覆盖索引  Using Index

#增加limit过滤条件，使用上索引了。
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;  



#过程三：order by时顺序错误，索引失效
#创建索引age,classid,stuno
CREATE  INDEX idx_age_classid_stuno ON student (age,classid,stuno); 

#以下哪些索引失效?
EXPLAIN  SELECT * FROM student ORDER BY classid LIMIT 10;#失效

EXPLAIN  SELECT * FROM student ORDER BY classid,NAME LIMIT 10; #失效 

EXPLAIN  SELECT * FROM student ORDER BY age,classid,stuno LIMIT 10;#使用索引，使用了三个字段 

EXPLAIN  SELECT * FROM student ORDER BY age,classid LIMIT 10;##使用索引，使用了三个字段 

EXPLAIN  SELECT * FROM student ORDER BY age LIMIT 10;#使用索引，使用了三个字段 



#过程四：order by时规则不一致, 索引失效 （顺序错，不索引；方向反，不索引）

EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;#失效，没用使用索引

EXPLAIN  SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10;#失效，没用使用索引

EXPLAIN  SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10; #失效，没用使用索引

EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;#使用了索引 Backward index scan



#过程五：无过滤，不索引

EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid;#使用了索引,仅age字段

EXPLAIN  SELECT * FROM student WHERE  age=45 ORDER BY classid,NAME; #使用了索引,仅age字段

EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age;  #没使用了索引
  
EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age LIMIT 10;#使用了索引,用了所有字段

CREATE INDEX idx_cid ON student(classid);
EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age;#使用了索引idx_cid

#实战：测试filesort和index排序

CALL proc_drop_index('atguigudb2','student');


EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;
#0.121

#创建新索引
CREATE INDEX idx_age_name ON student(age,NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;
#0.039

CREATE INDEX idx_age_stuno_name ON student (age,stuno,NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;
#时间更短了 0.002

DROP INDEX idx_age_stuno_name ON student;

CREATE INDEX idx_age_stuno ON student(age,stuno);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;
# idx_age_stuno 0.002  



#6. 覆盖索引
#删除之前的索引
#举例1：
DROP INDEX idx_age_stuno ON student;

CREATE INDEX idx_age_name ON student (age,NAME);

EXPLAIN SELECT * FROM student WHERE age <> 20;

EXPLAIN SELECT age,NAME FROM student WHERE age <> 20;
/*使用了索引，打破了前面说的“不等于”的查询索引会失效的原则
原因：查询优化器发现使用索引时，不会回表，开销更小，故使用了索引
*/

#举例2：
EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';

EXPLAIN SELECT id,age FROM student WHERE NAME LIKE '%abc';




#7.索引条件下推（ICP）

#举例一：
USE atguigudb1;

#Using index condition
#先key1 > 'z' 再  key1 LIKE '%a' 最后回表  
#优于 先key1 > 'z' 再 回表   最后 key1 LIKE '%a'
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';


#举例二：
CREATE TABLE `people`(
	`id` INT NOT NULL AUTO_INCREMENT,
	`zipcode` VARCHAR (20) COLLATE utf8_bin DEFAULT NULL,
	`firstname`  VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
	`lastname` VARCHAR(2) COLLATE utf8_bin DEFAULT NULL,
	`address` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `zip_last_first`( `zipcode`, `lastname` , `firstname`)
)ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;


INSERT INTO `people` VALUES
( '1', '000001','三','张','北京市'),
( '2', '000002','四','李','南京市'),
( '3', '000003','五','王','上海市'),
( '4', '000001','六','赵','天津市');

#select * from people;

EXPLAIN SELECT * FROM people
WHERE zipcode= '000001'
AND lastname LIKE '%张%'
AND address LIKE '%北京市%';
#Using index condition; Using where

EXPLAIN SELECT * FROM people
WHERE zipcode= '000001'
AND lastname LIKE '%张%';
#Using index condition


SET optimizer_switch = 'index_condition_pushdown=off';

#Using where
EXPLAIN SELECT * FROM people
WHERE zipcode= '000001'
AND lastname LIKE '%张%'
AND address LIKE '%北京市%';

SET optimizer_switch = 'index_condition_pushdown=on' ;

#Using index condition; Using where


#创建存储过程，向people表中添加1000000条数据，测试ICP开启和关闭状态下的性能
DELIMITER //
CREATE PROCEDURE insert_people( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
	SET i = i + 1;
	INSERT INTO people ( zipcode , firstname, lastname, address ) VALUES ( '000001', '六','赵','天津市');
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END //

DELIMITER ;



CALL insert_people(1000000);

SELECT COUNT(*) FROM people;#1000004


SELECT UUID() FROM DUAL;

07-数据表的设计规范.sql

#07-数据表的设计规范

#反范式化的举例：

CREATE DATABASE atguigudb3;
USE atguigudb3;

#学生表
CREATE TABLE student(
	stu_id INT PRIMARY KEY AUTO_INCREMENT,
	stu_name VARCHAR(25),
	create_time DATETIME
);
`atguigudb3`
#课程评论表
CREATE TABLE class_comment(
	comment_id INT PRIMARY KEY AUTO_INCREMENT,
	class_id INT,
	comment_text VARCHAR(35),
	comment_time DATETIME,
	stu_id INT
);

###创建向学生表中添加数据的存储过程
DELIMITER //
CREATE PROCEDURE batch_insert_student(IN START INT(10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2017-01-01 00:00:00');
DECLARE date_temp DATETIME;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = DATE_ADD(date_temp,INTERVAL RAND( )*60 SECOND);
INSERT INTO student(stu_id, stu_name,create_time)
VALUES ((START+i), CONCAT ('stu_',i), date_temp) ;
UNTIL i = max_num
END REPEAT;
COMMIT;
END //

DELIMITER ;


#调用存储过程,学生id从10001开始，添加1000000条数据
CALL batch_insert_student(10000,1000000)



###创建向课程评论表中添加数据的存储过程

DELIMITER //
CREATE PROCEDURE batch_insert_class_comments(IN START INT(10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2018-01-01 00:00:00');
DECLARE date_temp DATETIME;
DECLARE comment_text VARCHAR(25);
DECLARE stu_id INT;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = DATE_ADD(date_temp,INTERVAL RAND( )*60 SECOND);
SET comment_text=SUBSTR(MD5(RAND()),1,20);
SET stu_id=FLOOR(RAND()*1000000);
INSERT INTO `class_comment`(`comment_id`,`class_id`,`comment_text`,`comment_time`,`stu_id`)
VALUES ((START+i),10001,comment_text,date_temp,stu_id);
UNTIL i = max_num
END REPEAT;
COMMIT;
END //

DELIMITER;

#添加数据的过程的调用，一个1000000条数据
CALL batch_insert_class_comments(10000,1000000);

#######
SELECT COUNT(*) FROM student;#1000000

SELECT COUNT(*) FROM class_comment;#1000000

###需求####
#0.014
SELECT p.comment_text, p.comment_time, stu.stu_name
FROM class_comment AS p LEFT JOIN student AS stu
ON p.stu_id = stu.stu_id
WHERE p.class_id = 10001
ORDER BY p.comment_id DESC
LIMIT 1000;


####进行反范式化的设计#####
#表的复制
CREATE TABLE class_comment1
AS
SELECT * FROM class_comment;

#添加主键，保证class_comment1与class_comment的结构相同
ALTER TABLE class_comment1
ADD PRIMARY KEY(comment_id);

SHOW INDEX FROM class_comment1;

#向课程评论表中增加stu_name字段
ALTER TABLE class_comment1
ADD stu_name VARCHAR(25);


#给新添加的字段赋值
UPDATE class_comment1 c
SET stu_name=(
SELECT stu_name
FROM student s
WHERE c.stu_id=s.stu_id
);

#查询同样的需求
#0.004
SELECT comment_text, comment_time, stu_name
FROM class_comment1
WHERE class_id = 10001
ORDER BY class_id DESC 
LIMIT 1000;

08-数据库的其他优化策略.sql

#08-数据库的其他优化策略
CREATE TABLE `user1` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL,
  `age` INT DEFAULT NULL,
  `sex` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;

#######
SET GLOBAL log_bin_trust_function_creators = 1;

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 ;

###
DELIMITER //
CREATE PROCEDURE  insert_user( max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO `user1` ( NAME,age,sex ) 
 VALUES ("atguigu",rand_num(1,20),"male");  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT; 
END //
DELIMITER;

##
CALL insert_user(1000);

SHOW INDEX FROM user1;
#idx_name cardinality 1是因为name都是atguigu

SELECT * FROM user1;

UPDATE user1 SET NAME = 'atguigu01' WHERE id = 2;
#更新一个还是idx_name cardinality 1

#分析表
ANALYZE TABLE user1;
#idx_name cardinality 2

UPDATE user1 SET NAME = 'atguigu03' WHERE id = 3;
ANALYZE TABLE user1;
SHOW INDEX FROM user1;#3



#检查表
CHECK TABLE user1;

#优化表
CREATE TABLE t1(id INT,NAME VARCHAR(15)) ENGINE = MYISAM;

OPTIMIZE TABLE t1;
#Table is already up to date

CREATE TABLE t2(id INT,NAME VARCHAR(15)) ENGINE = INNODB;

OPTIMIZE TABLE t2;
#Table does not support optimize, doing recreate + analyze instead

####
CREATE TABLESPACE atguigu1 ADD DATAFILE 'atguigu1.ibd' file_block_size=16k;


CREATE TABLE test(id INT,NAME VARCHAR(10)) ENGINE=INNODB DEFAULT CHARSET utf8mb4 TABLESPACE atguigu1;

ALTER TABLE test TABLESPACE atguigu1;

DROP TABLESPACE atguigu1;

DROP TABLE test;

09-事务的基础知识.sql

#09-事务的基础知识

#1.事务的完成过程
#步骤1：开启事务：
#步骤2：一系列的DML操作
#...
#步骤3：结束的状态：提交的状态（COMMIT）、中止的状态（ROLLBACK）

#2.显式事务

#2.1 如何开启？使用关键字：start transaction 或 begin

# start transaction 后面可以跟read only/read write（默认） / with consistent snapshot

#2.2 保存点（SAVEPOINT）


#3.隐式事务


# 3.1 关键字：autocommit
#set autocommit=false

SHOW VARIABLES LIKE 'autocommit';#ON

UPDATE account SET balance = balance - 10 WHERE id = 1; #此时这条DML操作是一个独立的事务

UPDATE account SET balance = balance + 10 WHERE id = 2; #此时这条DML操作是一个独立的事务

#3.2 如何关闭自动提交？
#方式1：
SET autocommit = FALSE; #针对于DML操作是有效的，对DDL操作是无效的。

UPDATE account SET balance = balance - 10 WHERE id = 1;

UPDATE account SET balance = balance + 10 WHERE id = 2; 

COMMIT; #或rollback;

#方式2：在autocommit为true的情况下，使用start transaction 或begin开启事务，那么DML操作就不会自动提交数据


START TRANSACTION;

UPDATE account SET balance = balance - 10 WHERE id = 1;

UPDATE account SET balance = balance + 10 WHERE id = 2; 

COMMIT; #或rollback;

#4.案例分析
#SET autocommit = TRUE; 
#举例1： commit 和 rollback

USE atguigudb2;
#情况1：
CREATE TABLE user3(NAME VARCHAR(15) PRIMARY KEY);

SELECT * FROM user3;

BEGIN;
INSERT INTO user3 VALUES('张三'); #此时不会自动提交数据
COMMIT;

BEGIN; #开启一个新的事务
INSERT INTO user3 VALUES('李四'); #此时不会自动提交数据
INSERT INTO user3 VALUES('李四'); #受主键的影响，不能添加成功
ROLLBACK;

SELECT * FROM user3;
/*
+--------+
| NAME   |
+--------+
| 张三   |
+--------+
*/

#情况2：
TRUNCATE TABLE user3;  #DDL操作会自动提交数据，不受autocommit变量的影响。

SELECT * FROM user3;

BEGIN;
INSERT INTO user3 VALUES('张三'); #此时不会自动提交数据
COMMIT;

INSERT INTO user3 VALUES('李四');# 默认情况下(即autocommit为true)，DML操作也会自动提交数据。
INSERT INTO user3 VALUES('李四'); #事务的失败的状态

ROLLBACK;

SELECT * FROM user3;
/*
+--------+
| NAME   |
+--------+
| 张三   |
| 李四   |
+--------+
*/

#情况3：
TRUNCATE TABLE user3;

SELECT * FROM user3;

SELECT @@completion_type;#NO_CHAIN 0

SET @@completion_type = 1;

BEGIN;
INSERT INTO user3 VALUES('张三'); 
COMMIT;


SELECT * FROM user3;

INSERT INTO user3 VALUES('李四');
INSERT INTO user3 VALUES('李四'); 

ROLLBACK;


SELECT * FROM user3;
/*
+--------+
| NAME   |
+--------+
| 张三   |
+--------+
*/

USE atguigudb3;
#举例2：体会INNODB 和 MyISAM

CREATE TABLE test1(i INT) ENGINE = INNODB;

CREATE TABLE test2(i INT) ENGINE = MYISAM;

#针对于innodb表
BEGIN
INSERT INTO test1 VALUES (1);
ROLLBACK;

SELECT * FROM test1;
#Empty set (0.00 sec)


#针对于myisam表:不支持事务
BEGIN
INSERT INTO test2 VALUES (1);
ROLLBACK;

SELECT * FROM test2;
#1

#举例3：体会savepoint

CREATE TABLE user3(NAME VARCHAR(15),balance DECIMAL(10,2));

BEGIN
INSERT INTO user3(NAME,balance) VALUES('张三',1000);
COMMIT;

SELECT * FROM user3;
/*
+--------+
| NAME   |
+--------+
| 张三   |
+--------+
*/

BEGIN;
UPDATE user3 SET balance = balance - 100 WHERE NAME = '张三';

UPDATE user3 SET balance = balance - 100 WHERE NAME = '张三';

SAVEPOINT s1;#设置保存点

UPDATE user3 SET balance = balance + 1 WHERE NAME = '张三';

ROLLBACK TO s1; #回滚到保存点


SELECT * FROM user3;
#801->800

ROLLBACK; #回滚操作

SELECT * FROM user3;
#1000

10-事务日志.sql

#10-事务日志
USE atguigudb3;
CREATE TABLE test_load(
a INT,
b CHAR(80)
)ENGINE=INNODB;

#创建存储过程，用于向test_load中添加数据
DELIMITER//
CREATE PROCEDURE p_load(COUNT INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(80)DEFAULT REPEAT('a',80);
WHILE s<=COUNT DO
INSERT INTO test_load SELECT NULL,c;
COMMIT;
SET s=s+1;
END WHILE;
END //
DELIMITER;

#测试1：
#设置并查看：innodb_flush_log_at_trx_commit

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

#set GLOBAL innodb_flush_log_at_trx_commit = 1;

#调用存储过程
CALL p_load(30000); #1min 28sec

#测试2：
TRUNCATE TABLE test_load;

SELECT COUNT(*) FROM test_load;

SET GLOBAL innodb_flush_log_at_trx_commit = 0;

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

#调用存储过程
CALL p_load(30000); #37.945 sec

#测试3：
TRUNCATE TABLE test_load;

SELECT COUNT(*) FROM test_load;

SET GLOBAL innodb_flush_log_at_trx_commit = 2;

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

#调用存储过程
CALL p_load(30000); #45.173 sec