query - mysql create table with index

Características ocultas de MySQL

Question

He estado trabajando con Microsoft SQL Server hace muchos años, pero recientemente comencé a usar MySQL con mis aplicaciones web, y estoy ansioso por saberlo.

Para continuar con la larga lista de preguntas sobre funciones ocultas , me gustaría conocer las características ocultas o útiles de MySQL que, con suerte, mejorarán mi conocimiento de esta base de datos de código abierto.

Answer 1

Algunas cosas que pueden resultarle interesantes:

<query>/G -- /G in the CLI instead of the ; will show one column per row
explain <query>; -- this will show the execution plan for the query

Answer 2

Durante mis pruebas de referencia con grandes conjuntos de datos y campos DATETIME, siempre es más lento hacer esta consulta:

SELECT * FROM mytable
WHERE date(date_colum) BETWEEN ''2011-01-01'' AND ''''2011-03-03'';

Que este enfoque:

SELECT * FROM mytable
WHERE date_column BETWEEN ''2011-01-01 00:00:00'' AND ''2011-03-03 23:59:59''

Answer 3

El analizador de SQL incorporado.

Answer 4

El comando "pager" en el cliente

Si tiene, digamos, 10.000 filas en el resultado y desea verlas (esto supone que los comandos "menos" y "T" están disponibles, lo que normalmente es el caso en Linux, en Windows YMMV).

pager less
select lots_of_stuff FROM tbl WHERE clause_which_matches_10k_rows;

Y los obtendrá en el visor de archivos "menos" para que pueda hojearlos fácilmente, buscarlos, etc.

también

pager tee myfile.txt
select a_few_things FROM tbl WHERE i_want_to_save_output_to_a_file;

Convenientemente escribirá en un archivo.

Answer 5

Estos son algunos de mis consejos: publiqué sobre ellos en mi blog ( Link )

1. No necesita usar el signo ''@'' al declarar variables.
2. Debe usar un delimitador (el valor predeterminado es '';'') para demarcar el final de una instrucción - Link
3. Si intenta mover datos entre MS-SQL 2005 y mySQL hay algunos aros que saltar: Link
4. Hacer coincidencias sensibles a mayúsculas y minúsculas en mySQL - link

Answer 6

InnoDB almacena de manera predeterminada todas las tablas en un espacio de tablas global que nunca se reducirá .

Puede usar innodb_file_per_table que colocará cada tabla en un espacio de tablas separado que se eliminará cuando suelte la tabla o base de datos.

Planifique con anticipación, ya que de lo contrario tendrá que volcar y restaurar la base de datos para reclamar espacio.

Uso de espacios de tabla por tabla

Answer 7

Me encantan on duplicate key (AKA upsert, merge) para todo tipo de contadores creados perezosamente:

insert into occurances(word,count) values(''foo'',1),(''bar'',1) 
  on duplicate key cnt=cnt+1

Puede insertar muchas filas en una consulta e inmediatamente manejar el índice duplicado para cada una de las filas.

Answer 8

No creo que esto sea específico de MySQL, sino que destaque para mí:

En lugar de escribir

WHERE (x.id > y.id) OR (x.id = y.id AND x.f2 > y.f2) 

Puedes escribir

WHERE (x.id, x.f2) > (y.id, y.f2)

Answer 9

No es una función oculta, pero útil: http://mtop.sourceforge.net/

Answer 10

Nuevamente, no son funciones realmente ocultas, pero realmente útiles:

Característica

Adquiera fácilmente DDL:

SHOW CREATE TABLE CountryLanguage

salida:

CountryLanguage | CREATE TABLE countrylanguage (
  CountryCode char(3) NOT NULL DEFAULT '''',
  Language char(30) NOT NULL DEFAULT '''',
  IsOfficial enum(''T'',''F'') NOT NULL DEFAULT ''F'',
  Percentage float(4,1) NOT NULL DEFAULT ''0.0'',
  PRIMARY KEY (CountryCode,Language)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Característica: función agregada GROUP_CONCAT () Crea una cadena concatenada de sus argumentos por detalle, y agrega concatenando los por grupo.

Ejemplo 1: simple

SELECT   CountryCode
,        GROUP_CONCAT(Language) AS List
FROM     CountryLanguage
GROUP BY CountryCode             

Salida:

+-------------+------------------------------------+
| CountryCode | List                               |
+-------------+------------------------------------+
| ABW         | Dutch,English,Papiamento,Spanish   |
. ...         . ...                                .
| ZWE         | English,Ndebele,Nyanja,Shona       |
+-------------+------------------------------------+

Ejemplo 2: argumentos múltiples

SELECT   CountryCode
,        GROUP_CONCAT(
             Language
,            IF(IsOfficial=''T'', '' (Official)'', '''')
         )               AS List
