mysql12-索引

索引用于快速找出在某个列中有一特定值的行。
不使用索引，MySQL必须从第1条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间越多。
如果表中查询的列有一个索引，MySQL能快速到达某个位置去搜寻数据文件，而不必查看所有数据。

1、索引简介

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。

1、索引的含义和特点

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询速度的最佳途径。

索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。

MySQL中索引的存储类型有两种：BTREE和HASH
- MyISAM和InnoDB存储引擎只支持BTREE索引。
- MEMORY和HEAP存储引擎可以支持HASH和BTREE索引。

索引的优点:
- (1)通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
- (2)可以大大加快数据的查询速度，这也是创建索引的最主要的原因。
- (3)在实现数据的参考完整性方面，可以加速表和表之间的连接。
- (4)在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。
索引的缺点:
- (1)创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
- (2)索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
- (3)当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

2、索引的分类

1、普通索引和唯一索引

普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

2、单列索引和组合索引

单列索引即一个索引只包含一个列，一个表可以有多个单列案引。
组合索引指在表的多个字段组合上创建的素引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。

3、全文索引

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。
全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。
MySQL中只有MyISAM存储引擎支持全文索引。

4、空间索引

空间索引是对空间数据类型的字段建立的索引， MySQL中的空间数据类型有4种，分别是：GEOMETRY、POINT、LINESTRING和POLYGON，
MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。
创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

3、索引的设计原则

索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要。
(1)索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能，因为当表中的数据更改的同时，索引也会进行调整和更新。
(2)避免对经常更新的表添加过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
(3)数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
(4)在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引。如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度。
(5)当某列具有唯一性时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
(6)在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

2、创建索引

创建索引的方式：
- 使用CREATE TABLE语句在创建表时指定索引列。
- 使用ALTER TABLE语句在存在的表上创建索引。
- 使用CREATE INDEX语句在已存在的表上添加索引。

1、创建表时创建索引

使用CREATE TABLE创建表时，除了可以定义列的数据类型，还可以定义主键约束、外键约束或者唯一性约束，而不论创建那种约束，在定义约束的同时相当于在指定列上创建了一个索引。

创建表的时候创建索引，基本语法格式如下：

CREATE TABLE <表名>(
    字段名1 数据类型 [列级别约束条件] [默认值],
    字段名2 数据类型 [列级别约束条件] [默认值],
    ……
    [表级别约束条件],
    [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name1 [length][, col_name2 [length], ...]) [ASC | DESC]    --创建索引
);

- UNIQUE、FULLTEXT和SPATIAL：可选参数，分别表示唯一索引、全文索引和空间索引。
- INDEX与KEY：同义词，两者作用相同，用来指定创建索引。
- index_name：可选参数，索引的名称，如果不指定， MySQL默认值是col_name(索引字段)
- col_name：需要创建索引的字段名，该字段名必须是数据表中的列。
- length：可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度。
- ASC和DESC：使用升序或者降序的索引值存储。

1、创建单列索引

单列索引是在数据表中的某一个字段上创建的索引，一个表中可以创建多个单列索引。
创建单列索引，基本语法格式如下：

CREATE TABLE <表名>(
    字段名1 数据类型 [列级别约束条件] [默认值],
    字段名2 数据类型 [列级别约束条件] [默认值],
    ……
    [表级别约束条件],
    [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name1 [length]) [ASC | DESC]    --创建索引
);

示例：

--创建数据表
create table indx1(
    id int,
    name char(25),
    sex enum('男','女'),
    index(id)
);

mysql> show create table indx1\G
*************************** 1. row ***************************
       Table: indx1
