當(dāng)前位置：首頁 > news >正文

大連開發(fā)區(qū)網(wǎng)站制作建設(shè)公司游戲推廣合作

news 2025/7/6 4:45:12

大連開發(fā)區(qū)網(wǎng)站制作建設(shè)公司,游戲推廣合作,攝影網(wǎng)站的建設(shè)的論文,世界排名前十的服裝設(shè)計學(xué)院數(shù)據(jù)庫調(diào)優(yōu)的維度： 索引建立SQL優(yōu)化（本文重點）my.cnf的調(diào)整（線程數(shù)，緩存等）分庫分表 SQL查詢優(yōu)化的技術(shù)從大方向上可以分為物理查詢優(yōu)化，邏輯查詢優(yōu)化物理查詢優(yōu)化：即通過建立索…

數(shù)據(jù)庫調(diào)優(yōu)的維度：

索引建立
SQL優(yōu)化（本文重點）
my.cnf的調(diào)整（線程數(shù)，緩存等）
分庫分表

SQL查詢優(yōu)化的技術(shù)從大方向上可以分為物理查詢優(yōu)化，邏輯查詢優(yōu)化

物理查詢優(yōu)化：即通過建立索引，表連接的方式來進行優(yōu)化
邏輯查詢優(yōu)化：SQL等價變換提升效率

1. 數(shù)據(jù)準備

學(xué)員表插 50萬條，班級表插 1萬條。

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;命令開啟：允許創(chuàng)建函數(shù)設(shè)置：
set global log_bin_trust_function_creators=1; # 不加global只是當(dāng)前窗口有效。創(chuàng)建函數(shù)
保證每條數(shù)據(jù)都不同。隨機產(chǎn)生字符串
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 隨機產(chǎn)生班級編號
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創(chuàng)建存儲過程
#創(chuàng)建往stu表中插入數(shù)據(jù)的存儲過程
CREATE PROCEDURE insert_stu( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #設(shè)置手動提交事務(wù)
REPEAT #循環(huán)
SET i = i + 1; #賦值
INSERT INTO student (stuno, name ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事務(wù)
END創(chuàng)建往class表中插入數(shù)據(jù)的存儲過程
CREATE PROCEDURE `insert_class`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO class ( classname,address,monitor ) VALUES
(rand_string(8),rand_string(10),rand_num(1,100000));
UNTIL i = max_num
END REPEAT;
commit;
END#執(zhí)行存儲過程，往class表添加1萬條數(shù)據(jù)
CALL insert_class(10000);執(zhí)行存儲過程，往stu表添加50萬條數(shù)據(jù)
CALL insert_stu(100000,500000);# 刪除索引存儲過程
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE 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=1來控制游標的結(jié)束
DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;
#若沒有數(shù)據(jù)返回,程序繼續(xù),并將變量done設(shè)為2
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @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

2. 索引失效的11種情況

2.1 全值匹配我最愛

explain select * from student where age = 20

在這里插入圖片描述

explain select * from student where age = 20 and classId = 4

在這里插入圖片描述

explain select * from student where age = 30 and classId = 4 and name = 'abcd'

在這里插入圖片描述
上面三條sql語句的type全部為ALL

性能由好到最壞依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

SQL 性能優(yōu)化的目標：至少要達到 range 級別，要求是 ref 級別，最好是 consts級別。（阿里巴巴開發(fā)手冊要求）

加上索引

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)

再次進行explain
在這里插入圖片描述
就會使用上索引

2.2 最佳左前綴法則

要遵守最佳左前綴法則

CREATE INDEX idx_age_classid_name on student)age,classId,name)

explain select * from student where age = 30 and classId = 4 and name = 'abcd'

在這里插入圖片描述
上述sql是可以使用到聯(lián)合索引的，因為查詢條件的順序和個數(shù)都是完全匹配上索引的。

explain select * from student where classId = 4 and name = 'abcd' and age = 30

在這里插入圖片描述
那這里為什么順序和索引的順序不一致了，還能使用上索引呢？因為順序不一致，但是字段是能夠匹配的上索引的字段的，所以能夠使用上索引（滿足總結(jié)的3）

