mysql分布式思维（六）- 查询优化二

 一.查询优化

1.join关键字优化（原理，类似嵌套循环  Nested Loop)

1.1尽量减少Nested loop循环的总次数

1.  A表驱动   关联查询  B表
2.  A--->过滤完10条记录
3.  B--->过滤完20条记录
4. 用小结果集驱动大结果集 ----->大家可以通过查看执行计划了解执行原理

1.2被驱动表的条件尽量在索引列上（优化内存循环，注意索引失效的问题）

1.3当无法保证被驱动表的join条件在索引列上

     那么有一个参数join_buffer_size的设置不要太吝啬。
     windows--->my.ini    linux--->my.cnf文件中
     建议join_buffer_size为1MB，如果内存充足的话可以设置为2MB.
     每个线程（可以认为是每个连接)多会创建自己的buffer,是独占而不是共享。

 

 

2.order by关键字优化

       2.1 尽可能在索引列上完成排序操作

2.2 如果不在索引列上，mysql就要启动排序算法(执行计划中可以看到  extra--->filesort)

        mysql为了兼容老的版本，有两种进行排序的算法,可以简单的认为（老的排序算法，新的排序算法），新的排序算法，性能更好些，如何使用新的排序算法呢。

2.3 max_length_for_sort_data参数的设置（这个设置直接决定了是用的老的算法还是新的算法)

      当我们所有返回字段的最大长度小于这个参数值的时候，mysql会选择新的排序算法，否则使用老的排序算法。

2.4 只返回自己需要的列

2.5 增大sort_buffer_size参数的设置,不是为了进行选择排序算法而设置,减少排序过程中对数据进行分段，就会导致mysql启动临时表存放数据，进行交换排序,大小最好能够容纳需要排序的数据量，也是独占

     内存的，不是共享的.

 

 

GROUP BY关键字优化

 3.1 group by 原理其实是先进行排序，然后进行分组,要用到排序尽可能在索引列上完成分组

 3.2 当无法使用索引列，那么max_length_for_sort_data参数的设置.sort_buffer_size参数的设置

 3.3 能在where限定的条件就不要去having限定了,有些条件是既可以放在having又可以放在where的时候应该放在where.

 

 

DISINCT 关键字的优化

 4.1 原理先进行GROUP BY 然后在每组中取出一条记录,在DISINCT关键字中进行分组的不一定先进行排序,如果使用了聚合函数的情况下是要排序的，否则不进行排序工作,大家可以通过查看执行计划.

 4.2 很多的优化就和group by类似了。

 

 一.数据库设计优化

 1.高效的模型就一定合理吗

1.1范式的问题

     最终的目的是避免数据的冗余，保持数据的一致性（修改数据简单）

     那个年代存储代价相当昂贵，并发量，数据量没有达到现在互联网的大数据场景

1.2适当冗余数据 让Query尽量减少join(高并发，大数据量的场景下)

     例如：高并发的论坛-并发最高的是查看帖子标题和作者昵称，原始的就应该用join。帖子表中冗余存放作者的昵称.查询的时候就避免了join的出现。

      修改的时候必须修改两种表（这种修改是极少的也不会出现高并发)

1.3 范式和非范式结合使用

 2.大字段垂直拆分甚至引入其它数据源

2.1 一个字段很大，大于其它字段的总和甚至好几倍，但是这个字段又不是频繁使用的字段

2.2 如帖子表

      帖子内容是相当大的一个字段我们可以把该字段单独一张表存放，和帖子表一对一关联大大降低访问的IO

      如果访问频率很高就应该选择其它NOSQL类的数据源如文档数据库--->Mongodb

2.3 常用字段和不常用字段也做垂直拆分

      用户表(常用字段无非就是用户名、密码、昵称、邮箱等,其它像生日、QQ、MSN，地址电话可能很少使用）高并发，有常用字段和极少使用字段，

      那么垂直拆分,把常用字段做为一张表大大降低IO,不常用字段单独一张表一对一关联.

 

 3.大表水平拆分

把一张表拆成多张表--->按记录去拆分目的就是一张一张小表呈现，只要少量的数据,要有划分标准。 

 

 4.使用合适的数据类型

尽可能占用空间少--->IO，并且计算速度快的类型--->CPU。对mysql数据库的所有类型要详细的了解

 

 5.哪些数据适合放在关系型数据库

结构化数据

二进制多媒体数据 、图片等---->选用其它数据库NOSQL

实时性高的数据----->内存数据库