Volvemos a la carga con un artículo sobre índices, uno de los temas más importantes para el rendimiento de nuestras bases de datos y por ello sobre lo que más contenido tenemos en el blog. Si no has visto los artículos anteriores los tienes todos aquí. A estas alturas no es una sorpresa para nadie que diga que los índices son nuestros mejores aliados para optimizar las consultas y mejorar el rendimiento. Hoy, vamos a sumergirnos en el fascinante mundo de los índices de texto completo o Full-Text index en SQL Server. Este tipo de índice especial y no tan conocido en SQL Server nos va a permitir buscar en grandes cadenas de texto que, sin estos índices siempre es peor que un dolor de muelas.
¿Qué son los Índices de Texto Completo?
Los índices de texto completo son un tipo especial de índice de SQL Server que nos proporciona una solución eficaz para buscar palabras y frases dentro de una gran cantidad de datos de texto en SQL Server. A diferencia de los índices tradicionales, que se basan en la comparación de valores, los índices de texto completo nos permiten realizar búsquedas complejas en textos largos y no estructurados.
Creación de Índices de Texto Completo
Crear un índice de texto completo en SQL Server es un proceso sencillo. Primero, necesitamos una columna con un tipo de datos de texto. Luego, antes de poder crear en índice necesitaremos un catálogo de texto completo. Una vez creado el catálogo podremos crear nuestros índices de texto completo en sí.
La creación de catálogos es un aspecto fundamental para los índices de texto completo y de cómo los estructuremos ya que nos va a permitir organizarlos y va a afectar al rendimiento futuro de nuestras tareas de mantenimiento.
Uso de Índices de Texto Completo
Una vez creado el índice de texto completo, podemos utilizar la cláusula CONTAINS en nuestras consultas para buscar palabras o frases específicas. También podemos usar la cláusula FREETEXT para realizar búsquedas más generales.
Desventajas de los Índices de Texto Completo
Los índices de texto completo en SQL Server son una herramienta poderosa, pero como cualquier tecnología, tienen sus limitaciones y desventajas. Aquí vamos a nombrar algunas de las más importantes:
Espacio Adicional
Los índices de texto completo ocupan espacio adicional en el disco. Esto puede ser significativo en bases de datos con muchas tablas grandes y varios índices creados. Por lo tanto, es importante considerar el equilibrio entre el rendimiento de la consulta y el uso del espacio en disco.
Rellenado de Índices
El proceso para crear y mantener un índice de texto completo se denomina rellenado (o rastreo). Hay tres tipos de rellenado de índice de texto completo: rellenado completo, rellenado basado en el seguimiento de cambios, y rellenado incremental basado en una marca de tiempo. Este proceso puede ser costoso en términos de recursos y tiempo, especialmente para bases de datos grandes.
Mantenimiento de Índices de Texto Completo
Como cualquier otro índice, los índices de texto completo requieren un mantenimiento regular para garantizar su eficiencia. SQL Server proporciona varias herramientas y técnicas para ayudarnos en esta tarea. Al igual que en el resto de tipos de índices estas son REORGANIZE y REBUILD. Sin embargo, en el caso particular de estos índices las tareas de mantenimiento se realizan sobre el catálogo afectando a todos los índices incluidos y no se puede hacer sobre un único índice a petición. Siempre nos queda la opción de eliminar el índice y volverlo a crear pero no parece una opción aceptable. Por si esto fuera poco es un proceso que no está incluido en las principales soluciones de mantenimiento como OLA Hallengren.
Recomendaciones de rendimiento
Como acabamos de mencionar, optimizar el rendimiento de los índices de texto completo en SQL Server puede ser un desafío. Vamos a ver las mejores recomendaciones que pueden ayudarnos:
Arquitectura y Mantenimiento
Es una buena idea limitar la selección de columnas de clave de texto completo a una columna pequeña. Aunque se admite una columna de 900 bytes, se recomienda usar una columna de clave menor en un índice de texto completo, int y bigint ofrecen el mejor rendimiento.
Podremos reorganizar el catálogo de texto completo utilizando ALTER FULLTEXT CATALOG REORGANIZE. Esto debe hacerse antes de realizar pruebas de rendimiento, ya que la ejecución de esta instrucción produce una mezcla maestra de los índices de texto completo del catálogo.
Uso y consultas
Siempre es mejor combinar varios predicados CONTAINS en un predicado CONTAINS. En SQL Server puedes especificar una lista de columnas en la consulta CONTAINS.
Si solo necesitas información de clave de texto completo o de clasificación, usa CONTAINSTABLE o FREETEXTTABLE en lugar de CONTAINS o FREETEXT, respectivamente.
Para limitar los resultados y aumentar el rendimiento, usaremos el parámetro top_n_by_rank de las funciones CONTAINSTABLE y FREETEXTTABLE top_n_by_rank permite volver a recuperar solo las coincidencias más pertinentes.
Comprobar el Plan de Consultas
Y como siempre, deberemos comprobar constantemente el plan de consultas de texto completo para asegurarnos de que se selecciona el plan de combinaciones adecuado. Usaremos una sugerencia de consulta o forzaremos un plan de ejecución en Query Store si es necesario.
Conclusión
Los índices de texto completo son una herramienta potentísima en SQL Server que nos permite realizar búsquedas eficientes en grandes volúmenes de datos de texto. Aunque su creación y mantenimiento pueden requerir un poco de esfuerzo adicional, los beneficios que aportan en términos de rendimiento y flexibilidad de las consultas valen la pena.
Esperamos que este artículo te haya proporcionado una visión profunda de los índices de texto completo en SQL Server. Como siempre, te animamos a experimentar con estas técnicas y a explorar todas las posibilidades que ofrecen. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de LinkedIn y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!
[…] artículos hablando de los índices de texto completo (FullText) en SQL Server. Hemos aprendido lo qué son y cómo se administran, sin embargo hay un tema, quizá lo más importante, que habíamos dejado […]
[…] Por último, el uso de VARCHAR(MAX) y NVARCHAR(MAX) debe ser solo para casos puntuales e imprescindibles, ya que, cuando los valores almacenados superan los 8 KB, SQL Server los mueve a páginas de datos separadas, lo que puede ralentizar las consultas. Si es posible, es preferible definir una longitud específica en VARCHAR(n) o NVARCHAR(n) para mantener el rendimiento. Además, estos tipos de datos de gran tamaño no se pueden indexar con índices “normales” y si necesitamos filtrar por ellos tendremos que recurrir a los Full-Text Index […]
[…] diferenciador. No estamos hablando de una búsqueda con like ni de índices de texto completo (Full Text Indexes). Los embedding representan el significado de las palabras de manera que aspectos como sinónimos o […]