當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql not in优化_MySQL性能优化 — 实践篇2

發(fā)布時間：2025/3/21 数据库 19 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql not in优化_MySQL性能优化 — 实践篇2 小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

點贊再看，養(yǎng)成習(xí)慣，微信搜一搜【一角錢小助手】關(guān)注更多原創(chuàng)技術(shù)文章。本文 GitHub org_hejianhui/JavaStudy 已收錄，有我的系列文章。

前言

MySQL索引底層數(shù)據(jù)結(jié)構(gòu)與算法
MySQL性能優(yōu)化原理-前篇
MySQL性能優(yōu)化-實踐篇1

上一篇《MySQL性能優(yōu)化-實踐篇1》我們講了數(shù)據(jù)庫表設(shè)計的一些原則，Explain工具的介紹、SQL語句優(yōu)化索引的最佳實踐，本篇繼續(xù)來聊聊 MySQL 如何選擇合適的索引。

MySQL Trace 工具

MySQL 最終是否選擇走索引或者一張表涉及多個索引，最終是如何選擇索引，可以使用 trace 工具來一查究竟，開啟 trace工具會影響 MySQL 性能，所以只能臨時分析 SQL 使用，用完之后立即關(guān)閉。

案例分析

講 trace 工具之前我們先來看一個案例：

# 示例表
CREATE TABLE`employees`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年齡',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '職位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入職時間',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='員工記錄表';

INSERT INTO employees(name,age,position,hire_time)VALUES('ZhangSan',23,'Manager',NOW());
INSERT INTO employees(name,age,position,hire_time)VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

MySQL 如何選擇合適的索引

EXPLAIN select * from employees where name > 'a';

如果用name索引需要遍歷name字段聯(lián)合索引樹，然后還需要根據(jù)遍歷出來的主鍵值去主鍵索引樹里再去查出最終數(shù)據(jù)，成本比全表掃描還高，可以用覆蓋索引優(yōu)化，這樣只需要遍歷name字段的聯(lián)合索引樹就能拿到所有結(jié)果，如下:

EXPLAIN select name,age,position from employees where name > 'a' ;
EXPLAIN select * from employees where name > 'zzz' ;

對于上面這兩種 name>'a' 和 name>'zzz' 的執(zhí)行結(jié)果，mysql最終是否選擇走索引或者一張表涉及多個索引，mysql最終如何選擇索引，我們可以用trace工具來一查究竟，開啟trace工具會影響mysql性能，所以只能臨時分析sql使用，用完之后立即關(guān)閉。

trace工具用法

開啟/關(guān)閉Trace

#開啟trace
set session optimizer_trace="enabled=on",end_markers_in_json=on;
#關(guān)閉trace
set session optimizer_trace="enabled=off";

案例1

執(zhí)行這兩句sql

select * from employees where name >'a' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;

提出來trace值，詳見注釋

