'mysql'에 해당되는 글 11건

  1. 2016.06.16 Azure Linux VM에서 MySQL 성능 최적화
  2. 2013.10.28 보안 강도에 따른 해쉬함수 분류 ( sha2 512 in mysql, php )
  3. 2013.10.11 group_concat 중복제거하기
  4. 2013.09.27 MySQL 현재 접속자 보기 및 MYSQL 모니터링 명령어
  5. 2013.08.08 mysql 튜닝 최적화
  6. 2013.08.02 show variables
  7. 2013.04.27 [MySQL/명령어]REPAIR TABLE - 테이블을 복구한다.
  8. 2013.04.27 mysql 테이블과 인덱스 설계 시 주의사항 13가지 -- MySQL
  9. 2013.04.27 MySQL의 sock 에러
  10. 2013.04.27 MySQL Timestamp To DateTime

Azure Linux VM에서 MySQL 성능 최적화

mysql 2016.06.16 15:21


원문: 

https://azure.microsoft.com/ko-kr/documentation/articles/virtual-machines-linux-classic-optimize-mysql/



매개 변수


기본값



optmization

innodb_buffer_pool_size없음7G
innodb_log_file_size5M512M
max_connections1005000
innodb_file_per_table01
innodb_flush_log_at_trx_commit12
innodb_log_buffer_size8M128M
query_cache_size16M0




Trackback 0 : Comment 0

보안 강도에 따른 해쉬함수 분류 ( sha2 512 in mysql, php )

mysql 2013.10.28 15:09
[출처] KISA, 방송통신위원회 2012.9 개인정보의 기술적 관리적 보호조치 기준 해설서

보안강도

해쉬함수

안전성

80 비트 미만

MD5, SHA-1

권고하지 않음

80 비트

HAS-160

112 비트

SHA-224

2013년까지 권고

128 비트

SHA-256

2013년 이후에도 가능

192 비트

SHA-384

256 비트

SHA-512

sha2 512 in php 
echo hash('sha512', 'qwe123');

sha2 512 in mysql

 

SELECT SHA2('qwe123', 512);


sha2 512 in javascript


http://coursesweb.net/javascript/sha512-encrypt-hash_cs

 


Trackback 0 : Comment 0

group_concat 중복제거하기

mysql 2013.10.11 14:36


group by 로 그룹화 한 데이터의 내역을 확인 할 때 group_concat 을 사용합니다.

이때, 조회되는 데이터에서 중복을 정리하고자 할 때 DISTINCT 를 사용할 수 있습니다. 


ex)

select count(DISTINCT id), group_concat(DISTINCT id) from members group by name




Trackback 0 : Comment 0

MySQL 현재 접속자 보기 및 MYSQL 모니터링 명령어

mysql 2013.09.27 16:26
출처 : http://www.albumbang.com/board/board_view.jsp?board_name=free&no=139

가. 모니터링 및 초기화 명령어
  • show status - MySQL 데이타베이스의 현재 상황
  • show Processlist - MySQL 프로세스 목록
  • show variables - 설정 가능한 모든 변수 목록
  • flush logs - MySQL의 로그파일 초기화
  • flush status - MySQL 상태정보 초기화
  • flush thread - 쓰레드 캐시에 저장된 쓰레드 초기화
  • flush tables - MySQL에 캐싱된 테이블 초기화
  • flush privileges - 권한정보 재 설정
나. Connection 튜닝
1. status 
  • Aborted_clients - 클라이언트 프로그램이 비 정상적으로 종료된 수
  • Aborted_connects - MySQL 서버에 접속이 실패된 수
  • Max_used_connections - 최대로 동시에 접속한 수
  • Threads_cached - Thread Cache의 Thread 수
  • Threads_connected - 현재 연결된 Thread 수
  • Threads_created - 접속을 위해 생성된 Thread 수
  • Threads_running - Sleeping 되어 있지 않은 Thread 수