FROM     CountryLanguage
GROUP BY CountryCode

Salida:

+-------------+---------------------------------------------+
| CountryCode | List                                        |
+-------------+---------------------------------------------+
| ABW         | Dutch (Official),English,Papiamento,Spanish |
. ...         . ...                                         .
| ZWE         | English (Official),Ndebele,Nyanja,Shona     |
+-------------+---------------------------------------------+

Ejemplo 3: usar un separador personalizado

SELECT   CountryCode
,        GROUP_CONCAT(Language SEPARATOR '' and '') AS List
FROM     CountryLanguage
GROUP BY CountryCode

Salida:

+-------------+----------------------------------------------+
| CountryCode | List                                         |
+-------------+----------------------------------------------+
| ABW         | Dutch and English and Papiamento and Spanish |
. ...         . ...                                          .
| ZWE         | English and Ndebele and Nyanja and Shona     |
+-------------+----------------------------------------------+

Ejemplo 4: control del orden de los elementos de la lista

SELECT   CountryCode
,        GROUP_CONCAT(
         Language
         ORDER BY CASE IsOfficial WHEN ''T'' THEN 1 ELSE 2 END DESC
         ,        Language
         )               AS List
FROM     CountryLanguage
GROUP BY CountryCode

Salida:

+-------------+------------------------------------+
| CountryCode | List                               |
+-------------+------------------------------------+
| ABW         | English,Papiamento,Spanish,Dutch,  |
. ...         . ...                                .
| ZWE         | Ndebele,Nyanja,Shona,English       |
+-------------+------------------------------------+

Característica: COUNT (DISTINCT) con múltiples expresiones

Puede usar expresiones múltiples en una expresión COUNT (DISTINCT ...) para contar el número de combinaciones.

SELECT COUNT(DISTINCT CountryCode, Language) FROM CountryLanguage

Función / Gotcha: no es necesario incluir expresiones no agregadas en la lista GROUP BY

La mayoría de los RDBMS-es imponen un GROUP BY compatible con SQL92 que requiere que todas las expresiones no agregadas en la lista SELECT aparezcan en GROUP BY. En estos RDBMS-es, esta declaración:

SELECT     Country.Code, Country.Continent, COUNT(CountryLanguage.Language)
FROM       CountryLanguage 
INNER JOIN Country 
ON         CountryLanguage.CountryCode = Country.Code
GROUP BY   Country.Code

no es válido, porque la lista SELECCIONAR contiene la columna no agregada Country.Continent que no aparece en la lista GROUP BY. En estos RDBMS-es, debe modificar la lista GROUP BY para leer

GROUP BY   Country.Code, Country.Continent

o debe agregar algún agregado sin sentido a Country.Continent, por ejemplo

SELECT     Country.Code, MAX(Country.Continent), COUNT(CountryLanguage.Language)

Ahora, lógicamente, no hay nada que exija que Country.Continent se agregue. Ver, Country.Code es la clave principal de la tabla Country. Country.Continent también es una columna de la tabla Country y, por lo tanto, las definiciones dependen funcionalmente de la clave primaria Country.Code. Por lo tanto, debe existir exactamente un valor en Country.Continent para cada Country.Code distinto. Si se da cuenta de eso, entonces se da cuenta de que no tiene sentido agregarlo (solo hay un valor, correcto) ni agruparlo (ya que no hará que el resultado sea más único ya que se está agrupando en el pk)

De todos modos, MySQL le permite incluir columnas no agregadas en la lista SELECT sin que sea necesario que también las agregue a la cláusula GROUP BY.

El problema con esto es que MySQL no lo protege en caso de que utilice una columna no agregada. Entonces, una consulta como esta:

SELECT     Country.Code, COUNT(CountryLanguage.Language), CountryLanguage.Percentage
FROM       CountryLanguage 
INNER JOIN Country 
ON         CountryLanguage.CountryCode = Country.Code
GROUP BY   Country.Code

