一覧検索と分類ごとの件数取得を効率的に行いたい

Question

###実現したいこと
まずは画面UI上で実現したいことを説明します。

一覧画面で一覧検索と合わせて、サイドバーに件数を表示したいです。

例えばサイドバーには国と承認済み・未承認タグ、およびそれぞれの件数が表示されているとします。
（□はチェックボックス）
ーーーーー
□日本　１０件
□米国　４件
□中国　４件

□承認済み　１４件
□未承認　４件
ーーーーー

日本をチェックすると以下の通り、
承認済み、未承認は日本に絞られた件数で再検索されます。（一覧も再検索されます。）
ーーーーー
■日本　１０件　←ここをチェックすると
□米国　４件
□中国　４件

□承認済み　７件　←件数が変わる（日本１０件のうち承認済みが７件と言う意味）
□未承認　３件　　←件数が変わる（日本１０件のうち未承認が３件と言う意味）
ーーーーー

###準備したもの
取得元のテーブルは以下の通りです。
・一覧テーブル

id	国	承認フラグ	その他一覧情報
１	日本	承認済み	〜
２	米国	未承認	〜

###現状の実現方法
上記を実現するために以下のSQL実行しています。

-- 一覧を表示するためのSQL
select
  その他一覧情報
from
  一覧テーブル
where
  国 in (チェックされた国) and
  承認フラグ in (チェックされた承認フラグ) and
  (その他一覧情報の絞り込み)

-- 国の件数を表示するためのSQL
select
  国，count(*)
from
  一覧テーブル
where
  承認フラグ in (チェックされた承認フラグ) and
  (その他一覧情報の絞り込み)
group by
  国

-- 承認フラグの件数を表示するためのSQL
select
  承認フラグ，count(*)
from
  一覧テーブル
where
  国 in (チェックされた国) and
  (その他一覧情報の絞り込み)
group by
  承認フラグ

###現状の実現方法の課題
実際には上記以上にサイドバーのタグが複雑であること、データ量が多いことが原因で、
性能的にかなり問題が出ています。

###試したこと

• 1つのSQLにまとめる

→思いつきませんでした。

• 件数テーブルを別で準備

→その他一覧情報の条件ごとに持つ必要があり、その中には全文検索も含まれるので不可

• マルチスレッドなど並列処理

→PHPのため、困難と判断

###質問
上記実現したいことを性能問題を回避して実現するためにはどのようなアプローチがありますか。
過去の事例や世の中にあるプロダクトでどのように扱っているかアドバイスいただけないでしょうか。
方針レベルでも思いつく点があれ

ば教えていただけますと幸いです。

最後に自分で思いつくところを列挙します。

• ElasticsearchやRedisの利用

　→実現できるか不明

• Ajaxで件数を非同期取得

　→根本解決にはならない気がしている

###環境

• MySQL5.7
• PHP7.2

※上記、現行環境ですが、あまりこだわりません。

よろしくお願いします。

Answer 1

■日本　１０件　←ここをチェックすると

□米国　５件
□中国　５件

□承認済み　７件　←件数が変わる

□未承認　３件　　←件数が変わる

上記、もし未チェック分を表示する処理ならチェックしたもの以外を検索するので

select カテゴリ,count(*) from tbl where not 国 in('日本')

のような処理になります

sample

• 送り側

※今回は面倒なのでjQueryで

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script>
$(function(){
  $('[name="country[]"]').on('change',function(){
    $.ajax({
    url:"recv.php",
    data:$('[name="country[]"]:checked').map(function(){
      return "country[]="+encodeURIComponent($(this).val());
    }).get().join("&"),
    }).done(function(data){
      console.log(data);
      /* 実際にはここで承認済み、非承認の件数を書き換える */
    });
  });
});
</script>

<div><label><input type="checkbox" name="country[]" value="日本">日本</label>：10件</div>
<div><label><input type="checkbox" name="country[]" value="米国">米国</label>：4件</div>
<div><label><input type="checkbox" name="country[]" value="中国">中国</label>：4件</div>

