차이

문서의 선택한 두 판 사이의 차이를 보여줍니다.

차이 보기로 링크

양쪽 이전 판이전 판
다음 판
이전 판
tech:mysql [2014/03/18 23:28] V_Ltech:mysql [2019/04/04 03:09] (현재) – [참조 문서] V_L
줄 1: 줄 1:
 +{{tag>tech mysql}}
 +======Mysql======
 +MySQL은 DBMS DataBase Management System 로서.. DB를 구성하고 이를 다루기 위해 구성된 시스템이죠..
 +다수의 사용자들이 DB에 접속해서 자료의 생성, 삭제, 갱신, 조작등의 제어를 할 수 있도록 해주는 프로그램입니다 ^-^
  
 +=====팁=====
 +  * [[http://www.lovelgw.com/Blog/tag/769|MySQL 효율적인 테이블 설계 방법 --근원e - No place to go~]]((MySQL 의 효율적인 테이블 설계 방법
 +1. 저장될 형식에 맞추어서 데이터를 정규화 합니다.
 +
 +어떠한 데이터라도 정규화가 가능한 범위까지 최대한 정규화를 합니다. 예를 들어 학생정보를 입력하는 테이블의 경우 학생의 기본 정보와 학과, 전공, 수강과목등을 하나의 테이블에 몽땅 넣는것 보다는 학과 전공 강의과목을 분류해 다른 테이블로 분류를 하여 학생 테이블에는 관련된 항목에 대한 키를 가질 수 있도록 합니다. 또한, 모든 정보를 가지고 있지 않은 별도의 정보는 별도의 테이블에 따로 저장을 하여 불필요한 공간이 모든 학생 정보에 들어가지 않도록 합니다. 정규화에 대한 자세한 정의는 천천히 다뤄보도록 하겠습니다.
 +
 +2. 작은 형태의 데이터를 큰 데이터 형으로 저장하지 않도록 합니다. 
 +학생들의 과목 점수를 저장하는 성정 테이블을 예를 들어보도록 하겠습니다. 학생 과목과 함께 학생 성적이 0점에서 최대 100점까지 기록이 된다고 할때 저장되는 필드의 데이터형을 INT, MEDIUMINT , SMALLINT 등으로 지정하지 않고 가장 작은 형태인 TINYINT로 지정합니다. INT의 경우에는 4Byte의 공간을 차지하게 되지만. TINYINT의 경우에는 1Byte의 저장공간을 자치하게 됩니다. 많은 양의 데이터가 쌓이게 된다면 한개의 필드 형에 따른 연산 속도가 많은 차이를 불러오게 됩니다.
 +
 +3. NULL 의 최소화
 +MySQL은 조회시 필드의 값이 NULL인지를 판단하는 동작을 하게되어있습니다. 필드에 NULL값을 최소화 하여 쿼리 수행 속도를 높일수 있습니다.
 +
 +4. ENUM, SET 을 활용
 +데이터 중 형태, 속성등을 나타내는 값을 저장하기 위해 CHAR,VARCHAR를 사용하는것 보다는 ENUM을 사용하는것이 효율적입니다. 표현상으로는 문자열이지만 내부적으로는 bit 단위로 처리가 됩니다. 도한 2개 이상의 속성을 구분값으로 처리하는것 보다는 SET 형식으로 처리하는것이 훨씬 효율적이고 공간과 쿼리 수행속도에 도움이 됩니다.
 +
 +5. 테이블 Row format 을 고려합니다. 
 +테이블의 Row format 은 테이블 내에 가변 컬럼들의 유무에 따라 결정이 됩니다. 그 형태에 따라 테이블 연산 수행 속도와 , 저장공간에 많은 차이를 불러옵니다. 예를 들어 특정 프로그램에 대한 수행 로그를 기록하는 테이블을 예를 들어 봅시다. 프로그램 연산시 사용하는 중요한 테이블이 아닌 연산 결과에 따른 로그들을 기록하는 테이블의 경우 수 많은 데이터가 쌓이게 됩니다. 또한, 많은 연산 보다는 필요에 의해 가끔 조회를 하게 됩니다. 이런 테이블의 경우에는 가변형 데이터 타입인 VARCHAR등을 사용하여 테이블의 구조를 잡는것이 좋습니다. 가변형 테이블을 사용함으로서 많은 공간 절약 효과를 나타낼 수 있습니다.
 +
 +이것 과 반대로 회원 주문내역 테이블을 생각해 봅시다. 많은 회원들이 조회를 하는 주문내역 테이블에서는 가변 형태의 저장 포멧 보다는 고정형태의 필드를 사용하는것이 연산 속도 및 테이블 최적화, 복구에 더 좋습니다. 고정적인 형태의 저장 형태로 인해 필드 데이터의 종료 위치를 계산할 필요도 없으며 데이터 타입 선언 형태만큼 데이터를 불러오게 됩니다. 물론 10개의 문자를 저장할 수 있는 CHAR형태의 필드일 경우에 1개의 문자만 저장해도 실질적 공간은 10byte를 차지하게 됩니다. 테이블 설계시 이 2개의 Row format 에 대해서는 특 실을 잘 따져 보는게 좋습니다. SHOW TABLE STATUS 구문으로 테이블 형태를 알아 볼 수 있습니다.
 +
 +6. 큰 데이터 형은 별도의 테이블로 분리 합니다. 
 +큰 데이터형식 BLOB, BINARY BLOB, TEXT 등은 별도의 테이블로 저장하는것이 좋습니다. 게시판을 예를 들어 보겠습니다. 여러 목록을 불러올 시에 해당 목록의 번호, 제목, 작성자 등만을 목록으로 표시합니다. 하지만 내용도 같은 테이블에 있다고 하면 해당 내용의 길이 및 데이터 저장 끝부분을 검사 하고 ,또한 ROW의 종료 위치 마저 재 각각임으로 테이블 연산시 많은 시간이 걸리게 됩니다. 이처럼 가늠하기 힘든 크기의 데이터를 가진 컬럼 데이터 타입은 별도의 테이블로 저장을 하여 테이블의 효율성을 높일 수 있습니다.
 +
 +7. OPTIMIZE TABLE을 주기적으로 수행합니다. 
 +큰 데이터 형을 가지는 테이블에 대한 INSERT / UPDATE 가 많을 경우 그만큼 테이블에 대한 단편화가 심해지게 되어 있습니다. 데이터가 저장되어 있을때 해당 ROW에 대한 업데이트가 진행되었을때 더욱 작은 형태로 데이터를 수정하게 되면 빈공간이 중간에 남게 됩니다. 또, 더 큰 형태로 업데이트를 하게 될 경우 해당 ROW에 대한 데이터를 다른 위치에 저장을 하게 되고 그 위치는 빈공간으로 남게 되어 있습니다. 물론 다른 작은 데이터가 오게 되면 그 공간으로 들어가겠죠 (Windows 의 디스크 조각모음을 생각하시면 됩니다.) 큰 데이터가 자주 업데이트 되거나, 삽입 삭제가 이루어 진다면 가변 길이 저장형태에 따른 테이블 데이터의 단편화게 오게 됩니다. 이와 같은 현상을 최소화 하기 위해 테이블에 대한 OPTIMIZE TABLE을 수행하여 줍니다.))
 +=====타입=====
 +====숫자====
 +|TINYINT |A very small integer |The signed range is –128 to 127. The unsigned range is 0 to 255. |
 +|SMALLINT |A small integer |The signed range is –32768 to 32767. The unsigned range is 0 to 65535 |
 +|MEDIUMINT |A medium-size integer |The signed range is –8388608 to 8388607. The unsigned range is 0 to 16777215 |
 +|INT or INTEGER |A normal-size integer |The signed range is –2147483648 to 2147483647. The unsigned range is 0 to 4294967295 |
 +|BIGINT |A large integer |The signed range is –9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615 |
 +
 +====text와 varchar 타입====
 +
 +TEXT  텍스트형  63535개의 문자 
 +CHAR  고정길이문자  1~255개의 문자 
 +VARCHAR  가변길이문자  1~255개의 문자 
 +
 +varchar는 index를 탈수 있는데 text는 못탑니다. 
 +varchar와 char의 차이점은 varchar는 가변길이라서 속도가 느립니다. 
 +
 +====Set과 Enum 타입====
 +
 +둘의 차이는 복수 선택이 가능하고 안하고의 여부로 보면 되겠네요. 
 +
 +enum을 쓰면 한번에 하나 값만 입력(또는 선택)이 가능하고 
 +set을 쓰면 한번에 하나 이상의 값을 입력(또는 선택)이 가능하다는 말씀이지요? 
 +
 +내부적으로.. 예를 들어 위에 적으신 예같은 경우에... 
 +"짜장" ---> 00000...0001 (2진수 64bit) 
 +"짬뽕" ---> 00000...0010 
 +"우동" ---> 00000...0100 
 +"짜장, 짬뽕" ---> 00000...0011 
 +"짜장, 우동" ----> 00000...0101 
 +"짜장, 짬뽕, 우동" ---> 00000..0111 
 +
 +====접속 테스트====
 +
 +
 +<code php>
 +<?php
 +// we connect to example.com and port 3307
 +$link = mysql_connect('example.com:3307', 'mysql_user', 'mysql_password');
 +if (!$link) {
 +    die('Could not connect: ' . mysql_error());
 +}
 +echo 'Connected successfully';
 +mysql_close($link);
 +
 +?>
 +</code>
 +
 +  * http://php.net/manual/en/function.mysql-connect.php
 +=====문법=====
 +====테이블의 내용을 특정 값을 치환.====
 +
 +   update rg4_member set `mb_level`=REPLACE(`mb_level`,'0','1'
 +
 +
 +
 +====두테이블을 비교하여 더 있는 항목을 표시====
 +
 +outer join 사용 
 +
 +Outer Join 
 +두 TABLE 을 JOIN 할 때 JOIN 조건식을 만족시키지 못하는 ROW 는 검색에서 빠지게 된다. 
 +그런데 이러한 ROW 들이 검색되도록 하는 것이 OUTER JOIN 이다. 
 +(+) OUTER JOIN OPERATOR 를 데이타가 없는 어느 한쪽의 COLUMN 쪽에 붙인다. 
 +JOIN 결과, 데이타가 없는 쪽의 COLUMN 값은 NULL로 검색된다. 
 +조건식을 만족시키지 못하는 데이타도 검색한다. 
 +  SELECT table명.column명, table명.column명 
 +  FROM table1명, table2명 
 +  WHERE table1명.column1명 = table2명.column2명(+) 
 +[ 예제 ] 
 +S_EMP TABLE 과 S_CUSTOMER TABLE 을 사용하여 영업사원의 LAST_NAME, 
 +SALES_REP_ID, NAME 을 검색하시오. 
 +단, 영업사원이 정해져 있지 않은 고객의 이름도 검색하시오. 
 +
 +  SELECT E.LAST_NAME, C.SALES_REP_ID, 
 +  C.NAME 
 +  FROM S_EMP E, S_CUSTOMER C 
 +  WHERE E.ID(+) = C.SALES_REP_ID ; 
 +
 +
 +
 +  SELECT xe_point.member_srl, xe_point.point
 +  FROM xe_point
 +  LEFT OUTER JOIN xe_member ON xe_member.member_srl = xe_point.member_srl
 +  WHERE xe_member.member_srl IS NULL 
 +  LIMIT 0 , 300
 +
 +
 +
 +====삭제====
 +
 +
 +  delete from ABCDE where no='3'
 +'ABCDE' 테이블에서 'no' 컬럼값이 '3' 인 레코드를 전부 삭제한다.
 +
 +
 +  delete from ABCDE where no>3
 +'ABCDE' 테이블에서 'no' 컬럼값이 '3' 보다 큰 레코드를 전부 삭제한다.
 +
 +
 +  delete from ABCDE where no<>3
 +'ABCDE' 테이블에서 'no' 컬럼값이 '3' 이 '아닌' 레코드를 전부 삭제한다.
 +
 +
 +  delete from ABCDE where wdate>32015467 order by no limit 30
 +'ABCDE' 테이블에서 'wdate' 컬럼값이 '32015467' 보다 큰 레코드를 'no' 컬럼값을 기준으로
 +정렬시켜서 상위 30 개를 삭제한다.
 +
 +
 +  delete from ABCDE where tbody like '%무료동영상%'
 +'ABCDE' 테이블에서 'tbody' 컬럼값에 '무료동영상' 이란 단어가 포함된 레코드를 전부 삭제한다.
 +
 +
 +  delete from ABCDE where tbody like '%무료동영상%' or subject like '%광고%'
 +'ABCDE' 테이블에서 'tbody' 컬럼값에 '무료동영상' 이란 단어가 포함되어 있거나
 +'subject' 컬럼값에 '광고' 라는 단어가 포함된 레코드를 전부 삭제한다.
 +
 +=====기타=====
 +
 +====명령어로 가져오기====
 +
 +Your best bet is to FTP it to the server and then ssh in (command line) and import the database that way. The resulting command will look something like:
 +
 +  mysql -hlocalhost -uUser -pPassword database_name < file_name
 +
 +That way you can completely bypass any file upload or processing time restrictions.
 +
 +
 +====root 비번 잃었을때====
 +DB에 접속 후
 +
 +[비번변경]
 +
 +mysql > set password=password("바꿀 비밀번호");
 +
 +[분실시]
 +
 +<code console># cd /usr/local/mysql/bin   //mysql이 설치된 디렉토리의 bin
 +# killall mysqld
 +# ./safe_mysqld --skip-grant &
 +# mysql
 +
 +mysql > USE mysql;
 +mysql > UPDATE user SET Password=PASSWORD('패스워드') WHERE user='root';
 +mysql > FLUSH PRIVILEGES;
 +mysql > quit
 +
 +# mysqld restart
 +</code>
 +
 +====먹통====
 +
 +  start: Job failed to start.
 +http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
 +
 +   InnoDB: Waiting for the background threads to start
 +http://blackbird.si/mysql-corrupted-innodb-tables-recovery-step-by-step-guide/
 +
 +
 +http://dev.mysql.com/doc/refman/5.6/en/crashing.html 
 +
 +=====상태 보기=====
 +
 +https://alvinalexander.com/blog/post/mysql/how-show-open-database-connections-mysql
 +
 +
 +Probably the most common reason for a thread to stay with either killed or query end for a longer period of time is waiting for a transaction rollback on InnoDB tables. This sometimes can take a lot of time to complete, especially when hundreds of thousands or millions of changes have to be removed.
 +
 +
 +
 +===== 참조 문서 =====
 +  * [[http://gywn.net/2012/09/mysql-tuning/|MySQL 성능 최적화를 위한 몇 가지 팁!!]]
 +  * [[http://www.xaprb.com/blog/2006/07/31/how-to-analyze-innodb-mysql-locks/|innotop]] : Innodb 락 찾기
 +  * INNODB 상태 보기 : ''SHOW ENGINE INNODB STATUS''
 +  * 현재 처리중인 프로세스 목록 : ''show processlist;''
 +  * [[http://dev.paran.com/2012/05/22/mysql-low-performance-query-bad-habit/|MySQL 성능 죽이는 잘못된 쿼리 습관]]
 +  * [[http://gywn.net/2012/06/mysql-temporary-table-effect/|MySQL Temporary Table과 성능]]
 +  * [[http://gywn.net/2012/07/mysql-replication-driver-error-report/|MySQL Replication Driver 오류 리포트]]
 +  * [[http://gywn.net/2012/10/mysql-connection-limitation-nproc/|CentOS 6.x에서 ulimit 값을 확인해야 한다]]
 +  * [[http://gywn.net/2017/06/mysql-os-cache-management/|[MySQL] 바쁜 서비스 투입 전, 이런 캐시 전략 어때요? | gywndi's database]]
 +  * [[https://d2.naver.com/helloworld/1155|성능 향상을 위한 SQL 작성법]]