インデックス以外に、mysql クエリを遅くする要因は何ですか?

私は長年にわたり、ctrl c と ctrl v を使用してカード コードを開発することに熟練してきました。

mysql クエリが遅いのはなぜですか? この問題は実際の開発でよく遭遇する問題であり、面接でもよく聞かれる質問です。

この種の問題が発生した場合、私たちは通常、インデックスが原因であると考えます。

インデックス以外に、データベース クエリの速度低下の原因となる要因は何ですか?

mysql のクエリ機能を向上させることができる操作は何ですか?

今日の記事では、データベース クエリの速度低下を引き起こすシナリオについて説明し、その理由と解決策を示します。

データベース クエリ プロセス

まず、クエリ ステートメントがどのようなプロセスを通過するかを見てみましょう。

たとえば、データベース テーブル

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(100) NOT NULL DEFAULT '' COMMENT '名字',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `gender` int(8) NOT NULL DEFAULT '0' COMMENT '性别',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`),
  KEY `idx_gender` (`gender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

があります。通常、私たちが作成するアプリケーション コード (go または C など) は、現時点では client と呼ばれています。

クライアントの最下層はアカウントのパスワードを取得し、mysql への TCP ロング リンクを確立しようとします。

Mysql の 接続管理モジュール がこの接続を管理します。

接続を確立した後、クライアントはクエリ SQL ステートメントを実行します。例:

select * from user where gender = 1 and age = 100;

クライアントは、ネットワーク経由で SQL ステートメントを mysql に接続します。

mysql は SQL ステートメントを受信すると、まず analyzer の SQL ステートメントに文法エラー (select など) があるかどうかを判断します。 ##、slect として記述され、エラーが報告されますSQL 構文にエラーがあります;。このエラーレポートは、私のような障害者にとっては非常に身近なものと言えます。 次は

オプティマイザ

です。ここでは、特定のルールに従って使用するインデックスを選択します。 その後、

executor

を通じてストレージエンジンのインターフェース関数が呼び出されます。

ストレージ エンジン

コンポーネントと同様に、mysql が実際にデータ行を取得し、データを返す場所です。ストレージ エンジンは交換および変更できます。はい、トランザクションをサポートしていない MyISAM を使用するか、トランザクションをサポートする Innodb に置き換えることができます。テーブル作成時に指定できます。たとえば、

CREATE TABLE `user` (
  ...
) ENGINE=InnoDB;

は現在、最も一般的に使用されている

InnoDB

です。 これに焦点を当てましょう。

InnoDB では、ディスクを直接操作すると速度が低下するため、高速化するために

バッファ プール

と呼ばれるメモリ層が追加されます。その中には多くのメモリ ページがあり、各ページは 16KB です一部のメモリ ページにはデータベース テーブルに見られる行ごとのデータが保持され、一部のメモリ ページにはインデックス情報が保持されます。

InnoDB に SQL をクエリします。前のオプティマイザで計算されたインデックスに基づいて、対応するインデックス ページ

がクエリされ、バッファ プールにない場合は、インデックス ページがディスクからロードされます。

次に、インデックス ページのクエリを高速化して、データ ページの特定の場所を取得します。これらのデータ ページがバッファ プールにない場合、ディスクからロードされます。 このようにして、必要なデータの行を取得します。

最後に、取得したデータ結果をクライアントに返します。

遅いクエリ分析

上記のプロセスが比較的遅い場合は、プロファイリングをオンにすることで、プロセスが遅い箇所を確認できます。

mysql> set profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

その後、SQL ステートメントを通常どおり実行します。 これらの SQL ステートメントの実行時間が記録されます。この時点で、どのステートメントが記録されたかを確認したい場合は、

show profiles;

mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.06811025 | select * from user where age>=60                  |
|        2 | 0.00151375 | select * from user where gender = 2 and age = 80  |
|        3 | 0.00230425 | select * from user where gender = 2 and age = 60  |
|        4 | 0.00070400 | select * from user where gender = 2 and age = 100 |
|        5 | 0.07797650 | select * from user where age!=60                  |
+----------+------------+---------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

