澳门美高梅游戏官方网站 > 澳门美高梅游戏 > MySQL顾客端连接格局,20第114中学预估战略的校订

原标题:MySQL顾客端连接格局,20第114中学预估战略的校订

浏览次数:110 时间:2019-11-09

方今察觉三个分页查询存款和储蓄进度中的的二个SQL语句,当集中索引列的排序方式各异的时候,作用差异到达数十倍,让本身深感特别震动
透过引发出来分页查询的情状下对大表做Clustered Scan的时候,
不等景观下会接纳FOENCOREWA奥迪Q5D 或然BACKWALX570D差距,以至建构聚焦索引时,采取索引列的排序格局的有些盘算
废话相当的少,上代码
先创建一张测验表,在Col1上树立集中索引,写入100W条数据

SQL获取数据库名,表名,列名,说明等信息 转自汉城节度使 http://www.cnblogs.com/tuqun/p/3724000.html
/**************************************************************************************************************
1、获取所有用户名:
islogin='1'   :表示帐户
islogin='0'   :表示角色
status='2'   :表示用户帐户
status='0'   :表示系统帐户
**************************************************************************************************************/
SELECT name
FROM   sysusers
WHERE  status = '0'
       AND islogin = '1'
/**************************************************************************************************************
2、获取所有数据库名:
**************************************************************************************************************/
SELECT name
FROM     master..sysdatabases
ORDER BY name
/**************************************************************************************************************
3、获取所有表名:
XType='U'    :表示所有用户表;
XType='S'    :表示所有系统表;
**************************************************************************************************************/
SELECT name
FROM     数据库名..sysobjects
WHERE    xtype = 'U'
ORDER BY name
/**************************************************************************************************************
4、获取所有字段名:
**************************************************************************************************************/
SELECT name
FROM   syscolumns
WHERE  id = Object_id('表名')
/**************************************************************************************************************
5、获取数据库所有类型:
**************************************************************************************************************/
SELECT name
FROM   systypes
/**************************************************************************************************************
6、获取主键字段:
**************************************************************************************************************/
SELECT name
FROM   syscolumns
WHERE  id = Object_id('表名')
       AND colid = (SELECT TOP 1 keyno
                    FROM   sysindexkeys
                    WHERE  id = Object_id('表名'))
/**************************************************************************************************************
7、获取表字段的基本信息:
a.
**************************************************************************************************************/
SELECT 字段名 = Rtrim(b.name)
       ,主键 = CASE 
               WHEN h.id IS NOT NULL THEN 'PK'
               ELSE ''
             END
       ,字段类型 = Type_name(b.xusertype)
                + CASE 
                    WHEN b.colstat & 1 = 1 THEN '[ID('
                                                 + CONVERT(VARCHAR,Ident_seed(a.name))
                                                 + ','
                                                 + CONVERT(VARCHAR,Ident_incr(a.name))
                                                 + ')]'
                    ELSE ''
                  END
       ,长度 = b.length
       ,允许空 = CASE b.isnullable 
                WHEN 0 THEN 'N'
                ELSE 'Y'
              END
       ,默认值 = Isnull(e.TEXT,'')
       ,字段说明 = Isnull(c.VALUE,'')
FROM     sysobjects a
         INNER JOIN  sys.all_objects aa
           ON a.id=aa.object_id 
              AND  schema_name(schema_id)='dbo'
         ,syscolumns b
         LEFT OUTER JOIN sys.extended_properties c
           ON b.id = c.major_id
              AND b.colid = c.minor_id
         LEFT OUTER JOIN syscomments e
           ON b.cdefault = e.id
         LEFT OUTER JOIN (SELECT g.id
                                 ,g.colid
                          FROM   sysindexes f
                                 ,sysindexkeys g
                          WHERE  (f.id = g.id)
                                 AND (f.indid = g.indid)
                                 AND (f.indid > 0)
                                 AND (f.indid < 255)
                                 AND (f.status & 2048) <> 0) h
           ON (b.id = h.id)
              AND (b.colid = h.colid)
