1. 云栖社区>
  2. 博客列表>
  3. 正文

来,我们讨论下如何怎么才能让Sql执行的快。

机械键盘 2016-09-13 18:05:36 浏览140226 评论2

云栖征文

摘要: 本文从数据库的原理出发 讨论下如何写出高性能的Sql

发现问题

  • 系统业务性能表现
  • Mysql慢日志。看这里
  • Mysql主机 CPU负载过高
  • RDS等云数据库得监控
  • 增加慢sql层,比如利用Spring AOP重写数据源,增加慢Sql告警
  • explain命令发现使用where条件而没有命中任何索引,或者是为了得到返回结果用到了太多的行.返回结果中。type对应了查询所使用的类型,比如All代表全表扫描,ref代表索引扫描,还会有范围扫描、唯一索引扫描等等。最好都能够达到ref的级别。

Mysql 整体结构

在解决慢Sql问题之前,先看一下Mysql的整体结构和一个查看Sql的执行过程。
1

  • Connection Pool 连接池, 可以通过设置max_connections=200增加连接数提高并发性能,但是会增加CPU消耗和增加锁的开销。
  • Parser 解析器。 会把Sql解析成 语法树校验语法正确, 且 缓存语法树
  • Optimizer 优化器, 重新决定表的顺序, 选择索引,缓存命中
  • Cache & Buffer 缓存。 两个部分,查询缓存和索引缓存。innodb_buffer_pool_size 设置索引缓存。基本上越大越好。

查询缓存真的会快么?

很重要的三点:

  • 可以缓存执行计划,省去解析和生成执行计划的过程
  • 可以缓存数据,只有sql完全匹配才会命中缓存
  • 只要相关的表发生了变化,缓存即刻失效
    好下面来评价一下它是否会有用。
  • 会缓存执行计划,这个是有用的,这个也是为什么视图会稍微快一点的原因,因为视图是被Parser和Optimizer构造成执行计划缓存的。
  • 完全匹配包含了空格等等都不是完全匹配,有不确定因素不算完全匹配比如含有now()
  • 也就是任何的插入更新都会导致失效
  • 缓存是排它锁的,会成为瓶颈。(缓存更新的时候,查询是阻塞的)
  • 评价缓存的唯一标准,缓存命中率,很低。
    总结:基本上不会有人依赖于这个缓存的。

那么Buffer有用么?有,大用,后面说。

一些写Sql的基本原则和误解

写好SQL的唯一原则,就是在脑子中模拟数据库是如何做过滤的,这个前提是需要了解很多的知识,先来看一下一些基本的原则和容易的误解。

用具体的字段代替*么?

测试一下好了。

SELECT * FROM fm_news_news WHERE id=95950;
SELECT id FROM fm_news_news WHERE id=95950;

除了减少了IO外, *还会对应一个字典映射。

越小的条件越在右面么?

SELECT * FROM fm_news_news WHERE create_org = 3 AND date_publish > '2016-08-30 23:59:59';
调换顺序和EXPLAIN之后发现:是一样一样一样的啊。 为什么呢?
因为是全表扫的,每扫一行都是匹配这两项,不管什么位置都是要全表扫。

然后我们换一个带索引的SQL:

EXPLAIN SELECT * FROM fm_news_news WHERE id>95950 AND date_update > '2016-08-30 23:59:59';

调换位置会发现还是一样的,这个也是因为Optimizer发挥了作用,会初步计算会过滤掉的行,然后决定选取的索引,
唯一索引>普通索引, 其他的索引是先定义的索引,而不是SQL的位置。

OR 替代 IN么?

实际上Optimizer会把IN自动改成OR的格式,没有必要显示的修改。

hibernate 的 n+1问题。

hibernate设置延迟加载的时候会有N+1查询数据库的问题,
可以强制走左连接。

EXIST代替IN么?

首先应用场景不一样,EXIST仅仅适用于子查询。