explain select * from student where name = 'abcd' and classId = 4

在這里插入圖片描述
此時，就用不到索引了（因為不滿足下述總結(jié)的1）

explain select * from student where name = 'abcd' and age = 30

在這里插入圖片描述
為什么這個sql又能用到索引呢？（總結(jié)的2）

總結(jié)：對于聯(lián)合索引，是否能用到索引的條件

查詢條件的字段要能夠從最左邊開始覆蓋到索引的字段
如果能從最左邊開始覆蓋到索引，那怕中間斷開了，也能使用索引，只不過使用的索引不是索引的全部
順序如果與索引定義的順序不一致也沒關(guān)系，只要能滿足1，優(yōu)化器在底層也會給我們自動排序

2.3 主鍵插入順序

對于InnoDB存儲引擎的表來說，表中的實際數(shù)據(jù)都是存儲在聚簇索引的葉子節(jié)點的，記錄是存在數(shù)據(jù)頁中，數(shù)據(jù)頁和記錄是按照主鍵值從小到大進行排序的，如果我們插入的記錄的主鍵值是依次增大的話，那么插入的記錄會依次往后排，但是如果主鍵值忽大忽小，那么就會存在頁分裂的情況。

在這里插入圖片描述
例如現(xiàn)在這個數(shù)據(jù)頁已經(jīng)滿了，此時再插入id為9的數(shù)據(jù)

可這個數(shù)據(jù)頁已經(jīng)滿了，再插進來咋辦呢？我們需要把當(dāng)前頁面分裂成兩個頁面，把本頁中的一些記錄移動到新創(chuàng)建的這個頁中。頁面分裂和記錄移位意味著什么？意味著： 性能損耗 ！所以如果我們想盡量避免這樣無謂的性能損耗，最好讓插入的記錄的主鍵值依次遞增，這樣就不會發(fā)生這樣的性能損耗了。

所以我們建議：讓主鍵具有 AUTO_INCREMENT ，讓存儲引擎自己為表生成主鍵，而不是我們手動插入

但是在分布式系統(tǒng)中，主鍵一般都是代碼里生成的，所以…

2.4 計算、函數(shù)、類型轉(zhuǎn)換(自動或手動)導(dǎo)致索引失效

CREATE INDEX idx_name ON student(NAME);

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

上述兩個sql哪個性能更好？

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

在這里插入圖片描述

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

在這里插入圖片描述
由此可見是第一個更好！使用函數(shù)后，已經(jīng)不能使用上索引了。上述like可以使用上索引

接下來看看這三條sql語句

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE '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%';

其對應(yīng)的結(jié)果如下
在這里插入圖片描述

由此可見，只有第一條可以使用上索引

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;

上述兩個sql，一個對字段進行了計算，一個沒有，答案顯而易見不做運算的能夠使用上索引

在這里插入圖片描述

類型自動轉(zhuǎn)換不能使用索引

INSERT INTO `sql_optimize`.`student`(`id`, `stuno`, `name`, `age`, `classId`) VALUES (817239817, 1111111, '123', 12, 317);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

顯而易見第二條sql能夠使用上索引，因為name是varchar，第一條sql的name為int，第二條sql的name為字符串類型，雖然第一條也能匹配的上記錄，但是是由于底層給我們使用了函數(shù)進行類型轉(zhuǎn)換。

在這里插入圖片描述

2.5 范圍條件右邊的列索引失效

create index idx_age_classId_name on student(age,classId,`name`)

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

觀察上述sql能否用到索引
在這里插入圖片描述
雖然是用到索引了，但是只用到了age和classId兩個字段，name字段沒有用到。因為classId是范圍條件，范圍條件右邊的列索引失效

那如果我交換查詢條件classId和name的順序呢？

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

在這里插入圖片描述
結(jié)果也還是一樣，因為你的索引的順序是不變的(age,classId,name)，你的sql查詢條件的順序變化了，優(yōu)化器底層還是會改變查詢條件的順序來匹配索引列的順序來使用上索引，除非改變索引列的順序為(age,name,classId)，這樣就能使用完全索引了。

下述都是屬于范圍查詢