WHERE    (a.id = b.id)
         AND (a.id = Object_id('表名'))  --要查询的表改成你要查询表的名称
ORDER BY b.colid
/**************************************************************************************************************
b.
**************************************************************************************************************/
SELECT 表名 = CASE 
              WHEN a.colorder = 1 THEN d.name
              ELSE ''
            END
       ,表说明 = CASE 
                WHEN a.colorder = 1 THEN Isnull(f.VALUE,'')
                ELSE ''
              END
       ,字段序号 = a.colorder
       ,字段名 = a.name
       ,标识 = CASE 
               WHEN Columnproperty(a.id,a.name,'IsIdentity') = 1 THEN '√'
               ELSE ''
             END
       ,主键 = CASE 
               WHEN EXISTS (SELECT 1
                            FROM   sysobjects
                            WHERE  xtype = 'PK'
                                   AND name IN (SELECT name
                                                FROM   sysindexes
                                                WHERE  indid IN (SELECT indid
                                                                 FROM   sysindexkeys
                                                                 WHERE  id = a.id
                                                                        AND colid = a.colid))) THEN '√'
               ELSE ''
             END
       ,类型 = b.name
       ,字段长度 = a.length
       ,占用字节数 = Columnproperty(a.id,a.name,'PRECISION')
       ,小数位数 = Isnull(Columnproperty(a.id,a.name,'Scale'),0)
       ,允许空 = CASE 
                WHEN a.isnullable = 1 THEN '√'
                ELSE ''
              END
       ,默认值 = Isnull(e.TEXT,'')
       ,字段说明 = Isnull(g.[value],'')
FROM     syscolumns a
         LEFT JOIN systypes b
           ON a.xusertype = b.xusertype
         INNER JOIN sysobjects d
           ON (a.id = d.id)
              AND (d.xtype = 'U')
              AND (d.name <> 'dtproperties') 
          INNER JOIN  sys.all_objects c
            ON d.id=c.object_id 
                AND  schema_name(schema_id)='dbo'
         LEFT JOIN syscomments e
           ON a.cdefault = e.id
         LEFT JOIN sys.extended_properties g
           ON (a.id = g.major_id)
              AND (a.colid = g.minor_id)
         LEFT JOIN sys.extended_properties f
           ON (d.id = f.major_id)
              AND (f.minor_id = 0)
--where d.name='要查询的表'         --如果只查询指定表,加上此条件
ORDER BY a.id
         ,a.colorder
/***************************************************************************************************************
c. 前两种不完整~
****************************************************************************************************************/
-- 資料庫中所有資料表:透過內定的Stored Procedure sp_tables
EXEC sp_tables @table_name = '%',@table_owner = 'dbo',@table_qualifier = @DBName; 
--    取得資料表Schema
EXEC sp_columns @TableName 
--    取得欄位說明、備註(Extended Property):這個是透過Sql內定的Function來處理
SELECT * FROM  ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', @TableName, 'column', default) 
--    取得主索引
EXEC sp_pkeys @TableName 



/**************************************************************************************************************
表及字段描述信息处理示例
**************************************************************************************************************/

--创建表
CREATE TABLE 表 (
  a1  VARCHAR(10)
  ,a2 CHAR(2))
--为表添加描述信息
EXECUTE Sp_addextendedproperty N'MS_Description' , '人员信息表' , N'user' , N'dbo' , N'table' , N'表' , NULL , NULL
EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', @value=N'人员信息表' , 
@level0type=N'SCHEMA',@level0name=N'dbo', 
@level1type=N'TABLE',@level1name=N'表'



--为字段a1添加描述信息
EXECUTE Sp_addextendedproperty N'MS_Description' , '姓名' , N'user' , N'dbo' , N'table' , N'表' , N'column' , N'a1'
EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', @value=N'人员信息表' , 
@level0type=N'SCHEMA',@level0name=N'dbo', 
@level1type=N'TABLE',@level1name=N'表', 
@level2type=N'COLUMN',@level2name=N'a1'




