MySQL高级应用 - 索引

IllTamer
109 阅读3分钟

本文已参与「新人创作礼」活动， 一起开启掘金创作之路。

索引

简介

MySQL官方对索引的定义为: 索引(Index）是帮助MySQL高效获取数据的数据结构。

故可以得到索引的本质:索引是数据结构。可以简单理解为"排好序的快速查找数据结构”。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引(hash index)等。

image-20211021210641028.png

在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。

  • 优势
    • 提高数据检索效率，降低数据库的IO成本 (降低磁盘读取次数)
    • 通过索引对数据排序，降低数据排序的成本，降低了CPU的消耗
  • 劣势
    • 索引也是一张表，该表保存了主键与索引字段并指向实体表的记录，所以索引也要占用空间
    • 索引会降低表的更新速度。因为更新表时MySQL会额外维护索引
    • 索引只是提高效率的一个因素，如果存有大数据量的表，需要花时间研究建立最优秀的索引或查询优化

分类

  • 单值索引

即一个索引只包含单个列，一个表可以有多个单值索引

  • 唯一索引

    索引列的值必须唯一，但允许有空值

  • 复合索引

    即一个索引包含多个列

基本语法

  • 创建

    CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length)); # 第一种
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length)); # 第二种

  • 删除

    DROP INDEX [indexName] ON mytable;

  • 查看

    SHOW INDEX FROM mytable

  • ALTER添加索引

    ALTER TABLE tbl_name ADD PRIMARY KEY (column_list); # 添加主键 索引值须唯一且非NULL
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list); # 创建唯一索引 可谓NULL
ALTER TABLE tbl_name ADD INDEX index_name (column_list); # 添加普通索引 值可重复
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list); # 添加全局索引

索引的效率

索引的选择性

索引中不同值的数目 / 表中记录数量 (选择性越接近1，索引的效率越高)

  • 需要创建索引的情况

    • 主键自动建立唯一索引
    • 频繁作为查询条件(WHERE)的字段应该创建索引
    • 查询中与其它表关联的字段，外键关系建立索引
    • 查询中需要排序的字段，建立索引可大大提高速度
    • 查询中统计 / 分组的字段

  • 不要创建索引的情况

    • 频繁更新的字段不要创建索引
    • WHERE条件里用不到的字段不要创建索引
    • 记录太少的表
    • 数据重复且分布平均的表字段
avatar
文章
阅读
粉丝