MySQL查询优化方案汇总(索引相关)

这篇具有很好参考价值的文章主要介绍了MySQL查询优化方案汇总(索引相关)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

索引相关

类型隐式转换 大坑

**字段filed1是varchar类型,且加了索引,如果 where filed1 = 123; type 可能是all,因为123是数字类型,mysql内部会用函数做隐式转换,用了函数,索引就失效了。**

大数据深度分页,用主键

select field1,field2 from table limit 100000,10;
select field1,field2 from table where id > 100000 limit 10;

避免使用MySQL函数

MySQL内置了很多函数,使用函数可能导致索引失效,尽量让MySQL只做简单的增删改查。

避免类型的隐式转换

varchar等字符串类型的字段被加了索引,把这字段当做where条件,及时目标值是数字,也要加引号,否则类型的隐式转换,会引起索引失效的问题。

避免使用函数或表达式,尽量只让数据库做纯粹的增删改查。

用函数的前提是知道初始值,然后在操作数据,很多函数都是要传参的,所以mysql只能全表查,然后每次迭代将数据丢给函数处理。
表达式也是如此,例如where number + 1 = 10,都会让索引失效。

避免使用不等值做排除法

避免使用<>、!=、not in、is not null、这些都会使索引失效。

避免使用null值

避免字段使用null值,一是影响索引(mysql建索引建的是非null的值,大量的null值影响了正常的B+tree结构),而且造成聚合函数统计(如count(该字段))不准确的问题。

索引无关

减少大字段查询,避免使用*,不说磁盘io的损耗,连网络带宽都跟着损耗。
如果只select仅需字段,可能会触发覆盖索引机制,不用回表,提高性能。

select * from table;
select field1,field2 from table;

查询是否存在

select count(*) from table where...;
select field from table where ... limit 1;

冗余优化

想要查询一篇文章的浏览量,不用count(浏览记录)。
新建一个在文章表中建立一个浏览量的字段,这使得查询的时间复杂度从O(n)变成O(1);

避免join,适用于大表关联小表。文章来源地址https://www.toymoban.com/news/detail-837797.html

如果想要join的两个表,一个很大,一个很小,应尽量避免join。
可将小表数据全部取出来组装成数组,放入编程语言的内存,用编程语言的内存匹配的方式去关联。

哪些场景下索引会失效

  • 使用not in、is not null、<>、!=、这种排除法时会导致索引失效,覆盖索引除外。
  • 最左匹配原则,左边的字段缺少时会出现,覆盖索引除外。
  • 最左匹配原则,左边的字段有区间查询,导致右边的字段无法使用索引。
  • like左边或两边加百分号。
  • 类型的隐式转换,如varchar的字段,使用where varchar_field = 123,包括join表,用on连接的字段。
  • where条件有函数,或表达式。
  • where语句包含or,or中存在非索引列。
  • 大数据量对二级索引字段排序,如果select * 或者其它字段,这个过程涉及回表,可能无法使用索引,因为数据量大,走索引的每条数据都需要回表,代价会很大。
  • order by字段,如果排序与索引顺序不一致,则可能导致索引失效,如果order by的每个字段,都按照索引的顺序,或者反顺序,则仍旧会走索引。

那些查询适合创建索引?

  • 需要唯一性约束兜底的字段。
  • 经常被查询或者作为where条件的字段,=、>、<、<=、>=、in、between、like 右百分号。
  • 经常group by或者order by的字段。
  • delete或update被作为where条件的字段。
  • distinct的字段。
  • join on的连接字段需要加索引,但是需要类型一致,因为MySQL内部有用函数做隐式转换,用了函数就不适用索引。
  • 区分度(不重复度)高的字段。
  • 把搜索最频繁的列,放在联合索引的左侧,(受联合索引的最左原则影响)。

那些查询不适合创建索引?

  • 数据量小,一个表,例如配置表,总类别表,可能最多几十条记录,创建不创建区别不大。
  • 写多读少,数据的写操作对索引字段的开销比没有索引要大,而且读操作还少。
  • 区分度低的字段,例如性别状态等,这会导致线性查找,能提升搜索效率,但是不明显,可加可不加。
  • sql语句包含<>、!=、not in、is not null,无法使用索引,所以专门用作排除性查找的,不建议创建索引。

到了这里,关于MySQL查询优化方案汇总(索引相关)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

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

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

