"would a game become more heavy to run if it includes more tiles?"
Technically, yes, but for modern games it shouldn't really be a concern. Games from the '80s would use hundreds of tiles at a time (these are 8x8 tiles), in the '90s they'd use thousands, now there is practically no limit except the amount of time that developers want to put into something.
I think there are basically two extremes, in terms of the way people work with tiles. On the one hand, you can just draw a whole scene without even worrying about tiles, and have software convert it to tiles automatically (likely producing a huge number of tiles). Or, you start out with categories of tiles (eg. floors, walls, furniture, etc.) and start laying them down one by one to build your scene. The best way is probably somewhere in between. For instance, you start with an image of a building, and then you copy&paste pieces of it to make other buildings.