Physio here.
They will be in that it's a lengthy recovery, even once you're back playing, and you've got a period of 12-18 months during which you're unable to play entirely, and then you're not able to play at your best once you have returned. If that's hitting at a period of your career where you're supposed to be at your best, then it can cost you significant career opportunities, or stifle your progression and improvement as an athlete.
However, we don't see the long term/permanent issues that I think a lot of people seem to believe happen after an ACL tear. In the general sporting population, we see ~85-90% return to play at pre-injury levels after an ACL tear + repair. That's including people playing at competitive but non-professional levels.
In top flight football, which is arguably the most challenging sporting environment to return to post-ACL injury for a wide number of reasons, we see ~98% return to play rates, with ~55-60% returning to pre-injury levels of performance. (sources here and here. Numbers drop quite a bit if we look at people playing 5+ years after the injury (~70% and 40% respectively), but that's a far less useful stat in an elite sporting population than it is in the general population, due to elite sports being an inherently short and highly competitive career.
Football is also a bit notorious for still being one of the few sports that still rushes ACL returns. It's got a lot better over the past few years, but it's still not uncommon to see players returning 6 months post-injury, when we know that the risk of reinjury at that point is ~3-4x what it is at 9 months. So the higher rates of recurrence and slower return-to-form progress associated with that does skew the numbers for football a little.
But an ACL tear isn't the career-death-sentence that a lot of people seem to assume that it is.
Post-career, we do see higher rates of osteoarthritis in players who have had ACL injuries vs those who haven't, but it's also not to the level where their knees are just disintegrating at 40, which is what reading some of the public discourse around this stuff would have you think.
There's not much of a training/playing load-injury relationship for ACL tears. It's likely that just playing more games overall might increase the overall risk, as you just have more opportunities to do so, but you're also getting the conditioning benefits from that (as long as you're able to do it without picking up other injuries) that offset that increased risk somewhat as well.
There's plenty of other injuries that are the near direct result of overtraining/overplaying. Bony stress injuries/stress fractures in younger players and tendon injuries (primarily tendinopathies, rather than tears) are the big ones, and are a major concern. ACL just isn't really on that list.