개발자의 일상

1. 가끔 분산 DB, DB통합 등을 고려해서 auto inc가 아니라 uuid 등으로 PK를 생성해야하는 경우가 존재
2. 이때 몇가지를 고려해서 코드레벨에서 준비해야하는게 있었는데, mysql 8기준으로 이제는 native function에서 지원이 잘됨

관련해서 잘 정리된 글이 있어서 메모 목적으로 링크

핵심 부분은 'UUID_TO_BIN(UUID(),1)' 를 이용해서 uuid ver 1으로 생성된 uuid에서, 시간 관련된 필드를 조정 & 저장 용량을 줄이기 위해서 BINARY(16)로 저장
 - Clustered Index를 사용하는 Mysql이 index 조정 오버헤드를 줄이기 위해서

-- 테이블 생성 : UUID_TO_BIN(UUID(),1)
create table tb_test3 ( 
uuid binary(16) default (UUID_TO_BIN(UUID(),1)) primary key, 
first_name varchar(15), emp_no int unsigned)
engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci;


-- 데이터 입력
insert into tb_test3(first_name,emp_no) values ('Georgi',10001);
insert into tb_test3(first_name,emp_no) values ('Mary',10002);
insert into tb_test3(first_name,emp_no) values ('Saniya',10003);
insert into tb_test3(first_name,emp_no) values ('Sumant',10004);
insert into tb_test3(first_name,emp_no) values ('Duangkaew',10005);
insert into tb_test3(first_name,emp_no) values ('Patricio',10006);


-- 데이터 조회
mysql> select hex(uuid),first_name,emp_no
from tb_test3;
+----------------------------------+------------+--------+
| hex(uuid)                        | first_name | emp_no |
+----------------------------------+------------+--------+
| 11ED4F5A366CD3C3A20708002722A50F | Georgi     |  10001 |
| 11ED4F5A89BD4D38A20708002722A50F | Mary       |  10002 |
| 11ED4F5A8ED8C0C8A20708002722A50F | Saniya     |  10003 |
| 11ED4F5C16AAD10DA20708002722A50F | Sumant     |  10004 |
| 11ED4F5D5B56C1E8A20708002722A50F | Duangkaew  |  10005 |
| 11ED4F5E2CDE8B13A20708002722A50F | Patricio   |  10006 |
+----------------------------------+------------+--------+

개인 서버의 mysql을 8.x로 버전업하면서 간략하게 정리한 내용입니다.

메모 목적으로 정리한거라서 생략된 부분이 조금 있습니다.

#설치가능한 MySQL 8.0 저장소 확인
https://dev.mysql.com/downloads/repo/yum/

#MySQL 8.0 저장소 설치
yum install https://dev.mysql.com/get/mysql80-community-release-el7-9.noarch.rpm


# MySQL 8.0을 설치
yum install mysql-server


#설치된 MySQL 버전 확인
mysqld -V

#MySQL 시작 및 자동 실행 등록
systemctl start mysqld
systemctl enable mysqld


#초기 비밀번호 확인
grep 'temporary password' /var/log/mysqld.log


#비밀번호 변경(위 초기 비밀번호 확인 후 해당 값으로 접속)
mysql -u root -p  로 mysql 콘솔 접속 후

아래 명령어로 비밀번호 변경
ALTER USER 'root'@'localhost' IDENTIFIED BY '비밀번호';


#아래는 필요시 mysql 서버 설정 수정 사항들
vi /etc/my.cnf 후 아래 내용을 [mysqld] 영역에 추가

bind-address = 0.0.0.0

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect=SET collation_connection = utf8mb4_unicode_ci
init_connect=SET NAMES utf8mb4


이후 아래 명령어로 mysql서버 재 시작
systemctl restart mysqld


#계정 생성 및 권한 추가 관련 기타
# MySQL 8에서는 5와 다르게 계정 생성과 DB 권한 부여를 각각 해줘야함
# 참고로 기존 5버전과 동일한 방식으로 계정을 만들고 싶으면 'mysql_native_password' 방식으로 만들어야함. 잘못하면 로그인 힘듬

mysql> create user '계정ID'@'%' identified by '비밀번호' ;
mysql> grant all privileges on DB이름.* to '계정ID'@'%' with grant option;
mysql> flush privileges;

# 기존방식
mysql> create user '계정ID'@'%' identified WITH mysql_native_password by '비밀번호' ;

#샘플
create user '계정'@'%' identified by '암호블라블라' ;
grant all privileges on test_db.* to '계정'@'%' with grant option;
flush privileges;

