本文不涉及复杂的底层数据结构,通过explain解释SQL,并根据可能出现的情况,来做具体的优化,使千万级表关联查询第一页结果能在2秒内完成(真实业务告警系统优化结果)。
需要优化的查询:使用explain
出现了Using temporary;
有分页时出现了Using filesort则表示使用不了索引,需要根据下面的技巧来调整语句
rows过多,或者几乎是全表的记录数;
key 是 (NULL);
possible_keys 出现过多(待选)索引。
1.使用explain语法,对SQL进行解释,根据其结果进行调优:
MySQL 表关联的算法是 Nest Loop Join,是通过驱动表的结果集作为循环基础数据,然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果:
a.EXPLAIN 结果中,第一行出现的表就是驱动表
b.对驱动表可以直接排序,对非驱动表(的字段排序)需要对循环查询的合并结果(临时表)进行排序(Important!),即using temporary;
c. [驱动表] 的定义为:1)指定了联接条件时,满足查询条件的记录行数少的表为[驱动表];2)未指定联接条件时,行数少的表为[驱动表](Important!)。
d.优化的目标是尽可能减少JOIN中Nested Loop的循环次数,以此保证:永远用小结果集驱动大结果集(Important!)!:A JOIN B,A为驱动,A中每一行和B进行循环JOIN,看是否满足条件,所以当A为小结果集时,越快。
e.NestedLoopJoin实际上就是通过驱动表的结果集作为循环基础数据,然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。如果还有第三个参与Join,则再通过前两个表的Join结果集作为循环基础数据,再一次通过循环查询条件到第三个表中查询数据,如此往复
2.两表JOIN优化:
a.当无order by条件时,根据实际情况,使用left/right/inner join即可,根据explain优化 ;
b.当有order by条件时,如select * from a inner join b where 1=1 and other condition order by a.col;使用explain解释语句;
1)如果第一行的驱动表为a,则效率会非常高,无需优化;
2)否则,因为只能对驱动表字段直接排序的缘故,会出现using temporary,所以此时需要使用STRAIGHT_JOIN明确a为驱动表,来达到使用a.col上index的优化目的;或者使用left join且Where条件中不含b的过滤条件,此时的结果集为a的全集,而STRAIGHT_JOIN为inner join且使用a作为驱动表
3.多表JOIN优化:
a.无order by条件时,根据实际情况,使用left/right/inner join即可,根据explain优化;
b.有order by a.col条件时,所有join必须为left join,且每个join字段都创建索引,同时where条件中只能有a表的条件,即将其它表的数据关联到a中形成一张大表,再对a的全集进行过滤;
如果不能全使用left join,则需灵活使用STRAIGHT_JOIN及其它技巧,以时间排序为例:
1)数据入库按照平台时间入库,自然a的数据都按时间有序;
SELECT c.*, r.HYPERVISOR_HOST_NAME hostname, r.HOST_IP FROM trust_monitor c STRAIGHT_JOIN res_node r ON c.res_node_id = r.ID STRAIGHT_JOIN am_assets a ON r.ASSET_ID = a.ID AND a.status = 58 STRAIGHT_JOINse_role s ON a.DEPT_FLAG = s.ROLE_ORG AND s.ROLE_ID IN (32,33,36,41) where c.STATUS = 58 and c.changed_type = 79 limit 1,10;
SELECT c.*, r.HYPERVISOR_HOST_NAME hostname, r.HOST_IP FROM trust_monitor c inner JOIN res_node r ON c.res_node_id = r.ID INNER JOIN am_assets a ON r.ASSET_ID = a.ID AND a.status = 58 INNER JOIN se_role s ON a.DEPT_FLAG = s.ROLE_ORG AND s.ROLE_ID IN (32,33,36,41) where c.STATUS = 58 and c.changed_type = 79 order by c.changed_time limit 1,10;
两者结果一致
4.误区:
a.视图只是屏蔽或者高效集合多表数据的一种方法,视图与表JOIN,不会起到任何效果
参考:
相关推荐
Oracle Sql语句转换成Mysql Sql语句java 源码,非常简单,只要给定源oracle sql语句地址,和生成目标文件地址运行即可。
千万数据,方便测试,sql调优
mysql-全国五级省市县镇村sql语句,直接在mysql数据库中执行sql语句即可。
《DB2 SQL性能调优秘笈》不仅详尽阐述了100余条SQL语句优化的技巧和最佳实践、编写高性能SQL语句的标准和原则,以及DB2数据库性能优化的“15步法”,而且还包含大量案例,为解决各种复杂的DB2性能问题提供了解决方案...
Mysql的表对象Sql语句转换单表,转换成Oracle创建表sql,在样本.txt粘贴Mysql的单表所有字段信息,输入表名。就会在控件台输出转换后的Oracle语句。
自己写的两个SQL语句生成对应数据库的数据字典,一个生成表集合,一个生成字段集合 只需修改where语句后面的TABLE_SCHEMA的数据库名即可
《Effective MySQL之SQL语句最优化》是由MySQL专家Ronald Bradford撰著,书 中提供了很多可以用于改进数据库和应用程序性能的最佳实践技巧,并对这些技巧 做了详细的解释。本书希望能够通过一步步详细介绍SQL优化...
32丨MySQL调优之SQL语句:如何写出高性能SQL语句?.html
《Effective MySQL之SQL语句最优化》提供了很多可以用于改进数据库和应用程序性能的最佳实践技巧,并对这些技巧做了详细的解释。《Effective MySQL之SQL语句最优化》希望能够通过一步步详细介绍SQL优化的方法,帮助...
中国最新的地区表sql语句(mysql版本)
全国省市数据库 二级 sql语句全国省市数据库 二级 sql语句全国省市数据库 二级 sql语句全国省市数据库 二级 sql语句全国省市数据库 二级 sql语句
mysql数据库创建学生表 这个SQL语句会创建一个名为 "Students" 的表,...一旦表被创建,你可以使用 INSERT 语句来向表中插入学生的数据,使用 SELECT 语句来查询学生的信息,以及使用其他 SQL 语句来管理表中的数据。
表的创建-SQL语句 课程目标 掌握 —— 显示数据表文件和表结构的语法格式。 掌握 —— 创建数据表的语法格式; 表的创建 show tables; 说明:用于显示已经建立的数据库表文件 显示数据库表语法格式 表的创建 创建...
MYSQL调优
MySQL mysql_query 函数执行SQL语句.docx
Python使用sql语句对mysql数据库多条件模糊查询
Mysql 行转列,列转行 SQL语句和示例表结构SQL Mysql 行转列,列转行 SQL语句和示例表结构SQL
提升MYSQL查询效率的10个SQL语句优化技巧
mysql 城市地区四级联动 sql文件 省市县镇(区)四级城市表 带城市等级和上下关联的父子城市 sql文件
本文实例讲述了MySQL 多表关联一对多查询实现取最新一条数据的方法。分享给大家供大家参考,具体如下: MySQL 多表关联一对多查询取最新的一条数据 遇到的问题 多表关联一对多查询取最新的一条数据,数据出现重复 ...