2. system variables
  • wait_timeout - 종료전까지 요청이 없이 기다리는 시간 ( TCP/IP 연결, Shell 상의 접속이 아닌 경우 )
  • thread_cache_size - thread 재 사용을 위한 Thread Cache 수로써, Cache 에 있는 Thread 수보다 접속이 많으면 새롭게 Thread를 생성한다.
  • max_connections - 최대 동시 접속 가능 수
그외에 status 또는 system variables 값은 참고의 Mysql 메뉴얼을 참조해 주십시요.
mysql> show variables like '%max_connection%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> show status like '%connect%';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| Aborted_connects     | 3782    |
| Connections          | 2961108 |
| Max_used_connections | 90      |
| Threads_connected    | 1       |
+----------------------+---------+
4 rows in set (0.01 sec)

mysql> show status like '%clients%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Aborted_clients | 2160  |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> show status like '%thread%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_insert_threads | 0     |
| Slow_launch_threads    | 0     |
| Threads_cached         | 7     |
| Threads_connected      | 1     |
| Threads_created        | 1364  |
| Threads_running        | 1     |
+------------------------+-------+
6 rows in set (0.00 sec)

Cache Miss Rate(%) =  Threads_created / Connections * 100
Connection Miss Rate(%) = Aborted_connects / Connections * 100
Connection Usage(%) = Threads_connected / max_connections * 100

위의 경우는 Cache Miss Rate(%) = 0.05%, Connection Miss Rate(%) = 0.12%, Connection Usage(%) = 1%
3. 튜닝
  • Connection Usage(%)가 100% 라면 max_connections 수를 증가시켜 주십시요. Connection 수가 부족할 경우 Too Many Connection Error 가 발생합니다.
  • DB 서버의 접속이 많은 경우는 wait_timeout 을 최대한 적게 (10~20 정도를 추천) 설정하여 불필요한 연결을 빨리 정리하는 것이 좋습니다. 그러나 Connection Miss Rate(%) 가 1% 이상이 된다면 wait_timeout 을 좀 더 길게 잡는 것이 좋습니다.
  • Cache Miss Rate(%) 가 높다면 thread_cache_size를 기본값인 8 보다 높게 설정하는 것이 좋습니다. 일반적으로 threads_connected 가 Peak-time 시 보다 약간 낮은 수치로 설정하는 것이 좋습니다.
  • MySQL 서버는 외부로 부터 접속 요청을 받을 경우 인증을 위해 IP 주소를 호스트네임으로 바꾸는 과정을 수행하여 접속시에 불필요한 부하가 발생하게 됩니다. skip-name-resolve를 설정하시고 접속시에 IP 기반으로 접속을 하게 되면 hostname lookup 과정을 생략하게 되어 좀 더 빠르게 접속을 하실 수 있습니다.
다. Memory 튜닝
1. status
  • key_block_unused - Key Cache에서 사용되고 있지 않은 Block 수
  • key_reads - Key Block 읽기 요청시 Disk을 읽은 수
  • key_read_requests - Key Block 읽기 요청수
2. system variables
  • key_buffer_size - 인덱스를 메모리에 저장하는 버퍼의 크기
  • table_cache - 전체 쓰레드가 사용할 오픈 가능한 테이블 수
  • myisam_sort_buffer_size - 테이블 repair,Alter table,load data에 사용되는 버퍼 메모리 크기
  • join_buffer_size - 조인을 위한 메모리 버퍼 크기
  • record_buffer - 순차적인 검색을 위해 사용되는 메모리 버퍼 크기
  • record_rnd_buffer - order by 절을 사용할 경우 디스크 사용을 피하기 위하여 사용하는 메모리 버퍼 크기
  • sort_buffer - order by 와 group by에 사용되는 메모리 버퍼 크기
  • tmp_table_size - group by 시 디스크를 사용하지 않고 임시 테이블을 만들기 위해 사용되는 메모리 크기
  • key_cache_block_size - block 의 크기(bytes, 기본값 1024)
