数据库笔记

 

1.数据库的介绍

安装在操作系统之上的数据管理软件，存储和管理数据

1.1数据库的分类

  • 关系型数据库（SQL）

    MySQL、Oracle、SQL Server、DB2、SQLite

    通过表和表之间的、行和列之间的关系进行数据的存储

  • 非关系型数据库（Not Only SQL）

    Redis、MongoDB

    以对象方式存储，通过对象自身的属性来决定，如定位等需要动态更新的信息

1.2数据库管理系统（DBMS）

科学有效的管理数据、维护和获取数据；应用通过数据库管理系统操作数据库中的数据，如mysql

DDL ->数据库定义语言

DML ->数据库操作语言

DQL ->数据库查询语言

DCL ->数据库控制语言

2.数据库的使用

操作数据库->操作数据库中的表->操作数据库中表的数据 mysql中的关键字不区分大小写

2.1操作数据库(DDL)

1.创建数据库

CREATE DATABASE IF NOT EXISTS TEST;

2.删除数据库

DROP DATABASE IF EXISTS TEST;

3.使用数据库

USE `CRITICALWORD`;  --如果标名或字段名为关键词需要用``包住

4.查看数据库

SHOW DATABASES;  --查看所有的数据库
SHOW CREATE DATABASE campus  --查看创建数据库的语句
SELECT DATABASE();  -- 查看当前数据库

2.2数据库的数据类型

1.数值

  • tinyint 十分小的数据 1个字节
  • smallint 较小的数据 2个字节
  • mediumint 3个字节
  • int 标准的省属 4个字节（常用）
  • bigint 较大的数据 8个字节
  • float(t,d) 浮点数 4个字节 t表示总的位数，d表示小数位数
  • double(t,d) 浮点数 8个字节 t表示总的位数，d表示小数位数
  • decimal 字符串形式的浮点数 金融计算的时候

2.字符串

  • char 字符串固定的大小 0~255
  • varchar 可变字符串 0~65535 常用的String
  • tinytext 卫星文本 2^8-1
  • text 文本串 2^16-1 保存大文本

3.时间日期

  • date YYYY-MM-DD 日期格式
  • time HH:MM:ss 时间格式
  • datetime YYYY-MM-DD HH:MM:SS 最常用的时间格式
  • timestamp时间戳， 1970.1.1到现在的毫秒数
  • year年份表示

4.null

  • 没有值、未知
  • 不要使用null进行运算

2.3数据库的字段属性

1.unsigned

  • 无符号整数，声明该列不能为负数

2.zerofill

  • 不足的位数用0填充

3.自增（autoimplement）

  • 自动在上一条记录的基础上加一，通常用来设计唯一的主键index，必须为整数

4.约束

约束 说明 关键字
非空约束 非空 not null
唯一约束 不能有重复元素 unique
主键约束 主键 primary key
默认约束 默认值 default
检查约束（mysql8.0.16之后） 保证字段值满足某个条件 check
外键约束 外键 foreign key 
--check的使用	
age check(age > 0 && age < 120)

2.4操作表-创建

/*创建一个school数据库
--创建学生表（列、字段）使用SQL创建
--学号int、登录密码varchar（20）姓名varchar（20）性别varchar（2），出生日期（datetime）、家庭住址、email

--注意使用英文()、表的名称、字段尽量用``括起来
--AUTO INCREMENT 自增
--字符串用''括起来，所有语句后面加',',最后一个不加*/

