The most useful and simplest covering axiom is compactness: every open cover has a finite subcover. This has many applications and is well-behaved wrt topological operations like products.
Classical are also the "cardinality variations":
- Lindelöf : every open cover has a countable subcover.
- Countable compactness: every countable open cover has a finite subcover.
And this generalises easily to all sorts of $(\kappa,\lambda)$-compactness for cardinals $\lambda \le \kappa$: every open cover of size at most $\kappa$ has a subcover of size $< \lambda$, so that compactness becomes $(\infty,\aleph_0)$-compact, countably compact becomes $(\aleph_0, \aleph_0)$-compact and Lindelöf $(\infty, \aleph_1)$-compact (with the convention that $\infty$ means no restriction on cardinality at all). One can now study preservation of these by products of various sizes, etc.
Another way to vary is to consider refinements instead of subcovers. But the property that every open cover has a finite refinement (easily) turns out to be equivalent to compactness. The property that every finite open cover has a finite closed refinement (that is also a cover!) is (almost?) equivalent to being $T_4$, so we are getting non-trivial properties, close to normality. Instead of finite we can ask for point-finite (every point of $X$ is in at most finitely many members of the cover) or locally finite (every point has a neighbourhood that intersects at most finitely members of the cover, this implies point-finite of course), or even star finite (every member of the cover only intersects at most finitely many other members of the cover, this implies locally finite for open covers). Here we do need to consider refinements instead of subcovers:
$X$ is compact iff every open cover has a point-finite subcover. (see Brian's answer for a proof)
So we get a possibly new property by demanding that every open cover has a locally finite (or point-finite, or star-finite) open refinement. This is the famous paracompactness property (or weak paracompactness/metacompactness for point-finite and strong paracompactness for star-finite refinements). It has the nice property that paracompactness plus Hausdorff implies normal (as with compactness)
and that is preserved by closed continuous maps (Michael's theorem). It's not very well-behaved under products but Stone showed that all metric spaces are paracompact, so there are many non-compact examples.
Further seeming weakenings or variations of paracompactness turn out to be often equivalent to paracompactness: $X$ is paracompact iff every open cover has a $\sigma$-locally finite open refinement, or a locally finite closed refinement etc. See my notes here and here and here e.g. So paracompactness is a nice middle ground with the most theorems (like the existence of partitions of unity which also turns out to be equivalent).
It's not real intuition as such, but more a motivation how this property has been defined after a lot of related results in metrisation theorems that showed the usefulness of working with locally finite families and refinements of covers (Bing-Nagata-Smirnov etc.). The Handbook of Set-Theoretic Topology (from 1984) has a whole chapter on more properties like this (further weakenings that are not equivalent any more) and their relations. Not a lot of research is done any more in these, is my impression.
As to your last question: ordinals often not paracompact spaces in their order topology: $\omega_1$ (the first uncountable ordinal) is a standard example space that is not paracompact in the order topology e.g.