Si te encuentras con algo que no entiendes puedes dejarlo en los comentarios y haremos todo lo posible de ayudarte tan rápido como podamos.
Solución:
Los UUID son un desastre de rendimiento para tablas muy grandes. (200.000 filas no es “muy grande”).
Tu # 3 es realmente malo cuando el CHARCTER SET es utf8 – CHAR(36) ocupa 108 bytes! Actualización: hay ROW_FORMATs por lo que esto se quedará 36.
Los UUID (GUID) son muy “aleatorios”. Usándolos como ÚNICO o PRIMARIO key sobre grande tablas es muy ineficiente. Esto se debe a que tiene que saltar alrededor de la tabla / índice cada vez que INSERT un nuevo UUID o SELECT por UUID. Cuando la tabla / índice es demasiado grande para caber en la caché (consulte innodb_buffer_pool_size, que debe ser más pequeña que la RAM, normalmente un 70%), es posible que el UUID ‘siguiente’ no se almacene en caché, por lo tanto, un golpe de disco lento. Cuando la tabla / índice es 20 veces más grande que la caché, solo se almacenan en caché una vigésima parte (5%) de las visitas; usted está vinculado a E / S. Generalización: la ineficiencia se aplica a cualquier acceso “aleatorio”: UUID / MD5 / RAND () / etc.
Por lo tanto, no use UUID a menos que
- tienes mesas “pequeñas”, o
- realmente los necesita porque genera identificadores únicos de diferentes lugares (y no ha descubierto otra forma de hacerlo).
Más sobre UUID: http://mysql.rjweb.org/doc.php/uuid (Incluye funciones para convertir entre 36 caracteres estándar UUIDs y BINARY(16).) Actualización: MySQL 8.0 tiene una función incorporada para ello.
Tener tanto un ÚNICO AUTO_INCREMENT y un UNIQUE UUID en la misma tabla es un desperdicio.
- Cuando un
INSERTocurre, todos único / primario keys debe comprobarse si hay duplicados. - Cualquiera de los dos únicos key es suficiente para el requisito de InnoDB de tener un
PRIMARY KEY. BINARY(16)(16 bytes) es algo voluminoso (un argumento en contra de convertirlo en el PK), pero no está tan mal.- El volumen importa cuando tienes secundaria keys. InnoDB pega silenciosamente el PK al final de cada secundario key. La lección principal aquí es minimizar el número de secundarias keys, especialmente para mesas muy grandes. Elaboración: Para una secundaria key, el debate sobre el volumen generalmente termina en empate. Para 2 o más secundaria keys, una PK más gruesa generalmente conduce a una huella de disco más grande para la tabla, incluidos sus índices.
Para comparar: INT UNSIGNED es de 4 bytes con un rango de 0..4 mil millones. BIGINT es de 8 bytes.
Se agregaron actualizaciones en cursiva / etc en septiembre de 2017; nada crítico cambió.
Abordar la respuesta de Iván y el enlace que proporcionó.
- Sí, hay beneficios de un generador de ID distribuido. Pero…
- Hay otras formas de evitar la demora de un servidor de ID centralizado: distribuya 100 ID consecutivos a la vez al cliente. O utilice una concatenación generada por el cliente de client_id y time.
- Si todo el índice se almacena en caché en la RAM, entonces gran parte de mi argumento es discutible. Pero una vez que se derrama en el disco, el rendimiento se ve gravemente afectado. Su artículo señala brevemente que UUIDv4 es más lento que UUIDv1, pero no señala que los bits deben reorganizarse para lograr el beneficio.
- Estoy de acuerdo con comprimir un UUID de 36 bytes en uno de 16 bytes
BINARY. MySQL 8 incluso tiene una función para hacerlo. Además, (y mi blog) reorganizan los bits para que UUIDv1 tenga la característica temporal de un auto_increment. - La fragmentación, sostengo, normalmente no se realiza contra los identificadores de auto_increment, por lo que cambiar a UUID no compra nada.
‘Rick James’ dijo en la respuesta aceptada:
“Tener un AUTO_INCREMENT ÚNICO y un UUID ÚNICO en la misma tabla es un desperdicio”.
Pero esta prueba (la hice en mi máquina) muestra hechos diferentes.
Por ejemplo: con la prueba (T2) hago tabla con (INT AUTOINCREMENT) PRIMARY y UNIQUE BINARY (16) y otro campo como título, luego inserto más de 1.6M filas con muy buen desempeño, pero con otra prueba (T3) Hice lo mismo, pero el resultado es lento después de insertar solo 300,000 filas.
Este es el resultado de mi prueba:
T1:
char(32) UNIQUE with auto increment int_id
after: 1,600,000
10 sec for inserting 1000 rows
select + (4.0)
size:500mb
T2:
binary(16) UNIQUE with auto increment int_id
after: 1,600,000
1 sec for inserting 1000 rows
select +++ (0.4)
size:350mb
T3:
binary(16) UNIQUE without auto increment int_id
after: 350,000
5 sec for inserting 1000 rows
select ++ (0.3)
size:118mb (~ for 1,600,000 will be 530mb)
T4:
auto increment int_id without binary(16) UNIQUE
++++
T5:
uuid_short() int_id without binary(16) UNIQUE
+++++*
Entonces, binary (16) UNIQUE con auto incremento int_id es mejor que binary (16) UNIQUE sin auto incremento int_id.
Actualizar:
Vuelvo a hacer la misma prueba y anoto más detalles. este es el código completo y la comparación de resultados entre (T2) y (T3) como se explicó anteriormente.
(T2) crea tbl2 (mysql):
CREATE TABLE test.tbl2 (
int_id INT(11) NOT NULL AUTO_INCREMENT,
rec_id BINARY(16) NOT NULL,
src_id BINARY(16) DEFAULT NULL,
rec_title VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (int_id),
INDEX IDX_tbl1_src_id (src_id),
UNIQUE INDEX rec_id (rec_id)
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;
(T3) crea tbl3 (mysql):
CREATE TABLE test.tbl3 (
rec_id BINARY(16) NOT NULL,
src_id BINARY(16) DEFAULT NULL,
rec_title VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (rec_id),
INDEX IDX_tbl1_src_id (src_id)
)
ENGINE = INNODB
CHARACTER SET utf8
COLLATE utf8_general_ci;
Este es un código de prueba completo, está insertando 600,000 registros en tbl2 o tbl3 (código vb.net):
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim res As String = ""
Dim i As Integer = 0
Dim ii As Integer = 0
Dim iii As Integer = 0
Using cn As New SqlClient.SqlConnection
cn.ConnectionString = "Data Source=.sql2008;Integrated Security=True;User Instance=False;MultipleActiveResultSets=True;Initial Catalog=sourcedb;"
cn.Open()
Using cmd As New SqlClient.SqlCommand
cmd.Connection = cn
cmd.CommandTimeout = 0
cmd.CommandText = "select recID, srcID, rectitle from textstbl order by ID ASC"
Using dr As SqlClient.SqlDataReader = cmd.ExecuteReader
Using mysqlcn As New MySql.Data.MySqlClient.MySqlConnection
mysqlcn.ConnectionString = "User Id=root;Host=localhost;Character Set=utf8;Pwd=1111;Database=test"
mysqlcn.Open()
Using MyCommand As New MySql.Data.MySqlClient.MySqlCommand
MyCommand.Connection = mysqlcn
MyCommand.CommandText = "insert into tbl3 (rec_id, src_id, rec_title) values (UNHEX(@rec_id), UNHEX(@src_id), @rec_title);"
Dim MParm1(2) As MySql.Data.MySqlClient.MySqlParameter
MParm1(0) = New MySql.Data.MySqlClient.MySqlParameter("@rec_id", MySql.Data.MySqlClient.MySqlDbType.String)
MParm1(1) = New MySql.Data.MySqlClient.MySqlParameter("@src_id", MySql.Data.MySqlClient.MySqlDbType.String)
MParm1(2) = New MySql.Data.MySqlClient.MySqlParameter("@rec_title", MySql.Data.MySqlClient.MySqlDbType.VarChar)
MyCommand.Parameters.AddRange(MParm1)
MyCommand.CommandTimeout = 0
Dim mytransaction As MySql.Data.MySqlClient.MySqlTransaction = mysqlcn.BeginTransaction()
MyCommand.Transaction = mytransaction
Dim sw As New Stopwatch
sw.Start()
While dr.Read
MParm1(0).Value = dr.GetValue(0).ToString.Replace("-", "")
MParm1(1).Value = EmptyStringToNullValue(dr.GetValue(1).ToString.Replace("-", ""))
MParm1(2).Value = gettitle(dr.GetValue(2).ToString)
MyCommand.ExecuteNonQuery()
i += 1
ii += 1
iii += 1
If i >= 1000 Then
i = 0
Dim ts As TimeSpan = sw.Elapsed
Me.Text = ii.ToString & " / " & ts.TotalSeconds
Select Case ii
Case 10000, 50000, 100000, 200000, 300000, 400000, 500000, 600000, 700000, 800000, 900000, 1000000
res &= "On " & FormatNumber(ii, 0) & ": last inserting 1000 records take: " & ts.TotalSeconds.ToString & " second." & vbCrLf
End Select
If ii >= 600000 Then GoTo 100
sw.Restart()
End If
If iii >= 5000 Then
iii = 0
mytransaction.Commit()
mytransaction = mysqlcn.BeginTransaction()
sw.Restart()
End If
End While
100:
mytransaction.Commit()
End Using
End Using
End Using
End Using
End Using
TextBox1.Text = res
MsgBox("Ok!")
End Sub
Public Function EmptyStringToNullValue(MyValue As Object) As Object
'On Error Resume Next
If MyValue Is Nothing Then Return DBNull.Value
If String.IsNullOrEmpty(MyValue.ToString.Trim) Then
Return DBNull.Value
Else
Return MyValue
End If
End Function
Private Function gettitle(p1 As String) As String
If p1.Length > 255 Then
Return p1.Substring(0, 255)
Else
Return p1
End If
End Function
End Class
El resultado de (T2):
On 10,000: last inserting 1000 records take: 0.13709 second.
On 50,000: last inserting 1000 records take: 0.1772109 second.
On 100,000: last inserting 1000 records take: 0.1291394 second.
On 200,000: last inserting 1000 records take: 0.5793488 second.
On 300,000: last inserting 1000 records take: 0.1296427 second.
On 400,000: last inserting 1000 records take: 0.6938583 second.
On 500,000: last inserting 1000 records take: 0.2317799 second.
On 600,000: last inserting 1000 records take: 0.1271072 second.
~3 Minutes ONLY! to insert 600,000 records.
table size: 128 mb.
El resultado para (T3):
On 10,000: last inserting 1000 records take: 0.1669595 second.
On 50,000: last inserting 1000 records take: 0.4198369 second.
On 100,000: last inserting 1000 records take: 0.1318155 second.
On 200,000: last inserting 1000 records take: 0.1979358 second.
On 300,000: last inserting 1000 records take: 1.5127482 second.
On 400,000: last inserting 1000 records take: 7.2757161 second.
On 500,000: last inserting 1000 records take: 14.3960671 second.
On 600,000: last inserting 1000 records take: 14.9412401 second.
~40 Minutes! to insert 600,000 records.
table size: 164 mb.