mysql的索引

Ms.Zyh2022/4/14大约 16 分钟

一，索引介绍

1.1 什么是索引

索引其实是一种数据结构，能够帮助我们快速的检索数据库中的数据.

1.2 索引类型

1.2.1按数据结构分类

按数据结构分类：BTree索引，Hash索引，full-index全文索引.

	InnoDB	MyISAM	Memory
B+tree索引	√	√	√
Hash索引	√	√	×
Full-text索引	√（MySQL5.6.4）	√	×

1.2.1.1 B+tree索引

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎默认就是用B+Tree实现其索引结构。

常见面试题：为什么B+树比B树更适合实现数据库索引？

B+ 树叶子结点之间用链表有序连接，所以扫描全部数据只需扫描一遍叶子结点，利于扫库和范围查询；B 树由于非叶子结点也存数据，所以只能通过中序遍历按序来扫。也就是说，对于范围查询和有序遍历而言，B+ 树的效率更高。
B+ 树更相比 B 树减少了 I/O 读写的次数。由于索引文件很大因此索引文件存储在磁盘上，B+ 树的非叶子结点只存关键字不存数据，因而单个页可以存储更多的关键字，即一次性读入内存的需要查找的关键字也就越多，磁盘的随机 I/O 读取次数相对就减少了。
B+树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

1.2.1.2 hash索引

Memory引擎默认支持哈希索引，如果多个Hash值相同，出现哈希碰撞，那么索引就以链表方式存储，这有点类似于Java中的HashMap。InnoDB和MyISAM虽然也支持Hash索引，但是InnoDB中Hash索引属于是自适应Hash索引，它的创建过程由存储引擎引擎自动优化创建，不能人为干预是否为表创建Hash索引。

hash索引的缺点：

Hash索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。
Hash 索引无法被用来避免数据的排序操作。
Hash索引不能利用部分索引键查询，即模糊查询不适合。
Hash索引在任何时候都不能避免表扫描。
Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

1.2.1.3 full-index全文索引

Full-text索引一般使用倒排索引实现。倒排索引同B+tree索引一样，也是一种索引结构。MySQL中InnoDB存储引擎在之前版本中是不支持全文检索的，要使用全文检索的话只能使用MySIAM存储引擎。在 MySQL 5.6.4 版本中InnoDB存储引擎才开始支持Full-text索引。

对于文本类型的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时全文索引了，在生成全文索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。

Full-text索引的查询有自己特殊的语法，而不能使用 LIKE 模糊查询的语法，语法如下：

SELECT * FROM table_name MATCH(ft_index) AGAINST('查询字符串');

1.2.2 按物理存储分类

MySQL索引按叶子节点存储的是否为完整表数据分为：聚集索引、非聚集索引（也叫二级索引、辅助索引）

1.2.2.1 聚簇索引

聚簇索引就是按照每张表的主键构造一颗 B+tree，同时叶子节点中存放的就是整张表的行记录数据，聚集索引的叶子节点被称为数据页。

InnoDB表要求必须有聚簇索引，默认在主键字段上建立聚簇索引，在没有主键字段的情况下，表的第一个非空的唯一索引将被建立为聚簇索引，在前两者都没有的情况下，InnoDB将自动生成一个隐式的自增id列，并在此列上建立聚簇索引。

1.2.2.2 非聚集索引（也叫二级索引、辅助索引）

非聚集索引的结构和聚集索引基本相同（非叶子结点存储的都是索引指针），区别在于非聚集索引的叶子节点存放的是数据主键不是行数据。因此在使用非聚集索引进行查找时，需要先查找到主键值，然后再到聚集索引中进行查找。

两种索引的区别：每个索引上包含的字段内容不同，聚集索引包含所有真实的物理数据，非聚集索引只包含索引字段和主键字段。此外，聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。

常见面试题：

1，什么是回表查询？

回表查询简单来说就是通过非聚集索引查询数据时，得不到完整的数据内容，需要通过非聚集索引得到的主键，再次查询主键索引来获得数据内容。

2，什么是索引覆盖？

索引覆盖就是指索引的叶子节点已经包含了查询的数据，满足查询要求，没必要再查询主键索引来获得数据内容。

