The vibe I've always gotten is that if you build and use magic correctly, you end up being ludicrously powerful and utterly obliterating bosses, but if you build and use it wrong, you end up running out of spell charges and are left impotently poking things with a stick for 3 damage. There's no real middle ground, which isn't ideal for giving a sense of progression and feels excessively punishing if you don't know exactly what you're doing (particularly, you need to skip most trash enemies instead of using spell charges on them, which relies on knowing where you're going instead of exploring). 3 may have improved this with the switch to using FP to cast spells instead of spell charges, but I haven't dug deep enough into 3 yet to have really explored build options (I got filtered by Darkflame Friede a few years ago and kind of drifted away from the game before getting through Lothric Castle; I need to go back to it one of these days).
Attunement slots are simple enough, though. Each one lets you equip a different spell, which you can change at a bonfire equivalent. Some spells occupy multiple slots, but otherwise behave the same way. There's nothing really complicated about that except trying to figure out the appropriate point in your build to invest in attunement.
This is my signature. It exists to keep people from skipping the last line of my posts.