10-1 MySQL 索引優化與查詢優化

10-1 MySQL 索引優化與查詢優化

文章目錄

  • 10-1 MySQL 索引優化與查詢優化
  • 1. 數據準備
  • 2. 索引失效案例
    • 2.1 索引字段：全值匹配最優
    • 2.2 索引字段：最佳左前綴法則
    • 2.3 主鍵插入順序
    • 2.4 索引字段進行了：計算、函數、類型轉換(自動或手動)導致索引失效
    • 2.5 索引字段類型轉換導致索引失效
    • 2.6 索引字段：使用了范圍條件，右邊的列索引失效
    • 2.7 索引字段：不等于(!= 或者<>)索引失效
    • 2.8 索引字段： is null可以使用索引，is not null無法使用索引
    • 2.9 索引字段：使用了 like以通配符 % 開頭索引失效
    • 2.10 OR 前后存在非索引的列，索引失效
    • 2.11 數據庫和表的字符集統一使用utf8mb4
  • 3. 關聯查詢優化
    • 3.1 數據準備
    • 3.2采用左外連接
    • 3.3 采用內連接
  • 4. join語句原理
  • 5. 子查詢優化
  • 6. 排序優化
  • 最后：

這篇文章是我蹲在《尚硅谷》-康師傅博主家的 WiFi 上(不是)，連夜 Ctrl+C / V 倆的鎮站神文。

這篇轉載只是為了，跟大家分享好內容，沒有任何商業用途。如果你喜歡這篇文章，請一定要去原作者 B站《尚硅谷-MySQL從菜鳥到大牛》看看，說不定還能發現更多寶藏內容呢！

1. 數據準備

CREATE DATABASE dbtest4;

學員表 插 50萬 條， 班級表 插 1萬 條。

步驟1：建表


USE dbtest4;CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

步驟2：設置參數

命令開啟：允許創建函數設置：

set global log_bin_trust_function_creators=1;    # 不加global只是當前窗口有效。

步驟3：創建函數

保證每條數據都不同。


#隨機產生字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;#假如要刪除
#drop function rand_string;

隨機產生班級編號

#用于隨機產生多少到多少的編號
DELIMITER //
CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;#假如要刪除
#drop function rand_num;

步驟4：創建存儲過程


