sql优化常见策略

本贴最后更新于 3267 天前,其中的信息可能已经时移世易
常见的优化策略 从最核心的开始说起 ###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 引用 • 760 回帖
  • 索引
    24 引用 • 28 回帖
  • select
    16 引用 • 14 回帖 • 1 关注

相关帖子

欢迎来到这里!

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

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

推荐标签 标签

  • QQ

    1999 年 2 月腾讯正式推出“腾讯 QQ”,在线用户由 1999 年的 2 人(马化腾和张志东)到现在已经发展到上亿用户了,在线人数超过一亿,是目前使用最广泛的聊天软件之一。

    45 引用 • 557 回帖
  • SQLServer

    SQL Server 是由 [微软] 开发和推广的关系数据库管理系统(DBMS),它最初是由 微软、Sybase 和 Ashton-Tate 三家公司共同开发的,并于 1988 年推出了第一个 OS/2 版本。

    21 引用 • 31 回帖 • 1 关注
  • JVM

    JVM(Java Virtual Machine)Java 虚拟机是一个微型操作系统,有自己的硬件构架体系,还有相应的指令系统。能够识别 Java 独特的 .class 文件(字节码),能够将这些文件中的信息读取出来,使得 Java 程序只需要生成 Java 虚拟机上的字节码后就能在不同操作系统平台上进行运行。

    180 引用 • 120 回帖
  • 爬虫

    网络爬虫(Spider、Crawler),是一种按照一定的规则,自动地抓取万维网信息的程序。

    106 引用 • 275 回帖
  • H2

    H2 是一个开源的嵌入式数据库引擎,采用 Java 语言编写,不受平台的限制,同时 H2 提供了一个十分方便的 web 控制台用于操作和管理数据库内容。H2 还提供兼容模式,可以兼容一些主流的数据库,因此采用 H2 作为开发期的数据库非常方便。

    11 引用 • 54 回帖 • 675 关注
  • 996
    13 引用 • 200 回帖 • 2 关注
  • 开源

    Open Source, Open Mind, Open Sight, Open Future!

    415 引用 • 3601 回帖
  • 大数据

    大数据(big data)是指无法在一定时间范围内用常规软件工具进行捕捉、管理和处理的数据集合,是需要新处理模式才能具有更强的决策力、洞察发现力和流程优化能力的海量、高增长率和多样化的信息资产。

    89 引用 • 113 回帖
  • JRebel

    JRebel 是一款 Java 虚拟机插件,它使得 Java 程序员能在不进行重部署的情况下,即时看到代码的改变对一个应用程序带来的影响。

    26 引用 • 78 回帖 • 687 关注
  • 新人

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

    52 引用 • 228 回帖
  • Rust

    Rust 是一门赋予每个人构建可靠且高效软件能力的语言。Rust 由 Mozilla 开发,最早发布于 2014 年 9 月。

    59 引用 • 22 回帖 • 2 关注
  • Android

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

    337 引用 • 324 回帖
  • GitLab

    GitLab 是利用 Ruby 一个开源的版本管理系统,实现一个自托管的 Git 项目仓库,可通过 Web 界面操作公开或私有项目。

    46 引用 • 72 回帖
  • HHKB

    HHKB 是富士通的 Happy Hacking 系列电容键盘。电容键盘即无接点静电电容式键盘(Capacitive Keyboard)。

    5 引用 • 74 回帖 • 523 关注
  • Visio
    1 引用 • 2 回帖
  • Ubuntu

    Ubuntu(友帮拓、优般图、乌班图)是一个以桌面应用为主的 Linux 操作系统,其名称来自非洲南部祖鲁语或豪萨语的“ubuntu”一词,意思是“人性”、“我的存在是因为大家的存在”,是非洲传统的一种价值观,类似华人社会的“仁爱”思想。Ubuntu 的目标在于为一般用户提供一个最新的、同时又相当稳定的主要由自由软件构建而成的操作系统。

    127 引用 • 169 回帖 • 1 关注
  • TGIF

    Thank God It's Friday! 感谢老天,总算到星期五啦!

    292 引用 • 4495 回帖 • 665 关注
  • SVN

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

    29 引用 • 98 回帖 • 698 关注
  • Spring

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

    948 引用 • 1460 回帖 • 2 关注
  • Chrome

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

    63 引用 • 289 回帖
  • 链滴

    链滴是一个记录生活的地方。

    记录生活,连接点滴

    186 引用 • 3909 回帖
  • 国际化

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

    8 引用 • 26 回帖
  • CAP

    CAP 指的是在一个分布式系统中, Consistency(一致性)、 Availability(可用性)、Partition tolerance(分区容错性),三者不可兼得。

    12 引用 • 5 回帖 • 635 关注
  • sts
    2 引用 • 2 回帖 • 247 关注
  • FlowUs

    FlowUs.息流 个人及团队的新一代生产力工具。

    让复杂的信息管理更轻松、自由、充满创意。

    1 引用 • 3 关注
  • Ngui

    Ngui 是一个 GUI 的排版显示引擎和跨平台的 GUI 应用程序开发框架,基于
    Node.js / OpenGL。目标是在此基础上开发 GUI 应用程序可拥有开发 WEB 应用般简单与速度同时兼顾 Native 应用程序的性能与体验。

    7 引用 • 9 回帖 • 413 关注
  • WebComponents

    Web Components 是 W3C 定义的标准,它给了前端开发者扩展浏览器标签的能力,可以方便地定制可复用组件,更好的进行模块化开发,解放了前端开发者的生产力。

    1 引用 • 12 关注