MySQL索引知识介绍 - HelloWorld开发者社区

前言：

索引是MySQL数据库中的重要对象之一，索引的目的在于提高查询效率。可以类比字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。需要说明的是，MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同。为了避免混乱，本文将只关注于InnoDB引擎下的B+Tree索引。

1.索引结构及原理

要想了解索引的原理，首先要知道索引的结构，下面简单介绍下B+Tree索引的结构。

首先我们要知道索引是一种数据结构。在InnoDB中，每个索引其实都是一颗B+树，B+树是为了磁盘及其他存储辅助设备而设计的一种平衡查找树(不是二叉树)，在B+树中，所有的数据都在叶子节点，且每一个叶子节点都带有指向下一个节点的指针，形成了一个有序的链表。一般情况下数据库的B+树的高度一般在2~4层，这就是说找到某一键值的行记录最多需要2到4次逻辑IO，下图简单展示了B+树索引的结构。

MySQL索引知识介绍

2.索引的分类及创建方法

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。根据叶子节点的内容，索引类型分为主键索引和非主键索引。主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。非主键索引的叶子节点内容是索引列和主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）或辅助索引

一张InnoDB表必须有一个聚簇索引，当有主键时，会以主键作为聚簇索引；如果没有显式定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，则MySQL自动为InnoDB表生成一个隐含字段作为主键。除聚簇索引外的其他索引都可称为二级索引，比如我们常用到的唯一索引、普通索引、联合索引等。

上面讲过聚簇索引的叶子节点存的是整行数据，当某条查询使用的是聚簇索引时，只需要扫描聚簇索引一颗B+树即可得到所需记录，如果想通过二级索引来查找完整的记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的记录。也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

下面介绍下索引的创建、删除等操作方法。

# 建表时指定索引
CREATE TABLE `t_index` (
  `increment_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `col1` int(11) NOT NULL,
  `col2` varchar(20) NOT NULL,
  `col3` varchar(50) NOT NULL,
  `col4` int(11) NOT NULL,
  PRIMARY KEY (`increment_id`),
  UNIQUE KEY `uk_col1` (`col1`),
    KEY `idx_col2` (`col2`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='测试索引';

# 创建索引(两种方法)
# 普通索引
alter table `t_index` add index idx_col3 (col3);
create index idx_col3 on t_index(col3);
# 唯一索引
alter table `t_index` add unique index uk_col4 (col4);
create unique index uk_col4 on t_index(col4);
# 联合索引
alter table `t_index` add index idx_col3_col4 (col3,col4);
create index idx_col3_col4 on t_index(col3,col4);

# 删除索引
alter table `t_index` drop index uk_col4;
DROP INDEX idx_col3_col4 on t_index;