mysql分区

一、磁盘IO

假设  user表 1个G 线性查询(相当于全表扫描)

索引 1个G 

树结构  B+tree （支和高度）  是一种逻辑算法

1次IO大约需要9毫秒

二、大表拆分成小表

(1)垂直分表

(2)水平分表      目的都是为了减少表的大小   在物理上   大表分为几个小表   逻辑上  语法:    php路由、维护代价过高查找非常麻烦

例如:一张user表分成10个表user1、user2........user10

user利用%的方法求余数将余数为几的存在user几的表中

路由:定位------->hash算法

维度==条件

三、分区

必须在mysql5.1以上的版本

5种分区，四种被认可

1.RANGE分区:

基于属于一个给定连续区间的列值，把多行分配给分区。

用RANGE分区，将20家音像店职员记录，编号1-20,分成4个分区

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date,

salary int

)

partition by range(salary)

(

partition p1 values less than (1000),

partition p2 values less than (2000),

partition p3 values less than maxvalue ,

);

.frm  表的结构 (字段)  作用是为了校验

.par 相当于路由   作用是为了定位分区

MYD DATA数据

MYI INDEX  索引

存储引擎  MYISAM

大致流程:

sql语句---校验--->.frm---判断--->.par------>{走区间、减少时间

2.LIST分区

类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

枚举类型:固定值不变的

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by list(deptno)

(

partition p1 values in (10),

partition p2 values in (20),

partition p3 values in (30)

);

3.HASH:平均分配

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。

预先确定平均分布

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by hash(year(birthdate))

partitions 4;

4.KEY

类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值

可以不是整形

create table emp

(empno varchar(20) not null ,

empname varchar(20),

deptno int,

birthdate date not null,

salary int

)

partition by key(birthdate)

partitions 4;

四、 分区表的管理操作

删除分区:

alter table emp drop partition p1;

增加分区:

alter table emp add partition (partition p3 values less than (4000));

alter table empl add partition (partition p3 values in (40));

五、获取mysql分区表信息的几种方法

1. show create table 表名

可以查看创建分区表的create语句

2. show table status

可以查看表是不是分区表

3. 查看information_schema.partitions表

select

  partition_name part,

  partition_expression expr,

  partition_description descr,

  table_rows

from information_schema.partitions where

  table_schema = schema()

  and table_name='test';

可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息

4. explain partitions select语句

通过此语句来显示扫描哪些分区，及他们是如何使用的.

5.show plugins 查看插件

6.? 查询

7.insert into 表名 select * from 表名

8.watch -n1 ls -lh    更新 次/s

六、分区表性能比较

1.   创建两张表: part_tab(分区表),no_part_tab(普通表)

(1)首先创建分区表

CREATE TABLE part_tab

(c1 int default NULL, c2 varchar(30) default NULL, c3 date NOT NULL)

PARTITION BY RANGE(YEAR(c3))

(PARTITION p0 VALUES LESS THAN (1995),

PARTITION p1 VALUES LESS THAN (1996) ,

PARTITION p2 VALUES LESS THAN (1997) ,

PARTITION p3 VALUES LESS THAN (1998) ,

PARTITION p4 VALUES LESS THAN (1999) ,

PARTITION p5 VALUES LESS THAN (2000) ,

PARTITION p6 VALUES LESS THAN (2001) ,

PARTITION p7 VALUES LESS THAN (2002) ,

PARTITION p8 VALUES LESS THAN (2003) ,

PARTITION p9 VALUES LESS THAN (2004) ,

PARTITION p10 VALUES LESS THAN (2010),

PARTITION p11 VALUES LESS THAN (MAXVALUE) );

(2)创建非分区表即普通表

CREATE TABLE no_part_tab

(c1 int default NULL, c2 varchar(30) default NULL, c3 date NOT NULL);

2.用存储过程插入800万条数据

(1)向分区表中插入数据

CREATE PROCEDURE load_part_tab()

    begin

    declare v int default 0;

    while v <= 8000000

    do

        insert into part_tab

        values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652));

         set v = v + 1;

    end while;

end;

(1)向普通表中插入数据

insert into no_part_tab  select * from part_tab;

3. 测试sql性能

查询分区表：

select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';

+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (2.62 sec)

查询普通表：

selectcount(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';

+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (7.33 sec)

分区表的执行时间比普通表少70%。

4.通过explain语句来分析执行情况

七、ERROR

解决办法:

mysql> grant execute on procedure test.load_part_tab to ''@'localhost';

> flush privileges;