8장 - 인덱스

mysql> ALTER TABLE employees ADD INDEX ix_gender_birthdate (gender, birth_date); -- // 인덱스를 사용하지 못하는 쿼리 mysql> SELECT * FROM employees WHERE birth_date>='1965-02-01'; -- // 인덱스를 사용할 수 있는 쿼리 mysql> SELECT * FROM employees WHERE gender='M' AND birth_date>='1965-02-01';

mysql> SET optimizer_switch='skip_scan=off'; mysql> EXPLAIN SELECT gender, birth_date FROM employees WHERE birth_date>='1965-02-01'; +----+-----------+-------+---------------------+--------------------------+ | id | table | type | key | Extra | +----+-----------+-------+---------------------+--------------------------+ | 1 | employees | index | ix_gender_birthdate | Using where; Using index | +----+-----------+-------+---------------------+--------------------------+

mysql> SET optimizer_switch='skip_scan=off'; mysql> EXPLAIN SELECT gender, birth_date FROM employees WHERE birth_date>='1965-02-01'; +----+-----------+-------+---------------------+--------------------------+ | id | table | type | key | Extra | +----+-----------+-------+---------------------+--------------------------+ | 1 | employees | range | ix_gender_birthdate | Using where; Using index | +----+-----------+-------+---------------------+--------------------------+

mysql> CREATE INDEX ix_teamname_userscore ON employees (team_name ASC, user_score DESC);

mysql> SELECT * FROM employees ORDER BY first_name DESC LIMIT 1;

mysql> CREATE INDEX ix_teamname_userscore ON employees (team_name ASC, user_score DESC);

mysql> SELECT * FROM dept_emp WHERE dept_no='d002' AND emp_no >= 10114;

A : INDEX(first_name_ B : INDEX(dept_no, emp_no)

mysql> SELECT * FROM employees WHERE first_name LIKE '%mer';

mysql> SELECT * FROM dpet_emp WHERE emp_no >= 10144;

...WHERE column <> 'N' ...WHERE column NOT IN (10, 11, 12) ...WHERE column IS NOT NULL

...WHERE column LIKE '%승환'

...WHERE SUBSTRING(column, 1, 1) = 'X'

...WHERE column = deterministic_function()

...WHERE char_column = 10

...WHERE utf_bin_char_column = euckr_bin_char_column

INDEX ix_test (column_1, column_2, column_3 ..., column_n)

to be or not to be. That is the question

mysql> CREATE TABLE user ( user_id BIGINT, first_name VARCHAR(10), last_name VARCHAR(10), PRIMARY KEY (user_id), INDEX ix_fullname ((CONCAT(first_name,' ',last_name))));

mysql> EXPLAIN SELECT * FROM user WHERE CONCAT(first_name, ' ', last_name) = 'Matt Lee';

mysql> CREATE TABLE user ( user_id BIGINT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(10), last_name VARCHAR(10), credit_info JSON, INDEX mx_creditscores ( (CAST(credit_info->'$.credit_scores' AS UNSIGNED ARRAY)))); mysql> INSERT INTO user VALUES (1, 'Matt', 'Lee', '{"credit_scores":[360, 353, 351]}');

mysql> UPDATE tb_test SET emp_no=100002 WHERE emp_no=100007;

1. 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택 2. NOT NULL 옵션의 유니크 인덱스 중에서 첫 번째 인덱스를 클러스터링 키로 선택 3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터링 키로 선택

MyISAM: ix_firstname 인덱스를 검색해서 레코드의 주소를 확인한 후, 레코드의 주소를 이용해 최종 레코드를 가져옴 InnoDB: ix_firstname 인덱스를 검색해 레코드의 프라이머리 키 값을 확인한 후, 프라이머리 키 인덱스를 검색해 서 최종 레코드를 가져옴

mysql> CREATE TABLE tb_parent ( id INT NOT NULL, fd VARCHAR(100) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB; mysql> CREATE TABLE tb_child ( id INT NOT NULL, pid INT DEFAULT NULL, fd VARCHAR(100) DEFAULT NULL, PRIMARY KEY (id), KEY ix_parentid (pid), CONSTRAINT child_ibfk_1 FOREIGN KEY (pid) REFERENCES tb_parent (id) ON DELETE CASCADE ) ENGINE=InnoDB; mysql> INSERT INTO tb_parent VALUES (1, 'parent-1'), (2, 'parent-2'); mysql> INSERT INTO tb_child VALUES (100, 1, 'child-100');