首页 新闻 会员 周边

sql用上索引也很慢的优化问题

0
悬赏园豆:100 [待解决问题]

select count(1) from a left join b on a.url = b.url where a.name='百度'

sql如上,a表有一百五十万的数据,b表有一万五千多的数据,url是一个长度为500varchar类型的字段,是一个普通索引,name也是普通索引
explain计划显示 此sql使用了name普通索引和b表的唯一索引(b的url是唯一索引),row显示a表扫描了二十多万行,速度很慢,需要4-5S,这种大数据量已经用上了索引的sql如何优化才能在1s只能出结果呢

luokeio的主页 luokeio | 初学一级 | 园豆:102
提问于:2022-03-07 15:16
< >
分享
所有回答(6)
0

假如你实现Join函数如何实现?

来看看一个常见实现方式:

        private static IEnumerable<TResult> JoinIterator<TOuter, TInner, TKey, TResult>(IEnumerable<TOuter> outer, IEnumerable<TInner> inner, Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector, Func<TOuter, TInner, TResult> resultSelector, IEqualityComparer<TKey>? comparer)
        {
            using (IEnumerator<TOuter> e = outer.GetEnumerator())
            {
                if (e.MoveNext())
                {
                    Lookup<TKey, TInner> lookup = Lookup<TKey, TInner>.CreateForJoin(inner, innerKeySelector, comparer);
                    if (lookup.Count != 0)
                    {
                        do
                        {
                            TOuter item = e.Current;
                            Grouping<TKey, TInner>? g = lookup.GetGrouping(outerKeySelector(item), create: false);
                            if (g != null)
                            {
                                int count = g._count;
                                TInner[] elements = g._elements;
                                for (int i = 0; i != count; ++i)
                                {
                                    yield return resultSelector(item, elements[i]);
                                }
                            }
                        }
                        while (e.MoveNext());
                    }
                }
            }
        }

因此怎么办——

1.把结果用空间去换时间(如视图);

2.被数据放到输出结果更快的硬件上(如硬盘放内存,甚至把1放到内存中);

3.优化Join函数(很明显这个过程是透明的,改不了)

花飘水流兮 | 园豆:13560 (专家六级) | 2022-03-07 15:35

这么说,基本无解了啊,单方面从sql入手已经无法优化了吗,才一百多万数据的关联啊

支持(0) 反对(0) luokeio | 园豆:102 (初学一级) | 2022-03-07 15:45
0

a表的url列有没有索引?如果没有可以考虑加上。如果a表的url列也有索引且url列的数据前面有大部分字符是一样的,可以考虑给a、b两张表加一列来存放url的md5值,然后md5值的列也加索引,比较的时候直接用md5值比较。

我是满意吖 | 园豆:386 (菜鸟二级) | 2022-03-07 16:01

url上有索引,但是相当于前缀索引,因为长度有2千的索引不能那么长,至于加列存md5的值这个方案能详细说下吗,不是很懂

支持(0) 反对(0) luokeio | 园豆:102 (初学一级) | 2022-03-07 17:13
0

@我是满意吖 的答案可以尝试:存储URL的哈希值,然后用此哈希值作为索引,试试看。有的数据库自带计算哈希值的函数,问主可百度一下。

会长 | 园豆:12401 (专家六级) | 2022-03-07 19:10

mysql并没有计算哈希值的函数吧,因为是生产环境的库,所以最好有能用sql解决的方案

支持(0) 反对(0) luokeio | 园豆:102 (初学一级) | 2022-03-08 10:13
支持(0) 反对(0) 会长 | 园豆:12401 (专家六级) | 2022-03-08 11:27

@luokeio: 生产库,不好搞。看老板能不能下决心,重构数据库结构,迁移数据

支持(0) 反对(0) 会长 | 园豆:12401 (专家六级) | 2022-03-08 11:28

@会长: 好的,感谢指导

支持(0) 反对(0) luokeio | 园豆:102 (初学一级) | 2022-03-08 11:30

@luokeio: 不客气

支持(0) 反对(0) 会长 | 园豆:12401 (专家六级) | 2022-03-08 14:04

另外你说url加的试前缀索引,要不你试试用url前面一部分比较,不整个比较看看速度有提升吗

支持(0) 反对(0) 会长 | 园豆:12401 (专家六级) | 2022-03-08 14:05
0

这个 sql 用 left join 的意义是啥啊,感觉完全不需要啊

huiyuanai709 | 园豆:487 (菜鸟二级) | 2022-03-08 10:17

业务需要关联,问题是数据量太大,关联的话查询速度会很慢

支持(0) 反对(0) luokeio | 园豆:102 (初学一级) | 2022-03-08 10:18

这个只是实际sql的简化版,主要问题就是这俩表关联耗费了比较长的时间

支持(0) 反对(0) luokeio | 园豆:102 (初学一级) | 2022-03-08 10:19

@luokeio: 这个就需要试了,比如使用子查询让 join 的表变小,
有时候加上 char_length(url) 也有效果

支持(0) 反对(0) huiyuanai709 | 园豆:487 (菜鸟二级) | 2022-03-08 10:38
0

这个结构不复杂,而且150万的数据,这个速度已经是正常表现了。 4-5s能出来还算可以了,理论不仅仅是a表的索引那一下,再排除b表的数据这个还得要时间。
其实很多人我发现都不懂索引,我不是说楼主,我是身边工作好多年的人,只要慢就把条件上全部建索,有毛用啊,要明白,一个查询,不管几个表,能有效果的索引基本上就只能用一索,当然索引扫描不算,只算能大量优化效果的。

gw2010 | 园豆:1487 (小虾三级) | 2022-03-11 15:20

再仔细看了,这个语句left join 确实没有用,你这个语句就是 select count(1) from a where a.name='百度'

这种可以从方案上来调整,用空间来换时间,比如定时跑数据,然后查询的时候只查结果表就可以了。比如 数据更新于1天前,数据更新于14:00,这种方式不会卡,后面慢慢算好再展示。

支持(0) 反对(0) gw2010 | 园豆:1487 (小虾三级) | 2022-03-11 15:22

嗯,索引加不加看情况,毕竟有利有弊,sql要在1S只能出结果,目前的数据还不至于做分表,想在sql优化方面找点方案,实在不行再改表的结构

支持(0) 反对(0) luokeio | 园豆:102 (初学一级) | 2022-03-11 15:24
0

我觉得100多w 4-5s 其实不慢了

labman004 | 园豆:214 (菜鸟二级) | 2022-05-12 18:04
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册