首页 / MYSQL / SQL优化,百万级2张表关联,从40分钟到3秒的历程_MySQL

SQL优化,百万级2张表关联,从40分钟到3秒的历程_MySQL

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了SQL优化,百万级2张表关联,从40分钟到3秒的历程_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4421字，纯文字阅读大概需要7分钟。

内容图文

bitsCN.com

SQL优化,百万级2张表关联,从40分钟到3秒的历程

[sql]

表结构如下:

[sql]

CREATE TABLE `deviceback` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`imei` varchar(100) NOT NULL COMMENT '手机唯一标识',

`mid` varchar(50) DEFAULT NULL,

`mac` varchar(100) DEFAULT NULL,

`APNType` varchar(100) DEFAULT NULL,

`status` int(11) DEFAULT '0',

`ip` varchar(100) DEFAULT NULL,

`sn` varchar(100) DEFAULT NULL COMMENT '系列号',

`oem` varchar(100) DEFAULT NULL COMMENT '厂商',

`product` varchar(100) DEFAULT NULL COMMENT '产品',

`region` varchar(100) DEFAULT NULL COMMENT '区域',

`operator` varchar(100) DEFAULT NULL COMMENT '运营商',

`sim` varchar(100) DEFAULT NULL COMMENT 'sim卡号',

`push_time` timestamp NULL DEFAULT NULL COMMENT '第一次登陆时间',

`origin_version` varchar(100) DEFAULT NULL COMMENT '原始版本',

`province` varchar(100) DEFAULT NULL COMMENT '省份',

`provinceCode` varchar(100) DEFAULT NULL COMMENT '省份code',

`city` varchar(50) DEFAULT NULL COMMENT '城市',

`cityCode` varchar(50) DEFAULT NULL COMMENT '城市code',

`brands` varchar(50) DEFAULT '0',

`version` varchar(100) DEFAULT '0' COMMENT '客户端版本号',

`last_checktime` timestamp NULL DEFAULT NULL COMMENT '最后一次登录时间',

PRIMARY KEY (`id`),

FULLTEXT KEY `NewIndex1` (`imei`),

FULLTEXT KEY `NewIndex2` (`mid`),

FULLTEXT KEY `NewIndex3` (`product`),

FULLTEXT KEY `NewIndex4` (`brands`)

) ENGINE=MyISAM AUTO_INCREMENT=6832460 DEFAULT CHARSET=utf8;

[sql]

CREATE TABLE `20130602_AppLog` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`imei` varchar(100) DEFAULT NULL,

`mid` varchar(50) NOT NULL DEFAULT '',

`status` char(1) NOT NULL DEFAULT '0',

`mac` varchar(100) DEFAULT 'NULL',

`sn` varchar(100) DEFAULT NULL,

`sim` varchar(100) DEFAULT NULL,

`coperator` varchar(100) DEFAULT NULL,

`version` varchar(100) DEFAULT NULL,

`logintime` datetime DEFAULT NULL,

`ip` varchar(20) DEFAULT NULL,

`origin_version` varchar(100) DEFAULT NULL,

`now_version` varchar(100) DEFAULT NULL,

`APNType` varchar(20) DEFAULT NULL,

`oem` varchar(20) DEFAULT NULL,

`product` varchar(100) DEFAULT NULL,

`region` varchar(100) DEFAULT NULL,

`operator` varchar(100) DEFAULT NULL,

PRIMARY KEY (`id`),

FULLTEXT KEY `NewIndex1` (`imei`),

FULLTEXT KEY `NewIndex2` (`mid`)

) ENGINE=MyISAM AUTO_INCREMENT=3123866 DEFAULT CHARSET=utf8;

SQL如下:

[sql]

SELECT *

FROM deviceback d,20130602_AppLog g

WHERE d.mid=g.mid

AND d.imei=g.imei

AND d.mac=g.mac

AND d.brands=0

AND g.coperator <> '' limit 20;

explain

结果：

SQL优化,百万级2张表关联,从40分钟到3秒的历程_MySQL - 文章图片

