mysql 或者 mariadb 能不能限制某条语句的资源消耗？

[shendaowu]

我想做的一个网站的某类 SQL 语句可能会很消耗资源，或者说执行很长时间。为了防止这些执行这类语句的时候拖慢整个系统，导致网站访问卡顿，能不能限制这类语句的资源消耗？

[Red998]

根源问题就是慢 sql 呗、监控出来 解决不就好了

[iseki]

可以限制请求消耗的内存和时间呢

[aijam]

https://dev.mysql.com/blog-archive/server-side-select-statement-timeouts/

[shendaowu]

@redorblacck886
我估计是没法优化。

@iseki
能限制 IO 吗？另外限制内存不会跟限制时间一样都是到限制之后都停止执行吧？我的意思是类似让它优先级低一点，但是能执行完。

@aijam
我的意思是类似让它优先级低一点，但是能执行完。

[seers]

上缓存呗，redis

[Features]

你做个 slave ，然后这个 SQL 语句只在这个 slave 上面执行就好了
成本很低

[h0099]

可以基于#6 所说的单独开个 mysqld 进程作为只读 replicate 服务端
然后修改`innodb_buffer_pool_size`（ https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size ）和`innodb_redo_log_capacity`/`innodb_log_file_size`以约束进程常驻占用内存量： https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html#innodb-modifying-redo-log-capacity
根据每个 sql 的特点可能会产生巨大的临时表（ https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html ），您可以把默认 1G 的`temptable_max_ram`也调低 https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_temptable_max_ram
限制进程硬盘 io： https://unix.stackexchange.com/questions/48138/how-to-throttle-per-process-i-o-to-a-max-limit
限制进程 cpu 使用率： https://manpages.ubuntu.com/manpages/trusty/man1/cpulimit.1.html

[shendaowu]

@Features slave 是在另外一台服务器上是吧？

[Features]

@shendaowu 是的

[lululau]

标准做法不是搞个异步消息处理吗，控制下消息消费的并发数

[h0099]

#8 @shendaowu 通过控制进程的资源用量使得您也可以在一个系统上跑两个 mysqld
#10 @lululau 很明显他只是装个现有的程序来跑所以无法修改其内部结构 https://www.v2ex.com/t/908246

[shendaowu]

@lululau 一条 SQL 语句可能就会占用大量的硬盘 IO 等资源，然后在执行这条语句的时候可能正常的处理网页显示的代码读取数据库就会变慢了。类似消息处理的东西我是打算做的，我之前还计划把这些耗时的操作全都延迟到下半夜没人访问的时候执行。然后怕流失用户所以想分三档，不太消耗资源的这类 SQL 语句立即同步执行，中等的异步限制资源执行，太消耗资源的就只能下半夜不限制资源执行了。

[h0099]

#11 @shendaowu 既然阁下能够修改您要跑的程序的源码，那为什么不打开 https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html 看看哪些 sql 超级耗时然后 EXPLAIN https://dev.mysql.com/doc/refman/8.0/en/using-explain.html 他看看如何针对这个 sql 优化？
当然这样一个个去 tuning 可能太慢了
最省事也不需要改数据库表结构的方法就是#6 @Features 所说的单独开 replicate mysqld 从而实现您的`分三档，不太消耗资源的这类 SQL 语句立即同步执行，中等的异步限制资源执行，太消耗资源的就只能下半夜不限制资源执行`
但这仍然知道到底哪些 sql 查起来耗时，然后修改源码让这些 sql 去优先级低被各种限制使用系统资源了的 replicate mysqld 上执行

[shendaowu]

#13 @h0099 我说的那些消耗资源的 SQL 语句基本类似标签搜索，但是每次搜索会搜索哪些对象具有给定的很多个的标签，多的时候可能会有几十个标签吧。另外按标签匹配的个数进行排序。我用的基本上就是这个问题题主采纳的回答里的语句： https://stackoverflow.com/questions/8762333/multiple-tags-search-query 。不知道你觉得这种查询是否能优化。我还没学数据库优化。我之前生成一些随机的数据进行测试好像是搜一百个标签的时候在我的 SSD 电脑上搜索时间达到秒级了。好像还建索引了。另外我的那些随机数据应该是有一些问题的，跟实际的数据很可能有区别，不知道实际数据是会更快还是更慢。

[h0099]

让我康康您的
- 表结构（ SHOW CREATE TABLE ）
- 执行的 SQL
- `EXPLAIN SQL`结果
不然您说这么多也都是模糊的

