In SQL Server for an IO Intensive database or often for VLDB's we create multiple database files and put them on individual Disks/Luns, to spread the IO workload.
I cant see how this is possible with mySQL, as all databases are created in the instance directory by default, with no option to place them elsewhere or create multiple files. What am i missing?
Most people use RAID to spread the IO workload.
If you really need to, you can make each database's subdirectory a symbolic link to another disk volume. But this is not usually necessary.
You can also move the redo log, binary log, and query logs to another directory outside the data directory. Those files have a fairly high rate of IO usage, so this can be a way of balancing IO load.
Re your comment:
MySQL does not have any option for "filegroups" by that name. But you can move a given table to its own database. Databases on MySQL are pretty easy, think of them as simply a namespace for tables. No separate login required to access multiple databases, just qualify the table names in your query.
If you want a given database to have its own physical disk on Windows, I would use Windows Disk Manager to mount a disk to an empty directory instead of a distinct drive letter. So you could map one subdirectory under the MySQL data directory to a dedicated physical disk. Thus data and index files under that subdirectory would be on their own disk.
There are some data files that MySQL manages outside the database-specific subdirectories. For example, logs, and also the central InnoDB tablespace (which is important even if you configure innodb_file_per_table). Each of these allows you to specify a path so you can locate them anywhere on your filesystem. But you can't separate their writes per table or per database.
MySQL is a different product from Microsoft SQL Server (or Oracle, etc.) and you shouldn't expect every feature of SQL Server to have a direct analog in MySQL.
