'DB Skill/MySQL' 카테고리의 글 목록 DB Library

MySQL DELETE JOIN with INNER JOIN

DB Skill/MySQL 2022. 10. 11. 18:12

Query 형태

Example

t1 테이블과 t2 테이블의 조건에 해당하는 행 모두 삭제 ( t1, t2 테이블 동시 삭제 )

t1 id값이 1인 행 삭제

21 id값이 1인 행 삭제

Delete 실행

결과

참고

https://www.mysqltutorial.org/mysql-delete-join/

저작자표시 비영리 변경금지

'DB Skill > MySQL' 카테고리의 다른 글

Rocky Linux 8 + mha 5.7 구성 (1)	2022.11.03
MySQL 성능 관련 Config 설정 (0)	2022.08.19
MySQL 페이징 처리 limit 사용시 COUNT(*) 쉽게 하기 - FOUND_ROWS() (0)	2022.03.17
[MySQL] InnoDB File format - Barracuda (0)	2022.01.14
[MySQL] Long Query / Transaction , Block Query, Lock ... (0)	2022.01.13

By SJinDBA

MySQL 성능 관련 Config 설정

DB Skill/MySQL 2022. 8. 19. 11:11

--- MEMORY ----

* innodb buffer pool size를 크게 가져갈수록 디스크 IO를 줄일수 있음
* 일반적으로 OS의 70&~80% 사용

--- CPU ----

* 하이퍼 스레딩 활성화
* 일반적으로 CPU보다는 memory 및 Disk IO를 올리는것이 성능 향상에 효과적
* MySQL 버전에 따른 코어수 제한
   - MySQL 5.1 : ~ 4코어
   - MySQL 5.5 : ~ 16코어
   - MySQL 5.6 : ~ 36 쓰레드(Core)
   - MySQL 5.7 : ~ 64 쓰레드(32 Core-HT)
   - MySQL 8.0 : ~ 100 쓰레드(48 Core-HT)

--- Disk ----

* SSD, NVMe 사용시 innodb_page_size=4K, innodb_flush_neighbors=0 로 사용을 추천
* innodb datadir, tmp 파일 및 undo 로그 모두 Ramdom IO가 발생함으로 SSD로 할당
* 로그(빈로그 등등)의 경우 Sequnential IO가 발생함으로 디스크로 할당
* innodb_io_capacity
   - innodb가 사용 가능한 I/O 대역폭
   - 너무 큰 값을 사용한다고 해서 많은 이점이 있지는 않음( 20000 이상은 권장되지 않음 )
   - 쓰기 작업이 많은 시스템은 높은 값이 유리 ( Ex LogDB )
   - 쓰기 작업이 적은 시스템은 낮은 값이 유리
* innodb_io_capacity_max
   - innodb가 사용 가능한 I/O 최대 대역폭
   - 일반적으로 innodb_io_capacity 값의 두배로 설정

--- OS ----

* MySQL은 Linux 에 최적화 되어 있다.
* MySQL 이외에 OS에서 설정을 해줘야되는 부분
   - ulimit -n, 파일 수 제한 (connections, open tables, ...)
   - ulimit -u, 스레드 수 제한 (connections, InnoDB background threads, event scheduler, ....)
* NUMA 기반 서버의 경우 innodb_numa_interleave를 1로 설정
   - mysql과 os의 numa 설정이 다른경우, 노드간 메모리 사용 불균형으로 swap이 발생하여 성능 이슈가 발생할 가능성이 있음.
* InnoDB를 사용하는 경우 OS의 파일시스템 캐시를 사용하지 않는다.( 더블 버퍼링을 막아 메모리를 효율적으로 사용 )
   - set inndbo_flush_method=O_DIRECT
   - innodb pool이 훨씬 고도화된 형태로 동작

--- ETC ---

* innodb_log_file_size(redo log 사이즈)
   - 크게 가져 갈수록 체크포인트 동작이 덜 필요하여 성능에 좋으나, 너무 크게 설정하는 경우 DB 복구 시간이 느려짐.
   - 운영서버 최소 512MB 이상 권장.
* innodb_log_files_in_group(리두 로그 파일 갯수)
   - Default는 2이지만 일반적으로 3으로 운영
* skip-name-resolve
   - client에서 server로 접속시 dns lookup 과정을 생략
   - dns 서버가 느리거나 장애 발생시 DB 접속문제가 발생하는 것을 해결할 수 있음.
* innodb_write_io_threads / innodb_read_io_threads
   - SHOW ENGINE INNODB STATUS 에서 지연된 쓰기/읽기 요청이 64개 이상인 경우 값을 올려주는것이 좋음.
* innodb_log_file_size
   - 로그 파일 크기
   - innodb_buffer_pool_size의 25% 정도로 유지
   - 이 값이 클수록 버퍼 풀에서 체크포인트 발생 빈도가 낮음.
   - 이 값이 클수록 크래시 발생시 복구가 그만큼 느려짐.

--- ACID 유지 ---

* innodb_flush_log_at_trx_commit = 1
   - commit마다 리두 로그에 기록하고 디스크에 flush
   - ACID가 보장이 되지만, 디스크 I/O가 높음
   - 성능 위주 DB로 설정하고 싶은 경우 0
