Mysql 基本概念

Mysql Server-->DB1:create
Mysql Server-->DB2:create
DB1-->table1:create
DB1-->table2:create
table1-->row1:create
table1-->row2:create

Mysql Server

graph LR
A[Mysql Server] -->B(DB1)
A-->C(DB2)
    C -->D[table1]
    C -->E[table2]
        D -->F(row1)
        D -->G(row2)

1.select * from tabe;

2.select * from table where id =1 ;

3.select * from table group by name having name = 'zxm';

4.select * from table where id in (select id from table2);

5.select * from table jion table2 on table.id = table2.id;

6.select count(*) from table;

7.select count(1) from table;

8.select count(id) from table;

SQL 查詢流程

Mysqlclient=>start: Mysql client
cache=>operation: 查詢緩存
result=>condition: 沒有?
resolver=>operation: 解析器
issyntaxerror=>condition: 語法解析成功？
ispreprocessorerror=>condition: 預(yù)處理錯誤?
syntaxerror=>operation: 語法錯誤 
resolvetree=>operation: 解析樹
preprocessor=>operation: 預(yù)處理器 
newresolvetree=>operation: 新解析樹
reactor=>operation: 查詢優(yōu)化器
explain=>operation: 執(zhí)行計劃
engine=>operation: 查詢執(zhí)行引擎
api=>operation: 存儲引擎
end=>end: 查詢結(jié)果
error=>end: 語法解析錯誤
preprocessorerror=>end: 預(yù)處理錯誤(列名不匹配，沒有表權(quán)限)

Mysqlclient->cache->result
result(yes)->resolver->issyntaxerror
issyntaxerror(yes)->resolvetree
issyntaxerror(no)->error
resolvetree->preprocessor->ispreprocessorerror
ispreprocessorerror(yes)->newresolvetree
ispreprocessorerror(no)->preprocessorerror
newresolvetree->reactor
reactor->explain
explain->engine->api->end
result(no)->end

SQL優(yōu)化

graph LR
A[Mysql Server] -->B(DB1)
A-->C(DB2)
    C -->D[table1]
    C -->E[table2]
        D <-->F(百萬數(shù)據(jù))
        F -->H(索引)
        H -->L(復(fù)合索引失效 最左前綴原則)
        F -->I(分區(qū))
        I -->M(什么字段分區(qū) 時間)

1.創(chuàng)建表的時候

create table user( 
id bigint(64) primary key auto_increment， 
username varchar(50) not null comment '用戶名', 
password varchar(50) not null comment '密碼', 
phone varchar(20) not null comment '電話', 
create_by varchar(50) null comment '創(chuàng)建人', 
create_time timestamp not null default CURRENT_TIMESTAMP(), 
update_by varchar(50) null comment '修改人', 
update_time timestamp null on update current_timestamp()                       
)engine = innodb;
create table role( 
id bigint(64) primary key auto_increment, 
rolename varchar(50) null comment '角色名稱' 
);    
create table user_role( 
id bigint(64) primary key auto_increment, 
user_id bigint(64), 
role_id bigint(64) 
);     

? 1.創(chuàng)建索引(where ,group by)

? 2.表分區(qū)

2.全表查詢

? 1.in

start=>start: 開始
user_role=>operation: user_role
subquery2=>operation: subquery2
user=>operation: user   
end=>end: 結(jié)束
start->user_role
user_role->subquery2
subquery2->user
user->end

? 2.where 1 = 1

? 3.where name like %${name}

? 4.where substr(name) like ${name}

? 5.where name is null

? 6.or

3.執(zhí)行計劃

explain select * from table;

explain select * from table where id = 1;

explain select * from table join table1 on table1.id = table.id;

explain select * from table group by name having name = 'zxm';

索引

新增一條記錄，mysql要做神馬操作呢？