Einführung in die MySQL-Datenbankoptimierung (Bilder und Text)

本篇文章给大家带来的内容是关于MySQL数据库优化的介绍（图文），有一定的参考价值，有需要的朋友可以参考一下，希望对你有所帮助。

数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.（相关推荐：MySQL教程）

1. 优化一览图

2. 优化

笔者将优化分为了两大类,软优化和硬优化,软优化一般是操作数据库即可,而硬优化则是操作服务器硬件及参数设置.

2.1 软优化

2.1.1 查询语句优化

1.首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.
2.例:

DESC SELECT * FROM `user`

显示:

其中会显示索引和查询数据读取数据条数等信息.

2.1.2 优化子查询

在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.

2.1.3 使用索引

索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者一文,介绍比较详细,此处记录使用索引的三大注意事项:

1. LIKE关键字匹配'%'开头的字符串,不会使用索引.
2. OR关键字的两个字段必须都是用了索引,该查询才会使用索引.
3. 使用多列索引必须满足最左匹配.

2.1.4 分解表

对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

2.1.5 中间表

对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.

2.1.6 增加冗余字段

类似于创建中间表,增加冗余也是为了减少连接查询.

2.1.7 分析表,,检查表,优化表

分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.

分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;

Op:表示执行的操作.
Msg_type:信息类型,有status,info,note,warning,error.
Msg_text:显示信息.

检查表: 使用 CHECK关键字,如CHECK TABLE user [option]

option 只对MyISAM有效,共五个参数值:

QUICK:不扫描行,不检查错误的连接.

FAST:只检查没有正确关闭的表.

CHANGED:只检查上次检查后被更改的表和没被正确关闭的表.

MEDIUM:扫描行,以验证被删除的连接是有效的,也可以计算各行关键字校验和.

EXTENDED:最全面的的检查,对每行关键字全面查找.

优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.

2.2 硬优化

2.2.1 硬件三件套

1、配置多核心和频率高的cpu,多核心可以执行多个线程.

2.配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.

3.配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.

2.2.2 优化数据库参数

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

key_buffer_size:索引缓冲区大小

table_cache:能同时打开表的个数

query_cache_size und query_cache_type: Ersteres ist die Größe des Abfragepuffers, letzteres ist der Schalter des vorherigen Parameters. 0 bedeutet, den Puffer nicht zu verwenden, 1 bedeutet, den Puffer zu verwenden, aber Sie können SQL_NO_CACHE in der Abfrage verwenden, um dies zu bedeuten Den Puffer nicht verwenden, 2 bedeutet, dass der Puffer nur verwendet werden darf, wenn dies in der Abfrage eindeutig angegeben ist, d. h. SQL_CACHE.

sort_buffer_size: Sortierpuffer

Portal:Mehr Parameter

2.2.3 Unterdatenbank und Untertabelle

Da der Datenbankdruck zu hoch ist, besteht das erste Problem darin, dass die Systemleistung in Spitzenzeiten verringert werden kann, weil Eine übermäßige Datenbanklast wirkt sich negativ auf die Leistung aus. Noch eine Frage: Was sollten Sie tun, wenn Ihre Datenbank aufgrund übermäßigen Drucks abstürzt? Zu diesem Zeitpunkt müssen Sie das System also in Datenbanken und Tabellen + Lese-/Schreibtrennung unterteilen, dh eine Datenbank in mehrere Datenbanken aufteilen, diese auf mehreren Datenbankdiensten bereitstellen und dann als Hauptdatenbank für die Verarbeitung von Schreibanforderungen dienen. Anschließend stellt jede Master-Bibliothek mindestens eine Slave-Bibliothek bereit, und die Slave-Bibliothek verarbeitet Leseanforderungen.

2.2.4 Cache-Cluster

Wenn die Anzahl der Benutzer immer größer wird, können Sie zu diesem Zeitpunkt immer wieder Maschinen hinzufügen, beispielsweise um Durch das Hinzufügen weiterer Maschinen können Sie höhere gleichzeitige Anforderungen verarbeiten. Wenn dann die Schreibparallelität auf Datenbankebene immer höher wird, wird der Datenbankserver erweitert und die Maschine wird durch Unterdatenbank- und Tabellen-Sharding erweitert. Wenn die Leseparallelität auf Datenbankebene immer höher wird, erhöht sich die Kapazität erweitert werden und weitere Slave-Datenbanken hinzugefügt werden. Hier gibt es jedoch ein großes Problem: Die Datenbank selbst wird nicht tatsächlich für die Übertragung hoher gleichzeitiger Anforderungen verwendet. Daher liegt die von einer einzelnen Datenbankmaschine pro Sekunde ausgeführte Parallelität im Allgemeinen in der Größenordnung von Tausenden, und die von der Datenbank verwendeten Maschinen sind es auch relativ hohe Konfiguration, relativ teure Maschinen, die Kosten sind sehr hoch. Wenn man einfach immer wieder Maschinen hinzufügt, ist das eigentlich falsch. Daher ist der Cache normalerweise in Architekturen mit hoher Parallelität enthalten. Das Cache-System ist für eine hohe Parallelität ausgelegt. Daher beträgt die von einer einzelnen Maschine übertragene Parallelität Zehntausende oder sogar Hunderttausende pro Sekunde, und die Tragfähigkeit einer hohen Parallelität ist ein bis zwei Größenordnungen höher als die eines Datenbanksystems. Daher können Sie einen Cache-Cluster vollständig entsprechend den Geschäftsmerkmalen des Systems für Anforderungen einführen, die weniger Schreiben und mehr Lesen erfordern. Insbesondere wird beim Schreiben in die Datenbank gleichzeitig eine Kopie der Daten in den Cache-Cluster geschrieben, und dann wird der Cache-Cluster für die Übertragung der meisten Leseanforderungen verwendet. In diesem Fall können durch Cache-Clustering weniger Maschinenressourcen verwendet werden, um eine höhere Parallelität zu hosten.

Fazit

Eine vollständige und komplexe Systemarchitektur mit hoher Parallelität wird auf jeden Fall Folgendes umfassen: verschiedene komplexe selbstentwickelte Infrastruktursysteme. Alle Arten von exquisiten Architekturentwürfen können daher höchstens inspirierend wirken, aber das ist auch schon alles für Ideen zur Datenbankoptimierung

Das obige ist der detaillierte Inhalt vonEinführung in die MySQL-Datenbankoptimierung (Bilder und Text). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!