DBインデックスの利用

20135 ワード

開発ログ航海 troubleshoot TIL 9912週間航行 Spring テキストリンク

DBインデックスとは?

DBインデックスは,データベースの記憶領域をさらに利用し,テーブルの探索速度を向上させるためのデータ構造である.索引は翻訳して索引を残すという意味です.

これは,本の中で特定のキーワードがどのページにあるかを調べるときに見られるインデックスページと同じ原理であると考えられる.
インデックス・ページを表示すると、本の中で検索する内容のページを見つけることができます.DBインデックスを使用すると、テーブルの実行速度を速め、データをすばやく検索できます.
インデックスは良い方法です.

テーブルのロー数が多く、selectまたはupdateがinsertより多い場合、

indexを使用する列に重複値がないことを望む/非常に少ない場合は

である.
SQLはwhereセクションに影響します.
通常、検索時にプライマリ・キー「Id」を使用して検索する以外は(プライマリ・キーとして指定すると、一意のunclusted indexが自動的に生成されます).
select * from 'student' where 'student_name' = 'John Kim'
JPA query method: findAllByStudentName(String name)
上記のクエリー検索のように、特定のコラムを使用して検索することもできます.対応するコラム「student name」にインデックスを付けると、パフォーマンスが向上します.
なぜなら、インデックスに入ると、データが入る順序で完全なスキャンを行うのではなく、その場所を迅速に見つけることができるからです.同様の意味であるが、技術的には、インデックスのコラムは、クエリ要求時にO(n)の通常のスキャン速度よりも速いO(logn)のB−tree方式でスキャンすることができる.

プロジェクトに適用

現在のthandbagプロジェクトでは、JPAがデフォルトで提供するqueryメソッドに加えて、生成されるカスタムqueryメソッドは次のようになります.

    Page<Alarm> findAllByUserIdOrderByIdDesc(Long userId, Pageable pageable);
    
    void deleteAllByPostId(Long postId);
    
    Optional<ChatContent> findFirstByChatRoomOrderByCreatedAtDesc(ChatRoom chatRoom);
    
    List<ChatContent> findAllByChatRoomOrderByCreatedAtAsc(ChatRoom room);
    
    List<ChatContent> findAllByUserNotAndChatRoomAndIsRead(User user, ChatRoom chatRoom, Boolean isRead);
    
    List<ChatRoom> findAllByPubUserIdOrSubUserId(Long id, Long id2);
    
    ChatRoom findByPubUserIdAndSubUserId(Long id, Long id2);
    
    Boolean existsAllByPubUserIdAndSubUserId(Long id, Long id2);
    
    CommentLike findByUserIdAndComment(long userId, Comment comment);
    
    List<CommentLike> findAllByComment(Comment comment);
    
    boolean existsByCommentAndUserId(Comment comment, Long userId);
    
    @Query(value = "select p from Post p where p.share = true and (p.title like %:keyword% or p.content like %:keyword% or p.user in (select u from User u where u.nickname like %:keyword%))")
    Page<Post> findAllByShareTrueAndContainsKeywordForSearch(@Param("keyword") String keyword, Pageable pageable);
    
    List<Post> findAllByUser(User user);
    
    Page<Post> findAllByUserOrderByCreatedAtDesc(User user, Pageable pageable);
    
    Page<Post> findAllByShareTrueOrderByCreatedAtDesc(Pageable pageable);
    
    List<Post> findAllByShareTrueOrderByCreatedAtDesc();
    
    Optional<User> findByUsername(String username);
    
    Optional<User> findByNickname(String nickname);
    
    Optional<User> findByKakaoId(Long kakaoId);

このうち,インデックスに適したqueryメソッドとしては,以下の2つが考えられる.

Optional<User> findByUsername(String username);
    
Optional<User> findByNickname(String nickname);

検索クエリーでは、完全なスキャンが必要なため、インデックスは必要ありません.

    @Query(value = "select p from Post p where p.share = true and (p.title like %:keyword% or p.content like %:keyword% or p.user in (select u from User u where u.nickname like %:keyword%))")
    Page<Post> findAllByShareTrueAndContainsKeywordForSearch(@Param("keyword") String keyword, Pageable pageable);

次のクエリ・メソッドは、重複値が多すぎるため、基数または選択性の面で有効ではないと判断して除外されます.

    List<ChatRoom> findAllByPubUserIdOrSubUserId(Long id, Long id2);
    
    ChatRoom findByPubUserIdAndSubUserId(Long id, Long id2);
    
    Boolean existsAllByPubUserIdAndSubUserId(Long id, Long id2);
    Optional<User> findByKakaoId(Long kakaoId);

次のクエリは外部キーとして参照され、インデックスは生成され、除外されます.

    List<CommentLike> findAllByComment(Comment comment);
    
    boolean existsByCommentAndUserId(Comment comment, Long userId);
    
    Optional<ChatContent> findFirstByChatRoomOrderByCreatedAtDesc(ChatRoom chatRoom);
    
    List<ChatContent> findAllByChatRoomOrderByCreatedAtAsc(ChatRoom room);
    
    List<ChatContent> findAllByUserNotAndChatRoomAndIsRead(User user, ChatRoom chatRoom, Boolean isRead);

次の2つのクエリーは、主に私のページで有効性チェックやニックネームの変更に使用されます.

    Optional<User> findByUsername(String username);
    
    Optional<User> findByNickname(String nickname);

Insertに比べてselectやupdate構文が発生する回数が多いため、使用率の観点から「username」や「nickname」はインデックス化に適しており、以下に示すようにします.

@Entity
@Table(indexes = {@Index(name = "n_index", columnList = "nickname", unique = true),
        @Index(name = "u_index", columnList = "username", unique = true)})
public class User extends Timestamped {