什么是索引?
索引是一种能帮助MySQL提高查询效率的数据结构。MySQL主要有两种结构:Hash索引和B+树索引,其中InnoDB引擎默认采用B+树。实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。一般作为WHERE子句的条件。接下来对以下项目进行讲解。
索引表
索引表是一张指示逻辑记录和物理记录之间对应关系的表。索引表的每项索引按键(或逻辑记录号)顺序排列。优点是能加快数据的检索速度,加速表与表之间的连接。缺点是索引表里每项索引需要占物理空间。当对表中的数据进行CRUD操作时,索引也要动态维护,降低了数据的维护速度。
查询一张表中的所有索引:
SHOW INDEX FORM TABLE_NAME;
其中一个表最多16索引,最大索引长度256字节。建立索引的时间开销是O(1)或者O(logN)。
索引分类
MySQL的索引有两种分类方式:逻辑分类和物理分类。
逻辑分类:
- 主键索引:一张表只能有一个主键索引,不允许重复,不允许NULL。(特殊的唯一索引)
- 唯一索引:数据列不允许重复,允许为NULL值,一张表可以有多个唯一索引,但是一个唯一索引只能包含一列,常用作id,卡号等。
- 普通索引:一张表可以创建多个普通索引,一个普通索引可以包含多个字段,允许数据重复,允许NULL值插入。
- 全文索引:让搜索关键词更高效的一种索引。
物理分类:
- 聚集索引:一般是表中的主键索引,如果没有显示指定主键,则会选择表中的第一个不允许为NULL的唯一索引。每张表只有一个聚集索引,数据行的物理顺序与列值的逻辑顺序相同。在精确查找和范围查找方面具有较好的表现。一般让自增id充当聚集索引。
- 非聚集索引:索引的路径顺序与磁盘上的物理存储顺序不同,一个表中可以有多个非聚集索引。
# 创建主键索引/聚集索引
alter table table_name add primary key (