做网站的公司在哪,新网站一般多久收录,深圳seo网站,线上销售技巧Explain 命令中的 type 列#xff0c;显示MySQL查询所使用的 关联类型#xff08;Join Types#xff09; 或者 访问类型#xff0c;它表明 MySQL决定如何查找表中符合条件的行。 常见访问类型性能由最差到最优依次为#xff1a;ALL index range index_subq…Explain 命令中的 type 列显示MySQL查询所使用的 关联类型Join Types 或者 访问类型它表明 MySQL决定如何查找表中符合条件的行。 常见访问类型性能由最差到最优依次为ALL index range index_subquery unique_subquery index_merge ref_or_null fulltext ref eq_ref const system。
0、测试环境简述
本文 MySQL 实例版本为 5.7表存储引擎为 InnoDB
数据库 t 中有两张表 user、user_captcha每张表中有2W条数据下面是两张表的建表语句表结构只为满足实验要求没有实际业务逻辑参考价值
user 表
id 字段是主键email 字段建立了唯一索引phone 与 country_code 字段组成联合唯一索引birth_year 与 gender 字段组成联合普通索引nickname 字段前10个字符建立了普通索引
CREATE TABLE user (id int(11) NOT NULL AUTO_INCREMENT,nickname varchar(255) DEFAULT NULL,country_code smallint(6) unsigned NOT NULL DEFAULT 0,phone varchar(12) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT ,email varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,gender tinyint(4) DEFAULT NULL,birth_year smallint(11) unsigned DEFAULT NULL,created_at int(11) NOT NULL,PRIMARY KEY (id),UNIQUE KEY unq_phone_country_code (phone,country_code) USING BTREE,UNIQUE KEY unq_email (email),KEY idx_birth_year_gender (birth_year,gender) USING BTREE,KEY idx_nickname (nickname(10))
) ENGINEInnoDB DEFAULT CHARSETutf8mb4
user_captcha 表
id 字段是主键user_id 字段建立了唯一索引可以为空receiver 字段建立了唯一索引
CREATE TABLE user_captcha (id int(11) NOT NULL AUTO_INCREMENT,user_id int(11) unsigned DEFAULT NULL,code char(6) COLLATE utf8_unicode_ci NOT NULL COMMENT 验证码,retry_times int(11) NOT NULL COMMENT 重试次数,last_request_at int(11) unsigned DEFAULT NULL COMMENT 最后请求时间,receiver varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL COMMENT 接收者手机号或邮箱,created_at int(11) NOT NULL,expired_at int(11) NOT NULL COMMENT 过期时间,PRIMARY KEY (id),UNIQUE KEY unq_receiver (receiver) USING BTREE,UNIQUE KEY unique_user (user_id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4
1. ALL
全表扫描通常意味着MySQL必须从头到尾扫描整张表去查找匹配的行的行性能极差。但是如果在查询里使用了 LIMIT n虽然 type 依然是 ALL但是MySQL只需要扫描到符合条件的前 n 行数据就会停止继续扫描。
查询昵称中带 雪 字的用户数据因为使用了前缀模糊匹配不能命中索引会导致全表扫描
mysql EXPLAIN SELECT * FROM user WHERE nickname LIKE %雪% LIMIT 1 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 22748filtered: 11.11Extra: Using where
查询根据用户id可以被10整除的用户数据。因为在 前的索引列上进行了表达式运算不能命中索引会全表扫描。
mysql EXPLAIN SELECT * FROM user WHERE id%100 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 22293filtered: 100.00Extra: Using where
1 row in set, 1 warning (0.01 sec)
查询手机号是 18888888888 的用户数据由于数据表中 phone 字段是字符串类型而查询时使用了数字类型会触发隐式类型转换不会命中索引因此会全表扫描。
mysql EXPLAIN SELECT * FROM user WHERE phone18888888888 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: ALL
possible_keys: unq_phone_country_codekey: NULLkey_len: NULLref: NULLrows: 22293filtered: 10.00Extra: Using where
2. index
index 跟 ALL 一样也会进行全表扫描只是MySQL会按索引次序进行全表扫描而不是直接扫描行数据。它的主要优点是避免了排序最大的缺点是要承担按索引次序读取整个表的开销。若是按随机次序访问行开销将会非常大。
根据出生年分组去重查询用户数据。
mysql EXPLAIN SELECT * FROM user GROUP BY birth_year \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: index
possible_keys: idx_birth_year_genderkey: idx_birth_year_genderkey_len: 5ref: NULLrows: 22748filtered: 100.00Extra: NULL
如果在 Extra 列中看到 Using index说明MySQL正在使用覆盖索引索引的数据中包含了查询所需的所有字段因此只需要扫描索引树就能够完成查询任务。它比按索引次序全表扫描的开销要少很多因为索引树的大小通常要远小于全表数据。
根据出生年分组查询不同年份出生的用户个数这里用到了覆盖索引。
mysql EXPLAIN SELECT birth_year,COUNT(*) FROM user GROUP BY birth_year\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: index
possible_keys: idx_birth_year_genderkey: idx_birth_year_genderkey_len: 5ref: NULLrows: 22748filtered: 100.00Extra: Using index
查询用户的id、性别、出生年数据由于 idx_birth_year_gender 索引中包含 birth_year 和 gender字段而 InnoDB的所有索引都包含id字段不需要回表查询其他数据因此也能用到覆盖索引。
mysql EXPLAIN SELECT id,birth_year,gender FROM user LIMIT 10 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: index
possible_keys: NULLkey: idx_birth_year_genderkey_len: 5ref: NULLrows: 22748filtered: 100.00Extra: Using index
查询表数据总条数查询数据条数时InnoDB存储引擎会自动选择最短的索引通过遍历该索引就可以计算出数据总条数不需要回表查询其他数据因此也能用到覆盖索引。
mysql EXPLAIN SELECT COUNT(*) FROM user \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: index
possible_keys: NULLkey: idx_birth_year_genderkey_len: 5ref: NULLrows: 22748filtered: 100.00Extra: Using index
3. range
范围扫描就是一个有范围限制的索引扫描它开始于索引里的某一点返回匹配这个范围值的行。range 比全索引扫描更高效因为它用不着遍历全部索引。
范围扫描分为以下两种情况
范围条件查询在 WHERE 子句里带有 BETWEEN、、、、 的查询。多个等值条件查询使用 IN() 和 OR 以及使用 like 进行前缀匹配模糊查询。
查询 id 1000 且 id 2000 的用户数据。
mysql EXPLAIN SELECT * FROM user WHERE id1000 AND id2000 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: range
possible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: NULLrows: 8filtered: 100.00Extra: Using where
查询 90后 的用户数据。
mysql EXPLAIN SELECT * FROM user WHERE birth_year BETWEEN 1990 AND 1999 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: range
possible_keys: idx_birth_year_genderkey: idx_birth_year_genderkey_len: 3ref: NULLrows: 150filtered: 100.00Extra: Using index condition
查询昵称以 雪 字开头的用户数据。
mysql EXPLAIN SELECT * FROM user WHERE nickname LIKE 雪% \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: range
possible_keys: idx_nicknamekey: idx_nicknamekey_len: 43ref: NULLrows: 30filtered: 100.00Extra: Using where
分别使用 IN() 和 OR 两种方式查询出生年份在 1990,2000,2010 的用户数据。
mysql EXPLAIN SELECT * FROM user WHERE birth_year IN (1990,2000,2010) \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: range
possible_keys: idx_birth_year_genderkey: idx_birth_year_genderkey_len: 3ref: NULLrows: 41filtered: 100.00Extra: Using index conditionmysql EXPLAIN SELECT * FROM user WHERE birth_year1990 OR birth_year2000 OR birth_year2010 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: range
possible_keys: idx_birth_year_genderkey: idx_birth_year_genderkey_len: 3ref: NULLrows: 41filtered: 100.00Extra: Using index condition
4. index_subquery
index_subquery 替换了以下形式的子查询中的 eq_ref 访问类型其中 key_column 是非唯一索引。
value IN (SELECT key_column FROM single_table WHERE some_expr)
index_subquery 只是一个索引查找函数它可以完全替换子查询提高查询效率。
大多数情况下使用SELECT子查询时MySQL查询优化器会自动将子查询优化为联表查询因此 type 不会显示为 index_subquery。
在MySQL查询优化器判定可以对 SELECT 子查询进行优化的情况下使用子查询与联表查询的执行计划是相同的。
mysql EXPLAIN SELECT code FROM user_captcha LEFT JOIN user ON user.phoneuser_captcha.receiver WHERE phone like 1888% \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: range
possible_keys: unq_phone_country_codekey: unq_phone_country_codekey_len: 14ref: NULLrows: 44filtered: 100.00Extra: Using where; Using index
*************************** 2. row ***************************id: 1select_type: SIMPLEtable: user_captchapartitions: NULLtype: eq_ref
possible_keys: unq_receiverkey: unq_receiverkey_len: 257ref: t.user.phonerows: 1filtered: 100.00Extra: Using index conditionmysql EXPLAIN SELECT code FROM user_captcha WHERE receiver IN (SELECT phone FROM user WHERE phone like 1888%) \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: range
possible_keys: unq_phone_country_codekey: unq_phone_country_codekey_len: 14ref: NULLrows: 44filtered: 100.00Extra: Using where; Using index; LooseScan
*************************** 2. row ***************************id: 1select_type: SIMPLEtable: user_captchapartitions: NULLtype: eq_ref
possible_keys: unq_receiverkey: unq_receiverkey_len: 257ref: t.user.phonerows: 1filtered: 100.00Extra: Using index condition
我们可以通过在 UPDATE 语句的执行计划中看到 index_subquery。
mysql EXPLAIN UPDATE user_captcha SET retry_times1 WHERE receiver IN (SELECT phone FROM user WHERE phone like 1888%) \G
*************************** 1. row ***************************id: 1select_type: UPDATEtable: user_captchapartitions: NULLtype: index
possible_keys: NULLkey: PRIMARYkey_len: 4ref: NULLrows: 22433filtered: 100.00Extra: Using where
*************************** 2. row ***************************id: 2select_type: DEPENDENT SUBQUERYtable: userpartitions: NULLtype: index_subquery
possible_keys: unq_phone_country_codekey: unq_phone_country_codekey_len: 14ref: funcrows: 1filtered: 100.00Extra: Using where; Using index
5. unique_subquery
unique_subquery 跟 index_subquery 类似它替换了以下形式的子查询中的 eq_ref 访问类型其中 primary_key 可以是主键索引或唯一索引。
value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery 只是一个索引查找函数它可以完全替换子查询提高查询效率。
由于MySQL查询优化器会对 SELECT 子查询进行优化我们可以在 UPDATE 语句的执行计划中看到 unique_subquery。
mysql EXPLAIN UPDATE user_captcha SET retry_times1 WHERE user_id IN (SELECT id FROM user WHERE phone like %1888%) \G
*************************** 1. row ***************************id: 1select_type: UPDATEtable: user_captchapartitions: NULLtype: index
possible_keys: NULLkey: PRIMARYkey_len: 4ref: NULLrows: 22433filtered: 100.00Extra: Using where
*************************** 2. row ***************************id: 2select_type: DEPENDENT SUBQUERYtable: userpartitions: NULLtype: unique_subquery
possible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: funcrows: 1filtered: 11.11Extra: Using where
6. index_merge
表示出现了索引合并优化通常是将多个索引字段的范围扫描合并为一个。包括单表中多个索引的交集并集以及交集之间的并集但不包括跨多张表和全文索引。 这种优化并非必然发生的当查询优化器判断优化后查询效率更优时才会进行优化。详情可查看官方文档
查询出生年在 1990,2000,2010 年或 id1000 的用户数据
mysql EXPLAIN SELECT * FROM user WHERE birth_year IN (1990,2000,2010) OR id1000 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: index_merge
possible_keys: PRIMARY,idx_birth_year_genderkey: idx_birth_year_gender,PRIMARYkey_len: 3,4ref: NULLrows: 46filtered: 100.00Extra: Using sort_union(idx_birth_year_gender,PRIMARY); Using where
查询手机号以 183 开头或 出生年 大于1990 年的用户数据
mysql EXPLAIN SELECT * FROM user WHERE phone like 183% OR birth_year1990 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: index_merge
possible_keys: unq_phone_country_code,idx_birth_year_genderkey: unq_phone_country_code,idx_birth_year_genderkey_len: 14,3ref: NULLrows: 1105filtered: 100.00Extra: Using sort_union(unq_phone_country_code,idx_birth_year_gender); Using where
查询出生年在 1990 年或 id1000 的用户数据
mysql EXPLAIN SELECT * FROM user WHERE birth_year1990 OR id1000 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: index_merge
possible_keys: PRIMARY,idx_birth_year_genderkey: idx_birth_year_gender,PRIMARYkey_len: 3,4ref: NULLrows: 11filtered: 100.00Extra: Using sort_union(idx_birth_year_gender,PRIMARY); Using where
1 row in set, 1 warning (0.01 sec)
7. ref_or_null
ref_or_null 于 ref 类似但是MySQL必须对包含 NULL 值的行就行额外搜索。
查找昵称是 空字符串 或 NULL 的用户数据
mysql EXPLAIN SELECT * FROM user WHERE nickname OR nickname IS NULL \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: ref_or_null
possible_keys: idx_nicknamekey: idx_nicknamekey_len: 43ref: constrows: 2filtered: 100.00Extra: Using where
8. fulltext
命中全文索引时 type 为 fulltext。
9. ref
索引访问有时也叫做索引查找它返回所有匹配某个单个值的行。然而它可能会找到多个符合条件的行因此它是查找和扫描的混合体。此类索引访问只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。把它叫做 ref 是因为索引要跟某个参考值相比较。这个参考值或者是一个常数或者是来自多表查询前一个表里的结果值。
查找出生年在2000年的用户数据。
mysql EXPLAIN SELECT * FROM user WHERE birth_year2000 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: ref
possible_keys: idx_birth_year_genderkey: idx_birth_year_genderkey_len: 3ref: constrows: 30filtered: 100.00Extra: NULL
查找电话号码是 18888888888 的用户数据phone 与 country_count 联合组成唯一索引 unq_phone_country_codephone 是唯一索引 unq_phone_country_code 的非唯一性前缀。
mysql EXPLAIN SELECT * FROM user WHERE phone18888888888\G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: ref
possible_keys: unq_phone_country_codekey: unq_phone_country_codekey_len: 14ref: constrows: 1filtered: 100.00Extra: NULL
10. eq_ref
当进行等值联表查询时联结字段命中主键索引或唯一的非空索引时将使用 eq_ref。 (《高性能MySQL第3版》一书中说使用主键或唯一索引查询时会用 eq_ref经过反复测试并查阅MySQL5.6、5.7版本的官方文档实际上使用主键或唯一索引进行等值条件查询时 type 会显示 const《高性能MySQL第3版》这里应该是只适用于5.5之前的版本。)
以 user_captcha 表作为主表LEFT JOIN user 表查询用户数据因为user表中id字段是主键所以第二行的 user 表的 type 为 eq_ref
mysql EXPLAIN SELECT * FROM user_captcha LEFT JOIN user ON user.iduser_captcha.user_id \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: user_captchapartitions: NULLtype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 22433filtered: 100.00Extra: NULL
*************************** 2. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: eq_ref
possible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: t.user_captcha.user_idrows: 1filtered: 100.00Extra: Using where
2 rows in set, 1 warning (0.01 sec)
当使用 user 表作为主表LEFT JOIN user_captcha 表时因为 user_captcha 表中 user_id 字段与 device_id 组成联合唯一索引user_id 并非独立的唯一索引所以第二行的 user_captcha 表的 type 为 ref而并非 eq_ref
mysql EXPLAIN SELECT * FROM user LEFT JOIN user_captcha ON user.iduser_captcha.user_id \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: ALL
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 22999filtered: 100.00Extra: NULL
*************************** 2. row ***************************id: 1select_type: SIMPLEtable: user_captchapartitions: NULLtype: ref
possible_keys: unique_userkey: unique_userkey_len: 5ref: t.user.idrows: 1filtered: 100.00Extra: Using where
11. const
MySQL 知道查询最多只能匹配到一条符合条件的记录。因为只有一行所以优化器可以将这一行中的列中的值视为常量。const 表查询非常快因为它们只读取一次数据行。通常使用主键或唯一索引进行等值条件查询时会用 const。
使用主键查询用户数据
mysql EXPLAIN SELECT * FROM user WHERE id120 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: const
possible_keys: PRIMARYkey: PRIMARYkey_len: 4ref: constrows: 1filtered: 100.00Extra: NULL
使用唯一索引查询用户数据
mysql EXPLAIN SELECT * FROM user WHERE email54222806qq.com \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: const
possible_keys: unq_emailkey: unq_emailkey_len: 258ref: constrows: 1filtered: 100.00Extra: NULL
使用联合唯一索引查询用户数据
mysql EXPLAIN SELECT * FROM user WHERE country_code86 AND phone18888888888 \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: userpartitions: NULLtype: const
possible_keys: unq_phone_country_codekey: unq_phone_country_codekey_len: 16ref: const,constrows: 1filtered: 100.00Extra: NULL
12. system
官方文档原文是The table has only one row ( system table). This is a special case of the const join type. 该表只有一行系统表。这是 const 关联类型的特例。
从系统库mysql的系统表 proxies_priv 里查询数据这里的数据在Mysql服务启动时候已经加载在内存中不需要进行磁盘IO。
mysql EXPLAIN SELECT * FROM mysql.proxies_priv \G
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: proxies_privpartitions: NULLtype: system
possible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 1filtered: 100.00Extra: NULL
参考
