English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية

Algunas prácticas de optimización de bases de datos MySQL en modo mono

Experience Notes5Database optimization has many things to talk about, according to the amount of data supported, it can be divided into two stages: single-machine database and sharding, the former can generally support1000W or, the database optimization has many things to talk about, according to the amount of data supported, it can be divided into two stages: single-machine database and sharding, the former can generally support

1、Table Structure Optimization

When starting an application, the design of the database table structure often affects the performance of the application later, especially the performance after the user volume increases. Therefore, table structure optimization is an important step.

1.1、Character Set

Generally speaking, try to choose UTF-8Although GBK is better than UTF in storing noon,-8uses less storage space, but UTF-8Compatible with various languages, in fact, we do not need to sacrifice scalability for this little storage space. In fact, if you need to convert from GBK to UTF later-8The cost is very high, and data migration is required, while the storage space can be completely solved by spending money to expand the hard disk.

1.2、Primary Key

When using mysql's innodb, the underlying storage model of innodb is B+The tree, which uses the primary key as a clustered index, uses the inserted data as leaf nodes, and can quickly find leaf nodes through the primary key, thus quickly obtaining records. Therefore, it is necessary to add a primary key when designing the table, and it is better to be auto-incremented. Because auto-incremented primary keys can let the inserted data be inserted into the underlying B in order of the primary key+In the leaf nodes of the tree, since they are ordered, this insertion almost does not require moving existing other data, so the insertion efficiency is very high. If the primary key is not auto-incremented, then each time the value of the primary key is approximately random, and it may be necessary to move a large amount of data to ensure B+The characteristics of the tree, which increase unnecessary costs.

1.3、campos

1.3.1、los campos que han sido indexados deben agregar la restricción not null y establecer el valor por defecto

1.3.2、no se recomienda usar float、double para almacenar decimales, para evitar la pérdida de precisión, se recomienda usar decimal

1.3.3、no se recomienda usar Text/blob para almacenar datos en grandes cantidades, porque la lectura y escritura de grandes textos causará un gran I/O overhead, al mismo tiempo, ocupa el caché de mysql, bajo alta concurrencia, reducirá enormemente la capacidad de transmisión de la base de datos, se recomienda almacenar datos de gran texto en sistemas de almacenamiento de archivos especializados, mysql solo debe almacenar la dirección de acceso a este archivo, por ejemplo, los artículos de blog se pueden almacenar en archivos, mysql solo debe almacenar la dirección relativa del archivo.

1.3.4、la longitud del tipo varchar no debe superar8K。

1.3.5、se recomienda usar Datetime para tipos de tiempo, no usar timestamp, aunque Datetime ocupe8bytes,mientras que timestamp solo ocupa4bytes,pero el último debe garantizar que no esté vacío y que sea sensible a la zona horaria.

1.3.6、se recomienda agregar los campos gmt_create y gmt_modified a la tabla, para registrar el tiempo de creación y modificación de los datos. La razón para establecer estos campos es facilitar la búsqueda de problemas.

1.4、creación de índice

1.4.1、en esta etapa, debido a que no se conoce bien el negocio, se debe evitar agregar índices ciegamente, solo agregar índices normales a campos que se usarán inevitablemente.

1.4.2、la longitud del índice de columna única innodb no debe superar767bytes,si supera, se usará el255bytes como prefijo de índice

1.4.3、la longitud del índice de combinación innodb de las columnas no debe superar767bytes,en total, no debe superar3072bytes

2、optimización de SQL

En general, SQL tiene varios tipos: básico de aumento, disminución, modificación y búsqueda, consulta de paginación, consulta de rango, búsqueda borrosa, conexión de múltiples tablas

2.1、consulta básica

generalmente la consulta necesita seguir el índice,si no hay índice, se recomienda modificar la consulta, agregar el campo con índice, si debido a la escena de negocio no se puede usar este campo, entonces se necesita ver el volumen de la consulta de llamada, si es grande, por ejemplo, si se llama10W+,esto requiere la adición de un índice nuevo,si no es muy grande,por ejemplo, si se llama100+,en ese caso, puede considerar mantenerla como está. Además, select * usarlo lo menos posible,usar los campos necesarios en la sentencia SQL,no buscar campos innecesarios,no desperdiciar I/O y espacio en memoria.

2.2、págination eficiente