<ul>
<li>承認済み:<span id="shouninzumi">0</span>件
<li>未承認:<span id="mishounin">0</span>件
</ul>

• 受け側(recv.php)

<?PHP
$country=filter_input(INPUT_GET,"country", FILTER_DEFAULT , FILTER_REQUIRE_ARRAY);
$sql ="select カテゴリ,count(*) as 件数from tbl where 1 ";
$data=[];
if(count((array) $country)>0){
  $sql.="and 国 in (".implode(",",array_fill(0,count($country),"?")).")";
  $data=$country;
}
$sql.=" group by カテゴリ";
print $sql.";\n";
print_r($data);
/* 実際には$sqlをprepareで処理してカテゴリごとの件数をjsonで返すのがベター */

DB設計について

集計のロジックを変えます
テーブルはこんな感じになります

create table tbl(id int primary key,国 varchar(10),承認 varchar(10),その他 varchar(10));
insert into tbl values 
( 1,'日本','承認済み','その他1'),
( 2,'日本','承認済み','その他1'),
( 3,'日本','承認済み','その他2'),
( 4,'日本','承認済み','その他2'),
( 5,'日本','承認済み','その他2'),
( 6,'日本','承認済み','その他2'),
( 7,'日本','承認済み','その他3'),
( 8,'日本','未承認','その他1'),
( 9,'日本','未承認','その他2'),
(10,'日本','未承認','その他2'),
(11,'米国','承認済み','その他1'),
(12,'米国','承認済み','その他1'),
(13,'米国','承認済み','その他1'),
(14,'米国','未承認','その他3'),
(15,'中国','承認済み','その他1'),
(16,'中国','承認済み','その他2'),
(17,'中国','承認済み','その他3'),
(18,'中国','承認済み','その他2');

• 絞り込みなし

select '国' as カテゴリ,国 as 要素,sum(1) as 件数 from tbl group by 国
union all select '承認' as カテゴリ,承認 as 要素,sum(1) as 件数 from tbl group by 承認
union all select 'その他' as カテゴリ,その他 as 要素,sum(1) as 件数 from tbl group by その他

• 国が'日本'

国の集計以外の件数集計にor条件を追加します

select '国' as カテゴリ,国 as 要素,sum(1) as 件数 from tbl group by 国
union all select '承認' as カテゴリ,承認 as 要素,sum(1 and (0 or 国 in('日本'))) as 件数 from tbl group by 承認
union all select 'その他' as カテゴリ,その他 as 要素,sum(1 and (0 or 国 in('日本'))) as 件数 from tbl group by その他

• 国が'日本'または承認が'未承認'

select '国' as カテゴリ,国 as 要素,sum(1 and (0 or 承認 in('未承認'))) as 件数 from tbl group by 国
union all select '承認' as カテゴリ,承認 as 要素,sum(1 and (0 or 国 in('日本'))) as 件数 from tbl group by 承認
union all select 'その他' as カテゴリ,その他 as 要素,sum(1 and (0 or 国 in('日本') or 承認 in('未承認'))) as 件数 from tbl group by その他

これを更に集計するとこう

select カテゴリ,group_concat(concat(要素,":",件数)) as 要素件数 from(
select '国' as カテゴリ,国 as 要素,sum(1 and (0 or 承認 in('未承認'))) as 件数 from tbl group by 国
union all select '承認' as カテゴリ,承認 as 要素,sum(1 and (0 or 国 in('日本'))) as 件数 from tbl group by 承認
union all select 'その他' as カテゴリ,その他 as 要素,sum(1 and (0 or 国 in('日本') or 承認 in('未承認'))) as 件数 from tbl group by その他
) as sub
group by カテゴリ

• 結果：

カテゴリ	要素件数
その他	その他2:6,その他3:2,その他1:3
国	中国:0,日本:3,米国:1
承認	承認済み:7,未承認:3

これを受けてjsonを返してやればよいでしょう