--为字段a2添加描述信息
EXECUTE Sp_addextendedproperty N'MS_Description' , '性别' , N'user' , N'dbo' , N'table' , N'表' , N'column' , N'a2'
--更新表中列a1的描述属性:
EXEC Sp_updateextendedproperty 'MS_Description' , '字段1' , 'user' , dbo , 'table' , '表' , 'column' , a1
--显示表的描述属性
SELECT *
FROM   ::fn_listextendedproperty(NULL,'user','dbo','table','表','column',NULL)
--删除表中列a1的描述属性:
EXEC Sp_dropextendedproperty 'MS_Description' , 'user' , dbo , 'table' , '表' , 'column' , a1
--删除测试
DROP TABLE 表

 

 

正文目录列表:

create table ClusteredIndexScanDirection
(
    Col1 int identity(1,1),
    Col2 varchar(50),
    Col3 varchar(50),
    Col4 Datetime
)


create unique clustered index idx_Col1 on ClusteredIndexScanDirection(Col1 ASC) 

DECLARE @date datetime,@i int=0
    set @date=GETDATE()
    while @i<1000000
    begin
    insert into ClusteredIndexScanDirection values (NEWID(),NEWID(),DATEADD(MI,@i,GETDATE()-200))
    set @i=@i+1
end

 

前提