mysql> show status like '%key%';
+------------------------+-----------+
| Variable_name          | Value     |
+------------------------+-----------+
| Com_preload_keys       | 0         |
| Com_show_keys          | 2945      |
| Handler_read_key       | 365020739 |
| Key_blocks_not_flushed | 0         |
| Key_blocks_unused      | 222601    |
| Key_blocks_used        | 231960    |
| Key_read_requests      | 847204435 |
| Key_reads              | 4195954   |
| Key_write_requests     | 25034738  |
| Key_writes             | 16452136  |
+------------------------+-----------+
10 rows in set (0.00 sec)


Key Buffer Usage = 1 - ((Key_blocks_unused × key_cache_block_size) / key_buffer_size)Key_reads/Key_read_requests Rate(%) =  Key_reads/Key_read_requests * 100
Key_reads/Key_read_requests Relative Rate(%) = (1- ^Key_reads/^Key_read_requests) * 100

* ^Key_Reads = Current Key_Rreads - Previous Key_Reads

3. 튜닝
  • key_buffer_size는 총 메모리 크기의 25% 정도의 크기로 설정하는 것이 좋습니다.
  • Key_reads/Key_read_requests Rate(%)은 일반적으로 1%보다 적습니다. 1% 보다 높다면 Key Cache가 아닌 디스크를 읽은 경우가 많다고 판단할 수 있습니다. 또한 Key_reads/Key_reads_requests Relative Rate(%) 값이 지속적으로 90% 이상일 경우는 key_buffer_size가 효율적으로 설정되어 있다고 생각하시면 됩니다. 하지만 데이터베이스가 엄청나게 크거나 여러 데이터를 골고루 많이 읽는 데이터베이스라면 아무리 많은 양의 키 캐시를 설정해도 90% 이상의 적중률을 얻을 수는 없습니다.
라. 적용
system variables은 my.cnf 또는 my.ini 파일을 수정 후 MySQL Server 를 재시작 해 주십시요.
[www@smson www]$ vi /etc/my.cnf  # The MySQL server
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
skip-name-resolve
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
wait_timeout = 120

~~~

[root@smson mysql]# /usr/local/mysql/share/mysql/mysql.server restart


마. 참고


Trackback 1 : Comment 0

mysql 튜닝 최적화

mysql 2013.08.08 16:14


[출처] http://lonmoon.tistory.com/324


mysql> show status;

- Aborted_clients : 클라이언트 프로그램이 비정상적으로 종료된 수
- Aborted_connects : MySQL서버에 접속이 실패된 수
- Max_used_connections : 최대로 동시에 접속한 수
- Threads_cached : Thread Cache의 Thread 수
- Threads_connected : 현재 연결된 Thread 수
- Threads_created : 접속을 위해 생성된 Thread 수
- Threads_running : Sleeping 되어 있지 않은 Thread 수


mysql> show variables like '%max_connection%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

 

mysql> show variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |  종료전까지 요청이 없이 기다리는 시간 ( TCP/IP 연결, Shell 상의 접속이 아닌 경우 )
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 28800    |
+--------------------------+----------+

 

mysql> show variables like '%thread%';
+-----------------------------------------+---------------------------+
| Variable_name                           | Value                     |
+-----------------------------------------+---------------------------+
| innodb_purge_threads                    | 0                         |
| innodb_read_io_threads                  | 4                         |
| innodb_thread_concurrency               | 0                         |
| innodb_thread_sleep_delay               | 10000                     |
| innodb_write_io_threads                 | 4                         |
| max_delayed_threads                     | 20                        |
| max_insert_delayed_threads              | 20                        |
| myisam_repair_threads                   | 1                         |
| performance_schema_max_thread_classes   | 50                        |
| performance_schema_max_thread_instances | 1000                      |
| pseudo_thread_id                        | 33865                     |
| thread_cache_size                       | 8                         |  thread 재 사용을 위한 Thread Cache 수로써, Cache 에 있는 Thread 수보다 접속이 많으면 새롭게 Thread를 생성한다.
| thread_concurrency                      | 16                        |
| thread_handling                         | one-thread-per-connection |
| thread_stack                            | 262144                    |
+-----------------------------------------+---------------------------+

 

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |   최대 동시 접속 가능 수
+-----------------+-------+

 

 

