Well, I frequently travel to hot places in the hottest times of the year, but I am a sun worshipper so I can more or less handle it.
Preparation is key!
There is a reason the "siesta" exists--do your sightseeing early AM and later PM, and enjoy a nice relaxing meal mid-day.
First, I try to find hotels with air conditioning, big swimming pools, and comfortable locations. This does not always work in a big central city, but are you coming to Spain to just see Madrid, or to see more of the country? Not sure I would hop all the way over to Valencia by train just to go there, but I would fly to Mallorca (ha-ha).
In Madrid you can certainly sightsee INSIDE places, like the Prado, and the Royal Palace, which more or less removes the sun and heat issue. Cathedrals and churches are also good options. The Plaza Mayor is always buzzing later at night when it is a bit cooler, and it is so nice to stroll around and have some tapas and drinks.
If you are not able to walk on hot days, you can hire a taxi to show you around, or you could try out a Tuk-Tuk tour.
If you head to the South you can actually find some fabulous beaches if you like that kind of outing!
Yes, the North will definitely be more refreshing, but they sometimes have warm days as well. Many people go there to enjoy nature rather than historic sights.
You need to figure out exactly what you want to see and do in Spain before you finalize the trip, as it may be easier to fly into Barcelona or elsewhere rather than Madrid.
Have fun planning!