CREATE TABLE IF NOT EXISTS `student` (
		`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
		`name` VARCHAR(30) NOT NULL DEFAULT('匿名') COMMENT '姓名',
		`password` VARCHAR(20) NOT NULL DEFAULT'123456' COMMENT '密码',
		`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
		`birthday` DATE DEFAULT NULL COMMENT '出生日期',
		`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
		`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
		PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
/*格式
CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名` 列属性 [属性] [属性] [属性] [注释]，
	`字段名` 列属性 [属性] [属性] [属性] [注释]，
	···
	`字段名` 列属性 [属性] [属性] [属性] [注释]
)[表类型][字符集设置][注释]

*/

/*每个表都必须存在以下五个字段，表示一个记录存在的意义
ID  --主键
`VERSION`  --乐观锁
id_delete  --伪删除
gmt_create  --记录的创建时间
gmt_update  --修改时间*/

2.5操作表-查询

SHOW TABLES;
SHOW CREATE TABLE student  --查看创建表的语句
DESC student  --显示表的结构

2.6数据库引擎

  • InnoDB: mysql默认使用、 安全性高，事务的处理，多表多用户操作
  • MyISAM: 早些年使用的、 节约空间，速度较快
MyISAM InnoDB
事务支持 不支持 支持
数据行锁定 不支持（表锁） 支持（行锁）
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大，约为MyISAM的两倍

物理空间存在的位置

所有的数据库文件都存在data目录下，本质还是文件的存储，有特定的格式，一个文件夹对应一个数据库

MySQL引擎在物理文件上的区别

  • InnoDB在数据库表中只有*.frm文件，以及上级目录下的ib_data文件
  • MYISAM对应的文件
    • *.frm--表结构的定义文件
    • *.MYD--数据文件
    • *.MYI--索引文件

设置数据库的字符集编码

MySQL默认编码为Latin1，不支持中文

CHARTSET = utf8

2.7操作表-修改、删除

--ALTER TABLE [表名] RENAME AS [新表名]
ALTER TABLE teacher RENAME TO teacher1  --修改表名

--ALTER TABLE [表名] ADD [字段名] [列属性]
ALTER TABLE teacher1 ADD age INT(11)  --添加表的字段

--ALTER TABLE [表名] MODIFY [属性名] [新的属性约束]
ALTER TABLE teacher1 MODIFY age VARCHAR(11)  --修改约束

--ALTER TABLE [表名] CHANGE [旧属性名] [新属性名] [新属性的约束修改]
ALTER TABLE teacher1 CHANGE age age1  --表中属性重命名

--ALTER TABLE [表名] DROP[删除的属性名]
ALTER TABLE teacher1 DROP age1  --删除表中的属性

--DROP TABLE [表名] IF EXISTS [表名]
DROP TABLE teacher1 IF EXISTS teacher1  --删除表

--TRUNCATE TABLE [表名]

3.数据管理(DML)

3.1外键

阿里的JAVA规范：【强制】不得使用外键与级联，一切外键概念必须在应用层解决

创建表的时候没有外键关系

ALTER TABLE `[表名]`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)

--ALTER TABLE [表名] ADD CONSTRAINT `FK_[需要添加外键的属性名]` FOREIGN KEY(`[需要添加外键的属性名]`) REFERENCES `[作为约束添加的所在表名]`(`[作为约束添加的所在属性]`)

最佳实践：

  • 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
  • 若要使用外键需要在程序层面实现，否则做DELETE或UPDATE都必须考虑外键因素，开发、测试极不方便

对于删除和更新

行为 说明
no action 删除外键时，检查主表是否有外键，如果有则不允许删除/更新
restrict 删除外键时，检查主表是否有外键，如果有则不允许删除/更新
cascade 进行级联删除/更新
set null 在父表删除/更新时，检查是否有外键，如果有将子表中的值设置为null
set default 在父表删除/更新时，检查是否有外键，如果有将子表中的值设置为default 
foreign key(` `) references ``(``) on delete set null

3.2添加

  • INSERT
INSERT INTO [表名]([字段1,字段2,字段3]) VALUES('值1','值2','值3')  --多字段对多个值
INSERT INTO [表名]([字段]) VALUES('值1'),('值2')  --一个字段插入多个值

字段是可以省略的，但是后面的值必须要一一对应，不能少；可以同时插入多条数据

3.3修改

  • UPDATE
--UPDATE [表名] SET [属性1] = '值1',[属性2] = '值2' WHERE [其他属性] = 值3;
UPDATE `student` SET `name` = 'ygw' WHERE `id` = '1';  --如果不指定条件会改变所有的内容
操作符 意义 范围 影响
<>或!= 不等于 5<>6 true
BETWEEN...AND... 在什么之间 [2,5]
AND && 1>2 AND 2>1 false
OR || 1>2 AND 2>1 true

