【SQL优化/索引失效的几种情况/FIC/OnlineDDL】

【SQL】索引失效的11种情况 数据库调优的大致方向:sql查询优化技术有很多,大体分为物理查询优化和逻辑查询优化:数据准备: 索引失效案例 【1】. 全值匹配 【2】. 最佳左前缀法则 在定义表时,让主键auto_increment,否则,插入一条数据时可能会移动大量数据。 如,往 1 5 8 10 15 … 100 中插9,会放在8 10 中间,因为索引默认升序排列。那么10往后的数据都要挪动,页不够时又要放到下一页,每插一条数据都这样挪一次,开销很大 我们自定义的主键列id 拥有AUTO_INCREMENT 属性,在插入记录时存 阅读详情

廖志伟的自我介绍

SQL优化

  • 针对SQL进行调整,在写SQL的时候遵循最左前缀原则,向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,范围列可以用到索引,但是范围列后面的列无法用到索引。

  • like以通配符%开头索引失效会变成全表扫描的操作。

  • 如果查询条件中含有函数或表达式,将导致索引失效而进行全表扫描。

  • 只要列中包含有 NULL 值都将不会被包含在索引中,复合索引中只要有一列含有 NULL 值,那么这一列对于此复合索引就是无效的。

  • 不要使用select *,改用select加字段名称,因为select *走的聚集索引,会进行全表扫描,如果一定要使用select *的话,mysql至少使用5.6版本,这个版本有一个离散读的优化,离散读的优化是将离散度大的列放到联合索引的前面,举个例子,select * from user where staff_id = 2 and customer_id = 584,这个时候索引优化会将customer_id放到前面,因为它的离散度更高,可以通过select count(distinct customer_id),count(distinct staff_id) from user查看列的离散度。

5.6版本有一个ICP的优化,以往根据索引查找记录,再根据WHERE条件来过滤记录。使用ICP优化后,会在取出索引的同时,直接根据WHERE条件过滤,将WHERE的部分过滤操作放在了存储引擎层。在某些查询下可以大大减少上层SQL层对记录的索取,从而提高性能。

5.6版本还有一个MRR优化,是批量处理对键值的查询操作ICP优化,减少缓冲池中页被替换的次数,使数据访问变得较为顺序。辅助索引查询得到书签后,先对主键进行排序,再按序进行查找。

  • 另外在写sql的时候,尽量使用它的一个执行计划,去看我们的索引是不是失效了。

索引失效的几种情况

  • 如果条件中有or,即使其中有部分条件带索引也不会使用。

  • 对于复合索引,如果不使用前列,后续列也将无法使用。

  • like以%开头。列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引。

  • where中索引列有运算,有函数的,不使用索引。

  • 如果mysql觉得全表扫描更快的时候,数据少的情况下,不使用索引。

FIC(Fast IndexCreation)原理

MySQL 5.5版本之前(不包括5.5)存在的一个普遍被人诟病的问题是:MySQL数据库对于索引的添加或者删除的这类DDL操作,MySQL数据库的操作过程为:❑首先创建一张新的临时表,表结构为通过命令ALTERTABLE新定义的结构。❑然后把原表中数据导入到临时表。❑接着删除原表。❑最后把临时表重命名为原来的表名。可以发现,若用户对于一张大表进行索引的添加和删除操作,那么这会需要很长的时间。更关键的是,若有大量事务需要访问正在被修改的表,这意味着数据库服务不可用。MySQL数据库的索引维护始终让使用者感觉非常痛苦。InnoDB存储引擎从InnoDB 1.0.x版本开始支持一种称为Fast Index Creation(快速索引创建)的索引创建方式——简称FIC。对于辅助索引的创建,InnoDB存储引擎会对创建索引的表加上一个S锁。在创建的过程中,不需要重建表,因此速度较之前提高很多,并且数据库的可用性也得到了提高。删除辅助索引操作就更简单了,InnoDB存储引擎只需更新内部视图,并将辅助索引的空间标记为可用(不影响附注索引的使用,因为可读,后边的同时删除四个字非常传神),同时删除MySQL数据库内部视图上对该表的索引定义即可。由于FIC在索引的创建的过程中对表加上了S锁,因此在创建的过程中只能对该表进行读操作,若有大量的事务需要对目标表进行写操作,那么数据库的服务同样不可用。此外,FIC方式只限定于辅助索引,对于主键的创建和删除同样需要重建一张表。

