10 consejos SQL para acelerar tu Base de datos

12
241

La mayoría de sitios en la Web depende de una o varias Bases de datos, ya sean para consultar, ingresar, borrar registros. Y en todas es necesaria la optimización para el aceleramiento y ahorrar recursos de nuestro Servidor, este articulo trae 10 consejos muy útiles para optimizar y acelerar nuestras bases de datos.

1. Optimizar las consultas con el Cache de Consultas SQL

Utilizando la función date de PHP podemos consultar en el cache de la base de datos:

$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

2. No olvidar agregar índices a los campos

Esto acelerara considerablemente sus consultas a la base de datos, para ver si olvido poner índice en algunos campos puede utilizar EXPLAIN, colocándolo delante de la consulta SQL.

EXPLAIN SELECT ...
FROM usuarios u
JOIN comentarios c ON u.id=c.usuario_id

3. Utilizar LIMIT 1 para obtener una única fila

Cuando sabemos que nuestra consulta nos devolverá solo un registro, por ejemplo al consultar por un id de usuario, es recomendable utilizar LIMIT 1, esto hará que cuando el registro sea encontrado, ya no recorrerá las demás filas para comparar.

$rs = mysql_query("SELECT 1 FROM user WHERE id = '1' LIMIT 1");

4. Agregar índice a los campos de búsqueda

Al tener en una tabla, campos con los que hacemos búsqueda, es bueno que esos campos sean índices para acelerar la consulta.

5. Utilizar el mismo tipo de columnas para los JOIN

Cuando relacionamos tablas tenemos que agregar índices, luego de esto asegurarnos que los dos campos tengan el mismo tipo de dato (DECIMAL, INT, etc.). Ya que esto afectara a procesos y operaciones internas de MySQL.

 $r = mysql_query("SELECT company_name FROM users
     LEFT JOIN companies ON (users.state = companies.state)
     WHERE users.id = $user_id");

6. No utilizar el ORDER BY RAND()

Si queremos sacar un registro de una tabla aleatoriamente, es mejor sacar el valor por código PHP, ya que al utilizar RAND() en MySQL estaremos llamando a la función por cada registro en la tabla para obtener un resultado.

// mal
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");
// bien
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

7. Evitar utilizar el SELECT *

Traer toda la data es casi siempre innecesario y hace procesar más al motor de base de datos, es muy recomendable cambiar el habitual * por los campos necesarios.

// mal
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
// bien
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";

8. Utilizar NOT NULL

A menos que tengas buenas razones para poner NULL a un campo, trata siempre de evitar esto y colocarlas con NOT NULL, ya que este tipo de columnas (NULL) requieren de un espacio adicional y de una complejidad interna en las sentencias de comparación

9. Guardar direcciones IP en campos UNSIGNED INT

Muchos de los desarrolladores (me incluyo) guardamos las direcciones IP en un campo VARCHAR sin saber que las podemos almacenar en campos INT que tiene solo 4 bytes de espacio, tenemos que asegurarnos que el campo sea UNSIGNED INT para aceptar valores más altos, ya que la dirección IP utiliza el rango de 32 bit.
Para transformar las IP a campo numérico y viceversa podemos utilizar funciones MySQL y PHP.

//mysql
$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";
//php
$ip_num = ip2long($_SERVER['REMOTE_ADDR']);
$ip_normal = long2ip($row['ip_address']);

10. Evitar ejecutar consultas dentro de bucles

Un claro ejemplo es al actualizar una serie de filas en la BD, esto se podría resumir en una sola consulta para optimizar recursos en nuestro servidor.
PHP

foreach  ($display_order  as $id => $ordinal) {
    $sql = "UPDATE categories SET display_order = $ordinal WHERE id = $id";
    mysql_query($sql);
}

MySQL

UPDATE  categories
    SET display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END
WHERE id IN (1,2,3)

Post Original: Nettuts+

zp8497586rq

jfdghjhthit45

12 COMMENTS

  1. No entiendo el punto 1: que tiene que ver usar un criterio de fecha sobre un campo de tipo fecha de una tabla con el hecho de usar el cache de consultas SQL?
    Por lo demas, muy buenos tips…

  2. Hola Javier, respondiendo a tu pregunta sobre en punto numero 1:
    MySQL tiene integrado su propio sistema de cache, esto lo procesa internamente, el problema es cuando utilizamos una función MySQL dentro de un SELECT, al hacer esto ya no estamos beneficiados por el cache de MySQL, ya que tiene que procesar la función puesta en la consulta, es por eso que en mi ejemplo muestro una manera de aprovechar PHP para generar una fecha actual y tener el beneficio del cache de MYSQL, el ejemplo común que se utilizaría para una consulta sin cache seria:
    $ stmt = ” SELECT * FROM usuario donde signup_date > = NOW () ” ;
    Espero haber servido de algo.
    Este articulo explica algunos parametros para monitorear el cache en nuestro server de DB.
    http://bit.ly/aaVztU
    Saludos cordiales!

Comments are closed.