'Database/Mysql'에 해당하는 글 26건

 

 

 

아주 오래전, 기억이 나지 않는 어떤 이유로 워크벤치는 사용하지 않았다. dbforge가 가볍고 좋았다;
주변에서 워크벤치에 대한 질문들이 간혹있어 깔아봤는데 시작부터 장애가 ㅋㅋ

 

특정 테이블의 데이터를 직접 수정하려니 불가능하다.
Read Only 라고 쓰여 있는 곳에 마우스를 올리니,

 

The table has no unique row identifier (primary key or a NOT NULL unique index)

 

Primary key 가 필요없는 테이블이니 Primary key 를 넣지 않았을 것이고,
unique index 도 넣을 수 없으니 넣지 않았을 것이고...

 

Not unique index 하나 넣으니 Read Only 가 사라졌음. ^^
primary key 나 index 가 없다는 이유로 Read Only 를 거는 이유가 뭐지?
오히려 primary key 나 index 걸린 데이터를 수정하는게 더 위험한거 아닌가???

 

 


WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

트랙백  0 , 댓글  0개가 달렸습니다.
secret

단 하나 알고 있는 계정으로 들어갔는데 프로시저를 변경할 수가 없다. 콜은 되고, 내용은 볼 수 없고...ㅜ
프로시저 생성자 패스워드도 모르고, 결국 mysql root 패스워드 초기화하여 작업 준비.

- 1분 작업 예상하고 스피드하게 Start

# /etc/init.d/mysqld stop
# mysqld_safe --skip-grant &
[1] 1613
# Starting mysqld daemon with databases from /var/lib/mysql
# mysql

> use mysql;
> update user set password=password('#abcdefg$') where user='root';
> flush privileges;
> quit

# /etc/init.d/mysqld restart


WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

트랙백  0 , 댓글  0개가 달렸습니다.
secret
EXPLAIN 사용

  1. 테이블의 어느 곳에 인덱스를 추가해야 성능이 향상되는지 알 수 있음.
  2. 옵티마이저가 최적의 순서로 테이블을 조인할 수 있는 지 여부 검사.


