mysql中count关联查询优化_64MySQL-分页查询&表连接&count统计&索引优化总结

深藏阁楼爱情的钟 2023-01-11 09:28 361阅读 0赞

1 Mysql 分页查询sql 执行原理？

2，千万级数据mysql 分页查询如何优化

3，Mysql表连接底层实现原理

4，nested\_Loop Join(NLJ)与Block Nested-Loop(BNL)连表算法的区别

5，in/exist/count(\*)count(1) count(列名)查询优化

6，EXPLAIN 分析sql语句 type 最低满足什么级别

7，为什么阿里巴巴官方手册不推荐使用存储过程。

8，阿里官方手册mysql 索引优化总结》

分页查询优化：

select \* from employees limit 10000,10;

select \* from employees limit 100000,10;

select \* from employees limit 1000000,10;

select \* from employees limit 3000000,10;

![a0eb4863cc09][]

image.png

从表中只查询10条数据，实际上底层读取了300000条数据，然后读取最后10条

这种分页查询采用全表扫描的方式，查询效率极低。

1，根据主键且连续的主键排序的分页查询：

EXPLAIN select \* from employees where id>3000003 limit 10;

原理： 根据主键索引id排除< 3000003 ，取后10条数据避免全表扫描

缺点： 如果主键id不连续 ，可能无法实现效果。

2，非主键索引方式分页优化：

select \* from employees order by id limit 100000,10;

SELECT \* from employees e inner join (

select id from employees a order by a.id limit 100000,10 ) ed on e.id=ed.id

使用复合索引嵌套子查询，效率可以提高一半。

阿里巴巴手册：

![a0eb4863cc09][]

image.png

数据量超过500万或者大于2g的时候建议分表

count(\*) 优化：

临时关闭mysql 的查询缓存，为了查看sql多次执行的真实时间。

set global query\_cache\_size=0;

set global query\_cache\_type=0;

1,count(field) 不包含字段值为null 的值；

2，count() 包含字段为null 的值；

3，select() 与select(1) 在InnnDB 中性能没有任何区别，处理方式相同。

5.6版本：

1, select count(1) from employees; 使用辅助联合索引计数

2， select count(id) from employees; 使用辅助索引计数

3， select count(name) from employees; 使用辅助索引计数

4， select count() from employees; 使用辅助索引计数

5， select count(id) from employees force index (PRIMARY) 使用主键索引；

从效率上看： count(1) ==count()>count(name)>count(id)

因为主键索引的id 对应的叶子节点中存放data 数据，加载内存中计数的时候比较慢。 推荐使用 count(\*)；

在mysql 5.7 count(\*) 会选择聚集索引，进行一次内部handler函数调用，即可快速获得该表总数，执行计划Extra 显示 select tables optimized away SELECT 操作已经优化到不能再优化

如果聚集索引比较大(或者说表数据量比较大)。没有完全加载到buffer pool 中的话,MYSQL5.7的查询方式有可能反而更慢， 还不如原先放方式 MYSQL5.6

EXPLAIN select count(1) from employees;

EXPLAIN select count(id) from employees;

EXPLAIN select count(name) from employees;

EXPLAIN select count(\*) from employees;

EXPLAIN select count(id) from employees force index(PRIMARY) 强制使用聚集索引

聚集索引和非聚集索引的区别：

聚集索引： 就是主键id 非聚集索引就是自定义的userName 字段

其中聚集索引 clustered index(id) , 非聚集索引index (UserName)

InnoDB 表中在没有默认主键的情况下会生成一个6 byte 空间的自增长主键，可以用

select \_rowid from table 查询的是对应的主键值 select \_rowid from employees

常见的优化方案：

1，对于 myisam 存储引擎的表做不带where 条件的count 查询性能是很高的，因为mysiam 存储的表总行会被mysql 存储在磁盘上，查询不需要操作

2 show table status

3, 使用Redis set key 记录表总数。

Join 关联表查询

EXPLAIN select \* from mayikt\_1 left join mayikt\_2 on mayikt\_1.t1= mayikt\_2.t1;

EXPLAIN select \* from mayikt\_2 left join mayikt\_1 on mayikt\_1.t1= mayikt\_2.t1;

EXPLAIN select \* from mayikt\_2 right join mayikt\_1 on mayikt\_1.t1= mayikt\_2.t1;

Nested-Loop Join(NLJ) 嵌套循环连接 算法

Block Nested-Loop Join (BNL) 基于块的嵌套循环连接 算法

Nested-Loop Join 嵌套循环连接 算法(主键关联查询)

