`
SOULisy
  • 浏览: 25702 次
  • 性别: Icon_minigender_1
  • 来自: 北京
最近访客 更多访客>>
社区版块
存档分类
最新评论

MySQL 优化总结 (四)

阅读更多

减少不必要的表关联

        有时候一个查询需要很多表关联,表一多了就容易让人犯晕,没必要join的也都给塞进来了,看看下面这个句子:  

        老师在布置作业的时候,会写入【布置班级】表和【学生作业评价】表,现在要检索每个班、每个学生在一段时间内做过的作业。我们的同事开始的join是走了红色的那条线,后来经过分析,显然蓝线就够了。

尽可能的限制条件

  Where条件多了好啊,多了建索引的可能,也减少了结果集,尤其对于那种以查询结果做derived表的,更应该从中间就限制结果集。比如这个查询  

  1 select
  2 
  3   t.person_id,
  4 

  5   t.name,
  6 

  7   t.class_id,
  8 

  9   t.class_name,
10 

11   t.grade_num,
12 

13   t.cn,
14 

15   t1.goodnum,
16 

17   t2.infonum
18 

19   from
20 

21   (
22 

23   select
24 
25   bp.person_id,
26 

27   bp.name,
28 

29   bc.class_id,
30 

31   bc.class_name,
32 

33   bg.grade_num,
34 

35   COUNT(distinct v.person_id) cn
36 

37   from vir_extra_appraisal v,bas_person bp,bas_student b,bas_class bc,bas_grade bg
38 

39   where v.bas_person_id=bp.person_id
40 

41   and v.person_id=b.person_id
42 

43   and b.class_id=bc.class_id
44 

45   and bc.grade_id =bg.grade_id
46 

47   and v.bas_person_id =12762
48 
49   and v.appraisal_date >='2008-08-02 00:00:00'
50 
51   and v.appraisal_date <='2008-09-03 23:59:59'
52 
53   group by v.bas_person_id
54 

55   )
56 

57   t
58 

59   left join
60 
61   (
62 

63   select
64 
65   v1.bas_person_id,count(distinct v1.person_id)as goodnum
66 

67   from vir_extra_appraisal v1
68 

69   where v1.appraisal_type=8501001
70 
71   and v1.appraisal_date >='2008-08-02 00:00:00'
72 
73   and v1.appraisal_date <='2008-09-03 23:59:59'
74 
75   group by v1.bas_person_id
76 

77   )
78 

79   t1 on (t1.bas_person_id=t.person_id )
80 

81   left join
82 
83   (
84 

85   select
86 
87   v2.bas_person_id,count(distinct v2.person_id)as infonum
88 

89   from vir_extra_appraisal v2
90 

91   where v2.appraisal_type=8501002
92 
93   and v2.appraisal_date >='2008-08-02 00:00:00'
94 
95   and v2.appraisal_date <='2008-09-03 23:59:59'
96 
97   group by v2.bas_person_id
98 

99   )
100 

101   t2 on (t2.bas_person_id=t.person_id )

  可以看到,derived表t是限制了v.bas_person_id =12762,最终的结果集是只查一个人的情况,derived表t、t1是针对所有人做的汇总,而在和derived表t 做join的时候,给过滤掉了,最终只保留了v.bas_person_id =12762的记录,这是何苦呢?直接在t1 和 t2里过滤掉多好,于是在t1 和t2里分别加上条件person_id=12762。

 

过大的子查询用临时表处理效果会好的多

 

  -- /*以学生做题本为基础,加载某一天各班级的做题信息*/  

 

1 insert into tmp_bas_class_do_list(problem_id,class_id,subject_id,do_type,error_persons,do_persons,count_date)
2 

3   select bs.problem_id,bs.class_id,bs.subject_id,bs.do_type,
4 

5   sum(case bs.if_error when 1 then 0 else 1 end ) error_cnt,
6 

7   count(*) do_cnt,
8 

9   date_format(bs.do_date,'%Y-%m-%d')
10 
11   from bas_student_do_list bs
12 

13   group by bs.problem_id,bs.class_id,bs.subject_id,bs.do_type,date_format(bs.do_date,'%Y-%m-%d');
14 
15   create index idx_tmp on tmp_bas_class_do_list(class_id, problem_id);
16 

17   update tmp_bas_class_do_list bs,
18 

19   (select t3.problem_id,t3.do_type, t3.class_id,date_format(t3.do_date,'%Y-%m-%d') as do_date,group_concat(t1.name) as std_name
20 
21   from bas_person t1, bas_student_do_list t3
22 

23   where t1.person_id = t3.person_id
24 

25   and t3.if_error = 0
26 
27   group by t3.problem_id,t3.do_type, t3.class_id,date_format(t3.do_date,'%Y-%m-%d')) t2
28 
29   set bs.error_students = t2.std_name
30 

31   where bs.problem_id = t2.problem_id
32 

33   and bs.class_id = t2.class_id
34 

35   and count_date = t2.do_date
36 

37   and bs.do_type = t2.do_type;

 

  -- 临时表扶正

 

  truncate table bas_class_do_list;

 

  drop table bas_class_do_list;

 

  rename table tmp_bas_class_do_list to bas_class_do_list;

 

  大家可以看到,我是先把select的结果insert到临时表里,建立索引后,又用derived表t2更新临时表的数据,最后临时表替换成正式表。最初的时候,我是直接用select表和derived表t2做关联,直接insert到正式表里,这样写的select和t2做join的速度非常慢。改成这种写法后,速度由8分钟减少到40秒。

 

  通过这个例子我们也可以看到,大批量插入前先删除索引,插入后再建立索引,效果要比直接插入好的多。

 

 

分享到:
评论

相关推荐

    MySQL架构执行与SQL性能优化 MySQL高并发详解 MySQL数据库优化训练营四期课程

    课程内容进行了精华的浓缩,有四大内容主旨,MySQL架构与执行流程,MySQL索引原理详解,MySQL事务原理与事务并发,MySQL性能优化总结与MySQL配置优化。课程安排的学习的教程与对应的学习课件,详细的学习笔以及课程...

    MySQL性能优化总结.pdf

    MySQL性能优化总结.pdf

    mysql性能优化总结

    MySQL性能优化是一个涵盖广泛的主题,涉及数据库设计、SQL查询优化、索引策略等多个方面。以下是对标题和描述中提到的一些关键知识点的详细说明: 1. **表的优化**: - **定长与变长字段的分离**:将定长字段(如...

    mysql深度学习者 MySQL性能优化总结

    MySQL 性能优化总结 MySQL 性能优化是数据库管理和开发人员需要掌握的重要技能。性能优化的目标是让查询更快,减少查询所消耗的时间。为了达到这个目标,我们需要从每一个环节入手,包括连接、配置优化、索引优化、...

    Mysql 优化技巧总结(自己整理)

    ### MySQL优化技巧总结 #### 一、MySQL慢查询日志(Slow Query Log)与mysqldumpslow工具 **慢查询日志**是MySQL提供的一种非常实用的功能,它能够帮助我们记录并分析那些执行时间较长的SQL语句,进而找出性能瓶颈并...

    MySQL基础与性能优化总结思维导向图

    Mysql基础性能优化思维导向图 (其中包括:mysql基础、mysql性能优化、mysql锁机制和主从复制) 文件名称:MySQL基础与性能优化总结.xmind

    MySql优化.pdf

    MySQL优化 MySQL优化是数据库管理中的一个重要...总结而言,MySQL优化是一个涉及多个层面的复杂过程,需要综合考虑配置、索引、查询语句、服务器状态等多方面因素,通过合理配置和优化,以达到提升数据库性能的目的。

    MySQL优化.docx

    ### MySQL优化知识点详解 #### 一、MySQL简介 MySQL是一款由MySQL AB公司开发的开源数据库管理系统,后来该公司被Sun Microsystems收购。MySQL因其简单、高效、可靠的特点,在IT行业中迅速崭露头角,成为最受欢迎...

    mysql优化总结,可以参考学习下

    以下是对MySQL优化的详细总结,供您参考学习。 一、查询优化 1. **索引优化**:索引能显著提高查询速度。应为经常用于搜索的列创建索引,特别是主键和外键。复合索引在多条件查询时特别有用,但要注意避免冗余和...

    mysql5.6性能优化总结

    MySQL 5.6 性能优化总结 MySQL 5.6 是一个高性能的关系型数据库管理系统,然而随着数据库规模的增长和复杂度的增加,性能问题开始浮现。因此,性能优化成为 MySQL 数据库管理员和开发者的首要任务。本文将总结 ...

    MySQL优化 实战视频课程

    ### MySQL优化实战视频课程知识点概览 #### 一、MySQL优化基础 ##### 1.1 数据库优化的重要性 - **背景介绍**:随着互联网技术的发展,数据量呈指数级增长,对数据库系统的性能要求越来越高。 - **核心价值**:...

    MySql优化 自已总结

    MySQL优化是数据库管理中至关重要的环节,它关系到系统性能和响应速度。下面将详细介绍MySQL自带的慢查询日志分析工具mysqldumpslow及其使用方法,以及如何使用EXPLAIN来分析SQL查询。 首先,MySQL的慢查询日志...

    Mysql数据库优化总结

    Mysql数据库优化总结-飞鸿无痕-ChinaUnix博客................................................................................................................

    MySQL数据库查询优化

    第6课 查询优化技术理论与MySQL实践(四)------条件化简 什么是条件化简?MySQL中对什么样的条件自动进行优化?如何写出可利用索引的条件语句? 预计时间1小时 第7课 查询优化技术理论与MySQL实践(五)------外...

    2G内存的MYSQL数据库服务器优化

    ### 2G内存的MySQL数据库服务器优化 在IT行业中,对于资源有限的环境进行数据库优化是一项挑战性工作,尤其是在仅有2GB内存的情况下对MySQL数据库服务器进行优化。这种优化旨在提高性能的同时确保系统的稳定运行。 ...

    mysql性能的优化

    #### 一、什么是MySQL优化? MySQL优化是指通过合理安排资源和调整系统参数,使得MySQL运行得更快、更节省资源的过程。优化的目的在于减少系统瓶颈,降低资源消耗,提升系统的响应速度。 #### 二、优化的主要方面 ...

    mysql 性能优化与架构设计(word版)

    总结,MySQL性能优化与架构设计涵盖了许多方面,包括查询优化、索引策略、数据库设计、缓存利用、并行处理、架构设计、数据分布以及监控与调优工具的使用。理解和掌握这些知识点,能够帮助我们构建高效、稳定的...

    mysql5.6性能优化

    #### 四、优化数据库结构 优化数据库结构是另一个重要的优化方向。良好的数据库设计不仅可以提高查询效率,还可以简化业务逻辑,降低维护成本。 - **合理的表设计**:包括字段类型的选择、索引的设置等。 - **分区...

Global site tag (gtag.js) - Google Analytics