OnlineDDL

虽然FIC可以让InnoDB存储引擎避免创建临时表,从而提高索引创建的效率。但索引创建时会阻塞表上的DML操作(除读操作)。MySQL 5.6版本开始支持Online DDL(在线数据定义)操作,其允许辅助索引创建的同时,还允许其他诸如INSERT、UPDATE、DELETE这类DML操作,这极大地提高了MySQL数据库在生产环境中的可用性。不仅是辅助索引,以下这几类DDL操作都可以通过“在线”的方式进行操作:
❑辅助索引的创建与删除
❑改变自增长值
❑添加或删除外键约束
❑列的重命名

使用语法

ALTER TABLE tbl_name
|ADD{INDEX|KEY}[index_name]
[index_type](index_col_name,...)[index_option]...
ALGORITHM[=]{DEFAULT|INPLACE|COPY}
LOCK[=]{DEFAULT|NONE|SHARED|EXCLUSIVE}

ALGORITHM指定了创建或删除索引的算法
COPY表示按照MySQL 5.1版本之前的工作模式,即创建临时表的方式。
INPLACE表示索引创建或删除操作不需要创建临时表。
DEFAULT表示根据参数old_alter_table来判断是通过INPLACE 还是COPY的算法,该参数的默认值为OFF,表示采用INPLACE的方式。

LOCK部分为索引创建或删除时对表添加锁的情况:
(1)NONE
执行索引创建或者删除操作时,对目标表不添加任何的锁,即事务仍然可以进行读写操作,不会收到阻塞。因此这种模式可以获得最大的并发度。
(2)SHARE
这和之前的FIC类似,执行索引创建或删除操作时,对目标表加上一个S锁。对于并发地读事务,依然可以执行,但是遇到写事务,就会发生等待操作。如果存储引擎不支持SHARE模式,会返回一个错误信息。
(3)EXCLUSIVE
在EXCLUSIVE模式下,执行索引创建或删除操作时,对目标表加上一个X锁。读写事务都不能进行,因此会阻塞所有的线程,这和COPY方式运行得到的状态类似,但是不需要像COPY方式那样创建一张临时表。
(4)DEFAULT
DEFAULT模式首先会判断当前操作是否可以使用NONE模式,若不能,则判断是否可以使用SHARE模式,最后判断是否可以使用EXCLUSIVE模式。也就是说DEFAULT会通过判断事务的最大并发性来判断执行DDL的模式

InnoDB存储引擎实现Online DDL的原理是在执行创建或者删除操作的同时,将INSERT、UPDATE、DELETE这类DML操作日志写入到一个缓存中。待完成索引创建后再将重做应用到表上,以此达到数据的一致性。这个缓存的大小由参数innodb_online_alter_log_max_size控制,默认的大小为128MB。需要特别注意的是,由于Online DDL在创建索引完成后再通过重做日志达到数据库的最终一致性,这意味着在索引创建过程中,SQL优化器不会选择正在创建中的索引。

VT System故障排查指南:从License验证到硬件连接全解析 本文详细解析VT System故障排查全流程,从License验证、Real Bus模式设置到硬件连接与VT6000组网配置。针对VT7900板卡等特殊设备提供专业解决方案,并分享系统级故障的二分法排查技巧,帮助工程师快速定位和解决CANoe环境下的各类硬件识别问题。 阅读详情

相关推荐

ESP32水墨屏日历实战:低功耗自动更新与HTTPS数据解析

本文详细介绍了ESP32水墨屏日历项目的开发实践,重点讲解了低功耗自动更新与HTTPS数据解析的实现方法。通过ESP32的自动休眠功能和水墨屏的零功耗特性,结合HTTPS请求安全获取网络数据,实现了高效节能的桌面日历应用。项目代码开源,适合物联网开发者参考。

xx56789的博客 780

MYSQL 多表 LEFT JOIN 关联查询,索引失效导致全表扫描问题及解决方法

目前做商城系统二次重构,遇到原系统遗留订单到出问题,因涉及多表查询操作,原代码设计者对mysql中加了N多的索引,但是无论怎样弄,都会出现下面的情况

junh_422的博客 1万+

