未加星标

让你脑洞大开的MySQL优化技巧

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二05 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏
让你脑洞大开的mysql优化技巧

一点号linux资讯速推3小时前

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

先查询B中的 a_id

SELECT c_a FROM B WHERE xxx;

使用 IN 查询 A 表

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

现在表的数据量有 800万。

一般的使用语句是:

SELECT * FROM A WHERE c_a IN;

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

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

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

创建表结构语句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查询连续的数

SELECT * FROM t WHERE cid IN; | id | cid | c1 | 319330 | 955555 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | 1885293 | 955555 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | ...... | 8733757 | 955559 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa | 8796305 | 955559 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 41 rows in set (0.15 sec)

使用IN查询不连续的数

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优化

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了。

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/

本文地址:http://www.linuxprobe.com/special-optimize-sql.html编辑:杨鹏飞,审核员:岳国帅

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: MySQLSQLAULinux数据AUTUTRIMRY
分页:12
转载请注明
本文标题:让你脑洞大开的MySQL优化技巧
本站链接:http://www.codesec.net/view/524072.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(89)