sql优化常见策略

本贴最后更新于 3240 天前,其中的信息可能已经时移世易
常见的优化策略 从最核心的开始说起 ###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% 的性能瓶颈都在数据库。

    346 引用 • 757 回帖 • 1 关注
  • 索引
    24 引用 • 28 回帖
  • select
    16 引用 • 14 回帖 • 1 关注

相关帖子

欢迎来到这里!

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

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

推荐标签 标签

  • SSL

    SSL(Secure Sockets Layer 安全套接层),及其继任者传输层安全(Transport Layer Security,TLS)是为网络通信提供安全及数据完整性的一种安全协议。TLS 与 SSL 在传输层对网络连接进行加密。

    70 引用 • 193 回帖 • 408 关注
  • Typecho

    Typecho 是一款博客程序,它在 GPLv2 许可证下发行,基于 PHP 构建,可以运行在各种平台上,支持多种数据库(MySQL、PostgreSQL、SQLite)。

    12 引用 • 67 回帖 • 445 关注
  • 书籍

    宋真宗赵恒曾经说过:“书中自有黄金屋,书中自有颜如玉。”

    83 引用 • 412 回帖
  • MongoDB

    MongoDB(来自于英文单词“Humongous”,中文含义为“庞大”)是一个基于分布式文件存储的数据库,由 C++ 语言编写。旨在为应用提供可扩展的高性能数据存储解决方案。MongoDB 是一个介于关系数据库和非关系数据库之间的产品,是非关系数据库当中功能最丰富,最像关系数据库的。它支持的数据结构非常松散,是类似 JSON 的 BSON 格式,因此可以存储比较复杂的数据类型。

    91 引用 • 59 回帖 • 1 关注
  • 30Seconds

    📙 前端知识精选集,包含 HTML、CSS、JavaScript、React、Node、安全等方面,每天仅需 30 秒。

    • 精选常见面试题,帮助您准备下一次面试
    • 精选常见交互,帮助您拥有简洁酷炫的站点
    • 精选有用的 React 片段,帮助你获取最佳实践
    • 精选常见代码集,帮助您提高打码效率
    • 整理前端界的最新资讯,邀您一同探索新世界
    488 引用 • 384 回帖 • 3 关注
  • FreeMarker

    FreeMarker 是一款好用且功能强大的 Java 模版引擎。

    23 引用 • 20 回帖 • 469 关注
  • OneNote
    1 引用 • 3 回帖
  • Python

    Python 是一种面向对象、直译式电脑编程语言,具有近二十年的发展历史,成熟且稳定。它包含了一组完善而且容易理解的标准库,能够轻松完成很多常见的任务。它的语法简捷和清晰,尽量使用无异义的英语单词,与其它大多数程序设计语言使用大括号不一样,它使用缩进来定义语句块。

    554 引用 • 675 回帖
  • Kafka

    Kafka 是一种高吞吐量的分布式发布订阅消息系统,它可以处理消费者规模的网站中的所有动作流数据。 这种动作(网页浏览,搜索和其他用户的行动)是现代系统中许多功能的基础。 这些数据通常是由于吞吐量的要求而通过处理日志和日志聚合来解决。

    36 引用 • 35 回帖 • 3 关注
  • 京东

    京东是中国最大的自营式电商企业,2015 年第一季度在中国自营式 B2C 电商市场的占有率为 56.3%。2014 年 5 月,京东在美国纳斯达克证券交易所正式挂牌上市(股票代码:JD),是中国第一个成功赴美上市的大型综合型电商平台,与腾讯、百度等中国互联网巨头共同跻身全球前十大互联网公司排行榜。

    14 引用 • 102 回帖 • 314 关注
  • Spring

    Spring 是一个开源框架,是于 2003 年兴起的一个轻量级的 Java 开发框架,由 Rod Johnson 在其著作《Expert One-On-One J2EE Development and Design》中阐述的部分理念和原型衍生而来。它是为了解决企业应用开发的复杂性而创建的。框架的主要优势之一就是其分层架构,分层架构允许使用者选择使用哪一个组件,同时为 JavaEE 应用程序开发提供集成的框架。

    947 引用 • 1460 回帖 • 2 关注
  • uTools

    uTools 是一个极简、插件化、跨平台的现代桌面软件。通过自由选配丰富的插件,打造你得心应手的工具集合。

    7 引用 • 28 回帖
  • abitmean

    有点意思就行了

    35 关注
  • Webswing

    Webswing 是一个能将任何 Swing 应用通过纯 HTML5 运行在浏览器中的 Web 服务器,详细介绍请看 将 Java Swing 应用变成 Web 应用

    1 引用 • 15 回帖 • 643 关注
  • MySQL

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

    694 引用 • 537 回帖
  • AWS
    11 引用 • 28 回帖 • 9 关注
  • 工具

    子曰:“工欲善其事,必先利其器。”

    300 引用 • 768 回帖
  • OnlyOffice
    4 引用 • 18 关注
  • 单点登录

    单点登录(Single Sign On)是目前比较流行的企业业务整合的解决方案之一。SSO 的定义是在多个应用系统中,用户只需要登录一次就可以访问所有相互信任的应用系统。

    9 引用 • 25 回帖 • 1 关注
  • 七牛云

    七牛云是国内领先的企业级公有云服务商,致力于打造以数据为核心的场景化 PaaS 服务。围绕富媒体场景,七牛先后推出了对象存储,融合 CDN 加速,数据通用处理,内容反垃圾服务,以及直播云服务等。

    29 引用 • 230 回帖 • 122 关注
  • Hibernate

    Hibernate 是一个开放源代码的对象关系映射框架,它对 JDBC 进行了非常轻量级的对象封装,使得 Java 程序员可以随心所欲的使用对象编程思维来操纵数据库。

    39 引用 • 103 回帖 • 724 关注
  • Q&A

    提问之前请先看《提问的智慧》,好的问题比好的答案更有价值。

    10114 引用 • 45941 回帖 • 64 关注
  • TextBundle

    TextBundle 文件格式旨在应用程序之间交换 Markdown 或 Fountain 之类的纯文本文件时,提供更无缝的用户体验。

    1 引用 • 2 回帖 • 82 关注
  • Flutter

    Flutter 是谷歌的移动 UI 框架,可以快速在 iOS 和 Android 上构建高质量的原生用户界面。 Flutter 可以与现有的代码一起工作,它正在被越来越多的开发者和组织使用,并且 Flutter 是完全免费、开源的。

    39 引用 • 92 回帖 • 9 关注
  • SVN

    SVN 是 Subversion 的简称,是一个开放源代码的版本控制系统,相较于 RCS、CVS,它采用了分支管理系统,它的设计目标就是取代 CVS。

    29 引用 • 98 回帖 • 699 关注
  • Chrome

    Chrome 又称 Google 浏览器,是一个由谷歌公司开发的网页浏览器。该浏览器是基于其他开源软件所编写,包括 WebKit,目标是提升稳定性、速度和安全性,并创造出简单且有效率的使用者界面。

    63 引用 • 289 回帖
  • RESTful

    一种软件架构设计风格而不是标准,提供了一组设计原则和约束条件,主要用于客户端和服务器交互类的软件。基于这个风格设计的软件可以更简洁,更有层次,更易于实现缓存等机制。

    30 引用 • 114 回帖 • 6 关注