MySQL数据库—InnoDB索引

一、什么是索引

索引(Index)是帮助MySQL高效获得数据的、排好序的数据结构。

类比查字典，我们通过字典目录可以更快更精确的定位到某个字在那一页。数据库索引也是如此，在数据量较大时（一般百万级数据及以上），利用表的某一列或是某几列为关键字创建索引，会使mysql在以索引列为条件的查询中缩小查询的范围，从而减少不必要的IO步骤，提高查询效率。另外，索引都是排好序的结构，对于存在排序和分组的sql语句，使用索引还避免了在某些情况下排序的开销，降低了CPU的消耗。

但过度使用索引也是有一定弊端的，索引也是要占用物理空间的，更严重的是，为了保持索引可用，在对表进行DML操作（增删改）时，还需要对索引进行调整，在索引过多的情况下，无疑会降低效率。此外，如果索引创建不当或是sql语句编写不当，还会导致索引失效的问题，白白浪费资源。

接下来，将会从MySQL InnoDB存储引擎底层开始，了解数据及索引的存储形式，从而能够进一步了解索引的使用方法。

二、索引的分类

在InnoDB存储层面来看，其支持

• B+树索引
  • 聚集索引
  • 辅助索引（非聚集索引）
• 哈希索引
• 全文索引：意在提高通过字段（文章）的某一部分（非前缀）查询时的效率

其中哈希索引由引擎自主判断创建，无需人为干预

全文索引在InnoDB 1.2.x版本开始支持，意在提高通过字段（文章）的某一部分（非前缀）查询时的效率

B+数索引是使用最多且最值得学习的索引，下文将主要对B+数索引做介绍。

三、索引的创建

1、索引创建

#创建索引
##方式一
CREATE [UNIQUE] INDEX index_name ON table_name(field_list);#多个字段用逗号隔开
##方式二
ALTER table_name ADD [UNIQUE | INDEX | PRIMARY KEY | FULLTEXT][index_name] ON (field_list);#唯一|普通|主键|全文索引
#删除索引
DROP INDEX [index_name] ON table_name;
#查看索引
SHOW INDEX FROM table_name;

2、创建建议

• 建议创建索引

  • 主键、外键、唯一键会自动创建索引
  • 频繁作为查询条件的字段
  • 查询中需要排序的字段
  • 查询中需要统计或分组的字段
  • 尽量创建复合索引

• 不建议创建索引

  • 记录太少
  • 以DML为主的表

• Cardinality值

  另外，通过查看表的索引show index from table_name，可以看到一个Cardinality字段，如果Cardinality/记录行数的值越接近1，则说明这个索引可选性越高。

  通俗一点理解，一个表中记录数为n，表索引列有m个不同的值，那个这个索引的选择性就是m/n，一个索引的选择性越接近1，这个索引的效率越高。

  即数据重复的越少，索引效率越高。比如像是性别字段，仅有两个可选的值（男或女），那创建索引是没有意义的。（会导致索引失效࿰