<  <=  >  >=  between

總結(jié)應(yīng)用開發(fā)中范圍查詢，例如金額，日期等，設(shè)計索引時應(yīng)該將這些字段放到聯(lián)合索引的最后。

2.6 不等于(!= 或者<>)索引失效

create index idx_name on student(`name`)EXPLAIN SELECT * from student where name = 'abc'EXPLAIN SELECT * from student where name <> 'abc'

觀察上述sql，哪個不能使用上索引
在這里插入圖片描述

由此可見，不等于是不能使用索引的

2.7 is null可以使用索引，is not null無法使用索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

在這里插入圖片描述

is not null無法使用索引

2.8 like以通配符%開頭索引失效

上述最左匹配原則時有提到過

拓展：Alibaba《Java開發(fā)手冊》【強制】頁面搜索嚴禁左模糊或者全模糊，如果需要請走搜索引擎來解決

2.9 or 前后存在非索引的列，索引失效

create index idx_age on student(age)

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

在這里插入圖片描述
可以看到possible_keys有值，但是key沒有，因為age能匹配上索引，但是classId匹配不上，那為什么不用idx_age索引呢？

因為如果走了idx_age索引，后面跟上or classid = 100 ，classid沒有索引，就相當(dāng)于還是得走一遍全表掃描，所以idx_age還不如不走，直接走全表掃描來的更快。

create index idx_age on student(age)

在這里插入圖片描述
此時在創(chuàng)建classId的索引，就使用上了索引 type為index_merge

2.10 數(shù)據(jù)庫和表的字符集統(tǒng)一使用utf8mb4

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

3. 關(guān)聯(lián)查詢優(yōu)化

數(shù)據(jù)準備

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.1 左外連接

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

在這里插入圖片描述
上述沒有使用上索引

CREATE INDEX Y ON book(card);

給book加上索引
在這里插入圖片描述
給type加上索引

CREATE INDEX X ON `type`(card);

在這里插入圖片描述
我刪除掉book的索引

DROP INDEX Y ON book;

在這里插入圖片描述
可以得到book沒有使用上索引

3.2 內(nèi)連接

刪除掉上述的book和type的索引

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

在這里插入圖片描述

CREATE INDEX Y ON book(card);

在這里插入圖片描述

CREATE INDEX X ON `type`(card);

在這里插入圖片描述

對于內(nèi)連接來說，查詢優(yōu)化器是可以決定誰作為驅(qū)動表，誰作為被驅(qū)動表。

現(xiàn)在刪除book的索引

DROP INDEX Y ON book;

在這里插入圖片描述
可以看到book的位置跑到了type的上面，即book是驅(qū)動表

結(jié)論：
1. 如果內(nèi)連接有索引的話，索引給被驅(qū)動表，成本消耗是最低的。
2. 對于內(nèi)連接來說，如果兩個表的連接條件都存在索引的情況下，會選擇小表作為驅(qū)動表。即小表驅(qū)動大表

因為join連接中，例如 A inner join B … 例如是取出A的一條數(shù)據(jù)來匹配B的所有數(shù)據(jù)，若此時B有索引，則匹配的時候，就能使用上索引，但是A是必須得全部取出來的，所以這滿足了結(jié)論1

如果B的數(shù)量級很大，那么索引的優(yōu)勢越明顯，所以小表驅(qū)動大小滿足了結(jié)論2

3.3 join的底層原理

join方式連接表，本質(zhì)就是各個表之間數(shù)據(jù)進行循環(huán)匹配，Mysql5.5之前，Mysql只支持一種表間關(guān)聯(lián)方式，就是嵌套循環(huán)（Nested Loop Join）。如果關(guān)聯(lián)表的數(shù)據(jù)量很大，則join關(guān)聯(lián)的執(zhí)行時間會很長。在Mysql5.5之后的版本中，Mysql通過引入BNLJ算法來優(yōu)化嵌套執(zhí)行。

上述我們看到了Mysql優(yōu)化器會幫我們決定inner join中驅(qū)動表與被驅(qū)動表。那么對于外連接（left join，right join）Mysql優(yōu)化器也會幫我們決定驅(qū)動表與被驅(qū)動表

