SQL查询优化---单表使用索引及常见索引失效优化

这篇具有很好参考价值的文章主要介绍了SQL查询优化---单表使用索引及常见索引失效优化。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

如何避免索引失效

1、全值匹配

系统中经常出现的sql语句如下:

 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30  
 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4
 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'  

优化后

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME)

建立索引前
SQL查询优化---单表使用索引及常见索引失效优化,Java,java,sql,数据库,spring boot,mysql

索引后
SQL查询优化---单表使用索引及常见索引失效优化,Java,java,sql,数据库,spring boot,mysql

2、最佳左前缀法则

如果系统经常出现的sql如下:

 EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30   AND emp.name = 'abcd'   

或者

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1   AND emp.name = 'abcd'   

那原来的idx_age_deptid_name 还能否正常使用?
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

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

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

如果系统经常出现的sql如下:

 EXPLAIN SELECT  SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc' ; 

那么索引 idx_age_deptid_name这个索引还能正常使用么?
SQL查询优化---单表使用索引及常见索引失效优化,Java,java,sql,数据库,spring boot,mysql

如果这种sql 出现较多
应该建立:

create index idx_age_name_deptid on emp(age,name,deptid)

效果
SQL查询优化---单表使用索引及常见索引失效优化,Java,java,sql,数据库,spring boot,mysql

# drop index idx_age_name_deptid on emp

5、mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

 CREATE INDEX idx_name ON emp(NAME)
  EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE   emp.name <>  'abc' 

SQL查询优化---单表使用索引及常见索引失效优化,Java,java,sql,数据库,spring boot,mysql

6、is not null 也无法使用索引,但是is null是可以使用索引的

  UPDATE emp SET age =NULL WHERE id=123456;
   下列哪个sql语句可以用到索引
  EXPLAIN SELECT * FROM emp WHERE age IS NULL
  EXPLAIN SELECT * FROM emp WHERE age IS NOT NULL

SQL查询优化---单表使用索引及常见索引失效优化,Java,java,sql,数据库,spring boot,mysql

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

SQL查询优化---单表使用索引及常见索引失效优化,Java,java,sql,数据库,spring boot,mysql

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

SQL查询优化---单表使用索引及常见索引失效优化,Java,java,sql,数据库,spring boot,mysql文章来源地址https://www.toymoban.com/news/detail-721245.html

9、总结

假设index(a,b,c)
      														
where a = 3			索引是否被使用:Y,使用到a
where a = 3 and b = 5						索引是否被使用:Y,使用到a,b
where a = 3 and b = 5 and c = 4										索引是否被使用:Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4  或者 where c = 4		索引是否被使用:N
where a = 3 and c = 5	     索引是否被使用:使用到a, 但是c不可以,b中间断了
where a = 3 and b > 4 and c = 5	 索引是否被使用:使用到a和b, c不能用在范围之后,b断了
where a is null and b is not null  	 索引是否被使用: is null 支持索引 但是is not null 不支持,所以 a 可以使用索引,但是  b不可以使用
where a <> 3   	 索引是否被使用:不能使用索引
where   abs(a) =3	索引是否被使用:不能使用 索引
where a = 3 and b like 'kk%' and c = 4	索引是否被使用:Y,使用到a,b,c
where a = 3 and b like '%kk' and c = 4	索引是否被使用:Y,只用到a
where a = 3 and b like '%kk%' and c = 4	索引是否被使用:Y,只用到a
where a = 3 and b like 'k%kk%' and c = 4	索引是否被使用:Y,使用到a,b,c

