索引介绍

索引是什么

• 数据库索引相当于一本书的目录，能加快查找的查询速度
• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中
• 索引：包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，默认都是B+树结构组织的索引

索引的优势和劣势

优势：

• 可以提⾼数据检索的效率，降低数据库的IO成本，类似于书的⽬录 — 检索
• 通过索引列对数据进⾏排序，降低数据排序的成本，降低了CPU的消耗 –排序
  被索引的列会⾃动进⾏排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂⼀些

如果按照索引列的顺序进⾏排序，对应order by语句来说，效率就会提⾼很多
where 索引列 在存储引擎层 处理
覆盖索引，不需要回表查询

劣势：

• 索引会占据磁盘空间
• 索引虽然会提⾼查询效率，但是会降低更新表的效率。⽐如每次对表进⾏增删改操作，MySQL不

仅要保存数据，还有保存或者更新对应的索引⽂件

索引的分类

单列索引

• 普通索引： MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插⼊重复值和空值，纯粹为了查询数据更快⼀点 。add index

  CREATE INDEX index_name ON table(column(length)) ;
  ALTER TABLE table_name ADD INDEX index_name (column(length)) ;	
  复制代码

• 唯⼀索引： 索引列中的值必须是唯⼀的，但是允许为空值。add unique index

  CREATE UNIQUE INDEX index_name ON table(column(length)) ;
  ALTER TABLE table_name ADD UNIQUE INDEX index_name(column);
  复制代码

• 主键索引： 是⼀种特殊的唯⼀索引，不允许有空值。pk

组合索引

• 在表中的多个字段组合上创建的索引。dd index(col1,col2..)

• 组合索引的使⽤，需要遵循最左前缀原则。（最左匹配原则）

• ⼀般情况下，建议使⽤组合索引代替单列索引。（主键索引除外）

  ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10)) ;
  复制代码

全⽂索引

• 只有在MyISAM引擎、InnoDB（5.6以后）上才能使⽤，⽽且只能在CHAR,VARCHAR,TEXT类型字段上使⽤全⽂索引。 fulltext
• 优先级最⾼ 先执⾏、不会执⾏其他索引
• 存储引擎决定执⾏⼀个索引

  CREATE FULLTEXT INDEX index_name ON table(column(length)) ;
  ALTER TABLE  table_name ADD FULLTEXT index_name(column);
  复制代码

索引其他操作

• 删除索引

  DROP INDEX index_name ON table
  复制代码

• 查看索引

  SHOW INDEX FROM table_name \G
  复制代码

索引原理分析

索引存储结构

• 索引是在存储引擎中实现的，也就是说不同的存储引擎，会使⽤不同的索引
• MyISAM和InnoDB存储引擎：只⽀持B+ TREE索引， 也就是说默认使⽤BTREE，不能够更换
• MEMORY/HEAP存储引擎：⽀持HASH和BTREE索引

B树和B+树

B+ Tree 结构示例⽹站
B 树图示
B树是为了磁盘或其它存储设备⽽设计的⼀种多叉（相对于⼆叉，B树每个内结点有多个分⽀，即多叉）平衡查找树。 多叉平衡

B+树图示

• B树的⾼度⼀般都是在2-4这个⾼度，树的⾼度直接影响IO读写的次数
• 如果是三层树结构—⽀撑的数据可以达到20G，如果是四层树结构—⽀撑的数据可以达到⼏⼗T

B树和B+树的区别

• B树是⾮叶⼦节点和叶⼦节点都会存储数据
• B+树只有叶⼦节点才会存储数据，⽽且存储的数据都是在⼀⾏上，⽽且这些数据都是有指针指向的，也就是有顺序的。 索引列 order by

⾮聚集索引（MyISAM）

• B+树叶⼦节点只会存储数据⾏（数据⽂件）的指针，简单来说数据和索引不在⼀起，就是⾮聚集索引
• ⾮聚集索引包含主键索引和辅助索引都会存储指针的值

