「一分钟学Java」MySQL索引（一）

作为MySQL面试中较高频率考察的问题，我们来重点认识一下索引。

定义

索引是一种排好序的快速查找数据结构。

因此索引本质上是一种数据结构。一种可以高效获取数据的数据结构。

作用

提升查找与排序（order by）效率

B+树 索引的数据结构

除数据本身之外，数据库还维护一个满足特定查找算法的数据结构（B+树），这个数据结构以某种方式指向数据，即可以在数据结构的基础上实现高级查找的算法。这种数据结构就是索引。

B+树 结构优点

主键索引叶子节点的值存储的就是MySQL的数据行（数据和索引在一起），普通索引的叶子节点的值存储的是主键值（数据和索引分离）。

相比普通索引，B+树其实将数据和索引放到一起，到达叶子节点即可获得数据，不需要再通过索引指向再去文件中获得数据。

索引失效

数据的update与增删导致。

重建索引

一般索引文件很大，不会全放在内存当中，往往索引以索引文件的形式存储在磁盘上。

经常修改的字段不适合建索引

索引优点

1. 提高数据检索效率，降低数据库IO成本。类似图书馆目录。
2. 降低数据排序的成本，降低CPU的消耗。

索引缺点

1. 索引也是占空间的。索引也是一张表，保存主键与索引字段，并指向实体表记录。
2. 虽然提高检索效率，但是也降低了表写入的速度，对insert、update、delete操作有影响。更新数据时，不仅需要保存数据，也还要保存索引文件中新的索引字段。更新数据带来的键值变化需要更新索引信息。
3. 需要花时间研究建立最优秀的索引（复合哪几个字段）。

索引类型

• 单值索引：一个索引只包含一个列。 p.s. 一个表可以有多个单值索引。
• 唯一索引：索引列的值唯一，但允许有空值。主键属于唯一索引，且不可为null。
• 复合索引：一个索引包含多个列。建复合索引优于建单值索引。

单表不建议超过5个索引。

语法

• 创建

  create [unique] index indexName on tableName(columnname(length));
  
  alter table tableName add [unique|fulltext] index  [indexName] on (columnname(length));
  

• 删除

  drop index indexName on tableName;
  

• 查看

  show index from tableName;
  

• ALTER命令

  alter table tableName add primary key(column_list);
  #该语句添加一个主键索引，索引值必须唯一，且不能为null
  
  alter table tableName add unique indexName(column_list);
  #创建的索引值必须是唯一的，null可以出现多次。
  
  alter table tableName add index indexName(column_list);
  #普通索引
  
  alter table tableName add fulltext indexName(column_list);
  #全文索引，用于全文搜索。
  

索引结构

• B+树

  如何提高效率？

  答：因为增加一个树的层级就是一次IO，故减少树的层级，将树横向扩展。

• Hash

• Fulltext

• R-Tree

哪些情况适合建索引

• 适合

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该建立索引
  • 查询中与其他表关联的字段，外键关系建立索引
  • 建复合索引优于建单值索引
  • 需要排序的字段。排序字段若通过索引去访问将大大提高排序速度
  • 查询中统计或分组的字段

• 不适合

  • 频繁更新的字段不适合创建索引
  • where条件中用不到的字段不建索引
  • 表记录太少
  • 数据重复且分布平均（变化不大的）。重复的数据建索引没有太大效果，可选择性低。例如性别、国籍