当前位置:网站首页>Execution plan of mysql
Execution plan of mysql
2022-08-03 17:39:00 【Bugxiu_fu】
1. What is an execution plan?
Use the EXPLAIN keyword to simulate the optimizer executing SQL query statements, so as to know how MYSQL processes your SQL statements.
Execution planning process (best understood with the diagram)
The browser (client) sends a request. Before executing the database query, it finds the connection pool (recycling), connects with the database driver, finds the mysql service after obtaining the connection, then the connection pool finds the query cache, and then parses it, find the parse tree parsing, generate a new parse tree after preprocessing, query the optimizer after the new parse tree, execute sql, find the data file through the engine, read the data, return it to the cache, and finally return it to the user.
Some optimizers: (understand)
CBO: Cost-based optimizer, look at which index, and count the index according to the cost value.
RBO: Rule-based optimizer, with indexes using indexes.Then all tables with indexes will go through the index in any case
Second, the values and parameters of the execution plan
Code
explain(select * from t_users id=1unionselect * from t_users id=2);
Running Results
- select_type: category, mainly used to distinguish complex queries (union) such as ordinary queries, union queries, sub-queries, etc.
simple: a simple select query without subqueries or unions
primary: The query contains any complex subparts, the outermost query is marked
derived: The subquery is used in the from list, the table is used as the query condition, and the subquery is also calculated
union: Included in the subquery of the from clause, a temporary table will be generated when the subquery occurs
- paritiions: If the table data volume is large, set the partition conditions.
- possible_keys: possible keys (indexes)
- key: the index actually used
- key_len: length of key used (bytes)
- ref: Shows which field or constant is used with the key
- rows: how much data to traverse to find
- type: is a more important indicator, the result value is (performance) in order from best to worst
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > index_subquery > range > index > all
Note: (marked in red means that the system has gone to the index)
system: There is only one row in the table (equal to the system table);
const means that it is found once by index;
eq_ref: one-dimensional record;
ref: non-unique index scan;
range: retrieve only rows in a given range
all: full table scan
index: index coverage (scan all)
null: execute without even accessing the table or index;
- Extra: Indicates important extra information that doesn't fit in other columns
Using index: Covering the index to avoid accessing the table.
Using temporary: sorting temporary table
Using where: filter after retrieval
How to optimize the performance of myql?
The server checks the query cache first, and if it hits the cache, it immediately returns the result stored in the cache.Otherwise go to the next stage.
If a column is indexed, try to avoid using the null value, because its value is more complex and it is not easy to create an index.
边栏推荐
- 405. Convert a Number to Hexadecimal
- 401. Binary Watch
- 分享一个开源的USB-CAN工具!
- FinClip | July 2022 Product Highlights
- 多表查询最值
- 341. Flatten Nested List Iterator
- 数字IC笔迹-MCMM、WNS和TNS
- PMP考试通关宝典-敏捷专题
- How to write SQL statements in DataWorks monitoring data reaches a certain value to indicate the change of
- 【机器学习】机器学习基本概念/术语3
猜你喜欢
随机推荐
JSON.stringify()的深入学习和理解
“68道 Redis+168道 MySQL”精品面试题(带解析),你背废了吗?
ICDAR比赛技术分享
融云「音视频架构实践」技术专场【内含完整PPT】
JS中对象数组用sort按属性排序
security加密解密
#yyds干货盘点# 面试必刷TOP101:两个链表的第一个公共结点
Dataworks中PyOdps里面pandas.read_sql()支持Odps吗?
ASP.NET Core依赖注入之旅:3.Service Locator和依赖注入
使用o.execute_sql 查询很很很小的表, 要7/8秒钟, 这个怎么解决
计网知识点
sphinx coreseek的安装和php下使用
J9货币论:数字经济为全球经济复苏注入力量
Web3的开源为何会如此受到人们喜爱?
沃尔沃:这是会“种草”的“安全感”!
工程仪器设备在线监测管理系统常见问题和注意事项
软件盘点企业使用服装ERP的好处
关于vscode安装包下载太慢解决方法
fastposter v2.9.0 程序员必备海报生成器
【时间的比较】