从这里可以看到ALL以及key为NULL，就是全表扫描，没有走索引，索引失效了！

1 然后我建议建上加上联合索引，试试看效果如何：

SQL优化,百万级2张表关联,从40分钟到3秒的历程_MySQL - 文章图片

2 效果还是比较慢的，看来得换种办法了，检查所有关联字段，将为null的数据改成''。还是没有效果。

3 不查*了，直接count(*) 看看结果集大小，结果还是卡住了，短时间内没有出查询结果。

4，到这里我猜测估计是数据的构成问题，那就一个个条件去掉去尝试了。

SELECT *

FROM deviceback d,20130602_AppLog g

WHERE d.mid=g.mid

AND d.imei=g.imei

AND d.mac=g.mac

AND d.brands=0 limit 20;

去掉<> 条件试试看，God，结果是37秒就出来了，好，大概问题找到了，出在<>这条判断语句里面，也就是

AND g.coperator <> '' 这个影响还蛮大的。

那就先在20130602_AppLog 表的coperator字段单独建索引试试看，然后查下 20130602_AppLog里面 g.coperator <> ''的有多少？

结果是比较令人欣慰的，单独查询，一秒不到出来结果了，6W多条纪录。

SELECT COUNT(1)

FROM 20130602_AppLog gg

WHERE gg.coperator <> ''

63987

然后再试试整个sql，看需要多长时间。

先看下explain结果：

SQL优化,百万级2张表关联,从40分钟到3秒的历程_MySQL - 文章图片

SELECT *

FROM deviceback d,20130602_AppLog g

WHERE d.mid=g.mid

AND d.imei=g.imei

AND d.mac=g.mac

AND d.brands=0

AND g.coperator <> '' limit 20;

Great，不到3秒就出来结果了。

5，limit 20是OK了，我还想试试不限制limit的话，要多久可以查询出来结果。

直接执行

SELECT *

FROM deviceback d,20130602_AppLog g

WHERE d.mid=g.mid

AND d.imei=g.imei

AND d.mac=g.mac

AND d.brands=0

AND g.coperator <> '' ;

卡住了，很久都没有出来结果，在想是否是数据的问题？切换下where条件后面的字段顺序试试看。

SELECT d.mid,g.mac,

FROM deviceback d,20130602_AppLog g

WHERE

d.imei=g.imei

AND d.mac=g.mac

AND d.mid=g.mid

AND d.brands=0

AND g.coperator <> '' ;

OK，27秒出来了，AND d.mid=g.mid这个之前在第一条，现在放在后面，原因是手机唯一标示这个字段有空值。

God，最讨厌关键业务字段null值了，给我们的优化工作带来巨大的烦恼。

切记：大家以后千万要记住关键业务字段不能允许录入null值。

bitsCN.com

内容总结

以上是互联网集市为您收集整理的SQL优化,百万级2张表关联,从40分钟到3秒的历程_MySQL全部内容，希望文章能够帮你解决SQL优化,百万级2张表关联,从40分钟到3秒的历程_MySQL所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/574367.html

来源：【匿名】

【上一篇】Mysql开源备份工具Xtrabackup备份部署_MySQL 【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【SQL优化,百万级2张表关联,从40分钟到3秒的历程_MySQL】教程文章相关的互联网学习教程文章

11.mysql SQL优化之SQL问题定位【代码】【图】

3.1 查看SQL执行频率MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。下面的命令显示了当前 session 中所有统计参数的值：show status like‘Com_______‘;show status like‘I...

Mysql 优化原理【图】

说起MySQL的查询优化，相信大家收藏了一堆奇技淫巧：不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧？是否理解其背后的工作原理？在实际场景下性能真有提升吗？我想未必。因而理解这些优化建议背后的原理就尤为重要，希望本文能让你重新审视这些优化建议，并在实际业务场景下合理的运用。 MySQL逻辑架构如果能在头脑中构建一幅MySQL各组件之间如何协同工作的架构图，...

运行环境为centos7，tomcat和mysql的优化方式，经验总结【代码】

