MySQL 索引介绍和最佳实践

目录

    • 一、前言
    • 二、索引类型
      • 1.1 主键索引（PRIMARY KEY）
      • 1.2 唯一索引（UNIQUE）
      • 1.3 普通索引（NORMAL）
        • 1.3.1 单列普通索引
        • 1.3.2 单列前缀普通索引
        • 1.3.3 多列普通索引
        • 1.3.4 多列前缀普通索引
      • 1.4 空间索引（SPATIAL）
      • 1.5 全文索引（FULLTEXT）
    • 三、表数据准备（后续演示都基于这个表）
    • 四、索引最佳实践
      • 4.1 全值匹配
      • 4.2 最左前缀原则
      • 4.3 在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
      • 4.4 不能使用索引中范围查询条件右边的列
      • 4.5 尽量使用覆盖索引
      • 4.6 MySQL8.0之前在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描
      • 4.7 is null,is not null 一般情况下也无法使用索引
      • 4.8 like以通配符开头（'%abc...'）索引失效会变成全表扫描操作
      • 4.9 字符串不加单引号索引失效
      • 4.10 少用or或in，用它查询时，MySQL不一定使用索引
      • 4.11 范围查询索引失效
    • 五、索引使用总结

一、前言

索引是帮助高效获取数据排好序的数据结构，这里都会以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.2 唯一索引（UNIQUE）

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

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

1.3 普通索引（NORMAL）

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

1.3.1 单列普通索引
ALTER TABLE `table_name` ADD INDEX `index_name`(`column`);
1.3.2 单列前缀普通索引
ALTER TABLE `table_name` ADD INDEX `index_name`(`column`(2));
1.3.3 多列普通索引
ALTER TABLE `table_name` ADD INDEX `index_name`(`column1`, `column2`);
1.3.4 多列前缀普通索引
ALTER TABLE `table_name` ADD INDEX `index_name`(`column1`(2), `column2`(3));

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.5 全文索引（FULLTEXT）

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

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

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

我这里库使用的字符集为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());

四、索引最佳实践

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

4.1 全值匹配

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

在这里插入图片描述
这里可以看到使用了组合索引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;

在这里插入图片描述
和上面通过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';

在这里插入图片描述
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;

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

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

在这里插入图片描述
我们这里使用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';

在这里插入图片描述

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

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

在这里插入图片描述
我们查询时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';

在这里插入图片描述

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

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

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

在这里插入图片描述

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

EXPLAIN SELECT * FROM company_staff WHERE name IS NULL;

在这里插入图片描述

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

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

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

在这里插入图片描述

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

在这里插入图片描述

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

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

EXPLAIN SELECT * FROM company_staff WHERE name = 666;

在这里插入图片描述
发生隐式转换后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');

在这里插入图片描述

4.11 范围查询索引失效

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

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

  • 使用存储过程插入10000条数据
## 创建一个插入数据的存储过程
DROP PROCEDURE IF EXISTS insert_company_staff;
delimiter;;
CREATE PROCEDURE insert_company_staff () 
BEGINDECLARE i INT;SET i = 1;WHILE ( i <= 10000 ) DOINSERT 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 ();
  • 给年龄添加单值索引
ALTER TABLE `company_staff` ADD INDEX `idx_age`(`age`);
  • 范围查询数据
EXPLAIN SELECT * FROM company_staff WHERE age >= 1 AND age <= 3000

在这里插入图片描述
如果查询范围比较大索引可能会失效，缩小查询范围是可以走索引的，最终是否走索引还是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%相当于范围

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/144403.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

微信小程序开发基础（一）认识小程序

微信小程序开发基础（一）认识小程序

微信小程序&#xff0c;小程序的一种&#xff0c;英文名Wechat Mini Program&#xff0c;是一种不需要下载安装即可使用的应用&#xff0c;它实现了应用“触手可及”的梦想&#xff0c;用户扫一扫或搜一下即可打开应用。微信小程序是一种不用下载就能使用的应用&#xff0c;也是…
阅读更多...
React（react18）中组件通信06——redux-toolkit + react-redux

React（react18）中组件通信06——redux-toolkit + react-redux