* sync_binlog = 1
   - 트랜잭션이 커밋되기 전에 이진 로그를 디스크에 동기화
   - 정전 및 운영 체제 충돌에도 ACID가 보장되지만, 디스크 I/O가 높음
   - 성능 위주 DB로 설정하고 싶은 경우 0
* innodb_doublewrite = 1
   - innoDB의 페이지를 쓰기 전에 버퍼 풀에서 플러시된 페이지를 중복해서 저장
   - MySQL Crash 복구 중에 이중으로 쓰여진 버퍼에서 페이지 복사본을 찾아 복구 진행
   - 데이터 무결성을 유지할 수 있으나, 이중으로 데이터를 기록하여 I/O가 높음. (이중으로 기록 하지만 2배의 I/O가 발생하지는 않음)
   - 성능 위주 DB로 설정하고 싶은 경우 0

--- Buffer per Client Connections ---

* connection(세션)당 할당되는 버퍼들
   - read_buffer_size : Sequnential scan (full table scan)을 사용할 때 사용하는 버퍼
   - read_rnd_buffer_size : 정렬 작업후, 정렬된 순서대로 데이터를 다시 읽어 들일 때 사용하는 버퍼
   - join_buffer_size : 인덱스를 사용하지 않는 조인에 사용되는 버퍼, Session level에서 join 단위로 생성
   - sort_buffer_size : 인덱스를 사용하지 않는 정렬에 사용하는 버퍼
   - binlog_cache_size (if binary logginigs is enabled)
* connection이 많을 경우 이 버퍼들이 메모리를 점유하기 때문에 크게 세팅하면 안된다.
   - 만약 크게 필요한 경우가 있다면 필요한 세션에서만 크게 잡아서 사용한다.

참고

https://iamwhat.tistory.com/entry/MySQL-%ED%8F%AC%ED%8D%BC%EB%A8%BC%EC%8A%A4-%ED%8A%9C%EB%8B%9D

저작자표시 비영리 변경금지

'DB Skill > MySQL' 카테고리의 다른 글

Rocky Linux 8 + mha 5.7 구성 (1)	2022.11.03
MySQL DELETE JOIN with INNER JOIN (1)	2022.10.11
MySQL 페이징 처리 limit 사용시 COUNT(*) 쉽게 하기 - FOUND_ROWS() (0)	2022.03.17
[MySQL] InnoDB File format - Barracuda (0)	2022.01.14
[MySQL] Long Query / Transaction , Block Query, Lock ... (0)	2022.01.13

By SJinDBA

MySQL 페이징 처리 limit 사용시 COUNT(*) 쉽게 하기 - FOUND_ROWS()

DB Skill/MySQL 2022. 3. 17. 16:54

FOUND_ROWS 적용 전

페이징 처리를 위해 limit 사용 시 전체 게시물의 수를 알기 위해 쿼리를 한번 더 해야 한다.

1. 해당 조건을 만족하는 게시물의 전체 수 조회
2. 그 페이지에서 보여줄 내용 조회

FOUND_ROWS 적용 후

SQL_CALC_FOUND_ROWS & FOUND_ROWS

SELECT 최상단에 SQL_CALC_FOUND_ROWS 라는 옵션만 주고 FOUND_ROWS() 를 사용하면 된다.

쿼리는 마찬가지로 두번이지만 두 번째 쿼리는 훨씬 빠르다.

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_enchovy WHERE id < 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

첫번째 쿼리 검색 결과 : 10개

두번째 쿼리 카운트 값 : WHERE조건에 해당되는 Rows 카운트 ( 1~100사이에 누락된 번호가 없다면 99을 리턴 )

주의사항

1. SQL_CALC_FOUND_ROWS 키워드는 UNION의 첫 번째 SELECT에 나타나야 한다.
2. FOUND_ROWS()의 값은 UNION ALL을 사용하는 경우에만 정확합니다. ALL이 없는 UNION을 사용하면 중복 제거가 발생하고 FOUND_ROWS()의 값은 근사치만 된다.
3. UNION에 LIMIT가 없는 경우 SQL_CALC_FUND_ROWS는 무시되고 UNION을 처리하기 위해 생성된 임시 테이블의 행 수를 반환한다.

4. 복제시) FOUND_ROWS() 은 statement-based 형태로는 안정적으로 복제되지 않습니다, 이경우 row-based 형태로 자동 변경되어 복제가 됩니다.

※ MySQL 4.7.16 이전 버전에는 버그가 있어, 원하는 결과를 리턴하지 않음

* 5.7.16 이전 버전의 Query중 SybQuery가 있다면, SubQuery 내의 결과 Row 수를 FOUND_ROWS()로 Retrun
* 5.7.16 버그 픽스 이후는 전체쿼리문을 하나의 구문으로 보고 결과 ROW수를 FOUND_ROWS()로 return
FOUND_ROWS() 함수의 기능은 이전 SELECT 된 쿼리의 ROWS를 RETURN 하는 함수(즉, 5.7버전의 기능이 정확)

FOUND_ROWS() 함수는 MySQL 8.0.17에서 더 이상 사용되지 않으며 향후 버전의 MySQL에서 제거될 것으로 예상된다..

참고

https://string.tistory.com/93

저작자표시 비영리 변경금지

'DB Skill > MySQL' 카테고리의 다른 글

