ToB企服应用市场:ToB评测及商务社交产业平台

标题: 一招带你吃透MySQL高级 [打印本页]

作者: 河曲智叟    时间: 2023-6-26 14:43
标题: 一招带你吃透MySQL高级
MySQL8高级之架构和优化                                                                                                 让

第01章 Linux下MySQL的安装与使用

1、安装

1.1、docker安装
  1. docker run -d \
  2. -p 3309:3306 \
  3. -v /atguigu/mysql/mysql8/conf:/etc/mysql/conf.d \
  4. -v /atguigu/mysql/mysql8/data:/var/lib/mysql \
  5. -e MYSQL_ROOT_PASSWORD=123456 \
  6. --name atguigu-mysql8 \
  7. --restart=always \
  8. mysql:8.0.29
复制代码
1.2、查看是否启动
  1. docker ps
复制代码
1.3、测试远程链接

测试连接:MySQL 8 版本,图形连接时还会出现如下问题

配置新连接报错:错误号码 2058,出现这个原因是MySQL 8 之前的版本中加密规则是mysql_native_password,而在MySQL 8之后,加密规则是caching_sha2_password。
解决方案有两种,一种是升级SQLyog和Navicat(因此,新版SQLyog和Navicat不会出现此问题),另一种是把MySQL用户登录密码加密规则还原成mysql_native_password。
解决方法:登录你的 MySQL 数据库
  1. #进入容器:env LANG=C.UTF-8 避免容器中显示中文乱码
  2. docker exec -it atguigu-mysql8 env LANG=C.UTF-8 /bin/bash
  3. #进入容器内的mysql命令行
  4. mysql -uroot -p
  5. #修改默认密码校验方式
  6. ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
复制代码
然后再重新配置SQLyog的连接,重新填写密码,则可连接成功了。
2、字符集

2.1、默认字符集

MySQL 8版本之前,默认字符集为 latin1(ISO-8859-1) ,不支持中文,使用前必须设置字符集为utf8(utf8mb3)或utf8mb4。从MySQL 8开始,数据库的默认字符集为 utf8mb4 ,从而避免中文乱码的问题。
  1. SHOW VARIABLES LIKE '%char%';
复制代码
2.2、utf8与utf8mb4

utf8 字符集表示一个字符需要使用1~4个字节,但是我们常用的一些字符使用1~3个字节就可以表示了。而字符集表示一个字符所用的最大字节长度,在某些方面会影响系统的存储和性能,所以设计MySQL的设计者偷偷的定义了两个概念:
utf8mb3 :阉割过的 utf8 字符集,只使用1~3个字节表示字符。(无法存储emoji表情)
MySQL5.7中的utf8是utf8mb3字符集
utf8mb4 :正宗的 utf8 字符集,使用1~4个字节表示字符。
MySQL8.0中的utf8是utf8mb4字符集
3、 SQL大小写规范

3.1、Windows和Linux的区别

Windows环境:
全部不区分大小写
Linux环境:
1、数据库名、表名、表的别名、变量名严格区分大小写;
2、列名与列的别名不区分大小写。
3、关键字、函数名称不区分大小写;
3.2、Linux下大小写规则设置(了解)

在MySQL 8中设置的具体步骤为:
  1. 1、停止MySQL服务
  2. 2、删除数据目录,即删除 /var/lib/mysql 目录
  3. 3、在MySQL配置文件(/etc/my.cnf )的 [mysqld] 中添加 lower_case_table_names=1
  4. 4、初始化数据目录 mysqld --initialize --user=mysql
  5. 5、启动MySQL服务 systemctl start mysqld
复制代码
注意:不建议在开发过程中修改此参数,将会丢失所有数据
4、sql_mode

4.1、宽松模式 vs 严格模式

宽松模式:
执行错误的SQL或插入不规范的数据,也会被接受,并且不报错。
严格模式:
执行错误的SQL或插入不规范的数据,会报错。MySQL5.7版本开始就将sql_mode默认值设置为了严格模式。
4.2、查看和设置sql_mode

查询sql_mode的值:
  1. SELECT @@session.sql_mode;
  2. SELECT @@global.sql_mode;
  3. -- 或者
  4. SHOW VARIABLES LIKE 'sql_mode'; --session级别
复制代码

临时设置sql_mode的值:
  1. SET GLOBAL sql_mode = 'mode1,model2,...'; --全局,要重新启动客户端生效,重启MySQL服务后失效
  2. SET SESSION sql_mode = 'mode1,model2,...'; --当前会话生效效,关闭当前会话就不生效了。可以省略SESSION关键字
复制代码
在mysql配置文件中配置,永久生效:在宿主机上执行以下命令,创建配置文件:
  1. vim /atguigu/mysql/mysql8/conf/my.cnf
复制代码
编辑配置文件
  1. [mysqld]
  2. sql-mode = "mode1,model2,..."
复制代码
重启mysql容器
  1. docker restart atguigu-mysql8
复制代码
4.3、错误开发演示

建表并插入数据:
  1. CREATE DATABASE atguigudb;
  2. USE atguigudb;
  3. CREATE TABLE employee(id INT, `name` VARCHAR(16),age INT,dept INT);
  4. INSERT INTO employee VALUES(1,'zhang3',33,101);
  5. INSERT INTO employee VALUES(2,'li4',34,101);
  6. INSERT INTO employee VALUES(3,'wang5',34,102);
  7. INSERT INTO employee VALUES(4,'zhao6',34,102);
  8. INSERT INTO employee VALUES(5,'tian7',36,102);
复制代码
需求:查询每个部门年龄最大的人
  1. -- 错误演示
  2. SELECT `name`, dept, MAX(age) FROM employee GROUP BY dept;
复制代码
以上查询语句在 “ONLY_FULL_GROUP_BY” 模式下查询出错,因为select子句中的name列并没有出现在group by子句中,也没有出现在函数中:

在非 “ONLY_FULL_GROUP_BY” 模式下可以正常执行,但是得到的是错误的结果:
  1. SET SESSION sql_mode = '';
复制代码

正确的查询方式:查询应该分两个步骤
1、查询每个部门最大的年龄
2、查询人
正确的语句:
  1. SELECT e.*
  2. FROM employee e
  3. INNER JOIN (SELECT dept, MAX(age) age FROM employee GROUP BY dept) AS maxage
  4. ON e.dept = maxage.dept AND e.age = maxage.age;
复制代码
测试完成后再将sql_mode设置回来:
  1. SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
复制代码
4.4、sql_mode常用值(了解)

第02章 逻辑架构

1、逻辑架构剖析

1.1、服务器处理客户端请求

下面是MySQL5.7使用的经典架构图,MySQL 8中去掉了Caches&Buffers部分:

1.2、Connectors(客户端)

MySQL服务器之外的客户端程序,与具体的语言相关,例如Java中的JDBC,图形用户界面SQLyog等。本质上都是在TCP连接上通过MySQL协议和MySQL服务器进行通信。
1.3、MySQL Server(服务器)

第1层:连接层

第2层:服务层

Management Serveices & Utilities: 系统管理和控制工具
SQL Interface:SQL接口:
Parser:解析器:
在SQL命令传递到解析器的时候会被解析器验证和解析。解析器中SQL 语句进行词法分析、语法分析、语义分析,并为其创建语法树。
典型的解析树如下:

