欢迎您访问 最编程 本站为您分享编程语言代码，编程技术文章！

热门搜索/Hot Search

您现在的位置是： 首页

类型专栏详情和案例研究

最编程 2024-03-16 15:56:53
...


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、测试环境简述


数据库 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))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


1. ALL

全表扫描，通常意味着MySQL必须从头到尾扫描整张表，去查找匹配的行的行，性能极差。

但是，如果在查询里使用了 LIMIT n，虽然 type 依然是 ALL，但是MySQL只需要扫描到符合条件的前 n 行数据，就会停止继续扫描。


  • 查询昵称中带 雪 字的用户数据，因为使用了前缀模糊匹配，不能命中索引，会导致全表扫描
mysql> EXPLAIN SELECT * FROM `user` WHERE `nickname` LIKE '%雪%' LIMIT 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 22748
     filtered: 11.11
        Extra: Using where
  • 查询根据用户id可以被10整除的用户数据。因为在 = 前的索引列上进行了表达式运算，不能命中索引，会全表扫描。
mysql> EXPLAIN SELECT * FROM `user` WHERE id%10=0 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 22293
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)
  • 查询手机号是 18888888888 的用户数据，由于数据表中 phone 字段是字符串类型，而查询时使用了数字类型，会触发隐式类型转换，不会命中索引，因此会全表扫描。
mysql> EXPLAIN SELECT * FROM `user` WHERE phone=18888888888 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: unq_phone_country_code
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 22293
     filtered: 10.00
        Extra: Using where


2. index

index 跟 ALL 一样，也会进行全表扫描，只是MySQL会按索引次序进行全表扫描，而不是直接扫描行数据。它的主要优点是避免了排序；最大的缺点是要承担按索引次序读取整个表的开销。若是按随机次序访问行，开销将会非常大。


  • 根据出生年分组去重，查询用户数据。
mysql> EXPLAIN SELECT * FROM `user` GROUP BY `birth_year` \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: index
possible_keys: idx_birth_year_gender
          key: idx_birth_year_gender
      key_len: 5
          ref: NULL
         rows: 22748
     filtered: 100.00
        Extra: NULL

如果在 Extra 列中看到 Using index，说明MySQL正在使用覆盖索引，索引的数据中包含了查询所需的所有字段，因此只需要扫描索引树就能够完成查询任务。它比按索引次序全表扫描的开销要少很多，因为索引树的大小通常要远小于全表数据。

  • 根据出生年分组，查询不同年份出生的用户个数，这里用到了覆盖索引。
mysql> EXPLAIN SELECT `birth_year`,COUNT(*) FROM `user` GROUP BY `birth_year`\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: index
possible_keys: idx_birth_year_gender
          key: idx_birth_year_gender
      key_len: 5
          ref: NULL
         rows: 22748
     filtered: 100.00
        Extra: 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: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_birth_year_gender
      key_len: 5
          ref: NULL
         rows: 22748
     filtered: 100.00
        Extra: Using index
  • 查询表数据总条数，查询数据条数时，InnoDB存储引擎会自动选择最短的索引，通过遍历该索引，就可以计算出数据总条数，不需要回表查询其他数据，因此也能用到覆盖索引。
mysql> EXPLAIN SELECT COUNT(*) FROM user \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_birth_year_gender
      key_len: 5
          ref: NULL
         rows: 22748
     filtered: 100.00
        Extra: Using index


3. range


范围扫描，就是一个有范围限制的索引扫描，它开始于索引里的某一点，返回匹配这个范围值的行。range 比全索引扫描更高效，因为它用不着遍历全部索引。


范围扫描分为以下两种情况：

  1. 范围条件查询：在 WHERE 子句里带有 BETWEEN、>、<、>=、<= 的查询。
  2. 多个等值条件查询：使用 IN() 和 OR ，以及使用 like 进行前缀匹配模糊查询。
  • 查询 id >= 1000 且 id < 2000 的用户数据。
mysql> EXPLAIN SELECT * FROM `user` WHERE `id`>=1000 AND `id`<2000 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 8
     filtered: 100.00
        Extra: Using where
  • 查询 90后 的用户数据。
mysql> EXPLAIN SELECT * FROM `user` WHERE `birth_year` BETWEEN 1990 AND 1999 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: range
possible_keys: idx_birth_year_gender
          key: idx_birth_year_gender
      key_len: 3
          ref: NULL
         rows: 150
     filtered: 100.00
        Extra: Using index condition
  • 查询昵称以 雪 字开头的用户数据。
mysql> EXPLAIN SELECT * FROM `user` WHERE `nickname` LIKE '雪%' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: range
possible_keys: idx_nickname
          key: idx_nickname
      key_len: 43
          ref: NULL
         rows: 30
     filtered: 100.00
        Extra: Using where
  • 分别使用 IN() 和 OR 两种方式查询出生年份在 1990,2000,2010 的用户数据。
mysql> EXPLAIN SELECT * FROM `user` WHERE `birth_year` IN (1990,2000,2010) \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: range
possible_keys: idx_birth_year_gender
          key: idx_birth_year_gender
      key_len: 3
          ref: NULL
         rows: 41
     filtered: 100.00
        Extra: Using index condition
mysql> EXPLAIN SELECT * FROM `user` WHERE `birth_year`=1990 OR `birth_year`=2000 OR `birth_year`=2010 \G
*************************** 1. row ***************************
           id: 1

																				

						

							上一篇：							
								导出类型 使用方法							
						

						

							下一篇：							
								python 解析网络数据 BeautifulSoup 和 xpath							
						

					

															

						

							推荐阅读