对于回表查询问题，通常使用索引覆盖的方式解决，常见的方式就是通过建立联合索引（组合索引），实现索引覆盖，从而避免回表查询。

例如：假设我们给user表建立了一个主键ID，同时建立了为age字段一个索引。

查询sql如下：

select id,age from user where age = 30;

这个sql我们是不是就不用回表查询了，因为在非聚簇索引的叶子节点上已经有id和age的值，所以根本不需要拿着id的值再去聚簇索引定位行记录数据了，也就是在这一颗索引树上就可以完成对数据的检索，这样就实现了覆盖索引。

如果查询sql如下：

select id,age,name from user where age = 30;

那就不能实现索引覆盖了，因为name的值在age索引树上是没有的，还是需要拿着id的值再去聚簇索引定位行记录数据。如果我们对age和name字段新增一个组合索引，那就又可以实现索引覆盖了。

1.2.3 按照索引字段分类

普通索引（单值索引）：普通索引是最基本的索引，它没有任何限制，值可以为空；仅加速查询。
唯一索引：唯一索引与普通索引类似，不同的是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
主键索引：主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。
组合索引：组合索引指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。
全文索引：MyISAM 存储引擎支持Full-text索引，用于查找文本中的关键词，而不是直接比较是否相等。Full-text索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射，InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持Full-text索引

索引创建方式：

方式一使用CREATE INDEX创建：

-- 是否添加UNIQUE关键字 创建单值索或者唯一索引 
create [UNIQUE] index 索引名 ON 表名(列名(length)); 

-- 是否添加UNIQUE关键字 创建普通复合索引或唯一复合索引
create [UNIQUE] index 索引名 ON 表名(列名1(length),列名2(length));

方式二修改表结构时添加索引：

-- 是否添加UNIQUE关键字
alter table 表名 ADD [UNIQUE] index 索引名(columnName)

方式三创建表的时候直接指定：

-- index普通索引或者唯一索引选择index或UNIQUE关键字之一
CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    [index]或[UNIQUE] 索引名(列名(length))  
);

索引的其它操作：

-- 删除索引的语法
drop index[索引名] on 表名; 
-- 查看表的索引
show index from 表名

面试题：主键索引和唯一索引的区别？

主键必唯一，但是唯一索引不一定是主键，一张表上只能有一个主键，但是可以有一个或多个唯一索引。

二，explain

使用格式：

-- 得出一个表的字段结构
EXPLAIN tbl_name
-- 索引信息
EXPLAIN [EXTENDED] SELECT select_options

通过explain模拟Mysql优化器是如何执行SQL查询语句的，从而分析你的查询语句或是表结构的性能瓶颈：

mysql> explain select * from tb_user;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | tb_user | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+

下面解释explain得数据中各个字段的含义

2.1 id列

id表示执行顺序，有二种情况：

id 相同，执行顺序由上到下，开始执行。
id 不同，id值越大优先级就越高，越先被执行。

2.2 select_type列

数据读取操作的操作类型：

SIMPLE:简单的select 查询，SQL中不包含子查询或者UNION。
PRIMARY:查询中包含复杂的子查询部分，最外层查询被标记为PRIMARY。
SUBQUERY:在select 或者WHERE 列表中包含了子查询
DERIVED:在FROM列表中包含的子查询会被标记为DERIVED(衍生表)，MYSQL会递归执行这些子查询，把结果集放到零时表中。
UNION:如果第二个SELECT 出现在UNION之后，则被标记位UNION；如果UNION包含在FROM子句的子查询中，则外层SELECT 将被标记为DERIVED
UNION RESULT:从UNION表获取结果的select

2.3 table列

数据是关于哪张表

2.4 type列

访问类型由好到差system > const > eq_ref > ref > range > index > ALL

1、system:表只有一条记录(等于系统表),这是const类型的特例，平时业务中不会出现。

2、const:通过索引一次查到数据，该类型主要用于比较primary key 或者unique 索引，因为只匹配一行数据，所以很快;如果将主键置于WHERE语句后面，Mysql就能将该查询转换为一个常量。

3、eq_ref:唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。

4、ref:非唯一索引扫描，返回匹配某个单独值得所有行，本质上是一种索引访问，它返回所有匹配某个单独值的行，就是说它可能会找到多条符合条件的数据，所以他是查找与扫描的混合体。

