文档章节

SQL优化-跨裤查询wherein优化

沉淀岁月
 沉淀岁月
发布于 2017/09/04 20:48
字数 741
阅读 7
收藏 0
点赞 0
评论 0

由于分库分表的原因,和开发规定了不能使用 表表JOIN 语句。因此,我们要将 JOIN 语句的转化成使用 IN 来做。如现在有 表 A(a_id, c_a)c_a有普通索引,表 B(b_id, c_a) 这两个表要关联, 应该转化为以下步骤处理:

  • 先查询B中的 a_id

 

 

1

SELECT c_a FROM B WHERE xxx;

 

  • 使用 IN 查询 A 表

 

 

1

SELECT a_id, ... FROM A WHERE c_a IN(在 1 中查出来的 c_a)

 

场景

现在表的数据量有 800万。

一般的使用语句是:

 

1

SELECT * FROM A WHERE c_a IN(955555, 955556, 955557, 955558, 955559);

上面语句会执行的很快,知道使用 explain 的都明白这样一般都是会使用索引的,并且是所有范围扫描。

MySQL不会从 1 开始 扫描 800万,而是从555555 扫描到 555559(只要扫描5行数据)。

在一般情况下是没有什么问题的。但是如果 IN 里面的数据是不连续的就有很大问题了。

创建表结构语句

 

 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

CREATE TABLE t(

    id INT unsigned NOT NULL AUTO_INCREMENT,

    cid INT unsigned NOT NULL DEFAULT 0,

    c1 VARCHAR(50) NOT NULL DEFAULT '',

    c2 VARCHAR(50) NOT NULL DEFAULT '',

    c3 VARCHAR(50) NOT NULL DEFAULT '',

    c4 VARCHAR(50) NOT NULL DEFAULT '',

    c5 VARCHAR(50) NOT NULL DEFAULT '',

    c6 VARCHAR(50) NOT NULL DEFAULT '',

    PRIMARY KEY(id),

    INDEX idx$cid(cid)

);

INSERT INTO t VALUES(

    NULL,

    FLOOR(RAND() * 1000000),

    REPEAT('a', 50),

    REPEAT('a', 50),

    REPEAT('a', 50),

    REPEAT('a', 50),

    REPEAT('a', 50),

    REPEAT('a', 50)

);

-- 重复执行

INSERT INTO t

SELECT NULL,

    FLOOR(RAND() * 1000000),

    c1,

    c2,

    c3,

    c4,

    c5,

    c6

FROM t;

 

下面是具体的实验过程

  • 使用IN查询连续的数

 

 

1

2

3

4

5

6

7

8

9

10

11

12

13

SELECT *

FROM t

WHERE cid IN(955555, 955556, 955557, 955558, 955559);

+---------+--------+-----------------------------------

| id      | cid    | c1                              

+---------+--------+-----------------------------------

|  319330 | 955555 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

| 1885293 | 955555 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

| ......

| 8733757 | 955559 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

| 8796305 | 955559 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

+---------+--------+-----------------------------------

41 rows in set (0.15 sec)

 

  • 使用IN查询不连续的数

 

 

1

2

3

4

5

6

7

8

9

10

11

12

13

SELECT *

FROM t

WHERE cid IN(1, 5000, 50000, 500000, 955559);

+---------+--------+-----------------------------------

| id      | cid    | c1                              

+---------+--------+-----------------------------------

|      1 |  341702 |      1 | aaaaaaaaaaaaaaaaaaaaaaaaa

|      1 | 1045176 |      1 | aaaaaaaaaaaaaaaaaaaaaaaaa

......

| 955559 | 8733757 | 955559 | aaaaaaaaaaaaaaaaaaaaaaaaa

| 955559 | 8796305 | 955559 | aaaaaaaaaaaaaaaaaaaaaaaaa

+--------+---------+--------+--------------------------

41 rows in set (4.34 sec)

 

  • 使用UNION优化

 

 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

SELECT *

FROM (

    SELECT 1 AS cid UNION ALL

    SELECT 5000 UNION ALL

    SELECT 50000 UNION ALL

    SELECT 500000 UNION ALL

    SELECT 955559

) AS tmp, t

WHERE tmp.cid = t.cid;

+---------+--------+-----------------------------------

| id      | cid    | c1                              

+---------+--------+-----------------------------------