Optimizer:查询优化器:
Caches & Buffers: 查询缓存组件:
第3层:引擎层

存储引擎层( Storage Engines),负责MySQL中数据的存储和提取,对物理服务器级别维护的底层数据执行操作,服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同,管理的表有不同的存储结构,采用的存取算法也不同,这样我们可以根据自己的实际需要进行选取。例如MyISAM引擎和InnoDB引擎。
1.4、存储层

所有的数据、数据库、表的定义、表的每一行的内容、索引,都是存在文件系统 上,以文件的方式存在,并完成与存储引擎的交互。
1.5、查询流程说明


首先,MySQL客户端通过协议与MySQL服务器建连接,通过SQL接口发送SQL语句,先检查查询缓存,如果命中,直接返回结果,否则进行语句解析。也就是说,在解析查询之前,服务器会先访问查询缓存,如果某个查询结果已经位于缓存中,服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可,这将大大提高系统的性能。
接下来是解析过程,MySQL解析器通过关键字将SQL语句进行解析,并生成一棵对应的解析树,解析器使用MySQL语法规则验证和解析SQL语句。例如,它将验证是否使用了错误的关键字,或者使用关键字的顺序是否正确,引号能否前后匹配等;预处理器则根据MySQL规则进一步检查解析树是否合法,例如,这里将检查数据表和数据列是否存在,还会解析名字和别名,看是否有歧义等,并生成一棵新解析树,新解析树可能和旧解析树结构一致。
然后是优化过程,MySQL优化程序会对我们的语句做一些优化,将查询的IO成本和CPU成本降到最低。优化的结果就是生成一个执行计划。这个执行计划表明了应该使用哪些索引执行查询,以及表之间的连接顺序是啥样,必要时将子查询转换为连接、表达式简化等等。我们可以使用EXPLAIN语句来查看某个语句的执行计划。
最后,进入执行阶段。完成查询优化后,查询执行引擎会按照生成的执行计划调用存储引擎提供的接口执行SQL查询并将结果返回给客户端。在MySQL8以下的版本,如果设置了查询缓存,这时会将查询结果进行缓存,再返回给客户端。
2、SQL执行流程

利用SHOW VARIABLES 可以查看SQL的执行流程。使用前需要先开启该功能:
2.1、MySQL8

1.开启profiling
确认profiling是否开启
  1. SHOW VARIABLES LIKE '%profiling%';
复制代码

profiling=0 代表关闭,我们需要把 profiling 打开,即设置为 1:
  1. SET profiling = 1;  -- profiling = ON
复制代码

2.显示查询
执行任意SQL语句:
  1. SELECT * FROM atguigudb.employee;
  2. SELECT * FROM atguigudb.employee WHERE id = 5;
复制代码
显示最近的几次查询:
  1. SHOW PROFILES;
复制代码

3.查看执行流程
查看最后一个SQL的执行流程:
  1. SHOW PROFILE;
复制代码

查看指定SQL的执行流程:查询指定的 Query ID
  1. SHOW PROFILE FOR QUERY 3;
复制代码
查询更丰富的内容:
  1. SHOW PROFILE cpu,block io FOR QUERY 3;
复制代码

2.2、MySQL5.7(了解)

1. 查看查询缓存是否启用
  1. SHOW VARIABLES LIKE '%query_cache_type%';
复制代码
2. 开启查询缓存
修改配置文件:vim /etc/my.cnf
新增一行:query_cache_type=1
重启MySQL:systemctl restart mysqld
3. 执行SQL并查看执行流程
参考MySQl8中执行流程的启用和查看方式,在MySQL5.7中查看执行流程


3、存储引擎

3.1、查看存储引擎

查看MySQL提供什么存储引擎
  1. SHOW ENGINES;
复制代码
下面的结果表示MySQL中默认使用的存储引擎是InnoDB,支持事务,行锁,外键,支持分布式事务(XA),支持保存点(回滚)

也可以通过以下语句查看默认的存储引擎:
  1. SHOW VARIABLES LIKE '%default_storage_engine%';
复制代码

3.2、设置存储引擎(了解)

方法1:
设置默认存储引擎:
  1. SET DEFAULT_STORAGE_ENGINE=MyISAM;
复制代码
方法2:
或者修改 my.cnf 文件:vim /etc/my.cnf
在[mysqld]节点下新增一行:default-storage-engine=MyISAM
重启MySQL:systemctl restart mysqld
方法3:
我们可以为 不同的表设置不同的存储引擎
  1. CREATE TABLE 表名( 建表语句 ) ENGINE = 存储引擎名称;
  2. ALTER TABLE 表名 ENGINE = 存储引擎名称;
  3. -- 例如:
  4. CREATE TABLE student(id INT, `name` VARCHAR(16),age INT,dept INT) ENGINE = MyISAM;
复制代码
3.3、各种引擎介绍

1. InnoDB存储引擎
2. MyISAM存储引擎
3. Archive引擎
4. Blackhole引擎(黑洞)
5. CSV引擎
6. Memory引擎
7. Federated引擎
3.4、MyISAM和InnoDB的区别

对比项MyISAMInnoDB外键不支持支持事务不支持支持行表锁表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作行锁,操作时只锁某一行,不对其它行有影响,适合高并发的操作缓存只缓存索引,不缓存真实数据不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响。支持聚簇索引关注点并发查询,节省资源、消耗少、简单业务并发写、事务、更大更复杂的资源操作默认使用NY自带系统表使用YN第03章 SQL预热

1、创建测试数据
  1. CREATE TABLE `t_dept` (
  2. `id` INT NOT NULL AUTO_INCREMENT,
  3. `deptName` VARCHAR(30) DEFAULT NULL,
  4. `address` VARCHAR(40) DEFAULT NULL,
  5. PRIMARY KEY (`id`)
  6. );
  7. CREATE TABLE `t_emp` (
  8. `id` INT NOT NULL AUTO_INCREMENT,
  9. `name` VARCHAR(20) DEFAULT NULL,
  10. `age` INT DEFAULT NULL,
  11. `deptId` INT DEFAULT NULL,
  12. `empno` INT NOT NULL,
  13. PRIMARY KEY (`id`),
  14. KEY `idx_dept_id` (`deptId`)
  15. #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
  16. );
  17. INSERT INTO t_dept(id,deptName,address) VALUES(1,'华山','华山');
  18. INSERT INTO t_dept(id,deptName,address) VALUES(2,'丐帮','洛阳');
  19. INSERT INTO t_dept(id,deptName,address) VALUES(3,'峨眉','峨眉山');
  20. INSERT INTO t_dept(id,deptName,address) VALUES(4,'武当','武当山');
  21. INSERT INTO t_dept(id,deptName,address) VALUES(5,'明教','光明顶');
  22. INSERT INTO t_dept(id,deptName,address) VALUES(6,'少林','少林寺');
  23. INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(1,'风清扬',90,1,100001);
  24. INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(2,'岳不群',50,1,100002);
  25. INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(3,'令狐冲',24,1,100003);
  26. INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(4,'洪七公',70,2,100004);
  27. INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(5,'乔峰',35,2,100005);
  28. INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(6,'灭绝师太',70,3,100006);
  29. INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(7,'周芷若',20,3,100007);
  30. INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(8,'张三丰',100,4,100008);
  31. INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(9,'张无忌',25,5,100009);
  32. INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(10,'韦小宝',18,NULL,100010);
