实用技巧篇

都说InnoDB好，那还要不要使用Memory引擎

自增主键为什么不是连续的

怎么最快地复制一张表

要不要使用分区表

支持range、hash、list分区，具体参看手册

1.分区表的组织形式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

CREATE TABLE `t` (
  `ftime` datetime NOT NULL,
  `c` int(11) DEFAULT NULL,
  KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
insert into t values('2017-4-1',1),('2018-4-1',1);
  • 物理文件包含一个.frm文件和4个.ibd文件
  • 每个分区对应一个.ibd文件
  • 对于引擎层是4个表
  • 对于Server层是1个表

2.分区表的引擎层行为

  • 注意分区表间隙加锁
  • 手动分表和分区表区别
    • 分区表有server层决定使用哪个分区
    • 手工分表由应用层代码来决定使用哪个分表
    • 引擎层看，没有差别

3.分区策略

第一次访问一个分区表时，MySQL需要把所有分区都访问一遍

典型问题： 一个分区表的分区很多，比如超过1000个，而MySQL启动时，open_files_limit参数使用的是默认值1024 那么访问时大爱所有的文件，导致打开表文件的个数超过上限而报错

通用分区策略（generic partitioning）：MyISAM分区表使用的分区策略，每次访问分区都由server层控制

本地分区策略（native partitioning）：MySQL 5.7.9开始，InnoDB引擎引入，内部自己管理打开分区的行为

4.分区表的server层行为

一个分区就是一个表

1
2
3
4
5
6
7

-- session A 持有整个表的MDL锁
begin;
select * from t where ftime='2018-4-1';

-- session B alter语句被堵住
alter table t truncate partition p_2017;
(blocked)
  • MySQL在第一次打开分区表的时候，需要访问所有的分区；
  • 在server层，认为这是同一张表，因此所有分区共用同一个MDL锁；
  • 在引擎层，认为这是不同的表，因此MDL锁之后的执行过程，会根据分区表规则，只访问必要的分区。

5.分区表的应用场景

优势：对业务透明，业务代码简洁，方便清理历史数据alter table t drop partition …直接删除分区文件，效果跟drop普通表类似

与 delete 语句相比，优势是速度快、对系统影响小

注意：如果有ES索引来监听数据变更，建议先软删除，等ES更新后再 drop，实际情况需看架构影响