芯片设计新手必看:5分钟搞懂工艺角(Process Corner)到底是什么,为啥仿真总要用它?

本文详细解析了芯片设计中的工艺角(Process Corner)概念及其在仿真中的重要性。通过类比马拉松运动员的性能差异,解释了晶体管制造过程中的天然波动,并介绍了TT/FF/SS等工艺角代码的含义。文章还探讨了为何需要跑全工艺角仿真,以及现代芯片验证的完整流程,包括PVT矩阵和多模式分析(MMMC),帮助芯片设计新手快速掌握这一关键概念。

weixin_28713947的博客 248

索引覆盖与回表

懂的越多,不懂的越多 上回书说到:什么情况下,索引下推没办法提高sql查询效率? 表 info 主键 id 名称 name 值 value 别名 realname 对于info表,我们现在有(name,value)联合索引

AF_0228的博客 6173

自动驾驶仿真:python和carsim联合仿真案例

carsim内部有许多相关联合仿真的demo,simulink、labview等等都有涉及,这里简单介绍下python和carsim联合仿真的方法,虽然carsim官方有个Steer_Control.py相关的案例,但是感觉比较冗余,这里抽出重点部分和大家交流探讨下。提示:以下是本篇文章正文内容,下面案例可供参考1、这里关于solvers的细节其实都没说,因为里面确实也没什么内容好讲的,本质就是调用carsim.dll,如果你需要更多的函数解析其实可以看vs_api.h,路径在,具体内容如下图所示。

zataji的博客 9066

世界模型(World Models)进展全景调研(截至 2025-08)

A5) 2019-07(待核验) | C-SWM:Contrastive Structured World Models(Kipf et al.)A6) 2019-07(待核验) | SLAC:Stochastic Latent Actor-Critic(Lee et al.)A3) 2015-12(背景注记,待核验) | Embed to Control(E2C,Watter et al.)Nature 2020)Repro/Code:werner-duvaud/muzero-general(社区)

u013250861的博客 489

MYSQL性能调优03_在什么情况下会导致索引失效从而进行全表扫描

在什么情况下会导致索引失效从而进行全表扫描

所得皆惊喜 1394

解锁RK1126与SC132GS摄像头适配密码:技术与代码全解析

本文详细介绍了瑞芯微RK1126芯片与SC132GS摄像头的适配方法。首先分析了RK1126的四核Cortex-A7处理器、Mali-T860 GPU等硬件特性,以及SC132GS的130万像素、全局快门等技术参数。重点阐述了基于MIPI CSI接口的硬件连接原理和基于V4L2框架的软件驱动设计,并提供了完整的初始化、数据采集和图像处理代码实现。针对常见连接和图像质量问题给出了解决方案,展现了该组合在智能安防、工业检测等领域的应用潜力,为开发者提供了实用的技术参考。

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

什么情况下数据库索引会失效?

1、在where子句中进行null值判断的话会导致引擎放弃索引而产生全表扫描 2、避免在where子句中使用!= ,< >这样的符号,否则会导致引擎放弃索引而产生全表扫描 3、避免在where子句中使用or来连接条件,因为如果俩个字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描 4、避免在where子句中使用like模糊查询 5、在使用联合索引是要注意最左原则,例如如果当前联合索引是index(a,b,c),那么如果where子句中有a就会用到联合索引,但是如果只用到b,c就会失去索引效果

YangYoung_的博客 924

C++软件异常分析概述

本文将详细讲述Windows系统中软件异常的分类以及常用的排查方法,给大家提供一个借鉴与参考。

dvlinker的技术专栏 4万+

基于STM32的智能电子秤设计毕业论文

摘要:本文设计了一款基于STM32F103C8T6的智能电子秤系统,通过HX711称重传感器实现高精度测量,采用OLED显示和矩阵键盘完成人机交互。系统包含重量采集、数据处理、单价设置、去皮清零及总价计算等功能模块,经测试测量误差小于0.1%,稳定性良好。研究实现了低成本、高可靠性的智能称重方案,为后续无线传输和AI算法集成奠定了基础。关键词:STM32;智能电子秤;HX711;OLED显示;矩阵键盘。

3254968421的博客 851

索引失效的十种情形

