MySQL索引简述

1.什么是索引（index）

官方定义：一种帮助MySQL提高查询效率的数据结构。

2.索引的优点

大大提高查询效率。

3.索引的缺点

①.维护索引需要耗费数据库资源；

②.索引需要占用磁盘空间；

③.当对表数据进行增删改时，因为需要维护索引，速度会受到影响。

4.索引的分类

InnoDB引擎

①.主键索引

设定为主键后数据库会自动建立索引，InnoDB是聚簇索引，主键索引列的值不允许为空。

②.单值索引（普通索引|单列索引）

即一个索引只包含单个列，一个表可以有多个单列索引。

③.唯一索引

索引列的数据必须唯一，允许空值，唯一索引列的值可以为null，有且只能有一个null。

④.复合索引

即一个索引包含多个列。

MyISAM引擎

⑤.全文索引(FullText)，MySQL5.7以前，只能用于MyISAM引擎。

全文索引类型为FullText，在定义索引的列上支持值得全文查找，允许这些索引列插入重复值和空值。全文索引可以在char，varchar,Text类型列上创建，MySQL只有在MyISAM引擎上支持全文索引。

5.索引的基本操作

①.主键索引 主键自动创建主键索引

查看表索引：

show index from t_user;

②.单列|普通|单值索引

建表时创建：注意：随表一起创建的索引索引名与列明一致。

create table t_user(id varchar(20) primary key,name varchar(20), key(name));

建表后创建：

create index name_index on t_user(name);

删除索引：

drop index 索引名 on 表名;

③.唯一索引

建表时创建：注意：随表一起创建的索引索引名与列明一致。

create table t_user(id varchar(20) primary key, name varchar(20),unique(name));

建表后创建：

create unique index name_index on t_user(name);

④.复合索引

建表时创建：注意：随表一起创建的索引索引名与列明一致。

create table t_user(id varchar(20) primary key, name varchar(20), age int, key(name,age));

建表后创建：

create index name_age_index on t_user(name,age);

索引有效|失效案例：

原则：最左前缀原则；MySQL在查询时为了更好的利用索引，在查询过程中会动态调整查询字段顺序以便充分利用索引。

例：基于name，age，bir的顺序创建复合索引

a.name bir age 利用索引

b.name age bir 利用索引

c.bir name age 利用索引

d.age bir 不利用索引

e.name age 利用索引

f.name 利用索引

...

6.索引底层原理

create table t_user3(id varchar(20) primary key, name varchar(20), age int, bir varchar(20));
create index name_age_bir_index ON t_user3(name,age,bir);

7.B-Tree（B树）；B+Tree

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

②.B-Tree的每个节点中不仅包含数据的Key值，还有data值。而每一页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的Key的数量很小，同时导致B-Tree的深度较大，增大查询时的磁盘IO次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储Key值信息，这样可以大大增加每个节点存储的Key值的数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

a.非叶子节点只存储键值信息；

b.所有叶子节点之间都有一个键指针；

c.数据记录都存放在叶子节点中。

实际情况下，每个节点可能没有完全填充，因此在数据库中，B+Tree的高度一般在2~4层，MySQL的InnoDB引擎在设计的时候是将根节点常驻内存的，也就是说在查找某一键值的行记录时最多需要1~3次磁盘IO操作。

8.聚簇索引和非聚簇索引

①.聚簇索引：将数据与索引存储在一起，索引结构的叶子节点保存了行数据；

②.非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置。

在InnoDB引擎中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引|前缀索引|唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。

InnoDB

InnoDB中使用的是聚簇索引，将主键组织到一棵B+Tree中，而行数据就存储在叶子节点上，若使用"where id = 14"这样的条件查询主键，则按B+Tree的检索算法，即可找到对应的叶子节点，之后获得行数据。

若对name列进行检索，则需要两个步骤，第一步在辅助索引B+Tree中检索name，到达其叶子节点获取对应的主键。第二步使用主键在主键索引B+Tree中再执行一次B+Tree检索操作，最终达到叶子节点即可获得整行数据。（重点在于通过其他键需要建立辅助索引）

聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键（类似Oracle中的RowId）来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

MyISAM

MyISAM中使用的是非聚簇索引，非聚簇索引的两棵B+Tree看上去没有什么不同，节点结构完全一致只是存储的内容不同而已，主键索引B+Tree的节点存储了主键，辅助索引的B+Tree节点存储了辅助键，表数据存储在独立的地方。这两棵B+Tree的叶子节点都是用同一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键索引检索无需访问主键的索引树。

9.使用聚簇索引的优势

问题：每次使用辅助索引检索都要经过两次B+Tree查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗？聚簇索引的优势在哪？

①.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多行数据，访问同一页不同行数据时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻返回行数据了，按照主键ID来组织数据，获得数据更快。

②.辅助索引的叶子节点，存储主键值，而不是数据的存放位置。好处是当行数据发生变化时，索引树的节点也需要分裂变化；或者我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为聚簇索引存放的是主键值，减少了辅助索引占用的磁盘大小。

10.聚簇索引需要注意什么

①.当使用主键作为聚簇索引时，主键最好不要使用UUID，因为UUID值太过离散，不适合排序且可能出现新增加的UUID会插入索引树中间的位置的情况，导致索引树调整，复杂度变大，消耗更多的时间和资源。

②.建议主键使用int类型的自增形式，方便排序且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引保存的主键值占用存储空间也会跟着变大，也会影响IO操作读取到的数据量。

11.为什么主键通常建议使用自增ID

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻的存放在磁盘上的。如果主键不是自增ID，那么，将不断的调整数据的物理地址、分页，当然也有其他措施来减少这些操作，但却无法彻底避免。所以，如果主键是自增的，它只需要一页一页的写，索引结构相对紧凑，磁盘碎片少，效率也高。

12.什么情况下会无法利用索引呢

①.查询语句中使用LIKE关键字

在查询语句中使用LIKE关键字进行查询时，如果匹配字符串的第一个字符为%，索引不会被利用。如果%不在第一个字符位置，索引就会被利用。

②.查询语句中使用多列索引

多列索引是在表的多个列上创建一个索引，只有查询条件中包含了这些列的第一个字段，索引才会被利用。

③.查询语句中使用OR关键字

查询语句中只有OR关键字时，如果OR前后两个条件的列都是索引列，那么查询中将利用索引。如果OR前后两个条件的列有一个不是索引列，那么查询中将不会利用索引。

......