React&#xff08;react18&#xff09;中组件通信06——redux-toolkit react-redux 1 前言1.1 redux 和 react-redux1.2 关于redux-toolkit1.2.1 官网1.2.2 为什么要用Redux Toolkit&#xff1f; 1.3 安装 Redux Toolkit1.4 Redux Toolkit相关API 2. 开始例子——官网例子2.1 …
阅读更多...
更直观地学习 Git 命令

更直观地学习 Git 命令

theme: condensed-night-purple 前言 本文参考于 Learn Git Branching 这个有趣的 Git 学习网站。 在该网站&#xff0c;可以使用 show command 命令展示所有可用命令。 你也可以直接访问网站的sandbox&#xff0c;自由发挥。 本地篇 基础篇 git commit git commit将暂…
阅读更多...
文件I/O与标准I/O

文件I/O与标准I/O

如果不知道inode&#xff0c;请看这篇文章inode 我们知道当打开一个文件时&#xff0c;OS会先使用inode编号在磁盘文件系统里面去寻找这个文件&#xff0c;找到以后根据文件的属性为其创建一个内核层面的结构体来描述这个文件&#xff0c;该结构体里面含有文件的属性信息&#…
阅读更多...
蓝桥杯 题库 简单 每日十题 day11

蓝桥杯 题库 简单 每日十题 day11

01 质数 质数 题目描述 给定一个正整数N&#xff0c;请你输出N以内&#xff08;不包含N&#xff09;的质数以及质数的个数。 输入描述 输入一行&#xff0c;包含一个正整数N。1≤N≤10^3 输出描述 共两行。 第1行包含若干个素数&#xff0c;每两个素数之间用一个空格隔开&…
阅读更多...
SpringMVC+统一表现层返回值+异常处理器

SpringMVC+统一表现层返回值+异常处理器

一、统一表现层返回值 根据我们不同的处理方法&#xff0c;返回的数据格式都会不同&#xff0c;例如添加只返回true|false&#xff0c;删除同理&#xff0c;而查询却返回数据。 Result类 为此我们封装一个result类来用于表现层的返回。 public class Result {//描述统一格式…
阅读更多...
测试OpenCvSharp库的模板匹配功能

测试OpenCvSharp库的模板匹配功能

微信公众号“Dotnet讲堂”的文章《c#实现模板匹配&#xff0c;并输出匹配坐标》&#xff08;参考文献1&#xff09;中介绍了采用OpenCVSharp库实现模板匹配功能&#xff0c;也即在目标图片中定位指定图片内容的示例&#xff0c;本文参照参考文献1-4&#xff0c;学习并测试OpenC…
阅读更多...
基于SpringBoot的飘香水果购物网站

基于SpringBoot的飘香水果购物网站

目录 前言 一、技术栈 二、系统功能介绍 水果信息管理 减积分管理 会员购买订单管理 首页 水果 购买水果订单管理 三、核心代码 1、登录模块 2、文件上传模块 3、代码封装 前言 随着信息互联网购物的飞速发展&#xff0c;一般企业都去创建属于自己的电商平台以及购物…
阅读更多...
让文件传输变得更简单高效——推荐强大的Mac FTP客户端Transmit 5

让文件传输变得更简单高效——推荐强大的Mac FTP客户端Transmit 5

无论是个人用户还是专业人士&#xff0c;文件传输都是我们日常工作中不可或缺的一部分。而针对Mac用户&#xff0c;Transmit 5正是一款强大且易用的FTP客户端&#xff0c;为您提供了最佳的文件传输体验。 Transmit 5在功能上非常丰富&#xff0c;可以满足各种文件传输需求。首…
阅读更多...
数据结构学习笔记——查找算法中的树形查找（平衡二叉树）

数据结构学习笔记——查找算法中的树形查找（平衡二叉树）

目录 一、平衡二叉树的定义二、平衡因子三、平衡二叉树的插入和构造&#xff08;一&#xff09;LL型旋转&#xff08;二&#xff09;LR型旋转&#xff08;三&#xff09;RR型旋转&#xff08;四&#xff09;RL型旋转 四、平衡二叉树的删除&#xff08;一&#xff09;叶子结点&a…
阅读更多...
【网络协议】TCP

【网络协议】TCP

TCP协议全称为传输控制协议(Transmission Control Protocol).要理解TCP就要从他的特性开始说&#xff0c;这些特性各自之间或多或少各有联结&#xff0c;需要以宏观视角来看待。 目录&#xff1a; 1.TCP报文格式 因为报文解释过于繁琐&#xff0c;具体内容请看这篇文章TCP报文…
阅读更多...
oracle分组合并数值带顺序