#創建往stu表中插入數據的存儲過程
DELIMITER //
CREATE PROCEDURE insert_stu(startt INT ,  max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;SET autocommit =0;#設置手動提交事務REPEAT #循環SET i = i + 1; #賦值INSERT INTO student (stuno, NAME ,age ,classId ) VALUES ((startt+i),rand_string(6),rand_num(1,50),rand_num(1,1000));UNTIL i = max_num END REPEAT; COMMIT; #提交事務
END //
DELIMITER ;#假如要刪除
# drop PROCEDURE insert_stu;

創建往 class 表中插入數據的存儲過程

#執行存儲過程，往class表添加隨機數據
DELIMITER //
CREATE PROCEDURE `insert_class`(  max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i + 1;INSERT INTO class ( classname,address,monitor ) VALUES (rand_string(8),rand_string(10),rand_num(1,100000));UNTIL i = max_numEND REPEAT;COMMIT;
END //
DELIMITER ;#假如要刪除
#drop PROCEDURE insert_class;

步驟5：調用存儲過程

class

#執行存儲過程，往class表添加1萬條數據
CALL insert_class(10000);

在這里插入圖片描述

stu

#執行存儲過程，往stu表添加50萬條數據
CALL insert_stu(100000,500000);

在這里插入圖片描述

步驟6：刪除某表上的索引

創建存儲過程


DELIMITER //
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGINDECLARE done INT DEFAULT 0;DECLARE ct INT DEFAULT 0;DECLARE _index VARCHAR(200) DEFAULT '';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';DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 2; OPEN _cur;FETCH _cur INTO _index;WHILE _index <>'' DOSET @str = CONCAT("drop index", _index,"on" , tablename);PREPARE sql_str FROM @str ;EXECUTE sql_str;DEALLOCATE PREPARE sql_str;SET _index='';FETCH _cur INTO _index;END WHILE;CLOSE _cur;
END //
DELIMITER ;

執行存儲過程

CALL proc_drop_index("dbname","tablename");

2. 索引失效案例

2.1 索引字段：全值匹配最優

全值匹配最優：指的是我們查詢的內容，過濾上都走了我們的索引，都和我們創建的索引完全匹配上了。

在這里插入圖片描述

# 1. 全值匹配我最愛
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30;EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classid = 4;EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classid = 4 AND `name` = 'abcd';# 添加上索引
CREATE INDEX idx_age ON student(age);
CREATE INDEX idx_age_classid ON student(age,classId);
CREATE INDEX idx_age_classid_name ON student(age,classId,`name`);

2.2 索引字段：最佳左前綴法則

拓展：Alibaba《Java開發手冊》
索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引。

在這里插入圖片描述

在這里插入圖片描述

SHOW INDEX FROM student;
# 最佳左前綴法則
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND `name` = 'abcd';EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid = 1 AND `name` = 'abcd';EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid = 1 AND `name` = 'abcd' AND age = 30;

2.3 主鍵插入順序

在這里插入圖片描述

如果此時再插入一條主鍵值為 9 的記錄，那它插入的位置就如下圖：

在這里插入圖片描述

可這個數據頁已經滿了，再插進來咋辦呢？我們需要把當前 頁面分裂 成兩個頁面，把本頁中的一些記錄 移動到新創建的這個頁中。頁面分裂和記錄移位意味著什么？意味著：性能損耗 ！所以如果我們想盡量 避免這樣無謂的性能損耗，最好讓插入的記錄的 主鍵值依次遞增 ，這樣就不會發生這樣的性能損耗了。 所以我們建議：讓主鍵具有 AUTO_INCREMENT ，讓存儲引擎自己為表生成主鍵，而不是我們手動插入 ， 比如： person_info 表：

CREATE TABLE person_info(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);

我們自定義的主鍵列 id 擁有AUTO_INCREMENT屬性，在插入記錄時存儲引擎會自動為我們填入自增的 主鍵值。這樣的主鍵占用空間小，順序寫入，減少頁分裂。

2.4 索引字段進行了：計算、函數、類型轉換(自動或手動)導致索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

在這里插入圖片描述

# 計算、函數、類型轉換(自動或手動)導致索引失效EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE '%abc';

在這里插入圖片描述

在這里插入圖片描述

type為“ALL”，表示沒有使用到索引，查詢時間為 3.62 秒，查詢效率較之前低很多。

student表的字段stuno上設置有索引

CREATE INDEX idx_sno ON student(stuno);

# (索引字段)計算導致索引失效
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;

在這里插入圖片描述

# student 表的字段 stuno上設置有索引
CREATE INDEX idx_sno ON student(stuno);
# (索引字段)計算導致索引失效
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;# 索引優化：對于這種索引簡單運算的，我們可以優先將運算結果計算出來，再進行查詢，
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

在這里插入圖片描述

2.5 索引字段類型轉換導致索引失效


# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME=123;# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME='123';

在這里插入圖片描述

在這里插入圖片描述

2.6 索引字段：使用了范圍條件，右邊的列索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age = 30 AND student.classId > 20 AND student.name = 'abc';

在這里插入圖片描述

在這里插入圖片描述

將范圍查詢條件放置語句最后：

CREATE INDEX idx_age_classId_name ON student(age,NAME,classId);EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age = 30 AND student.classId > 20 AND student.name = 'abc';

在這里插入圖片描述

應用開發中范圍查詢，例如：金額查詢，日期查詢等等一些范圍查詢，在創建索引時，需將這些范圍查詢的字段放到(索引字段的最后面)。

2.7 索引字段：不等于(!= 或者<>)索引失效

為 name 字段創建索引

# 不等于 (!= 或者 <>) 索引失效
CREATE INDEX idx_name ON student(`name`);EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name` <> 'abc';EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name` != 'abc';

在這里插入圖片描述

在這里插入圖片描述

2.8 索引字段： is null可以使用索引，is not null無法使用索引

  • is null 可以使用索引：
# is null 可以使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name` IS NULL;

在這里插入圖片描述

  • is not null ：無法使用索引，索引失效
# is not null ：無法使用索引，索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name` IS NOT NULL;

在這里插入圖片描述

結論：最好在設計數據表的時候就將 字段設置為 NOT NULL 約束 ，比如可以將 INT 類型的字段，默認值設置為 0，將字符類型的默認值設置為空字符串"" 。

拓展：同理，在查詢中使用 no like 也無法使用索引，導致全表掃描。

2.9 索引字段：使用了 like以通配符 % 開頭索引失效

# 索引字段當中使用了 like 以通配符 '%' 開頭索引失效
# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name`LIKE '%ab';

在這里插入圖片描述

# 索引字段當中使用了 like 以通配符 '%' 開頭索引失效
# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`name`LIKE 'ab%';

在這里插入圖片描述

2.10 OR 前后存在非索引的列，索引失效

在 WHERE 子句中，如果在 OR 前的條件列進行了索引，而 OR后的條件沒有進行索引，那么索引會失效，也就是說，OR 前后的兩個條件中的列都時索引時，查詢中才使用索引 。

因為 OR 的含義就是兩個只要滿足一個即可，因此只有一個條件進行了索引是沒有意義的，只要有條件列沒有索引，就會進行全表掃描 ，因此索引的條件列也會失效。

# 創建 age 的索引
CREATE INDEX idx_age ON student(age);EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.`age` = 10 OR student.`name` = 'abc';

在這里插入圖片描述

2.11 數據庫和表的字符集統一使用utf8mb4

統一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，統一字符集可以避免由于字符集轉換產生的亂碼。不 同的 字符集 進行比較前需要進行轉換會造成索引失效。

3. 關聯查詢優化

3.1 數據準備

# 分類
CREATE TABLE IF NOT EXISTS `type` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
#圖書
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);#向分類表中添加20條記錄
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20)));#向圖書表中添加20條記錄
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

