php网站开发小程序,到哪里找人做网站,学校网站的建立,电商网站建设培训班MySQL 在我们日常技术中是一个广泛使用的开源关系型数据库管理系统#xff0c;所以作为测试同学#xff0c;掌握mysql的相关知识是必不可少的技能之一#xff0c;所以小编从软件测试的角色出发#xff0c;来整理一些跟测试相关的知识#xff0c;希望能够帮助到大家。
一、…MySQL 在我们日常技术中是一个广泛使用的开源关系型数据库管理系统所以作为测试同学掌握mysql的相关知识是必不可少的技能之一所以小编从软件测试的角色出发来整理一些跟测试相关的知识希望能够帮助到大家。
一、基础操作
1、库的常见操作方式DDL
DDL(Data Definition Language) 数据定义语言用来定义数据库对象数据库表列等
1、mysql [-h服务器地址] [-Pmysql端口][-u用户名] [-p密码]---mysql在服务器的链接方式
egmysql -h192.168.193.32 -P3306 -uwork -p12345;
2、create database my_db character set utf8;--- v忽视那个几十年一个人支持中文数据的数据库
3、show databases;---查看所有数据库
4、use 库名;----选择需要连接的数据库
5、alter database 数据库名 modify name新的数据库名;--修改数据库名
6、drop database 库名--删除数据库
7、mysqldump -u root -p db1 /tmp/db1.sql;---将db1库备份到db1.sql文件中
8、mysql -u root -p db2 /tmp/db1.sql;--导入备份的文件到db2库中2、表的常见操作方式DDL
MYSQL 数据类型
数值
tinyint : 小整数型占一个字节范围-128127
int 大整数类型占四个字节范围eg age int
bigint大整数类型占八个字节 eg id bigint
double 浮点类型双精度16位精度8字节使用格式 字段名 double(总长度,小数点后保留的位数)eg score double(5,2)
float浮点类型单精度8位精度4字节eg score float(6,3) 日期
date 日期值占3字节只包含年月日2024-12-01eg birthday date
datetime 混合日期和时间值。包含年月日时分秒2024-12-01 22:06:44
time时间值只包括时分秒。12:25:36
timestamp自动存储记录修改时间字符串
char 定长字符串最多255个字符优点存储性能高缺点浪费空间eg name char(10) 如果存储的数据字符个数不足10个也会占10个的空间
varchar 变长字符串最多65535字符存储实际字符数1字节优点节约空间比text查询速度快缺点存储性能底eg name varchar(10) 如果存储的数据字符个数不足10个那就数据字符个数是几就占几个的空间
text可变长度最多65535字符大小写不敏感存储实际字符数2字节,常用于存储长篇文章日志文件html代码等大量文本数据egcontent text not null;MYSQL表基本语句
1、create table class(sno int(11) primary key auto_increment,name varchar(32) not null,score float,phone int,addr varchar(32));--创建一个数据表,其中sno为主键并且自增name不为空
2、show tables--查看当前库下的所有表
3、desc class;----查询表中字段的类型设置
4、show create table class\G;---查看当前表格的字段设置
5、alter table class modify column addr int comment 详细地址;----修改列的备注信息
6、alter table class change name s_name varchar(30);---修改表中name字段为s_name字段
7、alter table class add age int;---在表中添加一列age字段
8、alter table class drop age;--删除某列字段
9、alter table class modify addr varchar(32) after name;--调整列的顺序,将addr字段调整到name字段之后如果使用first 则表示调整该字段到表的第一列
10、show procedure status where Dbdatabase()\G-----查找当前表的所有存储过程
11、show create procedure p_test\G ----查找指定的存储过程
12、drop table class--删除表
3、数据的常见操作方式DQL/DML
DQL(Data Query Language) 数据查询语言用来查询数据库中表的记录(数据) DML(Data Manipulation Language) 数据操作语言用来对数据库中表的数据进行增删改
1、insert into class name,score,phone,addrvalues(test,89.9,19900001111,北京市朝阳区新城小区)--在表中插入一条语句
2、select distinct 列名 [as 别名] [,...n]|*|表达式-from 表名[,...n]-[where 条件表达式]-[order by 列名] desc(倒排) asc(默认正)-[group by 列名]-[having 条件表达式] ----查询语句
3、update 表名 set 列名值 [where 条件表达式];---根据where条件修改数据内容如果不加where直接修改全部信息
4、select top 3 * from class或者 select * from class limit 13 ---限制查询前三条
5、相关聚合函数avg(expr) count(expr) sum(expr) max(expr) min(expr)
select 学生号 from 学生表 group by 学生号 having count(课程名)3 ---查询选修了3门以上课程的学生学生号
select count(name),sex form 学生表 group by sex---统计男女生各多少名
select name from 学生表 group by name having count(name)1---统计重名的名字
6、select * from user where name like %四% or name like %五%---模糊匹配%在哪就是哪匹配
7、select * from user where name like ‘张__’--严格匹配,_占一个字符
8、select * from user where name regexp 四|五--正则进行匹配
9、select * from class where score between 60 and 80--查询范围 在(60,80] 的成绩信息
10、select * from exam where english math ;--查询英语成绩大雨数据成绩的信息多列比较
11、select * from class where age in (19,20,22);--查询年龄等于19或者20或者22的同学离散查询
12、delete from class where age12;--删除表里特定数据
13、select * from class limit 5 offset 2;--分页查询,从第3条开始查询,展示5条数据
14、update exam set mathmath5 order by englishmath limit 3;--将成绩表中的数据成绩统一都加5然后根据英语和数据成绩的总和进行排序并返回前三名二、建表的三大范式
1、第一范式1NF
描述 数据表中的每一列每个字段必须是不可分割的最小单元也就是确保每一列的原子性。 举例 address中字段的值中国陕西省西安市临潼区 可拆分为国家、省、市、区四个字段 作用 第一范式设计的越详细对后期迭代更有利 缺点 如果需求需要返回整体的详细信息需要按照业务需求进行各个字段的拼接操作
2、第二范式2NF
描述 在满足第一范式的前提下除了主键以外的其他列必须完全依赖于主键。如果有一个列只依赖于主键的一部分那么它不满足第二范式。 举例 满足第二范式表一【产品id产品名】表二【客户id客户名】表三【订单id产品id客户id】 不满足第二范式表一学生成绩表【学号、姓名、课程、学分、成绩】–姓名跟课程没有关系学分跟学号没有关系。 不满足第二范式会带来的问题 1.数据冗余 学生姓名、课程、学分都重复出现造成了大量的数据冗余。 2.更新异常 如果需要调整JAVA的学分那么就需要更新所有记录关于JAVA的记录如果一旦某些记录更新成功某些更新失败就会造成数据中某一课程学分不一致的情况表现为数据不一致 3.插入异常 如果想要新增一门课程需要为每个同学都插入一条成绩为空的数据这样没有任何意义 4.删除异常 学生毕业之后把毕业的同学考试记录全部都删除之后删除记录的同时也可能把课程对应的学分全部删除导致一段时间内数据库中没有课程和学分的相关信息。
3、第三范式3NF
描述 在满足第二范式的前提下除了主键以外的其他列必须直接依赖于主键不能存在传递依赖。即非主键列必须直接依赖于主键不能依赖于其他非主键列。 举例 满足第三范式 不满足第三范式表一【订单id、产品id、客户id、客户电话】 在这个表中订单id跟客户id具有强关系客户id跟客户电话强相关 订单id- 客户id- 客户电话 这样的一个传递关系称为传递依赖这种设置不满足第三范式因为存在传递依赖。 解决方案 将传递的信息单独拆出一个独立的表 需要将客户电话单独拆出一个表表一【订单id、产品id、客户id】 表二【客户id、客户电话】 作用 第三范式可以解决数据冗余更新异常插入异常删除异常的问题。
第三范式在我们日常开发和测试中经常容易被忽视下面一个就是我们业务中不满足第三范式的数据表。
三、索引
描述 索引就是一种将数据库中的数据按照特殊形式存储的数据结构。 索引是一个排好序的列表在这个列表中存储着索引的值和包含这个值的数据 所在行的物理地址。 为什么索引查询快 这是因为使用索引后可以不用扫描全表来定位某行的数据 而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据 索引的优缺点
1、常见的索引
主键索引
是一种特殊的唯一索引一个表只能有一个主键 不允许有空值。一般是在建表的时候同时创建主键索引
# 在创建表的时候直接在字段名后指定 primary key
create table class(id int primary key, name varchar(30));# 在创建表的最后指定某列或某几列为主键索引
create table class(id int, name varchar(30), primary key(id));# 创建表以后再添加主键
create table class(id int, name varchar(30));
alter table class add primary key(id);
主键索引的特点 1、一个表中最多有一个主键索引当然可以使复合主键 2、主键索引的效率高主键不可重复 3、创建主键索引的列它的值不能为null且不能重复 4、主键索引的列基本上是int
唯一索引
索引列的值必须唯一但允许有空值
# 在创建表的时候直接在字段名后指定 unique
create table class(id int primary key, name varchar(30) unique);# 在创建表的最后在表的后面指定某列或某几列为unique
create table class(id int primary key, name varchar(30), unique(name));# 创建表以后再添加主键
create table class(id int, name varchar(30));
alter table class add unique(name);普通索引
是最基本的索引它没有任何限制
create table class(id int primary key,name varchar(20);index(name) --在表的定义最后指定某列为索引
);create table class(id int primary key, name varchar(20););
--创建完表以后指定某列为普通索引
alter table class add index(name); create table class(id int primary key, name varchar(20);
-- 创建一个索引名为 idx_name 的索引
create index idx_name on class(name);普通索引的特点 一个表中可以有多个普通索引普通索引在实际开发中用的比较多 如果某列需要创建索引但是该列有重复的值那么我们就应该使用普通索引
组合索引
多个字段上创建的索引 创建create index 索引名 on 表名(字段1字段2) 或 alter table 表名 add index 索引名(字段1字段2)
create table class(id int, name varchar(20); );
CREATE INDEX idx_name_date ON class (id, name);全文索引
主要用来查找文本中的关键字而不是直与索引中的值相比较。fulltext索引配合match against操作使用而不是一般的where语句加like
CREATE FULLTEXT INDEX content ON articles (content);2、索引查询方式
查看表的索引方式
show index from 表名
查看sql是否用到索引
explain select * from 表名
其中type字段中常见的几种情况 systemconsteq_refrefrangeindexall越大性能越好实际业务中system和const很难实现一般能实现到rep和rangesystem 只有一条数据的系统表或衍生只有一条数据的主查询 const explain select * from order where order_id 1 仅仅能查询到一条数据的sql用于Primary key 和unique索引 eq_ref 唯一性索引对于每个索引键的查询返回匹配有且有一条数据 ref explain select * from order where deleted_at ‘’ 非唯一性索引对于每个索引键的查询返回匹配0或多行数据 Range 检索指定范围的行where后面是一个范围查询 如between in 有时会失效从而为all explain select * from order where deleted_at ‘2022-04-14’ explain select * from order where deleted_at in (‘2022-03-01’,‘2021-04’) index 查询全部索引中的数据 explain select deleted_at from order all 查询全部表的中数据
3、删除索引
# 删除主键索引
alter table 表名 drop primary key;# 其他索引的删除
alter table 表名 drop index 索引名;
索引名就是show keysfrom 表名中的 Key_name 字段# 第三种方法方法
drop index 索引名 on 表名4、 避免索引失效的原则
1.复合索引不要跨列或无序使用
2.不要在索引上进行任何操作计算函数否则索引失效如where num*3 15
3.复合索引不能使用,,is null否则自身及右侧索引全部失效
4.like尽量以常量开头不要以%开头否则索引失效;eg:explain select id,name,from class where name like % 初级%;
5.尽量不要使用类型转换显示、隐式否则索引失效如where name 123name字段为varchar类型程序底层将123 123即进行了数据转换
6.尽量不要使用or否则索引失效但是使用or并不是一定会使索引失效你需要看or左右两边的查询列是否命中相同的索引。 where user_id 1 or user_id 2 where user_id 1 or age 2
7.In可能会使索引失效; eg: explain select id,name from class where name in(英语数学);
8.不使用Select * ;eg: explain select * from class;
9. 字符串不加单引号egexplain select id,name from class where name123;四、事务
1、什么是事务
一个最小的不可再分的单元可以理解为一个事务对应的是一组完整的业务并且在这个事务中所作的一切操作要么全部成功要么全部失败只要有一个操作没成功整个事务都将回滚到事务开始前。
2、事务的特征ACID
原子性
每一个事务都是一个不可再分的工作单位事务中包括的操作要么都做要么 都不做。不会结束在中间的某个环节如果中间有错误会被回滚到事务开始前的状态
一致性
对于数据的操作从一个一致的状态转变成另一个一致转态。
隔离性
指一个事务的执行不能被其他事务干扰即一个事务内部的操作对并发的其他事务是具有隔离的并发执行的各个事务之间不能互相干扰。 在使用并发事务时常见的问题有哪些
1、脏读事务A读取了事务B更新的数据然后B回滚操作那么A读取到的数据是脏数据
2、不可重复读事务 A 多次读取同一数据事务 B 在事务A多次读取的过程中对数据作了更新并提交导致事务A多次读取同一数据时结果 不一致。
3、幻读一个事物内多次执行同一条sql返回的结果集不一样。 比如一条select语句执行两次第二次返回了第一次查询不存在的数据。
4、丢失修改Lost to modify: 指在一个事务读取一个数据时另外一个事务也访问了该数据那么在第一个事务中修改了这个数据后第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失因此称为丢失修改。 例如事务 1 读取某表中的数据 A20事务 2 也读取 A20事务 1 修改 AA-1事务 2 也修改 AA-1最终结果 A19事务 1 的修改被丢失。注意不可重复读的和幻读很容易混淆不可重复读侧重于修改幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行解决幻读需要间隙锁MVCC事务隔离分为不同级别包括读未提交读提交可重复读和串行化
持久性
一个事务一旦提交他对数据库中的数据的改变就应该是永久性的。提交后的其他操作或故障不会对其具有任何影响。
3、事务的控制语句
BEGIN 或 START TRANSACTION# 显式地开启一个事务。COMMIT 或 COMMITWORK# 提交事务并使已对数据库进行的所有修改变为永久性的。ROLLBACK 或 ROLLBACK WORK# 回滚会结束用户的事务并撤销正在进行的所有未提交的修改。SAVEPOINT S1 使用SAVEPOINT #允许在事务中创建一个回滚点一个事务中可以有多个SAVEPOINT“S1”代表回滚点名称。ROLLBACK TO [SAVEPOINT] S1# 把事务回滚到标记点。
使用set设置控制事务 set AUTOCOMMIT0; #禁止自动提交仅针对当前会话
set AUTOCOMMIT1; #开启自动提交仅针对当前会话Mysql默认为1
set global AUTOCOMMIT0; #禁止自动提交针对全局事务
set global AUTOCOMMIT1; #开启自动提交针对全局事务Mysql默认为1
show variables like AUTOCOMMIT; #查看当前会话的AUTOCOMMIT值
show global variables like AUTOCOMMIT; #查看全局事务的AUTOCOMMIT值 - 如果没有开启自动提交当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback; 或 commit;当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。 - 如果开起了自动提交mysql 会把每个sql 语句当成一个事务然后自动的commit。 - 当然无论开启与否begin; commit | rollback; 都是独立的事务。
五、锁机制
锁是计算机中多个进程或者线程并发访问某个资源的一种机制对于数据来说如何保证数据并发访问的一致性有效性是所有数据库必须解决的一个问题锁冲突也是影响数据库并发访问性能的一个重要因素。锁按照级别分为共享锁S锁和排他锁X锁
1、共享锁S锁共享锁是指多个事务可以同时申请加锁且不会因为其他事务的共享锁而被阻塞。在共享锁存在的情况下其他事务可以读取数据但不能修改数据。当事务需要修改数据时必须等待其他事务释放共享锁或者升级为排他锁。共享锁用于保证并发读取数据的一致性提高系统读取吞吐量。
2、排他锁X锁排他锁是指只有一个事务可以申请加锁其他事务必须等待该事务释放锁后才能访问数据。在排他锁存在的情况下其他事务既不能读取数据也不能修改数据。排他锁用于保证数据的完整性和可靠性防止多个事务同时修改同一数据而导致数据不一致。按照锁的粒度分为全局锁表级锁行级锁。
1、全局锁
全局锁就是锁定数据库中的所有表其使用场景是做全库的逻辑备份需要对所有表进行锁定从而保证数据的完整性避免在备份过程中其他相关联的表有数据上增删改操作导致不同关联表之间的数据不一致。 加上全局锁后数据库处于只读状态整个数据库只能进行DQL语句不能进行DDL和DML操作。
操作方式
flush tables with read lock---加全局锁
mysqldump -uroot –p1234 class class.sql---数据备份
unlock tables;--释放锁2、表级锁
表级锁每次操作锁住当前整张表表锁通常用于对表进行DDL操作或者备份等需要操作整张表的情况。
操作方式
show open tables--查询表的加锁情况
lock tables 表名 read/write ---表加锁
unlock tables --释放锁表锁类型的举例说明
3、行级锁
行级锁是mysql中锁粒度最细的一种锁表示只对当前操作的行进行加锁常应用于事务中 优点能大大减少数据库操作的冲突 缺点加锁粒度小开销大 对于行级锁住要分为行锁、间隙锁、临键锁三种形式。 注意事项
1、如果sql中没有用到索引或者索引失效时则行锁会转换为表锁
2、commint和rollback相当于解锁
3、行级的一种特殊情况间隙锁egwhere id3 and id10 ,但是如果表中没有id7的数据系统会自动给id7的数据加上间隙锁
4、普通的select语句不会加锁但是如果select ......in share mode【共享锁】select ......for update【排他锁】操作方式
select * from class where id1 for update;--会对id1的数据行加上X锁排他锁阻止其他事务对数据行进行读取或者写入操作
select * from class where id1 lock share mode;--给id1的数据行加上S锁共享锁允许其他事务并发的读取相同的数据但是阻止其他事务对数据进行写操作保证读取数据不会被修改
在执行update和delete等修改数据操作时mysql会自动为设计的数据行加上X锁
show status like %innodb_row_lock%;--查看行锁的加锁情况innodb_row_lock_current_waits(当前正在等待锁的数量)innodb_row_lock_time系统启动至今一共等待的时间innodb_row_lock_time_avg(从系统启动至今平均等待的时间)innodb_row_lock_time_max从系统启动至今最大一次等待的时间innodb_row_lock_waites从系统启动至今平均等待次数事务与锁的结合应用
begin;
select * from test where id 1 for update;
commit;
select * from test;begin;
update test set money 0 where id 1;
select * from test;
commit;对应不同颗粒的锁对应的优缺点
六、慢sql分析
在我们日常测试中慢sql也是影响我们系统性能的因素之一所以作为测试我们也是需要了解慢sql的相关知识mysql提供一种日志查询用于记录mysql中响应时间超过阀值的sql语句
1、慢查询分析步骤
1、开启慢查询日志观察哪些sql比较慢
通过show variables like %query%查看query相关的配置long_query_time: 用于设置慢查询记录的阈值单位秒默认值为10。show_query_log: 是否启用慢查询日志记录默认为OFF。slow_query_log_file: 配置慢查询日志信息记录路径。1、开启慢查询日志 通过show variables like %query%查看query相关的配置
long_query_time: 用于设置慢查询记录的阈值单位秒默认值为10。
show_query_log: 是否启用慢查询日志记录默认为OFF。
slow_query_log_file: 配置慢查询日志信息记录路径。1、慢查询日志的开启与关闭查看是否开启show variables like %slow_query_log% slow_query_log_file为日志存放的路径临时开启set golbal slow_query_log 1; 1on, 0off重启服务后失效 service mysql restart永久开启/etc/my.cnf中追加配置vi /etc/my.cnf[mysql]slow_query_log1slow_query_log_file路径
2、阀值的查看与设置查看阀值查看是否开启show variables like %long_query_time%临时设置阀值set golbal long_query_time 5; 重新登录后失效 exit永久开启/etc/my.cnf中追加配置vi /etc/my.cnf[mysql] long_query_time 5
3、慢查询sql查看查看慢查询sql 的条数show global status like %slow_query%具体查询那条慢查询sql日志中查询mysqldumpslow工具查看
2、Explain和慢查询SQL分析
explain select * from class where id1explain分析方式可以参考索引部分内容讲解。
3、使用show processlis 更近一步分析SQL的执行细节 show processlist;–通过该命令可以实时查看sql的执行时长 sql的具体响应时间 profiles 记录每条sql 的实际响应时间 set profiling on 设置打开默认为关闭 show profiles 查看每条sql 的具体详情 show profile all for query 对应的id 查看该sql 的所有硬件花费时间 show profile cpu,block io for query 对应的id 查看该sql 的cpu和io花费时
2、导致慢sql的原因
1SQL没有加索引或索引失效 2limit分页太深 3join、子查询、in太多 4查询了很多无用的字段 5查询使用了临时表 6锁竞争问题
3、SQL优化方案
1、减少IO次数 IO经常是数据库最容易产生瓶颈的地方所以减少IO次数就能有明显效果具体操作就是无用的字段不需要返回出来减少使用select *的查询方式使用什么字段就查询什么字段 只要通过“show profile for query 具体id ” 查询出来对应sending data 字段耗时最长那就是IO问题
2、降低CPU计算 除了IO外SQL优化中需要考虑的就是CPU计算优化比如常见的order bygroup bydistinct 等操作都是十分占用CPU的所以我们尽量在写sql语句的时候避免使用这些操作如果必须要使用到尽量减少数据量。
那么以上就是mysql数据库的相关知识。
