当前位置： 首页 > news >正文

MySQL（python开发)——（10）Sql操作及优化

news 2024/10/25 10:42:05

MySQL（python开发)——（1）数据库概述及其MySQL介绍
MySQL（python开发)——（2）数据库基本操作及数据类型
MySQL（python开发)——（3）表数据的基本操作，增删改查
MySQL（python开发)——（4）高级查询语句
MySQL（python开发)——（5）聚合操作
MySQL（python开发)——（6）索引操作
MySQL（python开发)——（7）外键约束和表关联关系
MySQL（python开发)——（8）视图
MySQL（python开发)——（9）函数和存储过程

MySQL——Sql操作及优化

一. 事务控制

1. 事务概述

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，如果操作就必须同时操作成功，如果有一个不成功则所有数据都不动。这时候数据库操作语句就构成一个事务。事务主要处理数据的增删改操作。

  • 定义

一件事从开始发生到结束的过程

  • 作用

确保数据操作过程中的数据完整和使用安全。

2. 事务操作

  1. 开启事务
mysql>begin;
  1. 开始执行事务中的若干条SQL命令（增删改）
  2. 终止事务，若begin之后使用commit提交事务或者使用rollback进行事务回滚。
mysql>commit; # 事务中SQL命令都执行成功,提交到数据库,结束!
mysql>rollback; # 有SQL命令执行失败,回滚到初始状态,结束!

注意：事务操作只针对数据操作。rollback不能对数据库，数据表结构操作恢复。

3. 事务四大特性

  1. 原子性（atomicity）

一个事务必须视为一个不可分割的最小工作单元，对于一个事务来说，不可能只执行其中的一部分操作,整个事务中的所有操作要么全部提交成功，要么全部失败回滚

  1. 一致性（consistency）

事务完成时，数据必须处于一致状态，数据的完整性约束没有被破坏。

  1. 隔离性（isolation）

数据库允许多个并发事务同时对其数据进行读写和修改的能力，而多个事务相互独立。隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

  1. 持久性（durability）

一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

4. 事务隔离级别

事务四大特性中的隔离性是在使用事务时最为需要注意的特性，因为隔离级别不同带来的操作现象也有区别

  • 隔离级别

    • 读未提交：read uncommitted

      事物A和事物B，事物A未提交的数据，事物B可以读取到
      这里读取到的数据叫做“脏数据”
      这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别

    • 读已提交：read committed

      事物A和事物B，事物A提交的数据，事物B才能读取到
      这种隔离级别高于读未提交
      换句话说，对方事物提交之后的数据，我当前事物才能读取到
      这种级别可以避免“脏数据”
      这种隔离级别会导致“不可重复读取”

    • 可重复读：repeatable read

      事务A和事务B，事务A提交之后的数据，事务B读取不到
      事务B是可重复读取数据
      这种隔离级别高于读已提交
      MySQL默认级别
      虽然可以达到可重复读取，但是会导致“幻像读”

    • 串行化：serializable

      事务A和事务B，事务A在操作数据库时，事务B只能排队等待
      这种隔离级别很少使用，吞吐量太低，用户体验差
      这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发

二.数据库设计范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

各种范式呈递次规范，越高的范式数据库冗余越小。但是范式越高也意味着表的划分更细，一个数据库中需要的表也就越多，此时多个表联接在一起的花费是巨大的，尤其是当需要连接的两张或者多张表数据非常庞大的时候，表连接操作几乎是一个噩梦，这严重地降低了系统运行性能。所以通常数据库设计遵循第一第二第三范式，以避免数据操作异常,又不至于表关系过于复杂。

范式简介：

  • 第一范式： 数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等组合的数据项。简单来说要求数据库中的表示二维表，每个数据元素不可再分。

    例如： 在国内的话通常理解都是姓名是一个不可再拆分的单位，这时候就符合第一范式；但是在国外的话还要分为FIRST NAME和LAST NAME，这时候姓名这个字段就是还可以拆分为更小的单位的字段，就不符合第一范式了。

  • 第二范式： 第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分，所有属性依赖于主属性。即选取一个能区分每个实体的属性或属性组，作为实体的唯一标识，每个属性都能被主属性筛选。其实简单理解要设置一个区分各个记录的主键就好了。

  • 第三范式： 在第二范式的基础上属性不传递依赖，即每个属性不依赖其他非主属性。要求一个表中不包含已在其它表中包含的非主关键字信息。其实简单来说就是合理使用外键，使不同的表中不要有重复的字段就好了。

