Maison > Article > base de données > Pratique (2) - Optimisation des performances MySQL
Pratique (2) - Optimisation des performances MySQL
- coldplay.xixiavant
- 2020-09-19 09:45:592267parcourir
Recommandations d'apprentissage associées : tutoriel mysql
Préface
- Indice MySQL sous-jacent à la structure et à l'algorithme des données
- Principes d'optimisation des performances MySQL - Partie 1
- Pratique (1) - Optimisation des performances MySQL
Dans l'article précédent "Pratique (1)--Optimisation des performances MySQL", nous avons parlé de certains principes de conception de tables de base de données, de l'introduction à l'outil Explain et de la meilleure façon de optimiser les index pour les instructions SQL En pratique, cet article continue d'expliquer comment choisir l'index approprié dans MySQL.
Outil de trace MySQL
Que MySQL choisisse finalement d'utiliser un index ou une table implique plusieurs index, et comment choisir un index, vous pouvez utiliser l'outil de trace pour le savoir. L'outil de trace affectera les performances de MySQL, il ne pourra donc analyser que temporairement l'utilisation de SQL et le fermer immédiatement après utilisation.
Analyse de cas
Avant de parler de l'outil de trace, jetons un coup d'œil à un cas :
# 示例表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());复制代码
Comment choisir l'index approprié dans MySQL
EXPLAIN select * from employees where name > 'a';复制代码
Si vous utilisez l'index de nom, vous devez parcourir l'arborescence d'index conjoint du champ de nom, puis vous devez accéder à l'arborescence d'index de clé primaire en fonction de l'arborescence parcourue. valeur de clé primaire pour trouver les données finales. Le coût est supérieur à celui d'une analyse complète de la table. Élevé, vous pouvez utiliser l'optimisation de l'index de couverture, de sorte qu'il vous suffit de parcourir l'arborescence d'index conjointe du champ de nom pour obtenir tous les résultats. suit :
EXPLAIN select name,age,position from employees where name > 'a' ;复制代码
EXPLAIN select * from employees where name > 'zzz' ;复制代码
Concernant les résultats d'exécution des deux name>'a' et name>'zzz' ci-dessus, si mysql choisit finalement d'utiliser l'index ou une table implique plusieurs index, comment mysql choisit finalement l'index, nous pouvons utiliser l'outil de trace pour le savoir, ouvrir l'outil de trace Cela affectera les performances de mysql, vous ne pouvez donc analyser que temporairement l'utilisation de SQL et le fermer immédiatement après utilisation.
Utilisation de l'outil de trace
Activer/désactiver Trace
#开启traceset session optimizer_trace="enabled=on",end_markers_in_json=on; #关闭traceset session optimizer_trace="enabled=off";复制代码
Cas 1
Exécuter ces deux phrases de sql
select * from employees where name >'a' order by position;sELECT * FROM information_schema.OPTIMIZER_TRACE; 复制代码
pour présenter la valeur de trace, voir la note
{ "steps": [
{ "join_preparation": { --第一阶段：SQL准备阶段 "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优化阶段 "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": [ --预估表的访问成本
{ "table": "`employees`", "range_analysis": { "table_scan": { --全表扫描 "rows": 3, --扫描行数 "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_pes_for_eq_ranges": true, "rowid_ordered": false, --使用该索引获取的记录是否按照主键排序 "using_mrr": false, "index_only": false, --是否使用覆盖索引 "rows": 3, --索引扫描行数 "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": { --最优访问路径 "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执行阶段
"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": "<sort_key, packed_additional_fields>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}复制代码Conclusion : Le coût de l'analyse complète de la table est inférieur à celui de l'analyse d'index, donc MySQL choisit finalement l'analyse complète de la table.
Cas 2
select * from employees where name > 'zzz' order by position;SELECT * FROM information_schema.OPTIMIZER_TRACE; 复制代码
Conclusion : En regardant le champ de trace, nous pouvons voir que le coût de l'analyse d'index est inférieur à celui de l'analyse complète de la table, donc MySQL enfin choisit l'analyse d'index.
Optimisation en profondeur Common SQL
Order by et Group byoptimisation
Cas 1
EXPLAIN select * from employees where name = 'ZhangSan' and position = 'dev' order by age复制代码
Analyse :
Utilisez la règle de préfixe la plus à gauche : le champ du milieu ne peut pas être brisé, la requête utilise donc name索引, qui peut également visible depuis key_len = 74 Out, la colonne d'index d'âge est utilisée dans le processus tri , car il n'y a pas de using filesort dans le champ Extra.
Cas 2
EXPLAIN select * from employees where name = 'ZhangSan' order by position复制代码
Analyse :
À partir des résultats d'exécution d'expliquer Regardez : key_len = 74, la requête utilise le nom index, et comme la position est utilisée pour le tri, l'âge est ignoré et Using filesort apparaît.
Cas 3
EXPLAIN select * from employees where name = 'ZhangSan' order by age,position复制代码
Analyse :
La requête utilise uniquement 索引name, l'âge et la position sont utilisés pour le tri, sans Using filesort.
Cas 4
EXPLAIN select * from employees where name = 'ZhangSan' order by position,age复制代码
Analyse :
et expliquer dans le cas 3 Le le résultat de l'exécution est le même, mais Using filesort apparaît, car l'ordre de création de l'index est name,age,position, mais lors du tri, l'âge et la position inversent la position .
案例5
EXPLAIN select * from employees where name = 'ZhangSan' and age = 18 order by position,age复制代码
分析：
与案例4对比，在Extra中并未出现** Using filesort **，因为 age 为常量，在排序中被优化，所以索引未颠倒，不会出现 Using filesort 。
案例6
EXPLAIN select * from employees where name = 'ZhangSan' order by age asc, position desc;复制代码
分析：
虽然排序的字段列与索引顺序一样，且 order by 默认升序，这里 position desc 变成列降序，导致与索引的排序方式不同，从而产生 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;复制代码
可以用覆盖索引优化
EXPLAIN select name,age,position from employees where name > 'a' order by name;复制代码
优化总结
- MySQL支持两种方式的排序
filesort和index。Using index 是指MySQL 扫描索引本身完成排序。index 效率高，filesort 效率低。 - order by 满足两种情况会使用 Using index.
- order by 语句使用索引最左前例。
- 使用 where 子句与 order by 子句条件列组合满足索引最左前例。
- 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
- 如果 order by 的条件不在索引列上，就会产生 Using filesort。
- 能用覆盖索引尽量用覆盖索引。
- group by 和 order by 很类似，其实质是先排序后分组，遵循索引创建顺序的最左前缀法则。对于 group by 的优化如果不需要排序的可以加上
order by null禁止排序。注意：where 高于 having，能写在 where 中的限定条件就不要去 having 限定了。
Using filesort文件排序原理
filesort文件排序方式
-
单路排序：是一次性取出满足条件行的所有字段，然后在
sort buffer中进行排序；用 trace 工具可以看到 sort_mode 信息里显示 或者 。 - 双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位运行数据的行ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用 trace 工具可以看到 sort_mode 信息里显示
MySQL 通过比较系统变量 max_length_for_sort_data (默认1024字节) 的大小和需要查询的字段总大小来判断使用那种排序模式。
- 如果
max_length_for_sort_data比查询的字段的总长度大，那么使用单路排序模式； - 如果
max_length_for_sort_data比查询字段的总长度小，那么使用双路排序模式。
验证各种排序方式
EXPLAIN select * from employees where name = 'ZhangSan' order by position;复制代码
查看下这条sql对应trace结果如下(只展示排序部分):
set session optimizer_trace="enabled=on",end_markers_in_json=on; #开启traceselect * from employees where name = 'ZhangSan' order by position;select * from information_schema.OPTIMIZER_TRACE;复制代码
"join_execution": { --SQL执行阶段
"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, --使用临时文件的个数，这个只如果为0代表全部使用的sort_buffer内存排序，否则使用的磁盘文件排序
"sort_buffer_size": 200704, --排序缓存的大小
"sort_mode": "<sort_key, packed_additional_fields>" --排序方式，这里用的单路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */复制代码修改系统变量 max_length_for_sort_data (默认1024字节) ，employees 表所有字段长度总和肯定大于10字节
set max_length_for_sort_data = 10; select * from employees where name = 'ZhangSan' order by position;select * from information_schema.OPTIMIZER_TRACE;复制代码
trace排序部分结果:
"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": "<sort_key, rowid>" --排序方式，这里用饿的双路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
复制代码单路排序的详细过程：
- 从索引 name 找到第一个满足 name='ZhangSan' 条件的主键 id；
- 根据主键id取出整行，取出所有字段的值，存入sort_buffer中；
- 从索引name找到下一个满足 name='ZhangSan' 条件的主键 id;
- 重复步骤2、3直到不满足 name='ZhangSan'；
- 对 sort_buffer 中的数据按照字段 position 进行排序；
- 返回结果给客户端
双路排序的详细过程：
- 从索引 name 找到第一个满足 name='ZhangSan' 的主键id；
- 根据主键id取出整行，把排序字段 position 和 主键id 这两个字段放到 sort_buffer 中；
- 从索引 name 取下一个满足 name='ZhangSan' 记录的主键id；
- 重复步骤3、4直到不满足 name='ZhangSan'；
- 对 sort_buffer 中的字段 position 和 主键id按照 position 进行排序；
- 遍历排序好的 id 和 字段 position，按照 id 的值回到原表中取出所有的字段的值返回给客户端。
对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort_buffer 中，而双路排序只会把主键和需要排序的字段放到 sort_buffer 中进行排序，然后再通过主键回到原表查询需要的字段。
如果MySQL排序内存配置的比较小并且没有条件继续增加了，可以适当把 max_length_for_sort_data 配置小点，让优化器选择使用双路排序算法，可以在 sort_buffer 中一次排序更多的行，只是需要再根据主键回到原表取数据。
如果MySQL排序内存有条件可以配置比较大，可以适当增大 max_length_for_sort_data 的值，让优化器优先选择全字段排序（单路排序），把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果了。
所以，MySQL 通过 max_length_for_sort_data 这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率。
注意：如果全部使用sort_buffer 内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大 sort_buffer（默认1M），MySQL很多参数设置都做过优化的，不要轻易调整。
分页查询优化
在这我们先往 employess 插入一些测试数据
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();复制代码很多时候我们业务系统实现分页功能可能会用如下SQL实现
select * from employees limit 10000,10;复制代码
表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。
常见的分页场景优化技巧
- 根据自增且连续的主键排序的分页查询
- 根据非主键字段排序的分页查询
案例1: 根据自增且连续的主键排序的分页查询
首先来看一个根据自增且连续主键排序的分页查询的例子：
select * from employees limit 9000,5;复制代码
该 SQL 表示查询从第 9001开始的五行数据，没添加单独 order by，表示通过主键排序。我们再看表 employees ，因为主键是自增并且连续的，所以可以改写成按照主键去查询从第 9001开始的五行数据，如下:
select * from employees where id > 9000 limit 5;复制代码
查询结果是一致的，我们再对比一下执行计划：
EXPLAIN select * from employees limit 9000,5;复制代码
EXPLAIN select * from employees where id > 9000 limit 5;复制代码
显然改写后的 SQL 走了索引，而且扫描的行数大大减少，执行效率更高。 但是，这条改写的 SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致，如下图试验所示(先删除一条前面的记录，然后再测试原 SQL 和优化后的 SQL):
两条 SQL 的结果并不一样，因此，如果主键不连续，不能使用上面描述的优化方法。
另外如果原SQL是order by 非主键的字段，按照上面说饿的方法改写会导致两条SQL的结果不一致。所以这种改写得满足以下两个条件：
- 主键自增且连续
- 结果是按照主键排序的
案例2: 根据非主键字段排序的分页查询
再看一个根据非主键字段排序的分页查询，SQL 如下:
select * from employees ORDER BY name limit 9000,5;复制代码
EXPLAIN select * from employees ORDER BY name limit 90000,5;复制代码
发现并没有使用 name 字段的索引(key 字段对应的值为 null)，具体原因上前面讲过 : 扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。 知道不走索引的原因，那么怎么优化呢? 其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL 改写如下：
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;复制代码
需要的结果与原 SQL 一致，执行时间减少了一半以上，我们再对比优化前后sql的执行计划:
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 排序，而优化后的 SQL 使用的是索引排序。
Join关联查询优化
#示例表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 的表关联常见有两种算法
- Nested-Loop Join 算法
- Block Nested-Loop Join 算法
案例1：嵌套循环连接 Nested-Loop Join（NLJ）算法
一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。
EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;复制代码
从执行计划中可以看到这些信息:
- 驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql);优化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。
- 使用了 NLJ 算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。
上面SQL的大致流程如下：
- 从表 t2 中读取一行数据；
- 从第1步的数据中，取出关键字字段 a，到表 t1 中查找；
- 取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
- 重复上面 3 步。
整个过程会读取 t2 表的所有数据（扫描100行），然后遍历这每行数据中字段 a 的值，根据 t2 表中的 a 的值索引扫描 t1 表中对应的行（扫描 100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行）。因此整个过程扫描了 200 行。
如果被驱动表的关联字段没有索引，使用NLJ算法性能会比较低（下面有详细解释），MySQL 会选择 Block Nested-Loop Join 算法。
案例2：基于块的嵌套循环连接 Block Nested-Loop Join（BNL）算法
把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。
EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;复制代码
Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。
上面sql的大致流程如下:
- 把 t2 的所有数据放入到 join_buffer 中
- 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
- 返回满足 join 条件的数据
整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100 万次。
被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢?
如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。
很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNJ 的内存计算会快得多。
因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高。
对于关联SQL的优化
- 关联字段加索引，让mysql做join操作时尽量选择NLJ算法
-
小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用
straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间
straight_join解释
straight_join功能同join类似，但能让左边的表来驱动右边的表，能改变优化器对于联表查询的执行顺序。
比如 : select * from t2 straight_join t1 on t2.a = t1.a; 代表制定mysql选择 t2 表作为驱动表。
- straight_join只适用于inner join，并不适用于left join，right join。(因为left join，right join已经代表指 定了表的执行顺序)
- 尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因 为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。
in 和 exsits 优化
原则：小表驱动大表，即小的数据集驱动大的数据集。
in：当B表的数据集小于A表的数据集时，in优于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：当A表的数据集小于B表的数据集时，exists优于in
将主查询A的数据，放到子查询B中做条件验证，根据验证结果(true或false)来决定主查询的数据是否保留
select * from A where exists (select 1 from B whereB.id=A.id)
#等价于:for(select * from A){ select * from B where B.id = A.id
}
#A表与B表的ID字段应建立索引复制代码- EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会 忽略SELECT清单,因此没有区别；
- EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比；
- EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析；
Count(*) 查询优化
临时关闭mysql查询缓存，为了查看sql多次执行的真实时间。
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的执行计划一样，说明这四个sql执行效率应该差不多，区别在于根据某个字段count不会统计字段为null值的数据行。
为什么mysql最终选择辅助索引而不是主键聚集索引?
因为二级索引相对主键索引存储数据更少，检索性能应该更高
Les méthodes d'optimisation courantes sont les suivantes :
- Interroger le nombre total de lignes gérées par MySQL lui-même
- afficher l'état de la table
- Maintenir le nombre total dans Redis
- Augmenter la table de comptage
Interroger le nombre total de lignes maintenues par MySQL lui-même
Pour la table du moteur de stockage myisam, le les performances de la requête de comptage sans condition Where sont très bonnes. Élevées, car le nombre total de lignes dans la table du moteur de stockage myisam sera stocké sur le disque par MySQL et la requête n'a pas besoin d'être calculée.
Pour la table mysql du moteur de stockage innodb ne stocke pas le nombre total de lignes d'enregistrement dans la table, le le nombre de requêtes doit être calculé en temps réel.
afficher l'état de la table
Si vous avez seulement besoin de connaître l'estimation du nombre total de lignes dans la table, vous pouvez utiliser la requête SQL suivante, les performances sont très élevées
Conserver le nombre total dans Redis
Lors de l'insertion ou de la suppression de lignes de données de table, conservez la valeur de comptage du nombre total de lignes de table dans Redis en même temps (utilisez la commande incr ou decr), mais cette méthode peut ne pas être précise et il est difficile de garantir la cohérence des transactions entre les opérations de table et les opérations redis.
Ajouter une table de comptage
Lors de l'insertion ou de la suppression de lignes de données de table, conservez la table de comptage en même temps, afin qu'elles puissent opérer dans la même transaction.
Si vous souhaitez en savoir plus sur la programmation, faites attention à la rubrique Formation php !
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!
Articles Liés
Voir plus- Introduction détaillée à la sous-requête d'optimisation des performances Mysql
- Le partage d'expérience le plus complet sur l'optimisation des performances MySQL
- Une certaine expérience dans l'optimisation des performances MySQL
- Résoudre les problèmes d'optimisation des performances de MySQL
- Pratique (1) - Optimisation des performances MySQL