※注意
where句で絞り込みをすると0件のデータが表示されなくなります。
0と表示したいなら今回のようなsumで集計、
表示しなくてもいいなら(つまりjs側で例外処理が必要）where句で絞り込みをしてください

Comments

[love_engineerin]

ご回答ありがとうございます。
質問が悪かったので、すぐに直しますが、
欲しいのは、チェックされたものの件数です。
「日本」のうち「承認済み」が７件、「未承認」が３件と言う意味です。

というのと、こういったSQLをカテゴリごとに都度発行するのが性能劣化につながっている認識です。

[yambejp]

それはロジックがおかしい。
チェック済みを表示したいなら最初はすべてにチェックをいれるべき

[love_engineerin]

はい、厳密にはその通りです。
ただUI上，ユーザが絞り込みやすいように、初期表示はチェックを全て外しています。
チェックが全て外れている場合は全チェックと同じ扱いとするようにロジックを組んでいます。
今回の質問の意図と異なるところで混乱させてしまいすみません。

[yambejp]

一応指定された仕様どおりの流れを書いておきました
ユーザーが混乱しないように十分配慮してください

[love_engineerin]

詳細な記載ありがとうございます。
今まさに記載いただいた処理の流れで作っております。
（なお承認済み，未承認についてもチェックボックスのイメージです。）

一方でこの方法だと現状で３本のSQLが必要（本文記載の通り）ですし、
実際には「地域」「承認フラグ」以外のカテゴリもあるため、SQLが増えてしまいます。

このロジックではない方法で実現できないか教えていただけると助かります。
それが上記「質問」の意図でした。

[yambejp]

> 現状で３本のSQLが必要

多分考え方がおかしいか、仕様を正しく提示していないかだと思います
集計単位がちがうのですから、集計単位ごとにSQLを発行するしかありません

[yambejp]

DBの設計から見直しました、考え方を精査してみて下さい

[love_engineerin]

詳細にありがとうございます。大変参考になります。

いただいたSQLで実現可能なのですが、unionするのと複数回sqlを発行するのは、あまり性能差がないような気がします。

より具体的な性能の懸念点を説明させて下さい。
上記tblにcontentsというtext型のカラムがあると仮定します。

それをlike検索しようとすると

```
select '国' as カテゴリ,国 as 要素,sum(1) as 件数 from tbl where contents like '%あいうえお%' group by 国
union all select '承認' as カテゴリ,承認 as 要素,sum(1) as 件数 from tbl where contents like '%あいうえお%' group by 承認
union all select 'その他' as カテゴリ,その他 as 要素,sum(1) as 件数 from tbl where contents like '%あいうえお%' group by その他
```

となると思います。

そうすると一番コストがかかるであろうlike検索を同じ内容で３回走らせないといけません。
ですので、できるだけ1回で発行したいと考えております。

[yambejp]

なんどもいうようで申し訳ないですが、ロジックがおかしいので実現は基本的に不可能ですし
そもそもlikeで前方後方一致を書いている時点でスピード云々はあきらめてください
unionしているのはスピードのためではなく集計を一括するためですので
当然高速化はできません

[love_engineerin]

ロジックについては、初期表示時に全部チェックされている仕様と考えていただいて差し支えありません。
likeについては、便宜的にそのようにお伝えしましたが実際は性能に配慮してngramによる全文検索です。
（説明が不足しており申し訳ございません。）
SQLでの速度改善が難しいとわかったのでその点はよかったです。
ありがとうございます。

別のアプローチがあれば良いのですが、、

[yambejp]

たとえば、国=日本としたときに、米国、中国を0にしていいならやりようはありますよ
でもそうじゃないのですよね？
そうなると集計単位も検索条件もまとめようがないのできびしいです。

[love_engineerin]

国を日本にしたタイミングでは米国，中国を０とするのは良いです。
その場合は米国，中国の件数を変えないようにjqueryなどで制御すれば良いので。
ただ国に日本が選ばれている状態で、承認のチェックが変更された場合は、
承認の状態に合わせて米国，中国の件数も変更したいです。