linux技术--MySQL分区

# 分区方案 

MySQL的分区技术不同与之前的分表技术，它与水平分表有点类似，但是它是在逻辑层进行的水平分表，对与应用程序而言它还是一张表，MySQL5.1有4种分区类型:

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

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

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

　　KEY分区:类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数

例1：假定你创建了一个如下的表，该表保存有20家音像店的职员记录，这20家音像店的编号从1到20。如果你想将其分成4个小分区，那么你可以采用RANGE分区，创建的数据库表如下:


例2：假定你创建了一个如下的表，该表保存有20家音像店的职员记录，这20家音像店的编号从1到20。你想把不同时期离职的员工进行分别存储，那么你可以将日期字段separated（即离职时间）作为一个key，创建的SQL语句如下

        

这样你就可以对一个日期类型的字段调用mysql的日期函数YEAR()转换为一种整数类型，从而可以作为RANGE分区的key。这个时候你可以看到，分区后的物理文件是相对独立的

    

## LIST分区

LIST分区与RANGE分区有类似的地方，举个与例1类似的例子如下：

例3：假定你创建了一个如下的一个表，该表保存有20家音像店的职员记录，这20家音像店的编号从1到20。而这20个音像店，分布在4个有经销权的地区，如下表所示:    

那么你可以采用如下的LIST分区语句创建数据表:

     

同样，它在物理文件上也会标识不同的分区

·

## HASH分区 

HASH分区主要用来确保数据在预先确定数目的分区中平均分布。它可以基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算

例4：假定你创建了一个如下的一个表，该表保存有20家音像店的职员记录，这20家音像店的编号从1到20。你想把不同时期加入的员工进行分别存储，那么你可以将日期字段hired（即入职时间）作为一个key，创建的SQL语句如下:

··

## KEY分区

与HASH分区类似，但它的key可以不是整数类型，如字符串等类型的字段。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的哈希函数，这些函数是基于与PASSWORD()一样的运算法则 

## 不同分区技术的对比     

## Mysql分区实例

```shell
创建分区表,按日期的年份拆分
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 ); 
注意最后一行，考虑到可能的最大值

```

### 未分区表

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

```

```shell

通过存储过程灌入800万条测试数据
mysql> delimiter //   /* 设定语句终结符为 //，因存储过程语句用;结束 */
mysql>
CREATE PROCEDURE load_part_tab()
       begin
    declare v int default 0;
    while v < 8000000
    do
        insert into part_tab
        values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
         set v = v + 1;
    end while;
    end
    // 
mysql> delimiter ;

```

## 执行load_part_tab存储

```
mysql> call load_part_tab();
Query OK, 1 row affected (8 min 17.75 sec)
mysql> insert into no_part_tab select * from part_tab;
Query OK, 8000000 rows affected (51.59 sec)
Records: 8000000 Duplicates: 0 Warnings: 0

```

## 测试SQL性能

```shell
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';      
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (0.55 sec)
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'; 
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (4.69 sec)
结果表明分区表比未分区表的执行时间少90% 
```

## explain 或者 desc 分析mysql代码 为什么会慢  查看sql语句的问题 

```shell
mysql > explain select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G
/* 结尾的\G使得mysql的输出改为列模式 */                    
*************************** 1. row ***************************
           id: 1
select_type: SIMPLE  
        table: no_part_tab  #表名 
         type: ALL          #全局扫描 
possible_keys: NULL           #可能使用到的索引
          key: NULL           #实际使用到的索引
      key_len: NULL
          ref: NULL      
         rows: 8000000     #影响的行数  
        Extra: Using where  #实用的where条件  
1 row in set (0.00 sec)

```

```
mysql>desc select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G 
*************************** 1. row ***************************
           id: 1   
  select_type: SIMPLE 
        table: part_tab  #表名
   partitions: p1   #分区          重点关注 
         type: ALL  #全局扫描 
possible_keys: NULL #可能使用到的索引   重点关注
          key: NULL #实际使用到的索引   重点关注  
      key_len: NULL #索引的长度
          ref: NULL
         rows: 798458 #影响的行数    重点关注  
     filtered: 11.11 
        Extra: Using where #额外使用到的条件   where 条件  

explain语句显示了SQL查询要处理的记录数目

```

### 创建索引后 

```
mysql> create index idx_of_c3 on no_part_tab (c3);
Query OK, 8000000 rows affected (1 min 18.08 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
mysql> create index idx_of_c3 on part_tab (c3);
Query OK, 8000000 rows affected (1 min 19.19 sec)
Records: 8000000 Duplicates: 0 Warnings: 0 

```

### 测试索引后效果

```
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'; 
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (2.42 sec)   /* 为原来4.69 sec 的51%*/   
重启mysql ( net stop mysql, net start mysql)后，查询时间降为0.89 sec,几乎与分区表相同。
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'; 
+----------+
| count(*) |
+----------+
|   795181 |
+----------+
1 row in set (0.86 sec)

           id: 1
  select_type: SIMPLE
        table: part_tab
   partitions: p1
         type: index   #根据索引进行查找
possible_keys: idx_of_c3 #可能使用到的索引
          key: idx_of_c3 #实际使用到的索引
      key_len: 4 #索引长度
          ref: NULL
         rows: 798458 #影响的行数  
     filtered: 98.99
        Extra: Using where; Using index #where 条件  index

```

### 增加未索引字段查询

```shell
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date
'1996-12-31' and c2='hello';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.75 sec)
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < da
te '1996-12-31' and c2='hello';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (11.52 sec)

```

 ```
\d //
create procedure p3()
begin
set @i=@i+1;
while @i<=10000 do
insert into t3 values(@i)；
set @i=@i+1;
end while;
end //
\d ;
show procedure status;

drop procedure p3;

call p3;

清空任何数据 
 ```

```shell
[mysqld]  
#记录sql执行超过2秒的SQL语句  
long_query_time = 2  
#指定记录的文件名  
log-slow-queries=log-slow.txt  
#记录没有使用索引的SQL查询  
log-queries-not-using-indexes  
```

### 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 

## 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： 

select id from t where num is null 
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： 
select id from t where num=0 

## 3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。 

## 4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： 

select id from t where num=10 or num=20 
可以这样查询： 
select id from t where num=10 
union all 
select id from t where num=20 

## 5.in 和 not in 也要慎用，否则会导致全表扫描，如： 

select id from t where num in(1,2,3) 
对于连续的数值，能用 between 就不要用 in 了： 
select id from t where num between 1 and 3 

## 6.下面的查询也将导致全表扫描： 

select id from t where name like '%abc%' 
若要提高效率，可以考虑全文检索。 

## 7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描： 

select id from t where num=@num 
可以改为强制查询使用索引： 
select id from t with(index(索引名)) where num=@num

## 8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如： 

select id from t where num/2=100 
应改为: 
select id from t where num=100*2 

## 9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如： 

select id from t where substring(name,1,3)='abc'--name以abc开头的id 
select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id 
应改为: 
select id from t where name like 'abc%' 
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1' 

## 10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。 

## 11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。 

## 12.不要写一些没有意义的查询，如需要生成一个空表结构： 

select col1,col2 into #t from t where 1=0 
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样： 
create table #t(...) 

## 13.很多时候用 exists 代替 in 是一个好的选择： 

select num from a where num in(select num from b) 
用下面的语句替换： 
select num from a where exists(select 1 from b where num=a.num) 

## 14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。 

## 15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

## 14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。 

## 18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。 

## 20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。 

21.避免频繁创建和删除临时表，以减少系统表资源的消耗。 

## 23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。 

## 25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。 

## 28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。 

## 30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理