Venice is an extreme case, I agree. That's one of the places that we visited that had the small kind of bed tax I'm thinking of; unfortunately that probably isn't fixing their problems. Many of their tourists stay in cruise ships (which have to have the lagoon dredged to support them; sinking Venice itself...); a bed tax doesn't target them, and they're completely and utterly overwhelmed regardless. It was a fascinating and deeply depressing place to visit.
A bed tax in general, applicable to both hotels and AirBNB, I suspect probably deals better with the other end of the spectrum. At the end of the spectrum I'm thinking about (and probably my co-poster), extra funding is necessary for the municipalities that can sustain further growth of their tourism industry, but are struggling to keep up with demand. New Zealand falls somewhere in here; we have significant problems with tourists literally shitting on walking trails because the previous Government dropped all its tourist budget on global advertising, and didn't anticipate the sheer success they'd have; we therefore lack basic amenities like toilets in many popular tourist spots (or -- if there are toilets -- they're long drops maintained by the Department of Conservation, which most tourists won't use). We can do better with a little extra cash; I believe the new Government is currently investigating a border tax, but a bed tax would help the regions too. Other major metropolises popular with tourists like Paris, Rome, Barcelona etc. could use the extra cash in broadly similar ways; supporting the growth of transit systems and developing infrastructure that supports and promotes distributing the tourist load to other attractions.
I suspect that if you were to put in a large bed tax with the explicit goal of discouraging visitors in places like Venice... it might not work? The rich would put up with it and go anyway. You'd probably cut out many of the poorer visitors, but I doubt you'd see a big net drop unless the tax approached a point that it killed the tourism industry altogether, and sunk the local economy. I suspect that for somewhere like Venice, there needs to be stronger intervention; the category of idea I'm thinking of is a lottery system like the US parks or something.