三.MySQL存储引擎

  • 定义： mysql数据库管理系统中用来处理表的处理器
  • 基本操作
1、查看所有存储引擎mysql> show engines;
2、查看已有表的存储引擎mysql> show create table 表名;
3、创建表指定create table 表名(...)engine=MyISAM;
4、已有表指定alter table 表名 engine=InnoDB;

  • 常用存储引擎特点

    InnoDB

    1. 支持行级锁,仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进		行操作。
2. 支持外键、事务、事务回滚
3. 表字段和索引同存储在一个文件中1. 表名.frm ：表结构2. 表名.ibd : 表记录及索引文件

    MyISAM

    1. 支持表级锁,在锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则		读也不允许
2.  表字段和索引分开存储1. 表名.frm ：表结构2. 表名.MYI : 索引文件(my index)3. 表名.MYD : 表记录(my data)

  • 如何选择存储引擎

    1. 执行查操作多的表用 MyISAM(使用InnoDB浪费资源)
2. 执行写操作多的表用 InnoDBCREATE TABLE user(
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(30) DEFAULT NULL,
sex varchar(2) DEFAULT NULL,
PRIMARY KEY (id)
)ENGINE=MyISAM;alter table hobby engine=myisam;

四.字段数据类型和键的选择

  • 数据类型优先程度 数字类型 --> 时间日期类型 --> 字符串类型
  • 同一级别 占用空间小的 --> 占用空间大的
字符串在查询比较排序时数据处理慢
占用空间少，数据库占磁盘页少，读写处理就更快
  • Innodb如果不设置主键也会自己设置隐含的主键，所以最好自己设置
  • 尽量设置占用空间小的字段为主键
  • 建立外键会自动建立索引，在表关联查询时建议使用外键子段作为关联条件
  • 外键虽然可以保持数据完整性，但是会降低数据导入和操作效率，增加维护成本

五. SQL语句优化

  • explain工具

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询
explain select * from class where id <5;

EXPLAIN主要字段解析：

  • table：显示这一行的数据是关于哪张表的
  • type：这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL，一般来说，得保证查询至少达到range级别，最好能达到ref。
type中包含的值：
- system、const： 可以将查询的变量转为常量. 如id=1; id为 主键或唯一键.
- eq_ref： 访问索引,返回某单一行的数据.(通常在联接时出现，查询使用的索引为主键或唯一键)
- ref： 访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生 
- range： 这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西，并且该字段上建有索引时发生的情况
- index： 以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描 
- ALL： 全表扫描，应该尽量避免
  • possible_keys：显示可能应用在这张表中的索引。如果为空，表示没有可能应用的索引。
  • key：实际使用的索引。如果为NULL，则没有使用索引。
  • key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
  • rows：MySQL认为必须检索的用来返回请求数据的行数

SQL语句优化方法

  • 尽量选择数据类型占空间少，在where ，group by，order by中出现的频率高的字段建立索引

  • 尽量避免使用 select * …;用具体字段代替 * ,不要返回用不到的任何字段

  • 尽量控制使用自定义函数

  • 查询最后添加 LIMIT 会停止全表扫描

  • 尽量避免 NULL 值判断,否则会进行全表扫描，默认值为空时可以用默认0代替

    优化前：select number from t1 where number is null;

    优化后：select number from t1 where number=0;

  • 尽量避免 or 连接条件,否则会放弃索引进行全表扫描，可以用union代替

    优化前：select id from t1 where id=10 or id=20;

    优化后： select id from t1 where id=10 union all select id from t1 where id=20;

  • 尽量避免使用 in 和 not in,否则会全表扫描

    优化前：select id from t1 where id in (1,2,3,4);

    优化后：select id from t1 where id between 1 and 4;

六.表的拆分