复制代码
2、常见七种JOIN查询


需求1:查询所有有部门的员工信息以及他所在的部门信息
在A、和B中都存在的数据 => 查询A、B两表交集
  1. SELECT * FROM t_emp a INNER JOIN t_dept b ON a.deptid = b.id;
复制代码
需求2:查询所有用户,并显示其部门信息(如果员工没有所在部门,也会被列出) => 查询A的全集
  1. SELECT *
  2. FROM t_emp a
  3. LEFT JOIN t_dept b ON a.deptid = b.id;
复制代码
需求3:列出所有部门,并显示其部门的员工信息(如果部门没有员工,也会被列出)=> 查询B的全集
  1. SELECT *
  2. FROM t_emp a
  3. RIGHT JOIN t_dept b ON a.deptid = b.id;
复制代码
需求4:查询没有加入任何部门的员工(先查询所有员工,再过滤掉包含部门的数据) => 查询A且不包含B
  1. SELECT * FROM t_emp a
  2. LEFT JOIN t_dept b ON a.deptid = b.id
  3. WHERE b.id IS NULL;
复制代码
需求5:查询没有任何员工的部门 => 查询B且不包含A
  1. SELECT * FROM t_emp a
  2. RIGHT JOIN t_dept b ON a.deptid = b.id
  3. WHERE a.id IS NULL;
复制代码
需求6:查询所有员工和所有部门 => AB全有
MySQL FULL JOIN 的实现:因为MySQL不支持FULL JOIN,下面是替代方法
LEFT JOIN + UNION(合并并去重) + RIGHT JOIN
注意:
  1. SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id UNION SELECT *
  2. FROM t_emp a
  3. RIGHT JOIN t_dept b ON a.deptid = b.id;
复制代码
需求7:查询没有加入任何部门的员工,以及查询出部门下没有任何员工的部门 => A的独有+B的独有
  1. SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id WHERE b.id IS NULL UNION ALLSELECT * FROM t_emp a
  2. RIGHT JOIN t_dept b ON a.deptid = b.id
  3. WHERE a.id IS NULL;
复制代码
3、扩展掌门人

3.1、增加掌门人字段
  1. ALTER TABLE t_dept ADD CEO INT(11);
  2. UPDATE t_dept SET CEO=2 WHERE id=1;
  3. UPDATE t_dept SET CEO=4 WHERE id=2;
  4. UPDATE t_dept SET CEO=6 WHERE id=3;
  5. UPDATE t_dept SET CEO=8 WHERE id=4;
  6. UPDATE t_dept SET CEO=9 WHERE id=5;
复制代码
3.2、练习

需求1:求各个门派对应的掌门人
  1. SELECT b.deptname, a.name FROM t_dept b LEFT JOIN t_emp a ON b.ceo = a.id;
复制代码
需求2:求所有掌门人的平均年龄
  1. SELECT AVG(a.age) FROM t_emp a INNER JOIN t_dept b ON a.id = b.ceo;
复制代码
需求3:求所有人物对应的掌门名称(4种写法分析)
  1. -- 员工表(t_emp)、部门表(t_dept)、ceo(t_emp)表 关联查询
  2. SELECT emp.name, ceo.name AS ceoname
  3. FROM t_emp emp
  4. LEFT JOIN t_dept dept ON emp.deptid = dept.id
  5. LEFT JOIN t_emp ceo ON dept.ceo = ceo.id;
复制代码
  1. SELECT
  2. emp.name,
  3. (SELECT ceo.name FROM t_emp ceo WHERE ceo.id = dept.ceo) AS ceoname
  4. FROM t_emp emp
  5. LEFT JOIN t_dept dept ON emp.deptid = dept.id
复制代码
  1. SELECT emp_with_ceo_id.name, emp.name AS ceoname FROM
  2. -- 查询所有员工及对应的ceo的id
  3. (
  4. SELECT emp.name, dept.ceo
  5. FROM t_emp emp
  6. LEFT JOIN t_dept dept ON emp.deptid = dept.id
  7. ) emp_with_ceo_id
  8. LEFT JOIN t_emp emp ON emp_with_ceo_id.ceo = emp.id;
复制代码
  1. SELECT emp.name, ceo.ceoname FROM t_emp emp LEFT JOIN
  2. -- 查询并创建临时表ceo:包含ceo的部门id和ceo的name
  3. (
  4. SELECT emp.deptId AS deptId, emp.name AS ceoname
  5. FROM t_emp emp
  6. INNER JOIN t_dept dept ON emp.id = dept.ceo
  7. ) ceo
  8. ON emp.deptId = ceo.deptId;
复制代码
第04章 MySQL索引

1、索引简介

1.1、什么是索引

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
索引的本质:索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”。这些数据结构以某种方式指向数据, 可以在这些数据结构的基础上实现高级查找算法 。
1.2、索引的优缺点

优点:
(1)提高数据检索的效率,降低数据库的IO成本
(2)保证表中每条记录的唯一性 。

缺点:
(1)创建索引和维护索引要耗费时间 。
(2)索引是存储在磁盘上的,因此需要占用磁盘空间 。
1.3、索引分类

2、树

2.1、二叉树

二叉树
树有很多种,每个节点最多只能有两个子节点的一种形式称为二叉树。二叉树的子节点分为左节点和右节点。

二叉搜索树BST
BST(Binary Sort(Search) Tree):对于二叉排序树的任何一个非叶子节点,要求左子节点的值比当前节点的值小,右子节点的值比当前节点的值大。
特别说明:如果有相同的值,可以将该节点放在左子节点或右子节点。

BST的生成演示:https://www.cs.usfca.edu/~galles/visualization/BST.html
下图就是一种可能的索引方式示例:


BST的问题

平衡二叉树(AVL)
AVL树全称G.M. Adelson-Velsky和E.M. Landis,这是两个人的人名。
平衡二叉树也叫平衡二叉搜索树(Self-balancing binary search tree)又被称为AVL树, 可以保证查询效率较高。
具有以下特点:

AVL的生成演示:https://www.cs.usfca.edu/~galles/visualization/AVLtree.html
AVL的问题
众所周知,IO操作的效率很低,在大量数据存储中,查询时我们不能一下子将所有数据加载到内存中,只能逐节点加载(一个节点一次IO)。如果我们利用二叉树作为索引结构,那么磁盘的IO次数和索引树的高度是相关的。平衡二叉树由于树深度过大而造成磁盘IO读写过于频繁,进而导致效率低下。

为了提高查询效率,就需要 减少磁盘IO数 。为了减少磁盘IO的次数,就需要尽量降低树的高度 ,需要把原来“瘦高”的树结构变的“矮胖”,树的每层的分叉越多越好。针对同样的数据,如果我们把二叉树改成 三叉树:

上面的例子中,我们将二叉树变成了三叉树,降低了树的高度。如果能够在一个节点中存放更多的数据,我们还可以进一步减少节点的数量,从而进一步降低树的高度。这就是多叉树。
2.2、B树


