Notes sur l'optimisation MySQL

Tutoriel vidéo mysqlLa colonne présente la méthode d'optimisation de MySQL

Recommandé (gratuit) : Tutoriel vidéo MySQL

Enregistrez des notes d'étude et continuez à mettre à jour.

Direction de l'optimisation

Optimisation SQL

1. Analyse de l'optimisation SQL
2. Optimisation de l'index

Optimiser les objets de base de données

1. Optimiser les types de données de table
2. Répartition de table (horizontale, verticale)
3. Anti- paradigme
4. Utiliser la table intermédiaire

Optimiser le serveur MySQL

1. Optimisation de la gestion de la mémoire MySQL
2. Mécanisme de journalisation Et optimisation
3. Ajuster les paramètres de concurrence MySQL

Optimisation des applications

1. Pool de connexions à la base de données
2. Utiliser la réduction du cache Pression
3. Équilibrage de charge pour établir un cluster
4. Synchronisation maître-maître, réplication maître-esclave

Analyse et positionnement des problèmes d'optimisation MySQL

Analyser la fréquence d'exécution de SQL

show status

例如：分析读为主，还是写为主

Localiser SQL avec une faible efficacité d'exécution

慢查询日志定位-log-slow-queries = xxx（指定文件名）SHOW PROCESSLIST查看当前正在进行的线程，包括线程状态、是否锁表

Analyser le plan d'exécution de SQL

explain "your sql"desc "your sql"- 部分参数分析
select_type: SIMPLE 简单表，不使用表连接或子查询PRIMARY 主查询，即外层的查询UNION SUBQUER 子查询的第一个select

type: ALL 全表扫描
index 索引全扫描
range 索引范围扫描
ref 使用非唯一索引或唯一索引的前缀扫描
eq_ref 类似ref，使用的索引是唯一索引const/system 单表中最多有一个匹配行NULL 不用访问表或者索引，直接得到结果

afficher l'analyse de profil SQL

select @@have_profiling 是否支持
select @@profiling 是否开启

执行 "your sql"show profiles 
show profile block io for QUERY 17

Optimisation de l'index

Classification de stockage de l'index

B-TREE索引：常见，大部分都支持HASH索引：只有memory引擎支持R-TREE索引：空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型
full-text索引：全文索引，MyISAM的一个特殊索引类型，innodb从5.6开始支持

Création et suppression d'index

添加索引ALTER Table `table_name` ADD PRIMARY KEY（`column`）ALTER Table `table_name` ADD UNIQUE（`column`）ALTER Table `table_name` ADD INDEX（`column`）ALTER Table `table_name` ADD FULLTEXT（`column`）

删除ALTER Table `table_name` drop index index_name

Situations où les index peuvent être utilisés dans Mysql

匹配全值
匹配值范围查询
匹配最左前缀
仅仅对索引进行查询（覆盖查询）
匹配列前缀 （添加前缀索引）
部分精确+部分范围

Scénarios où les index ne peuvent pas être utilisés

以%开关的like查询
数据类型出现隐式转换
复合索引查询条件不包含最左部分
使用索引仍比全表扫描慢
用or分割开的条件

Optimisation des instructions MySQL

Optimiser la table régulièrement

optimize table table_name 合并表空间碎片，对MyISAM、BDB、INNODB有效

如果提示不支持，可以用 mysql --skip-new 或者 mysql --safe-mode 来重启，以便让其他引擎支持

Optimisation commune

尽量避免全表扫描，对where及orderby的列建立索引
尽量避免where使用 != 或 <>尽量避免where子句用 or 连接条件
乱用%导致全表扫描
尽量避免where子句对字段进行表达式操作
尽量避免where子句对字段进行函数操作
覆盖查询，返回需要的字段
优化嵌套查询，关联查询优于子查询
组合索引或复合索引，最左索引原则
用exist代替in当索引列有大量重复数据时，SQL查询可能不会去利用索引

Optimiser les objets de base de données

Optimiser les types de données de table

PROCEDURE ANALYSE (16,256) 排除多于16个，大于256字节的ENUM建议"your sql" PROCEDURE ANALYSE ()

Répartition des tables

垂直拆分
针对某些列常用、不常用

水平拆分
表很大
表中的数据有独立性，能简单分类
需要在表存放多种介质

Dénormalisation

增加冗余列、增加派生列、重新组表和分割表

Utiliser la table intermédiaire

数据查询量大
数据统计、分析场景

Comparaison du moteur MySQL

De quel moteur dispose MySQL ?

À propos des commandes du moteur de table

show engines; 查看myql所支持的存储引擎
show variables like '%storage_engine'; 查看mysql默认的存储引擎
show create table table_name 查看具体表使用的存储引擎

À propos d'innodb

1. 提供事务、回滚、系统奔溃修复能力、多版本并发控制事务2. 支持自增列3. 支持外键4. 支持事务以及事务相关联功能5. 支持mvcc的行级锁

À propos de MyISAM

1. 不支持事务、不支持行级锁，只支持并发插入的表锁，主要用于高负载的select2. 支持三种不同的存储结构：静态、动态、压缩

Ajuster les paramètres pour optimiser le service d'arrière-plan MySQL

Optimisation de la mémoire MyISAM

#修改相应服务器位置的配置文件 my.cnf

key_buffer_size
决定myisam索引块缓存区的大小，直接影响表的存取效率，建议1/4可用内存

read_buffer 读缓存

write_buffer 写缓存

Optimisation de la mémoire InnoDB

innodb_buffer_pool_size 存储引擎表数据和索引数据的最大缓存区大小

innodb_old_blocks_pct LRU算法 决定old sublist的比例

innodb_old_blocks_time LRU算法 数据转移间隔时间

Paramètres de concurrence MySQL

max_connections 最大连接数，默认151back_log 短时间内处理大量连接，可适当增大

table_open_cache 控制所有SQL执行线程可打开表缓存的数量，受其他参数制约

thread_cache_size 控制缓存客户服务线程数量，加快数据库连接速度，根据threads_created/connections来衡量是否合适

innodb_lock_wait_timeout 控制事务等待行锁时间，默认50ms

Introduction à l'optimisation des applications Mysql

Pourquoi l'optimisation des applications est nécessaire

• L'importance des données
• Le service MySQL et ses propres goulots d'étranglement en termes de performances
• Assurer un fonctionnement stable et fiable des grands systèmes

Appliquer des méthodes d'optimisation

1. Utiliser le pool de connexions

2. Réduire les connexions réelles à MySQL
   a. Évitez l'exécution répétée des mêmes données (cache de requêtes)
   b. Utilisez le cache mysql (cache sql)

3. Équilibrage de charge
   a. . Séparation en lecture et en écriture (la réplication maître-maître et la réplication maître-esclave assurent la cohérence des données)
   

Pool de connexions à la base de données

Sauvegarde maître-esclave et séparation lecture-écriture

Sauvegarde maître-maître

Équilibrage de charge

Recommandations d'apprentissage gratuites associées : programmation php(vidéo)

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!