Suppose that a random variable $X$ takes only values $n^n$ for natural $n$, with probabilities $c/n^{3n}$, with $c:=1/\sum_1^\infty1/n^{3n}$. Then $EX^2<\infty$. However, $\bar F(n^n/2)\sim c/n^{3n}\sim \bar F(n^n/(2\lambda))$ for any real $\lambda>1$ as $n\to\infty$. Taking here any large enough $\lambda>1$ such that $C\lambda^\alpha<1$, we see that $\bar F$ does not have the upper Matuszewska index $\alpha$, for any negative real $\alpha$.

The answer is no: $\bar F$ does not have to have any negative Matuszewska index (any tail function $\bar F$ trivially has any nonnegative Matuszewska index).

Indeed, take any negative real $\alpha$. Suppose that $P(X=n^n)=c/n^{3n}$ for natural $n$, where $c:=1/\sum_1^\infty1/n^{3n}$. Then $EX^2<\infty$. However, $$\bar F(n^n/2)\sim c/n^{3n}\sim \bar F(n^n/(2\lambda))$$ for any real $\lambda>1$ as $n\to\infty$. Taking here any large enough $\lambda>1$ such that $C\lambda^\alpha<1$, we see that $\bar F$ does not have the upper Matuszewska index $\alpha$, for any negative real $\alpha$.