尚硅谷MySQL高级学习笔记 -- 2.索引优化分析

最新推荐文章于 2024-04-11 12:50:02 发布
最新推荐文章于 2024-04-11 12:50:02 发布
阅读量1.4w 收藏 3
点赞数 2
分类专栏： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/exodus3/article/details/112417543
版权

1、慢 SQL

性能下降、 SQL 慢、执行时间长、等待时间长的原因分析

  1. 查询语句写的烂
  2. 索引失效：
    • 单值索引：在user表中给name属性建个索引，create index idx_user_name on user(name)
    • 复合索引：在user表中给name、email属性建个索引，create index idx_user_nameEmail on user(name,email)
  3. 关联查询太多join(设计缺陷或不得已的需求)
  4. 服务器调优及各个参数设置(缓冲、线程数等)

2、join 查询

2.1、SQL 执行顺序

我们手写的 SQL 顺序

image-20200803212731183

MySQL 实际执行 SQL 顺序

  1. mysql 执行的顺序：随着 Mysql 版本的更新换代， 其优化器也在不断的升级， 优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。
  2. 下面是经常出现的查询顺序：

image-20200803212826429

  • 总结：mysql 从 FROM 开始执行~

image-20200803212936111

2.2、JOIN 连接查询

常见的 JOIN 查询图

image-20200803213106434

建表 SQL