3.4删除

都能删除数据，都不会删除数据结构

  • DELETE

不会影响自增，删除后认为原来的自增数

重启数据库后的现象：

  • InnoDB使用DELETE后，自增列会从1开始（存在内存当中断电即消失）
  • MyISAM继续从上一个自增量开始（存在文件中的，不会丢失）
--DELETE FROM [表名] WHERE [属性];
DELETE FROM `student` WHERE id = 1;
  • TRUNCATE

完全清空一个数据库表，表的结构和索引约束不会变，

--TRUNCATE [表名];
TRUNCATE `student`;

TRUNCATE重新设置自增列，计数器会归零、TRUNCATE不会影响事务

4.数据查询（DQL）

4.1简介

  • 所有的查询操作都用它
  • 简单、复杂的查询
  • 数据库中最核心的语言、最重要
  • 使用频率最高的语言

4.2基本查询

1.基本语句

语法

-- 注意：起别名时要注意语句的执行顺序，不能乱取

select [distinct]
[字段列表]		--执行顺序4
from
[表名列表]		--执行顺序1
where
[分组前条件列表] --执行顺序2
group by
[分组字段列表]   --执行顺序3
having
[分组后条件列表] --执行顺序3
order by
[排序字段列表]   --执行顺序5
limit
[分页参数]		--执行顺序6

-- 选中并起别名
SELECT `studentno` AS 学号, `studentname` AS 学生姓名 FROM student

-- 函数Concat(a, b, ...)拼接
SELECT CONCAT('姓名：', studentname) AS 新名字 FROM student

2.不常见的功能

去除SELECT查询出来的结果中重复出现的数据

#SELECT `studentno` FROM result  -- 查询有哪些同学参加了考试

SELECT DISTINCT `studentno` FROM  result  -- 去重

表达式

SELECT VERSION();
SELECT 100 * 3 -1 AS 计算结果;  --select可以选中计算表达式
SELECT @@AUTO_INCREMENT_INCREMENT; -- 查询自增结果

-- 学院考试成绩+1查看

SELECT `studentno`, `studentresult` + 1	AS 加分后 FROM student;

3.约束列表

WHERE [约束条件]

运算符 语法 描述
IS NULL A + ~ 如果为null结果为真
IS NOT NULL A + ~
BETWEEN...AND... A BETWEEN B AND C A如果在B, C之间结果为真
LIKE A LIKE B SQL匹配，如果A匹配B，结果为真
IN A IN (A1, A2, A3, ...) 假设A在A1，A2，...其中的某个值，结果为真
and/or/not &&/||/!
</<=/>/>=/=
<>/!= 不等于

4.排序查询orderby

select [] from [] order by [字段1] [排序方式1], [字段2] [排序方式2]...

5.分组查询与聚类函数

select from [] where [] group by [] having []
-- group by 属性名
-- having、where的对比
/*
1.执行时机不同，where作用于分组前，筛选可以参加分组的数据，having作用于分组后，对分组后的结果进行筛选
2.where子句中不可以使用聚合函数，而having中可以使用聚合函数进行判断
eg. select workaddress, cnt(*) from emp where age < 45 group by workaddress having cnt(*) > 3
exp.选择年龄小于45岁的员工，并依照工作地址进行分组，选择工作人数大于三的数据
*/

6.模糊查询like

-- 查询姓刘的同学
SELECT `studentno`, `studentname` FROM `student`
WHERE studentname LIKE '刘%'

SELECT `studentno`, `studentname` FROM `student`
WHERE studentname LIKE '刘_'

-- 查询名字中间有佳字的同学
SELECT `studentno`, `studentname` FROM `student`
WHERE studentname LIKE '%佳%'

-- % / _  仅在LIKE中使用
-- %代表0~任意个字符
-- _代表一个字符


--    查询学号为1001、1002、1003的同学
SELECT `studentno` FROM `student`
WHERE studentno IN(1001, 1002, 1003)

7.分页查询limit

select [] from [] limit 起始索引, 查询记录数;

