• MySQL 索引介绍和最佳实践

    目录

    一、前言

    索引是帮助高效获取数据排好序的数据结构，这里都会以MySQL InnoDB 存储引擎做讲解。

    • InnoDB 存储引擎索引有两个特点
      • 聚簇索引
        索引与数据存放在一起、一张表只有一个聚簇索引找到索引的同时也找到了数据，同时聚簇索引具有唯一性，默认是主键，如果表中没有定义主键，InnoDB 会选择一个非空唯一索引代替。如果没有，InnoDB 会定义一个隐藏的_rowid 列来作为聚簇索引。
      • 非聚簇索引
        索引与数据分开存放，索引结构的叶子节点指向了数据的对应行，如：一个表id字段是主键索引，创建一个name字段的普通索引，叶子节点是指向对应主键索引的值，通过name字段的普通索引找到对应id值，然后通过id回表主键索引获取到行数据。

    二、索引类型

    1.1 主键索引（PRIMARY KEY）

    主键索引非空且唯一，在 InnoDB 存储引擎中会作为聚簇索引叶子节点会存放所有行数据。

    ALTER TABLE `table_name` ADD PRIMARY KEY (`column`);

    • 1

    1.2 唯一索引（UNIQUE）

    唯一索引值唯一，可为NULL值，而且可以多行数据为NULL值，也可以使用多个列作为唯一索引，在存储索引时会对多列组合唯一判断。

    ALTER TABLE `table_name` ADD UNIQUE INDEX `index_name`(`column`) USING BTREE;

    • 1

    1.3 普通索引（NORMAL）

    普通索引是我们最常用的一类索引，可以使用单个列作为索引，也可以使用多个列作为索引，多列索引也叫做复合索引或者组合索引，在字段的长度超过索引限制（索引最大长度是768字节），可以为字段的部分前缀创建索引。

    1.3.1 单列普通索引
    ALTER TABLE `table_name` ADD INDEX `index_name`(`column`);

    • 1
    1.3.2 单列前缀普通索引
    ALTER TABLE `table_name` ADD INDEX `index_name`(`column`(2));

    • 1
    1.3.3 多列普通索引
    ALTER TABLE `table_name` ADD INDEX `index_name`(`column1`, `column2`);

    • 1
    1.3.4 多列前缀普通索引
    ALTER TABLE `table_name` ADD INDEX `index_name`(`column1`(2), `column2`(3));

    • 1
    1.3.5 升序降序索引
    # 默认升序
ALTER TABLE `table_name` ADD INDEX `index_name`(`column1`, `column2` ASC, `column3` DESC);

    • 1
    • 2

    1.4 空间索引（SPATIAL）

    空间索引是对空间数据类型的字段建立的索引，MYSQL使用SPATIAL关键字进行扩展，使其能够在空间数据类型的语法上创建空间索引。

    # 建表