oracle分组合并数值带顺序

比如&#xff1a;有如下一张设备电子围栏位置坐标的表&#xff08;tb_equ_point&#xff09;。 equ_name:设备电子围栏名称 point_id:点位坐标id point_x:点位x坐标 point_y:点位y坐标。 附数据&#xff1a; INSERT INTO "tb_equ_point" ("EQU_NAME",…
阅读更多...
Spark SQL案例【电商购买数据分析】

Spark SQL案例【电商购买数据分析】

数据说明 Spark 数据分析 &#xff08;Scala&#xff09; import org.apache.spark.rdd.RDD import org.apache.spark.sql.{DataFrame, SparkSession} import org.apache.spark.{SparkConf, SparkContext}import java.io.{File, PrintWriter}object Taobao {case class Info(u…
阅读更多...
十六）Stable Diffusion教程：出图流程化

十六）Stable Diffusion教程：出图流程化

今天说一个流程化出图的案例&#xff0c;适用很多方面。 1、得到线稿&#xff0c;自己画或者图生图加线稿lora出线稿&#xff1b;如果想sd出图调整参数不那么频繁细致&#xff0c;则线稿的素描关系、层次、精深要表现出来&#xff0c;表现清楚。 2、文生图&#xff0c;seed随机…
阅读更多...
Java进阶篇--网络编程

Java进阶篇--网络编程

​​​​​​​ 目录 计算机网络体系结构 什么是网络协议&#xff1f; 为什么要对网络协议分层&#xff1f; 网络通信协议 TCP/IP 协议族 应用层 运输层 网络层 数据链路层 物理层 TCP/IP 协议族 TCP的三次握手四次挥手 TCP报文的头部结构 三次握手 四次挥手 …
阅读更多...
Fiddler抓取Https请求配置

Fiddler抓取Https请求配置

官网&#xff1a;https://www.telerik.com/fiddler 配置抓取https包 1.Tools->Options->Https&#xff0c;勾选下面。 2.Actions ->Trust Root Certificate.安装证书到本地 3.在手机端设置代理&#xff1a;本机ip如&#xff1a;192.168.1.168 端口号:8888。 4.手机…
阅读更多...
DockerKubernetes ❀ Service下Port端口区分

DockerKubernetes ❀ Service下Port端口区分

文章目录 概述案例 概述 在Kubernetes中&#xff0c;Service&#xff08;svc&#xff09;是一种抽象机制&#xff0c;用于将一组 Pod 暴露给其他应用程序或服务。Service 可以有三种类型的端口&#xff1a; nodePort&#xff1a;这是 Service 在节点上公开的端口。可以使用此…
阅读更多...
处理conda安装工具的动态库问题——解决记录 libssl.1.0.0 系统中所有openssl位置全览 whereis openssl

处理conda安装工具的动态库问题——解决记录 libssl.1.0.0 系统中所有openssl位置全览 whereis openssl

处理conda安装工具的动态库问题——解决记录 处理conda安装工具的动态库问题——解决记录 - 简书 解决libssl.so.1.0.0: cannot open shared object file: No such file or directory问题 - 简书 openssl 默认版本问题&#xff08;Anaconda相关&#xff09;_anaconda openssl-…
阅读更多...
嵌入式开源库之libmodbus学习笔记

嵌入式开源库之libmodbus学习笔记

socat 安装sudo apt-get install socat创建终端 socat -d -d pty,b115200 pty,b115200查看终端 ls /dev/pts/ minicom 安装 sudo apt-get install minicom链接虚拟终端 sudo minicom -D /dev/pts/3以十六进制显示 minicom -D /dev/pts/1 -H设置波特率 minicom -D /dev/pts/1…
阅读更多...
第1篇 目标检测概述 —（2）目标检测算法介绍

第1篇 目标检测概述 —（2）目标检测算法介绍

前言&#xff1a;Hello大家好&#xff0c;我是小哥谈。目标检测算法是一种计算机视觉算法&#xff0c;用于在图像或视频中识别和定位特定的目标物体。常见的目标检测算法包括传统的基于特征的方法&#xff08;如Haar特征和HOG特征&#xff09;以及基于深度学习的方法&#xff0…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发