-- 起始索引的计算：(页码 - 1)*每页返回记录数

-- eg. 查询性别为男，且年龄在20-40岁(含)之间的前五个员工信息，对查询的结果按年龄升序进行排序，年龄相同按入职时间升序排序
select * from emp where sex = '男' and (age between 20 and 40) order by age asc, workdate asc limit 5;

8.联表查询join

多表关系

  • 一对多：需要建立外键，少的那一方为多的一方的外键
  • 多对多：需要建立第三张表，为这两个表建立外键
  • 一对一：用于单表拆分，在任意一方加入外键，关联另一方的主键，并将外键设置为unique

连接分类

  • 内连接：查询两张表的交集部分，做笛卡尔积
-- 隐式内连接

select [字段列表] from [表1],[表2] where 连接条件

-- 显式内连接

select [字段列表] from [表1] [inner] join 表2 on 连接条件

  • 外连接

    • 左外连接：查询左表中的所有数据和两个表的交集，即使左表当中有null值
    select [字段列表] from [表1] left [outer] join [表2] on [条件]
    • 右外连接：查询右表中的所有数据，和两个表的交集

  • 自连接：当前表与自身的连接，自连接必须要用表别名

-- 查询员工及其所属领导的名字

select * from emp a, emp b where a.managerid = b.id

-- 如果没有领导也要查出来

select a.name '员工', b.name '领导' from emp a left join emp b where a.manager = b.id


-- INNER JOIN
SELECT studentno, studentname, subjectno, studentresult
FROM student AS s
INNER JOIN result AS r
WHERE S.studentno = r.studentno
-- RIGHT JOIN
SELECT studentno, studentname, subjectno, studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno
-- LEFT JOIN
SELECT studentno, studentname, subjectno, studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno

-- JOIN [连接的表] ON [条件] 连接查询
-- WHERE  等值查询    二者结果相同
操作 描述
INNER JOIN 如果表中至少有一个匹配就返回行
LEFT JOIN 以左表为基准进行查询，包含了左表的所有需要合并的列数据，即使右表没有匹配
RIGHT JOIN 以右表为基准进行查询，包含了右表的所有需要合并的列数据

9.联合查询union

把多次的查询结果合并起来，形成一个新的查询结果集，列数和字段类型要保持一致

select [字段列表] from [表A]
union [all]  -- union all直接合并，union去重
select [字段列表] from [表B]

10.子查询

  • 标量子查询(查询结果为单个值)

  • 列子查询(子查询结果为一列)

    常用的操作符：in、not in、any、some、all
    any -子查询返回的列表中有一个满足即可
    all -必须满足子查询返回的所有值
    some -与any使用一样

-- eg.查询比财务部所有人工资都高的个人信息

select id from dept where name = '财务部';

select salary from emp where dept_id = (select id from dept where name = '财务部');

select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name = '财务部'));

  • 行子查询(子查询结果为一行)

    常用操作符：in / not in

select managerid, salary from emp where name = '张三';

select * from emp where (managerid, salary) = (select managerid, salary from emp where name = '张三');

  • 表子查询(子查询结果为多行多列)

    可以作为一个结果集，也可以作为一个表与其他表进行联表查询

-- 查询与张三和李四工资和职位相同的员工

select salary, job from emp where name = '张三' or '李四';

select * from emp where (salary, job) in (select salary, job from emp where name = '张三' or '李四');

5.数据控制(DCL)

用来管理数据库用户，控制数据库访问权限

1.查询用户

select * from user;

2.创建用户

create user '用户名'@'主机名' identified by '密码';  -- 主机名如果为%说明可被任意主机访问

3.修改用户密码

alter user '用户名'@'主机名' identified with mysql_native_passward by '新密码';

4.删除用户

drop user '用户名'@'主机名';

5.权限控制

  • 查询权限
show grants for '用户名'@'主机名';
  • 授予权限
grant [权限列表] on [数据库名].[表名] to '用户名'@'主机名';
  • 撤销权限
revoke [权限列表] on [数据库名].[表名] from '用户名'@'主机名';

