What's the use of not relaying non-standard transactions if anyone can still use them using P2SH? It seems that non-standard transactions are not normally relayed because there is a concern this might break the network or it might make future upgrades harder.

But anyone can use these non-standard transactions (and get them relayed!) using P2SH. So what good is this restriction on non-P2SH scripts?
As you say, there are two separate concerns here:


There are some scripts that might cause harm to the network.
There are some scripts that might make future upgrades harder.


For the case of harm to the network, the non-standard transaction check was first implemented by Nakamoto before P2SH existed, so it couldn't be so easily circumvented.  This gave developers time to better analyze the script language and fix problems with the remaining opcodes (e.g. see cases like this where it was possible to create transactions that would take a very long time to verify).

However, even if the scripting language is perfectly safe, each script has to be stored by every full node until it is spent as part of the Unspent Transaction Output (UTXO) database.  Since scriptPubKeys are limited to 10,000 bytes, this means that an attacker can add up to 10KB to the UTXO set for every output he creates, potentially quickly adding enough data to degrade performance enough that the rate of stale blocks (orphan blocks) mined increases, which would reduce miner profits and encourage them to centralize further to recover that lost revenue.

That'd be bad.  Happily, there's an easy solution: if we use P2SH or segwit P2WSH, we only see the full script at the point when the output is being spent---when we can remove the output from the UTXO set.  This is somewhat inferior with P2SH due to it limiting scripts to 520 bytes, but P2WSH fixes that problem and restores the earlier limit of 10,000 bytes.  So that's the security reason why we require complex scripts today use P2SH.

For the upgrade case, there are some opcodes we don't want people using.  Most notably these are opcodes that might be redefined in the future, e.g. the OP_NOPx opcodes that have been used for soft forks in the past (OP_NOP1 became OP_CHECKLOCKTIMEVERIFY and OP_NOP2 became OP_CHECKSEQUENCEVERIFY).  Lately, Bitcoin Core 0.16.1 has stopped relay of OP_CODESEPARATOR in non-segwit in preparation for another potential soft fork that will reduce some lingering problems with expensive verification.

In those cases, standard transactions forbid both the scriptPubKey and the redeemScript (P2SH) versions (and, when applicable, the segwit P2WSH version), so the easy circumvention isn't possible in that case.