In ancient and medieval times, ranged cavalry were good against both heavy cavalry and heavy infantry (spear dudes) because of their speed. They lost to light infantry (archers), which typically had better accuracy and longer-ranged weapon (consider the difficulty of firing from horseback and the smaller bows used compared to the gigantic bow used by longbowmen).
In the 16th century, ranged cavalry (basically what we are calling Dragoons, though they are more accurately named reiters) revolutionzed combat. They were armed with pistols to fire at heavy infantry (pikemen) and sabres to charge at light infantry (crossbows and early muskets). Remember, that medieval knights were great individual warriors, but they weren't technically cavalry -- they didn't coordinate attacks well or move or fight in formation. Reiters did. To defend against them, infantry had to adopt very strict formations, such as the "Spanish Square," which mixed pike and musket units tightly together to support each other.
With the development of the bayonet and more accurate muskets, infantry were able to out-shoot the ranged cavalry, and stand up to a cavalry charge. Ranged cavalry essentially went away, and the difference between light and heavy cavalry had more to do with the size and armament of the cavalry dude.
In AOE3 terms, Dragoons are good against hand infantry (because they are speedy and ranged) and hand cavalry (because they get bonus damage). They aren't bad at raiding or sniping, but their attack is low. They lose to Musketeers and Skirmishers, because those types have good range, and the Musketeers in particular are deadly hand-to-hand. I still find them versatile enough that I make Dragoons a lot, though I match them with hand cavalry or something else.