MySQL索引

一、索引概述

• 索引就相当于一本书的目录，通过目录可以快速的找到对应的资源，是存储引擎用于快速找到记录的一种数据结构
• 在数据库方面，查询一张表的时候有两种检索方式：
  第一种方式：全表扫描
  第二种方式：根据索引检索（效率很高）
• 索引提高检索效率最根本的原因是缩小了扫描的范围
• 索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护，是有维护成本的
  比如：表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护
• 主键，unique 都会默认的添加索引

二、索引语法

• 创建索引对象

create index 索引名称 on 表名(字段名);

• 删除索引对象

drop index 索引名称 on 表名;

• 查看索引

show index from 表名;

三、索引的分类

1. 普通索引

• 最基本的索引，它没有任何限制，用于加速查询

2. 唯一索引

• 加速查询，列值唯一，允许有空值

3. 主键索引

• 一个表只能有一个主键，不允许有空值，一般是在建表的时候同时创建主键索引

4. 组合索引

• 给多个字段联合起来添加一个索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用，使用组合索引时遵循最左前缀集合
• 效率大于索引合并（使用多个单列索引组合搜索）

5. 全文索引

• 主要用来查找文本中的关键字，而不是直接与索引中的值相比较

四、索引的实现原理

• MySQL索引底层采用的数据结构是 B + Tree
• 通过B + Tree缩小扫描范围，底层索引进行了排序，分区，通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，即索引会携带数据在表中的“物理地址”，所以效率大大提升

SELECT a FROM A WHERE a = 'lzj'

通过索引转化为：

SELECT a FROM A WHERE a = 物理地址

• B+ 树 相对于B 树的不同：
  非叶子节点只存储键值信息
  所有叶子节点之间都有一个链指针
  数据记录都存放在叶子节点中

五、聚集索引与非聚集索引

1. 聚集索引

• 又称主键索引、聚簇索引，该索引中键值的逻辑顺序与数据行的物理顺序相同，每个表 (InnoDB) 只能有一个聚集索引
• 聚集索引查找时间较短，但索引占用的存储空间较大

选取规则

• 如果存在主键，则选取该主键索引作为聚集索引
• 如果不存在主键，则选取该表的第一个唯一非空索引作为聚集索引
• 如果既不存在主键，也不存在合适的唯一键，则 InnoDB 会在内部生成一个隐藏的主键，并选取该主键索引作为聚集索引

2. 非聚集索引

• 又称辅助索引、非聚簇索引，该索引中键值的逻辑顺序与数据行的物理顺序不同，每个表 (InnoDB、MyISAM) 可以有多个非聚集索引
• 在 InnoDB 中，非聚集索引 B+ 树的叶子节点中存放了主键信息，当查找数据时，需要先在非聚集索引中查找到对应的主键，然后再根据主键去聚集索引中查找数据
  但是如果使用了覆盖索引，则不需要回表，直接通过非聚集索引就可以查找到想要的数据
  覆盖索引指 select 查询的数据不需要读取数据行就能在索引中取得
• 在 MyISAM 中，非聚集索引 B+ 树的叶子节点中存放了键值信息以及指向数据的地址，可以直接通过非聚集索引查找数据
• 索引占用的存储空间较小，但查找时间较长

3. MyISAM索引实现

• 在 MyISAM 中，只存在非聚集索引
• MyISAM中索引文件和数据文件是分开储存的，并且主键索引和辅助索引的储存方式类似
• 存储表结构：xxx.frm 格式文件
• 存储表行的内容：xxx.MYD 数据文件
• 存储表上的索引：xxx.MYI 索引文件
• 主键索引 B+ 树的叶子节点中存放了主键信息以及指向数据的地址，可以直接通过主键索引查找到想要的数据
• 辅助索引 B+ 树的叶子节点中存放了辅助键信息以及指向数据的地址，可以直接通过辅助索引查找到想要的数据，无须访问主键的索引树

4. InnoDB索引实现

• 在 InnoDB 中，有且仅有一个聚集索引
• InnoDB中索引文件和数据文件是一起存放的（表数据文件本身是按照B + tree组织的一个索引结构文件），并且主键索引和辅助索引储存方式有所不同，辅助索引的叶子节点不储存数据，仅储存主键信息
• 存储表结构：xxx.frm文件
• 存储表行内容与索引：xxx.ibd文件

引、MySQL页文件默认16K

• MySQL每个B + 树节点最大存储容量：16KB （指针+数据+索引）

六、索引失效

• 模糊查询的时候，第一个通配符使用的是%，这个时候索引是是失效的

select a from A where a like ' %B% ';

七、索引的设计原则

• 索引并非越多越好
• 数据量小的表最好不要使用索引
• 在频繁进行排序或分组的列上建立索引
• 避免对经常更新的表进行过多的索引，并且索引种的列尽可能少
• 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值少的列上不要建立索引
• 当唯一性是某种数据本身的特征时，指定唯一索引

八、索引优缺点

1. 优点

1. 大大提高系统性能
2. 大大加快数据的查询速度
3. 加速表和表的连接
4. 在使用分组和排序查询子句时，可以显著减少分组和排序的时间

2. 缺点

1. 耗费添加过程时间
2. 占用磁盘空间
3. 当有增删改的时候，索引也要对应改动，降低了数据库的维护速度