MySQL はどのように最適化されていますか? 5 つの側面からパフォーマンスの最適化について話しましょう

面接官が次のように尋ねたら: MySQL のパフォーマンスをどのような側面から最適化しますか?あなたならどう答えますか？

いわゆるパフォーマンスの最適化は、通常、MySQL クエリの最適化を対象としています。クエリを最適化しているので、当然のことながら、まずクエリ操作がどのリンクを経由するかを知り、次にどのリンクを最適化できるかを考える必要があります。

画像を使用して、クエリ操作で実行する必要がある基本的な手順を示します。

#以下では、5 つの観点から MySQL 最適化のための戦略をいくつか紹介します。

1. 接続構成の最適化

接続の処理は、MySQL クライアントと MySQL サーバー間の関係の最初のステップです。最初の まともに歩くことさえできないなら、その後の話はやめましょう。

接続は双方の問題であるため、当然サーバー側とクライアント側の両方から最適化されます。

1.1 サーバー構成

サーバーが行う必要があるのは、できるだけ多くのクライアント接続を受け入れることです。おそらく、

エラー 1040: エラーが多すぎます。接続中?それは、サーバーの思考が十分に広くなく、レイアウトが小さすぎるためです。

接続不足の問題は 2 つの側面から解決できます:

1. 使用可能な接続の数を増やし、環境変数

max_connections を変更します。 、デフォルトでは、サーバー上の最大接続数は 151

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.01 sec)

2 です。非アクティブな接続は適時に解放してください。システムのデフォルトのクライアント タイムアウトは 28800 秒 (8この値はもう少し小さく調整できます

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)

MySQL には多くの設定パラメータがあり、ほとんどのパラメータにはデフォルト値が用意されています。デフォルト値は MySQL 作成者によって慎重に設計されており、状況によっては必要な場合もありますので、パラメータの意味を理解せずにむやみに変更することはお勧めできません。

1.2 クライアントの最適化

クライアントができることは、サーバーとの接続を確立する回数を最小限に抑えることです。確立された接続は使用できます。 SQL ステートメントを実行するたびに新しい接続を作成しないでください。サーバーとクライアントの両方のリソースが過負荷になります。

解決策は、

接続プールを使用して接続を再利用することです。

一般的なデータベース接続プールには、

DBCP、C3P0、Alibaba の Druid、Hikari が含まれます。最初の 2 つはそこで使用されます。は非常に少なく、後者の 2 つは現在ピークです。

ただし、接続プールは大きいほど良いことに注意してください。たとえば、

Druid のデフォルトの最大接続プール サイズは 8、デフォルトの最大接続プール サイズは Hikari は 10 です。接続プールのサイズをやみくもに増やすと、システムの実行効率が低下する可能性があります。なぜ？

接続ごとに、サーバーはそれを処理するための個別のスレッドを作成します。接続が増えるほど、サーバーはより多くのスレッドを作成します。スレッド数が CPU 数を超えると、CPU はスレッドのコンテキスト切り替えを実行するためにタイム スライスを割り当てる必要があり、コンテキスト切り替えが頻繁に行われると、パフォーマンスに多大なオーバーヘッドが発生します。

光公式では、PostgreSQLデータベース接続プールサイズ、CPUコア数*2 1の推奨値計算式を提示しています。サーバーの CPU コア数が 4 であると仮定すると、接続プールを 9 に設定するだけです。この公式は他のデータベースにもある程度当てはまりますので、面接時に自慢することもできます。

2. アーキテクチャの最適化

2.1 キャッシュの使用

システム内で低速のクエリが発生することは避けられません。これらのクエリ データの量が多いか、クエリが複雑 (多数の関連テーブルまたは複雑な計算) のため、クエリが長時間接続を占有します。

この種のデータの有効性がそれほど強くない場合 (日報など、刻々と変化するものではない)、この種のデータをキャッシュ システムに入れることができます。データ。キャッシュ システムからデータを直接取得します。これにより、データベースへの負荷が軽減され、クエリの効率が向上します。

2.2 読み取りと書き込みの分離 (クラスター、マスター/スレーブ レプリケーション)

