update

三个高频单点优化：深分页用覆盖索引 + 子查询，计数用 count(*)，更新必须走索引以免行锁升级为表锁。

一、limit 优化

深分页的典型问题：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;

先在覆盖索引（这里是主键）上完成分页、只取出 10 个 id，再靠这 10 个 id 回表取整行，避免对两百万行做回表与排序。

二、count 优化

explain select count(*) from tb_user;

引擎	`count(*)` 的执行方式
MyISAM	把一个表的总行数存在磁盘上，执行时直接返回这个数，效率很高
InnoDB	需要把数据一行一行地从引擎里面读出来，然后累积计数，比较麻烦

优化思路：自己计数（用 Redis 等外部存储维护计数，或维护计数表）。

2.1 count 的几种用法

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

用法：count(*)、count(主键)、count(字段)、count(1)。

用法	InnoDB 的执行过程
`count(主键)`	遍历整张表，把每一行的主键 id 值都取出来返回给服务层；服务层拿到主键后直接按行累加（主键不可能为 null）
`count(字段)`，无 `not null` 约束	遍历整张表把每一行的字段值都取出来返回给服务层；服务层判断是否为 null，不为 null 才累加
`count(字段)`，有 `not null` 约束	遍历整张表把每一行的字段值取出来返回给服务层，直接按行累加
`count(1)`	遍历整张表但不取值；服务层对返回的每一行放一个数字 “1” 进去，直接按行累加
`count(*)`	遍历整张表但不取值，InnoDB 专门做了优化；服务层直接按行累加

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

三、update 优化

-- where 条件是主键：走主键索引，加行锁
update student set no = '2000100100' where id = 1;

-- where 条件是 name（无索引）：无法定位索引 → 表锁
update student set no = '2000100105' where name = '韦一笑';

InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁，并发更新时阻塞其他事务。

速查

-- limit 深分页：覆盖索引 + 子查询
explain select * from tb_sku t,
       (select id from tb_sku order by id limit 2000000,10) a
where t.id = a.id;

-- count：优先 count(*)；效率 count(字段) < count(主键 id) < count(1) ≈ count(*)
explain select count(*) from tb_user;
-- 优化思路：自己计数（Redis / 计数表）

-- update：where 必须走索引，否则行锁升级为表锁
update student set no = '2000100100' where id = 1;          -- 主键索引 → 行锁
update student set no = '2000100105' where name = '韦一笑';  -- name 无索引 → 表锁

优化点	一句话结论
limit 深分页	先按覆盖索引取 `id` 完成分页，再回表取整行，避免丢弃前面的大量记录
count	InnoDB 只能逐行累积计数；写 `count(*)`，或改用外部计数
update	`where` 走主键 / 索引，否则行锁升级为表锁，阻塞并发更新

SQL 优化 - limit / count / update

一、limit 优化

二、count 优化

2.1 count 的几种用法

三、update 优化

速查

相关