Is there any real benefit to having a mattress, like a sleep number bed, for people with severe lower back pain?

Go for comfort. With low back pain, it is all about what feels good to your back. Some people want soft, others want firm. Some want inner-springs, other want water beds. If the sleep number bed works for you and your back feels better sleeping on it, then go for it.