limit m,n en realidad es ejecutar primero limit m+n,luego toma n filas desde la línea m,de modo que a medida que la paginación con limit se hace más avanzada,m se vuelve mayor y el rendimiento se vuelve más bajo. Por ejemplo

select * from A limit 100000,10,este tipo de sentencia SQL tiene muy mal rendimiento,se recomienda cambiar a la versión siguiente:

selec id,name,age from A where id >=(select id from A limit 100000,1) limit 10

2.3、consulta de rango

La consulta de rango incluye between, mayor que, menor que y in. La consulta in de mysql tiene una limitación en la cantidad, si la cantidad es pequeña puede seguir el índice, si es grande, se convierte en una búsqueda de tabla completa. Y entre, mayor que, menor que, estas consultas no seguirán el índice, por lo que es mejor ponerlas después de las condiciones de consulta que siguen el índice.

2.4、búsqueda borrosa like

El uso de la sentencia como like %name% no seguirá el índice, equivalente a una búsqueda de tabla completa, cuando la cantidad de datos es pequeña no habrá problemas muy grandes, pero cuando la cantidad de datos es grande, el rendimiento descenderá mucho, se recomienda usar un motor de búsqueda para reemplazar esta búsqueda borrosa después de que la cantidad de datos sea grande, o al menos agregar una condición que pueda seguir el índice antes de la búsqueda borrosa.

2.5、conexión de múltiples tablas

Consultas anidadas y join pueden realizarse para obtener datos entre múltiples tablas, pero las consultas anidadas tienen un rendimiento relativamente malo, se recomienda cambiar las consultas anidadas a join. Para el join de mysql, utiliza el algoritmo Nested Loop Join, es decir, mediante el conjunto de resultados de la tabla anterior se realiza una búsqueda en la tabla posterior, por ejemplo, el conjunto de resultados de la tabla anterior es100 datos, la siguiente tabla tiene10Para los datos W, se necesita en100*10Para obtener el conjunto de resultados final, se filtra desde la colección de datos W. Por lo tanto, es mejor usar tablas con conjuntos de resultados pequeños para hacer join con tablas grandes, y establecer índices en los campos de join. Si no se puede establecer un índice, se necesita configurar un tamaño de buffer de join suficientemente grande. Si todas estas técnicas no pueden resolver el problema de descenso en el rendimiento que trae el join, mejor no usar join, y dividir una consulta join en dos consultas simples. Además, es mejor no conectar más de tres tablas, ya que generalmente el rendimiento será muy malo, se recomienda dividir la sql.

3、optimización del pool de conexiones de base de datos

El pool de conexiones de base de datos es esencialmente un caché, es un medio para resistir altas concurrencias. La optimización del pool de conexiones de base de datos se realiza principalmente en los parámetros, generalmente usamos el pool de conexiones DBCP, sus parámetros específicos son los siguientes:

3.1  initialSize

El número inicial de enlaces, aquí el inicial se refiere a la primera vez que se llama getConnection, no al momento de inicio de la aplicación. El valor inicial puede configurarse como el promedio histórico de la cantidad de concurrencia

3.2、minIdle

El número mínimo de enlaces libres mantenidos. DBCP iniciará un hilo en segundo plano para reciclar enlaces libres, cuando este hilo realice el reciclaje de enlaces libres, mantendrá una cantidad de enlaces de minIdle. Generalmente se configura como5,la cantidad de concurrencia realmente muy pequeña puede configurarse como1.

3.3、maxIdle

El número máximo de enlaces libres mantenidos, configurado según el pico de concurrencia del negocio. Por ejemplo, si el pico de concurrencia es20, entonces después de que el pico se haya pasado, estos enlaces no se reciclarán de inmediato, si pasan un poco de tiempo y vuelve un pico, entonces la piscina de enlaces puede reutilizar estos enlaces libres sin necesidad de crear y cerrar enlaces con frecuencia.

3.4、maxActive

Maximum active connection count, set according to the acceptable concurrency extreme value. For example, the acceptable extreme value of single-machine concurrency is100, then this maxActive setting should be100 after, can only be served simultaneously for100 requests for service, extra requests will be discarded after the maximum waiting time. This value must be set, which can prevent malicious concurrent attacks and protect the database.

3.5、maxWait

The maximum waiting time to obtain a connection, it is recommended to set it shorter, such as3s, so that the request can fail quickly, because when a request is waiting to obtain a connection, the thread cannot be released, and the thread concurrency of a single machine is limited. If this time is set too long, such as the recommendation on the Internet60s, then this thread in this60s cannot be released, as soon as this kind of request becomes frequent, the available threads of the application will be fewer, and the service will become unavailable.

