MySQL(九)性能优化之索引原理与实际场景测试

MySQL性能优化之索引原理与实际场景测试

• InnoDB存储引擎只支持BTree索引
• 顾名思义，B-tree索引使用B-tree的数据结构存储数据，不同的存储引擎以不同的方式使用B-Tree索引，比如MyISAM使用前缀压缩技术使得索引空间更小，而InnoDB则按照原数据格式存储，且MyISAM索引在索引中记录了对应数据的物理位置，而InnoDB则在索引中记录了对应的主键数值。B-Tree通常意味着所有的值都是按顺序存储，并且每个叶子页到根的距离相同。
• B-Tree索引驱使存储引擎不再通过全表扫描获取数据，而是从索引的根节点开始查找，在根节点和中间节点都存放了指向下层节点的指针，通过比较节点页的值和要查找值可以找到合适的指针进入下层子节点，直到最下层的叶子节点，最终的结果就是要么找到对应的值，要么找不到对应的值。整个B-tree树的深度和表的大小直接相关。
• • 全键值匹配：和索引中的所有列都进行匹配，比如查找姓名为zhang san，出生于1982-1-1的人
• • 匹配最左前缀：和索引中的最左边的列进行匹配，比如查找所有姓为zhang的人
• • 匹配列前缀：匹配索引最左边列的开头部分，比如查找所有以z开头的姓名的人
• • 匹配范围值：匹配索引列的范围区域值，比如查找姓在li和wang之间的人
• • 精确匹配左边列并范围匹配右边的列：比如查找所有姓为Zhang，且名字以K开头的人
• • 只访问索引的查询：查询结果完全可以通过索引获得，也叫做覆盖索引，比如查找所有姓为zhang的人的姓名

聚簇索引的叶子节点包含了行的全部数据，而节点页只包含了索引列，比如下图索引列 图2

MySQL索引种类：

BTREE：               B+树索引
HASH：                HASH索引
FULLTEXT：            全文索引
RTREE：               R树索引

B树索引：

假如查找id为33的数据，首先判断大小后去"根"找P2对应的枝节点，然后在"枝节点"又找到P1，最后找到在"叶子节点"内id为33页码，然后根据"数据页码"直接去数据行找数据。

B+树索引：

B+树索引就是在查找到id为33存在的"叶子节点"后，它可以通过图中的粉红色Q字母自动识别相邻的叶子存储的id号，下次再去查找时可以跳过"根"与"枝节点"直接去对应的叶子找到要找的id号。

B树索引类型：

• 聚集索引： 基于primary key自动生成的索引，效率极高，叶子节点存储的是真实一行一行的数据行数据
• 辅助（普通）索引： 基于普通列人为生成的索引，叶子节点存储排序数据行后生成的数据页页码号

辅助索引+聚集索引：

辅助索引生成的数据页码号值不再是单纯的数据页码号，而是数据行的聚集索引生成的ID号对应辅助索引数据页码号

辅助索引与聚集索引工作机制参考链接：https://www.jianshu.com/p/3cd3cec2e28c

• 也就是说生成的辅助索引叶子节点存储的不是全部数据，还有指向聚集索引生成的书签ID号
• 注：聚集索引也一样是生成ID号码，分为根节点、枝节点、叶子节点，聚集索引的叶子节点存储的是数据行的全部数据。

每个InnoDB的表都拥有一个索引，称之为聚集索引，此索引中存储着行记录，一般来说，聚集索引是根据主键生成的。为了能够获得高性能的查询、插入和其他数据库操作，理解InnoDB聚集索引是很有必要的。

聚集索引：

聚集索引按照如下规则创建：

• 1、当定义了主键后，InnoDB会利用主键来生成其聚集索引；
• 2、如果没有主键，InnoDB会选择一个非空的唯一索引来创建聚集索引；
• 3、如果这也没有，InnoDB会隐式的创建一个自增的列来作为聚集索引。

聚集索引整体是一个b+树，非叶子节点存放的是键值，叶子节点存放的是行数据，称之为数据页，这就决定了表中的数据也是聚集索引中的一部分，数据页之间是通过一个双向链表来链接的，上文说到B+树是一棵平衡查找树，也就是聚集索引的数据存储是有序的，但是这个是逻辑上的有序，但是在实际数据的物理存储上是，因为数据页之间是通过双向链表来连接，假如物理存储是顺序的话，那维护聚集索引的成本非常的高。

