Jorge Sampaoli's side looked to be on course for a trophy in his first competitive match in charge of Sevilla after Franco Vazquez and Yevhen Konoplyanka had cancelled out Marco Asensio's stunning opener.
However, Sergio Ramos rescued extra time in the 93rd minute before Carvajal stole the win with a fine solo effort to fire his side to glory.
Madrid went into the match without the likes of Cristiano Ronaldo, Pepe, Gareth Bale and Toni Kroos following their Euro 2016 exploits, but that didn't prevent them from making a quick start and Ramos was the first to threaten with a header in the seventh minute.
Asensio was handed his debut following a bright pre-season, and his first sight of goal came shortly afterwards when his low drive was well held by Sergio Rico in the Sevilla goal.
The Europa League holders did start to settle into the match, but there was nothing Rico could do to prevent Madrid from taking the lead midway through the half as Asensio fired an unstoppable 30-yard strike into the top corner.
Sevilla, who were appearing in an unprecedented third consecutive Super Cup, finally created their first chance of the match shortly before the half-hour mark when Franco Vazquez stood the ball up for an unmarked Mariano at the back post, but the Brazilian sliced his volley well off target.
Kiko Casilla was called into his first piece of action shortly afterwards when Daniel Carrico strode forward before drilling a low, swerving effort towards goal that the Madrid keeper did well to parry away.
The equalising goal did arrive with less than five minutes remaining of the first half, though, and again it was a debutant who got it as Franco Vazquez steered a well-controlled volley into the bottom corner, opening his Sevilla account following his summer move from Palermo.
Madrid did have a chance to restore their lead before the interval when Lucas Vazquez reached the byline down the right and dug out a cross which found Isco, but his flicked header was too close to the keeper.
Isco was the first to threaten in the second half too when he collected a pass from Asensio before working space for a shot, only to see his effort deflect narrowly past the post.
Madrid were bidding for a third European Super Cup triumph following their successes in 2002 and 2014, the latter of which came against Sevilla, but they saw another chance go begging shortly after the hour mark when first Karim Benzema and then Ramos failed to convert headers.
Sampaoli turned to Konoplyanka from his bench in an attempt to win the game, and within four minutes of his introduction the Ukrainian winger had given his side the lead from the penalty spot.
It was Ramos who conceded the spot kick for a trip on Vitolo as he cut inside, and Konoplyanka coolly sent Casilla the wrong way to roll the ball into the bottom corner from 12 yards.
Just as Sevilla looked to be heading for the victory, Ramos redeemed himself in the third minute of stoppage time by levelling things up, applying a simple headed finish to Lucas Vazquez's cross to force extra time.
Madrid began the additional 30 minutes on top too, with Benzema testing Rico in the very first minute before only a last-ditch block from Nicolas Pareja denied the French striker another shot on goal.
Zinedine Zidane's side had the momentum on their side, and Sevilla's task got even harder just four minutes into extra time when Timothee Kolodziejczak picked up his second yellow card for a mistimed challenge on Lucas Vazquez.
The pressure continued to arrive from Los Blancos as first James Rodriguez stung the palms of Rico before the Sevilla keeper could only punch a cross as far as Luka Modric, who fired his first-time effort over the crossbar.
There was more controversy in an action-packed opening half of extra time when Ramos appeared to have doubled his personal tally against his former club with a clinical diving header, only for his celebrations to be cut short by the referee for a foul in the build-up.
Madrid's pressure continued in the second period of extra time, and Sevilla needed Rico in fine form to keep them in the game as first he denied James after the Colombian had been cleverly found by Benzema.
Another defence-splitting pass, this time from James, sent Lucas Vazquez clean through with less than 10 minutes remaining, but once again Rico was equal to it before Adil Rami completed the clearance from just in front of his line.
James was then denied by Rico with a well-struck volley from the edge of the area before Casemiro became the latest player to be thwarted by the keeper from range.
The winning goal did finally come in the 119th minute, though, as Carvajal danced his way into the area past a number of statuesque defenders before picking out the far corner with the outside of his boot to secure the trophy for his side.
The victory sees Zidane become only the fifth man to win the Super Cup as both a player and a coach, while Sevilla now share the unwanted record of four losing finals with Barcelona.
That includes three in a row for the Andalusian outfit, while Madrid's win means that the European Cup holders have gone on to win the Super Cup four years in a row for the first time ever.