MySQL数据库第十四课--------sql优化---------层层递进1

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL数据库第十四课--------sql优化---------层层递进

插播小知识

1.python导入sys ,sys,path.append(文件路径)的效果跟import的效果相似都可以导入python脚本文件

2.to_csv(文件名称)保存到csv文件, to_excel(文件名称)保存到xlsx文件中

SQL优化

这里的SQL优化主要是针对于数据量十分巨大时候的处理。在具体的环境,我们是需要逐步调试SQL语句,以保证运行的性能。

查询

星号(*)

尽量避免使用select * 进行全字段的查询,为啥这么说呢?,前面我们用这个命令查询是因为数据量少,数据量很大的话一使用这个就会看不清楚,满屏的数值,想想就很可怕,所以我们尽量使用写字段的方法

select a.id,a.`name`,a.age from 学生表格1 As a;

使用*还会降低性能,我们可以理解一下,当判断出所有字段后,还要再往后判断一次是否这个表还有字段,而直接写出字段就不会判断,节省了资源,如果不理解,我们可以想象一下,数据非常大,每多做出一次判断都会影响MySQL的性能

这样写既可以让我们知道表格里面有哪些字段,

去重(尽量使用group by)

select count(1) from employees  where first_name='Georgi';
select
    count(1) as 剩下的Georgi数量
from
    (
        select 
         DISTINCT first_name as first_name 
        from 
            employees
        where first_name='Georgi'
    ) as a
where
    a.first_name='Georgi';

上面代码就是使用distinct去重

而我们使用group by时

select 
    first_name
    ,count(1)
from
    employees
where first_name='Georgi'
group by first_name;

最终结果是一样的

如果使用python的逻辑去理解的话,distinct 是判断是否存在,


上面每个方框和下面数据对应一下

如果全为1则说明这两条数据相同,distinct 就是要一一比较然后再判断,而group by就是只要方框内的内容只要不相同就会停止判断

使用 distinct * 进行去重,如果数据量过大,会导致数据库运行效率很慢。

在SQL语句的查询中,中心点在于进一步缩小查询范围,然后找到满足条件的数据。所以在很多时

候,我们可以用冗长的SQL语句来节省数据库的运行时间。

select
id
,name
,age
from table_name
where age = 50
or age = 60
select
id
,name
,age
from table_name
where age = 50
union all
select
id
,name
,age
from table_name
where age = 60

简单理解就是我们来计算,计算机执行,我们花费时间写更多的代码来换取更多的内存空间,运行效率就会得到提升

关联查询 join

# 正常SQL数据查询获取
sql = '''
select
a.emp_no
,a.age
,a.gender
,b.dept_no
from new_employees a
join dept_emp b
on a.emp_no = b.emp_no
and b.dept_no = 'd001'
'''
Data_Dw().mysql_to_df(sql)

这里的意思就是new_employees和dept_emp通过a.emp_no = b.emp_no进行关联,关联出b.dept_no = ‘d001’

如果我们先从dept_emp表格里获取20条数据,获取主键值,再从表employees找出,

每次找出5条

sql_1 = """
select 
    emp_no
from
    dept_emp
limit 20
"""
retur = Data_Dw().mysql_to_df(sql_1)
print(type(retur))
print(list(retur))
print(len(retur))
retur
print(retur['emp_no'])
print(list(retur['emp_no']))
NPB = 5
retur_list = list(retur['emp_no'])
retur_list
while 1:
    if len(retur_list) <= NPB:
        #获取表头
        d_type = tuple(retur)[0]
        #获取内容
        sql_2 = f"""select  * from  employees where {d_type} in{tuple(retur_list)}"""
        data = Data_Dw().mysql_to_df(sql_2)
        print(data)
        break
    else:
        # 获取表头
        d_type = tuple(retur)[0]
        # 获取5个数据 并输出
        pop_list = list()
        while len(retur_list) > 0:
            if len( pop_list)< NPB:
                 pop_list.append(retur_list.pop())
            else:
                pop_tuple = tuple( pop_list)
                sql_3 = f"""select * from  employees where {d_type} in {pop_tuple} """
                data = Data_Dw().mysql_to_df(sql_3)
                print(data)
                break

