What does it do? (better performance [and how so?]? better mileage?
cooler sounds?)
It depends. One of the goals of a performance exhaust is usually to create a tuned system:
A tuned exhaust system is an exhaust system for an internal combustion
engine which improves its efficiency by using precise geometry to
reflect the pressure waves from the exhaust valve or port back to the
valve or port at a particular time in the cycle.
Note that the focus on this sort of optimization is the flow of exhaust gas, not the elimination of noise. This doesn't mean that a well-designed system can't also be street legal but there's an excellent chance that, for common street vehicles, an exhaust system replacement will be louder than the stock equipment.
Why do I want it? If I want to race, if I want to save money, etc?
If you're not sure, there's a good chance that you don't want a new exhaust.
This should follow from #1, but I'm still curious about what the
classic get-a-performance-pipe-if scenarios are.
To be honest, the most common story that I hear from people getting louder pipes is "spent money, was louder, went slower."
Here are some situations that have applied to me over the years:
Muffler on normally aspirated car rusted out. Sourced a replacement cat-back system that claimed single digit horsepower increases based on the manufacturing company's racing efforts. The system used stainless steel so it was also less likely to rust and significantly lighter. In the end, I paid hundreds of dollars for a few horsepower and more noise (admittedly, it sounded really cool).
Exhaust header on normally aspirated car lost an internal weld, turning the engine into Buzzy the Hummingbird. Replaced header with stainless steel (see above).
Muffler on turbo car got really rusty. Replaced with stainless steel with better flow. Very modest boost in power from increased exhaust flow (more important in a turbo car).
Replaced turbo up-pipe (the primary path of exhaust gas to the turbo's turbine). Significant increase in performance.
So, some basic guidelines drawn from my experience:
Replacing anything near the muffler is likely to increase noise significantly with only modest increases in performance.
Reducing weight is always a good idea.
Reducing rust risk is great, too.
A change to a forced-induction engine's exhaust components have the greatest effect near the "forcing" part of the system (e.g., the turbo). Things aren't so obvious for a normally aspirated engine.