In your example, only the new lot would be taxed at the higher level. Our tax team and California precedent (you mention prop 13) advise that the existing parcel's tax burden would not change, just the new APN. So, in your example above: yes, the state and municipality would receive more income from the new unit, and yes the homeowner would earn a profit from splitting their lot, but no, their taxes would not dramatically increase.
Historic conservation areas (as determined by the local municipality) are exempt from SB9, and cities are allowed to put in place "objective design standards" that apply to SB9 as well as new single family housing. But, we're also a team of great designers developing many unit types that will complement what's already there.
I personally live in this kind of density and really enjoy it. I have great relationships with my neighbors. I think you'd find that many people use their backyards much less than they would initially plan to, and many would happily trade it for income if they had an opportunity like this.