-- 权限列表比较复杂，此处不做细说，可以参考连接http://c.biancheng.net/view/7249.html

6.SQL总结

1.基本语法

---------------------对表属性的约束------------------------
--其中可放置多个主键
primary key(A1, A2, A3...)
--内部键的约束，直接在属性后引用
`id` int(9) not null primary key,
`cpid` int(9) not null references(`id`)
--外键引用，A1是需要加外键的属性
foreign key(A1) references `student`(`id`)
------------------------关键词总结及例句----------------------------
escape  --
order by  -- 用于句末
desc / asc  -- 与order by 一起使用
-- eg.select * from student order by score asc
--select... from...order by [属性名] asc 
limit  -- SELECT * FROM 表名 limit m,n (意思是从m的下一条开始查n条记录)
between and  -- [属性] between [2] and [3]  即2<=[属性]<=3
union  --
intersect  --
except  --
----------------------------聚集函数---------------------------------
-- 所有的null值不参与聚合函数的计算
select cnt([字段名]) from student where ...
avg、min、max、sum、count
some  --
every  --
------------------------------集合-----------------------------------
in / not in  --
exists / not exists  --
------------------------------子句-----------------------------------
select from [] where [] group by [] having []
-- group by 属性名
-- having、where的对比
/*
1.执行时机不同，where作用于分组前，筛选可以参加分组的数据，having作用于分组后，对分组后的结果进行筛选
2.where子句中不可以使用聚合函数，而having中可以使用聚合函数进行判断
eg. select workaddress, cnt(*) from emp where age < 45 group by workaddress having cnt(*) > 3
exp.选择年龄小于45岁的员工，并依照工作地址进行分组，选择工作人数大于三的数据
*/
lateral  --
with  --
case  --
    when [] then []
    when [] then []
    ...
    else []
end

2.中级SQL

1.连接

2.视图

MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图时动态生成的。

  • 创建视图
CREATE VIEW [视图名] AS [SELECT语句];
  • 查看视图的字段信息
DESC 视图名;
  • 修改视图
UPDATE [视图名] SET [修改的属性] WHERE id=1;
  • 删除视图
DROP VIEW IF EXISTS [视图名];

3.完整性约束

4.授权

3.高级SQL

1.函数和过程

-- 声明变量
declare ans int;

-- 过程的定义  过程没有返回值
create procedure [name](in [参数名] [参数类型]，参数2....)
begin
	[函数体]
end;

-- 过程的调用
call procedure(参数...);

-- 函数的定义  函数有返回值
create function [name](参数1，参数2....) returns [返回类型]
begin
[函数体]
end

-- 调用
select [name](参数1，参数2....)


-- if
if [条件] then 
	[执行1]
else 
	[执行2]
end if;

-- while
while i < 5 do
[执行]
set i=i+1
end while;

-- repeat 相当于do...while
repeat
[执行语句]
until [退出条件]
end repeat;

-- loop 死循环,循环名用于leave和iterate
[循环名]:loop 
[执行语句] -- 无leave语句则一直执行
end loop[循环名]

-- leave 相当于beak，退出循环语句
-- iterate 相当于continue

-- case
case [表达式]
when [value1] then [执行语句1];
when [value2] then [执行语句2];
...
end(case); -- 如果是嵌套在其他的begin和end中间则需要指明关闭的对象

--

2.触发器

触发器是与表有关的数据库对象，可以在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句。触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作 。

-- 模板
DELIMITER $
CREATE TRIGGER 触发器名称
BEFORE|AFTER  INSERT|UPDATE|DELETE
ON 表名
[FOR EACH ROW]  -- 行级触发器
BEGIN
  触发器要执行的功能;
END
DELIMITER ;

-- 日志触发器举例

-- 创建日志表
CREATE TABLE student_log(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 日志id
    operation VARCHAR(20),        -- 操作类型 (insert update delete)
    operation_time DATETIME,      -- 操作时间
    operation_id INT,          -- 操作表的id
    operation_params VARCHAR(200)     -- 操作参数
)engine=innodb default charset=utf8;


drop trigger student_log_insert;