上图所表示的 B 树就是一棵 3 阶的 B 树。假设一个磁盘块可以存储一个节点的数据。我们可以看下磁盘块 2,里面的关键字为(8,12),它有 3 个孩子 (3,5),(9,10) 和 (13,15),你能看到 (3,5) 小于 8,(9,10) 在 8 和 12 之间,而 (13,15)大于 12,三节点的子树的值大小仍然遵守 BST 二叉排序树的规则。
假设我们想要 查找的数据项是 9 ,那么步骤可以分为以下几步:
你能看出来在 B 树的搜索过程中,我们比较的次数并不少,但如果把数据读取出来然后在内存中进行比较,这个时间就是可以忽略不计的。而读取磁盘块本身需要进行 I/O 操作,消耗的时间比在内存中进行比较所需要的时间要多,是数据查找用时的重要因素。 B 树相比于平衡二叉树来说磁盘 I/O 操作要少 ,在数据查询中比平衡二叉树效率要高。所以只要树的高度足够低,IO次数足够少,就可以提高查询性能 。
再举例:
假设使用B树作为索引结构,存放MySQL中的数据:
蓝色部分表示数据的主键,黄色部分表示除主键外的其他数据,紫色部分表示指向子节点的指针

3、MySQL的索引结构:B+tree

3.1、InnoDB中的索引

3.1.1、设计索引

假设有一个表index_demo,表中有2个INT类型的列,1个CHAR(1)类型的列,c1列为主键:
  1. CREATE TABLE index_demo(c1 INT,c2 INT,c3 CHAR(1),PRIMARY KEY(c1)) ;
复制代码
index_demo表的简化的行格式示意图如下:

把一些记录放到页里的示意图就是(这里一页就是一个磁盘块,代表一次IO):

MySQL InnoDB的默认的页大小是16KB:
  1. -- 查看默认页的大小
  2. SHOW GLOBAL STATUS LIKE 'Innodb_page_size';
复制代码

因此数据存储在磁盘中,可能会占用多个数据页。如果各个页中的记录没有规律,我们就不得不依次遍历所有的数据页。如果我们想快速的定位到需要查找的记录在哪些数据页中,我们可以这样做 :

以页28为例,它对应目录项2 ,这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值 5。我们只需要把几个目录项在物理存储器上连续存储(比如:数组),就可以实现根据主键值快速查找某条记录的功能了。比如:查找主键值为 20 的记录,具体查找过程分两步:
至此,针对数据页做的简易目录就搞定了。这个目录有一个别名,称为索引 。
3.1.2、InnoDB中的索引方案

我们新分配一个编号为30的页来专门存储目录项记录,页10、28、9、20专门存储用户记录:

目录项记录和普通的用户记录的不同点:
现在查找主键值为 20 的记录,具体查找过程分两步:
更复杂的情况如下:
我们生成了一个存储更高级目录项的 页33 ,这个页中的两条记录分别代表页30和页32,如果用户记录的主键值在 [1, 320) 之间,则到页30中查找更详细的目录项记录,如果主键值 不小于320 的话,就到页32中查找更详细的目录项记录。这个数据结构,它的名称是 B+树 。

3.2、B树和B+树对比

B+ 树和 B 树的差异:
B+树为什么IO的次数会更少:
B+树的非叶子节点不存储用户记录,只存储目录记录,相对B树每个节点可以存储更多的记录,树的高度会更矮胖,IO次数也会更少。
3.3、聚簇索引

特点:
优点:
缺点:
限制:
3.4、非聚簇索引

(二级索引、辅助索引)
聚簇索引,只能在搜索条件是主键值时才发挥作用,因为B+树中的数据都是按照主键进行排序的,如果我们想以别的列作为搜索条件,那么需要创建非聚簇索引。
例如,以c2列作为搜索条件,那么需要使用c2列创建一棵B+树,如下所示:

这个B+树与聚簇索引有几处不同:
例如:根据c2列的值查找c2=4的记录,查找过程如下:

概念:回表
我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值,所以如果我们想根据c2列的值查找到完整的用户记录的话,仍然需要到聚簇索引中再查一遍,这个过程称为回表 。也就是根据c2列的值查询一条完整的用户记录需要使用到 2棵B+树!
问题:
为什么我们还需要一次回表操作呢?直接把完整的用户记录放到叶子节点不OK吗?
回答:
如果把完整的用户记录放到叶子节点是可以不用回表。但是太占地方了,相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍,这就有点太浪费存储空间了。
一张表可以有多个非聚簇索引:

3.5、联合索引

为c2和c3列建立联合索引:

3.6、覆盖索引

如果能通过读取索引就可以得到想要的数据,那就不需要读取用户记录,或者不用再做回表操作了。一个索引包含了满足查询结果的数据就叫做覆盖索引。
例如,如果为前面例子中的数据库表创建c2列索引,则如下SQL会使用覆盖索引
  1. SELECT c1, c2 from index_demo where c2 = 5;
复制代码
3.7、MyISAM中的索引

MyISAM引擎使用 B+Tree 作为索引结构,叶子节点的data域存放的是数据记录的地址 。
下图是MyISAM索引的原理图(索引和数据分开存储,是非聚簇索引):

如果我们在Col2上建立一个二级索引,则此索引的结构如下图所示(是非聚簇索引):

3.8、MyISAM与InnoDB对比


4、索引操作

4.1、创建索引

  1. CREATE TABLE customer (
  2.    
  3.   id INT UNSIGNED AUTO_INCREMENT,
  4.   customer_no VARCHAR(200),
  5.   customer_name VARCHAR(200),
  6.    
  7.   PRIMARY KEY(id), -- 主键索引:列设定为主键后会自动建立索引,唯一且不能为空。
  8.   UNIQUE INDEX uk_no (customer_no), -- 唯一索引:索引列值必须唯一,允许有NULL值,且NULL可能会出现多次。
  9.   KEY idx_name (customer_name), -- 普通索引:既不是主键,列值也不需要唯一,单纯的为了提高查询速度而创建。
  10.   KEY idx_no_name (customer_no,customer_name) -- 复合索引:即一个索引包含多个列。
  11. );
复制代码
  1. CREATE TABLE customer1 (
  2.   id INT UNSIGNED,
  3.   customer_no VARCHAR(200),
  4.   customer_name VARCHAR(200)
  5. );
  6. -- 建表后创建索引
  7. ALTER TABLE customer1 ADD PRIMARY KEY customer1(id); -- 主键索引
  8. CREATE UNIQUE INDEX uk_no ON customer1(customer_no); -- 唯一索引
  9. CREATE INDEX idx_name ON customer1(customer_name);  -- 普通索引
  10. CREATE INDEX idx_no_name ON customer1(customer_no,customer_name); -- 复合索引
  11. ALTER TABLE customer1 MODIFY id INT UNSIGNED AUTO_INCREMENT, ADD PRIMARY KEY customer1(id); --创建自增的主键索引
复制代码
  1. ALTER TABLE customer1 ADD PRIMARY KEY (id); -- 主键索引
  2. ALTER TABLE customer1 ADD UNIQUE INDEX uk_no (customer_no); -- 唯一索引
  3. ALTER TABLE customer1 ADD INDEX idx_name (customer_name);   -- 普通索引
  4. ALTER TABLE customer1 ADD INDEX idx_no_name (customer_no,customer_name);  -- 复合索引
