mysql分区表

1、当mysql单表的数据库过大时，数据库的访问速度会下降，“数据量大”问题的常见解决方案是“水平切分”

2、mysql常见的水平切分方式有哪些？答：分库分表，分区表

3、什么是mysql的分库分表？答：把一个很大的库（表）的数据分到几个库（表）中，每个库（表）的结构都相同，但他们可能分布在不同的mysql实例，甚至不同的物理机器上，以达到降低单库（表）数据量，提高访问性能的目的。分库分表往往是业务层实施的，分库分表后，为了满足某些特定业务功能，往往需要修改代码

4、什么是mysql的分区表？答：所有数据逻辑上还在一个表中，但物理存储则是根据一定的规则放在不同的文件中。这个是mysql支持的功能，业务代码无需改动

5、看上去分区表很帅气，为什么大部分互联网还是更多的选择自己分库分表来水平扩展咧？

• 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
• 一旦数据量并发量上来，如果在分区表实施关联，就是一个灾难
• 自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控
• mysql只支持水平分区不支持垂直分区，此外，mysql的分区是局部分区索引，一个分区中既存放了数据又存放了索引。
• 大多数人会有这样一个误区，认为只要启用了分区，数据库性能就会变得更快，其实分区对于某些sql语句性能可能会带来提升，但是分区主要用于高可用性，利于数据库的管理，对于使用分区应该非常小心
• 总之，如果你只是一味的使用分区，而不理解分区是如何工作的，也不清楚你的应用如何使用分区，那么分区极有可能只会对性能产生负面影响

6、分库分表后可以使用数据库中间件如：阿里的mycat，当当的sharding-jdbc等来操作，水平切分规则可以使用一致性哈希算法后方便扩容，哈希算法不太利于扩容

7、下面重点介绍分区表

1、对用户来说，分区表时一个独立的逻辑表，但是底层其实是由多个物理表组成
2、对分区表的请求，都会通过句柄对象转化成对存储引擎的接口调用。所以分区对于SQL层来说是一个完全封装底层实现的黑盒子，插入或者查询的数据最终在哪个分区则不需要我们关心，mysql已经帮我们做了
3、在执行查询的时候，优化器会根据分区时定义的分区字段或者分区方式来过滤哪些没有我们需要数据的分区，这样查询就无需扫描所有分区，只需要查找包含需要数据的分区就可以了。

8、分区原理

分区表是由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区和一个普通表没有任何不同，存储引擎也无须知道这是一个普通表是分区表的一部分。

在分区表上的操作是按照下面的操作逻辑进行：
select 查询：
当查询一份分区表的时候，分区层先打开并锁住所有的 底层表，优化器判断是否可以过滤部分分区，然后再调 用对应的存储接口访问各个分区。
insert操作
当写入一条记录时，分区层打开并锁住所有底层表，然后确定那个分区接受这条记录，再将这条记录写入对应的底层表
delete操作
当删除一条记录是，分区层先打开并锁住所有底层表，mysql先确定对象分区，最后对相应的底层表进行删除操作。
update操作
当更新一条数据时，分区层先打开并锁住所有的底层表，mysql先确定更新的记录再哪个分区，然后取出数据并更新，在判断更新后的数据应该放在哪个分区，然后对底层表进行写入操作，并对原数据的底层表进行删除操作

9、分区限制

A：一个表最多只能有1024个分区（mysql5.6之后支持8192个分区）
B：在mysql5.1中分区表达式必须是整数，或者返回整数表达式，在5.5之后，某些场景可以直接使用字符串和日期类型列进行分区
C：如果分区字段中有主键或者唯一索引列，那么所有主键列和唯一索引列都必须包含进来，如果表中有主键或唯一索引，那么分区键必须是主键或唯一索引。
D：分区表中无法使用外键约束
E：mysql数据库支持的分区类型为水平分区，并不支 持垂直分区，因此，mysql数据库的分区中索引是局部分区索引，一个分区中既存放了数据又存放了索引，而全局分区是指的数据库放在各个分区中，但是所有的数据的索引放在另外一个对象中
F：目前mysql不支持空间类型和临时表类型进行分区。不支持全文索引

10、分区类型

 不论创建何种类型的分区，如果表中存在主键列或者唯一索引时，分区列必须是唯一索引的一个组成部分，不然会创建报错

分区类型	说明	使用频率
range	对连续的值进行分区，例如日期，年月	较高
list	基于列值匹配一个离散值集合中的某个值来进行选择	一般
hash	分散热点读取，确保数据在预定确定个数分区中尽可能的平均分布	较多
key	类似于hash分区，区别在于KEY分区支持只支持计算一列或多列，且MySQL服务器提供自身的哈希函数。必须有一列或多列包含整数值	一般

11、创建分区表

•  分区列只要是唯一索引的一部分即可，不需要所有的唯一索引都是分区列，代码如下
• 
• 

11.分区表效率问题

1、当查询访问分区表的时候，MySQL需要打开并锁住所有的底层表，这是分区表的一个开销。这个操作在分区过滤之前发生，所以无法通过分区过滤降低此开销，并且该开销也和分区类型无关，会影响所有的查询
2、然后根据分区字段（比如使用主键作为分区字段）筛选出来符合条件的分区，然后在筛选出来的分区上在通过索引进行查找数据，最后通过条件筛选出满足的结果集
3、如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤。假设在A列上定义了索引，而在B列上进行分区。因为每个分区都有其独立的索引，所以扫描B列上的索引就需要扫描每一个分区内对应的索引。要避免这个问题，应该避免建立和分区列不匹配的索引，除非查询中还同时包含了可以过滤分区的条件

 12、分区表查询优化

1、分区最大的优点就是优化器可以根据分区函数（分区字段）来过滤一些分区。根据粗粒度索引的优势，通过分区过滤通常可以让查询扫描更少的分区，而不至于查询所有分区。
2、对于访问分区表来说，很重要的一点是要在where条件中带入分区列，有时候即使这个例很多余的也要带上，这样就可以让优化器能够过滤掉无需访问的分区。如果没有这些条件，MySQL就需要让对应存储引擎访问这个表的所有分区，如果表非常大的话，就可能会非常慢。

13、查看数据库分区情况

1、 show create table       可以查看创建分区表的create语句
2、show table status        查看某张表是不是分区表
3、show variables like '%partition%' 查看数据库是否启用了分区功能
4、 explain partitions select语句       通过此语句来显示扫描哪些分区，及他们是如何使用的