Por otro lado tambien puede que tengas conocimiento que MySQL es el motor de compañias como Google, Yahoo, Technorati y estas manejan algunos billones de registros y consiguen un gran rendimiento.

La pregunta que puedes estar haciendote es cuál es la razón...

La razón es que normalmente estas tablas estan diseñadas y entendidas para trabajar con MySQL, si diseñas tus datos considerando que puede hacer y que no puede hacer MySQL probablemente conseguiras un buen rendimiento, cualquier sistema de administración de bases de datos es diferente con respecto a los otros, lo que funciona bien y es eficaz en Oracle,MS SQL,PostgreSQL no debe de ser lo que mayor rendimiento ofrece en MYSQL.

Incluso en el sistema de almacenamiento tienen muchas diferencias que los hace diferentes.

Las tres claves que deberias de tener en cuenta con tablas muy grandes son: Buffers, indices y consultas.

Buffers

Como ya sabrás un buffer es una ubicación de la memoria reservada para el almacenamiento temporal de información digital.

La primera cosa que deberias de tener muy clara es el hecho de que hay una gran diferencia entre cuando los datos estan en memoria y cuando no estan en memoria.

Si empezaste con un tamaño de memoria y notas un descenso gradual del rendimiento porque la base de datos esta creciendo una buena solucion sería asegurarte que tienes suficiente memoria para el volumen de datos que estas utilizando esto podrias realizarlo con diferentes técnicas.

Indices

Los índices son usados para encontrar rápidamente los registros que tengan un determinado valor en alguna de sus columnas. Sin un índice, MySQL tiene que iniciar con el primer registro y leer a través de toda la tabla para encontrar los registros relevantes.

Aún en tablas pequeñas, de unos 1000 registros, es por lo menos 100 veces más rápido leer los datos usando un índice, que haciendo una lectura secuencial por lo tanto queda claro que los indices son realmente eficaces para acelerar el acceso a datos.

Antes de proseguir con la explicacion he de aclarar algunos terminos como escaneo completo, que es leer todos los registros de la tabla de manera secuencial.

Cuando MySQL encuentre que hay un índice en una columna, lo usará en vez de hacer un escaneo completo de la tabla. Esto reduce de manera imporante los tiempos de CPU y las operaciones de entrada/salida en disco.

Dejame explicartelo con datos, considerando una tabla que tiene 100 Bytes por registros, con una unidad SCSI nosotros podriamos obtener 100MB/segundo de velocidad de lectura que nos daría alrededor de 1.000.000 de registro por segundo, si hablamos de tablas tipo MyISAM.

Aquí te dejo un ejemplo, he creado una tabla con 30 millones de registros, con una columna ('val') que tiene 10000 valores diferentes,vamos a ver las diferencias entre el tiempo de un escaneo total de la tabla y un escaneo utilizando un rango de valores por ejemplo entre 1 y 100, el resultado es el siguiente:

1.
mysql> SELECT count(pad) FROM large;
2.
+------------+
3.
| count(pad) |
4.
+------------+
5.
| 31457280 |
6.
+------------+
7.
1 row IN SET (4 min 58.63 sec)
8.

9.
mysql> SELECT count(pad) FROM large WHERE val BETWEEN 1 AND 100;
10.
+------------+
11.
| count(pad) |
12.
+------------+
13.
| 314008 |
14.
+------------+
15.
1 row IN SET (29 min 53.01 sec)

Como puedes observar el resultado es contradictorio ha tardado 5 minutos en contar 30 millones de registros pero lo que sorprende es que ha tardado 30 minutos en contar el 1% de esos registros que eran los valores entre 1 y 100.

Hay muchas maneras de optimizar el trabajo con este tipo de consultas, por ejemplo ordenar primero el los valores y entonces hacer la búsqueda sobre estos registros ya ordenados.Esta solución reduciria el tiempo de ejecución de la consulta pero sin duda seria un resultado relativamente parecido. El uso de índices se antoja esencial en este tipo de ejemplos en los que tenemos grandes cantidades de datos en nuestras tablas, la mejora en la obtención de los datos puede ser muy significativa.

1.Indexe las columnas sobre las que realiza la búsqueda, no sobre las que selecciona

Las mejores columnas para indexar son en las que aparecen la cláusula WHERE o las nombradas en las cláusulas join.

2.Utilice índices únicos

Los índices trabajan mejor sobre columnas con valores único, y peor con aquellas que tiene muchos valores duplicados. Por ejemplo, si una columna contiene fechas y tiene varios valores diferentes, un índice diferenciará las filas fácilmente, sin embargo, no le ayudará tanto si se utiliza en una columna para registrar el valores como verdadero,falso y que contiene sólo los dos valores "V" y "F" (cualquiera que sea que busque, tomará casi la mitad de las filas).

3.Utilice índices cortos

Si va a indexar una columna de cadenas, especifique una longitud prefijada, siempres que sea razonable hacerlo asi, por ejemplo si tiene una columna CHAR (200), no indexe la columna entera si la mayor parte de los valores son únicos dentro de los 10 o 20 primeros caracteres. Indexar estos 10 o 20 primeros caracteres le ahorrara mucho espacio en el índice, y probablemente hará más rapidas su consultas.

4.No abuse de los índices

Los índices deben ser actualizados, y posiblemente reorganizados, cuando modifique los contenidos de sus tablas.

Si tiene un índice que raramente, o nunca, se usa estas hastas realentizando el sistema volviendo más lenta de forma innecesaria las modificaciones de la tabla.

5.Considere el tipo de comparaciones a realizar en un registro

Los índices se usan para operaciones <, <=, =, >=, > y BETWEEN. Tambien se usan par operaciones LIKE, cuando el patrón tiene un prefijo literal. Si sólo usa una columna para otro tipo de operaciones, tales como STRCMP(), no tiene sentido indexarlo.

Conclusión

Como conclusión tu puedes conseguir que MySQL rinda a buen rendimiento con grandes cantidades de datos pero para ello debes tener en cuenta sus limitaciones y saber cuales son las características que ofrecen mejor rendimiento.

Fuente: en ingles