sql优化常见策略

本贴最后更新于 3137 天前,其中的信息可能已经时移世易
常见的优化策略 从最核心的开始说起 ###1. 选择正确的存储引擎 这个是最核心的了,选错存储引擎那其他的都是白忙活了。那最常用的就是InnoDB了,那如果事务性要求不高的话,可以选择myisam了。 ###2. 不能用存在null值的列做索引 不能用null作索引,任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下,只要这些列中有一列含有null,该列就会从索引中排除。也就是说如果某列存在空值,即使对该列建索引也不会提高性能。 ###3. where后面的索引不一定100%有效 任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。 >* 尽量避免null类型的列 使得索引、索引统计、值比较都比较复杂、使用更多的存储空间、null的列为索引时,每个索引记录需要额外一个字节、 >* datetime timestamp timestamp只使用datetime一半的存储空间。并且会根据时区变化。 >* 整数类型 tinyint、smallint、mediumint、int、bigint分别使用8/16/24、32/64位存储空间 unsigned属性表示不允许负值 >* 字符串类型 varchar char的区别 varchar存储可变长的字符串、需要用额外1、2一个字节记录字符串长度信息、适合最大长度比平均长度大很多。。列的更新很少,碎片不是问题 char是定长的,适合经常变更的数据、因为不容易产生碎片。也适合非常短的列、比如char(1)存储y和n的值,char只需要一个字节,而varchar需要两个字节,额外一个字节存储长度信息 ###4. mysql手册里推荐的优化策略 ####4.1 MySQL怎样优化WHERE子句 >* 去除不必要的括号: `((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` >* 去除常量条件(由于常量重叠需要): `(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)` ·改为: -> `B=5 OR B=6` >* 尽量避免在 where 子句中使用!=或<>操作符,容易走全表扫描 >* 首先应考虑在 where order by 涉及的列上建立索引。 >* 尽量避免在 where 子句中对字段进行 null 值判断,否则会走全表扫描,例如: `select id from t where num is null`会走全表扫描。可以设成默认值0 >* 避免在 where 子句中使用 or 来连接条件.否则会走全表扫描。解决办法是多个条件用union all来连接。 >* like查询%不要前置,这样会走全表扫描。例如: `select id from t where name like ‘%cc%’`,酱紫不好。 >* in not in 也要慎用,否则会导致全表扫描.尽量用between and替换 >* 如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描: `select id from t where num=@num` 可以改为强制查询使用索引: `select id from t with(index(索引名)) where num=@num` >* 应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:`select id from t where num/2=100`应改为:`select id from t where num=100*2` >* 很多时候用 exists 代替 in 是一个好的选择:`select num from a where num in(select num from b)`用下面的语句替换:`select num from a where exists(select 1 from b where num=a.num)` >* 只有低选择性的数据行才有索引,一般根据经验来说,查出的数据量大于表数据量21%以上的话不走索引。 >* 根据经验,一个表的索引数最好不要超过6个 >* 尽量使用数字型字段.若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。 >* 避免使用`select * ` ###4.2 复合索引优化 索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引。 复合索引有个重要的原则就是`最左前缀原则`。 复合索引起作用的原则是 1. 必须包含最左边一个索引 2. 索引顺序不能改变 如果 (col1,col2,col3)有一个索引,则起作用的索引是col1、(col1, col2)、(col1, col3); 这就是最左前缀代表的意义。 ###4.3 复合索引对排序的优化 复合索引只对和索引中排序相同或相反的order by 语句优化。 在创建复合索引时,每一列都定义了升序或者是降序。如定义一个复合索引: `CREATE INDEX idx_example ON table1 (col1 ASC, col2 DESC, col3 ASC)` 其中 有三列分别是:`col1` 升序,`col2` 降序, `col3` 升序。现在如果我们执行两个查询 1:`Select col1, col2, col3 from table1 order by col1 ASC, col2 DESC, col3 ASC `和索引顺序相同 2:`Select col1, col2, col3 from table1 order by col1 DESC, col2 ASC, col3 DESC `和索引顺序相反 查询1,2 都可以别复合索引优化。 如果查询为: `Select col1, col2, col3 from table1 order by col1 ASC, col2 ASC, col3 ASC `排序结果和索引完全不同时,此时的查询不会被复合索引优化。

