MySQL 구조 MySQL의 SQL 튜닝을 하기 전에 MySQL가 어떤 구조로 동작하는지 알 필요가 있다. MySQL Connectors를 통해 SQL문을 보내게되면 간략히 아래와 같은 과정을 거친다. Parser를 통해 MySQL 엔진에서 문법 에러가 있는지, DB에 존재하는 테이블을 대상으로 SQL문을 작성했는지 검사한다. Optimizer를 통해 요청한 데이터를 빠르고 효율적으로 찾아가는 전략적 계획을 만든다. 계획을 토대로 스토리지 엔진에 위치한 데이터를 찾는다. 찾은 데이터에서 불필요한 부분을 필터링하고 필요한 연산을 수행한 뒤에 사용자에게 반환한다. 스토리지 엔진 InnoDB, MyISAM, Memory 등과 같은 스토리지 엔진은 사용자가 요청한 SQL 문을 토대로 DB에 저장된 디스크나 메모리에서 필요한 데이터를 가져오는 역할을 한다....
목표 record lock이 무엇인지 이해한다. 목적 index에 걸리는 락 SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE이고 c1에 대해서 인덱스가 걸려있는 경우에, 다른 트랜잭션에서 t.c1 = 10 인 값을 수정하지 않도록 막아야된다. UPDATE 쿼리를 수행할 때 c1 같은 secondary index로 조건을 건다면, innoDB에서 secondary index로 PK를 찾고, PK를 통해서 행을 조회하므로, c1 = 10과 그에 해당하는 모든 PK에 record lock이 걸린다. 참고 자료 https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-record-locks
목표 pagination 쿼리 최적화 방법을 이해한다. pagination을 사용중일 때 데이터 개수를 어떻게 구할지 이해한다. Pagination 최적화 방법 offset의 문제점 단순하게 pagination을 구현할 때, offset과 limit을 사용해서 구현한다. select * from news order by date desc, id desc limit 50 offset 4950; create index .. on news(date, id); 이 방식은 5000개의 데이터를 가져와서 4950개의 행을 버리는 방식이다. 페이지가 넘어갈수록 심각하게 느려지는 문제가 있다. 중간에 데이터가 추가되는경우, 다음 페이지네이션에 중복된 데이터가 보일 수 있다....
목표 새로 만든 MySQL 서버에 기존에 있던 MySQL 스키마를 그대로 가져오고 싶었다. 해결 방법 mysql에서 제공하는 mysql client에서 db 데이터를 dumping하는 기능을 제공하주고 있다. --no-data는 테이블 내에있는 row 들은 복제하지 않겠다는 옵션이다. mysqldump --user=<username> --host=<hostname> --password --no-data <original db> | mysql -u <user name> -p <new db> mysqldump와 관련된 추가적인 옵션들은 아래 링크에서 확인 가능하다. https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html 참고 자료 https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html https://dev.mysql.com/doc/refman/8.0/en/mysql-batch-commands.html https://www.prisma.io/dataguide/mysql/short-guides/exporting-schemas
https://mariadb.com/resources/blog/installing-mariadb-10-1-16-on-mac-os-x-with-homebrew/ 위 링크를 읽으면서 mariadb를 설치한 뒤, mariadb를 실행하니 다음과 같은 에러로 실행에 실패하는 문제가 생겼다. [ERROR] /usr/local/opt/mariadb/bin/mariadbd: unknown variable 'mysqlx-bind-address=127.0.0.1' /usr/local/etc/my.cnf 파일에 들어가서 저 부분을 주석처리한 뒤, 재실행하니 해결되었다. 참고 자료 https://stackoverflow.com/questions/58245118/warning-the-post-install-step-did-not-complete-successfully-when-trying-to-ins https://mariadb.com/resources/blog/installing-mariadb-10-1-16-on-mac-os-x-with-homebrew/
인덱스는 정의되어 있는 순서가 중요하다. 만약 인덱스에 정의된 순서가 col1, col2, col3, col4 라고 정의되어 있다면 아래와 같다. GROUP BY col1 # 사용 가능 GROUP BY col1, col2 # 사용 가능 GROUP BY col1, col2, col3 # 사용 가능 GROUP BY col1, col2, col3, col4 # 사용 가능 GROUP BY col2, col2 # 사용 불가능 GROUP BY col1, col3, col2 # 사용 불가능 GROUP BY col1, col2, col3, col4, col5 # 사용 불가능 WHERE 절이 있다고하면 그 다음 컬럼부터 GROUP BY에 있다면 사용 가능하다....
MySQL은 GROUP BY 사용시 자동으로 GROUP BY 열에 맞춰 정렬이 발생한다. ORDER BY NULL 를 추가해서 정렬을 하지 않도록 할 수 있다. 참고 자료 https://dba.stackexchange.com/questions/208166/group-by-needs-order-by-null-to-avoid-filesort
문자열을 비교할 때 어떤 규칙으로 비교하는지는 collation 설정에 따라 달렸다. mysql 기준으로 자주 사용하는 collation은 다음이 있다. utf8_general_ci utf8_unicode_ci utf8_bin 특수문자를 사용한다면 아래중 하나를 사용해야된다. utf8mb4_general_ci utf8mb4_unicode_ci ut8_bin의 경우는 A < B < a < b로 처리된다. utf8_general_ci는 정렬할 때는 A < a < B < b로 처리되고, ‘a’ = ‘A’는 참이 나온다. 참고 자료 https://sshkim.tistory.com/128 https://m.blog.naver.com/writer0713/221806591790