SELECT * FROM fm_news_news n WHERE EXISTS (SELECT 1 FROM fm_news_channelnews cn WHERE n.id = cn.news_id AND cn.channel_id=120 AND cn.channel_type = 5)
SELECT * FROM fm_news_news WHERE id IN (SELECT news_id FROM fm_news_channelnews WHERE channel_id=120 AND channel_type = 5);
SELECT * FROM fm_news_news n LEFT JOIN fm_news_channelnews cn ON n.id=cn.news_id AND cn.channel_id=120 AND cn.channel_type = 5;
// 上面这个会先查出一个channel_news的临时表,然后用news_news跟这个临时表LEFT JOIN, 所以很低是正常的
SELECT * FROM fm_news_news n LEFT JOIN fm_news_channelnews cn ON n.id=cn.news_id WHERE  cn.channel_id=120 AND cn.channel_type = 5;

2, 4的性能最好。
都会命中索引,网上有的说IN语句的子查询不会命中索引是错误的。

2比1块为什么?
首先看IN是怎么查的,IN中()内的子查询只执行一次,返回结果集int[] 然后是便利for(int i : int[]) 在便利中找到主表中满足要求的。
而EXIST呢,先执行A的全表扫描得到结果集News[] ns 然后便利这个ns, 判断每一条在EXIST条件中是否能返回true,所以本质是一个EXIST的全表扫。
选对驱动表, IN是子查询是驱动表,EXIST是外面是驱动表

IN不处理null

SELECT * FROM fm_news_news WHERE brief IN (NULL) LIMIT 10这样查询的结果为空,只能使用IS NULL;
但是有null的列都不入索引,所以is not null或者是is null 语句都不走索引,因此我们插入数据的时候索引字段不能为null

实际上决定快慢主要有如下的因素:

  1. 两个表的数据量,以及数据分布;
  2. 表有没有经过分析;
  3. 子查询中是否包含NULL值 (很重要);
  4. 是否存在索引;
  5. 数据库版本:不同版本的数据库,优化器的工作方式会有差异. 最新版本IN子查询已经可以走索引了

用NOT EXIST代替NOT IN么?

NOT EXIST 仍然是以左表作为驱动表,做全查询,然后判断条件是否成立的。

SELECT * FROM fm_user_devicechannel dc WHERE dc.channel_id NOT IN (NULL);
 SELECT * FROM fm_news_channelnews cn WHERE NOT EXISTS (SELECT 1 FROM fm_user_defaultchannel df WHERE cn.channel_id = df.channel_id );
SELECT * FROM fm_user_devicechannel dc WHERE dc.channel_id NOT IN (SELECT channel_id FROM fm_user_defaultchannel df);

NOT IN null的这个会返回空。 所以只有需要判断子查询的结果中不为null的时候才有意义。

其他的情况下因为NOT EXIST会使用索引, NOT IN 是完全的双表全表扫所以最慢,理论上讲永远不用。

先过滤后GROUP BY, 而不是先GROUP BY 再HAVING

尽量使用一个复杂语句而不是多次链接数据库么?

这个越来越不是这样了,特别是随着SOA,微服务的盛行。实际上拆分多个语句会有如下的好处:

  • 让缓存效率更高。 如果第一个查询已经执行过,那么就可以跳过。
  • 减少锁竞争
  • 在应用层做关联,可以对数据库进行拆分,获得更好的扩展性
  • 使用IN()代替关联查询,本身会比关联查询更高效
  • 在应用层可以重用第一次的查询结果,比如做缓存。

下面是索引,单独拿出来说

索引

为什么索引会变快?