辅助索引：

除了聚集索引之外的索引都可以称之为辅助索引，与聚集索引的区别在于辅助索引的叶子节点中存放的是主键的键值。一张表可以存在多个辅助索引，但是只能有一个聚集索引，通过辅助索引来查找对应的航记录的话，需要进行两步，第一步通过辅助索引来确定对应的主键，第二步通过相应的主键值在聚集索引中查询到对应的行记录，也就是进行两次B+树搜索。相反通过辅助索引来查询主键的话，遍历一次辅助索引就可以确定主键了，也就是所谓的索引覆盖，不用回表（查询聚集索引）。

• **所谓遍历：**是指沿着某条搜索路线，依次对树中每个结点均做一次且仅做一次访问。
• 第一步通过辅助索引来确定对应的主键，第二步通过相应的主键值在聚集索引中查询到对应的行记录

如何解决非聚集索引二次查询的问题：

• 建立两列以上的索引，即可查询联合索引里的列的数据而不需要进行回表二次查询，如index(col1, col2)，执行下面的语句：

  select col1, col2 from t1 where col1 = '213';
  

因为联合索引的列包括了col1和col2，不需要查询别的列，所以不需要进行二次查询。

要注意使用联合索引需要满足最左侧索引的原则，也就是查询的时候如果where条件里面没有最左边的一到多列，索引就不会起作用。

通俗的讲讲最左索引原则吧:

• 假设创建了联合索引index(A,B,C)，那么其实相当于创建了如下三个组合索引：

  index(A,B,C)
  index(A,B)
  index(A)
  

• 这就是最左索引原则，就是从最左侧开始组合。

创建索引的是三种途径包括：

直接创建索引

• column(length)是取字段数据的前几个字符做索引

CREATE INDEX index_name ON table(column(length))

修改表结构的方式添加索引

• column(length)是取字段数据的前几个字符做索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

创建表的时候同时创建索引

• column(length)是取字段数据的前几个字符做索引

CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
)

删除索引

DROP INDEX index_name ON table

MySQL聚集索引：

**表示：**PRI

聚集索引原理：

• 聚簇索引的叶子节点包含了行的全部数据，而节点页只包含了索引列
• 每个InnoDB表都会有一个特殊的索引，叫聚簇索引，索引中包含了所有的行数据。聚簇索引和主键是一个意思的两种叫法。
• 当显示定义一个主键时，则InnoDB就把它作为聚簇索引，当表中没有代表唯一的一个或一组字段时，可以增加一个auto-increment字段作为主键。
• 当没有定义主键时，则MySQL会寻找是否有非NULL的唯一索引，如果有就把第一个唯一索引作为聚簇索引
• 当没有主键或合适的唯一索引时，InnoDB内部会创建一个虚构的聚簇索引，其中包含 row ID。

聚集索引的优势：

• 当SQL语句通过聚簇索引访问表数据时，由于通过索引能直接定位并访问表数据，所以性能很高。
• 相关数据会保存在一起，比如表是包含用户的邮件信息，通过用户ID创建聚簇索引，则查询一个用户的所有邮件只需要读取少量的数据页。
• 使用覆盖索引扫描的查询可以直接使用页节点上的主键值

查看表索引：

mysql> desc temp;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(64) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

创建自增主键：

# 对id列添加自增
mysql> alter table temp modify id int(11) AUTO_INCREMENT;

或

# 对id列添加主键与自增
mysql> alter table temp modify id int(11) primary key AUTO_INCREMENT;

删除主键：

# 先把自增删除
mysql> alter table temp modify id int(11);

# 删除主键
mysql> alter table temp drop primary key;

mysql> desc temp;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(64) | YES  |     | NULL    |       |2019-11-06 15:36:06 星期三
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MySQL辅助索引：

**表示：**MUL

查看表索引：

mysql> DESC student;
+----------+---------------------+------+-----+-------------------+----------------+
| Field    | Type                | Null | Key | Default           | Extra          |
+----------+---------------------+------+-----+-------------------+----------------+
| id       | int(11)             | NO   | PRI | NULL              | auto_increment |
| name     | varchar(50)         | NO   |     | NULL              |                |
| cardid   | char(18)            | NO   | UNI | NULL              |                |
| age      | tinyint(3) unsigned | NO   |     | 18                |                |
| gender   | enum('男','女')     | NO   |     | 男                |                |
| birthday | datetime            | NO   |     | CURRENT_TIMESTAMP |                |
| tnum     | char(11)            | NO   | UNI | NULL              |                |
| state    | enum('1','0')       | NO   |     | 1                 |                |
+----------+---------------------+------+-----+-------------------+----------------+

