xml地图|网站地图|网站标签 [设为首页] [加入收藏]
关于SSMS显示select出来的数据行的疑问,SERVER中隐
分类:数据库

其实这是一篇没有技术含量的文章,精通SQL优化的请绕道。这个缘起于在优化一个SQL过程中,同事问了我一个问题,为什么SQL中存在隐式转换,但是执行计划没有变? 我思索了一下,觉得这个问题也有点意思,说不定有些对隐式转换了解得不深入的同学都有此疑问,那么下面结合上下文场景做一个细节方面的解答。

 

 

我们一个系统中使用了ORMLite框架,粗心的开发人员弄出了不少下面这样的SQL语句,都存在隐式转换问题,如下所示,表machine_stop_alarm_msg 的结构如下,字段machine_no、status都为VARCHAR(10),但是下面SQL,传入的变量@P0,@P1都是NVARCHAR(4000)类型。

直方图是表上某个字段在按照一定百分比和规律采样后的数据分布的一种描述,最重要的作用之一就是根据查询条件,预估符合条件的数据量,为sql执行计划的生成提供重要的依据
在MySQL 8.0之前的版本中,MySQL仅有一个简单的统计信息却没有直方图,没有直方图的统计信息可以说是没有任何意义的。
MySQL 8.0新特性之一就是开始支持统计信息的直方图,这个概念很早就提出来了,抽空具体尝试了一下使用方法。

调试存储过程时,往往可以用print将存储过程中的变量print出来,

 

之前写过MSSQL相关统计信息的一点东西,在原理上都是一致的,

但是print出来的字符串有一定长度限制,刚才专门试了一下,应该是4000个字符

澳门新葡亰手机版 1

 

如果超过4000个字符,超长的字符会被自动截断,这一点应该比较清楚。

DECLARE  @P0 nvarchar(4000),@P1 nvarchar(4000);

 

SET @P0='1';

SET @P1='K172';

 

SELECT [recid],[machine_no] 

   ,[stop_stime] 

   ,[stop_etime] 

   ,[status] 

   ,[memo] 

   ,[createddate]  

FROM machine_stop_alarm_msg t  

WHERE 1=1  

AND t.status=@P0  

AND t.machine_no in(@P1 )  

ORDER BY machine_no, 

   stop_stime ;  

照旧,直接上例子,造数据,创建一个测试环境

 

 

create table test
(
    id int auto_increment primary key,
    name varchar(100),
    create_date datetime ,
    index (create_date desc)
);


USE `db01`$$

DROP PROCEDURE IF EXISTS `insert_test_data`$$

CREATE DEFINER=`root`@`%` PROCEDURE `insert_test_data`()
BEGIN
    DECLARE v_loop INT;
    SET v_loop = 100000;
    WHILE v_loop>0 DO
        INSERT INTO test(NAME,create_date)VALUES (UUID(),DATE_ADD(NOW(),INTERVAL -RAND()*100000 MINUTE) );
        SET v_loop = v_loop - 1;
    END WHILE;
END$$

DELIMITER ;

对于超过4000的字符串没办法print出来的时候,往往可以选择select @v_str这种方式,将后台的字符串select出来,然后粘贴出来查看

 

MySQL中统计信息的创建,不同于MSSQL,MySQL统计信息不依赖于索引,需要单独创建,语法如下

但是今天发现不好使了,

machine_stop_alarm_msg 表只有一个聚集索引PK_machine_stop_alarm_msg,字段为recid。

--创建字段上的统计直方图信息
ANALYZE TABLE test UPDATE HISTOGRAM ON create_date,name WITH 16 BUCKETS;
--删除字段上的统计直方图信息
ANALYZE TABLE test DROP HISTOGRAM ON create_date

注意字符串是"aaaaa…………bbbbb",结尾时bbbbb

澳门新葡亰手机版 2

1,可以一次性创建多个字段的统计信息,系统会逐个创建列出的字段上的统计信息,统计信息不依赖于索引,这一点与MSSQL不同(当然MSSQL也可以抛开索引独立创建统计信息)
2,BUCKETS值是一个必须提供的参数,默认值为1000,范围是1-1024,这一点也不同与MSSQL也不一样,MSSQL是有一个类似的最大值为200的步长(step)字段
3,一般来说,数据量较大的情况下,对于不重复或者重复性不高的数据,BUCKETS值越大,描述出来的统计信息越详细
4,统计信息的具体内容在 information_schema.column_statistics中,但是可读性并不好,可以根据需求自行解析(出来一种自己喜欢的格式)

我随意定义一个超级长的字符串,可以看出来len是87049,同时用select @str 给他select 出来

 

与sqlserver中的统计信息一样,理论上,在准确性与取样百分比(BUCKETS)是成正比的,当然生成统计信息的代价也就越大,
至于BUCKETS与统计信息的取样百分比,以及综合代价,笔者暂时没有找到相关的资料。

澳门新葡亰手机版 3

当时我优化的时候,就觉得这个SQL语句存在两个问题:1 缺少索引; 2 存在隐式转换问题。当时创建了下面索引,并要求开发人员修改SQL,避免隐式转换。

如下是通过ANALYZE TABLE test UPDATE HISTOGRAM ON create_date WITH 4 BUCKETS;创建的统计信息直方图
可以发现直方图的HISTOGRAM字段是一个JSON格式的字符串,可读性并不好。

 

CREATE NONCLUSTERED INDEX ix_machine_stop_alarm_msg_n1

ON [dbo].[machine_stop_alarm_msg] ([machine_no],[status])

