Explication détaillée de la stratégie d'optimisation de la base de données MySQL en PHP

Cet article décrit brièvement la stratégie d'optimisation MySQL pour la programmation de bases de données PHP. Partagez-le avec tout le monde pour référence, les détails sont les suivants :

J'ai vu un article il y a quelques jours qui disait que dans de nombreux cas, le goulot d'étranglement de PHP n'est pas dans PHP lui-même, mais dans le base de données. Nous savons tous que dans le développement PHP, l'ajout, la suppression, la modification et la vérification des données sont au cœur. Afin d'améliorer l'efficacité opérationnelle de PHP, les programmeurs doivent non seulement écrire du code avec une logique claire et une efficacité élevée, mais doivent également être capables d'optimiser les instructions de requête. Bien que nous ne puissions rien faire contre la vitesse de lecture et d'écriture de la base de données, avec l'aide de certaines extensions de classe de base de données et de serveurs de stockage de données tels que Memcache, Mongodb et Redis, PHP peut également atteindre des vitesses d'accès plus rapides, alors comprenez et apprenez. Ces extensions sont également très nécessaires. Cet article parlera d'abord des stratégies d'optimisation courantes de MySQL.

Quelques conseils MySQL

1. Il est préférable d'écrire les mots-clés dans les instructions SQL en majuscules. Premièrement, il est plus facile de distinguer les mots-clés. et l'objet d'opération. Deuxièmement, lorsque l'instruction SQL est exécutée, MySQL la convertira en majuscules. L'écriture manuelle des majuscules peut augmenter l'efficacité des requêtes (même si elle est très faible).
2. Si nous ajoutons ou supprimons des lignes de données dans la base de données, l'ID de données sera trop grand. Utilisez ALTER TABLE tablename AUTO_INCREMENT=N pour que le nombre d'ID d'incrémentation automatique commence à partir de N .
3. Ajoutez l'attribut ZEROFILL au type int pour remplir automatiquement les données
4. Lors de l'importation d'une grande quantité de données, il est préférable de supprimer d'abord l'index, d'insérer le. données, puis ajoutez l'index. Sinon, mysql passera beaucoup de temps à mettre à jour l'index.
5. Lors de la création d'une base de données et de l'écriture d'instructions SQL, nous pouvons créer un fichier avec le suffixe .sql dans l'EDI reconnaîtra la syntaxe SQL, ce qui facilitera l'écriture. Plus important encore, si votre base de données est perdue, vous pouvez toujours trouver ce fichier et utiliser /path/mysql -uusername -ppassword databasename 7834fc3d76a526ea4dd7e4c593cbd0cf date, time > enum, char > varchar > être converti en type Unsign int est utilisé pour le stockage.

3. Pour le type char(n), essayez de garder la valeur n aussi petite que possible lorsque les données sont complètes.

4. L'utilisation de la commande partition pour partitionner une seule table lors de la création d'une table peut améliorer considérablement l'efficacité des requêtes. MySQL prend en charge les types de partition RANGE, LIST, HASH et KEY. Parmi eux, RANGE est le plus couramment utilisé. La méthode de partitionnement est la suivante :

CREATE TABLE tablename{
}ENGINE innodb/myisam CHARSET utf8 //选择数据库引擎和编码
PARTITION BY RANGE/LIST(column),//按范围和预定义列表进行分区
PARTITION partname VALUES LESS THAN /IN(n),//命名分区并详细限定分区的范围

5. Lors du choix d'un moteur de base de données, vous devez faire attention à la différence entre innodb et myisam.

Structure de stockage : MyISAM est stocké dans trois fichiers sur le disque. Toutes les tables d'InnoDB sont stockées dans le même fichier de données, généralement 2 Go
Prise en charge des transactions : MyISAM ne fournit pas de prise en charge des transactions. InnoDB fournit un support pour les transactions.
Différences de verrouillage de table : MyISAM ne prend en charge que les verrous au niveau de la table. InnoDB prend en charge les transactions et les verrous au niveau des lignes.
Index de texte intégral : MyISAM prend en charge l'index de texte intégral de type FULLTEXT (non applicable au chinois, le moteur d'indexation de texte intégral sphinx doit donc être utilisé). InnoDB ne le prend pas en charge.
Le nombre spécifique de lignes dans le tableau : MyISAM enregistre le nombre total de lignes dans le tableau et l'interrogation de count(*) est très rapide. InnoDB n'enregistre pas le nombre total de lignes dans le tableau et doit être recalculé.
Clés étrangères : non prises en charge par MyISAM. InnoDB prend en charge l'