3.3.1 Simple Nested-Loop Join

在這里插入圖片描述

開銷統(tǒng)計	SNLJ
外表掃描次數(shù)	1
內(nèi)表掃描次數(shù)	A
讀取記錄數(shù)	A+A*B
join次數(shù)	B*A
回表讀取次數(shù)	0 （因為沒有索引）

3.3.3 Index Nested-Loop Join

在這里插入圖片描述
Index Nested-Loop Join其優(yōu)化的主要思路就是減少內(nèi)層的匹配次數(shù)，所以要求被驅(qū)動表必須有索引。

開銷統(tǒng)計	SNLJ
外表掃描次數(shù)	1
內(nèi)表掃描次數(shù)	0
讀取記錄數(shù)	A+B（match）
join次數(shù)	A*Index(Height)
回表讀取次數(shù)	B（mathc）

3.3.3 Block Nested-Loop Join

如果存在索引，那么會使用index的方式進行join，如果join的列沒有索引，被驅(qū)動表掃描的次數(shù)太多了，每次訪問被驅(qū)動表，其表中的記錄都會被加載到內(nèi)存中，然后再從驅(qū)動表中進行匹配，匹配完之后取出內(nèi)存，然后再從驅(qū)動表中取出一條數(shù)據(jù)，加載被驅(qū)動表的記錄到內(nèi)存中繼續(xù)比較，周而復(fù)始，這種方式大大的增加了IO次數(shù)，為了減少被驅(qū)動表的IO次數(shù)，就出現(xiàn)了Block Nested-Loop Join。

不再是逐條獲取驅(qū)動表的數(shù)據(jù)，而是一塊一塊的獲取，存入join buffer緩沖區(qū)中，將驅(qū)動表join相關(guān)的部分數(shù)據(jù)列（大小受到j(luò)oin buffer的限制）緩存到j(luò)oin buffer中，然后全表掃描被驅(qū)動表，被驅(qū)動表的每一條記錄一次性和join buffer中的所有驅(qū)動表記錄進行匹配（內(nèi)存中操作），將簡單的嵌套循環(huán)中的多次比較合并為一次，降低了被驅(qū)動表的訪問頻率。

注意：

這里緩存的不只是關(guān)聯(lián)表的列，select 后面的列也會進行緩存
在一個有N個join關(guān)聯(lián)的SQL中會分配n-1個join buffer，所以查詢的時候盡量減少不必要的字段，可以讓join buffer中存放更多的列
所以盡量別用select * …

在這里插入圖片描述

3.3.4 相關(guān)參數(shù)

show variables like '%optimizer_switch%' 查看block_nested_loop的狀態(tài)，默認是ON

show variables like '%join_buffer_size%' 查看join_buffer_size的大小，默認是256K

join_buffer_size在32位系統(tǒng)上可以申請4G，在64位系統(tǒng)上可以申請大于4G的空間（64位windows系統(tǒng)除外，其最大值會被截斷位4G并發(fā)出警告）

3.3.5 總結(jié)

效率上：Index Nested-Loop Join > Block Nested-Loop Join > Simple Nested-Loop Join
永遠使用小結(jié)果集驅(qū)動大結(jié)果集（本質(zhì)就是減少外層循環(huán)數(shù)量）（小的度量單位是指表的行數(shù)*每行大小）
被驅(qū)動表匹配的條件增加索引列
增大join buffer size的大小
減少驅(qū)動表不必要的字段查詢（為什么是驅(qū)動表？因為如果是Block Nested-Loop Join，驅(qū)動表的查詢字段也會加載到j(luò)oin buffer中）

3.3.6 Hash Join

在這里插入圖片描述

4. 子查詢優(yōu)化

Mysql從4.1開始支持子查詢，使用子查詢可以進行SELECT語句的嵌套查詢，即一個子查詢的結(jié)果作為另一個SELECT語句的條件。子查詢可以一次性完成很多邏輯上需要多個步驟才能完成的SQL操作。但是子查詢的效率不高，原因如下：

