Wie optimiert man die MySQL-Leistung? Eine praktische Methode zur Leistungsoptimierung (als Referenz für Anfänger)

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

Wählen Sie die richtige Speicher-Engine: InnoDB

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).

So konvertieren Sie eine vorhandene MyISAM-Datenbank in InnoDB:

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.sql

Erstellen Sie eine InnoDB-DATEI für jede Tabelle:

innodb_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.

Garantiert, um Daten aus dem Speicher zu lesen und Daten im Speicher zu speichern

Eine ausreichend große innodb_buffer_pool_size

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.

Wie kann sichergestellt werden, dass innodb_buffer_pool_size groß genug ist und die Daten aus dem Speicher statt von der Festplatte gelesen werden?

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.

Ob auf dem Server genügend Speicher für die Planung vorhanden ist

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 .

Datenaufwärmphase

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

Lassen Sie die Daten nicht speichern in SWAP

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.

Datenbank regelmäßig optimieren und neu erstellen

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最新手册教程

2. MySQL 5.1参考手册