count(*) が非常に遅いのはなぜですか?原因分析

count(*) が非常に遅いのはなぜですか?次の記事では、その理由を分析し、count(*) の実行プロセスについて説明します。

経験豊富な開発者のほとんどがこの問題に遭遇しており、関連する理由を理解しているはずだと思うので、この記事を書きたくありませんでしたが、最近、いくつかの技術的な問題が発生しているのを目にしました。フォローしている公開アカウントが関連記事をプッシュしています。本当に驚きました！

まず、パブリック アカウントの記事の結論に進みます:

• count(*): 処理を行わずにすべての行のデータを取得します。行数が 1 追加されます。
• count(1): すべての行のデータを取得します。各行の固定値は 1 (行数に 1 を加えたもの) です。
• count(id): id は主キーを表します。データのすべての行から id フィールドを解析する必要があります。id は NULL であってはならず、行数は 1 ずつ増加します。
• count (通常のインデックス列): すべての行のデータから通常のインデックス列を解析し、NULL かどうかを判断する必要があります。NULL でない場合、行番号は 1 になります。
• count (インデックスなし列): テーブル全体をスキャンしてすべてのデータを取得し、分析にインデックス付き列を追加せず、NULL かどうかを判断します。NULL でない場合、行数は次のようになります。 1.

結論: count(*) ≈ count(1) > count(id) > count (通常のインデックス列) > count (インデックスなし列)

I 必要ありませんそれを他人に明かしたくない、上記の結論は全くの屁理屈です。単なる個人的な妄想であり、検証する気もありませんし、実行計画を見てもそんなとんでもない結論は出ません。

これが複数の技術公開アカウントによって再投稿された記事だなんて信じられません。

以下のすべてのコンテンツは、

mysql 5.7 InnoDB エンジンの分析に基づいています。

Extension:

MyISAM クエリ条件がなく、単にテーブル内のデータの総数をカウントする場合、テーブル内の総行数が計算されるため、戻り値は非常に高速になります。サービス層によって取得される情報は正確ですが、InnoDB は単なる推定値です。

例

早速、まず例を見てみましょう。

次は、データ量が 100 万件のテーブルです。テーブル内のフィールドは比較的短く、全体のデータ量は大きくありません。

