I'll give my Nikon options, but I'm not sure if there is an equivalent Canon option.
I used to shoot an RX100 II, but I got frustrated with poor autofocus speed. I too was frustrated with wet lenses and working distance.
I went full frame, taking my D810 under in a Nauticam housing. I shot macro using the Nikkor 105mm macro lens and (briefly) the 60mm macro. I later upgraded to the D850 (amazing autofocus), with the same 105mm lens. During the time I shot those cameras (2016 to 2022) I did a lot of macro shooting, and I determined that 105mm was a bit short for full frame. I was constantly cropping, often to about a DX/APS-C area. For that lens, a Nikon D500 might be perfect. But for me, there was a certain distance I could approach animals, and closer than that led to the animal fleeing. Based on my cropping, I figured a 150mm macro would work the best. And indeed it did - I put a 1.4TC on the 105 macro to give me 150mm, and I was then able to fill the frame at my normal approach distances.
However, I did not like the slight degradation in sharpness, along with slowing of the autofocus speed, so I took off the TC and never used it again. I try to get closer, and I just crop as needed.
For the REALLY small stuff, I have no answer yet. I have thought about add-on wet lenses like the SMC-1 or CMC-1, and I think if you are going to shoot creatures of the size of Wire Shrimp and smaller, you're going to need some way to get to 2X or more, possibly along with a tripod.
When it gets down to it, underwater photography is quite challenging. I think that's what makes it so fun when you get a good shot from your efforts. It actually takes effort, skill and good equipment to do well, though the definition of 'good equipment' can vary widely in price.