One 8x8 tile takes 16 bytes of nametable data, not 64.
There are a couple hundred mappers and variants, but the 4 most common ones (thinking of cnrom, unrom, mmc1, mmc3) cover 90-something percent of all cartridges out there, and don't have the address increment that you're describing. Most of them just cover bank switching, or (like MMC3) add some IRQ stuff under certain conditions. There are cartridges with extra copy protection, extra audio channels, etc. As you said, the ASICs do basically arbitrary things, but it's not like you have to learn one of the really esoteric mappers.
The most common mapper behaviors are comparable to bank-switching on other systems.
I'd say that the SNES gives NES a run for its money, in terms of weirdness. The ASICs in many of the cartridges there included whole processor cores.