Se ejecutará sin quejarse, pero la columna CountryLanguage.Percentage no tendrá sentido (es decir, de todos los porcentajes de idiomas, uno de los valores disponibles para el porcentaje se seleccionará al azar o al menos fuera de su control.

Ver: Desacreditar al grupo por mitos

Answer 11

Particularmente me gusta el soporte inet_ntoa() de inet_ntoa() para inet_ntoa() y inet_aton() . Hace que el manejo de las direcciones IP en las tablas sea muy sencillo (¡al menos mientras solo sean direcciones IPv4!)

Answer 12

Realmente documented , pero muy molesto: conversiones automáticas para fechas incorrectas y otras entradas incorrectas.

Antes de MySQL 5.0.2, MySQL es indulgente con los valores de datos ilegales o inapropiados y los coacciona a valores legales para la entrada de datos. En MySQL 5.0.2 y versiones posteriores, ese sigue siendo el comportamiento predeterminado, pero puede cambiar el modo SQL del servidor para seleccionar un tratamiento más tradicional de los valores incorrectos, de modo que el servidor los rechace y anule la declaración en la que ocurren.

En cuanto a las fechas: a veces serás "afortunado" cuando MySQL no ajuste la entrada a fechas válidas cercanas, sino que la almacene como 0000-00-00 que por definición no es válida. Sin embargo, incluso entonces es posible que haya querido que MySQL falle en lugar de almacenar silenciosamente este valor para usted.

Answer 13

Si inserta en la columna de fecha y hora el valor de cadena vacía "", MySQL retendrá el valor como 00/00/0000 00:00:00. A diferencia de Oracle, que salvará el valor nulo.

Answer 14

Si usa cmdline Mysq, puede interactuar con la línea de comando (en máquinas con Linux - no estoy seguro de si hay un efecto equivalente en Windows) usando el signo de exclamación / chillido. Por ejemplo:

/! cat file1.sql

mostrará el código para file1.sql. Para guardar su estado de cuenta y consulta en un archivo, use la función de T

/T filename

para apagar esto use / t

Por último, para ejecutar un script que ya ha guardado, use "nombre de archivo fuente". Por supuesto, la alternativa normal es dirigir en el nombre del script al iniciar mysql desde la línea de comando:

    mysql -u root -p < case1.sql

Espero que sea de utilidad para alguien!

Editar: Acabo de recordar otro: al invocar mysql desde la línea de comandos puede usar el modificador -t para que la salida esté en formato de tabla, una verdadera ventaja con algunas consultas (aunque, por supuesto, finalizar consultas con / G como se menciona en otro lugar aquí también es útil a este respecto). Mucho más en varios interruptores Command Line Tool

Acabo de descubrir una manera ordenada de cambiar el orden de un tipo (normalmente use Case ...) Si desea cambiar el orden de un tipo (tal vez ordenar por 1, 4, 3, 2 en lugar de 1, 2, 3, 4) puede usar la función de campo dentro de la cláusula Order by. Por ejemplo

Ordenar por campo (sort_field, 1,4,3,2)

• Encontré esto aquí Ordenar por day_of_week en MySQL courtesey del usuario gms8994

Answer 15

Si va a trabajar con una transacción grande o grande, las bases de datos de InnoDb aprenden y entienden el "MOSTRAR ESTADO DE INNODB" del Blog de rendimiento Mysql , se convertirá en su amigo.

Answer 16

Un comando para averiguar qué tablas están actualmente en la caché:

mysql> SHOW open TABLES FROM test;
+----------+-------+--------+-------------+
| DATABASE | TABLE | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | a     |      3 |           0 |
+----------+-------+--------+-------------+
1 row IN SET (0.00 sec)

(Desde el blog de rendimiento de MySQL )

Answer 17

Un comando para descubrir quién está haciendo qué:

mysql> show processlist;
show processlist;
+----+-------------+-----------------+------+---------+------+----------------------------------+------------------+
| Id | User        | Host            | db   | Command | Time | State                            | Info             |
+----+-------------+-----------------+------+---------+------+----------------------------------+------------------+
|  1 | root        | localhost:32893 | NULL | Sleep   |    0 |                                  | NULL             |
|  5 | system user |                 | NULL | Connect |   98 | Waiting for master to send event | NULL             |
|  6 | system user |                 | NULL | Connect | 5018 | Reading event from the relay log | NULL             |
+-----+------+-----------+---------+---------+-------+-------+------------------+
3 rows in set (0.00 sec) 

Y puedes matar un proceso con:

mysql>kill 5 

Answer 18

Una de las características no tan ocultas de MySQL es que no es realmente bueno para ser compatible con SQL, bueno, no son bugs realmente, pero, más gotchas ... :-)