在 MySQL 数据库中,索引可以大幅提高查询效率,但在某些情况下,索引可能会失效,导致 MySQL 进行全表扫描(Full Table Scan,简称 FTS)。掌握这些索引失效场景,可以帮助优化 MySQL 查询性能,减少不必要的全表扫描!:表数据变化较大,但索引统计信息未更新,导致 MySQL 误判索引是否可用。连接时,如果其中一个字段没有索引,MySQL 不会使用索引。:索引选择性(不同值的比例)太低,优化器认为全表扫描更高效。:MySQL 需要进行类型转换,导致索引失效。

SAFE20242034的博客 1141

VTK-9.5.0 1D2D3DWidget实现灰度图+深度图+3D点云显示(可轻松集成到自己的项目)

VTK1D2D3DWidget是一个基于Qt和VTK开发的3D点云可视化工具,支持RGB和深度点云数据的显示与处理。主要功能包括点云加载、可视化、保存为PLY格式文件,以及背景颜色、点间隔、坐标轴显示等参数的自定义设置。该工具采用模块化设计,核心组件包括主窗口管理类、VTK渲染组件和自定义鼠标交互样式,依赖Qt5+和VTK9.5.0库实现跨平台支持。项目提供完整的源码和构建指南,适用于点云数据的快速展示与分析。

Qt历险记,专注Qt技术,三年医疗设备研发经验,1年机器视觉研发经验,半年柔性传感研发经验。 390

MySQL索引失效的场景,什么情况下会造成MySQL索引失效

目录 一、准备工作,准备sql 二、索引是什么、使用explain关键字来解析sql使用索引情况 三、索引的使用与失效场景 1.使用全部复合索引(索引正常使用) 2.最佳左前缀法则 3.在索引列上做任何操作(计算、函数、自动或手动类型转换),会导致索引失效而转向全表扫描。 4.不能使用索引中范围条件右边的列。(范围条件之后的索引全失效,但是范围条件之前的和范围条件的还是用到索引的。) 5.尽量使用覆合索引(只访问索引的查询(索引列和查询列一致)),减少select *。 6.mysql在使

秃了也弱了 6278

廖雪峰Python教程[完整版]

廖雪峰的python教程整理的,已经排好版了,而且还是完整的,适合小白

android 蓝牙通讯demo

android 下蓝牙通讯demo,实现了发现蓝牙,使能蓝牙,通过蓝牙发送消息。

哪些情况下索引会失效?

索引并不是时时都会生效的,比如以下几种情况,将导致索引失效: 如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)      注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引   2.对于多列索引,不是使用的第一部分,则不会使用索引   3.like查询是以%开头   4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引      5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引 此外,查

repoman的博客 1万+

Probability and Statistics - 4th edition

Probability and Statistics - 4th edition

Mysql面经:什么是MRR和FIC?--03

1,什么是MRR? MRR,全称「Multi-Range Read Optimization」。 简单说:MRR 通过把「随机磁盘读」,转化为「顺序磁盘读」,从而提高了索引查询的性能。 他的过程其实这样的, 再上一遍中,使用回表的过程中,二级索引返回大量数据,比如一千条,数据取出来是随机乱序的,所以呢,这一千条数据进行排序,那么我们在主键索引中就可以对他进行范围查询了,而不是重新在根节点中一个个遍历。 就是一个针对优化的优化。 还是不理解的,可以去看知乎大佬的文章:MySQL 的 MRR 到底是什么? 2,

weixin_44242433的博客 1376

VC中sqlite的使用

在VC中对sqlite的使用,实现数据库大批数据的快速插入,读取数据库中的数据在ListCtrl中显示

sql 全解(索引、索引失效、sql优化)

1. 小表驱动大表 2. 建索引,一张表不超过5个索引 3. 走索引,尽量满足最左匹配,避免索引失效

demo_yo的博客 1099

详解MySQL索引失效的几种情况

MySQL索引失效的几种情况

m0_57614677的博客 1万+

MySQL InnoDB索引

