Não existe o Grand Unified Database Layout. Se houver questionários personalizados, realmente precisam ser tabelas personalizadas. Caso contrário, você está em um caminho rápido para uma única tabela de 200 colunas de monstros VARCHAR (128) - sem-chaves-primárias fora do thedailywtf.com, o que é ineficiente, insuportável e irá prejudicá-lo no futuro .
O sharding, como recomendado pelo toppledwagon, pode ser uma coisa a considerar, mas, primeiro, verifique se o banco de dados foi projetado racionalmente. Se não é normalizado, então tem um muito bom, de preferência apoiado por testes, razão, porque não é. Se tiver centenas de tabelas, provavelmente está errado. Se tem uma única mesa, está definitivamente errado. Veja como você pode dividir seu problema em conjuntos independentes. Você vai gastar mais esforço na frente, mas o sistema será melhor para ele.
Milhões de linhas, digamos, com 2k de dados por linha (o que parece um monte de caracteres para uma pesquisa), são 2GB de memória. Se você puder lançar um pouco mais de hardware no seu problema, talvez consiga manter seus dados na RAM?
O que leva à próxima pergunta: qual é a sua carga em números absolutos? Solicitações de clientes por segundo, traduzidas para I / Os por segundo, divididas em leituras e gravações por segundo, quantos gigabytes de dados, com qual taxa de crescimento? Como sua carga é dimensionada com o número de solicitações? Linearmente? Exponencialmente? Você não precisa publicar seus dados, apenas escreva e pense sobre isso. O que é hoje, como você acha que vai parecer daqui a um ano ou dois?
Wikipedia diz que uma unidade SAS de 15k rpm oferecerá 175-210 IOps. Quantos você precisa no RAID 10 para satisfazer sua carga atual e projetada? Qual é o tamanho do seu conjunto de dados? Quantas unidades você precisa ajustar ao seu conjunto de dados (provavelmente muito menos do que atender ao requisito de IOs). Comprar um par (ou uma dúzia) de SSD seria justificável? O armazenamento local será OK, ou você vai saturar dois links de fibra de 8 Gb para um subsistema de armazenamento high-end?
Se atualmente você precisa de 1k IOps, mas tem três HDDs de 10k rpm no RAID 5, não há como seu hardware atender às suas necessidades. OTOH se o seu aplicativo tiver uma solicitação de usuário por segundo e trouxer uma besta de 32 GB de 256 GB de RAM, apoiada por um armazenamento de classe corporativa, então é provável que o problema não esteja nas capacidades de hardware.