详解：这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一一的扫描判断，也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，要实现这种能快速查找的算法，索引就要满足特定的数据结构。简单说，也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。

5、range：只检索给定范围的行，使用一个索引来选着行。key列显示使用了哪个索引。一般在你的WHERE 语句中出现between 、< 、> 、in 等查询，这种给定范围扫描比全表扫描要好。因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

6、index：FUll Index Scan 扫描遍历索引树(index：这种类型表示是mysql会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个复合索引的一部分，mysql都可能会采用index类型的方式扫描。但是呢，缺点是效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引）。

7、ALL 全表扫描从磁盘中获取数据百万级别的数据ALL类型的数据尽量优化

Using filesort(文件排序)：mysql无法按照表内既定的索引顺序进行读取。
Using temporary:Mysql使用了临时表保存中间结果，常见于排序order by 和分组查询 group by。
Using index 表示相应的select 操作使用了覆盖索引，避免访问了表的数据行，效率不错。
如果同时出现Using where ，表明索引被用来执行索引键值的查找。
如果没有同时出现using where 表明索引用来读取数据而非执行查找动作。
Using where: WHERE子句用于限制匹配哪些行针对下一个表或发送到客户端
Using join buffer (Block Nested Loop)， Using join buffer (Batched Key Access) ：表示当前sql使用了连接缓存。来自较早联接的表被部分读取到联接缓冲区中，然后使用它们的行从缓冲区中执行与当前表的联接。
impossible where ：where 字句总是false ，mysql 无法获取数据行。
select tables optimized away：
distinct：MySQL正在寻找不同的值，因此在找到第一个匹配的行后，它将停止为当前行组合搜索更多行。
Using where with pushed condition: NDB Cluster正在使用条件下推优化来提高在非索引列和常量之间进行直接比较的效率
Using sort_union(...)，Using union(...)，Using intersect(...): 这些指示了特定算法，该算法显示了如何针对index_merge联接类型合并索引扫描。
Using MRR: 使用多范围读取优化策略读取表
Using index for group-by: 与Using index表访问方法类似，Using index for group-by 表示MySQL找到了一个索引，该索引可用于检索a GROUP BY或 DISTINCT查询的所有列，而无需对实际表进行任何额外的磁盘访问。此外，以最有效的方式使用索引，因此对于每个组，仅读取少数索引条目。
Using index condition: 通过访问索引元组并首先对其进行测试以确定是否读取完整的表行来读取表。这样，除非有必要，否则索引信息将用于延迟（“ 下推 ”）整个表行的读取。

三，索引优化

在学习完mysql的索引底层数据结构和explain后，就可以进行简单的索引优化了。

3.1 基本优化

连接查询优化：左连接，右表建索引，左表是数据量最好少些；右连接，左表建索引，右表数据量最好少些。（连接后的结果集相当一张虚表）
in和exsits优化：小表驱动大表原则，in内的表的数据集小于 in外表的数据集；exists内的表的数据集大于 in外表的数据集。

3.2 索引失效分析

①，查询使用索引以最左侧按顺序，最好不要跳过中间索引。 ---- 最左前缀原则

②，不要在索引上有任何操作计算，函数，自动或手动进行类型转换，会导致索引失效进而转向全表扫描。

③，查询时，尽量不使用select *，查询的列表最好和索引保持一致。 ---- 覆盖索引

④，使用不等于（！=或者<>）的时候无法使用索引，会导致全表扫描。

⑤，使用is null 或者 is not null的时候无法使用索引，会导致全表扫描。

⑥，使用like模糊查询，以%通配符开头的时候无法使用索引，会导致全表扫描最好将%放在右边。 ---- 索引下推

⑦，字符串不加单引号会导致索引失效。

⑧，in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描，少使用or，用nuion替换。

3.3 索引的设计原则：

索引列的区分度越高，索引的效果越好。比如使用性别这种区分度很低的列作为索引，效果就会很差。
尽量使用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引涉及到的磁盘I/O较少，查询速度更快。
索引不是越多越好，每个索引都需要额外的物理空间，维护也需要花费时间。
利用最左前缀原则。