Damion's Blog
所有文章 标签 分类 作品集
Damion's Blog

目录

【MySQL】索引

 收录于 MySQL
  发布于  

什么是索引

索引，就是帮助MySQL高效获取数据的数据结构。

索引的优势和劣势

1. 优势

  • 可以提高数据的检索效率，降低数据库的IO成本。

  • 可以降低数据排序的成本，降低CPU消耗。

2. 劣势

  • 索引也是一张表，存储了主键和索引字段，因此，索引也占用空间。

  • 索引虽然提高了查询效率，但是降低了更新的速度。因为进行增、删、改时，不但需要更新数据，还需要更新索引。

  • 索引不是所有情况均适用。如表中数据较少，字段频繁更新，字段很少使用，或者字段大量重复等，都不适合建立索引。

索引结构

索引是在MySQL的存储引擎层实现的，目前支持的索引结构如下：

  • BTree索引：是一类统称，包含B+树等数据结构。InnoDB、MyISAM、Memory都支持。

  • HASH索引：Memory支持。

  • R-tree索引：空间索引，MyISAM支持，主要用于地理空间数据类型。

  • Full-text索引：全文索引，MyISAM支持，InnoDB从MySQL5.6版本开始支持。

聚集索引、符合索引、前缀索引、唯一索引默认使用B+tree树结构组织的索引。

B-树

B-树又叫多路平衡搜索树，一颗m叉B-树的性质如下：

  1. 每个结点最多有m个分支。

  2. 非叶子结点的根结点，至少有2个分支。非根非叶子结点，至少有ceil(m/2)个分支。

  3. 有n个分支的结点，具有n-1个关键字，这些关键字按递增或者递减顺序排列。

  4. 结点内的关键字互不相等。

  5. 叶子结点都处于同一层，可以用空指针表示。

  6. B-树的阶数m，是人为规定的，不会因为结点关键字的最大个数变化而变化。

  7. 结点中的每个关键字，都对应着一个记录的存储地址。

B+树

一颗m叉B+树，具备如下性质：

  1. 具有n个关键字的结点，含有n个分支。

  2. 每个结点最多有m个分支。

  3. 非根非叶子结点，至少有ceil(m/2)个关键字。非叶子结点的根结点，至少有2个关键字。

  4. 叶子结点包含了全部关键字，关键字按序排列。并且叶子结点引出的指针，指向了具体的记录。

  5. 所有非叶子结点中，存储着子树的最大关键字和指向该子树的指针，不存储关键字对应记录的存储地址。即非叶子结点仅仅发挥着索引作用。

  6. 有一个指针指向关键字最小的叶子结点，所有叶子结点组成一个线性链表。

索引组织表

InnoDB中，表的数据是根据主键顺序，以索引结构形式存放的，这种存储方式称为索引组织表。

每个索引在InnoDB中对应了一颗B+树，而每棵B+树的叶子结点是双向循环链表。

主键索引树的叶子节点是数据，普通索引的叶子节点是主键值。

使用B+树的好处

数据以数据块的形式存放在磁盘中，如果想从磁盘随机读取一个数据块，需要消耗较长的寻址时间。

如果使用二叉树，树高太高，意味着访问磁盘的次数变多，查询会很慢。

而N叉树可以减少磁盘访问次数。

索引分类

  1. 单值索引

一个单值索引只包含单个列。单值索引不存在数量限制。

  1. 唯一索引

索引列的值必须唯一，但是允许存在多个NULL。

  1. 复合索引

一个f复合索引包含多个列。

索引管理

1. 索引创建

可以在创建表结构时创建索引，也可以建完表后再增加。

创建语法为：

1

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name(index_col_name...);

index_col_name可以指定字段的长度，并指明字段的排序规则，格式如下：

1

column_name[(length)] [ASC | DESC]

2. 查看索引

1

SHOW INDEX FROM 表名;

3. 删除索引

1

DROP INDEX 索引名 ON 表名;

4. ALTER更新索引

  • 创建主键索引：

    1

    ALTER TABLE table_name ADD PRIMARY KEY(column_list);

  • 创建唯一索引

    1

    ALTER TABLE table_naame ADD UNIQUE [KEY|INDEX] index_name(column_list);

  • 创建普通索引

    1

    ALTER TABLE table_name ADD KEY|INDEX index_name(column_list);

  • 创建全文索引

    1

    ALTER TABLE table_name ADD FULLTEXT [KEY|INDEX] index_name(column_list);

5. CREATE TABLE创建索引

在使用CREATE TABLE语句创建表时，添加下列语句即可建立索引。

  • 创建主键索引

    1

    PRIMARY KEY (column_list)

  • 创建普通索引

    1

    KEY | INDEX [index_name] (column_list)

  • 创建唯一索引

    1

    UNIQUE [ INDEX | KEY ] [index_name] (column_list)

主键索引

主键索引树的叶子节点存放的是整行数据。

如果无主键，使用第一个定义的非Null唯一索引，作为主键。如果该列不存在，则生成rowid作为主键。

在InnoDB里，主键索引也称为聚簇索引。

