技术问题, mysql, 如果某表某字段离散度很小,但分布及不均匀, 如仅有‘是’、’否‘两种数值, 但‘是’的数据可能仅为 100 个以内的数量, 全部数据有 100 万或更多, 现在需要查询状态为‘是’的数据,如何优化查询。

180 天前
zf1968  zf1968
目前想到的方案:
1 、直接对此字段加索引? 但 gpt 回答,还未自己验证,如果索引离散度太低,可能 mysql 查询优化器会自动退化为全表扫描
2 、再建一个新表,专门存状态为‘是’的数据的 id 。 相当于自建了一个仅包含部分数据的索引, 但这种又会增加代码复杂度,对业务逻辑有侵入
2492 次点击
所在节点   MySQL  MySQL
19 条回复
Tiaoooo
Tiaoooo
180 天前
试一下分区后加索引呢
以下内容来自 ai:

-- 假设我们有一个名为 'user_activities' 的表
CREATE TABLE user_activities (
id INT AUTO_INCREMENT,
user_id INT,
activity_type VARCHAR(50),
status ENUM('是', '否'),
created_at TIMESTAMP,
PRIMARY KEY (id, status)
) ENGINE=InnoDB;

-- 按 status 列进行分区
ALTER TABLE user_activities
PARTITION BY LIST COLUMNS(status) (
PARTITION p_yes VALUES IN ('是'),
PARTITION p_no VALUES IN ('否')
);

-- 插入一些示例数据
INSERT INTO user_activities (user_id, activity_type, status, created_at) VALUES
(1, '登录', '是', NOW()),
(2, '购买', '否', NOW()),
(3, '评论', '是', NOW()),
(4, '浏览', '否', NOW());

-- 查询 status 为 '是' 的记录
EXPLAIN SELECT * FROM user_activities WHERE status = '是';

-- 添加索引以进一步优化查询
CREATE INDEX idx_status_created_at ON user_activities(status, created_at);

-- 再次解释查询计划
EXPLAIN SELECT * FROM user_activities WHERE status = '是' ORDER BY created_at DESC LIMIT 10;

-- 查看分区信息
SELECT PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_activities';
awalkingman
awalkingman
180 天前
嫌加一张表太重,那就加一个字段,是就给个时间戳,否就给个 1000 以内的值,然后对该字段加索索引,离散度拉满,区分度拉满。
zf1968
zf1968
180 天前
分区表会影响其他索引的效率吧
xmumiffy
xmumiffy
180 天前
被查询的值少加索引是可以的
sagaxu
sagaxu
180 天前
是: 1
否: rand(-30000, 0)

离散度是不是一下子高了很多? 30000 不够那就 30000000
lesismal
lesismal
180 天前
id 或者其他冗余字段, int64 或者 string, "是"则该字段为时间戳*10000 的 int64 或者对应的 string, "否"则是时间戳不加倍

查询"是"的时候查大于时间戳 10000 倍的位数的最小数值的范围
lesismal
lesismal
180 天前
#6 该字段做索引
lenmore
lenmore
180 天前
如果只查询 “是”,大胆的建索引吧,效率非常高。查询“否”,配合其他字段建组合索引,效率同样杠杠的。我们上亿的表都这么干,一点问题没有。
salparadise
salparadise
180 天前
离散度太低不适合加索引
gary007lang
gary007lang
180 天前
mysql8.0 的话,可以建立直方图
zhouxiaoben
zhouxiaoben
180 天前
100 万数据对 mysql 来说小意思
hangszhang
180 天前
直接在这个字段上建索引就行
wuyiccc
179 天前
force index
flyingfz
179 天前
如果是 PG , PG 有 部分索引, 完美解决这个问题。
zoharSoul
179 天前
直接加索引就行
me1onsoda
179 天前
直接加索引啊,如果你要查否那可能有点麻烦
xuanbg
179 天前
直接加上索引就行,查“是”效率高的一批,查“否:就约等于没有索引了
RandomJoke
179 天前
100w 不用考虑这么多,啥都上索引肯定都行
wenxueywx
165 天前
这种字段肯定不会单独作为条件拿来查吧,建议和其他常用查询字段做联合索引。
#5 说的 是:1 否:rand (-30000 ,0 )的这种做法,离散度是高了,但并不能提升查询效率,即是走了索引,该扫描的行数也不会少,如果要回表,最坏的情况还可能导致一次全索引扫描+一次回表。这和使用 hint 强制走索引一样。

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/1067107

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX