system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL这些分别代表什么意思

时间: 2024-04-27 09:24:00 浏览: 127

Mysql面试过关！（详解：索引+常用引擎+常见问题+sql调优）

一、Mysql索引 1、添加索引sql语句 2、查看MySQL中建立的索引是否生效 3、索引失效场景（补充：以下在实际应用中并不会一定导致索引失效，基于mysql不同版本的优化规则） 3.1 在联合索引的场景下，查询条件不满足最左匹配原则 3.2 索引列参与了运算，会导致全表扫描，索引失效（除主键索引外） 3.3 模糊查询时（like语句），模糊匹配的占位符位于条件的首部 3.4 索引列参使用了函数 3.5 参数类型与字段类型不匹配，导致类型发生了隐式转换，索引失效 3.6 查询条件使用or关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效； or两边为“>”和“<”范围查询时，索引失效 3.7 两列数据做比较，即便两列都创建了索引，索引也会失效 3.8 查询条件使用不等进行比较时，需要慎重，普通索引会查询结果集占比较大时索引会失效。 3.9 查询条件使用is null时正常走索引，使用is not null时，不走索引 3.10 查询条件使用not in、not exists时，如果是主键则走索引，如果是普通索引，则索引失效 3.11 当查询条件涉及到order by、l ### MySQL索引详解 #### 一、MySQL索引概述索引在数据库中扮演着至关重要的角色，它能够显著提高数据检索的速度。简单来说，索引就像图书的目录一样，帮助用户快速定位所需数据的位置。当数据库表中的数据量增大时，如果没有合适的索引支持，查询操作可能会变得极其缓慢。 #### 1. 添加索引SQL语句添加索引是优化数据库性能的关键步骤之一。以下是几种常用的添加索引的方式： - **添加主键索引 (PRIMARY KEY)**： ```sql ALTER TABLE table_name ADD PRIMARY KEY (column); ``` - **添加唯一索引 (UNIQUE)**： - 通过 `ALTER TABLE` 命令添加： ```sql ALTER TABLE table_name ADD UNIQUE (column); ``` - 使用 `CREATE INDEX` 命令创建： ```sql CREATE UNIQUE INDEX index_name ON table_name (column); ``` - **添加普通索引 (INDEX)**： - 通过 `ALTER TABLE` 命令添加： ```sql ALTER TABLE table_name ADD INDEX index_name (column); ``` - 使用 `CREATE INDEX` 命令创建： ```sql CREATE INDEX index_name ON table_name (column); ``` - **添加全文索引 (FULLTEXT)**： - 通过 `ALTER TABLE` 命令添加： ```sql ALTER TABLE table_name ADD FULLTEXT (column); ``` - 使用 `CREATE INDEX` 命令创建： ```sql CREATE FULLTEXT INDEX index_name ON table_name (column); ``` - **添加联合索引**： - 通过 `ALTER TABLE` 命令添加： ```sql ALTER TABLE table_name ADD INDEX index_name (column1, column2, column3); ``` - 使用 `CREATE INDEX` 命令创建： ```sql CREATE INDEX index_name ON table_name (column1, column2, column3); ``` #### 2. 查看MySQL中建立的索引是否生效为了验证索引是否有效工作，可以使用 `EXPLAIN` 命令来分析查询计划。执行以下命令后，通过观察 `EXPLAIN` 的输出结果中的 `key` 列，如果为空，则表示未使用索引；如果显示了具体的索引名称，则表明该查询正在使用索引。 ```sql EXPLAIN SELECT surname, first_name FROM a, b WHERE a.id = b.id; ``` `EXPLAIN` 的输出还包含了其他有用的信息，比如访问类型 (`possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra` 等)，可以帮助我们更深入地理解查询计划和优化方向。 **八种访问类型**： - system - const - eq_ref - ref - range - index - all - null **从最好到最差的顺序**： - system > const > eq_ref > ref > range > index > all **最全的访问顺序**： - system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all 在实际项目开发中，应尽量优化到 `range` 和 `ref` 访问类型。 #### 3. 索引失效场景索引虽然能提高查询效率，但在某些情况下可能会导致索引失效，进而降低查询性能。下面列举了一些常见的索引失效情况： - **3.1 在联合索引的场景下，查询条件不满足最左匹配原则**：联合索引是由多个字段组成的索引。在查询时，必须确保查询条件能够覆盖联合索引中的所有字段，并且按照索引字段的顺序出现。例如，对于联合索引 `(a, b, c)`，查询 `WHERE a = ? AND c = ?` 不会使用索引，因为没有包含中间的 `b` 字段。 - **3.2 索引列参与了运算**：如果查询条件中对索引列进行了数学运算或其他类型的函数操作，通常会导致索引失效。 - **3.3 模糊查询时（like语句），模糊匹配的占位符位于条件的首部**：例如 `WHERE column LIKE '%abc'`，这种情况下索引可能无法发挥作用。 - **3.4 索引列使用了函数**：如果查询条件涉及索引列上的函数操作，索引通常不会被使用。 - **3.5 参数类型与字段类型不匹配，导致类型发生了隐式转换**：例如，索引字段是 `int` 类型，但查询条件传递的是字符串，则可能会导致索引失效。 - **3.6 查询条件使用or关键字，其中一个字段没有创建索引**：如果 `OR` 两侧的条件分别涉及不同的字段，且其中一个字段未建立索引，则可能导致索引失效。 - **3.7 两列数据做比较**：如果两个字段都建立了索引，但它们之间进行直接比较，则索引可能无法发挥作用。 - **3.8 查询条件使用不等进行比较**：当查询条件使用 `<` 或 `>` 进行比较时，索引的使用取决于具体情况。如果结果集占比较小，则可能仍然使用索引；反之，索引可能失效。 - **3.9 查询条件使用is null时正常走索引，使用is not null时，不走索引**：这是因为 `IS NULL` 和 `IS NOT NULL` 的处理方式不同。 - **3.10 查询条件使用not in、not exists时**：如果查询条件使用 `NOT IN` 或 `NOT EXISTS`，并且涉及的字段不是主键，则索引可能会失效。 - **3.11 当查询条件涉及到order by、limit时**：如果查询中包含 `ORDER BY` 和 `LIMIT`，且排序字段没有索引，则可能导致索引失效。通过了解这些索引失效的情况，开发者可以在设计数据库结构和编写查询语句时更加谨慎，从而避免不必要的性能损失。此外，还可以通过调整查询语句、增加适当的索引以及优化表结构等方式来提升查询效率。

