新特性解读 | MySQL 8.0 对 limit 的优化

作者:杨奇龙

网名“北在南方”,资深 DBA,主要负责数据库架构设计和运维平台开发工作,擅长数据库性能调优、故障诊断。

本文来源:原创投稿

*爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。


一、前言

提到 limit 优化,大多数 MySQL DBA 都不会陌生,能想到各种应对策略,比如延迟关联,书签式查询等等,之前我也写过一篇优化的文章:https://mp.weixin.qq.com/s/2nSdHiGsu35Yf3fRStzumQ ,有兴趣的朋友可以复习一下。

二、MySQL 8.0 对limit 的改进

对于 limit N 带有 group by ,order by 的 SQL 语句 (order by 和 group by 的字段有索引可以使用),MySQL 优化器会尽可能选择利用现有索引的有序性,减少排序–这看起来是 SQL 的执行计划的最优解,但是实际上效果其实是南辕北辙,相信很多 DBA 遇到的相关案例中 sql 执行计划选择 order by id 的索引进而导致全表扫描,而不是利用 where 条件中的索引查找过滤数据。MySQL 8.0.21 版本之前,并没有什么参数来控制这种行为,但是自 MySQL 8.0.21 之后提供一个优化器参数 prefer_ordering_index ,通过设置 optimizer_switch 来开启或者关闭该特性 。 比如:

SET  optimizer_switch  = "prefer_ordering_index=off";

SET  optimizer_switch = "prefer_ordering_index=on";

三、实践出真知

测试环境 MySQL 社区版 8.0.30

构造测试数据

CREATE TABLE t (
id1 BIGINT  NOT NULL  PRIMARY KEY auto_increment, 
id2 BIGINT NOT NULL,
c1 VARCHAR(50) NOT NULL,
c2 varchar(50) not null,
INDEX i (id2, c1));

insert into t(id2,c1,c2) values(1,'a','xfvs'),(2,'bbbb','xfvs'),(3,'cdddd','xfvs'),(4,'dfdf','xfvs'),(12,'bbbb','xfvs'),(23,'cdddd','xfvs'),(14,'dfdf','xfvs'),
(11,'bbbb','xfvs'),(13,'cdddd','xfvs'),(44,'dfdf','xfvs'),(31,'bbbb','xfvs'),(33,'cdddd','xfvs'),(34,'dfdf','xfvs');
3.1 默认开启参数
mysql  (test) >  SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';
+------------------------------------------------------+
| @@optimizer_switch LIKE '%prefer_ordering_index=on%' |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+
1 row in set (0.00 sec)

查询非索引字段 ,id2 上有索引 ,order by 主键 id1 ,explain 查看执行计划 type index 说明使用索引扫描使用 using where 过滤结果集。这个是优化器的自以为的最优选择,但是实际上遇到数据集合比较大的表,该执行计划就不是最优解,反而导致慢查。

mysql  (test) > explain select c2 from t where id2>8 ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: index
possible_keys: i
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 2
     filtered: 69.23
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
3.2 关闭该参数
mysql  (test) > SET optimizer_switch = "prefer_ordering_index=off";

mysql  (test) > explain select c2 from t where id2>8 ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 8
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)

经过调整之后,查看执行计划发现优化器选择 id2 索引字段找到记录做过滤,并且使用了ICP 特性,减少物理 io 请求,而不是选择使用主键 id1 遍历索引然后回表查询。

显然 通过人为介入参数调整优化器的行为能带来更好的优化效果。

四、总结

从不同版本的 MySQL 发展轨迹来看 MySQL 的优化器越来越智能 (比如大家期待已久的直方图特性) ,能更多的减少人为干预,提升执行计划的准确性。

have fun with MySQL 8.0 _

