在MySQL中,我们可以将NOT EXISTS语句转换为LEFT
JOIN语句来进行优化,哪为什么会有性能提升呢?

摘自:

 需要学习的朋友可以通过网盘下载:http://tadown.com/fs/cyi3becns9hu46cf7/

深入浅出mysql 从数据库的基础、开发、优化、管理维护4个方面对MySQL进行了详细的介绍,其中每一部分都独立成篇。
    基础篇主要适合于MySQL的初学者,内容包括MySQL的安装与配置、SQL基础、MySQL支持的数据类型、MySQL中的运算符、常用函数、图形化工具的使用等。
    开发篇主要适合于MySQL的设计和开发人员,内容包括表类型(存储引擎)的选择、选择合适的数据类型、字符集、索引的设计和使用、视图、存储过程和函数、触发器、事务控制和锁定语句、SQL中的安全问题、SQL Mode及相关问题等。
    优化篇主要适合于开发人员和数据库管理员,内容包括常用SQL技巧和常见问题、SQL优化、优化数据库对象、锁问题、优化 MySQL Server、磁盘I/O问题、应用优化等。
    管理维护篇主要适合于数据库管理员,内容包括MySQL高级安装和升级、MySQL中的常用工具、MySQL 日志、备份与恢复、MySQL权限与安全、MySQL复制、MySQL Cluster、MySQL常见问题和应用技巧等。

使用NOT EXISTS方式SQL为:

What is schema?

SELECT count(1)
FROM t_monitor m
WHERE NOT exists
(SELECT 1
FROM t_alarm_realtime AS a
WHERE a.resource_id=m.resource_id
AND a.resource_type=m.resource_type
AND a.monitor_name=m.monitor_name)

In MySQL, physically, a schema is synonymous with a
database. You can substitute the keyword schema instead of
database in MySQL SQL syntax, for example using create schema instead of
create database.

而使用LEFT JOIN方式SQL为:

Some other database products draw a distinction. For example, in the
Oracle Database product, a schema represents only a part of a
database: the tables and other objects owned by a single user.

SELECT count(1)
FROM t_monitor m
LEFT JOIN t_alarm_realtime AS a
ON a.resource_id=m.resource_id
AND a.resource_type=m.resource_type
AND a.monitor_name=m.monitor_name
WHERE a.resource_id is NULL

What is a Data Model?

从查询效果来看,NOT EXISTS 方式耗时29.38秒,而LEFT
JOIN方式耗时1.20秒,性能提升25倍左右。

A data model is an abstract model, that documents and organizes the
enterprise data for communication between developers. Most of the times
a data model is reprensented in a diagram. A data model is used as a
plan for developing software, specifically how data is stored and
accessed. A data model of your database can support the analysis,
design, documentation and migration of underlying database structures.

查看NOT EXISTS方式的执行计划:

How to design database/schema?

*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: m
partitions: NULL
type: index
possible_keys: NULL
key: idx_id_name_type
key_len: 119
ref: NULL
rows: 578436
filtered: 100.00
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: a
partitions: NULL
type: eq_ref
possible_keys: idx_id_name_type
key: idx_id_name_type
key_len: 119
ref: cmdb.m.resource_id,cmdb.m.monitor_name,cmdb.m.resource_type
rows: 1
filtered: 100.00
Extra: Using index

In software engineering, data modeling is the process of creating a data
model for an information system. This is done by applying formal data
modeling techniques.
 T‌here are many tools which can help you with modeling such as SQLDBM.

 

SQLDbm

查看LEFT JOIN方式的执行计划:

图片 1

发表评论

电子邮件地址不会被公开。 必填项已用*标注