執(zhí)行子查詢時，Mysql需要為內(nèi)層查詢語句的查詢結(jié)果建立一個臨時表，然后外層查詢語句從臨時表中查詢記錄，查詢完畢后，再撤銷這些表，這樣會消耗過多的cpu和io資源，產(chǎn)生大量慢查詢
子查詢的結(jié)果存儲的臨時表，不論是內(nèi)存臨時表還是磁盤臨時表都不會存在索引，所以查詢性能會有影響
對于返回結(jié)果集比較大的子查詢，其對查詢性能的影響也越大

建議實際開發(fā)中，使用join操作來替代子查詢

5. 排序優(yōu)化

為什么在order by字段上添加索引？

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

Index排序中，索引可以保證數(shù)據(jù)的有序性，不需要在進行排序，效率更高
FileSort排序則是在內(nèi)存中進行排序，占用CPU資源，如果待排序的數(shù)據(jù)較大，會產(chǎn)生臨時文件IO到磁盤進行排序，效率低下

優(yōu)化建議：

SQL中，可以在where和order by子句中使用索引，目的是在where子句中避免全表掃描，order by子句中避免使用FileSort排序，但是，某些情況下全表掃描或者FileSort排序不一定比索引排序慢。
盡量使用Index排序，如果where和order by是同一個字段，則單列索引就可以滿足，如果不一致，則使用聯(lián)合索引
無法使用Index排序，則對FileSort方式進行調(diào)優(yōu)

5.1 fileSort算法

雙路排序：MySQL 4.1之前是使用雙路排序，字面意思就是兩次掃描磁盤，最終得到數(shù)據(jù)，讀取行指針和order by列，對他們進行排序，然后掃描已經(jīng)排序好的列表，按照列表中的值重新從列表中讀取對應(yīng)的數(shù)據(jù)輸出從磁盤取排序字段，在buffer進行排序，再從磁盤取其他字段
單路排序（快）從磁盤讀取查詢需要的所有列，按照order by列在buffer對它們進行排序，然后掃描排序后的列表進行輸出，它的效率更快一些，避免了第二次讀取數(shù)據(jù)。并且把隨機IO變成了順序IO，但是它會使用更多的空間，因為它把每一行都保存在內(nèi)存中了。

在sort_buffer中，單路比多路要占用很多的空間，因為單路是把所有字段都取出，所以有可能取出的數(shù)據(jù)的總大小超過了sort_buffer的容量，導(dǎo)致每次只能去sort_buffer容量大小的數(shù)據(jù)進行排序（然后創(chuàng)建tmp文件，多路合并）排完后取sort_buffer容量大小，再排…導(dǎo)致多次IO

優(yōu)化策略：

提高sort_buffer_size大小 Mysql5.7默認位1M， show variables like '%sort_buffer_size%'
嘗試提高max_length_for_sort_data提高這個參數(shù)會增加用改進算法的概率，但是如果設(shè)置的太高，數(shù)據(jù)總?cè)萘咳菀壮^max_buffer_size，明顯癥狀就是IO增加，如果需要返回列的總長度大于max_length_for_sort_data，則使用雙路，否則使用單路，該值建議在1024-8192字節(jié)之間進行調(diào)整
order by時候建議不要使用 select * 原因見優(yōu)化策略1，2條

6. group by 優(yōu)化

group by 使用索引的原則幾乎跟order by一致，group by 即使沒有過濾條件用到索引，也可以直接使用索引。
group by 先排序再分組，遵照索引建的最佳左前綴法則
當(dāng)無法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 參數(shù)的設(shè)置
where效率高于having，能寫在where限定的條件就不要寫在having中了
減少使用order by，和業(yè)務(wù)溝通能不排序就不排序，或?qū)⑴判蚍诺匠绦蚨巳プ?。Order by、group by、distinct這些語句較為耗費CPU，數(shù)據(jù)庫的CPU資源是極其寶貴的。
包含了order by、group by、distinct這些查詢的語句，where條件過濾出來的結(jié)果集請保持在1000行以內(nèi)，否則SQL會很慢。

7. 分頁優(yōu)化

優(yōu)化思路1：使用order by