プロジェクトの初期段階では、データベース通常、サーバー上で実行されるため、ユーザーからのすべての読み取りおよび書き込みリクエストはこのデータベース サーバーに直接影響します。結局のところ、単一サーバーが耐えられる同時実行の量は制限されています。

この問題に対処するには、複数のデータベース サーバーを同時に使用し、そのうちの 1 つを master ノードと呼ばれるチーム リーダーとして設定し、残りのノードを ## と呼ばれるチーム メンバーとして設定します。 ＃奴隷＃＃＃。ユーザーは master ノードにのみデータを書き込み、読み取りリクエストはさまざまな slave ノードに分散されます。このソリューションは 読み取りと書き込みの分離 と呼ばれます。グループ リーダーとグループ メンバーで構成される小さなグループに、cluster という名前を付けます。

注: 多くの開発者は、

マスター-スレーブ

という攻撃的な言葉に不満を抱いています (人種差別、黒人に関連すると考えているため)奴隷など）のため、名前を変更する運動が起こりました。 これの影響により、MySQL は

master

や slave などの用語の使用を徐々にやめ、代わりに source や replica# を使用するようになります。 ＃＃置き換え、誰もがそれに遭遇したときにそれを理解してください。 クラスターを使用するときに直面する必要がある問題の 1 つは、複数のノード間でデータの一貫性を維持する方法です。結局のところ、書き込みリクエストは

master

ノードにのみ送信されます。

master ノードのデータのみが最新のデータです。書き込み操作を master に同期する方法 ノードをすべてのノードに接続しますか? slave ノードはどうですか? マスター/スレーブ レプリケーション

テクノロジーが登場しました! binlogのログについては前回の記事で簡単に紹介したのでそのまま移動しました。

binlog

は、MySQL のマスター/スレーブ レプリケーション機能を実装するコア コンポーネントです。

masterノードはすべての書き込み操作を binlog に記録します。slaveノードには、master ノードの binlog を読み取る専用の I/O スレッドがあり、 write 操作は現在の slave ノードに同期されます。

このクラスター アーキテクチャは、メイン データベース サーバーの負荷を軽減するのに非常に効果的ですが、ビジネス データが増加するにつれて、特定のテーブルのデータ量が増加すると、単一テーブルのクエリパフォーマンスが急激に向上すると、単一テーブルのクエリパフォーマンスは大幅に低下します。この問題は、読み取りと書き込みを分離しても解決できません。結局のところ、すべてのノードにはまったく同じデータが格納されます。クエリパフォーマンスは、単一テーブルのパフォーマンスが悪いので、当然、すべてのノードのパフォーマンスも悪くなります。

このとき、単一ノードのデータを複数のノードに分散して保存することができます。これが、

サブデータベースとサブテーブル

です。

2.3 サブデータベースとサブテーブル

サブデータベースとサブテーブルのノードの意味は比較的広く、データベースをノードとして使用する場合は、 、リーフレットがサブデータベースである場合、テーブルはノード、つまりサブテーブルとして機能します。

サブデータベースとテーブルが垂直サブデータベース、垂直サブテーブル、水平サブデータベース、水平サブテーブルに分かれていることは誰もが知っていますが、これらの概念を覚えていないたびに説明します。誰もが理解しています。

2.3.1 垂直サブデータベース

単一のデータベースに基づいて、ビジネス ロジックに従っていくつかの垂直方向のカットと分割を行います。異なるデータベースに分割する場合、これは 垂直サブデータベース

です。

#2.3.2 垂直テーブル パーティショニング

垂直テーブル パーティショニングは単一のテーブル内で行われます。基本的には、表内の複数の単語を縦にカット（またはいくつかのカット）して、いくつかの小さなテーブルに分割します。この操作は、具体的な業務に基づいて判断する必要があります。通常、頻繁に使用されるフィールド（ホット フィールド）は、テーブル、フィールドに分割されます頻繁に使用されない、またはすぐには使用されない (コールド フィールド) は、クエリ速度を向上させるために 1 つのテーブルに分割されます。