CREATE TABLE tbl_dept(
	id INT(11) NOT NULL AUTO_INCREMENT,
	deptName VARCHAR(30) DEFAULT NULL,
	locAdd VARCHAR(40) DEFAULT NULL,
	PRIMARY KEY(id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE tbl_emp (
	id INT(11) NOT NULL AUTO_INCREMENT,
	NAME VARCHAR(20) DEFAULT NULL,
	deptId INT(11) DEFAULT NULL,
	PRIMARY KEY (id),
	KEY fk_dept_Id (deptId)
	#CONSTRAINT 'fk_dept_Id' foreign key ('deptId') references 'tbl_dept'('Id')
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);

INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);
  • tbl_dept 表结构
mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
|  1 | RD       | 11     |
|  2 | HR       | 12     |
|  3 | MK       | 13     |
|  4 | MIS      | 14     |
|  5 | FD       | 15     |
+----+----------+--------+
5 rows in set (0.00 sec)
  • tbl_emp 表结构
select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
|  8 | s9   |     51 |
+----+------+--------+
8 rows in set (0.00 sec)

7 种 JOIN 示例

笛卡尔积

  1. tbl_emp 表和 tbl_dept 表的笛卡尔乘积：select * from tbl_emp, tbl_dept;
  2. 其结果集的个数为：5 * 8 = 40
select * from tbl_emp, tbl_dept;
+----+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+----+----------+--------+
|  1 | z3   |      1 |  1 | RD       | 11     |
|  1 | z3   |      1 |  2 | HR       | 12     |
|  1 | z3   |      1 |  3 | MK       | 13     |
|  1 | z3   |      1 |  4 | MIS      | 14     |
|  1 | z3   |      1 |  5 | FD       | 15     |
|  2 | z4   |      1 |  1 | RD       | 11     |
|  2 | z4   |      1 |  2 | HR       | 12     |
|  2 | z4   |      1 |  3 | MK       | 13     |
|  2 | z4   |      1 |  4 | MIS      | 14     |
|  2 | z4   |      1 |  5 | FD       | 15     |
|  3 | z5   |      1 |  1 | RD       | 11     |
|  3 | z5   |      1 |  2 | HR       | 12     |
|  3 | z5   |      1 |  3 | MK       | 13     |
|  3 | z5   |      1 |  4 | MIS      | 14     |
|  3 | z5   |      1 |  5 | FD       | 15     |
|  4 | w5   |      2 |  1 | RD       | 11     |
|  4 | w5   |      2 |  2 | HR       | 12     |
|  4 | w5   |      2 |  3 | MK       | 13     |
|  4 | w5   |      2 |  4 | MIS      | 14     |
|  4 | w5   |      2 |  5 | FD       | 15     |
|  5 | w6   |      2 |  1 | RD       | 11     |
|  5 | w6   |      2 |  2 | HR       | 12     |
|  5 | w6   |      2 |  3 | MK       | 13     |
|  5 | w6   |      2 |  4 | MIS      | 14     |
|  5 | w6   |      2 |  5 | FD       | 15     |
|  6 | s7   |      3 |  1 | RD       | 11     |
|  6 | s7   |      3 |  2 | HR       | 12     |
|  6 | s7   |      3 |  3 | MK       | 13     |
|  6 | s7   |      3 |  4 | MIS      | 14     |
|  6 | s7   |      3 |  5 | FD       | 15     |
|  7 | s8   |      4 |  1 | RD       | 11     |
|  7 | s8   |      4 |  2 | HR       | 12     |
|  7 | s8   |      4 |  3 | MK       | 13     |
|  7 | s8   |      4 |  4 | MIS      | 14     |
|  7 | s8   |      4 |  5 | FD       | 15     |
|  8 | s9   |     51 |  1 | RD       | 11     |
|  8 | s9   |     51 |  2 | HR       | 12     |
|  8 | s9   |     51 |  3 | MK       | 13     |
|  8 | s9   |     51 |  4 | MIS      | 14     |
|  8 | s9   |     51 |  5 | FD       | 15     |
+----+------+--------+----+----------+--------+
40 rows in set (0.00 sec)

inner join

  1. tbl_emp 表和 tbl_dept 的交集部分（公共部分）
  2. select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id;
select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id;
+----+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+----+----------+--------+
|  1 | z3   |      1 |  1 | RD       | 11     |
|  2 | z4   |      1 |  1 | RD       | 11     |
|  3 | z5   |      1 |  1 | RD       | 11     |
|  4 | w5   |      2 |  2 | HR       | 12     |
|  5 | w6   |      2 |  2 | HR       | 12     |
|  6 | s7   |      3 |  3 | MK       | 13     |
|  7 | s8   |      4 |  4 | MIS      | 14     |
+----+------+--------+----+----------+--------+
7 rows in set (0.00 sec)

left join

  1. tbl_emp 与 tbl_dept 的公共部分 + tbl_emp 表的独有部分
  2. left join：取左表独有部分 + 两表公共部分
  3. select * from tbl_emp e left join tbl_dept d on e.deptId = d.id;
select * from tbl_emp e left join tbl_dept d on e.deptId = d.id;
+----+------+--------+------+----------+--------+
| id | NAME | deptId | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  1 | z3   |      1 |    1 | RD       | 11     |
|  2 | z4   |      1 |    1 | RD       | 11     |
|  3 | z5   |      1 |    1 | RD       | 11     |
|  4 | w5   |      2 |    2 | HR       | 12     |
|  5 | w6   |      2 |    2 | HR       | 12     |
|  6 | s7   |      3 |    3 | MK       | 13     |
|  7 | s8   |      4 |    4 | MIS      | 14     |
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+
8 rows in set (0.00 sec)

right join

  1. tbl_emp 与 tbl_dept 的公共部分 + tbl_dept表的独有部分
  2. right join：取右表独有部分 + 两表公共部分
  3. select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;
select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;
+------+------+--------+----+----------+--------+
| id   | NAME | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
|    1 | z3   |      1 |  1 | RD       | 11     |
|    2 | z4   |      1 |  1 | RD       | 11     |
|    3 | z5   |      1 |  1 | RD       | 11     |
|    4 | w5   |      2 |  2 | HR       | 12     |
|    5 | w6   |      2 |  2 | HR       | 12     |
|    6 | s7   |      3 |  3 | MK       | 13     |
|    7 | s8   |      4 |  4 | MIS      | 14     |
| NULL | NULL |   NULL |  5 | FD       | 15     |
+------+------+--------+----+----------+--------+
8 rows in set (0.00 sec)

left join without common part

  1. tbl_emp 表的独有部分：将 left join 结果集中的两表公共部分去掉即可：where d.id is null
  2. select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null;
select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null;
+----+------+--------+------+----------+--------+
| id | NAME | deptId | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+
1 row in set (0.00 sec)

right join without common part

  1. tbl_dept表的独有部分：将 right join 结果集中的两表公共部分去掉即可：where e.id is null
  2. select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.id is null;
select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.id is null;
+------+------+--------+----+----------+--------+
| id   | NAME | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| NULL | NULL |   NULL |  5 | FD       | 15     |
+------+------+--------+----+----------+--------+
1 row in set (0.00 sec)

full join

  1. 兄弟，mysql 不支持 full join ，但是我们可以通过骚操作实现 full join ，union 关键字用于连接结果集，并且自动去重

  2. tbl_emp 与 tbl_dept 的公共部分 + tbl_emp 表的独有部分 + tbl_dept表的独有部分：将 left join 的结果集和 right join 的结果集使用 union 合并即可

  3. select * from tbl_emp e left join tbl_dept d on e.deptId = d.id union select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;

select * from tbl_emp e left join tbl_dept d on e.deptId = d.id
    union
select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;
+------+------+--------+------+----------+--------+
| id   | NAME | deptId | id   | deptName | locAdd |
+------+------+--------+------+----------+--------+
|    1 | z3   |      1 |    1 | RD       | 11     |
|    2 | z4   |      1 |    1 | RD       | 11     |
|    3 | z5   |      1 |    1 | RD       | 11     |
|    4 | w5   |      2 |    2 | HR       | 12     |
|    5 | w6   |      2 |    2 | HR       | 12     |
|    6 | s7   |      3 |    3 | MK       | 13     |
|    7 | s8   |      4 |    4 | MIS      | 14     |
|    8 | s9   |     51 | NULL | NULL     | NULL   |
| NULL | NULL |   NULL |    5 | FD       | 15     |
+------+------+--------+------+----------+--------+
9 rows in set (0.00 sec)

full join without common part

  1. tbl_emp 表的独有部分 + tbl_dept表的独有部分
  2. select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null union select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.id is null;
select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null
    union
select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.id is null;
+------+------+--------+------+----------+--------+
| id   | NAME | deptId | id   | deptName | locAdd |
+------+------+--------+------+----------+--------+
|    8 | s9   |     51 | NULL | NULL     | NULL   |
| NULL | NULL |   NULL |    5 | FD       | 15     |
+------+------+--------+------+----------+--------+
2 rows in set (0.00 sec)

3、索引简介

3.1、索引是什么

索引是个什么东东？

  1. MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构
  2. 你可以简单理解为"排好序的快速查找数据结构"，即索引 = 排序 + 查找
  3. 一般来说索引本身占用内存空间也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上
  4. 我们平时所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉树)结构组织的索引。
  5. 聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引(hash index)等。

