Been reading through this carefully to make sure I didn't miss over anything. Had a theory:
I understand the parallel to modern naval literature, but it bears remembering that Starfleet has always placed a fairly high priority on exploration and research. Every ship class we have seen in Trek history has been seen conducting some type of science mission as well as a combat role, enough for us to expect this to be the normal state of affairs fleetwide. This would mean the suggestion of "frigates and destroyers" existing in vast numbers would represent a huge unseen Starfleet we are in no way familiar with and have never actually seen.
OTOH, the Abramsverse shows us the USS Kelvin, which is pretty close to being a prime-universe Saladin class starship... except that it is actually slightly larger than a primeline Constitution. IMO, it would make sense to reconcile some of those Abramsverse Kelvin kitbashes into the prime timeline if only because THEIR existence is more likely to be canon than any of the FJ or Jackil speculative designs. IOW, those ships fanon traditionally describes as "frigates and destroyers" smaller than Enterprise might actually be LARGER than the Constitutions, balanced for a totally different type of mission that the Constitutions were never designed for (IOW: Constitution is a cruiser while the Kelvin is an aircraft carrier).
It seems to me we should keep in perspective what Starfleet is and how it operates. The US Navy is a combat organization; its largest ships are built for a combat mission. Starfleet is an exploration program; its largest ships are built for an exploration mission. Their version of an "aircraft carrier" is a ship that's designed to enter orbit of an unknown planet and aggressively hunt down and record any interesting piece of information about it. Such vessels would also be quite heavily armed, but may not be as effective in a fight -- especially against the Klingons -- as a larger number of smaller ships with slightly reduced armament.
In the end, I'm thinking Constitutions would be part of a classification scheme that runs from:
Despite what you'd expect, the scheme has nothing at all to do with size, but the capabilities a ship is designed to carry. Size is a FACTOR in that, but that wouldn't actually mean "cruisers are bigger than scouts and surveyors are bigger than cruisers" since size alone wouldn't be sufficient to ensure those capabilities.
Runabouts are self-explanatory: small starships attached to starbases may or may not have permanent crews (in which case, the Jenolan could be interpreted as a giant runabout).
Scouts are also self-explanatory: they're ships you send to look for something or to check out something that's been detected by remote sensing. Can be many different sizes with many different capabilities.
Cruisers are easy to confuse with their naval counterparts but in this case it would describe ships that travel through space with no specific objective, just "cruise" around a particular region of space looking for (or directed to) interesting exploration targets.
Surveyors are the ships that examine those interesting exploration targets in detail. Packed with shuttles, probes, drones, and multiple transporters, the Surveyors can efficiently explore whole continents in one go and can conduct detailed surveys of the flora and fauna, geological meteorological features from the entire world in a matter of days or weeks.
Explorers are the largest and most powerful of the fleet: they must combine the high cross-range of the cruisers with the exploratory muscle of the surveyors. Put another way: cruisers can spot check fifty planets and surveyors can fully explore a single planet. Explorers are the ships that fully explore fifty planets without ever returning to a star base in the mean time.
The case for the Constitutions and Excelsiors would have interesting implications:
In this scheme, the Mirandas and Constellations would both be "cruisers," though Constitution would be a "heavy" cruiser by virtue of its big navigational deflector giving it the capacity to safely operate in uncharted space (like a cruiser with a reinforced bow for icebreaking). The lack of new explorers in Starfleet would in this case reflect the fact that the Klingon menace put a greater priority on scouts and cruisers that could be more rapidly rushed to strategic flashpoints in the event of a Klingon incursion; Starfleet had to accept a temporary dominance of smaller vessels in order to continue to meet its security obligations. I might even go so far as to speculate that the new modular interior scheme was found to work much better on smaller vessels than larger ones, reducing the cost of those hulls to the point that Starfleet ceased to develop new scout and cruisers altogether and instead focused most of its resources on developing newer and explorers (and so saved money by keeping the old scouts and light cruisers in production for decades with superficial upgrades every thirty builds or so). A few new heavy and super-heavy cruisers did see some action (like the Apollo and the Nebula class and maybe even the Akiras) but in peacetime, the surveyors and explorers were the big fleet projects.