108836304a714ac591a79dadc6a7fc8e.png

第一框代码是使用join的,原理相当于是一次性从许多数据 找出一部分数据,

而第二框是先在ept_emp表格里获取需要的数据,然后拆分成许多小块,然后每个小块在表employees找出,

这样写的好处是啥呢?我们想一想,我们进会场,一次进1000人,现场就会管理很好,如果一下子全部人进入,就会很混乱

在mysql里如果一下子查询许多数据就会给内存增加很大的压力。

通过python实现两张表格的关联查询

原理就是使用sql语句分别查出需要关联的内容,然后通过python关联在一起,这样可以节约mysql内存

sql_4 = """
select 
    emp_no
    ,dept_no
from
    dept_emp
limit 20
"""
data_1 = Data_Dw().mysql_to_df(sql_4)
data_1
sql_4 = """
select 
    emp_no
    ,birth_date
from
     employees 
limit 20
"""
data_2 = Data_Dw().mysql_to_df(sql_4)
data_2
result=data_1.merge(data_2,how='inner',on='emp_no')
result["birth_date"].head(1)

merge函数构成:

参数介绍:

left:参与合并的左侧DataFrame;

right:参与合并的右侧DataFrame;

how:连接方式,有inner、left、right、outer,默认为inner;

on:指的是用于连接的列索引名称,必须存在于左右两个DataFrame中,如果没有指定且其他参数也没有指定,则以两个DataFrame列名交集作为连接键;

left_on:左侧DataFrame中用于连接键的列名,这个参数左右列名不同但代表的含义相同时非常的有 用;

right_on:右侧DataFrame中用于连接键的列名;

left_index:使用左侧DataFrame中的行索引作为连接键;

right_index:使用右侧DataFrame中的行索引作为连接键;

sort:默认为True,将合并的数据进行排序,设置为False可以提高性能;

suffixes:字符串值组成的元组,用于指定当左右DataFrame存在相同列名时在列名后面附加的后缀名称,默认为(‘_x’, ‘_y’);

copy:默认为True,总是将数据复制到数据结构中,设置为False可以提高性能;

indicator:显示合并数据中数据的来源情况。

result[“birth_date”].head(1) #查看前1条数据

删除 delete

delete from table_name where 条件

这是我们删除语句,但是这种往往不适合删除数据量很大的数据,由于服务器的运行性能的限制,我们就要考虑分段删除了,我们可以通过python语句来操控删除,

方法1

sql_5 = """
select 
    name
from 数据库1.学生表格1
where 
    name = '大佬'
"""
data_2 = Data_Dw().mysql_to_df(sql_5)
len(data_2)
while 1;
 #判断是否还有数据
    if len(data_2) == 0:
        break
    else:
        sql_6 = """
            delete from
                数据库1.学生表格1;
            where 
                name = '大佬'
        """
        data_3 = Data_Dw().mysql_to_df(sql_5)
        data_3

这个方法缺点就是每次查询都是在数据库里面进行的,很大程度上让数据库的负担加重了,如果数据量小还行,如果数据量大那就不适合了

方法2

sql_6= """
select 
    name
from 数据库1.学生表格1
where 
    name = '大佬'
"""
data_3 = Data_Dw().mysql_to_df(sql_6)
data_3["name"]
# # 获取表头
# list(data_3)
# data_list = list(data_3["name"])
# sa_tuple = list()
# CNB = 10
# while 1:
#     if (len(list(data_3["name"]))) < CNB:
#         sql_7 = f""" delete from 数据库1.学生表格1 where {list(data_3)[0]} in {tuple(data_3["name"])}"""
#         print(Data_Dw().mysql_to_df(sql_7))
#         break
#     else:
#         while 1:
#             if len(sa_tuple) < CNB:
#                 sa_tuple.append(data_list.pop())
#             else:
#                 sql_8 = f""" delete from 数据库1.学生表格1 where {list(data_3)[0]} in {tuple(sa_tuple)}"""
#                 print(Data_Dw().mysql_to_df(sql_8))
#                 break

对比以上两个方法:

方法一:

通过多次查询数据库,先确定是否存在需要删除的数据,然后进行删除。主要适用于服务器或者数据库硬件性能不足,但是本身使用频繁较低的情况。

