MySQL最適化のためのキャッシュ最適化の詳しい解説(1)

最もよく寄せられる質問は、MySQL データベースパフォーマンスの最適化に関するものであるため、最近、MySQL データベースのパフォーマンスの最適化に関する一連の記事を書く予定であり、MySQL のパフォーマンスに関する初級および中級の MySQL DBA やその他の人々に役立つことを願っています。最適化は興味がある人にとって役立ちます。

何人かのブロガーが私の記事にマークを付けてくれて嬉しいです。マークを知った後、私はめったに戻って注意を払い続けます。しかし、これは、ブロガーがブログをクリックすると、このブログに価値があり、自分の知識不足を補ってくれると感じていることを側面から示しています。ブログで最も重要なことは、自分にとって有益であることが他の人にとっても有益であるということです。私がブログにこだわる目的は、知識を忘れたときに、信頼できる解決策をできるだけ早く見つけることができるようにすることです。要約した知識を覚えていると、徐々に忘れていきますが、時間が経つと、その知識の一部が最終的に自分の言葉に変わり、忘れるのが怖くなくなります。このブログでは引き続き MySQL について話します。このブログでは キャッシュ 最適化について話します。このブログは私の学習プロセスでもあります。

まず mysql のバージョンを見てみましょう。私の Mac にインストールされているバージョンは 5.7 で、多くの内容が変更されています。ここで説明するのは主にバージョン 5.6 です。

[root@roverliang ~]# mysql --version
mysql Ver 14.14 Distrib 5.6.24, for Linux (x86_64) using EditLine wrapper

1. MySQL キャッシュの分類

MySQL の最適化とは、SQL ステートメントの最適化の観点からも効果があると話しました。論理的な側面から最適化されています。しかし、すべての論理レベルを最適化できず、すべての インデックス が追加されており、テーブル構造が合理的に設計されているにもかかわらず、高い同時実行性が発生した場合、なぜ MySQL はそれを処理できないのでしょうか?もちろん、MySQL へのプレッシャーは他の側面によって軽減できますが、ここではここでは説明しません。 MySQL の場合、すべてのコンピューティング リソースを無駄にすることなく利用できるように、マシンのパフォーマンスを最大限に絞り込む必要があります。 MySQL はサーバー、特に Linux サーバー上で実行されます。次に、サーバーのハードディスク、CPU、メモリ、ネットワークはすべて MySQL のパフォーマンスに影響します。 MySQL は非常に多くのメモリを消費します。オンライン サーバーの MySQL メモリは約 80% を消費します。メモリが小さすぎると、実際には他の最適化の余地がほとんどなくなります。

さらに、接続も MySQL のパフォーマンスに影響を与える重要な側面です。 MySQL クライアントと MySQL サーバー間の接続は、MySQL クライアントと MySQL サーバー間のハンドシェイクの繰り返しの結果です。各「ハンドシェイク」では、ID 検証、権限検証などが行われます。ハンドシェイクには、特定のネットワーク リソースと MySQL サーバーのメモリ リソースが必要です。

同時実行パフォーマンス要件が比較的高いデータベースの場合、ロックの競合が激しい場合、データベースのパフォーマンスに大きな影響を及ぼします。ロックの競合により、予想される需要とは関係なく、スレッド コンテキストの切り替えのオーバーヘッドが大幅に増加します。

2. show status と show variables

MySQL シリーズのこれまでのブログでは、これらのコマンドが頻繁に登場しました。この 2 つのコマンドが MySQL システム管理者にどのような情報を表示するかを見てみましょう。

ステータスを表示

MySQL サービスが実行されているとき、MySQL サービス インスタンスの

ステータス

情報は動的です。このコマンドを使用して、現在の MySQL サーバー接続のセッション ステータス 変数情報を表示します。デフォルトでは、変数名の最初の文字は大文字です。

show variables

show variables は、MySQL サービス インスタンスのさまざまな

システム変数

(グローバル システム変数、セッション システム変数、静的変数など) を表示するために使用されます。これらの変数にはデフォルト値が含まれています。 MySQL のコンパイル時パラメータ、または my.cnf に設定されたパラメータ値。システム変数またはパラメータは静的な概念です。デフォルトでは、システム変数名はすべて小文字です。 MySQL

コマンド show status または show

