Ahhh!, you want to master "The Way of the Horse"...
Well, to start and generally speaking they are support troops, they aren't good enough to tank, nor cities, nor many attacks, and also they are terrible at siege, they exist for the purpose of breaking enemy lines and kill Siege, Ranged units and wounded troops. They can be very Useful in a offensive war, can give you lots of advantage if the war is being developed on neutral lands, and they are really good helping in defense.
Now, there are many ways to use them but you have to note, they are good in Open terrain, where they have sight, mobility and the enemy doesn't have defensive bonuses.
Melee:
Early on they are great to distract enemy units, try to make them worry about your horses pillaging tiles or killing a bad placed unit. The Horseman is not that great, but is enough to be a big menace... Ever had to defend a Barbarian Horseman early on?... Well, it is hard and annoying as fck. Be like the barbarian!
The Knight is where the party start. The massive boost to strength is enough to break enemy lines without worrying too much for a while, as they should be treated as support troops you should have your main army near and ready to advance. You can kill wounded melee units, or erase any ranged unit that remained in a bad position when you killed the troops that were protecting them.
You need to learn to count your movements, Attacking in open terrain cost 1 movement. Rough terrain 2 movement. Rough terrain + Forest or Jungle 3 movement (Need confirmation about this...). Over river and forget about retreating... Be careful and keep your eyes open.
You should not let your horses to be surrounded in range of too many attacks, they are strong, but they are no God. If you don't want to lose them don't kill that trebuchet standing just outside of that city guarded by 3 pikemen and 2 compos + the City Castle.
Melee Horses work very good in teams, Flanking bonuses make them stronger, so does the against wounded units bonuses, they also become harder to kill. Think about them as Assassins.
UUs like Conquistadores, Cataphracts, Panzers and Mandekalu become part of your main army for a long time. You can use them with more liberty since they are incredibly strong, enough to take cities.
Later on with Tanks, it is similar, they are very tough, very mobile also, their main mission doesn't change at all.
Always try to have Medics near so they can heal faster. The main issue with them is that usually they kill, but need a lot of time to heal and attack again.
Ranged:
Skirmishers are IMO the best troop to weaken enemy lines, they can kill troops, of course, but they can also weaken the enemies so your knights or Spearmen can Kill or advance.
Specially good against melee troops, since you don't want to attack Pikemen with your Pikemen or Knights in a normal situation.
They are almost useless if the terrain is very rough too hard and risky to use... Unless your name is Genghis Khan.
I personally don't use them later on, just on defense... To be honest the skirmishers are very hard to use properly on enemy lands, at least for me. The melee horse too since if you make "the kill" on enemy lands they will be attacked a lot. So, IMO Cavalry is good to use particularly on neutral terrain and your own terrain, where you can flank your enemy without major problems and the AI will have a bad time protecting their backlines, making them a great troop for defense and a very reliable support troop if the terrain is good.