Cache Miss Rate(%) =  Threads_created / Connections * 100
 mysql> show status like '%thread%';
 mysql> show status like '%connect%';

 1.08% = 753 / 69671 * 100
 1.1% = 11 / 962 * 100

Connection Miss Rate(%) = Aborted_connects / Connections * 100
 mysql> show status like '%connect%';

 0.02% = 14 / 69671 * 100


Connection Usage(%) = Threads_connected / max_connections * 100
 mysql> show status like '%thread%';
 mysql> show variables like '%connect%';
 
 7.28% = 11 / 151 * 100
 2% = 4 / 200 * 100

 

 

. 튜닝

Connection Usage(%)가 100% 라면 max_connections 수를 증가시켜 주십시요. Connection 수가 부족할 경우 Too Many Connection Error 가 발생합니다.

DB 서버의 접속이 많은 경우는 wait_timeout 을 최대한 적게 (10~20 정도를 추천) 설정하여 불필요한 연결을 빨리 정리하는 것이 좋습니다.

그러나 Connection Miss Rate(%) 가 1% 이상이 된다면 wait_timeout 을 좀 더 길게 잡는 것이 좋습니다.

Cache Miss Rate(%) 가 높다면 thread_cache_size를 기본값인 8 보다 높게 설정하는 것이 좋습니다. 일반적으로 threads_connected 가 Peak-time 시 보다 약간 낮은 수치로 설정하는 것이 좋습니다.

MySQL 서버는 외부로 부터 접속 요청을 받을 경우 인증을 위해 IP 주소를 호스트네임으로 바꾸는 과정을 수행하여 접속시에 불필요한 부하가 발생하게 됩니다.

skip-name-resolve를 설정하시고 접속시에 IP 기반으로 접속을 하게 되면 hostname lookup 과정을 생략하게 되어 좀 더 빠르게 접속을 하실 수 있습니다.

 

 

. Memory 튜닝

mysql> show status like '%key%';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| Com_assign_to_keycache | 0       |
| Com_preload_keys       | 0       |
| Com_show_keys          | 0       |
| Handler_read_key       | 0       |
| Key_blocks_not_flushed | 0       |
| Key_blocks_unused      | 307043  | Key Cache에서 사용되고 있지 않은 Block 수
| Key_blocks_used        | 12864   |
| Key_read_requests      | 1305206 | Key Block 읽기 요청수
| Key_reads              | 198507  | Key Block 읽기 요청시 Disk을 읽은 수
| Key_write_requests     | 55      |
| Key_writes             | 55      |
+------------------------+---------+

1. status

key_block_unused - Key Cache에서 사용되고 있지 않은 Block 수
key_reads - Key Block 읽기 요청시 Disk을 읽은 수
key_read_requests - Key Block 읽기 요청수

 


mysql> show variables like '%key%';     
+--------------------------+----------------------+
| Variable_name            | Value                |
+--------------------------+----------------------+
| delay_key_write          | ON                   |
| foreign_key_checks       | ON                   |
| have_rtree_keys          | YES                  |
| key_buffer_size          | 402653184            | 인덱스를 메모리에 저장하는 버퍼의 크기
| key_cache_age_threshold  | 300                  |
| key_cache_block_size     | 1024                 |
| key_cache_division_limit | 100                  |
| max_seeks_for_key        | 18446744073709551615 |
| ssl_key                  |                      |
+--------------------------+----------------------+


2. system variables

