MYSQL ist wahrscheinlich die beliebteste WEB-Backend-Datenbank. Die Web-Entwicklungssprachen PHP, Ruby, Python und Java haben jeweils ihre eigenen Eigenschaften. Obwohl NOSQL in letzter Zeit immer häufiger erwähnt wird, glaube ich, dass die meisten Architekten immer noch MYSQL für die Datenspeicherung wählen werden. Wie erreicht MySQL eine Leistungsoptimierung? Im folgenden Artikel werden einige nützliche Methoden zur MySQL-Leistungsoptimierung vorgestellt. Ich hoffe, dass er für alle hilfreich ist. .
Siehe das MySQL-Tutorial auf der chinesischen PHP-Website: „Führen Sie Sie in sechs Tagen durch MySQL, Video-Tutorial“
Praktische MySQL-Methoden zur Leistungsoptimierung:
Verbessern Sie die Lese- und Schreibgeschwindigkeit der Festplatte
RAID0, insbesondere wenn Verwendung von EC2 Bei Verwendung dieser Art von virtueller Festplatte (EBS) ist es sehr wichtig, Soft-RAID0 zu verwenden.
MYSQL auf NOSQL-Art nutzen
B-TREE ist immer noch einer der effizientesten Indizes und das gesamte MYSQL ist immer noch zukunftssicher.
Verwenden Sie HandlerSocket, um die SQL-Parsing-Ebene von MYSQL zu überspringen, und MYSQL wird wirklich zu NOSQL.
Reduzieren Sie Schreibvorgänge auf der Festplatte
1 Verwenden Sie einen ausreichend großen Schreibcache innodb_log_file_size
Aber Sie müssen aufpassen, wenn Sie 1G innodb_log_file_size verwenden Servermaschine und die Wiederherstellung dauert 10 Minuten.
Es wird empfohlen, innodb_log_file_size auf 0,25 * innodb_buffer_pool_size zu setzen
2 innodb_flush_log_at_trx_commit
Diese Option steht in engem Zusammenhang mit Festplattenschreibvorgängen:
innodb_flush_log_at_trx_commit = 1 , dann wird jede Änderung geschrieben. Auf die Festplatte schreiben
innodb_flush_log_at_trx_commit = 0/2 Auf die Festplatte pro Sekunde schreiben
Wenn Ihre Anwendung keine hohe Sicherheit (Finanzsystem) beinhaltet oder die Infrastruktur sicher genug ist, oder die Transaktionen sind klein, beides. Sie können 0 oder 2 verwenden, um den Festplattenbetrieb zu verlangsamen.
3 Vermeiden Sie doppelte Schreibpufferung
innodb_flush_method=O_DIRECT
Es sei denn, Ihre Daten Die Tabelle für die schreibgeschützte Suche oder die Volltextsuche verwendet wird (ich glaube, dass jetzt niemand MYSQL verwenden wird, wenn es um die Volltextsuche geht), sollten Sie standardmäßig InnoDB wählen.
Möglicherweise stellen Sie bei Ihren eigenen Tests fest, dass MyISAM schneller ist als InnoDB. Dies liegt daran, dass MyISAM nur Indizes zwischenspeichert, während InnoDB keine Transaktionen unterstützt. Wenn Sie jedoch innodb_flush_log_at_trx_commit = 2 verwenden, können Sie eine nahezu gute Leseleistung erzielen (einen Hundertfachen Unterschied).
mysql -u [USER_NAME] -p -e "SHOW TABLES IN [DATABASE_NAME];" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=InnoDB;" > alter_table.sql
perl -p -i -e 's/(search_[a-z_]+ ENGINE=)InnoDB//1MyISAM/g' alter_table.sql
mysql -u [USER_NAME] -p [DATABASE_NAME] < alter_table.sqlinnodb_file_per_table=1
Dadurch wird sichergestellt, dass die Datei ibdata1 nicht vorhanden ist Es wird zu groß und außer Kontrolle geraten. Besonders beim Ausführen von mysqlcheck -o –all-databases.
wird empfohlen. Die Daten sind vollständig in innodb_buffer_pool_size gespeichert, dh die Kapazität von innodb_buffer_pool_size wird entsprechend der Speichermenge geplant. Auf diese Weise können Sie Daten vollständig aus dem Speicher lesen und so die Festplattenvorgänge minimieren.
Methode 1
mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%'; +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | Innodb_buffer_pool_pages_data | 129037 | | Innodb_buffer_pool_pages_dirty | 362 | | Innodb_buffer_pool_pages_flushed | 9998 | | Innodb_buffer_pool_pages_free | 0 | !!!!!!!! | Innodb_buffer_pool_pages_misc | 2035 | | Innodb_buffer_pool_pages_total | 131072 | +----------------------------------+--------+ 6 rows in set (0.00 sec)
Wenn Sie feststellen, dass Innodb_buffer_pool_pages_free 0 ist, bedeutet dies, dass der Pufferpool aufgebraucht ist und Sie innodb_buffer_pool_size erhöhen müssen
Mehrere andere Parameter von InnoDB:
innodb_additional_mem_pool_size = 1/200 of buffer_pool innodb_max_dirty_pages_pct 80%
Methode 2
Oder verwenden Sie den Befehl iostat -d -x -k 1, um den Betrieb der Festplatte zu überprüfen.
Führen Sie echo 1 > /proc/sys/vm/drop_caches aus, um den Dateicache des Betriebssystems zu löschen, und Sie können die tatsächliche Speichernutzung sehen .
Standardmäßig werden Daten nur dann im innodb_buffer_pool zwischengespeichert, wenn sie einmal gelesen werden. Daher wurde die Datenbank gerade erst gestartet und muss die Daten aufwärmen und alle Daten auf der Festplatte im Speicher zwischenspeichern. Das Aufwärmen der Daten kann die Lesegeschwindigkeit erhöhen.
Für die InnoDB-Datenbank können Sie die folgende Methode verwenden, um die Daten aufzuwärmen:
1. Speichern Sie das folgende Skript als MakeSelectQueriesToLoad.sql
SELECT DISTINCT
CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM
(
SELECT
engine,table_schema db,table_name tb,
index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
FROM
(
SELECT
B.engine,A.table_schema,A.table_name,
A.index_name,A.column_name,A.seq_in_index
FROM
information_schema.statistics A INNER JOIN
(
SELECT engine,table_schema,table_name
FROM information_schema.tables WHERE
engine='InnoDB'
) B USING (table_schema,table_name)
WHERE B.table_schema NOT IN ('information_schema','mysql')
ORDER BY table_schema,table_name,index_name,seq_in_index
) A
GROUP BY table_schema,table_name,index_name
) AA
ORDER BY db,tb
;2
mysql -uroot -AN < /root/MakeSelectQueriesToLoad.sql > /root/SelectQueriesToLoad.sql
3. Jedes Mal, wenn Sie die Datenbank neu starten oder wenn Sie sich vor dem Sichern der gesamten Datenbank aufwärmen müssen, führen Sie Folgendes aus:
mysql -uroot < /root/SelectQueriesToLoad.sql > /dev/null 2>&1
Wenn ja, kann SWAP bei einem dedizierten MYSQL-Server deaktiviert werden. Wenn es sich um einen gemeinsam genutzten Server handelt, stellen Sie sicher, dass innodb_buffer_pool_size groß genug ist. Oder verwenden Sie einen festen Speicherplatz zum Caching und verwenden Sie die Memlock-Anweisung.
mysqlcheck -o –all-databases sorgt dafür, dass ibdata1 weiter wächst. Die einzige wirkliche Optimierung besteht darin Erstellen Sie die Datentabellenstruktur neu:
CREATE TABLE mydb.mytablenew LIKE mydb.mytable; INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable; ALTER TABLE mydb.mytable RENAME mydb.mytablezap; ALTER TABLE mydb.mytablenew RENAME mydb.mytable; DROP TABLE mydb.mytablezap;
SHOW CREATE TABLE db1.tb1/G
添加必要的索引
索引是提高查询速度的唯一方法，比如搜索引擎用的倒排索引是一样的原理。
索引的添加需要根据查询来确定，比如通过慢查询日志或者查询日志,或者通过 EXPLAIN 命令分析查询。
ADD UNIQUE INDEX ADD INDEX
添加索引
ALTER TABLE users ADD UNIQUE INDEX username_ndx (username); ALTER TABLE users ADD UNIQUE INDEX username_password_ndx (username,password);
每次重启服务器进行数据预热
echo “select username,password from users;” > /var/lib/mysql/upcache.sql
添加启动脚本到 my.cnf
[mysqld] init-file=/var/lib/mysql/upcache.sql
比如，Rails 这样的框架，会自动添加索引，Drupal 这样的框架会自动拆分表结构。会在你开发的初期指明正确的方向。所以，经验不太丰富的人一开始就追求从 0 开始构建，实际是不好的做法。
记录所有查询，这在用 ORM 系统或者生成查询语句的系统很有用。
log=/var/log/mysql.log
注意不要在生产环境用，否则会占满你的磁盘空间。
记录执行时间超过 1 秒的查询：
long_query_time=1 log-slow-queries=/var/log/mysql/log-slow-queries.log
相关推荐：
1. MySQL最新手册教程
MySQL ändert den Namen der Datentabelle
MySQL erstellt eine gespeicherte Prozedur
Der Unterschied zwischen Mongodb und MySQL
So überprüfen Sie, ob das MySQL-Passwort vergessen wurde
MySQL-Datenbank erstellen
MySQL-Standard-Transaktionsisolationsstufe
Der Unterschied zwischen SQL Server und MySQL
mysqlPasswort vergessen
![[Web-Frontend] Node.js-Schnellstart](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