Create Table: CREATE TABLE `indx1` (
  `id` int(11) DEFAULT NULL,
  `name` char(25) DEFAULT NULL,
  `sex` enum('男','女') DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> explain select * from indx1 where id != 10\G    --explain语句查看SQL语句是否在使用了索引
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: indx1
   partitions: NULL
         type: ALL
possible_keys: Indexid
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

- (1)select_type行指定所使用的SELECT查询类型，这里值为SIMPLE，表示简单的SELECT，不使用UNION或子查询。其他可有：PRIMARY、UNION、SUBQUERY等。
- (2)table行指定数据库读取的数据表的名字，它们按被读取的先后顺序排列。
- (3)type行指定了本数据表与其他数据表之间的关联关系，可能的取值有system、const、eq_ref、ref、range、index和All
- (4)possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引。
- (5)key行是MySQL实际选用的索引。
- (6)keylen行给出索引按字节计算的长度， keylen数值越小，表示越快。
- (7)ref行给出了关联关系中另一个数据表里的数据列的名字。
- (8)rows行是MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数。
- (9)extra行提供了与关联操作有关的信息。

2、创建组合索引

组合索引是在多个字段上创建一个索引。
组合索引可起几个索引的作用，但是使用时并不是随便查询哪个字段都可以使用索引，而是遵从“最左前缀”：利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。

创建组合索引，基本语法格式如下：

CREATE TABLE <表名>(
    字段名1 数据类型 [列级别约束条件] [默认值],
    字段名2 数据类型 [列级别约束条件] [默认值],
    ……
    [表级别约束条件],
    [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name1 [length], col_name2 [length], ...) [ASC | DESC]    --创建索引
);

示例：

例如这里由id、name和age3个字段构成的索引，索引行中按id、name、age的顺序存放，索引可以搜索下面字段组合: (id, name, age) 、(id, name)或者id。如果列不构成索引最左面的前，MySQL不能使用局部索引，如(age)或者(name,age)组合则不能使用索引查询。

--创建表
create table indx2(
    id int,
    name char(25),
    age int(3),
    sex enum('男','女'),
    index Indexname(id,name(10),age)
);

mysql> show create table indx2\G
*************************** 1. row ***************************
       Table: indx2
Create Table: CREATE TABLE `indx2` (
  `id` int(11) DEFAULT NULL,
  `name` char(25) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `sex` enum('男','女') DEFAULT NULL,
  KEY `Indexname` (`id`,`name`(10),`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> explain select * from indx2 where id != 100 and name = 'hh'\G    --触发索引

mysql> explain select * from indx2 where name = 'hh' and age = 100\G    --不触发索引

3、普通索引

最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度。
普通索引就是创建索引没有UNIQUE、FULLTEXT、SPATIAL关键字做修饰。

创建普通索引，基本语法格式如下：

CREATE TABLE <表名>(
    字段名1 数据类型 [列级别约束条件] [默认值],
    字段名2 数据类型 [列级别约束条件] [默认值],
    ……
    [表级别约束条件],
    [INDEX | KEY] [index_name] (col_name1 [length][, col_name2 [length], ...]) [ASC | DESC]    --创建索引
);

示例：

create table indx3(
    id int,
    name char(25),
    age int(3),
    sex enum('男','女'),
    index Indexname(id)
);

mysql> show create table indx3\G
*************************** 1. row ***************************
       Table: indx3
Create Table: CREATE TABLE `indx3` (
  `id` int(11) DEFAULT NULL,
  `name` char(25) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `sex` enum('男','女') DEFAULT NULL,
  KEY `Indexname` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

4、创建唯一索引

创建唯一索引的主要原因是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表。
唯一索引与普通索引类似，不同的就是：
- 索引列的值必须唯一，但允许有空值。
- 如果是组合索引，则列值的组合必须唯一。

创建唯一索引，基本语法格式如下：

CREATE TABLE <表名>(
    字段名1 数据类型 [列级别约束条件] [默认值],
    字段名2 数据类型 [列级别约束条件] [默认值],
    ……
    [表级别约束条件],
    UNIQUE [INDEX | KEY] [index_name] (col_name1 [length][, col_name2 [length], ...]) [ASC | DESC]    --创建索引
);

示例：

create table indx4(
    id int,
    name char(25),
    age int(3),
    sex enum('男','女'),
    unique index Indexname(id)
);

mysql> show create table indx4\G
*************************** 1. row ***************************
       Table: indx4
Create Table: CREATE TABLE `indx4` (
  `id` int(11) DEFAULT NULL,
  `name` char(25) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `sex` enum('男','女') DEFAULT NULL,
  UNIQUE KEY `Indexname` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

5、创建全文索引

FULLTEXT(全文索引)可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列创建索引。
索引总是对整个列进行，不支持局部(前缀)索引。
全文索引非常适合于大型数据集,对于小的数据集,它的用处比较小。

创建全文索引，基本语法格式如下：

CREATE TABLE <表名>(
    字段名1 数据类型 [列级别约束条件] [默认值],
    字段名2 数据类型 [列级别约束条件] [默认值],
    ……
    [表级别约束条件],
    FULLTEXT [INDEX | KEY] [index_name] (col_name1 [length][, col_name2 [length], ...]) [ASC | DESC]    --创建索引
);

示例：

create table indx5(
    id int,
    name char(25),
    age int(3),
    sex enum('男','女'),
    info varchar(255),
    fulltext index Indexname(info)
)ENGINE = MyISAM;

mysql> show create table indx5\G
*************************** 1. row ***************************
       Table: indx5
Create Table: CREATE TABLE `indx5` (
  `id` int(11) DEFAULT NULL,
  `name` char(25) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `sex` enum('男','女') DEFAULT NULL,
  `info` varchar(255) DEFAULT NULL,
  FULLTEXT KEY `Indexname` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

6、创建空间索引

空间索引必须在MyISAM类型的表中创建，且空间类型的字段必须为非空。
注意创建时指定空间类型字段值的非空约束，并且表的存储引擎为MyISAM。

创建空间索引，基本语法格式如下：

CREATE TABLE <表名>(
    字段名1 数据类型 [列级别约束条件] [默认值],
    字段名2 数据类型 [列级别约束条件] [默认值],
    ……
    [表级别约束条件],
    SPATIAL [INDEX | KEY] [index_name] (col_name1 [length][, col_name2 [length], ...]) [ASC | DESC]    --创建索引
);

示例：

create table indx6(
    id int,
    name char(25),
    age int(3),
    sex enum('男','女'),
    g geometry not null,
    spatial index Indexname(g)
)ENGINE = MyISAM;

mysql> show create table indx6\G
*************************** 1. row ***************************
       Table: indx6
Create Table: CREATE TABLE `indx6` (
  `id` int(11) DEFAULT NULL,
  `name` char(25) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `sex` enum('男','女') DEFAULT NULL,
  `g` geometry NOT NULL,
  SPATIAL KEY `Indexname` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

2、在已经存在的表上创建索引

在已经存在的表中创建索引，可以使用ALTER TABLE语句或者CREATE INDEX语句。

使用ALTER TABLE语句创建索引，基本语法格式如下：

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] 
[INDEX | KEY] [index_name] (col_name [length], ...) [ASC | DESC]

使用CREATE INDEX创建索引，基本语法格式如下：

CREATE [UNIQUE | FULLTEXT | SPATIAL] 
INDEX index_name ON table_name (col_name [length], ...) [ASC | DESC]

示例1：

create table indx7(
    id int,
    name char(25),
    age int(3),
    sex enum('男','女')
);

mysql> ALTER TABLE indx7 ADD INDEX indexname1(name(10));

mysql> show index from indx7\G                 --查看表中的索引信息
*************************** 1. row ***************************
        Table: indx7
   Non_unique: 1
     Key_name: indexname1
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: 10
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:

- (1)Table表示创建索引的表。
- (2)Non_unique表示索引非唯一，1代表是非唯一索引，0代表唯一索引。
- (3)Key_name表示索引的名称。
- (4)Seq_in_index表示该字段在索引中的位置，单列索引该值为1，组合索引为每个字段在索引定义中的顺序。
- (5)Column_name表示定义索引的列字段。
- (6)Sub_part表示索引的长度。
- (7)Null表示该字段是否能为空值。
- (8)Index_type表示索引类型。

示例2：

mysql> ALTER TABLE indx7 ADD INDEX indexname1(name(10));         --创建普通单列索引
mysql> CREATE INDEX indexname2 ON indx7(name(10);

mysql> ALTER TABLE indx7 ADD INDEX indexname1(id,name(20));      --创建普通组合索引
mysql> CREATE INDEX indexname2 ON indx7(id,name(20));

mysql> ALTER TABLE indx7 ADD UNIQUE INDEX indexname1(id);        --创建唯一索引
mysql> CREATE UNIQUE INDEX indexname2 ON indx7(id);

mysql> ALTER TABLE indx7 ADD FULLTEXT INDEX indexname1(name);    --创建全文索引
mysql> CREATE FULLTEXT INDEX indexname2 ON indx7(name);

3、删除索引

MySQL中删除索引使用ALTER TABLE或者DROP INDEX语句，两者的功能相同。
DROP INDEX语句在内部被映射到一个ALTER TABLE语句中。

添加AUTO_INCREMENT约束字段的唯一索引不能被删除。
删除表中的列时，如果要删除的列是索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

使用ALTER TABLE删除索引，基本语法格式如下：

ALTER TABLE table_name DROP INDEX index_name;

使用DROP INDEX语句删除索引，基本语法格式如下：

DROP INDEX index_name ON table_name;

示例：

mysql> show create table indx7\G          --查看索引信息

mysql> drop index indexname1 on indx7;    --删除索引

4、explain详解

使用explain命令查看SQL语句的执行计划，查看SQL语句有没有使用索引，有没有做全表扫描。

1、索引信息

假设有两张表cell和project：

在这两张表上分别创建单索引和组合索引

mysql> ALTER TABLE cell ADD INDEX index_1 (id);
mysql> ALTER TABLE cell ADD INDEX index_2 (num(20));

mysql> ALTER TABLE project ADD INDEX index_3 (id,num(20));

查看索引的信息

mysql> show index from cell;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| cell  |          0 | PRIMARY  |            1 | id          | A         |      623525 |     NULL | NULL   |      | BTREE      |         |               |
| cell  |          1 | index_1  |            1 | id          | A         |      623525 |     NULL | NULL   |      | BTREE      |         |               |
| cell  |          1 | index_2  |            1 | num         | A         |        4110 |       20 | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> show create table cell\G
*************************** 1. row ***************************
       Table: cell
Create Table: CREATE TABLE `cell` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` varchar(32) DEFAULT NULL,
  `SetKey` varchar(32) DEFAULT NULL,
  `CreateTime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_1` (`id`),        --单列索引
  KEY `index_2` (`num`(20))    --单列索引
) ENGINE=InnoDB AUTO_INCREMENT=655351 DEFAULT CHARSET=utf8

mysql> show index from project;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| project |          0 | PRIMARY  |            1 | id          | A         |        4070 |     NULL | NULL   |      | BTREE      |         |               |
| project |          1 | index_3  |            1 | id          | A         |        4070 |     NULL | NULL   |      | BTREE      |         |               |
| project |          1 | index_3  |            2 | num         | A         |        4070 |       20 | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> show create table project\G
*************************** 1. row ***************************
       Table: project
Create Table: CREATE TABLE `project` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` varchar(32) DEFAULT NULL,
  `CreateTime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_3` (`id`,`num`(20))    --组合索引
) ENGINE=InnoDB AUTO_INCREMENT=4096 DEFAULT CHARSET=utf8

2、explain的使用

mysql> select * from cell where num = 'PC_2_201912271605180';
mysql> explain select * from cell where num = 'PC_2_201912271605180';
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | cell  | NULL       | ref  | index_2       | index_2 | 63      | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+

1、id列

id列的编号是select的序列号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的。
- id相同时，执行顺序由上至下。
- 如果是子查询，id的序号会递增。
- id序号越大优先级越高，越先被执行。id相同，可以认为是一组，从上往下顺序执行。id为NULL最后执行

2、select_type列

表示查询中每个select子句的类型
只要知道了某个select子句的select_type属性，就知道了这个select子句在整个大查询中扮演了一个什么角色。
大查询的组成
- 大查询由几个UNION连接的小查询组成。例如，小查询1 UNION 小查询2 UNION ...
- 大查询也可以由最外层查询和子查询组成。例如，select * from table_name1 where id in (select id from table_name2)
- 小查询是UNION连接的查询语句。
- 小查询和子查询可以相互套嵌。

1、SIMPLE：简单select，查询语句不使用UNION或子查询。

mysql> explain select * from cell where id = '88888';
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys   | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | cell  | NULL       | const | PRIMARY,index_1 | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+

2、PRIMARY：包含UNION或者子查询的大查询，该大查询语句由几个小查询语句组成，最外层（最左边）的select被标记为PRIMARY。

mysql> explain select * from cell where id = '88888' UNION select * from cell where id = '99999';
+------+--------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+-----------------+
| id   | select_type  | table      | partitions | type  | possible_keys   | key     | key_len | ref   | rows | filtered | Extra           |
+------+--------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+-----------------+
|  1   | PRIMARY      | cell       | NULL       | const | PRIMARY,index_1 | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
|  2   | UNION        | cell       | NULL       | const | PRIMARY,index_1 | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL            | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+-----------------+

3、UNION：包含UNION的大查询，该大查询语句由几个UNION连接的小查询语句组成，除了最左边的select都被标记为UNION。

4、UNION RESULT：MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的select被标记为UNION RESULT。

5、SUBQUERY：子查询中的第一个SELECT，不依赖于外部查询（即子查询语句不使用外层查询的表）。

mysql> explain select * from cell where num = (select num from project where id = 888);
+----+-------------+---------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys   | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | cell    | NULL       | ref   | index_2         | index_2 | 63      | const |   14 |   100.00 | Using where |
|  2 | SUBQUERY    | project | NULL       | const | PRIMARY,index_3 | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
+----+-------------+---------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+

6、DEPENDENT SUBQUERY：子查询中的第一个SELECT，依赖于外部查询（即子查询语句使用外层查询的表）。

mysql> explain select * from cell where num = (select num from project where id = 888 and cell.id = 117981);
+----+--------------------+---------+------------+-------+-----------------+---------+---------+-------+--------+----------+-------------+
| id | select_type        | table   | partitions | type  | possible_keys   | key     | key_len | ref   | rows   | filtered | Extra       |
+----+--------------------+---------+------------+-------+-----------------+---------+---------+-------+--------+----------+-------------+
|  1 | PRIMARY            | cell    | NULL       | ALL   | NULL            | NULL    | NULL    | NULL  | 623525 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | project | NULL       | const | PRIMARY,index_3 | PRIMARY | 4       | const |      1 |   100.00 | NULL        |
+----+--------------------+---------+------------+-------+-----------------+---------+---------+-------+--------+----------+-------------+

7、DEPENDENT UNION：包含UNION的大查询，该大查询语句由几个小查询语句组成，如果有小查询依赖于外层查询，除了最左边小查询，其余的小查询的select都被标记为DEPENDENT UNION。

mysql> explain select * from cell where num = (select num from project where id = 888 UNION select num from project where cell.id = 117981);
+------+----------------------+------------+------------+-------+-----------------+---------+---------+-------+--------+----------+-----------------+
| id | select_type          | table      | partitions | type  | possible_keys   | key     | key_len | ref   | rows   | filtered | Extra           |
+------+----------------------+------------+------------+-------+-----------------+---------+---------+-------+--------+----------+-----------------+
|  1   | PRIMARY              | cell       | NULL       | ALL   | NULL            | NULL    | NULL    | NULL  | 623525 |   100.00 | Using where     |
|  2   | UNCACHEABLE SUBQUERY | project    | NULL       | const | PRIMARY,index_3 | PRIMARY | 4       | const |      1 |   100.00 | NULL            |
|  3   | DEPENDENT UNION      | project    | NULL       | ALL   | NULL            | NULL    | NULL    | NULL  |   4070 |   100.00 | Using where     |
| NULL | UNION RESULT         | <union2,3> | NULL       | ALL   | NULL            | NULL    | NULL    | NULL  |   NULL |     NULL | Using temporary |
+----+----------------------+------------+------------+-------+-----------------+---------+---------+-------+--------+----------+-----------------+

8、DERIVED：对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的select被标记为DERIVED。

mysql> explain select * from  (select count(*) from project where num like 'PC_2_20190819%' group by id) as pj;
+----+-------------+------------+------------+-------+-----------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys   | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+-----------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL            | NULL    | NULL    | NULL |  452 |   100.00 | NULL        |
|  2 | DERIVED     | project    | NULL       | index | PRIMARY,index_3 | PRIMARY | 4       | NULL | 4070 |    11.11 | Using where |
+----+-------------+------------+------------+-------+-----------------+---------+---------+------+------+----------+-------------+

9、MATERIALIZED：当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的select被标记为MATERIALIZED。

mysql> explain select * from cell where num in (select num from project where id < 888);
+----+--------------+-------------+------------+-------+-----------------+---------+---------+-----------------+------+----------+-------------+
| id | select_type  | table       | partitions | type  | possible_keys   | key     | key_len | ref             | rows | filtered | Extra       |
+----+--------------+-------------+------------+-------+-----------------+---------+---------+-----------------+------+----------+-------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL   | NULL            | NULL    | NULL    | NULL            | NULL |   100.00 | Using where |
|  1 | SIMPLE       | cell        | NULL       | ref   | index_2         | index_2 | 63      | <subquery2>.num |  151 |   100.00 | Using where |
|  2 | MATERIALIZED | project     | NULL       | range | PRIMARY,index_3 | PRIMARY | 4       | NULL            |  887 |   100.00 | Using where |
+----+--------------+-------------+------------+-------+-----------------+---------+---------+-----------------+------+----------+-------------+

mysql> explain select * from cell where num in (select num from project where id = 888);
+----+-------------+---------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys   | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | project | NULL       | const | PRIMARY,index_3 | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | cell    | NULL       | ref   | index_2         | index_2 | 63      | const |   14 |   100.00 | Using where |
+----+-------------+---------+------------+-------+-----------------+---------+---------+-------+------+----------+-------------+

3、table列

表示该select访问的是哪个表。
注意：当from子句中有子查询时，table列是<derivenN>格式，表示当前查询依赖id=N的查询，于是先执行id=N的查询。当有union时，UNION RESULT的table列的值为<union1,2>，1和2表示参与union的select行id。

4、partitions列

explain partitions语句比explain语句多了个partitions字段，如果查询是基于分区表的话，会显示查询将访问的分区。

mysql> explain partitions select * from cell where id = '88888';
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys   | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | cell  | NULL       | const | PRIMARY,index_1 | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+

5、type列

表示关联类型、连接类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
查询性能从最差到最优分别为：ALL < index < range < ref < eq_ref < const < system。
一般来说，得保证查询达到range级别，最好达到ref

1、ALL：全表扫描（即没有命中索引）。如果第一个表没有标记为const，这通常不太好，甚至是很糟糕。通常，可以通过添加索引来避免ALL。

mysql> explain select * from cell where SetKey = '0354_I016122348';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | cell  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 623525 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

2、index：索引覆盖，扫描全部的索引记录（索引树）。通常比ALL快一些，因为索引的大小通常小于表数据。

这有两种方式：
- 如果索引是查询的覆盖索引，并且可以用来满足表中所需的所有数据，则只扫描索引树。Extra列显示Using index。
- 全表扫描是通过从索引中读取数据以按照索引顺序查找数据行来执行的。Extra列不显示Using index。
index是扫描全表索引。当且仅当查询使用的是单个索引（这里不是仅指单列索引）时，MySQL可以使用这种连接类型。
all是扫描全表记录，从硬盘中读取。

mysql> explain select id from cell;        --id属于单列索引
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | cell  | NULL       | index | NULL          | index_1 | 4       | NULL | 623525 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

mysql> explain select id from project;    --id属于组合索引
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | project | NULL       | index | NULL          | index_3 | 67      | NULL | 4070 |   100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

3、range：只检索给定范围内的行，使用索引选择行。

输出行中的key列指示使用哪个索引。key_len包含所使用的最长的密钥部分。对于这种类型，ref列是NULL。
当使用任意的=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、或者IN()操作符与常量进行比较时，type列被标记为range。

mysql> explain select * from project where id > 999;
+----+-------------+---------+------------+-------+-----------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys   | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+-----------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | project | NULL       | range | PRIMARY,index_3 | PRIMARY | 4       | NULL | 2035 |   100.00 | Using where |
+----+-------------+---------+------------+-------+-----------------+---------+---------+------+------+----------+-------------+

4、ref：如果连接仅使用索引的最左前缀，或者该列不是PRIMARY key或UNIQUE索引(换句话说，如果连接不能根据条件只读出一行记录)，则type列被标记为ref。

如果所使用的键只匹配少数行，那么这是一种很好的连接类型。
Ref可以用于使用= 或者<=>操作符。

mysql> explain select * from cell where num = 'PC_2_20191227160518';       --num是普通索引
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | cell  | NULL       | ref  | index_2       | index_2 | 63      | const |  227 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+

mysql> explain select * from project,cell where project.num = cell.num;    --cell.num是普通索引
+----+-------------+---------+------------+------+---------------+---------+---------+----------------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref            | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+---------+---------+----------------+------+----------+-------------+
|  1 | SIMPLE      | project | NULL       | ALL  | NULL          | NULL    | NULL    | NULL           | 4070 |   100.00 | Using where |
|  1 | SIMPLE      | cell    | NULL       | ref  | index_2       | index_2 | 63      | hh.project.num |  151 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+---------+---------+----------------+------+----------+-------------+

5、eq_ref：该列是PRIMARY key或UNIQUE索引，根据条件将只读出一行记录，则type列被标记为eq_ref。

mysql> explain select * from project,cell where project.id = cell.id;    --cell.id是主键
+----+-------------+---------+------------+--------+-----------------+---------+---------+---------------+------+----------+-------+
| id | select_type | table   | partitions | type   | possible_keys   | key     | key_len | ref           | rows | filtered | Extra |
+----+-------------+---------+------------+--------+-----------------+---------+---------+---------------+------+----------+-------+
|  1 | SIMPLE      | project | NULL       | ALL    | PRIMARY,index_3 | NULL    | NULL    | NULL          | 4070 |   100.00 | NULL  |
|  1 | SIMPLE      | cell    | NULL       | eq_ref | PRIMARY,index_1 | PRIMARY | 4       | hh.project.id |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+--------+-----------------+---------+---------+---------------+------+----------+-------+

6、const：最多有一个匹配的行，在查询的开始读取。

因为只有一行，所以这一行中的列的值可以被优化器的其余部分视为常量。Const表非常快，因为它们只被读取一次。
当将PRIMARY KEY或UNIQUE索引的所有部分与常量进行比较时，则type列被标记为const。

mysql> explain select * from cell where id = 66;
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys   | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | cell  | NULL       | const | PRIMARY,index_1 | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+

7、system：该表只有一行(=系统表)。这是const连接类型的特殊情况。

6、possible_keys列

possible_keys列显示查询可能使用哪些索引来查找。
如果该列为NULL(或在json格式的输出中未定义)，则没有相关索引。在这种情况下，可以通过检查WHERE子句来检查它是否引用了一些适合建立索引的列，从而提高查询的性能。如果是，则创建一个适当的索引，并再次使用EXPLAIN检查查询。

7、key列

key列显示mysql实际使用哪个索引来优化对该表的访问。

8、key_len列

key_len列表示MySQL决定使用的键的长度。
key_len的值能够确定MySQL实际使用的多部分键的多少部分。
如果key列为NULL, key_len列也为NULL。
由于键存储格式的原因，对于可以为NULL的列，键长度比NOT NULL列的长度大一个。

9、ref列

ref列显示哪些列或常量与键列中指定的索引进行比较，以从表中选择行。
如果值是func，则使用的值是某个函数的结果。要查看哪个函数，请使用EXPLAIN后面的SHOW WARNINGS查看扩展的EXPLAIN输出。这个函数实际上可能是一个运算符，比如算术运算符。

10、rows列

rows列表示MySQL认为执行查询时必须检查的行数。
对于InnoDB表，这个数字是一个估计，可能并不总是准确的。

11、filtered列

筛选的列指示表条件筛选的表行的估计百分比。
最大值是100，这意味着没有对行进行过滤。值从100下降表示过滤量增加。
显示已检查的估计行数=rows * filtered。例如，如果rows为1000，并且过滤的行数为50.00(50%)，则要与下表合并的行数为1000 * 50% = 500。

12、Extra列

这一列包含关于MySQL如何解析查询的附加信息。

3、命中索引

1、使用like

一定不能将通配符放到最前面。
靠近前面的位置尽量不要使用通配符。

mysql> select * from cell where num like 'PC201912271%';
+--------+------------------+-----------------+---------------------+
| id     | num              | SetKey          | CreateTime          |
+--------+------------------+-----------------+---------------------+
| 627240 | PC20191227165340 | 0476_1180227282 | 2019-12-27 17:16:42 |
+--------+------------------+-----------------+---------------------+

mysql> explain select * from cell where num like 'PC201912271%';       --命中索引
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | cell  | NULL       | range | index_2       | index_2 | 63      | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
mysql> explain select * from cell where num like '%20191227165340';    --没有命中索引
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | cell  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 623299 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

2、使用函数

一定不能在查询列上做计算或使用函数。

ALTER TABLE cell ADD INDEX index_4 (CreateTime);

mysql> explain select * from cell where DATE_FORMAT(CreateTime,'%Y/%m/%d %H:%i:%s') = '2019/12/27 17:16:42';    --没有命中索引
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | cell  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 623299 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
mysql> explain select * from cell where CreateTime = DATE_FORMAT('2019/12/27 17:16:42','%Y-%m-%d %H:%i:%s');    --命中索引
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | cell  | NULL       | ref  | index_4       | index_4 | 6       | const |   25 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+

3、使用or

当or连接的条件中，有不是索引的列时，将不会使用索引。

mysql> explain select * from cell where id > 1000;
+----+-------------+-------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys   | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | cell  | NULL       | range | PRIMARY,index_1 | PRIMARY | 4       | NULL | 311649 |   100.00 | Using where |
+----+-------------+-------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+

mysql> explain select * from cell where id > 1000 or SetKey = '0478_1180233098';    --没有命中索引，SetKey列不是索引
+----+-------------+-------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys   | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | cell  | NULL       | ALL  | PRIMARY,index_1 | NULL | NULL    | NULL | 623299 |    40.00 | Using where |
+----+-------------+-------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
mysql> explain select * from cell where id > 1000 or num = 'PC_2_20180810085007';    --命中索引，num列是索引
+----+-------------+-------+------------+-------------+-------------------------+-----------------+---------+------+--------+----------+------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys           | key             | key_len | ref  | rows   | filtered | Extra                                          |
+----+-------------+-------+------------+-------------+-------------------------+-----------------+---------+------+--------+----------+------------------------------------------------+
|  1 | SIMPLE      | cell  | NULL       | index_merge | PRIMARY,index_1,index_2 | index_2,PRIMARY | 63,4    | NULL | 311771 |   100.00 | Using sort_union(index_2,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+-------------------------+-----------------+---------+------+--------+----------+------------------------------------------------+

4、使用不同的数据类型

当使用的数据类型不一致时，将不会使用索引。

mysql> explain select * from cell where num = 9999;    --num时字符型的，9999是整数型
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | cell  | NULL       | ALL  | index_2       | NULL | NULL    | NULL | 623299 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

5、使用不等于（!=）

当使用“!=”的时候，将不会使用索引。
注意：当要查询的字段是主键时，则“!=”将会使用索引

mysql> explain select * from cell where num = 'PC_2_20180810085007';     --命中索引
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | cell  | NULL       | ref  | index_2       | index_2 | 63      | const |  122 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
mysql> explain select * from cell where num != 'PC_2_20180810085007';    --没有命中索引，num列不是主键
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | cell  | NULL       | ALL  | index_2       | NULL | NULL    | NULL | 623299 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+

mysql> explain select * from cell where id = 1000;     --命中索引
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys   | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | cell  | NULL       | const | PRIMARY,index_1 | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+-----------------+---------+---------+-------+------+----------+-------+
mysql> explain select * from cell where id != 1000;    --命中索引，id列是主键
+----+-------------+-------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys   | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | cell  | NULL       | range | PRIMARY,index_1 | PRIMARY | 4       | NULL | 312648 |   100.00 | Using where |
+----+-------------+-------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+

6、使用大于（>）

当使用“>”的时候，将不会使用索引。
注意：当要查询的字段是主键或要使用索引是整数时，则“>”将会使用索引

mysql> explain select * from cell where num > 'PC_2_20180810085007';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | cell  | NULL       | ALL  | index_2       | NULL | NULL    | NULL | 623299 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
mysql> explain select * from cell where id > 999;    --id是主键
+----+-------------+-------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys   | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | cell  | NULL       | range | PRIMARY,index_1 | PRIMARY | 4       | NULL | 311649 |   100.00 | Using where |
+----+-------------+-------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+

7、使用order by

使用order by的时候，将不会使用索引。
注意：如果对主键排序，将会使用索引。

mysql> explain select * from cell order by id DESC;       --命中索引，id是主键
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | cell  | NULL       | index | NULL          | PRIMARY | 4       | NULL | 623299 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------+

mysql> explain select num from cell order by num DESC;    --命中索引，num是普通索引
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | cell  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 623299 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------+

8、使用组合索引

组合索引，要使用“最左前缀”。

posted @ 2021-08-19 14:45 麦恒阅读(62) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

麦恒

mysql12-索引

1、索引简介

1、索引的含义和特点

2、索引的分类

1、普通索引和唯一索引

2、单列索引和组合索引

3、全文索引

4、空间索引

3、索引的设计原则

2、创建索引

1、创建表时创建索引

1、创建单列索引

2、创建组合索引

3、普通索引

4、创建唯一索引

5、创建全文索引

6、创建空间索引

2、在已经存在的表上创建索引

3、删除索引

4、explain详解

1、索引信息

2、explain的使用

1、id列

2、select_type列

3、table列

4、partitions列

5、type列

6、possible_keys列

7、key列

8、key_len列

9、ref列

10、rows列

11、filtered列

12、Extra列

3、命中索引

1、使用like

2、使用函数

3、使用or

4、使用不同的数据类型

5、使用不等于（!=）

6、使用大于（>）

7、使用order by

8、使用组合索引

公告