MySQL—— MySQL的索引最佳实践

MySQL索引使用技巧:优化数据库性能的最佳实践 在MySQL数据库中,索引是提高查询性能的关键因素之一。然而,不当地使用索引也可能导致性能下降。本文将介绍一些MySQL索引使用的最佳实践,帮助您优化数据库性能。 阅读详情

文章目录

 

1、测试表

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

 INSERT INTO employees(name,age,position,hire_time) VALUES('zhz',22,'manager',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei',23,'dev',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

2、最佳实战

2.1、全值匹配

EXPLAIN SELECT
	*
FROM
	employees
WHERE
	NAME = "zhz";
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

在这里插入图片描述

EXPLAIN SELECT
	*
FROM
	employees
WHERE
	NAME = "zhz"
AND 
	age = 22;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

在这里插入图片描述

EXPLAIN SELECT
	*
FROM
	employees
WHERE
	NAME = "zhz"
AND 
	age = 22
AND 
	position = "manager";
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

在这里插入图片描述

2.2、最左前缀法则

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

1、EXPLAIN SELECT
	*
FROM
	employees
WHERE
	NAME = 'Bill'
AND age = 31;

2、EXPLAIN SELECT
	*
FROM
	employees
WHERE
	age = 30
AND position = 'dev';

3、EXPLAIN SELECT
	*
FROM
	employees
WHERE
	position = 'manager';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

我们可以发现,只有第一个可以打到索引上

2.3、不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描

EXPLAIN SELECT
	*
FROM
	employees
WHERE
	NAME = 'zhz';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

在这里插入图片描述
给hire_time增加一个普通索引:

ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
EXPLAIN select * from employees where date(hire_time) ='2021-01-15';
  • 1
  • 2

在这里插入图片描述
转化为日期范围查询,有可能会走索引:

EXPLAIN select * from employees where hire_time >='2018‐09‐30 00:00:00' and hire_time <
='2018‐09‐30 23:59:59';
  • 1
  • 2

在这里插入图片描述
还原最初索引状态

ALTER TABLE `employees` DROP INDEX `idx_hire_time`;
  • 1

2.4、存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM employees WHERE name= 'zhz' AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE name= 'zhz' AND age > 22 AND position ='manager';
  • 1
  • 2

在这里插入图片描述
在这里插入图片描述

2.5、尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少 select * 语句

EXPLAIN SELECT name,age FROM employees WHERE name= 'zhz' AND age = 23 AND position ='manager';
  • 1

在这里插入图片描述

EXPLAIN SELECT * FROM employees WHERE name= 'zhz' AND age = 23 AND position ='manager';
  • 1

在这里插入图片描述

2.6、mysql在使用不等于(!=或者<>),not in ,not exists 的时候无法使用索引会导致全表扫描< 小于、 > 大于、 <=、>= 这些,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

EXPLAIN SELECT * FROM employees WHERE name != 'zhz';
  • 1

在这里插入图片描述

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

EXPLAIN SELECT * FROM employees WHERE name is null
  • 1

在这里插入图片描述

2.8、like以通配符开头(’$abc…’)mysql索引失效会变成全表扫描操作

EXPLAIN SELECT * FROM employees WHERE name like '%Lei'
  • 1

在这里插入图片描述

EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'
  • 1

在这里插入图片描述
问题:解决like’%字符串%'索引不被使用的方法?
a)使用覆盖索引,查询字段必须是建立覆盖索引字段

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';
  • 1

在这里插入图片描述
b)如果不能使用覆盖索引则可能需要借助搜索引擎

2.9、字符串不加单引号索引失效

EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;
  • 1
  • 2

在这里插入图片描述
在这里插入图片描述

2.10、少用or或in,用它查询时,mysql不一定使用索引,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引,详见范围查询优化

EXPLAIN SELECT * FROM employees WHERE name = 'zhz' or name = 'HanMeimei';
  • 1

在这里插入图片描述

2.11、范围查询优化

给年龄添加单值索引

ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;
explain select * from employees where age >=1 and age <=2000;
  • 1
  • 2

在这里插入图片描述
没走索引原因:mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子,可能是由于单次数据量查询过大导致优化器最终选择不走索引
优化方法:可以将大的范围拆分成多个小范围

explain select * from employees where age >=1 and age <=1000;
explain select * from employees where age >=1001 and age <=2000;
  • 1
  • 2

