Extracting Views SQL
I believe your primary question is:


  Is there a way to get a query that when run against MySQL directly, it will output similar results to that of views?


You're probably getting an error when you try to run the query when copied directly from Views UI.  Change the {node} to myprefix_node, changing myprefix_ to the prefix you gave your tables.  If you didn't supply a prefix than simply replace {node} with node.

To respond to your other question:


  I don't understand what it is doing here: 'node' AS field_data_field_vis_group_node_entity_type Is views doing some additional processing after this to get the data?


Views creates aliases for the columns that are returned by the query so that it can more easily reference it later on.  In this case it is creating a hard-coded value of "node" for each result, which is interesting.  And the hard-coded value has an alias of field_data_field_vis_group_node_entity_type.