到了这里,关于SQL查询优化---单表使用索引及常见索引失效优化的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL 索引优化实践(单表)

          索引是为了高效查询排好序的数据结构,当表数据量到达一个量级没有对应索引帮助查询耗时会很长,MySQL资源开销也会非常大,当然索引也不能随意创建,要做到 尽量少的索引解决尽量多的问题 ,这里会对一些业务场景做索引优化演示,这篇文中只介绍单表索引优

    2024年02月07日
    浏览(49)
  • MySQL索引3——Explain关键字和索引使用规则(SQL提示、索引失效、最左前缀法则)

    目录 Explain 索引性能分析 Id ——select的查询序列号 Select_type——select查询的类型 Table——表名称 Type——select的连接类型 Possible_key ——显示可能应用在这张表的索引 Key——实际用到的索引 Key_len——实际索引使用到的字节数 Ref    ——索引命中的列或常量 Rows——预

    2024年02月14日
    浏览(44)
  • 查询优化之单表查询

    查询 category_id 为1 且 comments 大于 1 的情况下,views 最多的 article_id 我们一般会使用以下语句查询 这个sql语句的type是all,并且Extra里面出现了Using filesort,这个必须优化 添加索引 我们再次进行上面语句的查询 我们可以看到type变成了range,但是extra里面却是Using filesort ,这个性能

    2024年02月01日
    浏览(32)
  • 索引使用——SQL提示、覆盖索引、回表查询

    use index是给MySQL一个提示,在执行的时候尽量使用该索引,至于MySQL最后会不会接收该建议,则不一定。 ignore index是mysql忽略某个索引。 force index则强制mysql使用该索引。mysql别无选择。 覆盖索引和回表查询是两个相对的概念,先讲一下回表查询。 回表:指的是先走二级索引去

    2024年02月16日
    浏览(45)
  • SQL Server 单表数据查询

    提示: 本篇文章详细介绍怎样向SQLServer中导入表格数据,导入之后根据不同的查询条件完成查询任务. 导入成功之后,可以点开表格查看内容: (如下图即导入成功) 其他表同理可得,也可以同时导入 (1)方法一 (2)方法二 (1)方法一 (2)方法二 (1)方法一 (2)方法二 (1)方法一 (2)方法二

    2023年04月08日
    浏览(41)
  • 【SQL开发实战技巧】系列(二):简单单表查询

    【SQL开发实战技巧】系列(一):关于SQL不得不说的那些事 【SQL开发实战技巧】系列(二):简单单表查询 【SQL开发实战技巧】系列(三):SQL排序的那些事 【SQL开发实战技巧】系列(四):从执行计划讨论UNION ALL与空字符串UNION与OR的使用注意事项 【SQL开发实战技巧】系列

    2024年01月16日
    浏览(40)
  • 如何使用索引加速 SQL 查询 [Python 版]

    推荐:使用 NSDT场景编辑器助你快速搭建可二次编辑器的3D应用场景 假设您正在筛选一本书的页面。而且您希望更快地找到所需的信息。你是怎么做到的?好吧,您可能会查找术语索引,然后跳转到引用特定术语的页面。SQL 中的索引的工作方式与书籍 中的索引 类似。 在大多

    2024年02月13日
    浏览(119)
  • SQL笔记 -- 索引失效情况

    对于多列索引,过滤条件要使用索引必须按照索引建立时的顺序,依次满足,一旦跳过某个字段,索引后面的字段都无法被使用。 例如以下情况,索引从左到右的顺序为age,classId,name,但是在查询时跳过了age,因此该查询无法使用索引。 查询条件中使用了函数会导致索引失

    2024年01月20日
    浏览(38)
  • Mysql中索引优化和失效

    要了解索引优化和索引失效的场景就要先了解什么是索引 索引是一种有序的存储结构,按照单个或者多个列的值进行排序,以提升搜索效率。 索引的类型 UNIQUE唯一索引 不可以出现相同的值,可以有NULL值。 INDEX普通索引 允许出现相同的索引内容。 PRIMARY KEY主键索引 不允许出

    2024年02月20日
    浏览(46)
  • like模糊查询导致索引失效的解决方案

    我们在使用like 通配符有下面三种用法,其中第1和第2的用法会引起索引失效的问题。这是因为MySQL的索引是按照从左到右的顺序进行匹配的,而前导通配符(比如%abc)会导致无法按照索引顺序进行匹配。 1、like \\\'%name\\\'   —— 失效 2、like \\\'%name%\\\'  —— 失效 3、like \\\'name%\\\' 现在来

    2024年02月11日
    浏览(43)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包