## を実行できます。 #それに従う 上記の

query_id、たとえば select * from user where age>=60

、対応する query_id は 1 です。この SQL ステートメントの具体的な消費時間を確認したい場合は、 、以下の注文を実行できます。

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000074 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000034 |
| init                 | 0.000032 |
| System lock          | 0.000027 |
| optimizing           | 0.000020 |
| statistics           | 0.000058 |
| preparing            | 0.000018 |
| executing            | 0.000013 |
| Sending data         | 0.067701 |
| end                  | 0.000021 |
| query end            | 0.000015 |
| closing tables       | 0.000014 |
| freeing items        | 0.000047 |
| cleaning up          | 0.000027 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

上記の項目により、具体的にどこに時間がかかっているかがわかります。たとえば、上記のデータから、データの送信に最も時間がかかることがわかります。これは、executor がデータのクエリを開始してクライアントにデータを送信するのにかかる時間を指します。 ## 対象となるデータ。#数万のエントリ であるため、この部分に最も時間がかかりますが、予想どおりです。

通常の状況では、開発プロセス中、ほとんどの時間は データ送信 段階に費やされます。この段階で速度が遅い場合、考えられる最も可能性の高い理由は次のとおりです。インデックス関連の理由です。

インデックス関連の理由

インデックス関連の問題は、通常、explain コマンドを使用して分析できます。これにより、どのインデックスが使用されているか、おそらく 何行がスキャンされるか、およびその他の情報がわかります。

mysql は オプティマイザー フェーズ でどのインデックスを選択するかを検討し、クエリ速度が速くなります。

一般に、次のような考慮すべき要素がいくつかあります。

• このインデックスを選択するためにスキャンする行数 (rows)
Inこれらを組み合わせるために行をフェッチするには、16kb ページを何ページ読み取る必要がありますか?
• 通常のインデックスにはテーブル バッキングが必要ですが、主キー インデックスには必要ありません。
テーブル バッキングのコストは## ＃ 大きい？
• show profile で説明した SQL ステートメントに戻り、
explain select * from user where age>=60

を使用して分析します。

上記のステートメントでは、type

を ALL として使用しています。これは、

full table scan、possible_keys を意味します。 によって使用される可能性のあるインデックス 。ここで使用される可能性のあるインデックスは、年齢に応じて構築された通常のインデックスですが、実際にはデータベースで使用されるインデックスは key 列にあります。無効である＃＃＃。言い換えると、 この SQL はインデックスを使用せず、テーブル全体をスキャンします 。 これは、データ テーブルに修飾されたデータ行 (rows) が多すぎるためです。年齢インデックスを使用する場合は、年齢インデックスからそれらを読み取る必要があり、年齢インデックス これは 通常のインデックス

であり、対応する

主キーを見つけるには テーブルに戻り、対応する データ ページ##を見つける必要があります。 ＃。結局のところ、主キーを直接使用する場合ほどコスト効率は高くありません。そこで私は最終的にフルテーブルスキャンを選択しました。 もちろん、上記は単なる一例であり、実際には、mysql が SQL を実行するとき、 インデックスが使用されていないか、使用されているインデックスが期待を満たしていません これはよく起こります。 不等号の使用、暗黙の変換 など、インデックスが失敗する多くのシナリオ。8 部構成のエッセイを暗記するときにこれをよく覚えていると思いますので、詳細は説明しません。

実稼働環境で遭遇しやすい 2 つの問題について話しましょう。

インデックスが期待どおりではありません

実際の開発では、一部のデータベース テーブルのデータ量が小さく、最初にインデックスがほとんどないなど、特殊な状況が発生します。 SQL を実行するときに実際に使用され、期待どおりのインデックスが得られます。しかし、時間が経つにつれて、開発する人が増え、データ量が増大し、他の重複した冗長なインデックスが追加されることもあり、期待を満たさない他のインデックスが使用される可能性があります。これにより、クエリが突然遅くなります。

この種の問題は、index

から force Index

までを指定することで簡単に解決できます。たとえば、

