专栏首页battcnMySQL - WHERE优化篇

MySQL - WHERE优化篇

日常开发中,编写SQL语句都避免不了使用到 WHERE关键字做条件过滤,细心的朋友就会发现,WHERE的不同表现形式会对数据库性能造成一定影响,本章主要针对 WHERE优化策略进行讨论....

优化要素

  • 想要让 SELECT....WHERE...变快,第一就是检查一下是否可以增加索引。在WHERE子句中创建索引,可以加快求值、过滤、和最终检索结果的速度。为避免浪费磁盘空间,可以通过创建联合索引来加速多个相关查询。
  • 尽量减少全表扫描的查询,尤其对于大表更要杜绝全表扫描。
  • 减少函数使用(尤其是耗时的函数)。一个函数可能在结果集中每行都被调用一次或者在一个表里面每一行都被调用一次,这样做效率是非常低的。
  • 掌握不同存储引擎的优化方案,合理的运用索引技术。
  • 优化InnoDB事务。(对于统计型的数据,开启只读事务)
  • 避免将查询转换成比较难以理解的方式,以免 MySQL无法进行优化
  • 熟练掌握 EXPLAIN计划
  • 调整 MySQL用于缓存数据的内存大小
  • 减少锁表的情况

内置优化

在做JAVA开发中,通过指令重拍会对代码做一定程度的优化,在数据库中 MYSQL优化器也做了一系列相关优化工作,下面要介绍的就是数据库做的内置优化

方案一: 删除不必要的括号

((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)

方案二: 常量折叠/常量叠算

(a < b AND b = c) AND a = 5
-> b > 5 AND b = c AND a = 5

更多: 其他方案

  • 索引使用常量表达式时只计算一次,所以尽可能使用产生 const的查询方式(主键查询)
  • 对于MyISAM和MEMORY表来说,在一个单独的表上,如果使用COUNT(*)但是没有WHERE子句的话,那么就会直接从表的信息里面检索数据。当在一个表中用NOT NULL表达式的时候也是这么做的。
  • 发现无效的常量表达式。 MySQL会及时发现无效 SELECT语句,然后不返回数据。
  • WHERE查询中发现未使用 GROUP BY或者 聚合函数(比如COUNT(),MIN()等),那么 HAVING会与 WHERE合并。
  • 多表查询中, MYSQL会对表进行评估从而构造出更简单的查询
  • 优先读取常量表
  • 空表或者一个有一行的表。
  • WHERE子句在 PRIMARY KEY或者 UNIQUE INDEX上的表,其中索引和常量表达式作比较,并被定义为 NOT NULL
SELECT * FROM t WHERE primary_key = 1;
SELECT * FROM t1,t2
  WHERE t1.primary_key = 1 AND t2.primary_key = t1.id;
  • 关联查询时, MySQL会去尝试所有的可能性,从而发现最好的的组合方式。当 ORDER BYGROUP BY子句的列都位于同一个表时,该表将会第一个被链接。
  • 如果 ORDER BYGROUP BY 字段不同,或是除join queue中的第一个表之外其它含有 ORDER BYGROUP BY的表都会为其创建临时表
  • 如果使用了 SQL_SMALL_RESULT 选项,那么MySQL就会在内存中创建一个临时表。
  • MySQL每次查询时都会检查是否有可用索引,除非 MySQL优化器认为全表扫描性能更快。早期版本中认为索引扫描行占 30%的时候就会换成全表扫描,但进过改进后,现在将根据 表的大小、行的数目、I/O块大小等综合评估
  • 在某些情况下, MySQL会直接跳过数据文件直接从索引中读取内容(比如: 索引列都是数字,那么这时候会直接解析索引树
  • 跳过不匹配 HAVING条件的内容

示例

查询快慢除软硬件优化外,索引是必不可少,下面列举一些使用索引提供查询速度的示例。

高效查询

SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name
  WHERE key_part1 = constant;

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

索引树查询(索引列是数字的情况下)

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1 = val;

SELECT COUNT(*) FROM tbl_name
  WHERE key_part1 = val1 AND key_part2 = val2;

SELECT key_part2 FROM tbl_name GROUP BY key_part1;

索引排序(无需单独排序传递)

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... ;

总结

最好的优化方案,跟着新版本走 推陈出新,新版中不仅扩展更多功能,同时会加强优化力度。虽然 MySQL优化器为我们做了很多事情,但开发过程中改主意还得注意。

本文分享自微信公众号 - battcn(battcn),作者:唐亚峰

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2017-12-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL - EXPLAIN详解

    EXPLAIN: 为 SELECT语句中使用到的每个表返回一条信息。它按照MySQL在处理语句时读取它们的顺序列出这些表。MySQL使用循环嵌套算法解析所有连接...

    battcn
  • MySQL - RANGE优化篇

    对于单列索引,索引值区间可以方便地用WHERE语句中的相应范围条件表示。优化器在常量传播阶段,会将一些非常量值转换为常量。

    battcn
  • MySQL - 索引详解

    索引依托于存储引擎的实现,因此,每种存储引擎的索引都不一定完全相同,并且每种存储引擎也不一定支持所有索引类型。所有存储引擎支持每个表至少16个索引,总索引长度至...

    battcn
  • 谁在用 Python 弹奏一曲《菊花台》

    看到朋友圈有人在手机app上弹《菊花台》,简直太好听了。于是乎我也开始蠢蠢欲动,迅速下载app,对着谱子弹了起来。

    华章科技
  • AI_第一部分 数据结构与算法(15.跳表算法)

    第四阶段我们进行深度学习(AI),本部分(第一部分)主要是对底层的数据结构与算法部分进行详尽的讲解,通过本部分的学习主要达到以下两方面的效果:

    还是牛6504957
  • 忘记吃药再不愁,PillDrill智能药盒能够提醒你按时吃药

    镁客网
  • Quartz的cron表达式

    其中每个元素可以是一个值(如6),一个连续区间(9-12),一个间隔时间(8-18/4)(/表示每隔4小时),一个列表(1,3,5),通配符。

    大数据工程师-公子
  • 从拜耳620亿美元收购孟山都展望数字化农业的发展

    大数据文摘
  • 数据结构与算法系列之跳表(GO)

    前边的一篇文章中分享了二分查找算法,里边有说到二分查找算法依赖数组的随机访问特性,只能用数组来实现。如果数据存储在链表中就没法用二分查找算法了

    书旅
  • Java8内存模型—永久代(PermGen)和元空间(Metaspace)

    根据 JVM 规范,JVM 内存共分为虚拟机栈、堆、方法区、程序计数器、本地方法栈五个部分。

    纯洁的微笑

扫码关注云+社区

领取腾讯云代金券