Mysql的大体结构
客户端：用于链接mysql的软件
连接池：
sql接口：
查询解析器：
MySQL连接层
连接层：
应用程序通过接口（如odbc,jdbc）来连接mysql，最先连接处理的是连接层。
连接层包括：
1.通信协议
2.线程处理
3.用户名密码认证
show variables like "%max_connections%";查询最大连接数
短连接 mysql数据库的连接 => 优化器解析 => 数据返回 => 关闭连接
长链接 mysql数据库的连接 => 优化器解析 => 数据返回 => 连接 => 数据返回 ... 8个小时
连接权限校验：
1.用户名密码
2.连接权限校验
select 'host','user' from mysql.'user'; create user 'admin'@'192.168.15.%' identified by 'root';
-- create user '用户名'@'ip' IDENTIFIED BY "密码" grant all on . to 'admin'@'192.168.15.%' WITH GRANT OPTION;
-- 设置允许远程用户访问
mysql的sql层
SQL层：
sql层是mysql的核心，mysql的核心服务都是在这层实现的。主要包含权限判断、解析器、预处理、查询优化器、缓存和执行计划
mysql8没有查询缓存
.权限判断可以审核用户有没有访问某个库、某个表，或者表里某行数据的权限。
.查询解析器针对SQL语句进行解析，判断语法是否正确。
.预处理器对解析器无法解析的语法进行处理。
.查询优化器对SQL进行改写和相应的优化，并生成最优的执行计划，就可以调用程序的API接口，通过存储引擎层访问数据。
sql接口 => 接收sql语句
sql语句的类型：
dml（insert，update，delete）
query（select）
ddl（alter）
status（show status）
create user 'xinkong'@'%' identified by "root";
Grant all on xx.* to 'xinkong'@'%' with grant option;
alter table user add index idx_age(age); --添加索引
alter table user add index idx_name_age(name,age);
drop index idx_age on user; ---删除索引
解析器：
select * from user where name = 'xx' and age = 18;
优化器：
SQL语句在 查询之前会使用查询优化器对查询进行优化，同时验证用户是否有权限进行查询。
获取表结构信息：字段信息，字段类型，表存储位置 ，索引信息
权限校验：Grant all on xx.* to '用户名'@'host' with grant option;
条件过滤与调整，根据索引确定计划：
set optimizer_trace="enabled=on";--开启trace查看优化器的结果
set end_marker_in_json=on;--增加注释
select * from information_schema.optimizer_trace \G;--查询打印执行计划
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `user`.`id` AS `id`,`user`.`name` AS `name` from `user` where ((`user`.`id` > 100000) and (`user`.`age` > 30))"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`user`.`id` > 100000) and (`user`.`age` > 30))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`user`.`id` > 100000) and (`user`.`age` > 30))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`user`.`id` > 100000) and (`user`.`age` > 30))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`user`.`id` > 100000) and (`user`.`age` > 30))"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [
{
"table": "`user`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`user`",
"range_analysis": {
"table_scan": {
"rows": 97925,
"cost": 9866.85
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"id"
] /* key_parts */
},
{
"index": "idx_age",
"usable": true,
"key_parts": [
"age",
"id"
] /* key_parts */
},
{
"index": "idx_name_age",
"usable": true,
"key_parts": [
"name",
"age",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"best_covering_index_scan": {
"index": "idx_name_age",
"cost": 10064.8,
"chosen": false,
"cause": "cost"
} /* best_covering_index_scan */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "query_references_nonkey_column"
},
{
"index": "idx_age",
"usable": false,
"cause": "query_references_nonkey_column"
},
{
"index": "idx_name_age",
"tree_travel_cost": 0.85,
"num_groups": 49161,
"rows": 97925,
"cost": 98534.7
}
] /* potential_skip_scan_indexes */
} /* skip_scan_range */,
"best_skip_scan_summary": {
"type": "skip_scan",
"index": "idx_name_age",
"key_parts_used_for_access": [
"name",
"age"
] /* key_parts_used_for_access */,
"range": [
"30 < age"
] /* range */,
"chosen": false,
"cause": "cost"
} /* best_skip_scan_summary */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "PRIMARY",
"ranges": [
"100000 < id"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 48962,
"cost": 4911.7,
"chosen": true
},
{
"index": "idx_age",
"ranges": [
"30 < age"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 48962,
"cost": 17137,
"chosen": false,
"cause": "cost"
},
{
"index": "idx_name_age",
"chosen": false,
"cause": "no_valid_range_for_this_index"
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "PRIMARY",
"rows": 48962,
"ranges": [
"100000 < id"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 48962,
"cost_for_plan": 4911.7,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`user`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 48962,
"access_type": "range",
"range_details": {
"used_index": "PRIMARY"
} /* range_details */,
"resulting_rows": 48962,
"cost": 9807.9,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 48962,
"cost_for_plan": 9807.9,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`user`.`id` > 100000) and (`user`.`age` > 30))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`user`",
"attached": "((`user`.`id` > 100000) and (`user`.`age` > 30))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"finalizing_table_conditions": [
{
"table": "`user`",
"original_table_condition": "((`user`.`id` > 100000) and (`user`.`age` > 30))",
"final_table_condition ": "((`user`.`id` > 100000) and (`user`.`age` > 30))"
}
] /* finalizing_table_conditions */
},
{
"refine_plan": [
{
"table": "`user`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
