忘记密码   免费注册 查看新帖 |

ChinaUnix.net

  平台 论坛 博客 文库 频道自动化运维 虚拟化 储存备份 C/C++ PHP MySQL 嵌入式 Linux系统
最近访问板块 发新帖
查看: 2793 | 回复: 6

如何优化索引? [复制链接]

论坛徽章:
0
发表于 2013-01-19 22:15 |显示全部楼层
5可用积分
SQL1:SELECT COUNT(*) FROM table1 WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954'
SQL2:select * from table1 WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954' order by lastpost desc limit 0,36

SELECT COUNT(*) FROM table1 有32万条数据
SELECT COUNT(*) FROM table1 WHERE  zplb0='954'  有10万条数据
SELECT COUNT(*) FROM table1 WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954'  有10万条数据

索引:KEY `zplb0` (`zplb0`,`displayorder`,`lastposter`)   

explain SELECT COUNT(*) FROM cdb_threads WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954'  
结果 Using where; Using index

explain select * from cdb_threads WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954' order by lastpost desc limit 0,36

结果 type = all ; extra  = Using where; Using filesort


sql2执行要1秒多,非常慢。

请问这样的sql如何优化好。谢谢!

论坛徽章:
0
发表于 2013-01-19 23:10 |显示全部楼层
回复 1# yonghedawang

对于这个,可以考虑建立索引+其他解决办法共同来解决。
关于建立索引,可以考虑在zplb0和lastpost建立索引,这样能兼顾到SQL1和SQL2
index idx_zplb0_lastpost(`zplb0`,`lastpost `) 。
关于其他解决办法,比如SQL1类型的统计可以使用其他方式实现统计,而不需要每次都统计10万多的数据,可以使用一个额外的表进行统计。比如凌晨使用事件定期统计一次,然后使用类似触发器的功能进行累加。对于这种统计类型的语句性能会好很多。尤其是统计结果不需要非常精确的时候。

论坛徽章:
0
发表于 2013-01-21 09:11 |显示全部楼层
谢谢你的回复,sql1的效率本身不低。sql2要一秒多,我现在还没有想到好的办法。

论坛徽章:
0
发表于 2013-01-21 09:38 |显示全部楼层
本帖最后由 wangjj20 于 2013-01-21 09:41 编辑

or条件可以使用类似
select * from table1 WHERE displayorder>='0'  and zplb0='954'
union all
select * from table1  where displayorder='-2' and zplb0='954'
这样的语句。
索引可以尝试displayorder+zplb0+lastpost

另外看下profile,看下慢在哪个部分。

论坛徽章:
0
发表于 2013-01-21 18:19 |显示全部楼层
mark下关注下结果

论坛徽章:
8
综合交流区版块每周发帖之星
日期:2015-12-02 15:03:53数据库技术版块每日发帖之星
日期:2015-10-02 06:20:00IT运维版块每日发帖之星
日期:2015-10-02 06:20:00IT运维版块每日发帖之星
日期:2015-09-14 06:20:00金牛座
日期:2014-10-10 11:23:34CU十二周年纪念徽章
日期:2013-10-24 15:41:34酉鸡
日期:2013-10-19 10:17:1315-16赛季CBA联赛之北京
日期:2017-03-06 15:12:44
发表于 2013-01-22 10:28 |显示全部楼层
count(*)和*,而且后面的选择性很差

第二个sql用index idx_zplb0_lastpost(`zplb0`,`lastpost `) 做一年应该比较好

论坛徽章:
0
发表于 2013-01-24 14:03 |显示全部楼层
本帖最后由 G8bao7 于 2013-01-24 14:09 编辑

回复 1# yonghedawang


1、由于满足where条件的记录数占全表的比重比较大(10W/32W),而且sql2是select *,也不能使用索引覆盖扫描。
    优化器认为索引还没有全表扫描快
2、由于where 条件中displayorder是范围查找,排序并不会使用`zplb0`索引(`zplb0`,`displayorder`都是等值查找才会使用),
    所以order by lastpost会引起Using filesort

sql2优化建议:
select t2.* from (select 主键ID from table1 WHERE (displayorder>='0' or displayorder='-2' ) and zplb0='954' order by lastpost desc limit 0,36 ) as t1
,table1 AS t2
where t1.主键ID=t2.主键ID;
您需要登录后才可以回帖 登录 | 注册

本版积分规则

  

北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802020122
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员  联系我们:wangnan@it168.com
感谢所有关心和支持过ChinaUnix的朋友们 转载本站内容请注明原作者名及出处

清除 Cookies - ChinaUnix - Archiver - WAP - TOP