mostrar - optimizar consultas mysql explain

MYSQL: palabra clave de índice en la tabla de creación y cuándo usarla

Question

¿Qué significa índice de palabras clave y qué función sirve? Entiendo que está destinado a acelerar las consultas, pero no estoy muy seguro de cómo se puede hacer.

¿Cuándo elegir la columna que se indexará?

A continuación, se muestra una muestra del uso del índice en la consulta Crear tabla:

CREATE TABLE `blog_comment`
(
    `id` INTEGER  NOT NULL AUTO_INCREMENT,
    `blog_post_id` INTEGER,
    `author` VARCHAR(255),
    `email` VARCHAR(255),
    `body` TEXT,
    `created_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `blog_comment_FI_1` (`blog_post_id`),
    CONSTRAINT `blog_comment_FK_1`
        FOREIGN KEY (`blog_post_id`)
        REFERENCES `blog_post` (`id`)
)Type=MyISAM

;

Answer 1

Esta palabra clave significa que está creando un índice en la columna blog_post_id junto con la tabla.

Consultas como esa:

SELECT *
FROM blog_comment
WHERE blog_post_id = @id

usará este índice para buscar en este campo y ejecutar más rápido.

Además, hay una foreign key en esta columna.

Cuando decida eliminar una publicación de blog, la base de datos deberá verificar esta tabla para asegurarse de que no haya comentarios huérfanos. El índice también acelerará esta verificación, por lo que las consultas como

DELETE
FROM blog_post
WHERE ...

también correrá más rápido.

Answer 2

Recomiendo leer cómo MySQL usa índices del Manual de referencia de MySQL . Indica que los índices se usan ...

• Para encontrar las filas que coincidan con una cláusula WHERE rápidamente.
• Para eliminar filas de consideración.
• Para recuperar filas de otras tablas cuando se realizan uniones.
• Para encontrar el valor MIN() o MAX() para una columna indexada específica.
• Para ordenar o agrupar una tabla (bajo ciertas condiciones).
• Para optimizar consultas utilizando solo índices sin consultar las filas de datos.

Los índices en una base de datos funcionan como un índice en un libro. Puede encontrar lo que está buscando en un libro más rápido, porque el índice está en orden alfabético. En lugar de una lista alfabética, MySQL usa B-trees para organizar sus índices, que es más rápido para sus propósitos (pero tomaría mucho más tiempo para un humano).

Usar más índices significa usar más espacio (así como la sobrecarga de mantener el índice), por lo que realmente vale la pena usar índices en columnas que cumplan con los criterios de uso anteriores.

En su ejemplo, las columnas id y blog_post_id usan índices ( PRIMARY KEY es un índice) para que la aplicación pueda encontrarlos más rápidamente. En el caso de la id , es probable que esto permita a los usuarios modificar o eliminar un comentario rápidamente, y en el caso de blog_post_id , para que la aplicación pueda encontrar rápidamente todos los comentarios para una publicación determinada.

Notará que no hay un índice para la columna de email . Esto significa que la búsqueda de todas las publicaciones de blog por una dirección de correo electrónico en particular probablemente tomará bastante tiempo. Si desea agregar todos los comentarios de una dirección de correo electrónico en particular, es posible que también tenga sentido agregar un índice.