28： mysql优化

数据库

事务控制

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

作用: 一件事从开始发生到结束的过程,确保数据操作过程中的数据完整和使用安全

事务操作

开启事务

begin;

开始执行事务中的若干条SQL命令（增删改）
终止事务，若begin之后使用commit提交事务或者使用rollback进行事务回滚。

commit; # 事务中SQL命令都执行成功,提交到数据库,结束!
rollback; # 有SQL命令执行失败,回滚到初始状态,结束!

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

事务四大特性

原子性（atomicity）

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

一致性（consistency）

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

隔离性（isolation）

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

持久性（durability）

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

事务隔离级别

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

读未提交：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. 执行写操作多的表用 InnoDB

CREATE 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如果不设置主键也会自己设置隐含的主键，所以最好自己设置
尽量设置占用空间小的字段为主键
建立外键会自动建立索引，在表关联查询时建议使用外键子段作为关联条件
外键虽然可以保持数据完整性，但是会降低数据导入和操作效率，增加维护成本

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。

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类型字段放到另一个表
水平拆分：减少每个表的数据量，通过关键字进行划分然后拆成多个表

数据库安全和管理

表的复制

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

create table 表名 select 查询命令;

create table student select name,age,score from class where score>80

数据库备份

备份命令格式

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

mysqldump -u root -p stu > stu.sql

恢复命令格式

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

mysql -u root -p student < stu.sql

MySQL远程连接

cd /etc/mysql/mysql.conf.d
sudo vi mysqld.cnf 找到43行左右,加 # 注释

# bind-address = 127.0.0.1

保存退出
sudo service mysql restart
进入mysql修改用户表host值

use mysql;
update user set host='%' where user='root';

刷新权限

flush privileges;

添加用户和授权

用root用户登录mysql

mysql -u root -p

添加用户%表示不限制该用户访问IP

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

权限管理

# 增加权限
grant 权限列表 on 库.表 to "用户名"@"%" identified by "密码" with grant option;

# 删除权限
revoke insert,update,select on 库.表 from 'user'@'%';

刷新权限

flush privileges;

删除用户

drop user "用户名"@"%"

--创建用户
create user  'work'@'%'  identified by '123';

--添加授权用户work,密码123,对所有库的所有表有所有权限
grant all privileges on *.* to 'work'@'%' identified by '123' with grant option;
flush privileges;

--添加用户duty，密码123,对books库中所有表有查看，插入权限
grant select,insert on books.* to 'duty'@'%' identified by '123' with grant option;
flush privileges;

--删除work用户的删除权限
revoke delete on *.* from "work"@"%";

--删除用户duty
drop user "duty"@"%";

pymysql模块

pymysql是一个第三方库，如果自己的计算机上没有可以在终端使用命令进行安装。

sudo pip3 install pymysql

pymysql使用流程

建立数据库连接：db = pymysql.connect(...)
创建游标对象：cur = db.cursor()
游标方法: cur.execute("insert ....")
提交到数据库或者获取数据 : db.commit() / cur.fetchall()
关闭游标对象：cur.close()
断开数据库连接：db.close()

常用函数

db = pymysql.connect(参数列表)
功能: 链接数据库

host ：主机地址,本地 localhost
port ：端口号,默认3306
user ：用户名
password ：密码
database ：库
charset ：编码方式,推荐使用 utf8

cur = db.cursor() 
功能： 创建游标
返回值：返回游标对象,用于执行具体SQL命令

cur.execute(sql,args_list) 
功能： 执行SQL命令
参数： sql sql语句
      args_list  列表，用于给sql语句传递参量

cur.executemany(sql命令,args_list)
功能： 多次执行SQL命令，执行次数由列表中元组数量决定
参数： sql sql语句
      args_list  列表中包含元组 每个元组用于给sql语句传递参量，一般用于写操作。

db.commit() 提交到数据库执行，必须支持事务操作才有效
db.rollback() 回到原来的数据形态，必须支持事务操作才有效
cur.fetchone() 获取查询结果集的第一条数据，查找到返回一个元组否则返回None
cur.fetchmany(n) 获取前n条查找到的记录，返回结果为元组嵌套元组， ((记录1),(记录2))，查询不到内容返回空元组。
cur.fetchall() 获取所有查找到的记录，返回结果形式同上。
cur.close() 关闭游标对象
db.close() 关闭数据库连接

"""
数据库操作基本结构示例
"""
import pymysql

kwargs = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "passwd": "123456",
    "database": "stu",
    "charset": "utf8"
}
# 链接数据库
db = pymysql.connect(**kwargs)

# 生成游标  游标：调用sql操作数据得到操作结果的对象
cur = db.cursor()

# 操作数据 读 / 写


# 关闭
cur.close()
db.close()

"""
数据库读操作示例
"""
import pymysql

kwargs = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "passwd": "123456",
    "database": "stu",
    "charset": "utf8"
}
# 链接数据库
db = pymysql.connect(**kwargs)

# 生成游标  游标：调用sql操作数据得到操作结果的对象
cur = db.cursor()

# 操作数据 读
# sql = "select name,age,score from class where score>80;"
# cur.execute(sql) # 执行语句

# 给sql语句传值 使用第二个参数按照位置给sql语句%s传值
sql = "select name,age,score from class " \
      "where score>%s and sex=%s;"
cur.execute(sql,[80,'m'])

# 一个查询结果
one = cur.fetchone()
print(one)

# 多个查询结果
many = cur.fetchmany(2)
print(many)

# 所有查询结果
all = cur.fetchall()
print(all)


# 迭代获取结果
# for row in cur:
#     print(row)

# 关闭
cur.close()
db.close()

手机上阅读

最后一次更新于2022-12-14 13:59

神出鬼没的Enderman

long long ago,I was born.

数据库

事务控制

事务操作

事务四大特性

事务隔离级别

数据库优化

数据库设计范式

MySQL存储引擎

字段数据类型和键的选择

explain语句

SQL优化

表的拆分

数据库安全和管理

表的复制

数据库备份

MySQL远程连接

添加用户和授权

pymysql模块

27：视图

29：网络通信基础概念

28： mysql优化

数据库

事务控制

事务操作

事务四大特性

事务隔离级别

数据库优化

数据库设计范式

MySQL存储引擎

字段数据类型和键的选择

explain语句

SQL优化

表的拆分

数据库安全和管理

表的复制

数据库备份

MySQL远程连接

添加用户和授权

pymysql模块

27： 视图

29： 网络通信基础概念

27：视图

29：网络通信基础概念