史上最全存储引擎、索引使用及SQL优化的实践
- 2019 年 10 月 3 日
- 筆記
??????????????SQL?????
??MySQL Server????? ?
Connection Pool ??????
Management Services & Utilities ??????????
SQL Interface ?SQL????
Parser ????????
Optimizer ??????
Caches & Buffers ??????
Pluggable Storage Engines ?????
File System ?????
1????
???????????????????sock???????????/???????????TCP/IP????????????????????????????????????????????????????????????????????????????SSL??????????????????????????????????
2????
?????????????????????SQL????????????SQL???????????????????????????????????????????????????????????????????????????????????????????????????????????????select?????????????????????????????????????????????????????
3????
????????????????MySQL???????????????API??????????????????????????????????????????????????
4????
??????????????????????????????????
?????????MySQL??????????????????????????????????????????????????????????????????????????????????????????????????????????
2.1 ??????
???????????MySQL????????????????????????????????? ???????????????????????????????????????????????????????????????? Oracle?SqlServer?????????????MySQL???????????????MySQL????????? ??????????????????????? MySQL5.0????????? ?InooDB?MyISAM?BDB?MEMORY?MERGE?EXAMPLE?NDB Cluster?ARCHIVE? CSV?BLACKHOLE?FEDERATED????InnoDB?BDB?????????????????????? ??????show engines???????????????? ? 
???????????????????????????????MySQL5.5??????????MyISAM?5.5??????InnoDB?
??MySQL?????????? ??? ?
show variables like ‘%storage_engine%’;
???????????????????????????????????? ?
2.2.1 InnoDB
InnoDB?????MySQL????????InnoDB???????????????????????????????MyISAM?????? InnoDB??????????????????????????????? InnoDB???????????????? ? ???? 
????InnoDB???????? ???? MySQL???????????InnoDB????????????????????????????????????????? ?????? ????????country_innodb????country_id??????city_innodb?????country_id????????? country_innodb????country_id 
??????????????????????????????????RESTRICT?CASCADE?SET NULL?NO ACTION?
RESTRICT?NO ACTION???????????????????????????
CASCADE???????????????????????????
SET NULL????????????????????????SET NULL.
???????????????????ON DELETE RESTRICT ON UPDATE CASCADE????????????????????????????????????????????????????????????????
ON DELETE RESTRICT —-> ????????????????????
ON UPDATE CASCADE —-> ???????????????????????
????????? ?
????
InnoDB????????????? ?
?1??????????????????????????.frm????????????innodb_data_home_dir?innodb_data_file_path????????????????
?2??????????????????????????.frm????????????????????.ibd??
2.2.2 MyISAM
MyISAM?????????????????????????????????????SELECT?INSERT??????????????? ??????????????????? ? ?????
???????????MyISAM???????????????
MySQL???????????show [session | global] status??????????????show [session | global] status ?????????? “session”??“global”???session????????????global???????????????????????????????“session”?
??????????session????????? ?
show status like “Com_”;
show status like ‘Innodb_rows_%’;
Com_xxx????xxx??????????????????????????
Com_*** : ???????????????????????
Innodb_*** ??????????InooDB????????????????
???????????????????SQL???
1?????? ???????????????????SQL????–log-slow-queries[=file_name]??????mysqld?????????????long_query_time??SQL????????
2?show processlist : ????????????????????????????????????????????????????? show processlist ????MySQL????????????????????????????SQL????????????????????
1?id??????mysql???????”connection_id”???????connection_id()??
2?user?????????????root????????????????sql??
3?host????????????ip????????????????????????
4?db??????????????????
5?command???????????????????????sleep?????query?????connect??
6?time??????????????????
7?state???????????sql????????????state??????????????????sql??????????????copying to tmp table?sorting result?sending data?????????
8?info??????sql?????????????????
?????????????SQL????????EXPLAIN??DESC????MySQL????SELECT?????????SELECT???????????????????
??SQL??????? ?
explain select * from tb_item where id = 1;
explain select * from tb_item where title = ‘?????ot-979???? ??3G??3’;
id???select??????????????????????select????????????id????? ?
1?id????????????????
explain select * from t_role r,t_user u,user_role ur where r.id = ur.role_id and u.id = ur.user_id;
2?id??id????????????????
EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = ‘stul’))
3?id??????????????id???????????????????????????id????????????????
EXPLAIN SELECT * FROM t_role r,(SELECT * FROM user_role ur WHERE ur.‘user_id’ = ‘2’) a WHERE r.id = a.role_id;
??SELECT??????????????? ?
SIMPLE ????select??????????????UNION
PRIMARY ???????????????????????????
SUBQUERY ??SELECT?WHERE?????????
DERIVED ??FROM??????????????DERIVED????MySQL????????????????????
UNION ?????SELECT???UNION???????UNION??UNION???FROM??????????SELECT????? ?DERIVED
UNION RESULT ??UNION??????SELECT
????????????????
type???????????????????????? ?
NULL ?MySQL????????????????
system ??????????????????const????????????
const ??????????????const????primary key??????unique????????????????????????????where????MySQL??????????????const??“??”?“??”????????????????
eq_ref ???ref?????????????????????????????????????????????????
ref ???????????????????????????????????????????????????????
range ??????????????????????where????between?<?>?in????
index ?index?ALL???? index??????????????ALL??ALL????????
all ?????????????
???????????? ?
NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system > const > eq_ref > ref > range > index > ALL
?????????????????range???????ref?
possible_keys : ????????????????????
key ????????????null?????????
key_len : ??????????????????????????????????????????????????????
??????
???????????????????
using filesort : ??mysql???????????????????????????????????“????”????
using temporary ??????????????MySQL??????????????????order by ?group by????
using index ??????select?????????????????????????
?extra???using filesort?using temporary??????sql????
??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????order by??????????????????????????????????????????????????ES?
MySQL?5.0.37???????? show profiles?show profile??????show profiles????SQL????????????????????
??have_profiling?????????MySQL????profile?
??profiling?????????set???Session????profiling?
set profiling=1; // ??profiling???
??profile???????????SQL??????
????????????????????? ?
show databasesl
use db01;
show tables;
select * from tb_item where id < 5;
select count(*) from tb_item;
?????????????show profiles??????SQL??????? ?
??show profile for query query_id ????????SQL?????????????????? ?
TIP :
Sending data ????MySQL???????????????????????????????????Sending data????MySQL???????????????????????????????????
????????????????MySQL???????all?cpu?block io?context switch?page faults???????MySQL????????????????????????CPU????? ?
show PROFILE ALL for QUERY 58; ?????????????
MySQL5.6????SQL???trace???trace?????????????????A????????B???
??trace??????JSON????trace????????????????????????????????????
set optimizer_trace=“enabled=on”,end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
??SQL?? ?
select * from tb_item where id < 4;
?????information_schema.optimizer_trace?????MySQL?????SQL? ?
select * from information_schema.optimizer_traceG;
?????SQL????SOAR???????
????????????????????????????????????????MySQL????????
??????300?????
A. ??ID??
select * from tb_item where id = 1999;
?????????0??????????id ????????
????????????????????
1?.???????????????????
???????????????
explain select * from tb_seller where name = ‘????’ and status = ‘1’ and address = ‘???’;
2?.??????
???????????????????????????????????????????????????
???????????? ?
???????????name?status?address?????????????name?????????status?????address????????????where?????and??????????????????
explain select * from tb_seller where name=‘????’ and address=‘???’;
???????????????name???????address???????key_len??403??name??????403??????????????status??????????????
3?.????????????????
?????????name?status???????????????address???????
4?.????????????????????
5?.????????????????
???????????????????MySQL?????????????????????????
6?.???????????select *
?????????????????????????????????select *?
???????????????????
TIP ?
using index : ?????????????
using where ?????????????????????????
using index condition ???????????????????
using index ?using where ??????????????????????????????????????
7??or?????????or????????????????????????????????????
?? ?name????????createtime?????????or?????????? ?
explain select * from tb_seller where name=‘??’ or createtime = ‘2018-01-01’?
8??%???Like??????????
??????????????????????????????????
???? ???????????????? ?
9???MySQL???????????????????
????????address?11?????????????????MySQL?????????????????????????????????????address?????MySQL????????????
10?is NULL?is NOT NULL ???????
??????????????null??????not null??????null????where??????is not null??MySQL????????????????not null????where??????is not null??MySQL???????????????????
11?in????not in?????
12?????????? ?????????????????? ?????? ? create index idx_name_sta_address on tb_seller(name,status,address); ?????????? ? name name + status name + status + address ?????? create index idx_seller_name on tb_seller(name); create index idx_seller_status on tb_seller(status); create index idx_seller_address on tb_seller(address); ??????????????????????????? show status like ‘Handler_read%’;
show global status like ‘Handler_read%’;
Handler_read_first : ??????????????????????????????????????????
Handler_read_key : ?????????????????????????????????????????????????????????????????
Handler_read_next : ???????????????????????????????????????????
Handler_read_prev : ?????????????????????????ORDER BY … DESC?
Handler_read_rnd : ?????????????????????????????????????????????????MySQL???????????????????????????????????????????????
Handler_read_rnd_next : ??????????????????????????????????????????????????????????
???load?????????????????????????
??InnoDB????????????????????? ?
1???????
??InnoDB???????????????????????????????????????????????????InnoDB?????????????????????????????????????????????????????????????
??ID??????? ?
????????????
????????????
2? ???????
????????SET_UNIQUE_CHECKS=0?????????????????SET_UNIQUE_CHECKS=1???????????????????
3???????
??????????????????????SET AUTOCOMMIT=0????????????????SET AUTOCOMMIT=1???????????????????
??????insert?????????????????????
??????????????????????????????insert????????????????????????????????????????????insert????
???????? ?
??????? ?
???????????
1????????????????????????filesort????????????????????????FileSort???
2?????????????????????????????using index???????????????
?????
1?order by??????????????????????????????????
2??????????????????????????????????
??MySQL?????????????? ?
???????????????????????where???order by??????????order by?????????????order by?????????????????????????????????FileSort?
??????????????FileSort????????????????????FileSort?????????FileSort????????FileSort?MySQL??????? ?
1??????? ?MySQL4.1????????????????????????????????????sort buffer??????sort buffer????????temporary table????????????????????????????????????????I/O???
2??????? ??????????????????????sort buffer??????????????????????????????????????
MySQL????????max_length_for_sort_data????Query?????????????????????????max_length_sort_data????????????????? ?????????
??????sort_buffer_size?max_length_for_sort_data??????????????????????? 
??GROUP BY?????????????????ORDER BY???GROUP BY????????????????????????????????????????????????????????????GROUP BY????????ORDER BY??????????
??????group by?????????????????????oerder by null??????? ?
drop index inx_emp_age_salary on emp;
explain select age,count() from emp group by age;
???
explain select age,count() from emp group by age order by null;
??????????????SQL??????“filesort”????SQL??order by null?????“filesort”??????FileSort?????????
?????????????????
create index idx_emp_age_salary on emp(age,salary);
MySQL4.1?????????SQL?????????????SELECT??????????????????????????????????????????????????????????????????SQL???????????????????????????????????????????????????JOIN????
?? ?????????????? ?
explain select * from t_user where id in (select user_id user_role);
????? ?
??? ?
explain select * from t_user u, user_role ur where u.id = ur.user_id;
????OR???????????????OR??????????????????????????????????????????????
??emp?????? ?
?? ?
explain select * from emp where id = 1 or age = 30?
????union ??or ?
???type?const??????range
??????????????????type?ref???
type?????????????????????????????? ?
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >
range > index > ALL
UNION???type??const?OR???type??range????????????????
UNION???ref??const?OR???type??null?const??????????????????????
UNION???OR?
????????????????????????????????????????? limit 20000000?10?????MySQL???2000010???????2000000 – 2000010??????????????????????
????????????????????????????????????
??SQL???????
??????????????????????????limit?????????????
SQL?????????????????????????SQL???????????????????????
??????????????use index?????MySQL?????????????MySQL????????????
create index idex_seller_name on tb_seller(name);
???????????MySQL????????????????ignore index??hint
explain select * from tb_seller ignore index(idx_seller_name) where name = ‘????’;
???MySQL?????????????????force index??hint?
create index inx_seller_address on tb_seller(address);
