It changes the resolution for the entire screen, all pixels will be narrower than in H32, though you also increase sprite bandwidth to compensate (you get 80 sprites on screen, 20 per line, and up to 320 pixels per line vs 64, 16, 256), though you still need more VRAM space (and ROM) to hold those higher res sprites. (otherwise you just get narrower sprites of the same resolution, like setting an emulator to square pixels for H32 games -which often looks better regardless of that since most games assume square pixels)
The PCE's higher res modes don't add to sprite bandwidth, so you're stuck with 256 pxels/16 sprites per line (and 64 on screen) in all modes, which is the main reason the high res modes were less popular. (sprites will be smaller and take up less of the screen -or have more flicker/drop-out -especially in 512 res)
Both the PCE and MD supported the NES/SMS(etc) resolution too (5.37 MHz) but had higher res modes on top of that . . . so making the VDP resolution more programmable would have been the issue, not NES compatibility.
For most games and most TVs at the time, the higher res wasn't that big of an advantage, though having closer to square pixels is nice too (MD went too far for that really to be worthwhile in NTSC -though incidental the PAL/NTEC compromise is nice).
Given the SNES's master clock (21.48 MHz), that could conveniently be divided to 7.16 MHz, so a clock source wasn't an issue in this case either. (and the exact same resolution as the Amiga and PCE highres) Albeit that's no good for square pixels . . . where you'd really want ~6.25 MHz, and the lowest 5.37 MHz-compatible clock that comes close to that would be 32.22 MHz (dividing nicely to both 3.58 MHz and 5.37 MHz while offering 6.44 MHz too -slightly narrower than square, but pretty close and a slight benefit to PAL users too).
Overscan is only a factor for vertical resolution, but only percentage of horizontal resolution . . . actual horizontal resolution is virtually unlimited and mainly dependent on dot clock (though practically limited by composite video and RF -or other transmission- limits as well as beam precision and phosphor dot pitch).
The main issue here is dot clock, and that's what the Genesis had over many contemporaries with H40 (320 wide mode) which did indeed allow roughly 320 visible pixels on average TVs of the early 90s (some a bit less and some might actually show a bit of boarder -ie TVs with very tight calibration with no overscan or even overkill showing more overscan than normal NTSC screen spec -albeit the latter case usually only from custom calibration).
In the MD's case, this is 6.7 MHz vs the common 5.37 MHz of NES/SMS/SNES/PCE(lores) and MD H32 (among others -like the Colecovision and MSX).
The Neo Geo runs at 6 MHz and gives nearly square pixels in NTSC (only slightly wide), but only shows ~280 visible pixels on average TVs. (ie TVs that show just under 256 pixels at 5.37 MHz or just under 320 pixels at 6.7 MHz)
However, there were several computers/consoles prior to the MD that have higher dot clocks: the PC Engine has 2 higher res modes (7.16 and 10.74 MHz -the former being much more commonly used), the Amiga's lowest resolution was 7.16 MHz, the Apple II used 7.16 MHz (though only 280 pixels), CoCo used 7.16 MHz (but only 256 pixels), the A8 computers have a 7.16 MHz mode, the C64 has an 8 MHz mode (320 wide with a large boarder), Atari ST's lowest res is 8 MHz (320 pixels with a large boarder), etc. But, prior to the MD, only ST and Amiga games used higher resolutions than 5.37 MHz as routine (and, again, both are higher dot clocks than the MD -and the Amiga wasn't limited to 320 pixels like the ST so the added screen space could be nominally useful -otherwise you'd at least be guaranteed to have 320 pixels on-screen even on TVs with hefty overscan)
It's also interesting to note that the MD's H40 resolution at 6.7 MHz is a nice compromise for PAL and NTSC pixels in games that have square pixel art (ie both look a bit off, but are still reasonably close to square -hence Sonic being a wide oval in PAL and a tall oval in NTSC -unless you specially calibrate the monitor to show square pixels)
The Amiga (and PCE highres, etc) has almost square pixels in PAL, but very tall/narrow pixels in NTSC (though having the resolution at exactly 2x the NTSC color clock makes for fewer artifacts in composite/RF than more in-between resolutions in the same range on systems using similar video encoders . . . especially at higher dot clocks like the ST -though lower clocks also have issues as seen in the MD and Neo Geo -5.37 MHz seems to fare better though, perhaps as it's exactly 1.5x the NTSC colorburst)
The ST (and C64 highres) has even narrower pixels such that even in PAL they're a bit off (but much more so in NTSC . . . somewhat like pixels in VGA mode 13h with the monitor calibrated to stretch 320x200 to 4:3)
As to why the designs used those resolutions, that's really up to the engineers, both in terms of hardware limitations (memory capacity and bandwidth/speed and DAC speed, and convenient system clock rates -many systems used colorburst-centric timing) and concerns for video quality (possible artifacting at higher resolutions and pixel aspect ratio issues).

