refactor: noteテーブルのインデックス整理と配列カラムへのクエリでインデックスを使うように (#12993)

* Optimize note model index

* enhance(backend): ANY()をやめる (MisskeyIO#239)

* add small e2e test drive endpoint

---------

Co-authored-by: まっちゃとーにゅ <17376330+u1-liquid@users.noreply.github.com>

4 files changed, 9 insertions, 7 deletions

diff --git a/packages/backend/src/server/api/endpoints/drive/files/attached-notes.ts b/packages/backend/src/server/api/endpoints/drive/files/attached-notes.ts
index 14a13b09c9..7a0b8b4417 100644
--- a/packages/backend/src/server/api/endpoints/drive/files/attached-notes.ts
+++ b/packages/backend/src/server/api/endpoints/drive/files/attached-notes.ts
@@ -74,7 +74,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 			}
 
 			const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), ps.sinceId, ps.untilId);
-			query.andWhere(':file = ANY(note.fileIds)', { file: file.id });
+			query.andWhere(':file <@ note.fileIds', { file: [file.id] });
 
 			const notes = await query.limit(ps.limit).getMany();
 
diff --git a/packages/backend/src/server/api/endpoints/hashtags/users.ts b/packages/backend/src/server/api/endpoints/hashtags/users.ts
index 50aea79943..8302d2380f 100644
--- a/packages/backend/src/server/api/endpoints/hashtags/users.ts
+++ b/packages/backend/src/server/api/endpoints/hashtags/users.ts
@@ -6,6 +6,7 @@
 import { Inject, Injectable } from '@nestjs/common';
 import { Endpoint } from '@/server/api/endpoint-base.js';
 import type { UsersRepository } from '@/models/_.js';
+import { safeForSql } from "@/misc/safe-for-sql.js";
 import { normalizeForSearch } from '@/misc/normalize-for-search.js';
 import { UserEntityService } from '@/core/entities/UserEntityService.js';
 import { DI } from '@/di-symbols.js';
@@ -47,8 +48,9 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 		private userEntityService: UserEntityService,
 	) {
 		super(meta, paramDef, async (ps, me) => {
+			if (!safeForSql(normalizeForSearch(ps.tag))) throw new Error('Injection');
 			const query = this.usersRepository.createQueryBuilder('user')
-				.where(':tag = ANY(user.tags)', { tag: normalizeForSearch(ps.tag) })
+				.where(':tag <@ user.tags', { tag: [normalizeForSearch(ps.tag)] })
 				.andWhere('user.isSuspended = FALSE');
 
 			const recent = new Date(Date.now() - (1000 * 60 * 60 * 24 * 5));
diff --git a/packages/backend/src/server/api/endpoints/notes/mentions.ts b/packages/backend/src/server/api/endpoints/notes/mentions.ts
index 2317f8f7b2..323c6c946b 100644
--- a/packages/backend/src/server/api/endpoints/notes/mentions.ts
+++ b/packages/backend/src/server/api/endpoints/notes/mentions.ts
@@ -61,9 +61,9 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 
 			const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), ps.sinceId, ps.untilId)
 				.andWhere(new Brackets(qb => {
-					qb
-						.where(`'{"${me.id}"}' <@ note.mentions`)
-						.orWhere(`'{"${me.id}"}' <@ note.visibleUserIds`);
+					qb // このmeIdAsListパラメータはqueryServiceのgenerateVisibilityQueryでセットされる
+						.where(':meIdAsList <@ note.mentions')
+						.orWhere(':meIdAsList <@ note.visibleUserIds');
 				}))
 				// Avoid scanning primary key index
 				.orderBy('CONCAT(note.id)', 'DESC')
diff --git a/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts b/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts
index b00f5207d8..0d7aca6621 100644
--- a/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts
+++ b/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts
@@ -87,14 +87,14 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 			try {
 				if (ps.tag) {
 					if (!safeForSql(normalizeForSearch(ps.tag))) throw new Error('Injection');
-					query.andWhere(`'{"${normalizeForSearch(ps.tag)}"}' <@ note.tags`);
+					query.andWhere(':tag <@ note.tags', { tag: [normalizeForSearch(ps.tag)] });
 				} else {
 					query.andWhere(new Brackets(qb => {
 						for (const tags of ps.query!) {
 							qb.orWhere(new Brackets(qb => {
 								for (const tag of tags) {
 									if (!safeForSql(normalizeForSearch(tag))) throw new Error('Injection');
-									qb.andWhere(`'{"${normalizeForSearch(tag)}"}' <@ note.tags`);
+									qb.andWhere(':tag <@ note.tags', { tag: [normalizeForSearch(tag)] });
 								}
 							}));
 						}