PRI是主键索引
UNI是唯一索引（不能有重复的值）

添加辅助索引：

#对name行添加索引并命名为idx_name，把name行对应的数据添加为索引
mysql> alter table student add index idx_name(name);

查询辅助索引：

#从student表中查询索引
mysql> show index from student;

删除辅助索引：

#对name行删除idx_name索引
mysql> alter table student drop index idx_name;

MySQL唯一索引：

表示： UNI

• 数据值不唯一不能创建唯一索引

查看表索引：

mysql> DESC student;
+----------+---------------------+------+-----+-------------------+----------------+
| Field    | Type                | Null | Key | Default           | Extra          |
+----------+---------------------+------+-----+-------------------+----------------+
| id       | int(11)             | NO   | PRI | NULL              | auto_increment |
| name     | varchar(50)         | NO   |     | NULL              |                |
| cardid   | char(18)            | NO   | UNI | NULL              |                |
| age      | tinyint(3) unsigned | NO   |     | 18                |                |
| gender   | enum('男','女')     | NO   |     | 男                |                |
| birthday | datetime            | NO   |     | CURRENT_TIMESTAMP |                |
| tnum     | char(11)            | NO   | UNI | NULL              |                |
| state    | enum('1','0')       | NO   |     | 1                 |                |
+----------+---------------------+------+-----+-------------------+----------------+

查看数据有没有重复值：

#判断tnum列的数据重复次数大于0的
mysql> select tnum,count(tnum) from student group by tnum having count(tnum) > 0;
+-------------+----+
| tnum        | ct |
+-------------+----+
| 18805403432 |  1 |
| 18805407666 |  1 |
| 18805407677 |  1 |
| 18805407688 |  1 |
| 18805407699 |  1 |
| 81818181818 |  1 |
+-------------+----+

创建唯一索引：

#基于tnum列的数据创建唯一索引，并命名为idx_tnum
mysql> alter table student add unique index idx_tnum(tnum);

查询唯一索引：

#从student表中查询索引
mysql> show index from student;

删除唯一索引：

#删除基于tnum列创建的唯一索引
mysql> alter table student drop index idx_tnum;

MySQL前缀索引：

• 当索引的字符太过于长时，需要把查询字符串的列做前缀索引

创建一个200字符的行：

mysql> alter table student add note varchar(200);

mysql> desc student;
+----------+---------------------+------+-----+-------------------+----------------+
| Field    | Type                | Null | Key | Default           | Extra          |
+----------+---------------------+------+-----+-------------------+----------------+
| id       | int(11)             | NO   | PRI | NULL              | auto_increment |
| name     | varchar(50)         | NO   |     | NULL              |                |
| cardid   | char(18)            | NO   | UNI | NULL              |                |
| age      | tinyint(3) unsigned | NO   |     | 18                |                |
| gender   | enum('男','女')     | NO   |     | 男                |                |
| birthday | datetime            | NO   |     | CURRENT_TIMESTAMP |                |
| tnum     | char(11)            | NO   | UNI | NULL              |                |
| state    | enum('1','0')       | NO   |     | 1                 |                |
| note     | varchar(200)        | YES  |     | NULL              |                |
+----------+---------------------+------+-----+-------------------+----------------+

创建前缀索引：

#基于note行创建前缀索引，也就是每一条数据的前10个字符作为索引
alter table student add index idx_note(note(10));

查询前缀索引：

#从student表中查询索引
mysql> show index from student;

删除前缀索引：

#删除基于note行创建的前缀索引
alter table student drop index idx_note;

MySQL联合索引：

• 当用户有多个条件时需要做联合索引
• 比如男人找女朋友，要求是性别：女，年龄：25，身材：火辣，这就是三个数据列，也就是把这三个列共同创建一个索引。

