Why doesn't Moodle allow Nginx/Apache to serve static resources directly? – It does: configure $CFG->xsendfile (Nginx-specific instructions).
[…] it also completely breaks CDNs like CloudFlare. CloudFlare can deliver static assets much faster and simpler than Moodle can. – Potentially faster, simpler and wrong. For example, theme SCSS can be customised, Moodle will then regenerate the cache and serve the change immediately, the CDN will serve the stale content.
A lot of "static" content is held in Moodle's caching systems so database access is not necessarily involved after the initial load. Caching is configurable, some stores will hold items in memory. Page resources like JavaScript, CSS and images tend to be read from the Moodle server-side cache and then cached by the browser for the remainder of that session.
As I see it Moodle has to walk the line between optimising content delivery and ensuring the right users get the right content in an LMS where there can be a lot of changes to data. If you look under the hood at how that's done you'll see a lot of work has gone into tuning performance.
But there can always be improvement. If you have specific examples of items which could be improved then please share them – there's a Hardware and Performance forum for discussion of performance issues – or add them to the Tracker.