INCLUDE ([recid],[stop_stime],[stop_etime],[memo],[createddate])

GO

澳门新葡亰手机版 4

但是我从查询结果select @str的结果中,将这个字符串复制出来之后,字符串的长度就被截断了,我从上面select出来的结果中复制

 

想到了sqlserver中DBCC SHOW_STATISTICS的直方图信息,如下的格式,直方图中的数据分布情况看起来非常清晰直观

新贴到一个查询窗口中,发现结尾不是上面定义时候的bbbbb,如截图,通过select len这个字段,发现是长度是43677

 

澳门新葡亰手机版 5

澳门新葡亰手机版 6

在测试环境测试时,我们先不增加这个索引,就出现了下面一个场景,两者都是走聚集索引扫描:

于是就做了一个MySQL直方图的格式转换,说白了就是解析information_schema.column_statistics表中的HISTOGRAM 字段中的JSON内容
如下,一个简单的解析直方图统计信息json数据的存储过程,参数分别是库名,表名,字段名

澳门新葡亰手机版 7

 

DELIMITER $$

USE `db01`$$

DROP PROCEDURE IF EXISTS `parse_column_statistics`$$

CREATE DEFINER=`root`@`%` PROCEDURE `parse_column_statistics`(
    IN `p_schema_name` VARCHAR(200),
    IN `p_table_name` VARCHAR(200),
    IN `p_column_name` VARCHAR(200)
)
BEGIN

    DECLARE v_histogram TEXT;
    -- get the special HISTOGRAM
    SELECT HISTOGRAM->>'$."buckets"' INTO v_HISTOGRAM 
    FROM   information_schema.column_statistics
    WHERE schema_name =  p_schema_name 
    AND table_name = p_table_name 
    AND column_name = p_column_name; 

    -- remove the first and last [ and ] char
    SET v_histogram = SUBSTRING(v_HISTOGRAM,2,LENGTH(v_HISTOGRAM)-2);

    DROP TABLE IF EXISTS t_buckets ;
    CREATE TEMPORARY TABLE t_buckets
    (
        id INT AUTO_INCREMENT PRIMARY KEY,
        buckets_content VARCHAR(500)
    );

    -- split by "]," and get single bucket content    
    WHILE (INSTR(v_histogram,'],')>0) DO
        INSERT INTO t_buckets(buckets_content)
        SELECT SUBSTRING(v_histogram,1,INSTR(v_histogram,'],'));
        SET v_HISTOGRAM = SUBSTRING(v_histogram,INSTR(v_histogram,'],')+2,LENGTH(v_histogram));    
    END WHILE;
   
    INSERT INTO t_buckets(buckets_content) 
    SELECT v_histogram;

    -- get the basic statistics data
    WITH cte AS
    (
        SELECT 
        HISTOGRAM->>'$."last-updated"' AS last_updated,
        HISTOGRAM->>'$."number-of-buckets-specified"' AS number_of_buckets_specified
        FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
        WHERE schema_name =  p_schema_name 
        AND table_name = p_table_name 
        AND column_name = p_column_name
    )
    SELECT 
        CASE WHEN id = 1 THEN p_schema_name ELSE '' END AS schema_name,
        CASE WHEN id = 1 THEN p_table_name ELSE '' END AS table_name,
        CASE WHEN id = 1 THEN p_column_name ELSE '' END AS column_name, 
        CASE WHEN id = 1 THEN last_updated ELSE '' END AS last_updated,
        CASE WHEN id = 1 THEN number_of_buckets_specified ELSE '' END AS 'number_of_buckets_specified' ,
        id AS buckets_specified_index,
        buckets_content
    FROM
    (
        SELECT * FROM cte,t_buckets
    )t;

END$$

DELIMITER ;

 

1: 执行计划走聚集索引扫描(Cluster Index Scan)

于是,第一个截图中的结果就转换为了如下的格式
这里刻意按照4个buckets生成的直方图,应该来说足够简单了,熟悉MSSQL直方图同学,应该一眼就可以看明白这个直方图的含义(测试数据量是400,000)
以第一个bucket为例:["2018-06-15 04:57:48.000000", "2018-07-02 15:13:04.000000", 0.25, 95311]

因为今天遇到了类似的问题,调试存储过程的时候,对于存储过程中处理的字符串,因为print会被截断,那么就select出来,

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

DECLARE  @P0 nvarchar(4000),@P1 nvarchar(4000);

 

SET @P0='1';

SET @P1='K172';

SELECT [recid],[machine_no] 

   ,[stop_stime] 

   ,[stop_etime] 

   ,[status] 

   ,[memo] 

   ,[createddate]  

FROM machine_stop_alarm_msg t  

WHERE 1=1  

AND t.status=@P0  

AND t.machine_no in(@P1 )  

ORDER BY machine_no, 

   stop_stime ;  

 

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

很明显,
1,"2018-06-15 04:57:48.000000"和"2018-07-02 15:13:04.000000"是类似于sqlserver中直方图中的下限值与上限值
2,0.25小于bucket的值的比例(也就小于这个区间上限制值的比例)
3,95311是这个区间的字段值不重复的行数。
到最后一个bucket,采样率必然是1,也就是100%

然后复制select出来的结果到一个新窗口中,发现字符串结尾跟处理字符串的逻辑对不上,

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:关于SSMS显示select出来的数据行的疑问,SERVER中隐

上一篇:没有了 下一篇:没有了
猜你喜欢
热门排行
精彩图文