Answer 19

Ya que pones una recompensa, compartiré mis secretos ganados con esfuerzo ...

En general, todos los SQL que sintonicé hoy requieren el uso de subconsultas. Habiendo venido del mundo de la base de datos Oracle, las cosas que daba por sentadas no funcionaban igual con MySQL. Y mi lectura sobre el ajuste de MySQL me hace concluir que MySQL está detrás de Oracle en términos de optimización de consultas.

Si bien las consultas simples requeridas para la mayoría de las aplicaciones B2C pueden funcionar bien para MySQL, la mayoría de las consultas agregadas para informes de inteligencia parecen requerir bastante planificación y reorganización de las consultas SQL para guiar a MySQL para que las ejecute más rápido.

Administración:

max_connections es el número de conexiones simultáneas. El valor predeterminado es 100 conexiones (151 desde 5.0), muy pequeño.

Nota:

las conexiones toman la memoria y es posible que su sistema operativo no pueda manejar muchas conexiones.

Los binarios de MySQL para Linux / x86 le permiten tener hasta 4096 conexiones simultáneas, pero los binarios compilados a menudo tienen menos límite.

Establezca table_cache para que coincida con el número de sus tablas abiertas y conexiones concurrentes. Mire el valor de open_tables y si está creciendo rápidamente, necesitará aumentar su tamaño.

Nota:

Los 2 parámetros anteriores pueden requerir una gran cantidad de archivos abiertos. 20 + max_connections + table_cache * 2 es una buena estimación de lo que necesita. MySQL en Linux tiene una opción open_file_limit, establezca este límite.

Si tiene consultas complejas, sort_buffer_size y tmp_table_size son muy importantes. Los valores dependerán de la complejidad de la consulta y los recursos disponibles, pero 4Mb y 32Mb, respectivamente, son puntos de partida recomendados.

Nota: Estos son valores "por conexión", entre read_buffer_size, read_rnd_buffer_size y algunos otros, lo que significa que este valor puede ser necesario para cada conexión. Por lo tanto, considere su carga y el recurso disponible al establecer estos parámetros. Por ejemplo, sort_buffer_size se asigna solo si MySQL necesita hacer una ordenación. Nota: tenga cuidado de no quedarse sin memoria.

Si tiene muchos enlaces establecidos (es decir, un sitio web sin conexiones persistentes), puede mejorar el rendimiento configurando thread_cache_size en un valor distinto de cero. 16 es un buen valor para empezar. Aumenta el valor hasta que tus threads_created no crezcan muy rápido.

CLAVE PRIMARIA:

Solo puede haber una columna AUTO_INCREMENT por tabla, debe estar indexada y no puede tener un valor DEFAULT

KEY es normalmente un sinónimo de INDEX. El atributo clave PRIMARY KEY también se puede especificar como solo KEY cuando se proporciona en una definición de columna. Esto se implementó para compatibilidad con otros sistemas de bases de datos.

Una PRIMARY KEY es un índice único donde todas las columnas clave deben definirse como NOT NULL

Si un índice PRIMARY KEY o UNIQUE consta de una sola columna que tiene un tipo entero, también puede referirse a la columna como "_rowid" en las instrucciones SELECT.

En MySQL, el nombre de una PRIMARY KEY es PRIMARY

Actualmente, solo las tablas InnoDB (v5.1?) Admiten claves foráneas.

Generalmente, usted crea todos los índices que necesita cuando está creando tablas. Cualquier columna declarada como PRIMARY KEY, KEY, UNIQUE o INDEX se indexará.

NULL significa "no tener un valor". Para probar NULL, no puede usar los operadores de comparación aritmética como =, <o <>. Use los operadores IS NULL y IS NOT NULL en su lugar:

NO_AUTO_VALUE_ON_ZERO suprime el incremento automático para 0 de modo que solo NULL genere el siguiente número de secuencia. Este modo puede ser útil si 0 ha sido almacenado en la columna AUTO_INCREMENT de una tabla. (Por cierto, el almacenamiento 0 no es una práctica recomendada).

