[MySQL] PK없는 테이블 빠르게 찾기

PK가 없는 테이블에 관심을 가지는 이유

디폴트 엔진인 innodb의 테이블은 오라클의 클러스터링 테이블와 유사한 구조를 지닌다.

기본적으로 사용자가 생성하지 않더라도 auto_increment 속성의 hidden PK가 생성된다.

이럴꺼면 명시적으로 생성해서 추후 활용하는 것이 나은 것 같다.

특히 PK가 없는 테이블에서 대량의 변경작업이 발생할 경우 secondary장비에서 복제지연이 발생하는 경우를 간헐적으로 경험할 수 있었기에 주기적으로 모니터링하여 생성을 유도하고 있다.

PK없는 테이블 찾기

SELECT a.table_schema,
       a.table_name
FROM information_schema.tables a
         LEFT JOIN (SELECT table_schema,
                           table_name
                    FROM information_schema.table_constraints
                    WHERE constraint_type = 'PRIMARY KEY') b ON (
            a.table_schema = b.table_schema
        AND a.table_name = b.table_name
    )
WHERE a.table_schema NOT IN (
                             'information_schema',
                             'db_helper',
                             'mysql',
                             'performance_schema',
                             'sys'
    )
  AND b.table_name IS NULL
  AND a.table_type = 'BASE TABLE'
ORDER BY table_schema,
         table_name;

결과셋

+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| howknow      | meter_aggr |
| howknow      | meter_raw  |
+--------------+------------+

PRIMARY KEY 찾기

SELECT table_schema,
       table_name
FROM information_schema.table_constraints
WHERE constraint_schema NOT IN (
                                'information_schema',
                                'db_helper',
                                'mysql',
                                'performance_schema',
                                'sys'
    )
  AND constraint_type IN ('PRIMARY KEY');

FOREIGN KEY 찾기

SELECT table_schema,
       table_name
FROM information_schema.table_constraints
WHERE constraint_schema NOT IN (
                                'information_schema',
                                'db_helper',
                                'mysql',
                                'performance_schema',
                                'sys'
    )
  AND constraint_type IN ('FOREIGN KEY');