#참고. MySQL에서 caching_sha2_password 을 mysql_native_password으로 플러그인을 변경
ALTER user '유저명'@'localhost' IDENTIFIED WITH mysql_native_password by '비밀번호';


#참고: root 계정을 모든 IP에서 접근허용 처리(보안 조심)
GRANT ALL PRIVILEGES ON *.* to 'root'@'%';

#결과 확인 용 쿼리
#SELECT Host,User,plugin,authentication_string FROM mysql.user;

mysql> use mysql 후에 아래 명령어로 확인
mysql> SELECT host, user, plugin, LEFT(authentication_string,15) AS password, password_last_changed FROM user ;


#기타: virtualbox 등에 설치해서 방화벽으로 차단되고 있다면 내리거나 허용
# 방화벽 중지
systemctl disable firewalld
systemctl stop firewalld

1. 목적

 - Mysql은 날짜/시간과 관련된 여러 데이터 타입을 제공하는데, 글로벌 서비스를 개발시 여러 시간대 정보가 포함되었을때를 위해서 DB데이터 타입에 대한 확인

2. 확인을 위한 테스트 및 결과

-- 테스트 데이터 저장용 테이블 생성
CREATE TABLE `timezone_test` (
  `datetime` DATETIME DEFAULT NULL COMMENT 'datetime필드',
  `date` DATE DEFAULT NULL COMMENT 'date필드',
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'timestamp필드',
  `int_unixTS` INT(10) UNSIGNED DEFAULT NULL COMMENT 'unix TS를 저장하기 위한 int(고정값 저장)'
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='타임존 테스트';

-- 테스트 데이터 insert
INSERT INTO timezone_test VALUES (NOW(), NOW(), NOW(), UNIX_TIMESTAMP());

-- 타임존 변경 전 저장된 데이터 확인
SELECT t.*, UNIX_TIMESTAMP(t.datetime) AS convertUNIX_TS FROM timezone_test AS t;

-- 현재 타임존 정보 확인
SELECT @@GLOBAL.time_zone, @@SESSION.time_zone, @@system_time_zone;

-- 세션의 타임존 변경(예. +1시로 변경)
SET time_zone='+01:00';

-- 타임존 정보 변경 후 현재시간 확인하여 변경되었는지 확인
SELECT NOW();

-- 타임존 변경 후 저장된 값의 내용 조회
-- datetime, date필드는 DB타임존이 변경되어도 기존 값이 변경안됨, timestamp는 DB내부적으로 UTC로 저장 후 DB타임존 설정정보를 이용해서 변환하여 보여줌 -> 즉, timestmap만 타임존이 변경되었을때 영향을 받음
-- 여러 시간대를 사용한다면, 가능하면 timestamp타입으로 데이터를 저장하는게 좋음
SELECT t.*, UNIX_TIMESTAMP(t.datetime) AS convertUNIX_TS FROM timezone_test AS t;

3. 주의

 - timestamp는 2038년까지만 표시되기에 주의(예. admin시스템 만들때 무한이라는 미래값 처리할때 2999년 과 같은 값으로 처리 불가)

샘플: 저장된 동일 데이터를 DB타임존 변경 전과 후 비교

 - 타임존 변경 전

 - 타임존 변경 후

참고

 - https://dev.mysql.com/doc/refman/5.7/en/datetime.html

참고
 - 타임존 변환 사이트 : https://savvytime.com/converter/pdt-to-kst-utc/aug-1-2021/3am

보통은 검색엔진 elastic search로 검색기능을 개발합니다.

다만, 백오피스 or 사용자가 적음 or 검색엔진 도입하기에는 ROI가 맞지 않는 경우가 생각보다 많습니다.

이런 경우 개인적으로는 mysql n-gram으로 검색 기능을 많이 만듭니다.(일반적으로 RDB는 서비스에서 거의 필수로 사용)

관련하여 간단히 내용을 정리해둡니다.

• 개인 메모 목적으로 작성하는 글이라서 생략되는 부분이 많습니다. 구글을 검색해보면 저보다 더 자세히 정리해둔 분들도 많으니 누락된 부분은 해당글을 확인하거나 mysql 공식 문서를 확인해보세요.

1. N-gram이란?
   1. 전문 검색에서 보통 많이 쓰며 문자열에서 n개의 연속적인 단어 나열
   2. 예) abcd라는 문자
      1. N이 2라면 ab, bc, cd라는 인덱스
      2. N이 3이라면 abc, bcd라는 인덱스
