浙江龙网

  1. 首页 > 生活百科 > >

一文读懂 MySQL 索引( 三 )

生活百科

5 索引优化5.1 慢查询5.1.1 简介慢查询日志是MySQL 提供的日志记录，用来记录所有的慢 SQL 语句，我们可以通过设置慢查询的时间阈值 long_query_time，来定义什么样的 SQL 是慢 SQL 。通过慢查询日志我们可以找出需要优化的 SQL，下一步就是进行 SQL 优化
5.1.2 慢查询配置第一步：我们可以通过 show variables like 'slow_query_log' 语句查询慢查询是否开启，默认是关闭（OFF）

一文读懂 MySQL 索引

文章插图
slow_query_log_file 是慢查询日志存放的位置 ， 如果是 window 的话，通常在你的安装文件夹 Data 目录下
第二步：打开慢查询
set global slow_query_log= 1;第三步：设置慢查询阈值
什么样的查询叫做慢查询呢？1s，5s 还是 10s，这点 MySQL 不知道，所以需要我们通过配置去设置 long_query_time 参数
一文读懂 MySQL 索引

文章插图
通过命令 show variables like '%long_query_time%' 查看慢查询时间，默认是 10 s
如果需要修改，可以通过命令 set global long_query_time = 5 来设置
一文读懂 MySQL 索引

文章插图
注意：这里通过set global long_query_time = 5 设置完慢查询时间后，再次查询发现慢查询时间依然是 10s，难道是设置没生效？
使用此命令修改后 ， 需要重新连接或者新开启一个会话就可以看到修改后的配置
一文读懂 MySQL 索引

文章插图
或者通过 show global variables like '%long_query_time%' 命令查看
5.1.3 慢查询日志分析我们刚才已经将慢查询阈值设置为 5s，现在我们执行一条这样的 sql 语句
select sleep(6);这条语句执行时间为 6s ， 我们打开慢查询日志可以发现增加了一些数据
# Time: 2022-10-02T09:16:23.194396Z# User@Host: root[root] @ localhost [::1]Id:6# Query_time: 6.011569Lock_time: 0.000000 Rows_sent: 1Rows_examined: 0SET timestamp=1664675770;select sleep(6);我们来逐个分析一下每行代表什么含义：
User@Host：执行该 SQL 的用户和慢查询 IP 地址
Query_time：语句执行时长
Lock_time：获取锁的时长
Rows_sent：MySQL 返回给客户端的行数
Rows_examined：MySQL 扫描行数
timestamp：表示慢 SQL 记录时的时间戳
select sleep(6)：则是慢查询 SQL
下面我们来分析一条真实的慢查询 SQL，之前测试时的一条 SQL 语句
# Time: 2022-07-27T09:26:44.440318Z# User@Host: root[root] @ localhost [127.0.0.1]Id:249# Query_time: 68.461112Lock_time: 0.000938 Rows_sent: 877281Rows_examined: 877303SET timestamp=1658914004;SELECTid,prd_line_id,shift_name,shift_id,app_id,weight,upload_time,operator,status,prd_line_nameFROM prd_weightWHERE (upload_time > '2022-07-27 00:00' AND upload_time < '2022-07-27 17:24');Query_time：总查询时长 68.461112s
Lock_time：0.000938s
Rows_examined：扫描行 877281
Rows_sent：返回了 877303
当然了，这是测试用的，生产上一般不会出现这么离谱的 SQL 语句
5.1.4 注意事项
  1. 在 MySQL 中，慢查询日志中默认不记录管理语句 ， 如：alter table, ， analyze table，check table 等 。不过可通过以下属性进行设置：set global log_slow_admin_statements = "ON"
  2. 在 MySQL 中，还可以设置将未走索引的 SQL 语句记录在慢日志查询文件中(默认为关闭状态) 。通过下述属性即可进行设置：set global log_queries_not_using_indexes = "ON"
  3. 在 MySQL 中，日志输出格式有支持：FILE(默认)，TABLE 两种 ， 可进行组合使用 。如下所示:set global log_output = "FILE,TABLE"这样设置会同时在 FILE ，  MySQL 库中的 slow_log 表中同时写入 。但是日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能 ， 那么建议优先记录到文件 。
5.2 Explain 执行计划通过上面的慢查询日志分析 ， 我们可以知道有哪些慢 SQL 语句 。但是这些 SQL 具体慢在哪里 ， 需要如何优化，我们还需要更详细的分析计划，这里 MySQL 给我们提供了 Explain 关键字，通过该关键字我们可以分析出 SQL 语句的详细执行信息 。
5.2.1 Explain 使用我们在数据库中创建一张 user 表用于测试
SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for user-- ----------------------------DROP TABLE IF EXISTS `user`;CREATE TABLE `user`(`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`password` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`dept_id` int(10) NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE,INDEX `idx_name`(`name`) USING BTREE,INDEX `idx_dept_id`(`dept_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of user-- ----------------------------INSERT INTO `user` VALUES (1, '张三', '123', '男', '12323432', 1);INSERT INTO `user` VALUES (2, '李四', '456', '男', '178873937', 1);INSERT INTO `user` VALUES (3, '小花', '123', '女', '1988334554', 2);INSERT INTO `user` VALUES (4, '小芳', '334', '女', '18765287937', 2);INSERT INTO `user` VALUES (5, NULL, '122', NULL, NULL, NULL);DROP TABLE IF EXISTS `dept`;CREATE TABLE `dept`(`id` int(11) NOT NULL AUTO_INCREMENT,`dept_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of dept-- ----------------------------INSERT INTO `dept` VALUES (1, '开发部');INSERT INTO `dept` VALUES (2, '销售部');

推荐阅读


上一篇：springboot H2 linux下搭建使用

下一篇：Docker | Compose创建mysql容器