MySQL优化大盘点（二）

上一篇文章我们一起探讨了MySQL优化的存储引擎选择，配置参数优化，索引优化等，今天小马就继续接下来的篇幅，来唠嗑一下MySQL优化剩下的一些招式。

表分区

谈起分表可能有些小伙伴还可能没用上，但对于表分区，大部分人应该并不陌生。我们先来看百科多分区表的定义：

分区表是将大表的数据分成称为分区的许多小的子集，类型有FAT16,FAT32,NTFS,exFAT,Ext2/3/4,HFS+,VMFS。另外，分区表的种类划分主要有：range（范围）、list（列表）和hash（散列）分区。划分依据主要是根据其表内部属性。同时，分区表可以创建其独特的分区索引。倘若硬盘丢失了分区表，数据就无法按顺序读取和写入，导致无法操作。

来看一则关于表分区的介绍，小马觉得非常易懂。

那么MySQL该如何实现表分区？先来看MySQL支持的分区的类型有哪些。

1、RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。常用的比如按某一时间范围分区；

2、LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择；小马比较少用。

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

4、KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

值得一提的是，在以往的经验中通常对用来分区的字段，是整形的则使用hash分区，如PARTITION BY HASH (uId) PARTITIONS 10；是字符串型则使用KEY分区，如PARTITION BY KEY (openid) PARTITIONS 11。一般情况下如果对字符串型的列进行hash分区的话会直接报错。

又到了埋彩蛋的时间了，这里注意到KEY分区为什么是11呢不是10呢？以为有些大神分析，当key分区为合数的时候，会出现散列分布不均匀的问题，所以最好建议是质数分区数。

好了，至于具体如何实现的话，创建表分区语句可以网上一找一大把，这里不再赘述。有一点非常有趣的是，MySQL用于表分区的字段列/键必须在该表的所有唯一索引中（含主键）。这句话什么意思呢？就是用在分区上的键，如果表有主键或者唯一索引存在，则它必须在主键和唯一索引里，是的，你没看错，两个都要在。所以这点在创建表结构的时候要兼顾考虑一下。参看两个例子。

为什么呢？因为分区后索引结构发生变化，MySQL不能保证之前索引的唯一，所以要求你妥协，分区键要放唯一索引里组合唯一。这点因为结构有差异和Oracel略有不同。

小马认为分区只是基于在物理结构上的，毕竟有局限性，比如对伸缩扩展比较麻烦。那么接下来看基于逻辑上的分表操作。这种方式在大型架构和分布式中尤为受欢迎，因为分表也便于后续作一些高可用动作。话又说回来，当系统体量没有那么大的情况下，就不要分表了，反而影响性能成为累赘。

分表

分表的法子比较多样灵活，但大致可分为两种。

1、利用merge存储引擎来实现分表

此方法的好处就是只需要处理好表结构和插入逻辑，对于查询逻辑是透明的，虽然被分成了N个表但查询时会自动关联。语句场景参考如下：

2、散列某字段为表索引分表

将表字段其中的某一个唯一标志如openid来哈希作为表前缀来均匀分布，直接将表分为N个。如将user表分10个表，假设根据字段qq字段分表，则将得到user_(qq%10)这样的表名后缀索引0-9十个表，于是逻辑代码根据(qq%10)来决定将操作哪个表。这个法子最简单好用。这里qq号刚好是整形直接取模哈希，那如果是openid字符串呢该怎么处理？这点留给观者思考。

这里的分表自增主键原则上是不受影响的，因为各自代码逻辑也是根据哈希键分开来的，但这里建议使用分布式的ID为好，比如雪花算法。

题外话，这里既然提到哈希，那顺带思考下，哈希冲突的解决方式有哪些呢？比如再哈希法，然后呢？

分表后如何支持 全表的搜索和后台的分页查询。参考：

1   2

主从复制，读写分离，负载均衡

只要服务器安装了mysql proxy或Ameoba软件就可以实现读写分离和负载均衡。小马认为，这可以认为这是服务器实现层面的，也就是中间件自己判断是读还是写自己找到配置的机器，以及自己计算负载自己实现均衡。但在TP框架主从DB配置中似乎主从读写分离的实现是从代码层面实现的，是否有印象呢？

编写合理的SQL

这点我想也是老生常谈了。就是不要写查询效率低的慢查询SQL语句。关于是否有慢查询这点可以配置打开慢查询日志并查看，这里不赘述。

关于效率低下的一些经典行为：

1、尽量避免where语句中对字段进行null判断（表设计的时候就尽量不要NULL值），否则将放弃使用索引而进行全表扫描，如：select id from where num is null；

2、最好不要select *，会导致全表扫描。能不用join表连接就不用，非要用记得join on字段加索引；

3、查询有限数据请加limit；尽量给表设置主键，尽管已经有了唯一索引。

4、尽量避免wherer语句后使用！=或者<>操作符或者or，否则将导致放弃索引而进行全表扫描，这点可以参看导致索引失效的例子。or语句可以用uion语句来替换。

5、in和not in也要慎用，否则会进行全表查找，对于连续的数值，能用between就不要用in。很多时候用exist是代替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)；

6、应避免在where子句中对字段进行表达式操作或者对字段进行函数操作，这样导致引擎放弃使用索引而进行全表查询，比如：select id from t where num/2=100；

7、如果开启事务请检查异常或者出错的情况下是否回滚或者提交了事务。

MySQL优化就暂告一段落了，欢迎补充指点。

彩蛋时间：

分库分表后如何解决分页问题？

设计的时候让查询的条件都落在一个表上，无法满足加一个满足条件的冗余表；搞个全量索引二次查询；借助中间件，比如mycat（根据sql找到需要的库表集合查询结果，跟查询单表一样）。

三范式

什么是三大范式：

第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。

第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.

三范式主要是针对数据库 字段 设计而言，为了好记，小马归纳总结为：

1、字段值的原子性 ：学生ID    姓名    学院班级；     不符合：其中学院班级值非原子性 ；正确：分开两个字段存 学院 班级。

2、字段不要冗余：学生ID    宿舍ID     宿舍名；    不符合：其中宿舍ID和宿舍名冗余；正确： 宿舍名存另一个表，用外键关联来实现。

3、不能有值是与主键间接相关： 学号   姓名    院系名称     院系地址；  不符合：其中院系地址是与主键间接相关的是与院系才是直接相关；正确：学号 姓名 院系ID外键 另一个表：院系ID 院系名称 院系地址。

很多时候为了查询效率等其他原因会牺牲一些范式规范，比如第二范式，这也是允许的，并非必须严格遵循。

原创文章，未经允许请勿转载。