3.2、索引原理

将索引理解为"排好序的快速查找数据结构"

  1. 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
  2. 下图就是一种可能的索引方式示例：
    • 左边是数据表，一共有两列七条记录，最左边的十六进制数字是数据记录的物理地址
    • 为了加快col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

image-20200803222848380

3.3、索引优劣势

索引的优势

  1. 类似大学图书馆的书目索引，提高数据检索效率，降低数据库的IO成本
  2. 通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗

索引的劣势

  1. 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占用空间的
  2. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如果对表INSERT，UPDATE和DELETE。因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
  3. 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句

3.4、MySQL 索引分类

mysql 索引分类

参考资料：https://www.cnblogs.com/luyucheng/p/6289714.html

  1. 普通索引：是最基本的索引，它没有任何限制，即一个索引只包含单个列，一个表可以有多个单列索引；建议一张表索引不要超过5个，优先考虑复合索引
  2. 唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
  3. 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：
  4. 复合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
  5. 全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配

3.5、MySQL 索引语法

建立索引的 SQL 语句

  1. 创建索引：

    • 如果是CHAR和VARCHAR类型，length可以小于字段实际长度；
    • 如果是BLOB和TEXT类型，必须指定length。
CREATE [UNIQUE] INDEX  indexName ON mytable(columnname(length));
' or '
ALTER mytable ADD [UNIQUE]  INDEX [indexName] ON(columnname(length));

  1. 删除索引