垂直拆分 ： 表中列太多，分为多个表，每个表是其中的几个列。将常查询的放到一起，blob或者text类型字段放到另一个表

水平拆分 ： 减少每个表的数据量，通过关键字进行划分然后拆成多个表

七.数据备份和导入

1.表的复制

  1. 表能根据实际需求复制数据
  2. 复制表时不会把KEY属性复制过来

语法

create table 表名 select 查询命令;create table student select name,age,score from class where score>80

2. 数据库备份

  1. 备份命令格式

mysqldump -u 用户名 -p 源库名 > stu.sql

mysqldump -u root -p stu > stu.sql
  1. 恢复命令格式

mysql -u root -p 目标库名 < stu.sql

mysql -u root -p student < stu.sql

八.数据库管理

1. MySQL远程连接

更改配置文件，重启服务！
1.cd /etc/mysql/mysql.conf.d
2.sudo vi mysqld.cnf  找到43行左右,加 # 注释# bind-address = 127.0.0.13.保存退出
4.sudo service mysql restart
5.进入mysql修改用户表host值 use mysql;update user set host='%' where user='root';
6.刷新权限flush privileges;

2.添加用户和授权

1. 用root用户登录mysqlmysql -u root -p2. 添加用户 % 表示自动选择可用IPCREATE USER 'username'@'host' IDENTIFIED BY 'password';3. 权限管理# 增加权限grant 权限列表 on 库.表 to "用户名"@"%" identified by "密码" with grant option;# 删除权限revoke insert,update,select on 库.表 from 'user'@'%';4. 刷新权限flush privileges;5. 删除用户drop user "用户名"@"%"

权限列表

all privileges ，select ，insert ，update，delete，alter，create，drop等。
库.表 ： *.* 代表所有库的所有表权限添加和删除时指定库表的方式需要一样

示例

1. 创建用户mysql>create user  'work'@'%'  identified by '123';2. 添加授权用户work,密码123,对所有库的所有表有所有权限mysql>grant all privileges on *.* to 'work'@'%' identified by '123' with grant option;mysql>flush privileges;3. 添加用户duty，密码123,对books库中所有表有查看，插入权限mysql>grant select,insert on books.* to 'duty'@'%' identified by '123' with grant option;mysql>flush privileges;4. 删除work用户的删除权限mysql>revoke delete on *.* from "work"@"%";5. 删除用户dutydrop user "duty"@"%";

http://www.mrgr.cn/news/58168.html

相关文章：

  • Docker 搭建mysql
  • 【力扣】[Java版] 刷题笔记-21. 合并两个有序链表
  • TinyC编译器6—用 flex 做词法分析
  • 域渗透相关内网 或 域AD环境搭建 域环境收集方式方法命令 查看 检测域控主机或者域内主机是否安装杀软方式
  • 【WebSocket实战】——创建项目初始架构
  • 百钱买百鸡问题
  • 智联引擎是什么？
  • 基于ssm+vue的房源管理系统设计与实现
  • 中国区 Microsoft365主页链接请您参考：
  • 时间数据可视化基础实验（大数据可视化）——Python热狗大胃王比赛前三名分析
  • xss-labs靶场第十二关测试报告
  • 程序员的最终出路在哪
  • ZYNQ AXI_GPIO_INT
  • 使用Python画一个蓝色的动感爱心
  • 升级到Delphi 12，DUnitx 测试用例项目闪退
  • C语言——求解一元二次方程
  • 【付费】Ambari集成Dolphin实战-004-实战bigtop.bom——下
  • 网易博客旧文----BASE64编码解码工具的使用
  • Jenkins + GitLab + Docker实现自动化部署（Java项目）
  • 基于ssm+jsp的宠物常规护理知识管理系统设计与实现（含源码+数据库）
  • Strategy_Mode
  • vue中使用 html2canvas绘制图片并下载
  • 一些关于 WinCC Comfort 和 WinCC Advanced 脚本编程语言 VBS 的实用技巧
  • 《Python游戏编程入门》注-第3章3
  • TinyC编译器6—用 flex 做词法分析
  • JVM学习之路（3）类加载器