[h0099]

另外对于搜索标签这种类似 https://www.v2ex.com/t/900089 的场景
我合理怀疑您现在是直接把每个 item 所具有的 tags 直接序列化后存在 item record 的某个 field 中
就好比 item1 有 3 个 tags：a 和 b 和 c ，那么您直接把这仨 abc 拼在一起变成`a,b,c`然后作为这个 item 在数据库行（ record ）中的一个列（ field ）的值

从关系代数的角度看，这是违反 1NF https://en.wikipedia.org/wiki/First_normal_form 的，因为您没有将符合一对多关系的 item->tags 关系给拆出单独的 item-tag 中间表

而为了 1NF 您需要建立 `表述所有可能存在的 tag 表` 和 `表述所有 item 与 tag 的一对多关系的表`
前者我假设您已经有了

后者就是一个只有两个字段的表：itemId 和 tagId ，同时 UNIQUE 约束建立在这两个字段上（因为对于单个 item 不应该具有多个完全相同的 tags ，除非这也是您的需求）
把关系数据填充进这个表之后
想要查询某个 item 有哪些 tag 只需要`SELECT tagId FROM 这个表 WHERE itemId = 您要查的 item`
想要查询某个 tag 下有哪些 item 只需要`SELECT itemId FROM 这个表 WHERE tagId = 您要查的 tag`

[shendaowu]

#16 @h0099
我试了一下，关系表一千万条查询一百个左右 tag 需要两秒多。一百万是不到 100 毫秒。不过我发现好像一千万的记录如果 tag 小于七个能在不到一毫秒的时间内完成。而从八个到一百个左右时间基本都是两秒左右。还有个奇怪的地方，我某次生成了测试数据之后搜索一百个左右的 tag 好像只用了不到一毫秒。不知道是怎么回事。我弄了好几次也没成功复现，我都有点怀疑是我 explain 忘了删才不到一毫秒的。或者可能是 MySQL 或者 SSD 的缓存的问题？

不知道到千万的级别后是分表更好还是用我这贴说的限制资源的方式更好。之前我考虑过按使用目的进行分表，让用户依据使用的目的选择项目放到哪个表中。

让你回复了这么多很不好意思。你回这么多图的是什么？这些明显属于个人咨询了，你回的这些东西好像很难帮到除了我以外的人吧？

表结构之类的东西： https://pastebin.com/Kin9UkXg 。太长了，直接发我嫌浪费积分。之前问朋友朋友说如果达到千万级别就不用我优化了。不过现在互联网这么不景气，感觉悲观一些也不错。你感觉麻烦的的话就不要回复了。

[h0099]

> 让你回复了这么多很不好意思。你回这么多图的是什么？这些明显属于个人咨询了，你回的这些东西好像很难帮到除了我以外的人吧？

那阁下来 v2 问您的特定于您的表结构场景的问题又什么什么目的？

> 之前问朋友朋友说如果达到千万级别就不用我优化了

经典提前优化与钞能力

[h0099]

```
tbm> CALL add_tag(1000)
[2023-01-13 19:51:43] 100 rows affected in 842 ms
tbm> CALL add_content(1000)
[2023-01-13 19:51:44] 100 rows affected in 875 ms
tbm> CALL add_tag_content_rel(100000, 100000, 100000)
[2023-01-13 19:54:15] 100 rows affected in 2 m 30 s 141 ms
tbm> USE tag_test
[2023-01-13 19:54:17] completed in 356 ms
tag_test> SELECT *
FROM tag_content_rel
WHERE content_id = 50000
[2023-01-13 19:54:25] 85 rows retrieved starting from 1 in 6 s 972 ms (execution: 6 s 700 ms, fetching: 272 ms)
tag_test> USE tag_test
[2023-01-13 19:54:26] completed in 572 ms
tag_test> SELECT content_id, COUNT(*)
FROM tag_content_rel
WHERE tag_id IN (730,2621,2805,3200,3340,3590,3969,4039,4799,5249,8859,11894,12628,12646,16959,17024,17142,18032,18861,19316,20839,21179,22346,22507,22522,22639,23562,23822,25172,25786,25821,26606,29899,29917,30586,30901,31216,31413,32562,32567,34740,36586,36954,38109,39202,40519,40756,40816,41464,42942,43069,43286,43344,44787,44950,45549,45652,46313,47111,50549,51942,52738,52959,52961,54034,55526,59162,59767,59945,60361,60816,61307,61730,62269,62503,62589,63960,64580,64634,64794,65209,66332,68222,69396,69905,70629,70939,71277,71804,72580,72896,73651,74301,74525,74706,75153,76169,76500,78042,78148,79109,81463,82140,84217,85212,85327,85584,86392,86908,88188,88475,89175,89190,91156,93202,94124,95294,95345,96013,98135,99679)
GROUP BY content_id
ORDER BY COUNT(*) DESC
[2023-01-13 19:54:28] 500 rows retrieved starting from 1 in 555 ms (execution: 402 ms, fetching: 153 ms)
```