3.2采用左外連接

下面開始 EXPLAIN 分析

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

在這里插入圖片描述

添加索引優化

# 添加索引
ALTER TABLE book ADD INDEX Y ( card);   #【被驅動表】，可以避免全表掃描EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

在這里插入圖片描述

可以看到第二行的 type 變為了 ref，rows 也變成了優化比較明顯。這是由左連接特性決定的。LEFT JOIN 條件用于確定如何從右表搜索行，左邊一定都有，所以 右邊是我們的關鍵點,一定需要建立索引 。

ALTER TABLE `type` ADD INDEX X (card); #【驅動表】，無法避免全表掃描
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

在這里插入圖片描述

接著：

DROP INDEX Y ON book;
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

在這里插入圖片描述

3.3 采用內連接

drop index X on type;
drop index Y on book;（如果已經刪除了可以不用再執行該操作）

換成 inner join（MySQL自動選擇驅動表）

EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;

在這里插入圖片描述

添加索引優化

# 添加索引優化
ALTER TABLE book ADD INDEX Y ( card);
EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;

在這里插入圖片描述

ALTER TABLE TYPE ADD INDEX X (card);
EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;

在這里插入圖片描述

接著：

DROP INDEX X ON `type`;
EXPLAIN SELECT SQL_NO_CACHE * FROM TYPE INNER JOIN book ON type.card=book.card;

在這里插入圖片描述

對于內連接來說，如果表的連接條件中只有一個字段有索引，則有索引的字段所在的表會被作為驅動表。

接著


#向圖書表中添加20條記錄
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

DROP INDEX `Y` ON book; # 刪除索引
SHOW INDEX FROM book;EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON `type`.card = book.card;

在這里插入圖片描述

對于內連接來說：在兩個表的連接條件都存在索引(都不存在索引的)的情況下，會選擇小表作為驅動表，“小表驅動大表”

4. join語句原理

