一.查询优化
1.join关键字优化(原理,类似嵌套循环 Nested Loop)
1.1尽量减少Nested loop循环的总次数
-
-
-
- A表驱动 关联查询 B表
- A--->过滤完10条记录
- B--->过滤完20条记录
- 用小结果集驱动大结果集 ----->大家可以通过查看执行计划了解执行原理
-
-
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
实时性高的数据----->内存数据库