[h0099]

OPTIMIZE 个 TABLE 先



所以阁下想优化`SELECT * FROM tag_content_rel WHERE content_id = 50000`？还是什么？

[h0099]

```sql
CREATE TABLE tag_content_rel(
tag_id INT NOT NULL,
content_id INT NOT NULL);

CREATE INDEX tag_content_rel_index ON tag_content_rel(tag_id, content_id);
```

您为什么不用自带 unique 约束的 primary key ？还是说您的需求就是允许一个 content 有着多个完全重复的 tag ？
您设置为普通 index 就意味着他是 secondary index ，而这个表无 PK 也无 UK 就意味着 innodb 只能自动生成一个隐式的自增 id 字段作为主键，这被关系代数学家称作 https://en.wikipedia.org/wiki/Surrogate_key： https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html
> If a table has no PRIMARY KEY or suitable UNIQUE index, InnoDB generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column that contains row ID values. The rows are ordered by the row ID that InnoDB assigns. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in order of insertion.

> 可能是 MySQL 或者 SSD 的缓存的问题
考虑到您都不知道`innodb_buffer_pool_size` https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool.html：
建议深入学习贯彻 RDBMS 供应商无关的关系代数理论知识： https://use-the-index-luke.com 然后再结合 innodb 本身的实现进行思考

[h0099]

```sql
ALTER TABLE `tag_test`.`tag_content_rel` DROP INDEX `tag_content_rel_index`, ADD PRIMARY KEY (`tag_id`, `content_id`) USING BTREE
```
#1062 - Duplicate entry '87582-57377' for key 'tag_content_rel.PRIMARY'

真就 `需求就是允许一个 content 有着多个完全重复的 tag` 呗（当然我知道有重复的`tag_id,content_id`对是因为您的存储结构是直接生成随机数值来 INSERT 的而又没有去重）

```sql
CREATE TABLE IF NOT EXISTS tag_content_rel_uniq
(
tag_id INT NOT NULL,
content_id INT NOT NULL,
PRIMARY KEY (tag_id, content_id)
);
INSERT IGNORE INTO tag_content_rel_uniq(tag_id, content_id) SELECT * FROM tag_content_rel;
OPTIMIZE TABLE tag_content_rel_uniq;
SELECT COUNT(*) FROM tag_content_rel_uniq;
SELECT * FROM tag_content_rel_uniq WHERE content_id = 50000;
```
耗时还是差不多

[h0099]

而 uniq 表比原表小的多，这就是因为不再需要那个 `隐式的自增 id 字段作为主键`



回到`SELECT * FROM tag_content_rel WHERE content_id = 50000`本身，他的 EXPLAIN 是


Using index for skip scan 意味着查询计划是将 key （这里是 PK ）用于帮助加快 full table scan 的速度，因为根据 key 中的信息可以知道有些行可以直接跳过（所以叫 skip scan ），但这并不能改变查询计划仍然是在做 full table scan 的罪恶本质： https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html
而只能 full table scan 的根本原因是这个 sql 的 where 子句只有对字段 content_id 的约束，而 content_id 不在任何索引的最左字段（第一个）之中，因为您的 PK 是(tag_id, content_id)而不是(content_id, tag_id)

所以在
ALTER TABLE `tag_content_rel_uniq` ADD INDEX(`content_id`)
之后

立省 50%
EXPLAIN 显示这下的确是直接使用了新建的 content_id 索引，所以不再需要任何形式的 full table scan 了：


但代价是这个新索引吃了 148mb 空间（加了之后我还没 OPTIMIZE TABLE ），但整个表大小 460 也小于您最初设计的表 604