https://github.com/codinglin/StudyNotes/blob/main/MySQL%E9%AB%98%E7%BA%A7%E7%AF%87/MySQL%E7%B4%A2%E5%BC%95%E5%8F%8A%E8%B0%83%E4%BC%98%E7%AF%87.md#3-%E5%85%B3%E8%81%94%E6%9F%A5%E8%AF%A2%E4%BC%98%E5%8C%96

在這里插入圖片描述

5. 子查詢優化

MySQL從4.1版本開始支持子查詢，使用子查詢可以進行SELECT語句的嵌套查詢，即一個SELECT查詢的結 果作為另一個SELECT語句的條件。 子查詢可以一次性完成很多邏輯上需要多個步驟才能完成的SQL操作 。

**子查詢是 MySQL 的一項重要的功能，可以幫助我們通過一個 SQL 語句實現比較復雜的查詢。但是，子 查詢的執行效率不高。**原因：

  1. 執行子查詢時，MySQL需要為內層查詢語句的查詢結果 建立一個臨時表 ，然后外層查詢語句從臨時表 中查詢記錄。查詢完畢后，再 撤銷這些臨時表 。這樣會消耗過多的CPU和IO資源，產生大量的慢查詢。

  2. 子查詢的結果集存儲的臨時表，不論是內存臨時表還是磁盤臨時表都 不會存在索引 ，所以查詢性能會 受到一定的影響。

  3. 對于返回結果集比較大的子查詢，其對查詢性能的影響也就越大。

**在MySQL中，可以使用連接（JOIN）查詢來替代子查詢。**連接查詢 不需要建立臨時表 ，其 速度比子查詢 要快 ，如果查詢中使用索引的話，性能就會更好。

舉例1：查詢學生表中是班長的學生信息

# 創建班級表中班長的索引
CREATE INDEX idx_monitor ON class(monitor);EXPLAIN SELECT * FROM student stu1
WHERE stu1.`stuno` IN (
SELECT monitor
FROM class c
WHERE monitor IS NOT NULL
)
  • 推薦使用多表查詢
EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c
ON stu1.`stuno` = c.`monitor`
WHERE c.`monitor` is NOT NULL;

在這里插入圖片描述

舉例2：取所有不為班長的同學

  • 不推薦
EXPLAIN SELECT SQL_NO_CACHE a.*
FROM student a
WHERE a.stuno NOT IN (SELECT monitor FROM class bWHERE monitor IS NOT NULL
);

在這里插入圖片描述

  • 推薦：
EXPLAIN SELECT SQL_NO_CACHE a.*
FROM student a LEFT OUTER JOIN class b
ON a.stuno = b.monitor
WHERE b.monitor IS NULL;

在這里插入圖片描述

結論：盡量不要使用 NOT IN或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

6. 排序優化

問題：在 WHERE 條件字段上加索引，但是為什么在 ORDER BY 字段上還要加索引呢？

回答：

在MySQL中，支持兩種排序方式，分別是 FileSort 和 Index 排序。

  • Index 排序中，索引可以保證數據的有序性，不需要再進行排序，效率更高。
  • FileSort 排序則一般在 內存中 進行排序，占用CPU較多。如果待排結果較大，會產生臨時文件 I/O 到磁盤進行排序的情況，效率較低。

優化建議：

  1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表掃描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。當然，某些情況下全表掃描，或者 FileSort 排序不一定比索引慢。但總的來說，我們還是要避免，以提高查詢效率。
  2. 盡量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用單索引列； 如果不同就使用聯合索引。
  3. 無法使用 Index 時，需要對 FileSort 方式進行調優。

測試：

刪除student表和class表中已創建的索引。

# 方式1
DROP INDEX idx_monitor ON class;
DROP INDEX idx_cid ON student;
DROP INDEX idx_age ON student;
DROP INDEX idx_name ON student;
DROP INDEX idx_age_name_classId ON student;
DROP INDEX idx_age_classId_name ON student;

以下是否能使用到索引，能否去掉using filesort

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;

在這里插入圖片描述

