Mysql分区实验

不同分区技术的对比
range    适合与日期类型，支持复合分区， 有限的分区 ，一般只针对某一列
list     适合与有固定取值的列，支持复合分区   ，有限的分区，插入记录在这一列的值不值list中，则数据丢失，一般只针对某一列
Hash 线性Hash的数据分布不均匀，而一般Hash的数据分布较均匀 ，一般只针对某一列
key，列可以为字符型等其他非int类型，效率较之前的低，因为函数为复杂的函数（如md5或SHA函数），
     一般只针对某一列


show engines;
show plugins;
create table t1(id int);
show create table t1;


mysql> create table t2( id int )  engine=myisam partition by hash(id) partitions 5;
mysql> show create table t2;
[root@localhost test]# ls -l
total 52
-rw-rw---- 1 mysql mysql 8556 Jul  2 09:58 t1.frm
-rw-rw---- 1 mysql mysql    0 Jul  2 09:58 t1.MYD
-rw-rw---- 1 mysql mysql 1024 Jul  2 09:58 t1.MYI
-rw-rw---- 1 mysql mysql 8556 Jul  2 10:15 t2.frm
-rw-rw---- 1 mysql mysql   40 Jul  2 10:15 t2.par
-rw-rw---- 1 mysql mysql    0 Jul  2 10:15 t2#P#p0.MYD
-rw-rw---- 1 mysql mysql 1024 Jul  2 10:15 t2#P#p0.MYI
-rw-rw---- 1 mysql mysql    0 Jul  2 10:15 t2#P#p1.MYD
-rw-rw---- 1 mysql mysql 1024 Jul  2 10:15 t2#P#p1.MYI
-rw-rw---- 1 mysql mysql    0 Jul  2 10:15 t2#P#p2.MYD
-rw-rw---- 1 mysql mysql 1024 Jul  2 10:15 t2#P#p2.MYI
-rw-rw---- 1 mysql mysql    0 Jul  2 10:15 t2#P#p3.MYD
-rw-rw---- 1 mysql mysql 1024 Jul  2 10:15 t2#P#p3.MYI
-rw-rw---- 1 mysql mysql    0 Jul  2 10:15 t2#P#p4.MYD
-rw-rw---- 1 mysql mysql 1024 Jul  2 10:15 t2#P#p4.MYI


往t2表里插入1000万行数据
insert into t2 select * from t2;


ll -h
watch -n1 ls -lh  时时监控
total 7.6M
-rw-rw---- 1 mysql mysql 8.4K Jul  3 06:58 t2.frm
-rw-rw---- 1 mysql mysql   40 Jul  3 06:58 t2.par
-rw-rw---- 1 mysql mysql 448K Jul  3 07:09 t2#P#p0.MYD
-rw-rw---- 1 mysql mysql 1.1M Jul  3 07:09 t2#P#p0.MYI
-rw-rw---- 1 mysql mysql 448K Jul  3 07:09 t2#P#p1.MYD
-rw-rw---- 1 mysql mysql 1.1M Jul  3 07:09 t2#P#p1.MYI
-rw-rw---- 1 mysql mysql 448K Jul  3 07:09 t2#P#p2.MYD
-rw-rw---- 1 mysql mysql 1.1M Jul  3 07:09 t2#P#p2.MYI
-rw-rw---- 1 mysql mysql 448K Jul  3 07:09 t2#P#p3.MYD
-rw-rw---- 1 mysql mysql 1.1M Jul  3 07:09 t2#P#p3.MYI
-rw-rw---- 1 mysql mysql 448K Jul  3 07:09 t2#P#p4.MYD
-rw-rw---- 1 mysql mysql 1.1M Jul  3 07:09 t2#P#p4.MYI


myisam表引擎增删改查是非常快的 比innodb表快
myisam宁可牺牲掉数据库里其他的功能，也要为增删改查提速，这是myisam最大的优势


top 命令查看cpu的使用使用情况


free -m查看内存的使用情况




一千万行的记录表t2  id为主键
select count(*) from t2   //查看速度是很快的，因为mysql早就对这些数据做了优化
select count(*) from t2 where id>5 //这个的查询速度是很慢的
select count(id) from t2;//这行的速度也比较慢


t2表只有id字段 一千万行的数据 总大小大约为 241M


