简介

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。
索引的作用就相当于目录的作用。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

优缺点

优点 ：

  • 使用索引可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。
  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点 ：

  • 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
  • 索引需要使用物理文件存储，也会耗费一定空间。

但是，使用索引一定能提高查询性能吗?
大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

索引的底层数据结构

Hash表

哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1））。

优缺点：
1.Hash 冲突问题 ：Hash 冲突，不过对于数据库来说这还不算最大的缺点。
2.Hash 索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点： 假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。

B 树& B+树

B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。
目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B 树& B+树两者有何异同呢？

  • B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
  • B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
  • B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

image.png

索引类型

主键索引(Primary Key)

数据表的主键列使用的就是主键索引。
一张数据表有只能有一个主键，并且主键不能为 null，不能重复。
在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

二级索引(辅助索引)

二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。

唯一索引，普通索引，前缀索引等索引属于二级索引。

  1. 唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
  2. 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
  3. 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。

  4. 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

    集索引与非聚集索引

    聚集索引

    聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。
    在 Mysql 中，InnoDB 引擎的表的 .ibd文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

    聚集索引的优点

    聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

    聚集索引的缺点

  5. 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。

  6. 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改， 而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。

    非聚集索引

    非聚集索引即索引结构和数据分开存放的索引。

二级索引属于非聚集索引。

非聚集索引的叶子节点并不一定存放数据的指针， 因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。


非聚集索引的优点

更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的

非聚集索引的缺点

  1. 跟聚集索引一样，非聚集索引也依赖于有序的数据
  2. 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

    回表

    回表大概就是我们有个主键为ID的索引，和一个普通字段的索引（非聚集索引），我们在普通字段上搜索，执行的流程是先查询到普通索引上的字段，然后找到他的对应的主键id，最后去主键索引找到id对应的值。回到主键索引树搜索的过程，就是回表。不过也有方法避免回表，那就是覆盖索引。

非聚集索引不一定回表查询。

试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。 那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。 再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

如何添加索引

1.添加 PRIMARY KEY（主键索引）

  1. ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )Copy to clipboardErrorCopied

2.添加 UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` )Copy to clipboardErrorCopied

3.添加 INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )Copy to clipboardErrorCopied

4.添加 FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)Copy to clipboardErrorCopied

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

mysql强制指定索引查询

当有多个索引生效时，MySQL选择使用的索引可能并非我们想要，可以通过force 的方式强制使用指定索引

select * from table_name force index (index_name) where conditions;

创建索引的注意事项

1.选择合适的字段创建索引：

  • 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
  • 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
  • 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
  • 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
  • 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

    2.被频繁更新的字段应该慎重建立索引。

    虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

    3.尽可能的考虑建立联合索引而不是单列索引。

    因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

    4.注意避免冗余索引 。

    冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

    5.考虑在字符串类型的字段上使用前缀索引代替普通索引。

    前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

explain执行计划

使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MYSQL是如何处理SQL语句的.我们可以用执行计划来分析查询语句或者表结构的性能瓶颈。

1、作用

  1. 查看表的读取顺序
  2. 查看数据库读取操作的操作类型
  3. 查看哪些索引有可能被用到
  4. 查看哪些索引真正被用到
  5. 查看表之间的引用
  6. 查看表中有多少行记录被优化器查询

    2、语法

    ```sql explain + sql语句

explain select * from user; +——+——————-+————-+———+———————-+———+————-+———+———+———-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +——+——————-+————-+———+———————-+———+————-+———+———+———-+ | 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 16 | | +——+——————-+————-+———+———————-+———+————-+———+———+———-+

<a name="NulIb"></a>
#### 2.1 id
select 查询的序列号,包含一组数字,表示查询中**执行Select子句或操作表的顺序**

1. id值相同,执行顺序由上而下
1. id值不同,id值越大优先级越高,越先被执行
1. id值有相同的也有不同的,如果id相同,从上往下执行,id值越大,优先级越高,越先执行
<a name="1360203"></a>
#### 2.1 select_type
 查询类型,主要用于区别

- **SIMPLE** : 简单的select查询,查询中不包含子查询或者UNION
- **PRIMARY**: 查询中若包含复杂的子查询,最外层的查询则标记为PRIMARY
- **SUBQUERY **: 在SELECT或者WHERE列表中包含子查询
- **DERIVED **: 在from列表中包含子查询被标记为DRIVED衍生,MYSQL会递归执行这些子查询,把结果放到临时表中
- **UNION**: 若第二个SELECT出现在union之后,则被标记为UNION, 若union包含在from子句的子查询中,外层select被标记为:derived
- **UNION RESULT**: 从union表获取结果的select
<a name="1360204"></a>
#### 2.3 table
　　显示这一行的数据是和哪张表相关
<a name="1360205"></a>
#### 2.4 type
访问类型: all, index,range,ref,eq_ref, const,system,null

最好到最差依次是: **system > const > eq_ref>ref >range > index > all **, 最好能优化到range级别或则ref级别

