sql server management Studio 设置回显表结构

转载

mob64ca14031c97 2024-09-05 09:55:27

文章标签 数据库 mysql 大数据数据执行时间 文章分类 SQL Server 数据库

回表优化-mysql&dm7测试分析

回表说明

一、mysql回表优化测试

1.1 测试-数据初始化
1.2 测试分析

二、dm7回表优化测试

2.1 测试-数据初始化
2.2 测试分析

三、测试总结

回表说明

通过索引访问得到表的ROWID，然后根据这些ROWID再去访问表中数据行，就称为回表。Oracle如果执行计划里出现table access by rowid，mysql5.6及以上出现Using MRR就说明要回表。
减少回表次数将提高SQL查询性能。

一、mysql回表优化测试

1.1 测试-数据初始化

1、定义测试表：

CREATE TABLE test (id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
 val INT(10) UNSIGNED NOT NULL DEFAULT 0,
 source INT(10) UNSIGNED NOT NULL DEFAULT 0
 );
 CREATE INDEX test_val_index ON test(val ASC);

2、注入500百万条随机测试数据：

DROP PROCEDURE IF EXISTS twhile;
 DROP PROCEDURE IF EXISTS tcountinsert;DELIMITER //
 CREATE PROCEDURE twhile(IN total INT)
 BEGIN
 DECLARE i INT;
 SET i = 0;
 WHILE i <total DO
 INSERT INTO test (val,source) VALUES (FLOOR(RAND() * 1000),FLOOR(RAND() * 10000));
 SET i = i+1;
 END WHILE;
 COMMIT;
 END
 //
 DELIMITER ;DELIMITER //
 CREATE PROCEDURE tcountinsert(IN pageCount INT,IN allCount INT)
 BEGIN
 DECLARE i INT;
 SET i = 0;
 WHILE i <allCount DO
 CALL twhile(pageCount);
 SET i = i+1;
 END WHILE;
 END
 //
 DELIMITER ;CALL tcountinsert(10000,500);

sql server management Studio 设置回显表结构_数据库

初始化数据，执行时间26分钟6秒；

1.2 测试分析

1、查询val>500,从20万开始的5条数据，sql如下：

SELECT * FROM test WHERE val>500 LIMIT 200000,5;

sql server management Studio 设置回显表结构_mysql_02

执行时间：4.39秒

Sql解析：

sql server management Studio 设置回显表结构_执行时间_03

使用了val上的范围索引，并使用了MRR策略的回表查询。

2、回表优化后sql如下：

SELECT * FROM test a INNER JOIN (SELECT id FROM test WHERE val>500 LIMIT 200000,5) b ON a.id=b.id;

sql server management Studio 设置回显表结构_数据_04

执行时间：0.17秒

Sql解析：

sql server management Studio 设置回显表结构_mysql_05

使用了val上的范围索引，并使用了Using where策略，避免了回表查询。

3、优化后查询时间提升26倍。

二、dm7回表优化测试

2.1 测试-数据初始化

由于达梦dm7在字段较少时，减少回表的优化体现不明显，因此多加入10个字段（t1~t7）

create tablespace test5 datafile ‘/dm7/data/DAMENG/test5_01.dbf’ size 1024;
 create user test5 identified by dameng123 default tablespace test5;CREATE TABLE test5.test (id int PRIMARY KEY identity(1,1),
 val int NOT NULL DEFAULT 0,
 source int NOT NULL DEFAULT 0,
 t1 int NOT NULL DEFAULT 0,
 t2 int NOT NULL DEFAULT 0,
 t3 int NOT NULL DEFAULT 0,
 t4 int NOT NULL DEFAULT 0,
 t5 int NOT NULL DEFAULT 0,
 t6 int NOT NULL DEFAULT 0,
 t7 int NOT NULL DEFAULT 0,
 t8 int NOT NULL DEFAULT 0,
 t9 int NOT NULL DEFAULT 0,
 t10 int NOT NULL DEFAULT 0
 );
 CREATE INDEX test5.test_val_index ON test5.test(val ASC);

– 创建存储过程twhile，针对单次插入

CREATE PROCEDURE TEST5.TWHILE(TOTAL IN INT) as
 BEGIN
 declare
 I INT;
 begin
 for I in 1…TOTAL loop
 INSERT INTO test5.test (val,source,t1,t2,t3,t4,t5,t6,t7,t8,t9,t10) VALUES (FLOOR(RAND() * 1000),FLOOR(RAND() * 10000),
 FLOOR(RAND() * 100),FLOOR(RAND() * 100),FLOOR(RAND() * 100),FLOOR(RAND() * 100),FLOOR(RAND() * 100),FLOOR(RAND() * 100),FLOOR(RAND() * 100),FLOOR(RAND() * 100)
 ,FLOOR(RAND() * 100),FLOOR(RAND() * 100));
 end loop;
 COMMIT;
 end;
 END;

– 创建存储过程tCountInsert，针对单次插入,pageInsert每次事务插入量，allIndex插入总次数

CREATE or replace PROCEDURE TEST5.TCOUNTINSERT(PAGEINSERT IN INT,ALLINDEX IN INT) as
 BEGIN
 declare
 I INT;
 SQLTEXT varchar2(100);
 begin
 for I in 1…ALLINDEX loop
 SQLTEXT := ‘call TEST5.twhile(’||PAGEINSERT||’)’;
 EXECUTE IMMEDIATE SQLTEXT;
 end loop;
 COMMIT;
 dbms_stats.gather_table_stats(‘TEST5’,‘TEST’);–更新统计信息
 end;
 END;
 call TEST5.TCOUNTINSERT(10000,500);

sql server management Studio 设置回显表结构_执行时间_06