DROP INDEX [indexName] ON mytable;

  1. 查看索引（\G表示将查询到的横向表格纵向输出，方便阅读）

SHOW INDEX FROM table_name\G

使用 ALTER 命令，有四种方式来添加数据表的索引：

  1. ALTER TABLE tbl_name ADD PRIMARY KEY(column_list)：该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
  2. ALTER TABLE tbl_name ADD UNIQUE index_name(column_list)：这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
  3. ALTER TABLE tbl_name ADD INDEX index_name(column_list)：.添加普通索引，索引值可出现多次。
  4. ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list)：该语句指定了索引为FULLTEXT，用于全文索引。

带你看看 mysql 索引：Index_type 为 BTREE

show index from tbl_emp;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_emp |          0 | PRIMARY    |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| tbl_emp |          1 | fk_dept_Id |            1 | deptId      | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

3.6、MySQL 索引结构

3.6.1、Btree 索引

Btree 索引搜索过程

【初始化介绍】

  1. 一颗 b 树， 浅蓝色的块我们称之为一个磁盘块， 可以看到每个磁盘块包含几个数据项（深蓝色所示） 和指针（黄色所示）
  2. 如磁盘块 1 包含数据项 17 和 35， 包含指针 P1、 P2、 P3
  3. P1 表示小于 17 的磁盘块， P2 表示在 17 和 35 之间的磁盘块， P3 表示大于 35 的磁盘块
  4. 真实的数据存在于叶子节点和非叶子节点中

【查找过程】

  1. 如果要查找数据项 29， 那么首先会把磁盘块 1 由磁盘加载到内存， 此时发生一次 IO， 在内存中用二分查找确定 29在 17 和 35 之间， 锁定磁盘块 1 的 P2 指针， 内存时间因为非常短（相比磁盘的 IO） 可以忽略不计
  2. 通过磁盘块 1的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存， 发生第二次 IO， 29 在 26 和 30 之间， 锁定磁盘块 3 的 P2 指针
  3. 通过指针加载磁盘块 8 到内存， 发生第三次 IO， 同时内存中做二分查找找到 29， 结束查询， 总计三次 IO。

image-20200804093236612

3.6.2、B+tree 索引

B+tree 索引搜索过程

【B+Tree 与 BTree 的区别】

B-树的关键字（数据项）和记录是放在一起的； B+树的非叶子节点中只有关键字和指向下一个节点的索引， 记录只放在叶子节点中。

【B+Tree 与 BTree 的查找过程】

  1. 在 B 树中， 越靠近根节点的记录查找时间越快， 只要找到关键字即可确定记录的存在； 而 B+ 树中每个记录的查找时间基本是一样的， 都需要从根节点走到叶子节点， 而且在叶子节点中还要再比较关键字。
  2. 从这个角度看 B 树的性能好像要比 B+ 树好， 而在实际应用中却是 B+ 树的性能要好些。 因为 B+ 树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比 B 树多， 树高比 B 树小， 这样带来的好处是减少磁盘访问次数。
  3. 尽管 B+ 树找到一个记录所需的比较次数要比 B 树多， 但是一次磁盘访问的时间相当于成百上千次内存比较的时间， 因此实际中B+ 树的性能可能还会好些， 而且 B+树的叶子节点使用指针连接在一起， 方便顺序遍历（范围搜索）， 这也是很多数据库和文件系统使用 B+树的缘故。

【性能提升】

真实的情况是， 3 层的 B+ 树可以表示上百万的数据， 如果上百万的数据查找只需要三次 IO， 性能提高将是巨大的，如果没有索引， 每个数据项都要发生一次 IO， 那么总共需要百万次的 IO， 显然成本非常非常高。

