In lore, do ships sometimes get destroyed by their own cannons? How often do they explode? I'm glad this isn't a thing in gameplay.
Not very often. The water-borne platform of a ship can support large, overengineered cannons that can be properly reinforced.
Of course, it makes them slow, but hey, better than nothing.