CREATE TABLE `hospital_statistics_data` (
  `pk_id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `id` varchar(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT '外键',
  `hospital_code` varchar(36) COLLATE utf8mb4_general_ci NOT NULL COMMENT '医院编码',
  `biz_type` tinyint NOT NULL COMMENT '1服务流程  2管理效果',
  `item_code` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '考核项目编码',
  `item_name` varchar(64) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '考核项目名称',
  `item_value` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '考核结果',
  `is_deleted` tinyint DEFAULT NULL COMMENT '是否删除 0否 1是',
  `gmt_created` datetime DEFAULT NULL COMMENT '创建时间',
  `gmt_modified` datetime DEFAULT NULL COMMENT 'gmt_modified',
  `gmt_deleted` datetime(3) DEFAULT '9999-12-31 23:59:59.000' COMMENT '删除时间',
  PRIMARY KEY (`pk_id`)
) DEFAULT CHARSET=utf8mb4  COMMENT='医院统计数据';

このテーブルの初期状態には、

クラスター化インデックスが 1 つだけあります。

以下はさまざまなインデックス状況の内訳であり、COUNT(*) の実行計画を見てみましょう。

1) クラスター化インデックスが 1 つしかない場合の実行プランを見てください。

EXPLAIN select COUNT(*) from hospital_statistics_data;

結果:

#実行プランの各パラメータの意味はこの記事の範囲外ですが、理解できるはずです。あなた自身。

ここでは、次の属性のみに注目してください。

• type: Index が表示され、インデックスが使用されていることを示します。

• key: PRIMARY は主キー インデックスを使用します。

• key_len: インデックスの長さは 8 バイトです。

ここには非常に重要な点があります。

count(*) はインデックス にも移動し、現在のケースではクラスター化インデックスが使用されます。

さて、下を見てみましょう。

2) 非クラスター化インデックス (セカンダリ インデックス) があります。

hospital_code インデックスをテーブルに追加します。

alter table hospital_statistics_data add index idx_hospital_code(hospital_code)

現時点では、テーブルには

primary key と hospital_code という 2 つのインデックスがあります。

同様に、再度実行します:

EXPLAIN select COUNT(*) from hospital_statistics_data;

結果:

##同様に、 type、key、key_len の 3 つのフィールドを確認します。

ちょっとした「

魔法

」を感じませんか? インデックスが新しく追加された

idx_hospital_code

に変更されるのはなぜですか。 結論を急がずに、次の状況を見てください。

3) ノンクラスタード インデックス (セカンダリ インデックス) が 2 つあります。

上記を踏まえて、セカンダリ インデックスをもう 1 つ追加します。

alter table hospital_statistics_data add index idx_biz_type(biz_type)

現時点でテーブルには、主キー、hospital_code、biz_type の 3 つのインデックスがあります。

同様に、実行:

EXPLAIN select COUNT(*) from hospital_statistics_data;

結果:

さらに混乱していますか? インデックスは... そして... .Changed.

は、新しく追加された idx_biz_type になります。

上記の変化がなぜ起こったのかについては説明しないで、以下の分析を続けましょう。

上記の 3 つのインデックスに基づいて、それぞれ

count(1)

、count(id)、count(index) を見てみましょう。 , count (インデックスなし)これら 4 つの状況と count(*) の実行計画の違いは何ですか。

カウント(1)

##カウント(id) サンプル テーブルの主キーは pk_id

です。

• count(index)

这里选取biz_type索引字段。

• count(无索引)

小结：

• count(index) 会使用当前index指定的索引。

• count(无索引) 是全表扫描，未走索引。

• count(1) , count(*), count(id) 一样都会选择idx_biz_type索引

看到这，你还觉得那些千篇一律的公众号文章的结论正确吗？

必要知识点

• mysql 分为service层和引擎层。

• 所有的sql在执行前会经过service层的优化，优化分为很多类型，简单的来说可分为成本和规则。

• 执行计划所反映的是service层经过sql优化后，可能的执行过程。并非绝对（免得有些人说我只看执行计划过于片面）。绝大多数情况执行计划是可信的。

• 索引类型分为聚簇索引和非聚簇索引（二级索引）。其中数据都是挂在聚簇索引上的，非聚簇索引上只是记录的主键id。

• 抛开数据内存，只谈数据量，都是扯淡。什么500w就是极限，什么2个表以上的join都需要优化了，什么is null不会走索引等，纯纯的放屁。

• 相信一点，编写mysql代码的人比，看此文章的大部分人都要优秀。他们会尽可能在执行前，对我这样菜逼写的乱七八糟的sql进行优化。

原因分析

其实原因非常非常简单，上面也说了，service层会基于成本进行优化。

并且，正常情况下，非聚簇索引所占有的内存要远远小于聚簇索引。所以问题来了，如果你是mysql的开发人员，你在执行count(*)查询的时候会使用那个索引？

我相信正常人都会使用非聚簇索引。

那如果存在2个甚至多个非聚簇索引又该如何选择呢？

那肯定选择最短的，占用内存最小的一个呀，在回头看看上面的实例，还迷惑吗。

同样都是非聚簇索引。idx_hospital_code的len是146字节；而idx_biz_type的len只有1。那还要选吗？

那为何count(*)走了索引，却还是很慢呢？

这里要明确一点，索引只是提升效率的一种方式，但不能完全的解决效率问题。count(*)有一个明显的缺陷，就是它要计算总数，那就意味着要遍历所有符合条件的数据，相当于一个计数器，在数据量足够大的情况下，即使使用非聚簇索引也无法优化太多。

官方文档：

InnoDBhandlesSELECT COUNT(*)andSELECT COUNT(1)operations in the same way. There is no performance difference.

简单的来说就是，InnoDB下 count(*) 等价于 count(1)

既然会自动走索引，那么上面那个所谓的速度排序还觉得对吗？ count(*)的性能跟数据量有很大的关系，此外最好有一个字段长度较短的二级索引。

拓展：

另外，多说一下，关于网上说的那些索引失效的情况，大多都是片面的，我这里只说一点。量变才能引起质变，索引的失效取决于你圈定数据的范围，若你圈定的数据量占整体数据量的比例过高，则会放弃使用索引，反之则会优先使用索引。但是此规则并不是完美的，有时候可能与你预期的不同，也可以通过一些技巧强制使用索引，但这种方式少用。

举个栗子：

通过上面这个表hospital_statistics_data，我进行了如下查询：

select * from hospital_statistics_data where hospital_code is not null;

此时这个sql会使用到hospital_code的索引吗？

这里也不卖关子了，若hospital_code只有很少一部分数据是null值，那么将不会走索引，反之则走索引。

原因就2个字：回表。

シュガー オレンジを買うようなものです。数キロしか買わない場合は、かごの中から最高のものを選ぶだけです。でも、もしあなたがカゴを買いたいと思ったら、上司はあなたに一つずつ選ばせるのではなく、一度に全部のカゴをくれると思います。もちろん、誰もが愚かではありません、そして彼らは皆、いくつかのカゴがあるに違いないことを知っていますかごの中には悪い果物。しかし、これが最も効率的であり、ボスの損失も少なくなります。

実行プロセス

「MySQL をルートから理解する」からの抜粋。 MySQL を体系的に学んだことがない人には、この本を読むことを強くお勧めします。

1. まずサーバー層でカウント変数を維持します

2. サーバー層は InnoDB エンジンに最初のレコードを要求します

3. InnoDB は最初のセカンダリ インデックスを見つけますレコードを取得してサーバー層に返します (注: この時点ではレコード数をカウントしているだけなので、テーブルを返す必要はありません)

4. COUNT 関数のパラメータは * であるため、 , MySQL は * を定数 0 として扱います。 0 は NULL ではないため、サーバー層は count 変数に 1 を追加します。

5. サーバー層は InnoDB に次のレコードを要求します。

6.InnoDB は、セカンダリ インデックス レコードの next_record 属性を通じて次のセカンダリ インデックス レコードを検索し、サーバー層に返します。

7. サーバー層はカウント変数に 1 を加算し続けます。

8. InnoDB がサーバー層に記録可能なメッセージを返さなくなるまで、上記のプロセスを繰り返します。

9. サーバー層は count 変数の最終値をクライアントに送信します。

まとめ

書き終わった後もかなり落ち込んでいたのですが、公開アカウントから得られる良い記事はどんどん少なくなり、今ではすっかり落ち込んでいます。知識に対してお金を払う、時代が来ました。

働き始めた頃がとても懐かしく、当時は時間をかけて毎朝公式アカウントの記事を読んでいましたが、今では広告ばかりになってしまいました。なぜ！

しかし、それが普通のことです。愛のために常に発電できる人はいません。

勉強するときはもっと本を読むことをお勧めしますが、基本的に本に書けるものはそれほど悪くありません。今、夜に検索できるのは同じ記事ばかりで、正しいか間違っているかはわかりません。オンライン

[関連する推奨事項: mysql ビデオ チュートリアル ]

以上がcount(*) が非常に遅いのはなぜですか?原因分析の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。