Tengo una tabla de USUARIO con millones de filas. Estoy implementando una función de búsqueda que permite a alguien buscar un usuario escribiendo un nombre de usuario. Esta función de autocompletar debe ser increíblemente rápida. Dado que, en MySQL, los índices de columna aceleran las consultas usando LIKE {string}%, ¿el siguiente enfoque tiene el rendimiento suficiente para regresar dentro de los 200 ms? (Nota: la sobrecarga de memoria no es un problema aquí, el nombre de usuario tiene un máximo de 30 caracteres).
Cree una tabla USERSEARCH que tenga una clave externa para la tabla de usuario y una columna de nombre de usuario de ngram indexada :
USERSEARCH user_id username_ngram ------------------------- 1 crazyguy23 1 razyguy23 1 azyguy23 1 zyguy23 ...La consulta sería entonces:
SELECT user_id FROM myapp.usersearch WHERE username_ngram LIKE {string}% LIMIT 10Soy consciente de que existen soluciones de terceros, pero me gustaría mantenerme alejado de ellos en este momento por otras razones. ¿Es este enfoque viable en términos de velocidad? ¿Estoy sobreestimando el poder de los índices si la base de datos necesitara verificar todas las filas O (30n) donde n es la cantidad de usuarios?
Probablemente no. La union distinct va a procesar cada subconsulta hasta su finalización.
Si solo desea filas arbitrarias, exprese esto como:
(SELECT user_id FROM myapp.usersearch WHERE username_1 LIKE {string}% LIMIT 10 ) UNION DISTINCT (SELECT user_id FROM myapp.usersearch WHERE username_2 LIKE {string}% LIMIT 10 ) LIMIT 10; Esto al menos le ahorrará mucho tiempo para los prefijos comunes, diga 'S' .
Dicho esto, esto solo devuelve una lista arbitraria de 10 user_id de usuario cuando puede haber muchos más.
No sé si la velocidad será lo suficientemente rápida para su aplicación. Tienes que hacer ese juicio probando en un conjunto apropiado de datos.
Asumiendo SSD, eso debería ser ultrarrápido, sí.
Aquí hay algunas optimizaciones adicionales:
DISTINCT a su consulta, ya que no tiene sentido devolver el mismo user_id varias veces. Especialmente cuando se busca un prefijo muy común, como una sola letra.
También considere buscar solo al menos 3 letras de entrada. Menos tiende a no tener sentido (ya que es de esperar que sus nombres de usuario tengan al menos 3 caracteres) y es un golpe innecesario en su base de datos.
Si no está agregando más columnas (¡espero que no lo esté, ya que esta tabla está diseñada para una búsqueda ultrarrápida!), podemos hacerlo mejor. Cambia las columnas. Cree la clave principal (username_ngram, user_id). De esta manera, está buscando directamente en la clave principal. (¡Observe el beneficio adicional del orden alfabético de los resultados! Bueno... alfabético en los sufijos coincidentes, es decir, no en los nombres de usuario completos).
Asegúrese de tener un índice en user_id, para poder reemplazar todo para un usuario si alguna vez necesita cambiar un nombre de usuario. (Para hacerlo, simplemente elimine todas las filas para ese ID de usuario e inserte otras nuevas).
Tal vez podamos hacerlo aún mejor. Dado que esto es solo para búsquedas rápidas, podría usar un nivel de aislamiento de READ_UNCOMMITTED . Eso evita colocar bloqueos de lectura, si no me equivoco, y debería ser aún más rápido. Puede leer datos no confirmados, pero ¿y qué? Después, simplemente consultará los ID de usuario resultantes en otra tabla y tal vez no los encuentre, si ese usuario aún se estaba creando. No has perdido nada. :)
Creo que necesita usar el índice de texto completo de mysql para mejorar el rendimiento. Debe cambiar su sintaxis para usar su índice de texto completo.
Crear índice de texto completo :
CREATE FULLTEXT INDEX ix_usersearch_username_ngram ON usersearch(username_ngram);
La documentación oficial de mysql sobre cómo usar el índice de texto completo : https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html