主键索引

这⾥设表⼀共有三列,假设我们以 Col1 为主键,则上图是⼀个 MyISAM 表的主索引(Primary key)示意
可以看出 MyISAM 的索引⽂件仅仅保存数据记录的地址

辅助索引（次要索引）

在 MyISAM 中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求 key 是唯⼀的,⽽辅助索引的 key 可以重复

同样也是⼀颗 B+Tree,data 域保存数据记录的地址。因此,MyISAM 中索引检索的算法为⾸先按照B+Tree 搜索算法搜索索引,如果指定的 Key 存在,则取出其data 域的值,然后以 data 域的值为地址,读取相应数据记录。

聚集索引（InnoDB）

• 主键索引（聚集索引）的叶⼦节点会存储数据⾏，也就是说数据和索引是在⼀起，这就是聚集索引
• 辅助索引只会存储主键值
• 如果没有没有主键，则使⽤唯⼀索引建⽴聚集索引；如果没有唯⼀索引，MySQL会按照⼀定规则创建聚集索引

主键索引

• InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则 MySQL系统会⾃动选择⼀个可以唯⼀标识数据记录的列作为主键,如果不存在这种列,则MySQL ⾃动为 InnoDB 表⽣成⼀个隐含字段作为主键,类型为⻓整形。

InnoDB 主索引(同时也是数据⽂件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据⽂件本身要按主键聚集

辅助索引（次要索引）

• 第⼆个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值⽽不是地址。换句话说,InnoDB 的所有辅助索引都引⽤主键作为 data 域
• 聚集索引这种实现⽅式使得按主键的搜索⼗分⾼效,但是辅助索引搜索需要检索两遍索引:⾸先检索辅助索引获得主键,然后⽤主键到主索引中检索获得记录。

  select * from user where name='Alice'   --回表查询 检索两次 ⾮主键索引 --- pk---索引--->数据
  select id,name from user where name='Alice'  --不需要回表 在辅助索引树上就可以查询到 覆盖索引(多⽤组合索引)
  复制代码

为什么不建议使⽤过⻓的字段作为主键?

• 因为所有辅助索引都引⽤主索引,过⻓的主索引会令辅助索引变得过⼤
• 同时,请尽量在 InnoDB 上采⽤⾃增字段做表的主键

其他知识点

哪些情况需要创建索引

1. 主键⾃动建⽴唯⼀索引
2. 频繁作为查询条件的字段应该创建索引
3. 多表关联查询中，关联字段应该创建索引 on 两边都要创建索引
4. 查询中排序的字段，应该创建索引
5. 频繁查找字段 覆盖索引
6. 查询中统计或者分组字段，应该创建索引 group by

哪些情况不需要创建索引

1. 表记录太少
2. 经常进⾏增删改操作的表
3. 频繁更新的字段
4. where条件⾥使⽤频率不⾼的字段

为什么使⽤组合索引

mysql创建组合索引的规则是⾸先会对组合索引的最左边的，也就是第⼀个name字段的数据进⾏排序，在第⼀个字段的排序基础上，然后再对后⾯第⼆个的cid字段进⾏排序。其实就相当于实现了类似order by name cid这样⼀种排序规则

为了节省mysql索引存储空间以及提升搜索性能，可建⽴组合索引（能使⽤组合索引就不使⽤单列索引）

例如：

创建组合索引（相当于建⽴了col1,col1 col2,col1 col2 col3三个索引）：

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3'）
复制代码

⼀颗索引树上创建3个索引 ： 省空间

使⽤遵循最左前缀原则

1、前缀索引 like a%
2、从左向右匹配直到遇到范围查询 > < between like

建⽴组合索引 (a,b,c,d)
where a=1 and b=1 and c>3 and d=1
到c>3停⽌了 所以d ⽤不到索引了
怎么办？
更改索引顺序：（a,b,d,c）