꿈꾸는 DB 전문가

date_format(date , format) 

date_format 함수 사용시 date 에는 반드시 MySQL 인식할 수 있는 날짜형식을 사용해야 한다.

기본적인 날자 형식은

"20220716"

"2022-07-16"

"2022-07-16 09:00:00"  특히 시간정보를 표시할때는 " : "  이 있어야 시간정보로 인식한다. 

select date_format('2022071619', '%Y%m%d%H'); -- >Incorrect datetime value: '2022071619' 

 "NULL"

 
show warnings ;

select date_format('2022-07-16 19', '%Y%m%d%H');

"2022071619"

select * from information_schema.partitions

where table_name = '테이블명' ;

https://idea-sketch.tistory.com/47?category=547413 

SHOW PROCESSLIST ;

KILL 255;

참고사이트 

m.blog.naver.com/PostView.nhn?blogId=bsos1202&logNo=221003933887&proxyReferer=https:%2F%2Fwww.google.com%2F

select * from city ;
select * from country;

 explain format = 'json'

-- explain
select * from city c join country cy
on c.country_id = cy.country_id 
where city_id between 1 and 10
;
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "5.76"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "c",
          "access_type": "range",
          "possible_keys": [
            "PRIMARY",
            "idx_fk_country_id"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "city_id"
          ],
          "key_length": "2",
          "rows_examined_per_scan": 10,
          "rows_produced_per_join": 10,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "1.26",
            "eval_cost": "1.00",
            "prefix_cost": "2.26",
            "data_read_per_join": "2K"
          },
          "used_columns": [
            "city_id",
            "city",
            "country_id",
            "last_update"
          ],
          "attached_condition": "(`c`.`city_id` between 1 and 10)"
        }
      },
      {
        "table": {
          "table_name": "cy",
          "access_type": "eq_ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "country_id"
          ],
          "key_length": "2",
          "ref": [
            "sakila.c.country_id"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 10,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "2.50",
            "eval_cost": "1.00",
            "prefix_cost": "5.76",
            "data_read_per_join": "2K"
          },
          "used_columns": [
            "country_id",
            "country",
            "last_update"
          ]
        }
      }
    ]
  }
}

5.7

SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t2

WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);

SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2

WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);

SELECT id, a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2;

SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ a FROM t1);

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...

SELECT /*+ QB_NAME(qb1) MRR(@qb1 t1) BKA(@qb2) NO_MRR(@qb3t1 idx1, id2) */ ...

FROM (SELECT /*+ QB_NAME(qb2) */ ... FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

SELECT /*+ BKA(@`my hint name`) */ ... FROM (SELECT /*+ QB_NAME(`my hint name`) */ ...) ...

SELECT /*+ BKA(@"my hint name") */ ... FROM (SELECT /*+ QB_NAME("my hint name") */ ...) ...

docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.BestPractices.html#Aurora.BestPractices.HashJoin

적용할 수 없는 MySQL 파라미터 및 상태 변수

Aurora MySQL과 MySQL 간의 아키텍처 차이 때문에 일부 MySQL 파라미터와 상태 변수는 Aurora MySQL에 적용되지 않습니다.

다음 MySQL 파라미터는 Aurora MySQL에 적용되지 않습니다.

• innodb_adaptive_flushing

• innodb_adaptive_flushing_lwm

• innodb_change_buffering

• innodb_checksum_algorithm

• innodb_doublewrite

• innodb_flush_method

• innodb_flush_neighbors

• innodb_io_capacity

• innodb_io_capacity_max

• innodb_log_buffer_size

• innodb_log_file_size

• innodb_log_files_in_group

• innodb_max_dirty_pages_pct

• innodb_use_native_aio

• innodb_write_io_threads

• thread_cache_size

다음 MySQL 상태 변수는 Aurora MySQL에 적용되지 않습니다.

• innodb_buffer_pool_bytes_dirty

• innodb_buffer_pool_pages_dirty

• innodb_buffer_pool_pages_flushed

오로라 사용 설명서 

docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.html#AuroraMySQL.Reference.Parameters.Inapplicable

downloads.percona.com/downloads/percona-toolkit/3.3.0/binary/tarball/percona-toolkit-3.3.0_x86_64.tar.gz