在索引上完成排序分頁操作，最后根據(jù)主鍵關(guān)聯(lián)回原表查詢所需要的其他列內(nèi)容。

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10)
a WHERE t.id = a.id;

優(yōu)化思路2：該方案適用于主鍵自增的表，可以把Limit 查詢轉(zhuǎn)換成某個位置的查詢。

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

8. 覆蓋索引

理解方式一：索引是高效找到行的一個方法，但是一般數(shù)據(jù)庫也能使用索引找到一個列的數(shù)據(jù)，因此它不必讀取整個行。畢竟索引葉子節(jié)點存儲了它們索引的數(shù)據(jù)；當(dāng)能通過讀取索引就可以得到想要的數(shù)據(jù)，那就不需要讀取行了。一個索引包含了滿足查詢結(jié)果的數(shù)據(jù)就叫做覆蓋索引。

理解方式二：非聚簇復(fù)合索引的一種形式，它包括在查詢里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆蓋查詢條件中所涉及的字段）。

簡單說就是，索引列+主鍵包含 SELECT 到 FROM之間查詢的列。
查詢的字段在索引中存在，即不需要回表進行查找

優(yōu)點：

避免Innodb表進行索引的二次查詢（回表）
可以把隨機IO變成順序IO加快查詢效率（我們二級索引是有順序的，但是如果沒有索引覆蓋，就得回表，從二級索引獲取的主鍵值，在聚簇索引中不一定是連續(xù)的，所以就有可能是隨機IO）

缺點：

索引字段的維護總是有代價的。因此，在建立冗余索引來支持覆蓋索引時就需要權(quán)衡考慮了。

具體的聯(lián)合索引內(nèi)容見Innodb索引還不清楚？看這一篇就夠啦

9. 索引下推（ICP）

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一種在存儲引擎層使用索引過濾數(shù)據(jù)的一種優(yōu)化方式。ICP可以減少存儲引擎訪問基表的次數(shù)以及MySQL服務(wù)器訪問存儲引擎的次數(shù)。

create index idx_name_stuno_age on student(name,stuno,age)

explain select * from student where name = '大帥' and stuno like '%10' and classId = 11

在這里插入圖片描述
可以看到Extra中有Using index condition;即ICP索引下推

原理過程分析：

name使用到了索引，在二級索引過濾name的查詢條件后，然后就直接回表了嗎？
此時并沒有直接回表，因為使用到的聯(lián)合索引中還包含了stuno字段，我們可以在過濾name后的數(shù)據(jù)集中進行stuno的過濾，然后再去回表

減少了回表的隨機IO的次數(shù)

在不使用ICP索引掃描的過程：

storage層：只將滿足index key條件的索引記錄對應(yīng)的整行記錄取出，返回給server層

server 層：對返回的數(shù)據(jù)，使用后面的where條件過濾，直至返回最后一行

在這里插入圖片描述

使用ICP掃描的過程：
storage層：首先將index key條件滿足的索引記錄區(qū)間確定，然后在索引上使用index filter進行過濾。將滿足的indexfilter條件的索引記錄才去回表取出整行記錄返回server層。不滿足index filter條件的索引記錄丟棄，不回表、也不會返回server層。

server 層：對返回的數(shù)據(jù)，使用table filter條件做最后的過濾

在這里插入圖片描述
使用前后的成本差別：

使用前，存儲層多返回了需要被index filter過濾掉的整行記錄
使用ICP后，直接就去掉了不滿足index filter條件的記錄，省去了他們回表和傳遞到server層的成本。
ICP的加速效果取決于在存儲引擎內(nèi)通過 ICP篩選掉的數(shù)據(jù)的比例

ICP的使用條件：

只能用于二級索引(secondary index)
explain顯示的執(zhí)行計劃中type值（join 類型）為 range 、 ref 、 eq_ref 或者 ref_or_null 。
并非全部where條件都可以用ICP篩選，如果where條件的字段不在索引列中，還是要讀取整表的記錄
到server端做where過濾。
ICP可以用于MyISAM和InnnoDB存儲引擎
MySQL 5.6版本的不支持分區(qū)表的ICP功能，5.7版本的開始支持。
當(dāng)SQL使用覆蓋索引時，不支持ICP優(yōu)化方法
ICP是一定基于有回表操作的情況下的

