文档章节

SQL优化-跨裤查询wherein优化

沉淀岁月
 沉淀岁月
发布于 2017/09/04 20:48
字数 741
阅读 17
收藏 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/

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

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

whs0668
03/27
0
0
MySQL性能管理及架构设计(三):SQL查询优化、分库分表 - 完结篇

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

hansonwong
11/23
0
0
Apache Trafodion——分布式 SQL 引擎

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

oschina
2016/07/18
17
0
分布式SQL引擎--Apache Trafodion

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

yangxujun
2016/07/17
4.5K
1
Yearning v1.2 正式发布,Web 端 SQL 审核平台

Yearning 1.2正式发布了。 Yearning SQL 审核平台,基于 Vue.js 与 Django 的整套 sql 审核平台解决方案。 提供基于 Inception 的 SQL 检测及执行。 更新内容: 查询支持字段脱敏 支持多级审...

cookieY
07/10
1K
1

没有更多内容

加载失败,请刷新页面

加载更多

MyBaties分页插件PageHelper的简单使用

抛出问题: 如果想要将现有的select语句改为支持分页功能的查询语句该怎么做呢? 最简单的一种做法就是将所有的select语句都加上limit来实现分页,这种做法有什么问题呢? 有没有一种简便方法...

嘴角轻扬30
22分钟前
1
0
创业公司如何快速构建高效的监控系统?

12 月 7 日,在 2018 ArchSummit 全球架构师峰会·运维与监控专场,七牛云资深运维开发工程师贺强带来了主题为《如何快速构建高效的监控系统》的内容分享。 本文是对演讲内容的实录整理。 大...

七牛云
22分钟前
0
0
Docker与自动化测试及其测试实践

Docker 与自动化测试 对于重复枯燥的手动测试任务,可以考虑将其进行自动化改造。自动化的成本在于自动化程序的编写和维护,而收益在于节省了手动执行用例的时间。简而言之,如果收益大于成本...

微笑向暖wx
23分钟前
1
0
Spring Cloud 分布式链路跟踪 Sleuth + Zipkin + Elasticsearch

随着业务越来越复杂,系统也随之进行各种拆分,特别是随着微服务架构的兴起,看似一个简单的应用,后台可能很多服务在支撑;一个请求可能需要多个服务的调用;当请求迟缓或不可用时,无法得知...

编程SHA
26分钟前
1
0
Swift-清除缓存

func removeCache (){ // 取出cache文件夹路径.如果清除其他位子的可以将cachesDirectory换成对应的文件夹 let cachePath = NSSearchPathForDirectoriesInDomains(FileMan...

west_zll
26分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部