• 周四. 12 月 12th, 2024

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

MySQL 分区知识点(二)

admin

11 月 28, 2021

前言:

  MySQL 5.1+ 版本就开始支持分区功能了。

  分区本质上就是在物理文件层面划分了多个物理子表来支撑,或者说是一组底层表的句柄对象的封装。

  对于分区表的请求,都是通过句柄对象转化成对存储引擎的接口调用。

  从底层的文件系统就可以看出来,使用了 # 分割的命名表文件,就是分区表;

  ls /home/mysql/data/mysql/  # 可以查看到

  

  

  什么场景使用分区才能起到非常大的作用:

  1、表非常大以至于无法全部都放在内存中;(被挤出内存,MySQL 的缓存不起作用了)

  2、分区表的数据容易维护的。

  3、分区表的数据可以分布在不同物理设备上的;

  4、如果需要,可以备份和恢复独立的分区的;

一、分区表原理

  SELECT 查询:

    当查询一个分区表的时候,分区层先打开并锁住所有的底层表,

    优化器先判断是否可以过滤部分分区,然后再调用对应的存储引擎接口访问各个分区的数据。

    break;

  INSERT 操作:
  DELTE 操作:

    当写入一条记录时,分区层先打开并锁住所有底层表,然后确定哪个分区接收这条记录,再写入。

    break;

  UPDATE 操作:

    当更新一条记录时,分区层先锁底层表,然后寻找哪个分区,然后取数据更新。

    更新完成后再看数据应该放在哪个分区,如果跨分区则删除老分区、写入新分区;

  // 所谓的锁住底层表,也是针对不同存储引擎而来的;

二、如何选择性分区

  分区技巧:

  1、可以根据键值进行分区,来减少 InnoDB 的互斥量竞争;

  2、使用数学模函数来进行分区,然后将数据轮询放入不同的分区。

    如,针对日期做模 7 的运算,或者简单使用周几的函数,进行分区;

  3、假设表有一个自增主键列 ID,希望根据时间将最近的热点数据集中存放,

    这种情况可以采用这样的分区表达式来实现:HASH(ID DIV 100000),这将

    为 100 万数据建立一个分区。

  在数据量超大的时候,B-Tree 索引就无法起作用了,除非是索引覆盖查询,否则数据库服务器需要根据索引扫描的结果回表,

  查询所有符合条件的记录,如果数据量巨大,这将产生大量随机 I/O,数据库响应时间将大到不可接受的程度,另外索引维护成本也非常高;   

  分区策略:

  1、全量扫描数据,不要任何索引。

    可以使用简单的分区方式存放表,不要任何索引,根据分区的规则大致定位需要的数据位置。

    只要能够使用 WHERE 条件,将需要的数据限制在少数分区中,则效率很高的。

    当然,也需要做一些简单的运算保证查询的响应时间能够满意需求。

    此策略适用于以正常的方式访问大量数据的时候。

  2、索引数据,并分离热点。

    如果数据有明显的 “热点”,而且除了这部分数据,其他数据很少被访问,那么可以独立出一个分区,

    让这个分区的数据能够有机会缓存在内存中。

  分区的坑:

  1、NULL 值会使分区过滤无效;

  2、分区列和索引不匹配;

    如果定义的索引列和分区列不匹配,会导致查询无法进行分区的过滤。

  3、打开并锁住所有底层表的成本可能很高;

  4、维护分区的成本可能会很高;

发表回复