¿Qué es SQL? La lengua franca del análisis de datos

Hoy en día, el lenguaje de consulta estructurado es el medio estándar para manipular y consultar datos en bases de datos relacionales, aunque con extensiones propietarias entre los productos. La facilidad y la ubicuidad de SQL incluso han llevado a los creadores de muchos almacenes de datos "NoSQL" o no relacionales, como Hadoop, a adoptar subconjuntos de SQL o crear sus propios lenguajes de consulta similares a SQL.

Pero SQL no siempre fue el lenguaje "universal" para las bases de datos relacionales. Desde el principio (alrededor de 1980), SQL tuvo ciertos ataques en su contra. Muchos investigadores y desarrolladores en ese momento, incluido yo, pensamos que la sobrecarga de SQL evitaría que fuera práctico en una base de datos de producción.

Claramente, estábamos equivocados. Pero muchos todavía creen que, a pesar de la facilidad y accesibilidad de SQL, el precio exigido por el rendimiento en tiempo de ejecución suele ser demasiado alto.

Historial de SQL

Antes de que existiera SQL, las bases de datos tenían interfaces de programación de navegación ajustadas y, por lo general, se diseñaron en torno a un esquema de red llamado modelo de datos CODASYL. CODASYL (Comité de Lenguajes de Sistemas de Datos) era un consorcio responsable del lenguaje de programación COBOL (a partir de 1959) y las extensiones del lenguaje de bases de datos (a partir de 10 años después).

Cuando programó contra una base de datos CODASYL, navegaba a registros a través de conjuntos, que expresan relaciones de uno a muchos. Las bases de datos jerárquicas más antiguas solo permiten que un registro pertenezca a un conjunto. Las bases de datos de red permiten que un registro pertenezca a varios conjuntos.

Supongamos que desea enumerar a los estudiantes inscritos en CS 101. Primero, buscaría "CS 101"en el Coursesconjunto por nombre, establecerlo como propietario o padre del Enrolleesconjunto, buscar el primer miembro ( ffm) del Enrolleesconjunto, que es un Studentregistro, y listar eso. Luego entraría en un bucle: busque el siguiente miembro ( fnm) y anótelo. Cuando fnmfallaba, saldría del ciclo.

Eso puede parecer mucho trabajo de scut para el programador de la base de datos, pero fue muy eficiente en el momento de la ejecución. Expertos como Michael Stonebraker de la Universidad de California en Berkeley e Ingres señalaron que hacer ese tipo de consulta en una base de datos CODASYL como IDMS tomó aproximadamente la mitad del tiempo de CPU y menos de la mitad de la memoria que la misma consulta en una base de datos relacional usando SQL. .

A modo de comparación, la consulta SQL equivalente para devolver todos los estudiantes en CS 101 sería algo así como 

SELECCIONE el nombre del alumno DE los cursos, los inscritos y los alumnos DONDE nombre del curso

Esa sintaxis implica una combinación interna relacional (en realidad, dos de ellas), como explicaré a continuación, y omite algunos detalles importantes, como los campos utilizados para las combinaciones.

Bases de datos relacionales y SQL

¿Por qué renunciar a un factor de dos mejoras en la velocidad de ejecución y el uso de la memoria? Hubo dos grandes razones: facilidad de desarrollo y portabilidad. No pensé que ninguno de los dos importara mucho en 1980 en comparación con el rendimiento y los requisitos de memoria, pero a medida que el hardware de la computadora mejoraba y se abarataba, la gente dejó de preocuparse por la velocidad de ejecución y la memoria y se preocupaba más por el costo del desarrollo.

En otras palabras, la Ley de Moore acabó con las bases de datos CODASYL en favor de las bases de datos relacionales. Resultó que la mejora en el tiempo de desarrollo fue significativa, pero la portabilidad de SQL resultó ser una quimera.

¿De dónde provienen el modelo relacional y SQL? EF "Ted" Codd era un científico informático en el Laboratorio de Investigación de IBM San José que elaboró ​​la teoría del modelo relacional en la década de 1960 y la publicó en 1970. IBM tardó en implementar una base de datos relacional en un esfuerzo por proteger los ingresos de su base de datos CODASYL IMS / DB. Cuando IBM finalmente inició su proyecto System R, el equipo de desarrollo (Don Chamberlin y Ray Boyce) no estaba bajo Codd, e ignoraron el artículo sobre lenguaje relacional Alpha de 1971 de Codd para diseñar su propio lenguaje, SEQUEL (Structured English Query Language). En 1979, antes de que IBM incluso lanzara su producto, Larry Ellison incorporó el lenguaje en su base de datos Oracle (utilizando las publicaciones SEQUEL previas al lanzamiento de IBM como su especificación). SEQUEL pronto se convirtió en SQL para evitar una violación de marca internacional.

