Log queries, filter our the ones that are very frequent or take loads of time to execute, cache the frequent ones, optimize the fat ones, do this systematically and your system will be healthier.
Things that help massively from my experience: - APM - slow query log - DB read/write replicas - partitioning and sharding
Tools like https://explainmysql.com that make it clearer what you actually need to optimise are an easier system for Devs with enough database knowledge to set stuff up, but not enough to understand how it's used.
I assume someone's already working on an AI system that takes schema and logs and returns the SQL needed to magically improve things. Not sure I'd trust that, but I'd bet many companies would rather use that then get a full DBA.
This should unclog the most low hanging fruit. Then there is of course more advanced scenarios, especially with joins.
That’s not to say that the UX for explaining (hah) this doesn’t have a lot of room for improvement.
Use the Index Luke (also recommended by cocoflunchy) was one of my go to resources once.
Also, Tobias Petry does a really good job by covering many advanced topics on a Twitter and his books: https://twitter.com/tobias_petry
You don't fix bad coding with a new architecture. That just puts the problem off by some time.