是由索引的数据结构决定的。索引是一个二叉树,二叉树的便利过程就是一个二分查找算法。查找的复杂度为logn,n是树的高度。
因为效率跟树的高度有关,所以一般索引都是平衡二叉树,平衡二叉树就是两边的高度尽量一样高。这种再插入的时候会增加左旋和右旋,但是查询效率很高。
再进化就是B+ tree了,如下图的结构:
2
可见每个节点是多个值,这个主要是因为磁盘IO的读取性质, 连续读远远>随即读, B+Tree的一个节点会放在连续的空间,然后通过系统的页加载机制,一次读出多个数据,大大的减少磁盘I/O次数,增加性能。

通过上面的方法保证了有索引的查询会比较快。

索引一定快么?

  • 不一定,首先如果不是很唯一的值就不一定快。
  • 另外因为需要先查找索引页在查找数据页,一般当使用索引仍然需要查询70%以上数据的时候索引就比不用索引还慢了
  • 如果数据特别少也是不用索引比较快。比如在数据少的时候冒泡比二分查找还快。

聚簇索引、非聚簇索引、覆盖索引

聚簇索引:

 类似于没有目录的字典,表的数据就是按照索引的顺序来存储的,既叶子节点就是表数据。 所以一张表上最多只有一个。更新的性能差。
 但是因为物理上的位置就是相邻的,所以呢范围查找会比较快。
 还有一个问题是容易出现尾端热点,可能有性能问题。

非聚簇索引:

 叶子节点是到真实物理数据的指针和索引字段的内容,类似于目录。
 插入的性能会好
 范围查询就没有这么大的优势了。

覆盖索引: 所有的数据都是索引字段,直接在内存中便利索引就拿到了,不需要。

索引命中的问题

  • 连接列不会用索引 a||""||b
  • like 右通配符能命中索引 'a%' 左通配符不行'%b'
  • 组合索引,(a, b, c) 查询 a AND b, 或者是a 但是如果是 b AND c的这种就不行
  • != 不会使用索引,改为范围
  • 更新少,唯一性高,短列比较适合做索引
  • 对列的函数计算不会用到索引, 比如md5(colume1)
  • 函数计算针对常量则没有问题,比如 date_publish > DATE_FORMAT(now(), '')
  • NULL 字段不会命中索引 IS NULL 和 IS NOT NULL都要少用
  • 索引字段都命中索引,则会变成覆盖索引,效率更高
  • 很重要的一点, LEFT JOIN 的时候ON的语句一定是要有索引的,否则会变成全表查。

承接前面的Buffer,这个就是用来保存索引的, 可以用innodb_buffer_pool_size来设定,一般这台机器的所有内存的70-80%就是给他了。

分库分表下的查询优化

  • 全局表。
    插入更新的时候会同步到所有的分片。

查询的时候使用一个分片。

  • ER Join
    表分组的思路, 子表的存储位置依赖于主表,解决Join的问题。
  • Share Join
    支持两张表, 解析SQL语句,分别查询,然后再聚合
版权声明:本文内容由互联网用户自发贡献,版权归作者所有,本社区不拥有所有权,也不承担相关法律责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:yqgroup@service.aliyun.com 进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。

用云栖社区APP,舒服~

【云栖快讯】中办国办印发《推进互联网协议第六版(IPv6)规模部署行动计划》加快推进基于 IPv6 的下一代互联网规模部署,计划指出2025年末中国 IPv6 规模要达到世界第一,阿里云也第一时间宣布了将全面提供IPv6服务,那么在全面部署 IPV6 前,你需要了解都在这儿  详情请点击

网友评论

1F
周梦康

这么好的文章怎么没人评论,这不科学!

2F
火山山

收藏

关注
机械键盘
人在成都 互联网相关技术爱好者
136篇文章|38关注

云数据库SQL Server,具有企业许可授权,权限更为开放,引擎功能更为强大。能完美支持Windows平台的.... 更多>

为您提供简单高效、处理能力可弹性伸缩的计算服务,帮助您快速构建更稳定、安全的应用,提升运维效率,降低 IT 成本... 更多>
订阅广场全新上线

订阅广场全新上线