5.1.1 MySql数据类型
数值类型
整数类型：TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。分别占用1、2、3、4、8个字节。并可以存储不同的储值范围。
例如TINYINT可以存在-127至128之间的整数;BIGINT可以存储范围在-9,223,372,036,854,775,808到9,223,372,036,854,775,807之间的整数
浮点类型：FLOAT,DOUBLE。用于存储单精度和双精度浮点数,分别占用4、8各个字节。FLOAT可以存储大约7位精度的浮点数,DOUBLE可以存储大约15位精度的浮点数
定点数类型：DECIMAL,NUMERIC。用于存储精确的值。
字符串类型
定长字符串类型：CHAR
变长字符串类型：VARCHAR
其他字符串类型：TEXT(大文本数据)、BLOB(二进制对象)等
日期时间类型
存储年月日：DATE
存储时间信息：TIME
存储年月日和时间信息：DATETIME
存储时间戳信息：TIMESTAMP
布尔类型：使用TINYINT(1)表示,可以存储true和false的值
5.1.2 锁机制
1.作用：解决资源共享造成的并发问题
2.锁分类
按照操作类型分：
读锁(共享锁):对同一个数据,多个读操作,可以同时进行,互不干扰
写锁(排他锁):当前写操作没有结束,无法进行其他的读、写操作
按照操作范围分：
表锁： 一次性针对一张表整体进行加锁。如MyISAM存储引擎使用表锁,开销小,加锁快;无死锁;但是锁范围大,容易发生锁冲突、并发度低
行锁：一次性针对一条数据加锁,如InnoDB存储引擎使用行锁,开销大,加锁慢;容易出现死锁,锁范围小,不易发生锁冲突,并发度高。
页锁：开销和粒度介于表锁和页锁之间,会出现死锁,并发度一般
3.表锁基本使用
加锁相关命令：
加读锁-命令：LOCK TABLES '表名' READ;
加写锁-命令：LOCK TABLES '表名' WRITE;
释放锁：unlock tables;
查看加锁的表：show open tables; 1表示被加了锁
分析表锁的严重程度：show status like 'table%';
Table_locks_immediate：即可能获取到的锁数
Table_locks_waited：需要等待的表锁数(如果该值越大，说明存在越大的锁竞争)。
一般建议：Table_locks_immediate/Table_locks_waited > 5000， 建议采用InnoDB引擎，否则MyISAM引擎
192.168.11.101（1）会话给表t_user加读锁(共享锁)
示例：lock tables 't_user ' read;
结论：某会话对A表增加读锁,则该会话可对此表进行查询操作,无法进行写操作。也不能对其他表进行读、写操作;如果某会话给A表增加读锁,其他会话可对A表进行读操作,但是写操作需要等待原会话释放锁;其他会话可以针对其他表进行读、写操作
192.168.11.101（1）会话给表t_user加写锁(排他锁)
示例：lock tables 't_user' write;
结论：某会话对A表增加写锁,则该会话对此表可以进行任何操作,但是不能操作其他表
如果某会话给A表增加写锁,其他会话可对此表进行操作,前提是原会话释放掉锁;其他会话可以针对其他表进行读、写操作
4.行锁基本使用
前提：mysql默认自动commit;oracle默认不会自动commit;暂时将自动commit关闭命令：set autocommit = 0;start transaction;begin;
相关说明：通过索引对记录进行加锁,包括共享锁和排他锁;共享锁(S Lock)可让事务读取记录，但不能修改记录；排他锁(X Lock)可让事务读取并修改记录
在InnoDB引擎中,默认使用行锁,通过索引来锁定行的,如果SQL没有使用索引或者索引失效,那么InnoDB会使用表锁
命令：select ... LOCK IN SHARE MODE; 通过select 给某行数据添加共享锁
SELECT ... FOR UPDATE ; 通过select 给某行数据添加排他锁
示例：
-- 使用SELECT ... LOCK IN SHARE MODE获取共享锁
SELECT * FROM table_name WHERE condition LIMIT 1 LOCK IN SHARE MODE;
-- 使用SELECT ... FOR UPDATE获取排他锁
SELECT * FROM table_name WHERE condition LIMIT 1 FOR UPDATE;
行锁分析：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_waits：等待次数。从系统启动到现在一共等待的次数。
总结：在先关闭自动提交的前提下,某会话针对一条数据进行加锁,其他会话针对此数据操作必须先等原会话释放掉锁(commit或者rollback),但是可以针对其他数据进行操作。
行锁的特殊情况：间隙锁(值在范围内,但却不存在)
示例：t_role有id为1,2,3,4,6,7,8的数据,如果此时执行 update t_role set code = 'x' where id >1 and id <9;
即在此范围内没有id=5的数据,则id=5的数据成为间隙.
mysql会自动给间隙添加间隙锁。上述示例中会自动给id=5的数据添加间隙锁/行锁。
缺点：比表锁性能损耗大
优点：并发能力强,效率高。高并发使用InnoDB存储引擎,否则使用MyISAM
5.MySql死锁
定义：两个或多个事务在同一资源上相互占有资源,而又在等待其他事务释放资源,从而导致无法向前推进的情况
预防：确保事务有序访问资源,减少事务持有锁的时间,保证相同的锁顺序
通常解决方法：回滚其中一个事务
检验是否死锁：SHOW ENGINE INNODB STATUS;
或者通过innodb_print_all_deadlocks配置项记录死锁日志,前提是InnoDB监控开启此功能,开启此功能在配置文件中设置innodb_print_all_deadlocks=1,重启即可
频繁死锁可能导致日志迅速膨胀,生产环境谨慎使用
参考资料：https://blog.csdn.net/weixin_55076626/article/details/128532271
https://blog.csdn.net/jkzyx123/article/details/123716477
5.1.3 事务
定义：由单独单元的1个sql或多个sql组合而成,单元中每个sql相互依赖,单元是不可分隔的整体,里面的内容要么全部执行成功,要么全部失败。
与存储引擎的关系：InnoDB支持事务,MYISAM和MEMORY不支持事务。show engines;查看存储引擎。
特性：
原子性(Atomicity):事务是不可分隔的最小执行单位,要么全部成功要么全部失败
一致性(Consistency):事务必须使数据库从一个一致状态转换为另一个一致状态。例如张三给李四转账50.事务中会减少张三50,增加李四50.一致性是指其他事务要么看到张三还没给李四转账,要么已经看到张三少了50,李四多了50.
隔离性(Isolation):一个事务内部的操作和使用的数据对其他事务是隔离的,并发执行的事务之间不可互相干扰
持久性(Durability):一个事务一旦提交成功,对数据库中数据的改变是永久的,后续操作不应对其有影响
分类：
隐式事务:没有明显的开启和结束标记,具有自动提交事务的功能
DML语句(insert、update、delete)
显示事务:有明显的开启和结束标记,使用显示事务前提是将自动提交事务功能给关闭,即将autocommit的变量值设置为0（1为开启,0为关闭）。set autocommit = 0;
查看是否自动提交事务：select @@autocommit;
开启事务步骤
start transaction;
编写事务执行sql;(insert、update、delete)
commit;或者rollback; (提交或者回滚事务)
事务并发可能出现的问题
脏读：两个事务S1,S2,S2读取了S1更新但还未提交的数据,若S1回滚,S2读取的数据是无效的
幻读：两个事务S1,S2,S2在表中读取了一个字段,然后S1在表中插入了一些新的数据,S2再读取此表时,会莫名多出现一些数据
不可重复读：两个事务S1,S2,S2读取了一个字段,S1将此字段更新后,S2再次读同一字段,值不相同了
事务的隔离级别
读未提交(read uncommitted):允许事务读取未被其他事务提交的变更。脏读、幻读、不可重复读都可能出现
读已提交(read committed):只允许事务读取已被其他事务提交的变更。幻读、不可重复读都可能出现
可重复读(repeatable read):确保事务可以多次从一个字段中读取相同的值,在此事务期间,禁止其他事务对此字段进行更新。幻读可能出现
串行化(serializable):确保事务可以从一张表中读取相同的行,在此事务期间,禁止其他事务对此表进行插入、更新、删除操作。所有并发问题都可避免,但性能很低。
oracle支持两种事务隔离级别：读已提交、串行化,默认事务隔离级别是读已提交
mysql事务默认隔离级别是可重复读
查看事务隔离级别：select @@tx_isolation; mysql8.0之后采用select @@transaction_isolation;
#设置当前mysql连接的隔离级别:
set session transaction isolation level read uncommitted;
#设置数据库系统的全局的隔离级别:
set global transaction isolation level read uncommitted;
事务的保存点(回滚点)
回滚点表示的是事务回滚到指定回滚点。
使用语法：savepoint 节点名称;
注意事项：只能搭配rollback一起使用
参考资料：https://blog.csdn.net/qq_56880706/article/details/122653735
5.1.4 MVCC(多版本并发控制)
前提了解：
MVCC:
定义：全称Multi-Version Concurrency Control，即多版本并发控制。是一种并发控制的方法,一般在数据库管理系统中,实现对数据库的并发访问,在编程语言中实现事务内存。
作用：MVCC在MySql InnoDB中的实现主要是为了提高数据库并发性能,用更好的方式处理读写冲突,即便有读写冲突时,也能做到不加锁,非阻塞并发读。
MVCC本质
为了实现快照读-写冲突不加锁;当前读是一种加锁的操作,是悲观锁的实现
MVCC在mysql中的实现是由3个隐式字段,undo日志,Read View等去完成的
MySQL InnoDB下的当前读和快照读
当前读：共享锁(select lock in share mode )、排他锁(select for update ,update,insert,delete)等都是当前读,因为读取的是记录的最新版本,读取时还需保证其他事务不会影响当前记录,会对读取的记录进行加锁
快照读：不加锁的select就是快照读,即不加锁的非阻塞读;
快照读的前提是隔离级别非串行级别,串行级别下的快照读会退化成当前读;
快照读的出现是基于提高并发性能的考虑,快照读实现基于MVCC,MVCC是行锁的一个变种,但它在很多情况下,避免了加锁,降低了开销。
快照读不一定读取的是最新的数据,可能是历史版本的数据
当前读、快照读和MVCC之间的关系
MVCC是 维持一个数据的多个版本,使读写操作没有冲突 的概念,并非实现
快照读是MySQL实现MVCC理想模型的其中一个非阻塞读功能;当前读就是悲观锁的具体功能实现。
MVCC解决什么问题：
数据库并发场景有以下3种类,分别是
读-读：不存在问题,无需并发控制
读-写：有线程安全问题,可能遇到脏读、幻读、不可重复读
写-写：有线程安全问题,可能存在更新丢失问题
MVCC好处
解决读-写冲突的无锁并发控制
并发读写数据库时,读操作和写操作不会相互阻塞,提高了数据库并发读写的性能
解决脏读、幻读、不可重复读等事务隔离问题,但不能解决更新丢失问题
MVCC是为了避免单一采用悲观锁解决读写冲突提出的解决方案,
在数据库中,MVCC可以分别和悲观锁、乐观锁进行阻塞
MVCC+悲观锁 MVCC解决读写冲突,悲观锁解决写写冲突
MVCC+乐观锁 MVCC解决读写冲突,乐观锁解决写写冲突
以上两种方式可最大程度提高数据库并发性能,并解决读写冲突、写写冲突导致的问题
MVCC实现原理：依赖记录中的3个隐式字段,undo日志,Read View来实现的
隐式字段：每行数据除了自定义字段外,还有数据库隐式定义的 DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID 等字段
DB_TRX_ID：6 byte，最近修改(修改/插入)事务 ID：记录创建这条记录/最后一次修改该记录的事务 ID
DB_ROLL_PTR：7 byte，回滚指针，指向这条记录的上一个版本（存储于 rollback segment 里）
DB_ROW_ID：6 byte，隐含的自增 ID（隐藏主键），如果数据表没有主键，InnoDB 会自动以DB_ROW_ID产生一个聚簇索引
undo日志：主要分两种
insert undo log:insert新纪录时产生的undo log,只在事务回滚时需要,事务一旦提交则立即丢弃
update undo log:update或delete时产生的undo log,事务回滚和快照读时都需要,所以不可随意丢弃,只有在快速读或事务回滚不涉及该日志时,对应日志才会被purge线程统一清除
purge:InnoDB的MVCC机制,更新或删除都是设置下deleted——bit,并非真将数据删除,purge是InnoDB中专门清理deleted-bit为true的记录的线程
Read View读视图
Read View就是事务进行快照读操作时产生的读视图,是数据库系统当前的一个快照,记录并维护系统当前活跃事务的ID(每个事务开启时,都会分配一个ID,这个ID是递增的,事务越新,ID越大)
参考资料：https://blog.csdn.net/SnailMann/article/details/94724197
https://zhuanlan.zhihu.com/p/421769708
5.1.5 存储引擎(Innodb,MyISAM)
定义：存储引擎是数据库底层软件组织,数据库使用存储引擎进行创建、查询、更新、删除数据。
mysql存储引擎查询命令：SHOW ENGINES;
mysql常见存储引擎：
InnoDB：事务性数据库首选引擎,MySQL的默认存储引擎
MyISAM:基于ISAM存储引擎,并对齐进行扩展
MERGE：一组MyISAM表的组合
MEMORY：将表的数据存储到内存中
ARCHIVE：仅支持插入和查询两种功能
CSV：将CSV类型的文件当作表进行处理
BLACKHOLE:黑洞引擎,支持事务,支持mvcc的行级锁,写入此引擎的数据都会消失
PERFORMANCE_SCHEMA：用于收集服务器性能参数
Federated：将不同的mysql服务器联合起来,组成一个完整的数据库
存储引擎如何选择：查看参考资料
参考资料：https://blog.csdn.net/yjclsx/article/details/81911027
扫一扫
1058
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
