The lure of ruins in art, from crumbling abbeys to all-out apocalypse, makes for a compelling show at Tate Britain, writes Laura Cumming