【思考： 为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引？】

  1. B+树的磁盘读写代价更低：B+树的内部结点并没有指向关键字具体信息的指针。 因此其内部结点相对 B 树更小。 如果把所有同一内部结点的关键字存放在同一盘块中， 那么盘块所能容纳的关键字数量也越多。 一次性读入内存中的需要查找的关键字也就越多。 相对来说 IO 读写次数也就降低了。
  2. B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点， 而只是叶子结点中关键字的索引。 所以任何关键字的查找必须走一条从根结点到叶子结点的路。 所有关键字查询的路径长度相同， 导致每一个数据的查询效率相当。

image-20200811145838930

3.7、何时需要建索引

哪些情况下适合建立索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询的条件的字段应该创建索引
  3. 查询中与其他表关联的字段，外键关系建立索引
  4. 频繁更新的字段不适合创建索引
  5. Where 条件里用不到的字段不创建索引
  6. 单间/组合索引的选择问题，Who？（在高并发下倾向创建组合索引）
  7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
  8. 查询中统计或者分组字段

哪些情况不要创建索引

  1. 表记录太少
  2. 经常增删改的表
  3. 数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

案例分析：

  1. 假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
  2. 索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。
  3. 一个索引的选择性越接近于1，这个索引的效率就越高。

4、性能分析

4.1、性能优化概述

MySQL Query Optimizer 的作用

  1. MySQL 中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（MySQL认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间）
  2. 当客户端向MySQL 请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer 首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query中的Hint信息（如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint 或Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

MySQL 常见瓶颈

  1. CPU 瓶颈：CPU在饱和的时候一般发生在数据装入在内存或从磁盘上读取数据时候
  2. IO 瓶颈：磁盘I/O瓶颈发生在装入数据远大于内存容量时
  3. 服务器硬件的性能瓶颈：top、free、iostat和vmstat来查看系统的性能状态

4.2、Explain 概述

Explain

是什么？Explain 是查看执行计划

  1. 使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈
  2. 官网地址：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

能干嘛？

  1. 表的读取顺序（id 字段）
  2. 数据读取操作的操作类型（select_type 字段）
  3. 哪些索引可以使用（possible_keys 字段）
  4. 哪些索引被实际使用（keys 字段）
  5. 表之间的引用（ref 字段）
  6. 每张表有多少行被优化器查询（rows 字段）

怎么玩？

  • Explain + SQL语句
explain select * from tbl_emp;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | tbl_emp | ALL  | NULL          | NULL | NULL    | NULL |    8 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

4.3、Explain 详解

id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

id 取值的三种情况：

  1. id相同，执行顺序由上至下

    image-20200804101016101

  2. id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

    image-20200804101005684

  3. id相同不同，同时存在：id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行；衍生=DERIVED

    image-20200804101502048

select_type：查询的类型，主要用于区别普通查询、联合查询、子查询等复杂查询

  1. SIMPLE：简单的select查询，查询中不包含子查询或者UNION
  2. PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
  3. SUBQUERY：在SELECT或者WHERE列表中包含了子查询
  4. DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里
  5. UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
  6. UNION RESULT：从UNION表获取结果的SELECT

UNION 和 UNION RESULT举例

explain
select * from tbl_emp e left join tbl_dept d on e.deptId = d.id
union
select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;
+----+--------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------------+
| id | select_type  | table      | type | possible_keys | key        | key_len | ref       | rows | Extra                                              |
+----+--------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------------+
|  1 | PRIMARY      | e          | ALL  | NULL          | NULL       | NULL    | NULL      |    8 | NULL                                               |
|  1 | PRIMARY      | d          | ALL  | PRIMARY       | NULL       | NULL    | NULL      |    5 | Using where; Using join buffer (Block Nested Loop) |
|  2 | UNION        | d          | ALL  | NULL          | NULL       | NULL    | NULL      |    5 | NULL                                               |
|  2 | UNION        | e          | ref  | fk_dept_Id    | fk_dept_Id | 5       | db01.d.id |    1 | NULL                                               |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL       | NULL    | NULL      | NULL | Using temporary                                    |
+----+--------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------------+
5 rows in set (0.00 sec)