复制代码
4.2、查看索引
  1. SHOW INDEX FROM customer;
复制代码
4.3、删除索引
  1. DROP INDEX idx_name ON customer; -- 删除单值、唯一、复合索引
  2. ALTER TABLE customer MODIFY id INT UNSIGNED, DROP PRIMARY KEY; -- 删除主键索引(有主键自增)
  3. ALTER TABLE customer1 DROP PRIMARY KEY;  -- 删除主键索引(没有主键自增)
复制代码
5、索引的使用场景

哪些情况适合创建索引:
哪些情况不要创建索引:
第05章 索引优化

1、数据库优化方案

问题:
哪些方法可以进行数据库调优?
解决方案:
2、性能分析(EXPLAIN)

2.1、EXPLAIN是什么

查看SQL执行计划:使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
用法:
  1. EXPLAIN + SQL语句
复制代码
2.2、数据准备
  1. USE atguigudb;
  2. CREATE TABLE t1(id INT(10) AUTO_INCREMENT, content VARCHAR(100) NULL, PRIMARY KEY (id));
  3. CREATE TABLE t2(id INT(10) AUTO_INCREMENT, content VARCHAR(100) NULL, PRIMARY KEY (id));
  4. CREATE TABLE t3(id INT(10) AUTO_INCREMENT, content VARCHAR(100) NULL, PRIMARY KEY (id));
  5. CREATE TABLE t4(id INT(10) AUTO_INCREMENT, content1 VARCHAR(100) NULL, content2 VARCHAR(100) NULL, PRIMARY KEY (id));
  6. CREATE INDEX idx_content1 ON t4(content1);  -- 创建普通索引
  7. # 以下新增sql多执行几次,以便演示
  8. INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1+RAND()*1000)));
  9. INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1+RAND()*1000)));
  10. INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1+RAND()*1000)));
  11. INSERT INTO t4(content1, content2) VALUES(CONCAT('t4_',FLOOR(1+RAND()*1000)), CONCAT('t4_',FLOOR(1+RAND()*1000)));
复制代码
2.3、各字段解释

2.3.1、table

  1. EXPLAIN SELECT * FROM t1;
复制代码

  1. EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
复制代码

2.3.2、id

在一个完整的查询语句中,每个SELECT关键字,都对应一个唯一的id。同时通过id也可以知道操作表的顺序。
  1. EXPLAIN SELECT * FROM t1, t2, t3;
复制代码

  1. EXPLAIN SELECT t1.id FROM t1 WHERE t1.id =(
  2.   SELECT t2.id FROM t2 WHERE t2.id =(
  3.     SELECT t3.id FROM t3 WHERE t3.content = '000'
  4.   )
  5. );
复制代码

注意:如果t3表查询无结果,则table列t1和t2处为NULL
注意:查询优化器可能对涉及子查询的语句进行优化,转为连接查询
  1. EXPLAIN SELECT * FROM t1 WHERE content IN (SELECT content FROM t2 WHERE content = 'a');
复制代码

  1. EXPLAIN SELECT * FROM t1 UNION SELECT * FROM t2;
复制代码

因此包含UNION ALL的执行计划中就没有这条记录
  1. EXPLAIN SELECT * FROM t1 UNION ALL SELECT * FROM t2;
复制代码

小结:
2.3.3、select_type

查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询。
  1. EXPLAIN SELECT * FROM t1;
复制代码

  1. EXPLAIN SELECT * FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content= 'a');
复制代码

  1. EXPLAIN SELECT * FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content = t3.content);
复制代码

  1. EXPLAIN SELECT * FROM t3
  2. WHERE id = ( SELECT id FROM t2 WHERE content = @@character_set_server);
复制代码

  1. EXPLAIN
  2. SELECT * FROM t3 WHERE id = 1
  3. UNION  
  4. SELECT * FROM t2 WHERE id = 1;
复制代码

  1. EXPLAIN SELECT * FROM t1 WHERE content IN
  2. (
  3. SELECT content FROM t2
  4. UNION
  5. SELECT content FROM t3
  6. );
复制代码

  1. EXPLAIN SELECT * FROM (
  2.    SELECT content, COUNT(*) AS c FROM t1 GROUP BY content
  3. ) AS derived_t1 WHERE c > 1;
复制代码
这里的就是在id为2的查询中产生的派生表。

补充:MySQL在处理带有派生表的语句时,优先尝试把派生表和外层查询进行合并,如果不行,再把派生表物化掉,然后执行查询。下面的例子就是就是将派生表和外层查询进行合并的例子:
  1. EXPLAIN SELECT * FROM (SELECT * FROM t1 WHERE content = 't1_832') AS derived_t1;
复制代码

物化:执行子查询,将结果放入临时表的过程称为物化,默认情况下会建立基于内存的物化表,并建立哈希索引,如果子查询的结果非常大,超过了系统变量tmp_table_size的设置,会建立基于磁盘的物化表,并建立B+树索引。(MySQl5.7及之后)
  1. EXPLAIN SELECT * FROM t1 WHERE content IN (SELECT content FROM t2);
复制代码

2.3.4、partitions

代表分区表中的命中情况,非分区表,该项为NULL
2.3.5、type

说明:
结果值从最好到最坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
比较重要的包含:system > const > eq_ref > ref > range > index > ALL
SQL 性能优化的目标:至少要达到 range 级别,要求是 ref 级别,最好是 const级别。(阿里巴巴
开发手册要求)

  1. EXPLAIN SELECT * FROM t1;
复制代码

  1. EXPLAIN SELECT content1 FROM t4;
复制代码
  1. EXPLAIN SELECT id FROM t1;
复制代码

  1. EXPLAIN SELECT * FROM t1 WHERE id > 2;
复制代码

  1. EXPLAIN SELECT * FROM t4 WHERE content1 = 'a';
复制代码

  1. EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
复制代码

  1. EXPLAIN SELECT * FROM t1 WHERE id = 1;
复制代码

  1. CREATE TABLE t(i int) Engine=MyISAM;
  2. INSERT INTO t VALUES(1);
  3. EXPLAIN SELECT * FROM t;
复制代码

2.3.6、possible_keys 和 key

  1. EXPLAIN SELECT id FROM t1 WHERE id = 1;
复制代码

2.3.7、key_len

表示索引使用的字节数,根据这个值可以判断索引的使用情况,检查是否充分利用了索引,针对联合索引值越大越好。
如何计算:
  1. -- 创建索引
  2. CREATE INDEX idx_age_name ON t_emp(age, `name`);
  3. -- 测试1
  4. EXPLAIN SELECT * FROM t_emp WHERE age = 30 AND `name` = 'ab%';
  5. -- 测试2
  6. EXPLAIN SELECT * FROM t_emp WHERE age = 30;
复制代码

2.3.8、ref

显示与key中的索引进行比较的列或常量。
  1. EXPLAIN SELECT * FROM t4 WHERE content1 = 'a';
复制代码

  1. EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
复制代码

2.3.9、rows