MySQL DELETE JOIN with INNER JOIN (1)	2022.10.11
MySQL 성능 관련 Config 설정 (0)	2022.08.19
[MySQL] InnoDB File format - Barracuda (0)	2022.01.14
[MySQL] Long Query / Transaction , Block Query, Lock ... (0)	2022.01.13
[MySQL] AUTO_INCREMENT 사용법 (0)	2021.12.31

By SJinDBA

[MySQL] InnoDB File format - Barracuda

DB Skill/MySQL 2022. 1. 14. 01:29

MySQL 성능 최적화 작업 중 하나로 DISK I/O를 줄이는 방법중 하나

파일 포멧을 Barracuda로 설정

Barracuda 이전의 파일 포멧은 Antelope에는 Row 포멧이 'Compact'만 제공되었으나,

Barracuda 파일 포멧에서는 'Compact'이외에 'Dynamic','Compressed'이 추가됨.(기본은 'Compact')

Row 포맷에 따라 가변 길이 컬럼(VARCHAR, TEXT, BLOB)의 처리와 압축 기능 사용이 결정된다.
InnoDB에서는 VARCHAR, TEXT, BLOB을 완전히 동일한 타입으로 인식하고 처리하는 점 참고하자.

Compact	가변 길이 컬럼의 767 byte 까지만 B-Tree에 저장하고, 나머지는 Off-Page에 저장
Dynamic	Page 사이즈와, Row 사이즈를 고려하여, 길이가 긴 컬럼들은 전체를 Off-Page에 저장하되, B-Tree에는 20 byte의 포인터만 저장
Compressed	Dynamic과 거의 유사하지만 KEY_BLOCK_SIZE 옵션을 통해 16KB의 데이터 Page를 압축 가능 (KEY_BLOCK_SIZE = 1, 2, 4, 8, 16 기본값 : 8) KEY_BLOCK_SIZE = 16인 경우 기본적으로 데이터 Page를 압축하지 않지만, VARCHAR, TEXT, BLOB 컬럼만 선택적으로 압축

기존 Compact Row 포맷에서 VARCHAR, TEXT, BLOB 컬럼을 많이 포함하고 있는 테이블의 경우, 데이터 Page가 VARCHAR, TEXT, BLOB 데이터들로 채워져서, Page 내 의미있는 데이터의 밀도가 낮아지고 Range 스캔시 읽어야 할 Page가 많아져 성능 저하를 일으키는 문제가 있었다.
하지만 Barracuda 파일 포맷에서는 이런 테이블들을 선택적으로 Dynamic 또는 Compressed로 변경해서 I/O를 최적화 할 수 있는 장점이 있다.

my.cnf

innodb_file_per_table
innodb_file_format=barracuda

동적으로 MySQL 서버에 설정

SET GLOBAL innodb_file_format=barracuda;
SET GLOBAL innodb_file_per_table=ON;

테이블 생성시 압축 옵션 추가

CREATE TABLE tblTest_compressed

(

id int(11) NOT NULL AUTO_INCREMENT,

aa int(11) DEFAULT NULL,

bb varchar(100) DEFAULT NULL,

PRIMARY KEY (id)

)

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

참고

http://jason-heo.github.io/mysql/2014/03/05/innodb-barracuda.html

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=seuis398&logNo=70094098699

저작자표시 비영리 변경금지

'DB Skill > MySQL' 카테고리의 다른 글

MySQL 성능 관련 Config 설정 (0)	2022.08.19
MySQL 페이징 처리 limit 사용시 COUNT(*) 쉽게 하기 - FOUND_ROWS() (0)	2022.03.17
[MySQL] Long Query / Transaction , Block Query, Lock ... (0)	2022.01.13
[MySQL] AUTO_INCREMENT 사용법 (0)	2021.12.31
[MySQL] regexp 복잡한 패턴 매칭 (0)	2021.12.31

By SJinDBA

[MySQL] Long Query / Transaction , Block Query, Lock ...

DB Skill/MySQL 2022. 1. 13. 00:51

MySQL Blocking Query

Version 5.7

select blocking.trx_mysql_thread_id                       as blocking_processlist_id
      ,max(proc.USER)                                          as blocking_user
      ,max(proc.HOST)                                          as blocking_host
      ,max(proc.DB)                                            as blocking_db
      ,max(ifnull(blocking.trx_query, 'Null'))                 as blocking_query
      ,max(TIMESTAMPDIFF(second, blocking.trx_started, now())) as blocking_started_sec
      ,max(ifnull(blocking.trx_started, 'Null'))               as blocking_started_time
      ,max(blocking.trx_state)                                 as blocking_state
      ,'Null'                                             as blocking_command
      ,max(concat(blocking.trx_id, ' (', ifnull(waiting.blocking_lock_id, 'Null'), ')')) as trx_id
      ,max(ifnull(lock_info.lock_mode, 'Null'))                                          as lock_mode
      ,max(ifnull(lock_info.lock_type, 'lock')) as lock_type
      ,if(max(waiting_info.trx_started is not null)
      ,concat('\nlock waiting into : ', count(*)
      ,substring(group_concat('\n\t'
                 ,'waiting ', now() - waiting_info.trx_started, '(sec) : '
                 ,'query -> ', waiting_info.trx_query
                  order by waiting_info.trx_started), 1, 1000))
      ,'\nlock waiting into : Null') as trx_info
