MySQL
一、MySQL存储引擎
1.MySQL体系结构
-
体系结构的概念
- 任何一套系统当中，每个部件都能起到一定的作用！
- 例如：汽车。虽然由成千上万的零件构成，但是最重要的肯定是发动机。
- 普通家用车：发动机排量在0.5 ~ 2.0升之间，买菜用。
- 豪华跑车：发动机排量在2.0升以上，撩妹用。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IvkHNnVe-1663995733139)(MySQL.assets\image-20210313001702601.png)]
-
MySQL的体系结构
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ze357OkV-1663995733140)(MySQL.assets\image-20210313001639776.png)]
- 体系结构详解
- 客户端连接
- 支持接口：支持的客户端连接，例如C、Java、PHP等语言来连接MySQL数据库
- 第一层：网络连接层
- 连接池：管理、缓冲用户的连接，线程处理等需要缓存的需求。
- 例如：当客户端发送一个请求连接，会从连接池中获取一个连接进行使用。
- 第二层：核心服务层
- 管理服务和工具：系统的管理和控制工具，例如备份恢复、复制、集群等。
- SQL接口：接受SQL命令，并且返回查询结果。
- 查询解析器：验证和解析SQL命令，例如过滤条件、语法结构等。
- 查询优化器：在执行查询之前，使用默认的一套优化机制进行优化sql语句
- 缓存：如果缓存当中有想查询的数据，则直接将缓存中的数据返回。没有的话再重新查询！
- 第三层：存储引擎层
- 插件式存储引擎：管理和操作数据的一种机制，包括(存储数据、如何更新、查询数据等)
- 第四层：系统文件层
- 文件系统：配置文件、数据文件、日志文件、错误文件、二进制文件等等的保存
- 客户端连接
2.MySQL存储引擎
-
引擎的概念
- 生活中，引擎就是整个机器运行的核心，不同的引擎具备不同的功能，应用于不同的场景之中，如下图：
- 假设：直升机需要50排量的发动机，民用客机需要30排量的发动机，战斗机需要70排量的发动机，火箭需要200排量的发动机。在不同场景下、都需要选择不同排量的发动机。我们不能在火箭上安装一个民用发动机作为引擎，这样的话别说上月球了，连能否飞出大气层都不一定！
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-h0GhD4rD-1663995733141)(MySQL.assets\image-20210313001744236.png)]
-
MySQL存储引擎的概念
- MySQL数据库使用不同的机制存取表文件 , 机制的差别在于不同的存储方式、索引技巧、锁定水平以及广泛的不同的功能和能力，在MySQL中 , 将这些不同的技术及配套的功能称为存储引擎
- 在关系型数据库中数据的存储是以表的形式存进行储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。
- Oracle , SqlServer等数据库只有一种存储引擎 , 而MySQL针对不同的需求, 配置MySQL的不同的存储引擎 , 就会让数据库采取了不同的处理数据的方式和扩展功能。
- 通过选择不同的引擎 ,能够获取最佳的方案 , 也能够获得额外的速度或者功能，提高程序的整体效果。所以了解引擎的特性 , 才能贴合我们的需求 , 更好的发挥数据库的性能。
-
MySQL支持的存储引擎
- MySQL5.7支持的引擎包括：InnoDB、MyISAM、MEMORY、Archive、Federate、CSV、BLACKHOLE等
- 其中较为常用的有三种：InnoDB、MyISAM、MEMORY
3.常用引擎的特性对比
- 常用的存储引擎
- MyISAM存储引擎
- 访问快,不支持事务和外键。表结构保存在.frm文件中，表数据保存在.MYD文件中，索引保存在.MYI文件中。
- InnoDB存储引擎(MySQL5.5版本后默认的存储引擎)
- 支持事务 ,占用磁盘空间大 ,支持并发控制。表结构保存在.frm文件中，如果是共享表空间，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。如果是多表空间存储，每个表的数据和索引单独保存在 .ibd 中。
- MEMORY存储引擎
- 内存存储 , 速度快 ,不安全 ,适合小量快速访问的数据。表结构保存在.frm中。
- MyISAM存储引擎
- 特性对比
| 特性 | MyISAM | InnoDB | MEMORY |
|---|---|---|---|
| 存储限制 | 有(平台对文件系统大小的限制) | 64TB | 有(平台的内存限制) |
| 事务安全 | 不支持 | 支持 | 不支持 |
| 锁机制 | 表锁 | 表锁/行锁 | 表锁 |
| B+Tree索引 | 支持 | 支持 | 支持 |
| 哈希索引 | 不支持 | 不支持 | 支持 |
| 全文索引 | 支持 | 支持 | 不支持 |
| 集群索引 | 不支持 | 支持 | 不支持 |
| 数据索引 | 不支持 | 支持 | 支持 |
| 数据缓存 | 不支持 | 支持 | N/A |
| 索引缓存 | 支持 | 支持 | N/A |
| 数据可压缩 | 支持 | 不支持 | 不支持 |
| 空间使用 | 低 | 高 | N/A |
| 内存使用 | 低 | 高 | 中等 |
| 批量插入速度 | 高 | 低 | 高 |
| 外键 | 不支持 | 支持 | 不支持 |
4.引擎的操作
- 查询数据库支持的引擎
-- 标准语法
SHOW ENGINES;
-- 查询数据库支持的存储引擎
SHOW ENGINES;
-- 表含义:
- support : 指服务器是否支持该存储引擎
- transactions : 指存储引擎是否支持事务
- XA : 指存储引擎是否支持分布式事务处理
- Savepoints : 指存储引擎是否支持保存点
- 查询某个数据库中所有数据表的引擎
-- 标准语法
SHOW TABLE STATUS FROM 数据库名称;
-- 查看leadnews_admin数据库所有表的存储引擎
SHOW TABLE STATUS FROM leadnews_admin;
- 查询某个数据库中某个数据表的引擎
-- 标准语法
SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称';
-- 查看数据库中stu_score表的存储引擎
SHOW TABLE STATUS FROM leadnews_admin WHERE NAME = 'ad_channel';
- 创建数据表，指定存储引擎
-- 标准语法
CREATE TABLE 表名(
列名,数据类型,
...
)ENGINE = 引擎名称;
-- 创建db01数据库
CREATE DATABASE db01;
-- 使用db11数据库
USE db01;
-- 创建engine_test表，指定存储引擎为MyISAM
CREATE TABLE engine_test(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10)
)ENGINE = MYISAM;
-- 查询engine_test表的引擎
SHOW TABLE STATUS FROM db01 WHERE NAME = 'engine_test';
- 修改表的存储引擎
-- 标准语法
ALTER TABLE 表名 ENGINE = 引擎名称;
-- 修改engine_test表的引擎为InnoDB
ALTER TABLE engine_test ENGINE = INNODB;
-- 查询engine_test表的引擎
SHOW TABLE STATUS FROM db01 WHERE NAME = 'engine_test';
5.总结：引擎的选择
- MyISAM ：由于MyISAM不支持事务、不支持外键、支持全文检索和表级锁定，读写相互阻塞，读取速度快，节约资源，所以如果应用是以查询操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
- InnoDB : 是MySQL的默认存储引擎， 由于InnoDB支持事务、支持外键、行级锁定 ，支持所有辅助索引(5.5.5后不支持全文检索)，高缓存，所以用于对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作，那么InnoDB存储引擎是比较合适的选择，比如BBS、计费系统、充值转账等
- MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
- 总结：针对不同的需求场景，来选择最适合的存储引擎即可！如果不确定、则使用数据库默认的存储引擎！
二、MySQL索引
使用索引的前提：
数据量要达到百万级别
假如表的数据频繁新增、删除、更新，不建议使用索引
查询多的，并且新增、删除、更新少的，才添加索引
1.索引的概念
- 我们之前学习过集合，其中的ArrayList集合的特点之一就是有索引。那么有索引会带来哪些好处呢？
- 没错，查询数据快！我们可以通过索引来快速查找到想要的数据。那么对于我们的MySQL数据库中的索引功能也是类似的！
- MySQL数据库中的索引：是帮助MySQL高效获取数据的一种数据结构！所以，索引的本质就是数据结构。
- 在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
- 一张数据表，用于保存数据。 一个索引配置文件，用于保存索引，每个索引都去指向了某一个数据(表格演示)
- 举例，无索引和有索引的查找原理
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QbM9qNBQ-1663995733141)(MySQL.assets\image-20210313001806710.png)]
2.索引的分类
- 功能分类
- 普通索引： 最基本的索引，它没有任何限制。
- 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。
- 主键索引：一种特殊的唯一索引，不允许有空值。一般在建表时同时创建主键索引。
- 组合索引：顾名思义，就是将单列索引进行组合。
- 外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。
- 全文索引：快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。
- 结构分类
- B+Tree索引 ：MySQL使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎默认的索引类型。
- Hash索引 : MySQL中Memory存储引擎默认支持的索引类型。
3.索引的操作
- 数据准备
-- 创建db02数据库
CREATE DATABASE db02;
-- 使用db02数据库
USE db02;
-- 创建student表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
age INT,
score INT
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,98),(NULL,'李四',24,95),
(NULL,'王五',25,96),(NULL,'赵六',26,94),(NULL,'周七',27,99);
- 创建索引
- 注意：如果一个表中有一列是主键，那么就会默认为其创建主键索引！(主键列不需要单独创建索引)
-- 标准语法
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称
[USING 索引类型] -- 默认是B+TREE
ON 表名(列名...);
-- 为student表中姓名列创建一个普通索引
CREATE INDEX idx_name ON student(NAME);
-- 为student表中年龄列创建一个唯一索引
CREATE UNIQUE INDEX idx_age ON student(age);
- 查看索引
-- 标准语法
SHOW INDEX FROM 表名;
-- 查看student表中的索引
SHOW INDEX FROM student;
- alter语句添加索引
-- 普通索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名);
-- 组合索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);
-- 主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(主键列名);
-- 外键索引(添加外键约束，就是外键索引)
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
-- 唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);
-- 全文索引(mysql只支持文本类型)
ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);
-- 为student表中name列添加全文索引
ALTER TABLE student ADD FULLTEXT idx_fulltext_name(name);
-- 查看student表中的索引
SHOW INDEX FROM student;
- 删除索引
-- 标准语法
DROP INDEX 索引名称 ON 表名;
-- 删除student表中的idx_score索引
DROP INDEX idx_score ON student;
-- 查看student表中的索引
SHOW INDEX FROM student;
4.索引效率的测试
-- 创建product商品表
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT, -- 商品id
NAME VARCHAR(10), -- 商品名称
price INT -- 商品价格
);
-- 定义存储函数，生成长度为10的随机字符串并返回
DELIMITER $
CREATE FUNCTION rand_string()
RETURNS VARCHAR(255)
BEGIN
DECLARE big_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';
DECLARE small_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 1;
WHILE i <= 10 DO
SET small_str =CONCAT(small_str,SUBSTRING(big_str,FLOOR(1+RAND()*52),1));
SET i=i+1;
END WHILE;
RETURN small_str;
END$
DELIMITER ;
-- 定义存储过程，添加100万条数据到product表中
DELIMITER $
CREATE PROCEDURE pro_test()
BEGIN
DECLARE num INT DEFAULT 1;
WHILE num <= 1000000 DO
INSERT INTO product VALUES (NULL,rand_string(),num);
SET num = num + 1;
END WHILE;
END$
DELIMITER ;
-- 查询存储过程
show procedure status;
-- 调用存储过程
CALL pro_test();
-- 查询总记录条数
SELECT COUNT(*) FROM product;
-- 查询product表的索引
SHOW INDEX FROM product;
-- 查询name为OkIKDLVwtG的数据 (0.049)
SELECT * FROM product WHERE NAME='OkIKDLVwtG';
-- 通过id列查询OkIKDLVwtG的数据 (1毫秒)
SELECT * FROM product WHERE id=999998;
-- 为name列添加索引
ALTER TABLE product ADD INDEX idx_name(NAME);
-- 查询name为OkIKDLVwtG的数据 (0.001)
SELECT * FROM product WHERE NAME='OkIKDLVwtG';
/*
范围查询
*/
-- 查询价格为800~1000之间的所有数据 (0.052)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000;
/*
排序查询
*/
-- 查询价格为800~1000之间的所有数据,降序排列 (0.083)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000 ORDER BY price DESC;
-- 为price列添加索引
ALTER TABLE product ADD INDEX idx_price(price);
-- 查询价格为800~1000之间的所有数据 (0.011)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000;
-- 查询价格为800~1000之间的所有数据,降序排列 (0.001)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000 ORDER BY price DESC;
5.索引的实现原则
- 索引是在MySQL的存储引擎中实现的，所以每种存储引擎的索引不一定完全相同，也不是所有的引擎支持所有的索引类型。这里我们主要介绍InnoDB引擎的实现的B+Tree索引。
- B+Tree是一种树型数据结构，是B-Tree的变种。通常使用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序。我们逐步的来了解一下。
5.1磁盘存储
- 系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的
- 位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
- InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB。
- InnoDB引擎将若干个地址连接磁盘块，以此来达到页的大小16KB，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。
5.2BTree
-
BTree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述BTree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。BTree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的BTree：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3PPmYN1Q-1663995733142)(MySQL.assets\image-20210313001828655.png)]
-
根据图中结构显示，每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。
查找顺序：
模拟查找15的过程 :
1.根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字15在区间（<17），找到磁盘块1的指针P1。
2.P1指针找到磁盘块2，读入内存。【磁盘I/O操作第2次】
比较关键字15在区间（>12），找到磁盘块2的指针P3。
3.P3指针找到磁盘块7，读入内存。【磁盘I/O操作第3次】
在磁盘块7中找到关键字15。
-- 分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。
-- 由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个BTree查找效率的决定因素。BTree使用较少的节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。
5.3B+Tree
- B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
- 从上一节中的BTree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。
- B+Tree相对于BTree区别：
- 非叶子节点只存储键值信息。
- 所有叶子节点之间都有一个连接指针。
- 数据记录都存放在叶子节点中。
- 将上一节中的BTree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aStmcngJ-1663995733142)(MySQL.assets\image-20210313001845962.png)]
通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：
- 【有范围】对于主键的范围查找和分页查找
- 【有顺序】从根节点开始，进行随机查找
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在**24**层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要13次磁盘I/O操作。
扩展 ：
数据库中的B+Tree索引可以分为聚集索引（clustered index）和 非聚集索引：（聚簇索引和非聚簇索引的区别）
- 聚集索引：数据行的物理存储顺序与索引列（一般是主键列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。好比字典中的首字母与文字的顺序。MySQL中，通常主键索引就是聚集索引。
- 非聚集索引：数据行的物理存储顺序与索引列的逻辑顺序不同，一个表中可以拥有多个非聚集索引。好比字典中的偏旁部首与文字的顺序。MySQL中，普通索引，唯一索引，全文索引等都是非聚集索引。
- 回表：第一次搜索 B+Tree 拿到主键值后再去搜索聚集索引的 B+Tree，这个过程就是回表。
6.总结：索引的设计原则
索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。
- 创建索引时的原则
- 对查询频次较高，且数据量比较大的表建立索引。
- 使用唯一索引，区分度越高，使用索引的效率越高。枚举
- 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
- 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
- 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
- 联合索引的特点
在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，
对列name列、address和列phone列建一个联合索引
ALTER TABLE user ADD INDEX index_three(name,address,phone);
联合索引index_three实际建立了(name)、(name,address)、(name,address,phone)三个索引。所以下面的三个SQL语句都可以命中索引。
SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';
SELECT * FROM user WHERE name = '张三' AND address = '北京';
SELECT * FROM user WHERE name = '张三';
SELECT * FROM user WHERE address = '张三' AND name = '北京';
上面三个查询语句执行时会依照最左前缀匹配原则，检索时分别会使用索引
(name,address,phone)
(name,address)
(name)
进行数据匹配。
索引的字段可以是任意顺序的，如：
-- 优化器会帮助我们调整顺序，下面的SQL语句都可以命中索引
SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';
Mysql的优化器会帮助我们调整where条件中的顺序，以匹配我们建立的索引。
联合索引中最左边的列不包含在条件查询中，所以根据上面的原则，下面的SQL语句就不会命中索引。
-- 联合索引中最左边的列不包含在条件查询中，下面的SQL语句就不会命中索引
SELECT * FROM user WHERE address = '北京' AND phone = '12345';
三、优化SQL性能分析
在应用的的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式
上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时
这些有问题的 SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化
当面对一个有 SQL 性能问题的数据库时，我们应该从何处入手来进行系统的分析，使得能够尽快定位问题 SQL 并
尽快解决问题。
1 SQL执行频率
MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YoPuQp8z-1663995733143)(MySQL.assets/image-20220121141244135.png)]
Com_delete: 删除次数
Com_insert: 插入次数
Com_select: 查询次数
Com_update: 更新次数
查看Innodb引擎的sql执行频次
show status like 'Innodb_rows_%';
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fguQ9T3S-1663995733143)(MySQL.assets/image-20220402010719447.png)]
2 定位低效率执行SQL
可以通过以下两种方式定位执行效率较低的 SQL 语句。
-
慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句
mysql中的my.ini 或my.cnf文件中修改
log-output=FILE #输出为文件 slow-query-log=1 # 开启慢查询日志 slow_query_log_file="LAPTOP-EK2VLALR-slow.log" #指定文件名称 long_query_time=2 # sql执行时间阈值 单位：秒 -
show processlist : 慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询
日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否
锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。show processlist[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fToRKcN3-1663995733143)(MySQL.assets/image-20220402010831697.png)]
1 ） id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
2） user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
3） host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
4） db列，显示这个进程目前连接的是哪个数据库5） command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）
6） time列，显示这个状态持续的时间，单位是秒
7） state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一
个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
8） info列，显示这个sql语句，是判断问题语句的一个重要依据
3 profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：
SELECT @@have_profiling ;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nMA00sqn-1663995733144)(MySQL.assets/image-20220121144002520.png)]
可以看到，当前MySQL是支持 profile操作的，但是开关是关闭的。可以通过set语句在session/global级别开启profiling：
SET profiling = 1;
开关已经打开了，接下来，我们所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去了。 我们直接执行如下的SQL语句：
select * from tb_user;
select * from tb_user where id = 1;
select count(*) from tb_sku;
执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：
-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
查看每一条SQL的耗时情况:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KFPjkq4P-1663995733144)(MySQL.assets/image-20220121144638165.png)]
查看指定SQL各个阶段的耗时情况 :
**[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GQ33Xoom-1663995733144)(MySQL.assets/image-20220121145252211.png)] **
4 explain
4.1 环境准备
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Kfa2l74G-1663995733145)(MySQL.assets/image-20220402012201100.png)]
CREATE TABLE `t_role` (
`id` varchar(32) NOT NULL,
`role_name` varchar(255) DEFAULT NULL,
`role_code` varchar(255) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_user` (
`id` varchar(32) NOT NULL,
`username` varchar(45) NOT NULL,
`password` varchar(96) NOT NULL,
`name` varchar(45) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `user_role` (
`id` int(11) NOT NULL auto_increment ,
`user_id` varchar(32) DEFAULT NULL,
`role_id` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_ur_user_id` (`user_id`),
KEY `fk_ur_role_id` (`role_id`),
CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON
DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON
DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `t_user` (`id`, `username`, `password`, `name`)
values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超级管理员');
insert into `t_user` (`id`, `username`, `password`, `name`)
values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系统管理员');
insert into `t_user` (`id`, `username`, `password`, `name`)
values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
insert into `t_user` (`id`, `username`, `password`, `name`)
values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学生1');
insert into `t_user` (`id`, `username`, `password`, `name`)
values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','学生2');
insert into `t_user` (`id`, `username`, `password`, `name`)
values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老师1');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','生','student','学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`)
VALUES('7','老师','teacher','老师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`)
VALUES('8','教学管理员','teachmanager','教学管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`)
VALUES('9','管理员','admin','管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`)
VALUES('10','超级管理员','super','超级管理员');
INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),
(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;
4.2 explain 之 id
id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id 情况有三种
1） id 相同表示加载表的顺序是从上到下。
explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id
and u.id = ur.user_id ;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5D5yV9Nt-1663995733145)(MySQL.assets/image-20220402012635611.png)]
2） id 不同id值越大，优先级越高，越先被执行。
EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'));
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xIr4Ehpv-1663995733146)(MySQL.assets/image-20220402012654326.png)]
3） id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。
EXPLAIN SELECT * FROM t_role r, (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a
WHERE r.id = a.role_id AND r.role_name = (SELECT r.role_name FROM t_role r WHERE r.role_name = '教学管理员')
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jW7bVZ1M-1663995733146)(MySQL.assets/image-20220821021342831.png)]
4.3 explain 之 select_type
表示 SELECT 的类型，常见的取值，如下表所示：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jENGPl2D-1663995733146)(MySQL.assets/image-20220402012749725.png)]
union 去重聚合查询，多个查询，返回的数据结构必须一致 （效率不高，有去重的操作）
union all 不去重聚合查询 （效率更高）
4.4 explain 之 table
展示这一行的数据是关于哪一张表的
4.5 explain 之 type
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IQytALGs-1663995733146)(MySQL.assets/image-20220402012831557.png)]
结果值从最好到最坏以此是：
NULL > 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
一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref 。
4.6 explain 之 key
possible_keys : 显示可能应用在这张表的索引， 一个或多个。
key ： 实际使用的索引， 如果为NULL， 则没有使用索引。
key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。
4.7 explain 之 rows
扫描行的数量。
4.8 explain 之 extra
其他的额外的执行计划信息，在该列展示 。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Yg0DsSQc-1663995733147)(MySQL.assets/image-20220402013043923.png)]
四、避免索引失效
准备环境
create table `tb_seller` (
`sellerid` varchar (100),
`name` varchar (100),
`nickname` varchar (50),
`password` varchar (60),
`status` varchar (1),
`address` varchar (100),
`createtime` datetime,
primary key(sellerid)
)engine=innodb default charset=utf8;
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) VALUES('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) VALUES('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) VALUES('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) VALUES('itcast','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) VALUES('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) VALUES('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) VALUES('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) VALUES('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) VALUES('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) VALUES('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) VALUES('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`,
`address`, `createtime`) VALUES('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
最左匹配规则
以上已经给tb_seller表创建了一个复合索引
避免索引失效情况分析
1). 全值匹配 ，对索引中所有列都指定具体值。
该情况下，索引生效，执行效率高。
explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yNzKFuKD-1663995733147)(MySQL.assets/image-20220402015120643.png)]
2). 最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。
匹配最左前缀法则，走索引：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sUDbfxPr-1663995733147)(MySQL.assets/image-20220402015342903.png)]
违法最左前缀法则 ， 索引失效：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1FljzpsM-1663995733148)(MySQL.assets/image-20220402015405424.png)]
如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6WVX3m56-1663995733148)(MySQL.assets/image-20220402015425744.png)]
3). 范围查询右边的列，不能使用索引 。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-d1Pxht8J-1663995733148)(MySQL.assets/image-20220402015442589.png)]
根据前面的两个字段 name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。
4). 不要在索引列上进行运算操作， 索引将失效。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nLXAQZEi-1663995733149)(MySQL.assets/image-20220402015507834.png)]
5). 字符串不加单引号，造成索引失效。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CGjbM97j-1663995733149)(MySQL.assets/image-20220402015524822.png)]
由于，在查询是，没有对字符串加单引号， MySQL的查询优化器，会自动的进行类型转换，造成索引失效。
6). 尽量使用覆盖索引，避免select *
尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0xPgSzlA-1663995733149)(MySQL.assets/image-20220402015553678.png)]
如果查询列，超出索引列，也会降低性能。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SacEJ86M-1663995733150)(MySQL.assets/image-20220402015610221.png)]
TIP :
using index ：使用覆盖索引的时候就会出现
using where：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition：查找使用了索引，但是需要回表查询数据
using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表
查询数据
7). 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ：
explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00';
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BF1Q9yzR-1663995733150)(MySQL.assets/image-20220402015711415.png)]
8). 以%开头的Like模糊查询，索引失效。
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dtEGrzpO-1663995733151)(MySQL.assets/image-20220402015729911.png)]
解决方案 ：
通过覆盖索引来解决
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BvN7mc6F-1663995733151)(MySQL.assets/image-20220402015748294.png)]
9). 如果MySQL评估使用索引比全表更慢，则不使用索引。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-guol0Lzs-1663995733151)(MySQL.assets/image-20220402015838310.png)]
10). is NULL ， is NOT NULL 有时索引失效。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZeWKnz40-1663995733152)(MySQL.assets/image-20220402015859587.png)]
11). in 走索引， not in 索引失效。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oPAL7PH8-1663995733152)(MySQL.assets/image-20220402015915816.png)]
12). 单列索引和复合索引。
尽量使用复合索引，而少使用单列索引 。
创建复合索引
create index idx_name_sta_address on tb_seller(name, status, address);
就相当于创建了三个索引 ：
name
name + status
name + status + address
，但是需要回表查询数据
using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表
查询数据
7). 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ：
```sql
explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00';
[外链图片转存中…(img-BF1Q9yzR-1663995733150)]
8). 以%开头的Like模糊查询，索引失效。
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
[外链图片转存中…(img-dtEGrzpO-1663995733151)]
解决方案 ：
通过覆盖索引来解决
[外链图片转存中…(img-BvN7mc6F-1663995733151)]
9). 如果MySQL评估使用索引比全表更慢，则不使用索引。
[外链图片转存中…(img-guol0Lzs-1663995733151)]
10). is NULL ， is NOT NULL 有时索引失效。
[外链图片转存中…(img-ZeWKnz40-1663995733152)]
11). in 走索引， not in 索引失效。
[外链图片转存中…(img-oPAL7PH8-1663995733152)]
12). 单列索引和复合索引。
尽量使用复合索引，而少使用单列索引 。
创建复合索引
create index idx_name_sta_address on tb_seller(name, status, address);
就相当于创建了三个索引 ：
name
name + status
name + status + address
扫一扫
3654
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