測試2： order by 時 不使用 limit ，索引失效

# 過程二: order by 時 不使用 limit ，索引失效
# 創建索引
CREATE INDEX idx_age_classid_name ON student (age,classid,`name`);
# 不使用 limit 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid ;

在這里插入圖片描述

# order by  使用 limit 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 100;

在這里插入圖片描述

測試三：order by 排序當中字段，不滿足索引最左匹配原則，順序錯誤，索引失效

# 測試三：order by 排序當中，順序錯誤，索引失效 EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY classid,`name` LIMIT 100;

在這里插入圖片描述

測試四： order by 時，規則不一致(索引排序不一致(反序可以走索引，mysql 8.0 支持)，)，索引失效

# 測試： order by 時，規則不一致(索引排序不一致(反序可以走索引，mysql 8.0 支持)，)，索引失效EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age DESC, classid DESC LIMIT 100;

在這里插入圖片描述

# 測試： order by 時，規則不一致(索引排序不一致(反序可以走索引，mysql 8.0 支持)，)，索引失效EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age ASC, classid DESC LIMIT 100;

在這里插入圖片描述

最后：

“在這個最后的篇章中，我要表達我對每一位讀者的感激之情。你們的關注和回復是我創作的動力源泉，我從你們身上吸取了無盡的靈感與勇氣。我會將你們的鼓勵留在心底，繼續在其他的領域奮斗。感謝你們，我們總會在某個時刻再次相遇。”

在這里插入圖片描述

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/pingmian/87099.shtml
繁體地址，請注明出處：http://hk.pswp.cn/pingmian/87099.shtml
英文地址，請注明出處：http://en.pswp.cn/pingmian/87099.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

基于目標驅動的分布式敏捷開發

基于目標驅動的分布式敏捷開發

研究結論 風險對項目目標的影響 時間目標&#xff1a;需求管理不當&#xff08;如需求優先級不明確、多產品負責人需求沖突&#xff09;、架構變更導致的返工、跨站點協調問題&#xff08;如第三方依賴、通信基礎設施不足&#xff09;是影響項目時間的主要風險因素。質量目標&…
閱讀更多...
高通手機跑AI系列之——穿衣試裝算法

高通手機跑AI系列之——穿衣試裝算法

環境準備 手機 測試手機型號&#xff1a;Redmi K60 Pro 處理器&#xff1a;第二代驍龍8移動--8gen2 運行內存&#xff1a;8.0GB &#xff0c;LPDDR5X-8400&#xff0c;67.0 GB/s 攝像頭&#xff1a;前置16MP后置50MP8MP2MP AI算力&#xff1a;NPU 48Tops INT8 &&…
閱讀更多...
opencv入門(5)圖像像素的讀寫操作和算術運算

opencv入門(5)圖像像素的讀寫操作和算術運算

文章目錄 1 圖像遍歷與修改1.1 使用數組1.2 使用指針 2 圖像的算術運算2.1 一般算術操作2.2 算術API 1 圖像遍歷與修改 C中支持 數組遍歷 和 指針方式遍歷 1.1 使用數組 訪問使用 image.at(row,col) 進行訪問 如果是單通道灰度圖&#xff0c;就使用image.at進行讀取 如果是三…
閱讀更多...
Stable Diffusion入門-ControlNet 深入理解-第三課：結構類模型大揭秘——深度、分割與法線貼圖

Stable Diffusion入門-ControlNet 深入理解-第三課：結構類模型大揭秘——深度、分割與法線貼圖

大家好，歡迎回到Stable Diffusion入門-ControlNet 深入理解系列的第三課！ 在上一課中，我們深入探討了 ControlNet 文件的命名規則，以及線條類 ControlNet模型的控制方法。如果你還沒有看過第二篇，趕緊點這里補課：Stable Diffusion入門-ControlNet 深入理解 第二課：Contr…
閱讀更多...
噴油嘴深凹槽內輪廓測量的方法探究 —— 激光頻率梳 3D 輪廓測量