#根据年龄与性别创建索引
mysql> desc student;
+----------+---------------------+------+-----+-------------------+----------------+
| Field    | Type                | Null | Key | Default           | Extra          |
+----------+---------------------+------+-----+-------------------+----------------+
| id       | int(11)             | NO   | PRI | NULL              | auto_increment |
| name     | varchar(50)         | NO   |     | NULL              |                |
| cardid   | char(18)            | NO   | UNI | NULL              |                |
| age      | tinyint(3) unsigned | NO   |     | 18                |                |
| gender   | enum('男','女')     | NO   |     | 男                |                |
| birthday | datetime            | NO   |     | CURRENT_TIMESTAMP |                |
| tnum     | char(11)            | NO   | UNI | NULL              |                |
| state    | enum('1','0')       | NO   |     | 1                 |                |
| note     | varchar(200)        | YES  |     | NULL              |                |
+----------+---------------------+------+-----+-------------------+----------------+

创建联合索引：

#创建一个性别与年龄的联合索引
alter table student add index idx_object(gender,age);

查询联合索引：

#从student表中查询索引
mysql> show index from student;

删除联合索引：

#删除基于年龄与性别的联合索引
alter table student drop index idx_object;

MySQL覆盖索引：

• 当select语句 where多个条件时，为了避免辅助索引的叶子节点数据页数据不够导致要查询主键索引，所以要创建覆盖索引。
• 也就是说另类的联合索引
• 假设应用程序经常执行：select name,age,gender from students where name='olda';
• 如果在name字段建立辅助索引，那在叶子节点数据页中只包含数据 olda 与 id 主键值。不满足 name,age,gender 三个字段要求的数据，这时就会通过主键 id 值去二次查询聚集索引。
  
• 如果在 name,age,gender三个字段建立联合覆盖式索引，让叶子节点数据页中包含数据 id,name,age,gender 四个字段的值，那就满足了当前select语句的需求了，不需要二次查询聚集索引。
  

mysql> desc students;
+----------+---------------------+------+-----+-------------------+-------------------+
| Field    | Type                | Null | Key | Default           | Extra             |
+----------+---------------------+------+-----+-------------------+-------------------+
| id       | int(11)             | NO   | PRI | NULL              | auto_increment    |
| name     | varchar(50)         | NO   |     | NULL              |                   |
| age      | tinyint(3) unsigned | NO   |     | 18                |                   |
| gender   | enum('男','女')     | NO   |     | 男                |                   |
| birthday | datetime            | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+----------+---------------------+------+-----+-------------------+-------------------+
5 rows in set (0.01 sec)

# 当有经常使用的字段数据查询时，可以考虑创建覆盖索引
mysql> select name,age,gender from students where name='olda';

# 根据 select 的字段进行创建索引
mysql> alter table students add index idx_name(name,age,gender);

MySQL索引执行计划:

创建索引的原则：

当数据表过大时，可以选择创建辅助索引

当查询的数据表内值唯一，可以选择创建唯一索引

当数据表值字符过长，可以选择创建前缀索引

当数据表查询的条件不唯一时，可以选择创建联合索引

select语句查询方式：

优化器决定走全表扫描还是索引扫描

怎么确认语句是否走索引扫描？

1、从查询速度上
2、优化器执行计划选择情况

优化原则：

优化原则一：

• 尽可能消除全表扫描，除非表数据量是在万条以下。

优化原则二：

• 增加适当的索引能提高查询的速度，但增加索引需要遵循一定的基本规则。

1. 加在where条件上
2. 加在表之间join的键值上
3. 如果查询范围是少量字段，可以考虑增加覆盖索引（仅走索引）
4. 有多个查询条件时，考虑增加复合索引，并把最常使用的字段放在索引前面
5. 不要将索引加在区别率不高的字段上
6. 字段上增加函数，则字段上的索引用不了，需考虑改变写法

建索引原则：

