一、磁盘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));
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
![](https://img-blog.csdn.net/20150507205642691)
解决办法:
mysql> grant execute on procedure test.load_part_tab to ''@'localhost';
> flush privileges;