改变语句终结符
mysql>\d //
创建存储过程 
mysql> create procedure p3()
    -> begin
    -> set @i=1;
    -> while @i<10000 do
    -> insert into t3 values(@i);
    -> set @i=@i+1;
    -> end while;
    -> end //
Query OK, 0 rows affected (0.00 sec)


mysql> select count(*) from t3;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)


mysql> call p3() //
Query OK, 0 rows affected (0.91 sec)


mysql>show procedure status;
mysql>show create procedure p3;


mysql>drop procedure p3;




range分区
mysql> create table part_tab (c1 int default null,c2 varchar(30) default null,c3 date default null) engine=myisam
    -> 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);
Query OK, 0 rows affected (0.13 sec)


mysql> create table no_part_tab (c1 int(11) default null,c2 varchar(30) default null,c3 date default null) engine=myisam;




mysql>\d //
mysql> create procedure load_part_tab()
    -> begin
    -> declare v int default 0;
    -> while v<8000000
    -> do
    -> insert into par_tab
    -> values(v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 36520));
    -> set v = v + 1;
    -> end while;
    -> end
    -> //


mysql>show procedure status;
mysql>call load_part_tab();




[root@localhost test]# watch -n1 ls -lh
[root@localhost ~]# free -m
[root@localhost ~]#top
Cpu(s): 60.6%us


mysql> select count(*) from par_tab;
mysql>insert into no_part_tab select * from part_tab;  //insert into ... select ... 这样的语句比单行插入的要快


mysql>select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'  耗时1.95s
mysql>select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31' 耗时8.74s
mysql> desc select count(*) from par_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: par_tab
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25084
        Extra: Using where
1 row in set (0.00 sec)
mysql> explain select count(*) from par_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: par_tab
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25084
        Extra: Using where
1 row in set (0.00 sec)


innodb表数据结构
对innodb的数据表结构，首先要解决两个概念性的问题:共享表空间及独占表空间
什么是共享表空间和独占表空间
共享表空间及独占表空间都是针对数据的存储方而言的。
共享表空间：某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认
这个共享表空间的文件路径在data目录下，默认的文件名为ibdata1 初始化为10M


注:目前测试发现不同的数据库下，索引数据和表数据都存储在了一个文件ibdata1文件里面
-rw-rw---- 1 mysql mysql 8.4K Jul  4 07:23 t3.frm
-rw-rw---- 1 mysql mysql 8.4K Jul  4 07:23 t4.frm


[root@localhost test]# ls -lh ../ibdata1 
-rw-rw---- 1 mysql mysql 26M Jul  4 08:13 ../ibdata1


独占表空间：每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm
表描述文件，还有一个.idb文件。其中这个文件包括了单独一个表的数据内容以及索引内容，
默认情况下它的存储位置也是在表的位置之中


innodb_file_per_table=1    是否使用共享目录及独占目录 1表示独占表空间


开启独占空间
[root@localhost test]# ls -lh
-rw-rw---- 1 mysql mysql 8.4K Jul  4 08:13 t5.frm
-rw-rw---- 1 mysql mysql  96K Jul  4 08:13 t5.ibd
-rw-rw---- 1 mysql mysql 8.4K Jul  4 08:13 t6.frm
-rw-rw---- 1 mysql mysql  96K Jul  4 08:13 t6.ibd


只有独占表空间才能实现分区技术




mysql> create table t7(
    -> id int)
    -> engine=innodb
    -> partition by hash(id)
    -> partitions 5;
Query OK, 0 rows affected (0.84 sec)


mysql> show create table t7;
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| t7    | CREATE TABLE `t7` (
  `id` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PARTITION BY HASH (id) PARTITIONS 5  |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
[root@localhost test]# ls -lh
-rw-rw---- 1 mysql mysql 8.4K Jul  4 08:21 t7.frm
-rw-rw---- 1 mysql mysql   40 Jul  4 08:21 t7.par
-rw-rw---- 1 mysql mysql  96K Jul  4 08:22 t7#P#p0.ibd
-rw-rw---- 1 mysql mysql  96K Jul  4 08:22 t7#P#p1.ibd
-rw-rw---- 1 mysql mysql  96K Jul  4 08:22 t7#P#p2.ibd
-rw-rw---- 1 mysql mysql  96K Jul  4 08:22 t7#P#p3.ibd