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

[shendaowu]

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

[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 ？