EXPLAIN 결과 컬럼

  1. id : 쿼리 안의 SELECT 순차 번호
  2. select_type : SELECT 타입
    SIMPLE - 단순 SELECT (UNION 이나 서브쿼리를 사용하지 않음)
    PRIMARY - 가장 외곽의 SELECT
    UNION - UNION의 두번째 혹은 그 이후의 SELECT
    DEPENDENT UNION - UNION의 두번째 혹은 그 이후의 SELECT, 외곽 쿼리에 의존적
    UNION RESULT - UNION의 결과
    SUBQUERY - 서브쿼리의 첫번째 SELECT
    DEPENDENT SUBQUERY - 서브쿼리의 첫번째 SELECT, 외곽 쿼리에 의존적
    DERIVED - SELECT로 추출된 테이블 (FROM 절의 서브쿼리)
    UNCACHEABLE SUBQUERY - 결과가 캐시될 수 없고 외곽 쿼리의 각 행에 대해 재평가되어야만 하는 서브쿼리
    UNCACHEABLE UNION - 캐시될 수 없는 서브쿼리에 속하는 UNION의 두번째 혹은 그 이후의 SELECT
  3. table : 결과 열이 참조하는 테이블
  4. type : 조인(join) 타입
    system - 하나의 열만 가지는 테이블(=시스템 테이블)
    const - primary key나 unique 인덱스의 모든 부분을 상수값과 비교. 하나의 열만 존재하기 때문에 매우 빠름.
    SELECT * FROM tbl_name WHERE primary_key=1;
    eq_ref - = 연산자를 사용해서 비교되는 인덱스된 컬럼용으로 사용.
    SELECT * FROM ref_table, other_table WHERE ref_table.key_column = other_table.column;
    ref - = 또는 <=> 연산자를 사용해서 비교되는 인덱스된 컬럼에 대해 사용
    SELECT * FROM ref_table WHERE key_column = expr;
    ref_or_null - ref 와 유사하지만, null 값을 가지고 있는 열에 대해서도 검색. 서브 쿼리를 해석할 때 자주 사용
    SELECT * FROM ref_table WHERE key_column = expr OR key_column IS NULL;
    index_merge -  인덱스 병합 최적화가 사용되었음.
    unique_subquery - IN 서브 쿼리 (subqueries)에 대해서 ref를 대체하는 인덱스 lookup 함수임.
    index_subquery - unique_subquery와 유사하지만 non-unique 인덱스에 대해서도 동작함.
    range - 주어진 범위에 들어 있는 열만을 추출하며, 열 선택은 인덱스를 사용함. 키 컬럼이 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, 또는 IN 연산자를 사용하는 상수 (constant)와 비교할 때 사용할 수 있음.
    index - ALL과 동일하지만 인덱스 트리만을 스캔함.
    ALL - 전체 테이블 스캔.
  5. possible_keys : 테이블에서 열을 찾기 위해 선택한 인덱스.
  6. key : 실제 사용할 예정인 키(인덱스)
  7. key_len : MySQL이 사용하기로 결정한 키의 길이.
  8. ref : 테이블에서 열을 선택하기 위해 인덱스를 어떤 컬럼 또는 상수(constant)와 비교하는지를 표시.
  9. rows : 쿼리를 실행하기 위해 조사해야 하는 열의 숫자를 가리킨다.
  10. filtered : 테이블 정의문이 필터링하는 테이블 열을 추정한 비율. rows는 조사된 열의 추정 숫자이며, rows × filtered / 100은 조인될 열의 숫자임. EXPLAIN EXTENDED를 사용하게 되면, 이 컬럼이 출력.
  11. Extra : 쿼리의 추가 정보
    Distinct - 명확한 값을 찾게 되며 매칭되는 열을 찾게 되면 검색을 중단.
    Full scan on NULL key - 인덱스 룩업(index-lookup) 접속을 할 수 없고 펄백(fallback) 방식으로 서브 쿼리 최적화를 할 때.
    Impossible WHERE noticed after reading const tables - 모든 const(system) 테이블 값을 읽었으며, WHERE 구문이 항상 거짓(false)일 때.
    No table - FROM 구문이 없거나, FROM DUAL 구문이 있을 때.
    Not exists - LEFT JOIN 최적화를 실행 했으며, 이 최적화와 매치되는 열을 찾은 후에는 이전 열 조합 검색 중단.
    SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;
    range checked for each record (index map: N) : 사용하기에 좋은 인덱스를 찾지 못했으나, 컬럼값을 찾고 난 후에는 사용할만한 인덱스를 찾았음.
    Select tables optimized away : 인덱스, 또는 MyISAM용 COUNT(*)을 사용하되 GROUP BY 구문은 사용하지 않은 채로 처리된 집단 함수(MIN(), MAX())만을 가지고 있음.
    Using filesort : 정렬(sort)은 조인(join) 타입과 정렬 키 and WHERE 구문과 매치가 되는 모든 열에 대한 열 포인터(pointer)를 사용해서 모든 열에 걸쳐 진행.
    Using index : 인덱스 트리에 있는 정보만을 가지고 테이블에서 컬럼 정보를 추출.
    Using join cache : 테이블을 부분적으로 읽어온 후에, 읽어 온 열을 사용해서 조인을 실행.
    Using temporary : 결과를 저장할 임시 테이블을 하나 생성해야 함. GROUP BY and ORDER BY 구문을 가지고 있는 경우 나타남.
    Using where : 테이블에 대한 열 매치 (match) 또는 클라이언트에 보내지는 열을 제한하기 위해 사용.
    Using sort_union(...), Using union(...), Using intersect(...) : 인덱스 스캔이 어떻게 index_merge 조인 타입과 병합(merge)이 되는지 출력.
    Using index for group-by : 실제 테이블을 추가 접속하지 않은 채로 GROUP BY 또는 DISTINCT 쿼리의 모든 컬럼을 추출할 때 사용할 수 있는 인덱스.
    Using where with pushed condition : 테이블을 추가 검색하지 않고도, GROUP BY 또는 DISTINCT 쿼리의 모든 컬럼을 추출하기 위해 사용될 인덱스를 찾았음.

WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

트랙백  0 , 댓글  0개가 달렸습니다.
secret
1. 우편번호 원본 다운로드 - http://www.epost114.co.kr/

2. 테이블 생성 예)

CREATE TABLE db1.zipcode(
  zipcode CHAR(7) NOT NULL COMMENT '우편번호',
  sido VARCHAR(12) DEFAULT NULL COMMENT '시도',
  gugun VARCHAR(12) DEFAULT NULL COMMENT '시군구',
  dong VARCHAR(255) DEFAULT NULL COMMENT '동',
  ri VARCHAR(255) DEFAULT NULL COMMENT '리',
  bunji VARCHAR(255) DEFAULT NULL COMMENT '번지',
  building VARCHAR(255) DEFAULT NULL COMMENT '건물명'
)
ENGINE = MYISAM
CHARACTER SET utf8
COLLATE utf8_general_ci;

3. 다운받은 csv 파일의 field를 db 순서에 맞춤.


번거로운 방법

4-1. 귀찮게 에디터에서 정규식을 사용하여 insert into zipcode ~~~ 자동 변환. sql 로 저장

4-2. utf-8 DB 를 사용중이라면 # iconv -f euc-kr -t utf-8 zipcode.sql > zipcode_utf8.sql

4-3. # mysql -u username -p db1 < zipcode_utf8.sql


그나마 편한 방법

4-1. utf-8 DB 를 사용중이라면 # iconv -f euc-kr -t utf-8 zip.csv > zip_utf8.csv

4-2. mysql> load data local infile 'zip_utf8.csv' into table db1.zipcode fields terminated by ',';

둘 다 잘들어감 ^^

WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

트랙백  0 , 댓글  0개가 달렸습니다.
secret

AUTO_INCREMENT

Database/Mysql 2007. 2. 7. 21:58
AUTO_INCREMENT 사용하기

새로운 레코드에 대해 유일한(unique) 값을 생성시킬 때 사용된다.
mysql> CREATE TABLE animals (
  -> id MEDIUMINT NOT NULL AUTO_INCREMENT,
  -> name CHAR(30) NOT NULL,
  -> PRIMARY KEY (id) );
Query OK, 0 rows affected (0.00 sec)
.
mysql> INSERT INTO animals (name) VALUES
  -> ('dog'),('cat'),('penguin'),('lax'),('whale'),('ostrich');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
.
mysql> SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)

  • LAST_INSERT_ID() SQL 함수 또는 mysql_insert_id() C API 함수를 사용해서 가장 최근의 AUTO_INCREMENT 값을 불러올 수가 있다.
  • 다중 줄 삽입에 대해서는 LAST_INSERT_ID() 와 mysql_insert_id()는 실제로 삽입된 맨 처음 줄로부터 AUTO_INCREMENT 를 돌려 준다.
AUTO_INCREMENT의 값이 1인 아닌 것으로 시작으로 하기 위해, 그 값을 CREATE TABLE 또는 ALTER TABLE을 가지고 설정한다:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;


명령어 라인에서 옵션 사용

shell> mysql -u root -p --execute="SELECT User, Host FROM user" mysql
shell> mysql -u root -p --execute="SELECT User, Host FROM mysql.user"
shell> mysql -u root -p -e "SELECT VERSION();SELECT NOW()"


WRITTEN BY
손가락귀신
정신 못차리면, 벌 받는다.

트랙백  0 , 댓글  0개가 달렸습니다.
secret