table：显示这一行的数据是关于哪张表的

type：访问类型排列，显示查询使用了何种类型

  1. type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：system>const>eq_ref>ref>fultext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
  2. 挑重要的来说：system>const>eq_ref>ref>range>index>ALL，一般来说，得保证查询至少达到range级别，最好能达到ref。

从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL

  1. system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计

  2. const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量

    image-20200804103028065

  3. eq_ref：唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描

    image-20200804103543783

  4. ref：非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

    image-20200804103959011

  5. range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为他只需要开始索引的某一点，而结束于另一点，不用扫描全部索引

    image-20200804104130086

  6. index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘数据库文件中读的）

    image-20200804104254208

  7. all：FullTable Scan，将遍历全表以找到匹配的行（全表扫描）

    image-20200804104358142

  8. 备注：一般来说，得保证查询只是达到range级别，最好达到ref

possible_keys

  1. 显示可能应用在这张表中的索引，一个或多个
  2. 若查询涉及的字段上存在索引，则该索引将被列出，但不一定被查询实际使用

key

  1. 实际使用的索引，如果为null，则没有使用索引

  2. 若查询中使用了覆盖索引，则该索引仅出现在key列表中

    image-20200804105225100

key_len

  1. 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
  2. key_len显示的值为索引最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

image-20200804105833936

ref

  1. 显示索引哪一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值
  2. 由key_len可知t1表的索引idx_col1_col2被充分使用，t1表的col1匹配t2表的col1，t1表的col2匹配了一个常量，即’ac’

image-20200804110346982

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

image-20200804111249713

Extra：包含不适合在其他列中显示但十分重要的额外信息

  1. Using filesort（文件排序）：

    • MySQL中无法利用索引完成排序操作成为“文件排序”
    • 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取
    • 出现 Using filesort 不好（九死一生），需要尽快优化 SQL
    • 示例中第一个查询只使用了 col1 和 col3，原有索引派不上用场，所以进行了外部文件排序
    • 示例中第二个查询使用了 col1、col2 和 col3，原有索引派上用场，无需进行文件排序

    image-20200804111738931

  2. Using temporary（创建临时表）：

    • 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by
    • 出现 Using temporary 超级不好（十死无生），需要立即优化 SQL
    • 示例中第一个查询只使用了 col1，原有索引派不上用场，所以创建了临时表进行分组
    • 示例中第二个查询使用了 col1、col2，原有索引派上用场，无需创建临时表

    image-20200804112558915

  3. Using index（覆盖索引）：

    • 表示相应的select操作中使用了覆盖索引（Coveing Index），避免访问了表的数据行，效率不错！

    • 如果同时出现using where，表明索引被用来执行索引键值的查找

    • 如果没有同时出现using where，表明索引用来读取数据而非执行查找动作

      image-20200804113450147

    • 覆盖索引（Covering Index），也说为索引覆盖

      • 理解方式一：就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
      • 理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。
      • 注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select * ，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

  4. Using where：表明使用了where过滤

  5. Using join buffer：表明使用了连接缓存

  6. impossible where：where子句的值总是false，不能用来获取任何元组

    image-20200804113946144

  7. select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

  8. distinct：优化distinct，在找到第一匹配的元组后即停止找同样值的工作

Explain 热身 Case

  1. 第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为<derived3>，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name ...】
  2. 第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id, name from t1 where other_column= ' '】
  3. 第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】
  4. 第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行【select name, id from t2】
  5. 第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1, 4>表示用第一个和第四个select的结果进行union操作。【两个结果进行uinion操作】

image-20200804114203012

