The melody is from a French song, Ah! vous dirai-je, maman
The first known publishing of the melody dates from 1761, who knows how much further back it goes.
Mozart wrote some variations based on it, which probably helped popularise it a lot greatly
Nursery rhymes, sport chants, folk songs, political rally songs etc. often use existing melodies that are already well remembered, and just change the words, this happens with a lot of melodies.
This particular melody? Musically, it's simple, memorable, and easy for children and adults to sing and play because of its short range (6 notes, less than an octave). It's rhythmically repetitive and pretty much as simple as you can get: 1 2 3 4 5 6 7 (8) x6 which makes it very easy to put words to, and very easy to remember. Also more subjectively, it's a lovely little melody, it's not surprising it's stuck around.
The question as to why twinkle twinkle is such a memorable and well loved melody is despite its simplicity is difficult to answer without either being too technical or too hand-wavy, but suffice to say that it packs a lot of content into it short and sweet package. It's conversational, it sort of raises a question and answers it. It has a sort of tension and release, with the middle section being reassuringly answered by the same melodic phrase that came at the beginning. The harmonic structure is perfectly resolved: I'd be hard pressed to think of a more "complete" sounding short melody.
I think it's just one of those melodies like "oh when the saints" "amazing grace" "O tannenbaum/Oh Christmas Tree/The red flag etc." - which knows exactly what it is, doesn't have any unnecessary frills: every note is pretty much there for a purpose, and it makes them pretty timeless. "Amazing Grace" and "Oh when the saints" function very differently harmonically to "Twinkle Twinkle", but the point I just made applies to them both. But if it was easy to exactly quantify what makes these melodies so timeless, then we'd all be writing them. There's an element of tradition of course, but there's more to it than that. I'm sure people have written long books about it.