相关文章

  • MySQL查询性能优化——索引分类(二)

    目录 一、索引分类   1.按存储引擎存储形式分类     1.1聚集索引    1.1.1聚集索引结构   1.2 二级索引  1.2.1二级索引结构 2.按数据库分类  2.1 主键索引   2.2 唯一索引  2.3 常规索引  2.4 联合索引  2.5 全文索引 上一期说到索引的原理其实就是B+树,这期我们来聊一下索引的

    2024年01月22日
    浏览(45)
  • mysql高级三:sql性能优化+索引优化+慢查询日志

    内容介绍 单表索引失效案例 0 、思考题: 如果把 100 万数据插入 MYSQL ,如何提高插入效率 (1)关闭自动提交,只手动提交一次 (2)删除除主键索引外其他索引 (3)拼写mysql可以执行的长sql,批量插入数据 (4)使用java多线程 (5)使用框架,设置属性,实现批量插入 1、

    2024年02月12日
    浏览(72)
  • MySQL索引优化:提升查询速度的实战解析

    当涉及到大型数据库和复杂查询时,索引在MySQL中是一个重要的性能优化工具。通过使用索引,可以加速查询速度,减少查询的执行时间。下面是一个详细的MySQL添加索引的教程,使用Markdown格式进行说明。 步骤1:选择合适的列 首先,需要选择哪些列需要添加索引。通常情况

    2024年02月12日
    浏览(67)
  • MySQL数据库索引优化指南:提升查询效率的利器

    本文将详细探讨MySQL数据库索引的概念、作用以及不同类型的索引,包括主键索引、唯一索引和普通索引。通过实际案例分析,我们将深入理解索引的工作原理,并提供实用的优化建议,帮助读者提升数据库性能。

    2024年02月09日
    浏览(91)
  • MySQL索引以及优化解决方案

    1、 索引介绍 “索引是帮助MySQL高效获取数据的数据结构” 例如字典里的目录,索引的目的就是为了让你能够快速查找数据 2、索引优势 提高数据检索的效率,降低数据库的IO成本 通过索引对数据进行排序,降低数据排序的成本,降低了CPU的消耗 3、索引劣势 索引实际上也是

    2024年02月08日
    浏览(44)
  • 深入解析MySQL视图、索引、数据导入导出:优化查询和提高效率

    目录 1. 视图(View): 什么是视图? 为什么要使用视图? 视图的优缺点 1) 定制用户数据,聚焦特定的数据 2) 简化数据操作 3) 提高数据的安全性 4) 共享所需数据 5) 更改数据格式 6) 重用 SQL 语句 示例操作 没使用前 使用后 2. 索引(Index): 什么是索引? 为什么要使用索引?

    2024年02月13日
    浏览(64)
  • MySQL慢SQL优化方案汇总

      ⛰️个人主页:       蒾酒 🔥系列专栏 : 《mysql经验总结》 🌊山高路远,行路漫漫,终有归途 目录 写在前面 优化思路 避免查询不必要的列 分页优化 索引优化 JOIN优化 排序优化 UNION 优化 写在最后 本文介绍了MySQL常见的优化慢sql的手段,坚持看完相信对你有帮助。 同时

    2024年04月17日
    浏览(32)
  • 【性能优化】MySql数据库查询优化方案

    了解系统运行效率提升的整体解决思路和方向 学会MySQl中进行数据库查询优化的步骤 学会看慢查询、执行计划、进行性能分析、调优 ​关于这个问题,我们通常首先考虑的是硬件升级,毕竟服务器的内存、CPU、磁盘IO速度 、网络速度等都是制约我们系统快慢的首要因素。硬

    2024年02月03日
    浏览(59)
  • Mysql如何优化数据查询方案

    mysql做读写分离 读写分离是提高mysql并发的首选方案。 Mysql主从复制的原理 mysql的主从复制依赖于binlog,也就是记录mysql上的所有变化并以二进制的形式保存在磁盘上,复制的过程就是将binlog中的数据从主库传输到从库上。 主从复制过程详细分为3个阶段: 第一阶段:主库写

    2024年02月21日
    浏览(45)
  • mysql 模糊查询like优化方案(亲测)

    本文的测试是基于 740w条 测试数据进行的,只讨论like模糊查询的优化方案。其他SQL优化可参考: SQL优化的几种方式 查询开头是“今天不开心”的聊天记录,是可以走索引的。 查询包含“今天不开心”的聊天记录,是不能走索引的。 咱们主要优化的是第二种情况,我本人测

    2023年04月08日
    浏览(89)

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

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

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

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

二维码1

领取红包

二维码2

领红包