-- 创建触发器
create trigger student_log_insert
after insert on student
for each row
begin
insert into student_log values (
    null,'insert', now(),NEW.Sno,concat('插入后{ id=',NEW.Sno,' name=',NEW.Sname,' dept=',NEW.Sdept,'}')
);
end;
delimiter ;

4.常用函数

1.字符串函数

函数 功能
concat(str1,str2,...) 将字符串str1，str2...进行拼接
lower(str) 将str全部转为小写
upper(str) 大写
lpad(str, n, pad) 左填充，使达到n个字符
rpad(str, n, pad) 右填充
trim(str) 去掉字符串头部和尾部的空格
substring(str, start,len) 返回字符串str从start位置起的len个长度的字符串

2.数值函数

函数 功能
ceil(x) 向上取整
floor(x) 向下取整
mod(x,y) 返回x/y的模
rand() 返回0~1内的随机数
round(x,y) 对x四舍五入保留y位小数

3.日期函数

函数 功能
curdate()/curtime 获取当前的日期/时间
now() 返回当前的日期和时间
year(date)/month(date)/day(date) 获取date的year/month/day
date_add(date, interval expr type) 返回在date后加上时间间隔expr后的时间值(type为年/月/日)
datediff(date1,date2) 返回起始日期date1和date2之间的天数

4.流程控制函数

函数 功能
if(value, t, f) value为true返回t否则返回f
ifnull(value1,value2) value不为空返回1否则2
case when [val1] then [res1].... else [default] end va1为true返回res1
case [expr] when [val1] then[res1] ...else [default] end switch，expr为属性名

7.事务

要么都成功，要么都失败！

