文档章节

SQL优化-跨裤查询wherein优化

沉淀岁月
 沉淀岁月
发布于 2017/09/04 20:48
字数 741
阅读 10
收藏 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
博文 257
码字总数 91615
作品 0
朝阳
高级程序员
MySQL性能管理及架构设计:SQL查询优化、分库分表

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

whs0668
03/27
0
0
Apache Trafodion——分布式 SQL 引擎

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

oschina
2016/07/18
9
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
0
0
Linux-MySQL基本命令-SQL语句

服务端命令SQL 在数据库系统中,SQL语句不区分大小写(建议用大写) SQL语句可单行或多行书写,以“;”结尾 关键词不能跨多行或简写 用空格和缩进来提高语句的可读性 子句通常位于独立...

一入IT深似海·
07/29
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

(三)Nginx配置·续

概述 前文写了关于Nginx环境配置,但是还没有完,接下来将会继续讲三个相关的配置 主要是以下三个 1.Nginx访问日志 2.Nginx日志切割 3.静态文件不记录日志和过期时间 Nginx访问日志 1.先看看...

杉下
今天
1
0
jquery创建类似于java的map

var map = {}; // Map map = new HashMap(); map[key] = value; // map.put(key, value); var value = map[key]; // Object value = map.get(key); var has = key in map; // boolean has = ......

SuperDabai
今天
0
0
java大数据转换16进制转10进制

public static void main(String[] args) {String hex = "0xdbf3accc683297cf0000";BigInteger amount = new BigInteger(hex.substring(2), 16);System.out.println(amount);......

任梁荣
昨天
2
0
OSChina 周六乱弹 —— 目测我们程序员丁克的几率不大

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @真Skr小机灵鬼儿:8.13分享Jocelyn Pook/Russian Red的单曲《Loving Strangers》 《Loving Strangers》- Jocelyn Pook/Russian Red 手机党少...

小小编辑
昨天
14
4
TypeScript基础入门 - 函数 - 剩余参数

转载 TypeScript基础入门 - 函数 - 剩余参数 项目实践仓库 https://github.com/durban89/typescript_demo.gittag: 1.2.1 为了保证后面的学习演示需要安装下ts-node,这样后面的每个操作都能...

durban
昨天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部