(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列

(2) 经常做为where条件列  order by  group by  join on, distinct 的条件(业务:产品功能+用户行为)

(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引

(4) 列值长度较长的索引列,我们建议使用前缀索引.

(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)

(6) 索引维护要避开业务繁忙期

强制使用指定索引：

• force index(idx_name) 强制使用索引

mysql> select sname from students force index(idx_name);
+--------+
| sname  |
+--------+
| Andrew |
| Andy   |
| Bob    |
| Cindy  |
| John   |
| Mike   |
| Ruth   |
| Susan  |
+--------+
8 rows in set (0.01 sec)          # 强制使用指定索引后使用了10毫秒

mysql> select sname from students;
+--------+
| sname  |
+--------+
| Andrew |
| Andy   |
| Bob    |
| Cindy  |
| John   |
| Mike   |
| Ruth   |
| Susan  |
+--------+
8 rows in set (0.00 sec)          # 没有强制使用指定索引 使用了0毫秒

索引执行计划解释：

• 执行计划用来显示对应语句在MySQL中是如何执行的。explain语句对select，delete，update，insert，replace语句有效。
• id列：表示执行顺序，值越大则优先级越高；值相同则从上而下执行

select_type列：

simple：                表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且 只有一个

primary：               一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。 且只有一个

union：                 union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表 select_type都是union

dependent union：       与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响

union result：          包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null

subquery：              除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery

dependent subquery：    与dependent union类似，表示这个subquery的查询要受到外部表查询的影响

derived：               from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select

table列：

• 显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划 中的id，表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>，与类似， 也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

Type列：

• 表示访问类型，性能从低到高依次是：ALL -> index -> range -> ref -> eq_ref -> const,system -> NULL

ALL：              Full Table Scan， MySQL将遍历全表以找到匹配的行

index：            Full Index Scan，index与ALL区别为index类型只遍历索引树

range：            索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询

unique_subquery：  用于where中的in形式子查询，子查询返回不重复值唯一值

index_subquery：   用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重

ref：              非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引和唯一索引的非唯一前缀进行的查找

eq_ref：           唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描的多表链接操作中

system：           当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该 查询转换为一个常量。System为表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index

const:             使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描

NULL：             MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

possible_keys列：

• 表示MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

Key列：

• 表示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

key_len列：

• 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

Ref列：

• 如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

Rows列：

• 表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra列：

Using index：       该值表示相应的select操作中使用了覆盖索引（Covering Index）

Using where：       表示MySQL服务器在存储引擎收到记录后进行“后过滤”   多个where条件，根据查询出的数据进行二次筛选

Using temporary：   表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

Using filesort：    MySQL中无法利用索引完成的排序操作称为“文件排序”，常见于order by和group by语句中

explain(desc)使用场景（面试题）

题目意思: 我们公司业务慢,请你从数据库的角度分析原因

一.mysql出现性能问题,我总结有两种情况:

1）应急性的慢：突然夯住 
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
1. show processlist;   获取到导致数据库hang的语句
2. explain 分析SQL的执行计划,有没有走索引,索引的类型情况
3. 建索引,改语句
2）一段时间慢(持续性的):
处理过程:
1.记录慢日志slowlog,分析slowlog
2.explain 分析SQL的执行计划,有没有走索引,索引的类型情况 
3.建索引,改语句

查看索引数据区别度：

• 区别度越高越好，说明不重复的数据越多

Cardinality: 3 字段是数据不重复的数据行数

mysql> show index from temp;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| temp  |          1 | idx_name |            1 | name        | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

优化器执行计划参数详解：

table：              被扫描的表名
type：               优化器走的索引类型
    ALL              全表扫描
        示例：desc select * from city;

    index            需要扫描整个索引树,获取到想要数据,比ALL性能好,顺序IO,可以减少回表查询
        示例：desc select id from city;

    range            基于索引范围作为查询条件，> < >= <= or like
        示例：desc select * from city where id<10;

    ref              普通索引，等值查询
        示例：desc select * from city where CountryCode='CHN';
        示例：desc select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

    eq_ref           在发生join操作时，on的条件列是主键或唯一键
        示例：desc select country.name,city.name,country.SurfaceArea from city join country on city.countrycode=country.code where city.name='shenyang';

    const或system     唯一键索引，主键索引等值查询
        示例：desc select * from city where id='1';

    NULL              索引中扫描不到这个数据
        示例：desc select * from city where id=5000;
possible_keys         可能会用到的索引

key                   真正使用的索引

key_len               索引占用磁盘空间大小，越小越好

rows                  扫描后返回的数据行，超过所有数据行的25%会自动转为全表扫描

Extra                 Using index

命令查看优化器选择后的执行计划：

desc + select语句` 或 `explain + select语句

通过全表查询：

#看type的类型与key最后走的索引,（这是一个全表查询的选择）

mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.10 sec)

通过idx_name查询：

#这是一个通过自定义的idx_name辅助索引查询的

mysql> explain select * from student where name='olda';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_name      | idx_name | 152     | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

explain(desc):

执行过程Extra字段的参数意思

• using index:表示数据不需要回表查询，查询所需的数据都是从索引文件（数据）中获取，
• using where:需要回表进行数据查询