mysql 分表/分区

一个数据表里边可以存储许多记录信息，如果一个数据表里边存储的数据非常多(例如 淘宝商城 的商品表)，这样该商品表的相关工作量就很多(数据的增、删、改、查)

负载(工作量)高到一定程度，会造成把表锁死的情况发生。
为了降低商品表的负载/工作量，可以给该表拆分为多个数据表。这样每个数据表的工作量会有多降低。
Mysql5.1版本之后就支持分表分区的设计。
宏观拆分可以如下：
Goods数据表需要拆分：Goods_1 Goods_2 Goods_3…Goods_10

数据表拆分为以后，需要考虑php如何操作这些数据表。
php------------([手动/mysql]算法)--------------数据表(分表)

手动算法：需要在php语言里边设计操作逻辑，增加php语言的代码工作量
mysql算法：php语言不需要做额外操作就可以像以往一样操作同一个数据表的不同分区，是mysql分表推荐的方式

1.创建一个”分表/分区”数据表
Myisam和innodb数据表都可以做分表设计
推荐使用Myisam

设计分区的字段，需要是主键的一部分。

创建一个有10个分区的goods数据表：

可以看到goods数据表有10个分区：

上图每个分区表 都独立的*.MYD数据文件和*.MYI索引文件
给该表存放信息，信息会平均分摊到各个数据表里边。
2. 四种分表分区算法
各种分区设计关联的字段必须是主键的一部分
或者是主键本身、或者是复合主键索引的从属主键部分
求余：
key 根据指定的字段进行分区设计
hash 根据指定的表达式进行分区设计
条件：
range 字段/表达式 符合某个条件范围的分区设计
list 字段/表达式 符合某个列表范围的分区设计
2.1 key的分区算法

2.2 hash分区算法
根据指定的表达式进行分区设计

设计分区的时候，分区字段必须是主键的一部分：



给数据表写入数据，数据会根据“月份”添加到对应的分区表中：


2.3 range() 分区算法
range 条件范围分区


数据添加的时候根据年份，写入到对应的分区：


2.4 list 分区算法


key：该方式区分不明显(不一定会严格平均给分区分配数据)，但是大方向明显
hash/range/list:会根据业务特点把数据写入到对应的分区表里边。
3. 管理分区
增加、减少分区
3.1 求余(key、hash)算法管理
增加分区：alter table 表名 add partition partitions 数量;
减少分区：alter table 表名 coalesce partition 数量;
减少分区数据要丢失



3.2 条件(range、list)算法管理
增加分区：
alter table 表名 add partition(
partition 分区名 values less than[in] (常量[列表]),
partition 分区名 values less than[in] (常量[列表]),
…
)
减少分区：
alter table 表名 drop partition 分区名称;
减少分区，会丢失对应分区的数据。
增加：

减少：

4. 物理分表设计
该分表是纯”物理分表”
Goods: Goods_1、Goods_2、Goods_3、Goods_4、Goods_5
该物理分表需要通过php算法，实现数据平均分配给每个表存储。

创建5张物理表(表内部结构完全一致)：


通过php的算法，实现给分表进行数据的增、删、改、查操作：


五.垂直分表
对记录进行分割并存储到许多不同的表，称为“水平分表”
对字段进行分割并存储到许多不同表，称为“垂直分表”

一个数据表，内部有许多字段，有的字段频繁被操作，有的字段很少被操作。
这样当操作数据表中一些字段的时候，没有直接业务关系的字段也需要给其分配相应的资源，这样速度会稍慢，还要消耗系统额外的工作量。
例如数据表（student）有如下字段：
id 登录名 名称 密码 生日 身高 体重 手机号码 qq号码 简介 城市
student_zhu： id 登录名 密码 手机号码
student_fu： id 名称 生日 身高 体重 qq号码 简介 城市
以上两个数据表(student_zhu、student_fu)，其中zhu表需要被频繁操作。
后期数据维护需要同时维护两个数据表
insert into student_zhu values (…)
生成一个zhu的id信息$zhuid
那么就：insert into student_fu values ($zhuid)

六．架构(集群)设计
一个mysql服务器的操作分为：增、删、改、查
其中查询操作最为频繁：查询/写入 = 7/1
查询操作本身还最消耗资源

架构设计：原先有一个mysql服务器做的工作现在平摊给多个mysql服务器实现。
多个数据库设计与Redis的分布式设计雷同：
主从模式(一主多从/读写分离)

安装多个服务器(多个mysql服务器)
负载均衡 软件
主mysql 给 从mysql同步数据
七．慢查询日志
系统运行起来，内部需要执行许多sql语句
此时要把查询速度很慢的sql语句给统计出来，并做优化设计。
设定一个时间阀值，超过该时间，就说明sql语句很慢。

开启慢查询日志：


设置时间阀值(set后边没有global)：