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只能出结果呢
所有回答(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函数（很明显这个过程是透明的，改不了）
这么说，基本无解了啊，单方面从sql入手已经无法优化了吗，才一百多万数据的关联啊
0
a表的url列有没有索引？如果没有可以考虑加上。如果a表的url列也有索引且url列的数据前面有大部分字符是一样的，可以考虑给a、b两张表加一列来存放url的md5值，然后md5值的列也加索引，比较的时候直接用md5值比较。
url上有索引，但是相当于前缀索引，因为长度有2千的索引不能那么长，至于加列存md5的值这个方案能详细说下吗，不是很懂
0
@我是满意吖 的答案可以尝试：存储URL的哈希值，然后用此哈希值作为索引，试试看。有的数据库自带计算哈希值的函数，问主可百度一下。
mysql并没有计算哈希值的函数吧，因为是生产环境的库，所以最好有能用sql解决的方案
@luokeio: 当然有：https://www.geeksforgeeks.org/mysql-sha1-function/
@luokeio: 生产库，不好搞。看老板能不能下决心，重构数据库结构，迁移数据
@会长: 好的，感谢指导
@luokeio: 不客气
另外你说url加的试前缀索引，要不你试试用url前面一部分比较，不整个比较看看速度有提升吗
0
这个 sql 用 left join 的意义是啥啊，感觉完全不需要啊
业务需要关联，问题是数据量太大，关联的话查询速度会很慢
这个只是实际sql的简化版，主要问题就是这俩表关联耗费了比较长的时间
@luokeio: 这个就需要试了，比如使用子查询让 join 的表变小，
有时候加上 char_length(url) 也有效果
0
这个结构不复杂，而且150万的数据，这个速度已经是正常表现了。 4-5s能出来还算可以了，理论不仅仅是a表的索引那一下，再排除b表的数据这个还得要时间。
其实很多人我发现都不懂索引，我不是说楼主，我是身边工作好多年的人，只要慢就把条件上全部建索，有毛用啊，要明白，一个查询，不管几个表，能有效果的索引基本上就只能用一索，当然索引扫描不算，只算能大量优化效果的。
再仔细看了，这个语句left join 确实没有用，你这个语句就是 select count(1) from a where a.name='百度'
这种可以从方案上来调整，用空间来换时间，比如定时跑数据，然后查询的时候只查结果表就可以了。比如 数据更新于1天前，数据更新于14：00，这种方式不会卡，后面慢慢算好再展示。
嗯，索引加不加看情况，毕竟有利有弊，sql要在1S只能出结果，目前的数据还不至于做分表，想在sql优化方面找点方案，实在不行再改表的结构
0
我觉得100多w 4-5s 其实不慢了