exodus3
关注
专栏目录
excel怎么启用宏_Excel高级应用案例五 学生成绩管理系统
weixin_39653766的博客
10-22 1090
此案例要求用Excel VBA 制作学生成绩管理系统，其中，系统有登录窗体界面、浏览查询数据、成绩输入以及统计总分、平均分等功能。(1) 将Sheet1、Sheet2、Sheet3工作表分别改名为“浏览”、“主界面”和“用户表”。将工作簿文件另存为“学生成绩管理.xlsm”，保存类型要选择“Excel启用宏的工作簿(*.xlsm)”(2)选择菜单“开...
excel制作窗体查询界面_Excel实战技巧37：制作淡入淡出的用户窗体效果
weixin_28987735的博客
12-27 2464
学习Excel技术，关注微信公众号：excelperfect本文将给出一段VBA代码，实现在加载用户窗体时逐步显现以及在关闭用户窗体时逐渐淡出的效果，如下图1所示。图1第1步：创建示例用户窗体如下图2所示，我们简单地创建了一个用户窗体，在其中放置了标签、文本框、复选框和按钮控件。图2第2步：添加代码在用户窗体中单击鼠标右键，选择快捷菜单中的“查看代码”命令。在用户窗体模块中输入下面的代码...
EXCEL制作的简单查询系统
12-03
EXCEL VBA通过access提取数据进行多字段查询
Django模型层之多表操作---Django–admin、多表增删改、多表查询(跨表查询、双下划线、聚合、分组、F查询、Q查询)
李波的blog，一名喜欢编程和分享技术的研究僧~
11-16 1422
Django模型层—多表操作 参考：https://www.cnblogs.com/clschao/articles/10439958.html 三种表关系：一对一、一对多、多对多。 创建模型 # models.py # 快速运行： # Tools-->run manage.py Task from django.db import models # Create your models here. from django.db import models # Create your mode
excel中vlookup函数的使用方法_老师必学技巧之“利用Excel表格-VLOOKUP函数制作一个查询系统”...
weixin_39870092的博客
10-14 512
相信老师们在日常办公中用得最多的工具要数Excel了,今天就和老师们聊聊如何用excel制作一个查询系统!这里我们需要用到VLOOKUP函数,VLOOKUP函数是Excel常用函数之一，今天用该函数做一个“学生成绩查询系统”用以说明VLOOKUP函数的使用方法。具体步骤如下:在制作查询系统前,我们先来认识一下VLOOKUP函数,VLOOKUP函数的用途主要是在表格或数值数组的首列查找指定的数值，并...
尚硅谷周阳Mysql高级思维导图脑图学习笔记.rar
07-04
【尚硅谷周阳Mysql高级思维导图脑图学习笔记】是针对MySQL数据库系统的一份高级学习资料，包含了丰富的知识体系，旨在帮助学习者深入理解并掌握MySQL的高级特性和优化技巧。这份资源以.mmap格式提供，这是一种专业的...
尚硅谷mysql笔记.zip
08-18
尚硅谷的MySQL笔记可能是为了帮助学习者深入理解和掌握MySQL的相关知识而编写的。这份笔记可能包含了从基础概念到高级特性的全面讲解，包括但不限于SQL语言、数据类型、数据库设计、索引、事务处理、视图、存储过程...
MySQL入门到高级-康师傅版.zip
08-30
4. **索引和优化**：了解索引的作用，学习创建和管理不同类型的索引（如B树、哈希等），以及如何优化查询性能。 5. **存储过程和函数**：学习编写存储过程和自定义函数，提高代码复用性和效率。 6. **视图和触发器...
尚硅谷MySQL高级学习笔记：存储引擎、SQL优化与索引详解
本资源是一份尚硅谷MySQL高级视频学习笔记，涵盖了多个关键知识点，旨在帮助用户深入理解并掌握MySQL高级操作。以下是对部分章节的详细解读： 1. Linux下MySQL编码设置：当在Linux环境下修改MySQL的字符集编码时，...
尚硅谷 mysql基础 学习笔记
Late_whale的博客 作者：代码搬运工老李的二师兄
06-13 1405
文章目录Mysql相关概念常见命令语法规范DQL(data query language)基础查询条件查询排序查询常见函数分组查询连接查询sql92sql99子查询标量子查询列子查询行子查询分页查询联合查询执行顺序DML(data managlation language)插入 insert修改 update删除 dropDDL(data define language)库表常见数据类型常见约束TCL(transaction control language)事物数据库隔离级别视图变量存储过程和函数流程控制
Excel VBA也可以设计精美界面的
10-21
开发程序时都需要写帮助，如何帮助即能表达需要的说明又美观？而且占用资源不能太大？ 提供4个精美界面： 程序界面演示.rar (378.94 KB)
ＥＸＣＥＬ窗体及控件实例
05-01
ＥＸＣＥＬ窗体及控件实例 非常适合初学者
万能数据库查询分析器使用EXCEL进行数据分析
11-29
大型数据库的数据展现方面功能普遍都比较弱,更不用说一些企业级乃至一些桌面数据库了。而万能数据库查询分析器能够将从各种数据库中获取的数据方便地Copy、导出到Excel中。Excel的强大的图表功能,使得它在数据展现方面的优势比较突出。 万能数据库查询分析器中英文版本《DB 查询分析器》、《DB Query Analyzer》均采用了EXE文件加壳技术。但会造成杀毒软件误将其当作木马病毒。 大家不需要担心。
excel制作窗体查询界面_Excel如何制作考勤表，你会做这样可以自动查询数据的考勤表吗？...
weixin_39702316的博客
12-03 912
Excel动态考勤表的制作，相信对于每一位HR来讲都是见得非常多的。今天我们就来学习一下，如何制作可以自动查询月份考勤数据的考勤表。我们来看一下效果图是怎么样的，如下图：如上图：我们每一张sheet页都代表了每个月的出勤情况，每个月的表格格式、登记人姓名工号都是一致的。现在我们需要在汇总表中单独选择下拉月份就可以调取每个月的数据。下面我们来学习一下。第一步：先在每张sheet页都固定一样的出勤登记...
excel制作窗体查询界面_利用 VBA窗体制作excel登录界面
weixin_39586335的博客
12-28 5557
thisworkbook代码Private Sub Workbook_Open()UserForm1.ShowEnd Subuserform代码Private Sub CommandButton1_Click()If TextBox1.Value = "张三" And TextBox2.Value = "1234" Then Application.Visible = True ...
access窗体中再制作查询窗体_Excel订单管理系统，窗体录入查询，快捷汇总统算，一键不加班...
weixin_39926193的博客
10-29 1065
Hello大家好，我是帮帮。今天跟大家分享一张Excel订单管理系统，窗体录入查询，快捷汇总统算，一键不加班。有个好消息！为了方便大家更快的掌握技巧，寻找捷径。请大家点击文章末尾的“了解更多”，在里面找到并关注我，里面有海量各类模板素材免费下载，我等着你噢^^メ大家请看范例图片，Excel订单管理系统，弹窗录入VBA操作，点击单元格连接跳转目标工作表。メメ先录入基础信息，按照表格设置录入信息，生成...
EXCEL：查询系统的制作与跨表引用
最新发布
2301_76815178的博客
04-11 2964
使用EXCEL进行查询系统和分表汇总
【EXCEL查询系统制作】
qq_45108376的博客
10-23 5284
EXCEL制作下拉菜单栏，同时后续表格数据根据所选择菜单数据变化而变化
Excel小技巧--高级漂亮的查询界面
cungudafa的博客
07-19 2855
文章目录一、前言二、小技巧系列1.快速求和2.文本格式统一转换为数字3.多列匹配三、高逼格1.锁住单元格2.取消网格线 一、前言 为什么断更这么久呢？因为每天沉迷于Ps和Excel，培训中，技能++1 最近的确没写代码了，不过一点都没落下手上功夫哦！ 出几篇办公教程！献上Excel系列。 excel类网页版查询。 二、小技巧系列 1.快速求和 方法一：自动求和 选中求和数据 和 总计空单元格，公式选中自动求和 即可。 举例： 方法二：快捷键Alt和= 举例： 选中求和数据 和 总计空单元格，快捷键Alt
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

exodus3

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值