噴油嘴深凹槽內輪廓測量的方法探究 —— 激光頻率梳 3D 輪廓測量

引言 噴油嘴作為燃油噴射系統核心部件&#xff0c;其深凹槽內輪廓精度直接影響燃油霧化效果與發動機排放性能。噴油嘴深凹槽具有深徑比大&#xff08;可達 30:1&#xff09;、孔徑小&#xff08;φ0.5 - 2mm&#xff09;、表面質量要求高&#xff08;Ra≤0.2μm&#xff09;等…
閱讀更多...
上證ETF50期權交易規則一文詳解

上證ETF50期權交易規則一文詳解

50ETF期權&#xff0c;首先這是期權交易&#xff0c;所以50ETF期權有期權交易的所有特征&#xff0c;其次&#xff0c;50ETF期權的標的對象是上證50&#xff0c;所以50ETF&#xff08;認購看漲&#xff09;期權的走勢和上證50的走勢是一樣的。 行權時間&#xff1a; 在行權日當…
閱讀更多...
Oracle獲取執行計劃之10046 技術詳解

Oracle獲取執行計劃之10046 技術詳解

Oracle 的 10046 事件是性能調優中最常用的工具之一&#xff0c;通過跟蹤會話的 SQL 執行細節&#xff0c;生成包含執行計劃、等待事件、綁定變量等信息的跟蹤文件&#xff0c;幫助定位性能瓶頸。以下是技術詳解&#xff1a; 一、10046 事件基礎 10046 是 Oracle 內部事件&…
閱讀更多...
Linux 日志監控工具對比：從 syslog 到 ELK 實戰指南

Linux 日志監控工具對比：從 syslog 到 ELK 實戰指南

更多云服務器知識&#xff0c;盡在hostol.com 你有沒有被 Linux 上滿屏飛滾的日志整崩潰過&#xff1f;看著 /var/log 目錄越來越肥&#xff0c;關鍵日志像大海撈針一樣藏在里面&#xff0c;每次出故障就像拆盲盒&#xff0c;賭你能不能第一眼看出問題。 日志系統&#xff0c…
閱讀更多...
本地服務器部署后外網怎么訪問不了？內網地址映射互聯網上無法連接問題的排查

本地服務器部署后外網怎么訪問不了？內網地址映射互聯網上無法連接問題的排查

我的網站部署搭建在本地服務器上的&#xff0c;在內網可以正常訪問&#xff0c;但是外網無法訪問&#xff0c;該怎么排查&#xff1f;局域網內部經過路由器的&#xff0c;有設置了虛擬服務器轉發規則&#xff0c;在互聯網公網上還是無法訪問服務器怎么辦&#xff1f;相信很多人…
閱讀更多...
如何免費正確安裝微軟的office全家桶

如何免費正確安裝微軟的office全家桶

記錄一下如何正確安裝微軟的office全家桶 找到安裝包傻瓜式安裝 找到安裝包 安裝包在附件&#xff0c;大家可以自行進行下載 傻瓜式安裝 操作一目了然&#xff0c;點你需要的就行了
閱讀更多...
論文閱讀：BLIPv1 2022.2

論文閱讀：BLIPv1 2022.2

文章目錄 一、研究背景與問題現有方法的局限性研究目標 二、核心方法與創新點多模態編碼器 - 解碼器混合架構&#xff08;MED&#xff09;標題生成與過濾&#xff08;CapFilt&#xff09;數據自舉方法 三、實驗與結果數據集與訓練配置關鍵實驗發現與 state-of-the-art 方法的對…
閱讀更多...
630，百度文心大模型4.5系列開源！真香

630，百度文心大模型4.5系列開源！真香

2025年被普遍認為是AI Agent商業化的關鍵之年&#xff0c;而大模型正是Agent能力的核心支撐。 當開發成本大幅降低&#xff0c;我們很可能看到各種垂直領域的Agent應用如雨后春筍般涌現。 技術普惠的現實意義對于廣大AI創業者和開發者來說&#xff0c;這無疑是個好消息。 之…
閱讀更多...
數據結構：遞歸：斐波那契數列（Fibonacci Sequence）