一般在服务的cpu占用很高时，1.使用ps -mp pid -o THREAD,tid,time命令查看该进程的线程情况，可以列举出占用高的线程，挑选个占用高的线程的tid，eg:挑选TID为14065的线程，查看该线程的堆栈情况，先将线程id转为16进制，使用printf "%x\n" tid命令进行转换(printf "%x\n" 14065)再使用jstack命令打印线程堆栈信息，命令格式：jstack pid |grep tid -A 30(pid:进程id，tid：转换后的线程id的16进制)，可以打印出堆栈信息，在堆栈信...

MySQL优化技巧【代码】【图】

原文:MySQL优化技巧目录MySQL的特点数据类型优化整型类型小数类型字符串类型时间类型主键类型的选择特殊类型的数据索引优化一个使用Hash值创建索引的技巧前缀索引多列索引聚簇索引覆盖索引重复索引和冗余索引不使用的索引索引使用总结查询优化查询慢的原因重构查询的方式杂七杂八参考书本文的内容是总结一些MySQL的常见使用技巧，以供没有DBA的团队参考。以下内容以MySQL5.5为准，如无特殊说明，存储引擎以InnoDB为准。MySQL的特点...

[mysql][【优化集合】mysql数据库优化集合【代码】

三个层面：1.系统层面2.mysql配置参数3.sql语句优化===========================================================一。系统层面 ===========================================================二。mysql参数层面http://www.oicto.com/mysql-explain-show/2.1slowlog配置slowlog 配置文件： log-slow-queries=/path/to/save/slowlog long_time_query=2 变量设置： show variables like ‘%slow%‘setglobal log_slow_queries = ON...

mysql优化limit查询语句的5个方法

mysql的分页比较简单，只需要limit offset,length就可以获取数据了，但是当offset和length比较大的时候，mysql明显性能下降1.子查询优化法先找出第一条数据，然后大于等于这条数据的id就是要获取的数据缺点：数据必须是连续的，可以说不能有where条件，where条件会筛选数据，导致数据失去连续性，具体方法请看下面的查询实例：复制代码代码如下: mysql> set profiling=1; Query OK, 0 rows affected (0.00 sec) mysql> select c...

mysql优化--explain分析sql语句执行效率

Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。Explain语法：explain select … from … [where …]例如：explain select * from news;输出：+----+-------------+-------+-------+-------------------+---------+---------+-------+------| id | select_type | table | typ...

Mysql优化（一）

总结下MySql优化。防止数据灾难的发生。

在PHP开发中用到的数据库中MySql是最牛逼的数据库，没有之一……^_^相比Sqlite个人最喜欢的特性就是“支持多线程，充分利用 CPU 资源”，不像Sqlite那样，动不动就锁死，导致存储发生异常，特别是.NET开发桌面应用时，为了方便用了Sqlite，那真的是先甜后苦啊，有苦说不出的苦。MySql遇到比较多的就是商城系统、博客系统，订单和数据存储达到一定量级之后，会出现慢查询，这个问题在开发维护过程中谁也躲不过去。下面是常规的处理方...

Mysql 优化建议【代码】

1、硬件层相关优化1.1、CPU相关在服务器的BIOS设置中，可调整下面的几个配置，目的是发挥CPU最大性能，或者避免经典的NUMA问题：1、选择Performance Per Watt Optimized(DAPC)模式，发挥CPU最大性能，跑DB这种通常需要高运算量的服务就不要考虑节电了； 2、关闭C1E和C States等选项，目的也是为了提升CPU效率3、Memory Frequency（内存频率）选择Maximum Performance（最佳性能）； 4、内存设置菜单中，启用Node Interleaving，避免...

Mysql优化之索引和字段【图】

Mysql优化是一个老生常谈的问题,优化的方向也优化很多:从架构层;从设计层;从存储层;从SQL语句层;今天讲解一下从索引和字段:字段优化:① 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED;② VARCHAR的长度只分配真正需要的空间;③ 使用枚举或整数代替字符串类型;④ 尽量使用TIMESTAMP而非DATETIME;⑤ 单表不要有太多字段，建议在20以内;⑥ 避免使用NULL字段，很难查询优化且占用额外索引空...

MySQL 优化实施方案【代码】

1.1 前言　在进行MySQL的优化之前必须要了解的就是MySQL的查询过程，很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。更多关于MySQL查询相关参照：http://www.cnblogs.com/clsn/p/8038964.html#_label6 系列文章。图 - MySQL查询过程1.2 优化的哲学优化有风险，涉足需谨慎1.2.1 优化可能带来的问题优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统。优化手段本来...

mysql的优化:官网地址

http://dev.mysql.com/doc/refman/5.1/zh/optimization.html#index-merge-optimization原文：http://www.cnblogs.com/ribavnu/p/3541273.html

MySql 优化全集【代码】【图】

前言：在日常工作中，我们需要对查询较慢的SQL进行分析，这时我们就会用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描。-- 分析SQL EXPLAIN SELECT * FROM zy_ticket_sw_classify WHERE industry_name = ‘石油开采‘； expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra描述：id：选择标识符；如果explain的...

大牛是怎么思考设计MySQL优化方案的？【代码】【图】

在进行MySQL的优化之前，必须要了解的就是MySQL的查询过程，很多查询优化工作实际上就是遵循一些原则，让MySQL的优化器能够按照预想的合理方式运行而已。图-MySQL查询过程一、优化的哲学注：优化有风险，涉足需谨慎1、优化可能带来的问题优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统；优化手段本来就有很大的风险，只不过你没能力意识到和预见到；任何的技术可以解决一个问题，但必然存在带来一个问题的风险...

MYSQL - 技术教程分类

MySQL 教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL 创建数据库 MySQL 删除数据库 MySQL 选择数据库 MySQL 数据类型 MySQL 创建数据表 MySQL 删除数据表 MySQL 插入数据 MySQL 查询数据 MySQL WHERE 子句 MySQL UPDATE 更新 MySQL DELETE 语句 MySQL LIKE 子句 MySQL UNION MySQL 排序 MySQL 分组 MySQL 连接的使用 MySQL NULL 值处理 MySQL 事务 MySQL ALTER命令 MySQL 索引 MySQL 临时表 MySQL 复制表 MySQL 元数据 MySQL 序列使用 MySQL 处理重复数据 MySQL 及 SQL 注入 MySQL 导出数据 MySQL 导入数据 MySQL 函数 MySQL 运算符 mysql 全部

MYSQL - 最热教程

sql分组取最大记录方法 mysql如何设置默认值 mysql创建数据表时指定默认值教程 MySQL 5.7 的初始化操作（root初始密码...Oracle中合并数据集(多行变一行)mysql 相同内容的字段合并为一条的方法解决mysql设置时区时的错误Unknown or ...解决ubuntu下mysql的'Access denied fo...ubuntu系统中MysqlERROR1045(28000)报错...mysql数据库设置不区分大小写

首页 / MYSQL / SQL优化,百万级2张表关联,从40分钟到3秒的历程_MySQL

SQL优化,百万级2张表关联,从40分钟到3秒的历程_MySQL

内容导读

内容图文

内容总结

内容备注

内容手机端

【SQL优化,百万级2张表关联,从40分钟到3秒的历程_MySQL】教程文章相关的互联网学习教程文章

11.mysql SQL优化之SQL问题定位【代码】【图】

Mysql 优化原理【图】

运行环境为centos7，tomcat和mysql的优化方式，经验总结【代码】

MySQL优化技巧【代码】【图】

[mysql][【优化集合】mysql数据库优化集合【代码】

mysql优化limit查询语句的5个方法

mysql优化--explain分析sql语句执行效率

Mysql优化（一）

总结下MySql优化。防止数据灾难的发生。

Mysql 优化建议【代码】

Mysql优化之索引和字段【图】

MySQL 优化实施方案【代码】

mysql的优化:官网地址

MySql 优化全集【代码】【图】

大牛是怎么思考设计MySQL优化方案的？【代码】【图】

MYSQL - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程