索引优化（索引失效）

最新推荐文章于 2022-07-23 18:29:06 发布
最新推荐文章于 2022-07-23 18:29:06 发布
阅读量133 收藏
点赞数
分类专栏： MySQL 文章标签： mysql 数据库 索引 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sinat_41847122/article/details/114273848
版权

文章目录

性能下降SQL慢

  • 查询语句写的烂
  • 索引失效
    • 单值索引
    • 复合索引
  • 关联查询太多join（设计缺陷或不得已的需求）
  • 服务器调优及各个参数设置（缓冲、线程数等）

索引优化

单表

建立表
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

两表

建立表
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

总结：左连接建右表，右连接建左表。
理由：以左连接为例，左表的信息全都有，所以右表需要查找，所以建立右表index。

三表

建表SQL
在这里插入图片描述
在这里插入图片描述
总结：Join语句的优化

  • 尽可能减少Join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集”。
  • 优先优化NestedLoop的内层循环。
  • 保证Join语句中被驱动表上Join条件字段已经被索引。
  • 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置。

索引失效

建表
在这里插入图片描述
案例（索引失效）

  1. 全值匹配我最爱
    在这里插入图片描述
    也即是对建立的复合索引全部使用到，则能保证极高的性能
  2. 最佳左前缀法则：
    如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。（带头大哥不能死，中间兄弟不能断）
  3. 索引不操作
    不在索引列上作任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
    在这里插入图片描述
  4. 存储引擎不能使用索引中范围条件右边的索引列
    在这里插入图片描述
    为保证性能，中间兄弟别搞范围，要搞等值
  5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

所谓覆盖索引是指索引的列刚和能够从建立的复合索引中来读取而无需读取数据库
在这里插入图片描述
按需取数据，用多少取多少，尽量与索引一致
Extra中出现了using index很好！
使用select *的一个潜在问题就是让覆盖索引失效

  1. mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
    在这里插入图片描述
  2. is null，is not null也无法使用索引
    在这里插入图片描述
  3. like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作
    在这里插入图片描述
    like%加右边，索引不会失效

问题：解决like ‘%字符串%’时索引不被使用的方法？

  1. 字符串不加单引号索引失效

img
该问题同问题3，是索引列上做了类型转换！

VARCHAR类型绝对不能失去单引号

  1. 少用or，用它来连接时会索引失效

在这里插入图片描述
小总结
在这里插入图片描述

优化总结口诀：
全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用；

VAR引号不可丢，SQL高级也不难！

