MySQL 锁机制、存储引擎、数据类型

 

数据库锁的分类

数据库本身也提供了一些锁，按加锁方式分为

• 乐观锁：需要增加一个额外的版本号字段，标识记录的数据版本，提交更新时校验数据版本是否一致。实际并没有加锁，并发支持好。
• 悲观锁：数据库本身提供的锁机制，直接调用相关语句即可，可细分为排它锁、共享锁。操作之前需要先加锁，并发支持差。
   

按照读写权限分为

• 排它锁：又叫做写锁、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。