目录
数据库锁的分类
数据库本身也提供了一些锁,按加锁方式分为
- 乐观锁:需要增加一个额外的版本号字段,标识记录的数据版本,提交更新时校验数据版本是否一致。实际并没有加锁,并发支持好。
- 悲观锁:数据库本身提供的锁机制,直接调用相关语句即可,可细分为排它锁、共享锁。操作之前需要先加锁,并发支持差。
按照读写权限分为
- 排它锁:又叫做写锁、X锁,同一时刻,最多只能被一个线程持有,最多只能有1个线程对加锁的这块数据进行读写。
- 共享锁:又叫做读锁、S锁,同一时刻,可以被多线程共同持有,可以有多个线程同时对加锁的这块数据进行读,但读的同时所有线程都不能对这块数据进行写操作,进行写操作需要等对这块数据的所有读操作都完成、共享锁被释放。
排它锁、共享锁都是数据库提供的锁机制,直接调用数据库的相关语句即可。
共享锁可能发生死锁问题:一块数据已经加了共享锁,如果一个事务先对这块数据进行读,再进行写,写操作需要等待共享锁释放才能执行,而这个事务的写操作尚未执行,事务没有提交,一直持有共享锁,写操作永远等不到共享锁释放,即造成了死锁。
其它线程等待获取锁,如果等待指定时间后还没获取到锁,会抛出超时等待异常。
按锁粒度分为
- 行级锁:针对行加锁,加锁慢;锁粒度小,并发支持好。
- 表级锁:直接给整张表加锁,加锁快;锁粒度大,并发支持差。
- 间隙锁:对InnoDB引擎的表进行范围查找时,给整个范围区间加锁,区间上不存在的行也会被加锁。
eg. where id>=1 and id<=10,操作id在[1,10]上的记录,会给[1,10]整个id区间加锁,加锁范围的整个区间,不仅仅只是区间上存在的记录。
假设之前删除了id=5、id=8的记录,这2行不存在的记录即间隙,也会被锁住,暂时不能被其它线程访问。
mysql的存储引擎
mysql提供了多种存储引擎,最常见的有2种:MyISAM 、InnoDB 。
MyISAM
- 不支持事务、外键
- 使用表级锁,不支持行级锁,并发支持差。读时自动给表加上读锁(也叫作共享锁),其它事务可以读、但不能写,其它事务要执行写操作需要等待表被释放;写时自动给表加上写锁(也叫作x锁、排它锁),其它事务不能读、写,直到表被释放。
- 支持全文索引,但全文索引一般用ES、Solr,基本不使用mysql的全文索引
MyISAM是5.5之前默认的存储引擎,适合创建
- 不需要事务、不使用外键的表
- 主要进行读操作的表。由于MyISAM写时要加表锁,性能差,所以不适合创建频繁增删改的表
InnoDB
- 支持外键。mysql的存储引擎中只有InnoDB支持外键
- 支持事务。默认的事务隔离级别是可重复读
- 默认使用行级锁(通过MVCC实现),也支持表级锁,并发支持好。读时自动给使用的行加读锁,写时自动给使用的行加写锁。
InnoDB是mysql5.5及其之后的默认存储引擎,适合创建
- 需要使用外键的表
- 对安全性要求高、要使用事务的表
- 频繁增删改的表
执行insert、update、delete语句之前,InnoDB会自动给涉及到的数据集加排它锁,但只有在sql语句走索引时加的排它锁才是行锁,表没有建立索引或者sql语句没有走索引时加的排它锁是表锁。
InnoDB的行锁是给索引中的行加锁,表锁是给数据表中的行加锁。行锁的性能远高于表锁,尽量让sql语句走索引,一来可以快速定位记录位置,二来借助行锁可以实现更高的并发。
mysql常用数据类型
数值型
数据类型 | 描述 |
---|---|
tinyint | 1字节,-128 ~ 127。如果指定无符号,则为0~255,下列的其它整型可依此类推 |
smallint | 2字节,正负3w+ |
mediumint | 3字节,正负800w+ |
int 或者 integer | 4字节,正负21亿+ |
bigint | 8字节,正负900千万亿+ |
float | 单精度,4字节,10的-38次方 |
double | 双精度,8字节,10的-308次方 |
decimal | 定点型 |
整型不能指定最大存储位数,但可以指定显示位数,需要和 zerofill 搭配使用才有效,超过指定位数时完整展示,长度不够时自动在开头补0凑齐位数,示例 status tinyint(4) zerofill not null comment '状态'
。型的显示位数比较鸡肋,不建议指定。
float、double有误差,如果对精度要求很高,比如涉及到钱财,用decimal。
float 、decimal、decimal 都可以指定最大总位数、小数部分固定位数,示例:float(4,2),总位数最多4位,小数部分固定2位,就是说整数部分最多2位
#小数部分位数不足时,自动在末尾补0
1.1 => 1.10
#整数部分位数超出时,即超出能存储的最值时,不会报错但会有warning,自动取能存储的最值
111.1 => 99.99
#整数部分位数ok,小数部分位数超出时,不会报错也没有warning,会自动对小数超出部分四舍五入,以满足小数部分的位数要求
11.1111 => 11.11
11.1151 => 11.12
字符串
数据类型 | 最大字符数 |
---|---|
char | 255 |
varchar | 6w+ |
tinytext | 255 |
text | 6w+ |
mediumtext | 1千万+ |
longtext | 42亿+ |
char是定长字符串,内容长度不够时自动补空格,适合存储固定长度的字符串,varchar是变长字符串。
char、varchar可指定最大字符数、默认值,eg. varchar(5) 最多只能存储5个字符,其它字符串类型不能指定长度、默认值。
日期时间
数据类型 | 范围 |
---|---|
time | hh:mm:ss,00:00:00~23:59:59 |
date | yyyy-MM-dd,1000-01-01 ~ 9999-12-31 |
datetime | 格式:yyyy-MM-dd hh:mm:ss ;8字节,1000-01-01 00:00:00 ~ 9999-12-31 23:59:59;与时区无关 |
timestamp | 格式:yyyy-MM-dd hh:mm:ss;4字节,1970-01-01 00:00:00 ~ 2038-01-19…;与时区有关(受时区影响) |
使用时间类型的注意点
java.util.Date 是毫秒级时间,mysql中的 time、datetime、timestamp 默认保存到秒级,会自动对毫秒部分进行舍入,[0, 500)毫秒直接舍弃,[500, 999]毫秒则进1秒。示例
java.util.Date | mysql datetime |
---|---|
2020-01-01 00:00:00.000 | 2020-01-01 00:00:00 |
2020-01-01 00:00:00.400 | 2020-01-01 00:00:00 |
2020-01-01 00:00:00.500 | 2020-01-01 00:00:01 |
//假设是 2020-01-01 00:00:00.500
Date nowTime = new Date();
//db中实际保存的 finshTime 是 2020-01-01 00:00:01
OrderDao.insert(OrderPo.builder()
.orderNo()
.finshTime(nowTime)
.build());
//用 finsh_time <= #{nowTime} 查不到刚才插入的记录
List<OrderPo> orderPos = OrderDao.listByQo(OrderQo.builder()
.maxFinshTime(nowTime)
.build());
二进制数据
- blob
- tinyblob
- mediumblob
- longblob
mysql可以存储二进制数据,但操作二进制数据的性能并不好,尽量少用二进制数据类型。
关于 enum 枚举
mysql支持enum这种数据类型,插入枚举类型时,mysql会先检查值是否在允许值列表中,如果不在则检查是否在枚举的索引列表中,在枚举索引列表中的值也可以插入。
比如enum的允许值是字符串"0"、“1”、“2”,对应的索引列表是1、2、3(从1开始)
- 插入数值3,3不在允许值列表中,但3在索引值列表中,所以能插入;
- 插入字符串"3"时,3不再允许值列表中,自动转换为整型,在索引值列表中,也能插入。
不熟悉mysql枚举特性的人使用容易出错,尽量避免使用枚举。
mysql标识符命名规则
mysql标识符标识符包括表名、字段名、数据库编程中的变量名
- 可包含字母、数字、三个特殊字符(#_$),必须以字母开头
- 使用多个单词组成时,单词之间用下划线_连接
- 不能使用mysql的关键字、保留字
建表时尤其要注意:字段名不要使用数据库关键字,比如 type,可以换成 xxx_type
mysql中utf8、utf8mb4字符集的区别
utf8的表示一个字符最大可用三个字节,基本可以表示所有字符,但表示不了极其生僻的字符、新增的字符,比如Emoji 表情。
utf8mb4是utf8的超集,mb4即most bytes 4的意思,用于兼容四字节的unicode。
一般使用utf8就够了,当然使用utf8mb4兼容性更好。
关于null
在mysql中,如果不指定字段not null,字段默认可以为null。
- 存储:null不能像字符串、数值一样直接存储,需要mysql特殊处理。空字符串占用的长度是0,数值0占用的长度是1,null占用的长度并非是0,而是1。
- 对索引的影响:mysql难以对值可以为null的字段进行优化。
- 作为操作条件:判断字段是否为null,不能用=、!=、<>,要用is null、is not null。mysql会给!=会自动加上is not null,比如 username != ‘xxx’ ,相当于 username != ‘xxx’ and username is not null。
- 参与排序:null会当做最小值对待,asc排序时null会被放在最前面,desc排序时null会被放在最后面。
- 参与聚合:值为null的行不参与聚合。比如50个学生,10人缺考成绩是null,avg(score)、sum(score)统计的都是40个考了的同学的score,会先去除值为null的行再进行聚合。
- 参与计算:null参与计算时,结果都是null。比如select 1+null; 结果是null。