运维首页 > 数据运维 > MySQL索引

MySQL索引

2023年 11月 23日 57.4k 0

为了提高查询速度，可以给某些列加上索引。索引就像看书时的目录一样，目录的内容较少，很快能找到想查的部分，然后根据目录对应的页码去找到详细内容，查询速度会快很多。

索引按照包含1个列还是多个列，可以分为：

  • 单列索引：即一个索引只包含单个列，一个表可以有多个单列索引。
  • 组合索引：一个索引包含多个列。

InnoDB中的索引使用B+树存储，按照B+树存储的内容不同，分为：

  • 聚簇索引（又称主键索引）： 叶子节点存储的是行数据，通过索引能直接找到对应的行数据。聚簇索引中的索引部分是使用的主键。
  • 非聚簇索引（又称二级索引）：叶子节点存储的是主键值，需要通过主键值在聚簇索引中进行第二次查找，找到对应的数据。

如果限制某个索引在表中必需是唯一的，那么这个索引称为唯一索引。

主键索引在唯一索引的基础上，限制不能为NULL。一张表里最多只能有一个主键索引，一个主键索引可以包含多个字段。

创建、删除、查看索引

创建索引

  • 创建表的时候直接指定：
    • CREATE TABLE 表名 (
  列名1 列的定义,
  列名2 列的定义,
  ...,
  INDEX 自定义的索引名称 (列名1 [ASC|DESC], 列名2 [ASC|DESC], ...)
);
  • 使用CREATE INDEX创建索引：
    • CREATE INDEX 自定义的索引名称
ON 表名 (列名1 [ASC|DESC], 列名2 [ASC|DESC], ...);

    ASC和DESC是可选的，用于指定索引的排列顺序，默认情况下索引是升序（ASC）排序。

  • 通过修改表结构添加索引：
    • ALTER TABLE 表名
ADD INDEX 自定义的索引名称 (列名1 [ASC|DESC], 列名2 [ASC|DESC], ...);

    练习代码：

    -- 创建表的时候直接为spu_id列指定索引
CREATE TABLE product (
  id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键id',
    spu_id BIGINT NOT NULL COMMENT '主商品id',
    price DECIMAL(12,2) NOT NULL COMMENT '商品售价',
    title VARCHAR(100) NOT NULL COMMENT '商品标题',
    categorization_id INT COMMENT '分类id',
    INDEX spu_id_idx (spu_id)
);

    通过SHOW INDEX FROM product;查看表包含的索引：

    image-20231122170417301.png
    (这个图片上传之后变得比较模糊，需要点开看)

    一共有两个索引，一个主键索引PRIMARY，一个创建的索引spu_id_idx，索引类型为BTREE表示索引的数据结构为B树（B+树是B树的变形）。（MySQL并不只有BTREE类型的索引，还有RTree、Hash、FullText、Spatial这些类型的索引）

    -- 通过CREATE INDEX 创建索引
CREATE INDEX price_idx ON product (price);

-- 通过修改表结构添加索引
ALTER TABLE product
ADD INDEX categrz_id_idx (categorization_id);

    创建表的时候列的定义中使用了字符串和数字类型，简单了解一下字符和数字类型相关的内容：
    char和varchar：

    括号里面的数字表示能存储的字符的最大数量，比如char(30)表示最多能存储30个字符。

    • char是固定长度，长度范围为0~255。

      • 优点：存储空间固定，没有碎片，尤其是更新比较频繁的时候，方便数据文件指针的操作，所以存储读取速度快。
      • 缺点：对数据量大的表，非固定长度的属性使用char，浪费空间。

    • varchar是弹性长度，长度范围是0~65535。

      • 优点：存储的空间根据存储的内容变化，节约空间。
      • 缺点：需要额外的1到2个字节存储长度信息，读取和存储的时候，需要根据长度信息计算下标才能获取完整内容，读取存储相较偏慢。

    image-20231122152732698.png
    float、double、decimal：

    • float：浮点型，4字节，32bit

    • double：双精度实型，8字节，64bit

    • decimal：数字型，128bit，不存在精度损失

      声明语法DECIMAL(M, D)中，M是最大位数（精度），范围是1到65，可不指定，默认为10；D是小数点右边的位数（小数位），范围是0到30，不能大于M，可不指定，默认是0。

      salary DECIMAL(5, 2)能存储的值的范围是-999.99到999.99。

    删除索引

  • 使用DROP INDEX删除索引
    • DROP INDEX 索引名称 ON 表名;
  • 使用ALTER TABLE修改表结构来删除索引：
    • ALTER TABLE 表名
