Macro photography is a rabbit hole unto itself.
THIS IS A WORK IN PROGRESS: PLEASE CHECK BACK SOON.
Even the name "macro" can be confusing...
For those of you who do or ever studied economics, you might think that micro photography is the small things, and macro photography is the "big stuff". Normally, you'd be right, in any other field but photography. Counter-intuitively, "macro" photography is about photographing small things, like insects, flower stamens, tiny crystals, or detailed gears found in watches. It's not usually as small as microscopes, but it can be. This is usually described as "extreme macro photography".
So why macro, and not micro?
Believe it or not, in the early days of photography, they didn't exactly have the lenses we have today. So while microscopes were around, the best microscope lenses back then were aimed at scientists doing live viewing. A human eye's iris is only 12mm (half inch) wide at it's widest diameter. Simply put, microscopes were simply not capable of projecting the captured light large enough for the often massive medium and/or large format films/plates that most cameras used back then.
So when technology improved, cameras got smaller, and so did their films/sensors. Meanwhile, special lenses were developed to project an image of an item that was potentially multiple times larger than the original object film (or sensor these days), hence making the image larger, or "macro" comparative to real life.
Ultimately, technological developments simultaneously made smaller sensors work better, and lenses project clearer and larger images... so they basically "met in the middle".
Magnification, focal length, effective aperture, working distance, and "depth of field" work very differently in macro.
Most people intuitively grasp that "blowing the image up to a larger size", smaller details become much more visible.
However, what's not intuitive, is that most "macro" lenses do not enlarge the image at all.
Wait! What?!
Now, the majority of macro lenses sold today are literally 1x magnification. It doesn't matter if they're 50mm, 65mm, 100mm or even 180mm in "focal length". So if you shoot a grain of rice, the image projected onto the film/sensor will be.... the size of that grain of rice!
But wait, if macro ends up with a magnified image, what else but the lens could do that?
Well... you have to understand, that "full frame" sensors are only 35mm x 24mm (or 840 mm squared)... that grain of rice, at 7mm long and 2.5mm wide... while only 17.5mm squared, the 1:1 scale image of it still takes up 2% of that sensor's total physical area. That doesn't sound like much... but when most sensors are 23-50 megapixels (MP)... on a 45MP camera like the Canon R5, that's nearly an entire megapixel used on a single grain of rice.
If the entire screen of a standard 1080p TV is 2.07MP, and you just used 45% of that on a single grain of rice, you can bet that you'll see some details. Can you imagine what size that image would be on a 27" 1080p monitor? (the grain would be 42cm/17" long, and 15cm/6" wide).
Imagine that now that rice grain is shown on a 55" 1080p TV or even a 125" projector was used... it's a lot bigger, but that 1MP of details will start looking blurry pretty quickly if blown up that much. The more details (more megapixels) the larger you can blow an image up without seeming blurry or lacking detail.
Resolution really matters, but so does sensor size!
Obviously you can't just keep magnifying more and more with the same amount of details. If you've ever spent any time close to a bill board, you'll know that each pixel might be an inch squared or even more. It's anything but sharp "up close".
That said... what if you take a photo with a lower resolution sensor, but also a smaller sensor? If you have a budget mirrorless or DSLR with a "APS-C" (smaller sensor 25.1mm x 16.7mm totalling 419mm squared. Or half the area of the full frame. Now if you have only 23MP (roughly half the megapixel of the Canon R5 mentioned above) with a half-sized sensor, the you'll get roughly the same number of megapixels on that grain of rice... so the detail is likely the same as the R5 scenario... on a much cheaper camera!
What about a fun example?
Imagine you have or can use a Canon R7. While it does have the smaller APS-C sensor (half of full frame area), but it still packs 33MP (much more than half the resolution). Now, the rice takes 4% of the smaller sensor area but that sensor has more than half the megapixels of the R5, so it basically packs in more details per square millimetre than the R5. That rice grain, now has 1.38MP worth of detail. That's a 47% increase in detail over the R5 example above. Meaning, you can blow it up another 47% in size while keeping the details sharp!
Ultimately, you can get more details with a cheaper (perhaps even older) camera than you can with a flagship modern camera in the right circumstances. If, you can get enough light on your subject. Which in most indoor macro rigs with numerous flashes... isn't a stretch.
What I'm really trying to say here, is that with most macro lenses, the lens doesn't magnify images at all, but the display of that image afterwards on screen or in print... is when the real magnification happens.
Then why have macro lenses of different focal lengths if most all offer 1x magnification?
Honestly, this comes down to how far away you want the lens to be while it's focussed. When it comes to macro, stationary objects like gemstones and watch gears don't care if you practically rest the front of your lens on them, so shorter focal distances offer an affordable option. However, living insects don't like you inserting a lens many times their size into their eye sockets. So working further away with macros that have longer focal lengths helps to keep them still.
Most people, using non-macro lenses for "close up" work, run into the issue of minimum focal distance... if a subject gets too close, you can't focus. With macro lenses however, issues arise from maximum focal distances where you still need to focus, albeit at extremely close range. Now, I'm not saying that a macro lens can't focus at long range, many can... but you can't do that and be close to something while keeping it focussed as well.
The maximum range a macro lens can focus on close stuff is called "working distance". It's literally measured from the subject to the front element (piece of glass) of your lens, and that "room to breathe" So a 65mm macro is great at shooting inanimate objects, but a 100mm or 180mm macro will allow you to shoot from greater distances, making scaring off insects and such less likely.
Greater working distances also offer an additional advantage.. with macro, your lens can get very close.. and if you have flashes are not mounted to the lens itself, you may find that the camera/lens shades your subjects, causing lighting issues.
However, this usually comes at the cost of light gathering capability, working distance, and naturally, depth of field!
Starting macro at the cheap end
You don't have to buy special lenses to get great close-up shots. Many lenses can focus as closely as 30cm (1 foot) or so, and some closer. Getting as close as possible using the lenses that you already have is a great first step.
That said....
When you still want to get closer, a third-party set of "extension rings" or "extension tubes" will allow you to get closer. You insert them between the camera and the lens. By pushing the lens further away from the sensor, you lose the ability to focus at infinity (or even long range shots), but you reduce that minimum focal distance. The bigger the ring, the closer you can get. If you stack the rings together, you can do that, but the light reaching the sensor decreases.
The best set of extension tubes/rings is one that passes the electrical signals from the camera to the lenses. There's no glass in it, so I found the 3rd party models vastly cheaper than on brand offerings, and just as good.
Putting the lens on backwards!
Did you know that most lenses can be turned into macro? Just put it on backwards!
Another cheap-ish option is to mount a lens on backwards. Yes, you buy a special adaptor (see thin object between lens and camera?) that screws into the front filter thread of your lens, and you connect that to your camera to the adaptor. Note don't do this for your good lenses, dust/rain will get in!
The setup shown has an additional special adaptor for connecting electronically controlled lenses via a cable to the camera while reversed. However, most people use old manual film camera lenses and a reversing adaptor... and adjust the aperture/focus manually. It works just as well!