Mysql临时表及分区表区别详解

临时表与内存表

内存表，指的是使用Memory引擎的表，建表语法是create table … engine=memory。这种 表的数据都保存在内存"text-align: center">

• 建表语法是create temporary table …。
• 一个临时表只能被创建它的session访问，对其他线程"text-align: center">

  分区key的选择是以“减少跨库和跨表查询”为依据的。如果大部分的语"text-align: center">

  这个进程的进程号是1234，session A的线程id是4，session B的线程id是5。所以session A和session B创建的临时表，在磁盘上的文件"htmlcode">

  ATE	TABLE	`t`	(
  		`ftime`	datetime	NOT	NULL,
  		`c`	int(11)	DEFAULT	NULL,
  		KEY	(`ftime`)
  )	ENGINE=InnoDB	DEFAULT	CHARSET=latin1
  PARTITION	BY	RANGE	(YEAR(ftime))
  Û "text-align: center">
  
  初始化表的时候，只插入了两行数据，sessionA的select语句对ftime这两个记录之间的间隙加了锁，间隙和加锁状态如图：
  
  也就是说，2017-4-1和2018-4-1这两个记录之间的间隙会被锁住，那么sessionB的两条插入语句都应该进入锁等待状态。但是从效果上看，第一个insert语句是可以执行成功的，因为对于引擎来说，p2018和p2019是不同的表，2017的下一个记录不是2018-4-1而是p2018中的supremum，所以在t1时刻索引如图：
  
  由于分区表的规则，sessionA只操作了p2018，sessionB要插入2018-2-1是可以的但要写入2017-12-1要等待sessionA的间隙锁。
  对于MYISAM引擎：
  
  因为在sessionA中，sleep了100秒，由于myisam只支持表锁，所以这条update会锁住整个表t的读，但是结果是，B的第一条语句是可以执行的，第二条语句才进入锁等待状态。
  这是myisam表锁只在引擎层实现的，sessionA加的表锁，是所在p2018上，因此只会堵住分区上执行的查询，落到其他分区的查询不受影响。这样看来，分区表还不错，为什么不用呢，我们使用分区表的一个原因就是单表过大，那么不使用分区表，就要使用手动分表的方式。
  手动分表需要创建t_2017,t_2018,t_2019，也就是找到需要更新的所有分表，依次执行，这和分区表无实质的差别，两者一个由serverceng决定使用哪个分区，一个由应用层代码决定使用哪个分表，因此，从引擎层看无实际差别。其实主要区别是在server层：打开表行为。
  分区策略
  每当第一次访问一个分区表时，mysql需要把所有分区都访问一遍：如果分区很多，比如查过了1000个，mysql启动的时候，open_files_limit默认为1024，那么就会在访问表的时候，由于打开了所有文件，超过了上限而报错。
  mysiam使用的分区策略成为通用分区策略，每次访问分区都是有server层控制。有比较严重的性能问题。
  innodb引擎引入了本地分区策略，是在innodb内部自己管理打开分区的行为。
  分区表的server层行为
  从server层看，一个分区表就是一个表。
  
  虽然B只操作2017分区，但是由于A持有整个表t的mdl锁，导致了B的alter语句被堵住。如果是使用普通分表，不会跟另外一个分表上的查询语句出现MDL冲突。
  小结：
  
    
  mysql在第一次打开分区表的时候，需要访问所有分区
    
  在server层，认为这是同一张表，因此所有分区公用MDL锁
    
  在引擎层，认为这是不同的表，因此在MDL锁之后，会根据分区表规则，只访问必要的分区。
  
  分区表应用场景
  分区表的优势是对业务透明，相对于用户分表来说，使用分区表的业务代码更简洁，分区表可以很方便的清理历史数据。
  alter table t drop partition 操作是删除分区文件，效果跟drop类似，与delete相比，优势是速度快，对系统影响小。
  以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。