MySQL
MySQL基本知识
MySQL 表名/库名 ⇒ 区分大小写
MySQL 关键字 ⇒ 不区分大小写 ⇒ 建议大写
MySQL命令以；结束
本质上库是一个文件夹，表是一个文件
# 库是在下列文件夹下中的一个文件夹，表则是对应文件夹中的.frm文件
cd /var/lib/mysql
ls
aria_log.00000001 aria_log_control hunan ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock performance_schema test1 test2
MySQL服务的管理
查看MySQL的状态
# 查看MySQL状态
# 通过服务查看
systemctl status mariadb # 查看服务
# 通过查看进程
ps aux |grep mariadb
mysql 4124 0.0 3.8 1477024 149104 ? Ssl 07:13 0:19 /usr/sbin/mariadbd
# 通过查看端口
yum install lsof
[root@localhost shell]# lsof -i:3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mariadbd 4475 mysql 19u IPv4 70479 0t0 TCP *:mysql (LISTEN)
mariadbd 4475 mysql 20u IPv6 70480 0t0 TCP *:mysql (LISTEN)
yum install net-tools
netstat -tulnp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 985/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 1162/master
tcp 0 0 0.0.0.0:3366 0.0.0.0:* LISTEN 4124/mariadbd
tcp6 0 0 :::22 :::* LISTEN 985/sshd
tcp6 0 0 ::1:25 :::* LISTEN 1162/master
tcp6 0 0 :::3366 :::* LISTEN 4124/mariadbd
udp 0 0 0.0.0.0:68 0.0.0.0:* 5459/dhclient
udp 0 0 127.0.0.1:323 0.0.0.0:* 692/chronyd
udp6 0 0 ::1:323 :::* 692/chronyd
# 直接登录 下面信息代表服务未启动
mysql -uroot -p12345678
ERROR 2002 (HY000): Can't connect to local server through socket '/var/lib/mysql/mysql.sock' (2)
/mysqldata/mysql.sock
MySQL连接方式
-
套接字连接
使用场景：一台机器之间不同进程之间通信，使用套接字进行连接：/var/lib/mysql/mysql.sock
mysql -uroot -p123456 -S /var/lib/mysql/mysql.sock # 默认没有修改可以不加-S -
网络连接
使用场景：一台机器访问另一台机器上的MySQL服务
mysql -h 192.168.10.137 -P3306 -uroot -p123456
MySQL安装后的一些工具文件
产生的工具/命令
[root@localhost shell]# mysql
mysql mysqlbinlog mysqld_multi mysqldump mysql_fix_extensions mysql_plugin mysql_tzinfo_to_sql
mysqlaccess mysqlcheck mysqld_safe mysql_embedded mysqlimport mysqlshow mysql_upgrade
mysqladmin mysqld mysqld_safe_helper mysql_find_rows mysql_install_db mysqlslap mysql_waitpid
数据文件
数据文件： mysql库表 ： 默认位置：/var/lib/mysql
库 => 文件夹/目录
表 => 文件
mysql表 ：用户和权限、系统配置和日志记录
performance_schema: 用于性能监控和调优（收集和记录了MySQL在运行过程中各种性能信息，SQL查询执行情况，锁，内存，文件i/o...）
information_schema: 数据库对象的信息，库，表，视图...
配置文件
/etc/my.cnf -> !includedir /etc/my.cnf.d
# 服务端配置
[mysqld] => 配置mariadb服务的信息
# 默认字符编码
character-set-server=utf8
# 配置默认存储引擎
default-storage-engine=innodb
# 配置数据文件目录（!!注意 -> 一般建议安装完数据库就修改）
datadir=/data/
# 配置错误日志
log-error=/mysqldata/mariadb.err
# pid文件
pid_file=/data/mariadb.pid
# socket文件
socket=/data/mysql.sock
# 端口
port=3366
# 客户端配置块
[mysql] => 配置客户端信息
socket = /data/mysql.sock # 套接字
# tab 支持tab补齐
auto-rehash
# prompt 配置命令的提示符
prompt = \\u@\\d \\h>
字符集的继承问题
数据库字符集
数据表字符集
字段字符集
库latin1 -> 表latin1 -> 字段 latin1 => 插入数据 （乱码?????）
=> ALTER 库 -> ALTER表 -> ALTER 字段的字符集 （字符集转换有导致数据损坏或乱码的情况）
-> 表手动指定utf8 -> 字段utf8
如果没有数据 -> ALTER 修改字符集
如果有数据 -> 表可以删除（建议删除重建表）
MySQL库操作
MySQL创建库
- 创建时指定字符编码 ⇒ UTF8编码，世界通用编码，不设置的话默认是拉丁字符集（latin1），会出现中文乱码
CREATE DATABASE test DEFAULT CHARACTER SET UTF8;
- 修改已创建表的字符编码
ALTER DATABASE test DEFAULT CHARACTER SET UTF8;
删除数据库
DROP DATABASE test3;
切换数据库
USE test;
查看数据库
SHOW DATABASES;
查看数据库创建
SHOW CREATE DATABASE <库名>;
MySQL表操作
MySQL表基本知识
- 表 ⇒ 二维表、多行、多列
- 数据类型 ⇒ 数据存储在表中有多种数据类型供选择，正确选择数据类型时的好处：当插入数据不符合预定义数据类型时会提示warning信息
- 减少存储空间
- 减少处理数据所需要的资源
- 可以减少无效数据的插入
- 增加可读性
- 数据类型
- 整数类型
- TINYINT:1字节（8位）0~255， -128~127
- SMALLINT:2字节（16位）
- MEDIUMINT:3字节
- INT:4字节
- BIGINT:8字节 2^64 -1
- 浮点类型
- FLOAT:单精度浮点数，较小精度（五位小数） 存储空间小
- DOUBLE:双精度浮点数，高精度，存储空间多
- 字符串类型
- CHAR(N):固定长度字符串（手机号、身份证号……）当长度大于设置时截取前面n个字符
- VARCHAR(N):可变长度字符串（占用存储空间），根据数据长短来确定需要多少存储空间（多了计算步骤）
- TEXT:用于存储大块文本
- TINYTEXT:更少
- LONGTEXT:更多
- 日期时间类型
- DATE:日期类型不包括时间 ⇒ 出生日期
- TIME:只有时间，没有日期 ⇒ 跑步耗时，一节课的时长
- DATETIME:日期时间 ⇒ 精度的时间
- TIMESTAMP:时间戳（数字类型s）
- 枚举类型和集合类型 只能选择枚举中设置好的
-
ENUM:sex ⇒ 男/女
sex ENUM('男', '女') -- 创建一个名为students的表 CREATE TABLE students ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), gender ENUM('Male', 'Female', 'Other') ); -- 向gender列的枚举列表中添加新值'Prefer not to say' ALTER TABLE students MODIFY COLUMN gender ENUM('Male', 'Female', 'Other', 'Prefer not to say'); -- 从gender列的枚举列表中删除'Other' ALTER TABLE students MODIFY COLUMN gender ENUM('Male', 'Female', 'Prefer not to say');
-
- 整数类型
表的操作
- 新建表
CREATE TABLE <表名> (字段名 字段类型 字段属性)
CREATE TABLE user(age INT UNSIGNED);
- 查看表的信息
SHOW CREATE TABLE user;
# 或者
DESC user;
-
表数据的新增
在表名后面填写需要添加的字段名，使用括号和逗号新增多条数据
CREATE INTO user(age) VALUES(12),(13),(14);
- 表数据的查询
SELECT * FROM user;
- 删除表
DROP TABLE user;
- 修改表
-
添加新字段 ⇒ ADD
ALTER TABLE user ADD python VARCHAR(12); # 添加字段可以选择在何处添加，默认不带任何参数添加到末尾 # FRIST 添加到第一行 # AFTER 已存在的字段 => 添加到已存在字段的后面 -
修改字段属性 ⇒ MODIFY
ALTER TABLE user MODIFY passwd VARCHAR(20) NOT NULL; -
修改字段名称和属性 ⇒ CHANGE
ALTER TABLE user CHANGE passwd password VARCHAR(18); -
删除字段 ⇒ DROP COLUMN
ALTER TABLE user DROP COLUMN age; -
修改表名 ⇒ RENAME TO
ALTER TABLE user RENAME TO usertable;
-
字段约束
# 不能为空
NOT NULL;
# 字段值必须唯一
UNIQUE;
# 默认值 =>当插入数据的时候没有给初始值，则使用默认值
DEFALUT;
# 主键 => 非空唯一标识（NOT NULL UNIQUE）
PRIMARY KEY;
# 外键 => 节省空间 数据一致性 数据完整性
FOREGIN KEY;
主键相关知识
主键的作用
- 唯一标识记录
- 数据完整性和准确性（重复/空值都能提醒出来）
- 建立表间关系（外键-主键）
- 提高查询效率
- 主键索引
相关SQL语句
-
设置主键
-
建表后设置主键
ALTER TABLE user ADD CONSTRAINT pk_userno PRIMARY KEY (userno); -
建表时设置主键 ⇒ AUTO_INCREMENT 设置主键自增（适用于主键为number）
CREATE TABLE user( id AUTO_INCREMENT PRIMARY KEY, name VARCHAR(18), phone VARCHAR(17) );
-
-
删除主键
ALTER TABLE user DROP PRIMARY KEY;
外键相关知识
外键的优缺点
- 优点
- 节省空间
- 数据一致性
- 数据完整性
- 缺点
- 外键可能会引入一定的性能开销（内存/CPU）
- 复杂性（进行数据插入和删除时，由于约束可能更麻烦）
- 兼容性问题，不同数据支持不同
- 企业中
- DBA不推荐使用外键，使用代码来解决一致性和完整性问题
创建外键
CREATE TABLE scores( id INT AUTO_INCREMENT PRIMARY KEY, student_id INT, subject VARCHAR(10), score INT ,FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE);
外键删除策略
-
ON DELETE RESTRICT
默认策略。如果子表中数据依赖父表中的记录，那么父表中该条记录将无法删除或者更新
-
ON DELETE CASCADE
级联删除。如果父表中的记录被删除，那么子表中所有引用该条记录的数据将全部被删除
-
ON DELETE SET NULL
如果父表中的记录被删除，子表中对应的字段内容将会被设置为null
-
ON DELETE SET DEFAULT
如果父表中的记录被删除，那么子表中对应的字段内容将会被设置为默认值
-
ON DELETE NO ACTION
与RESTRICT类似
数据库表查询
SELECT 查询顺序
SELECT ... FROM ... GROUP BY ... HAVING ... IRDER BY ...
# SELECT语句执行的时候，每一步都会产生一个虚拟表，虚拟表传入下一步作为下一步的输入
# 小小案例
SELECT DISTINCT play_id, count(*) as num # 顺序5
FROM player LEFT JOIN team ON player.team_id = team.team_id # 顺序1
WHERE height>1.80 # 顺序2
GROUP BY player.team_id # 顺序3
HAVING num >2 # 顺序4
ORDER BY num DESC # 顺序6
LIMIT 2 # 顺序7
WHERE和HAVING的区别
# WHERE 后面只能对表的原始属性做判断
# HAVING 可以对聚合函数后的结果做判断
单表查询
多表查询
数据库常用函数
数据库中有很多的功能函数，运算函数，主要分为字符型的，数值型的和日期型的
学习回顾
MySQL安装、简单管理（停启查看状态）、连接MySQL
MySQL表库管理（查看、创建、修改）
MySQL增删改查语句（单表查询、子查询、多表联合查询）
学习展望
Python ⇒ 操作MySQL
前沿知识：Python基础 ⇒ 语法、基本数据类型、流程控制（if …else/for）
Python操作MySQL
python可以连接数据库对数据库进行执行sql语句（增删改查）操作
数据库索引
索引是一种特殊的数据结构，他就像一本书的目录（字典-目录）
MySQL数据导入和导出
导出：将数据库中的数据导出为文件的形式
导入：将文件中的数据导入到数据库中进行管理
MySQL的日志信息
日志可以提供一些数据，作为监控报警依据
数据库的备份与还原
数据库的视图、触发器、函数、存储过程
视图
视图就是一条select语句执行后返回的结果集。
创建视图时，主要的任务就是这一条SQL查询语句。
视图的作用：
- 将复杂的SQL查询写成视图，方便下次使用（函数复用）
- DBA写好视图给普通开发者用
- 写好视图，将视图授权给用户，达到更细粒度和精确的授权
视图的特性：
- 视图是对若干张表的引用，是一张虚表，不保存具体的数据，只保存查询方法（SQL语句）
- 如果数据表中的数据发生了变化，视图的结果也会一起变化
视图的使用场合：
- 简化查询
- 权限控制
视图的MySQL操作
数据库中的触发器
满足了某些条件，触发了执行另外一件事
当执行insert，delete，update等语句前后执行时会触发
针对每一行执行触发（插入多行值则执行多次）
触发器定义在表上
关于触发器
- 触发器尽量少用，也不推荐使用
- 触发器可能会在设置之后自己忘记，造成后期不正确的使用
- 触发器的执行需要消耗一定的时间，影响执行的速度
触发器的创建
CREATE TRIGGER <触发器名>
trigger_time(BEFORE|AFTER) trigger_event(INSERT|DELETE|UPDATE)
ON table FOR EACH ROW
trigger_body(执行语句)
CREATE TRIGGER trige1 UPDATE
触发器的查看
# 查看所有的触发器
SHOW TRIGGERS\G
# 查看触发器的详情
SHOW CREATE TRIGGER trig2\G
# 从存储信息表里面查看相关信息
SELECT * FROM information_schema.triggers WHERE trigger_name="trig2"\G
触发器的删除
DROP TRIGGER trig2;
数据库中的函数 - 自定义函数
函数：一段可以接受参数并返回值的代码，提高代码的复用性、可读性，使代码更加简洁
自定义函数
DELIMITER || # 改变结束符，区分命令执行结束标志和函数创建体的结束标志
CREATE FUNCTION function_name([参数列表]) RETURNS return_type
BEGIN
-- 函数主体
xxxx;
xxxx;
RETURN value
END ||
DELIMITER ;
案例
DELIMITER ||
CREATE FUNCTION price2(price INT, rate DECIMAL(3,2)) RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
DECLARE result DECIMAL(10,2);
SET result = price*(1-rate);
RETURN result;
END||
DELIMITER ;
DECLARE => 声明一个变量
SET => 设置变量的值
RETURN => 返回变量值
错误信息
ERROR 1805(HY000):Column count of mysql.proc is wrong
Expected 2l,found 20. The table is probably corrupted
错误原因：当时做了数据库的升级Mariadb5.x -> Mariadb10.x
yum install Mariadb-server
mysql_upgrade -uroot -p12345678 # 更新数据结构
函数的使用
SELECT *,prices(price,0.2) FROM product;
MariaDB [test2]> SELECT *,prices(price, 0.2) FROM product;
+-----+-----------------+-------+-------------+--------------------+
| pid | pname | price | category_id | prices(price, 0.2) |
+-----+-----------------+-------+-------------+--------------------+
| 1 | 联想 | 5018 | c001 | 4014.40 |
| 2 | 海尔 | 3002 | c001 | 2401.60 |
| 3 | 雷神 | 5002 | c001 | 4001.60 |
| 4 | 杰克琼斯 | 800 | c002 | 640.00 |
| 5 | 真维斯 | 201 | c002 | 160.80 |
| 6 | 花花公子 | 440 | c002 | 352.00 |
| 7 | 劲霸 | 2003 | c002 | 1602.40 |
| 8 | 香奈儿 | 800 | c003 | 640.00 |
| 9 | 相宜本草 | 200 | c003 | 160.00 |
| 10 | 面霸 | 6 | c003 | 4.80 |
| 11 | 好想你枣 | 57 | c004 | 45.60 |
| 12 | 香飘飘奶茶 | 1 | c005 | 0.80 |
| 13 | 海澜之家 | 1 | c002 | 0.80 |
| 14 | test | 1 | c006 | 0.80 |
| 100 | 海南之家 | 1000 | c001 | 800.00 |
| 200 | 中国宝贝 | 10000 | c002 | 8000.00 |
| 300 | 海南之家 | 1000 | c001 | 800.00 |
| 400 | 中国宝贝 | 10000 | c002 | 8000.00 |
+-----+-----------------+-------+-------------+--------------------+
18 rows in set (0.022 sec)
函数的查看
# 查看数据库中有哪些函数
SHOW FUNCTION status\G
# 查看自定义函数的创建
SHOW CREATE FUNCTION prices;
函数的删除
DROP FUNCTION prices;
数据库的存储过程
存储过程：是一组为了完成特定功能的SQL语句集合，她经过编译后存储在数据库中。
存储过程可以接受参数，执行复杂的查询和数据库操作
语法格式
DELIMTER ||
CREATE PROCEDURE procedure_name([参数列表])
BEGIN
--存储过程
END ||
DELIMITER;
# 简单示例
CREATE PROCEDURE get_all_products()
BEGIN
SELECT * FROM product;
END ||
Query OK, 0 rows affected (0.002 sec)
# 使用存储过程
CALL get_all_products();
示例： 指定输入和获取存储过程输出
- 书写一个存储过程查找特定id的产品 - 指定输入
DELIMITER ||
CREATE PROCEDURE get_product_by_id(IN product_id INT)
BEGIN
SELECT * FROM product WHERE pid=product_id;
END ||
DELIMITER ;
# 使用
CALL get_product_by_id(1); # 查看id为1的产品
MariaDB [test2]> CALL get_product_by_id(1);
+-----+--------+-------+-------------+
| pid | pname | price | category_id |
+-----+--------+-------+-------------+
| 1 | 联想 | 5018 | c001 |
+-----+--------+-------+-------------+
1 row in set (0.001 sec)
- 创建一个存储过程，获取产品的最高价格 - 没有输入，获取存储过程的输出
# 创建存储过程
DELIMITER ||
CREATE PROCEDURE get_max_price(OUT max_price INT)
BEGIN
SELECT MAX(price) INTO max_price FROM product;
END ||
DELIMITER ;
# 使用存储过程
# 1.定义变量用来接受结果
SET @max_price_value = 0;
# 2.运行存储过程
CALL get_max_price(@max_price_value);
# 3.查看结果
SELECT @max_price_value;
查看存储过程、删除和修改
# 查看有哪些存储过程
SHOW PROCEDURE status\G
# 查看创建存储过程
SHOW CREATE PROCEDURE get_max_price;
# 删除存储过程
DROP PROCEDURE IF EXIST get_max_price;
MySQL的事务
事务：是一种机制，一个事务包含了一组数据库操作命令，所有的命令是一个整体，要么全部执行，要么全部不执行
MySQL常见问题解决
MySQL存储引擎
在 MySQL 中，存储引擎是数据库管理系统用于存储、索引和处理数据的组件。不同的存储引擎具有不同的特点和适用场景，用户可以根据具体的需求选择合适的存储引擎。
show engines;
# 查看数据库支持的存储引擎
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |
| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.000 sec)
最常用的两个存储引擎：
- InnoDB ->默认存储引擎
- MyISAM
cd /var/lib/mysql/test_engine
[root@localhost test_engine]# ll
总用量 80
-rw-rw---- 1 mysql mysql 67 3月 1 15:57 db.opt
-rw-rw---- 1 mysql mysql 433 3月 1 16:02 test2.frm
-rw-rw---- 1 mysql mysql 0 3月 1 16:02 test2.MYD
-rw-rw---- 1 mysql mysql 1024 3月 1 16:02 test2.MYI
-rw-rw---- 1 mysql mysql 925 3月 1 15:57 test.frm
-rw-rw---- 1 mysql mysql 65536 3月 1 15:57 test.ibd
1. InnoDB
- 特点
- 事务支持：InnoDB 是 MySQL 5.5 版本之后的默认存储引擎，它支持事务处理，遵循 ACID（原子性、一致性、隔离性、持久性）原则。这使得它非常适合处理需要保证数据完整性和一致性的业务场景，如金融交易系统。
- 外键约束：支持外键约束，能确保数据之间的引用完整性。例如，在订单表和客户表之间建立外键关系，当删除客户记录时，相关的订单记录可以根据外键约束进行相应的处理。
- 行级锁：采用行级锁机制，在并发访问时，只锁定需要操作的行，而不是整个表，从而提高了并发性能。
- 聚簇索引：使用聚簇索引存储数据，将数据和索引存储在一起，提高了数据的检索效率。
- 适用场景：适用于对数据完整性要求较高、需要处理大量并发事务的场景，如在线交易系统、企业资源规划（ERP）系统等。
2. MyISAM
- 特点
- 不支持事务：MyISAM 不支持事务处理，因此在数据一致性方面不如 InnoDB。
- 表级锁：采用表级锁机制，在进行写操作时会锁定整个表，并发性能较差。
- 全文索引：支持全文索引，适合用于全文搜索场景。
- 数据压缩：可以对数据进行压缩，减少磁盘空间的占用。
- 适用场景：适用于对查询性能要求较高、对事务处理和数据完整性要求较低的场景，如日志记录系统、静态数据存储等。
知识点：存储引擎的概念、存储引擎的类别、默认存储引擎的配置文件修改(/etc/my.cnf,d/server.cnf - default-storage-engine=myisam)、表的存储引擎的修改（alter）
MySQL 锁
为什么要有锁？解决事务并发时资源争抢问题
客户端连接到数据库 → 发送SQL语句，每一条SQL语句都可以理解成一个单独的事务（默认事务是自动提交的） COMMIT。当多个事务同时执行时（同时执行多个SQL语句，也叫事务的并发）
MySQL锁总体结构
从锁粒度划分
- 全局锁：锁定数据库中所有表，加上全局锁，数据库只能读，不允许写（数据备份）
- 表级锁：锁定整张表
- 表锁（共享读锁、独占写锁）
- 元数据锁（理解为表结构，锁定表结构。ALTER）
- 意向锁（提升检查效率）
- 行级锁：锁定指定行数据1，2，5，6，20
- 记录锁：只锁定指定的记录（解决脏读-不能deletee和update）
- 间隙锁：锁定记录的间隙（解决幻读）
- 临键锁：间隙锁的升级（记录+间隙）
这些锁都是数据库自己调整的，满足指定的条件，触发对应锁
从互斥性的角度划分
- 共享锁（S锁share）：不同事务之间不会相互排斥，可以同时获取锁（读锁）
- 排他锁（X锁）：不同事务之间会相互排斥，不可以同时获取锁（写锁）
- 共享排他锁（SX锁）：MySQL5.7之后引入的锁
从加锁方式划分
- 显示锁：编写SQL语句的时候，手动指定加锁
- 隐式锁：执行SQL语句的时候，数据库根据隔离级别自动为我们的SQL加锁
从思想的维度划分
- 乐观锁：每次执行SQL语句前都认为自己会成功，先尝试执行，失败后再获取锁
- 悲观锁：默认。每次执行前，都认为自己会失败，先获取锁然后再执行。
共享锁（S锁）
对一行数据加上共享锁后，两个事务可读不可写。
当一个事务对一行数据进行更新操作时，自动加入了锁，只能读不能写
# 显式加锁
select ... lock in share mode;
select ... for share;
排他锁
一个事务获取排他锁，当前事务可读写，其他事务不可写
# 获取排他锁
# 显式
select ... for update
# 隐式
update product set price=price+1 where id=1
全局锁
加锁后数据库处于只读状态（数据库备份）
# 加锁
flush tables with read lock;
# 释放锁
unlock talbes;
表锁
给table加上读锁或者写锁、
（共享）读锁：事务A加读锁，事务A可读不可写，事务B同理
（独占）写锁：事务A加写锁，事务A可读可写，事务B不可读不可写
# 加锁
lock tables tablename read/write；
# 释放锁
unlock tablesname
# 查看表锁
show open tables where in_use>0
元数据锁MDL锁（Meta Data Lock）
锁定表的结构，系统自动控制，访问/操作一张表的时候自动加上
MDL读锁：当对表增删改查，系统自动加MDL读锁
MDL写锁：ALTER TABLE时，会自动加MDL写锁
意向锁
当加表级锁的时候，可以通过意向锁减少表锁的检查
页面锁
页面锁是Berkeley DB存储引擎支持的一种锁粒度
乐观锁（无锁）
通过代码逻辑控制的一种锁，通常通过版本号或i时间戳机制来实现。更新时会检查当前版本号和之前取得版本号是否一致，如果一致，更新数据，版本号+1；如果不一致，更新失败
行锁（行级别）
开销较大，加 慢。粒度小，并发度高，锁冲突概率最小。 InnoDB存储引擎支持
分类：共享锁、排他锁
行锁分了三类：
行锁：锁定单个行记录，防止其他事务对数据进行update操作和delete操作（RC/RR隔离级别支持）
间隙锁：锁定索引记录间隙（不包含记录），防止其他事务在这个间隙进行insert操作，产生幻读（RR隔离级别）
临键锁：行锁+间隙锁，通常左开右闭（RR隔离级别）
最终加锁得时候，加的是哪一种取决于数据库
事务并发可能存在的问题
-
死锁
情况：两个不同的事务同时需要获取共同的资源无法获取
解决方法：
- 设计流程，先拿到条件1，在拿条件2
- 设计一个单独的进程检查死锁情况，通过设计一个算法，杀死其中一个进程释放所需资源
-
活锁
情况：在竞争资源的时候，一直得不到资源，一直等待
解决方法：
设置超时时间
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
