MySQL索引设计与EXPLAIN

最新推荐文章于 2024-05-19 09:40:43 发布
最新推荐文章于 2024-05-19 09:40:43 发布
阅读量433 收藏 1
点赞数
分类专栏： MySQL 文章标签： mysql 索引 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42612223/article/details/113829135
版权

文章目录

前言

本文主要介绍设计索引的原则及如何使用EXPLAIN对SQL进行分析

一、索引设计原则

索引的设计可以遵守一些原则，创建索引时可以尽可能考虑这些原则，便于提升索引的使用效率。

  • 最适合索引的列是出现在WHERE子句中的列，或链接子句中的列，而不是出现在SELECT关键字后选择类表中的列。
  • 使用唯一索引。区分度越高，使用索引的效率越高
  • 使用短索引；例如有一个CHAR(200)列，如果前10或者20个字符内，多数值是唯一的，那么就不要对整个列进行索引。对前10个或者20个字符进行索引能够节省大量索引空间。对于较短的键值，索引高速缓存中的块能容纳更多的键值，减少IO。
  • 利用最左前缀。在创建一个n列的索引时实际上市创建了MySQL可利用的n个索引。多列索引可起到几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
  • 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价也高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。

二、索引的分类

1.主键索引

在InnoDB存储引擎中，每张表都会有主键。数据按照主键顺序组织存放，如果表定义时没有显示定义主键，则会按照以下方式选择或创建主键：

  • 先判断表中是否有“非空的唯一索引”，如果有
    • 如果仅有一条”非空唯一索引“，则该索引为主键
    • 如果有多条“非空唯一索引”则根据索引的先后顺序，选择第一个定义的非空唯一索引为主键
  • 如果表中无“非空唯一索引”，则自动创建一个6字节大小的指针作为主键。但是该主键不能被查询的