上の図を例に挙げます。通常、製品の詳細は比較的長く、製品リストを表示するときに製品の詳細をすぐに表示する必要がないことがよくあります (通常は詳細ボタンをクリックすると表示されますが、商品のより重要な情報（価格など）が表示されるため、このビジネスロジックに従って、元の商品テーブルを縦方向のサブテーブルに作成しました。

2.3.3 水平テーブル シャーディング

特定のルール (専門用語ではシャーディング ルールと呼ばれます) に従って、単一のテーブルのデータを複数のデータ テーブルに水平に保存します。データ テーブルには 1 つのナイフ (または複数のナイフ) があり、それは horizo​​ntal table になります。

2.3.4 水平分库

水平分库就是对单个数据库水平切一刀，往往伴随着水平分表。

2.3.5 总结

水平分，主要是为了解决存储的瓶颈；垂直分，主要是为了减轻并发压力。

2.4 消息队列削峰

通常情况下，用户的请求会直接访问数据库，如果同一时刻在线用户数量非常庞大，极有可能压垮数据库（参考明星出轨或公布恋情时微博的状态）。

这种情况下可以通过使用消息队列降低数据库的压力，不管同时有多少个用户请求，先存入消息队列，然后系统有条不紊地从消息队列中消费请求。

3. 优化器——SQL分析与优化

处理完连接、优化完缓存等架构的事情，SQL查询语句来到了解析器和优化器的地盘了。在这一步如果出了任何问题，那就只能是SQL语句的问题了。

只要你的语法不出问题，解析器就不会有问题。此外，为了防止你写的SQL运行效率低，优化器会自动做一些优化，但如果实在是太烂，优化器也救不了你了，只能眼睁睁地看着你的SQL查询沦为慢查询。

3.1 慢查询

慢查询就是执行地很慢的查询（这句话说得跟废话似的。。。），只有知道MySQL中有哪些慢查询我们才能针对性地进行优化。

因为开启慢查询日志是有性能代价的，因此MySQL默认是关闭慢查询日志功能，使用以下命令查看当前慢查询状态

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/9e74f9251f6c-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)

slow_query_log表示当前慢查询日志是否开启，slow_query_log_file表示慢查询日志的保存位置。

除了上面两个变量，我们还需要确定“慢”的指标是什么，即执行超过多长时间才算是慢查询，默认是10S，如果改成0的话就是记录所有的SQL。

mysql> show variables like '%long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

3.1.1 打开慢日志

有两种打开慢日志的方式

1、修改配置文件my.cnf

此种修改方式系统重启后依然有效

# 是否开启慢查询日志
slow_query_log=ON
# 
long_query_time=2
slow_query_log_file=/var/lib/mysql/slow.log

2、动态修改参数（重启后失效）

mysql> set @@global.slow_query_log=1;
Query OK, 0 rows affected (0.06 sec)

mysql> set @@global.long_query_time=2;
Query OK, 0 rows affected (0.00 sec)

3.1.2 慢日志分析

MySQL不仅为我们保存了慢日志文件，还为我们提供了慢日志查询的工具mysqldumpslow，为了演示这个工具，我们先构造一条慢查询：

mysql> SELECT sleep(5);

然后我们查询用时最多的1条慢查询：

[root@iZ2zejfuakcnnq2pgqyzowZ ~]# mysqldumpslow -s t -t 1 -g 'select' /var/lib/mysql/9e74f9251f6c-slow.log

Reading mysql slow query log from /var/lib/mysql/9e74f9251f6c-slow.log
Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  SELECT sleep(N)

其中，

• Count：表示这个SQL执行的次数
• Time：表示执行的时间，括号中的是累积时间
• Locks：表示锁定的时间，括号中的是累积时间
• Rows：表示返回的记录数，括号中的是累积数

更多关于mysqldumpslow的使用方式，可以查阅官方文档，或者执行mysqldumpslow --help寻求帮助。

3.2 查看运行中的线程

我们可以运行show full processlist查看MySQL中运行的所有线程，查看其状态和运行时间，找到不顺眼的，直接kill。

其中，