CREATE TABLE `gis_position` (
	`id` INT NOT NULL,
	`gis` geometry NOT NULL COMMENT '空间位置信息',
	`geohash` VARCHAR ( 20 ) GENERATED ALWAYS AS (st_geohash ( `gis`, 12 )) VIRTUAL,
	PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '空间位置信息';

# 创建空间索引
ALTER TABLE gis_position ADD SPATIAL INDEX `idx_gis` ( `gis` );

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    1.5 全文索引（FULLTEXT）

    全文索引类似于 elasticsearch 这样的搜索引擎，会对索引内容进行分词，当然比起 elasticsearch 还是差一些。

    ALTER TABLE `table_name` ADD FULLTEXT INDEX `index_name`(`column`);

    • 1

    三、表数据准备（后续演示都基于这个表）

    我这里库使用的字符集为utf8mb4，一个字符占用4个字节，utf8占用3个字节，如果字段为varchar类型，需要加的2字节用来存储字符串长度，因为varchar是变长字符串。

    CREATE TABLE `company_staff` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `interest` varchar(20) DEFAULT NULL COMMENT '兴趣爱好',
  `entry_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs COMMENT='公司员工表';

INSERT INTO company_staff(name,age,position,interest,entry_time) VALUES('Kerwin',28,'cto','唱跳',NOW());
INSERT INTO company_staff(name,age,position,interest,entry_time) VALUES('Alia',26,'dev','rap',NOW());
INSERT INTO company_staff(name,age,position,interest,entry_time) VALUES('Coco',26,'dev','篮球',NOW());

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    四、索引最佳实践

    对MYSQL的 explain SQL分析工具不熟的可以看看 MySQL explain SQL分析工具详解与最佳实践

    4.1 全值匹配

    • 通过 name 字段精准匹配
    EXPLAIN SELECT * FROM company_staff WHERE name= 'Kerwin';

    • 1

    在这里插入图片描述
    这里可以看到使用了组合索引idx_name_age_position，我们的name字段为varchar(30)，计算出name字段占用字节数为key_len = 30*4+2=122，和我们的执行计划中的key_len相等，同时ref为const，可以确定通过name字段精准匹配，使用到了组合索引idx_name_age_position，但是只使用到了一个name字段。

    • 通过 name + age 字段精准匹配
    EXPLAIN SELECT * FROM company_staff WHERE name= 'Kerwin' AND age=28;

    • 1

    在这里插入图片描述
    和上面通过name字段匹配执行计划类似，name字段占用字节数为122，age字段为int类型，int类型占用4个字节，name + age 字段一共为126字节和执行计划的key_len相等，同时ref为const,const代表使用了两个字段常量，可以确定使用到了组合索引idx_name_age_position，并且使用到了两个字段name + age。

    • 通过 name + age + position 字段精准匹配
    EXPLAIN SELECT * FROM company_staff WHERE name= 'Kerwin' AND age=28 AND position='cto';

    • 1

    在这里插入图片描述
    position字段为varchar(20)，计算出字节数为20*4+2=82，在加上 name、age占用的字节数合计122+4+82=208字节和执行计划的key_len相等，同时ref为const,const,const代表使用了三个字段常量，确认idx_name_age_position索引中三个字段全部被使用到了。

    4.2 最左前缀原则

    如果索引了多列，要遵守最左前缀原则，指的是查询从索引的最左前列开始并且不跳过索引中的列。

    PS：在MySQL8.0的时候加入了一个跳跃索引，在某些情况下会跳过中间没有被匹配的列去匹配后面的列，如果使用到了跳跃索引那么在Extra中会出现 Index skip scan。

    • 通过 age 字段精准匹配
    EXPLAIN SELECT * FROM company_staff WHERE age= 28;

    • 1

    在这里插入图片描述
    我们直接使用age查询，这里发现没有使用到索引，因为我们的组合索引第一个值是name，如果查询条件中没有name值是无法使用索引的。

    • 通过 name + position 字段精准匹配
    EXPLAIN SELECT * FROM company_staff WHERE name= 'Kerwin' AND position='cto';

    • 1

    在这里插入图片描述
    我们这里使用name + position 字段精准匹配，执行计划中显示使用到了索引，但是key_len=122，name字段占用的字节数刚好是122，证明这里虽然使用到了idx_name_age_position索引，但是只使用到了name一个字段匹配。

    4.3 在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

    • 这里将name转化成小写查询
    EXPLAIN SELECT * FROM company_staff WHERE LOWER(name)= 'kerwin';

    • 1

    在这里插入图片描述

    4.4 不能使用索引中范围查询条件右边的列

    • 通过name + position 精准匹配和age范围查询
    EXPLAIN SELECT * FROM company_staff WHERE name= 'Kerwin' AND age<35 AND position='cto';

    • 1

    在这里插入图片描述
    我们查询时age条件使用范围查询，这里可以看到key_len=126，name+age字节数为126，没有使用到position

    4.5 尽量使用覆盖索引

    查询条件和响应结果的列都存在索引中，这样只需要通过索引就能获取需要的数据，就不会在进行回表。

    EXPLAIN SELECT name,age,position FROM company_staff WHERE name= 'Kerwin' AND age=28 AND position='cto';

    • 1

    在这里插入图片描述

    4.6 MySQL8.0之前在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描

    in、< 小于、 > 大于、 <=、>= 这些，MySQL内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，MySQL8.0的时候不等于（！=或者<>），not in 也会根据检索比例、表大小等多个因素整体评估是否使用索引。

    EXPLAIN SELECT * FROM company_staff WHERE name != 'Kerwin';

    • 1

    在这里插入图片描述

    4.7 is null,is not null 一般情况下也无法使用索引

    EXPLAIN SELECT * FROM company_staff WHERE name IS NULL;

    • 1

    在这里插入图片描述

    4.8 like以通配符开头（‘%abc…’）索引失效会变成全表扫描操作

    like查询是可以走索引的，但是只能通过前缀查询，不能以通配符开头。

    • 模糊匹配 name 字段，以win结尾的数据
    EXPLAIN SELECT * FROM company_staff WHERE name LIKE "%win";

    • 1

    在这里插入图片描述

    • 模糊匹配 name 字段，以Ker开头的数据
    EXPLAIN SELECT * FROM company_staff WHERE name LIKE "Ker%";

    • 1

    在这里插入图片描述

    4.9 字符串不加单引号索引失效

    字符串字段查询如果不加单引号或者算引号会发生隐式转换导致索引失效，如果是数字类型字段查询时使用单引号索引不会失效。

    EXPLAIN SELECT * FROM company_staff WHERE name = 666;

    • 1

    在这里插入图片描述
    发生隐式转换后SQL相当于变成SELECT * FROM company_staff WHERE CAST(name AS signed int) = 666;，name字段使用了函数索引失效

    4.10 少用or或in，用它查询时，MySQL不一定使用索引

    MySQL内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，拿我这里的数据来说，表里一共三条数据，我IN查询4个值以内（包含4个值）是可以走索引的，一旦超过4个值到了5个值索引就会失效了，MySQL内部优化器会认为我们要IN查询这5个值全表扫描耗时会比使用索引要快，因为使用非聚簇索引是需要回表的。

    EXPLAIN SELECT * FROM company_staff WHERE name IN ('4Kerwin','3Kerwin','2Kerwin','1Kerwin','Kerwin');

    • 1

    在这里插入图片描述

    4.11 范围查询索引失效

    MySQL内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，可能是由于单次数据量查询过大导致优化器最终选择不走索引。

    要想看到效果需要先添加10000条数据的样子，如果只有几条数据是会一直走索引。

    • 使用存储过程插入10000条数据
    ## 创建一个插入数据的存储过程
DROP PROCEDURE IF EXISTS insert_company_staff;
delimiter;;
CREATE PROCEDURE insert_company_staff () 
BEGIN
	DECLARE i INT;
	SET i = 1;
	WHILE ( i <= 10000 ) DO
		INSERT INTO `company_staff`(`name`, `age`, `position`, `interest`, `entry_time`) VALUES (CONCAT('Kerwin',i), 28 + i, 'cto', '唱跳', NOW());
		SET i = i + 1;
	END WHILE;
END;;
delimiter;

// 调用存储过程插入数据
CALL insert_company_staff ();

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 给年龄添加单值索引
    ALTER TABLE `company_staff` ADD INDEX `idx_age`(`age`);

    • 1
    • 范围查询数据
    EXPLAIN SELECT * FROM company_staff WHERE age >= 1 AND age <= 3000

    • 1

    在这里插入图片描述
    如果查询范围比较大索引可能会失效，缩小查询范围是可以走索引的，最终是否走索引还是MySQL内部优化器会根据检索比例根据表大小等多个因素整体评估。

    五、索引使用总结

    假设有一个组合索引 index(a,b,c)

    where语句是否使用索引
    where a = 3是：使用到a
    where a = 3 and b = 5是：使用到a，b
    where a = 3 and b= 5 and c = 4是：使用到a,b.c
    where b = 3 或 where b = 3 and c = 4 或 where c = 4否
    where a = 3 and c = 5是：使用到a，但是c不可以，b中间断了
    where a = 3 and b > 4 and c=5是：使用到a和b，c不能用在范围之后，b断了
    where a = 3 and b like "kk%’ and c =4是：使用到a,b.c
    where a = 3 and b like ‘%kk’ and c = 4是：只用到a
    where a = 3 and b like ‘%kk%’ and c =4是：只用到a
    where a = 3 and b like ‘k%kk%’ and c = 4是：使用到a,b,c

    like KK%相当于=常量，%KK和%KK%相当于范围

  • 相关阅读:
    flink连接kafka报：org.apache.kafka.common.errors.TimeoutException
    Linux工具——gdb
    单调性约束与反单调性约束的区别 monotonicity and anti-monotonicity constraint
    manjaro系统无法安装
    计算机毕业论文Java项目源码下载基于SSM实现的宠物销售网站
    VC/C++ Intel x86 内联汇编实现 “Interlocked” 原子变量各种操作
    手机储存快满了，怎么备份手机图片和视频到电脑上？
    GaussDB数据库SQL系列-数据去重
    kubernetes
    系统架构设计】计算机公共基础知识： 5 数学与经济管理
  • 原文地址：https://blog.csdn.net/weixin_44606481/article/details/133410720