EXPLAIN select \* from mayikt\_1 inner join mayikt\_2 on mayikt\_1.t1= mayikt\_2.t1;

![a0eb4863cc09][]

image.png

全表扫描查询到mayikt\_2 表中索引的数据，在根据 mayikt2 中的t1 索引字段数据查询mayikt1 索引字段中的数据，总共扫描2000行

驱动表 mayikt2(小表)被驱动表： mayikt\_1(大表)

优化器一般会选择小表做驱动表，所以使用 inner join 时 ，排在后面的表并不一定

Block Nested-Loop Join 基于块的嵌套循环连接 算法(非索引关联查询)

关联查询使用的是 BNL 算法：

EXPLAIN select \* from mayikt\_2 straight\_join mayikt\_1 on mayikt\_1.t2= mayikt\_2.t2;

![a0eb4863cc09][]

image.png

原理：

1.将mayikt2表中所有的数据放入到join\_buffer 中

2.在读取mayikt1表中每行数据与join\_buffer中数据实现匹配关联

3.最后在返回查询的数据

1000\*32869次 查询效率极低。

有索引是情况下 NLJ 算法比BNL 算法性能要高；

对于关联sql的优化：

1， 超过三个表禁止join, 需要join 的字段 ，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引，尽量使用NLJ 算法；

2， 小表驱动大表 ，写多表连接sql 时如果明确知道那张表是小表可以用 straight\_join 写法固定方式 ，省去mysql 优化器自己判断时间。

EXPLAIN select \* from mayikt\_2 straight\_join mayikt\_1 on mayikt\_1.t2= mayikt\_2.t2;

in 和exist 优化：

也是需要遵循原则；

原则： 小表驱动大表，即小的数据集驱动大的数据集

如果 mayikt\_b 中的数据小于mayikt\_a 的数据可以使用in

select \* from mayikt\_a where id in (select id from mayikt\_b)

底层类似于这样实现

for(select id from mayikt\_B)\{

select \* from mayikt\_a where mayikt\_a.id = mayikt\_b.id

如果mayikt\_a中的数据小于mayikt\_b数据可以使用exists

select \* from mayikt\_a where exists (select id from mayikt\_b where mayikt\_b.id = mayikt\_a.id)

for(select \* from mayikt\_a)\{

select \* from mayikt\_a where mayikt\_b.id = mayikt\_a.id

1 mysql 索引优化原则：

2 核心点： B+树，优化原则： 常见方式，分页，排序，连表， count(\*)

3 MYSQL 索引底层采用B+树； 减少磁盘IO操作，支持高效的范围查询：

4 优化原则：

核心： 先定位慢查询，在通过慢查询日志文件 分析sql 语句；

分析sql 语句工具： ExPLAIN/trace 工具

EXPLAIN type 满足级别： type最低 满足 range 范围查询级别

防止索引失效，避免全表扫描；

5， 优化方案：

常见方案：

常见方式，分页，排序，连表，count(\*)

1，必须遵循最佳左前缀原则 防止索引失效

2，尽量使用覆盖索引 查询列都是加上索引 ，减少select \*

3，is null ，is not null 索引会失效，空值用专门特定的常量值定义； dfe

4， like 模糊查询遵循最佳左前缀原则 ，使用复合索引模糊查询

5，排序相关： 最佳左前缀原则，避免 filesort

6, 分页查询 ， 根据 where id 条件过滤 offset 或者使用子查询先定位id， 在查询效率可以提高一半，但是如果数据量大于500万的情况下建议使用分表。

7，连表查询 ： 小表驱动大表数据避免全表扫描，超过三张表禁止使用join

8，count(\*) 查询优化 ，辅助索引count 比主键索引count

效率高； mysql5.7 主键id count mysql5.6 辅助索引 count

[a0eb4863cc09]: /images/20221119/2ccf0eb89bf64468b26e195d828c5453.png

mysql中count关联查询优化_64MySQL-分页查询&表连接&count统计&索引优化总结

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关优化mysql分页查询

相关 MySQL分页查询优化

相关 mysql 大分页查询优化_Mysql优化大分页查询

相关 mysql中count关联查询优化_64MySQL-分页查询&表连接&count统计&索引优化总结

相关 mysql分页查询优化

相关 MySQL count(*)、count(1) 和count(字段)的区别以及count()查询优化手段

相关 Mysql（五）索引优化常见的分页场景优化技巧 Join关联查询优化 in和exsits优化 count(*)查询优化

相关优化limit、count查询

相关 MySQL 利用延迟关联优化分页查询

相关 MySQL分页查询优化