分库分表SQL优化

分库拆表：

好处：

1. 数据库容量问题；

2. 解决性能压力的最优选择；

原则：

反范式数据结构设计，所谓反范式，第一要点是不用外键，不允许Join 操作，不允许任何需要跨越两个表的查询请求；第二要点是适度冗余减少查询请求。

 

分库方案：

1. 安全性拆分

将高安全性数据与低安全性数据分库，这样的好处第一是便于维护，第二是 高安全性数据的数据库参数配置可以以安全优先，而低安全性数据的参数配置以性能优先。

2. 基于业务逻辑拆分

基于业务逻辑拆分，可以减少前端应用请求发送到不同数据库服务器的频次，从而减少链接开销，便于日常维护和前端调用；

3. 基于负载压力拆分

基于负载压力对数据结构拆分，便于直接将负载分担给不同的服务器。

基于负载压力拆分，可能拆分后的数据库包含不同业务类型的数据表，日常 维护会有一定的烦恼

 

分表：

数据量过大或者访问压力过大的数据表需要切分

忙闲分表

单数据表字段过多，可将频繁更新的整数数据与非频繁更新的字符串数据切分

横向切表

a. 等分切表，如哈希切表或其他基于对某数字取余的切表。等分切表的优点是 负载很方便的分布到不同服务器；缺点是当容量继续增加时无法方便的扩容， 需要重新进行数据的切分或转表。而且一些关键主键不易处理。

b. 递增切表，比如每 1kw 用户开一个新表，优点是可以适应数据的自增趋势； 缺点是往往新数据负载高，压力分配不平均。

c. 日期切表，适用于日志记录式数据，优缺点等同于递增切表。

热点数据分表

将数据量较大的数据表中将读写频繁的数据抽取出来，形成热点数据表。通常一个庞大数据表经常被读写的内容往往具有一定的集中性，如果这些集中数据单独处理，就会极大减少整体系统的负载。

热点数据表与旧有数据关系:

a. 可以是一张冗余表，即该表数据丢失不会妨碍使用，因源数据仍存在于 旧有结构中。优点是安全性高，维护方便，缺点是写压力不能分担，仍 需要同步写回原系统。

b. 可以是非冗余表，即热点数据的内容原有结构不再保存，优点是读写效 率全部优化；缺点是当热点数据发生变化时，维护量较大。

c. 具体方案选择需要根据读写比例决定，在读频率远高于写频率情况下，优先考虑冗余表方案。

 

主从架构：

1. 读写分离对负载的减轻远远不如分库分表来的直接；

2. 写压力会传递给从表，只读从库一样有写压力，一样会产生读写锁！

3. 主从延迟也是重大问题。一旦有较大写入问题，如表结构更新，主从会产生巨大 延迟。

 

mysql优化：

建索引的原则：

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

 

慢查询优化步骤：

1. 先从单个表where条件后，返回记录数最小的表查起；

2. order by limit 形式的sql语句让排序的表优先查；

3. explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）；

4. 根据建索引的原则，添加索引