Alignment is often descriptive, but there are classes, noticeably Paladin, which have abilities tied to Alignment status. When used as an entire system, and actual part of the game, alignment covers behavior. That's a huge portion of the game, right?
Players take actions with their PCs and Alignment is tracked. If Alignment is going to mean anything, learning what one's alignment is must be possible within the game world. You should be able to hunt down a cleric you trust, have them Detect Alignment on you and then tell you the result. Whether they tell you the truth or not is another thing. Knowing one's alignment as a Paladin is so vital that class has Know Alignment as an at-will ability (or used to) sort of like how the Cleric can call on the power of their beliefs whenever they want (i.e. "Turning").
The world treats the character based upon their reputation and current behavior. But not just that, the world, including NPCs/monsters in it, treats everything according to their alignment too. It's not just the PCs Alignment and behavior that matter. And it can get really complex quite fast depending on where you go and what you do. That's a good thing. That's gameable and fun.
It might seem odd that there is an answer to the question "What happens if I'm charmed and I take actions to leave my alignment?" has a definite answer in D&D, but that's part of what makes it a game. We can learn through play the consequences of actions and extrapolate from them for future play.
As for "Detect Alignment" ruining mysteries, I can only say there is a spectrum of Alignment running around in most any sizable population. And "Detect Alignment" works like "Detect Magic". Only the caster can know for sure (having been told privately). Even a Paladin is in a He said, She said situation. Imagine a powerful divination that allow you to determine who really DID kill the victim? Is that still proof?