MYSQL_03Mysql索引优化实战二
2023-04-11 16:45:15 14 举报AI智能生成
在MYSQL_03Mysql索引优化实战二中，我们将深入探讨如何通过创建和使用索引来提高数据库查询性能。我们将详细介绍不同类型的索引，如主键、唯一键、全文索引等，并解释它们在不同场景下的适用性。此外，我们还将讨论如何评估索引的有效性，以及如何通过分析和调整查询语句来进一步提高性能。在这个实战中，您将学习到如何充分利用MySQL的索引功能，为您的数据库应用带来显著的性能提升。
作者其他创作
大纲/内容
分页场景优化技巧
根据自增且连续的主键排序的分页查询
原来sql 语句
EXPLAIN select * from employees limit 90000,5;
优化sql语句
EXPLAIN select * from employees where id > 90000 limit 5;
使用了索引，扫描行数减少，效率提高
问题点
很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致
如果主键不连续，不能使用上面描述的优化方法
order by 非主键的字段，按照上面说的方法改写会导致两条 SQL 的结果不一致
适用的场景
主键自增且连续
结果是按照主键排序的
根据非主键字段排序的分页查询
原来sql 语句
EXPLAIN select * from employees ORDER BY name limit 90000,5；
优化sql语句
让排序时返回的字段尽可能少
可以让排序和分页操作先查出主键，然后根据主键查到对应的记录
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
Join关联查询优化
mysql的表关联算法
Nested-Loop Join 算法
一次一行循环地从第一张表（称为驱动表）中读取行
根据关联字段在另一张表（被驱动表）里取出满足条件的行
然后取出两张表的结果合集
例子
EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;
驱动表是 t2，被驱动表是 t1
优化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表
当使用left join时，左表是驱动表，右表是被驱动表
当使用right join时，右表时驱动表，左表是被驱动表
当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表
Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ
Block Nested-Loop Join 算法
把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比
Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法
优化思路
NLJ 算法是磁盘扫描，BNL是内存计算
MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法
MySQL对于被驱动表的关联字段有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高
关联字段加索引，让mysql做join操作时尽量选择NLJ算法
小表驱动大表
多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式
straight_join
功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序
straight_join只适用于inner join，并不适用于left join，right join
尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的
straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱
小表定义
两个表按照各自的条件过滤，过滤完成之后，计算参与 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
}
for(select id from B){
select * from A where A.id = B.id
}
exists
当A表的数据集小于B表的数据集时，exists优于in
将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留
例子
elect * 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字段应建立索引
for(select * from A){
select * from B where B.id = A.id
}
#A表与B表的ID字段应建立索引
EXISTS (subquery)只返回TRUE或FALSE
因此子查询中的SELECT * 也可以用SELECT 1替换
EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析
count(*)查询优化
例子
EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;
字段有索引
count(*)≈count(1)>count(字段)>count(主键 id)
count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id)
字段无索引
count(*)≈count(1)>count(主键 id)>count(字段)
count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)
count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，
count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点。
count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点。
对于count(id)，mysql最终选择辅助索引
因为二级索引相对主键索引存储数据更少，检索性能应该更高
在5.7版本才优化
0 条评论
下一页
