风的影子


千风

MySql索引详解

  • 2021-07-14
  • 作者 浪客千风
  • ~4.77K 字

了解索引之前先了解一下约束。

约束（constraint）

约束实际上就是表中数据的限制条件 ，表在设计的时候加入约束的目的就是为了保证表中的记录完整和有效 ，常见约束如下

非空约束（not null）

约束的字段不能为null

唯一性约束（unique）

约束的字段是唯一的（可以为null），Mysql会为该字段会自动创建索引。

唯一性约束也称列级约束。

主健约束（primary key(简称PK)）

表中的某个字段添加主键约束后，该字段为主键字段，主键字段中出现的每一个数据都称为主键值，必须唯一且不能为null，即primary key = unique + not null。当一个字段同时被非空约束和唯一性约束时，自动成为主键。但oracle并不如此。

主键约束也称表级约束。

  • 单一主键：给一个字段添加主键约束
  • 复合主键：给多个字段联合添加一个主键约束(只能用表级定义)，用的少

外键约束（foreign key (简称FK)）

若有两个表A、B，id是A的主键，而B中也有id字段，则id就是表B的外键，外键约束主要用来维护两个表之间数据的一致性。

某个字段添加外键约束之后，该字段称为外键字段，外键字段中每个数据都是外键值。

  • 外键必须至少是受唯一性约束的。
  • 外键值可以为null。
  • 一张表可以有多个外键字段。

检查约束（check (mysql不支持，oracle支持））

略

索引概念

MySql官方对索引的定义为：索引（Index)）是帮助MySql高效获取数据的数据结构。

提取句子主干，就可以得到索引的本质：索引是数据结构。

在mysql当中，使用explain关键字查看一个SQL语句是否使用了索引进行检索

1
explain select * from tb_user

观察查询结果key列中是否列出了显示MySQL实际决定使用的键(索引) ，未使用则为null。

同时，索引是各种数据库进行优化的重要手段。

索引实现原理

  • 在MySql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在。
  • 在MyISAM存储引擎中，索引存储在一个.MYI文件中。
  • 在InnoDB存储引擎中索引存储在表空间（tablespace）中。
  • 在MEMORY存储引擎当中索引被存储在内存当中。
  • 不管索引存储在哪里，索引在mysql当中都是一个B+树的形式存在。

（至于为啥MySql使用B+树作为存储的数据结构，一时半会也说不明白，笔者也是略知一二，不想在这强行装X，目前知道B+树的结构就好了）

索引类型

普通索引

是最基本的索引，它没有任何限制。

创建示例：

  • 直接创建

    1
    CREATE INDEX index_name ON table(column(length));

  • 修改表结构创建

    1
    ALTER TABLE 'table_name' ADD INDEX index_name ON (column(length));

  • 创建表时创建

    1
    2
    3
    4
    5
    6
    CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `org` char(255) CHARACTER NOT NULL ,
        PRIMARY KEY (`id`),
        INDEX index_name (org(length))
    );

删除索引：

1
DROP INDEX index_name ON table

唯一索引

与普通索引类似，不同之处在于：索引列的值必须唯一，但允许有空值，即列受唯一性约束（unique）；显然的，一张表可以有多个唯一索引。

创建唯一索引：

  • 直接创建

    1
    CREATE UNIQUE INDEX index_name ON table(column(length));

  • 修改表结构创建

    1
    ALTER TABLE 'table_name' ADD UNIQUE index_name ON (column(length));

  • 创建表时创建

    1
    2
    3
    4
    5
    6
    CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `org` char(255) CHARACTER NOT NULL ,
        PRIMARY KEY (`id`),
        UNIQUE  index_name (org(length))
    );

主键索引

一种特殊的唯一索引。

  • 主键不可重复，只能有一个列作为主键
  • 一张表中主键索引只能存在一个

一般是在建表的时候同时创建主键索引：