这些是MySQL查询优化器在执行查询时，可能使用的访问类型（Access Type）的顺序，表示访问数据的方式，常见的访问类型包括： - ALL：全表扫描，即扫描整个表来找到匹配的行 - index：仅扫描索引来找到匹配的行，性能比全表扫描要好 - range：扫描索引的一个范围来找到匹配的行，通常是在索引列上使用了比较运算符（如>、<、BETWEEN等） - index_merge：使用多个索引来优化查询，将多个索引的结果合并 - unique_subquery：使用子查询来查找唯一的值 - index_subquery：使用子查询来查找值，子查询结果使用索引来进行优化 - ref_or_null：使用索引查找匹配的行，如果索引中没有匹配的值，则返回NULL - fulltext：使用全文索引来查找匹配的行 - ref：使用非唯一索引查找匹配的行 - eq_ref：使用唯一索引查找匹配的行这些访问类型的性能从左到右逐渐变好，即越靠左的访问类型性能越好。因此，查询优化器会尝试使用最左边的访问类型来执行查询，以获得最好的性能。

阅读全文

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL这些分别代表什么意思

相关推荐

Mysql设计与优化专题-062315491

行业-87以MySQL单表查询来举例，看看执行计划包含哪些内容（2）？.rar

SL-ST 差速器3D模型 SL-ST 差速器

C#大型药品进销存管理系统源码数据库 Access源码类型 WinForm

JAVAKTV点歌系统源码数据库 MySQL源码类型 WinForm

树叶形状、分布与树枝结构关系及其质量估算模型研究

大数据1+x(蓝桥课堂实操231216）解析

阿里云的yum源，替换CentOS的yum源

基于JAVA+SpringBoot+MySQL的职称评审管理系统lw设计与实现.docx

海康威视摄像头64位 API demo

C#Asp.net Core MVC开发WMS仓库仓储管理系统源码数据库 SQL2014源码类型 WebForm

bios-计算机启动与固件接口-从传统BIOS到UEFI的历史演变及其功能解析

【java毕业设计】助学贷款网站源码（完整前后端+说明文档+LW）.zip

C#ASP.NET设备管理系统源码带文档+视频数据库 SQL2008源码类型 WebForm

【java毕业设计】高校科研管理系统源码（完整前后端+说明文档+LW）.zip

基于JAVA+SpringBoot+MySQL的生鲜交易系统设计与实现.docx

精选微信小程序源码：寄快递小程序（含源码+源码导入视频教程&文档教程，亲测可用）

在Android使用深度学习模型实现图像识别（使用Tensorflow Lite、MNN、TNN）.zip

无线网络渗透测试WPA,WPA2密码还原.zip

最新推荐

linux创建线程之pthread_create的具体使用

详细解析命令行的getopt_long()函数

Linux中mkdir函数与Windows中_mkdir函数的区别

C++标准库：std_set作为一个有序集合

关于vs strcpy_s（）和strcat_s()用法探究

C语言数组操作：高度检查器编程实践

管理建模和仿真的文件

【KUKA系统变量进阶】：揭秘从理论到实践的5大关键技巧

如何使用Python编程语言创建一个具有动态爱心图案作为背景并添加文字'天天开心（高级版）'的图形界面？

基于Swift开发的嘉定单车LBS iOS应用项目解析