What is the in-setting lore reason for the Constitution-class being retired so early while the Miranda-class was kept around for roughly a century longer?

Could be due to crew numbers? I mean compare pikes Enterprise to TOS era, and double the crew gets you a lot less quarter space (pike's quarters in particular clearly get subdivided to hell by TOS). Maybe they were too cramped with increasing crew numbers.

Looking through MA it mentions they were meant to have an operational lifespan of 18-odd years, given their battle positions and exploration use (both high attrition) it mught be that they were seen as the sweet spot between endurance and disposable

Maybe it's just that there were fewer of them and their captains kept destroying them

There's a couple things here. The most obvious explanation is that there just weren't very many Constitution class ships in service, and their attrition rate was brutal.

As for why there were so few in the first place, and why more were not built to replace the losses, the conventional wisdom prior to Discovery coming out (and perhaps it still holds) was that the Connie was actually substantially less automated, and less well designed to facilitate automation, than it's rough contemporaries and immediate successors. The Connie refit gives some support to this assertion in that an extremely extensive rebuild was apparently necessary to get the ship to modern standards only 20ish years after their original commissioning. Such an extensive refit process could very well have still fallen short of what a brand new ship designed from the ground up to use the latest tech was capable of, meaning that it was more cost effective to crank out Excelsiors where frontline ships were needed, and far more cost effective to build Mirandas and Oberths to do lightweight tasks in safe areas.

In other words, the Constitution class was an awkward, inflexible, and inefficient design which happened to be in the right place, at the right time, and just good enough to have a staring role in a key period of Federation history. It carved it's niche and made it's mark, but was rightly supplanted by better ships as Federation technological and industrial capacity progressed into the late 23rd century.

I think looking at the ships' different roles may answer that question. The Miranda-class is a backline support and science platform, only called to the front during extreme circumstances (Wolf-359, Dominion War). They can be efficient at simple taskings like specific missions or supply runs. Like its successor, the California-class, they're also the mass produced, easy to build/repair workhorse type, so a simple, tested design just works over a longer life time.

Whereas the Constitution-class is a much larger, more complex, and line serving Heavy Cruiser. Presumably being on the frontline of exploration and military intervention, they have a higher need for the bleeding edge tech The Federation can field, especially with the fleet flagship USS Enterprise. Its unique hardware would also make it harder to repair and maintain which probably affected the lifespan of the class as a whole.

Like others said, it's most likely more sensible to do a full redesign whenever a new frontline or special class is called for instead of relying on retrofitting to keep up with enemy tech advances. Especially with core components like warp core upgrades, computing types (ie Galaxy-class moving to isolinear), or completely new tech like the Intrepid-class' bio-neural circuitry. That could maybe answer the shorter lifespan only lasting 1-2 retrofits before class charge (ie NCC-1701-A/B to the NCC-1701-C to the NCC-1701-D/E)

I think looking at the ships' different roles may answer that question. The Miranda-class is a backline support and science platform, only called to the front during extreme circumstances (Wolf-359, Dominion War). They can be efficient at simple taskings like specific missions or supply runs. Like its successor, the California-class, they're also the mass produced, easy to build/repair workhorse type, so a simple, tested design just works over a longer life time.

Whereas the Constitution-class is a much larger, more complex, and line serving Heavy Cruiser. Presumably being on the frontline of exploration and military intervention, they have a higher need for the bleeding edge tech The Federation can field, especially with the fleet flagship USS Enterprise. Its unique hardware would also make it harder to repair and maintain which probably affected the lifespan of the class as a whole.

Like others said, it's most likely more sensible to do a full redesign whenever a new frontline or special class is called for instead of relying on retrofitting to keep up with enemy tech advances. Especially with core components like warp core upgrades, computing types (ie Galaxy-class moving to isolinear), or completely new tech like the Intrepid-class' bio-neural circuitry. That could maybe answer the shorter lifespan only lasting 1-2 retrofits before class charge (ie NCC-1701-A/B to the NCC-1701-C to the NCC-1701-D/E)