聚簇索引是指和整行数据存储在一起的索引，找到索引就可以找到整行数据。

非聚簇索引是指和数据分开存储的索引，找到索引之后，通常还得回表查询。

非主键索引

索引树的叶子节点存的是主键值。

在InnoDB中，非主键索引也称为二级索引。

使用非主键索引进行查询时，通常还需要回表。即先搜索普通索引树，得到主键，再到主键树搜索。

索引维护

在更新数据时，数据库会对索引进行必要维护：

  • 插入一个新值，可能会导致旧值挪动位置。如果数据页满了，还会导致页分裂，页分裂会使空间利用率降低。

  • 删除数据则可能会导致数据页合并。

  • 自增主键的插入模式是追加插入，不会挪动其它记录。使用自增主键，还能降低非主键索引叶子节点占用的空间，因为数值型主键值占用内存更小。

覆盖索引

覆盖索引是指，非主键索引已经覆盖了查询需求，不再需要回表。

使用覆盖索引可以减少树的搜索次数，提升查询性能。

但是如果通过联合索引来支持覆盖索引，会产生维护代价。

最左前缀原则

定义索引(a, b, c)就相当于定义了索引(a), (a, b), (a, b, c)。

最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

联合索引建立原则

联合索引有如下建立原则：

  • 通过调整索引顺序，尽量少的维护多余索引。

  • 如果既要建立联合索引，也要建立一个单字索引，则尽量少的使用空间。

索引下推

MySQL5.6之后，引入了索引下推优化。

在索引遍历过程中，可以对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

自增主键原理

自增主键是指将主键索引列定义为auto_increment，且主键索引是单值索引，类型为数值型。

申请到的自增值是不能保证连续性的，使用命令show create 表名\G，输出结果AUTO_INCREMENT=后面的值，就是当前将会使用的自增值。

自增值保存方式

表的结构定义存放在.frm文件中，该文件不保存自增值。

  • MyISAM的自增值，保存在数据文件中；

  • MySQL5.7以及之前版本，自增值保存在内存中。重启之后，会从记录中读取自增值的最大值max(id)，并且将max(id)+1作为这个表的自增值；

  • MySQL8.0之后，自增值的变更记录存在了redo log，重启时依靠redo log日志恢复重启之前的值。

自增值修改机制

假设id字段被定义为auto_increment。

  1. 插入一行数据时的变化情况。

  • 如果id字段为0、null或者未指定值，就将自增值填入这个字段；

  • 如果指定了id字段值，就直接使用指定的值。

  1. 自增值变化原理。

  • 如果插入id值小于自增值，则自增值不变；

  • 如果插入id值大于等于自增值，就需要根据算法生成新的自增值。

自增值生成算法

自增值受两个系统参数影响：

  • auto_increment_offset：自增的初始值，默认1

  • auto_increment_increment：自增的步长，默认1

在双M的主备结构中，通常会将auto_increment_increment设置为2，让一个库的自增id是奇数，另一个库的自增id是偶数，避免两个库生成的主键冲突。

  • 如果auto_increment_offset和auto_increment_increment都是1，则新的自增值就是准备插入的值 + 1。

  • 否则，从auto_increment_offset开始，以auto_increment_increment为步长，持续累加，直到找到第一个大于插入值的值。

自增值修改时机

存储引擎在接受插入命令之后，执行插入操作之前，会获取并更新自增值。

自增主键ID不连续的情况

  1. 唯一键冲突会造成自增主键不连续。

  2. 事务回滚会造成自增主键不连续。

  3. 批量插入时，申请到的自增id会不连续。

注意：自增主键是不能回退的。

索引设计原则

  1. 对于查询频率高、且数据量大的表，可以建立索引。

  2. 索引字段，应该选择最常用、过滤效果最好的组合。

  3. 尽量使用唯一索引，区分度越高，索引的效果越好。

  4. 不应该创建太多索引。索引越多，更新、插入、删除操作的效率越低。索引太多，MySQL生成执行计划时选项也会过多，代价太高。

  5. 尽量使用短索引。索引也是存储在磁盘中，如果索引长度较短，既能节约存储空间，也能提升访问索引的IO效率。

  6. 充分利用最左前缀原则。

  7. 如果某个数据列包含许多重复内容，建立索引则没有什么实际效果。索引的选择性是指索引列中不同值的数据与表中记录数的比。一个索引的选择性越接近1，这个索引的效率就越高。

索引的使用

  1. 尽量使用复合索引，少使用单列索引。

  2. 如果建立了多个索引，数据库会选择一个最优的索引，不会使用全部索引。

  3. 尽量使用覆盖索引。

  4. 复合索引不要跨列或无序使用。

  5. 不要在索引上进行任何操作，如计算，函数操作，类型转换等，否则索引会失效。

  6. like尽量以“常量”开头，不要以"%“开头，否则索引会失效。

  7. 一般而言，范围查询之后的索引会失效。

  8. 尽量不要使用or，否则索引可能会失效。

更新于 2020-07-27
 MySQL
返回 | 主页