- system: 表中只有一行记录(系统表), 这是const类型的特例, 基本上不会出现
- const: **通过索引一次查询就找到了**,const用于比较primary key或者unique索引,因为只匹配一行数据,所以很快,如将主键置于where列表中,mysql就会将该查询转换为一个常量
- eq_ref: **唯一性索引扫描**, 对于每个索引键,表中只有一条记录与之匹配, 常见于主键或者唯一索引扫描
- ref :** 非唯一性索引扫描,返回匹配某个单独值的所有行**,本质上也是一种索引访问,它返回所有符合条件的行,然而它可能返回多个符合条件的行
- range : **只检索给定范围的行**, 使用一个索引来选择行.key列显示的是真正使用了哪个索引,一般就是在where条件中使用between,>,<,in 等范围的条件,这种在索引范围内的扫描比全表扫描要好,因为它只在某个范围中扫描,不需要扫描全部的索引
- index :** 扫描整个索引表**, index 和all的区别为index类型只遍历索引树. 这通常比all快,因为索引文件通常比数据文件小,虽然index和all都是读全表,但是index是从索引中读取,而all是从硬盘中读取数据
- all : full table scan**全表扫描 **,将遍历全表以找到匹配的行
<a name="1360206"></a>
#### 2.5 possible_keys
　　SQL查询中可能用到的索引,但查询的过程中不一定真正使用
<a name="1360207"></a>
#### 2.6 key
查询过程中真正使用的索引,如果为null,则表示没有使用索引<br />查询中使用了覆盖索引,则该索引仅出现在key列表中
<a name="1360208"></a>
#### 2.7 key_len
　　索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确度的情况下,长度越短越好, key_len显示的值为索引字段的最大可能长度,并非实际使用长度, 即key_len是根据表定义计算而得
<a name="1360209"></a>
#### 2.8 ref
　　显示索引的哪一列被使用了,如果可能的话,是一个常数.哪些列或者常量被用于查找索引列上的值
<a name="1360210"></a>
#### 2.9 rows
根据表统计信息及索引选用的情况,估算找出所需记录要读取的行数 (有多少行记录被优化器读取)
<a name="1360211"></a>
#### 2.10 extra
包含其它一些非常重要的额外信息

- Using filesort : 说明mysql会对数据使用一个**外部的索引排序**,而不是按照表内的索引顺序进行读取,Mysql中无法利用索引完成的排序操作称为**文件排序**
- Using temporary : **使用了临时表保存中间结果**,Mysql在对查询结果排序时使用了临时表,常见于order by 和分组查询group by
- Using index :
   1. 查询操作中使用了**覆盖索引**(查询的列和索引列一致),避免访问了表的数据行,效率好
   1. 如果同时出现了using where, 表明索引被用来执行索引键值的查找
   1. 如果没有同时出现using where, 表明索引用来读取数据而非执行查找动作
   1. 覆盖索引: 查询的列和索引列一致, 换句话说查询的列要被所键的索引覆盖,就是select中数据列只需从索引中就能读取,不必读取原来的数据行,MySql可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件
- using where : 表明使用了**where条件过滤**
- using join buffer : 表明**使用了连接缓存**, join次数太多了可能会出现
- impossible where : where子句中的**值总是false**,不能用来获取任何数据
- select tables optimized away :
   - 在没有group by 子句的情况下, 基于索引优化min/max操作或者对于MyISAM存储引擎优化count(*)操作,不必等到执行阶段再进行计算,查询执行计划生成阶段即完成优化
- distinct : 优化distinct操作,在找到第一个匹配的数据后即停止查找同样的值的动作
- Using index condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；

<a name="2PuYf"></a>
# 索引查询失效
<a name="item-13"></a>
## 使用的示例数据
```sql
mysql> show create table user \G
******************************************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(10) DEFAULT '0',
  `pos` varchar(30) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `created_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age_pos_phone` (`name`,`age`,`pos`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1、违反最左前缀法则

如果索引有多列，要遵守最左前缀法则
即查询从索引的最左前列开始并且不跳过索引中的列
explain select * from user where age = 20 and phone = '18730658760' and pos = 'cxy';

2、在索引列上做任何操作

如计算、函数、（自动or手动）类型转换等操作，会导致索引失效从而全表扫描
explain select * from user where left(name,5) = 'zhangsan' and age = 20 and phone = '18730658760';

3、索引范围条件右边的列

索引范围条件右边的索引列会失效
explain select * from user where name = 'zhangsan' and age > 20 and pos = 'cxy';

4、尽量使用覆盖索引

只访问索引查询（索引列和查询列一致），减少select*
explain select name,age,pos,phone from user where age = 20;

5、使用不等于（!=、<>）

mysql在使用不等于（!=、<>）的时候无法使用索引会导致全表扫描（除覆盖索引外）
explain select * from user where age != 20;
explain select * from user where age <> 20;

6、like以通配符开头（’%abc’）

索引失效
explain select * from user where name like '%zhangsan';

索引生效
explain select * from user where name like 'zhangsan%';

7、字符串不加单引号索引失效

explain select * from user where name = 2000;

8、or连接

少用or
explain select * from user where name = '2000' or age = 20 or pos ='cxy';

9、order by

正常（索引参与了排序）
explain select * from user where name = 'zhangsan' and age = 20 order by age,pos;
备注：索引有两个作用：排序和查找

导致额外的文件排序（会降低性能）
explain select name,age from user where name = 'zhangsan' order by pos;//违反最左前缀法则
explain select name,age from user where name = 'zhangsan' order by pos,age;//违反最左前缀法则
explain select * from user where name = 'zhangsan' and age = 20 order by created_time,age;//含非索引字段

10、group by

正常（索引参与了排序）
explain select name,age from user where name = 'zhangsan' group by age;
备注：分组之前必排序（排序同order by）

导致产生临时表（会降低性能）
explain select name,pos from user where name = 'zhangsan' group by pos;//违反最左前缀法则
explain select name,age from user where name = 'zhangsan' group by pos,age;//违反最左前缀法则
explain select name,age from user where name = 'zhangsan' group by age,created_time;//含非索引字段