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`？还是什么？