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, '銷售部');

推薦閱讀