|      1 |  341702 |      1 | aaaaaaaaaaaaaaaaaaaaaaaaa

|      1 | 1045176 |      1 | aaaaaaaaaaaaaaaaaaaaaaaaa

......

| 955559 | 8733757 | 955559 | aaaaaaaaaaaaaaaaaaaaaaaaa

| 955559 | 8796305 | 955559 | aaaaaaaaaaaaaaaaaaaaaaaaa

+--------+---------+--------+--------------------------

41 rows in set (0.01 sec)

从上面可以看出上面使用UNION的方法生成一个临时表作为关联的主表。

拓展

要是MySQL有只带的一个行转列的函数那就完美了。这样我们就可以不用使用UNION了。

 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

SELECT 1, 5000, 50000, 500000, 955559;

+---+------+-------+--------+--------+

| 1 | 5000 | 50000 | 500000 | 955559 |

+---+------+-------+--------+--------+

| 1 | 5000 | 50000 | 500000 | 955559 |

+---+------+-------+--------+--------+

1 row in set (0.00 sec)

    

变成以下

SELECT row_to_col(1, 5000, 50000, 500000, 955559);

+--------+

|     id |

+--------+

|      1 |

|   5000 |

|  50000 |

| 500000 |

| 955559 |

+--------+

要是能像上面就太棒了简直。

本文转载自:http://www.ttlsa.com/mysql/mysql-sql-performance-let-you-head-big-open/

共有 人打赏支持
沉淀岁月
粉丝 25
博文 252
码字总数 91615
作品 0
朝阳
高级程序员
MySQL性能管理及架构设计:SQL查询优化、分库分表

一、SQL查询优化(重要) 1.1 获取有性能问题SQL的三种方式 通过用户反馈获取存在性能问题的SQL; 通过慢查日志获取存在性能问题的SQL; 实时获取存在性能问题的SQL; 1.1.2 慢查日志分析工具...

whs0668 ⋅ 03/27 ⋅ 0

Apache Trafodion——分布式 SQL 引擎

Trafodion提供了一个基于Hadoop平台的交易型分布式SQL引擎。它是一个擅长处理交易型负载的Hadoop大数据解决方案。其主要特性包括: 完整的ANSI SQL 92/99语言支持 完整的ACID事务支持。对于读...

oschina ⋅ 2016/07/18 ⋅ 0

分布式SQL引擎--Apache Trafodion

概览 Trafodion提供了一个基于Hadoop平台的交易型分布式SQL引擎。它是一个擅长处理交易型负载的Hadoop大数据解决方案。其主要特性包括: 完整的ANSI SQL 92/99语言支持 完整的ACID事务支持。...

yangxujun ⋅ 2016/07/17 ⋅ 1

TiDB RC4 发布,新型分布式 NewSQL 数据库

8 月 4 日,TiDB 正式发布 RC4 版。该版本对 MySQL 兼容性、SQL 优化器、系统稳定性、性能做了大量的工作。性能方面重点优化了写入速度,计算任务调度支持优先级,避免分析型大事务影响在线事...

局长 ⋅ 2017/08/05 ⋅ 7

分布式关系型数据库 TiDB 正式发布 RC1 版本

12 月 23 日,分布式关系型数据库 TiDB 正式发布 RC1。更新如下: TiKV: 提升写入速度 降低磁盘空间占用 支持百 TB 级别数据 提升稳定性,集群规模支持 200 个节点 提供 Raw KV API,以及 ...

TiDB ⋅ 2016/12/23 ⋅ 18

sql SELECT时的with(nolock)选项说明

sql SELECT时的with(nolock)选项说明 要提升SQL的查询效能,一般来说大家会以建立索引(index)为第一考虑。其实除了index的建立之外,当我们在下SQL Command时,在语法中加一段WITH (NOLOCK)...

writeademo ⋅ 2016/10/21 ⋅ 0

课程「性能优化之MySQL优化」的复习笔记

可以进行优化的层面 硬件 系统配置 数据库表结构 SQL 语句和索引 进行优化前的数据准备 打开以下链接下载数据 打开终端,执行以下命令 SQL 语句和索引 MySQL 慢查询日志 如何发现有问题的 SQ...

javaer ⋅ 2016/11/04 ⋅ 0

MetaModel 3.3 发布,数据库元模型

