【MySQL高级篇】07-索引及调优篇

最新推荐文章于 2024-05-19 03:18:57 发布

开五档的蒙奇

最新推荐文章于 2024-05-19 03:18:57 发布

阅读量308

点赞数 4

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_43916081/article/details/136451154

版权

mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

第06章:索引的数据结构

6.1 为什么使用索引

索引是一种数据结构

6.2 索引及其优缺点

6.2.1 索引概述

6.2.2 优点

6.2.3 缺点

6.3 InnoDB中索引的推演

6.3.1 索引之前的查找

6.3.2 设计索引

6.3.2.1 一个简单的索引方案

6.3.2.2 InnoDB中的索引方案

默认数据页大小16KB=16000byte，假设一条数据记录占用160byte

假设所有存放用户记录的叶子节点代表的数据页存放100条用户记录；所有存放目录项的数据页可以存放1000条目录项记录

如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录。

如果B+树有2层，最多能存放 1000×100=10,0000 条记录。

如果B+树有3层，最多能存放 1000×1000×100=1,0000,0000 条记录。

如果B+树有4层，最多能存放 1000×1000×1000×100=1000,0000,0000 条记录。相当多的记录！！！

你的表里能存放 1000,0000,0000（一千亿）条记录吗？所以一般情况下，我们用到的B+树都不会超过4层，那我们通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页），又因为在每个页面内有所谓的 Page Directory （页目录），所以在页面内也可以通过二分法实现快速定位记录。

6.3.3 常见索引概念

6.3.3.1 聚簇索引

InnoDB中数据和索引都存储在.ibd文件中；MyISAM中数据和索引分别存储在.myd和.myi文件中，不包含聚簇索引。

6.3.3.2 二级索引(辅助索引、非聚簇索引)

6.3.3.3 联合索引

6.3.4 InnoDB的B+树索引的注意事项

6.3.4.1 根页面位置万年不动

6.3.4.2 内节点中目录项记录的唯一性

6.3.4.3 一个页面最少存储2条记录

6.4 MyISAM中的索引方案

MySQL官方中写的B-Tree，可以理解为就是B+Tree(官方没有说B树的概念，国内一般会补充B树和B+树作对比)

6.4.2 MyISAM索引的原理

6.4.3 MyISAM与InnoDB对比

6.5 索引的代价

6.6 MySQL数据结构选择的合理性

MySQL衡量查询效率的标准就是磁盘IO次数

6.6.1 全表遍历

这都懒得说了。（从头到尾顺序查找）

6.6.2 Hash结构

哈希函数h有可能将两个不同的关键字映射到相同的位置，这就叫做碰撞，在数据库中一般采用链接法来解决。在链接法中，将散列到同一槽位的元素放在一个链表中，如下图所示

Redis存储的核心就是Hash表

记住：

Hash索引对于等值判断效果比较好，MySQL中的Memory存储引擎支持Hash存储。当字段的重复度低，而且经常需要进行等值查询的时候，采用Hash索引是个不错的选择。

6.6.3 二叉搜索树

6.6.4 AVL树

6.6.5 B-Tree

6.6.6 B+Tree

思考题（重点）

6.6.7 R树

6.6.8 小结

B+树和B树的区别；B+树的索引和Hash索引的区别

基于B+树的结构可以谈索引的优化问题

6.6.9附录：算法的时间复杂度

第07章: InnoDB数据存储结构

7.1 数据库的存储结构：页

7.1.1 磁盘与内存交互基本单位：页

7.1.2 页结构概述

7.1.3 页的大小

7.1.4 页的上层结构

7.2 页的内部结构

7.2.1 第1部分:File Header(文件头部)和File Trailer(文件尾部)

7.2.1.1 File Header(文件头部)

校验和中，很长的字节串可以替换成页。比较两个页是否相等，可以给两个页都提供一个校验和。如果校验和相等，表示两个页相同；反之两个页不同。

7.2.1.2 File Ttailer(文件尾部)

7.2.2 第2部分:User Records(用户记录)、最大最小记录、Free Space(空闲空间)

next_record非常重要，B+树中的叶子节点的单链表靠它实现。该属性保存的是从当前记录的真实数据到下一条记录的真实数据的地址偏移量

7.2.3 第3部分:Page Directory(页目录)、Page Header(页面头部)