方法二:

先圈选需要删除的数据,然后通过循环进行数据的删除。减少了数据库查询的次数,将更多的运算逻辑运用于python中。主要适用于服务器性能充沛,但是数据库已经被其他任务过多占用的情况。


这两种方法都比直接一次性删除全部数据要快很多

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
10天前
|
SQL 存储 缓存
浅析MySQL中的SQL执行过程
本文探讨了MySQL的体系结构、SQL执行流程及SQL执行时间分析方法。首先介绍了MySQL由连接层、SQL层和存储引擎层构成;接着详细描述了SQL从客户端发送到服务器执行的具体流程;最后,通过启用profiling功能,展示了如何分析SQL执行时间,并说明了MySQL 8.0版本后移除查询缓存的原因。
浅析MySQL中的SQL执行过程
|
1天前
|
SQL 关系型数据库 MySQL
SQL和MySQL
SQL和MySQL
7 1
|
1天前
|
存储 缓存 关系型数据库
MySQL 视图:数据库中的灵活利器
视图是数据库中的虚拟表,由一个或多个表的数据经筛选、聚合等操作生成。它不实际存储数据,而是动态从基础表中获取。视图可简化数据访问、增强安全性、提供数据独立性、实现可重用性并提高性能,是管理数据库数据的有效工具。
|
1天前
|
SQL 关系型数据库 MySQL
MySQL技术安装配置、数据库与表的设计、数据操作解析
MySQL,作为最流行的关系型数据库管理系统之一,在WEB应用领域中占据着举足轻重的地位。本文将从MySQL的基本概念、安装配置、数据库与表的设计、数据操作解析,并通过具体的代码示例展示如何在实际项目中应用MySQL。
6 0
|
1天前
|
SQL 关系型数据库 MySQL
MySQL根据某个字段包含某个字符串或者字段的长度情况更新另一个字段的值,如何写sql
MySQL根据某个字段包含某个字符串或者字段的长度情况更新另一个字段的值,如何写sql
6 0
|
10天前
|
SQL 安全 数据库
基于SQL Server事务日志的数据库恢复技术及实战代码详解
基于事务日志的数据库恢复技术是SQL Server中一个非常强大的功能,它能够帮助数据库管理员在数据丢失或损坏的情况下,有效地恢复数据。通过定期备份数据库和事务日志,并在需要时按照正确的步骤恢复,可以最大限度地减少数据丢失的风险。需要注意的是,恢复数据是一个需要谨慎操作的过程,建议在执行恢复操作之前,详细了解相关的操作步骤和注意事项,以确保数据的安全和完整。
26 0
|
14天前
|
前端开发 C# 设计模式
“深度剖析WPF开发中的设计模式应用:以MVVM为核心,手把手教你重构代码结构,实现软件工程的最佳实践与高效协作”
【8月更文挑战第31天】设计模式是在软件工程中解决常见问题的成熟方案。在WPF开发中,合理应用如MVC、MVVM及工厂模式等能显著提升代码质量和可维护性。本文通过具体案例,详细解析了这些模式的实际应用,特别是MVVM模式如何通过分离UI逻辑与业务逻辑,实现视图与模型的松耦合,从而优化代码结构并提高开发效率。通过示例代码展示了从模型定义、视图模型管理到视图展示的全过程,帮助读者更好地理解并应用这些模式。
30 0
|
14天前
|
SQL 数据处理 数据库
|
14天前
|
SQL 数据采集 关系型数据库
|
14天前
|
Java 数据库连接 数据库
告别繁琐 SQL!Hibernate 入门指南带你轻松玩转 ORM,解锁高效数据库操作新姿势
【8月更文挑战第31天】Hibernate 是一款流行的 Java 持久层框架,简化了对象关系映射(ORM)过程,使开发者能以面向对象的方式进行数据持久化操作而无需直接编写 SQL 语句。本文提供 Hibernate 入门指南,介绍核心概念及示例代码,涵盖依赖引入、配置文件设置、实体类定义、工具类构建及基本 CRUD 操作。通过学习,你将掌握使用 Hibernate 简化数据持久化的技巧,为实际项目应用打下基础。
35 0