MySQL高级 SQL优化【limit&count&update优化】

以你之姓@ 2023-10-03 23:02 20阅读 0赞

**目录**

1：SQL优化

1.1：limit优化

1.2：count优化

1.2.1：概述

1.2.2：count用法

1.3：update优化

--------------------

## 1：SQL优化 ##

### 1.1：limit优化 ###

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。 我们一起来看看执行limit分页查询耗时对比：

![fb738b103c92449a91b8c983715a4cf6.png][]

通过测试我们会看到，越往后，分页查询效率越低，这就是分页查询的问题所在。

因为，当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记 录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

优化思路: **一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查 询形式进行优化。**

explain select * from tb_sku t , (select id from tb_sku order by id
    limit 2000000,10) a where t.id = a.id;

### 1.2：count优化 ###

#### 1.2.1：概述 ####

select count(*) from tb_user ;

在之前的测试中，我们发现，如果数据量很大，在执行count操作时，是非常耗时的。

1：**MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(\*) 的时候会直接返回这个 数，效率很高；** 但是如果是带条件的count，MyISAM也慢。

2：**InnoDB 引擎就麻烦了，它执行 count(\*) 的时候，需要把数据一行一行地从引擎里面读出 来，然后累积计数。**

如果说要大幅度提升InnoDB表的count效率，主要的优化思路：**自己计数(可以借助于redis这样的数 据库进行,但是如果是带条件的count又比较麻烦了)。**

#### 1.2.2：count用法 ####

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加，最后返回累计值。

用法：count（\*）、count（主键）、count（字段）、count（数字）

**count(主 键)：**InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。 服务层拿到主键后，直接按行进行累加(主键不可能为null)

count(字 段)：

没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出 来，返回给服务层，服务层判断是否为null，不为null，计数累加。

有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返 回给服务层，直接按行进行累加。

**count(数 字)：**InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1” 进去，直接按行进行累加。

**count(\*)：**InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接 按行进行累加。

**按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(\*)，所以尽 量使用 count(\*)。**

### 1.3：update优化 ###

我们主要需要注意一下update语句执行时的注意事项。

update course set name = 'javaEE' where id = 1 ;

当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。

但是当我们在执行如下SQL时。

update course set name = 'SpringBoot' where name = 'PHP' ;

> InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁 升级为表锁 。

[fb738b103c92449a91b8c983715a4cf6.png]: https://img-blog.csdnimg.cn/fb738b103c92449a91b8c983715a4cf6.png

发表评论取消回复

表情：

评论列表（有 0 条评论，20人围观）

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

相关阅读

相关 MySQL高级 SQL优化【limit&count&update优化】

目录 1：SQL优化 1.1：limit优化 1.2：count优化 1.2.1：概述 1.2.2：count用法 1.3：update优化 ----------

以你之姓@/ 2023年10月03日 23:02/ 0 赞/ 21 阅读

相关 MySQL高级 SQL优化【order by&group by优化】

目录 1：SQL优化 1.1：order by优化 A. 数据准备 B. 执行排序SQL C. 创建索引 D. 创建索引后，根据age, phone进行升序排序

谁践踏了优雅/ 2023年10月03日 22:58/ 0 赞/ 25 阅读

相关 MySQL高级 SQL优化【插入数据&主键优化】

目录 1：SQL优化 1.1：插入数据 1.1.1：insert 1). 优化方案一（批量插入数据) 2). 优化方案二（手动控制事务） 3). 优化方案三（主键

清疚/ 2023年10月03日 22:54/ 0 赞/ 18 阅读

相关 MySQL单机优化---SQL优化

SQL优化（变多次维护为一次维护）　　Sql优化分为：DDL、DML、DQL 　　一、DDL优化　　　　1 、通过禁用索引来提供导入数据性能。这个操作主要针对有数

阳光穿透心脏的1/2处/ 2023年08月17日 17:49/ 0 赞/ 126 阅读

相关 mysql sql优化建议_sql优化建议

1、查询SQL尽量不要使用select \，而是select具体字段。反例子： select \ from employee; 正例子： select id，name

缺乏、安全感/ 2022年10月28日 11:28/ 0 赞/ 161 阅读

相关 MySQL高级性能优化---优化分析

1. sql性能下降的表现执行时间长等待时间长 2. 性能下降的原因 1. 查询语句写的不好各种连接，各种子查询导致用不上索引或者

超、凢脫俗/ 2021年12月11日 06:55/ 0 赞/ 471 阅读

相关 MySQL高级性能优化---sql的执行顺序

1. 代码编写顺序 1. select 查询字段 2. from 表名 3. JOIN 表名 4. ON 连接条件 5. where 查询条件 6. grou

分手后的思念是犯贱/ 2021年12月11日 06:13/ 0 赞/ 247 阅读

相关 MySQL高级性能优化---limit优化

当表中存在大批量数据时分页如何优化？我们先创建一张employee表，使用存储过程或者函数创建1000万条数据。 limit查询分析使用limit随着offs

逃离我推掉我的手/ 2021年12月10日 14:37/ 0 赞/ 478 阅读

相关【MYSQL高级】索引优化

1.先来学习索引的一些语法：展示索引 show index from 表名; 创建索引 create ind

待我称王封你为后i/ 2021年10月03日 02:04/ 0 赞/ 561 阅读

相关 Mysql优化之高级

一、exists和in - - - 小表驱动大表前言： 500\10000和10000\500，在数学角度来说是没什么区别的，从java角度来说是这样的： f

小灰灰/ 2021年05月12日 11:55/ 0 赞/ 566 阅读