MySQL 17 - 数据库锁

数据库锁

数据库锁：

• 表锁
• 行锁
• 间隙锁

锁是计算机协调多个进程或线程并发访问某一资源的机制

1.表锁

表锁(偏读)

偏向MyISAM存储引擎,开销小,加锁快;无死锁,锁定粒度大,发送锁冲突的概率最高,并发度低

表锁案例
建表

create table mylock(
    id int not null primary key auto_increment,
    name varchar(20)
)engine myisam;

插入数据

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

手动增加表锁

lock table 表名字 read(write),表名字2 read(write);

加上读锁 就只能读不能修改表内容，当前客户端其他表的内容不能查询
加上写锁 当前客户端可以读写，不能查询其他表，其他客户端可以查看其他表，不能查看本表

查看所有锁

show opren tables;

释放表锁

unlock tables;

表锁总结

MyISAM在执行查询语句(select)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁

• 对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其他进行的写操作
• 对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其他进程的读写操作

读锁 会阻塞写 不会阻塞读 写锁会把读和写都阻塞

2. 行锁(偏写)

偏向InnoDB存储引擎,开销大,加锁慢,会出现死锁。锁定粒度最小,发生锁冲突的概率最低,并发度也最高

InnoDB与MyISAM的最大不同点,支持事务,采用了行级锁

行锁案例

create table test_innodb_lock(a int(11),b varchar(16))engine=innodb;

insert into test_innodb_lock values(1,'b2');
insert into test_innodb_lock values(3,'3');
insert into test_innodb_lock values(4,'4000');
insert into test_innodb_lock values(5,'5000');

create index idx_test_innodb_a on test_innodb_lock(a);

create index idx_test_innodb_b on test_innodb_lock(b);

如何分析行锁定

通过检查innodb_row_lock状态变量来分析系统上的行锁争夺情况
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           系统启动后到现在总共等待的次数

3. MySQL操作规范

命名规范

• 表名建议使用有业务意义的英文词汇，必要时可加数字和下划线，并以英文字母开头
• 库、表、字段全部采用小写
  • MySQL 在 Linux 下默认是区分大小写的，而在 Windows 下不区分大小写。因此，防止出现问题，建议都设置为小写。
• 避免用 MySQL 的保留字
• 命名（包括表名、列名）禁止超过 30 个字符
• 临时库、表名必须以 tmp 为前缀，并以日期为后缀，如：tmp_shop_info_20190404
• 备份库、表必须以 bak 为前缀，并以日期为后缀，如：bak_shop_info_20190404
• 索引命名
  • 非唯一索引必须按照"idx_字段名称"进行命名
  • 唯一索引必须按照"uniq_字段名称"进行命名

设计规范

• 主键

  • 表必须有主键
  • 不使用更新频繁的列做主键
  • 尽量不选择字符串列做主键
  • 不使用 UUID MD5 HASH 做主键
  • 默认使用非空的唯一键

• 如无特殊要求，建议都使用 InnoDB 引擎

• 默认使用 utf8mb4 字符集，数据排序规则使用 utf8mb4_general_ci

  • utf8mb4 为万国码，无乱码风险；与 utf8 编码相比，utf8mb4 能支持 Emoji 表情

• 所有表、字段都需要增加 comment 来描述此表、字段所表示的含义

  • data_status TINYINT NOT NULL DEFAULT ‘1’ COMMENT ‘1代表记录有效，0代表记录无效’

• 尽可能不使用 TEXT、BLOB 类型

  • 原因：会浪费更多的磁盘和内存空间，非必要的大量大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能。如果实在有某个字段过长需要使用 TEXT、BLOB 类型，则建议独立出来一张表，用主键来对应，避免影响原表的查询效率。

• 单表列数目建议小于 30

SQL语句规范

• 避免隐式转换
• 尽量不使用select *,只 select 需要的字段
  • 读取不需要的列会增加 CPU、IO、NET 消耗，并且不能有效的利用覆盖索引。使用 SELECT * 容易在增加或者删除字段后导致程序报错
• 建议将子查询转换为关联查询
• 建议应用程序捕获 SQL 异常，并有相应处理

行为规范

• 批量导入、导出数据必须提前通知 DBA 协助观察
• 不在业务高峰期批量更新、查询数据库
• 删除表或者库要求尽量先 rename，观察几天，确定对业务没影响，再 drop