I' m not well-versed in early modern warfare, but I can explained some of your questions.

First, unsurprisingly, AoE3 had many intentional historical inaccuracies, coexisting with historical inspired game mechanics. In the game, "Hussars" are classified as "Heavy Cavalry" and "Dragoons" are "Light Cavalry". In reality and in the game "History" and "Description" taps, it is the opposite. (Dragoons can be heavy or light depend on time periods and countries). The makers did not make a mistake, they decided on gameplay. They want higher tech range light-cavalry to counter introductory melee heavy cavalry. In reality, hussars are the versaltile light cavary unit that is used for scouting, raidings, persuing the enemies,...etc. In the game, in addition, hussars are also tanky, because it is the basic horse units. Actual heavy cavalry such as curaissers are restricted only to France. The lancers, in the game, is also restrictions only to Spain. In reality, these limitations did not exist.

For the tradeoff, understand this about cavalry. They are often dependant on other types of units to be effective. At the end of the Napoleonic era, the French doctrine required that no cavalry charge without infantry or artillery support. In most cases, they are only used in the beginning for scouting and only used at the end for pursuit. In those cases, a slashing shorter weapons such as a saber would allow more range of movements than a stabbing long weapons such as a spear. You don't have to deal with the risks that your weapon getting stuck.

In Waterloo, heavy British Dragoons charging at broken French Infantry columns were knocked off their horses by French lancers. Civ5 is more correct on that one unless we consider the time period. In AoE3, the lancers are heavy cavalry in the early modern eras. Heavy cavalry are used for shock purposes, that' s explained the area of effect damages from curaissers and the lancers. I would defer the last question regarding the choices to someone else.

However, to your main question: Light Cavalry such as Hussars or Uhlans are more versatile units used for a variety of purposes while heavy cavalry such as Dragoons are better for sustained combat and moral shocks. Horses get tired when charging under heavy armours too often. Lancers cavalry are less versatile and morale shocks from cavalry were less effective against bayonet infantry squares or columns. Charging the lances against cavalry is their highlights. At least that what happened in the late modern periods.

Sources:

David Chandler. "The Campaigns of Napoleon".

Rory Miur. "Tactics and the Experience of Combat in the Age of Napoelon".