MetaModel 3.3 增加了用于查询和更新 Salesforce.com 安装的模块,通过 Salesforce.com 的 Web 服务接口实现;增加了用于查询 SugarCRM 安装的模块;优化对 DB2 查询的支持(firstRow和maxRo...

oschina ⋅ 2013/02/28 ⋅ 0

统一数据分析接口--Apache Lens

Lens 提供了一个统一数据分析接口。通过提供一个跨多个数据存储的单一视图来实现数据分析任务切分,同时优化了执行的环境。无缝的集成 Hadoop 实现类似传统数据仓库的功能。 该项目主要特性:...

红薯 ⋅ 2015/03/06 ⋅ 3

分布式关系型数据库 TiDB 正式发布 RC2 版本

3 月 1 日,TiDB 正式发布 RC2 版。该版本对 MySQL 兼容性、SQL 优化器、系统稳定性、性能做了大量的工作。对于 OLTP 场景,读取性能提升 60%,写入性能提升 30%。另外提供了权限管理功能,用...

TiDB ⋅ 2017/03/01 ⋅ 11

没有更多内容

加载失败,请刷新页面

加载更多

下一页

【elasticsearch】 随笔 Date datatype

一。时间类型的本质 首先json是没有时间类型的,对于es来说,时间类型的标示可以是下面三种情况 1.一个时间格式的字符串,如:"2014-11-27T08:05:32Z","2015-01-01" or "2015/01/01 12:10:3...

xiaomin0322 ⋅ 4分钟前 ⋅ 0

阿里云资源编排ROS使用教程

阿里云资源编排ROS详细内容: 阿里云资源编排ROS使用教程 资源编排(Resource Orchestration)是一种简单易用的云计算资源管理和自动化运维服务。用户通过模板描述多个云计算资源的依赖关系、...

mcy0425 ⋅ 6分钟前 ⋅ 0

适配器设计模式

1、适配器模式 把一个类的接口变换成客户端所期待的另一种接口 使原本因接口不匹配而无法在一起工作的两个类能够在一起工作 分为类的适配器模式和对象的适配器模式 2、类适配器模式 类的适配...

职业搬砖20年 ⋅ 11分钟前 ⋅ 0

npm操作报错 _stream_writable.js:61

有一天 不知道什么原因(估计和node的版本有关),无论你做什么npm的操作 都会报错/usr/local/lib/node_modules/npm/node_modules/readable-stream/lib/_stream_writable.js:61 这时候只要执...

lilugirl ⋅ 14分钟前 ⋅ 0

Eclipse安装插件的几种方式

Eclipse魅力之一就是支持可扩展的插件,来丰富自身的功能,这种方式也是建立在开源思想之上的。具体使用什么方式去安装插件,要看我们拿到的是什么。 1. 拿到的是一串URL,如http://subclips...

GordonNemo ⋅ 17分钟前 ⋅ 0

div图片叠加

css实现代码如下: <div style="position: relative;"><!--这个层为外面的父层,需设置相对位置样式--> <div style="position: absolute;"><!--子层,需设置绝对位置样式--> <i......

niithub ⋅ 18分钟前 ⋅ 0

作用域slot

如果父组件需要使用子组件中的内容怎么办,比如父组件需要控制子组件的显示 <div id="root"><child><template slot-scope="props"><h1>{{props.item}} <div>编辑</div></h1><......

金于虎 ⋅ 21分钟前 ⋅ 1

HongHu commonservice-eureka 项目构建过程

上一篇我们回顾了关于 spring cloud eureka的相关基础知识,现在我们针对于HongHu cloud的eureka项目做以下构建,整个构建的过程很简单,我会将每一步都构建过程记录下来,希望可以帮助到大家...

明理萝 ⋅ 24分钟前 ⋅ 1

xml和对象的相互转化

@Data//setter和getter方法,toString和equals,hashcode方法@EqualsAndHashCode//代表重写equals和hashcode方法@XmlAccessorType(XmlAccessType.FIELD)public class Classroom {@X......

拐美人 ⋅ 24分钟前 ⋅ 0

tableView cell的高度 分组头部尾部的高度 自适应

@property (nonatomic) CGFloat rowHeight; // default is UITableViewAutomaticDimension@property (nonatomic) CGFloat sectionHeaderHeight; // default is UITableViewA......

娜一片蓝色星海 ⋅ 25分钟前 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部