做网站设计的公司柳州,wordpress不响应,网站建设与策划,北京门户网站设计昨天领导突然问到#xff0c;MySQL中explain获取到的type字段中index和ref的区别是什么。 这两种状态都是在使用索引后产生的#xff0c;但具体区别却了解不多#xff0c;只知道ref相比于index效率更高。 因此#xff0c;本文较为详细地记录了MySQL性能中返回字段的含义、状… 昨天领导突然问到MySQL中explain获取到的type字段中index和ref的区别是什么。 这两种状态都是在使用索引后产生的但具体区别却了解不多只知道ref相比于index效率更高。 因此本文较为详细地记录了MySQL性能中返回字段的含义、状态级别的产生条件与区别。 索引
假设有一个表 employees包含以下字段id、first_name、last_name 和 address。
普通索引 (Normal Index)这是最基本的索引类型它没有任何限制。可以对表中的一个或多个字段创建普通索引以加快数据查询的速度。
示例CREATE INDEX idx_last_name ON employees (last_name);唯一索引 (Unique Index)该索引与普通索引类似不同之处在于索引列中的值必须唯一但允许有空值NULL。如果在列中插入重复值MySQL 会报错。
示例CREATE UNIQUE INDEX idx_unique_first_name ON employees (first_name);主键索引 (Primary Key Index)主键索引是一种特殊的唯一索引不允许空值NULL。一个表只能有一个主键索引一般用于标识表中的唯一记录。
示例ALTER TABLE employees ADD PRIMARY KEY (id);全文索引 (Full-text Index)用于全文搜索的索引主要用于 CHAR、VARCHAR 和 TEXT 类型的字段。它可以加快对大文本数据的搜索速度适用于需要进行全文检索的场景。
示例CREATE FULLTEXT INDEX idx_fulltext_address ON employees (address);组合索引 (Composite Index)组合索引是对表中的多个列创建的索引用于提高多列条件查询的性能。MySQL 会根据组合索引中列的顺序来优化查询。
示例CREATE INDEX idx_name ON employees (first_name, last_name);空间索引 (Spatial Index)这是 MySQL 特有的索引类型用于空间数据类型如 POINT、LINESTRING、POLYGON 等的索引。主要用于地理空间查询。
示例CREATE SPATIAL INDEX idx_geometry ON locations (geometry);关键字
EXPLAIN 是 MySQL 中的一个关键字用于分析 SQL 查询语句的执行计划。通过 EXPLAIN 返回的信息用户可以了解查询优化器是如何选择执行计划的以及可能的性能瓶颈。 id 每个查询的唯一标识符。对于多表查询id的值会增大。 select_type 查询的类型主要有以下几种 SIMPLE: 简单查询不包含子查询或UNION。PRIMARY: 最外层的SELECT。SUBQUERY: 子查询中的第一个SELECT。DERIVED: 派生表中的SELECT比如在FROM子句中包含子查询。UNION: UNION中的第二个或后续的SELECT。UNION RESULT: UNION的结果集。 table 当前查询的表。 partitions 匹配的分区信息如果有分区。 type 表连接类型显示查询中使用的连接类型主要有以下几种从优到劣排列 system: 表只有一行系统表。const: 表最多有一个匹配行用于主键或唯一索引。eq_ref: 对每个来自前一个表的行组合从该表读取一行。ref: 对于每个来自前一个表的行组合从该表读取所有匹配的行。range: 检索给定范围的行使用索引来选择行。index: 扫描整个索引。ALL: 扫描整个表。 possible_keys 查询中可能使用的索引。 key 查询中实际使用的索引。 key_len 使用的索引的长度。 ref 显示哪一列或常量与key一起使用。 rows MySQL 估计要读取的行数。 filtered 经过WHERE条件过滤后返回的行的百分比。 Extra 附加信息 Using index: 表示使用了覆盖索引只从索引中读取信息不用回表。Using where: 使用了WHERE子句来过滤行。Using temporary: 使用了临时表来保存中间结果。Using filesort: MySQL使用外部排序而不是从表中按索引顺序读取行。
示例
以下是一个使用 EXPLAIN 的查询及其返回结果的示例
EXPLAIN SELECT first_name, last_name FROM employees WHERE id 1;假设返回结果如下
idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEemployeesconstPRIMARYPRIMARY4const1100.0Using index
解释
id 是 1表示这是一个简单查询。select_type 是 SIMPLE表示没有子查询。table 是 employees查询的表是 employees。type 是 const表示使用了主键查询。possible_keys 是 PRIMARY表示可能使用的索引是主键。key 是 PRIMARY实际使用的索引是主键。key_len 是 4表示索引的长度为 4 字节。ref 是 const表示查询条件使用了常量。rows 是 1表示预期扫描1行。filtered 是 100.0表示返回的行没有被过滤。Extra 是 Using index表示查询只使用了索引。
通过分析这些信息用户可以优化查询调整索引提高查询性能。
type级别解释
在 EXPLAIN 语句的输出中type 列表示 MySQL 在执行查询时使用的连接类型。不同的连接类型表示 MySQL 如何从表中选择数据。从性能优到劣排序 system 表只有一行系统表。这是一个特殊的 const 连接类型是性能最优的连接类型。 const 表最多有一个匹配行用于主键或唯一索引。因为只有一行匹配MySQL 可以将该值视为常量。对于 PRIMARY KEY 或 UNIQUE 索引字段进行等值查询时会使用这种类型。 EXPLAIN SELECT * FROM employees WHERE id 1;eq_ref 对每个来自前一个表的行组合从该表读取一行。这是性能次优的连接类型用于使用唯一索引的所有部分进行等值比较的情况。通常用于带有主键或唯一索引的连接。 EXPLAIN SELECT * FROM employees e JOIN departments d ON e.department_id d.id;ref 对于每个来自前一个表的行组合从该表读取所有匹配的行。这种类型用于非唯一索引或非主键的情况。 EXPLAIN SELECT * FROM employees WHERE department_id 1;range 检索给定范围的行使用索引来选择行。常用于范围查询如使用 , , , , BETWEEN, IN 等操作符的查询。 EXPLAIN SELECT * FROM employees WHERE id BETWEEN 1 AND 10;index 全索引扫描Index Scan。这种类型与 ALL 类似但只遍历索引树。它比 ALL 更快因为索引文件通常比数据文件小。 EXPLAIN SELECT * FROM employees ORDER BY last_name;ALL 全表扫描Table Scan。这是性能最差的连接类型。MySQL 必须扫描整个表才能找到匹配的行。通常这是由于查询没有使用索引或者优化器认为全表扫描比使用索引更快。 EXPLAIN SELECT * FROM employees WHERE first_name John;示例及详细解释
假设有一个表 employees表结构如下
CREATE TABLE employees (id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),department_id INT,INDEX (department_id)
);使用不同查询进行 EXPLAIN const EXPLAIN SELECT * FROM employees WHERE id 1;type 是 const因为 id 是主键查询只会匹配一行。 eq_ref EXPLAIN SELECT * FROM employees e JOIN departments d ON e.department_id d.id;type 是 eq_ref因为 department_id 是一个索引并且是连接条件的一部分。 ref EXPLAIN SELECT * FROM employees WHERE department_id 1;type 是 ref因为 department_id 是一个非唯一索引。 range EXPLAIN SELECT * FROM employees WHERE id BETWEEN 1 AND 10;type 是 range因为使用了范围查询。 index EXPLAIN SELECT * FROM employees ORDER BY last_name;type 是 index因为查询需要按照 last_name 进行排序而没有其他过滤条件。 ALL EXPLAIN SELECT * FROM employees WHERE first_name John;type 是 ALL因为 first_name 没有索引需要全表扫描。
