mysql结构与sql执行流程

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 */
}