1
2
3
4
5
CREATE TABLE `table_name` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `org` char(255) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`)
);

组合（复合、联合）索引

指多个字段上创建的索引

  • 组合索引中，列值的组合必须唯一。
  • 只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。
  • 使用组合索引时遵循最左前缀集合

创建方式：

1
ALTER TABLE 'table_name' ADD INDEX fa_fb_fc (fa,fb,fc);

全文索引

 InnoDB 存储引擎在MySql 5.6 之后才支持。

只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

1
ALTER TABLE 'table_name' ADD FULLTEXT INDEX index_name (column1,column2,...);

跟普通索引稍有不同，使用全文索引的格式MATCH (columnName) AGAINST ('string')

1
SELECT * FROM `table_name` WHERE MATCH(`name`) AGAINST('马')

match() 函数中指定的列必须和全文索引中指定的列完全相同，否则就会报错，无法使用全文索引，这是因为全文索引不会记录关键字来自哪一列。如果想要对某一列使用全文索引，需要单独为该列创建全文索引。

索引失效情况

常见的索引失效情况：

  1. 以”%”开头进行模糊查询，索引失效（后缀有%时 不失效）

    1
    2
    3
    # 有索引
    ALTER TABLE tableName ADD INDEX （col); 
    select * from tableName where col like '%T';

    尽量避免模糊查询的时候以”%”开始,这是一种优化的手段/策略。

  2. or 操作两边未同时使用索引，索引失效

    1
    2
    3
    4
    # 有索引
    ALTER TABLE tableName ADD INDEX （col1); 
    # or 查询
    select * from tableName where col1 = 100 or col2 = 'tom';

    如果使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效。

  3. 使用组合（复合）索引时，未使用左侧的列，索引失效

    1
    2
    3
    4
    # 有索引
    ALTER TABLE tableName ADD INDEX fa_fb_fc (fa,fb,fc); 
    # 查询时未使用左侧的列
    select * from tableName where fc = 100;

    遵循最左前缀集合。

  4. 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描

  5. 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引，对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。很显然，索引是想知道你要什么而不是你不要什么，你品你细品。

  6. 索引的列参加了运算，索引失效

    1
    2
    3
    4
    # 有索引
    ALTER TABLE tableName ADD INDEX fa_fb_fc (fa,fb,fc); 
    # 查询
    select * from tableName where fa + 1 = 101;

    说实话，没人写这种sql吧。

  7. 索引的列使用了函数，索引失效

    1
    2
    3
    4
    # 有索引
    ALTER TABLE tableName ADD INDEX fa_fb_fc (fa,fb,fc); 
    # 查询
    select * from tableName where lower(fb)= 'tom';

还有一点要注意的是：当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

说白了就一句话：索引是想要你直接告诉它你想要什么，而不是你不要什么，也不是你把这个东西（字段）包装之后，说包装完就是这样子，给我找。┐(´-｀)┌

回表

mysql的索引分为两大类：聚簇索引、非聚簇索引，它们不是一种单独的索引类型，而是一种数据存储方式

聚簇索引

将数据存储与索引放到了一块，找到索引也就找到了数据（严格来说这里的数据指的是行的物理位置），由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引。

如果表中没有定义主键，则第一个not NULL unique列是聚集索引。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

下面是聚簇索引B+树的结构示意图

非聚簇索引

非聚簇索引也叫辅助索引或二级索引，非聚簇索引叶子节点存储的不再是数据（行的物理位置），而是主键值

下面是非聚簇索引B+树的结构示意图

回表

先通过非聚簇索引定位到主键值，在通过聚簇索引定位到行记录，这就是所谓的回表或回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。

索引覆盖

MySql官网类似的说法是：使用explain查询计划时，explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

也就是如果触发了索引覆盖，那么explain输出的Extra字段为Using index。

一般的说法是：索引中已经包含所有需要读取的列的查询称为覆盖索引。

就是利用优化器的优化机制，只搜索了非聚簇索引，不进行回表操作，然后达到select的目的。一般主要适用于select出的字段很少的情况。

举例：

有张user表（PK id,name, age, email, address，……），我们的模板是select name, age ，然后只需要建立一个name和age的复合索引，然后这个非聚簇索引的叶子节点有了这两个字段和主键字段（虽然主键在这里没啥用）。然后执行器只需要搜索这个二级索引即可，不需要回表操作就完成了select查询操作。

小结

  • 主键上以及unique字段是上都会自动添加索引
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete时。因为更新表时，不仅要保存数据，还要保存一下索引文件。
  • 建立索引会占用磁盘空间的索引文件。一般情况影响不大，但如果在一个大的表上创建了多种组合索引，索引文件大小会增长的很快。
  • 索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优的索引，或优化查询语句。
本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可
最后编辑：2024-08-13