What does a failed autopilot look like? Would a pilot do any better with an "aerodynamic stall"? I know little about planes and it seems like that'd be a big problem with or without a pilot driving.

All pilots are trained to recover from a stall.

A failed autopilot could look like all sorts of things, from just automatically disconnecting itself (usually with a loud warning alert) to issuing incorrect instructions (which is why the pilots are supposed to be awake and alert while it's engaged, watching the instruments).

Actually a key finding in AF447 was that pilots were not trained on how to recognize and recover from a high altitude stall. It is not like flying a Cessna 150. The junior first officer didn't realize the aircraft had stalled.

Pilots were trained on the procedure for recovering from a low altitude stall; 100% or TOGA thrust and power out of it while minimizing altitude loss. Training has now changed for both low altitude and high altitude stall recovery.

IIRC, part of the issue was that the two pilots issued contradictory joystick commands, which the plane averaged to zero. Which is a bit terrifying.

AF447 was a PILOT failure, not AUTOPILOT failure. The pilot didn't use proper stall recovery procedures, and put the aircraft into an unrecoverable stall.