otticelli's Venus has parked her shell outside your house and come to pay a night-time visit. The glowing strands of her hair glitter against blackness as she looks past you at a mystery only she can see, while your eyes follow those river-like locks down to a hand that gathers a looping cascade between her thighs. She is, unmistakably, the goddess from Botticelli's The Birth of Venus in the Uffizi, Florence. This very similar painting from about 1490, also by Botticelli and his workshop, suggests his Uffizi masterpiece was already such an icon that someone commissioned this version without the zephyrs and green sea, just that goddess body.

So it goes. Venus, in art, is just a word for "female nude". Botticelli started a modern appetite that has never ended. From his hypnotic paintings a host of lush sex goddesses rose up, or reclined, to rule European art through to Michelangelo Pistoletto's ironic 1967 assemblage Venus of the Rags, also here, in which a heap of old clothes overflow from a corner around a marble nude seen from behind, the mix of beauty and chaos resembling a rubbish-strewn Naples piazza.

But this exhibition is not called Venus. And it doesn't tell her artistic story except in bits with vast lacunae. Instead, it sees Venus as a cheap Roman substitute for the original Greek love goddess Aphrodite, who in turn is exposed as a ripoff of a matriarchal deity once worshipped on Cyprus. The result is a mixture of world-class loans and an archaeological hobbyhorse.

Both the quality and gaps hit you straight away with a classical marble Crouching Venus (sorry, Aphrodite), lent from the royal collection. There's no exploration of its impact on later art. In fact, this Roman copy of an ancient Greek original was sold by Cromwell's republican regime after the beheading of its royal owner, Charles I. It was acquired by the artist Peter Lely who painted his own mysterious, nude, anti-Puritan masterpiece Nymphs by a Fountain. After the Restoration he also portrayed Nell Gwyn nude – his interest in this Venus was perversely creative. Before that, Rubens saw this same statue and based his painting Venus Frigida on it.

Which is another way of saying that the story of Aphrodite/Venus in art is massive, epic and complex. Perhaps the comparatively small size of the exhibition can't hold all that, yet a lot of space is wasted. Still, it has to start somewhere and the first room displays the many ways Aphrodite was depicted in ancient Greece, including a Red-figure vase with a scene of her and rival goddesses, Hera and Athena, preparing for the beauty contest she will win, leading to the Trojan war. So many stories, and this room seems almost breezy with them. The marriage of Aphrodite to the smith-god Hephaestos and her adultery with Ares, god of war, is told in a yada yada way and illustrated by a minor 16th-century print. Then suddenly you are plunged way, way back to encounter what this show claims was the real, original Aphrodite.

In classical mythology, Aphrodite has a special relationship with Cyprus: it is to Cyprian shores that her shell wafts in The Birth of Venus. That is because the Greek Aphrodite, we're told here, is an appropriation of a female deity who was once all-powerful on the island. Prehistoric totems with abstract bodies, and in one case a bird's head, attest to the centrality of this cult. Terracotta figures of childbirth and motherhood may have been offered by women to this nameless deity. Other images mix her with Egyptian and Phoenician deities. But I wasn't convinced that any of this explains the Greek and Roman love divinity. Either way, it is crushingly out of sequence – why didn't they start here then go forward in time?

What's inescapable is that the ancient Greeks made nudity their thing. It embodied (literally) their sense of being different from other cultures. But you don't want to be caught looking too long at the Capitoline Venus. This second-century AD Roman marble statue is a copy of a lost work that was an ancient scandal: the Venus of Knidos by Praxiteles. Before Praxiteles, the Greeks didn't portray Aphrodite nude, so people were powerfully struck by its intimacy when it was unveiled in the fourth century BC. Aphrodite has stripped for her bath and is trying, failing, to cover herself with her arms. One besotted admirer allegedly entered her sanctuary by night and rubbed himself against the statue's cold hard thigh, leaving a semen stain.

It's troubling to see this after you've encountered the evidence for the ancient Cypriot Aphrodite. It looks as if the Greeks took a matriarchal deity and turned her into a sex toy. The Romans borrowed that nude image and spread it throughout their empire: a raw, graffiti-like relief from a fort north of Hadrian's Wall of a naked Venus bathing is a funny, fascinating delight.

For me it's Botticelli who is the true believer in Aphrodite/Venus. For 1,000 years, ever since the fall of the Roman empire, she was cast out by Christianity. Her worship was revived in the Renaissance. When you gaze at the unfathomable eyes of his Venus, you too believe that love is a holy thing that deserves its own deity.