##explain から、force インデックスを追加した後、SQL は idx_age インデックスを使用することがわかります。

インデックス作成後も依然として非常に遅いです

一部の SQL では、explain コマンドを使用すると、インデックスが作成されていることが明らかです。しかし、それでも非常に遅いです。通常、状況は 2 つあります:

1 つ目は、インデックスの区別が低すぎることです。たとえば、Web ページへの URL リンクのフル パスがインデックス作成に使用されます。一見すると、それらはすべて同じドメイン名である場合、プレフィックス インデックス の長さが十分ではないため、インデックス作成は

全テーブル スキャン

と似ています。正しいアプローチは、# を作成することです。ドメイン名の削除など、インデックスの上位の ##discrimination では、インデックス作成に URI の後半部分のみを使用します。

2 つ目は、インデックスに一致するデータが大きすぎることです。このとき注意する必要があるのは、rows フィールドです。説明の中で。 これは、このクエリ ステートメントについてチェックする必要がある行数を

推定するために使用されます。完全に正確ではない可能性がありますが、大まかな大きさを反映することができます。

これが非常に大きい場合、一般に次のような状況が一般的です。

• 如果这个字段具有唯一的属性，比如电话号码等，一般是不应该有大量重复的，那可能是你代码逻辑出现了大量重复插入的操作，你需要检查下代码逻辑，或者需要加个唯一索引限制下。
• 如果这个字段下的数据就是会很大，是否需要全部拿？如果不需要，加个limit限制下。如果确实要拿全部，那也不能一次性全拿，今天你数据量小，可能一次取一两万都没啥压力，万一哪天涨到了十万级别，那一次性取就有点吃不消了。你可能需要分批次取，具体操作是先用order by id排序一下，拿到一批数据后取最大id作为下次取数据的起始位置。

连接数过小

索引相关的原因我们聊完了，我们来聊聊，除了索引之外，还有哪些因素会限制我们的查询速度的。

我们可以看到，mysql的server层里有个连接管理，它的作用是管理客户端和mysql之间的长连接。

正常情况下，客户端与server层如果只有一条连接，那么在执行sql查询之后，只能阻塞等待结果返回，如果有大量查询同时并发请求，那么后面的请求都需要等待前面的请求执行完成后，才能开始执行。

因此很多时候我们的应用程序，比如go或java这些，会打印出sql执行了几分钟的日志，但实际上你把这条语句单独拎出来执行，却又是毫秒级别的。 这都是因为这些sql语句在等待前面的sql执行完成。

怎么解决呢？

如果我们能多建几条连接，那么请求就可以并发执行，后面的连接就不用等那么久了。

而连接数过小的问题，受数据库和客户端两侧同时限制。

数据库连接数过小

mysql的最大连接数默认是100, 最大可以达到16384。

可以通过设置mysql的max_connections参数，更改数据库的最大连接数。

mysql> set global max_connections= 500;
Query OK, 0 rows affected (0.00 sec)

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

上面的操作，就把最大连接数改成了500。

应用侧连接数过小

数据库连接大小是调整过了，但貌似问题还是没有变化？还是有很多sql执行达到了几分钟，甚至超时？

那有可能是因为你应用侧（go，java写的应用，也就是mysql的客户端）的连接数也过小。

应用侧与mysql底层的连接，是基于TCP协议的长链接，而TCP协议，需要经过三次握手和四次挥手来实现建连和释放。如果我每次执行sql都重新建立一个新的连接的话，那就要不断握手和挥手，这很耗时。所以一般会建立一个长连接池，连接用完之后，塞到连接池里，下次要执行sql的时候，再从里面捞一条连接出来用，非常环保。

我们一般写代码的时候，都会通过第三方的orm库来对数据库进行操作，而成熟的orm库，百分之一千万都会有个连接池。

而这个连接池，一般会有个大小。这个大小就控制了你的连接数最大值，如果说你的连接池太小，都还没有数据库的大，那调了数据库的最大连接数也没啥作用。

