Update `SuperComponent` to specify if Pipeline or AsyncPipeline should be used in `from_dict`

[sjrl]

Currently in SuperComponent, the from_dict method does not specify whether the component should be loaded using Pipeline or AsyncPipeline. It just always uses Pipeline.from_dict. We should update the internal logic so to_dict specifies whether the component should be loaded using Pipeline or AsyncPipeline, and then from_dict should use that information.

This is especially problematic if loading a SuperComponent from yaml within an async pipeline. Since SuperComponent has a run_async method defined if it's placed in an AsyncPipeline at runtime AsyncPipeline will try and run the run_async method which will throw an error. This error is caused by SuperComponent complaining that it was given a normal Pipeline in it's init and not a AsyncPipeline. This means that it's not possible currently to use SuperComponent within an AsyncPipeline when loading from yaml.

Related to this issue https://github.com/deepset-ai/haystack/issues/9435