1.ACID原则（数据库事务正常执行的四个原则）

  • 原子性(Atomicity)：是指一个事务要么全部执行，要么不执行，也就是说一个事务不可能只执行了一半就停止了。比如你从取款机取钱，这个事务可以分成两个步骤：1划卡，2出钱。不可能划了卡，而钱却没出来。这两步必须同时完成，要么就不完成。
  • 一致性(Consistency)：是指事务的运行并不改变数据库中数据的一致性。例如，完整性约束了a+b=10，一个事务改变了a，那么b也应该随之改变。
  • 隔离性(Isolation）：事务的独立性也称作独立性，是指两个以上的事务不会出现交错执行的状态。因为这样可能会导致数据不一致，更加具体的来讲，就是事务之间的操作是独立的。
  • 持久性(Durability）：事务的持久性是指事务执行成功以后，该事务对数据库所作的更改便是持久的保存在数据库之中，不会无缘无故的回滚

SQL中事务的相关操作：

-- 查看/设置事务提交方式
select @@autocommit;
set @@autocommit = 0;

-- 开启事务
start transaction; / begin;

-- 提交事务
commit;

-- 回滚事务
rollback;

如果在运行发生错误的情况下不进行回滚，数据库中的数据会随着程序的运行而更新，回滚则提供了在程序出错的情况下保持原数据不变

2.并发事务问题

问题 描述
脏读(两个事务间) 一个事务读到另一个事务还没有提交的数据
不可重复读(一个事务) 一个事务中先后读取同一条记录，但两次读取的数据不同
幻读 一个事务按照条件查询时没有对应的数据行，但在插入数据时数据又存在了,例：在预防不可重复读时隔离了两个客户端，使得数据不能及时更新

3.事务的隔离级别

隔离级别(从上到下依次增加) 脏读 不可重复度 幻读
read uncommitted √ √ √
read committed(oracle默认) √ √
repeatable read(mysql默认) √
serializable(串行化) 
-- 查看/设置事务的隔离级别

select @@transaction_isolation;

set [session | global] transaction isolation level [read uncommitted | read committed | repeatable read | serializable];

8.锁

为什么需要锁？

利用锁来解决并发过程中数据出现的各种问题，简单的来说，就是要用锁来控制对相同数据的操作顺序，使得数据正常、合理的进行改变；当然，如果对数据安全性要求极高，可以使用简单的串行处理来进行，但是效率极低，极易容易阻塞，因此就要求锁来实现，提高并发度，增加数据的安全性、可靠性。

在事务中进行加锁，增加并发度，事务结束锁释放。

1.全局锁

锁定数据库中的所有表。加锁后整个实例就处于只读状态，后序的DML、DDL都将被阻塞；典型的应用场景是做全库的逻辑备份，对全局的表进行锁定，从而获取一致性视图，保证数据的完整性。

-- 通过全局锁实现数据库的备份操作
flush tables with read lock;

-- 在cmd中写此句
mysqldump -uroot -p123456 [数据库名] > [数据库备份地址]test.sql;

-- 另一种在不加锁的情况下实现数据备份 --single-transaction
-- mysqldump --sigle-transaction -uroot -p123456 [数据库名] > [数据库备份地址]test.sql;

-- 关闭锁
unlock tables;

2.表级锁

每次锁住整张表。

1.表锁

  • 表共享读锁(read lock)：当前客户端和其他客户端都可执行DQL操作，都不可执行DML/DDL

  • 表独占写锁(write lock)：当前客户端可以执行DQL、DML和DDL，其他客户端不可执行

读锁不会阻塞其他客户端的读，会阻塞其他客客户端的写和当前客户端的写；写锁阻塞其他客户端的读和写

-- 加锁语法
lock table [表名] read / write;

-- 释放锁
unlock tables /客户端释放连接

2.元数据锁MDL

在数据进行改变时，不能改变表的结构；在改变表的数据结构时，不能改变数据。为了避免DML和DDL冲突，保证读写的正确性。不需要显示定义，系统自动控制。

image-20220522113303223

3.意向锁

解决行锁与表锁的冲突问题，使用意向锁减少对行锁的检查，意向锁中记录了行锁的信息；如果意向锁与表锁不兼容(同时存在)，则不能加入表锁，将对表锁操作进行阻塞，等待行锁结束

  • 意向共享锁(IS)：与行锁共享锁(read)兼容，与行锁排它锁(write)互斥。
  • 意向排它锁(IX)：与共享锁和排它锁都互斥。意向锁之间不会互斥。

3.行级锁

每次锁住对应的行数据。锁定粒度小，发生锁冲突的概率最低，并发度最高。应用在InnoDB引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录的加锁。

如果更新的数据查询条件没有建立索引，那么InnoDB会对表中的所有数据进行加锁，即行锁升级为了表锁。比如说name默认是未建立索引的，那么以name为查询条件进行更新、删除时，就会锁住整张表。

1.行锁

锁定单个记录的锁，防止其他事物对此进行update和delete，存在于包括主键在内的唯一索引中。在RC、RR级别下都支持。

  • 共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁(当前行数据在读，不允许被改变，但可以与其他事物共享读取)
  • 排它锁(X)：允许获取排它锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排它锁。
-- insert、update、delete都是自动加的排它锁

-- select ... lock in share mode 加的是共享锁

-- select ... for update 加的是排它锁

2.间隙锁

锁定索引记录的间隙，确保间隙不变，防止其他事物进行插入操作，可以防止幻读现象，存在于非唯一索引中，锁定开区间的一段间隔，基于临键锁实现。在RR隔离级别下支持。

间隙锁的唯一目的是防止其他事务 插入间隙。间隙锁可以共存，一个事务的间隙锁不会阻止另一个事务使用间隙锁。

3.临键锁

行锁和间隙锁的组合，同时锁住数据，并锁住数据之前的间隙，存在于非唯一索引中，该类型的每条记录的索引上都存在这种锁，它是一种特殊的间隙锁，锁定一段左开右闭的索引区间。在RR隔离级别下支持。

image-20220522152300587

小结：

索引上的等值查询(唯一索引)，给不存在的记录加锁时，优化为间隙锁

索引上的等值查询(普通索引)，会给当前数据加上临键锁(前一个间隙和当前数据)和间隙锁(后一个间隙)

索引上的范围查询(唯一索引)，会访问到不满足条件的第一个值为止(先对两端加锁，再对中间的内容加锁)

9.索引

基本语法

-- 创建索引
create index idx_user_name on tb_user(name);

create unique index idx_user_phone on tb_user(phone);

create index idx_user_pro_age_sta on tb_user(profession, age, status);

-- 删除索引
drop index [索引名] on [表名];

-- 查看索引
show index from [表名];

执行频次查询

-- 七个下划线
show global status like 'Com_______';

慢查询日志

<!--在mysql配置文件(/etc/my.cnf)中配置-->

<!--慢查询开关-->

slow_query_log=1

<!--界定慢查询的标准-->

long_query_time=2

profile

-- show profiles 可以帮助我们在优化时了解事件都消耗在哪里
select @@have_profiling;  -- 查看当前数据库是否支持

set profiling = 1;  -- 开关，默认关闭

explain 查看复杂查询的执行计划

  • id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，从上到下执行，否则id越大越先执行）
  • type: 表示多表查询的连接类型，性能由好到差为：null、system、const（根据主键进行查询）、eq_ref、ref（非唯一性的索引）、range、index、all
  • possible key：可能用到的索引
  • key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，越小越好
  • rows：mysql认为必须要执行查询的行数，InnoDB中是一个与估值
  • filtered：返回结果的行数占读取行数的比例，越大越好

索引的使用

10.优化（即一些好习惯）

1.插入数据的优化

  • 批量插入
insert into values(1,'TOM'),(2,'Cat');
  • 手动提交事务
start transaction;
insert into values(1,'TOM'),(2,'Cat');
insert into values(3,'TOM'),(5,'Cat');
insert into values(4,'TOM'),(6,'Cat');
commmit;
  • 主键顺序输入
  • 使用load指令加载数据
-- 客户端连接服务端时，加上参数 --loacl-infile
mysql --local-infile -uroot -p

-- 设置全局参数local_infile为1
set local_infile = 1;

-- 将准备好的数据加载到数据库；字段间以,分割；不同行数据以换行符分割
load data local infile '/root/sql.log' into table `tb_user` fields terminated by ',' lines terminated by '\n';

2.主键优化

主键的设计原则：

  • 在满足业务需求的情况下，应尽量降低主键的长度
  • 插入数据时，应尽量选择顺序插入，选择使用auto_increment自增主键
  • 尽量不要使用uuid做主键或者是其他自然主键，如身份证号。
  • 在业务操作时，避免对主键的修改

3.order by 、group by优化

using filesort < using index、using temporary < using index

  • filesort是不使用索引，先将数据加载到缓冲区，在缓冲区排好序返回排序结果

  • index使用索引进行排序，效率高

优化的话就是尽量为排序条件建立索引(单列/联合索引)

默认的联合索引中的属性都是按照升序建立索引的，如果出现查找按一个升序一个降序的情况，可以重新建立索引

create index ind_age_name_ad on table tb_user(age asc, name desc);

总结：

  • 二者根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
  • 应尽量使用覆盖索引，减少回表查询
  • 注意多字段排序时出现升序和降序同时出现的情况
  • 如果不可避免的出现filesort，大数据量排序时，可以适当增大缓冲区的大小sort_buffer_size(默认256K)

4.limit优化

limit m, n -> 从m开始往后的n调记录，limit的执行效率随m的增大而降低

可以使用覆盖查询，查到id；再根据id联表查询数据

-- eg.
select s.* from tb_sku s, (select id from tb_sku order by id limit 9000000, 10) a where s.id = a.id;

5.count优化

如果考虑优化的话需要自己去计数，下面给出几种用法的对比：

count(字段)需要从表中获取数据并且需要判断是否为空

count(主键))需要从表中获取数据

count(1)和count(*)不用从表中获取数据

count(字段) < count(主键id) < count(1) ≈ count(* ),所以尽量使用count(* )

6.update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则行锁升级为表锁。

在进行update时，注意where的查询条件是有索引的，否则行锁会升级为表锁

posted @   CDUT的一只小菜鸡  阅读(83)  评论(0)  编辑  收藏  举报
相关博文：
· JDBC简单复习
· 基于redis的查询业务缓存实现
· MySQL基础笔记
· 10记录数据库基础知识
· 4. 事务和锁
阅读排行：
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
点击右上角即可分享
微信分享提示