I do this stuff for a living.
The best, and by far most expensive, is a good inspection microscope. This is what we use at work - all the techs have their own and their is one in the lab for general use by the engineers. Since these are in the range of $1k USD to $5k USD depending on features, they are not for hobbyists (unless they are very very well off hobbyists). The big advantage with them is that you can do work underneath them; they make soldering 0402 resistors quite easy. They offer a lot of magnification and also a large (50-75mm) working distance between the lense and the board, so you can solder while looking at it through the microscope.
The next best for inspection IMO is those somewhat expensive (100-300USD) USB microscopes. I haven't used one, but from the images I've seen (such as the ones above) they look very good for inspecting work afterwards. Like say you drag soldered a fine pitch QFP package, they would be great for locating pin shorts. I don't know how they are for looking at something while you are working on it. Perhaps someone could pitch in on that one.
After that come loupes, bench magnifiers, etc. They are somewhat helpful but I do not thing nearly as helpful as the USB microscopes. They are an incremental improvement of your own vision, not the leaps and bounds better provided by the USB microscope or the dedicated inspection scope.
Anyway, that's my two cents.