Mysql入门操作

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要做神马操作呢？