from information_schema.INNODB_TRX blocking
left join information_schema.INNODB_LOCK_WAITS waiting on waiting.blocking_trx_id = blocking.trx_id
left join information_schema.INNODB_TRX waiting_info on waiting_info.trx_id = waiting.requesting_trx_id
left join information_schema.INNODB_LOCKS lock_info on lock_info.lock_trx_id = blocking.trx_id
inner join information_schema.processlist proc on proc.id = blocking.trx_mysql_thread_id
where blocking.trx_state = 'RUNNING'
and blocking.trx_requested_lock_id is null
and blocking.trx_wait_started is null
group by blocking.trx_mysql_thread_id ;

MySQL Status Check Query

Version 5.7 & 8.0

select concat('LongQuery', '::', count(*)) as current_value
from information_schema.processlist proc
where proc.command not in ('sleep', 'daemon', 'binlog dump', 'killed')
and proc.id <> connection_id()
and proc.time > 10
union all
select concat('LongTransaction', '::', count(*)) as current_value
from information_schema.innodb_trx trx
where TIMEDIFF(now(), trx.trx_started) > '00:01:00'
union all
select concat('MetaLock', '::', count(*)) as current_value
from performance_schema.metadata_locks ml
where ml.lock_status = 'PENDING'
union all
select concat('LockWait', '::', count(*)) as current_value
from information_schema.INNODB_TRX trx
where trx.trx_state = 'LOCK WAIT'
and trx.trx_wait_started <= date_sub(now(), interval 3 second) ;

MySQL Long Query

Version 5.7 & 8.0

select proc.time, proc.command, proc.id
     , proc.user, proc.host, proc.db, proc.info
from information_schema.processlist proc
where proc.command not in ('sleep', 'daemon', 'binlog dump', 'killed')
and proc.id <> connection_id()
and proc.time > 10
order by proc.time desc ;

MySQL Long Transaction Query

Version 5.7 & 8.0

select trx_mysql_thread_id as 'id'
     , proc.user
     , proc.host
     , proc.command
     , trx.trx_state
     , concat(trx_started, '') as 'start_time'
     , concat(TIMEDIFF(now(), trx_started), '') as 'diff_time'
     , ifnull(trx_query, '.......') as 'query_info'
     , ( select ifnull(esc.SQL_TEXT, '.......')
         from performance_schema.threads thread
       , performance_schema.events_statements_current esc
         where thread.processlist_id = trx.trx_mysql_thread_id
         and esc.thread_id = thread.thread_id) as 'query_info2'
from information_schema.innodb_trx trx
    ,information_schema.processlist proc
where proc.id = trx.trx_mysql_thread_id
and trx.trx_query is null
and TIMEDIFF(now(), trx.trx_started) > '00:01:00'
order by trx_started ;

MySQL Meta Lock Query

Version 5.7 & 8.0

select th.thread_id as 'blocking_thread_id'
     , th.processlist_id as 'blocking_id'
     , th.processlist_user as 'blocking_user'
     , th.processlist_host as 'blocking_host'
     , th.processlist_db as 'blocking_db'
     , th.processlist_command as 'blocking_command'
     , th.processlist_time as 'blocking_time'
     , ifnull(th.processlist_state, '---') as 'blocking_state'
     , ifnull(th.processlist_info, ifnull((select esc.sql_text
                                           from performance_schema.events_statements_current esc
                                           where esc.thread_id = th.thread_id), '---')) as 'blocking_query'
     , (select group_concat(concat('(id : ', waiting.processlist_id, ', waiting : ', waiting.processlist_time, ' sec) -> ', processlist_info)
        order by waiting.processlist_time desc)
        from performance_schema.threads waiting
        where waiting.processlist_state = 'Waiting for table metadata lock') as 'waiting_query'
from (select granted.owner_thread_id
            ,count(*) as cnt
      from performance_schema.metadata_locks pending
          ,performance_schema.metadata_locks granted
      where pending.lock_status   = 'PENDING'
      and granted.object_type   = pending.object_type
      and granted.object_schema = pending.object_schema
      and granted.object_name   = pending.object_name
      and granted.owner_thread_id != pending.owner_thread_id
      and granted.lock_status   = 'GRANTED'
      group by granted.owner_thread_id
      order by 2 desc
      limit 1) as metalock_parent
     ,performance_schema.threads th
where th.thread_id = metalock_parent.owner_thread_id
and th.processlist_time >= 3 ;

MySQL Lock Wait Query

Version 8.0

select TIMESTAMPDIFF(second, trx_waiting.trx_started, now()) as 'waiting_started_sec'
     , concat(trx_waiting.trx_started, '') as 'waiting_started_time'
     , trx_waiting.trx_state as 'waiting_state'
     , waiting_thread.processlist_command as 'waiting_command'
     , waiting_thread.processlist_id as 'waiting_processlist_id'
     , waiting_thread.processlist_user as 'waiting_user'
     , waiting_thread.processlist_host as 'waiting_host'
     , waiting_thread.processlist_db as 'waiting_db'
     , waiting_thread.processlist_info as 'waiting_query'
     , TIMESTAMPDIFF(second, blocking_thread_time.trx_started, now()) as 'blocking_started_sec'
     , concat(blocking_thread_time.trx_started, '') as 'blocking_started_time'
     , blocking_thread_time.trx_state as 'blocking_state'
     , blocking_thread.processlist_command as 'blocking_command'
     , blocking_thread.processlist_id as 'blocking_processlist_id'
     , blocking_thread.processlist_user as 'blocking_user'
     , blocking_thread.processlist_host as 'blocking_host'
     , blocking_thread.processlist_db as 'blocking_db'
     , ifnull(blocking_thread.processlist_info
           , (select esc.SQL_TEXT
              from performance_schema.events_statements_current esc
              where esc.thread_id = blocking_thread.thread_id)) as 'blocking_query'