MySQL连接格局
MySQL除了最广泛的TCP连接方式外,还提供SOCKET(LINUX暗许连接情势卡塔 尔(英语:State of Qatar)、PIPE和SHARED MEMO福特ExplorerY连接情势。
各连接方式的服务器、客户端运转选项,及连接暗中同意值见下表:

1、sql server查看可编制程序对象定义的艺术对待

 

      本文仅研究SQL Server查询时,
    对于非复合总结音信,也即每一个字段的总计音信只含有当前列的数据布满的情事下,
    在用三个字段实行组合查询的时候,如何依照总结音信去预估行数的。
    利用分化字段的总计新闻做多少行数预估的算法原理,以致SQL Server 2011和SQL Server 二零一六该算法的差异情形,
    这里一时半刻不关乎复合总括音信,暂不涉及总计消息的翻新攻略及优化相关话题,以至别的SQL Server版本总计办法。   

TCP连接(Linux,Windows):
对象 运维选项 暗许值
SERVER --port=PORT 3306
CLIENT --protocol=TCP --port=PORT 3306

2、整合达成全数可编制程序对象定义的查阅功能的存款和储蓄dbo.usp_helptext2

 

 

SOCKET连接(Linux):
对象 运维选项 暗中同意值
SERVER --enable-named-pipe --socket=SOCKET /tmp/mysql.sock
CLIENT --protocol=SOCKET --socket=SOCKET /tmp/mysql.sock

3、dbo.helptext2的选用性测验

先直观地看一下聚焦索引围观时候的FO奥迪Q3WA福特ExplorerD 和 BACKWARubiconD

总计消息是怎样

PIPE连接(Windows):
对象 运行选项 默许值
SERVER --enable-named-pipe --socket=SOCKET MYSQL
CLIENT --protocol=PIPE --socket=SOCKET MYSQL

4、总结语

 

    轻松说正是对某个字段的数据遍布的意气风发种描述,让SQL Server在借助标准做询问的时候,大致知道预期的数码大小,
    从而指点生成合理实践陈设的风流浪漫种数据库对象

SHARED MEMORY连接(Windows):
对象 运维选项 暗许值
SERVER --shared-memory --shared-memory-base-name=MEMORY MySQL
CLIENT --protocol=MEMORY --shared-memory-base-name=MEMORY MySQL

5、仿照效法清单列表

 BACKWARD

 

 

  施行如下分页查询,当遵照Col4切合2017-7-18和2017-7-23,而且Col1 倒序排序的时候
  从试行安插看,Clustered Index Scan的Scan Direction的措施是BACKWA奇骏D

总计消息的归类

1、sql server查看可编制程序对象定义的法子比较

  图片 1

     索引上会自行创制计算音讯,SQL Server也会根据实际的查询,在少数非索引自动创制索引,理所当然也能够通过手动方式开创计算新闻。
     先来直观地打听一下总计音讯长什么,参谋截图,正是这么个样子,
     _WA_Sys_****开头的是系统基于须要创立的总括音讯,
    与索引同名的是索引上成立的总计新闻,
    手动创设总计音讯也足以在知足SQL Server命名必要的动静下活动命名。

 

图片 2

  

上大器晚成篇博文重构sql server的sys.helptext存储中写了sys.helptext的范围和输出格式每行自带char(13)和char(10)那多少个字符。为了将可编制程序对象定义查询形式研商透顶,以下表格列出了查询可编制程序对象定义的例外格局的却别和对可编制程序对象定义查看的扶植程度。

 

  上边二个是索引的总结音信。

对象类型描述 对象类型简写
sys.sp_helptext
sys.sql_modules
sys.system_sql_modules
sys.all_sql_modules
object_definition
CHECK_CONSTRAINT
C
支持 不支持
不支持
不支持
支持
DEFAULT_CONSTRAINT(contraint,stand-alone)
D
支持 支持
不支持
支持
支持
SQL_SCALAR_FUNCTION
FN
支持
支持
支持
支持
支持
SQL_INLINE_TABLE_VALUED_FUNCTION
IF
支持
支持
支持
支持
支持
SQL_STORED_PROCEDURE
P
支持
支持 支持
支持
支持
RULE(old-style,stand-alone)
R
支持
支持
不支持
支持
支持
REPLICATION FILTER PROCEDURE
RF
支持
支持
支持
支持
支持
SQL_TABLE_VALUED_FUNCTION
TF
支持
支持
支持
支持
支持
SQL_TRIGGER
TR
支持(除数据库DDL触发器和服务器触发器外)
支持(除服务器触发器外)
不支持
支持(除服务器触发器外)
支持(除服务器触发器外)
USER_TABLE
U
computed_column
支持
不支持
不支持
不支持
不支持
VIEW
V
支持
支持
支持
支持
支持

 FORWARD

  图片 3  

注意:

  实践如下分页查询,当依据Col4合乎2017-7-18和2017-7-23,并且Col1 正序排序的时候
  从实行安顿看,Clustered Index Scan的Scan Direction的诀要是FO揽胜WA奥迪Q5D

 

MySQL顾客端连接格局,20第114中学预估战略的校订。1、带有_modules的是系统提供的目录视图类。

图片 4

计算新闻的遵循

2、sys.sql_modules包含持有客商定义的可编制程序对象的,当然也不协助总结列和服务器触发器的。

图片 5

    查询引擎依照总括消息提供的多寡做出合理的试行布署。
    那么,查询引擎毕竟是怎么使用总结音讯做预估的呢,
    甚至上边就要提到的SQL Server 20第114中学较早前的版本有啥样变化?
    本文将对此两点做叁个简练的剖析来证实SQL Server是怎么依照总括新闻做猜测的,下边开放正文。

3、sys.system_sql_modules不协助系统定义的门类为C、D、福睿斯、T福睿斯的靶子。

  查询条件相似,分页情形下,排序情势不均等,品质上有么迥然分裂?肯定有,太明朗了,若无,本文也就从不怎么意义了
  如图是上述二种查询艺术在本人本机的测量检验结果,肖似是前100条数据,因为排序方式各异,其代价也是见仁见智的
  逻辑读,叁个是2327,叁个是99七十七回,差距相当大吧,在实质上境况中,那个出入是特别丰裕大的,大到能够超越你用脑筋想

 

4、sys.all_sql_modules就是sys.sql_modules和sys.system_sql_modules这么些视图的并集合果的,当然也不援救系统定义的门类为C、D、Odyssey、T奥迪Q5类型的指标。

 

    测量检验景况搭建

5、sys.sp_helptext即使协助上述表格中除数据库DDL触发器和服务器触发器之外的可编制程序对象,可是其出口格式有节制:1、每行最多225双字节字符号,那样有一点都不小希望形成输出将一个标志符分割为上下两行的。

 对FOLX570WA智跑D和BACKWA宝马X5D有三个直观的感触之后,来讲说那四头的区分

  习于旧贯性地做四个演示的遭遇,成立一个表,写入100W的数量背后测量检验用。

6、object_definition函数帮助上述表格中除服务器除触发器和测算列外的能够改为对象。

  假使驾驭B树索引结构来讲,应该精通集中索引是以贴近于B树结构的主意来公司的,既然是B树结构,
  那么上边这些图就简单精通了,
  在索引列根据某件事方式排序的场馆下,比方

create table TestStatistics 
(
    Id int identity(1,1),
    Status1 int,
    Status2 int,
    Status3 int
)

insert into TestStatistics values (RAND()*1000,RAND()*250,RAND()*50)
go 1000000

7、sys.sp_helpttext和object_definition有个职能的范围:1、在SSMS客户端中风流罗曼蒂克经选取字符串类型变量选取重返的而结果,有相当大希望受制于SSMS顾客端针对字符串变量的最大范围(sql server 2011中的最大规模是43679双字节字符长度卡塔 尔(阿拉伯语:قطر‎无法生龙活虎体出口到顾客端,那几个毛病能够从通进度序编码达成获得完美呈现。

  create unique clustered index idx_Col1 on ClusteredIndexScanDirection(Col1 ASC) 
  或者是
  create unique clustered index idx_Col1 on ClusteredIndexScanDirection(Col1 DESC) 

表中有八个字段,第一个是自增列,首要看Status1,Status2,Status3那八个字段,
四个字段的取值都以用随便数乘以两个常量周全的出来的,
故而那多少个字段的数据布满范围分别是
Status1:0-999(1000种数据布满卡塔 尔(英语:State of Qatar)
Status2:0-249(250种数据分布卡塔 尔(阿拉伯语:قطر‎
Status3:0-49(50种数据布满卡塔 尔(英语:State of Qatar)
其黄金时代前边有用。

 

  上边那张图分别是FOTucsonWAXC60D和BACKWA汉兰达D三种Scan direction的完毕方式

 

2、整合实现全部可编制程序对象定义的查阅功能的仓库储存dbo.usp_helptext2

   图片 6

 

 

                FORWARD 

 

经过上述二种办法的相比,大家能够看出这一个格局都无法将上述表格中列出的指标类型的定义全部都满意,为了缓慢解决那个不足,大家将构成这么些功效来封装在二个仓储(其名为dbo.usp_helptext2)。须求注意的少数正是服务器触发器本来不是有些数据库中的对象的,服务器触发器和数据库中可编制程序对象分别更加好的明亮,也能大约些的,可是为了查询的便利性,大家此番封装的积累完毕服务器触发器定义查看。

 

首先在SQL Server 二零一三中做测量检验

 

图片 7

      先做如此一个询问:select * from TestStatistics where Status1=885 and Status2=88 and Status3=8
    那些查询落成今后,表上活动创设叁个多少个总括音信,
    这七个总结信息分别是Status1,Status2,Status3那么些三个字段的数据分布描述

功效整合的蕴藏进度T-SQL代码如下:

                    BACKWARD

  图片 8

IF OBJECT_ID(N'[dbo].[usp_helptext2]', 'P') IS NOT NULL
BEGIN
    DROP PROCEDURE [dbo].[usp_helptext2];
END
GO

--==================================
-- 功能: 查看可编程对象定义
-- 说明: 支持用户定义类型,可以运行于SQL Server 2005+
-- 创建: yyyy-MM-dd hh:mm-hh:mm XXX 创建内容描述
-- 修改: yyyy-MM-dd hh:mm-hh:mm XXX 修改内容描述
--==================================
CREATE PROCEDURE [dbo].[usp_helptext2]
(
     @nvcObjectName AS NVARCHAR(776)                    -- 对象名称,可以支持的对象类型为(C、D、FN、IF、P、R、RF、TF、TR、U、V)
    ,@nvcComputedColumnName AS NVARCHAR(128) = NULL        -- 计算列名称(如果@nvcObjectName的对象类型为U,则该参数表示计算列名称)
)
AS
BEGIN
    SET NOCOUNT ON;

    SET @nvcObjectName = ISNULL(@nvcObjectName, N'');
    IF (@nvcObjectName = N'')
    BEGIN
        RAISERROR(16902, -1, -1,N'usp_helptext2', N'@nvcObjectName');
        RETURN(1);
    END

    SET @nvcComputedColumnName = ISNULL(@nvcComputedColumnName, N'');

    DECLARE @tntRetVal AS TINYINT;
    SET @tntRetVal = 0;

    DECLARE @tblObjDef AS TABLE (
        [Text] NVARCHAR(1000) NULL
    );

    DECLARE
         @intObjectID AS INT
        ,@chaType AS CHAR(2)
        ,@nvcText AS NVARCHAR(MAX);
    SELECT
         @intObjectID = 0
        ,@chaType = ''
        ,@nvcText = N'';
    SELECT
         @intObjectID = [object_id]
        ,@chaType = [type]        
    FROM [sys].[all_objects]
    WHERE 
        [type] IN ('C', 'D', 'FN', 'IF', 'P', 'R', 'RF', 'TF', 'TR', 'U', 'V')
        AND [name] = PARSENAME(@nvcObjectName, 1);

    IF (@nvcComputedColumnName > N'')    -- 获取计算列定义
    BEGIN
        IF (@chaType NOT IN ('S', 'U', 'TF'))
        BEGIN
            RAISERROR(15218, -1, -1, @nvcObjectName);
            RETURN(1);
        END

        INSERT INTO @tblObjDef ([Text])        
        EXEC [sys].[sp_helptext]
             @objname = @nvcObjectName                -- nvarchar(776)
            ,@columnname = @nvcComputedColumnName    -- sysname

        IF(@@ROWCOUNT = 0)
        BEGIN
            SET @tntRetVal = 1;
        END

        SELECT 
            @nvcText = ISNULL([Text], N'')
        FROM @tblObjDef;            
    END
    ELSE IF (@intObjectID <> 0)    -- 获取除计算列和服务器触发器以外的所有对象类型的定义
    BEGIN
        SET @nvcText = OBJECT_DEFINITION(@intObjectID);

        IF(@@ROWCOUNT = 0)
        BEGIN
            SET @tntRetVal = 1;
        END
    END
    ELSE IF (@intObjectID = 0)    -- 尝试获取服务器触发器定义
    BEGIN
        SELECT 
            @nvcText = T1.[definition]
        FROM [sys].[server_sql_modules] AS T1
            INNER JOIN [sys].[server_triggers] AS T2
                ON [T1].[object_id] = [T2].[object_id]
        WHERE T2.[name] = @nvcObjectName;

        IF(@@ROWCOUNT = 0)
        BEGIN
            SET @tntRetVal = 1;
        END
    END

    SELECT 
        @nvcText AS [Text];

    RETURN(@tntRetVal);
END
GO

 

  

 

本文由澳门美高梅游戏官方网站发布于澳门美高梅游戏,转载请注明出处:MySQL顾客端连接格局,20第114中学预估战略的校订

关键词:

上一篇:去掉某字段括号中的值,SQL数据库底蕴

下一篇:没有了