MySQL认为它执行查询时实际从索引树中查找到的行数。值越小越好。
  1. -- 如果是全表扫描,rows的值就是表中数据的估计行数
  2. EXPLAIN SELECT * FROM t_emp WHERE empno = '100001';
  3. -- 如果是使用索引查询,rows的值就是预计扫描索引记录行数
  4. EXPLAIN SELECT * FROM t_emp WHERE deptId = 1;
复制代码

2.3.10、filtered

最后查询出来的数据占所有服务器端(server)检查行数(rows)的百分比。值越大越好。
例如上一个例子。
2.3.11、Extra

包含不适合在其他列中显示但十分重要的额外信息。通过这些额外信息来理解MySQL到底将如何执行当前的查询语句。MySQL提供的额外信息有好几十个,这里只挑比较重要的介绍。
  1. EXPLAIN SELECT * FROM t_emp WHERE 1 != 1;
复制代码

  1. EXPLAIN SELECT * FROM t_emp WHERE `name` = '风清扬';
复制代码

在对查询结果中的记录进行排序时,是可以使用索引的,如下所示:
  1. EXPLAIN SELECT * FROM t1 ORDER BY id;
复制代码

如果排序操作无法使用到索引,只能在内存中(记录较少时)或者磁盘中(记录较多时)进行排序(filesort),如下所示:
  1. EXPLAIN SELECT * FROM t1 ORDER BY content;
复制代码

  1. EXPLAIN SELECT id, content1 FROM t4;
复制代码

  1. -- content1列上有索引idx_content1
  2. EXPLAIN SELECT * FROM t4 WHERE content1 > 'z' AND content1 LIKE '%a';
复制代码

注意:如果这里的查询条件只有content1 > 'z',那么找到满足条件的索引后也会进行一次索引下推的操作,判断content1 > 'z'是否成立(这是源码中为了编程方便做的冗余判断)

  1. EXPLAIN  SELECT * FROM t1, t2 WHERE t1.content = t2.content;
复制代码

下面这个例子就是被驱动表使用了索引,此时Extra中就没有Using join buffer了:
  1. EXPLAIN SELECT * FROM t_emp, t_dept WHERE t_dept.id = t_emp.deptId;
复制代码

课外阅读:在没有索引的情况下,为了优化多表连接,减少磁盘IO读取次数和数据遍历次数,MySQL为我们提供了很多不同的连接缓存的优化算法,可参考https://blog.csdn.net/qq_35423190/article/details/120504960
3、准备数据

在做优化之前,要准备大量数据。接下来创建两张表,并往员工表里插入50W数据,部门表中插入1W条数据。
怎么快速插入50w条数据呢? 存储过程
怎么保证插入的数据不重复?函数
部门表:
员工表:
总结:需要产生随机字符串和区间随机数的函数。
3.1、创建表
  1. CREATE TABLE `dept` (
  2.         `id` INT(11) NOT NULL AUTO_INCREMENT,
  3.         `deptName` VARCHAR(30) DEFAULT NULL,
  4.         `address` VARCHAR(40) DEFAULT NULL,
  5.         ceo INT NULL ,
  6.         PRIMARY KEY (`id`)
  7. ) ENGINE=INNODB AUTO_INCREMENT=1;
  8. CREATE TABLE `emp` (
  9.         `id` INT(11) NOT NULL AUTO_INCREMENT,
  10.         `empno` INT NOT NULL ,
  11.         `name` VARCHAR(20) DEFAULT NULL,
  12.         `age` INT(3) DEFAULT NULL,
  13.         `deptId` INT(11) DEFAULT NULL,
  14.         PRIMARY KEY (`id`)
  15.         #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
  16. ) ENGINE=INNODB AUTO_INCREMENT=1;
复制代码
3.2、创建函数
  1. -- 查看mysql是否允许创建函数:
  2. SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
  3. -- 命令开启:允许创建函数设置:(global-所有session都生效)
  4. SET GLOBAL log_bin_trust_function_creators=1;
复制代码
  1. -- 随机产生字符串
  2. DELIMITER $$
  3. CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
  4. BEGIN   
  5.         DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  6.         DECLARE return_str VARCHAR(255) DEFAULT '';
  7.         DECLARE i INT DEFAULT 0;
  8.         WHILE i < n DO  
  9.                 SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));  
  10.                 SET i = i + 1;
  11.         END WHILE;
  12.         RETURN return_str;
  13. END $$
  14. -- 假如要删除
  15. -- drop function rand_string;
复制代码
  1. -- 用于随机产生区间数字
  2. DELIMITER $$
  3. CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
  4. BEGIN   
  5. DECLARE i INT DEFAULT 0;  
  6. SET i = FLOOR(from_num +RAND()*(to_num -from_num+1));
  7. RETURN i;  
  8. END$$
  9. -- 假如要删除
  10. -- drop function rand_num;
复制代码
3.3、创建存储过程
  1. -- 插入员工数据
  2. DELIMITER $$
  3. CREATE PROCEDURE  insert_emp(START INT, max_num INT)
  4. BEGIN  
  5.         DECLARE i INT DEFAULT 0;   
  6.         #set autocommit =0 把autocommit设置成0  
  7.         SET autocommit = 0;   
  8.         REPEAT  
  9.                 SET i = i + 1;  
  10.                 INSERT INTO emp (empno, NAME, age, deptid ) VALUES ((START+i) ,rand_string(6), rand_num(30,50), rand_num(1,10000));  
  11.                 UNTIL i = max_num  
  12.         END REPEAT;  
  13.         COMMIT;  
  14. END$$
  15. -- 删除
  16. -- DELIMITER ;
  17. -- drop PROCEDURE insert_emp;
复制代码
  1. -- 插入部门数据
  2. DELIMITER $$
  3. CREATE PROCEDURE insert_dept(max_num INT)
  4. BEGIN  
  5.         DECLARE i INT DEFAULT 0;   
  6.         SET autocommit = 0;   
  7.         REPEAT  
  8.                 SET i = i + 1;  
  9.                 INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));  
  10.                 UNTIL i = max_num  
  11.         END REPEAT;  
  12.         COMMIT;  
  13. END$$
  14. -- 删除
  15. -- DELIMITER ;
  16. -- drop PROCEDURE insert_dept;
复制代码
3.4、调用存储过程
  1. -- 执行存储过程,往dept表添加1万条数据
  2. CALL insert_dept(10000);
  3. -- 执行存储过程,往emp表添加50万条数据,编号从100000开始
  4. CALL insert_emp(100000,500000);
复制代码
3.5、批量删除表索引
  1. -- 批量删除某个表上的所有索引
  2. DELIMITER $$
  3. CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
  4. BEGIN
  5.         DECLARE done INT DEFAULT 0;
  6.         DECLARE ct INT DEFAULT 0;
  7.         DECLARE _index VARCHAR(200) DEFAULT '';
  8.         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'  ;
  9.         DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;      
  10.         OPEN _cur;
  11.                 FETCH _cur INTO _index;
  12.                 WHILE  _index<>'' DO
  13.                         SET @str = CONCAT("drop index ",_index," on ",tablename );
  14.                         PREPARE sql_str FROM @str ;
  15.                         EXECUTE sql_str;
  16.                         DEALLOCATE PREPARE sql_str;
  17.                         SET _index='';
  18.                         FETCH _cur INTO _index;
  19.                 END WHILE;
  20.         CLOSE _cur;
  21. END$$
