Mass x Speed= Force. bigger horses, bigger weapons make bigger crush. The question of morale and training of cavalry is also vital. A regular horse will, understandably, shy away from a large group of men, let alone armed and violent ones. Larger horses are also scarier for the infantry they face. As a reenactor myself, and having been at the receiving end of a charging horse (scripted mind you), I can tell you it's bloody terrifying. As for light cavalry, being without armour and with breeds designed for speed and stamina, will not want to engage in melee. Neither will their riders, if they have any sense.
To use your cannon vs missile analogy, the damage of the cannon is a simple high-speed impact, whereas the missile's explosives will affect a wide area. Bear in mind that "racing" horses are not bred for combat, there are few breeds today that are.
The two handed lance is not for extra reach, but extra power, it puts more momentum of horse and rider into the tip than a one handed lance. Late Medieval lances were one handed, but a world away in terms of design. As for going through multiple targets, unlikely but possible, probably more for piercing thick shields and armour. The impact of a charging horse and rider will be deadly to an open or disorganised formation, as people will be thrown to the floor and trampled. A strong, tight formation, even without pikes or spears, will fare much better. With a long lance and a big mount, you can turn that formation into a terrified and open one but, again, few horses will charge in this case. After a successful charge reorganising your cavalry, calming the horses and stopping your men from running down fleeing enemies is not easy so even a well trained unit couldn't do that repeatedly.
Light cavalry, being without armour and with breeds designed for speed and stamina, will not want to engage in melee. Neither will their riders, if they have any sense.
In the end it's so hard to figure out how battles were actually fought. We are forced to infer things from the designs of equipment/contemporary artwork/knowledge of physics.