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

系统管理员 2023-02-13 14:52 8阅读 0赞

### 目录 ###

*   *   *  数据库锁的分类
         *  mysql的存储引擎
         *   *  MyISAM
             *  InnoDB
         *  mysql常用数据类型
         *   *  数值型
             *  字符串
             *  日期时间
             *  二进制数据
             *  关于 enum 枚举
         *  mysql标识符命名规则
         *  mysql中utf8、utf8mb4字符集的区别
         *  关于null

### 数据库锁的分类 ###

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

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

按照读写权限分为

*  排它锁：又叫做写锁、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支持外键
 *  支持事务。默认的事务隔离级别是可重复读
 *  默认使用行级锁，也支持表级锁，并发支持好。读时自动给使用的行加读锁，写时自动给使用的行加写锁。

InnoDB是mysql5.5及其之后的默认存储引擎，适合创建

*  需要使用外键的表
 *  对安全性要求高、要使用事务的表
 *  频繁增删改的表

执行insert、update、delete语句之前，InnoDB会自动给涉及到的数据集加排它锁，但只有在sql语句走索引时加的排它锁才是行锁，表没有建立索引或者sql语句没有走索引时加的排它锁是表锁。

InnoDB的行锁是给索引中的行加锁，表锁是给数据表中的行加锁。行锁的性能远高于表锁，尽量让sql语句走索引，一来可以快速定位记录位置，二来借助行锁可以实现更高的并发。

### mysql常用数据类型 ###

#### 数值型 ####

<table> 
 <thead> 
  <tr> 
   <th align="left">数据类型</th> 
   <th align="left">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">tinyint</td> 
   <td align="left">-128 ~ 127</td> 
  </tr> 
  <tr> 
   <td align="left">smallint</td> 
   <td align="left">正负3w+</td> 
  </tr> 
  <tr> 
   <td align="left">mediumint</td> 
   <td align="left">正负800w+</td> 
  </tr> 
  <tr> 
   <td align="left">int 或者 integer</td> 
   <td align="left">正负21亿+</td> 
  </tr> 
  <tr> 
   <td align="left">bigint</td> 
   <td align="left">正负900千万亿+</td> 
  </tr> 
  <tr> 
   <td align="left">float</td> 
   <td align="left">单精度，4字节，10的-38次方</td> 
  </tr> 
  <tr> 
   <td align="left">double</td> 
   <td align="left">双精度，8字节，10的-308次方</td> 
  </tr> 
  <tr> 
   <td align="left">decimal</td> 
   <td align="left">定点型</td> 
  </tr> 
 </tbody> 
</table>

整型可以指定最大位数，eg. int(11)，不指定默认为可存储的最大位数。

浮点型、decimal可以指定总位数、小数位数，eg. double(10,2)，最长10位，小数2位（超出会四舍五入），decimal不指定默认为(10,0)。

float、double有误差，如果对精度要求很高，比如涉及到钱财，用decimal。

#### 字符串 ####

<table> 
 <thead> 
  <tr> 
   <th align="left">数据类型</th> 
   <th align="left">最大字符数</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">char</td> 
   <td align="left">255</td> 
  </tr> 
  <tr> 
   <td align="left">varchar</td> 
   <td align="left">6w+</td> 
  </tr> 
  <tr> 
   <td align="left">tinytext</td> 
   <td align="left">255</td> 
  </tr> 
  <tr> 
   <td align="left">text</td> 
   <td align="left">6w+</td> 
  </tr> 
  <tr> 
   <td align="left">mediumtext</td> 
   <td align="left">1千万+</td> 
  </tr> 
  <tr> 
   <td align="left">longtext</td> 
   <td align="left">42亿+</td> 
  </tr> 
 </tbody> 
</table>

char、varchar可指定字符数、可设置默认值，eg. varchar(5) 最多只能存储5个字符，其它字符串类型不能指定长度、默认值。

char是定长字符串，内容长度不够时自动补空格，适合存储固定长度的字符串，varchar是变长字符串。

#### 日期时间 ####

<table> 
 <thead> 
  <tr> 
   <th align="left">数据类型</th> 
   <th align="left">范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">date</td> 
   <td align="left">yyyy-MM-dd，1000-01-01 ~ 9999-12-31</td> 
  </tr> 
  <tr> 
   <td align="left">datetime</td> 
   <td align="left">yyyy-MM-dd hh:mm:ss ，1000-01-01 00:00:00 ~ 9999-12-31 23:59:59</td> 
  </tr> 
  <tr> 
   <td align="left">timestamp</td> 
   <td align="left">yyyy-MM-dd hh:mm:ss，1970-01-01 00:00:00 ~ 2038-01-19…</td> 
  </tr> 
 </tbody> 
</table>

操作数据库时，使用的就是上面的格式，eg. timestamp

insert into tb_user (create_time) values ('2019-02-02 09:02:20');

增删改是这个格式，查询返回的也是这个格式。可以直接new Date()获取当期的日期时间，Date使用util包下的Date即可。

datetime、timestamp的区别是能表示的范围不同，datetime 8字节，timestamp 4字节，datetime能表示的范围更大。

时间日期类型独有的选项：根据当前时间戳更新。勾选后，如果插入时未设置该字段的值，会自动使用当前时间；每次更新该条记录时，不管有没有手动set该字段的值，该字段都会更新为当前时间。如果要记录的是最近一次操作的时间，可以勾选；其他情况下，都应该手动set设置值。

#### 二进制数据 ####

*  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。