如果您需要节省空间（既是指硬盘上的空间也是指 innodb buffer pool 中能塞下多少个这样大的表的 index ）
那么需要根据您实际业务的查询，您是主要查询 WHERE content_id 还是 WHERE tag_id （都只约束一个字段，如果两个字段都约束那肯定会用上 composite key ，因为您的 CK 只有这两个字段），就把哪个字段移动到现有 CK （在这里是 PK ）的最左（第一个）

[shendaowu]

@h0099
你说的东西我基本上只能看懂一部分。另外我打算暂时不纠结这个东西了，因为了解越多我越感觉这个东西复杂。比如那个 in 据说在某些情况下会出问题。而我现在应该是处理不了这么复杂的东西，因为很多数据库优化方面的概念我都没有系统的了解。有时间还是看数据库优化方面的书吧。

我想优化的其实是那个带 in 的查询语句。今天我又试了一下结果 HeidiSQL 又偶尔出现不到一毫秒的情况了，这次没看错。不过我猜应该是 HeidiSQL 的显示不准确，因为用 show profile 看大概是十毫秒左右。之前的两秒多的情况反而无法复现了。

你愿意提供收费优化 SQL 的服务吗？感觉有点主动上钩的感觉，如果你真是有意的的话。抱歉我带着恶意揣测你了，因为我之前好像看过相关的东西，我记得好像是《影响力》里有相关的东西。能留个联系方式吗？邮箱就行。不想留的话我留。

> 那阁下来 v2 问您的特定于您的表结构场景的问题又什么什么目的？

因为我对只能帮助一个人没什么太大的排斥，另外如果只能帮助一个人但是能提升自己的表达或者其他能力我是愿意帮的，我估计你可能也有类似或者其他的原因吧。所以我估计你可能会回复我，所以就问了。还有我感觉好像是你引导我问这种问题的。我问那个是因为我估计网上绝大多数人输出内容应该都是为了更大的利益吧，输出只能帮助到一个人的内容明显不能最大化自己的利益。这个应该算是在知乎学到的吧，知乎禁止过于个人化的问题，想要解决个人问题基本上只能用付费咨询。

[h0099]

> 比如那个 in 据说在某些情况下会出问题

啥问题？

> 我想优化的其实是那个带 in 的查询语句

#19 显示`WHERE content_id = 50000`比那一坨`WHERE tag_id IN`要慢，因为 content_id 只能`skip scan with index`
而且 tag_id 应该是走 PK 上的索引了的因为您的 PK 是(tag_id, content_id)符合最左字段
所以阁下要优化这几百 ms 的 sql ？

> 今天我又试了一下结果 HeidiSQL 又偶尔出现不到一毫秒的情况了，这次没看错。不过我猜应该是 HeidiSQL 的显示不准确

建议 phpmyadmin/datagrip

> 你愿意提供收费优化 SQL 的服务吗？感觉有点主动上钩的感觉，如果你真是有意的的话。抱歉我带着恶意揣测你了
v2 人发帖回答您时收您费了？顶多您回帖需要站内积分

> 表结构之类的东西： https://pastebin.com/Kin9UkXg 。太长了，直接发我嫌浪费积分

然而您有着 14k 积分

> 知乎禁止过于个人化的问题，想要解决个人问题基本上只能用付费咨询。

什么知乎盐选
所以这里是知乎还是 V2EX ？还是说 V2EX 早已被知乎收购？建议立即致电 livid

[h0099]

`解决个人问题`建议立即前往 stackexchange 站群的各个站点如 stackoverflow ，他们可不会计较什么`这是您自己的复杂问题关我啥事`，因为这种 answer 发出来就会被 tag off-topic 一瞬削除

[h0099]

> answer 发出来就会被 tag off-topic 一瞬削除

回顾经典之 https://stackoverflow.com/questions/18113418/ignore-duplicate-key-insert-with-entity-framework/75113236#comment132551981_75113236

[h0099]

与此同时：我还在与表结构 migration 搏斗

[shendaowu]

@h0099

> 比如那个 in 据说在某些情况下会出问题

> 啥问题？

https://blog.csdn.net/kevinxxw/article/details/109567275 。

> 所以阁下要优化这几百 ms 的 sql ？

在我的电脑上那条带 IN 的语句的执行时间很不稳定，有时候 10 毫秒左右，有时候一两秒，有时候一二十秒。这么不稳定的执行时间我接受不了了。之前说不纠结这个一部分就是因为很多次都是 10 毫秒左右，现在这么不稳定不能不纠结了。我新问了一个问题： https://www.v2ex.com/t/909074 。忙的话就不用看了。

