Like you, I usually try and rent an entire apartment/studio but there are some cities / countries where my self imposed budget of an average cost of £30 per night means that this is impossible, in which case I have to rent a room in someone's apartment.
I think there will always be an element of awkwardness in being the guest in a stranger's home, but of course some people are so welcoming that you immediately feel at ease and any awkwardness quickly dissappears... The problem of course is of course you don't know this until you actually arrive, although reading reviews can help. I always try and choose somewhere with lots of reviews and then read through a good selection of them.
For me sharing a bathroom is potentially the most awkward so looking for an en-suite (by using the search filters) is a good idea, although in many places this will massively reduce your choice of accommodation.
I also look for places where you have full access to the kitchen and living room as it can feel a bit awkward/unwelcoming when these areas are out of bounds to guests. (Although I can totally understand hosts wanting some privacy and not wanting stranger's to cook in their kitchen...)
Some places that are listed as being a room in someone's apartment can occasionally be almost a separate suite/ studio (own bathroom, kitchenette etc) so in those cases almost like having your own place except you share an entrance. So worth reading the listing description and reviews to try and find somewhere like that..
Also some people rent out entire apartments to Airbnb guests by the room so you share kitchen etc with other guests and not the host, but the downsides are other guests may be noisy/ inconsiderate and cleanliness of common areas partly depends on other guests cleaning up after themselves.
But if you are open minded and sociable and get a good host, it can be a great experience staying with a local resident host. They can of course also be a great source of information on the city they live in as well as good company. It's always a bit of a gamble, but the reviews do usually at least help to give you an idea of what kind of person will be hosting you. It can even be an advantage over having a place to yourself in that sense, in that when you rent an entire place you never really properly meet any locals, apart from just saying hello to neighbors etc in passing..
I think a lot of it depends on your own attitude as well, if you are positive friendly and open minded then there is no need to feel awkward and most of the people I have been hosted by have been lovely people as well as being interesting to chat to.