<think>嗯,用户问的是MySQL 9.2是否支持向量搜索。首先,我需要确认MySQL的版本情况。目前,MySQL的最新稳定版本是8.0系列,而9.x版本可能还在开发或测试阶段,尚未正式发布。因此,用户提到的MySQL 9.2可能是一个假设的版本,或者可能混淆了其他数据库的版本号,比如PostgreSQL的某个版本支持向量扩展。 接下来,关于向量搜索的支持,需要了解MySQL现有的功能。MySQL 8.0引入了JSON字段、全文搜索等功能,但原生并不支持向量搜索。向量搜索通常用于机器学习中的相似性匹配,比如使用余弦相似度等算法。这通常需要特定的数据类型和索引结构,比如Faiss或Annoy这样的库,或者像PostgreSQL的pgvector扩展。 然后,用户可能是在寻找在MySQL中实现类似功能的方法。如果MySQL 9.2真的存在,可能是否包含向量搜索模块呢?根据目前公开的信息,Oracle官方并没有宣布MySQL 9.x版本支持向量搜索。可能用户看到了某些第三方插件或自定义解决方案,或者混淆了其他数据库的功能。 另外,用户可能有特定的项目需求,需要将向量搜索集成到MySQL中,可能需要考虑替代方案,比如使用外部工具与MySQL结合,或者迁移到支持向量搜索的数据库如PostgreSQL加上pgvector,或者使用专门的向量数据库如Milvus、Pinecone等。 还要考虑用户的技术背景,是否了解向量搜索的基本概念,或者是否有特定的使用场景,比如推荐系统、图像识别等。可能需要进一步询问用户的需求,或者提供替代解决方案的指导。 最后,需要确保回答准确,避免误导用户。应明确指出MySQL当前版本不支持向量搜索,并建议可行的替代方案,同时提醒用户确认MySQL版本的准确性,避免混淆。</think>截至 **2024年7月**,**MySQL 官方并未发布 9.2 版本**,当前最新的稳定版本是 **MySQL 8.0** 系列。此外,MySQL 原生版本(包括社区版和企业版)**暂不支持向量搜索(Vector Search)功能**。 --- ### **关键信息解读** 1. **版本澄清**: - MySQL 的版本号遵循 `主版本.次版本` 的命名规则,如 `8.0.36`。 - **不存在 MySQL 9.2**,可能混淆了其他数据库(如 PostgreSQL)的版本或测试分支。 2. **向量搜索的现状**: - **向量搜索**通常指通过机器学习模型生成的向量(高维数组)进行相似性匹配,需结合特定算法(如余弦相似度)和索引(如 HNSW、IVF)。 - **MySQL 原生不支持向量数据类型或向量索引**,无法直接实现类似 `pgvector`(PostgreSQL 扩展)的功能。 --- ### **替代解决方案** 若需在 MySQL 生态中实现类似功能,可参考以下方法: #### 1. **结合外部工具** - **流程**: - 使用 Python/TensorFlow 生成向量。 - 将向量存储在 MySQL 的 `BLOB` 或 `JSON` 字段中。 - 通过应用程序层计算相似度(如余弦相似度)。 - **缺点**: - 全表扫描性能差,不适合大规模数据。 #### 2. **使用专用向量数据库** - **推荐工具**: - [Milvus](https://milvus.io/):开源向量数据库,支持高性能相似性搜索。 - [Pinecone](https://www.pinecone.io/):云原生向量数据库,适合企业级应用。 - **流程**: - 在 MySQL 中存储元数据(如文本、图片路径)。 - 在向量数据库中存储向量,通过外键关联两者数据。 #### 3. **迁移到 PostgreSQL + pgvector** - **优势**: - PostgreSQL 的 `pgvector` 扩展直接支持向量存储和相似性搜索。 - 性能优于 MySQL 手动实现方案。 - **示例代码**: ```sql CREATE TABLE items (id SERIAL PRIMARY KEY, vector VECTOR(128)); CREATE INDEX ON items USING hnsw (vector); SELECT * FROM items ORDER BY vector <-> '[0.1, 0.2, ...]' LIMIT 10; ``` --- ### **未来可能性** - **MySQL 潜在支持**: Oracle 官方未公布向量搜索开发计划,但可关注 [MySQL Labs](https://labs.mysql.com/) 的实验性功能。 - **云厂商优化**: 部分云数据库(如 AWS Aurora、阿里云 PolarDB)可能通过插件或定制引擎提供类似功能。 --- ### **总结建议** 1. **确认需求**:明确是否需要实时向量搜索,或离线计算即可满足需求。 2. **评估规模**:小规模数据可通过应用层实现;大规模数据建议使用专用向量数据库。 3. **技术选型**:优先考虑 PostgreSQL + pgvector 或 Milvus 等成熟方案。 如果需要进一步探讨具体场景,请提供更多细节(如数据规模、查询频率等)!
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值