湛江做网站建设,网站建设服务中心,阜阳网站制作公司去哪找,网站描述 关键词MySQL查询成本
MySLQ会将优化后的语句分别计算成本#xff0c;取最优SQL执行。MySQL查询成本分为#xff1a;
IO成本#xff1a;将磁盘数据加载到内存的成本。在MySQL的InnoDB引擎中就是一个叶子页。默认成本1.0.CPU成本#xff1a;数据读取检测是否复核搜索条件。默认成…MySQL查询成本
MySLQ会将优化后的语句分别计算成本取最优SQL执行。MySQL查询成本分为
IO成本将磁盘数据加载到内存的成本。在MySQL的InnoDB引擎中就是一个叶子页。默认成本1.0.CPU成本数据读取检测是否复核搜索条件。默认成本0.2.
基于成本的优化步骤 根据搜索条件分析可能用到的索引。计算全表扫描的查询成本。计算使用每个索引的查询成本。对比各种执行方案取查询成本最低的方案。 单表查询计算成本计算
准备环境
CREATE TABLE product_order (id int(11) NOT NULL AUTO_INCREMENT,order_number varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,order_desc varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,order_status char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,create_time datetime NOT NULL,expire_time datetime NOT NULL,PRIMARY KEY (id) USING BTREE,UNIQUE INDEX u_idx_three(create_time, order_status, expire_time) USING BTREE,INDEX idx_number(order_number) USING BTREE,INDEX idx_expire_time(expire_time) USING BTREE
) ENGINE InnoDB AUTO_INCREMENT 10004 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ROW_FORMAT COMPACT;创建一个表批量生成10000数据其中有三个索引 idx_number二级索引idx_expire_time二级索引create_time order_status expire_time联合索引 成本分析
要分析的SQL
SELECT*
FROMproduct_order o
WHEREo.order_number IN ( 9671332, 9504815, 2890999 )AND o.expire_time 2024-06-01 AND o.expire_time 2022-06-01AND o.order_status 0AND o.order_desc LIKE %这是备注%;分析过程
分析可能用到的索引order_number或者expire_time。全表扫描成本分析
show TABLE status like product_order; -- 查看系统文件其中两个属性 rows预估行数:9907 data_length数据大小:1589248全表扫描成本为 IO成本在InnoDB存储引擎中一个叶子页的大小即一次IO一个叶子大小为16KB所以IO成本为(data_length/16/1024) * 1 微调值(默认1.1)。CPU成本rows * 0.2 微调值默认1.0。在本SQL中的全表扫描成本为1589248/16/1024 * 0.1 1.1 9907 * 0.2 1.0 1993.2。 分析可能用到的索引下的查询成本 使用索引查询的成本为二级索引IO成本 回表操作的成本。 使用expire_time索引查询成本 二级索引成本如果使用到了索引且索引可以锁定某些范围也就是B树子节点的数据范围每有一个范围IO成本1因为o.expire_time ‘2024-06-01’ AND o.expire_time ‘2022-06-01’ 可以确定一个范围所以IO成本为1回表成本 找到数据最左边数据然后找到最右边的数据这个过程成本忽略不计。计算范围内数据量MySQL会进行估算此处为2721条。 cpu成本就为2721*0.20.01微调值 544.21每次回表是一次IO所以IO成本为2721*1 2721得到数据后过滤除使用索引的其他条件这里是CPU成本2721*0.2 544.2 使用expire_time索引查询成本为2721 544.21 544.2 3809.41比全表扫描成本还高所以不会使用这个二级索引原因是回表的数据量高达2000。 使用order_number索引的查询成本 二级索引成本in条件相当于三个叶子页所以IO成本为 3;回表成本同上面逻辑 30.20.01微调值 31 3*0.2 4.21总成本 3 4.21 7.21 验证通过explain format json select语句查看SQL的查询成本7.21
{query_block: {select_id: 1,cost_info: {query_cost: 7.21},table: {table_name: o,access_type: range,possible_keys: [idx_number,idx_expire_time],key: idx_number,used_key_parts: [order_number],key_length: 34,rows_examined_per_scan: 3,rows_produced_per_join: 0,filtered: 1.67,index_condition: (my-test.o.order_number in (9671332,9504815,2890999)),cost_info: {read_cost: 7.20,eval_cost: 0.01,prefix_cost: 7.21,data_read_per_join: 54},used_columns: [id,order_number,order_desc,order_status,create_time,expire_time],attached_condition: ((my-test.o.expire_time 2024-06-01) and (my-test.o.expire_time 2022-06-01) and (my-test.o.order_status 0) and (my-test.o.order_desc like %这是备注%))}}
}总结 在使用二级索引的时候如果需要回表回表的成本是及其高的所以MySQL一般会使用通过二级索引检索出来数据量少的索引因为需要回表的数据量少成本低。 Optimizer Trace优化器追踪
查看Optimizer Trace是否开启默认关闭。
show variables like Optimizer Trac;-- 查看开启Optimizer Trace注意开启是session级别的。
set optimizer_trace enabled on;查看分析内容,会看到一个json串
select * from information_schema.OPTIMIZER_TRACE;json串中的参数 join_preparation:准备阶段expanded_query:优化后的SQL语句。join_optimization:分析阶段original_condition:索引处理。analyzing_range_alternatives:分析索引查看成本considered_execution_plans:执行阶段 两表连接查询成本计算
两表连接查询的成本单次查询驱动表的成本多次查询被驱动表的成本。其中单次查询驱动表的成本称为驱动表扇出fanout);被驱动表的查询次数取决于驱动表的扇出。
单词驱动表的删除是MySQL根据一定的算法进行猜测。因为如果驱动表的扇出可以根据驱动表的where条件确定范围是可以计算的如果where条件无法确定范围只能猜测。
多表连接查询成本计算
再多表连接查询时候如果使用内连接查询多个表可作为驱动表的表很多而且连接顺序也很多。怎么办
提前结束 如果计算的成本大于前面计算过的执行计划成本会结束运算。 控制连接的深度MySQL有一个参数可以控制连接的深度笼统地说就是超过多少个表就不会继续计算查询成本了
show variables like optimizer_search_depth;-- 默认62启发式规则 mysql根据以往SQL经验计算查询成本。 成本计算参数
因为每个服务器的IO效率和CPU效率不同可以通过调节来提高效率。这里的参数就是我们计算IO和CPU开销的成本计算参数。一般不建议调整因为不了解MySQL计算成本的底层逻辑。
如果未设置使用默认值。
select * from mysql.server_cost;
select * from mysql.engine_cost;
