There is a fear of confrontation mainly because it’s reported as news whereas the vast majority of interactions with the public when flying a drone are positive (in my experience). But positive news tends not to be newsworthy!
A sub-250-gram drone gives you more options for places to fly. The DJI Mini 4 you are contemplating is ideal.
The law around drone flying is relatively straightforward and has been presented in a simplified form as The Drone Code. Follow that and you will be ok. And the Drone Code can be further simplified into a simple motto - Don’t be a Dick!
There are exclusion zones around airports. Want to fly in them? Don’t be a Dick! - get permission first from the relevant authority. They are normally (again, in my experience) very helpful.
With a Mini 4 you can fly close to people. But you don’t have to. If it will annoy them or invade their privacy - Don’t be a Dick!
Regarding SSSIs. As others have said, it depends on the reason for it being an SSSI. Would you let your dog run around in it? Grasses and insects - possibly. Nesting birds -no. Common sense applies - or the motto Don’t be Dick!
tl;dr
Fly with forethought and courtesy, consider others. You’ll enjoy it!
Don’t be a Dick!