Para cambiar el valor del contador AUTO_INCREMENT que se utilizará para nuevas filas:

ALTER TABLE mytable AUTO_INCREMENT = value; 

o SET INSERT_ID = valor;

A menos que se especifique lo contrario, el valor comenzará con: 1000000 o lo especificará de la siguiente manera:

...) ENGINE = MyISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT = 1

TIMESTAMPS:

Los valores para las columnas TIMESTAMP se convierten de la zona horaria actual a UTC para el almacenamiento, y de UTC a la zona horaria actual para la recuperación.

http://dev.mysql.com/doc/refman/5.1/en/timestamp.html Para una columna TIMESTAMP en una tabla, puede asignar la marca de tiempo actual como el valor predeterminado y el valor de actualización automática.

Una cosa que debe tenerse en cuenta al usar uno de estos tipos en una cláusula WHERE, es mejor hacer WHERE datecolumn = FROM_UNIXTIME (1057941242) y NO DONDE UNIX_TIMESTAMP (datecolumn) = 1057941242. haciendo esto último no se aprovechará de un índice en esa columna.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

 UNIX_TIMESTAMP() 
 FROM_UNIXTIME() 
 UTC_DATE()
 UTC_TIME()
 UTC_TIMESTAMP()

si convierte un datetime a unix timestamp en MySQL:
Y luego agregue 24 horas para ello:
Y luego convertirlo de nuevo a una fecha y hora ¡mágicamente pierde una hora!

Esto es lo que está pasando. Al convertir la marca de tiempo de Unix a una fecha y hora, se toma en cuenta la zona horaria y ocurre que entre el 28 y el 29 de octubre de 2006, pasamos el horario de verano y perdimos una hora.

A partir de MySQL 4.1.3, las funciones CURRENT_TIMESTAMP (), CURRENT_TIME (), CURRENT_DATE () y FROM_UNIXTIME () devuelven valores en la zona horaria actual de la conexión, que está disponible como el valor de la variable del sistema time_zone. Además, UNIX_TIMESTAMP () asume que su argumento es un valor de fecha y hora en la zona horaria actual.

La configuración actual de la zona horaria no afecta los valores mostrados por funciones tales como UTC_TIMESTAMP () o valores en las columnas DATE, TIME o DATETIME.

NOTA: SOLO EN LA ACTUALIZACIÓN actualiza el DateTime si se cambia un campo. Si una ACTUALIZACIÓN no da como resultado que se cambien campos, ¡entonces el DateTime NO se actualiza!

Además, el primer TIMESTAMP siempre es AUTOUPDATE por defecto, incluso si no se especifica

Cuando trabajo con Dates, casi siempre me dirijo a Julian Date porque las matemáticas son una simple cuestión de sumar o restar números enteros, y segundos desde la medianoche por la misma razón. Es raro que necesite un tiempo de resoultion de granularidad más fina que segundos.

Ambos pueden almacenarse como un entero de 4 bytes, y si el espacio es muy ajustado se puede combinar en tiempo UNIX (segundos desde la época 1/1/1970) como un entero sin signo que será bueno hasta alrededor de 2106 como:

''segundos en 24 horas = 86400

''Número máximo con signo firmado val = 2,147,483,647 - puede contener 68 años de segundos

''Valor máximo de enteros sin signo = 4,294,967,295 - puede contener 136 años de segundos

Protocolo binario:

MySQL 4.1 introdujo un protocolo binario que permite enviar y devolver valores de datos que no sean de cadena en formato nativo sin conversión hacia y desde el formato de cadena. (Muy útil)

Además, mysql_real_query () es más rápido que mysql_query () porque no llama a strlen () para operar en la cadena de instrucciones.

http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html El protocolo binario admite declaraciones preparadas del lado del servidor y permite la transmisión de valores de datos en formato nativo. El protocolo binario se sometió a una gran revisión durante las versiones anteriores de MySQL 4.1.

Puede usar la macro IS_NUM () para probar si un campo tiene un tipo numérico. Pase el valor de tipo a IS_NUM () y se evalúa como VERDADERO si el campo es numérico:

Una cosa a tener en cuenta es que los datos binarios PUEDEN enviarse dentro de una consulta normal si usted escapa y recuerda que MySQL solo requiere que se escape esa barra invertida y el carácter de comillas. Así que esa es una manera realmente fácil de INSERTAR cadenas binarias más cortas como contraseñas encriptadas / saladas, por ejemplo.