在这里插入图片描述
在这里插入图片描述
还原最初索引状态

ALTER TABLE `employees` DROP INDEX `idx_age`;
  • 1

3、总结

在这里插入图片描述
like KK%相当于=常量,%KK和%KK% 相当于范围

‐‐ mysql5.7关闭ONLY_FULL_GROUP_BY报错

select version(), @@sql_mode;SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
MySQL 索引最佳实践 MySQL 索引最佳实践很不错,虽然是英文版 立即下载

相关推荐

Quartus原理图设计入门:从半加器到4位全加器的保姆级教程

本文提供Quartus原理图设计入门教程,详细讲解从半加器到4位全加器的实现过程。通过原理图输入法,逐步构建数字电路设计基础模块,涵盖环境搭建、逻辑设计、层次化实现及功能验证等关键步骤,帮助FPGA初学者快速掌握Quartus工具链和加法器设计方法。

weixin_29291185的博客 467

mysql索引实践

1.全值匹配 2.最左前缀法则 如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。 3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描 4.存储引擎不能使用索引中范围条件右边的列 5.尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少 select * 语句 6.mysql在使用不等于(!=或者<>),not in ,not exists

小丑竟是我自己 444

超详细网安学习路线(Day1-Day2)

渗透测试(Penetration Testing),就是一种通过模拟恶意攻击者的技术与方法,挫败目标系统安全控制措施,取得访问控制权,并发现具备业务影响后果安全隐患的一种安全测试与评估方法。简单来说,就是合法去入侵别人的系统,来帮助其找出漏洞,增强防护能力。

aobulaien001的博客 3392

mysql索引实践_MySQL 索引最佳实践

原文请关注 这里这是 文章 的翻译,在翻译过程中,会对其中涉及到的语句加上一些个人理解以及 SQL 语句的执行,并进行特别的标注。1. 你做了一个很棒的选择,因为:对于普通开发者和 DBA,理解索引都是非常重要的;对于大量的生产环境上的问题,糟糕的索引要负有责任;索引没有非常的高深。2. MySQL 索引事项理解索引;为自己的应用选择最好的索引;解决常见的 MySQL 限制。3. 废话少数,索引有...

weixin_36116139的博客 189

03. 线性规划与混合整数规划 (LP/MIP)

本文介绍了OR-Tools中的线性规划(LP)和混合整数规划(MIP)求解器。主要内容包括:1) LP/MIP的基本概念和数学形式,对比了LP、MIP、ILP和BIP的区别;2) OR-Tools支持的求解器类型,如GLOP、SCIP等,以及它们各自的特点和适用场景;3) 详细展示了如何使用GLOP求解器进行线性规划求解,包括变量定义、约束添加和目标函数设置;4) 介绍了SCIP求解器的配置和使用方法;5) 讨论了变量类型、约束类型、多目标优化等进阶功能。文章通过Java代码示例演示了如何实现一个完整的优化

智慧是做事用的 161

3、Tone Mapping色调映射(Advanced High Dynamic Range Imaging )

3、Tone Mapping色调映射(Advanced High Dynamic Range Imaging) 如今,大多数可用的显示设备无法本地显示HDR内容。 入门级显示器的对比度仅为200:1。 尽管高端液晶电视具有更高的对比度,但平均对比度约为10,000:1,但通常每个色彩通道的离散度为8位,很少为10位。 这意味着色调限制为255。在过去的二十年中,研究人员花费了大量时间和精力来压缩HDR图像和视频的范围,以便可以在LDR显示器上更“自然”地看到数据。 图3.1 色调映射和真实场景之间.

ISP行业的忠实粉丝 1867

MySQL--联合索引应用细节&应用规范

3)索引本身失效,统计信息不真实(过旧),对于表内容变化比较频繁的情况下,有可能会出现索引失效,一般是删除重建,如果有一条select语句平时查询很快,突然有一天很慢,应该是索引失效,统计信息不真实、在mysql中,索引下推允许在存储引擎层过滤索引中的记录,而不是在server层进行,如果查询包含多个条件并且索引不是很精确时,索引下推可以在存储引擎层进行更多的过滤工作,从而减少不必要的IO.辅助索引条件查询时,先扫描辅助索引,获得ID值,放在read_rnd_buffer中,由MRR进行排序后,回表查询。

数据库学习ing 1505

AMBA-CHI协议详解(十)

