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)
id | name | remark |
---|---|---|
1 | zxm | |
2 | zzm | |
30000000 | sgf |
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: 预处理错误?
syntaxerror=>operation: 语法错误
resolvetree=>operation: 解析树
preprocessor=>operation: 预处理器
newresolvetree=>operation: 新解析树
reactor=>operation: 查询优化器
explain=>operation: 执行计划
engine=>operation: 查询执行引擎
api=>operation: 存储引擎
end=>end: 查询结果
error=>end: 语法解析错误
preprocessorerror=>end: 预处理错误(列名不匹配,没有表权限)
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优化
graph LR
A[Mysql Server] -->B(DB1)
A-->C(DB2)
C -->D[table1]
C -->E[table2]
D <-->F(百万数据)
F -->H(索引)
H -->L(复合索引失效 最左前缀原则)
F -->I(分区)
I -->M(什么字段分区 时间)
1.创建表的时候
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 '创建人',
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.创建索引(where ,group by)
2.表分区
2.全表查询
1.in
start=>start: 开始
user_role=>operation: user_role
subquery2=>operation: subquery2
user=>operation: user
end=>end: 结束
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.执行计划
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要做神马操作呢?
网友评论