數據結構：遞歸：斐波那契數列（Fibonacci Sequence）

目錄 什么是斐波那契數列&#xff1f; 用遞歸推導Fibonacci 復雜度分析 用迭代推導Fibonacci 復雜度分析 遞歸優化&#xff1a;記憶化遞歸&#xff08;Memoized Recursion&#xff09; 復雜度分析 什么是斐波那契數列&#xff1f; 斐波那契數列&#xff08;Fibonacci Seq…
閱讀更多...
ArcGIS Pro利用擦除工具，矢量要素消除另一矢量部分區域

ArcGIS Pro利用擦除工具，矢量要素消除另一矢量部分區域

選擇“System Toolboxes”→“Analysis Tools.tbx”→“Overlay”→“Erase&#xff08;擦除&#xff09;”。 原始 擦除后
閱讀更多...
Linux: network: 性能 pause

Linux: network: 性能 pause

最近看到一個問題，是關于網卡的throughput的性能問題，后來在ethtool-S里看到有pause的counter，這個也是網絡性能問題的一個分析方向。算是學到了新的知識點。 $ grep -i -e 2025- -e pause ethtool*ens2f1np1 | grep -v -e ": 0\$" | headtail 4====
閱讀更多...
目標檢測系列（五）已標注數據集（yolo格式）導入labelstudio繼續標注

目標檢測系列（五）已標注數據集（yolo格式）導入labelstudio繼續標注

目錄 1、labelstudio安裝 2、yolo(txt)轉json 3、COCO轉yolo(僅針對coco格式標注信息) 4、設置環境變量并啟動labelstudio 5、進入label studio創建工程并設置任務標簽 6、安裝http-server并啟動文件映射服務 7、進入label studio導入json文件即可 1、labelstudio安裝 …
閱讀更多...
pytorch底層原理學習--Libtorch

pytorch底層原理學習--Libtorch

libtorch libtorch 是 PyTorch 的 C 實現版本&#xff0c;可以認為所有的pytorch底層都是由c實現&#xff0c;而pytorch的所有C實現就叫libtorch&#xff0c;也就是我們在pytorch官網getstart頁面下載的cpytorch版本。我們用python寫的pytorch神經網絡代碼都會通過pybind11將p…
閱讀更多...
TCP 三次握手協商 MSS 前，如何確定 MSS 值(附 Linux 內核源碼)

TCP 三次握手協商 MSS 前，如何確定 MSS 值(附 Linux 內核源碼)

文章目錄 一、SYN總結影響 SYN MSS 的因素 二、SYNACK總結影響 SYNACK MSS 的因素 結合 Linux 內核源碼 一、SYN 總結影響 SYN MSS 的因素 套接字選項 TCP_MAXSEG路由選項 advmss出口 MTU 減去 40(TCP 和 IP 的固定首部大小)IPV4_MAX_PMTU - 40(同上) 二、SYNACK 總結影響 SY…
閱讀更多...
掃描電子顯微鏡（SEM）夏令營面試基礎題及答案

掃描電子顯微鏡（SEM）夏令營面試基礎題及答案

第二期表征問題SEM&#xff0c;后續會陸續更新其他表征 SEM和XRD一樣&#xff0c;都是表征里面很常見的手段&#xff0c;基本上看論文這兩個都是必不可少的 對于這部分內容&#xff0c;理解記憶&#xff1e;死記硬背&#xff0c;到時會問起來回答個大概就行&#xff0c; 像上…
閱讀更多...
Leetcode力扣解題記錄--第49題（map）

Leetcode力扣解題記錄--第49題（map）

題目鏈接&#xff1a;49. 字母異位詞分組 - 力扣&#xff08;LeetCode&#xff09; 題目描述 給你一個字符串數組&#xff0c;請你將 字母異位詞 組合在一起。可以按任意順序返回結果列表。 示例 1: 輸入: strs ["eat", "tea", "tan", &quo…
閱讀更多...
最新文章

Copyright @ 2022~2023