该规范提供了一个Request Retry(请求重试)机制,确保当请求到达一个Completer 时,它要么被接受,要么被给予一个RetryAck响应,以防止阻塞REQ通道。

Crazzy_M的博客 989

mysql索引最佳实践 及 索引优化

对于辅助的联合索引(name,age,position),正常情况按照最左前缀原则,SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager' 这种情况只会走name字段索引,因为根据name字段过滤完,得到的索引行里的age和 position是无序的,无法很好的利用索引。

weixin_43874650的博客 412

水与光的奇妙“切割之舞”:水导激光切割技术探秘

水导激光切割技术的诞生,是材料加工领域不断追求高精度、高质量加工的成果。传统激光切割技术在加工过程中,材料会因瞬间高温而产生热变形、热影响区大以及切割面粗糙等问题,这些弊端在对一些高精度、热敏性材料的加工中尤为突出 ,迫切需要一种新的技术来克服这些难题。水导激光切割技术的起源可以追溯到 20 世纪 80 年代。1986 年,德国公司 Aesculap-Werke AG 首次提出了将水流引入激光切割过程的创新想法,并制造出首代水导激光设备。不过,当时的技术尚处于雏形阶段,仅仅是在光纤出口添加了一小柱水流,激光

远程部署调试 运行安装 项目调试 二次开发 项目技术新 持续迭代 部分源码免费分享 1675

北斗导航 | 卫星信号捕获跟踪原理解析

卫星信号的捕获与跟踪是导航接收机的"眼睛",其性能直接决定定位精度、动态适应性和抗干扰能力。随着北斗三号全球组网完成,新一代接收机正朝着。两大核心阶段,二者共同构成定位的基础。调制,频谱主瓣宽度达4.092MHz,较传统BPSK调制抗干扰能力提升3dB[38]。的目标持续演进,在自动驾驶、精密农业等领域展现出巨大应用潜力。二维空间中搜索峰值相关能量[4]。卫星导航接收机对信号的处理可分为。北斗三号B1C/B2a信号采用。北斗三号新增的B1C信号采用。

尘世冰封的专栏 783

MySQL 索引介绍和最佳实践

MySQL 索引介绍和最佳实践

kerwin的博客 7290

【高级强化学习:算法、优化与泛化】第13章 扩散模型策略优化(Diffusion Policy Optimization)

\quad$ 计算均值 $\mu_t = \frac{1}{\sqrt{\alpha_t}} \left( x_t - \frac{1 - \alpha_t}{\sqrt{1 - \bar{\alpha}_t}} \tilde{\epsilon} \right)$.$\quad$ 组合得分估计: $\tilde{\epsilon} = \epsilon_{\text{unc}} + w \cdot (\epsilon_{\text{con}} - \epsilon_{\text{unc}})$.

u012133341的博客 339

MySql优化之索引最佳实践

假设index(a,b,c)where语句是否使用索引where a=3使用到a使用到a,bwhere b = 3 或者where b=3 and c=4或者where c=4未使用,不符合最佳最前缀法则使用到a,未使用到c,b中间断了,无法排序使用到a和b,未使用到c,因为c数据范围查询右边的使用到a,b和c;b属于like开头的,使用了索引使用到a,未使用到b和c,b属于like结尾的,中断了使用到a,道理同上使用到a,b和c。

m0_68472894的博客 1067

C# WinForm 基于DirectShow.dll编写的程序,可边预览边保存视频,音频和图片

基于微软的DirectShow.dll的二次开发1.自动识别连接的摄像头跟麦克风且可以对其进行选择(默认选中第一个)2.可录制wmv和avi两种格式的视频并且都带有音频,avi可以录制的分辨率和帧数进行选择,wmv默认进行了淸晰度优化如不喜欢可去除代码中有相关提示3.可在预览点开之后进行拍照,也可边录制视频边拍照4.添加了视频和音频的压缩功能(酌情使用)这次二次开发发现C#对DirectShow的相关信息太少,这次做完也不敢藏私共享出来,还望大家指正

Probability and Statistics - 4th edition

Probability and Statistics - 4th edition

MySQL 索引的最佳实践分享

