MySQL 分区知识点（二）

前言：

　　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、维护分区的成本可能会很高；