key_buffer_size - 인덱스를 메모리에 저장하는 버퍼의 크기
table_cache - 전체 쓰레드가 사용할 오픈 가능한 테이블 수
myisam_sort_buffer_size - 테이블 repair,Alter table,load data에 사용되는 버퍼 메모리 크기
join_buffer_size - 조인을 위한 메모리 버퍼 크기
record_buffer - 순차적인 검색을 위해 사용되는 메모리 버퍼 크기
record_rnd_buffer - order by 절을 사용할 경우 디스크 사용을 피하기 위하여 사용하는 메모리 버퍼 크기
sort_buffer - order by 와 group by에 사용되는 메모리 버퍼 크기
tmp_table_size - group by 시 디스크를 사용하지 않고 임시 테이블을 만들기 위해 사용되는 메모리 크기
key_cache_block_size - block 의 크기(bytes, 기본값 1024)

 

Key Buffer Usage = 1 - ((Key_blocks_unused × key_cache_block_size) / key_buffer_size)
 mysql> show status like '%key%';
 mysql> show variables like '%key%';
  
 0.211 = 1 - ((307095 * 1024) / 402653184)
 0.189 = 1 - ((850422 * 1024) /  1073741824)

 

Key_reads/Key_read_requests Rate(%) =  Key_reads/Key_read_requests * 100
 mysql> show status like '%key%';

  15% = 207265/1374958 * 100
  17% = 9709 / 54200 * 100
  2699 / 12898 * 100
  

 

Key_reads/Key_read_requests Relative Rate(%) = (1- ^Key_reads/^Key_read_requests) * 100
* ^Key_Reads = Current Key_Rreads - Previous Key_Reads

 = 1 - 8455/

 


3. 튜닝

key_buffer_size는 총 메모리 크기의 25% 정도의 크기로 설정하는 것이 좋습니다.

Key_reads/Key_read_requests Rate(%)은 일반적으로 1%보다 적습니다.

1% 보다 높다면 Key Cache가 아닌 디스크를 읽은 경우가 많다고 판단할 수 있습니다.

또한 Key_reads/Key_reads_requests Relative Rate(%) 값이 지속적으로 90% 이상일 경우는 key_buffer_size가 효율적으로 설정되어 있다고 생각하시면 됩니다.

하지만 데이터베이스가 엄청나게 크거나 여러 데이터를 골고루 많이 읽는 데이터베이스라면 아무리 많은 양의 키 캐시를 설정해도 90% 이상의 적중률을 얻을 수는 없습니다.

 


.적용

[www@smson www]$ vi /etc/my.cnf 
# The MySQL server

[mysqld]
skip-name-resolve
key_buffer_size = 1024M

max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M

wait_timeout = 120


참고:  mysql 튜닝 툴
wget http://mysqltuner.com/mysqltuner.pl
chmod +x ./mysqltuner.pl
./mysqltuner.pl

Trackback 0 : Comment 0

show variables

mysql 2013.08.02 11:52


[출처] http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html


system variable


 - automatic_sp_privileges : 설정값 1이면 stored routine 을 create 하는 계정에 대해 자동적으로 EXECUTE, ALTER 권한을 할당, 설정값이 0 이면 반대


Trackback 0 : Comment 0

[MySQL/명령어]REPAIR TABLE - 테이블을 복구한다.

mysql 2013.04.27 13:01

[출처] http://comnic.tistory.com/41

 

Repair Table은 적은 수의 레코드를 가진 Table의 복구에 유용하다. 실제로 수천만건 이상이 되었을때는 시스템에 따라 다르겠지만, 상당한 시간이 소요되었다.
Repair Table에 대해 간략히 알아 보면 다음과 같다.

- REPAIR TABLE(출처 : http://dev.mysql.com/doc/refman/4.1/en/repair-table.html)
 Repair Table은 Table에 문제가 발생 했을때 이를 복구하는 명령어 이다. 기본적으로 shell에서의 myisamchk --recover 와 같은 효과를 가진다. 그리고 이는 MyISAM과 ARCHIVE table에서만 작동하며, select와 insert의 권한이 있어야 가능하다.

사용법] 

REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...
    [QUICK] [EXTENDED] [USE_FRM]

repair table table명