3.6、minEvictableIdleTimeMillis

The time for the connection to remain idle without being recycled, default30 minutes.

3.7、validationQuery

Used to check whether the connection is valid, generally a simple SQL statement, it is recommended to set

3.8、testOnBorrow

Check the connection when applying for it, it is not recommended to enable it as it will seriously affect performance

3.9、testOnReturn

Check the connection when returning it, it is not recommended to enable it as it will seriously affect performance

3.10、testWhileIdle

After enabling it, the background thread that cleans up connections will periodically perform validateObject on idle connections. If the connection fails, it will be cleared, which will not affect performance and is recommended to be enabled.

3.11、numTestsPerEvictionRun

Represents the number of checks per link check, it is recommended to set it to be the same as maxActive, so that all links can be effectively checked each time.

3.12、预热连接池

For connection pools, it is recommended to preheat the application when it starts, perform simple SQL queries before providing external access, so that the connection pool is filled with the necessary number of connections.

4、索引优化

After the data volume reaches a certain level, performance improvement through SQL optimization is no longer possible. At this point, it is necessary to use the big move: indexing. There are three levels of indexing, generally speaking, mastering these three levels is sufficient. In addition, the selectivity of the fields on which indexes are established should be considered.

4.1、一级索引

An index should be created on the conditions following 'where', a single column can be established as a general index, while multiple columns should be established as a composite index. The principle of the leftmost prefix should be noted for composite indexes.

4.2、二级索引

If a field used in 'order by' or 'group by' is present, it is advisable to create an index on this field. This is because the index is naturally ordered, which can avoid the sorting brought by 'order by' and 'group by', thereby improving performance.

4.3、índice de tres niveles

Si las dos técnicas anteriores no funcionan, entonces también agregue el campo consultado al índice, en este momento se forma lo que se llama cobertura de índice, lo que puede reducir una vez I/Operación O, porque mysql primero consulta el índice de clave principal al buscar datos, luego consulta el índice común según el índice de clave principal, luego consulta el registro correspondiente según el índice común. Si todos los registros necesarios están en el índice común, no es necesario el tercer paso. Por supuesto, este método de creación de índices es bastante extremo y no es adecuado para escenarios generales.

4.4、selectividad del índice

Al crear índices, intenta crearlos en campos con alta selectividad. ¿Qué es la selectividad alta? La selectividad alta significa que la cantidad de datos devueltos por este campo es baja, por ejemplo, buscar información de una persona por nombre, la cantidad de datos devueltos generalmente será baja, mientras que buscar por género puede devolver la mitad de los datos de la base de datos, por lo que el nombre es un campo con alta selectividad, mientras que el género es un campo con baja selectividad.

5、archivo de datos históricos

Cuando la cantidad de datos增加到一年5Cuando el índice llega a 00W, la indexación también es inútil, en este caso, el pensamiento general es considerar la división de bases de datos y tablas. Si el negocio no tiene un crecimiento explosivo, pero los datos están aumentando lentamente, no es necesario considerar la técnica compleja de división de bases de datos y tablas, sino que se realiza el archivo de datos históricos. Nosotros nos enfocamos en los datos históricos que han finalizado su ciclo de vida, como6Meses de datos anteriores, para el archivo. Podemos usar tareas programadas de quartz para programar la-archive en la madrugada.6Meses de datos anteriores se han encontrado y se han almacenado en el servidor hbase remoto. Por supuesto, también necesitamos proporcionar una interfaz de consulta de datos históricos para estar preparados para emergencias.

Aquí está la recopilación de información de optimización de la base de datos de servidor mysql mono, continuaremos complementando información relevante, gracias por el apoyo de todos a este sitio!

Declaración: El contenido de este artículo se ha obtenido de la red, es propiedad del autor original, el contenido se ha contribuido y subido por los usuarios de Internet, este sitio no posee los derechos de propiedad, no se ha editado manualmente y no asume la responsabilidad de las responsabilidades legales relacionadas. Si encuentra contenido sospechoso de infracción de derechos de autor, le invitamos a enviar un correo electrónico a: notice#oldtoolbag.com (al enviar un correo electrónico, reemplace # con @) para denunciar, y proporcione evidencia relevante. Una vez confirmado, este sitio eliminará inmediatamente el contenido sospechoso de infracción.

Te gustará