DROP INDEX 索引名称;

    练习代码：

    -- 使用DROP INDEX删除索引
DROP INDEX categrz_id_idx ON product;

-- 通过修改表结构删除索引
ALTER TABLE product
DROP INDEX price_idx;

    查看索引

    使用以下语句查看表中创建好的索引：

    SHOW INDEX FROM 表名 [ FROM 数据库名]

    单列索引和组合索引

    单列索引

    单列索引指一个索引只包含一个列，一个表可以有多个单列索引。目前上文中创建的索引都是单列索引。

    组合索引

    一个索引包含多个列，就称为组合索引（或者称为联合索引、复合索引）。在需要同时查询多个列的时候，就能使用组合索引，组合索引的创建方式和上文中创建索引的方式一样，只是括号里面是两个到多个列。

    例如，如果经常需要查询某个分类下，某个主商品id下的所有的商品时，可以这样创建一个组合索引：

    CREATE INDEX cate_spu_idx 
ON product (categorization_id, spu_id);

    向product表中插入一些数据：

    INSERT INTO product(id, spu_id, price, title, categorization_id)
VALUES (1000, 100, 10.00, '商品1', 1),
(1001, 100, 11.00, '商品2', 1),
(1002, 101, 12.00, '商品3', 2),
(1003, 101, 13.00, '商品4', 2),
(1004, 102, 14.00, '商品5', 3),
(1005, 102, 15.00, '商品6', 3);

    查找数据：

    mysql> SELECT * FROM product WHERE categorization_id = 2 AND spu_id = 101;
+------+--------+-------+---------+-------------------+
| id   | spu_id | price | title   | categorization_id |
+------+--------+-------+---------+-------------------+
| 1002 |    101 | 12.00 | 商品3   |                 2 |
| 1003 |    101 | 13.00 | 商品4   |                 2 |
+------+--------+-------+---------+-------------------+
2 rows in set (0.01 sec)

    查看查询时使用了哪个索引：

    EXPLAIN SELECT * FROM product WHERE categorization_id = 1 AND spu_id = 102;

    image-20231122180237351.png

    通过key列知道，实际使用了cate_spu_idx索引。

    唯一索引

    唯一索引确保在整个表中索引的值是唯一的，不允许有重复值。

    唯一索引会影响性能，因为在进行操作的时候需要先确定是否已经存在数据。虽然会影响insert性能，但是在业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。一方面是因为提高查找的速度是很明显的，另一方面如果没有使用唯一索引，即使业务上尽量校验了，也有很大可能会产生脏数据。

    创建唯一索引

  • 创建表的时候在需要创建唯一索引的列定义中，使用UNIQUE关键字。
    • CREATE TABLE table_name (
  列名1 列的定义,
  列名2 列的定义 UNIQUE,
  ...
);

    定义列的时候使用UNIQUE，会自动创建唯一索引，索引名称也是自动生成的。

  • 创建表的时候，使用UNIQUE KEY关键字。
    • CREATE TABLE 表名(
   ...
   UNIQUE KEY(列名1,列名2,...) 
);

    包含多个列说明多个列组合起来是唯一的。

  • 创建表的时候使用CONSTRAINT 索引名 UNIQUE (列名)
    • CREATE TABLE table_name (
  列名1 列的定义,
  列名2 列的定义,
  ...,
  CONSTRAINT 索引名称 UNIQUE (列名1 [ASC|DESC], 列名2 [ASC|DESC], ...)
);
  • 使用CREATE UNIQUE INDEX创建索引：
    • CREATE UNIQUE INDEX 索引名称
ON 表名称(列名1, 列名2,...);
  • 通过修改表结构创建索引：
    • ALTER TABLE 表名称