from information_schema.innodb_trx trx_waiting
    ,performance_schema.data_lock_waits dlw
    ,performance_schema.threads waiting_thread
    ,performance_schema.threads blocking_thread
    ,information_schema.innodb_trx blocking_thread_time
    -- performance_schema.events_statements_current
where trx_waiting.trx_state = 'LOCK WAIT'
and trx_waiting.trx_wait_started <= date_sub(now(), interval 3 second)
and cast(dlw.requesting_engine_transaction_id as char charset utf8mb4) = trx_waiting.trx_id
and waiting_thread.thread_id = cast(dlw.requesting_thread_id as char charset utf8mb4)
and blocking_thread.thread_id = cast(dlw.blocking_thread_id as char charset utf8mb4)
and blocking_thread_time.trx_id = cast(dlw.BLOCKING_ENGINE_TRANSACTION_ID as char charset utf8mb4) ;

저작자표시 비영리 변경금지

'DB Skill > MySQL' 카테고리의 다른 글

MySQL 페이징 처리 limit 사용시 COUNT(*) 쉽게 하기 - FOUND_ROWS() (0)	2022.03.17
[MySQL] InnoDB File format - Barracuda (0)	2022.01.14
[MySQL] AUTO_INCREMENT 사용법 (0)	2021.12.31
[MySQL] regexp 복잡한 패턴 매칭 (0)	2021.12.31
Mysql Date and Time functions (0)	2021.12.10

By SJinDBA

[MySQL] AUTO_INCREMENT 사용법

DB Skill/MySQL 2021. 12. 31. 16:09

테이블 생성시 auto_increment 설정

CREATE TABLE people 
(
    number INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

AUTO_INCREMENT 확인

다음에 삽입될 Auto_increment(number 컬럼)의 값 확인

SHOW TABLE STATUS WHERE name = '테이블 이름';
SHOW TABLE STATUS LIKE '테이블 이름';

mysql> show table status where name = 'people' \G;
*************************** 1. row ***************************
           Name: _people
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 82
    Create_time: 2021-12-31 15:02:42
    Update_time: 2021-12-31 15:10:03
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

AUTO_INCREMENT 초기값 변경

AUTO_INCREMENT를 초기화(변경)할 때는 변경할 AUTO_INCREMENT의 값이 해당 컬럼에서 가장 큰 값보다 커야 합니다

ALTER TABLE 테이블 이름 AUTO_INCREMENT=변경할 숫자;

mysql> alter table _people auto_increment=5
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status where name = 'people' \G;
*************************** 1. row ***************************
           Name: _people
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 5
    Create_time: 2021-12-31 15:57:51
    Update_time: 2021-12-31 15:10:03
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

AUTO_INCREMENT 1부터 순서대로 정렬

레코드 삭제와 업데이트 등과 같은 이유로 AUTO_INCREMENT로 설정된 컬럼의 숫자가 난잡하게 될 수 있습니다. 이때 보기 좋게 1부터 순서대로 정렬할 수 있습니다.

SET @count=0;
UPDATE 테이블 이름 SET 컬럼 이름=@count:=@count+1;

mysql> SET @count=0;
mysql> UPDATE people SET number=@count:=@count+1;

# 데이터 정렬 후 AUTO_INCREMENT 값을 변경
ALTER TABLE 테이블 이름 AUTO_INCREMENT=변경할 숫자;

mysql> alter table people AUTO_INCREMENT=4;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

예제

AUTO_INCREMENT를 5로 변경

ex) ALTER TABLE people AUTO_INCREMENT=5;

// people 테이블의 AUTO_INCREMENT 값을 5로 변경

* 변경된 것을 확인(SHOW TABLE STATUS LIKE 'people';)

1) 데이터 삽입

ex) INSERT INTO people VALUES (NULL, "name_D");

Auto_increment를 5로 변경했기 때문에 number에 5가 삽입됩니다.

삽입을 완료하면 Auto_increment는 6이 됩니다.

다시 AUTO_INCREMENT를 4로 변경하려고 하더라도 오류는 발생하지 않지만 number 컬럼의 가장 큰 값이 5이기 때문에 Auto_increment의 값은 4로 변경되지 않고 6으로 되어있습니다.

* 현재 5보다 큰 숫자로만 변경 가능

2) 데이터 삭제

ex) DELETE FROM people WHERE number=5;

number가 5인 데이터를 삭제하면 number 컬럼에서 가장 큰 값이 3이기 때문에 Auto_increment를 4로 변경 가능합니다.

* 현재 3보다 큰 숫자로 변경 가능

* 참고

Auto_increment를 1로 초기화하려면 테이블의 데이터가 없어야 합니다.

참고

https://m.blog.naver.com/dldudcks1779/222006115309

저작자표시 비영리 변경금지

'DB Skill > MySQL' 카테고리의 다른 글