optimisation de l'index

1. Innodb est un index clusterisé. Il doit y avoir une clé primaire lors du stockage de l'index si elle n'est pas spécifiée. , le moteur générera automatiquement une clé primaire cachée et générera un index primaire. L'adresse physique de la clé primaire est stockée dans l'index. Les données sont stockées en fonction de la clé primaire. Chaque fois que vous utilisez l'index, vous devez d'abord la rechercher. l'index principal, puis recherchez les données sous l'index principal.

L'avantage est que la recherche via la clé primaire est très rapide. L'inconvénient est que l'index secondaire sera plus lent car vous devez d'abord trouver l'index primaire via l'index secondaire (l'index secondaire est l'emplacement de l'index principal.), puis recherchez les données de l'index principal. Et si la clé primaire est irrégulière, davantage de blocs de données doivent être déplacés lors de l'insertion de nouvelles valeurs, ce qui affectera l'efficacité, alors essayez d'utiliser un type int qui augmente régulièrement comme clé primaire. De plus, comme les données sont placées immédiatement après la clé primaire, s'il y a des colonnes (texte/blob) avec une quantité de données particulièrement importante dans les données, InnoDB ignorera de nombreux blocs de données pendant la requête, ce qui entraînera également une lenteur.

2. Chaque index des index myisam est identique et pointe vers l'adresse de chaque ligne du disque. Ce sont tous des données de pointeur légères. L'inconvénient est que chaque index n'est pas établi via la clé primaire et que la requête n'est pas aussi rapide que la recherche de la clé primaire dans l'index clusterisé. Mais comme il stocke l'adresse, l'aspect de la comparaison se déplace et change lors de l'insertion d'une nouvelle valeur.

3. Lors de l'exécution d'une requête multi-conditions, lors de la création d'index séparés pour plusieurs conditions, lors de l'exécution d'une requête SQL, MySQL sélectionnera uniquement l'index le plus proche à utiliser, donc si une requête multi-conditions est requise, un index commun doit être établi, même si cela entraîne une redondance des données.

联合索引的BTREE建立方法：对第一个条件建立索引，在第一个索引的BTREE区域对第二个条件建立索引，以此类推，所以，在使用索引时，不用第一个条件用第二个条件也不会用到联合索引。使用索引时要条件要有顺序，有序列的使用。

4、索引长度对查询也有很大影响，我们应该尽量建立短的索引长度，我们可以使用查询列

SELECT COUNT(DISTINCT LEFT(column)) / COUNT(*) FROM tablename

  来测试对column列建立索引时选取不同的长度，索引的覆盖率有多大，我们选择一下接近饱和的n个长度来建立索引
ALTER TABLE tablename ADD INDEX (column(n));  来对某一列的前n个字符建立索引。若前n个字符相同，我们甚至可以对字符串进行反转存储，然后建立索引。

5、对于经常修改导致的索引碎片的维护方式：ALTER TABLE tablename ENGINE oldengine;即再次应用一下表存储引擎，使其自动维护；也可以用 OPTIMIZE tablename 命令来进行维护。

数据查询方面优化

数据库操作尽量少查询，有查询时尽量不在数据库层面上进行数据操作，而是返回到PHP脚本中操作数据，减轻数据库压力。

一旦发现有数据库性能问题，要及时解决，一般用慢查询日志记录查询很"慢"的语句，用EXPLAIN分析查询和索引使用情况，用PROFILE分析语句执行时的具体资源消耗。

慢查询日志：

1、在my.ini或my.cnf的[mysqld]下添加

slow_query_log_file=/path //设置日志存储路径
long_query_time=n //设置如果语句执行时间达到n秒，就会被记录下来

2、然后在MySQL里设置SET slow_query_log='ON'来开启慢查询。

3、记录下日志后，我们用/bin/目录下的mysqldumpslow filename来查看日志，其常用参数如下:

-g pattern 使用正则表达式
-t n返回前n条数据
-s c/t/l/r 以记录次数/时间/查询时间/返回记录数来排序

EXPLAIN语句

使用方法，在要执行的查询语句前面加EXPLAIN

EXPLAIN SELECT * FROM user;

得到形如下图的结果：

下面是对每一项的解释：

id 查询语句的id，简单查询无意义，多重查询时可以看出执行查询的顺序
select-type 执行的查询语句的类型，对应多重查询，有simple/primary/union等。
tabel 查询语句查询的数据表
type  获得数据的类型 常见的类型效率从高到低为 null>const>eq_ref>ref>range>index>all
possible-keys:可能使用到的索引
key 使用到的索引
key_len索引长度
ref 使用哪个列与索引一起从表中选择。
rows  查找到数据要扫描的大概行数，可看出索引的优劣
extra  常见的有
using filesort 查询到数据后进行文件排序，较慢，需要优化索引
using where 读取整行数据后进行判断过滤，是否符合where条件
using index 索引覆盖，即在牵引中已经有这存储了目标数据，直接读取索引，很快。

PROFILE

用SELECT @@frofiling来查看PROFILE的开启状态。
如果未开启，用SET profiling=1来开启。
开启之后，再执行查询语句，MySQL会自动记录profile信息。
应用show profiles查看所有的sql信息，结果为 Query_ID Duration Query三列结果，分别是查询ID，用时和所用的sql语句。
我们可以使用

SHOW PFROFILE [type[,type]][FOR QUREY Query_ID][Limit rwo_count [OFFSET offset]]

type常见有ALL(全部) BLOCK IO(显示IO相关开销) CPU(CPU开销) MEMORY(内存开销)等

大型存储方面优化

数据库主从复制和读写分离

1、master将改变记录到二进制日志中，slave将master的二进制拷贝到它的中继日志中，重新将数据返回到它自己的数据中，达到复制主服务器数据的目的。

主从复制可以用作：数据库负载均衡、数据库备份、读写分离等功能。

2、配置主服务器master

修改my.ini/my.conf

[mysqld]
log-bin=mysql-bin //启用二进制日志
server-id=102 //服务器唯一ID

3、配置从服务器slave

log-bin=mysql-bin //启用二进制日志
server-id=226 //服务器唯一ID

4、在主服务器上授权从服务器

GRANT REPLICATION SLAVE ON *.* to 'slavename'@'IP' identified by 'root'

5、在从服务器上使用

remplacez master par
master_host="masterip",
master_user="masteruser",
master_password="masterpasswd";

6. La commande slave démarre la réplication maître-esclave.

N'oubliez pas de redémarrer le serveur après chaque modification de configuration. Vous pouvez ensuite utiliser show master/slave status sur les serveurs maître et esclave pour afficher l'état maître/esclave.

La séparation de la lecture et de l'écriture dans la base de données s'appuie sur des middleware MySQL, tels que mysql_proxy, atlas, etc. En configurant ces middlewares pour séparer la lecture et l'écriture entre les serveurs maître et esclave, le serveur esclave assume la responsabilité d'être lu, réduisant ainsi la charge sur le serveur maître.

Partage de base de données

Lorsque la quantité de données dans la table de données de la base de données est très importante, l'indexation et la mise en cache sont soumises à une forte pression . Si la base de données est volumineuse, partitionnez-la afin qu'elle soit stockée sur plusieurs serveurs de base de données ou plusieurs tables afin de réduire la pression des requêtes.

Les méthodes incluent la segmentation verticale, la segmentation horizontale et la segmentation combinée.

Segmentation verticale : Lorsqu'il y a beaucoup de tables de données, les tables qui sont étroitement liées dans la base de données (comme le même module, souvent connecté et interrogé) sont divisées en différents maîtres -esclaves sur le serveur.

Segmentation horizontale : Lorsqu'il n'y a pas beaucoup de tables et que la quantité de données dans la table est très importante, afin d'accélérer la requête, vous pouvez utiliser le hachage et d'autres algorithmes pour diviser une table de données en plusieurs Chacun d'entre eux est placé sur des serveurs différents pour accélérer la requête. La différence entre le partitionnement horizontal et le partitionnement de tables de données réside dans la différence de support de stockage.

Segmentation conjointe : Le plus souvent, le tableau de données et la quantité de données dans le tableau sont très volumineux, une segmentation conjointe est donc nécessaire, c'est-à-dire que la division verticale et horizontale du tableau est effectuée en même temps. La base de données est divisée en une matrice distribuée pour le stockage.

Chacune de ces méthodes d'optimisation de base de données peut être utilisée pour rédiger un article. On peut dire qu'elle est profonde et profonde. Si vous comprenez et mémorisez ces méthodes, vous pouvez effectuer une sélection et une optimisation ciblées lorsque cela est nécessaire pour atteindre l'objectif. meilleurs résultats pour la base de données Efficacité de l'efficacité.

Ensuite, nous résumerons plus en détail les scénarios d'utilisation de base et les méthodes d'utilisation des extensions de classe de base de données PHP couramment utilisées, memcache, redis et mongodb.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!