7.2.4 从数据页的角度看B+树如何查询

7.3 InnoDB行格式(或记录格式)

学习行格式主要以COMPACT行格式为主，在此基础上关注Dynamic和Compressed行格式，Redundant几乎可以忽略。

COMPACT：紧凑的；紧密的 Dynamic：动态的

Compressed：（被）压缩的 Redundant：冗余的

7.3.1 指定行格式的语法

7.3.2 COMPACT行格式

7.3.2.1 变长字段长度列表

7.3.2.2 NULL值列表

7.3.2.3 记录头信息

7.3.2.4 记录的真实数据

7.3.3 Dynamic和Compressed行格式

7.3.3.1 行溢出

CHARSET=ASCII，一个字符=1个字节，则最多65532个字符；如果有NOT NULL，最多65533个字符

7.3.3.2 Dynamic和Compressed行格式

7.3.4 Redundant行格式

7.3.4.1 字段长度偏移列表

7.3.4.2 记录头信息（record header）

7.4 区、段与碎片区

7.4.1 为什么要有区？

7.4.2 为什么要有段？

7.4.3 为什么要有碎片区？

7.4.4 区的分类

7.5 表空间

7.5.1 独立表空间

7.5.2 系统表空间

7.6 附录：数据页加载的三种方式

第08章:索引的创建与设计原则

8.1 索引的声明与使用

8.1.1 索引的分类

8.1.2 创建索引

8.1.2.1 创建表的时候创建索引

# 01-索引的创建

#第1种：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_bname(book_name)
);

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

#方式2：
SHOW INDEX FROM book;

#性能分析工具：EXPLAIN
EXPLAIN SELECT * FROM book WHERE book_name = 'mysql高级';

#② 创建唯一索引
# 声明有唯一索引的字段，在添加数据时，要保证唯一性，但是可以添加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高级','适合有数据库开发经验的人员学习');

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

SELECT * FROM book1;

#③ 主键索引
#通过定义主键约束的方式定义主键索引
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),
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))
)

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);

8.1.2.2 在已经存在的表上创建索引

8.1.3 删除索引

# 02-索引的删除

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;

8.2 MySQL8.0索引新特性

8.2.1 支持降序索引

# 03-MySQL8.0新特性
#1. 支持降序索引
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;

8.2.2 隐藏索引

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

CREATE INDEX idx_year_pub ON book7(year_publication);

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

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

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

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

SELECT @@optimizer_switch \G

SET SESSION optimizer_switch="use_invisible_indexes=on";

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

8.3 索引的设计原则

8.3.1 数据准备

#04-索引的设计原则

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

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

SET GLOBAL log_bin_trust_function_creators = 1;

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

8.3.2 哪些情况适合创建索引

8.3.2.1 字段的数值有唯一性的限制

8.3.2.2 频繁作为WHERE查询条件的字段

#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

8.3.2.3 经常GROUP BY和ORDER BY的列

#③ 经常 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);


SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  #5.212s

#修改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);

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;

SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC 
LIMIT 100;  #3.790s

8.3.2.4 UPDATE、DELETE的WHERE条件列

#④ UPDATE、DELETE 的 WHERE 条件列
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

8.3.2.5 DISTINCT字段需要创建索引

8.3.2.6 多表JOIN连接操作时，创建索引注意事项

# ⑤ DISTINCT 字段需要创建索引

# ⑥ #### 多表 JOIN 连接操作时，创建索引注意事项

#首先，`连接表的数量尽量不要超过 3 张`，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

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

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


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

8.3.2.7 使用列的类型小的创建索引

8.3.2.8 使用字符串前缀创建索引

8.3.2.9 区分度高(散列性高)的列适合作为索引

8.3.2.10 使用最频繁的列放到联合索引的左侧

8.3.2.11 在多个字段都要创建索引的情况下,联合索引优于单值索引

#⑦使用列的类型小的创建索引

#⑧使用字符串前缀创建索引

#⑨ 区分度高(散列性高)的列适合作为索引

#⑩ 使用最频繁的列放到联合索引的左侧
SELECT *
FROM student_info
WHERE student_id = 10013 AND course_id = 100;

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

8.3.3 限制索引的数目

8.3.4 哪些情况不适合创建索引