ADD CONSTRAINT 索引名称 UNIQUE KEY(列名1,列名2,...);

    删除唯一索引

    删除唯一索引的方式和删除普通索引的方式一样。

    主键索引

    创建主键索引

  • 通过创建表时，在列定义中使用PRIMARY KEY关键字创建主键索引。
    • CREATE TABLE students (
  id INT PRIMARY KEY,
  ...
);
  • 通过修改表结构添加唯一索引
    • ALTER TABLE 表名称 ADD PRIMARY KEY (列名);

    删除主键索引

    ALTER TABLE 表名称 DROP PRIMARY KEY;

    聚簇索引和非聚簇索引

    InnoDB从磁盘中读取数据的最小单位是数据页，数据页大小是16KB。InnoDB并不是一行一行读的数据，而是一页一页读的。

    聚簇索引

    特点：

    • 索引和数据保存在同一个B+树中。
    • 页内的记录是按照主键的大小顺序排成一个单向链表。
    • 页和页之间也是根据页中记录的主键的大小顺序排成一个双向链表。
    • 非叶子节点存储的是记录的主键+页号。
    • 叶子节点存储的是完整的用户记录。

    MySQL索引-1

    （这个图是我照着参考链接的视频里的图画的，可以放大之后仔细观察）

    记录头信息中的record_type的值代表的意思如下：

    • 0：普通的记录
    • 1：目录项记录
    • 2：最小记录
    • 3：最大记录

    优点：

    • 数据访问更快，因为索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。
    • 聚簇索引对于主键的排序查找和范围查找速度非常快。
    • 按照聚簇索引的排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库可以从更少的数据块中提取数据，节省了大量的IO操作。

    缺点：

    • 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID为主键。
    • 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

    MySQL索引-2
    如图所示（把一些数据去掉看起来直观一点），以自增ID为主键，那么新增数据的时候，直接在最后一个叶子节点的单向链表后面添加数据即可，非叶子节点也进行相应调整。但是如果不按照自增主键来添加，比如上图中最后一个主键是320，插入数据时插入id为9的数据，这样需要在第2个叶子节点里面进行修改，如果第二个叶子节点的容量不足，后面的所有叶子节点都需要进行改动，以及非叶子节点也要进行相应的修改，会严重影响性能。更新主键也是一样，可能会导致索引中的数据需要进行大量移动来保证索引的顺序。

    限制：

    • 只有InnoDB引擎支持聚簇索引，MyISAM不支持聚簇索引。
    • 由于数据的物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。
    • 如果没有为表定义主键，InnoDB会选择非空的唯一索引列代替。如果没有这样的列，InnoDB会隐式地定义一个主键作为聚簇索引。
    • 为了充分利用聚簇索引的聚簇特性，InnoDB表中的主键应选择有序的id，不建议使用无序的id，比如UUID、MD5、HASH、字符串作为主键，无法保证数据的顺序增长。

    聚簇索引的查找逻辑

    MySQL索引-2
    比如查找id为99的数据：

    • 1 <= 99 < 320，所以99的数据在id为1的数据对应的页中，页的id是30。
    • 12 <= 99 < 209，所以99的数据在id为12的数据对应的页中，页的id是9。
    • 20 <= 99 < 100，在页9中没有找到99的数据，返回空。假如有数据，会把对应的一整行的数据都读取出来。

    非聚簇索引

    聚簇索引是按照主键值进行排序的，如果要以非主键的列作为搜索条件，需要创建非聚簇索引。

    假设以某列为搜索条件，用该列创建一棵B+树，为了方便，称这个列为c2列，图中蓝色部分就是列的值，叶子节点中黄色的部分是主键id：

    MySQL索引-3
    非聚簇索引是用的是列数据来进行排序而不是主键值，非聚簇索引的叶子节点中存储的不是完整的数据，而是主键值。

    非聚簇索引与聚簇索引的不同之处：

    • 页内的记录是按照c2列的大小顺序排成一个单向链表。
    • 页和页之间也是根据页中记录的c2列的大小顺序排成一个双向链表。
    • 非叶子节点存储的是记录c2列+页号。
    • 叶子节点存储的并不是完整的记录，而是c2列+主键两个列的值。

    非聚簇索引的查找逻辑

    MySQL索引-4

    如果列的值是字符串，会转换成ASCII码，按照ASCII码去比对大小。非聚簇索引会有很多种形式，可以加UNIQUE约束，加了后就不可重复了，还可以针对文本创建索引，还可以给多个字段创建组合索引，不管哪种索引，匹配的内容都会转换成ASCII码或者是具体的数字来做比对。

    例如：根据c2列的值查找c2=4的记录，查找过程如下：

    • 2 <=4 < 9，根据跟页面44定位到页42。
    • 由于c2没有唯一性约束，所以c2=4的记录可能分布在多个数据页中，又因为 2 <= 4 <= 4，所以确定存储用户记录的页在页34和页35中。
    • 在页34和35中定位到具体的记录。
    • 但是这个B+树的叶子节点只存储了c2和c1（主键）两个列，所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录（这称为回表操作）。

    假如用户要查找的列就是创建索引的列SELECT c2 FROM table_name WHERE c2=4;，那么就不需要再从聚簇索引中查找一次了，这种情况称为覆盖索引。

    组合索引会遵循最左匹配原则。就像"like 张%"可以利用索引，而"like %张" 不会使用索引。查找的时候如果没有使用组合索引的第一个列，索引不会生效。

    索引失效的情况

  • 使用计算函数导致索引失效

    以下查询走索引：

    SELECT * FROM product WHERE title LIKE '商品1%';

    • image-20231123113546498.png

    type为range表示使用索引来进行范围查找。key_len表示此次查询中，所选择的索引的字节长度。

    以下查询不走索引：

    SELECT * FROM product WHERE LEFT(title, 3)='商品1';

    image-20231123113742753.png

    type为ALL表示SQL会执行全表扫描，对整个表进行扫描。

  • LIKE以%、_开头导致索引失效

    以下查询不走索引：

    SELECT * FROM product WHERE title LIKE '%商品1%';

    • image-20231123115016500.png

    %表示匹配0个或多个字符，_匹配1个字符。

    没有使用索引是因为字符串索引会逐个转换成ASCII码，生成的B+树按照按照字符串的顺序排序，跳过开始的部分就无法使用生成的B+树了。

  • 组合索引没有使用到最左边的列时索引会失效

    CREATE INDEX cate_price_idx 