[MySQL] InnoDB File format - Barracuda (0)	2022.01.14
[MySQL] Long Query / Transaction , Block Query, Lock ... (0)	2022.01.13
[MySQL] regexp 복잡한 패턴 매칭 (0)	2021.12.31
Mysql Date and Time functions (0)	2021.12.10
Mysql like 조건에 역슬래쉬 포함하여 검색 (0)	2021.12.10

By SJinDBA

[MySQL] regexp 복잡한 패턴 매칭

DB Skill/MySQL 2021. 12. 31. 13:33

처음과 끝은 각각 ^과 $로 표현할 수 있어요. 예를 들어서, '^a'라면, a로 시작하는 패턴입니다. 그리고 'E$'이라면, E로 끝나는 패턴입니다. 그러면 '^eat$'은 무엇을 의미할까요? "eat"를 찾으라는 거겠네요. 그리고, .은 임의의 문자를 의미하는데요. '^...$'은, 길이가 3인 문자열이 패턴에 매칭된다는 이야기입니다.

이 쿼리를 수행해 봅시다. 그러면 문자열이 시작하고 임의의 문자가 3개 나오고 끝납니다. 그 말은, "Tom"이나 "Bob"도 matching이 된다는 소리일 거에요.

결과는, _name 필드의 문자열 길이가 3인 것들만 출력됩니다.

그런데, 우리는 _name이 모음으로 시작하는 것들을 구한다고 해 봅시다. LIKE를 쓰면 대충 10개의 조건절을 and로 연결하거나, 아니면 left 함수로 1개의 문자열을 가져온 것이, IN에 속해있는지. 등 조금 복잡하게 쿼리를 작성해야 할 거에요.

[...] 패턴은 ...에 속하는 문자 집합이 패턴에 걸린다는 의미인데요. 예를 들자면 이런 경우를 봅시다.

이 쿼리는 무엇을 의미할까요? 일단 문자열이 시작했다는 ^가 들어와 있고, []가 있네요. 안에 것을 보니까 aAeE가 있는데, 첫 문자의 가능 집합이 a, A, e, E라는 것입니다. 즉, _name 필드가 이 4개의 문자로 시작하는 레코드들을 모두 출력하라는 의미입낟.

쿼리의 결과는 다음과 같습니다. 그러면, a, A, e, E라는 문자가, 첫 번째에도 나오고 두 번째에도 나와야 하는 경우는 어떻게 작성해야 할까요? 이 때, {n}이라는 키워드를 씁니다. 해당 패턴이 n회 반복된다.

이 문장의 의미는, string이 시작하고 나서, a나 A나 e나 E가 2회 반복되어야 한다는 것입니다. 즉, aa~나 aA~나 ae~나 aE나, ... , EE가 해당 패턴에 걸릴 거에요. 그러면 저 셋 중에는, eat가 있는 필드만 걸리겠네요.

하핫. 만약에 1회 이상 2회 미만 그러한 패턴이 나타나는 패턴이 참이 되게 하고 싶다면 어떻게 하면 될까요? {n,m}을 쓰시면 됩니다. 이것은 패턴이 n회 이상, m회 이하 등장한다는 의미입니다. Arie, Asia, eat 셋 중에, 다음 패턴을 만족하는 것이 몇 개나 있을까요?

일단, a나 A나 e나 E가 나타나는 패턴이 최소 1회, 최대 1회 반복이 되고, 임의의 문자가 하나 나오면 A?나 A?나 e?나 E?가 저것과 매치가 될 거에요. 그러면, 저 셋 중에서 만족하는 것이 없어요. 그런데, 아래와 같은 경우에는 이야기가 달라집니다.

보시면 패턴이 1회 이상 2회 이하 반복되고, 임의의 문자 하나가 들어오고 끝나면 만족을 하는데요.

eat는 만족합니다. e가 aAeE 패턴에 만족하고, a 또한 aAeE 조건에 만족합니다. 2회 반복되었고, 다음에 t는 임의의 문자 중 하나입니다. 개행을 제외한. 따라서 eat가 있는 레코드만 출력이 됩니다. [^...]은 무엇을 의미할까요? ...에 속하지 않는 문자 집합을 의미합니다.

위 쿼리를 실행해 봅시다. 그러면 문자열이 시작하고, 첫 번째 문자가 a나 A가 아니여야 합니다. _name 필드가 그러한 것만 고르는 query입니다. 한 번 실행을 해 봅시다.

Asia랑 Anie는 빠졌음을 알 수 있어요.

*과 +이 남았네요. *은 패턴이 0번 이상, +은 1번 이상 나타난다는 것을 의미합니다. 예를 들어서, 다음과 같은 것을 생각해 봅시다.

이것은 문자열이 시작했을 때, A,a,E,e가 최소 0회 이상 나오는 패턴과 매칭이 됩니다.

실행 결과는 아래와 같이 나옵니다. 시작 문자가 어느 것이던 상관 없다는 겁니다. 그런데 +가 붙으면 이야기가 달라지는데요. 이것은 최소 1회 이상 문제의 패턴이 반복되어야 한다는 겁니다.

쿼리를 이렇게 작성해 봅시다.

그러면 A나 a나 E나 e로 시작하는 것들만 나올 거에요. 마지막으로, |가 있는데요. 이것은 or를 의미합니다.

다음 쿼리는 A로 시작하거나, B로 시작하는 패턴을 찾으라는 겁니다.

결과는 3개의 row가 리턴됩니다.