Servidor principal:

http://www.experts-exchange.com/Database/MySQL/Q_22967482.html

http://www.databasejournal.com/features/mysql/article.php/10897_3355201_2

GRANT REPLICATION SLAVE ON . a slave_user IDENTIFICADO POR ''slave_password''

#Master Binary Logging Config  STATEMENT causes replication 
              to be statement-based -  default

log-bin=Mike
binlog-format=STATEMENT
server-id=1            
max_binlog_size = 10M
expire_logs_days = 120    


#Slave Config
master-host=master-hostname
master-user=slave-user
master-password=slave-password
server-id=2

El archivo de registro binario debe leer:

http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

http://www.mydigitallife.info/2007/10/06/how-to-read-mysql-binary-log-files-binlog-with-mysqlbinlog/

http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog.html

http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html

Puede eliminar todos los archivos de registro binarios con la instrucción RESET MASTER, o un subconjunto de ellos con PURGE MASTER

--result-file = binlog.txt TrustedFriend-bin.000030

Normalización:

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

Funciones UDF

http://www.koders.com/cpp/fid10666379322B54AD41AEB0E4100D87C8CDDF1D8C.aspx

http://souptonuts.sourceforge.net/readme_mysql.htm

Tipos de datos:

http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html

http://www.informit.com/articles/article.aspx?p=1238838&seqNum=2

http://bitfilm.net/2008/03/24/saving-bytes-efficient-data-storage-mysql-part-1/

Una cosa a tener en cuenta es que en una tabla mixta con CHAR y VARCHAR, mySQL cambiará los CHAR a VARCHAR

RecNum integer_type UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (RecNum)

MySQL siempre representa las fechas con el año primero, de acuerdo con las especificaciones estándar SQL e ISO 8601

Misc:

Desactivar algunas funciones de MySQL dará como resultado archivos de datos más pequeños y un acceso más rápido. Por ejemplo:

--datadir especificará el directorio de datos y

--skip-innodb desactivará la opción inno y te ahorrará 10-20M

Más aquí http://dev.mysql.com/tech-resources/articles/mysql-c-api.html

Descargar el Capítulo 7 - Gratis

InnoDB es transaccional, pero hay una sobrecarga de rendimiento que viene con eso. He encontrado que las tablas MyISAM son suficientes para el 90% de mis proyectos. Las tablas no transaccionales (MyISAM) tienen varias ventajas propias, todas las cuales ocurren porque:

no hay gastos generales de transacción:

Mucho mas rápido

Menor espacio de disco requerido

Se requiere menos memoria para realizar actualizaciones

Cada tabla MyISAM se almacena en el disco en tres archivos. Los archivos tienen nombres que comienzan con el nombre de la tabla y tienen una extensión para indicar el tipo de archivo. Un archivo .frm almacena el formato de tabla. El archivo de datos tiene una extensión .MYD (MYData). El archivo de índice tiene una extensión .MYI (MYIndex).

Estos archivos se pueden copiar a una ubicación de almacenamiento intacta sin utilizar la función de copia de seguridad de administradores de MySQL, que consume mucho tiempo (también lo es la restauración)

El truco es hacer una copia de estos archivos y luego DEJAR caer la mesa. Cuando vuelva a guardar los archivos MySQl los reconocerá y actualizará el seguimiento de la tabla.

Si debe hacer una copia de seguridad / restaurar,

Restaurar una copia de seguridad o importar desde un archivo de volcado existente puede llevar mucho tiempo dependiendo de la cantidad de índices y claves principales que tenga en cada tabla. Puede acelerar este proceso drásticamente modificando su archivo de volcado original rodeándolo con lo siguiente:

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

.. your dump file ..

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;

Para aumentar enormemente la velocidad de la recarga, agregue el comando SQL SET AUTOCOMMIT = 0; al comienzo del archivo de volcado, y agregue el COMPROMISO; comando hasta el final

De forma predeterminada, el autocommit está activado, lo que significa que todos y cada uno de los comandos de inserción en el archivo de volcado se tratarán como una transacción separada y se escribirán en el disco antes de que se inicie el siguiente. Si no agrega estos comandos, volver a cargar una gran base de datos en InnoDB puede llevar muchas horas ...