{
"steps": [
{
"join_preparation": { --第一階段：SQL準(zhǔn)備階段
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { --第二階段：SQL優(yōu)化階段
"select#": 1,
"steps": [
{
"condition_processing": { --條件處理
"condition": "WHERE",
"original_condition": "(`employees`.`name` > 'a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`employees`.`name` > 'a')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [ --表依賴詳情
{
"table": "`employees`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [ --預(yù)估表的訪問成本
{
"table": "`employees`",
"range_analysis": {
"table_scan": { --全表掃描
"rows": 3, --掃描行數(shù)
"cost": 3.7 --查詢成本
} /* table_scan */,
"potential_range_indexes": [ --查詢可能使用的索引
{
"index": "PRIMARY", --主鍵索引
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_position", --輔助索引
"usable": true,
"key_parts": [
"name",
"age",
"position",
"id"
] /* key_parts */
},
{
"index": "idx_age",
"usable": false,
"cause": "not_applicable"
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": { --分析各個索引使用成本
"range_scan_alternatives": [
{
"index": "idx_name_age_position",
"ranges": [
"a < name" --索引使用范圍
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false, --使用該索引獲取的記錄是否按照主鍵排序
"using_mrr": false,
"index_only": false, --是否使用覆蓋索引
"rows": 3, --索引掃描行數(shù)
"cost": 4.61, --索引使用成本
"chosen": false, --是否選擇該索引
"cause": "cost"
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`employees`",
"best_access_path": { --最優(yōu)訪問路徑
"considered_access_paths": [ --最終選擇的訪問路徑
{
"rows_to_scan": 3,
"access_type": "scan", --訪問類型：為sacn，全表掃描
"resulting_rows": 3,
"cost": 1.6,
"chosen": true, --確定選擇
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 3,
"cost_for_plan": 1.6,
"sort_cost": 3,
"new_cost_for_plan": 4.6,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`employees`.`name` > 'a')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`employees`",
"attached": "(`employees`.`name` > 'a')"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`employees`.`position`",
"items": [
{
"item": "`employees`.`position`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`employees`.`position`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"index_order_summary": {
"table": "`employees`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "unknown",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [
{
"table": "`employees`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { --第三階段：SQL執(zhí)行階段

"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 3,
"examined_rows": 3,
"number_of_tmp_files": 0,
"sort_buffer_size": 200704,
"sort_mode": ""
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}

結(jié)論：全表掃描的成本低于索引掃描，所以MySQL最終選擇全表掃描。

案例2

select * from employees where name > 'zzz' order by position;
SELECT * FROM information_schema.OPTIMIZER_TRACE;

結(jié)論：查看trace字段可知索引掃描的成本低于全表掃描，所以MySQL最終選擇索引掃描。

常見SQL深入優(yōu)化

Order by與Group by優(yōu)化

案例1

EXPLAIN select * from employees where name = 'ZhangSan' and position = 'dev' order by age;

分析：

利用最左前綴法則：中間字段不能斷，因此查詢用到了 name索引?，從 key_len = 74 也能看出，age 索引列用在排序過程過程中，因為 Extra 字段里沒有 using filesort 。

案例2

EXPLAIN select * from employees where name = 'ZhangSan' order by position;

分析：

從 explain 的執(zhí)行結(jié)果來看：key_len = 74，查詢使用了 name 索引，由于用了 position 進行排序，跳過了 age，出現(xiàn)了 Using filesort。

案例3

EXPLAIN select * from employees where name = 'ZhangSan' order by age,position

分析：

查詢只用到索引name，age 和 position 用于排序，無Using filesort。

案例4

EXPLAIN select * from employees where name = 'ZhangSan' order by position,age

分析：

和案例3中explain的執(zhí)行結(jié)果一樣，但是出現(xiàn)了Using filesort ，因為索引的創(chuàng)建順序為 name,age,position?, 但是排序的時候 age 和 position 顛倒位置了。

案例5

EXPLAIN select * from employees where name = 'ZhangSan' and age = 18 order by position,age

分析：

與案例4對比，在Extra中并未出現(xiàn)** Using filesort **，因為 age 為常量，在排序中被優(yōu)化，所以索引未顛倒，不會出現(xiàn) Using filesort 。

案例6

EXPLAIN select * from employees where name = 'ZhangSan' order by age asc, position desc;

分析：

雖然排序的字段列與索引順序一樣，且 order by 默認(rèn)升序，這里 position desc 變成列降序，導(dǎo)致與索引的排序方式不同，從而產(chǎn)生 Using filesort 。MySQL8 以上版本有降序索引可以支持該種查詢方式。

案例7

EXPLAIN select * from employees where name in ('ZhangSan', 'hjh') order by age, position;

分析：

對于排序來說，多個相等條件也是范圍查詢。

案例8

EXPLAIN select * from employees where name > 'a' order by name;

可以用覆蓋索引優(yōu)化

EXPLAIN select name,age,position from employees where name > 'a' order by name;

優(yōu)化總結(jié)

MySQL支持兩種方式的排序 filesort 和 index。Using index 是指MySQL 掃描索引本身完成排序。index 效率高，filesort 效率低。

order by 滿足兩種情況會使用 Using index.

order by 語句使用索引最左前例。
使用 where 子句與 order by 子句條件列組合滿足索引最左前例。

盡量在索引列上完成排序，遵循索引建立(索引創(chuàng)建的順序)時的最左前綴法則。如果 order by 的條件不在索引列上，就會產(chǎn)生 Using filesort。能用覆蓋索引盡量用覆蓋索引。group by 和 order by 很類似，其實質(zhì)是先排序后分組，遵循索引創(chuàng)建順序的最左前綴法則。對于 group by 的優(yōu)化如果不需要排序的可以加上 order by null?禁止排序。注意：where 高于 having，能寫在 where 中的限定條件就不要去 having 限定了。

Using filesort文件排序原理

filesort文件排序方式

單路排序：是一次性取出滿足條件行的所有字段，然后在 sort buffer 中進行排序；用 trace 工具可以看到 sort_mode 信息里顯示 < sort_key, additional_fields > 或者 < sort_key, packed_additional_fields >。
雙路排序(又叫回表排序模式)：是首先根據(jù)相應(yīng)的條件取出相應(yīng)的排序字段和可以直接定位運行數(shù)據(jù)的行ID，然后在 sort buffer 中進行排序，排序完后需要再次取回其它需要的字段；用 trace 工具可以看到 sort_mode 信息里顯示 < sort_key, rowid >

MySQL 通過比較系統(tǒng)變量 max_length_for_sort_data (默認(rèn)1024字節(jié)) 的大小和需要查詢的字段總大小來判斷使用那種排序模式。

如果max_length_for_sort_data 比查詢的字段的總長度大，那么使用單路排序模式；
如果max_length_for_sort_data 比查詢字段的總長度小，那么使用雙路排序模式。

驗證各種排序方式

EXPLAIN select * from employees where name = 'ZhangSan' order by position;

查看下這條sql對應(yīng)trace結(jié)果如下(只展示排序部分):

set session optimizer_trace="enabled=on",end_markers_in_json=on; #開啟trace
select * from employees where name = 'ZhangSan' order by position;
select * from information_schema.OPTIMIZER_TRACE;
"join_execution": { --SQL執(zhí)行階段
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": { --文件排序信息
"rows": 1, --預(yù)計掃描行數(shù)
"examined_rows": 1, --參數(shù)排序的行
"number_of_tmp_files": 0, --使用臨時文件的個數(shù)，這個值如果為0代表全部使用的sort_buffer內(nèi)存排序，否則使用的磁盤文件排序
"sort_buffer_size": 200704, --排序緩存的大小
"sort_mode": "" --排序方式，這里用的單路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */

修改系統(tǒng)變量 max_length_for_sort_data (默認(rèn)1024字節(jié)) ，employees 表所有字段長度總和肯定大于10字節(jié)

set max_length_for_sort_data = 10;
select * from employees where name = 'ZhangSan' order by position;
select * from information_schema.OPTIMIZER_TRACE;

trace排序部分結(jié)果:

"join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 1,
"examined_rows": 1,
"number_of_tmp_files": 0,
"sort_buffer_size": 53248,
"sort_mode": "" --排序方式，這里用餓的雙路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */

單路排序的詳細(xì)過程：

從索引 name 找到第一個滿足 name='ZhangSan' 條件的主鍵 id；

根據(jù)主鍵id取出整行，取出所有字段的值，存入sort_buffer中；

從索引name找到下一個滿足 name='ZhangSan' 條件的主鍵 id;

重復(fù)步驟2、3直到不滿足 name='ZhangSan'；

對 sort_buffer 中的數(shù)據(jù)按照字段 position 進行排序；

返回結(jié)果給客戶端

雙路排序的詳細(xì)過程：

從索引 name 找到第一個滿足 name='ZhangSan' 的主鍵id；

根據(jù)主鍵id取出整行，把排序字段 position 和主鍵id 這兩個字段放到 sort_buffer 中；

從索引 name 取下一個滿足 name='ZhangSan' 記錄的主鍵id；

重復(fù)步驟3、4直到不滿足 name='ZhangSan'；

對 sort_buffer 中的字段 position 和主鍵id按照 position 進行排序；

遍歷排序好的 id 和字段 position，按照 id 的值回到原表中取出所有的字段的值返回給客戶端。

對比兩個排序模式，單路排序會把所有需要查詢的字段都放到 sort_buffer 中，而雙路排序只會把主鍵和需要排序的字段放到 sort_buffer 中進行排序，然后再通過主鍵回到原表查詢需要的字段。

如果MySQL排序內(nèi)存配置的比較小并且沒有條件繼續(xù)增加了，可以適當(dāng)把 max_length_for_sort_data?配置小點，讓優(yōu)化器選擇使用雙路排序算法，可以在 sort_buffer 中一次排序更多的行，只是需要再根據(jù)主鍵回到原表取數(shù)據(jù)。

如果MySQL排序內(nèi)存有條件可以配置比較大，可以適當(dāng)增大 max_length_for_sort_data?的值，讓優(yōu)化器優(yōu)先選擇全字段排序(單路排序)，把需要的字段放到 sort_buffer 中，這樣排序后就會直接從內(nèi)存里返回查詢結(jié)果了。

所以，MySQL 通過 max_length_for_sort_data?這個參數(shù)來控制排序，在不同場景使用不同的排序模式，從而提升排序效率。

注意：如果全部使用sort_buffer 內(nèi)存排序一般情況下效率會高于磁盤文件排序，但不能因為這個就隨便增大 sort_buffer(默認(rèn)1M)，MySQL很多參數(shù)設(shè)置都做過優(yōu)化的，不要輕易調(diào)整。

分頁查詢優(yōu)化

在這我們先往 employess?插入一些測試數(shù)據(jù)

drop procedure if exists insert_emp;
delimiter ;;
create procedure insert_emp()
begin
declare i int;
set i=1;
while(i<=100000) do
insert into employees(name,age,position) values(CONCAT('hjh',i),i,'dev');
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp();

很多時候我們業(yè)務(wù)系統(tǒng)實現(xiàn)分頁功能可能會用如下SQL實現(xiàn)

select * from employees limit 10000,10;

表示從表 employees 中取出從 10001 行開始的 10 行記錄?？此浦徊樵兞?10 條記錄，實際這條 SQL 是先讀取 10010 條記錄，然后拋棄前 10000 條記錄，然后讀到后面 10 條想要的數(shù)據(jù)。因此要查詢一張大表比較靠后的數(shù)據(jù)，執(zhí)行效率是非常低的。

常見的分頁場景優(yōu)化技巧

根據(jù)自增且連續(xù)的主鍵排序的分頁查詢

根據(jù)非主鍵字段排序的分頁查詢

案例1: 根據(jù)自增且連續(xù)的主鍵排序的分頁查詢

首先來看一個根據(jù)自增且連續(xù)主鍵排序的分頁查詢的例子：

select * from employees limit 9000,5;

該 SQL 表示查詢從第 9001開始的五行數(shù)據(jù)，沒添加單獨 order by，表示通過主鍵排序。我們再看表 employees ，因為主鍵是自增并且連續(xù)的，所以可以改寫成按照主鍵去查詢從第 9001開始的五行數(shù)據(jù)，如下:

select * from employees where id > 9000 limit 5;

查詢結(jié)果是一致的，我們再對比一下執(zhí)行計劃：

EXPLAIN select * from employees limit 9000,5;
EXPLAIN select * from employees where id > 9000 limit 5;

顯然改寫后的 SQL 走了索引，而且掃描的行數(shù)大大減少，執(zhí)行效率更高。但是，這條改寫的 SQL 在很多場景并不實用，因為表中可能某些記錄被刪后，主鍵空缺，導(dǎo)致結(jié)果不一致，如下圖試驗所示(先刪除一條前面的記錄，然后再測試原 SQL 和優(yōu)化后的 SQL):兩條 SQL 的結(jié)果并不一樣，因此，如果主鍵不連續(xù)，不能使用上面描述的優(yōu)化方法。

另外如果原SQL是order by 非主鍵的字段，按照上面說的方法改寫會導(dǎo)致兩條SQL的結(jié)果不一致。所以這種改寫得滿足以下兩個條件：

主鍵自增且連續(xù)
結(jié)果是按照主鍵排序的

案例2: 根據(jù)非主鍵字段排序的分頁查詢

再看一個根據(jù)非主鍵字段排序的分頁查詢，SQL 如下:

select * from employees ORDER BY name limit 9000,5;
EXPLAIN select * from employees ORDER BY name limit 90000,5;

發(fā)現(xiàn)并沒有使用 name 字段的索引(key 字段對應(yīng)的值為 null)，具體原因上前面講過 : 掃描整個索引并查找到?jīng)]索引的行(可能要遍歷多個索引樹)的成本比掃描全表的成本更高，所以優(yōu)化器放棄使用索引。知道不走索引的原因，那么怎么優(yōu)化呢? 其實關(guān)鍵是讓排序時返回的字段盡可能少，所以可以讓排序和分頁操作先查出主鍵，然后根據(jù)主鍵查到對應(yīng)的記錄，SQL 改寫如下：

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

需要的結(jié)果與原 SQL 一致，執(zhí)行時間減少了一半以上，我們再對比優(yōu)化前后sql的執(zhí)行計劃:

EXPLAIN select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
原 SQL 使用的是 filesort 排序，而優(yōu)化后的 SQL 使用的是索引排序。

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

#示例表
CREATE TABLE `t1` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`a` INT (11) DEFAULT NULL,
`b` INT (11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE = INNODB AUTO_INCREMENT = 10001 DEFAULT CHARSET = utf8;

CREATE TABLE t2 LIKE t1;

往t1表插入1萬行記錄，往t2表插入100行記錄

#t1 1萬條記錄
drop procedure if exists insert_emp_t1;
delimiter ;;
create procedure insert_emp_t1()
begin
declare i int;
set i=1;
while(i<=10000) do
insert into t1(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp_t1();

#t2 100條記錄
drop procedure if exists insert_emp_t2;
delimiter ;;
create procedure insert_emp_t2()
begin
declare i int;
set i=1;
while(i<=100) do
insert into t2(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_emp_t2();

MySQL 的表關(guān)聯(lián)常見有兩種算法

Nested-Loop Join 算法

Block Nested-Loop Join 算法

案例1：嵌套循環(huán)連接 Nested-Loop Join(NLJ)算法

一次一行循環(huán)地從第一張表(稱為驅(qū)動表)中讀取行，在這行數(shù)據(jù)中取到關(guān)聯(lián)字段，根據(jù)關(guān)聯(lián)字段在另一張表(被驅(qū)動表)里取出滿足條件的行，然后取出兩張表的結(jié)果合集。

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

從執(zhí)行計劃中可以看到這些信息:

驅(qū)動表是 t2，被驅(qū)動表是 t1。先執(zhí)行的就是驅(qū)動表(執(zhí)行計劃結(jié)果的id如果一樣則按從上到下順序執(zhí)行sql);優(yōu)化器一般會優(yōu)先選擇小表做驅(qū)動表。所以使用 inner join 時，排在前面的表并不一定就是驅(qū)動表。
使用了 NLJ 算法。一般 join 語句中，如果執(zhí)行計劃 Extra 中未出現(xiàn) Using join buffer 則表示使用的 join 算法是 NLJ。

上面SQL的大致流程如下：

從表 t2 中讀取一行數(shù)據(jù)；

從第1步的數(shù)據(jù)中，取出關(guān)鍵字字段 a，到表 t1 中查找；

取出表 t1 中滿足條件的行，跟 t2 中獲取到的結(jié)果合并，作為結(jié)果返回給客戶端；

重復(fù)上面 3 步。

整個過程會讀取 t2 表的所有數(shù)據(jù)(掃描100行)，然后遍歷這每行數(shù)據(jù)中字段 a 的值，根據(jù) t2 表中的 a 的值索引掃描 t1 表中對應(yīng)的行(掃描 100次 t1 表的索引，1次掃描可以認(rèn)為最終只掃描 t1 表一行完整數(shù)據(jù)，也就是總共 t1 表也掃描了100行)。因此整個過程掃描了 200 行。

如果被驅(qū)動表的關(guān)聯(lián)字段沒有索引，使用NLJ算法性能會比較低(下面有詳細(xì)解釋)，MySQL 會選擇 Block Nested-Loop Join 算法。

案例2：基于塊的嵌套循環(huán)連接 Block Nested-Loop Join(BNL)算法

把驅(qū)動表的數(shù)據(jù)讀入到 join_buffer 中，然后掃描被驅(qū)動表，把被驅(qū)動表每一行取出來跟 join_buffer 中的數(shù)據(jù)做對比。

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;

Extra 中的Using join buffer (Block Nested Loop)說明該關(guān)聯(lián)查詢使用的是 BNL 算法。

上面sql的大致流程如下:

把 t2 的所有數(shù)據(jù)放入到 join_buffer 中

把表 t1 中每一行取出來，跟 join_buffer 中的數(shù)據(jù)做對比

返回滿足 join 條件的數(shù)據(jù)

整個過程對表 t1 和 t2 都做了一次全表掃描，因此掃描的總行數(shù)為10000(表 t1 的數(shù)據(jù)總量) + 100(表 t2 的數(shù)據(jù)總量) = 10100。并且 join_buffer 里的數(shù)據(jù)是無序的，因此對表 t1 中的每一行，都要做 100 次判斷，所以內(nèi)存中的判斷次數(shù)是 100 * 10000= 100 萬次。

被驅(qū)動表的關(guān)聯(lián)字段沒索引為什么要選擇使用 BNL 算法而不使用 Nested-Loop Join 呢?

如果上面第二條sql使用 Nested-Loop Join，那么掃描行數(shù)為 100 * 10000 = 100萬次，這個是磁盤掃描。

很顯然，用BNL磁盤掃描次數(shù)少很多，相比于磁盤掃描，BNJ 的內(nèi)存計算會快得多。

因此MySQL對于被驅(qū)動表的關(guān)聯(lián)字段沒索引的關(guān)聯(lián)查詢，一般都會使用 BNL 算法。如果有索引一般選擇 NLJ 算法，有索引的情況下 NLJ 算法比 BNL算法性能更高。

對于關(guān)聯(lián)SQL的優(yōu)化

關(guān)聯(lián)字段加索引，讓mysql做join操作時盡量選擇NLJ算法
小標(biāo)驅(qū)動大表，寫多表連接sql時如果明確知道哪張表是小表可以用straight_join寫法固定連接驅(qū)動方式，省去mysql優(yōu)化器自己判斷的時間

straight_join解釋

straight_join功能同join類似，但能讓左邊的表來驅(qū)動右邊的表，能改表優(yōu)化器對于聯(lián)表查詢的執(zhí)行順序。

比如 : select * from t2 straight_join t1 on t2.a = t1.a; 代表制定mysql選?t2 表作為驅(qū)動表。

straight_join 只適用于inner join，并不適用于left join，right join。(因為left join，right join已經(jīng)代表指定了表的執(zhí)行順序)
盡可能讓優(yōu)化器去判斷，因為大部分情況下mysql優(yōu)化器是比人要聰明的。使用straight_join一定要慎重，因為部分情況下人為指定的執(zhí)行順序并不一定會比優(yōu)化引擎要靠譜。

in 和 exsits 優(yōu)化

原則：小表驅(qū)動大表，即小的數(shù)據(jù)集驅(qū)動大的數(shù)據(jù)集。

in：當(dāng)B表的數(shù)據(jù)集小于A表的數(shù)據(jù)集時，in優(yōu)于exists

select * from A where id in(select id from B)
#等價于:
for(select id from B){
select * from A where A.id = B.id
}

exists：當(dāng)A表的數(shù)據(jù)集小于B表的數(shù)據(jù)集時，exists優(yōu)于in

將主查詢A的數(shù)據(jù)，放到子查詢B中做條件驗證，根據(jù)驗證結(jié)果(true或false)來決定主查詢的數(shù)據(jù)是否保留

select * from A where exists (select 1 from B where B.id=A.id)
#等價于:
for(select * from A){
select * from B where B.id = A.id
}

#A表與B表的ID字段應(yīng)建立索引

EXISTS (subquery)只返回TRUE或FALSE,因此子查詢中的SELECT * 也可以用SELECT 1替換,官方說法是實際執(zhí)行時會忽略SELECT清單,因此沒有區(qū)別；

EXISTS子查詢的實際執(zhí)行過程可能經(jīng)過了優(yōu)化而不是我們理解上的逐條對比；

EXISTS子查詢往往也可以用JOIN來代替，何種最優(yōu)需要具體問題具體分析；

Count(*) 查詢優(yōu)化

臨時關(guān)閉mysql查詢緩存，為了查看sql多次執(zhí)行的真實時間。

set global query_cache_size=0;
set global query_cache_type=0;
EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;

四個sql的執(zhí)行計劃一樣，說明這四個sql執(zhí)行效率應(yīng)該差不多，區(qū)別在于根據(jù)某個字段count不會統(tǒng)計字段為null值的數(shù)據(jù)行。

為什么mysql最終選擇輔助索引而不是主鍵聚集索引?

因為二級索引相對主鍵索引存儲數(shù)據(jù)更少，檢索性能應(yīng)該更高

常見的優(yōu)化方法如下：

查詢MySQL自己維護的總行數(shù)
show table status
將總數(shù)維護到Redis里
增加計數(shù)表

查詢MySQL自己維護的總行數(shù)

對于myisam存儲引擎的表做不帶where條件的count查詢性能是很高的，因為myisam存儲引擎的表的總行數(shù)會被 mysql存儲在磁盤上，查詢不需要計算。對于innodb存儲引擎的表mysql不會存儲表的總記錄行數(shù)，查詢count需要實時計算。

show table status

如果只需要知道表總行數(shù)的估計值可以用如下sql查詢，性能很高

將總數(shù)維護到Redis里

插入或刪除表數(shù)據(jù)行的時候同時維護redis里的表總行數(shù)key的計數(shù)值(用incr或decr命令)，但是這種方式可能不準(zhǔn)，很難保證表操作和redis操作的事務(wù)一致性。

增加計數(shù)表

插入或刪除表數(shù)據(jù)行的時候同時維護計數(shù)表，讓他們在同一個事務(wù)里操作。

部分圖片來源于網(wǎng)絡(luò)，版權(quán)歸原作者，侵刪。
?點擊閱讀原文，查看往期內(nèi)容！??????????????????????????????????????????????????????????????快留言?和我互動吧～

總結(jié)

以上是生活随笔為你收集整理的mysql not in优化_MySQL性能优化 — 实践篇2的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

上一篇： python输入隔行的数组_python
下一篇： mysql打印语句_大数据挖掘—（八）：