--创建表
CREATE TABLE `tb_test1` (
`id` int(11) NOT NULL, -- 非空
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
UNIQUE KEY `id` (`id`) USING BTREE -- 唯一索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tb_test1` (`id`, `name`, `age`) VALUES ('1', 'zhangsan', '20');
INSERT INTO `tb_test1` (`id`, `name`, `age`) VALUES ('2', 'lisi', '21');
--查询， _rowid就是视为主键，如果表⾥设置了主键之后， _rowid就是对应主键。
mysql> SELECT *,_rowid FROM tb_test1;
--查询结果，可以看到_rowid的值与id值相同
+----+----------+------+--------+
| id | name     | age  | _rowid |
+----+----------+------+--------+
|  1 | zhangsan |   20 |      1 |
|  2 | lisi     |   21 |      2 |
+----+----------+------+--------+

2.联合索引

联合索引就是将表中的多个列一起进行索引，需要注意的是，联合索引是有顺序的，比如：A、B、C列的索引与A、C、B列的索引是不一样的。

CREATE TABLE `tb_contact` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`index_code` varchar(1) DEFAULT NULL COMMENT '索引编号',
`surname` varchar(5) DEFAULT NULL COMMENT '姓',
`name` varchar(10) DEFAULT NULL COMMENT '名',
`mobile_code` varchar(11) DEFAULT NULL COMMENT '手机号',
`created` datetime DEFAULT NULL,
`updated` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_code` (`index_code`,`surname`,`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;


INSERT INTO `tb_contact` (`id`, `index_code`, `surname`, `name`, `mobile_code`,`created`, `updated`) VALUES ('1', 'Z', 'zhang', 'san', '13911111111', now(), now());
INSERT INTO `tb_contact` (`id`, `index_code`, `surname`, `name`, `mobile_code`,`created`, `updated`) VALUES ('2', 'L', 'li', 'si', '13922222222', now(), now());
INSERT INTO `tb_contact` (`id`, `index_code`, `surname`, `name`, `mobile_code`,`created`, `updated`) VALUES ('3', 'W', 'wang', 'wu', '13933333333', now(), now());
INSERT INTO `tb_contact` (`id`, `index_code`, `surname`, `name`, `mobile_code`,`created`, `updated`) VALUES ('4', 'Z', 'zhao', 'liu', '13944444444', now(), now());
INSERT INTO `tb_contact` (`id`, `index_code`, `surname`, `name`, `mobile_code`,`created`, `updated`) VALUES ('5', 'L', 'liu', 'hulan', '13955555555', now(), now());
INSERT INTO `tb_contact` (`id`, `index_code`, `surname`, `name`, `mobile_code`,`created`, `updated`) VALUES ('6', 'L', 'lei', 'jun', '13966666666', now(), now());
INSERT INTO `tb_contact` (`id`, `index_code`, `surname`, `name`, `mobile_code`,`created`, `updated`) VALUES ('7', 'M', 'ma', 'yun', '13977777777', now(), now());
INSERT INTO `tb_contact` (`id`, `index_code`, `surname`, `name`, `mobile_code`,`created`, `updated`) VALUES ('8', 'Q', 'qian', 'laoda', '13988888888', now(), now());

mysql> show index from tb_contact;
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb_contact |          0 | PRIMARY    |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| tb_contact |          1 | index_code |            1 | index_code  | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
| tb_contact |          1 | index_code |            2 | surname     | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
| tb_contact |          1 | index_code |            3 | name        | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

底层数据结构
联合索引的底层结构也是基于B+TREE，结构如下
在这里插入图片描述
InnoDB会使用主键索引在B+TREE维护索引和数据文件，然后我们创建一个联合索引（index_code,surname,name）也会生成一个索引树，同一是B+TREE结构，它的data部分存储的是联合索引所在行的的主键值。

联合索引比单值索引多了几列，而这些索引列全部出现在索引树上。对于联合索引，存储引擎会首先根据第一个索引列排序，如果第一列相对再根据第二列排序，以此类推构成索引树。

查找过程

select * from tb_contact where index_code='M' AND surname='ma' and name='yun';

首先从根节点开始查找，根节点一般是常驻内存中的，第一个列为index_code，其值为： M，在L与W之间，会继续向子节点查询：
在这里插入图片描述
在查找到字节点后，将子节点数据从磁盘中价值到内存中，找到对应的数据 M ma yun。再继续查找子节点，读取到子节点中的data数据，其数据就是这条记录的主键。然后再根据主键索引查找数据，最终在主键索引中查询到数据。
在这里插入图片描述

三、EXPLAIN

使用EXPLAIN可以查看SQL的执行计划，从而可以知道SQL的瓶颈在哪里，就可以有针对性地进行优化。

mysql>  EXPLAIN SELECT * FROM tb_contact WHERE index_code='M'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_contact
   partitions: NULL
         type: ref
possible_keys: index_code
          key: index_code
      key_len: 6
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)
字段描述
idselect查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type表示 SELECT 的类型，常见的取值有 SIMPLE、 PRIMARY、UNION、SUBQUERY等
table输出结果集的表
type表示关联类型或访问类型，即MySQL决定如何查找表中的⾏，查找数据⾏记录的⼤概范围。依次从最优到最差分别为： system > const > eq_ref > ref > range > index > ALL
possible_keys查询可能使用哪些索引来查找。
key表示实际用到的索引如果为null,则没有使用
key_len索引字段的长度
ref显示了在key列记录索引中， 表查找值所用到的列或常量
rows扫描行的数量 （不是结果集里的行数）
filtered返回结果的行占需要读到的行(rows列的值)的百分比
Extra展示额外的信息。常见有：Distinct，Not exists，Using filesort，Using index，Using where等

下面对每列具体说明
数据准备：

CREATE TABLE `t_role` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT,
`role_name` varchar(32) DEFAULT NULL,
`role_code` varchar(32) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_user` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT,
`username` varchar(32) NOT NULL,
`password` varchar(96) NOT NULL,
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_role` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` int DEFAULT NULL,
`role_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_ur_user_id` (`user_id`),
KEY `fk_ur_role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `t_user` (`id`, `username`, `password`, `name`)values(null,'super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超级管理员');
insert into `t_user` (`id`, `username`, `password`, `name`)values(null,'admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系统管理员');
insert into `t_user` (`id`, `username`, `password`, `name`)values(null,'itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
insert into `t_user` (`id`, `username`, `password`, `name`)values(null,'stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','学生1');
insert into `t_user` (`id`, `username`, `password`, `name`)values(null,'stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','学生2');
insert into `t_user` (`id`, `username`, `password`, `name`)values(null,'t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老师1');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES(null,'学生','student','学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES(null,'老师','teacher','老师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES(null,'教学管理员','teachmanager','教学管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES(null,'管理员','admin','管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES(null,'超级管理员','super','超级管理员');

INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;

1.id

id列的编号是select的序列号，有几个select就有几个id,并且id的顺序是按select出现的顺序增长的。其值越大执行优先级越高，id相同则由上往下执行，id为null最后执行。

mysql> EXPLAIN SELECT * FROM t_role r,t_user u,user_role ur WHERE r.id=ur.role_id AND u.id=ur.user_id;
+----+-------------+-------+------------+--------+-----------------------------+---------------+---------+---------------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type   | possible_keys               | key           | key_len | ref                 | rows | filtered | Extra                              |
+----+-------------+-------+------------+--------+-----------------------------+---------------+---------+---------------------+------+----------+------------------------------------+
|  1 | SIMPLE      | r     | NULL       | ALL    | PRIMARY                     | NULL          | NULL    | NULL                |    5 |   100.00 | NULL                               |
|  1 | SIMPLE      | ur    | NULL       | ref    | fk_ur_user_id,fk_ur_role_id | fk_ur_role_id | 5       | order_db.r.id       |    1 |   100.00 | Using index condition; Using where |
|  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY                     | PRIMARY       | 4       | order_db.ur.user_id |    1 |   100.00 | Using where                        |
+----+-------------+-------+------------+--------+-----------------------------+---------------+---------+---------------------+------+----------+------------------------------------+

mysql> EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id =(SELECT id FROM t_user WHERE username='stu1'));
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+--------------------------------+
| id | select_type | table     | partitions | type  | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra                          |
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+--------------------------------+
|  1 | PRIMARY     | NULL      | NULL       | NULL  | NULL                 | NULL                 | NULL    | NULL  | NULL |     NULL | no matching row in const table |
|  2 | SUBQUERY    | user_role | NULL       | ref   | fk_ur_user_id        | fk_ur_user_id        | 5       | const |    1 |   100.00 | Using where                    |
|  3 | SUBQUERY    | t_user    | NULL       | const | unique_user_username | unique_user_username | 98      | const |    1 |   100.00 | Using index                    |
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+--------------------------------+

2.select_type

表示SELECT语句的类型，有如下几种类型

select_type描述
SIMPLE简单select查询，查询中不包含子查询或UNION
PRIMARY查询中若包含任何子查询，最外层查询标记为该标识
SUBQUERY在SELECT 或 WHERE 列表中包含了子查询
DERIVED在from列表中包含的子查询，被标记为DERIVED(衍生) MySQL会递归执行这些子查询，把结果放到临时表中
UNION若第二个SELECT出现在UNION之后，则标记为UNION;若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED
UNION RESULT从UNION表获取结果的SELECT
mysql> EXPLAIN SELECT id FROM t_user WHERE username='stu1';
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | const | unique_user_username | unique_user_username | 98      | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+

mysql> EXPLAIN SELECT role_id FROM user_role WHERE user_id =(SELECT id FROM t_user WHERE username='stu1');
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys        | key                  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | user_role | NULL       | ref   | fk_ur_user_id        | fk_ur_user_id        | 5       | const |    1 |   100.00 | Using where |
|  2 | SUBQUERY    | t_user    | NULL       | const | unique_user_username | unique_user_username | 98      | const |    1 |   100.00 | Using index |
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------+------+----------+-------------+

3.type

type 显示的是访问类型，是较为重要的一个指标，可取值为：

type描述
NULLMySQL不访问任何表、索引，直接返回结果
system表只有一行记录，这是cost类型的特例，一般不会出现
const数据表最多只有一个匹配行，因为只匹配一行数据，所以很快；常出现于根据primary key或者unique索引查询
eq_refprimary key 或unique key索引的所有部分被连接使用，最多只会返回一条符合条件的记录
ref相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行
range只检索给定返回的行，使用一个索引来选择行。where之后出现between,<,>,in等操作
indexindex与ALL的区别是index类型只遍历了索引树，通常比ALL快，ALL是遍历数据文件
ALL将遍历全表以找到匹配的行

结果从最好到最坏：
system > const > eq_ref > ref > range > index > ALL
一般来说， 我们需要保证查询至少达到 range 级别， 最好达到ref 。

mysql> EXPLAIN SELECT * FROM t_user WHERE id=1;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

mysql> EXPLAIN SELECT * FROM t_user u,user_role ur WHERE u.id=ur.user_id;
+----+-------------+-------+------------+------+---------------+---------------+---------+---------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref           | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+---------------+---------+---------------+------+----------+-----------------------+
|  1 | SIMPLE      | u     | NULL       | ALL  | PRIMARY       | NULL          | NULL    | NULL          |    6 |   100.00 | NULL                  |
|  1 | SIMPLE      | ur    | NULL       | ref  | fk_ur_user_id | fk_ur_user_id | 5       | order_db.u.id |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+---------------+---------+---------------+------+----------+-----------------------+

mysql> EXPLAIN SELECT * FROM t_user WHERE id IN (1,4,5);
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

4.key_len

这一列显示了MySQL在所有中使用的字节数。通过这个值，可以算出具体使用了索引中的哪些列
key_len的计算规则如下：

  • 字符串
    • char(n)： n字节⻓度
    • varchar(n)： 2字节存储字符串⻓度，如果是utf-8，则长度度 3n + 2
  • 数值类型
    • tinyint:1字节
    • smallint:2字节
    • int:4字节
    • bigint:8字节
  • 时间类型
    • date:3字节
    • timestamp:4字节
    • datetime:8字节
  • 如果字段允许为null,需要1字节记录是否为null
mysql> SHOW CREATE TABLE tb_contact\G;
*************************** 1. row ***************************
       Table: tb_contact
Create Table: CREATE TABLE `tb_contact` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `index_code` varchar(1) DEFAULT NULL COMMENT '索引编号',
  `surname` varchar(5) DEFAULT NULL COMMENT '姓',
  `name` varchar(10) DEFAULT NULL COMMENT '名',
  `mobile_code` varchar(11) DEFAULT NULL COMMENT '手机号',
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_code` (`index_code`,`surname`,`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM tb_contact WHERE index_code = 'Z' AND surname='zhang'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_contact
   partitions: NULL
         type: ref
possible_keys: index_code
          key: index_code
      key_len: 24
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

上面的key_len计算：（31+2）+（35+2）+1+1=24
索引最大长度是768字节，当字符串过长时， mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

5.Extra

Distinct
发现第一个匹配行后，停止为当前的行组合搜索更多的行
Not exists
MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行为后,不再为前面的行组合在该表内检查更多的行。
Using filesort
MySQL会对结果使用一个外部索引排序，而不是按照索引次序从表里读取行。此时MySQL会根据联结类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况一般也是要考虑使用索引来优化的

mysql>  EXPLAIN SELECT id,name FROM tb_contact WHERE index_code='Z' ORDER BY mobile_code\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_contact
   partitions: NULL
         type: ref
possible_keys: index_code
          key: index_code
      key_len: 6
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.01 sec)

mysql> EXPLAIN SELECT id,name FROM tb_contact WHERE index_code='Z' ORDER BY surname\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_contact
   partitions: NULL
         type: ref
possible_keys: index_code
          key: index_code
      key_len: 6
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

Using index
从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息

-- 这里要查询的id和name都是索引
mysql> EXPLAIN SELECT id,name FROM tb_contact WHERE index_code='Z'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_contact
   partitions: NULL
         type: ref
possible_keys: index_code
          key: index_code
      key_len: 6
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Using temporary
MySQL需要创建一张临时表来处理查询，出现这种情况一般要进行优化。常见于 order by 和group by。

mysql> EXPLAIN SELECT COUNT(*) FROM tb_contact GROUP BY name\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_contact
   partitions: NULL
         type: index
possible_keys: index_code
          key: index_code
      key_len: 57
          ref: NULL
         rows: 8
     filtered: 100.00
        Extra: Using index; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)

Using where
MySQL服务器将在存储引擎检索行之后再进行过滤。先读取整行数据，再按WHERE条件进行检查，符合则留下，否则丢弃。

mysql> EXPLAIN SELECT * FROM tb_contact WHERE name='test02'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_contact
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8
     filtered: 12.50
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

NULL
查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引
Using index condition
与Using where类似，查询的列不完全被索引覆盖， where条件中是一个前导列的范围；

小结

  • using index ：使用覆盖索引的时候就会出现
  • using where：在查找使用索引的情况下，需要回表去查询所需的数据
  • using index condition：查找使用了索引，但是需要回表查询数据
  • using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

6.覆盖索引与回表查询

回表查询：先定位主键值，再定位行记录，它的性能较扫一遍索引树低
覆盖索引：只需要在一颗索引树上就能获取SQL所需要的所有列数据，无需回表

mysql> SHOW CREATE TABLE tb_contact\G;
*************************** 1. row ***************************
       Table: tb_contact
Create Table: CREATE TABLE `tb_contact` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `index_code` varchar(1) DEFAULT NULL COMMENT '索引编号',
  `surname` varchar(5) DEFAULT NULL COMMENT '姓',
  `name` varchar(10) DEFAULT NULL COMMENT '名',
  `mobile_code` varchar(11) DEFAULT NULL COMMENT '手机号',
  `created` datetime DEFAULT NULL,
  `updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_code` (`index_code`,`surname`,`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

-- 覆盖索引
mysql> EXPLAIN SELECT id,name FROM tb_contact WHERE index_code='L';
+----+-------------+------------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tb_contact | NULL       | ref  | index_code    | index_code | 6       | const |    3 |   100.00 | Using index |
+----+-------------+------------+------------+------+---------------+------------+---------+-------+------+----------+-------------+

-- 回表查询
mysql> EXPLAIN SELECT * FROM tb_contact WHERE index_code='L';
+----+-------------+------------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tb_contact | NULL       | ref  | index_code    | index_code | 6       | const |    3 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------------+---------+-------+------+----------+-------+

总结

本篇主要是用EXPLAIN如何分析SQL,在此基础上。下一篇介绍如何避免SQL的索引失效。

他叫阿来
关注
专栏目录
mysql索引和explain的详解
12-14
MySQL中的索引是一种为了加速数据查询的特殊数据结构，它允许数据库系统快速定位到数据行。索引原理基于存储引擎的...根据业务需求和数据特点，合理设计索引并利用`EXPLAIN`进行查询分析，可以有效地优化数据库的性能。
mysql not exists 效率高_mysql not in、left join、IS NULL、NOT EXISTS 效率问题记录
weixin_36072709的博客
02-01 205
语句一：select count(*) from A where A.a not in (select a from B)语句二：select count(*) from A left join B on A.a = B.a where B.a is null语句三：select count(*) from A where not exists (select a from B where A.a...
mysql not exists无效_分析MySQL中哪些情况下数据库索引会失效
weixin_34675395的博客
01-27 909
要想分析MySQL查询语句中的相关信息，如是全表查询还是部分查询，就要用到explain.一、explain用法：explain +查询语句。id：查询语句的序列号，上面图片中只有一个select 语句，所以只会显示一个序列号。如果有嵌套查询，如下select_type：表示查询类型，有以下几种simple:简单的 select (没有使用 union或子查询)primary:最外层的 sel...
mysql中not exists用法_not exists用法
热门推荐
weixin_34438539的博客
01-19 4万+
not exists是sql中的一个语法，常用在子查询和主查询之间，用于条件判断，根据一个条件返回一个布尔值，从而来确定下一步操作如何进行，not exists也是exists或in的对立面。not exists 是exists的对立面，所以要了解not exists的用法，我们首先了解下exists、in的区别和特点：exists : 强调的是是否返回结果集，不要求知道返回什么, 比如：sele...
【mysql】一 where not exists 语句用于“按条件查询没有记录才新增”
百物易用是苏生
03-25 1万+
【mysql】 where not exists 语句用于“按条件查询没有记录才新增”需求：实现sql语句测试：（version mysql 5.7.18）重点：from dual 的使用测试 变种update 无法配合 where not exist还有if not exist 是写sql存储过程方法的 需求： 多机器分布式环境下，某列要去重插入，但本身就带有重复项，无法建立唯一索引去inser...
Explain详解与索引优化
03-17
在SQL查询语句前加上"Explain"关键字，MySQL会返回关于查询如何执行的信息，包括数据表的扫描方式、使用的索引、预计的行数等。这些信息有助于我们分析查询的性能瓶颈，并找到可能的优化策略。 "Explain"的输出主要...
Mysql实验之使用explain分析索引的走向
09-09
在本实验中，我们将通过使用`EXPLAIN`关键字来分析不同查询语句如何利用创建的索引来执行，以理解MySQL的索引规则，并学习如何优化SQL语句。 首先，实验涉及的表`qz_users`包含了如`uid`、`user_name`、`real_name`...
MySQL索引优化Explain详解
01-21
找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL...
MySQL NOT EXISTS优化的一个案例
刀刀的专栏
10-22 2万+
原始语句：SELECT * FROM dcf_account.t_posting_transaction t1 WHERE NOT EXISTS ( SELECT * FROM dcf_loan.t_account_posting_detail t2
MySQL Explain详解
孟庆云的博客
04-12 470
Explain命令用于查看一个这些SQL语句的执行计划，信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra 1 id：选择标识符 这是SELECT的查询序列号 1.1 执行顺序 id相同：执行顺序由上至下； id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行 有相同也有不同...
MySQL常用DDL语法总结
他叫阿来
02-13 6779
文章目录前言一、DDL语句简述二、数据库操作三、表操作总结 前言 本篇主要总结MySQL的数据库操作，表操作方面的常用DDL(Data Definition Language)语句。 一、DDL语句简述 DDL是数据定义语言的缩写，简单来说就是对数据库内部的对象进行创建、删除、修改等操作的语句。它和DML(Data Manipulation Language)语句最大的区别是DML只是对表内部的数据进行操作，而不涉及表的定义、结构的修改。 二、数据库操作 -- 创建数据库 create database
MySQL的undo log 与MVCC
他叫阿来
11-27 1785
MVCC（Multi Version Concurrency Control）被称为多版本控制，是指在数据库中为了实现高并发的数据访问，对数据进行多版本处理，并通过版本链保证事务能看到自己应该看到的数据版本。 InnoDB 的**行数据有多个版本**，每个数据版本有自己的 row trx_id，每个事务或者语句有自己的一致性视图。普通查询语句是一致性读(快照读)，一致性读会根据 row trx_id 和一致性视图确定数据版本的可见性。
MySQL线上死锁案例分析
他叫阿来
12-13 1398
生产环境mysql死锁排查
InnoDB的B+树索引（一）
他叫阿来
12-04 1196
回到我们最开始的问题，一条记录在 ``B+树``中是如何进行查找的？只要分为两大步骤： 1. 找到该条记录所在的`页` - 从根节点出发，通过c1的值跟根页面中的目录项中的主键值进行比较，确认下一层级**内节点**的页号 - 在内节点中再次比较主键值 - 重复步骤2，直到找到叶子节点 2. 在叶子节点中找到该条记录 - 通过二分法确定该记录所在的槽。 - 通过记录的next_record属性遍历该槽所在的组中的各个记录。 假设所有存放用户记录的叶子节点数据页可以存放100条用户记录，存放目录项记
MySQL的Redo Log跟Binlog
他叫阿来
11-23 1128
Redo Log和Binlog是MySQL日志系统中非常重要的两种机制，也有很多相似之处，本文主要介绍两者细节和区别
MySQL集群架构
最新发布
他叫阿来
05-19 960
在集群架构设计时，主要遵从下面三个维度 - 可用性 - 扩展性 - 一致性 **可用性设计** - 站点高可用，冗余站点 - 服务高可用，冗余服务 - 数据高可用，冗余数据
InnoDB存储引擎中的锁
他叫阿来
11-29 897
如果事务之间涉及到相同的数据项时，会使用排他锁，或叫互斥锁，先进入的事务独占数据项以后，其他事务被阻塞，等待前面的事务释放锁。上面的查询操作，对account表中的id = 1的记录增加了共享锁，其他事务依然可以查到该账户的余额，但是不能对账户余额进行修改，在某种场景下能够满足业务的需求。规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在在等待。，写操作针对的是最新版本的记录，读记录的历史版本和改动记录的最新版本本身并不冲突，也就是采用。
MySQL权限管理
他叫阿来
02-19 888
mysql创建账号 mysql赋予、更改账号权限 mysql修改用户密码 mysql删除用户
Mysql索引优化与EXPLAIN技巧深度解析
本篇文章主要针对MySQL数据库设计与优化进行深入探讨，特别关注于索引策略和查询优化。索引在MySQL中扮演着关键角色，它通过算法如二叉树(BTREE)和哈希算法，对数据进行预排序，提升查询效率。BTREE是最常用的索引...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值