帮忙mysql 语句调优

NinGoo

在多表连接的情况下，优化器需要判断比较不同的连接方式和顺序何种最优，在硬解析的时候需要多花点时间，不过这个不是问题，只要执行计划能重用基本上影响不大。

多表连接的主要的问题，是可能导致过多的逻辑读请求，极大的消耗CPU和IO，在高并发大数据量的时候效果会很差，如果能在应用层做拆分，会比较好

yueliangdao0608

原帖由 NinGoo 于 2008-1-19 17:48 发表
在多表连接的情况下，优化器需要判断比较不同的连接方式和顺序何种最优，在硬解析的时候需要多花点时间，不过这个不是问题，只要执行计划能重用基本上影响不大。

多表连接的主要的问题，是可能导致过多的逻辑读请求，极大的消耗CPU和IO，在高并发大数据量的时候效果会很差，如果能在应用层做拆分，会比较好

老大的回复就是专业。

jinguanding

原帖由 cyt2005 于 2008-1-19 11:26 发表
呵呵
首先 jinguanding 老兄
left join跟inner join 不同我晓得，但是斑竹给我的回复，让我有点迷惑
我也觉得explain的结果很理想的，这个语句是开发写的，这个是最里层的sql，他在网页上还是对这个结果集再次进行操作比如分页显示啥的
语句已经是我优化过一次的了。。。。
你说的时间花在了，这点我不太明白
可否进一步说明一下呢？生成执行计划会用这么长时间么？

执行计划。。。。就是优化器根据所设计到的表统计信息，使用到的索引信息，以及语句分析后（也即通过优化器进行优化过的）语句，提交给mysql服务器执行，
你使用explain看到的信息就是通过优化期进行优化后，可能需要扫描的记录与使用到的索引信息。。。。都是这步完成的。。。

explain的信息，跟真正执行时所预期的，有可能会存在区别的。。。。


其实，还有部分内容，我没有说有关I/O的问题，因为数据量大的话。。就不能全部加载到内存中，需要不停地切换地读入新记录进行比较。。。。。而比较运算就需要使用到CPU。。。。。这块元老 NiGoo。。。。已经解释了。。。

若你使用存储过程实现这个语句的话，你的执行计划就可以缓存起来。。。这也就是为什么使用存储过程查询执行快的其中一个原因。。。
若你的记录修改的机会少的话，你可以把这个查询语句的数据结果缓存起来。。第二进行就会更快。。。。。。


兄弟，说句心得的话。。。。。所谓数据库的优化就是寻找一个平衡点。。。追求最佳解的过程。。。具体的情况需要具体的 分析。。。


我建议你先使用这样的语句，，，，，可能需要再进行适当的调整。。。。。若效果还达不到你所需要的效果。。。你可以考虑听NiGoo大师的方式，进行业务逻辑层的修改。。把语句进行拆分。。。。。。这是这样 可能就出现另外两个问题。。。程序需要修改而且可能会比较大（逻辑要增加），另外一个可能会增加网络流量。。。。

另外一个解决办法就是考虑使用存储过程去解决多表的连接的问题。。。。。让程序调用的是一个存储过程。。。。

我就说这么多了。。。。。那个有效。。。需要在实际生产环境下，根据实际的情况进行调整语句，，以及测试下了。。。。
我现在羡慕你们有机会去测试与实践自己的方案，以及有机会碰到问题啊。。。。。。。我只能看看大家发上了贴了。。。。发表下不知道是否合理的解决方案（因为没有测试过）。。。。。今天喝点酒。。所以就多说了点。。请LZ ,杨兄，NiGoo见谅！

jinguanding

cyt2005 ：
你的数据量大了，索引的数据量也就大了．．是不是你的key_buffer设置的太小了．．．．．

要是你使用的索引记录没有加载到内存中缓存起来．．．．这也是速度变慢一种可能性噢．．．．这是针对同一语句，数据量增加之后的情况

也会增加更多的I/O读写操作噢．．．毕竟磁盘的转速是有限的．．．且是固定的

cyt2005

呵呵
斑竹和我一起等jinguanding
的回复吧~

yueliangdao0608

呵呵。等待。

WESTLIFE_XU

其实我是很不喜欢用inner join，可读性很差，为什么不直接用等号呢？
如  FROM mocha_document_body b, mocha_bo_instance bo_inst  where bo_inst.bo_instance_id = b.bo_instance_id

这个语句速度的快慢在于：WHERE b.document_state <> 300    AND i1.work_status_id = 2   AND bo_inst.submit_flag = 1  AND bo_inst.save_flag = 1 如果每个表结果集都很大的话，而且你是多表关联，那慢是必然的

不管是mysql还是oracle，实际上调优的思想是一样的。看看下面的连接，是oracle的，看对你有没有帮助。
http://www.taobaodba.com/html/20 ... dex_dy.html#more-20

yueliangdao0608

原帖由 WESTLIFE_XU 于 2008-1-21 10:16 发表
其实我是很不喜欢用inner join，可读性很差，为什么不直接用等号呢？
如  FROM mocha_document_body b, mocha_bo_instance bo_inst  where bo_inst.bo_instance_id = b.bo_instance_id

这个语句速度的快慢在于：WHERE b.document_state  300    AND i1.work_status_id = 2   AND bo_inst.submit_flag = 1  AND bo_inst.save_flag = 1 如果每个表结果集都很大的话，而且你是多表关联，那慢是必然的

不管是mysql还是oracle，实际上调优的思想是一样的。看看下面的连接，是oracle的，看对你有没有帮助。
http://www.taobaodba.com/html/20 ... dex_dy.html#more-20

你写的语句在MYSQL中就是INNER JOIN和CROSS JOIN 。这三个在MYSQL中是一样做优化的。

cyt2005

原帖由 NinGoo 于 2008-1-19 17:48 发表
在多表连接的情况下，优化器需要判断比较不同的连接方式和顺序何种最优，在硬解析的时候需要多花点时间，不过这个不是问题，只要执行计划能重用基本上影响不大。

多表连接的主要的问题，是可能导致过多的逻辑读请求，极大的消耗CPU和IO，在高并发大数据量的时候效果会很差，如果能在应用层做拆分，会比较好

看来还是多表连接，在高并发的时候才会出现问题

还有老大，目前这个东西就要结项了，做应用层的改动可能性不大了，现在也就是允许改sql

cyt2005

哈哈
感觉jinguanding兄那个酒醉的晚上有些消沉啊
我的key_buffer是256m
我想应该够用吧