那些网站是php开发的,长沙网络营销哪家平台专业,聊天软件开发教程,网站标头设计Mysql的大体结构
客户端#xff1a;用于链接mysql的软件 连接池#xff1a; sql接口#xff1a; 查询解析器#xff1a; MySQL连接层
连接层#xff1a;
应用程序通过接口#xff08;如odbc,jdbc#xff09;来连接mysql#xff0c;最先连接处理的是连接层。
连接层…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 admin192.168.15.% identified by root;
-- create user 用户名ip IDENTIFIED BY 密码 grant all on . to admin192.168.15.% WITH GRANT OPTION;
-- 设置允许远程用户访问 mysql的sql层
SQL层
sql层是mysql的核心mysql的核心服务都是在这层实现的。主要包含权限判断、解析器、预处理、查询优化器、缓存和执行计划
mysql8没有查询缓存
.权限判断可以审核用户有没有访问某个库、某个表或者表里某行数据的权限。
.查询解析器针对SQL语句进行解析判断语法是否正确。
.预处理器对解析器无法解析的语法进行处理。
.查询优化器对SQL进行改写和相应的优化并生成最优的执行计划就可以调用程序的API接口通过存储引擎层访问数据。 sql接口 接收sql语句
sql语句的类型
dmlinsertupdatedelete
queryselect
ddlalter
statusshow 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_traceenabledon;--开启trace查看优化器的结果
set end_marker_in_jsonon;--增加注释
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 */
}
