请教这条 mysql 优化出路在哪？

[demonps]

表结构

create table assign_idx(
  it_id BIGINT UNSIGNED NOT NULL,
  cat_id BIGINT UNSIGNED NOT NULL,
  c_type BIGINT UNSIGNED NOT NULL,
  c_user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
  l_user_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
  create_time BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY(it_id),
  INDEX idx_c_type_cat_c_user_time(c_type, cat_id, c_user_id, create_time)
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 default charset utf8mb4;

explain select it_id from assign_idx where c_user_id = 0 and l_user_id != 3333 and (c_type, cat_id) in ((30, 1), (30, 2), (30, 3), (20, 4), (20, 6), (20, 9), (20, 10)) order by create_time asc limit 1;

explain 结果 总有 filesort 如何破

[demonps]

```mysql
> explain select it_id from assign_idx where c_user_id = 0 and l_user_id != 3333 and (c_type, cat_id) in ((30, 1), (30, 2), (30, 3), (20, 4), (20, 6), (20, 9), (20, 10)) order by create_time asc limit 1;
+----+-------------+------------+------------+------+----------------------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+----------------------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | assign_idx | NULL | ALL | idx_c_type_cat_c_user_time | NULL | NULL | NULL | 1 | 100.00 | Using where; Using filesort |
+----+-------------+------------+------------+------+----------------------------+------+---------+------+------+----------+-----------------------------+
```mysql

[TanLeDeDaNong]

所有的 in 全部拆成
(c_type=a AND cat_id=b) OR （...）

[zhangysh1995]

(c_type, cat_id) 是索引的一部分，所以建的索引不能用，慢
where 里面的 col 都不能用索引，慢

[zhangysh1995]

https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html 文档

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

[poisedflw]

单从你的 sql 来看，索引顺序建错了吧？
KEY `idx` (`c_user_id`,`l_user_id`,`c_type`,`cat_id`,`create_time`)

[zhangysh1995]

自己打下脸，好像 (c_type, cat_id) 可以优化

https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html#row-constructor-range-optimization

Only IN() predicates are used, not NOT IN().
On the left side of the IN() predicate, the row constructor contains only column references.
On the right side of the IN() predicate, row constructors contain only runtime constants, which are either literals or local column references that are bound to constants during execution.
On the right side of the IN() predicate, there is more than one row constructor.

[zhangysh1995]

给一下
explain select it_id where (c_type, cat_id) in ((30, 1), (30, 2), (30, 3), (20, 4), (20, 6), (20, 9), (20, 10));
结果？

[wangyzj]

参考 #5 试试
然后再看

[demonps]

@zhangysh1995 mysql> explain select it_id from assign_idx where (c_type, cat_id) in ((30, 1), (30, 2), (30, 3), (20, 4), (20, 6), (20, 9), (20, 10));
+----+-------------+------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | assign_idx | NULL | range | idx_c_type_cat_c_user_time | idx_c_type_cat_c_user_time | 16 | NULL | 86 | 100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+----------------------------+----------------------------+---------+------+------+----------+--------------------------+

[Foredoomed]

所以顺序错了，create_time 放第一个

[demonps]

@Foredoomed 还是不对，create_time 放第一个 ，type 就变成 index 了 虽然没了 filesort

[lpts007]

这张表目前多少数据？
这个语句目前耗时？
有 filesort 怎么了？
变成 index 怎么了？

[lpts007]

这个语句没有毛病。不太清楚要优化什么。

[ElmerZhang]

没用到期望的索引时，就 force index 试试

[demonps]

@lpts007 其实这个本来就是一张 index 作用的表，
目前数据 300 万+
高频最高耗时 0.13s
本来这个是要 update ... from (select ...)
因为读写频次高导致 锁表，所以 拆成两步，先 select 再 update 。
本来就是 index 功能的中间表，扫 index 和扫全表就没啥差异了呀

[jiehuangwei]

如果不是写的特别差的语句，其优化的空间很有限，投入产出比太低，不如从业务入手，梳理业务逻辑

[zhangysh1995]

@demonps 问题应该在 where 里面前两个条件没有索引，可以拆开成 explain select it_id where c_user_id = 0 and l_user_id != 3333; 然后继续拆，单独看一下数据情况，估计会有至少一个估计不准确的（应该是 l_user_id 没有索引）。

我写这句的时候突然有个想法，你可以试下：

select it_id from assign_idx where (c_type, cat_id) in ((30, 1), (30, 2), (30, 3), (20, 4), (20, 6), (20, 9), (20, 10)) and c_user_id = 0 and l_user_id != 3333 order by create_time asc limit 1;

[guyskk0x0]

目测 2 楼正解。建的索引问题不大，基数大的列放前面会更好。

[controller]

不等号不会走索引。。。

[demonps]

@jiehuangwei 感谢🙏

[demonps]

@zhangysh1995 试了好多方法 是 in 的锅 目前 in 不太好再抽一个量表示 蛋疼中～