# 3. 哪些情况不适合创建索引
# ① 在where中使用不到的字段，不要设置索引

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

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

#④ 避免对经常更新的表创建过多的索引

#⑤ 不建议用无序的值作为索引

# ⑥ 删除不再使用或者很少使用的索引

# ⑦ 不要定义冗余或重复的索引

8.3.4.1 在where中使用不到的字段,不要设置索引

8.3.4.2 数据量小的表最好不要使用索引

8.3.4.3 有大量重复数据的列上不要建立索引

8.3.4.4 避免对经常更新的表创建过多的索引

8.3.4.5 不建议用无序的值作为索引

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

8.3.4.7 不要定义冗余或重复的索引

8.3.5 小结

第09章:性能分析工具的使用

9.1 数据库服务器的优化步骤

9.2 查看系统性能参数

9.3 统计SQL的查询成本:last_query_cost

9.4 定位执行慢的SQL:慢查询日志

9.4.1 开启慢查询日志参数

9.4.2 查看慢查询数目

9.4.3 案例演示

9.4.4 测试及分析

9.4.5 慢查询日志分析工具:mysqldumpslow

9.4.6 关闭慢查询日志

9.4.7 删除慢查询日志

9.5 查看SQL执行成本:SHOW PROFILE

9.6 分析查询语句:EXPLAIN

9.6.1 概述

9.6.2 基本语法

9.6.3 数据准备

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;

#创建存储函数：
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 ;

SET GLOBAL log_bin_trust_function_creators=1;

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

SELECT COUNT(*) FROM s2;

9.6.4 EXPLAIN各列作用

9.6.4.1 table

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

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

9.6.4.2 id

#2. id：在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
 SELECT * FROM s1 WHERE key1 = 'a';


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


 SELECT * FROM s1 
 WHERE key1 IN (SELECT key3 FROM s2);


 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';
 
 ######查询优化器可能对涉及子查询的查询语句进行重写,转变为多表查询的操作########
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');
 
 #Union去重
 EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
 
 
 EXPLAIN SELECT * FROM s1  UNION ALL SELECT * FROM s2;

9.6.4.3 select_type

#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;
 
 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); #子查询被转为了物化表

9.6.4.4 partitions(可略)

9.6.4.5 type★

