mysql高性能—分区表

1、分区表

1）是什么：

mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。

2）分区表语法：

分区表分为RANGE,LIST,HASH,KEY四种类型,并且分区表的索引是可以局部针对分区表建立的。

实例：

（1）使用PARTITION方式：

CREATETABLE sales (

    id INT AUTO_INCREMENT,

    amount DOUBLE NOT NULL,

    order_day DATETIME NOT NULL,

    PRIMARY KEY(id, order_day)

)ENGINE=Innodb PARTITION BY RANGE(YEAR(order_day)) (

    PARTITION p_2010 VALUES LESS THAN (2010),

    PARTITION p_2011 VALUES LESS THAN (2011),

    PARTITION p_2012 VALUES LESS THAN (2012),

    PARTITION p_catchall VALUES LESS THANMAXVALUE);

这段语句表示将表内数据按照order_dy的年份范围进行分区,2010年一个区,2011一个,2012一个,剩下的一个.要注意如果这么做,则order_day必须包含在主键中,且会产生一个问题,就是当年份超过阈值,到了2013,2014时,需要手动创建这些分区

（2）使用hash方式：

CREATETABLE sales (

    id INT PRIMARY KEY AUTO_INCREMENT,

    amount DOUBLE NOT NULL,

    order_day DATETIME NOT NULL

)ENGINE=Innodb PARTITION BY HASH(id DIV 1000000);

 

这种分区表示每100W条数据建立一个分区,且没有阈值范围的影响

 

3）对于大数据而言

 

　　对于大数据(如10TB)而言,索引起到的作用相对小,因为索引的空间与维护成本很高,另外如果不是索引覆盖查询,将导致回表,造成大量磁盘IO.那么对于这种情况的解决策略是:

 

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

 

　　通过分区表表达式将数据定位在少量的分区表,然后正常访问这些分区表的数据

 

　　2.分离热点,索引数据

 

　　将热点数据分离出来在一个小的分区,并对分区建立索引,对热点数据的查询提高效率.

4）分区表的问题:

 

　　1.NULL值使分区过滤无效

 

　　假设按照RANGE YEAR(order_date)分区,那么如果这个表达式计算出来的时NULL值,记录就会被存放到第一个分区.所以在查询时加入查询条件有可能出现NULL值,那么就会去检查第一个分区.解决的方法可以是将第一个分区建立为NULL分区 PARTITIONp_nulls VALUES LESS THAN (0),或者在MySQL5.5以后,直接使用COLUMN建立分区 PARTITION BY RANGE COLUMNS(order_date)

 

　　2.选择分区的成本

 

　　每插入一行数据都需要按照表达式筛选插入的分区地址

 

　　3.分区列和索引列不匹配

 

　　如果索引列和分区列不匹配,且查询中没有包含过滤分区的条件,会导致无法进行分区过滤,那么将会导致查询所有分区.

 

　　4.打开并锁住所有底层表

 

　　分区表的的查询策略是在分区过滤之前,打开并锁住所有底层表,这会造成额外的开销,解决问题的方法是尽量使用批量操作,例如LOAD DATA INFILE,或者一次删除多行数据.

 

5）过滤分区表的要点

 

　　过滤分区表的WHERE条件必须是切分分区表的列,而不能带有函数,例如只能是order_day,而不能是YEAR(order_day)

 

6）其他知识点：

·表分区与分表的区别

 

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。

分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

 

· 表分区有什么好处？

 

1）分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。

2）和单个磁盘或者文件系统相比，可以存储更多数据

3）优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。

4）分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。

5）可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问，ext3问价你系统的inode锁竞争等。

 

4. 分区表的限制因素

 

1）一个表最多只能有1024个分区

2）MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

3）如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

4）分区表中无法使用外键约束

5）MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

 

5. 如何判断当前MySQL是否支持分区？

命令：show variables like'%partition%' 运行结果:

mysql> show variableslike '%partition%';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| have_partitioning |YES   |

+-------------------+-------+

1 row in set (0.00 sec)

have_partintioning的值为YES，表示支持分区。

 

6. MySQL支持的分区类型有哪些？

 

1）RANGE分区：按照数据的区间范围分区区间要连续并且不能互相重叠。（存在问题：当插入的记录中对应的分区键不在分区定义的范围中的时候，插入语句失败）

2）LIST分区：建立离散的值列表告诉数据库特定的值属于哪个分区，与RANGE的区别是，range分区的区间范围值是连续的。

3）HASH分区：Hash分区主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。 MySQL支持两种Hash分区:常规Hash分区和线性Hash分区。 A.常规Hash分区:使用取模算法 线性Hash分区：分区函数是一个线性的2的幂的运算法则。

4）Columns分区MySQL5.5中引入的分区类型，解决了5.5版本之前range分区和list分区只支持整数分区的问题。 Columns分区可以细分为 rangecolumns分区和 list columns分区，他们都支持整数，日期时间，字符串三大数据类型。（不支持text和blob类型作为分区键）columns分区还支持多列分区（这里不详细展开）。

5）Key分区：类似Hash分区，Hash分区允许使用用户自定义的表达式，但Key分区不允许使用用户自定义的表达式。Hash仅支持整数分区，而Key分区支持除了Blob和text的其他类型的列作为分区键。

 

7、分区对于null值的处理

 

MySQ允许分区键值为NULL，分区键可能是一个字段或者一个用户定义的表达式。一般情况下，MySQL在分区的时候会把NULL值当作零值或者一个最小值进行处理。

 

注意

Range分区中：NULL值被当作最小值来处理

List分区中：NULL值必须出现在列表中，否则不被接受

Hash/Key分区中：NULL值会被当作零值来处理