MySQl基础总结
一、SQL基础:
-
什么是SQL
- Structured Query Language：结构化查询语言
- 其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”。
-
SQL通用语法
- SQL 语句可以单行或多行书写，以分号结尾。
- 可使用空格和缩进来增强语句的可读性。
- MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
- 数据库的注释：
- 单行注释：-- 注释内容 #注释内容(mysql特有)
- 多行注释：/* 注释内容 */
-
SQL分类
- DDL(Data Definition Language)数据定义语言
- 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
- DML(Data Manipulation Language)数据操作语言
- 用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
- DQL(Data Query Language)数据查询语言
- 用来查询数据库中表的记录(数据)。关键字：select, where 等
- DCL(Data Control Language)数据控制语言(了解)
- 用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等
- DDL(Data Definition Language)数据定义语言
二、DDL
-- 查询所有数据库
SHOW DATABASES;
-- 查看某个数据库的创建格式
SHOW CREATE DATABASE 数据库名;
-- 创建数据库 第一种如果该库已存在,会报错1007
CREATE DATABASE 数据库名;
CREATE DATABASE IF EXISTS 数据库名;
-- 创建数据库并指定字符集UTF_8/GBK等
CREATE DATABASE 数据库名 CHARACTER SET UTF-8;
-- 查看数据库的字符集
SHOW CREATE DATABASE 数据库名;
-- 修改数据库的字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集名称;
-- 删除数据库
DROP DATABASE IF EXISTS 数据库名;
-- 使用数据库
USE 数据库名;
-- 查询该库下所有表
SHOW TABLES;
-- 查询表结构
DESC 表名;
-- 查询表字符集
SHOW TABLE STATUS FROM 库名 LIKE '表名';
-- 查询建表语句
SHOW CREATE TABLE 表名;
-- 创建数据表,注意:最后一列不需要加逗号
CREATE TABLE 表名(列名1 数据类型1,
....
列名n 数据类型n
);
数据类型:
1. int：整数类型
* age int
2. double:小数类型
* score double(5,2) -- 总长度为5,其中有两位小数(不区分正负数)
* price double
3. date:日期，只包含年月日 yyyy-MM-dd
4. datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
5. timestamp:时间戳类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
* 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
6. varchar：字符串
* name varchar(20):姓名最大20个字符
* zhangsan 8个字符 张三 2个字符
注意:与char相比,varchar长度可变,但如果已知长度,可以使用char,效率略高.
-- 修改表名
ALTER TABLE 原表名 RENAME TO 新表名;
-- 修改表的字符集
ALTER TABLE 表名 CHARACTER SET 字符集名称;
-- 添加一列,最后可追加AFTER ID;表示新列插在ID列后
ALTER TABLE 表名 ADD 列名 数据类型;
-- 修改列数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;
-- 修改列名和数据类型 标准语法
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
## 注意: 修改类型必须兼容,例如 可以把int 修改为varchar ,但是如果把varchar 修改为int 则底层会进行强制转换,只要有一个值转换失败则报错,修改失败
-- 删除列
ALTER TABLE 表名 DROP 列名;
-- 删除数据表
DROP TABLE IF EXISTS 表名;
三、DML
-- 添加数据
-- 向表中添加数据
INSERT INTO 表名(列名1,列名2,...) VALUES (值1,值2,...);
INSERT INTO 表名 VALUES (值1,值2,值3,...);
INSERT INTO 表名(列名1,列名2,...) VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);
-- 修改数据
UPDATE 表名 SET 列名1 = 值1,列名2 = 值2,... [where 条件];
## 注意:修改语句中必须加条件;如果不加条件，则将所有数据都修改.
-- 删除数据
DELETE FROM 表名 [WHERE 条件];
## 注意:删除语句中必须加条件;如果不加条件，则将所有数据删除
-- 清空数据, 不可恢复,删除表再创建表,速度较快
truncate tabel 表名;
四、DQL
-- 查询语法:
select
字段列表 -- 如列名或者 *
from
表名列表 -- 表名
where
条件列表 -- 判断条件
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
-- 查询
-- 查看表中全部数据,*代表所有列
SELECT * FROM 表名;
-- 多个字段查询
SELECT 列名1,列名2,... FROM 表名;
-- 去除重复查询,,只有全部重复的才可以去除
SELECT DISTINCT 列名1,列名2,... FROM 表名;
-- 计算列的值(四则运算),如果某一列为null，可以进行替换
SELECT 列名1 运算符(+ - * /) 列名2 FROM 表名;
-- 起别名,每一个列名后都可以直接写一个别名,AS可省略
SELECT 列名1,列名2,... AS 别名 FROM 表名;
条件查询:
| 符号 | 功能 |
|---|---|
| > | 大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| = | 等于 |
| <> 或 != | 不等于 |
| BETWEEN … AND … | 在某个范围之内(都包含) |
| IN(…) | 多选一 |
| LIKE 占位符 | 模糊查询 _单个任意字符 %多个任意字符 |
| IS NULL | 是NULL |
| IS NOT NULL | 不是NULL |
| AND 或 && | 并且 |
| OR 或 || | 或者 |
| NOT 或 ! | 非，不是 |
聚合函数:将一列数据作为一个整体，进行纵向的计算.
分类:
| 函数名 | 功能 |
|---|---|
| count(列名) | 统计数量(一般选用不为null的列) |
| max(列名) | 最大值 |
| min(列名) | 最小值 |
| sum(列名) | 求和 |
| avg(列名) | 平均值 |
-- 语法:
SELECT 函数名(列名) FROM 表名 [WHERE 条件];
排序查询:多个排序条件，当前边的条件值一样时，才会判断第二条件.
| 关键词 | 功能 |
|---|---|
| ORDER BY 列名1 排序方式1,列名2 排序方式2 | 对指定列排序，ASC升序(默认的) DESC降序 |
SELECT 列名 FROM 表名 [WHERE 条件] ORDER BY 列名1 排序方式1,列名2 排序方式2;
-- 分组查询
SELECT 列名 FROM 表名 [WHERE 条件] GROUP BY 分组列名 [HAVING 分组后条件过滤] [ORDER BY 排序列名 排序方式];
-- 分页查询
SELECT 列名 FROM 表名 [WHERE 条件] GROUP BY 分组列名 [HAVING 分组后条件过滤] [ORDER BY 排序列名 排序方式] LIMIT 开始索引,查询条数;
-- 公式：开始索引 = (当前页码-1) * 每页显示的条数
五、约束
概念:对表中的数据进行限定，保证数据的正确性、有效性、完整性！
| 约束 | 说明 |
|---|---|
| PRIMARY KEY | 主键约束 |
| PRIMARY KEY AUTO_INCREMENT | 主键、自动增长 |
| UNIQUE | 唯一约束 |
| NOT NULL | 非空约束 |
| FOREIGN KEY | 外键约束 |
| FOREIGN KEY ON UPDATE CASCADE | 外键级联更新 |
| FOREIGN KEY ON DELETE CASCADE | 外键级联删除 |
-- 约束实现:
-- 主键约束:一般用于表中数据的唯一标识
CREATE TABLE 表名(
列名 数据类型 PRIMARY KEY,
列名 数据类型,
...
);
-- 删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;
-- 添加主键
ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;
-- 主键自增约束,使主键值自增
CREATE TABLE 表名(
列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
列名 数据类型,
...
);
--删除自增
ALTER TABLE 表名 MODIFY 列名 数据类型;
-- 添加自动增长
ALTER TABLE 表名 MODIFY 列名 数据类型 AUTO_INCREMENT;
-- 唯一约束:
CREATE TABLE 表名(
列名 数据类型 UNIQUE,
列名 数据类型,
...
);
-- 删除唯一约束
ALTER TABLE 表名 DROP INDEX 列名;
-- 添加唯一约束
ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;
-- 非空约束:
CREATE TABLE 表名(
列名 数据类型 NOT NULL,
列名 数据类型,
...
);
-- 删除非空约束
ALTER TABLE 表名 MODIFY 列名 数据类型;
-- 添加非空约束
ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;
-- 外键约束：让表与表之间产生关系，从而保证数据的准确性
-- 优点 : 保证字段有效性的； 缺点 : 影响增删改效率，更新不方便，企业一般不用外键约束。
CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名)
-- 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
-- 添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);
六、数据库表设计
三大范式：
第一范式:(列不可分割)
列不可分割,即再分割将破坏数据的完整性,例如身份证号
遗漏: 冗余,删除/添加时关联删除时不完整
第二范式:(每个表都有主键)
在1NF的基础上,不能部分依赖主键
(如果有联合主键,则其他字段,依赖于联合主键,不能只依赖联合主键中的一个字段)
第三范式: 不存在间接依赖
**一对一：**在任意一个表建立外键，去关联另一个表的主键。
**一对多：**在多的一方，建立外键约束，来关联一的一方主键。
**多对多：**需要借助第三张表中间表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键。
七、多表查询（非常重要）
了解：笛卡尔积–>>两张表的所有组合
多表查询格式：
SELECT
列名列表
FROM
表名列表
WHERE
条件...
多表查询-内连接查询：
-- 原理：内连接查询的是两张表有交集的部分数据(有主外键关联的数据)
-- 显式内连接
SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 条件; -- INNER可省略
-- 隐式内连接
SELECT 列名 FROM 表名1,表名2 WHERE 条件;
多表查询-外连接查询：
-- 原理：查询左表的全部数据，和左右两张表有交集部分的数据
-- 左外连接
SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;
-- 右外连接
SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;
-- 选择使用：
1) 如果表小(百万级) 可以使用内连接 ,因为方便简单
2) 左(右)外连接查询的效率比内连接高,如果数据量大 用外连接
多表查询-子查询：
-- 概念：查询语句中嵌套了查询语句。我们就将嵌套查询称为子查询！
-- 结果是单行单列的
SELECT 列名 FROM 表名 WHERE 列名=(SELECT 聚合函数(列名) FROM 表名 [WHERE 条件]);
-- 结果是多行单列的
SELECT 列名 FROM 表名 WHERE 列名 [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]);
-- 结果是多行多列的
SELECT 列名 FROM 表名 [别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];
多表查询-自关联查询：
-- 概念：同一张表中有数据关联。可以多次查询这同一个表！
-- 自己查自己
八、视图
1.视图的概念 (: 类似于 window 的快捷方式)
- 视图是一种虚拟存在的数据表
- 这个虚拟的表并不在数据库中实际存在
- 作用是将一些比较复杂的查询语句的结果，封装到一个虚拟表中。后期再有相同复杂查询时，直接查询这张虚拟表即可
- 说白了，视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上
2.视图的好处
- 简单
- 对于使用视图的用户不需要关心表的结构、关联条件和筛选条件。因为这张虚拟表中保存的就是已经过滤好条件的结果集
- 安全
- 视图可以设置权限 , 致使访问视图的用户只能访问他们被允许查询的结果集
- 数据独立
- 一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响
-- 创建
CREATE VIEW 视图名称 [(列名列表)] AS 查询语句;
-- 查询
SELECT * FROM 视图名称;
-- 修改
UPDATE 视图名称 SET 列名=值 WHERE 条件
-- 修改表结构
ALTER VIEW 视图名称 [(列名列表)] AS 查询语句;
-- 视图删除
3.视图的总结
- 视图是一种虚拟存在的数据表
- 这个虚拟的表并不在数据库中实际存在
- 说白了，视图就是将一条SELECT查询语句的结果封装到了一个虚拟表中，所以我们在创建视图的时候，工作重心就要放在这条SELECT查询语句上
- 视图的好处
- 简单
- 安全
- 数据独立
作用:
1,封装复杂查询
2, 屏蔽敏感数据(经常使用)
九、备份与还原
备份：直接选中要保存的数据库，选择备份–
还原：找到准备好的sql文件，或者备份好的数据库，导入即可。
十、MySQL存储过程和函数
1.存储过程
概念:事先经过编译并存储在数据库中的一段 SQL 语句的集合
优势:
减少网络流量
提高数据处理的效率
java 代码变少了
与函数区别：函数必须有返回值，必须使用return，存储过程没有。另外，存储过程中是PROCEDURE，而函数中是FUNCTION；调用一个是CALL，另一个是SELECT。
补：使用场景不一样
PROCEDURE 用来 处理业务逻辑
function 一般用来自定义函数,例如 : 根据出生日期,计算年龄
创建存储过程语法:
-- 修改分隔符为$
DELIMITER $
-- 标准语法
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
/*
当然，括号内也可以不带任何参数
IN:代表输入参数，需要由调用者传递实际数据。默认的
OUT:代表输出参数，该参数可以作为返回值
INOUT:代表既可以作为输入参数，也可以作为输出参数
*/
BEGIN
-- 首先，如果有需要定义的变量要先定义
-- 定义变量格式：
DECLARE 变量名 数据类型 [DEFAULT 默认值];
SET NAME = '存储过程'; -- 为变量赋值
SELECT NAME; -- 查询变量
sql语句;
END$
-- 修改分隔符为分号
DELIMITER ;
-- 调用存储过程语法
CALL 存储过程名称(实际参数);
-- 查询数据库中所有的存储过程 标准语法
SELECT * FROM mysql.proc WHERE db='数据库名称';
-- 删除存储过程语法
DROP PROCEDURE [IF EXISTS] 存储过程名称;
-- if语句的使用
IF 判断条件1 THEN 执行的sql语句1;
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;
@变量名: 这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程他都是有作用的，这个类似于全局变量一样。
@@变量名: 这种在变量前加上 "@@" 符号, 叫做系统变量
-- while循环
初始化语句;
WHILE 条件判断语句 DO
循环体语句;
条件控制语句;
END WHILE;
-- 创建存储函数
DELIMITER $
-- 标准语法
CREATE FUNCTION 函数名称([参数 数据类型])
RETURNS 返回值类型
BEGIN
执行的sql语句;
RETURN 结果;
END$
DELIMITER ;
-- 调用存储函数
SELECT 函数名称(实际参数);
-- 删除存储函数
DROP FUNCTION 函数名称;
2.MySQL触发器
作用：最大的作用就是记录日志, 而记录日志 有log4j 等技术。
| 触发器类型 | OLD的含义 | NEW的含义 |
|---|---|---|
| INSERT 型触发器 | 无 (因为插入前状态无数据) | NEW 表示将要或者已经新增的数据 |
| UPDATE 型触发器 | OLD 表示修改之前的数据 | NEW 表示将要或已经修改后的数据 |
| DELETE 型触发器 | OLD 表示将要或者已经删除的数据 | 无 (因为删除后状态无数据) |
-- 创建触发器
DELIMITER $
CREATE TRIGGER 触发器名称
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名
[FOR EACH ROW] -- 行级触发器
BEGIN
触发器要执行的功能;
END$
DELIMITER ;
注意：
FOR EACH ROW每一行执行一次 ,行级别触发器(mysql只有这个写法)
3.MySQL事务
-- 需要一个功能(特性), 能够保证 多条sql 同时成功和同时失败 : 数据库已经提供了 , 特性叫 "事务"
-- 多条sql 同时成功和同时失败 这组sql 叫事务!
-- 一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败，单元中的每条 SQL 语句都相互依赖，形成一个整体
至于那些sql 语句组成一个组 需要开发人员自己根据业务判断, 以业务为准
基本使用：
-- 开启事务 : 通知程序请将接下来的更新操作,先暂时保存在 内存中
START TRANSACTION;
UPDATE account SET money=money -500 WHERE id=1;
UPDATE account SET money=money +500 WHERE id=2;
-- 回滚事务: 通知程序,出错误了,内存删除数据还原
ROLLBACK;
-- 提交事务: 通知程序,出都成功,把内存中的数据更新到磁盘,永久存储
COMMIT;
事务的提交方式：
-- 默认提交方式的修改(一般不修改,知道有这回事)
0 代表手动提交 1 代表自动提交
-- 查看
SELECT @@autocommit;
-- 修改
SET @@autocommit=1;
-- 另一种写法
SET autocommit=1
**事务的四大特征：**原子性，持久性，隔离性，一致性。
事务的隔离级别：
| 隔离级别 | 名称 | 出现脏读 | 出现不可重复读 | 出现幻读 | 数据库默认隔离级别 | |
|---|---|---|---|---|---|---|
| 1 | read uncommitted | 读未提交 | 是 | 是 | 是 | |
| 2 | read committed | 读已提交 | 否 | 是 | 是 | Oracle / SQL Server |
| 3 | repeatable read | 可重复读 | 否 | 否 | 是 | MySQL |
| 4 | **serializable ** | 串行化 | 否 | 否 | 否 |
注意：隔离级别从小到大安全性越来越高，但是效率越来越低 , 所以不建议使用READ UNCOMMITTED 和 SERIALIZABLE 隔离级别.
十一、存储引擎
常用引擎的特性对比
- 常用的存储引擎
- MyISAM存储引擎
- 访问快,不支持事务和外键。表结构保存在.frm文件中，表数据保存在.MYD文件中，索引保存在.MYI文件中。
- InnoDB存储引擎 (MySQL5.5版本后默认的存储引擎)
- 支持事务 ,占用磁盘空间大 ,支持并发控制。表结构保存在.frm文件中，如果是共享表空间，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。如果是多表空间存储，每个表的数据和索引单独保存在 .ibd 中。
- MEMORY存储引擎
- 内存存储 , 速度快 ,不安全 ,适合小量快速访问的数据。表结构保存在.frm中。
- 内存断电丢失，所以不安全
- MyISAM存储引擎
总结：引擎的选择
- MyISAM ：由于MyISAM不支持事务、不支持外键、支持全文检索和表级锁定，读写相互阻塞，读取速度快，节约资源，所以如果应用是以查询操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
- InnoDB : 是MySQL的默认存储引擎， 由于InnoDB支持事务、支持外键、行级锁定 ，支持所有辅助索引(5.5.5后不支持全文检索)，高缓存，所以用于对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，读写频繁的操作，那么InnoDB存储引擎是比较合适的选择，比如BBS、计费系统、充值转账等
- MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供更快的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
- 总结：针对不同的需求场景，来选择最适合的存储引擎即可！如果不确定、则使用数据库默认的存储引擎！
十二、索引
功用：增加查询效率
1.概念
MySQL数据库中的索引:
-- 是帮助MySQL高效获取数据的一种数据结构,所以，索引的本质就是数据结构。
通过合理的数据结构 比如 BTree,hash , 创建的一个 类似 字典目录的文件,通过这个文件能够快速的找到数据;
索引是一个文件
2.分类
- 功能分类
- 普通索引： 最基本的索引，它没有任何限制。（需要单独创建才有）
- 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。
- 主键索引：一种特殊的唯一索引，不允许有空值。一般在建表时同时创建主键索引。
- 组合索引：顾名思义，就是将单列索引进行组合。（需要单独创建才有）
- 外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。
- 全文索引：快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。
- 结构分类
- B+Tree索引 ：MySQL使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎默认的索引类型。
- Hash索引 : MySQL中Memory存储引擎默认支持的索引类型。
3.操作
-- 查看student表中的索引
SHOW INDEX FROM student;
-- 为student表中年龄列创建一个唯一索引
CREATE UNIQUE INDEX idx_age ON student(age);
-- 为student表中年龄列创建一个普通索引
CREATE INDEX idx_age ON student(age);
-- 添加一个索引
ALTER TABLE student ADD INDEX idx_name(NAME);
-- 删除索引
DROP INDEX 索引名称 ON 表名;
4.说明
底层算法都是 :B+tree
1. 创建唯一索引 : = 普通索引+ 唯一约束
2. 主键自带索引: 主键索引= 普通索引+唯一约束+非空约束;
3. 外键约束自带索引: 外键索引
5.索引分类：普通索引和组合索引
说明: 多条件查询,组合索引效率高于普通索引
-- 普通索引
ALTER TABLE student ADD INDEX idx_name(NAME);
ALTER TABLE student ADD INDEX idx_gender(gender);
-- 组合索引
ALTER TABLE student ADD INDEX idx_name_gender(NAME,gender);
-- 普通索引,组合索引
-- 如果查询只有一个条件,则创建普通索引
SELECT * FROM student WHERE NAME='张三';
SELECT * FROM student WHERE gender='男';
-- 如果是多条件查询,组合索引效率高于普通索引
SELECT * FROM student WHERE gender='男' AND NAME='张三';
6.索引的优点和缺点
优点: 增加查询的效率
缺点: 浪费空间,增删该的效率降低了
不要创建过多的索引, 只在经常查询的字段上创建索引
-- 命中索引
SELECT * FROM student WHERE NAME LIKE '张%';
-- 不命中
SELECT * FROM student WHERE NAME LIKE '%张三%';
-- 不命中
SELECT * FROM student WHERE NAME LIKE '%三';
7.sql优化
1, like 尽量不使用 % 写在左边
2, char ,varchar
十三、锁
为什么要使用锁?
多线程环境下为了防止多个线程对同一条数据修改造成的数据不一致问题(也称线程安全问题),必须加锁
为什么不用java 锁
因为java锁只能锁一台tomcat ,多台机器不行
1.锁的分类
- 按操作分类：
- 共享锁：也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响 ，但是不能修改数据记录
- 排他锁：也叫写锁。当前的操作没有完成前,会阻断其他操作的读取和写入
- 按粒度分类：
- 表级锁：操作时，会锁定整个表。开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。偏向于MyISAM存储引擎！
- 行级锁：操作时，会锁定当前操作行。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。偏向于InnoDB存储引擎！
- 页级锁：锁的粒度、发生冲突的概率和加锁的开销介于表锁和行锁之间，会出现死锁，并发性能一般。
- 按使用方式分类：
- 悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁
- 乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据
- 不同存储引擎支持的锁
| 存储引擎 | 表级锁 | 行级锁 | 页级锁 |
|---|---|---|---|
| MyISAM | 支持 | 不支持 | 不支持 |
| InnoDB | 支持 | 支持 | 不支持 |
| MEMORY | 支持 | 不支持 | 不支持 |
| BDB | 支持 | 不支持 | 支持 |
2.InnoDB
共享锁特点
- 数据可以被多个事务查询，但是不能修改
- 如果一个线程加锁了，其他线程只允许读，不允许写，除非一方释放；
- 如果两个线程都加锁了,再同时更新会出现问题 死锁。
-- 标准语法 (mode：模式，share mode：共享模式，共享锁模式)
SELECT语句 LOCK IN SHARE MODE;
-- 是 行锁
如果有索引, 加的是行锁,锁定where 条件匹配的行
如果没有索引,表锁
-- 是 悲观锁
查询时只要加锁,就是悲观锁(mysql 没有乐观锁)
排他锁特点
- 排他锁特点
- 加锁的数据，不能被其他事务加锁查询或修改
- 一个线程获得了 排他锁 则其他线程不允许加锁查询或者修改(允许其他先不加锁查询)
- 如果其他线程有了其他锁,则排他锁,加不了
- 一个线程获得了 排他锁 信息修改只能是当前线程修改,其他线程等待释放
- 语法：
-- 标准语法
SELECT语句 FOR UPDATE;
-- 是行锁
不管有没有索引,都是行锁
-- 是悲观锁
查询时只要加锁,就是悲观锁(mysql 没有乐观锁)
注意：锁的兼容性
- 共享锁和共享锁 兼容
- 共享锁和排他锁 冲突
- 排他锁和排他锁 冲突
- 排他锁和共享锁 冲突
3.MylSAM
MyISAM引擎：不支持行锁，只能加表锁
读锁：
读锁特点
- 所有连接只能查询数据，不能修改
-- 标准语法
-- 加锁
LOCK TABLE 表名 READ;
-- 解锁(将当前会话所有的表进行解锁)
UNLOCK TABLES;
-- 是一个共享锁;是一个表锁;是一个 悲观锁
写锁：
写锁特点
- 其他连接不能查询和修改数据
- 当前连接可以查询和修改数据
-- 标准语法
-- 加锁
LOCK TABLE 表名 WRITE;
-- 解锁(将当前会话所有的表进行解锁)
UNLOCK TABLES;
-- 排他锁 : 其他线程不允许加锁,不允许读;表锁;悲观锁.
-- 应用场景：防止 多买多卖
4.悲观锁和乐观锁
-
悲观锁的概念
- 就是很悲观，它对于数据被外界修改的操作持保守态度，认为数据随时会修改。
- 整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系型数据库提供的锁机制。
- 我们之前所学的行锁，表锁不论是读写锁都是悲观锁。
-
乐观锁的概念
- 就是很乐观，每次自己操作数据的时候认为没有人会来修改它，所以不去加锁。
- 但是在更新的时候会去判断在此期间数据有没有被修改。
- 需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。
-
悲观锁和乐观锁使用前提
- 对于读的操作远多于写的操作的时候，这时候一个更新操作加锁会阻塞所有的读取操作，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，这时候可以选择乐观锁。
- 如果是读写比例差距不是非常大或者系统没有响应不及时，吞吐量瓶颈的问题，那就不要去使用乐观锁，它增加了复杂度，也带来了业务额外的风险。这时候可以选择悲观锁。
-
乐观锁的实现方式
-
版本号
- 给数据表中添加一个version列，每次更新后都将这个列的值加1。
- 读取数据时，将版本号读取出来，在执行更新的时候，比较版本号。
- 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。
- 用户自行根据这个通知来决定怎么处理，比如重新开始一遍，或者放弃本次更新。
-
时间戳
-
和版本号方式基本一样，给数据表中添加一个列，名称无所谓，数据类型需要是timestamp
-
每次更新后都将最新时间插入到此列。
-
读取数据时，将时间读取出来，在执行更新的时候，比较时间。
-
如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。
-
5.锁的总结
-
表锁和行锁
- 行锁：锁的粒度更细，加行锁的性能损耗较大。并发处理能力较高。InnoDB引擎默认支持！
- 表锁：锁的粒度较粗，加表锁的性能损耗较小。并发处理能力较低。InnoDB、MyISAM引擎支持！
-
InnoDB锁优化建议
-
尽量通过带索引的列来完成数据查询，从而避免InnoDB无法加行锁而升级为表锁。
-
合理设计索引，索引要尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定。
-
尽可能减少基于范围的数据检索过滤条件。
-
尽量控制事务的大小，减少锁定的资源量和锁定时间长度。
-
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率。
-
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁的产生。
-
745
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