作者爱说话 忙忙碌碌的一周又过去了,这周最大的乐趣就是买了个小音箱,又可以下班的时候开始愉快的开始练琴了,程序员嘛,还是得培养点艺术细菌。哈哈 这周本来没想好写什么东西,刚好周五公司进行了内部技术分享,内容为《MySQL索引讲解 + 最佳实践》,摘出了几条重要,和较常见的和大家一起分享,当然还有很多没有涉及到,关于索引的知识点,就不具体展开讲解,本来想转载一篇文章与大家分享,但是卑微的我还没加上...

qq_39010254的博客 348

jre9下载 jdk9

(Java SE Runtime Environment 9.0) 9.0u175 官方最新版 【x86|x64】

Mysql Explain与索引最佳实践学习总结

Explain与索引最佳实践学习总结

weixin_45161367的博客 510

VB摄像头监控

该VB模块可对摄像头捕获的画面进行录像等,可在创建的窗口进行控制

MySQL索引优化最佳实践

,<>,not in ,not exists,is null ,is not null查询,否则会走全表扫描。尽量避免使用in或者or查询,MySQl会进行成本计算决定是否使用索引。使用<,>,<=,>=时MySQL会进行成本计算评估是否使用索引。中做任何操作(计算,函数,类型转换)like查询时遵循最左前缀查询。范围查询优化,大范围拆分小范围。减少select * 语句,

GzlAndy的博客 488

【MySQL索引列最佳实践】

MySQL索引列最佳实践示例表的创建全值匹配最左前缀法则不在索引列上做任何操作存储引擎不能使用索引中范围条件右边的列尽量使用覆盖索引不等于会使索引失效is null, is not null 也无法使用索引like以通配符开头('$abc...')mysql索引失效会变成全表扫描操作字符串不加单引号索引失效少用or或in范围查询优化 示例表的创建 CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varch

qq_36348243的博客 675

【MySQL】第3节|Explain详解与索引最佳实践

覆盖索引定义:mysql执行计划explain结果里的key有使用索引,如果select后面查询的字段都可以从这个索引的树中获取,这种情况一般可以说是用到了覆盖索引,extra里一般都有using index;:扫描全索引就能拿到结果,一般是扫描某个二级索引,这种扫描不会从索引树根节点开始快速查找,而是直接对二级索引的叶子节点遍历和扫描,速度还是比较慢的,这种查询一般为使用覆盖索引,二级索引一般比较小,所以这种通常比ALL快一些。例如:在索引列中选取最小值,可以单独查找索引来完成,不需要在执行时访问表。

泽韦德的专栏 880

mysql索引 最佳_mysql索引最佳实践

索引最佳实践使用的表CREATE TABLE `employees` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',`position` varchar(20) NOT ...

weixin_39660408的博客 154

三、mysql——索引最佳实践

sql存储过程 CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位', `hire_time` timestamp

lovesman的博客 409

Mysql优化之Explain详解、索引最佳实践

使用EXPLAIN关键字可以模拟优化器执行SQL语句,分析你的查询语句或是结构的性能瓶颈,在 select 语句之前增加 explain 关键字,MySQL 会在查询上设置一个标记,执行查询会返回执行计划的信息,而不是执行这条SQL。

Z_Lisa的博客 1万+

mysql索引最佳实践-笔记

在网上找到一篇mysql索引最佳实践的ppt,感觉很好,记一下笔记: mysql使用索引干什么? 1、数据查找。 2、排序。 3、避免读“数据” 4、特殊优化。 我们可能用到的索引类型: BTREE索引:大部分mysql的索引都是这种索引。 RTREE索引:只有MyISAM引擎用,for GIS HASH索引:存储引擎有 MEMORY, NDB BITMAP索引:目

baiyangfu的专栏 2035

MySQL 索引最佳实践之问题反馈

MySQL 索引最佳实践之问题反馈 我之前发布的 PPT —— 《MySQL 索引最佳实践》中,有很多人提了很多问题,我没有时间一一回答,于是我决定把这些问题集中在一起进行回答。 问:我们团队中的一人想要使用 bigint 字段类型来代替 25-30 长度的 varchar 类型来存储 CRC64 数据,然后将索引也改成 bigint 的索引,这会节省索引的空间。请问这否是合理的性能优化方

haoke 2万+
上一篇: 灵感来了挡也挡不住,基于Redis解决业务场景中延迟队列
下一篇: 一篇与面试官和蔼交流的深入了解JVM(JDK8)
爱玛士
博客等级 码龄7年 720粉丝 253原创
评论 1
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值