复制代码
  1. -- 执行批量删除:dbname 库名称, tablename 表名称
  2. CALL proc_drop_index("dbname","tablename");
复制代码
3.6、开启SQL执行时间的显示

为了方便后面的测试中随时查看SQL运行的时间,测试索引优化后的效果,我们开启profiling
  1. -- 显示sql语句执行时间
  2. SET profiling = 1;
  3. SHOW VARIABLES  LIKE '%profiling%';
  4. SHOW PROFILES;
复制代码
4、单表索引失效案例

MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法,并且加快查询的速度,因此索引对查询的速度有着至关重要的影响。
我们创建索引后,用不用索引,最终是优化器说了算。优化器会基于开销选择索引,怎么开销小就怎么来。不是基于规则,也不是基于语义。
另外SQL语句是否使用索引,和数据库的版本、数据量、数据选择度(查询中选择的列数)运行环境都有关系。
  1. -- 创建索引
  2. CREATE INDEX idx_name ON emp(`name`);
复制代码
4.1、计算、函数导致索引失效
  1. -- 显示查询分析
  2. EXPLAIN SELECT * FROM emp WHERE emp.name  LIKE 'abc%';
  3. EXPLAIN SELECT * FROM emp WHERE LEFT(emp.name,3) = 'abc'; --索引失效
复制代码

4.2、LIKE以%开头索引失效
  1. EXPLAIN SELECT * FROM emp WHERE name LIKE '%ab%'; --索引失效
复制代码

拓展:Alibaba《Java开发手册》
【强制】页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。
4.3、不等于(!= 或者)索引失效
  1. EXPLAIN SELECT * FROM emp WHERE emp.name = 'abc' ;
  2. EXPLAIN SELECT * FROM emp WHERE emp.name <> 'abc' ; --索引失效
复制代码

4.4、IS NOT NULL 和 IS NULL
  1. EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL;
  2. EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; --索引失效
复制代码

注意:当数据库中的数据的索引列的NULL值达到比较高的比例的时候,即使在IS NOT NULL 的情况下 MySQL的查询优化器会选择使用索引,此时type的值是range(范围查询)
  1. -- 将 id>20000 的数据的 name 值改为 NULL
  2. UPDATE emp SET `name` = NULL WHERE `id` > 20000;
  3. -- 执行查询分析,可以发现 IS NOT NULL 使用了索引
  4. -- 具体多少条记录的值为NULL可以使索引在IS NOT NULL的情况下生效,由查询优化器的算法决定
  5. EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL;
复制代码

测试完将name的值改回来
  1. UPDATE emp SET `name` = rand_string(6) WHERE `id` > 20000;
复制代码
4.5、类型转换导致索引失效
  1. EXPLAIN SELECT * FROM emp WHERE name='123';
  2. EXPLAIN SELECT * FROM emp WHERE name= 123; --索引失效
复制代码

4.6、全值匹配我最爱

准备:
  1. -- 首先删除之前创建的索引
  2. CALL proc_drop_index("atguigudb","emp");
复制代码
问题:为以下查询语句创建哪种索引效率最高
  1. -- 查询分析
  2. EXPLAIN SELECT * FROM emp WHERE emp.age = 30 and deptid = 4 AND emp.name = 'abcd';
  3. -- 执行SQL
  4. SELECT * FROM emp WHERE emp.age = 30 and deptid = 4 AND emp.name = 'abcd';
  5. -- 查看执行时间
  6. SHOW PROFILES;
复制代码
创建索引并重新执行以上测试:
  1. -- 创建索引:分别创建以下三种索引的一种,并分别进行以上查询分析
  2. CREATE INDEX idx_age ON emp(age);
  3. CREATE INDEX idx_age_deptid ON emp(age,deptid);
  4. CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);
复制代码
结论:可以发现最高效的查询应用了联合索引 idx_age_deptid_name

4.7、最佳左前缀法则

准备:
  1. -- 首先删除之前创建的索引
  2. CALL proc_drop_index("atguigudb","emp");-- 创建索引CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);
复制代码
问题:以下这些SQL语句能否命中 idx_age_deptid_name 索引,可以匹配多少个索引字段
测试:
  1. EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.name = 'abcd' ;
  2. -- EXPLAIN结果:
  3. -- key_len:5 只使用了age索引
  4. -- 索引查找的顺序为 age、deptid、name,查询条件中不包含deptid,无法使用deptid和name索引
  5. EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd';
  6. -- EXPLAIN结果:
  7. -- type: ALL, 执行了全表扫描
  8. -- key_len: NULL, 索引失效
  9. -- 索引查找的顺序为 age、deptid、name,查询条件中不包含age,无法使用整个索引
  10. EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND emp.deptid=1 AND emp.name = 'abcd';
  11. -- EXPLAIN结果:
  12. -- 索引查找的顺序为 age、deptid、name,匹配所有索引字段
  13. EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd' AND emp.age = 30;
  14. -- EXPLAIN结果:
  15. -- 索引查找的顺序为 age、deptid、name,匹配所有索引字段
复制代码
4.8、索引中范围条件右边的列失效

准备:
  1. -- 首先删除之前创建的索引
  2. CALL proc_drop_index("atguigudb","emp");
复制代码
问题:为以下查询语句创建哪种索引效率最高
  1. EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.deptId>1000 AND emp.name = 'abc';
复制代码
测试1:
  1. -- 创建索引并执行以上SQL语句的EXPLAIN
  2. CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);
  3. -- key_len:10, 只是用了 age 和 deptid索引,name失效
复制代码
注意:当我们修改deptId的范围条件的时候,例如deptId>100,那么整个索引失效,MySQL的优化器基于成本计算后认为没必要使用索引了,所以就进行了全表扫描。(注意:因为表中的数据是随机生成的,因此实际测试中根据具体数据的不同测试的结果也会不一样,最终是否使用索引由优化器决定)

测试2:
  1. -- 创建索引并执行以上SQL语句的EXPLAIN(将deptid索引的放在最后)
  2. CREATE INDEX idx_age_name_deptid ON emp(age,`name`,deptid);
  3. -- 使用了完整的索引
复制代码

补充:以上两个索引都存在的时候,MySQL优化器会自动选择最好的方案
5、关联查询优化

5.1、数据准备

创建两张表,并分插入16条和20条数据:
  1. -- 分类
  2. CREATE TABLE IF NOT EXISTS `class` (
  3. `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  4. `card` INT(10) UNSIGNED NOT NULL,
  5. PRIMARY KEY (`id`)
  6. );
  7. -- 图书
  8. CREATE TABLE IF NOT EXISTS `book` (
  9. `bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  10. `card` INT(10) UNSIGNED NOT NULL,
  11. PRIMARY KEY (`bookid`)
  12. );
  13. -- 插入16条记录
  14. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  15. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  16. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  17. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  18. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  19. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  20. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  21. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  22. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  23. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  24. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  25. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  26. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  27. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  28. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  29. INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
  30. -- 插入20条记录
  31. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  32. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  33. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  34. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  35. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  36. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  37. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  38. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  39. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  40. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  41. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  42. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  43. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  44. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  45. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  46. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  47. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  48. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  49. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  50. INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
复制代码
5.2、左外连接

