Los beneficios de tener un \u00edndice son bien conocidos, podemos obtener los mismos resultados leyendo una cantidad menor de datos, por lo que la mejora en el rendimiento puede ser de varios minutos a segundos o incluso menos.<\/p>\n
Eso suena incre\u00edble y ciertamente lo es, hay personas que viven de ello, as\u00ed que obviamente es bastante importante.<\/p>\n
Pero no siempre es as\u00ed, y las cosas pueden salir mal f\u00e1cilmente y convertir a todos estos \u00edndices en una carga in\u00fatiles.<\/p>\n
Dejadme que os muestre algunos ejemplos, donde podemos ver nuestros \u00edndices en uso, pero tambi\u00e9n c\u00f3mo el procesador de consultas puede ignorarlos y volverse totalmente in\u00fatiles. Voy a utilizar la base de datos de ejemplo de Microsoft [WideWorldImporters] para que poda\u00eds seguir conmigo si quer\u00e9is.<\/p>\n
Si comprobamos la tabla [Sales].[InvoiceLines], podemos observar que hay un \u00edndice no agrupado en [PackageTypeID], por lo que esperamos que las consultas que utilicen esta columna para filtrar los resultados se pueden beneficiar de \u00e9l. Ex\u00e1ctamente como esta<\/p>\n Pero no todo es arco iris y unicornios, hay d\u00edas en que los \u00edndices son in\u00fatiles, no es su culpa, generalmente la nuestra, pero al final no importa, nuestros \u00edndices son in\u00fatiles.<\/p>\n Hemos visto que la primera consulta es el ejemplo perfecto de lo que estamos buscando, leyendo s\u00f3lo de nuestro \u00edndice y s\u00f3lo las filas que nos interesan.<\/p>\n El segundo ejemplo ya se empieza a salir del buen camino y tiene que a\u00f1adir otra operaci\u00f3n para obtener el resto de las columnas que no est\u00e1n incluidas en nuestro \u00edndice no agrupado.<\/p>\n Siguiendo este segundo ejemplo, podemos encontrar nuestro primer inhibidor, que incluyo en este post aunque se puede decir que es \u00abbenigno\u00bb.<\/p>\n El procesador de consultas usa las estad\u00edsticas para determinar el mejor plan de consulta para cada consulta y, a veces, es m\u00e1s eficiente leer la table entera que buscar en un \u00edndice no agrupado m\u00e1s b\u00fasquedas para obtener el resto de columnas.<\/p>\n \u00bfHe dicho estad\u00edsticas?, vamos a echar un vistazo<\/p>\n Observad c\u00f3mo cuando usamos el valor 1, el optimizador cree que Seek + Lookup es el m\u00e1s eficiente considerando que solo hay ~ 725 filas, pero para el valor 9 hay ~ 4988 filas, demasiadas b\u00fasquedas, por lo tanto, el plan es simplemente escanear toda la tabla y elige aquellas filas que coinciden con el filtro.<\/p>\n En alg\u00fan lugar entre el n\u00famero de filas para el valor 1 y 9 hay un punto llamado \u00abpunto de inflexi\u00f3n\u00bb una fila por encima de ese umbral y el optimizador cambiar\u00e1 el plan de buscar a escanear. Esto no es necesariamente algo malo. El optimizador suele saber mejor que nosotros cu\u00e1l es el mejor plan la mayor\u00eda de las veces.<\/p>\n Otro problema que podemos tener cuando el optimizador mira las estad\u00edsticas del \u00edndice es si no estamos proporcionando un valor sino una variable. Ahora el optimizador tiene que adivinar cu\u00e1ntas filas se devolver\u00e1n para este valor desconocido antes de ejecutar la consulta.<\/p>\n Veamos nuestro valor 1 cuando est\u00e1 en una variable.<\/p>\n Teniendo en cuenta la cardinalidad de esta columna (\u00edndice) donde el n\u00famero de filas para la mayor\u00eda de los valores y el promedio supera el punto de inflexi\u00f3n, ser\u00eda m\u00e1s sensato si siempre realizamos un escaneo de tabla.<\/p>\n Todo esto hace que nuestro \u00edndice sea totalmente in\u00fatil en la mayor\u00eda de los casos …<\/p>\n Ejecutar las consultas de arriba tal y como est\u00e1n, probablemente no sea lo que hacemos normalmente. Por lo general, escribimos procedimientos almacenados por aquello de reutilizar el c\u00f3digo y eso.<\/p>\n El problema es que si la primera vez que ejecutamos el procedimiento proporcionamos el valor 1, el plan ser\u00eda \u00abSeek + Lookup\u00bb y ser\u00e1 as\u00ed independientemente del valor en las ejecuciones posteriores, mientras el plan est\u00e9 en memoria.<\/p>\n Si el primer valor es 9, entonces leer la table entera ser\u00eda la elecci\u00f3n.<\/p>\n No voy a profundizar en esto ya que el tema es lo suficientemente amplio como para tener una publicaci\u00f3n o sesi\u00f3n completa, simplemente haced una busqueda en Bing\/Google y ver\u00e9is posibles soluciones para este problema.<\/p>\n Porque seamos honestos, a veces escribimos c\u00f3digo que apesta.<\/p>\n Imaginemos que tenemos otro \u00edndice que en ambos casos, para valores espec\u00edficos o para variables, siempre devuelve index seek + key lookup y que tambi\u00e9n es la forma m\u00e1s eficiente … \u00bfC\u00f3mo suena eso? bien, \u00bfno?<\/p>\n Y ahora imaginaos que escribimos nuestro c\u00f3digo y lo fastidiamoes \ud83d\ude41<\/p>\n Los valores est\u00e1n distribuidos uniformemente, de modo que tanto cada valor como el promedio (utilizando una variable) produce el mismo plan de consulta, seek + lookup.<\/p>\n Pues mala soluci\u00f3n tiene el asunto, y como suele decirse, \u00abdepende\u00bb.<\/p>\n Obviamente habr\u00eda que tener en cuenta muchos factores, porque es raro que tengamos un proceso t\u00e1n simple como una consulta. La complejidad del mismo as\u00ed como la frecuencia con la que llamamos a este proceso pueden ser determinantes.<\/p>\n Una de las posibles soluciones ser\u00eda usar dos consultas en vez de una y ejecutar una u otra en funci\u00f3n del valor del par\u00e1metro.<\/p>\n Y si pasamos NULL, entoces tenemos<\/p>\n
\nComportamiento deseado<\/strong><\/p>\n\r\nUSE [WideWorldImporters]\r\n\r\nSELECT COUNT(*) \r\nFROM [Sales].[InvoiceLines]\r\nWHERE [PackageTypeID] = 9\r\n<\/pre>\n
![\"\"](\"https:\/\/sqldoubleg.live-website.com\/wp-content\/uploads\/2018\/02\/02_index_seek.png\")
<\/a><\/p>\n
\nY aqu\u00ed tenemos otro ejemplo.<\/p>\n\r\nUSE [WideWorldImporters]\r\n\r\nSELECT * \r\nFROM [Sales].[InvoiceLines]\r\nWHERE [PackageTypeID] = 1\r\n<\/pre>\n
<\/a><\/p>\n
\nEste segundo ejemplo no es tan bueno como el primero, ya que tambi\u00e9n debemos realizar b\u00fasquedas en la tabla para satisfacer la consulta porque estamos solicitando m\u00e1s columnas que las que existen en el \u00edndice.<\/p>\n
\nInhibidores de b\u00fasqueda de \u00edndices<\/strong> <\/p>\n
\nEstad\u00edsticas<\/strong> <\/p>\n\r\nUSE [WideWorldImporters]\r\n\r\nSELECT * \r\nFROM [Sales].[InvoiceLines]\r\nWHERE [PackageTypeID] = 9\r\n<\/pre>\n
<\/a><\/p>\n<\/a><\/p>\n
\nVariables<\/strong> <\/p>\n\r\nUSE [WideWorldImporters]\r\n\r\nDECLARE @PackageTypeID INT = 1\r\n\r\nSELECT * \r\nFROM [Sales].[InvoiceLines]\r\nWHERE [PackageTypeID] = @PackageTypeID\r\n-- Estimated rows = 57066\r\n-- Query plan Clustered Index Scan\r\nGO\r\n<\/pre>\n
<\/a><\/p>\n
\nEl optimizador calcula cu\u00e1ntas filas por valor en promedio, para adivinar cu\u00e1ntas filas se devolver\u00e1n y si este n\u00famero supera el punto de inflexi\u00f3n, el plan es escanear la tabla entera.<\/p>\n
\nM\u00e1s problemas relacionados con estad\u00edsticas, detecci\u00f3n de par\u00e1metros <\/strong><\/p>\n
\nMal T-SQL<\/strong><\/p>\n\r\nDBCC SHOW_STATISTICS ('[Sales].[InvoiceLines]', [FK_Sales_InvoiceLines_StockItemID])\r\n\r\n\/*\r\nAvg -> Rows * All density = 951\r\n\r\nRANGE_HI_KEY\tEQ_ROWS\r\n1\t\t\t\t1097.179\r\n2\t\t\t\t991.8209\r\n3\t\t\t\t999.087\r\n4\t\t\t\t1060.849\r\n...\t\t\t\t...\r\n\t\t\r\n*\/\r\n<\/pre>\n<\/a><\/p>\n\r\nUSE [WideWorldImporters]\r\n\r\nSELECT * \r\nFROM [Sales].[InvoiceLines]\r\nWHERE [StockItemID] = 1\r\n-- Estimated rows = 1159\r\n-- Query plan Index Seek + Key Lookup\r\nGO\r\n\r\n\r\nDECLARE @StockItemID INT = 1\r\n\r\nSELECT * \r\nFROM [Sales].[InvoiceLines]\r\nWHERE [StockItemID] = @StockItemID\r\n-- Estimated rows = 1005\r\n-- Query plan Index Seek + Key Lookup\r\nGO\r\n\r\n<\/pre>\n
<\/a><\/p>\n
\nEste ejemplo es perfecto, entonces, \u00bfqu\u00e9 puede salir mal?, f\u00e1cil, \u00bfcu\u00e1ntas veces tenemos que escribir c\u00f3digo como este?<\/p>\n\r\nUSE [WideWorldImporters]\r\n\r\nDECLARE @StockItemID INT = 1\r\n\r\nSELECT * \r\nFROM [Sales].[InvoiceLines]\r\nWHERE [StockItemID] = @StockItemID \r\n\tOR @StockItemID IS NULL\r\n<\/pre>\n
<\/a><\/p>\n
\nBOOOM!!!<\/strong> ah\u00ed lo tienes, aqu\u00ed esta tu \u00edndice que no vale para nada.<\/p>\n
\n\u00bfY que hacemos ahora?<\/strong><\/p>\n\r\nDECLARE @StockItemID INT = 1\r\n\r\nIF @StockItemID IS NULL BEGIN\r\n\tSELECT * \r\n\t\tFROM [Sales].[InvoiceLines]\r\nEND\r\nELSE BEGIN\r\n\tSELECT * \r\n\t\tFROM [Sales].[InvoiceLines]\r\n\t\tWHERE [StockItemID] = @StockItemID \r\nEND\r\n<\/pre>\n
<\/a><\/p>\n