2. 테이블 생성
   

   -- 테이블 생성
   CREATE TABLE `articles` (
     `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
     `title` VARCHAR(200) NOT NULL COMMENT '제목',
     `body` TEXT COMMENT '본문',
     PRIMARY KEY (`id`,`title`),
     FULLTEXT KEY `title` (`title`,`body`)
   ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='글'​

3. 사용 예
   

   -- 테스트 데이터 insert
   INSERT INTO articles(
   	title,
   	body
   ) 
   VALUES(
   	'국민연금 기금 적립금 900조원 돌파',
   	'25일 국민연금에 따르면 올 2분기 기금 적립금은 908조 3000억 원으로 집계됐다. 연금보험료(655조 4000억 원)와 운용 수익금(502조 3000억 원)을 합산한 금액에 연금급여(239조 6000억 원)와 관리 운영비(9조 8000억 원)를 제외한 게 적립금이다'
   )
   ;
   
   
   -- 쿼리 플랜 확인
   EXPLAIN
   SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('연금' IN NATURAL LANGUAGE MODE);​

1. n-gram token size
   1. InnoDB에서 n-gram의 최소 토큰 사이즈는 2
      

      

   2. 중국어와 같이 1글자도 처리하고 싶으면 1로 변경
      1. 단, DB에 부하가 올라가겠죠.
2. STOPWORD에 대해서
   1. 영어를 ngram으로 적용하였을 경우, a,for, to와 같은 검색결과 퀄리티를 떨어트리는 단어는 제외됨. 이를 stopword라고 함(참고 링크)
   2. 비 활성화 하고자 하면 innodb_ft_enable_stopword OFF
3. 추가 팁
   1. AWS 오로라 DB는 ngram_token_size 수정 불가 -> 그냥 mysql rds사용
      1. 개인적으로는 오로라 DB가 좋아서 여러 서비스에서 사용 중
      2. 오라라 DB의 엔진이 업데이트되어서 현재는 수정 가능해졌을 수도 있음

www.eversql.com

개인적인 생각과 비슷한 부분이 많아서 메모

참고 : alibaba mysql rules(with java) 

 - https://github.com/alibaba/Alibaba-Java-Coding-Guidelines#3-mysql-rules

Table Schema Rules

1. [Mandatory] Columns expressing the concept of True or False, must be named as is_xxx, whose data type should be unsigned tinyint (1 is True, 0 is False).

Note: All columns with non-negative values must be unsigned.

2. [Mandatory] Names of tables and columns must consist of lower case letters, digits or underscores. Names starting with digits and names which contain only digits (no other characters) in between two underscores are not allowed. Columns should be named cautiously, as it is costly to change column names and cannot be released in pre-release environment.

Positive example: getter_admin, task_config, level3_name
Counter example: GetterAdmin, taskConfig, level_3_name

3. [Mandatory] Plural nouns are not allowed as table names.

4. [Mandatory] Keyword, such as desc, range, match, delayed, etc., should not be used. It can be referenced from MySQL official document.

5. [Mandatory] The name of primary key index should be prefixed with pk_, followed by column name; Unique index should be named by prefixing its column name with uk_; And normal index should be formatted as idx_[column_name].

Note: pk means primary key, uk means unique key, and idx is short for index.

6. [Mandatory] Decimals should be typed as decimal. float and double are not allowed.

Note: It may have precision loss when float and double numbers are stored, which in turn may lead to incorrect data comparison result. It is recommended to store integral and fractional parts separately when data range to be stored is beyond the range covered by decimal type.

7. [Mandatory] Use char if lengths of information to be stored in that column are almost the same.

8. [Mandatory] The length of varchar should not exceed 5000, otherwise it should be defined as text. It is better to store them in a separate table in order to avoid its effect on indexing efficiency of other columns.

9. [Mandatory] A table must include three columns as following: id, gmt_create and gmt_modified.

Note: id is the primary key, which is unsigned bigint and self-incrementing with step length of 1. The type of gmt_createand gmt_modified should be DATE_TIME.

10. [Recommended] It is recommended to define table name as [table_business_name]_[table_purpose].

Positive example: tiger_task / tiger_reader / mpp_config

11. [Recommended] Try to define database name same with the application name.

12. [Recommended] Update column comments once column meaning is changed or new possible status values are added.

13. [Recommended] Some appropriate columns may be stored in multiple tables redundantly to improve search performance, but consistency must be concerned. Redundant columns should not be:
  1) Columns with frequent modification.
  2) Columns typed with very long varchar or text.

Positive example: Product category names are short, frequently used and with almost never changing/fixed values. They may be stored redundantly in relevant tables to avoid joined queries.

14. [Recommended] Database sharding may only be recommended when there are more than 5 million rows in a single table or table capacity exceeds 2GB.

Note: Please do not shard during table creation if anticipated data quantity is not to reach this grade.

15. [For Reference] Appropriate char column length not only saves database and index storing space, but also improves query efficiency.

Positive example: Unsigned types could avoid storing negative values mistakenly, but also may cover bigger data representative range.

Index Rules

1. [Mandatory] Unique index should be used if business logic is applicable.

Note: Negative impact of unique indices on insert efficiency is neglectable, but it improves query speed significantly. Additionally, even if complete check is done at the application layer, as per Murphy's Law, dirty data might still be produced, as long as there is no unique index.

2. [Mandatory] JOIN is not allowed if more than three tables are involved. Columns to be joined must be with absolutely similar data types. Make sure that columns to be joined are indexed.

Note: Indexing and SQL performance should be considered even if only 2 tables are joined.

3. [Mandatory] Index length must be specified when adding index on varchar columns. The index length should be set according to the distribution of data.

Note: Normally for char columns, an index with the length of 20 can distinguish more than 90% data, which is calculated by count(distinct left(column_name, index_length)) / count()*.

4. [Mandatory] LIKE '%...' or LIKE '%...%' are not allowed when searching with pagination. Search engine can be used if it is really needed.

Note: Index files have B-Tree's left most prefix matching characteristic. Index cannot be applied if left prefix value is not determined.

5. [Recommended] Make use of the index order when using ORDER BY clauses. The last columns of ORDER BY clauses should be at the end of a composite index. The reason is to avoid the file_sort issue, which affects the query performance.

Positive example: where a=? and b=? order by c; Index is: a_b_c 
Counter example: The index order will not take effect if the query condition contains a range, e.g., where a>10 order by b;Index a_b cannot be activated.

6. [Recommended] Make use of Covering Index for query to avoid additional query after searching index.

Note: If we need to check the title of Chapter 11 of a book, do we need turn to the page where Chapter 11 starts? No, because the table of contents actually includes the title, which serves as a covering index.
Positive example: Index types include primary key index, unique index and common index. Covering index pertains to a query effect. When refer to explain result, using index may appear in extra columns.

7. [Recommended] Use late join or sub-query to optimize scenarios with many pages.

Note: Instead of bypassing offset rows, MySQL retrieves totally offset+N rows, then drops off offset rows and returns N rows. It is very inefficient when offset is very big. The solution is either limiting the number of pages to be returned, or rewriting SQL statement when page number exceeds a predefined threshold.
Positive example: Firstly locate the required id range quickly, then join:
select a.* from table1 a, (select id from table1 where some_condition LIMIT 100000, 20) b where a.id=b.id;

8. [Recommended] The target of SQL performance optimization is that the result type of EXPLAIN reaches REF level, or RANGEat least, or CONSTS if possible.

Counter example: Pay attention to the type of INDEX in EXPLAIN result because it is very slow to do a full scan to the database index file, whose performance nearly equals to an all-table scan.
CONSTS: There is at most one matching row, which is read by the optimizer. It is very fast.
REF: The normal index is used.
RANGE: A given range of index are retrieved, which can be used when a key column is compared to a constant by using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, or IN() operators.

9. [Recommended] Put the most discriminative column to the left most when adding a composite index.

Positive example: For the sub-clause where a=? and b=?, if data of column a is nearly unique, adding index idx_a is enough.
Note: When equal and non-equal check both exist in query conditions, put the column in equal condition first when adding an index. For example, where a>? and b=?, b should be put as the 1st column of the index, even if column a is more discriminative.

10. [For Reference] Avoid listed below misunderstandings when adding index:
  1) It is false that each query needs one index.
  2) It is false that index consumes story space and degrades update, insert operations significantly.
  3) It is false that unique index should all be achieved from application layer by "check and insert".

SQL Rules

1. [Mandatory] Do not use COUNT(column_name) or COUNT(constant_value) in place of COUNT(*). COUNT(*) is SQL92 defined standard syntax to count the number of rows. It is not database specific and has nothing to do with NULL and non-NULL.

Note: COUNT(*) counts NULL row in, while COUNT(column_name) does not take NULL valued row into consideration.

2. [Mandatory] COUNT(distinct column) calculates number of rows with distinct values in this column, excluding NULL values. Please note that COUNT(distinct column1, column2) returns 0 if all values of one of the columns are NULL, even if the other column contains distinct non-NULL values.

3. [Mandatory] When all values of one column are NULL, COUNT(column) returns 0, while SUM(column) returns NULL, so pay attention to NullPointerException issue when using SUM().

Positive example: NPE issue could be avoided in this way:
SELECT IF(ISNULL(SUM(g)), 0, SUM(g)) FROM table;

4. [Mandatory] Use ISNULL() to check NULL values. Result will be NULL when comparing NULL with any other values.

Note: 
  1) NULL<>NULL returns NULL, rather than false.
  2) NULL=NULL returns NULL, rather than true.
  3) NULL<>1 returns NULL, rather than true.

5. [Mandatory] When coding on DB query with paging logic, it should return immediately once count is 0, to avoid executing paging query statement followed.

6. [Mandatory] Foreign key and cascade update are not allowed. All foreign key related logic should be handled in application layer.

Note: e.g. Student table has student_id as primary key, score table has student_id as foreign key. When student.student_id is updated, score.student_id update is also triggered, this is called a cascading update. Foreign key and cascading update are suitable for single machine, low parallel systems, not for distributed, high parallel cluster systems. Cascading updates are strong blocked, as it may lead to a DB update storm. Foreign key affects DB insertion efficiency.

7. [Mandatory] Stored procedures are not allowed. They are difficult to debug, extend and not portable.

8. [Mandatory] When correcting data, delete and update DB records, SELECT should be done first to ensure data correctness.

9. [Recommended] IN clause should be avoided. Record set size of the IN clause should be evaluated carefully and control it within 1000, if it cannot be avoided.

10. [For Reference] For globalization needs, characters should be represented and stored with UTF-8, and be cautious of character number counting.

Note: SELECT LENGTH("轻松工作"); returns 12.
SELECT CHARACTER_LENGTH("轻松工作"); returns 4.
Use UTF8MB4 encoding to store emoji if needed, taking into account of its difference from UTF-8.

11. [For Reference] TRUNCATE is not recommended when coding, even if it is faster than DELETE and uses less system, transaction log resource. Because TRUNCATE does not have transaction nor trigger DB trigger, problems might occur.

Note: In terms of Functionality, TRUNCATE TABLE is similar to DELETE without WHERE sub-clause.

ORM Rules

1. [Mandatory] Specific column names should be specified during query, rather than using *.

Note:

1. * increases parsing cost.
2. It may introduce mismatch with resultMap when adding or removing query columns.

2. [Mandatory] Name of Boolean property of POJO classes cannot be prefixed with is, while DB column name should prefix with is. A mapping between properties and columns is required.

Note: Refer to rules of POJO class and DB column definition, mapping is needed in resultMap. Code generated by MyBatis Generator might need to be adjusted.

3. [Mandatory] Do not use resultClass as return parameters, even if all class property names are the same as DB columns, corresponding DO definition is needed.

Note: Mapping configuration is needed, to decouple DO definition and table columns, which in turn facilitates maintenance.

4. [Mandatory] Be cautious with parameters in xml configuration. Do not use ${} in place of #{}, #param#. SQL injection may happen in this way.

5. [Mandatory] iBatis built in queryForList(String statementName, int start, int size) is not recommended.

Note: It may lead to OOM issue because its implementation is to retrieve all DB records of statementName's corresponding SQL statement, then start, size subset is applied through subList.
Positive example: Use #start#, #size# in sqlmap.xml.

Map<String, Object> map = new HashMap<String, Object>();  
map.put("start", start);  
map.put("size", size);  

6. [Mandatory] Do not use HashMap or HashTable as DB query result type.

7. [Mandatory] gmt_modified column should be updated with current timestamp simultaneously with DB record update.

8. [Recommended] Do not define a universal table updating interface, which accepts POJO as input parameter, and always update table set c1=value1, c2=value2, c3=value3, ... regardless of intended columns to be updated. It is better not to update unrelated columns, because it is error prone, not efficient, and increases binlog storage.

9. [For Reference] Do not overuse @Transactional. Because transaction affects QPS of DB, and relevant rollbacks may need be considered, including cache rollback, search engine rollback, message making up, statistics adjustment, etc.

10. [For Reference] compareValue of <isEqual> is a constant (normally a number) which is used to compared with property value. <isNotEmpty> means executing corresponding logic when property is not empty and not null. <isNotNull> means executing related logic when property is not null.

1. DDL

2. 결과
1. 최초 테이블 생성 결과
   
2. 파티션 추가