mysql数据库学习之锁操作和分区

数据库锁

数据库的锁机制，是数据库的一种自我保护，是数据库安全的保证，同时也会对数据库性能带来一些影响。

查看数据库mysql中表锁定状态的sql语句

use mysql;
show open tables;

结果中字段 In_use为1时，表示改行对应的表处于锁定状态。

手动为表 servers 加读锁，如加写锁则用write替换read。
lock table servers read;

解除锁定
unlock tables;

锁的分类
根据数据库（表）所采用的储存引擎的不同，操作数据库时触发的锁也会不同。

表锁

对应MyIsam引擎的一类锁，偏读操作，分为读锁（对应查询操作）和写锁（对应增、删、改操作）。

特征：优点是锁开销小，加锁快，无死锁，缺点是锁定粒度大（整个表），发生锁冲突的概率高，并发度低。

读锁：不会阻塞其他（客户端）进程对同一（锁定）表的读请求，但会阻塞对同一表的写请求，只有当读锁释放后，才会执行其他进行的写操作。

写锁：会阻塞其他（客户端）进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程 的读写操作。

行锁

对应InnoDB引擎的一类锁，偏写操作。
特点：加锁开销大，加锁慢，会出现死锁，优点是锁定粒度最小，发生锁冲突的概率最低，并发度高。

行锁定状态查看
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：系统启动后到现在总共等待的次数，重点关注

手动添加行锁
select * from mysql.user where id = 1 for update;

间隙锁

同样存在于InnoDB引擎的一类锁，当使用范围条件检索数据，并请求共享或排他锁时，innodb会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做"间隙"，innodb会对这个"间隙"加锁，这种锁机制就是所谓的间隙锁。

举例：
范围条件
where id >1 and id < 5;
当前表中id实际存在的值：1，2，3，5，则4为间隙值，当执行上述范围查询时，对于id=4的行进行的写操作会被阻塞。

间隙锁的危害
当锁定一定范围键值之后，即使某些不存在的键值也会被无辜的锁定，造成在锁定的时候无法插入锁定键值范围内的任何数据。

数据库分区表

分区表，是按照一定的规则（分区键，即用于分区的某个字段）将原本保存在一张表的数据分别存储在不同的分区文件中。

分区原则：不能使用主键或者唯一字段之外的其他字段进行分区，也就是：除非没有主键或者唯一字段，否则必须使用主键或者唯一字段进行分区。

分区类型：RANGE分区，LIST分区，HASH分区。

RANGE分区举例

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

上面根据login_id字段创建了4个分区，p0分区：0-9999，p1分区：10000-19999，p2分区：20000-29999，p3分区：30000-无穷大。
查看数据库的data目录下对应数据库名目录中，会发现4个存储该表数据的文件。

查询分区

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

添加或 修改分区
alter table test_range add partition (partition p4 values less than(40000));

删除某个分区
alter table test_range drop partition p0;

HASH分区举例

HASH分区的键值必须是一个INT类型的值，或是通过函数可以转为INT类型
数据可以平均的分布在各个分区中。

通过hash分为4个区：

create table `test_hash`(
login_id int(10) unsigned not null,
login_time timestamp not null default CURRENT_TIMESTAMP, 
login_ip int(10) unsigned not null 
) engine=innodb
partition by hash(login_id) partitions 4;

LIST分区举例
按分区键取值的列进行分区，同范围分区一样，各分区的列值不能重复，每一行数据必须能找到对应的分区列，否则数据插入失败。
可以看作类似 外键约束 或者 enum类型数据的限制。

分区为两个list分区：

create table `test_list`(
login_id int(10) unsigned not null,
login_time timestamp not null default CURRENT_TIMESTAMP, 
login_type int(10) unsigned not null 
) engine=innodb
partition by hash(login_type)(
partition p0 values in(1,3,5), 
partition p1 values in(2,4,6)
);

分区数据的迁移

也叫数据归档，具体过程：

1、建立与目标数据表（test_range）相同结构的表 t1

create table `t1`(
login_id int(10) unsigned not null,
login_time timestamp not null default CURRENT_TIMESTAMP, 
login_ip int(10) unsigned not null 
) engine=innodb;

2、迁移分区数据

alter table test_range exchange partition p0 with table t1;