Los “tom-toms batiendo por SQL” (como dijo Michael Stonebraker) venían no solo de Oracle e IBM, sino también de los clientes. No fue fácil contratar o capacitar a los diseñadores y programadores de bases de datos CODASYL, por lo que SEQUEL (y SQL) parecían mucho más atractivos. SQL era tan atractivo a finales de la década de 1980 que muchos proveedores de bases de datos esencialmente engraparon un procesador de consultas SQL encima de sus bases de datos CODASYL, para gran consternación de Codd, quien sintió que las bases de datos relacionales tenían que diseñarse desde cero para ser relacionales.

Una base de datos relacional pura, diseñada por Codd, se basa en tuplas agrupadas en relaciones, de acuerdo con la lógica de predicados de primer orden. Las bases de datos relacionales del mundo real tienen tablas que contienen campos, restricciones y disparadores, y las tablas están relacionadas mediante claves externas. SQL se utiliza para declarar los datos que se devolverán, y un procesador de consultas SQL y un optimizador de consultas convierten la declaración SQL en un plan de consulta que ejecuta el motor de la base de datos.

SQL incluye un sub-lenguaje para definir esquemas, el lenguaje de definición de datos (DDL), junto con un sub-lenguaje para modificar datos, el lenguaje de manipulación de datos (DML). Ambos tienen sus raíces en las primeras especificaciones de CODASYL. El tercer sub-lenguaje en SQL declara consultas, a través de SELECTdeclaraciones y combinaciones relacionales.

SELECTDeclaración SQL 

La SELECTdeclaración le dice al optimizador de consultas qué datos devolver, qué tablas buscar, qué relaciones seguir y qué orden imponer sobre los datos devueltos. El optimizador de consultas tiene que averiguar por sí mismo qué índices usar para evitar escaneos de tablas de fuerza bruta y lograr un buen rendimiento de consultas, a menos que la base de datos en particular admita sugerencias de índice.

Parte del arte del diseño de bases de datos relacionales depende del uso juicioso de los índices. Si omite un índice para una consulta frecuente, toda la base de datos puede ralentizarse bajo grandes cargas de lectura. Si tiene demasiados índices, toda la base de datos puede ralentizarse con cargas pesadas de escritura y actualización.

Otro arte importante es elegir una clave primaria buena y única para cada mesa. No solo debe considerar el impacto de la clave principal en las consultas comunes, sino también cómo se reproducirá en las combinaciones cuando aparezca como clave externa en otra tabla y cómo afectará la localidad de referencia de los datos.

En el caso avanzado de las tablas de la base de datos que se dividen en diferentes volúmenes según el valor de la clave principal, llamado fragmentación horizontal, también debe considerar cómo la clave principal afectará la fragmentación. Sugerencia: desea que la tabla se distribuya uniformemente en todos los volúmenes, lo que sugiere que no desea utilizar marcas de fecha o números enteros consecutivos como claves primarias.

Las discusiones sobre el SELECTenunciado pueden comenzar de manera simple, pero rápidamente pueden volverse confusas. Considerar:

SELECCIONAR * DE Clientes;

Simple, ¿verdad? Solicita todos los campos y todas las filas de la Customerstabla. Sin embargo, suponga que la Customerstabla tiene cien millones de filas y cien campos, y uno de los campos es un campo de texto grande para comentarios. ¿Cuánto tiempo llevará extraer todos esos datos a través de una conexión de red de 10 megabits por segundo si cada fila contiene un promedio de 1 kilobyte de datos?

Quizás debería reducir la cantidad que envía por cable. Considerar:

SELECCIONAR TOP 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount FROM clientes

DONDE estado Y ciudad

ORDER BY lastSaleDate DESCENDING;

Ahora vas a extraer muchos menos datos. Le ha pedido a la base de datos que le proporcione solo cuatro campos, que solo considere las empresas de Cleveland y que le proporcione solo las 100 empresas con las ventas más recientes. Sin embargo, para hacer eso de manera más eficiente en el servidor de la base de datos, la Customerstabla necesita un índice state+citypara la WHEREcláusula y un índice lastSaleDatepara las cláusulas ORDER BYy TOP 100.

Por cierto, TOP 100es válido para SQL Server y SQL Azure, pero no para MySQL u Oracle. En MySQL, usaría LIMIT 100después de la WHEREcláusula. En Oracle, usaría un límite ROWNUMcomo parte de la WHEREcláusula, es decir WHERE... AND ROWNUM <=100. Desafortunadamente, los estándares ANSI / ISO SQL (y hay nueve de ellos hasta la fecha, desde 1986 hasta 2016) solo van tan lejos, más allá de lo cual cada base de datos presenta sus propias cláusulas y características de propiedad.

Uniones SQL 