> 然而您有着 14k 积分

我以后可能会在 V 站充钱，然后花积分置顶帖子，积分能省点就省点吧。

> v2 人发帖回答您时收您费了？顶多您回帖需要站内积分

是这样。我是在某本书上看到说先某个人一些好处，然后再向这个人提要求这个人更容易答应。你这么热心的人我之前好像基本没见过。然后再联想到之前我也有过付费咨询的经历，所以就想到找你做付费咨询了。感觉我的思维方式有点跳跃。不过感觉好像也算是稍微又那么一些合理吧，我好像只在付费咨询的时候得到过类似的连续的问答。

> 所以这里是知乎还是 V2EX ？还是说 V2EX 早已被知乎收购？建议立即致电 livid
> `解决个人问题`建议立即前往 stackexchange 站群的各个站点如 stackoverflow ，他们可不会计较什么`这是您自己的复杂问题关我啥事`，因为这种 answer 发出来就会被 tag off-topic 一瞬削除

估计属于可得性偏差了。大意是认为容易想到的东西出现的概率更大。我平时经常用知乎，stackoverflow 好像是没用过，虽然经常能搜到上面的问题。因为经常用知乎，所以以为知乎的那套规矩是比较常见的。其实我之前也想过那可能只是为了赚钱和增长之类的原因才那么定的，不过应该是可得性偏差的力量太强大了。

> 与此同时：我还在与表结构 migration 搏斗

你的意思是你很忙吗？是的话那抱歉消耗你时间了。

[h0099]

> https://blog.csdn.net/kevinxxw/article/details/109567275
> in 通常是走索引的，当 in 后面的数据在数据表中超过 30%（上面的例子的匹配数据大约 6000/16000 = 37.5%）的匹配时，会走全表扫描，即不走索引，因此 in 走不走索引和后面的数据有关系。

这不就是 cost-based optimizer 认为直接 full table scan 的 IO 时间开销比去使用索引然后反复在 primary/secondary index 和具体的 datanode 之间绕圈子更快？这跟 where in 有关系吗？
您自己建个只有 10 行的表，哪怕您给`WHERE field=1`的 field 加了索引 EXPLAIN 也会显示根本不使用您加的索引因为 scan10 行远比去索引里绕要快
so 人早已道明真相： https://stackoverflow.com/questions/586381/mysql-not-using-indexes-with-where-in-clause

另外阁下看着这些简中互联网的 csdn 垃圾还不如去啃 en 文档或 en 书籍：
https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html
https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/greater-less-between-tuning-sql-access-filter-predicates


> 估计属于可得性偏差了。大意是认为容易想到的东西出现的概率更大。我平时经常用知乎，stackoverflow 好像是没用过，虽然经常能搜到上面的问题。因为经常用知乎，所以以为知乎的那套规矩是比较常见的。其实我之前也想过那可能只是为了赚钱和增长之类的原因才那么定的，不过应该是可得性偏差的力量太强大了。

逼乎那群功利主义者带 v KOL 您指望什么？整天吹嘘着知识付费然后反手把几年前发的回答给删了放进盐选订阅里
等您花了几块钱巨款打开一看全都是车轱辘话和十几年前 web2.0 时代的互联网上唾手可得的信息的复制粘贴嗯缝合，或是像 csdn 人那样去机翻 en 互联网上的公开免费信息回来兜售二手屎
stackexchange 人至少不搞什么乱七八糟的 monetize ，您把您的 sql pastebin 发到 dba.stackexchange 上同样会有人指出您的`WHERE tag_id`无法直接使用索引而只能`using index for skip scan`，所以应该按照最左字段原则改变 composite key 中的字段顺序

> 你的意思是你很忙吗？是的话那抱歉消耗你时间了。

我只是说我们都在与 mysql 搏斗

[h0099]

> 在我的电脑上那条带 IN 的语句的执行时间很不稳定，有时候 10 毫秒左右，有时候一两秒，有时候一二十秒。这么不稳定的执行时间我接受不了了。之前说不纠结这个一部分就是因为很多次都是 10 毫秒左右，现在这么不稳定不能不纠结了。我新问了一个问题： https://www.v2ex.com/t/909074 。忙的话就不用看了。

您后台是不是跑着一堆频繁内存 /硬盘 io 的程序？您应该先把他们都关掉以控制变量，或是直接去开个空白服务器专门测试这些
您 innodbbufferpoolsize 多少？是不是还用着默认的 128M ？