一般情况下，可以翻下你使用的orm库的文档，看下怎么设置这个连接池的大小，就几行代码的事情，改改就好。比如go语言里的gorm里是这么设置的

func Init() {
  db, err := gorm.Open(mysql.Open(conn), config)
  sqlDB, err := db.DB()
  // SetMaxIdleConns 设置空闲连接池中连接的最大数量
  sqlDB.SetMaxIdleConns(200)
  // SetMaxOpenConns 设置打开数据库连接的最大数量
  sqlDB.SetMaxOpenConns(1000)
}

buffer pool太小

连接数是上去了，速度也提升了。

曾经遇到过面试官会追问，有没有其他办法可以让速度更快呢？

那必须要眉头紧锁，假装思考，然后说：有的。

我们在前面的数据库查询流程里，提到了进了innodb之后，会有一层内存buffer pool，用于将磁盘数据页加载到内存页中，只要查询到buffer pool里有，就可以直接返回，否则就要走磁盘IO，那就慢了。

也就是说，如果我的buffer pool 越大，那我们能放的数据页就越多，相应的，sql查询时就更可能命中buffer pool，那查询速度自然就更快了。

可以通过下面的命令查询到buffer pool的大小，单位是Byte。

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

也就是128Mb。

如果想要调大一点。可以执行

mysql> set global innodb_buffer_pool_size = 536870912;
Query OK, 0 rows affected (0.01 sec)

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

这样就把buffer pool增大到512Mb了。

但是吧，如果buffer pool大小正常，只是别的原因导致的查询变慢，那改buffer pool毫无意义。

但问题又来了。

怎么知道buffer pool是不是太小了？

这个我们可以看buffer pool的缓存命中率。

通过 show status like 'Innodb_buffer_pool_%';可以看到跟buffer pool有关的一些信息。

Innodb_buffer_pool_read_requests表示读请求的次数。

Innodb_buffer_pool_reads 表示从物理磁盘中读取数据的请求次数。

所以buffer pool的命中率就可以这样得到：

buffer pool 命中率 = 1 - (Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%

比如我上面截图里的就是，1 - (405/2278354) = 99.98%。可以说命中率非常高了。

一般情况下buffer pool命中率都在99%以上，如果低于这个值，才需要考虑加大innodb buffer pool的大小。

当然，还可以把这个命中率做到监控里，这样半夜sql变慢了，早上上班还能定位到原因，就很舒服。

还有哪些骚操作？

前面提到的是在存储引擎层里加入了buffer pool用于缓存内存页，这样可以加速查询。

那同样的道理，server层也可以加个缓存，直接将第一次查询的结果缓存下来，这样下次查询就能立刻返回，听着挺美的。

按道理，如果命中缓存的话，确实是能为查询加速的。但这个功能限制很大，其中最大的问题是只要数据库表被更新过，表里面的所有缓存都会失效，数据表频繁的更新，就会带来频繁的缓存失效。所以这个功能只适合用于那些不怎么更新的数据表。

另外，这个功能在8.0版本之后，就被干掉了。所以这功能用来聊聊天可以，没必要真的在生产中使用啊。

总结

• 数据查询过慢一般是索引问题，可能是因为选错索引，也可能是因为查询的行数太多。
• 客户端和数据库连接数过小，会限制sql的查询并发数，增大连接数可以提升速度。
• innodb里会有一层内存buffer pool用于提升查询速度，命中率一般>99%，如果低于这个值，可以考虑增大buffer pool的大小，这样也可以提升速度。
• 查询缓存（query cache）确实能为查询提速，但一般不建议打开，因为限制比较大，并且8.0以后的mysql里已经将这个功能干掉了。

最后

最近原创更文的阅读量稳步下跌，思前想后，夜里辗转反侧。

我有个不成熟的请求。

离开广东好长时间了，好久没人叫我靓仔了。

大家可以在评论区里，叫我一靓仔吗？

我这么善良质朴的愿望，能被满足吗？

如果实在叫不出口的话，能帮我点下右下角的点赞和在看吗？

【相关推荐：mysql视频教程】

以上がインデックス以外に、mysql クエリを遅くする要因は何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。