API: Should indexing on columns give views or copies?

[jbrockmendel]

Migrating the discussion from #33597:

We can improve performance (and memory footprint) by returning views in more cases. The downsides are 1) the returned DataFrames are less-consolidated and 2) users may be relying on getting copies in some situations.