Hasta ahora, he descrito la SELECTsintaxis para tablas individuales. Antes de que pueda explicar las  JOINcláusulas, debe comprender las claves externas y las relaciones entre tablas. Explicaré esto usando ejemplos en DDL, usando la sintaxis de SQL Server.

La versión corta de esto es bastante simple. Cada tabla que desee utilizar en las relaciones debe tener una restricción de clave primaria; puede ser un solo campo o una combinación de campos definidos por una expresión. Por ejemplo:

CREAR TABLA Personas (

    PersonID int NOT NULL CLAVE PRIMARIA,

    PersonName char (80),

    ...

Cada tabla con la que se debe relacionar Personsdebe tener un campo que corresponda a la Personsclave principal y, para preservar la integridad relacional, ese campo debe tener una restricción de clave externa. Por ejemplo:

CREAR TABLA Órdenes (

    OrderID int NOT NULL CLAVE PRIMARIA,

    ...

    PersonID int REFERENCIAS CLAVE EXTRANJERAS Personas (PersonID)

);

Hay versiones más largas de ambas declaraciones que usan la CONSTRAINTpalabra clave, lo que le permite nombrar la restricción. Eso es lo que generan la mayoría de las herramientas de diseño de bases de datos.

Las claves primarias siempre están indexadas y son únicas (los valores de campo no se pueden duplicar). Opcionalmente, se pueden indexar otros campos. A menudo es útil crear índices para campos de clave externa y para campos que aparecen en cláusulas WHEREy ORDER BY, aunque no siempre, debido a la posible sobrecarga de escrituras y actualizaciones.

¿Cómo escribiría una consulta que devuelva todos los pedidos realizados por John Doe?

SELECT PersonName, OrderID FROM Personas

INNER JOIN Órdenes ON Persons.PersonID = Orders.PersonID

DONDE PersonName;

De hecho, hay cuatro tipos de JOIN: INNER, OUTER, LEFT, y RIGHT. El INNER JOINes el predeterminado (puede omitir la palabra INNER) y es el que solo incluye filas que contienen valores coincidentes en ambas tablas. Si desea enumerar las personas, tengan o no pedidos, usaría LEFT JOIN, por ejemplo:

SELECT PersonName, OrderID FROM Personas

Órdenes LEFT JOIN ON Persons.PersonID = Orders.PersonID

ORDER BY PersonName;

Cuando comienza a realizar consultas que unen más de dos tablas, que usan expresiones o que coaccionan tipos de datos, la sintaxis puede volverse un poco complicada al principio. Afortunadamente, existen herramientas de desarrollo de bases de datos que pueden generar consultas SQL correctas para usted, a menudo arrastrando y soltando tablas y campos desde el diagrama de esquema en un diagrama de consulta.

Procedimientos almacenados SQL

A veces, la naturaleza declarativa de la SELECTdeclaración no le lleva a donde quiere ir. La mayoría de las bases de datos tienen una función denominada procedimientos almacenados; desafortunadamente, esta es un área en la que casi todas las bases de datos usan extensiones propietarias de los estándares ANSI / ISO SQL.

En SQL Server, el dialecto inicial para los procedimientos almacenados (o procesos almacenados) era Transact-SQL, también conocido como T-SQL; en Oracle, fue PL-SQL. Ambas bases de datos han agregado lenguajes adicionales para procedimientos almacenados, como C #, Java y R. Un procedimiento almacenado T-SQL simple podría ser solo una versión parametrizada de una SELECTdeclaración. Sus ventajas son la facilidad de uso y la eficiencia. Los procedimientos almacenados se optimizan cuando se guardan, no cada vez que se ejecutan.

Un procedimiento almacenado de T-SQL más complicado podría usar múltiples sentencias SQL, parámetros de entrada y salida, variables locales, BEGIN...ENDbloques, IF...THEN...ELSEcondiciones, cursores (procesamiento fila por fila de un conjunto), expresiones, tablas temporales y una gran cantidad de otros sintaxis procedimental. Obviamente, si el lenguaje de procedimiento almacenado es C #, Java o R, utilizará las funciones y la sintaxis de esos lenguajes de procedimiento. En otras palabras, a pesar del hecho de que la motivación de SQL era utilizar consultas declarativas estandarizadas, en el mundo real se ve mucha programación de servidor de procedimientos específicos de la base de datos.

Eso no nos lleva de vuelta a los viejos tiempos de la programación de bases de datos CODASYL (aunque los cursores se acercan), pero sí retrocede de las ideas de que las declaraciones SQL deben estandarizarse y que las preocupaciones sobre el rendimiento deben dejarse al optimizador de consultas de la base de datos. . Al final, duplicar el rendimiento suele ser demasiado para dejarlo sobre la mesa.

Aprenda SQL

Los sitios que se enumeran a continuación pueden ayudarlo a aprender SQL o descubrir las peculiaridades de varios dialectos SQL.