mysqlsumif优化_Mysql查询性能优化
Mysql查询性能优化
慢查询优化基础：优化数据访问
查询需要的记录。查询100条，应⽤层仅需要10条。
多表关联时返回全部列。*，多表关联，字段查询要加前缀。
总是取出全部列。*
重复查询相同的数据。例如：在⽤户评论的地⽅需要查询⽤户头像URL，那么⽤户多次评论的时候，可能就会反复查询这个数据。⽐较好的⽅案，当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好。
重构查询⽅式
切分查询
将⼤查询切分成⼩查询，每个查询功能完全⼀样，只完成⼀⼩部分，每次只返回⼀⼩部分查询结果。如
果⼀次性完成的话，则可能需要⼀次锁住很多数据、占满整个事务⽇志、耗尽系统资源、阻塞很多⼩的但重要的查询。
分解关联查询
很多⾼性能的应⽤都会对关联查询进⾏分解。
简单地，可以对每⼀个表进⾏⼀次单表查询，然后将结果在应⽤程序中进⾏关联。
select * from tag join tag_post on tag_post.id = tag.id join post on post.id = tag_post.id
where tag.tag = 'msyql';
分解为：
select * from tag from where tag = 'msyql';
select * from tag_post where id = 1234;
select * from post where id in (1,2,3);
优势
让缓存的效率更⾼。许多应⽤程序可以⽅便地缓存单表查询对应的结果对象。例如：上⾯查询中的tag已经被缓存了，那么应⽤就可以跳过第⼀个查询。再例如，应⽤中已经缓存了ID为1，2的内容，那么第三个查询的in()中就可以少了⼏个ID，对MYSQL的查询缓存来说，如果关联中的某个表发⽣了变化，那么久⽆法使⽤查询缓存了，⽽拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利⽤查询缓存结果了。
将查询分解后，执⾏单个查询就可以减少锁的竞争。
在应⽤层做关联，可以更容易对数据库进⾏拆分，更容易做到⾼性能和⾼扩展。
查询本⾝效率也可能会有所提升。使⽤IN()代替关联查询，可以让MYSQL按照ID顺序进⾏查询，这可能⽐随机的关联要更搞笑。
可以减少冗余记录的查询。在应⽤层做关联查询，意味着对于某条记录应⽤只需要查询⼀次，⽽在数据库中做关联查询，则可能需要重复地访问⼀部分数据。从这点看，这样的重构还可能会减少⽹络和内存的消耗。
更进⼀步，这样做相当于在应⽤中实现了哈希关联，⽽不是使⽤MYSQL的潜逃循环关联。某些场景哈希关联的效率要⾼很多。
在很多场景下，通过重构查询将关联放到应⽤程序中将会更加⾼效，这样的场景有很多，⽐如：当应⽤能够⽅便地缓存单个查询的结果的时候，当可以将数据分布到不同的MYSQL服务器上的时候，当能够使⽤IN的⽅式代替关联查询的时候、当查询中使⽤同⼀个数据表的时候。
查询执⾏基础
MYSQL接收到请求都做了什么？
客户端发送⼀条查询给服务器。
服务器先检查查询缓存，如果命中了缓存，则⽴刻返回存储在缓存中的结果。否则进⼊下⼀阶段。
服务器进⾏SQL解析、预处理，再由优化器⽣成对应的执⾏计划。
MYSQL根据优化器⽣成的执⾏计划，调⽤存储引擎的API来执⾏查询。
将结果返回给客户端。
MYSQLk客户端/服务端通信协议
MYSQL客户端和服务端之间的通信协议是“半双⼯”的，这意味着，在任何⼀个时刻，要么是由服务器
向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发⽣。⼀旦⼀端开始发送消息，另⼀端要接收完整个消息才能响应它。这就像来回抛球的游戏：在任何时刻，只能⼀个⼈控制球，⽽且只能空值求得⼈才能将球抛回去。
客户端⽤⼀个单独的数据包将数据传给服务器，这也是为什么当查询的语句很长的时候，参数mac_allow_package就特别重要了。⼀旦客户端发送了请求，它能做的事情就只能是等待结果了。
相反的，⼀般服务器响应给⽤户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，⽽不能简单地只取前⾯⼏条结果，然后让服务器停⽌发送数据。这种情况下，客户端若接收完成的结果，然后取前⾯⼏条需要的结果，或者接完⼏条结果后就“粗暴”地断开连接，都不是好主意。这也是在必要的时候⼀定要在查询中加上LIMIT限制的原因。
查询状态
对于⼀个MYSQL连接，或者说⼀个线程，任何时刻都有⼀个状态，该状态表⽰了MYSQL当前正在做什么。有很多⽅式能查询当前状态，最简单的是使⽤show full processlist命令。⼀个查询的⽣命周期中，状态会变化很多次。
Sleep
线程正在等待客户端发送新的请求。
Query
线程正在执⾏查询或者正在将结果发送给客户端。
Locked
在MYSQL服务器层，该线程正在等待表锁。在存储引擎级别实现的锁。例如：Innodb的⾏锁，并不会体现在线程状态中。对于MyISAM来说这是⼀个⽐较典型的状态，但在其他没有⾏锁的引擎中也经常出现。
Analyzing and statistics
线程正在收集存储引擎的统计信息，并⽣成查询执⾏计划。
Copying to tmp table [on disk]
线程正在执⾏查询，并且将其结果集都复制到⼀个临时表中，这种状态⼀般要么在做Group By操作，要么是⽂件排序操作，或者是UNION 操作。如果这个状态后⾯还有“on disk”标记，那表⽰MYSQL正在讲⼀个内存临时表放到磁盘上。
Sorting result
线程正在对结果集进⾏排序。
Sending data
这表⽰多种情况：线程可能在多个状态之间传送数据，或者在⽣成结果集，或者在客户端返回数据。
了解这些状态的基本含义⾮常有⽤，这可以让你很好地了解当前“谁正在持球”。在⼀个繁忙的服务器上，可能会看到⼤量的不正常状态，例如statistics正占⽤⼤量的时间。这通常表⽰，某个地⽅有异常了。
查询优化
MYSQL如何执⾏关联查询
对于UNION查询，MYSQL先将⼀系列的单个查询结果放到⼀个临时表中，然后再重新读取临时表数据来完成UNION查询。
在MYSQL的概念中，每个查询都是⼀次关联，所以读取结果临时表也是⼀次关联。
当前MYSQL关联执⾏的策略很简单：MYSQL对任何关联都执⾏嵌套循环关联操作，即MYSQL先在⼀个表中循环取出单条数据，然后再嵌套循环到下⼀个表中寻匹配的⾏，依次下去，知道到所有表中匹配的⾏为⽌。然后根据各个表匹配的⾏，返回查询中需要的各个列。MYSQL会尝试在最后⼀个关联表中到所有匹配的⾏，如果最后⼀个关联表⽆法到更多的⾏以后，MYSQL返回到上⼀层次关联表，看是否能够到更多匹配记录，⼀次类推迭代执⾏。
简单的内连接查询：
l1, l2 from tab1 inner join tab2 using(col3) l1 in (1,2);
实际执⾏的伪代码表⽰：
outer_iter = iterator over tabl1 where col1 in (1,2)
outer_row =
while outer_row
inner_iter = iterator over tab2 where col3 = l3
inner_row =
while inner_row
output [ l1, l2]
inner_row =
end
outer_row =
end
简单的外连接查询：
l1, l2 from tab1 outer join tab2 using(col3) l1 in (1,2);
实际执⾏的伪代码表⽰：
outer_iter = iterator over tabl1 where col1 in (1,2)
outer_row =
while outer_row
inner_iter = iterator over tab2 where col3 = l3
inner_row =
if inner_row
while inner_row
output [ l1, l2]
inner_row =
end
else
output [ l, null ]
end
outer_row =
end
MYSQL的临时表是没有任何索引的，在编写复杂的⼦查询和关联查询的时候需要注意这⼀点。这⼀点对UNION查询也是⼀样的。
关联⼦查询
MYSQL的⼦查询实现得⾮常糟糕。最糟糕的⼀类查询是where条件中包含IN()的⼦查询语句。
select * from tab1 where col1 in (
select col2 from tab2 where col3 = 1;
)
MYSQL对IN()列表中的 选项有专门的优化策略，⼀般会认为MYSQL会先执⾏⼦查询返回所有包含col3为1的col2。⼀般来说，IN()列查询速度很快，所以我们会认为上⾯的查询会这样执⾏：
- SELECT GROUP_CONCAT(col2) from tab2 where col3 = 1;
mysql下载后的初次使用- Reuslt : 1,2,3,4,
select * from tabl1 where col1 in (1,2,3,4);
很不幸，MYSQL不是这样做的。MYSQL会将相关的外层表压到⼦查询中，它认为这样可以更⾼效率地查到数据⾏。也就是说，MYSQL 会将查询改成下⾯的这样：
select * from tab1
where exists (
select * from tab2 where col3 = 1
l1 = l1
);
这时，⼦查询需要根据col1来关联外部表的film，因为需要到col1字段，所以MYSQL认为⽆法先执⾏这个⼦查询。
如果tab1表数据量⼩，性能还不是很糟糕，如果是⼀个⾮常⼤的表，那这个查询性能会⾮常糟糕。改
写这个⼦查询
select * from tab1 inner join tab2 using(col1) where col3 = 1;
&&
select * from tab1
where exists (
select * from tab2 where col3 = 1
l1 = l1
);
⼀旦使⽤了DISTINCT和GROUP by，在查询执⾏的过程中，通常产⽣临时中间表。可以使⽤EXISTS⼦查询优化
UNION的限制
通过将两个表查询结果集合并取前20条
(select *from tab1 order by col1) union all (select * from tab2 order by col2) limit 20;
优化为:
(select *from tab1 order by col1 limit 20) union all (select * from tab2 order by limit 20)
UNION 临时表的数据会⼤⼤减少
优化COUNT()查询
Count()是⼀个特殊的函数，有两种⾮常不同的作⽤：它可以统计某个列的数量，也可以统计⾏数。在统计列值时要求列值是⾮空的(不统计NULL)。如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。
Count()的另外⼀个作⽤是统计结果集的⾏数。当MYSQL确定括号内的表达式值不可能为空时，实际上就是在统计⾏数。最简单的就是COUNT(*)。
简单的优化
select count(*) from tab1 where col >5;
优化为:
select (select count(*) from tab1 ) - count(*) from tab1 where col <5;
扫描的数量会减少很多
⼦查询也会当成常数，使⽤expand可知
情景：在同⼀个查询中统计⼀个列的不同值的数量，以减少查询的语句量
select sum(if(color = blue), 1, 0) as blue , sum(if(color = red), 1, 0) as red from items ;
同样也可以使⽤Count
优化关联查询
确保ON或者USING⼦句中的列有索引。
确保任何group by和order by只涉及到⼀个表中的列。
优化LIMIT分页
select col1, col2 from tab1 order by col3 limit 50,5;
改写成：
select col1, col2 from tab1 inner join (
select col1 from tab1 order by col3 limit 50,5
) as lim using(col1);
这⾥的“延迟关联”将⼤⼤提升查询效率，它让MYSQL扫描尽可能少的页⾯，获取需要访问的记录后再根据关联列回原表查询需要的所有列。这个技术可以优化LIMIT查询。
有时候也可以将LIMIT查询转换为已知位置的查询，让MYSQL通过范围扫描获得到对应的结果。
select col1, col2 from tab1 where col1 between 30 and 50;
select col1, col2 from tab1 where col1 < 500 order by col1 limit 20;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。