significa - Longitud del índice varchar de MySQL

unique index mysql que significa

Question

Tengo una mesa como esta:

CREATE TABLE `products` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(512) NOT NULL,
  `description` text,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8;

y uno como este:

CREATE TABLE `product_variants` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(11) unsigned NOT NULL,
  `product_code` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_code` (`product_code`),
  KEY `product_variant_product_fk` (`product_id`),
  CONSTRAINT `product_variant_product_fk` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1037 DEFAULT CHARSET=utf8;

y una declaración SQL como esta

SELECT p.id AS id, p.name AS name, p.description AS description, pv.id AS product_variant_id, pv.product_code AS product_code
FROM products p
INNER JOIN product_variants pv ON pv.product_id = p.id
ORDER BY p.name ASC
LIMIT 300 OFFSET 0;

que si explico me da esto:

+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
| id | select_type | table | type | possible_keys              | key                        | key_len | ref     | rows   | Extra          |
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
|  1 | SIMPLE      | p     | ALL  | PRIMARY                    | NULL                       | NULL    | NULL    | 993658 | Using filesort |
|  1 | SIMPLE      | pv    | ref  | product_variant_product_fk | product_variant_product_fk | 4       | db.p.id |      1 |                |
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
2 rows in set (0.00 sec)

Para un millón de filas, esto es bastante lento. Intenté agregar un índice en products.name con:

ALTER TABLE products ADD INDEX `product_name_idx` (name(512));

que le da esto:

mysql> show indexes from products;
+----------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name         | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| products |          0 | PRIMARY          |            1 | id              | A         |      993658 |     NULL | NULL   |      | BTREE      |         |               |
| products |          1 | product_manf_fk  |            1 | manufacturer_id | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |               |
| products |          1 | product_name_idx |            1 | name            | A         |         201 |      255 | NULL   |      | BTREE      |         |               |
+----------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

Creo que la columna Sub_part muestra el prefijo que se ha indexado (en bytes), como se describe en esta página .

Cuando vuelvo a explicar la consulta, obtengo:

+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
| id | select_type | table | type | possible_keys              | key                        | key_len | ref     | rows   | Extra          |
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
|  1 | SIMPLE      | p     | ALL  | PRIMARY                    | NULL                       | NULL    | NULL    | 993658 | Using filesort |
|  1 | SIMPLE      | pv    | ref  | product_variant_product_fk | product_variant_product_fk | 4       | db.p.id |      1 |                |
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
2 rows in set (0.00 sec)

que parece que el nuevo índice no se está utilizando. Como se describe en esta página , los índices no se usarán para clasificar si son índices de prefijos. De hecho, si trunco ​​los datos con:

alter table products modify `name`  varchar(255) not null;

La explicación da:

+----+-------------+-------+-------+----------------------------+----------------------------+---------+----------------------------------------------+------+-------+
| id | select_type | table | type  | possible_keys              | key                        | key_len | ref                                          | rows | Extra |
+----+-------------+-------+-------+----------------------------+----------------------------+---------+----------------------------------------------+------+-------+
|  1 | SIMPLE      | p     | index | PRIMARY                    | product_name_idx           | 767     | NULL                                         |  300 |       |
|  1 | SIMPLE      | pv    | ref   | product_variant_product_fk | product_variant_product_fk | 4       | oh_2c98c233_69fe_4f06_ad0d_fe6f85a5beac.p.id |    1 |       |
+----+-------------+-------+-------+----------------------------+----------------------------+---------+----------------------------------------------+------+-------+

que creo que respalda eso. Sin embargo, dice en esta página que las tablas InnoDB pueden tener hasta 767 bytes de índice. Si la longitud es en bytes, ¿por qué se niega a tener más de 255? Si está en caracteres, ¿cómo está decidiendo la longitud de cada carácter UTF-8? ¿Es solo asumir 3?

Además, estoy usando esta versión de MySQL:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.27-log |
+------------+
1 row in set (0.00 sec)

Answer 1

Debo revisar mi respuesta debido a mi investigación. Originalmente publiqué esto (citando a mí mismo):

Creo que la respuesta es que no puede saber cuántos caracteres habrá en el índice porque no puede saber cuántos bytes serán sus caracteres (a menos que haga algo para excluir caracteres de varios bytes).

Y no estoy seguro, pero podría ser correcto, pero no del modo en que estaba pensando.

Aquí está la respuesta correcta:

MySQL asume 3 bytes por carácter utf8. 255 caracteres es el tamaño de índice máximo que puede especificar por columna, porque 256x3 = 768, que rompe el límite de 767 bytes.

Si no especifica el tamaño del índice, MySQL elige el tamaño máximo (es decir, 255 por columna). Una restricción ÚNICA no se puede poner en una columna utf8 cuya longitud sea mayor que 255, porque un índice único debe contener el valor de celda completo. Pero se puede usar un índice regular: solo indexará los primeros 255 caracteres (¿o los primeros 767 bytes?). Y ahí es donde todavía hay un misterio para mí.

El MySTERY: puedo ver por qué MySQL supone 3 bytes por carácter, por seguridad, porque de lo contrario la restricción UNIQUE podría romperse. Pero los documentos parecen sugerir que el índice en realidad está dimensionado en bytes, no en caracteres. Entonces, supongamos que coloca un índice de 25 5 caracteres (765 bytes) en una columna varchar (25 6 ). Si los caracteres que almacena son todos ASCII, caracteres de 1 byte, como AZ, az, 0-9, entonces puede ajustar toda la columna en el índice de 767 bytes. Y parece que eso es lo que realmente sucederá.

A continuación hay más información de mi respuesta original sobre caracteres, bytes, etc.

Según wikipedia , el personaje UTF-8 puede tener 1,2, 3 o 4 bytes de longitud. Pero, de acuerdo con esta documentación de mysql , el tamaño del carácter maximium es de 3 bytes, por lo que cualquier índice índice de columna de más de 255 caracteres puede alcanzar ese límite de bytes. Pero tal como lo entiendo, puede que no. Si la mayoría de tus personajes están en el rango ASCII, entonces el tamaño promedio de tu personaje estará más cerca de 1 byte. Si el tamaño promedio de su carácter es, por ejemplo, 1.3 bytes (principalmente 1 byte, pero un número significativo de caracteres de 2-3 bytes), entonces podría especificar un índice de 767 / 1.3

Por lo tanto, si está almacenando principalmente caracteres de 1 byte, su límite de caracteres real sería más parecido a: 767 / 1.3 = 590. Pero resulta que no es la forma en que funciona. 255 caracteres es el límite.

Como se menciona en esta documentación de MySQL ,

Los límites del prefijo se miden en bytes, mientras que la longitud del prefijo en las instrucciones CREATE INDEX se interpreta como el número de caracteres para los tipos de datos no binarios (CHAR, VARCHAR, TEXT). Tenga esto en cuenta cuando se especifica una longitud de prefijo para una columna que usa un juego de caracteres de múltiples bytes.

Parece que MySQL está aconsejando a las personas que hagan un cálculo / estimación como lo hice para determinar el tamaño de tu clave para una columna varchar. Pero, de hecho, no puede especificar un índice de más de 255 para columnas utf8.

Finalmente, si vuelves a consultar mi segundo enlace, también está esto:

Cuando la opción de configuración innodb_large_prefix está habilitada, este límite de longitud se eleva a 3072 bytes, para las tablas InnoDB que usan los formatos de filas DYNAMIC y COMPRESSED.

Entonces, parece que puede obtener índices mucho más grandes si lo desea, con un poco de ajuste. Solo asegúrese de que los formatos de fila sean DINÁMICOS o COMPRIMIDOS. Probablemente pueda especificar un índice de 1023 o 1024 caracteres en ese caso.

Por cierto, resulta que puedes almacenar caracteres de 4 bytes usando el conjunto de caracteres utf8mb4 . El juego de caracteres utf8 aparentemente almacena solo caracteres de "plano 0" .

EDITAR:

Intenté crear un índice compuesto en una columna varchar (511) con una columna tinyint (1) y obtuve el mensaje de error que decía que el tamaño máximo del índice era de 767 bytes. Esto me hace creer que MySQL asume que las columnas del conjunto de caracteres utf8 contendrán 3 bytes por carácter (el máximo), y le permite usar 255 caracteres máximos. Pero tal vez eso solo sea con índices compuestos. Actualizaré mi respuesta a medida que descubro más. Pero por ahora lo dejo como una edición.

Answer 2

Límites en tablas InnoDB

Advertencia

No convierta tablas del sistema MySQL en la base de datos mysql desde MyISAM a tablas InnoDB. Esta es una operación no compatible. Si hace esto, MySQL no se reinicia hasta que restaure las tablas del sistema anterior a partir de una copia de seguridad o las vuelva a generar con el programa mysql_install_db.

Advertencia

No es una buena idea configurar InnoDB para utilizar archivos de datos o archivos de registro en volúmenes NFS. De lo contrario, los archivos podrían estar bloqueados por otros procesos y dejar de estar disponibles para el uso de MySQL.

Máximos y mínimos

1. Una tabla puede contener un máximo de 1000 columnas.
2. Una tabla puede contener un máximo de 64 índices secundarios.
3. De forma predeterminada, una clave de índice para un índice de una sola columna puede tener hasta 767 bytes. El mismo límite de longitud se aplica a cualquier prefijo de clave de índice. Por ejemplo, puede llegar a este límite con un índice de prefijo de columna de más de 255 caracteres en una columna TEXT o VARCHAR, asumiendo un conjunto de caracteres UTF-8 y el máximo de 3 bytes para cada carácter. Cuando la opción de configuración innodb_large_prefix está habilitada, este límite de longitud se eleva a 3072 bytes, para las tablas InnoDB que usan los formatos de filas DYNAMIC y COMPRESSED.
4. Si especifica una longitud de prefijo de índice que es mayor que el valor máximo permitido, la longitud se reduce silenciosamente a la longitud máxima. En MySQL 5.6 y posterior, especificar un índice de longitud de prefijo mayor que la longitud máxima produce un error.

Cuando innodb_large_prefix está habilitado, el intento de crear un prefijo de índice con una longitud de clave mayor que 3072 para una tabla REDUNDANT o COMPACT provoca un error ER_INDEX_COLUMN_TOO_LONG.

La longitud de la clave máxima interna de InnoDB es de 3500 bytes, pero MySQL restringe esto a 3072 bytes. Este límite se aplica a la longitud de la clave de índice combinada en un índice de varias columnas.

La longitud máxima de fila, a excepción de las columnas de longitud variable (VARBINARY, VARCHAR, BLOB y TEXT), es ligeramente inferior a la mitad de una página de base de datos. Es decir, la longitud máxima de la fila es de aproximadamente 8000 bytes. Las columnas LONGBLOB y LONGTEXT deben tener menos de 4 GB y la longitud total de la fila, incluidas las columnas BLOB y TEXT, debe ser inferior a 4 GB.

Referencia: Restricciones de InnoDB