面接官が次のように尋ねたら: MySQL のパフォーマンスをどのような側面から最適化しますか?あなたならどう答えますか?
いわゆるパフォーマンスの最適化は、通常、MySQL クエリの最適化を対象としています。クエリを最適化しているので、当然のことながら、まずクエリ操作がどのリンクを経由するかを知り、次にどのリンクを最適化できるかを考える必要があります。
画像を使用して、クエリ操作で実行する必要がある基本的な手順を示します。
#以下では、5 つの観点から MySQL 最適化のための戦略をいくつか紹介します。エラー 1040: エラーが多すぎます。接続中?それは、サーバーの思考が十分に広くなく、レイアウトが小さすぎるためです。
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 作成者によって慎重に設計されており、状況によっては必要な場合もありますので、パラメータの意味を理解せずにむやみに変更することはお勧めできません。
接続プールを使用して接続を再利用することです。
一般的なデータベース接続プールには、DBCP、
C3P0、Alibaba の
Druid、
Hikari が含まれます。最初の 2 つはそこで使用されます。は非常に少なく、後者の 2 つは現在ピークです。
Druid のデフォルトの最大接続プール サイズは 8、デフォルトの最大接続プール サイズは
Hikari は 10 です。接続プールのサイズをやみくもに増やすと、システムの実行効率が低下する可能性があります。なぜ?
光公式では、PostgreSQLデータベース接続プールサイズ、
CPUコア数*2 1の推奨値計算式を提示しています。サーバーの CPU コア数が 4 であると仮定すると、接続プールを 9 に設定するだけです。この公式は他のデータベースにもある程度当てはまりますので、面接時に自慢することもできます。
この問題に対処するには、複数のデータベース サーバーを同時に使用し、そのうちの 1 つを master
ノードと呼ばれるチーム リーダーとして設定し、残りのノードを ## と呼ばれるチーム メンバーとして設定します。 #奴隷###。ユーザーは master
ノードにのみデータを書き込み、読み取りリクエストはさまざまな slave
ノードに分散されます。このソリューションは 読み取りと書き込みの分離
と呼ばれます。グループ リーダーとグループ メンバーで構成される小さなグループに、cluster という名前を付けます。
マスター-スレーブノードにのみ送信されます。という攻撃的な言葉に不満を抱いています (人種差別、黒人に関連すると考えているため)奴隷など)のため、名前を変更する運動が起こりました。
masterこれの影響により、MySQL は
や
masterslave
などの用語の使用を徐々にやめ、代わりにsource
やreplica# を使用するようになります。 ##置き換え、誰もがそれに遭遇したときにそれを理解してください。
クラスターを使用するときに直面する必要がある問題の 1 つは、複数のノード間でデータの一貫性を維持する方法です。結局のところ、書き込みリクエストは
master ノードのデータのみが最新のデータです。書き込み操作を
master に同期する方法 ノードをすべてのノードに接続しますか?
slave ノードはどうですか?
マスター/スレーブ レプリケーション
binlog
は、MySQL のマスター/スレーブ レプリケーション機能を実装するコア コンポーネントです。masterノードはすべての書き込み操作を binlog に記録します。
slaveノードには、
master ノードの binlog を読み取る専用の I/O スレッドがあり、 write 操作は現在の
slave ノードに同期されます。
このクラスター アーキテクチャは、メイン データベース サーバーの負荷を軽減するのに非常に効果的ですが、ビジネス データが増加するにつれて、特定のテーブルのデータ量が増加すると、単一テーブルのクエリパフォーマンスが急激に向上すると、単一テーブルのクエリパフォーマンスは大幅に低下します。この問題は、読み取りと書き込みを分離しても解決できません。結局のところ、すべてのノードにはまったく同じデータが格納されます。クエリパフォーマンスは、単一テーブルのパフォーマンスが悪いので、当然、すべてのノードのパフォーマンスも悪くなります。
このとき、単一ノードのデータを複数のノードに分散して保存することができます。これが、サブデータベースとサブテーブル
です。2.3 サブデータベースとサブテーブル
2.3.1 垂直サブデータベース
単一のデータベースに基づいて、ビジネス ロジックに従っていくつかの垂直方向のカットと分割を行います。異なるデータベースに分割する場合、これは 垂直サブデータベース
です。
#2.3.2 垂直テーブル パーティショニング
水平分库就是对单个数据库水平切一刀,往往伴随着水平分表。
水平分,主要是为了解决存储的瓶颈;垂直分,主要是为了减轻并发压力。
通常情况下,用户的请求会直接访问数据库,如果同一时刻在线用户数量非常庞大,极有可能压垮数据库(参考明星出轨或公布恋情时微博的状态)。
这种情况下可以通过使用消息队列降低数据库的压力,不管同时有多少个用户请求,先存入消息队列,然后系统有条不紊地从消息队列中消费请求。
处理完连接、优化完缓存等架构的事情,SQL查询语句来到了解析器和优化器的地盘了。在这一步如果出了任何问题,那就只能是SQL语句的问题了。
只要你的语法不出问题,解析器就不会有问题。此外,为了防止你写的SQL运行效率低,优化器会自动做一些优化,但如果实在是太烂,优化器也救不了你了,只能眼睁睁地看着你的SQL查询沦为慢查询。
慢查询就是执行地很慢的查询(这句话说得跟废话似的。。。),只有知道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)
有两种打开慢日志的方式
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)
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)
其中,
更多关于mysqldumpslow
的使用方式,可以查阅官方文档,或者执行mysqldumpslow --help
寻求帮助。
我们可以运行show full processlist
查看MySQL中运行的所有线程,查看其状态和运行时间,找到不顺眼的,直接kill。
其中,
使用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)
SHOW ENGINE
用来展示存储引擎的当前运行信息,包括事务持有的表锁、行锁信息;事务的锁等待情况;线程信号量等待;文件IO请求;Buffer pool统计信息等等数据。
例如:
SHOW ENGINE INNODB STATUS;
上面这条语句可以展示innodb存储引擎的当前运行的各种信息,大家可以据此找到MySQL当前的问题,限于篇幅不在此意义说明其中信息的含义,大家只要知道MySQL提供了这样一个监控工具就行了,等到需要的时候再来用就好。
通过慢查询日志我们可以知道哪些SQL语句执行慢了,可是为什么慢?慢在哪里呢?
MySQL提供了一个执行计划的查询命令EXPLAIN
,通过此命令我们可以查看SQL执行的计划,所谓执行计划就是:优化器会不会优化我们自己书写的SQL语句(比如外连接改内连接查询,子查询优化为连接查询...)、优化器针对此条SQL的执行对哪些索引进行了成本估算,并最终决定采用哪个索引(或者最终选择不用索引,而是全表扫描)、优化器对单表执行的策略是什么,等等等等。
EXPLAIN在MySQL5.6.3之后也可以针对UPDATE、DELETE和INSERT语句进行分析,但是通常情况下我们还是用在SELECT查询上。
这篇文章主要是从宏观上多个角度介绍MySQL的优化策略,因此这里不详细说明EXPLAIN
的细节,之后单独成篇。
SQL优化指的是SQL本身语法没有问题,但是有实现相同目的的更好的写法。比如:
针对最后一条举个简单的例子,下面两条语句能实现同样的目的,但是第二条的执行效率比第一条执行效率要高得多(存储引擎使用的是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)
为慢查询创建适当的索引是个非常常见并且非常有效的方法,但是索引是否会被高效使用又是另一门学问了。
推荐阅读:《如何用好MySQL索引?你必须了解这些事!》,感兴趣的读者可以看一下。
https://www.php.cn/mysql-tutorials-493147.html
一般情况下,我们会选择MySQL默认的存储引擎存储引擎InnoDB
,但是当对数据库性能要求精益求精的时候,存储引擎的选择也成为一个关键的影响因素。
建议根据不同的业务选择不同的存储引擎,例如:
MyISAM
;Memory
;InnoDB
;字段优化的最终原则是:使用可以正确存储数据的最小的数据类型。
MySQL提供了6种整数类型,分别是
不同的存储类型的最大存储范围不同,占用的存储的空间自然也不同。
例如,是否被删除的标识,建议选用tinyint
,而不是bigint
。
你是不是直接把所有字符串的字段都设置为varchar
格式了?甚至怕不够,还会直接设置成varchar(1024)
的长度?
如果不确定字段的长度,肯定是要选择varchar
,但是varchar
需要额外的空间来记录该字段目前占用的长度;因此如果字段的长度是固定的,尽量选用char
,这会给你节约不少的内存空间。
非 null フィールドを NOT NULL
に設定し、デフォルト値を指定するか、代わりに特別な値を使用してみてください。 NULL
の。
NULL
型のストレージと最適化ではパフォーマンスの問題が発生するため、具体的な理由についてはここでは説明しません。
これは、「Alibaba 開発マニュアル」にも記載されている原則です。理由は 3 つあり、
可読性が低下する、コードを確認しながらデータベースのコードを確認する必要がある、
計算作業を引き継ぐプログラムの場合、データベースはストレージ作業のみを行い、これを適切に実行します。
データ整合性検証の作業は、外部キーに依存するのではなく、開発者が完了する必要があります。外部キーを使用すると、テスト中に一部のジャンク データを削除するのが非常に困難になることがわかります。
大きなファイルを直接保存するのではなく、大きなファイルのアクセス アドレスを保存します。
大規模なフィールドの分割実際には、これは前述した垂直テーブル パーティショニングです。特に SELECT *
の記述に慣れている場合は、列が多すぎたり、データ量が多すぎたりするのを避けるために、フィールドまたは大量のデータを含むフィールドを使用しました。問題は深刻に拡大します。
フィールド冗長性原則として、データベース設計パラダイムには準拠しませんが、高速な検索には非常に役立ちます。例えば、契約テーブルに顧客IDを格納する場合、顧客名を重複して格納することができるため、問い合わせ時に顧客IDからユーザ名を取得する必要がなくなる。したがって、ビジネス ロジックにある程度の冗長性を持たせることも、より良い最適化手法です。
厳密に言えば、ビジネスの最適化は MySQL チューニングの手段ではなくなりましたが、ビジネスの最適化はデータベース アクセスのプレッシャーを非常に効果的に軽減します。この典型的な例は淘宝網です。いくつかのアイデアを提供するために、いくつかの簡単な例を次に示します:
以前は、買い物はダブル 11 の夜から始まりました。数年前から、ダブル 11 のプレセールは半月以上前から始まるようになり、様々な入金紅包モデルが際限なく登場するようになり、この方法は プレセール転用## と呼ばれています。 #。これにより、顧客サービスの要求をそらすことができ、まとめて注文するためにダブル イレブンの早朝まで待つ必要はありません。
ダウングレード戦略 であり、現在の中核ビジネスを確保するために重要でないサービスにコンピューティング リソースを収集します。
mysql ビデオ チュートリアル ]