mysql> repair table user;
+------------+--------+----------+----------+
| Table         | Op        | Msg_type | Msg_text |
+------------+--------+----------+----------+
| mysql.user | repair    | status      | OK         |
+------------+--------+----------+----------+
1 row in set (0.00 sec)

각 필드의 의미는 다음과 같다.
Column Value
Table The table name
Op Always repair
Msg_type status, error, info, or warning
Msg_text An informational message

Repair table전에 check table를 해 보는 것도 의미가 있다.

 

'mysql' 카테고리의 다른 글

mysql 튜닝 최적화  (0) 2013.08.08
show variables  (0) 2013.08.02
[MySQL/명령어]REPAIR TABLE - 테이블을 복구한다.  (0) 2013.04.27
mysql 테이블과 인덱스 설계 시 주의사항 13가지 -- MySQL  (0) 2013.04.27
MySQL의 sock 에러  (0) 2013.04.27
MySQL Timestamp To DateTime  (0) 2013.04.27
Trackback 0 : Comment 0

mysql 테이블과 인덱스 설계 시 주의사항 13가지 -- MySQL

mysql 2013.04.27 13:01


[출처] http://tiger5net.egloos.com/5660848

 

테이블 설계 시 유의 사항

 

1. 반드시 Primary Key를 정의하고 최대한 작은 데이터 타입을 선정한다.

로그 성 테이블에도기본적으로 PK 생성을 원칙으로 함

InnoDB에서 PK는인덱스와 밀접한 관계를 가지므로 최대한 작은 데이터 타입을 가지도록 유지

 

2. 테이블 Primary Key는 auto_increment를 사용한다.

InnoDB에서는 기본키 순서로 데이터가 저장되므로, Random PK 저장 시 불필요한 DISK I/O가 발생 가능

InnoDB의 PK는절대 갱신되지 않도록 유지

(갱신 시 갱신된 행이후 데이터를 하나씩 새 위치로 옮겨야 함)

 

3. 데이터 타입은 최대한 작게 설계한다.

시간정보는MySQL데이터 타입 date/datetime/timestamp 활용

IP는INET_ATON(‘IP’), INET_NTOA(int) 함수를 활용

정수 타입으로 저장가능한 문자열 패턴은 최대한 정수 타입으로 저장

 

4. 테이블 내 모든 필드에 NOT NULL 속성을 추가한다.

NULL을 유지를 위한추가 비용 발생

(NULL 허용 칼럼을인덱싱 할 때 항목마다 한 바이트 씩 더 소요)

 

5. Partitioning을 적절하게 고려하여 데이터를 물리적으로 구분한다.

데이터 및 인덱스파일이 커질수록 성능이 저하되므로Partitioning 유도

PK 존재 시 PK내부에 반드시 Partitioning 조건이 포함되어야 함

 

인덱스 설계 시 유의 사항

 

1. 인덱스 개수를 최소화 한다.

현재 인덱스로Range Scan이 가능한지 여부를 사전에 체크

인덱스도 서버 자원을소모하는 자료구조이므로 성능에 영향을 줌

 

2. 인덱스 칼럼은 분포도를 고려하여 선정한다.

인덱스 칼럼 데이터의중복이 줄어들수록 인덱스는 최대의 효과를 가짐

하단 쿼리 결과 값이 1에 가까울수록(0.9이상 권고) 인덱스 컬럼으로 적합함

 

SELECT count(distinctINDEX_COLUMN)/count(*)

FROM TABLE;

 

3. 커버링 인덱스(Covering Index)를 활용한다.

쿼리 조건이 인덱스안에 포함된 경우 인덱스에서만 연산 유도

인덱스는 일반적으로 행전체보다 작으므로 불필요한 Disk I/O 회피 가능

MySQL에서 커버링 인덱스로 쿼리 성능을 높여보자!! 참고

 

4. 스토리지 엔진 별 INDEX 특성을 정확히 인지한다.

InnoDB에서데이터는 PK 순서로 저장되고, 인덱스는 PK를 Value로 가짐

MyISAM은 PK와일반 인덱스의 구조는 동일하나, Prefix 압축 인덱스를 사용