• Id：线程的唯一标志，可以使用Id杀死指定线程
• User：启动这个线程的用户，普通账户只能查看自己的线程
• Host：哪个ip和端口发起的连接
• db：线程操作的数据库
• Command：线程的命令
• Time：操作持续时间，单位秒
• State：线程的状态
• Info：SQL语句的前100个字符

3.3 查看服务器运行状态

使用SHOW STATUS查看MySQL服务器的运行状态，有session和global两种作用域，一般使用like+通配符进行过滤。

-- 查看select的次数
mysql> SHOW GLOBAL STATUS LIKE 'com_select';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 168241 |
+---------------+--------+
1 row in set (0.05 sec)

3.4 查看存储引擎运行信息

SHOW ENGINE用来展示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况；线程信号量等待；文件IO请求；Buffer pool统计信息等等数据。

例如：

SHOW ENGINE INNODB STATUS;

上面这条语句可以展示innodb存储引擎的当前运行的各种信息，大家可以据此找到MySQL当前的问题，限于篇幅不在此意义说明其中信息的含义，大家只要知道MySQL提供了这样一个监控工具就行了，等到需要的时候再来用就好。

3.5 EXPLAIN执行计划

通过慢查询日志我们可以知道哪些SQL语句执行慢了，可是为什么慢？慢在哪里呢？

MySQL提供了一个执行计划的查询命令EXPLAIN，通过此命令我们可以查看SQL执行的计划，所谓执行计划就是：优化器会不会优化我们自己书写的SQL语句（比如外连接改内连接查询，子查询优化为连接查询...）、优化器针对此条SQL的执行对哪些索引进行了成本估算，并最终决定采用哪个索引（或者最终选择不用索引，而是全表扫描）、优化器对单表执行的策略是什么，等等等等。

EXPLAIN在MySQL5.6.3之后也可以针对UPDATE、DELETE和INSERT语句进行分析，但是通常情况下我们还是用在SELECT查询上。

这篇文章主要是从宏观上多个角度介绍MySQL的优化策略，因此这里不详细说明EXPLAIN的细节，之后单独成篇。

3.6 SQL与索引优化

3.6.1 SQL优化

SQL优化指的是SQL本身语法没有问题，但是有实现相同目的的更好的写法。比如：

• 使用小表驱动大表；用join改写子查询；or改成union
• 连接查询中，尽量减少驱动表的扇出（记录数），访问被驱动表的成本要尽量低，尽量在被驱动表的连接列上建立索引，降低访问成本；被驱动表的连接列最好是该表的主键或者是唯一二级索引列，这样被驱动表的成本会降到更低
• 大偏移量的limit，先过滤再排序

针对最后一条举个简单的例子，下面两条语句能实现同样的目的，但是第二条的执行效率比第一条执行效率要高得多（存储引擎使用的是InnoDB），大家感受一下：

-- 1. 大偏移量的查询
mysql> SELECT * FROM user_innodb LIMIT 9000000,10;
Empty set (8.18 sec)

-- 2.先过滤ID（因为ID使用的是索引），再limit
mysql> SELECT * FROM user_innodb WHERE id > 9000000 LIMIT 10;
Empty set (0.02 sec)

3.6.2 索引优化

为慢查询创建适当的索引是个非常常见并且非常有效的方法，但是索引是否会被高效使用又是另一门学问了。

推荐阅读：《如何用好MySQL索引？你必须了解这些事！》，感兴趣的读者可以看一下。
https://www.php.cn/mysql-tutorials-493147.html

4. 存储引擎与表结构

4.1 选择存储引擎

一般情况下，我们会选择MySQL默认的存储引擎存储引擎InnoDB，但是当对数据库性能要求精益求精的时候，存储引擎的选择也成为一个关键的影响因素。

建议根据不同的业务选择不同的存储引擎，例如：

• 查询操作、插入操作多的业务表，推荐使用MyISAM；
• 临时表使用Memory；
• 并发数量大、更新多的业务选择使用InnoDB；
• 不知道选啥直接默认。

4.2 优化字段

字段优化的最终原则是：使用可以正确存储数据的最小的数据类型。

4.2.1 整数类型

MySQL提供了6种整数类型，分别是

• tinyint
• smallint
• mediumint
• int
• integer
• bigint