1. InnoDB索引概述InnoDB可支持如下几种索引: B+树索引:由平衡二叉树演化而来,给定键值不能找到具体的行,只是找到数据行所在的页,然后将该页读入内存,再在内存中进行查找想要的数据. 全文索引 哈希索引:InnoDB支持的哈希索引是自适应的,会根据表的使用情况自动为表生成哈希索引,不能人为干预一张表的是否生成哈希索引. 2. B+树索引B+树索引的一个特点是“高扇出性”(可理解为每个节点

ZhangXL的专栏 516

MySQL between…and…索引失效原因

背景:需要筛选这个月内新增的作品,对日期列使用between…and进行索引查找,表数据10w,到5000左右索引失。查询的sql语句查询数量超过表总数量的多少会失效?

m0_48926849的博客 1483

【数据库】Mysql中between...and引起的索引失效

mysql索引失效问题

等待中的小码农 7955

sql索引失效的情况,以及优化方式。

通过上述例子,我们可以看到在使用索引时需要注意的几个问题,包括不使用索引列进行查询、使用函数或者表达式进行查询、对索引列进行类型转换、在索引列上进行计算、对索引列进行排序或者分组以及使用 NOT IN 或者 进行查询。总之,要保证 MySQL 索引的有效性,需要注意查询条件中是否包含索引列,避免使用函数、表达式、计算、排序、分组、NOT IN、、LIKE、隐式转换、OR 连接等操作,以及注意数据量大小。如果需要使用 LIKE 查询,可以使用全文索引或者将查询条件转换成范围查询。

ZWH的博客 1915

between函数为导致索引失效吗?

这会导致MySQL优化器放弃使用索引而选择全表扫描,因为对于优化器来说,全表扫描可能更快。函数可能导致索引失效的情况是在范围涵盖了大部分索引数据时。函数用于WHERE条件时,索引可能会失效的情况是当被比较的列(例如日期或数字列)上存在索引,但是。:如果查询的数据范围很广,可以将查询分割成多个较小的范围查询,每个查询只涵盖索引中的一部分数据。索引,因为几乎所有的行都在这个日期范围内,这时进行全表扫描可能比使用索引更快。:确保索引覆盖了查询中涉及的所有列,或者考虑创建更适合的索引。举个例子,假设有一个。

hello_boyu的博客 2354

FIC(Fast Index Creation)原理

MySQL 5.5版本之前(不包括5.5)存在的一个普遍被人诟病的问题是:MySQL数据库对于索引的添加或者删除的这类。对于辅助索引的创建,InnoDB存储引擎会对创建索引的表加上一个S锁。由于FIC在索引的创建的过程中对表加上了S锁,因此在创建的过程中只能对该表进行读操作,若有大量的事务需要对。可以发现,若用户对于一张大表进行索引的添加和删除操作,那么这会需要很长的时间。更关键的是,若有大量事务。视图,并将辅助索引的空间标记为可用(不影响附注索引的使用,因为可读,后边的同时删除四个字非常传神),同。

li418418qq的专栏 396

彻底搞懂 SQL 优化:EXPLAIN、索引失效与性能瓶颈全拆解

索引不是万能的,写 SQL 时要避免函数、运算、类型转换、错误顺序、模糊匹配等操作,才能让索引乖乖发挥作用!优先找出慢 SQL使用发现当前执行慢的 SQL;使用慢查询日志定位;配合 APM(如阿里云 DBA助手、腾讯 DBbrain)查看执行耗时最多的 SQL。“查慢因、看执行、调结构、用索引、搞缓存、分冷热、控事务”这几个字基本就是 SQL 优化的大纲思路。不同项目不同瓶颈,优化策略要结合业务和数据特性,逐层深入。

一键难忘的博客 2903

mysql ddl 进度_关于MySQL Online DDL

1. Online DDL在 MySQL 5.1 (带InnoDB Plugin)和5.5中,有个新特性叫 Fast Index Creation(下称 FIC),就是在添加或者删除二级索引的时候,可以不用复制原表。对于之前的版本对于索引的添加删除这类DDL操作,MySQL数据库的操作过程为如下:首先新建Temp table,表结构是 ALTAR TABLE 新定义的结构然后把原表中数据导入到这个...

weixin_42350940的博客 605
上一篇: 【索引数据结构】
下一篇: 【聚集索引、辅助索引、覆盖索引、联合索引、filesort过程】
Java程序员廖志伟
Java程序员廖志伟 优质创作者: Java技术领域 优质创作者: Java技术领域 领域专家: 后端开发技术领域 领域专家: 后端开发技术领域
博客等级 码龄8年 2万+粉丝 1902原创
评论 5
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Java程序员廖志伟

赏我包辣条呗

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值