es cierto que los hombre fieles, sinceros y con buen corazón son a los que mas lastiman y casi siempre están solteros?

Yo creo que sufren siempre un desamor. :-)

por que no hacen una lista de comandos :v? con las jerarquias y todo ordenado y especificado

En realidad, tienen una lista. Puede encontrarlo aquí: https://es.khanacademy.org/computing/computer-programming/sql-documentation

Contenido principal

SQL más eficiente con planeación y optimización de consultas

Google Classroom

Ya que aprendiste muchas formas de seleccionar datos y estás empezando a hacer SELECTs a través de múltiples tablas, es un buen momento para hablar acerca de la eficiencia de tus consultas de SQL. ¿Qué tan rápido se ejecutan? y ¿podrían ejecutarse más rápido?

SQL es un lenguaje declarativo: cada consulta declara qué queremos que haga el motor de SQL, pero no dice cómo. Sin embargo, resulta que el cómo, el "plan", es lo que afecta la eficiencia de las consultas, así que es bastante importante.

¿Por qué las consultas de SQL necesitan un plan?

Por ejemplo, digamos que tenemos esta consulta sencilla:

SELECT * FROM books WHERE author = "J K Rowling";

Para esta consulta, hay dos formas diferentes en las que SQL podría encontrar los resultados:

Hacer una "exploración completa de la tabla": buscar en cada renglón de la tabla y regresar los renglones que coincidan.
Crear un "índice": hacer una copia de la tabla ordenada por autor, después hacer una búsqueda binaria para encontrar el renglón en la que el autor es "J K Rowling", encontrar los IDs que coincidan, después hacer una búsqueda binaria en la tabla original que regrese los renglones que coincidan con el ID.

¿Cuál es más rápida? Depende de los datos, y de qué tan frecuenteme será ejecutada la consulta. Si la tabla solo tiene 10 renglones, entonces una exploración completa de la tabla solo requiere ver 10 renglones y el primer plan funcionaría bien.

Si la tabla tuviera 10 millones de renglones, entonces una exploración completa de la taba requeriría ver 10 millones de renglones. Sería más rápido hacer una búsqueda binaria en una tabla ordenada: solo necesitamos 23 búsquedas para encontrar un valor en 10 millones de renglones. Sin embargo, crear la tabla ordenada tomaría un buen tiempo (~230 millones de operaciones, dependiendo de nuestro motor). Si estuviéramos ejecutando esa consulta muchas veces (más de 23 veces) o si ya tuviéramos esa tabla creada, entonces el segundo plan sería mejor.

¿Cómo decide un motor de SQL cuál plan elegir? Ese es un paso importante acerca del cual no hemos hablado aún porque hemos estado enfocados en la sintaxis de nuestras consultas, no en su implementación. A medida que llegues a un uso más avanzado de SQL en bases de datos grandes, el paso de planeación se vuelve cada vez más importante.

El ciclo de vida de una consulta de SQL

Podemos pensar que el motor de SQL pasa por estos pasos para cada consulta que le demos:

El analizador de consultas se asegura de que la consulta sea sintácticamente correcta (por ejemplo, sin comas fuera de lugar) y semánticamente correcta (es decir, que las tablas existan), y regresa errores si no. Si es correcta, entonces la convierte en una expresión algebraica y la pasa al siguiente paso.
El planeador y optimizador de consultas hace el trabajo pesado de pensar. Primero realiza optimizaciones directas (mejoras que siempre resultan en un mejor rendimiento, como simplificar 5*10 en 50). Después considera diferentes "planes de consulta" que pueden tener diferentes optimizaciones, estima el costo (CPU y tiempo) de cada consulta con base en el número de renglones en las tablas relevantes, después escoge el plan óptimo y lo pasa al siguiente paso.
El ejecutor de la consulta toma el plan y lo convierte en operaciones de la base de datos, regresándonos los resultados si es que hay algunos.

¿Dónde entran los humanos?

La planeación y optimización de la consulta sucede para cada consulta, y podrías pasarte toda la vida emitiendo consultas de SQL sin darte cuenta. Sin embargo, una vez que empieces a lidiar con conjuntos de datos más grandes, empezarás a preocuparte más por la velocidad de tus consultas, y puede que te encuentres preguntándote si hay alguna manera en la que podrías mejorar el rendimiento de tus consultas.

Mucha veces, especialmente para consultas complicadas, ciertamente hay maneras en las que puedes ayudar a optimizar una consulta, y eso se llama "afinación de la consulta".

El primer paso es identificar qué consultas quieres afinar, las cuales puedes averiguar al ver cuáles de tus llamadas a la base de datos están tardando más o usando la mayor parte de los recursos, como con un analizador de SQL. Algunas veces, puedes descubrir una consulta con un muy mal desempeño después de que se tarda tanto que hace que se caiga toda tu base de datos. Con fortuna, habrás averiguado eso antes.

El siguiente paso es entender cómo un motor de SQL particular está ejecutando una consulta, y todos los sistemas de SQL vienen con una manera de preguntarle al motor. En SQLite, puedes pegar EXPLAIN QUERY PLAN en frente de cualquier SQL para ver qué está haciendo tras bambalinas. Si usas eso, debes estar preparado para consultar profundamente la referencia de EXPLAIN QUERY PLAN, porque la "explicación" es bastante detallada y depende de la implementación. Si estás usando otro motor de SQL, puedes buscar "cómo obtener un plan de ejecución en X".

Ahora viene la parte difícil: la optimización manual para mejorar el plan de ejecución. Esta también es la parte que a menudo depende de las particularidades del motor de SQL que estés usando, así como de las particularidades de tus propios datos.

Por ejemplo, ¿recuerdas la consulta que discutimos hasta arriba? Si supiéramos por adelantado que nos gustaría hacer cientos de consultas que restringieran WHERE en la columna del autor, entonces podríamos crear el índice de manera explícita al usar CREATE INDEX. Entonces, el motor de SQL sería capaz de usar ese índice para encontrar de manera eficienteme los renglones que coincidieran. Puedes leer esta guía acerca de planeación de consultas en SQLite para ayudarte a entender cuándo te ayudarían los índices.

Crear índices a menudo puede hacer más eficientes las consultas que se repiten. Pero también hay muchos otros enfoques. Para SQLite, puedes tener una visión más amplia en su resumen del planeador de consultas y pon especial atención a las secciones de "manual".

No podemos cubrir todas las complejidades de la optimización y afinación de una consulta aquí, así que recomiendo que te sumerjas en lo profundo cuando lo necesites.

(Aquí hay algunas inmersiones profundas en distintos planificadores de consultas de SQL que me parecen interesantes: Optimizador de consultas de SQL Server, Afinación de Oracle SQL, Conceptos básicos del Plan de Ejecución de MSSQL.