ON product (categorization_id, price);

-- 走索引
EXPLAIN SELECT * FROM product WHERE categorization_id = 1 AND price = 10.00;
-- 不走索引
EXPLAIN SELECT * FROM product WHERE price = 10.00;

    • image-20231123155156651.png

    image-20231123155238199.png

  • MySQL查询优化器认为直接全表扫描比走索引快时，不会走索引

    • 可能是我电脑上安装的MySQL版本的原因，参考链接的视频中说的几个索引失效的情况，实际都是用上了索引的。电脑中的MySQL版本为：

    $ mysql --version
mysql  Ver 8.0.33 for macos13.3 on x86_64 (Homebrew)

    a. 使用不等于没有导致索引失效

    以下两个SQL语句都会走索引：

    SELECT * FROM product WHERE title != '商品1';
SELECT * FROM product WHERE title <> '商品1';

    image-20231123120150677.png

    Using index condition表示使用了Index Condition Pushdown Optimization（ICP）索引下推优化。索引下推大致意思是将服务层要做的一些事情交给引擎层来完成，提高效率。

    不使用ICP的情况下，存储引擎读取索引记录，根据索引的主键值读取完整的行记录，将行记录给到Server层，由Server层来判断是否满足where条件，使用ICP的情况下，直接在存储引擎中判断是否满足where条件，满足where条件再读取行数据，然后给到Server层，Server层来判断是否满足其他where条件。

    b. 使用IS NOT NULL或IS NULL没有导致索引失效

    SELECT * FROM product WHERE title IS NOT NULL;
SELECT * FROM product WHERE title IS NULL;

    image-20231123152557120.png

    image-20231123152701147.png

    type为ref表示查找条件使用了索引，并且索引不为主键和unique。

    c. 类型转换没有导致索引失效

    spu_id是整数类型，但是使用了字符串类型来查：

    EXPLAIN SELECT * FROM product WHERE spu_id = '100';

    image-20231123153442857.png

    索引的优点和缺点

    优点：提高查询性能。

    缺点：

    • 索引需要占用额外的存储空间。
    • 对表进行插入、更新和删除操作时，索引需要维护，可能会影响性能。
    • 过多或不合理的索引可能会导致性能下降。

    因此需要谨慎选择和规划索引。

    适合创建索引的字段：

    • 频繁作为where条件语句查询的字段。
    • 关联字段，比如主商品表中的id和商品表中的spu_id，主商品和商品通过两者关联，这两个字段就是关联字段。
    • 排序字段。
    • 分组字段（因为分组首先要进行排序）。
    • 统计字段。

    不适合创建索引的字段：

    • 频繁更新的字段。
    • where、分组、排序中用不到的字段。
    • 参与MySQL函数计算的列。

    学习地址

    www.bilibili.com/video/BV1N2…

    www.runoob.com/mysql/mysql…

    大白菜程序猿
    数据运维
    0 0

    相关文章

    Oracle如何使用授予和撤销权限的语法和示例
    Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
    下载丨66页PDF，云和恩墨技术通讯（2024年7月刊）
    社区版oceanbase安装
    Oracle 导出CSV工具-sqluldr2
    ETL数据集成丨快速将MySQL数据迁移至Doris数据库

    发布评论

    大白菜程序猿

    这个人很懒，什么都没有留下～

    文章8480 评论0