The former Tory prime minister also called for "more charm and a lot less cheap rhetoric" from the UK government towards the rest of the EU.
And he said the costs of leaving would be "substantial" and "unpalatable".
Downing Street said the government was determined to make a success of the UK's departure from the EU.
Conservative former cabinet minister Iain Duncan Smith said it was a "peculiar speech in the sense that it looked backwards the whole time".
He told BBC Newsnight: "It was almost like a re-fight of the referendum... strangely bitter really, and almost really the speech of someone who simply refuses to accept that the British people should have made a decision such as they did."
Prime Minister Theresa May plans to invoke Article 50 of the Lisbon Treaty, which begins two years of formal negotiations, by the end of March.
She has already confirmed the UK will not remain a member of the EU single market but will instead seek a new free trade deal with the remaining members.
In a speech in London, Sir John, who campaigned for a Remain vote in June's referendum, claimed there was "little chance" the advantages of being part of the EU single market could be replicated once the UK leaves.
"I have watched with growing concern as the British people have been led to expect a future that seems to be unreal and over-optimistic," he said.
"Obstacles are brushed aside as of no consequence, whilst opportunities are inflated beyond any reasonable expectation of delivery."
For Theresa May, also an unflashy leader who was propelled to No 10 by a surprising political moment, Europe will be defining in a way no others could even have anticipated.
In Sir John's carefully calibrated speech, there are plenty of messages for her, some of which may be welcome, some not.
First off, having campaigned to stay in the European Union, with sober warnings particularly about the consequences for the Northern Irish peace process, it's no surprise that Sir John says that in his view, Brexit will be a "historic mistake".
It is notable, although again not surprising, that he cautions that the UK will be a diminished diplomatic force in the world after we walk away from the EU, with a warning too that we will be less useful to our most important ally, the US, as a consequence.
Also, even as the PM who lived through the Commons trauma of trying to deliver the Maastricht Treaty, it is logical that he calls for Parliament to have a full role in shaping the negotiations over our place in Europe.
What may be harder for No 10 to dismiss is Sir John's obvious political concern about how the public is being treated in the months after the referendum decision.
Read more from Laura
Sir John said Brexit talks require "statesmanship of a high order" and warned of a "real risk" of the exit deal falling "well below the hopes and expectations" that have been raised, saying he doubted the "rosy confidence being offered to the British people".
"In my own experience, the most successful results are obtained when talks are conducted with goodwill: it is much easier to reach agreement with a friend than a quarrelsome neighbour.
"Behind the diplomatic civilities, the atmosphere is already sour. A little more charm, and a lot less cheap rhetoric, would do much to protect the UK's interests."
He also said the "cheerleaders" for Brexit had shown a "disregard that amounts to contempt" towards those that backed the losing side.
And he said the UK would become "far more dependent" on the US after it leaves the EU, describing President Donald Trump as "less predictable, less reliable and less attuned to our free market and socially liberal instincts than any of his predecessors".
Sir John, who as prime minister between 1990 and 1997 oversaw the start of the Northern Ireland peace process, warned that "uncertainties over border restrictions" after Brexit were "a serious threat to the UK, to the peace process and for Ireland, North and South".
The ex-PM, who faced battles with Eurosceptic MPs during his time in Downing Street, also said Mrs May would have to "face down" people calling for "total disengagement" from Europe.
But the Leave Means Leave campaign hit back, recalling Sir John's famous "don't bind my hands" plea to Tory Eurosceptics ahead of EU talks and saying he was now "seeking to do just this to the British prime minister ahead of negotiations with the EU".
Conservative MP Jacob Rees-Mogg added: "It was a craven and defeated speech of a bitter man who was heavily defeated by the electorate for his own failings in Europe in 1997, was defeated again last June and now wishes to take out his failures on Mrs May."
Britons are being offered an "unreal and over-optimistic" vision of what Brexit will look like, Sir John Major has warned.