session status を使用して、現在の MySQL サーバー接続のセッション変数情報を表示します。セッション ステータスの変数値は、Opened_tables、Opened_table_settings など、現在の MySQL クライアントに対して有効です。状態変数。 キャッシュメカニズム

缓存之所以有效，主要是因为程序运行时对内存或者外存的访问呈现局部性特征，局部性特征为空间局部性和时间局部性两方面。时间局部性是指刚刚访问过的数据近期可能再次被访问，空间局部性是指，某个位置被访问后，其相邻的位置的数据很可能被访问到。而MySQL的缓存机制就是把刚刚访问的数据（时间局部性）以及未来即将访问到的数据（空间局部性）保存到缓存中，甚至是高速缓存中。从而提高I/O效率。

按照缓存读写功能的不同，MySQL将缓存分为Buffer缓存和Cache缓存。

Buffer缓存。由于硬盘的写入速度过慢，或者频繁的I/O，对于硬盘来说是极大的效率浪费。那么可以等到缓存中储存一定量的数据之后，一次性的写入到硬盘中。Buffer 缓存主要用于写数据，提升I/O性能。

Cache 缓存。 Cache 缓存一般是一些访问频繁但是变更较少的数据，如果Cache缓存已经存储满，则启用LRU算法，进行数据淘汰。淘汰掉最远未使用的数据，从而开辟新的存储空间。不过对于特大型的网站，依靠这种策略很难缓解高频率的读请求，一般会把访问非常频繁的数据静态化，直接由nginx返还给用户。程序和数据库I/O设备交互的越少，则效率越高。

三、MySQL 超时

在使用MySQL的过程中，可能会出现各种超时（timeout）异常，典型的有连接超时、锁等待等。

查看超时时间的类型有哪些：

mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name        | Value  |
+-----------------------------+----------+
| connect_timeout       | 10    |
| delayed_insert_timeout   | 300   |
| innodb_flush_log_at_timeout | 1    |
| innodb_lock_wait_timeout  | 50    |
| innodb_rollback_on_timeout | OFF   |
| interactive_timeout     | 28800  |
| lock_wait_timeout      | 31536000 |
| net_read_timeout      | 30    |
| net_write_timeout      | 60    |
| rpl_stop_slave_timeout   | 31536000 |
| slave_net_timeout      | 3600   |
| wait_timeout        | 28800  |
+-----------------------------+----------+

1、连接超时（connect_timeout）

connect_timeout默认为10s,获取MySQL连接是客户机与服务器之间握手的结果，并且是多次握手的结果，每次握手，除了验证账户名和身份信息外，还需要验证主机、域名解析。如果客户机和服务器之间存在网络故障，可以通过connect_timeout参数来设置，防止它们之间重复握手。

interactive_timeout指的是交互式的终端，在命令行中输入的这种。超过了其设置的默认值就会断开。

wait_timeout指的是非交互式的终端，比如PHP实例化的Mysql连接，一直占用着，超过了这个参数设置的值，就会自动断开。

net_write_timeout MySQL服务器产生一个很大的数据集，MySQL客户机在该值设置的时间内不能接受完毕，则会断开连接。

net_read_timeout MySQL客户机读取了一个很大的数据，在设置值内不能读取完毕，则会自动断开连接。

InnoDB锁等待超时

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name      | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50  |
+--------------------------+-------+

InnoDB 的锁等待时间默认为50s,设置行级锁锁等待的值，当出现锁等待的时候，等待时长超过该值会导致锁等待的SQL回滚（不是整个事务回滚）。如果希望整个事务回滚，需要开启innodb_rollback_on_timeout参数。

mysql> show variables like '%rollback%';
+----------------------------+-------+
| Variable_name       | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF  |
| innodb_rollback_segments  | 128  |
+----------------------------+-------+

innodb_rollback_on_timeout设置为true 后，遇到事务超时，会回滚整个事务的操作。

复制连接超时

当主从配置是，从服务器（slave）从主服务器（master）读取二进制日志失败后，从服务器会等待 slave_net_timeout 后，从新从master机拉去二进制日志。可以设置成10s.

mysql> show variables like 'slave_net_timeout';
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| slave_net_timeout | 3600 |
+-------------------+-------+

这部分总结，应该是周日晚上就该整理好的，结果拖到了今天。后面的计划又要后延了，拖延症真严重。

以上がMySQL最適化のためのキャッシュ最適化の詳しい解説(1)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。