备注:部分来自《mysql 技术内幕》

  • 数据库

    据说 99% 的性能瓶颈都在数据库。

    345 引用 • 742 回帖
  • 索引
    24 引用 • 28 回帖
  • select
    16 引用 • 14 回帖 • 1 关注

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...
guobing
会当凌绝顶,一览众山小 北京

推荐标签 标签

  • OneNote
    1 引用 • 3 回帖
  • TensorFlow

    TensorFlow 是一个采用数据流图(data flow graphs),用于数值计算的开源软件库。节点(Nodes)在图中表示数学操作,图中的线(edges)则表示在节点间相互联系的多维数据数组,即张量(tensor)。

    20 引用 • 19 回帖
  • ngrok

    ngrok 是一个反向代理,通过在公共的端点和本地运行的 Web 服务器之间建立一个安全的通道。

    7 引用 • 63 回帖 • 644 关注
  • Firefox

    Mozilla Firefox 中文俗称“火狐”(正式缩写为 Fx 或 fx,非正式缩写为 FF),是一个开源的网页浏览器,使用 Gecko 排版引擎,支持多种操作系统,如 Windows、OSX 及 Linux 等。

    7 引用 • 30 回帖 • 391 关注
  • Gzip

    gzip (GNU zip)是 GNU 自由软件的文件压缩程序。我们在 Linux 中经常会用到后缀为 .gz 的文件,它们就是 Gzip 格式的。现今已经成为互联网上使用非常普遍的一种数据压缩格式,或者说一种文件格式。

    9 引用 • 12 回帖 • 168 关注
  • Android

    Android 是一种以 Linux 为基础的开放源码操作系统,主要使用于便携设备。2005 年由 Google 收购注资,并拉拢多家制造商组成开放手机联盟开发改良,逐渐扩展到到平板电脑及其他领域上。

    335 引用 • 324 回帖
  • 设计模式

    设计模式(Design pattern)代表了最佳的实践,通常被有经验的面向对象的软件开发人员所采用。设计模式是软件开发人员在软件开发过程中面临的一般问题的解决方案。这些解决方案是众多软件开发人员经过相当长的一段时间的试验和错误总结出来的。

    200 引用 • 120 回帖
  • Love2D

    Love2D 是一个开源的, 跨平台的 2D 游戏引擎。使用纯 Lua 脚本来进行游戏开发。目前支持的平台有 Windows, Mac OS X, Linux, Android 和 iOS。

    14 引用 • 53 回帖 • 545 关注
  • 正则表达式

    正则表达式(Regular Expression)使用单个字符串来描述、匹配一系列遵循某个句法规则的字符串。

    31 引用 • 94 回帖 • 1 关注
  • jQuery

    jQuery 是一套跨浏览器的 JavaScript 库,强化 HTML 与 JavaScript 之间的操作。由 John Resig 在 2006 年 1 月的 BarCamp NYC 上释出第一个版本。全球约有 28% 的网站使用 jQuery,是非常受欢迎的 JavaScript 库。

    63 引用 • 134 回帖 • 731 关注
  • 小说

    小说是以刻画人物形象为中心,通过完整的故事情节和环境描写来反映社会生活的文学体裁。

    31 引用 • 108 回帖 • 1 关注
  • jsoup

    jsoup 是一款 Java 的 HTML 解析器,可直接解析某个 URL 地址、HTML 文本内容。它提供了一套非常省力的 API,可通过 DOM,CSS 以及类似于 jQuery 的操作方法来取出和操作数据。

    6 引用 • 1 回帖 • 488 关注
  • Sym

    Sym 是一款用 Java 实现的现代化社区(论坛/BBS/社交网络/博客)系统平台。

    下一代的社区系统,为未来而构建

    524 引用 • 4601 回帖 • 702 关注
  • 新人

    让我们欢迎这对新人。哦,不好意思说错了,让我们欢迎这位新人!
    新手上路,请谨慎驾驶!

    52 引用 • 228 回帖
  • Windows

    Microsoft Windows 是美国微软公司研发的一套操作系统,它问世于 1985 年,起初仅仅是 Microsoft-DOS 模拟环境,后续的系统版本由于微软不断的更新升级,不但易用,也慢慢的成为家家户户人们最喜爱的操作系统。

    226 引用 • 476 回帖
  • Mac

    Mac 是苹果公司自 1984 年起以“Macintosh”开始开发的个人消费型计算机,如:iMac、Mac mini、Macbook Air、Macbook Pro、Macbook、Mac Pro 等计算机。

    167 引用 • 595 回帖 • 1 关注
  • Shell

    Shell 脚本与 Windows/Dos 下的批处理相似,也就是用各类命令预先放入到一个文件中,方便一次性执行的一个程序文件,主要是方便管理员进行设置或者管理用的。但是它比 Windows 下的批处理更强大,比用其他编程程序编辑的程序效率更高,因为它使用了 Linux/Unix 下的命令。

    124 引用 • 74 回帖 • 1 关注
  • Hprose

    Hprose 是一款先进的轻量级、跨语言、跨平台、无侵入式、高性能动态远程对象调用引擎库。它不仅简单易用,而且功能强大。你无需专门学习,只需看上几眼,就能用它轻松构建分布式应用系统。

    9 引用 • 17 回帖 • 618 关注
  • 旅游

    希望你我能在旅途中找到人生的下一站。

    93 引用 • 901 回帖
  • AngularJS

    AngularJS 诞生于 2009 年,由 Misko Hevery 等人创建,后为 Google 所收购。是一款优秀的前端 JS 框架,已经被用于 Google 的多款产品当中。AngularJS 有着诸多特性,最为核心的是:MVC、模块化、自动化双向数据绑定、语义化标签、依赖注入等。2.0 版本后已经改名为 Angular。

    12 引用 • 50 回帖 • 503 关注
  • 人工智能

    人工智能(Artificial Intelligence)是研究、开发用于模拟、延伸和扩展人的智能的理论、方法、技术及应用系统的一门技术科学。

    159 引用 • 298 回帖
  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    693 引用 • 537 回帖
  • FFmpeg

    FFmpeg 是一套可以用来记录、转换数字音频、视频,并能将其转化为流的开源计算机程序。

    23 引用 • 32 回帖 • 2 关注
  • InfluxDB

    InfluxDB 是一个开源的没有外部依赖的时间序列数据库。适用于记录度量,事件及实时分析。

    2 引用 • 87 关注
  • SendCloud

    SendCloud 由搜狐武汉研发中心孵化的项目,是致力于为开发者提供高质量的触发邮件服务的云端邮件发送平台,为开发者提供便利的 API 接口来调用服务,让邮件准确迅速到达用户收件箱并获得强大的追踪数据。

    2 引用 • 8 回帖 • 489 关注
  • webpack

    webpack 是一个用于前端开发的模块加载器和打包工具,它能把各种资源,例如 JS、CSS(less/sass)、图片等都作为模块来使用和处理。

    41 引用 • 130 回帖 • 249 关注
  • 国际化

    i18n(其来源是英文单词 internationalization 的首末字符 i 和 n,18 为中间的字符数)是“国际化”的简称。对程序来说,国际化是指在不修改代码的情况下,能根据不同语言及地区显示相应的界面。

    8 引用 • 26 回帖 • 1 关注