code_java_zqy
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
[MySQL高级](四) 索引优化之索引失效
睁眼看世界
05-22 3647
1. 前言 　　索引的建立是为了让我们更加高效快速的查询出结果，但是，要想充分利用起索引，我们首先要解决的最大问题就是要避免索引失效，下面我们来一起通过实例来探讨造成索引失效的情况，并通过优化SQL查询语句来避免索引失效。 　　➤ 准备工作：、 创建数据表SQL CREATE TABLE `staffs` ( `id` int(11) NOT NULL AUTO_INCREMENT...
mysql 索引优化（索引失效的原因）
qq_41585840的博客
01-26 348
索引失效的原因： 全值匹配我最爱 最佳左前缀法则，带头大哥不能死，中间兄弟不能少。 不要在索引列上做任何操作 范围条件后列上索引失效 explain select * from student where age > 1 and name = ‘王五’; 查找级别是范围，name这个索引失效了 尽量使用覆盖索引减少使用select * 使用不等于（！= 或者<&...
MYSQL索引优化（索引失效场景）
我神级欧文的博客
08-02 920
学习mysql是作为一名Java工程师必不可少的事情，但是我们只认识mysql的增删查改建表等等的sql语句其实远远不够的，对于进阶mysql来说，索引是一个很重要的部分。下面我们就来说一下在mysql中索引失效的几种场景吧。 首先先来看看测试的表结构 dept部门表 建立的索引（name，number，comment三个字段的复合索引） 测试数据 1.遵从最左前缀...
索引优化，如何避免索引失效
Alphr的博客
06-26 559
索引失效的前提是建立索引，并且在一定情况下索引失效不起作用了，下面列出的就是避免索引失效的十种情况 这里说一下全值匹配和最佳左前缀法则： 全值匹配的意思是查找的字段顺序和个数与索引的列的个数和顺序一致 我们发现只要索引中第一列没有或者缺少某个复合索引中的索引列都会导致索引失效，这是因为违反了最佳左前缀法则导致的，所以我们在写SQL时最好讲where后的查询列的个数顺序与复合索引的个数顺序一致 链接：最佳左前缀法则 有的小伙伴在测试的时候会出现明明违反了最佳左前缀法则仍然能够用上索引，建议看一看这篇文章
索引失效优化方案
清宵尚温的博客
07-28 171
mysql索引失效几个点 给索引加上函数索引失效 left(–,3)=‘abc’ 范围查询右侧索引失效 emp(age,deptid,name) 其中deptId>10 不等于失效 deptId<>20或者deptId!=20 like后首字母%索引失效 like ‘%abc’ 类型不匹配失效 emp.name=123其中name是字符串 is not null索引失效 deptId is not null is not null 优化方案 使用IFNULL(条件字段,‘自定义取值’)
oracle数据库索引失效
09-10
Oracle数据库索引失效是一个常见的性能问题，它可能导致查询效率降低，影响系统的整体性能。索引失效可能是由多种原因引起的，理解这些原因并采取适当的预防措施至关重要。 首先，当WHERE子句中的条件筛选出的数据...
重温索引优化
12-14
1. 索引失效 1.1 普通索引 场景一：SELECT * FROM user_info WHERE id + 1 = 2; 索引的列如果是表达式的一部分或者是函数的参数，则失效。 场景二：SELECT * FROM user_info WHERE name LIKE '%ook'; like查询前面...
数据库索引设计和优化
06-24
2. 避免索引失效：使用函数、IN操作符、NOT操作符等可能导致索引失效，应尽量让索引列直接参与比较。 3. 选择正确的JOIN策略：了解不同JOIN类型的性能差异，如NLJOIN、HASH JOIN和MERGE JOIN。 五、实际应用中的...
oracle索引失效的总结
08-17
### Oracle索引失效的原因及解决方法 在Oracle数据库中，索引是提高查询效率的关键工具之一。然而，在实际的应用过程中，由于多种原因可能会导致索引失效，从而影响系统的性能。本文将详细介绍Oracle索引失效的一些...
mysql索引有效性_MySQL之索引复合索引有效性
weixin_29638241的博客
02-19 105
首先这里建立一张数据表,并建立符合索引(index_A,index_B,index_C)CREATE TABLE `test_index_sequence` (`Id` int(11) NOT NULL AUTO_INCREMENT,`index_A` varchar(255) DEFAULT NULL,`index_B` varchar(255) DEFAULT NULL,`index_C` ...
MySQL索引优化（如何避免索引失效）
昔拉的博客
02-06 577
索引优化 索引失效（应该避免） 全值匹配我最爱： 最佳左前缀法则：如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。（带头大哥不能死，中间兄弟不能断） ...
如何设计有效的索引
@日月空@的博客
11-15 1118
目录使用单列索引还是多列索引？选择合适的索引列顺序减少重复、冗余以及未使用的索引如果需要索引很长的字符串，此时需要考虑前缀索引不要过度索引减少索引和数据碎片 索引可以让服务器快速地定位到表的指定位置，正确地创建和使用索引是实现高性能查询的基础。 那么该怎么设计索引呢？ 在《高性能MySQL》中提到如何评价一个索引是否适合某个查询的 “三星系统”： 索引将相关记录放到一起，则获得一星 索引中的数据顺序和查找中的排列顺序一致，则获得二星 索引中的列包含了查询需要的全部列，则获得三星 使用单列索引还是多列索引
索引失效原因
weixin_43370079的博客
06-25 823
索引失效原因 一.准备工作 创建student表，id是主键 创建复合索引 create index idx_name_age on student(name,age); 查看索引 show index from student; 二.索引失效原因 1.全值匹配我最爱 explain select * from student where name = ‘张三’ and age = 1; 2.最佳...
Mysql-索引，优化方案，以及索引失效情况：
weixin_38120374的博客
06-08 2283
声明一下：下面的优化方案都是基于 “ Mysql-索引-BTree类型 ” 的一、EXPLAIN做MySQL优化，我们要善用 EXPLAIN 查看SQL执行计划。下面来个简单的示例，标注(1,2,3,4,5)我们要重点关注的数据type列，连接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式key_l...
MySQL高级（四）、索引优化之索引失效问题
长林攻城狮的博客
05-29 332
本文以案例形式介绍索引失效问题，并在最后给出索引失效的小结。 索引失效情况 全值匹配我最爱； 最佳左前缀法则：如果索引了多列，要遵守最左前缀法则，查询从索引的最左前列开始，且不能跳过索引中的列； 不在索引列上做任何操作（计算，函数，类型转换），会导致索引失效而转向全表扫描； 存储引擎不能使用索引中范围条件右边的列，即范围之后全失效； 尽量使用覆盖索引，只访问索引的查询（索引列和查询列一致），减少selec *； MySQL在使用不等于（!= 或者<>）的时候无法使用索引会导致全表扫描； is
MySQL索引优化：索引失效以及不适合建立索引的场景
PeaK欢迎大家评论留言，一起交流Java技术
07-23 4258
索引失效以及不适合建立索引的场景，本篇文章是在看完尚硅谷宋红康老师的视频后总结的内容，希望可以和大家进行交流学习
MySql 索引优化 和 避免索引失效
方亚军的博客
06-05 414
建表和索引 索引优化法则 全值匹配案例： 最佳左前缀法则： 如果中间断了，那么只部分使用了索引，只有前面的的字段使用了索引 不在索引列上做任何操作（计算，函数，…） 索引中范围条件右边的列全失效 尽量使用覆盖索引 覆盖索引，直接去索引中取数据，不去表中取数据，大大提高效率 mysql 在使用不等于（!= 或者 <>）的时候无法使用索引导致全表扫描 is null , not null 也无法使用索引 like以通配符开头（‘%abc…’） mysql索引失效会变成全表扫描 案
MySQL索引失效、优化的方法
花无缺的博客
07-06 681
MySQL索引失效的情况
MySQL计数bug与索引优化解析
5. **避免索引失效**：避免在索引列上使用NOT、!=、或函数，这可能导致索引无法被有效利用。 理解并应用这些原则，可以显著改善MySQL的查询性能，尤其是在处理大量数据时。在实践中，还应结合EXPLAIN计划分析查询...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值