# 5. type：针对单表的访问方法
 
 #当表中`只有一条记录`并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，
 #那么对该表的访问方法就是`system`。
 CREATE TABLE t(i INT) ENGINE=MYISAM;
 INSERT INTO t VALUES(1);
 
 EXPLAIN SELECT * FROM t;
 
 #换成InnoDB
 CREATE TABLE tt(i INT) ENGINE=INNODB;
 INSERT INTO tt VALUES(1);
 EXPLAIN SELECT * FROM tt;
 
 
 #当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是`const`
 EXPLAIN SELECT * FROM s1 WHERE id = 10005;
 
 EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;
 
 
 #在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的
 #（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则
 #对该被驱动表的访问方法就是`eq_ref`
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
  
  
 #当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是`ref`
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
 
 
 #当对普通二级索引进行等值匹配查询，该索引列的值也可以是`NULL`值时，那么对该表的访问方法
 #就可能是`ref_or_null`
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;
 
 
 #单表访问方法时在某些场景下可以使用`Intersection`、`Union`、
 #`Sort-Union`这三种索引合并的方式来执行查询
 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`
 EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
 
 
 #最熟悉的全表扫描
 EXPLAIN SELECT * FROM s1;

9.6.4.6 possible_keys和key

#6. possible_keys和key：可能用到的索引 和  实际上使用的索引
 
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';

9.6.4.7 key_len★

#7.  key_len：实际使用到的索引长度(即：字节数)
# 帮你检查`是否充分的利用上了索引`，`值越大越好`,主要针对于联合索引，有一定的参考意义。
 EXPLAIN SELECT * FROM s1 WHERE id = 10005;


 EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;


 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';


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

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

 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
 
 EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a';
 
#练习：
#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)

9.6.4.8 ref

 # 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.6.4.9 rows★

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

9.6.4.10 filtered

# 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';

9.6.4.11 Extra★

#11. Extra:一些额外的信息
 #更准确的理解MySQL到底将如何执行给定的查询语句
 
 
 #当查询语句的没有`FROM`子句时将会提示该额外信息
 EXPLAIN SELECT 1;
 
 
 #查询语句的`WHERE`子句永远为`FALSE`时将会提示该额外信息
 EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
 
 
 #当我们使用全表扫描来执行对某个表的查询，并且该语句的`WHERE`
 #子句中有针对该表的搜索条件时，在`Extra`列中会提示上述额外信息。
 EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
 
 
 #当使用索引访问来执行对某个表的查询，并且该语句的`WHERE`子句中
 #有除了该索引包含的列之外的其他搜索条件时，在`Extra`列中也会提示上述额外信息。
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
 
 
 #当查询列表处有`MIN`或者`MAX`聚合函数，但是并没有符合`WHERE`子句中
 #的搜索条件的记录时，将会提示该额外信息
 EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';
 
 EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'NlPros'; #NlPros 是 s1表中key1字段真实存在的数据
 
 #select * from s1 limit 10;
 
 #当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以
 #使用覆盖索引的情况下，在`Extra`列将会提示该额外信息。比方说下边这个查询中只
 #需要用到`idx_key1`而不需要回表操作：
 EXPLAIN SELECT key1,id FROM s1 WHERE key1 = 'a';
 
 
 #有些搜索条件中虽然出现了索引列，但却不能使用到索引
 #看课件理解索引条件下推
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
 
 
 #在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为
 #其分配一块名叫`join buffer`的内存块来加快查询速度，也就是我们所讲的`基于块的嵌套循环算法`
 #见课件说明
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
 
 
 #当我们使用左（外）连接时，如果`WHERE`子句中包含要求被驱动表的某个列等于`NULL`值的搜索条件，
 #而且那个列又是不允许存储`NULL`值的，那么在该表的执行计划的Extra列就会提示`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`索引合并的方式执行查询。
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
 
 
 #当我们的`LIMIT`子句的参数为`0`时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息
 EXPLAIN SELECT * FROM s1 LIMIT 0;
 
 
 #有一些情况下对结果集中的记录进行排序是可以使用到索引的。
 #比如：
 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 DISTINCT key1 FROM s1;
 
 #同上。
 EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
 
 #执行计划中出现`Using temporary`并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以
 #我们`最好能使用索引来替代掉使用临时表`。比如：扫描指定的索引idx_key1即可
 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';

9.6.4.12 小结

9.7 EXPLAIN的进一步使用

9.7.1 EXPLAIN四种输出格式

9.7.1.1 传统格式

9.7.1.2 JSON格式

9.7.1.3 TREE格式

9.7.1.4 可视化输出

9.7.2 SHOW WARNINGS的使用

9.8 分析优化器执行计划:trace

9.9 MySQL监控分析视图-sys schema

9.9.1 Sys schema视图摘要

9.9.2 Sys schema视图使用场景

9.10 小结

查询是数据库中最频繁的操作，提高查询速度可以有效地提高MySQL数据库的性能。通过对查询语句的分析可以了解查询语句的执行情况，找出查询语句执行的瓶颈，从而优化查询语句。

第10章:索引优化与查询优化

10.1 数据准备

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

 #随机产生字符串
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 ;

#用于随机产生多少到多少的编号
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 ;

#创建往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 ;


#执行存储过程，往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 ;


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

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

SELECT COUNT(*) FROM class;

SELECT COUNT(*) FROM student;



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 ;

10.2 索引失效案例

10.2.1 全值匹配我最爱

#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';

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);

10.2.2 最佳左前缀法则

#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'; 

DROP INDEX idx_age ON student;
DROP INDEX idx_age_classid ON student;

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

10.2.3 主键插入顺序

10.2.4 计算、函数、类型转换(自动或手动)导致索引失效

#3)主键插入顺序

#4)计算、函数、类型转换(自动或手动)导致索引失效

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

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc'; 

CREATE INDEX idx_name ON student(NAME);


#

CREATE INDEX idx_sno ON student(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';

10.2.5 类型转换导致索引失效

#5)类型转换导致索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123; 

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

10.2.6 范围条件右边的列索引失效

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

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


CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ; 


EXPLAIN SELECT SQL_NO_CACHE * FROM student 
WHERE student.age=30 AND student.name = 'abc' AND student.classId>20; 


CREATE INDEX idx_age_name_cid ON student(age,NAME,classId);

10.2.7 不等于(!= 或者<>)索引失效

#7)不等于(!= 或者<>)索引失效
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' ;

10.2.8 is null可以使用索引,is not null无法使用索引

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

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

10.2.9 like以通配符%开头索引失效

#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.2.10 OR前后存在非索引的列,索引失效

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

CREATE INDEX idx_cid ON student(classid);

10.2.11 数据库和表的字符集统一使用utf8mb4

# 11)数据库和表的字符集统一使用utf8mb4

10.2.12 练习及一般性建议

10.3 关联查询优化

10.3.1 数据准备

#3. 关联查询优化

# 情况1：左外连接
#分类
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)));

10.3.2 采用左外连接

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

#添加索引
CREATE INDEX Y ON book(card);

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

CREATE INDEX X ON `type`(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;

10.3.3 采用内连接

# 情况2：内连接

DROP INDEX X ON `type`;

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

#添加索引
CREATE INDEX Y ON book(card);

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

CREATE INDEX X ON `type`(card);

#结论：对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

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

CREATE INDEX Y ON book(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

#向type表中添加数据（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;

10.3.4 join语句原理

#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);


#测试1
EXPLAIN SELECT * FROM a LEFT 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);

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


SHOW VARIABLES LIKE '%optimizer_switch%';

SHOW VARIABLES LIKE '%join_buffer%';

10.3.5 小结

10.4 子查询优化

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

10.5 排序优化

10.5.1 排序优化

10.5.2 测试

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

#增加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;

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

EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid;

EXPLAIN  SELECT * FROM student WHERE  age=45 ORDER BY classid,NAME; 

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;

10.5.4 案例实战

可行，效果相同

#实战：测试filesort和index排序
CALL proc_drop_index('atguigudb2','student');

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

#方案一: 为了去掉filesort我们可以把索引建成

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 ;

#方案二：

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 ;

DROP INDEX idx_age_stuno_name ON student;

CREATE INDEX idx_age_stuno ON student(age,stuno);

10.5.4 filesort算法:双路排序和单路排序

10.6 GROUP BY优化

10.7 优化分页查询

10.8 优先考虑覆盖索引

10.8.1 什么是覆盖索引?

#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';

###
SELECT CRC32('hello')
FROM DUAL;

10.8.2 覆盖索引的利弊

10.9 如何给字符串添加索引

10.9.1 前缀索引

10.9.2 前缀索引对覆盖索引的影响

10.10 索引下推

全称：索引条件下推

10.10.1 使用前后对比

10.10.2 ICP的开启/关闭

10.10.3 ICP使用案例

10.10.4 开启和关闭ICP的性能对比

10.10.5 ICP的使用条件

#7. 索引条件下推(ICP)

#举例1：
USE atguigudb1;

EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

#举例2：
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(20) 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', '六', '赵', '天津市');


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

EXPLAIN SELECT * FROM people
WHERE zipcode='000001'
AND lastname LIKE '张%'
AND firstname LIKE '三%';


SET optimizer_switch = 'index_condition_pushdown=on';

#创建存储过程，向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);

10.11 普通索引 vs 唯一索引

10.11.1 查询过程

10.11.2 更新过程

10.11.3 change buffer的使用场景

10.12 其他查询优化策略

10.12.1 EXISTS和IN的区分

10.12.2 COUNT(*)与COUNT(具体字段)效率★

10.12.3 关于SELECT(*)★

10.12.4 LIMIT 1对优化的影响★

10.12.5 多使用COMMIT

10.13 淘宝数据库,主键如何设计的?★

10.13.1 自增ID的问题

10.13.2 业务字段做主键

10.13.3 淘宝的主键设计

10.13.4 推荐的主键设计

UUID

####

SELECT UUID() FROM DUAL;

SET @uuid = UUID();

SELECT @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);

第11章:数据库的设计规范

11.1 为什么需要数据库设计

11.2 范式

11.2.1 范式简介

11.2.2 范式都包括哪些

11.2.3 键和相关属性的概念

11.2.4 第一范式(1st NF)

11.2.5 第二范式(2nd NF)

11.2.6 第三范式(3rd NF)★

11.2.7 小结

11.3 反范式化★

11.3.1 概述

11.3.2 应用举例

#07-数据表的设计规范

#反范式化的举例：

CREATE DATABASE atguigudb3;

USE atguigudb3;

#学生表
CREATE TABLE student(
stu_id INT PRIMARY KEY AUTO_INCREMENT,
stu_name VARCHAR(25),
create_time DATETIME
);

#课程评论表
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;

SELECT COUNT(*) FROM class_comment;

###需求######
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 10000;


#####进行反范式化的设计######
#表的复制
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
);

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

11.3.3 反范式的新问题

11.3.4 反范式的使用场景

11.3.4.1 增加冗余字段的建议

11.3.4.2 历史快照、历史数据的需要

11.4 BCNF(巴斯范式)

11.5 第四范式

11.6 第五范式、域键范式

11.7 实战案例

11.7.1 迭代1次:考虑1NF

11.7.2 迭代2次:考虑2NF

11.7.3 迭代3次:考虑3NF

11.7.4 反范式化:业务优先的原则

供货商表：

进货单头表：

进货单明细表：

商品信息表：

11.8 ER模型

11.8.1 ER模型包括哪些要素?

11.8.2 关系的类型

11.8.3 建模分析

11.8.4 ER模型的细化

11.8.5 ER模型图转换成数据表

11.8.5.1 一个实体转换成一个数据表

11.8.5.2 一个多对多的关系转换成一个数据表

11.8.5.3 通过外键来表达一对多的关系

11.8.5.4 把属性转换成表的字段

11.9 数据表的设计原则

11.10 数据库对象编写建议

11.10.1 关于库

11.10.2 关于表、列

11.10.3 关于索引

11.10.4 SQL编写

11.11 PowerDesigner的使用

11.11.1 开始界面

11.11.2 概念数据模型

11.11.2.1 Entity实体

11.11.2.2 填充实体字段

11.11.2.3 设置主标识符

11.11.2.4 放大模型

11.11.2.5 实体关系

11.11.3 物理数据模型

11.11.4 概念模型转为物理模型

11.11.5 物理模型转为概念模型

11.11.6 物理模型导出SQL语句

第12章:数据库其他调优策略

12.1 数据库调优的措施

12.1.1 调优的目标

12.1.2 如何定位调优问题

12.1.3 调优的维度和步骤

12.1.3.1 第1步:选择适合的DBMS

12.1.3.2 第2步:优化表设计

12.1.3.3 第3步:优化逻辑查询

12.1.3.4 第4步:优化物理查询

12.1.3.5 第5步:使用Redis或Memcached作为缓存

12.1.3.6 第6步:库级优化

12.2 优化MySQL服务器

12.2.1 优化服务器硬件

12.1.2 优化MySQL的参数

12.3 优化数据库结构

12.3.1 拆分表:冷热数据分离

12.3.2 增加中间表

12.3.3 增加冗余字段

12.3.4 优化数据类型

12.3.5 优化插入记录的速度

12.3.6 使用非空约束

12.3.7 分析表、检查表与优化表

12.3.7.1 分析表

12.3.7.2 检查表

12.3.7.3 优化表

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

SELECT * FROM user1;

UPDATE user1 SET NAME = 'atguigu03' WHERE id = 3;

#分析表
ANALYZE TABLE user1;

#检查表
CHECK TABLE user1;

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

OPTIMIZE TABLE t1;


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

OPTIMIZE TABLE t2;

12.3.8 小结

12.4 大表优化

12.4.1 限定查询的范围

12.4.2 读/写分离

12.4.3 垂直拆分

12.4.4 水平拆分

12.5 其他调优策略

12.5.1 服务器语句超时处理

12.5.2 创建全局通用表空间

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

12.5.3 MySQL8.0新特性:隐藏索引对调优的帮助

开五档的蒙奇

关注

4
点赞
踩
5

收藏

觉得还不错? 一键收藏
打赏
0
评论
【MySQL高级篇】07-索引及调优篇

默认数据页大小16KB=16000byte，假设一条数据记录占用160byte假设所有存放用户记录的叶子节点代表的数据页存放100条用户记录；所有存放目录项的数据页可以存放1000条目录项记录如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录。如果B+树有2层，最多能存放 1000×100=10,0000条记录。如果B+树有3层，最多能存放 1000×1000×100=条记录。如果B+树有4层，最多能存放 1000×1000×1000×100=条记录。
复制链接

扫一扫