工作中遇到的一些数据库问题记录（随时更新）

1. delete where is null + JSON + 无索引

问题语句：

1

delete from commodity_feature where source->>"$.shopId" is null;

表现： 数据库19W条数据，where条件符合790+条，多表级联删除（commodity_feature有几个cascade关联表），耗时几十分钟仍未结束
原因分析：
1. JSON字段没有索引，触发全表扫描。
2. IS NULL应该没有问题，MySQL会执行优化，但是也需要特别关注。
MySQL is null 优化
3. 级联删除会查找关联表中需删除数据，本来就慢。
解决办法：
1. 给JSON字段建立索引

1
2

alter table commodity_feature add column shopId char(36) generated always as (json_unquote(json_extract(source,'$.shopId'))) VIRTUAL;
create index `shopId_index` on commodity_feature(`shopId`) using btree;

1. 针对级联删除耗时，将commodity_feature分片多次执行删除。

2. delete join 不能添加limit限制

问题语句：

1
2
3
4
5
6

delete a from commodity_feature a
  left join (select cid, source->>"$.shopId" as shopId, count(*) as num from commodity_feature group by cid, shopId) b
  on (a.cid = b.cid and a.source->>"$.shopId"=b.shopId)
  where mc<=0 and createdAt<"2021-12-06"
  and b.num > 500
  limit 10;

表现： 报错

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘limit 10’ at line 1
原因分析：就是对于多表Join删除时，delete不支持limit语法。
For the multiple-table syntax, DELETE deletes from each tbl_name the rows that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used.
解决办法：
1. 先将待删除ID查出来，然后第二个语句删除。
2. 仔细设计表结构和语句，争取不用limit实现删除目标。

3. delete where exists 子查询，等效于delete join, 也不能加limit

问题语句:

1
2
3
4
5
6
7

delete a from commodity_feature a
  where a.createdAt < "2021-12-06" and a.mc<=0
  and exists (
    select cid from (
      select cid, source->>"$.shopId" as shopId, count(*) as num from commodity_feature group by cid, shopId
    ) b where a.cid=b.cid and a.source->>"$.shopId"=b.shopId and b.num >= 500
  ) limit 10;

原因和解决办法与delete join 一样。

4. delete where 子查询，不能是同一张表，否则报错

问题语句:

1

delete from commodity_feature where id in (select id from commodity_feature where id > 30);

表现：

[Err] 1093 - You can’t specify target table ‘commodity_feature’ for update in FROM clause
原因分析：
1. delete子查询不允许是同一张表
MySQL delete 文档
2. 之所以不直接用delete where的原因是: 若条件不是主键，在级联删除时mysql会先查找待删除数据缓存起来，这时候若表中存在超大字段，可能会导致数据库内存耗尽宕机。

解决办法： 子查询再包一层。

1

delete from commodity_feature where id in (select * from (select id from commodity_feature where id > 30)b);

5. delete where in 问题

问题语句:

1

delete from commodity_feature where id in (select * from (select id from commodity_feature where source->>"$.shopId" is null)b) limit 10;

表现： 当子查询结果数量小于limit时，触发全表扫描
原因分析：
1. mysql limit使用不当可能导致全表扫描。当limit数超过实际符合条件的数据量时会触发全表扫描，不仅是delete, selete也会(可能部分版本会这样)。
看这个例子: mysql的limit使用不当导致了全表扫描
解决办法：
1. 尽量不用limit, 善用合适的where条件获取或删除数据。
2. 可以先查询出待删除数据的量，再设置合适的limit删除。

6. mysql5.7中子查询中的order by失效

问题语句: (该记录没有记录完整，以后补充)

原因分析：
A “table” (and subquery in the FROM clause too) is - according to the SQL standard - an unordered set of rows. Rows in a table (or in a subquery in the FROM clause) do not come in any specific order. That’s why the optimizer can ignore the ORDER BY clause that you have specified. In fact, SQL standard does not even allow the ORDER BY clause to appear in this subquery (we allow it, because ORDER BY … LIMIT … changes the result, the set of rows, not only their order). You need to treat the subquery in the FROM clause, as a set of rows in some unspecified and undefined order, and put the ORDER BY on the top-level SELECT。
解决办法：
1. 子查询 order by 添加limit