MySQL 索引

目录

• • 什么是索引
  • • 概念
    • 优缺点
  • 索引的分类
  • • InnoDB
    • • 主键索引
      • 单值索引
      • 唯一索引
      • 复合索引
    • MyISAM
    • • Full Text 全文索引
  • 索引的创建方式
  • • 建表时创建
    • 建表后创建
    • 查看索引是否创建
  • 索引原理
  • • B+ 树
    • B+ 树和 B 树的区别
    • 聚簇索引与非聚簇索引
    • InnoDB
    • MyISAM
    • • 聚簇索引的优势
      • 使用聚簇索引时需要注意什么
      • 为什么主键通常使用自增 id
      • 什么情况下无法使用索引

什么是索引

概念

一种帮助 MySQL 提高查询效率的数据结构

优缺点

优点：

1. 大大加快查询速度

缺点：

1. 维护索引需要消耗数据库资源
2. 索引需要占据磁盘空间
3. 对表进行增、删、改的时候，因为需要维护索引，速度会受到影响

索引的分类

InnoDB

主键索引

设定为主键后数据库会自动建立索引，InnoDB 为聚簇索引

创建表的时候会有一个主键（primary key），这个主键就是索引

单值索引

也叫单列索引、普通索引
一个索引只包含单个列，一个表可以有多个单列索引

除了主键以外，为其他列创建索引，叫单值索引；
比如一个表中，有 age，name，id，假如 id 为主键索引，那么可以为 name 创建单值索引。

唯一索引

索引列的值必须唯一，但允许有空值；
主键索引不能未 null，而唯一索引可以为 null。

复合索引

即一个索引包含多个列

比如有一个表，id，name，age，复合索引就是用 name 和 age 组合作为索引；
如果查询 where age = 18 或者是 where name = ‘逍遥’，可以用单值索引；
如果是查询 where name = 18 and age = ’逍遥‘，此时用复合索引就会更快，常用 name 和 age 查询的话就可以使用 name + age 共同创建的复合索引。

MyISAM

Full Text 全文索引

全文索引类型为 FULL TEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在 CHAR、VARCHAR 、TEXT 类型列创建。MySQL 只有 MyISAM 存储引擎支持全文索引。

索引的创建方式

主键索引是在建表后自动创建的

建表时创建

-- 主键索引和普通索引
create table [表名](id varchar(20) primary key, name varchar(20),key(name));

-- 唯一索引
create table [表名](id varchar(20) primary key, name varchar(20),unique(name));

-- 符合索引
create table [表名](id varchar(20) primary key, name varchar(20),age int ,unique(name,age));

建表后创建

-- 普通索引
create index [列名] on [表面](列名);

-- 唯一索引
create unique index [列名] on [表面](列名);

-- 复合索引
create index [列名] on [表面](列名1,列名2);

创建复合索引 age，name，bir，只能利用 最左前缀原则 查询，也就是说，只能根据

1. age
2. age ，name
3. age ，name， bir
4. age，bir

这三条查询
由于 MySQL 优化，在查询过程中动态调整查询字段顺序以便利用索引，因此可以查询

5. age，bir，name
6. bir，age，name
7. bir，name，age
   …

也就是说，有 age 就可以查询！

查看索引是否创建

select index form [表名]

索引原理

B+ 树

建表，插入数据并查找

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

insert into user values(3,'d',15);
insert into user values(5,'a',23);
insert into user values(2,'c',22);
insert into user values(6,'b',13);
insert into user values(1,'f',24);
insert into user values(4,'e',11);
insert into user values(9,'h',20);
insert into user values(8,'g',19);

select * from user;

可以发现，插入 id、name、age 是无序的，但是插入过后，数据是按照 id 排序的，而把 id 设置为主键，主键默认是索引的。

因此 MySQL 底层第一步就是对插入的数据按照索引排序；

那为什么要排序呢？
当然是方便查找；

当数据量很大的时候，这样类似链表的结构就不方便查找了，因此 MySQL 底层又做了进一步的改善

也就是出现了页目录的概念，叶子节点存储所有信息，而目录只存储索引以及下一个节点的地址信息；

B+ 树和 B 树的区别

1. B 树所有节点都存储全部数据，B + 树只有叶子节点存储全部数据
2. 所有叶子节点之间都有一个指针；
3. 数据记录都都存放在叶子节点中。

MySQL 底层原理

1. 存储数据的时候首先根据主键排序，用指针串起来
2. 基于 B+ 树的结构，对数据进行分页存储，默认大小为 16 KB，一个三层的 B+ 树大概能存 10 亿数据，顶层目录是常驻内存，也就是说查询需要1 - 2 次查询。

聚簇索引与非聚簇索引

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

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

InnoDB

不需要二次查找，直接对 B+ 树进行查找

MyISAM

需要二次查找，先利用非聚簇索引查找主键，然后回表，对聚簇索引的 B+ 树查询。

聚簇索引的优势

1. 由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多行数据，访问同一页不同数据时，已经把同一页的数据加载到缓冲器中，后续查询不必重新访问磁盘，
2. 辅助索引存储的是主键值，减少辅助索引占用空间大小；
3. 不使用聚簇索引如果发送节点的增、删、改，就会导致需要维护索引树。

使用聚簇索引时需要注意什么

1. 使用主键作为聚簇索引时，不要使用 uuid，uuid 太过于离散，不适合排序，如果出现新增记录，肯能会插入到索引中间，消耗过多的资源和时间。

为什么主键通常使用自增 id

聚簇索引的数据的物理存放顺序与索引存放顺序是一致的，也就是说，只要索引是相邻的，那么对应的数据一定也是相邻存放在磁盘上，如果不是自增，那么存放时就会不断调整数据的物理位置等；如果是自增，只需要一页一页的写，索引结构相对紧凑，磁盘碎片少，效率也高。

什么情况下无法使用索引

1. 查询关键字中 like 关键字

使用 like 关键字查询时，如果第一个就是 %，那么就无法利用索引，如果 % 不在第一个位置，那么就可以成功利用索引；

2. 查询过程中使用多列索引

多列索引是在表的多个字段创建一个索引，如果查询条件中使用了第一个字段，才能利用索引；

3. 查询过程中使用 or 关键字

查询语句只有 or 时，如果 or 前后两个条件都是索引，那么可以利用，其中有一个不是，就不能利用。