10 一切基于成本考慮

上述說明了索引失效的很多種情況，但是實際中并不是死板的，所有的一切，是否使用索引，最終還是交由Mysql的優(yōu)化器來根據(jù)成本進行決策。

舉個例子：

!= 或者 <> 無法使用索引

上述結(jié)論有提到過

create index idx_age_name on student(age,name)

創(chuàng)建一個索引然后執(zhí)行sql

explain select * from student where age <> 20

在這里插入圖片描述
可以看到并沒有使用上索引，然后我再修改一下sql

explain select age,name from student where age <> 20

在這里插入圖片描述
可以看到已經(jīng)使用上了索引，因為此時我改變查詢列，完全滿足索引覆蓋，沒必要回表，開銷小，所以就使用上了索引。總之一切都是基于開銷來做決定。

查看全文

http://m.aloenet.com.cn/news/42030.html

国产亚洲精品福利在线无卡一,国产精久久一区二区三区,亚洲精品无码国模,精品久久久久久无码专区不卡

大連開發(fā)區(qū)網(wǎng)站制作建設(shè)公司游戲推廣合作

1. 數(shù)據(jù)準備

2. 索引失效的11種情況

2.1 全值匹配我最愛

2.2 最佳左前綴法則

2.3 主鍵插入順序

2.4 計算、函數(shù)、類型轉(zhuǎn)換(自動或手動)導(dǎo)致索引失效

2.5 范圍條件右邊的列索引失效

2.6 不等于(!= 或者<>)索引失效

2.7 is null可以使用索引，is not null無法使用索引

2.8 like以通配符%開頭索引失效

2.9 or 前后存在非索引的列，索引失效

2.10 數(shù)據(jù)庫和表的字符集統(tǒng)一使用utf8mb4

3. 關(guān)聯(lián)查詢優(yōu)化

3.1 左外連接

3.2 內(nèi)連接

3.3 join的底層原理

3.3.1 Simple Nested-Loop Join

3.3.3 Index Nested-Loop Join

3.3.3 Block Nested-Loop Join

3.3.4 相關(guān)參數(shù)

3.3.5 總結(jié)

3.3.6 Hash Join

4. 子查詢優(yōu)化

5. 排序優(yōu)化

5.1 fileSort算法

6. group by 優(yōu)化

7. 分頁優(yōu)化

8. 覆蓋索引

9. 索引下推（ICP）

10 一切基于成本考慮

相關(guān)文章：

国产亚洲精品福利在线无卡一,国产精久久一区二区三区,亚洲精品无码国模,精品久久久久久无码专区不卡

1. 數(shù)據(jù)準備

2. 索引失效的11種情況

2.1 全值匹配我最愛

2.2 最佳左前綴法則

2.3 主鍵插入順序

2.4 計算、函數(shù)、類型轉(zhuǎn)換(自動或手動)導(dǎo)致索引失效

2.5 范圍條件右邊的列索引失效

2.6 不等于(!= 或者<>)索引失效

2.7 is null可以使用索引，is not null無法使用索引

2.8 like以通配符%開頭索引失效

2.9 or 前后存在非索引的列，索引失效

2.10 數(shù)據(jù)庫和表的字符集統(tǒng)一使用utf8mb4

3. 關(guān)聯(lián)查詢優(yōu)化

3.1 左外連接

3.2 內(nèi)連接

3.3 join的底層原理

3.3.1 Simple Nested-Loop Join

3.3.3 Index Nested-Loop Join

3.3.3 Block Nested-Loop Join

3.3.4 相關(guān)參數(shù)

3.3.5 總結(jié)

3.3.6 Hash Join

4. 子查詢優(yōu)化

5. 排序優(yōu)化

5.1 fileSort算法

6. group by 優(yōu)化

7. 分頁優(yōu)化

8. 覆蓋索引

9. 索引下推（ICP）

10 一切基于成本考慮

相關(guān)文章：

2.4 計算、函數(shù)、類型轉(zhuǎn)換(自動或手動)導(dǎo)致索引失效