没有创建索引前的测试:进行了全表扫描,查询次数为16*20
  1. EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
  2. -- 左表class:驱动表、右表book:被驱动表
复制代码

测试1:在驱动表上创建索引:进行了全索引扫描,查询次数是16*20
  1. -- 创建索引
  2. CREATE INDEX idx_class_card ON class(card);
复制代码

测试2:在被驱动表上创建索引:可以避免全表扫描,查询次数是16*1
  1. -- 首先删除之前创建的索引
  2. CALL proc_drop_index("atguigudb","class");
  3. -- 创建索引
  4. CREATE INDEX idx_book_card ON book(card);
复制代码

测试3:同时给两张表添加索引:充分利用了索引,查询次数是16*1
  1. -- 已经有了book索引
  2. CREATE INDEX idx_class_card ON class(card);
复制代码

结论:
针对两张表的连接条件涉及的列,索引要创建在被驱动表上,驱动表尽量是小表
5.3、内连接

测试:将前面外连接中的LEFT JOIN 变成 INNER JOIN
  1. -- 换成inner join
  2. EXPLAIN SELECT * FROM class INNER JOIN book ON class.card=book.card;
  3. -- 交换class和book的位置
  4. EXPLAIN SELECT * FROM book INNER JOIN class ON class.card=book.card;
复制代码
都有索引的情况下:查询优化器自动选择数据量小的表做为驱动表

class表有索引的情况下:book表是驱动表

book表有索引的情况下:class表是驱动表

都没有索引的情况下:选择数据量小的表做为驱动表

结论:发现即使交换表的位置,MySQL优化器也会自动选择驱动表,自动选择驱动表的原则是:索引创建在被驱动表上,驱动表是小表。
5.4、扩展掌门人的练习
  1. -- 首先删除之前创建的索引
  2. CALL proc_drop_index("atguigudb","emp");CALL proc_drop_index("atguigudb","dept");
复制代码
1.三表左连接方式:
  1. -- 员工表(t_emp)、部门表(t_dept)、ceo(t_emp)表 关联查询
  2. EXPLAIN SELECT emp.name, ceo.name AS ceoname
  3. FROM emp
  4. LEFT JOIN dept ON emp.deptid = dept.id
  5. LEFT JOIN emp ceo ON dept.ceo = ceo.id;
复制代码
一趟查询,用到了主键索引,效果最佳

2.子查询方式:
  1. EXPLAIN SELECT emp.name,
  2. (SELECT emp.name FROM emp WHERE emp.id = dept.ceo) AS ceoname
  3. FROM emp
  4. LEFT JOIN dept ON emp.deptid = dept.id;
复制代码
两趟查询,用到了主键索引,跟第一种比,效果稍微差点。

3.临时表连接方式
  1. EXPLAIN SELECT emp_with_ceo_id.name, emp.name AS ceoname
  2. FROM
  3. (
  4. SELECT emp.name, dept.ceo
  5. FROM emp
  6. LEFT JOIN dept ON emp.deptid = dept.id
  7. ) emp_with_ceo_id
  8. LEFT JOIN emp ON emp_with_ceo_id.ceo = emp.id;
复制代码
查询一趟,MySQL查询优化器将衍生表查询转换成了连接表查询,速度堪比第一种方式

MySQL5.5查询结果:两趟查询,先查询a,b产生衍生表ab,衍生表作为驱动表,c作为被驱动表,使用到c表主键。效果比后面一种要好一点。

4、临时表连接方式2
  1. EXPLAIN SELECT emp.name, ceo.ceoname FROM emp LEFT JOIN
  2. (
  3. SELECT emp.deptId AS deptId, emp.name AS ceoname
  4. FROM emp
  5. INNER JOIN dept ON emp.id = dept.ceo
  6. ) ceo
  7. ON emp.deptId = ceo.deptId;
复制代码
查询一趟,MySQL查询优化器将衍生表查询转换成了连接表查询,但是只有一个表使用了索引,数据检索的次数稍多,性能最差。

MySQL5.5查询结果:两趟查询,先查询b, a产生衍生表ab,衍生表作为被驱动表,衍生表无法建立索引,也就无法优化; 所以,这种语句是性能最差的。

5.5、总结

6、子查询优化

查询非掌门人
  1. -- 不推荐
  2. -- 查询员工,这些员工的id没在(掌门人id列表中)
  3. -- 【查询不是CEO的员工】
  4. SELECT * FROM t_emp emp WHERE emp.id NOT IN
  5. (SELECT dept.ceo FROM t_dept dept WHERE dept.ceo IS NOT NULL);
复制代码

注意:使用大表(emp、dept表)测试更加直观
  1. -- 推荐
  2. -- 按照集合查询
  3. SELECT emp.* FROM t_emp emp
  4. LEFT JOIN t_dept dept ON emp.id = dept.ceo WHERE dept.id IS NULL;
复制代码

也可以为ceo添加一个索引字段

结论:尽量不要使用NOT IN 或者 NOT EXISTS,用LEFT JOIN xxx ON xx = xx WHERE xx IS NULL替代
7、排序优化

7.1、索引失效的情况

以下三种情况不走索引:
准备:
  1. -- 删除现有索引
  2. CALL proc_drop_index("atguigudb","emp");
  3. -- 创建索引
  4. CREATE INDEX idx_age_deptid_name ON emp (age,deptid,`name`);
复制代码
无过滤,不索引:
  1. -- 没有使用索引:
  2. EXPLAIN SELECT * FROM emp ORDER BY age,deptid;
  3. -- 使用了索引:order by想使用索引,必须有过滤条件,索引才能生效,limit也可以看作是过滤条件
  4. EXPLAIN SELECT * FROM emp ORDER BY age,deptid LIMIT 10;
复制代码
顺序错,不索引:
  1. -- 排序使用了索引:
  2. -- 注意:key_len = 5是where语句使用age索引的标记,order by语句使用索引不在key_len中体现。
  3. --      order by语句如果没有使用索引,在extra中会出现using filesort。                  
  4. EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid;
  5. -- 排序使用了索引:
  6. EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid, `name`;
  7. -- 排序没有使用索引:因为索引列中不存在empno
  8. EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid, empno;
  9. -- 排序没有使用索引:order by 后的排序条件的顺序,与索引顺序不一致
  10. EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY `name`, deptid;
  11. -- 排序没有使用索引:出现的顺序要和复合索引中的列的顺序一致!
  12. EXPLAIN SELECT * FROM emp WHERE deptid=45 ORDER BY age;
复制代码
方向反,不索引:
  1. -- 排序使用了索引:排序条件和索引一致,并方向相同,可以使用索引
  2. EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid DESC, `name` DESC;
  3. -- 没有使用索引:两个排序条件方向相反
  4. EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid ASC, `name` DESC;
复制代码
7.2、索引优化案例

排序优化的目的是,去掉 Extra 中的 using filesort(手工排序)
准备:
  1. -- 删除现有索引
  2. CALL proc_drop_index("atguigudb","emp");
  3. -- 这个例子结合 show profiles; 查看运行时间
  4. SET profiling = 1;
复制代码
需求:查询 年龄为30岁的,且员工编号小于101000的用户,按用户名称排序
测试1:很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
[code]EXPLAIN SELECT * FROM emp WHERE age =30 AND empno




欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4