数据库优化第12讲 - 数据库锁&数据库分区

一、数据库锁

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

数据库锁

• 表锁
• 行锁
• 间隙锁

1.1 表锁

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

手动增加表锁

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

释放表锁

unlock tables;

表锁总结

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

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

1.2 行锁

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

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

如何分析行锁定

通过检查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           系统启动后到现在总共等待的次数

什么是间隙锁

当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,
innodb会给符合条件的已有数据记录的索引项加锁, 对于键值在条件范围内但并不存在的记录,叫做"间隙"

innodb也会对这个"间隙"加锁,这种锁机制就是所谓的间隙锁

间隙锁的危害

• 因为SQL执行过程中通过范围查找的话,他会锁定整个范围内所有的索引值,即使这个键值并不存在
• 间隙锁有一个比较致命的弱点,就是当锁定以为范围键值之后,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

如何锁定一行

select * from test_innodb_lock where a = 8 for update;  

---

二、MySQL分区表

特点：在逻辑上为一个表,在物理上存储在多个文件中

在逻辑上为一个表,在物理上存储在多个文件中

create table `login_log`(
    login_id int(10) unsigned not null comment '登录用户id',
    login_time timestamp not null default current_timestamp,
    login_ip int(10) unsigned not null comment '登录类型'
)engine=innodb default charset=utf8 partition by hash(login_id) partitions 4;

分区键

分区引入了分区键的概念,分区键用于根据某个区间值、特定值、或者HASH函数值执行数据的聚集,让数据根据规则分布在不同的分区中。

分区类型

• RANGE分区
• LIST分区
• HASH分区

无论那种分区类型,要么分区表上没有主键/唯一键,要么分区表的主键/唯一键都必须包括分区键,也就是说不能使用主键/唯一字段之外的其他字段分区

2.1 RANGE分区

分区特点：

• 根据分区键值的范围把数据行存储到表的不同分区中
• 多个分区的范围要连续,但是不能重叠
• 分区不包括上限,取不到上限值

RANGE分区使用场景：

• 分区键为日期或是时间类型
• 经常运行包含分区键的查询,MySQL可以很快的确定只有某一个或某些分区需要扫描,例如检索商品login_id小于10000的记录数,MySQL只需要扫描p0分区即可
• 定期按分区范围清理历史数据

建立RANGE分区

create table `login_log_range`(
    login_id int(10) unsigned not null comment '登录用户ID',
    login_time timestamp not null default CURRENT_TIMESTAMP,
    login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb 
partition by range(login_id)(
partition p0 values less than(10000),   # 实际范围0-9999
partition p1 values less than(20000),   # 实际范围10000-19999
partition p2 values less than(30000),
partition p3 values less than maxvalue  # 存储大于30000的数据
);

2.2 HASH分区

特点：

• 根据MOD(分区键,分区值)的值把数据行存储到表的不同分区内
• 数据可以平均的分布在各个分区中
• HASH分区的键值必须是一个INT类型的值,或是通过函数可以转为INT类型

如何建立HASH分区表

create table `login_log`(
    login_id int(10) unsigned not null comment '登录用户ID',
    login_time timestamp not null default CURRENT_TIMESTAMP,
    login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb default charset=utf8 partition by hash(login_id) partitions 4;

create table `login_log`(
    login_id int(10) unsigned not null comment '登录用户ID',
    login_time timestamp not null default CURRENT_TIMESTAMP,
    login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb default charset=utf8 partition by hash(UNIX_TIMESTAMP(login_time)) partitions 4;

2.3 LIST分区

特点：

• 按分区键取值的列表进行分区
• 同范围分区一样,各分区的列表值不能重复
• 每一行数据必须能找到对应的分区列表,否则数据插入失败

建立LIST分区

create table `login_log_list`(
    login_id int(10) unsigned not null comment '登录用户ID',
    login_time timestamp not null default CURRENT_TIMESTAMP,
    login_ip int(10) unsigned not null comment '登录ip',
    login_type int(10) not null
)engine=innodb 
partition by list(login_type)(
partition p0 values in(1,3,5,7,9),    
partition p1 values in(2,4,6,8)   
);

如何选择合适的分区方式

业务场景

1. 用户每次登陆都会记录到日志表中
2. 用户登录日志保存一年,一年后可以删除

create table `login_log_range`(
    login_id int(10) unsigned not null comment '登录用户id',
    login_time datetime  not null default current_timestamp,
    login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb 
partition by range(year(login_time))(
partition p0 values less than(2015),   
partition p1 values less than(2016),   
partition p2 values less than(2017)   
);

插入数据

insert into login_log_range values
(1,'2015-01-25',1),
(2,'2015-07-25',2),
(3,'2015-06-25',3),
(4,'2016-03-25',2),
(5,'2016-02-25',1);

查询表

select table_name,partition_name,partition_description,table_rows from 
information_schema.`partitions` where table_name = 'login_log_range'

修改分区-添加分区

alter table login_log_range add partition (partition p4 values less than(2018))

分区删除

alter table login_log_range drop partition p0;

使用分区表的注意事项

• 结合业务场景选择分区键,避免跨分区查询
• 对分区表进行查询最好在where从句中包含分区键
• 具有主键或唯一索引的表,主键或唯一索引必须是分区键的一部分