una - llaves unicas en sql server
Índices y claves primarias de múltiples columnas. (5)
Fui a buscar y no encontré la respuesta a esta pregunta específica de noob. Mis disculpas si me lo perdí.
En una base de datos MySQL tengo una tabla con la siguiente clave principal
ID CLAVE PRIMARIA (factura, artículo)
En mi solicitud, también con frecuencia seleccionaré "artículo" solo y, con menos frecuencia, solo en "factura". Supongo que me beneficiaría de los índices en estas columnas.
MySQL no se queja cuando defino lo siguiente:
ÍNDICE (factura), ÍNDICE (artículo), ID DE CLAVE PRIMARIA (factura, artículo)
Pero no veo ninguna evidencia (utilizando DESCRIBIR, la única forma en que sé cómo buscar) de que se han establecido índices separados para estas dos columnas.
Entonces, la pregunta es, ¿las columnas que conforman una clave primaria se indexan automáticamente de forma individual? Además, ¿hay una manera mejor que DESCRIBIR para explorar la estructura de mi tabla?
Hay una diferencia entre el índice compuesto y la clave primaria compuesta. Si ha definido un índice compuesto como el siguiente
INDEX idx(invoice,item)
el índice no funcionará si realiza una consulta basada en un item y necesita agregar un índice separado
INDEX itemidx(item)
Pero, si ha definido una clave primaria compuesta como a continuación
PRIMARY KEY(invoice, item)
el índice funcionaría si realiza una consulta basada en un item y no se requiere un índice separado.
Ejemplo de trabajo:
mysql>create table test ( col1 int(20), col2 int(20) ) primary key(col1,col2);
mysql>explain select * from test where col2 = 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | test | index | NULL | PRIMARY | 8 | NULL | 10 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
No estoy familiarizado con los aspectos internos de los índices en mySql, pero en los dos productos de proveedores de bases de datos con los que estoy familiarizado (MsSQL, Oracle) los índices son estructuras de árbol equilibrado, cuyos nodos están organizados como una tupla secuenciada de las columnas el índice se define en ( en la secuencia definida )
Entonces, a menos que mySql lo haga de manera muy diferente, (probablemente no), cualquier consulta que necesite filtrar u ordenar por un subconjunto de las columnas en el índice puede utilizar cualquier índice compuesto (en más de una columna), siempre y cuando la lista de columnas es compatible, es decir, si las columnas, cuando se secuencian igual que la lista de columnas secuenciadas en el índice completo, es un subconjunto ordenado del conjunto completo de columnas de índice, que comienza al comienzo de la secuencia de índice real, sin huecos excepto al final ...
En otras palabras, esto significa que si tiene un índice en (a, b, c, d) una consulta que filtra en (a), (a, b) o (a, b, c) también puede usar el índice , pero una consulta que necesita filtrar en (b), o (c) o (b, c) no podrá usar el índice ...
Entonces, en su caso, si a menudo necesita filtrar u ordenar solo el elemento de la columna, debe agregar otro índice en esa columna por sí mismo ...
No estoy muy familiarizado con MySQL, pero en general, un índice de múltiples columnas es igualmente útil en la primera columna del índice como un índice en esa columna sola. El índice de varias columnas se vuelve menos útil para consultar en una sola columna cuanto más aparece la columna en el índice.
Esto tiene sentido si piensa en el índice de varias columnas como una jerarquía. La primera columna en el índice es la raíz de la jerarquía, por lo que buscarla es solo una cuestión de escanear ese primer nivel. Sin embargo, para escanear la segunda columna, la base de datos tiene que buscar el árbol para cada valor único encontrado en la primera columna. Esto puede ser lo suficientemente costoso como para que la mayoría de los optimizadores no se molesten en analizar en profundidad un índice de varias columnas, en lugar de optar por un escaneo completo de la tabla.
Por ejemplo, si tiene una tabla de la siguiente manera:
Col1 |Col2 |Col3
----------------
A | 1 | Z
A | 2 | Y
A | 2 | X
B | 1 | Z
B | 2 | X
Suponiendo que tiene un índice en las tres columnas, en orden, el árbol tendrá un aspecto similar al siguiente:
A
+-1
+-Z
+-2
+-X
+-Y
B
+-1
+-Z
+-2
+-X
Buscar Col1 = ''A'' es fácil: solo tienes que mirar 2 valores ordenados. Sin embargo, para resolver col3 = ''X'', debe mirar todos los valores en los 4 grupos más grandes, cada uno de los cuales se ordena individualmente.
Para devolver información de índice de tabla, puede utilizar:
SHOW INDEX FROM <table>;
Consulte: http://dev.mysql.com/doc/refman/5.0/en/show-index.html
Para ver la información de la mesa:
SHOW CREATE TABLE <table>;
Consulte: http://dev.mysql.com/doc/refman/5.0/en/show-create-table.html
Las claves primarias son índices, por lo que no es necesario crear índices adicionales. Puede encontrar más información sobre ellos en la sintaxis de CREATE TABLE (hay mucho para insertar aquí):
Personalmente uso phpMyAdmin para ver y editar la estructura de las bases de datos MySQL. Es una aplicación web, pero se ejecuta lo suficientemente bien en un servidor web local (ejecuto una instancia de apache en mi máquina para esto y phpPgAdmin).
En cuanto a la clave compuesta de (invoice, item) , actúa como un índice para (invoice, item) y para invoice . Si desea indexar solo un item , debe agregar ese índice usted mismo. Su PK se ordenará por invoice y luego por item donde la invoice es la misma en varios registros. Si bien el orden en una PK compuesta no importa para la aplicación de la unicidad, sí importa para el acceso.
En tu mesa yo usaría:
PRIMARY KEY id (invoice, item), INDEX (item)