mysql单机数据库优化的一些实践

Home Page5Experience Notes1000W 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-8, although GBK is better than UTF in storing noon,-8uses less storage space, but UTF-8Compatible with various languages, in fact, we do not have 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 the leaf nodes through the primary key, thus quickly obtaining the records. Therefore, when designing the table, it is necessary to add a primary key, 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 the value of the primary key is approximately random each time, at this time, 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、字段

1.3.1、建了索引的字段必须加上not null约束，并且设置default值

1.3.2、不建议使用float、double来存小数，防止精度损失，建议使用decimal

1.3.3、不建议使用Text/blob来保存大量数据，因为对大文本的读写会造成比较大的I/O开销，同时占用mysql的缓存，高并发下会极大的降低数据库的吞吐量，建议将大文本数据保存在专门的文件存储系统中，mysql中只保存这个文件的访问地址，比如博客文章可以保存在文件中，mysql中只保存文件的相对地址。

1.3.4、varchar类型长度建议不要超过8K。

1.3.5、时间类型建议使用Datetime，不要使用timestamp，虽然Datetime占用8个字节，而timestamp只占用4个字节，但是后者要保证非空，而且后者是对时区敏感的。

1.3.6、建议表中增加gmt_create和gmt_modified两个字段，用来记录数据创建的修改时间。这两个字段建立的原因是方便查问题。

1.4、索引创建

1.4.1、这个阶段由于对业务并不了解，所以尽量不要盲目加索引，只为一些一定会用到索引的字段加普通索引。

1.4.2、创建innodb单列索引的长度不要超过767bytes，如果超过会用前255bytes作为前缀索引

1.4.3、创建innodb组合索引的各列索引长度不要超过767bytes，一共加起来不要超过3072bytes

2、SQL优化

一般来说sql就那么几种：基本的增删改查，分页查询，范围查询，模糊搜索，多表连接

2.1、基本查询

一般查询需要走索引，如果没有索引建议修改查询，把有索引的那个字段加上，如果由于业务场景没法使用这个字段，那么需要看这个查询调用量大不大，如果大，比如每天调用10W+，这就需要新增索引，如果不大，比如每天调用100+，则可以考虑保持原样。另外，select * 尽量少用，用到什么字段就在sql语句中加什么，不必要的字段就别查了，浪费I/O et l'espace mémoire.

2.2、高效分页

limit m,n其实质就是先执行limit m+n，然后从第m行取n行，这样当limit翻页越往后翻m越大，性能越低。比如

select * from A limit 100000,10，这种sql语句的性能是很差的，建议改成下面的版本:

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

2.3、recherche par plage

La recherche par plage inclut between, supérieur, inférieur et in. La condition de la requête in de MySQL a une limitation sur le nombre, si le nombre est petit, elle peut passer par l'index, si le nombre est grand, elle deviendra une analyse complète de la table. Quant à between, supérieur, inférieur, etc., ces requêtes ne passent pas par l'index, donc il est préférable de les placer après les conditions de recherche indexables.

2.4、recherche floue like

L'utilisation de l'expression like %name% ne passe pas par l'index, c'est équivalent à une analyse complète de la table, ce qui ne pose pas de problème lorsque la quantité de données est petite, mais la performance décline considérablement lorsque la quantité de données est grande, il est recommandé d'utiliser un moteur de recherche pour remplacer cette recherche floue, ou d'ajouter une condition indexable avant la recherche floue si cela est impossible.

2.5、joins de plusieurs tables

Les sous-requêtes et les joins peuvent tous être utilisés pour extraire des données entre plusieurs tables, mais les sous-requêtes ont une performance médiocre, il est recommandé de les changer en join. Pour le join de MySQL, il utilise l'algorithme Nested Loop Join, c'est-à-dire qu'il utilise le jeu de résultats de la première table pour rechercher dans la deuxième table, par exemple, le jeu de résultats de la première table est100 données, la table suivante a10Les données W, il faut donc dans100*10Pour filtrer et obtenir le jeu de résultats final. Par conséquent, il est préférable d'utiliser des tables de petits résultats pour joindre des grandes tables, et d'établir des index sur les champs de jointure. Si l'index ne peut pas être créé, il est nécessaire de configurer une taille de join buffer suffisamment grande. Si les techniques ci-dessus ne peuvent pas résoudre le problème de dégradation des performances causé par le join, mieux vaut ne pas utiliser de join, et diviser une requête join en deux requêtes simples. De plus, il est préférable de ne pas joindre plus de trois tables, car généralement, les performances seront très mauvaises, et il est recommandé de décomposer l'expression SQL.

3、optimisation du pool de connexions de base de données

Le pool de connexions de base de données est essentiellement un cache, c'est un moyen de résister aux hautes concomitances. L'optimisation du pool de connexions de base de données consiste principalement à optimiser les paramètres. Nous utilisons généralement le pool de connexions DBCP, et ses paramètres spécifiques sont les suivants:

3.1　 initialSize

