一文快速入门 MySQL 索引

Posted by 彭超 on 2019-05-15
Estimated Reading Time 3 Minutes
Words 986 In Total
Viewed Times

什么是索引

MySQL 官方对索引的定义:索引(Index)是帮助 MySQL高效获取数据的数据结构。因此 索引的本质就是数据结构。索引的目的在于提高查询效率,可类比字典、书籍的目录等这种形式。

可简单理解为 排好序的快速查找数据结构。在数据之外,数据库系统还维护着 满足特定算法查询的数据结构,这些数据结构以某种方式指向数据。这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。

一般来说,索引比较大,不可能全部存储在内存中,因此索引往往以 索引文件 的形式存储在磁盘上。

平常所说的索引,如果没有特别指明,都是 B 树索引。其中聚集索引、次要索引、覆盖索引、前缀索引、唯一索引默认都是用 B 树。

可以通过命令 show index from table_name 查看表的索引情况

索引的优缺点

优点

  • 类似于大学图书馆的书目索引,提高数据的检索效率,降低数据库的 IO 成本
  • 通过索引列对数据进行排序,降低数据的排序成本,从而降低 CPU 的消耗

缺点

  • 索引实际上也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也要占用空间
  • 虽然索引大大提高了查询效率,但是降低了更新表的速度,如 insert、update 和 delete 操作。因为更新表时,MySQL 不仅要保存数据,还要保存索引文件每次更新的索引列字段,并且在更新操作后,会更新相应字段索引的信息
  • 索引只是提高查询效率的一个因素,如果你的 MySQL 有大量的数据表,就需要花时间研究建立最优秀的索引或优化查询语句

索引分类

索引主要分为以下三类:

  • 单值索引:一个索引只包含单个列,一个表可以有多个单值索引
  • 唯一索引:索引列的值必须唯一,但允许有空值,主键就是唯一索引
  • 复合索引:一个索引包含多个列

以索引的结构划分为以下四类:

  • BTREE 索引
  • Hash 索引
  • Full-Text 索引
  • R-Text 索引

基本语法使用

创建索引

创建一个普通索引

1
CREATE INDEX index_name ON table_name (column_name(length))

也可以通过修改表结构的方式来添加索引

1
ALTER TABLE table_name ADD INDEX index_name (column_name(length))

注:如果是 char、varchar 类型的字段,length 可以小于字段实际长度;如果是blob、text 类型,必须指定 length

其它创建索引的方式

添加主键索引

1
ALTER TABLE table_name ADD PRIMARY KEY (column_name)

添加唯一索引

1
ALTER TABLE table_name ADD UNIQUE (column_name)

添加全文索引

1
ALTER TABLE table_name ADD FULLTEXT (column_name)

添加普通索引

1
ALTER TABLE table_name ADD INDEX index_name (column_name)

添加组合索引

1
ALTER TABLE table_name ADD INDEX index_name (column_name_1, column_name_2, column_name_3)

删除索引

1
DROP INDEX index_name ON table_name

查看索引

1
SHOW INDEX FROM table_name

需要建立索引的情况

  • 主键自动建立唯一索引
  • 频繁作为 查询条件 的字段
  • 查询中与其它表关联的字段:外键关系建立索引
  • 高并发 下趋向创建 组合索引
  • 查询中 排序 的字段。排序字段若通过索引去访问将大大提高排序速度
  • 查询中 分组 字段

不需要建立索引的情况

  • 表记录太少(数据量太少 MySQL 自己就可以搞定了)
  • 经常增删改的表
  • 数据重复且平均分配的字段,如国籍、性别等
  • 频繁更新的字段不适合建立索引
  • Where 条件里面用不到的字段同样不适合建立索引

If you like this blog or find it useful for you, you are welcome to comment on it. You are also welcome to share this blog, so that more people can participate in it. If the images used in the blog infringe your copyright, please contact the author to delete them. Thank you !