To understand how extension tubes work, consider how close-focusing works. With many lenses, as you focus closer, the length of the lens increases. Or to put it another way, the optics inside the lens move away from the camera as the subject moves nearer. Some lenses have internal focusing, meaning that their elements move back and forth within the barrel and the length of the lens does not change, but the principle is the same.
The closest focusing distance is reached when the lens is extended as far as it will go or the elements are moved to their furthest point. This focusing distance varies considerably from lens to lens – for example, the Canon EF-M 22mm f/2 STM lens enables you to focus on a subject just 15cm from the camera's sensor, while the EF 800mm f/5.6L IS USM has a closest focusing distance of 6 metres. But the magnification of the subject (actually a reduction) stays within a narrower range, from about 0.1x to 0.3x.
Why do lenses stop extending? Partly because it costs more to design and manufacture lenses with longer extensions, partly because it is difficult to design a lens that gives high-performance results over a wide range of focusing distances.