그러면 _name이 모음으로 시작하면서, 모음으로 끝나는 것들은 어떻게 구하면 좋을까요? 일단, 저는 다음과 같은 쿼리를 수행해 보겠습니다. 당연하게도, 길이가 1짜리면서 모음인 데이터를 추가한 겁니다.

다음에, 쿼리를 생각해 봅시다. 일단 문자열이 시작해야 하고, [aeiouAEIOU] 라는 pattern이 들어와야 할 겁니다. 다음에 임의의 문자가 0개 이상 오고, [aeiouAEIOU] 패턴이 온 다음에 끝나야 겠지요.

그러면, 이런 식으로 작성할 수 있을 겁니다. 그런데, 이 경우 길이가 1인 데이터는 매칭이 되지 않을 겁니다.

결과를 확인해 보면, "A"라는 것은 matched가 되지 않았음을 알 수 있어요. 그러면 어떻게 작성하면 좋을까요?

이렇게 작성하면 어떻게 수행이 될까요? 일단, 첫 패턴이 모음인지 검사합니다. 그 다음에 괄호 안으로 들어갈 건데요. 판단 state를 그려보면 아래와 같습니다.

보시면 모음이 나온 후에 바로 Terminate가 되거나, 아니면 임의의 문자가 0회 이상 나온 후에, 모음으로 끝나는 패턴만 regexp에 걸리게끔 하고 있어요. 조금 어려운 예제였습니다. 그러면, 모음으로 시작하거나, 모음으로 끝나는 건 어떻게 작성하면 좋을까요?

Conditions를 괄호로 묶고 |를 붙이면 됩니다. 어떤 조건인가요? ^[모음들]이거나, [모음들]$이겠지요. 이 둘을 괄호로 묶어준 다음에 |로 연결해 주시면 됩니다. 정규 표현식을 연습하는 사이트는 여러 곳이 있습니다. 거기에서 연습해 보셔도 좋을 듯 싶습니다.

참고

https://codingdog.tistory.com/entry/mysql-regexp-%EB%B3%B5%EC%9E%A1%ED%95%9C-%ED%8C%A8%ED%84%B4-%EB%A7%A4%EC%B9%AD%EC%9D%84-%ED%95%B4-%EB%B4%85%EC%8B%9C%EB%8B%A4

mysql regexp : 복잡한 패턴 매칭을 해 봅시다.

저번에 LIKE 연산자를 배웠습니다. 이번에는 조금 더 복잡한 패턴 매칭에 대해서 다뤄보겠습니다. [관련글] LIKE 연산자 알아봅시다. 눈치 채신 분도 있으실지 모르겠습니다. 자바에서 파싱할

codingdog.tistory.com

저작자표시 비영리 변경금지

'DB Skill > MySQL' 카테고리의 다른 글

[MySQL] Long Query / Transaction , Block Query, Lock ... (0)	2022.01.13
[MySQL] AUTO_INCREMENT 사용법 (0)	2021.12.31
Mysql Date and Time functions (0)	2021.12.10
Mysql like 조건에 역슬래쉬 포함하여 검색 (0)	2021.12.10
MySQL Query 결과 File로 저장 (0)	2021.07.09

By SJinDBA

Mysql Date and Time functions

DB Skill/MySQL 2021. 12. 10. 10:58

mysql 8.0 기준

Table 12.11 Date and Time Functions

NameDescription

ADDDATE()	Add time values (intervals) to a date value
ADDTIME()	Add time
CONVERT_TZ()	Convert from one time zone to another
CURDATE()	Return the current date
CURRENT_DATE(), CURRENT_DATE	Synonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIME	Synonyms for CURTIME()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP	Synonyms for NOW()
CURTIME()	Return the current time
DATE()	Extract the date part of a date or datetime expression
DATE_ADD()	Add time values (intervals) to a date value
DATE_FORMAT()	Format date as specified
DATE_SUB()	Subtract a time value (interval) from a date
DATEDIFF()	Subtract two dates
DAY()	Synonym for DAYOFMONTH()
DAYNAME()	Return the name of the weekday
DAYOFMONTH()	Return the day of the month (0-31)
DAYOFWEEK()	Return the weekday index of the argument
DAYOFYEAR()	Return the day of the year (1-366)
EXTRACT()	Extract part of a date
FROM_DAYS()	Convert a day number to a date
FROM_UNIXTIME()	Format Unix timestamp as a date
GET_FORMAT()	Return a date format string
HOUR()	Extract the hour
LAST_DAY	Return the last day of the month for the argument
LOCALTIME(), LOCALTIME	Synonym for NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP()	Synonym for NOW()
MAKEDATE()	Create a date from the year and day of year
MAKETIME()	Create time from hour, minute, second
MICROSECOND()	Return the microseconds from argument
MINUTE()	Return the minute from the argument
MONTH()	Return the month from the date passed
MONTHNAME()	Return the name of the month
NOW()	Return the current date and time
PERIOD_ADD()	Add a period to a year-month
PERIOD_DIFF()	Return the number of months between periods
QUARTER()	Return the quarter from a date argument
SEC_TO_TIME()	Converts seconds to 'hh:mm:ss' format
SECOND()	Return the second (0-59)
STR_TO_DATE()	Convert a string to a date
SUBDATE()	Synonym for DATE_SUB() when invoked with three arguments
SUBTIME()	Subtract times
SYSDATE()	Return the time at which the function executes
TIME()	Extract the time portion of the expression passed
TIME_FORMAT()	Format as time
TIME_TO_SEC()	Return the argument converted to seconds
TIMEDIFF()	Subtract time
TIMESTAMP()	With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
TIMESTAMPADD()	Add an interval to a datetime expression
TIMESTAMPDIFF()	Subtract an interval from a datetime expression
TO_DAYS()	Return the date argument converted to days
TO_SECONDS()	Return the date or datetime argument converted to seconds since Year 0
UNIX_TIMESTAMP()	Return a Unix timestamp
UTC_DATE()	Return the current UTC date
UTC_TIME()	Return the current UTC time
UTC_TIMESTAMP()	Return the current UTC date and time
WEEK()	Return the week number
WEEKDAY()	Return the weekday index
WEEKOFYEAR()	Return the calendar week of the date (1-53)
YEAR()	Return the year
YEARWEEK()	Return the year and week