(MyISAM 엔진에서ORDER BY 시 DESC는 가급적 지양)

 

5. 문자열을 인덱싱 시 Prefix 인덱스 활용한다.

긴 문자열 경우Prefix 인덱스(앞 자리 몇 글자만 인덱싱)를 적용

 

CREATE INDEX IDX01 ON TAB1(COL(4), COL(4))

 

Prifix Size는앞 글자 분포도에 따라 적절하게 설정

(하단 결과가 1에가까울 수록 최적의 성능 유지, 0.9이상 권고)

 

SELECT count(distinctLEFT(INDEX_COLUMN,3))/count(*)

FROM TABLE;

 

6. CRC32함수 및 Trigger를 활용하여 인덱스 생성한다.

URL/Email같이문자 길이기 긴 경우 유용

INSERT/UPDATE발생 시 Trigger로 CRC32 함수 실행 결과 값을 인덱싱

CRC32 결과값을저장할 칼럼 추가 및 인덱스 생성

 

alter table user_tbl add email_crc intunsigned not null;

create index idx01_email_crc on user_tbl (email_crc);

 

InsertTrigger 생성

 

create trigger trg_user_tbl_insert

beforeinsert on user_tbl

for each row

begin

setnew.email_crc = crc32(lower(trim(new.email)));

end$$

 

UpdateTrigger 생성

 

create trigger trg_user_tbl_update

beforeupdate on user_tbl

for each row

begin

if old.email<>new.email then

setnew.email_crc = crc32(lower(trim(new.email)));

end if;

end$$

 

검색 쿼리

 

select *

from user_tbl

where email_crc = crc32(lower(trim('mail@domain.com')))

and email='mail@domain.com'

 

CRC32 결과가중복되어도, email값을 직접 비교하는 부분에서 중복이 제거됩니다.

 

7. 중복 인덱스 생성 회피

MySQL은 동일한인덱스를 중복 생성해도 에러를 발생하지 않음

Primary Key로구성된 칼럼과 동일한 인덱스를 생성하지 않도록 주의


출처 : http://dev.kthcorp.com/2012/05/15/mysql-table-index-design-tip/

Trackback 0 : Comment 0

MySQL의 sock 에러

mysql 2013.04.27 13:01

[출처] http://web.dicnote.com/bbs/board.php?bo_table=web_tip&wr_id=66&page=3 

 

mysql -u root -p

기본으로 root의 암호는 공백이니까 그냥 엔터를 칩니다. 그런데.. 들어가지지는 않고 에러가 나옵니다.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

아.. 이 끔찍한 에러. 이전에도 몇 번 만난적이 있는데, 그 때 잘 정리해 두지 않고 어찌저찌해서 접속 한 다음에 그냥 사용하고 있다가 다시 오랜만에 서버에 깔다가 만나니.. 참으로 난감한 에러입니다.

1. 프로세스가 돌고 있지 않아서 에러가 납니다. 그럼 프로세스를 돌려줍니다.  mysqld (MySQL 데몬)을 실행하면 되는데, 이 파일이 여러군대에 있더군요. 그중에서 저는

/usr/local/mysql/bin/mysqld_safe --user=mysql&

이렇게 하니까 동작 됐습니다.
Starting mysqld daemon with databases from /usr/local/mysql/var 이런 메시지가 출력됩니다.

2. 그 다음 다시 로그인을 시도하면 또 똑같은 에러가 나타납니다. 이번에는 링크를 만들어 줍니다.
temp 폴더에 생성되는 sock 파일을 mysql이 원츄하고 있는 이름으로 심볼릭 링크를 만들어 줍니다.

ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock

 

Trackback 0 : Comment 0

MySQL Timestamp To DateTime

mysql 2013.04.27 13:00

 

[출처] MySQL Timestamp To DateTime

 

MySQL Timestamp To DateTime

FROM_UNIXTIME

UNIX_TIMESTAMP

 

Trackback 0 : Comment 0