El tamaño máximo de una fila en una tabla MySQL es 65.535 bytes

La longitud máxima efectiva de un VARCHAR en MySQL 5.0.3 y on = tamaño máximo de fila (65.535 bytes)

Los valores de VARCHAR no se rellenan cuando se almacenan. Los espacios finales se conservan cuando los valores se almacenan y recuperan, de conformidad con el estándar SQL.

Los valores CHAR y VARCHAR en MySQL se comparan sin tener en cuenta los espacios finales.

Usar CHAR solo acelerará su acceso si el registro completo es de tamaño fijo. Es decir, si usa cualquier objeto de tamaño variable, también puede hacer que todos ellos tengan un tamaño variable. No gana velocidad usando un CHAR en una tabla que también contiene un VARCHAR.

El límite VARCHAR de 255 caracteres se elevó a 65535 caracteres a partir de MySQL 5.0.3

Las búsquedas de texto completo solo son compatibles con tablas MyISAM.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Las columnas BLOB no tienen ningún juego de caracteres, y la clasificación y comparación se basan en los valores numéricos de los bytes en los valores de columna

Si el modo SQL estricto no está habilitado y asigna un valor a una columna BLOB o TEXT que exceda la longitud máxima de la columna, el valor se trunca para que se ajuste y se genera una advertencia.

Comandos útiles:

verifique el modo estricto: SELECCIONE @@ global.sql_mode;

apague el modo estricto:

SET @@ global.sql_mode = '''';

SET @@ global.sql_mode = ''MYSQL40''

o eliminar: sql-mode = "STRICT_TRANS_TABLES, ...

MOSTRAR COLUMNAS DE mytable

SELECCIONAR max (nombre de pila) AS virtualcolumn FROM mytable ORDER BY virtualcolumn

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html

http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id last_insert_id ()

obtiene el PK de la última fila insertada en el máximo de hilo actual (pkcolname) obtiene el último PK general.

Nota: si la tabla está vacía, max (pkcolname) devuelve 1 mysql_insert_id () convierte el tipo de devolución de la función MySQL C API mysql_insert_id () a un tipo de longitud (denominada int en PHP).

Si su columna AUTO_INCREMENT tiene un tipo de columna de BIGINT, el valor devuelto por mysql_insert_id () será incorrecto. En su lugar, utilice la función SQL interna de MySQL LAST_INSERT_ID () en una consulta SQL.

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Solo una nota que cuando intenta insertar datos en una tabla y obtiene el error:

Unknown column ‘the first bit of data what you want to put into the table‘ in ‘field list’

usando algo como

INSERT INTO table (this, that) VALUES ($this, $that)

es porque no tienes ningún apóstrofo en torno a los valores que estás intentando incluir en la tabla. Entonces deberías cambiar tu código a:

INSERT INTO table (this, that) VALUES (''$this'', ''$that'') 

recordatorio de que `` se utilizan para definir campos, bases de datos o tablas MySQL, no valores;)

Conexión perdida al servidor durante la consulta:

http://dev.mysql.com/doc/refman/5.1/en/gone-away.html

http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html

http://dev.mysql.com/doc/refman/5.1/en/show-variables.html

http://dev.mysql.com/doc/refman/5.1/en/option-files.html

http://dev.mysql.com/doc/refman/5.1/en/error-log.html

Tuning Queries

http://www.artfulsoftware.com/infotree/queries.php?&bw=1313

Bueno, eso debería ser suficiente para ganar el bono, creo ... Los frutos de muchas horas y muchos proyectos con una gran base de datos gratuita . Desarrollo servidores de datos de aplicaciones en plataformas Windows principalmente con MySQL. El peor desastre que tuve que enderezar fue

La última pesadilla de base de datos heredada de MySQL

Esto requirió una serie de aplicaciones para procesar las tablas en algo útil usando muchos de los trucos mencionados aquí.

Si esto le pareció increíblemente útil, exprese su agradecimiento votando.

También vea mis otros artículos y libros blancos en: www.coastrd.com

Answer 20

mysqlsla: una de las herramientas de análisis de registro de consultas lentas más utilizadas. Puedes ver las 10 principales consultas de worsts desde la última vez que desplegaste registros de consulta lentos. También le puede decir la cantidad de veces que se activó la consulta MALA y cuánto tiempo total tomó en el servidor.