참고

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_adddate

MySQL :: MySQL 8.0 Reference Manual :: 12.7 Date and Time Functions

12.7 Date and Time Functions This section describes the functions that can be used to manipulate temporal values. See Section 11.2, “Date and Time Data Types”, for a description of the range of values each date and time type has and the valid formats

dev.mysql.com

저작자표시 비영리 변경금지

'DB Skill > MySQL' 카테고리의 다른 글

[MySQL] AUTO_INCREMENT 사용법 (0)	2021.12.31
[MySQL] regexp 복잡한 패턴 매칭 (0)	2021.12.31
Mysql like 조건에 역슬래쉬 포함하여 검색 (0)	2021.12.10
MySQL Query 결과 File로 저장 (0)	2021.07.09
MySQL 계정 및 권한 관리 (0)	2021.07.06

By SJinDBA

Mysql like 조건에 역슬래쉬 포함하여 검색

DB Skill/MySQL 2021. 12. 10. 00:01

문제

MySQL의 like 조건에서 \가 포함된 경우 원하는 형태로 조회가 되지 않음

해결

조건

title 컬럼의 \m가 포함된 형태로 검색 ( 검색 내용에 \ 가 포함 )

1. \를 두개 더 붙인다

where title like '%\\\m%'


2. escape를 사용
   기본적으로 escape가 \로 설정되어 있음. 이걸 다른 문자로 변경(예제는 | 를 사용)

where title like '%|m%' escape '|'

참고

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=leokevin&logNo=220645049628

저작자표시 비영리 변경금지

'DB Skill > MySQL' 카테고리의 다른 글

[MySQL] regexp 복잡한 패턴 매칭 (0)	2021.12.31
Mysql Date and Time functions (0)	2021.12.10
MySQL Query 결과 File로 저장 (0)	2021.07.09
MySQL 계정 및 권한 관리 (0)	2021.07.06
MySQL GROUP_CONCAT 사용하여 여러 컬럼을 하나의 열로 출력 (0)	2021.06.09

By SJinDBA

« 2024/05 »
일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

'DB Skill/MySQL'에 해당되는 글 13건

MHA 특징

MHA 구성 조건

MySQL 설치

MHA 설치

rpm 다운로드 링크

mha 구성을 위해 필요한 rpm 설치

mha node 설치

mha manager 설치

MySQL 설정

MySQL 계정 생성

MySQL 설정 변경

Mysql binary File Copy

MySQL Backup 및 Restore

MySQL Replication 구성

OS 설정

유저 생성

유저 기간 만료 확인 및 수정

mha directory 생성

mhauser 권한 부여

mha user ssh 설정

VIP 설정

master_ip_failover 파일 생성

scripts/master_ip_failover 실행 권한 추가

conf 파일 추가

relay log 삭제 스크립트 추가

crontab 등록

perl 실행 권한 부여

MHA 설정 Test

masterha_check_ssh

masterha_check_repl

mha 실행

'DB Skill > MySQL' 카테고리의 다른 글

Query 형태

Delete 실행

결과

참고

'DB Skill > MySQL' 카테고리의 다른 글

--- MEMORY ----

--- CPU ----

--- Disk ----

--- OS ----

--- ETC ---

--- ACID 유지 ---

--- Buffer per Client Connections ---

참고

'DB Skill > MySQL' 카테고리의 다른 글

FOUND_ROWS 적용 전

FOUND_ROWS 적용 후

참고

'DB Skill > MySQL' 카테고리의 다른 글

MySQL 성능 최적화 작업 중 하나로 DISK I/O를 줄이는 방법중 하나

참고

'DB Skill > MySQL' 카테고리의 다른 글

MySQL Blocking Query

MySQL Status Check Query

MySQL Long Query

MySQL Long Transaction Query

MySQL Meta Lock Query

MySQL Lock Wait Query

'DB Skill > MySQL' 카테고리의 다른 글

테이블 생성시 auto_increment 설정

AUTO_INCREMENT 확인

AUTO_INCREMENT 초기값 변경

AUTO_INCREMENT 1부터 순서대로 정렬

예제

참고

'DB Skill > MySQL' 카테고리의 다른 글

참고

'DB Skill > MySQL' 카테고리의 다른 글

Table 12.11 Date and Time Functions

참고

'DB Skill > MySQL' 카테고리의 다른 글

문제

해결

참고

'DB Skill > MySQL' 카테고리의 다른 글

이수진(Lee Su Jin)'s Blog

Category

MyLink