Le nombre initial de connexions, ici, l'initial signifie la première fois getConnection, pas au moment du lancement de l'application. La valeur initiale peut être la valeur moyenne historique de la concomitance.

3.2、minIdle

Le nombre minimum de connexions disponibles conservées. DBCP démarre un thread en arrière-plan pour recycler les connexions disponibles. Lorsque ce thread recyclera les connexions disponibles, il conservera un nombre de connexions de minIdle. Généralement, il est configuré à5，la concomitance est vraiment très faible, elle peut être configurée à1.

3.3、maxIdle

Le nombre maximum de connexions disponibles conservées, configuré en fonction des sommets de concomitance de l'activité. Par exemple, si la concomitance est20, alors lorsque le sommet est passé, ces connexions ne seront pas immédiatement recyclées. Si après un court moment, un autre sommet arrive, le pool de connexions peut réutiliser ces connexions disponibles sans avoir à créer et fermer fréquemment des connexions.

3.4maxActive

Maximum active connection number, set according to the acceptable maximum concurrency. For example, the acceptable maximum concurrency for a single machine is100, then this maxActive should be set to100 after, can only be served at the same time for100 requests can be served at the same time. Excessive 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.5maxWait

The maximum waiting time to obtain a connection, it is recommended to set it shorter, for example3s, so that the request can fail quickly because a request cannot be released while waiting to obtain a connection, and the thread concurrency of a single machine is limited. If this time is set too long, for example, as suggested on the Internet,60s, then this thread will be60s cannot be released. If this kind of request is too frequent, the available threads of the application will be less, and the service will become unavailable.

3.6minEvictableIdleTimeMillis

The time the connection remains idle and is not recycled, default30 minutes.

3.7validationQuery

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

3.8testOnBorrow

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

3.9testOnReturn

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

3.10testWhileIdle

After enabling it, the background thread that clears 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.11numTestsPerEvictionRun

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

3.12Preheat connection pool

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.

4Index optimization

After the data volume reaches a certain level, SQL optimization can no longer improve performance. At this point, it is necessary to resort to the big move: indexing. There are three levels of indexes, generally speaking, mastering these three levels is enough. In addition, for the fields where indexes are established, their selectivity needs to be considered.

4.1First-level index

An index is established on the condition after 'where', a single column can be established as a normal index, and multiple columns can be established as a composite index. The principle of the leftmost prefix should be noted for composite indexes.

4.2Second-level index

If a field used in 'order by' or 'group by' is present, consider creating an index on this field. Since indexes are naturally ordered, this can avoid the sorting caused by 'order by' and 'group by', thereby improving performance.

4.3、index de niveau trois

Si les deux précédents ne fonctionnent pas, ajoutez également des index aux champs consultés, ce qui forme ce que l'on appelle le couverture des index, ce qui peut réduire une fois l'I/O opération, car MySQL recherche les données en premier lieu par l'index primaire, puis selon l'index primaire pour rechercher l'index normal, puis selon l'index normal pour rechercher les enregistrements correspondants. Si les enregistrements nécessaires sont tous dans l'index normal, le troisième pas n'est pas nécessaire. Bien sûr, ce type de création d'index est extrême et ne convient pas aux scénarios généraux.

4.4、sélection des index

Lors de la création des index, essayez de les créer sur les champs à haute sélection. Que signifie une haute sélection ? Une haute sélection signifie que le volume de données récupérées via ce champ est faible, par exemple, chercher des informations sur une personne par nom, le volume de données récupérées est généralement faible, tandis que selon le sexe, il pourrait récupérer la moitié des données de la base de données, donc, le nom est un champ à haute sélection, et le sexe est un champ à faible sélection.

5、sélection des index

、archivage des données historiques5Lorsque le volume de données atteint une augmentation annuelle6Les données d'il y a un mois, procéder à l'archivage. Nous pouvons utiliser des tâches planifiées quartz pour planifier l'archivage à minuit.6Les données d'il y a un mois sont récupérées et stockées sur le serveur hbase distant. Bien sûr, nous devons également fournir une interface de consultation des données historiques pour être prêt en cas d'urgence.

Voici la compilation des informations sur l'optimisation de la base de données mysql single-instance, nous continuerons à compléter les informations pertinentes, merci de votre soutien à ce site !

Déclaration : Le contenu de cet article est tiré d'Internet, propriété de l'auteur original, le contenu est apporté par les utilisateurs d'Internet de manière volontaire et téléversé, ce site Web ne possède pas de propriété, n'a pas été traité par l'éditeur humain et n'assume aucune responsabilité juridique. Si vous trouvez du contenu présumé enfreindre les droits d'auteur, veuillez envoyer un e-mail à : notice#oldtoolbag.com (veuillez remplacer # par @ lors de l'envoi d'un e-mail pour signaler une violation, et fournir des preuves pertinentes. Une fois vérifié, ce site supprimera immédiatement le contenu présumé enfreindre les droits d'auteur.)