不同的存储类型的最大存储范围不同，占用的存储的空间自然也不同。

例如，是否被删除的标识，建议选用tinyint，而不是bigint。

4.2.2 字符类型

你是不是直接把所有字符串的字段都设置为varchar格式了？甚至怕不够，还会直接设置成varchar(1024)的长度？

如果不确定字段的长度，肯定是要选择varchar，但是varchar需要额外的空间来记录该字段目前占用的长度；因此如果字段的长度是固定的，尽量选用char，这会给你节约不少的内存空间。

4.2.3 非 null

非 null フィールドを NOT NULL に設定し、デフォルト値を指定するか、代わりに特別な値を使用してみてください。 NULL の。

NULL 型のストレージと最適化ではパフォーマンスの問題が発生するため、具体的な理由についてはここでは説明しません。

4.2.4 外部キー、トリガー、ビュー関数を使用しない

これは、「Alibaba 開発マニュアル」にも記載されている原則です。理由は 3 つあり、

• 可読性が低下する、コードを確認しながらデータベースのコードを確認する必要がある、

• 計算作業を引き継ぐプログラムの場合、データベースはストレージ作業のみを行い、これを適切に実行します。

• データ整合性検証の作業は、外部キーに依存するのではなく、開発者が完了する必要があります。外部キーを使用すると、テスト中に一部のジャンク データを削除するのが非常に困難になることがわかります。

4.2.5 画像、音声、ビデオのストレージ

大きなファイルを直接保存するのではなく、大きなファイルのアクセス アドレスを保存します。

4.2.6 大規模なフィールドの分割とデータの冗長性

大規模なフィールドの分割実際には、これは前述した垂直テーブル パーティショニングです。特に SELECT * の記述に慣れている場合は、列が多すぎたり、データ量が多すぎたりするのを避けるために、フィールドまたは大量のデータを含むフィールドを使用しました。問題は深刻に拡大します。

フィールド冗長性原則として、データベース設計パラダイムには準拠しませんが、高速な検索には非常に役立ちます。例えば、契約テーブルに顧客IDを格納する場合、顧客名を重複して格納することができるため、問い合わせ時に顧客IDからユーザ名を取得する必要がなくなる。したがって、ビジネス ロジックにある程度の冗長性を持たせることも、より良い最適化手法です。

5. ビジネスの最適化

厳密に言えば、ビジネスの最適化は MySQL チューニングの手段ではなくなりましたが、ビジネスの最適化はデータベース アクセスのプレッシャーを非常に効果的に軽減します。この典型的な例は淘宝網です。いくつかのアイデアを提供するために、いくつかの簡単な例を次に示します:

• 以前は、買い物はダブル 11 の夜から始まりました。数年前から、ダブル 11 のプレセールは半月以上前から始まるようになり、様々な入金紅包モデルが際限なく登場するようになり、この方法は プレセール転用## と呼ばれています。 ＃。これにより、顧客サービスの要求をそらすことができ、まとめて注文するためにダブル イレブンの早朝まで待つ必要はありません。

• ダブル イレブンの早朝に、次のことを行うことができます。その日以外の注文を確認するためでしたが、クエリは失敗しました。Alipay の鶏肉の配給さえも遅れました。これは

  ダウングレード戦略 であり、現在の中核ビジネスを確保するために重要でないサービスにコンピューティング リソースを収集します。

• ダブルイレブン期間中、Alipay は、支払いに銀行カードの代わりに Huabei を使用することを強く推奨しています。ソフトウェアの粘着性を向上させることも考慮されていますが、一方で、Yu' を使用することを強く推奨しています。 e Bao は実際に Alipay を使用しています。内部サーバーのアクセス速度は速いですが、銀行カードを使用するには銀行インターフェイスを呼び出す必要があり、比較するとかなり遅いです。

---

MySQL 最適化の概要はここで終わりますが、詳細については言及されていないことが多く、この記事は完璧ではないと感じます。ただし、詳しく説明するには知識が多すぎて、一度にすべてを書き出すのは不可能なので、後で書き留めましょう。

[関連する推奨事項:

mysql ビデオ チュートリアル ]