如何在mysql中使用索引加速JSON字段查询

直接上结论吧。

JSON字段查询速度慢。

使用生成的列提取 JSON 值。

创建一个 STORED 生成列。

添加索引。

年龄查询示例：
sql ALTER TABLE users ADD COLUMNage INT AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.age'))) STORED; 在用户上创建索引 idx_age(年龄);
城市查询示例：
sql ALTER TABLE users ADD COLUMN city VARCHAR(5 0) AS (profile->>'$.city') STORED; 在用户（年龄，城市）上创建索引 idx_age_city;
虚拟列不占用空间。

存储的列占用空间。

索引会增加写入开销。

JSON结构必须稳定。

不要忘记添加非空约束。

mysql如何强制使用索引 mysql创建索引后的查询优化技巧

说实话，对于MySQL的索引，你不能随意添加。
你需要了解优化器在想什么，并让它选择正确的路径。

1 .如何让优化器使用正确的索引
ANALYZER必须定期运行。
例如，如果您的表三个月没有更新统计信息，只需 ANALYZETABLEjour_table;。
优化器依靠此数据来确定索引的效率。
如果添加新数据，或者数据分布发生巨大变化，它的大脑将不得不更新其知识库。

谨慎使用 FORCEINDEX！这迫使优化器使用特定的索引。
仅当您确定必须使用此方法并且您无能为力时才考虑使用它。
如果一个慢索引被阻塞，整个查询就卡住了，那不是很麻烦吗？
优化器希望这是一个很好的作弊。
USEINDEX 说：“嘿，优先考虑这个索引。
” IGNOREINDEX 说：“不要使用这个，烦人。
”例如，SELECTFROMyour_tableUSEINDEX(idx_name)WHERE...；这可以控制优化器。

查询语句需要优化。
例如，如果您键入 WHEREDate(order_date)='2 02 3 -1 0-2 6 '，则索引将为空。
应更改为 WHEREorder_date>='2 02 3 -1 0-2 6 'ANDorder_date<'2 02 3 -1 0-2 7 '。
列类型也必须匹配。
对于整数 id 查询，请使用 WHEREid=1 2 3 不要写 WHEREid=‘1 2 3 ’。

2 索引构建后优化技巧
索引覆盖技巧很神奇。
查询只需要索引中的字段。
不需要返回表，直接从索引中取出数据。
比如经常查id和name，可以直接创建复合索引(id,name)，查起来很快。

必须记住最左边的前缀规则。
复合索引必须从左到右匹配。
例如，索引(a,b,c)可以支持WHEREa=1 或WHEREa=1 ANDb=2 但 WHEREb=2 是没有用的。

索引的选择性也很重要。
选择性=唯一值的数量/记录总数。
例如，不要在性别这样的列上创建索引，这是一种浪费。

不要创建太多索引。
指数不错，但不便宜。
每次写操作都必须同步更新，这也占用空间。
您应该定期使用 INFORMATION_SCHEMA 检查哪些索引未使用并删除它们。

EXPLAIN应该经常使用。
例如，EXPLAINSELECTFROMyour_tableWHERE...；视图类型（ALL表示全表扫描，范围表示索引扫描），key（使用哪个索引），rows（预计扫描的行数）。
如果你卡在这里，你需要更改索引或语句。

3 常见索引失败陷阱
在 WHERE 子句中使用函数或表达式将导致失败。
比如WHEREYEAR(date_column)=2 02 3 ，这个索引就没用了。
应更改为 WHEREDate_column>='2 02 3 -01 -01 'ANDdate_column<'2 02 4 -01 -01 '。

小心类型转换。
WHERE 条件与索引列的类型不匹配，例如字符串与整数，优化器会感到困惑。
您必须显式转换类型。

只要其中一个OR条件没有索引，整个条件就无效。
可以分区成UNIONALL，也可以创建索引对于所有条件。

LIKE '%...%' 这种模糊匹配的前端比较混乱。
例如LIKE'abc%'会直接破坏索引。
考虑全文索引。

NOTIN 和 != 有时会混淆索引。
您可能需要重写逻辑，例如使用 NOTEXISTS。

4 监控索引性能
慢查询日志是必须的。
例如，设置 long_query_time=1 秒以记录所有超过 1 秒的查询。
检查谁慢并相应优化。

也可以使用PMM、Prometheus+Grafana等监控工具。
实时查看查询时间、索引使用情况和关闭延迟，提前发现问题。

定期使用 INFORMATION_SCHEMA.STATISTICS 检查索引。
删除三年未使用的索引或合并重复的索引。

简而言之，索引优化就是让优化器为自己选择正确的路径，不要强迫它。
统计信息、查询语句、索引设计都要考虑。
在实际使用中，必须结合数据特性和业务需求。