文档章节

SQL优化-跨裤查询wherein优化

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

没有更多内容

加载失败,请刷新页面

加载更多

自己手写一个 SpringMVC 框架

前端框架很多,但没有一个框架称霸,后端框架现在Spring已经完成大一统.所以学习Spring是Java程序员的必修课. Spring 框架对于 Java 后端程序员来说再熟悉不过了,以前只知道它用的反射实现的,...

别打我会飞
12分钟前
0
0
01-《Apache Tomcat 9》之文件索引

《Apache Tomcat 9》是《看Apache官方文档学英语》的第一个专栏!让我们一起在看文档的过程中学英语,在学英语的过程中夯实技术! Documentation Index - 文件索引 Introduction - 介绍 This...

飞鱼说编程
13分钟前
0
0
最近

20181016最近在熟悉业务 关于money的 要涉及到流程中转同步 这个点感觉 业务大于技术 关于业务性的内容 还是要把自己及时清零的好 我们需要好好的梳理下业务内容 业务作为导向 技术提供解决方...

JAVA码猿
23分钟前
0
0
JDK1.8HashMap源码分析

HashMap和Hashtable的主要区别是: 1. Hashtable是线程安全,而HashMap则非线程安全,Hashtable的实现方法里面大部分都添加了synchronized关键字来确保线程同步,因此相对而言HashMap性能会高...

小小明童鞋
33分钟前
15
0
以Redis为例,详谈分布式系统缓存的细枝末节

前言: 在分布式Web程序设计中,解决高并发以及内部解耦的关键技术离不开缓存和队列,而缓存角色类似计算机硬件中CPU的各级缓存。如今的业务规模稍大的互联网项目,即使在最初beta版的开发上...

Java干货分享
35分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部