You already received a good explanation of how DNS works in relation to your question. I'll answer the SNI part.
Short answer: Your ISP would only be able to see the hostname. SNI only contains the hostname your browser is trying to access. That is sent in plain text and is necessary for your browser to tell the web server which SSL certificate its requesting. The handshake is then made, and the connection secured before the full URL is sent.
Not as short answer (much more than you asked for but...)
SNI=Server Name Indication. It's part of the HTTPS TLS handshake process. When you want to connect to twitter.com, first the DNS is resolved for it. Then your browser sends a request to that IP address on port 443 (when using https://). Part of that request includes the SNI, if your browser supports it, which most do. The SNI only contains the domain name. If you typed https//www.twitter.com/bejrjoftj then the DNS lookup would resolve www.twitter.com and then include www.twitter.com as the SNI request. Note that "www." is actually a subdomain of the top-level domain name. A single IP can host many domains. Only HTTP and HTTPS access different resources based on the hostname requested. This is important because even though twitter.com and geocities.com might resolve to the same IP address, a web browser will receive different resources (the web page the server serves to you) based on the hostname requested, but that IP address can only host, for example, one SSH server on port 22. So, when you're accessing different websites with the same IP, that IP is only running one webserver, which decides which page to send you based on the SNI hostname. But that's all SNI is, is the hostname.
Apache HTTP Server and nginx both support virtual hosts. The server has a "default host" that it will serve if you, for example, used the IP address directly in your browser. This most often redirects to call a virtual host config. Virtual hosts aren't just the hostname though.
A virtual host can also be data to the right of the host name. For example, twitter.com and twitter.com/something/ could be two different virtual hosts. Since DNS only resolves the domain name/host name, twitter.com would resolve to the same IP no matter what the rest of the URL is. But the webserver does receive the full requested URL after the TLS handshake is made and the connection is encrypted. To reiterate, the purpose of SNI is to make sure the web server sends the correct SSL certificate to encrypt your connection, because if you're trying to access example.com and its IP address is the same as twitter.com, the server needs to make sure it sends the right certificate to your browser so your browser can verify that the certificate it received matches the host name it's trying to connect to.
Without the SNI, the server would have no way of knowing that you want the example.com virtual host from the server, not the twitter.com virtual host. And your browser needs to receive the example.com certificate to complete the handshake without any issues. The web server at that IP needs to have a virtual host entry for the hostname before it can define URL virtual hosts. example.com/ and example.com/page/ are not necessarily the same virtual host, even if they share the same certificate, if the config file has a virtual host defined for example.com/page/*. As for the reason why, you might end up at 192.168.2.1 for example.com/page while example.com/ is at 192.168.1.1, is because the virtual host can have a redirect defined. If it does, it will redirect your browser to the other IP. This is a software defined redirect and is defined by a result code, 300. A more commonly known result code is 404, which means the file requested doesn't exist. if the virtual host config includes a custom response page to send back to your browser whenever the server receives a 404 response back from URL you requested, it will send you that page every time you request a file that doesn't exist. The redirect response of 300 also includes a new URL, which tells your browser "Hey, you reached the example.com/page/ virtual host but sorry Mario, your princess is in another castle. You need to send that request to twitter.com/page/ instead." And then your browser says, "oh damn, okay, my bad." And then sends a request to whatever URL the server told it to go to. Thats how you end up getting redirected to a malicious URL when you tried accessing a seemingly innocent URL. But that redirect comes directly from the web server and not DNS. A DNS redirect is only when the DNS config has a CNAME record (normal IP address entries for IPV4 are A records). A CNAME record is an alias record. And an A or CNAME record is assigned to a host name. So, if page.example.com has a CNAME record in the example.com master record file with a value of "twitter.com", then the DNS client will be told to look up twitter.com to complete the request for page.example.com. a CNAME record is always a domain name and never an IP address. It tells your DNS client that page.example.com is just another name to use for twitter.com. this might be useful if you wanted to use page.example.com as another name for pagebook.com and you want the DNS client to follow the trail over to pagebook.com. this doesn't require you to run a web server with a virtual host set up for page.example.com. your DNS client will then look up pagebook.com and you'll get back whatever IP is assigned to pagebook.com instead.