Server依期自动抓取耗费时间SQL并归档数据发邮件

在ASP.NET里的GridView的控件里也提供了活动分页的意义,但它的管理进程是把数据源贰次全部加载到网址上囤积起来,并透过每页突显几条,

SQL Server准时自动抓取耗费时间SQL并归档数据发邮件脚本分享

第一步建库和建表

USE [master]
GO

CREATE DATABASE [MonitorElapsedHighSQL]
GO

--建表

USE [MonitorElapsedHighSQL]
GO


 --1、表[SQLCountStatisticsByDay]
  --抓取到的sql语句数量
CREATE TABLE [dbo].[SQLCountStatisticsByDay]
    (
      id INT IDENTITY(1, 1)  PRIMARY KEY ,
      [SQLCount] INT ,
      [gettime] DATETIME
    )

CREATE INDEX [Idx_SQLCountStatisticsByDay_SQLCount] ON [MonitorElapsedHighSQL].[dbo].[SQLCountStatisticsByDay]([SQLCount])
CREATE INDEX [Idx_SQLCountStatisticsByDay_gettime] ON [MonitorElapsedHighSQL].[dbo].[SQLCountStatisticsByDay]([gettime])
GO



 --2、表[MostElapsedStatisticsByDay]
 --每条不同的sql耗时最多
CREATE TABLE [dbo].[MostElapsedStatisticsByDay]
    (
      id INT IDENTITY(1, 1)
             PRIMARY KEY ,
      [ElapsedMS] INT ,
      [IOReads] BIGINT ,
      [IOWrites] BIGINT ,
      [DBName] NVARCHAR(128) ,
      [paramlist] NVARCHAR(MAX) ,
      [planstmttext] NVARCHAR(MAX) ,
      [stmttext] NVARCHAR(MAX) ,
      [xmlplan] XML ,
      [gettime] DATETIME
    )

CREATE INDEX [Idx_MostElapsedStatisticsByDay_ElapsedMS] ON [MonitorElapsedHighSQL].[dbo].[MostElapsedStatisticsByDay]([ElapsedMS])
CREATE INDEX [Idx_MostElapsedStatisticsByDay_gettime] ON [MonitorElapsedHighSQL].[dbo].[MostElapsedStatisticsByDay]([gettime])
GO


 --3、表[MostIOReadStatisticsByDay]
--每条不同的sql的IOread最多
CREATE TABLE [dbo].[MostIOReadStatisticsByDay]
    (
      id INT IDENTITY(1, 1)
             PRIMARY KEY ,
      [IOReads] BIGINT ,
      [DBName] NVARCHAR(128) ,
      [paramlist] NVARCHAR(MAX) ,
      [planstmttext] NVARCHAR(MAX) ,
      [stmttext] NVARCHAR(MAX) ,
      [xmlplan] XML ,
      [gettime] DATETIME
    )

CREATE INDEX [Idx_MostIOReadStatisticsByDay_IOReads] ON [MonitorElapsedHighSQL].[dbo].[MostIOReadStatisticsByDay]([IOReads])
CREATE INDEX [Idx_MostIOReadStatisticsByDay_gettime] ON [MonitorElapsedHighSQL].[dbo].[MostIOReadStatisticsByDay]([gettime])
GO


 --4、表[MostIOWriteStatisticsByDay]
--每条不同的sql的IOwrite最多
CREATE TABLE [dbo].[MostIOWriteStatisticsByDay]
    (
      id INT IDENTITY(1, 1)
             PRIMARY KEY ,
      [IOWrites] BIGINT ,
      [DBName] NVARCHAR(128) ,
      [paramlist] NVARCHAR(MAX) ,
      [planstmttext] NVARCHAR(MAX) ,
      [stmttext] NVARCHAR(MAX) ,
      [xmlplan] XML ,
      [gettime] DATETIME
    )

CREATE INDEX [Idx_MostIOWriteStatisticsByDay_IOWrites] ON [MonitorElapsedHighSQL].[dbo].[MostIOWriteStatisticsByDay]([IOWrites])
CREATE INDEX [Idx_MostIOWriteStatisticsByDay_gettime] ON [MonitorElapsedHighSQL].[dbo].[MostIOWriteStatisticsByDay]([gettime])
GO


 --5、表[sp_executesqlCountStatisticsByDay]
--使用sp_executesql的sql有多少条
CREATE TABLE [dbo].[sp_executesqlCountStatisticsByDay]
    (
      id INT IDENTITY(1, 1)
             PRIMARY KEY ,
      [sp_executesqlCount] INT ,
      [DBName] NVARCHAR(128) ,
      [planstmttext] NVARCHAR(MAX) ,
      [gettime] DATETIME
    )

CREATE INDEX [Idx_sp_executesqlCountStatisticsByDay_sp_executesqlCount] ON [MonitorElapsedHighSQL].[dbo].[sp_executesqlCountStatisticsByDay]([sp_executesqlCount])
CREATE INDEX [Idx_sp_executesqlCountStatisticsByDay_gettime] ON [MonitorElapsedHighSQL].[dbo].[sp_executesqlCountStatisticsByDay]([gettime])
GO

 

 

第二步创设sp_who3存款和储蓄进度

图片 1图片 2

-- http://sqlserverplanet.com/dba/a-better-sp_who2-using-dmvs-sp_who3

USE [MonitorElapsedHighSQL]
GO

CREATE PROCEDURE [dbo].[sp_who3] 

AS
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
    SPID                = er.session_id
    ,BlkBy              = CASE WHEN lead_blocker = 1 THEN -1 ELSE er.blocking_session_id END
    ,ElapsedMS          = er.total_elapsed_time
    ,CPU                = er.cpu_time
    ,IOReads            = er.logical_reads   er.reads
    ,IOWrites           = er.writes     
    ,Executions         = ec.execution_count  
    ,CommandType        = er.command         
    ,LastWaitType       = er.last_wait_type    
    ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid)   '.'   OBJECT_NAME(qt.objectid, qt.dbid)  
    ,SQLStatement       =
        SUBSTRING
        (
            qt.text,
            er.statement_start_offset/2,
            (CASE WHEN er.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                ELSE er.statement_end_offset
                END - er.statement_start_offset)/2
        )        
    ,STATUS             = ses.STATUS
    ,[Login]            = ses.login_name
    ,Host               = ses.host_name
    ,DBName             = DB_Name(er.database_id)
    ,StartTime          = er.start_time
    ,Protocol           = con.net_transport
    ,transaction_isolation =
        CASE ses.transaction_isolation_level
            WHEN 0 THEN 'Unspecified'
            WHEN 1 THEN 'Read Uncommitted'
            WHEN 2 THEN 'Read Committed'
            WHEN 3 THEN 'Repeatable'
            WHEN 4 THEN 'Serializable'
            WHEN 5 THEN 'Snapshot'
        END
    ,ConnectionWrites   = con.num_writes
    ,ConnectionReads    = con.num_reads
    ,ClientAddress      = con.client_net_address
    ,Authentication     = con.auth_scheme
    ,DatetimeSnapshot   = GETDATE()
    ,plan_handle        = er.plan_handle
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
OUTER APPLY 
(
    SELECT execution_count = MAX(cp.usecounts)
    FROM sys.dm_exec_cached_plans cp
    WHERE cp.plan_handle = er.plan_handle
) ec
OUTER APPLY
(
    SELECT
        lead_blocker = 1
    FROM master.dbo.sysprocesses sp
    WHERE sp.spid IN (SELECT blocked FROM master.dbo.sysprocesses)
    AND sp.blocked = 0
    AND sp.spid = er.session_id
) lb
WHERE er.sql_handle IS NOT NULL
AND er.session_id != @@SPID
ORDER BY
    CASE WHEN lead_blocker = 1 THEN -1 * 1000 ELSE -er.blocking_session_id END,
    er.blocking_session_id DESC,
    er.logical_reads   er.reads DESC,
    er.session_id

END

View Code

 

其三步成立[usp_checkElapsedHighSQL]仓库储存进程

图片 3图片 4

USE [MonitorElapsedHighSQL]
GO
/****** Object:  StoredProcedure [dbo].[usp_checkElapsedHighSQL]    Script Date: 2015/6/23 17:16:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--创建存储过程
CREATE  PROCEDURE [dbo].[usp_checkElapsedHighSQL] ( @SessionID INT )
AS
    BEGIN

        IF  ( SELECT  OBJECT_ID('MonitorElapsedHighSQL.dbo.ElapsedHigh') ) IS NULL
            BEGIN
                 CREATE TABLE [MonitorElapsedHighSQL].[dbo].[ElapsedHigh]
                    (
                      id INT IDENTITY(1, 1)   PRIMARY KEY ,
                      [SPID] SMALLINT ,
                      [ElapsedMS] INT ,
                      [IOReads] BIGINT ,
                      [IOWrites] BIGINT ,
                      [DBName] NVARCHAR(128) ,
                      [plan_handle] VARBINARY(64) ,
                      [paramlist] NVARCHAR(MAX) ,
                      [planstmttext] NVARCHAR(MAX) ,
                      [stmttext] NVARCHAR(MAX) ,
                      [xmlplan] XML,
                      [gettime] DATETIME
                    )

                CREATE INDEX [Idx_ElapsedHigh_ElapsedMS] ON [MonitorElapsedHighSQL].[dbo].[ElapsedHigh]([ElapsedMS])
                CREATE INDEX [Idx_ElapsedHigh_IOReads] ON [MonitorElapsedHighSQL].[dbo].[ElapsedHigh]([IOReads])

            END

        IF  ( SELECT  OBJECT_ID('MonitorElapsedHighSQL.dbo.ElapsedHigh') ) IS NOT NULL
            BEGIN

                SET NOCOUNT ON 

                SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

                DECLARE @Duration INT -- in milliseconds, 10000 = 10 sec
                DECLARE @now DATETIME
                DECLARE @plan_handle VARBINARY(64)
                DECLARE @ElapsedMS INT
                DECLARE @SPID INT
                DECLARE @IOReads BIGINT
                DECLARE @IOWrites BIGINT
                DECLARE @DBName NVARCHAR(128)
                DECLARE @planstmttext NVARCHAR(MAX)
                DECLARE @stmttext NVARCHAR(MAX)
                DECLARE @paramlist NVARCHAR(MAX)
                DECLARE @plan_xml XML
                DECLARE @paramtb TABLE
                    (
                      paramlist NVARCHAR(MAX) ,
                      planstmttext NVARCHAR(MAX)
                    )
                DECLARE @paramtb2 TABLE
                    (
                      paramlist NVARCHAR(MAX) ,
                      planstmttext NVARCHAR(MAX)
                    )

                SELECT  @Duration = 10000  --★Do -- in milliseconds, 10000 = 10 sec



                IF OBJECT_ID('tempdb..#ElapsedHigh') IS NOT NULL
                    BEGIN
                        DROP TABLE [#ElapsedHigh]  --删除临时表  
                    END 


--建临时表
                CREATE TABLE [#ElapsedHigh]
                    (
                      [SPID] SMALLINT ,
                      [BlkBy] INT ,
                      [ElapsedMS] INT ,
                      [CPU] INT ,
                      [IOReads] BIGINT ,
                      [IOWrites] BIGINT ,
                      [Executions] INT ,
                      [CommandType] NVARCHAR(40) ,
                      [LastWaitType] NVARCHAR(60) ,
                      [ObjectName] NVARCHAR(1000) ,
                      [SQLStatement] NVARCHAR(MAX) ,
                      [STATUS] NVARCHAR(30) ,
                      [Login] NVARCHAR(128) ,
                      [Host] NVARCHAR(128) ,
                      [DBName] NVARCHAR(128) ,
                      [StartTime] DATETIME ,
                      [Protocol] NVARCHAR(40) ,
                      [transaction_isolation] NVARCHAR(100) ,
                      [ConnectionWrites] INT ,
                      [ConnectionReads] INT ,
                      [ClientAddress] VARCHAR(48) ,
                      [AUTHENTICATION] NVARCHAR(40) ,
                      [DatetimeSnapshot] DATETIME ,
                      [plan_handle] VARBINARY(64)
                    )




--处理逻辑
                INSERT  INTO [#ElapsedHigh]
                        ( [SPID] ,
                          [BlkBy] ,
                          [ElapsedMS] ,
                          [CPU] ,
                          [IOReads] ,
                          [IOWrites] ,
                          [Executions] ,
                          [CommandType] ,
                          [LastWaitType] ,
                          [ObjectName] ,
                          [SQLStatement] ,
                          [STATUS] ,
                          [Login] ,
                          [Host] ,
                          [DBName] ,
                          [StartTime] ,
                          [Protocol] ,
                          [transaction_isolation] ,
                          [ConnectionWrites] ,
                          [ConnectionReads] ,
                          [ClientAddress] ,
                          [AUTHENTICATION] ,
                          [DatetimeSnapshot] ,
                          [plan_handle]
                        )
                        EXEC [MonitorElapsedHighSQL].[dbo].[sp_who3]

        --如果传入的是会话ID 只显示所在会话ID的信息
                IF ( @SessionID IS NOT NULL AND @SessionID <> 0 )
                    BEGIN 

                        SELECT TOP 1
                                @ElapsedMS = [ElapsedMS] ,
                                @SPID = [SPID] ,
                                @plan_handle = [plan_handle] ,
                                @IOReads = [IOReads] ,
                                @IOWrites = [IOWrites] ,
                                @DBName = [DBName]
                        FROM    [#ElapsedHigh]
                        WHERE   [#ElapsedHigh].[SPID] = @SessionID


                        SELECT  @stmttext = [text]  FROM    sys.fn_get_sql(@plan_handle)



                        BEGIN TRY
        -- convert may fail due to exceeding 128 depth limit
                            SELECT  @plan_xml = CONVERT(XML, query_plan)
                            FROM    sys.dm_exec_text_query_plan(@plan_handle, 0, -1)
                        END TRY
                        BEGIN CATCH 
                            SELECT  @plan_xml = NULL
                        END CATCH;

                        WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
INSERT @paramtb ( [paramlist], [planstmttext] )
    SELECT 
        parameter_list.param_node.value('(./@Column)[1]', 'nvarchar(128)')  '='  parameter_list.param_node.value('(./@ParameterCompiledValue)[1]', 'nvarchar(max)')  AS paramlist,
        ISNULL(@plan_xml.value('(//@StatementText)[1]', 'nvarchar(max)'), N'Unknown Statement') AS stmttext
    FROM (SELECT @plan_xml AS xml_showplan) AS t
        OUTER APPLY t.xml_showplan.nodes('//sp:ParameterList/sp:ColumnReference') AS parameter_list (param_node)

                        SELECT TOP 1
                                @SPID spid ,
                                @ElapsedMS ElapsedMS ,
                                @IOReads IOReads ,
                                @IOWrites IOReads ,
                                @DBName DBName ,
                                @plan_handle plan_handle ,
                                @plan_xml planxml,
                                @stmttext stmttext ,
                                [planstmttext] planstmttext ,
                                ( SELECT    [paramlist]   '  '
                                  FROM      @paramtb
                                  WHERE     [planstmttext] = A.[planstmttext]
                                FOR
                                  XML PATH('')
                                ) AS [paramlist]
                        FROM    @paramtb A
                        GROUP BY [planstmttext]

                    END
                ELSE
        --如果没有对存储过程传入参数,那么显示耗时最多的那条SQL的信息
                    BEGIN 

                        SELECT TOP 1
                                @ElapsedMS = [ElapsedMS] ,
                                @SPID = [SPID] ,
                                @plan_handle = [plan_handle] ,
                                @IOReads = [IOReads] ,
                                @IOWrites = [IOWrites] ,
                                @DBName = [DBName]
                        FROM    [#ElapsedHigh] WHERE [#ElapsedHigh].[DBName] NOT IN('master','distribution','model','msdb','tempdb')
                        ORDER BY [ElapsedMS] DESC 

                        SELECT  @stmttext = [text]  FROM    sys.fn_get_sql(@plan_handle)



--抓取占用时间长的SQL
                        IF ( @ElapsedMS > @Duration )
                            BEGIN 
                                SELECT  @now = GETDATE()


                                BEGIN TRY
        -- convert may fail due to exceeding 128 depth limit
                                    SELECT  @plan_xml = CONVERT(XML, query_plan)
                                    FROM    sys.dm_exec_text_query_plan(@plan_handle,
                                                              0, -1)
                                END TRY
                                BEGIN CATCH
                                    SELECT  @plan_xml = NULL
                                END CATCH;

                                WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
INSERT @paramtb ( [paramlist], [planstmttext] )
    SELECT 
        parameter_list.param_node.value('(./@Column)[1]', 'nvarchar(128)')  '='  parameter_list.param_node.value('(./@ParameterCompiledValue)[1]', 'nvarchar(max)')  AS paramlist,
        ISNULL(@plan_xml.value('(//@StatementText)[1]', 'nvarchar(max)'), N'Unknown Statement') AS stmttext
    FROM (SELECT @plan_xml AS xml_showplan) AS t
        OUTER APPLY t.xml_showplan.nodes('//sp:ParameterList/sp:ColumnReference') AS parameter_list (param_node)


                                INSERT  @paramtb2( [planstmttext] , [paramlist])
                                        SELECT TOP 1
                                                [planstmttext] ,
                                                ( SELECT    [paramlist]   '  '
                                                  FROM      @paramtb
                                                  WHERE     [planstmttext] = A.[planstmttext]
                                                FOR
                                                  XML PATH('')
                                                ) AS [paramlist]
                                        FROM    @paramtb A
                                        GROUP BY [planstmttext]


                                SELECT TOP 1
                                        @planstmttext = [planstmttext] ,
                                        @paramlist = [paramlist]
                                FROM    @paramtb2

                                INSERT  INTO [MonitorElapsedHighSQL].[dbo].[ElapsedHigh]
                                        ( [SPID] ,
                                          [ElapsedMS] ,
                                          [IOReads] ,
                                          [IOWrites] ,
                                          [DBName] ,
                                          [plan_handle] ,
                                          [paramlist] ,
                                          [stmttext] ,
                                          [planstmttext] ,
                                          [xmlplan],
                                          [gettime]
                                        )
                                VALUES  ( @SPID , -- SPID - smallint
                                          @ElapsedMS , -- ElapsedMS - int
                                          @IOReads , -- IOReads - bigint
                                          @IOWrites , -- IOWrites - bigint
                                          @DBName , -- DBName - nvarchar(128)
                                          @plan_handle , -- plan_handle - varbinary(64)
                                          @paramlist , -- paramlist - nvarchar(max)
                                          @stmttext , -- stmttext - nvarchar(max)
                                          @planstmttext , -- planstmttext - nvarchar(max)
                                          @plan_xml ,  --plan_xml - xml
                                          @now  -- gettime - datetime
                                        )

                            END 
                    END

            END

    END

View Code

 

第四步成立[usp_Resettbname]积累进程 

图片 5图片 6

USE [MonitorElapsedHighSQL]
GO
--重设ElapsedHigh表名,进行归档
CREATE  PROCEDURE [dbo].[usp_Resettbname]
AS
    BEGIN

         IF EXISTS ( SELECT  OBJECT_ID('MonitorElapsedHighSQL.dbo.ElapsedHigh') )
            BEGIN
               --kill掉数据库所有连接
                DECLARE @DBNAME NVARCHAR(100)
                DECLARE @SQL NVARCHAR(MAX)
                DECLARE @SPID NVARCHAR(100)
                DECLARE @OwnSPID NVARCHAR(100)
                DECLARE @TBNAME NVARCHAR(1000)

                SELECT  @OwnSPID = @@SPID
                SET @DBNAME = 'MonitorElapsedHighSQL'  


                DECLARE CurDBName CURSOR
                FOR
                    SELECT  [spid]
                    FROM    sys.sysprocesses
                    WHERE   [spid] >= 50
                            AND DBID = DB_ID(@DBNAME)

                OPEN CurDBName
                FETCH NEXT FROM CurDBName INTO @SPID

                WHILE @@FETCH_STATUS = 0
                    BEGIN  
        --kill process 不kill掉本存储过程的spid
                        IF ( @SPID <> @OwnSPID )
                            BEGIN
                                SET @SQL = N'kill '   @SPID
                                EXEC (@SQL)
                            END 

                        FETCH NEXT FROM CurDBName INTO @SPID
                    END
                CLOSE CurDBName
                DEALLOCATE CurDBName

                SET @TBNAME='ElapsedHigh' CONVERT(NVARCHAR(200), GETDATE(), 112) 

                EXEC sys.[sp_rename] @objname = N'ElapsedHigh', -- nvarchar(1035)
                    @newname =@TBNAME    -- sysname



            END


    END

View Code

 

第五步制造[usp_StatisticsTask]储存进程

图片 7图片 8

USE [MonitorElapsedHighSQL]
GO
/****** Object:  StoredProcedure [dbo].[usp_StatisticsTask]    Script Date: 2015/6/24 18:05:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--创建存储过程
CREATE   PROCEDURE [dbo].[usp_StatisticsTask]
AS
    BEGIN

        IF ( ( SELECT   OBJECT_ID('MonitorElapsedHighSQL.dbo.SQLCountStatisticsByDay')
             ) IS NULL
             AND ( SELECT   OBJECT_ID('MonitorElapsedHighSQL.dbo.MostElapsedStatisticsByDay')
                 ) IS NULL
             AND ( SELECT   OBJECT_ID('MonitorElapsedHighSQL.dbo.MostIOReadStatisticsByDay')
                 ) IS NULL
             AND ( SELECT   OBJECT_ID('MonitorElapsedHighSQL.dbo.MostIOWriteStatisticsByDay')
                 ) IS NULL
             AND ( SELECT   OBJECT_ID('MonitorElapsedHighSQL.dbo.sp_executesqlCountStatisticsByDay')
                 ) IS NULL
           )
            BEGIN
                RETURN 1

            END
        ELSE
            BEGIN
                --最耗时SQL
                INSERT  INTO [dbo].[MostElapsedStatisticsByDay]
                        ( [ElapsedMS] ,
                          [IOReads] ,
                          [IOWrites] ,
                          [DBName] ,
                          [paramlist] ,
                          [planstmttext] ,
                          [stmttext] ,
                          [xmlplan] ,
                          [gettime]
                        )
                        SELECT  [ElapsedMS] ,
                                [IOReads] ,
                                [IOWrites] ,
                                [DBName] ,
                                [paramlist] ,
                                [planstmttext] ,
                                [stmttext] ,
                                [xmlplan] ,
                                GETDATE()
                        FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY spid ORDER BY [ElapsedMS] DESC ) rowid ,
                                            *
                                  FROM      [ElapsedHigh]
                                  WHERE     [DBName] NOT IN ( 'MASTER',
                                                              'MODEL', 'MSDB',
                                                              'ReportServer',
                                                              'ReportServerTempDB',
                                                              'distribution' )
                                ) t
                        WHERE   rowid = 1



--读IO最多SQL
                INSERT  INTO [dbo].[MostIOReadStatisticsByDay]
                        ( [IOReads] ,
                          [DBName] ,
                          [paramlist] ,
                          [planstmttext] ,
                          [stmttext] ,
                          [xmlplan] ,
                          [gettime]
                        )
                        SELECT  [IOReads] ,
                                [DBName] ,
                                [paramlist] ,
                                [planstmttext] ,
                                [stmttext] ,
                                [xmlplan] ,
                                GETDATE()
                        FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY spid ORDER BY [IOReads] DESC ) rowid ,
                                            *
                                  FROM      [ElapsedHigh]
                                  WHERE     [DBName] NOT IN ( 'MASTER',
                                                              'MODEL', 'MSDB',
                                                              'ReportServer',
                                                              'ReportServerTempDB',
                                                              'distribution' )
                                ) t
                        WHERE   rowid = 1



--写IO最多SQL
                INSERT  INTO [dbo].[MostIOWriteStatisticsByDay]
                        ( [IOWrites] ,
                          [DBName] ,
                          [paramlist] ,
                          [planstmttext] ,
                          [stmttext] ,
                          [xmlplan] ,
                          [gettime]
                        )
                        SELECT  [IOWrites] ,
                                [DBName] ,
                                [paramlist] ,
                                [planstmttext] ,
                                [stmttext] ,
                                [xmlplan] ,
                                GETDATE()
                        FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY spid ORDER BY [IOWrites] DESC ) rowid ,
                                            *
                                  FROM      [ElapsedHigh]
                                  WHERE     [DBName] NOT IN ( 'MASTER',
                                                              'MODEL', 'MSDB',
                                                              'ReportServer',
                                                              'ReportServerTempDB',
                                                              'distribution' )
                                ) t
                        WHERE   rowid = 1



--统计sp_executesql次数
                DECLARE @tbsp_executesqlCountStatisticsByDay TABLE
                    (
                      [DBName] [nvarchar](128) ,
                      [planstmttext] [nvarchar](MAX)
                    )
                DECLARE @sp_executesqlCount INT

                INSERT  INTO @tbsp_executesqlCountStatisticsByDay
                        ( [DBName] ,
                          [planstmttext] 
                        )
                        SELECT  [DBName] ,
                                [planstmttext]
                        FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY spid ORDER BY [IOWrites] DESC ) rowid ,
                                            *
                                  FROM      [ElapsedHigh]
                                  WHERE     [planstmttext] LIKE '(@%'
                                            AND [DBName] NOT IN ( 'MASTER',
                                                              'MODEL', 'MSDB',
                                                              'ReportServer',
                                                              'ReportServerTempDB',
                                                              'distribution' )
                                ) t
                        WHERE   rowid = 1

                SELECT  @sp_executesqlCount = COUNT(*)
                FROM    @tbsp_executesqlCountStatisticsByDay

                INSERT  INTO [dbo].[sp_executesqlCountStatisticsByDay]
                        ( [sp_executesqlCount] ,
                          [DBName] ,
                          [planstmttext] ,
                          [gettime]
                        )
                        SELECT  @sp_executesqlCount ,
                                [DBName] ,
                                [planstmttext] ,
                                GETDATE()
                        FROM    @tbsp_executesqlCountStatisticsByDay



--统计一共有多少SQL被抓取
                INSERT  INTO [dbo].[SQLCountStatisticsByDay]
                        ( [SQLCount] ,
                          [gettime]
                        )
                        SELECT  COUNT(DISTINCT ( [planstmttext] )) ,
                                GETDATE()
                        FROM    [dbo].[ElapsedHigh]
                        WHERE   [DBName] NOT IN ( 'MASTER', 'MODEL', 'MSDB',
                                                  'ReportServer',
                                                  'ReportServerTempDB',
                                                  'distribution' )


            END



    END

View Code

 

第六步成立[usp_SendStatisticsMail]储存进度

图片 9图片 10

USE [MonitorElapsedHighSQL]
GO

--对统计数据定时发邮件
CREATE  PROCEDURE [dbo].[usp_SendStatisticsMail]
AS
    BEGIN

        --定义变量
        DECLARE @SQL NVARCHAR(MAX)
        DECLARE @SQLConcat NVARCHAR(MAX)
        DECLARE @infoConcat NVARCHAR(MAX)
        DECLARE @finalSQL NVARCHAR(MAX)


        DECLARE @DBID NVARCHAR(MAX)
        DECLARE @servername NVARCHAR(200)
        DECLARE @date DATETIME

        DECLARE @sqlversion NVARCHAR(200)
        DECLARE @uptime NVARCHAR(200)


        --1.数据库版本信息
        SELECT  @sqlversion = @@version


        --2.数据库服务器已运行时间信息
        SELECT  @uptime = CONVERT(NVARCHAR(200), DATEDIFF(DAY, sqlserver_start_time, GETDATE()))
        FROM    sys.dm_os_sys_info WITH ( NOLOCK )
        OPTION  ( RECOMPILE )



        --3.查看数据库服务器名
        SELECT  @servername = LTRIM(@@servername)


        SET @date = GETDATE()
        SET @SQL = ' '
        SET @SQLConcat = ' '
        SET @infoConcat = ' '



        IF ( @servername IS NOT NULL AND @servername <> '' )
            BEGIN
                SET @infoConcat = '<h3><font color="#FF0000">主机名:'   @ServerName   '</font></h3></br>'
            END

        IF ( @uptime IS NOT NULL  AND @uptime <> '' )
            BEGIN
                SET @infoConcat = @infoConcat   '<h4>数据库服务器已运行天数:'   @uptime    '天</h4></br>' 
            END

        IF ( @sqlversion IS NOT NULL AND @sqlversion <> '' )
            BEGIN
                SET @infoConcat = @infoConcat   '<h4>数据库版本信息:'   @sqlversion   '</h4></br>'
            END


      -----------------------------------------------------------


        SET @SQL = N'<H3>['   @servername   ']_前5条不同的最耗时SQL 表名:[MostElapsedStatisticsByDay] ------   邮件发出时间:'   CONVERT(NVARCHAR(200), @date, 120)   '</H3>'
              '<table border="1">'   N'<tr>
<th>[id]</th>
<th>[耗时]</th>
<th>[IO读次数]</th>
<th>[IO写次数]</th>
<th>[数据库名称]</th>
<th>[执行计划SQL]</th>
<th>[日期]</th>
</tr>'   CAST(( SELECT TOP 5
                        [id] AS 'td' ,
                        '' ,
                        [ElapsedMS] AS 'td' ,
                        '' ,
                        [IOReads] AS 'td' ,
                        '' ,
                        [IOWrites] AS 'td' ,
                        '' ,
                        [DBName] AS 'td' ,
                        '' ,
                        LEFT([planstmttext], 100) AS 'td' ,
                        '' ,
                        CONVERT(DATE, [gettime]) AS 'td' ,
                        ''
                FROM    [dbo].[MostElapsedStatisticsByDay]
                WHERE   DATEPART(DAY, [gettime]) = DATEPART(DAY, GETDATE()) AND DATEPART(MONTH , [gettime]) = DATEPART(MONTH, GETDATE()) AND DATEPART(YEAR, [gettime]) = DATEPART(YEAR, GETDATE())
                ORDER BY [ElapsedMS] DESC
              FOR
                XML PATH('tr') ,
                    ELEMENTS-- TYPE 
              ) AS NVARCHAR(MAX))   N'</table>';
        PRINT @SQL

        IF ( @SQL IS NOT NULL
             AND @SQL <> ''
           )
            BEGIN
                SET @SQLConcat = @SQL   @SQLConcat

            END




      --------------------------------------------------------



        SET @SQL = N'<H3>['   @servername   ']_前5条I/O read最多的SQL 表名:[MostIOReadStatisticsByDay]------   邮件发出时间:'   CONVERT(NVARCHAR(200), @date, 120)   '</H3>'
              '<table border="1">'   N'<tr>
<th>[id]</th>
<th>[IO读次数]</th>
<th>[数据库名称]</th>
<th>[执行计划SQL]</th>
<th>[日期]</th>
</tr>'   CAST(( SELECT TOP 5
                        [id] AS 'td' ,
                        '' ,
                        [IOReads] AS 'td' ,
                        '' ,
                        [DBName] AS 'td' ,
                        '' ,
                        LEFT([planstmttext], 100) AS 'td' ,
                        '' ,
                        CONVERT(DATE, [gettime]) AS 'td' ,
                        ''
                FROM    [dbo].[MostIOReadStatisticsByDay]
                WHERE   DATEPART(DAY, [gettime]) = DATEPART(DAY, GETDATE()) AND DATEPART(MONTH , [gettime]) = DATEPART(MONTH, GETDATE()) AND DATEPART(YEAR, [gettime]) = DATEPART(YEAR, GETDATE())
                ORDER BY [IOReads] DESC
              FOR
                XML PATH('tr') ,
                    ELEMENTS-- TYPE 
              ) AS NVARCHAR(MAX))   N'</table>';



        IF ( @SQL IS NOT NULL
             AND @SQL <> ''
           )
            BEGIN
                SET @SQLConcat = @SQL   @SQLConcat

            END

--      -----------------------------------------------------



        SET @SQL = N'<H3>['   @servername   ']_前5条I/O write最多的SQL 表名:[MostIOWriteStatisticsByDay]------   邮件发出时间:'  CONVERT(NVARCHAR(200), @date, 120)   '</H3>'
              '<table border="1">'   N'<tr>
<th>[id]</th>
<th>[IO写次数]</th>
<th>[数据库名称]</th>
<th>[执行计划SQL]</th>
<th>[日期]</th>
</tr>'   CAST(( SELECT TOP 5
                        [id] AS 'td' ,
                        '' ,
                        [IOWrites] AS 'td' ,
                        '' ,
                        [DBName] AS 'td' ,
                        '' ,
                        LEFT([planstmttext], 100) AS 'td' ,
                        '' ,
                        CONVERT(DATE, [gettime]) AS 'td' ,
                        ''
                FROM    [dbo].[MostIOWriteStatisticsByDay]
                WHERE   DATEPART(DAY, [gettime]) = DATEPART(DAY, GETDATE()) AND DATEPART(MONTH , [gettime]) = DATEPART(MONTH, GETDATE()) AND DATEPART(YEAR, [gettime]) = DATEPART(YEAR, GETDATE())
                ORDER BY [IOWrites] DESC
              FOR
                XML PATH('tr') ,
                    ELEMENTS-- TYPE 
              ) AS NVARCHAR(MAX))   N'</table>';



        IF ( @SQL IS NOT NULL
             AND @SQL <> ''
           )
            BEGIN
                SET @SQLConcat = @SQL   @SQLConcat

            END

--      -------------------------------------------------------




        SET @SQL = N'<H3>['   @servername   ']_前5条使用sp_executesql执行的SQL 表名:[sp_executesqlCountStatisticsByDay]------   邮件发出时间:'  CONVERT(NVARCHAR(200), @date, 120)   '</H3>'
              '<table border="1">'   N'<tr>
<th>[id]</th>
<th>[sp_executesql调用次数]</th>
<th>[数据库名称]</th>
<th>[执行计划SQL]</th>
<th>[日期]</th>
</tr>'   CAST(( SELECT TOP 5
                        [id] AS 'td' ,
                        '' ,
                        [sp_executesqlCount] AS 'td' ,
                        '' ,
                        [DBName] AS 'td' ,
                        '' ,
                        LEFT([planstmttext], 100) AS 'td' ,
                        '' ,
                        CONVERT(DATE, [gettime]) AS 'td' ,
                        ''
                FROM    [dbo].[sp_executesqlCountStatisticsByDay]
                WHERE   DATEPART(DAY, [gettime]) = DATEPART(DAY, GETDATE()) AND DATEPART(MONTH , [gettime]) = DATEPART(MONTH, GETDATE()) AND DATEPART(YEAR, [gettime]) = DATEPART(YEAR, GETDATE())
                ORDER BY [sp_executesqlCount] DESC
              FOR
                XML PATH('tr') ,
                    ELEMENTS-- TYPE 
              ) AS NVARCHAR(MAX))   N'</table>';


        IF ( @SQL IS NOT NULL
             AND @SQL <> ''
           )
            BEGIN
                SET @SQLConcat = @SQL   @SQLConcat

            END

--      --------------------------------------------------------

        SET @SQL = N'<H3>['   @servername  ']_SQL语句数量 表名:[SQLCountStatisticsByDay]------   邮件发出时间:'   CONVERT(NVARCHAR(200), @date, 120)   '</H3>'
              '<table border="1">'   N'<tr>
<th>[id]</th>
<th>[SQL数量]</th>
<th>[日期]</th>
</tr>'   CAST(( SELECT  [id] AS 'td' ,
                        '' ,
                        [SQLCount] AS 'td' ,
                        '' ,
                        CONVERT(DATE, [gettime]) AS 'td' ,
                        ''
                FROM    [dbo].[SQLCountStatisticsByDay]
                WHERE   DATEPART(DAY, [gettime]) = DATEPART(DAY, GETDATE()) AND DATEPART(MONTH , [gettime]) = DATEPART(MONTH, GETDATE()) AND DATEPART(YEAR, [gettime]) = DATEPART(YEAR, GETDATE())
              FOR
                XML PATH('tr') ,
                    ELEMENTS-- TYPE 
              ) AS NVARCHAR(MAX))   N'</table>';


        IF ( @SQL IS NOT NULL
             AND @SQL <> ''
           )
            BEGIN
                SET @SQLConcat = @SQL   @SQLConcat

            END

      -----------------------------------------------

        IF ( @infoConcat IS NOT NULL AND @infoConcat <> '' AND @SQLConcat IS NOT NULL  AND @SQLConcat <> '')
            BEGIN
                SET @finalSQL = @infoConcat   '</br></br>'   @SQLConcat
                EXEC [msdb].[dbo].[sp_send_dbmail] @profile_name = 'SQLServer',
                    @recipients = 'dba@xx.com', -- varchar(max) --收件人
                    @subject = N'SQL Server 实例SQL语句抓取统计信息', -- nvarchar(255) 标题
                    @body_format = 'HTML', -- varchar(20) 正文格式可选值:text html
                    @body = @finalSQL
            END





    END

View Code

 

 

第七步创制AutocaptureElapsedHighSQL作业

图片 11图片 12

USE [msdb]
GO
/****** 对象:  Job [自动抓取耗时SQL]    脚本日期: 07/29/2014 15:44:57 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** 对象:  JobCategory [[Uncategorized (Local)]]]    脚本日期: 07/29/2014 15:44:57 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'AutocaptureElapsedHighSQL', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'自动抓取耗时SQL', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** 对象:  Step [execute usp_checkElapsedHighSQL script]    脚本日期: 07/29/2014 15:44:58 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep  @job_name=N'AutocaptureElapsedHighSQL', @step_name=N'execute usp_checkElapsedHighSQL script', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'exec [dbo].[usp_checkElapsedHighSQL] null',  --调用存储过程
        @database_name=N'MonitorElapsedHighSQL', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_name=N'AutocaptureElapsedHighSQL', @name=N'ScheduleAutocaptureCheck', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=4, 
        @freq_subday_interval=1, --每一分钟抓取一次耗时SQL
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20110224, 
        @active_end_date=99991231, 
        @active_start_time=200, 
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_name=N'AutocaptureElapsedHighSQL', @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

View Code

 

第八步创立ResetcheckElapsedHighSQLtbname作业

图片 13图片 14

USE [msdb]
GO
/****** 对象:  Job [定时改表名]    脚本日期: 07/29/2014 15:44:57 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** 对象:  JobCategory [[Uncategorized (Local)]]]    脚本日期: 07/29/2014 15:44:57 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'ResetcheckElapsedHighSQLtbname', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'修改抓取耗时SQL的表名', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** 对象:  Step [execute usp_checkElapsedHighSQL script]    脚本日期: 07/29/2014 15:44:58 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep  @job_name=N'ResetcheckElapsedHighSQLtbname', @step_name=N'execute usp_Resettbname script', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'exec [dbo].[usp_Resettbname] ',  --调用存储过程
        @database_name=N'MonitorElapsedHighSQL', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_name=N'ResetcheckElapsedHighSQLtbname', @name=N'Scheduleusp_Resettbname', 
    @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=1, 
        @freq_subday_interval=1, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20110224, 
        @active_end_date=99991231, 
        @active_start_time=235900, 
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_name=N'ResetcheckElapsedHighSQLtbname', @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

View Code

 

第九步创造StatisticsforElapsedHigh作业

图片 15图片 16

USE [msdb]
GO
/****** 对象:  Job [定时统计[ElapsedHigh]表数据]    脚本日期: 07/29/2014 15:44:57 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** 对象:  JobCategory [[Uncategorized (Local)]]]    脚本日期: 07/29/2014 15:44:57 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'StatisticsforElapsedHigh', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'统计[MonitorElapsedHighSQL]库里的[ElapsedHigh]表各项数据', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** 对象:  Step [execute usp_checkElapsedHighSQL script]    脚本日期: 07/29/2014 15:44:58 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep  @job_name=N'StatisticsforElapsedHigh', @step_name=N'execute usp_StatisticsTask script', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'exec [dbo].[usp_StatisticsTask] ',  --调用存储过程
        @database_name=N'MonitorElapsedHighSQL', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_name=N'StatisticsforElapsedHigh', @name=N'Scheduleusp_StatisticsTask', 
    @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=1, 
        @freq_subday_interval=1, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20110224, 
        @active_end_date=99991231, 
        @active_start_time=235000, 
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_name=N'StatisticsforElapsedHigh', @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

View Code

 

第十步创建ScheduleSendStatisticsMail作业

图片 17图片 18

USE [msdb]
GO
/****** 对象:  Job [定时发统计邮件]    脚本日期: 07/29/2014 15:44:57 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** 对象:  JobCategory [[Uncategorized (Local)]]]    脚本日期: 07/29/2014 15:44:57 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'ScheduleSendStatisticsMail', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'定时发统计邮件', 
        @category_name=N'[Uncategorized (Local)]',  
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** 对象:  Step [execute usp_checkElapsedHighSQL script]    脚本日期: 07/29/2014 15:44:58 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep  @job_name=N'ScheduleSendStatisticsMail', @step_name=N'execute usp_SendStatisticsMail script', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'exec  [dbo].[usp_SendStatisticsMail]',  --调用存储过程
        @database_name=N'MonitorElapsedHighSQL', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_name=N'ScheduleSendStatisticsMail', @name=N'Scheduleusp_SendStatisticsMail', 
    @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=1, 
        @freq_subday_interval=1, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20110224, 
        @active_end_date=99991231, 
        @active_start_time=235500, 
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_name=N'ScheduleSendStatisticsMail', @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

View Code

 

 

 

规律解释:

AutocaptureElapsedHighSQL作业每间距一分钟调用[usp_checkElapsedHighSQL]仓库储存进度,而[usp_checkElapsedHighSQL]积攒进程又会调用

sp_who3存款和储蓄进程获得一些当前线上蒙受的新闻,被记录到[ElapsedHigh]表里

ResetcheckElapsedHighSQLtbname作业会在每一日的23点59分试行,调用[usp_Resettbname]储存进度, [usp_Resettbname]仓库储存进程会将[ElapsedHigh]表

的表名纠正为:表名 当天日子,比方:ElapsedHigh2014-6-19 ,那样就开展了归档

图片 19

图片 20

 

[usp_checkElapsedHighSQL] 存款和储蓄进程有二种调用格局,意气风发种是传播NULL,那么[usp_checkElapsedHighSQL] 存款和储蓄进程就能够抓取最耗费时间的十三分session

假诺传入spid,那么就能够展现这些spid的session

--调用示例
--不提供参数,抓取最耗时的一个SQL
EXEC [MonitorElapsedHighSQL].[dbo].[usp_checkElapsedHighSQL] NULL


--提供sessionsid参数,抓取那个sessionid相关的SQL
EXEC [MonitorElapsedHighSQL].[dbo].[usp_checkElapsedHighSQL] 182

 

StatisticsforElapsedHigh作业每日会在23点50分对[ElapsedHigh]表里的多少开展总计,把数据放进去五张总括表里

ScheduleSendStatisticsMail作业会抽出五张总结表里的数码并发送邮件,让DBA知道当天数据库有怎样慢SQL

 

效果

USE [sss]
GO

WHILE 1=1
BEGIN
DECLARE @test NVARCHAR(100)
SET @test='你好'
DECLARE @id int
SET @id=2
SELECT * FROM [sss].[dbo].[test] WHERE [id]=@id
EXEC [dbo].[aa] @test =@test
EXEC [dbo].[ab] @id=@id

END

SELECT * FROM [dbo].[ElapsedHigh]
go

图片 21

图片 22

能够见见,参数也能抓取到,日常依据sys.dm_exec_sql_text视图和sys.[fn_get_sql]()视图是敬谢不敏获得到参数的

SQL Server profiler也是,它是漫天RPC和Statement去抓

並且还有恐怕会抓取这时候的XML实行布置,点击它就会展现图形化的实施安顿,那样对解析那个时候讲话的实践情况至极常有帮带

图片 23

 

 

邮件功效

图片 24


总结

眼前剧本依然比较简单,后续还索要对风度翩翩意气风发数据库服务器的总括数据实行聚焦,用web页面彰显,那样正是数据库服务器再多也足以洞察

天天经过发邮件,把总括表的故事情节发邮件给开辟职员,指点他们调治SQL,缓慢解决DBA的局部职业量

 

若有脱漏或失误,请留言回复,多谢!

 

如有不对的地点,接待大家拍砖o(∩_∩)o 

前段时间,发掘众多合营社对mysql的品质监察和控制是透过show global status达成的,由此对于这一个命令想要切磋黄金年代番,看他是或不是是实时更新的。

二把任何的回避起来,就算也达成了分页效果,但在真的含义上是假分页的意义。

以前,大家亟须搞领悟mysql对于那一个命令的进行进度来确认它所展现的数目到底是怎么、是还是不是实时更新以致是不是标准。借那一个空子,大家也能够通晓顾客端与mysql服务器是何许相互的以至对于SQL命令的肖似推行进度。为此,笔者以为那是一个很好的转搭乘飞机,当然中间也参照了好三人对此从前SQL版本的视角。

现今我给我们演示的是在数额库端实现分页,那样的话,每点下大器晚成页时都以从数据库加载出来的数量

是因为SQL命令的日常推行进度比较复杂,作者单独描述二个光景,纵然不是很标准,可是出于由源码和gdb调节和测量试验的结果做参谋,为此应当是着力科学。

1.在数据Curry成立二个sql分页的蕴藏过成,那么些能够通用,也从今现在做形似的,直接托用就行了

留意:大家的mysql版本是mysql5.7.13,运维条件是Linux,仅对于show global status的经过进展解析。

能够建一张测验表

 生机勃勃、show global status的切实流程

图片 25

1.mysql客商端的连锁动作

成立存款和储蓄进度

图片 26

 图片 27

图片 28

图片 29图片 30

read_and_execute和main在mysql.cc中

CREATE PROCEDURE sp_page --存储过程名

@strTable varchar(50), --表名 
@strColumn varchar(50), --按该列来进行分页 
@intColType int, --@strColumn列的类型,0-数字类型,1-字符类型,2-日期时间类型 
@intOrder bit, --排序,0-顺序,1-倒序 
@strColumnlist varchar(800), --要查询出的字段列表,*表示全部字段 
@intPageSize int, --每页记录数 
@intPageNum int, --指定页 
@strWhere varchar(800), --查询条件 
@intPageCount int OUTPUT --总页数 
AS 
DECLARE @sql nvarchar(4000) --用于构造SQL语句 
DECLARE @where1 varchar(800) --构造条件语句 
DECLARE @where2 varchar(800) --构造条件语句 
IF @strWhere is null or rtrim(@strWhere)='' 
-- 为了避免SQL关键字与字段、表名等连在一起,首先为传入的变量添加空格 
BEGIN --没有查询条件 
SET @where1=' WHERE ' 
SET @where2=' ' 
END 
ELSE 
BEGIN --有查询条件 
SET @where1=' WHERE (' @strWhere ') AND ' 
SET @where2=' WHERE (' @strWhere ') ' 
END 

set @strColumn = ' '   @strColumn   ' ' 
set @strColumnlist = ' '   @strColumnlist   ' ' 
--构造SQL语句,计算总页数。计算公式为 总页数 = Ceiling ( 记录个数 / 页大小 ) 

SET @sql='SELECT @intPageCount=CEILING((COUNT(*) 0.0)/' 
  CAST(@intPageSize AS varchar) 
  ') FROM '   @strTable   @where2 

--执行SQL语句,计算总页数,并将其放入@intPageCount变量中 

EXEC sp_executesql @sql,N'@intPageCount int OUTPUT',@intPageCount OUTPUT 

--将总页数放到查询返回记录集的第一个字段前,此语句可省略 

SET @strColumnlist= Cast(@intPageCount as varchar(30))   ' as PageCount,'   @strColumnlist 

IF @intOrder=0 --构造升序的SQL 

SET @sql='SELECT TOP '  CAST(@intPageSize AS varchar)   ' '   
@strColumnlist   
' FROM '   @strTable   @where1   
@strColumn   '>(SELECT MAX(' @strColumn ') '  
' FROM (SELECT TOP '  CAST(@intPageSize*(@intPageNum - 1) AS varchar)   
@strColumn   ' FROM '  @strTable @where2 'ORDER BY ' @strColumn ') t) ORDER BY '  @strColumn 

ELSE --构造降序的SQL 

SET @sql='SELECT TOP '  CAST(@intPageSize AS varchar)   ' '   
@strColumnlist  
' FROM '  @strTable   @where1   
@strColumn   '<(SELECT MIN(' @strColumn ') '  
' FROM (SELECT TOP '  CAST(@intPageSize*(@intPageNum - 1) AS varchar)   
@strColumn   ' FROM '  @strTable @where2 'ORDER BY ' @strColumn ' DESC) t) ORDER BY '  
@strColumn   ' DESC' 

IF @intPageNum=1--第一页 

SET @sql='SELECT TOP '  CAST(@intPageSize AS varchar(10))   ' '   @strColumnlist   ' FROM ' @strTable  
@where2 ' ORDER BY ' @strColumn   CASE @intOrder WHEN 0 THEN '' ELSE ' DESC' 

END 
--PRINT @sql 
EXEC(@sql) 

GO

read_and_execute正如其名成功mysql对命令的读入和实施,格局分为批量读入和不成批量的读入,对于show global status是后世

View Code

(1)readline函数完毕对单行命令的读入。

 2.在表中新建查询,调用存款和储蓄进程,修改参数完毕分页

(2)find_command函数对命令语法举行检查。

sql语句为

(3)com_go函数对命令实行发送、选用结果以至彰显

图片 31图片 32

  1)mysql_real_query_for_lazy函数对命令推行发送。

declare @intPageCount int --声明变量
exec sp_page --执行sp_page存储表
--以下的对应存储过程填写参数
'db_userInfo',--要执行分页的表名
'ID',--按该列来进行分页 
1,--@strColumn列的类型
0, --排序,0-顺序,1-倒序 
'*',--要查询出的字段列表,*表示全部字段 
2, --每页记录数
1,--指定页 
'', --用于构造SQL语句 
@intPageCount output
Print @intPageCount --输出分页

  2)mysql_next_result函数和mysql_store_result_for_lazy函数完结对结果的抽出。

View Code

  3)print_table_data*以及print_tab_data函数对结果按供给的格式举行打字与印刷。

图片 33

2.mysql服务端的连锁动作。

退换一下点名页看一下意义

mysql服务端在创立连接后会为每贰个老是创设贰个独立线程去管理该连接所发送的乞请。(管理流程如图0所示)

图片 34

图片 35

以上就是数量库端制作的分页效果

 

而在实际项目个中平常要在系统或网址上应用,所以显得三个好的分页效果也很主要的,

                                 图0.mysql服务端处理流程

你只供给在你创设的项目里调用存款和储蓄进程就ok了。

图片 36

 

              图1、解析sql的逻辑

(1)在handle_connection中(图1的#5)

闭馆连接并压缩连接数

图片 37图片 38

 1     close_connection(thd, 0, false, false);
 2 
 3     thd->get_stmt_da()->reset_diagnostics_area();
 4     thd->release_resources();
 5 
 6     // Clean up errors now, before possibly waiting for a new connection.
 7     ERR_remove_state(0);
 8 
 9     thd_manager->remove_thd(thd);
10     Connection_handler_manager::dec_connection_count();

View Code

这是handle_connection函数的主导

图片 39图片 40

 1     if (thd_prepare_connection(thd))
 2       handler_manager->inc_aborted_connects();
 3     else
 4     {
 5       while (thd_connection_alive(thd))
 6       {
 7         mysql_audit_release(thd);
 8         if (do_command(thd))
 9           break;
10       }
11       end_connection(thd);
12     }
13     close_connection(thd, 0, false, false);

View Code

管理命令的输入在do_command中

我们得以观看它管理的逻辑是就算那几个三番四次还持续着,它就能够接连施行命令。

(2)在do_command函数中(图1的#4)

图片 41图片 42

 1 if (classic)
 2   {
 3     /*
 4       This thread will do a blocking read from the client which
 5       will be interrupted when the next command is received from
 6       the client, the connection is closed or "net_wait_timeout"
 7       number of seconds has passed.
 8     */
 9     net= thd->get_protocol_classic()->get_net();
10     my_net_set_read_timeout(net, thd->variables.net_wait_timeout);
11     net_new_transaction(net);
12   }

View Code

图片 43图片 44

1   thd->m_server_idle= true;
2   rc= thd->get_protocol()->get_command(&com_data, &command);
3   thd->m_server_idle= false;

View Code

经过这么些获得命令的品类command和下令的源委com_data

在进展局地备选进度后

图片 45图片 46

  return_value= dispatch_command(thd, &com_data, command);

View Code

踏向函数dispatch_command

(3)在函数dispatch_command中(图1的#3)

图片 47图片 48

  thd->set_command(command);
  /*
    Commands which always take a long time are logged into
    the slow log only if opt_log_slow_admin_statements is set.
  */
  thd->enable_slow_log= TRUE;
  thd->lex->sql_command= SQLCOM_END; /* to avoid confusing VIEW detectors */
  thd->set_time();

View Code

在此边设置了指令给thd,并设置了开班时间

图片 49图片 50

1   if (!(server_command_flags[command] & CF_SKIP_QUESTIONS))
2     thd->status_var.questions  ;

View Code

此处总结了伏乞数的数目。

出于大家的恳求类型是COM_QUEEnclaveY为此进入了COM_QUERY分支

图片 51图片 52

 1  case COM_QUERY:
 2   {
 3     DBUG_ASSERT(thd->m_digest == NULL);
 4     thd->m_digest= & thd->m_digest_state;
 5     thd->m_digest->reset(thd->m_token_array, max_digest_length);
 6 
 7     if (alloc_query(thd, com_data->com_query.query,
 8                     com_data->com_query.length))
 9       break;                    // fatal error is set
10     MYSQL_QUERY_START(const_cast<char*>(thd->query().str), thd->thread_id(),
11                       (char *) (thd->db().str ? thd->db().str : ""),
12                       (char *) thd->security_context()->priv_user().str,
13                       (char *) thd->security_context()->host_or_ip().str);
14 
15     const char *packet_end= thd->query().str   thd->query().length;
16 
17     if (opt_general_log_raw)
18       query_logger.general_log_write(thd, command, thd->query().str,
19                                      thd->query().length);
20 
21     DBUG_PRINT("query",("%-.4096s", thd->query().str));
22 
23 #if defined(ENABLED_PROFILING)
24     thd->profiling.set_query_source(thd->query().str, thd->query().length);
25 #endif
26 
27     MYSQL_SET_STATEMENT_TEXT(thd->m_statement_psi, thd->query().str,
28                              thd->query().length);
29 
30     Parser_state parser_state;
31     if (parser_state.init(thd, thd->query().str, thd->query().length))
32       break;
33 
34     mysql_parse(thd, &parser_state);

View Code

general_log_write会写一个general query log,可是由于我们记录了binlog,为此该选取为false

还要跻身关键的管理流程mysql_parse

(4)在mysql_parse函数中(图1的#2)

该函数首要开展了多个至关心注重要流程:parse_sql函数和mysql_execute_command函数。

1)parse_sql函数首倘使为了成功分析sql命令,将生龙活虎部分sql命令进行改动,建构sql命令的依赖关系,进而完毕实践顺序的构建。

2)mysql_execute_command函数主要基于指令类型实行命令。

(5)在parse_sql函数中(图1的#1)

在这函数中最器重施行的函数是MYSQLparse(thd),会递归实施该函数数次,具体流程和解析进程有关,MYSQLparse(thd)正是yyparse

(6)在yyparse函数中(图1的#0)

在分析show global status时主要履行了以下代码,该代码是在sql_parse.yy上实行的,那是二个开源的GUN bison语法深入分析器的格式

是因为过于复杂,所以本人取了珍爱的片段,供我们参谋

1)最初化成立音信

图片 53

2)设置语句属性

图片 54

3)将语句进行转账

 图片 55

通过bulid_show_global_status将讲话转变成SELECT * FROM 

                                     (SELECT VARIABLE_NAME as Variable_name, VARIABLE_VALUE as Value

                 FROM performance_schema.global_status) global_status

 图片 56

4)将其设置为singlequery

图片 57图片 58

         图2       实施SQL命令的逻辑关系(开首化数据)

(7)在mysql_execute_command函数(图2#16)中

 由于本语句的项目是SQLCOM_SHOW_STATUS,为此进入

图片 59图片 60

 1 case SQLCOM_SHOW_STATUS:
 2   {
 3     system_status_var old_status_var= thd->status_var;
 4     thd->initial_status_var= &old_status_var;
 5 
 6     if (!(res= select_precheck(thd, lex, all_tables, first_table)))
 7       res= execute_sqlcom_select(thd, all_tables);
 8 
 9     /* Don't log SHOW STATUS commands to slow query log */
10     thd->server_status&= ~(SERVER_QUERY_NO_INDEX_USED |
11                            SERVER_QUERY_NO_GOOD_INDEX_USED);
12     /*
13       restore status variables, as we don't want 'show status' to cause
14       changes
15     */
16     mysql_mutex_lock(&LOCK_status);
17     add_diff_to_status(&global_status_var, &thd->status_var,
18                        &old_status_var);
19     thd->status_var= old_status_var;
20     thd->initial_status_var= NULL;
21     mysql_mutex_unlock(&LOCK_status);
22     break;
23   }

View Code

(8)在execute_sqlcom_select函数(图2#15)中

会遵照进展局地限量以致安装反应计时器

图片 61图片 62

 1 {
 2     SELECT_LEX *param= lex->unit->global_parameters();
 3     if (!param->explicit_limit)
 4       param->select_limit=
 5         new Item_int((ulonglong) thd->variables.select_limit);
 6   }
 7 
 8   //check if timer is applicable to statement, if applicable then set timer.
 9   if (is_timer_applicable_to_statement(thd))
10     statement_timer_armed= set_statement_timer(thd);

View Code

是因为命令未有扩黄石一时候能开荒全数表,进入handle_query

图片 63图片 64

 1 if (!(res= open_tables_for_query(thd, all_tables, 0)))
 2   {
 3     MYSQL_SELECT_START(const_cast<char*>(thd->query().str));
 4     if (lex->is_explain())
 5     {
 6       /*
 7         We always use Query_result_send for EXPLAIN, even if it's an EXPLAIN
 8         for SELECT ... INTO OUTFILE: a user application should be able
 9         to prepend EXPLAIN to any query and receive output for it,
10         even if the query itself redirects the output.
11       */
12       Query_result *const result= new Query_result_send;
13       if (!result)
14         return true; /* purecov: inspected */
15       res= handle_query(thd, lex, result, 0, 0);
16     }
17     else
18     {
19       Query_result *result= lex->result;
20       if (!result && !(result= new Query_result_send()))
21         return true;                            /* purecov: inspected */
22       Query_result *save_result= result;
23       Query_result *analyse_result= NULL;
24       if (lex->proc_analyse)
25       {
26         if ((result= analyse_result=
27                new Query_result_analyse(result, lex->proc_analyse)) == NULL)
28           return true;
29       }
30       res= handle_query(thd, lex, result, 0, 0);
31       delete analyse_result;
32       if (save_result != lex->result)
33         delete save_result;
34     }

View Code

(9)在handle_query函数(图2#14)中

该函数分成连个首要流程optimize和exec

1)optimize先实行sql命令实行措施的优化,再建设构造了sql命令的推行安顿,结独资料发掘它会优化where条件、having条件、group by、sort、limit以至jion等话语的举办安插,不会对本命令发生太大的震慑,做出此判别的说辞是自身的where条件事实上是NULL。(详细情况见(6)的3)),为此不做具体深入分析。

2)exec先按陈设推行命令,再将结果上报给客户。(这步是这一次的尤为重要流程)

图片 65图片 66

 1   if (single_query)
 2   {
 3     unit->set_limit(unit->global_parameters());
 4 
 5     select->context.resolve_in_select_list= true;
 6     select->set_query_result(result);
 7     select->make_active_options(added_options, removed_options);
 8     select->fields_list= select->item_list;
 9 
10     if (select->prepare(thd))
11       goto err;
12 
13     unit->set_prepared();
14   }

View Code

图片 67图片 68

 1   if (single_query)
 2   {
 3     if (select->optimize(thd))
 4       goto err;
 5 
 6     unit->set_optimized();
 7   }
 8   else
 9   {
10     if (unit->optimize(thd))
11       goto err;
12   }
13 
14   if (lex->is_explain())
15   {
16     if (explain_query(thd, unit))
17       goto err;     /* purecov: inspected */
18   }
19   else
20   {
21     if (single_query)
22     {
23       select->join->exec();
24       unit->set_executed();
25       if (thd->is_error())
26         goto err;
27     }
28     else
29     {
30       if (unit->execute(thd))
31         goto err;
32     }
33   }

View Code

(10)在exec函数(图2#13 #7)中

为了求证,方便那边将发送的数据结构做多个交代。

图片 69图片 70

1   query_result->send_result_set_metadata(*fields,
2                                    Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF);

View Code

那是多个Item的链表

随着我们找到Item的概念,发掘其有以下定义:

图片 71图片 72

1   /*
2     str_values's main purpose is to be used to cache the value in
3     save_in_field
4   */
5   String str_value;
6 
7   Item_name_string item_name;  /* Name from select */
8   Item_name_string orig_name;  /* Original item name (if it was renamed)*/

View Code

笔者们开采她的全体数值都以以字符串格局存在的。

该函数会施行do_select来成功对话语的查询专门的职业。

(11)在do_select函数(图2#12,#6)中

出于要求有的时候表,为此他进去

图片 73图片 74

1   else
2   {
3     QEP_TAB *qep_tab= join->qep_tab   join->const_tables;
4     DBUG_ASSERT(join->primary_tables);
5     error= join->first_select(join,qep_tab,0);
6     if (error >= NESTED_LOOP_OK)
7       error= join->first_select(join,qep_tab,1);
8   }

View Code

first_select指向sub_select

当无需临时表时,他利用get_end_select_func即end_send()

图片 75图片 76

1 if (join->plan_is_const() && !join->need_tmp)
2   {
3     Next_select_func end_select= join->get_end_select_func();

View Code

(12)在sub_select函数(图2#11,#5)中

sub_select首先扫描从前的信赖表(prepare_scan),然后黄金年代旦该表不依靠于其余表,则跻身批量管理performance schema的进度

图片 77图片 78

 1  while (rc == NESTED_LOOP_OK && join->return_tab >= qep_tab_idx)
 2   {
 3     int error;
 4     if (in_first_read)
 5     {
 6       in_first_read= false;
 7       error= (*qep_tab->read_first_record)(qep_tab);
 8     }
 9     else
10       error= info->read_record(info);
11 
12     DBUG_EXECUTE_IF("bug13822652_1", join->thd->killed= THD::KILL_QUERY;);
13 
14     if (error > 0 || (join->thd->is_error()))   // Fatal error
15       rc= NESTED_LOOP_ERROR;
16     else if (error < 0)
17       break;
18     else if (join->thd->killed)            // Aborted by user
19     {
20       join->thd->send_kill_message();
21       rc= NESTED_LOOP_KILLED;
22     }
23     else
24     {
25       if (qep_tab->keep_current_rowid)
26         qep_tab->table()->file->position(qep_tab->table()->record[0]);
27       rc= evaluate_join_record(join, qep_tab);
28     }
29   }
30 
31   if (rc == NESTED_LOOP_OK &&
32       qep_tab->last_inner() != NO_PLAN_IDX &&
33       !qep_tab->found)
34     rc= evaluate_null_complemented_join_record(join, qep_tab);
35 
36   if (pfs_batch_update)
37     qep_tab->table()->file->end_psi_batch_mode();

View Code

read_first_record开端化数据(在一遍巡回里只进行1次),read_record会读取下一条数据

sqlsql_executor.cc:2819:read_first_record =  join_init_read_record;

              read_record= rr_sequential;

(13)在prepare_scan函数(图2#10)中

图片 79图片 80

 1 bool QEP_TAB::prepare_scan()
 2 {
 3   // Check whether materialization is required.
 4   if (!materialize_table || materialized)
 5     return false;
 6 
 7   // Materialize table prior to reading it
 8   if ((*materialize_table)(this))
 9     return true;
10 
11   materialized= true;
12 
13   // Bind to the rowid buffer managed by the TABLE object.
14   if (copy_current_rowid)
15     copy_current_rowid->bind_buffer(table()->file->ref);
16 
17   return false;
18 }

View Code

sqlsql_select.cc: 2302:materialize_table =  join_materialize_derived;

(14)在join_materialize_derived函数(图2#9)中

图片 81图片 82

 1 int join_materialize_derived(QEP_TAB *tab)
 2 {
 3   THD *const thd= tab->table()->in_use;
 4   TABLE_LIST *const derived= tab->table_ref;
 5 
 6   DBUG_ASSERT(derived->uses_materialization() && !tab->materialized);
 7 
 8   if (derived->materializable_is_const()) // Has been materialized by optimizer
 9     return NESTED_LOOP_OK;
10 
11   bool res= derived->materialize_derived(thd);
12   res|= derived->cleanup_derived();
13   DEBUG_SYNC(thd, "after_materialize_derived");
14   return res ? NESTED_LOOP_ERROR : NESTED_LOOP_OK;
15 }

View Code

(13在materialize_derived(图2#8)中

是因为不是union重新步向exec(详细见(10))

图片 83图片 84

 1   if (unit->is_union())
 2   {
 3     // execute union without clean up
 4     res= unit->execute(thd);
 5   }
 6   else
 7   {
 8     SELECT_LEX *first_select= unit->first_select();
 9     JOIN *join= first_select->join;
10     SELECT_LEX *save_current_select= thd->lex->current_select();
11     thd->lex->set_current_select(first_select);
12 
13     DBUG_ASSERT(join && join->is_optimized());
14 
15     unit->set_limit(first_select);
16 
17     join->exec();
18     res= join->error;
19     thd->lex->set_current_select(save_current_select);
20   }

View Code

(14)在join_init_read_record(图2#4)中

图片 85图片 86

 1 /**
 2   @brief Prepare table for reading rows and read first record.
 3   @details
 4     Prior to reading the table following tasks are done, (in the order of
 5     execution):
 6       .) derived tables are materialized
 7       .) duplicates removed (tmp tables only)
 8       .) table is sorted with filesort (both non-tmp and tmp tables)
 9     After this have been done this function resets quick select, if it's
10     present, sets up table reading functions, and reads first record.
11 
12   @retval
13     0   Ok
14   @retval
15     -1   End of records
16   @retval
17     1   Error
18 */
19 
20 int join_init_read_record(QEP_TAB *tab)
21 {
22   int error;
23 
24   if (tab->distinct && tab->remove_duplicates())  // Remove duplicates.
25     return 1;
26   if (tab->filesort && tab->sort_table())     // Sort table.
27     return 1;
28 
29   if (tab->quick() && (error= tab->quick()->reset()))
30   {
31     /* Ensures error status is propageted back to client */
32     report_handler_error(tab->table(), error);
33     return 1;
34   }
35   if (init_read_record(&tab->read_record, tab->join()->thd, NULL, tab,
36                        1, 1, FALSE))
37     return 1;
38 
39   return (*tab->read_record.read_record)(&tab->read_record);
40 }

View Code

进入init_read_record

(15)在init_read_record(图2#3)中

不举办quick操作即不开展range,也不开展sort

图片 87图片 88

 1   else
 2   {
 3     DBUG_PRINT("info",("using rr_sequential"));
 4     info->read_record=rr_sequential;
 5     if ((error= table->file->ha_rnd_init(1)))
 6       goto err;
 7     /* We can use record cache if we don't update dynamic length tables */
 8     if (!table->no_cache &&
 9     (use_record_cache > 0 ||
10      (int) table->reginfo.lock_type <= (int) TL_READ_HIGH_PRIORITY ||
11      !(table->s->db_options_in_use & HA_OPTION_PACK_RECORD) ||
12      (use_record_cache < 0 &&
13       !(table->file->ha_table_flags() & HA_NOT_DELETE_WITH_CACHE))))
14       (void) table->file->extra_opt(HA_EXTRA_CACHE,
15                   thd->variables.read_buff_size);
16   }

View Code

在其间调用ha_rnd_init函数,下三个if差十分的少不进去

(16)在ha_rnd_init(图2#2)中

调用了rnd_init,他是多个虚方法,会触发调用ha_perfschema::rnd_init

(17)在ha_perfschema::rnd_init(图2#1)中

图片 89图片 90

 1 int ha_perfschema::rnd_init(bool scan)
 2 {
 3   int result;
 4   DBUG_ENTER("ha_perfschema::rnd_init");
 5 
 6   DBUG_ASSERT(m_table_share);
 7   DBUG_ASSERT(m_table_share->m_open_table != NULL);
 8 
 9   stats.records= 0;
10   if (m_table == NULL)
11     m_table= m_table_share->m_open_table();
12   else
13     m_table->reset_position();
14 
15   if (m_table != NULL)
16     m_table->rnd_init(scan);
17 
18   result= m_table ? 0 : HA_ERR_OUT_OF_MEM;
19   DBUG_RETURN(result);
20 }

View Code

(18)在ha_perfschema::rnd_init(图2#0)中

图片 91图片 92

 1 int table_global_status::rnd_init(bool scan)
 2 {
 3   /* Build a cache of all global status variables. Sum across threads. */
 4   m_status_cache.materialize_global();
 5 
 6   /* Record the current number of status variables to detect subsequent changes. */
 7   ulonglong status_version= m_status_cache.get_status_array_version();
 8 
 9   /*
10     The table context holds the current version of the global status array.
11     If scan == true, then allocate a new context from mem_root and store in TLS.
12     If scan == false, then restore from TLS.
13   */
14   m_context= (table_global_status_context *)current_thd->alloc(sizeof(table_global_status_context));
15   new(m_context) table_global_status_context(status_version, !scan);
16   return 0;
17 }

View Code

在materialize_global中

图片 93图片 94

1 template <class Var_type>
2 int PFS_variable_cache<Var_type>::materialize_global()
3 {
4   if (is_materialized())
5     return 0;
6 
7   return do_materialize_global();
8 }

View Code

执行do_materialize_global

图片 95图片 96

1   PFS_connection_status_visitor visitor(&status_totals);
2   PFS_connection_iterator::visit_global(false, /* hosts */
3                                         false, /* users */
4                                         false, /* accounts */
5                                         false, /* threads */
6                                         true,  /* THDs */
7                                         &visitor);

View Code

(19)在PFS_status_variable_cache::init_show_var_array中

函数将all_status_vars里切合要求的赋给m_show_var_array中m_show_var_array的类型详见(那生龙活虎节的(21)),至于all_status_vars的来源,小编会在后面第二章第1小节中会提到。

图片 97图片 98

 1   for (Status_var_array::iterator show_var_iter= all_status_vars.begin();
 2        show_var_iter != all_status_vars.end();
 3        show_var_iter  )
 4   {
 5     SHOW_VAR show_var= *show_var_iter;
 6 
 7     /* Check if this status var should be excluded from the query. */
 8     if (filter_show_var(&show_var, strict))
 9       continue;
10 
11     if (show_var.type == SHOW_ARRAY)
12     {
13       /* Expand nested subarray. The name is used as a prefix. */
14       expand_show_var_array((SHOW_VAR *)show_var.value, show_var.name, strict);
15     }
16     else
17     {
18       show_var.name= make_show_var_name(NULL, show_var.name);
19       m_show_var_array.push_back(show_var);
20     }
21   }

View Code

有关为啥用倒车的sql语句只好猎取206个,而转用后能获取355个,少了Com初步的门类,前面的第二章第2小节中会提到,正好少146个。

图片 99图片 100

 1 bool PFS_status_variable_cache::filter_by_name(const SHOW_VAR *show_var)
 2 {
 3   DBUG_ASSERT(show_var);
 4   DBUG_ASSERT(show_var->name);
 5 
 6   if (show_var->type == SHOW_ARRAY)
 7   {
 8     /* The SHOW_ARRAY name is the prefix for the variables in the subarray. */
 9     const char *prefix= show_var->name;
10     /* Exclude COM counters if not a SHOW STATUS command. */
11     if (!my_strcasecmp(system_charset_info, prefix, "Com") && !m_show_command)
12       return true;
13   }
14   else
15   {
16     /*
17       Slave status resides in Performance Schema replication tables. Exclude
18       these slave status variables from the SHOW STATUS command and from the
19       status tables.
20       Assume null prefix to ensure that only server-defined slave status
21       variables are filtered.
22     */
23     const char *name= show_var->name;
24     if (!my_strcasecmp(system_charset_info, name, "Slave_running") ||
25         !my_strcasecmp(system_charset_info, name, "Slave_retried_transactions") ||
26         !my_strcasecmp(system_charset_info, name, "Slave_last_heartbeat") ||
27         !my_strcasecmp(system_charset_info, name, "Slave_received_heartbeats") ||
28         !my_strcasecmp(system_charset_info, name, "Slave_heartbeat_period"))
29     {
30       return true;
31     }
32   }
33 
34   return false;
35 }

View Code

秘密在PFS_status_variable_cache::filter_show_var中的filter_by_name中

(20)在FS_connection_iterator::visit_global中

m_status_vars指向do_materialize_global对应于status_totals

对于global_status_var的值进行总括PFS_connection_status_visitor::visit_global()->add_to_status(m_status_vars, &global_status_var, false);将global_status_var加到m_status_vars里。
对于各个线程对应status_var的值进行总结Global_THD_manager::do_for_all_thd() -> for_each()->add_to_status(m_status_vars, &thd->status_var, false);将global_status_var加到m_status_vars里。

对于global_status_var的品种感兴趣的话在其次章第3小节中作者会提到。

图片 101图片 102

1 manifest(m_current_thd, m_show_var_array.begin(), &status_totals, "", false, true);

View Code

(21)在PFS_status_variable_cache::manifest中

对于SHOW_VAR

图片 103图片 104

 1 /**
 2   SHOW STATUS Server status variable
 3 */
 4 struct st_mysql_show_var
 5 {
 6   const char *name;
 7   char *value;
 8   enum enum_mysql_show_type type;
 9   enum enum_mysql_show_scope scope;
10 };

View Code

对于enum_mysql_show_type的定义,展现的系列,便是函数、数组、字符、字符串等等。

图片 105图片 106

 1 /**
 2   Declarations for SHOW STATUS support in plugins
 3 */
 4 enum enum_mysql_show_type
 5 {
 6   SHOW_UNDEF, SHOW_BOOL,
 7   SHOW_INT,        ///< shown as _unsigned_ int
 8   SHOW_LONG,       ///< shown as _unsigned_ long
 9   SHOW_LONGLONG,   ///< shown as _unsigned_ longlong
10   SHOW_CHAR, SHOW_CHAR_PTR,
11   SHOW_ARRAY, SHOW_FUNC, SHOW_DOUBLE
12 #ifdef MYSQL_SERVER
13   /*
14     This include defines server-only values of the enum.
15     Using them in plugins is not supported.
16   */
17   #include "sql_plugin_enum.h"
18 #endif
19 };

View Code

对于enum_mysql_show_scope的定义,彰显用于那种命令。

图片 107图片 108

 1 /**
 2   Status variable scope.
 3   Only GLOBAL status variable scope is available in plugins.
 4 */
 5 enum enum_mysql_show_scope
 6 {
 7   SHOW_SCOPE_UNDEF,
 8   SHOW_SCOPE_GLOBAL
 9 #ifdef MYSQL_SERVER
10   /* Server-only values. Not supported in plugins. */
11   ,
12   SHOW_SCOPE_SESSION,
13   SHOW_SCOPE_ALL
14 #endif
15 };

View Code

对于每一个m_show_var_array中的成分

第后生可畏推行SHOW_VASportage下表示项目是函数的函数

图片 109图片 110

 1     /*
 2       If the value is a function reference, then execute the function and
 3       reevaluate the new SHOW_TYPE and value. Handle nested case where
 4       SHOW_FUNC resolves to another SHOW_FUNC.
 5     */
 6     if (show_var_ptr->type == SHOW_FUNC)
 7     {
 8       show_var_tmp= *show_var_ptr;
 9       /*
10         Execute the function reference in show_var_tmp->value, which returns
11         show_var_tmp with a new type and new value.
12       */
13       for (const SHOW_VAR *var= show_var_ptr; var->type == SHOW_FUNC; var= &show_var_tmp)
14       {
15         ((mysql_show_var_func)(var->value))(thd, &show_var_tmp, value_buf);
16       }
17       show_var_ptr= &show_var_tmp;
18     }

View Code

再要是越过SHOW_VA君越下代表项目是数组的,就重新调用manifest展开数组,方便赋值。

图片 111图片 112

1  if (show_var_ptr->type == SHOW_ARRAY)
2     {
3       /*
4         Status variables of type SHOW_ARRAY were expanded and filtered by
5         init_show_var_array(), except where a SHOW_FUNC resolves into a
6         SHOW_ARRAY, such as with InnoDB. Recurse to expand the subarray.
7       */
8       manifest(thd, (SHOW_VAR *)show_var_ptr->value, status_vars, show_var_ptr->name, true, strict);
9     }

View Code

末尾蒙受SHOW_VA凯雷德下代表项目不是数组的,将m_show_var_array复制给了m_cache。

图片 113图片 114

 1 else
 2     {
 3       /* Add the materialized status variable to the cache. */
 4       SHOW_VAR show_var= *show_var_ptr;
 5       /*
 6         For nested array expansions, make a copy of the variable name, just as
 7         done in init_show_var_array().
 8       */
 9       if (nested_array)
10         show_var.name= make_show_var_name(prefix, show_var_ptr->name);
11 
12       /* Convert status value to string format. Add to the cache. */
13       Status_variable status_var(&show_var, status_vars, m_query_scope);
14       m_cache.push_back(status_var);
15     }

View Code

图片 115

               图3       施行SQL命令的逻辑关系(读取下一条数据)   

(22)在rr_sequential(图3#3中)

图片 116图片 117

 1 int rr_sequential(READ_RECORD *info)
 2 {
 3   int tmp;
 4   while ((tmp=info->table->file->ha_rnd_next(info->record)))
 5   {
 6     /*
 7       ha_rnd_next can return RECORD_DELETED for MyISAM when one thread is
 8       reading and another deleting without locks.
 9     */
10     if (info->thd->killed || (tmp != HA_ERR_RECORD_DELETED))
11     {
12       tmp= rr_handle_error(info, tmp);
13       break;
14     }
15   }
16   return tmp;
17 }

View Code

(23)同样地在ha_rnd_next执行了rnd_next,这些rnd_next都是如出后生可畏辙的接口(图3#2是虚函数),个中int ha_perfschema::rnd_next(uchar *buf)

图片 118图片 119

 1 int ha_perfschema::rnd_next(uchar *buf)
 2 {
 3   DBUG_ENTER("ha_perfschema::rnd_next");
 4   if (!PFS_ENABLED())
 5   {
 6     table->status= STATUS_NOT_FOUND;
 7     DBUG_RETURN(HA_ERR_END_OF_FILE);
 8   }
 9 
10   DBUG_ASSERT(m_table);
11   ha_statistic_increment(&SSV::ha_read_rnd_next_count);
12 
13   int result= m_table->rnd_next();
14   if (result == 0)
15   {
16     result= m_table->read_row(table, buf, table->field);
17     if (result == 0)
18       stats.records  ;
19   }
20   table->status= (result ? STATUS_NOT_FOUND : 0);
21   DBUG_RETURN(result);
22 }

View Code

这里的uchar *buf就是record

(24)在table_global_status::rnd_next中

图片 120图片 121

 1 int table_global_status::rnd_next(void)
 2 {
 3   for (m_pos.set_at(&m_next_pos);
 4        m_pos.m_index < m_status_cache.size();
 5        m_pos.next())
 6   {
 7     const Status_variable *status_var= m_status_cache.get(m_pos.m_index);
 8     if (status_var != NULL)
 9     {
10       make_row(status_var);
11       m_next_pos.set_after(&m_pos);
12       return 0;
13     }
14   }
15   return HA_ERR_END_OF_FILE;
16 }

View Code

(25)在PFS_engine_table::read_row中

图片 122图片 123

 1 /**
 2   Read a table row.
 3   @param table            Table handle
 4   @param buf              Row buffer
 5   @param fields           Table fields
 6   @return 0 on success
 7 */
 8 int PFS_engine_table::read_row(TABLE *table,
 9                                unsigned char *buf,
10                                Field **fields)
11 {
12   my_bitmap_map *org_bitmap;
13   Field *f;
14   Field **fields_reset;
15 
16   /*
17     Make sure the table structure is as expected before mapping
18     hard wired columns in read_row_values.
19   */
20   if (! m_share_ptr->m_checked)
21   {
22     return HA_ERR_TABLE_NEEDS_UPGRADE;
23   }
24 
25   /* We must read all columns in case a table is opened for update */
26   bool read_all= !bitmap_is_clear_all(table->write_set);
27 
28   /* We internally write to Fields to support the read interface */
29   org_bitmap= dbug_tmp_use_all_columns(table, table->write_set);
30 
31   /*
32     Some callers of the storage engine interface do not honor the
33     f->is_null() flag, and will attempt to read the data itself.
34     A known offender is mysql_checksum_table().
35     For robustness, reset every field.
36   */
37   for (fields_reset= fields; (f= *fields_reset) ; fields_reset  )
38     f->reset();
39 
40   int result= read_row_values(table, buf, fields, read_all);
41   dbug_tmp_restore_column_map(table->write_set, org_bitmap);
42 
43   return result;
44 }

View Code

先是会把田野同志s里的因素清空,然后经过read_row_values将新值赋给QEP_TAB的fields,

图片 124图片 125

 1 int table_global_variables
 2 ::read_row_values(TABLE *table,
 3                   unsigned char *buf,
 4                   Field **fields,
 5                   bool read_all)
 6 {
 7   Field *f;
 8 
 9   if (unlikely(! m_row_exists))
10     return HA_ERR_RECORD_DELETED;
11 
12   /* Set the null bits */
13   DBUG_ASSERT(table->s->null_bytes == 1);
14   buf[0]= 0;
15 
16   for (; (f= *fields) ; fields  )
17   {
18     if (read_all || bitmap_is_set(table->read_set, f->field_index))
19     {
20       switch(f->field_index)
21       {
22       case 0: /* VARIABLE_NAME */
23         set_field_varchar_utf8(f, m_row.m_variable_name.m_str, m_row.m_variable_name.m_length);
24         break;
25       case 1: /* VARIABLE_VALUE */
26         set_field_varchar_utf8(f, m_row.m_variable_value.m_str, m_row.m_variable_value.m_length);
27         break;
28       default:
29         DBUG_ASSERT(false);
30       }
31     }
32   }
33 
34   return 0;
35 }

View Code

(26)在end_send中

图片 126图片 127

  1 /*****************************************************************************
  2   DESCRIPTION
  3     Functions that end one nested loop iteration. Different functions
  4     are used to support GROUP BY clause and to redirect records
  5     to a table (e.g. in case of SELECT into a temporary table) or to the
  6     network client.
  7     See the enum_nested_loop_state enumeration for the description of return
  8     values.
  9 *****************************************************************************/
 10 
 11 /* ARGSUSED */
 12 static enum_nested_loop_state
 13 end_send(JOIN *join, QEP_TAB *qep_tab, bool end_of_records)
 14 {
 15   DBUG_ENTER("end_send");
 16   /*
 17     When all tables are const this function is called with jointab == NULL.
 18     This function shouldn't be called for the first join_tab as it needs
 19     to get fields from previous tab.
 20 
 21     Note that qep_tab may be one past the last of qep_tab! So don't read its
 22     pointed content. But you can read qep_tab[-1] then.
 23   */
 24   DBUG_ASSERT(qep_tab == NULL || qep_tab > join->qep_tab);
 25   //TODO pass fields via argument
 26   List<Item> *fields= qep_tab ? qep_tab[-1].fields : join->fields;
 27 
 28   if (!end_of_records)
 29   {
 30     int error;
 31 
 32     if (join->tables &&
 33         // In case filesort has been used and zeroed quick():
 34         (join->qep_tab[0].quick_optim() &&
 35          join->qep_tab[0].quick_optim()->is_loose_index_scan()))
 36     {
 37       // Copy non-aggregated fields when loose index scan is used.
 38       if (copy_fields(&join->tmp_table_param, join->thd))
 39         DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */
 40     }
 41     // Use JOIN's HAVING for the case of tableless SELECT.
 42     if (join->having_cond && join->having_cond->val_int() == 0)
 43       DBUG_RETURN(NESTED_LOOP_OK);               // Didn't match having
 44     error=0;
 45     if (join->do_send_rows)
 46       error= join->select_lex->query_result()->send_data(*fields);
 47     if (error)
 48       DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */
 49 
 50       join->send_records;
 51     if (join->send_records >= join->unit->select_limit_cnt &&
 52         !join->do_send_rows)
 53     {
 54       /*
 55         If we have used Priority Queue for optimizing order by with limit,
 56         then stop here, there are no more records to consume.
 57         When this optimization is used, end_send is called on the next
 58         join_tab.
 59       */
 60       if (join->order &&
 61           join->calc_found_rows &&
 62           qep_tab > join->qep_tab &&
 63           qep_tab[-1].filesort &&
 64           qep_tab[-1].filesort->using_pq)
 65       {
 66         DBUG_PRINT("info", ("filesort NESTED_LOOP_QUERY_LIMIT"));
 67         DBUG_RETURN(NESTED_LOOP_QUERY_LIMIT);
 68       }
 69     }
 70     if (join->send_records >= join->unit->select_limit_cnt &&
 71         join->do_send_rows)
 72     {
 73       if (join->calc_found_rows)
 74       {
 75         QEP_TAB *first= &join->qep_tab[0];
 76           if ((join->primary_tables == 1) &&
 77                   !join->sort_and_group &&
 78                   !join->send_group_parts &&
 79                   !join->having_cond &&
 80                   !first->condition() &&
 81                   !(first->quick()) &&
 82               (first->table()->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) &&
 83                   (first->ref().key < 0))
 84           {
 85             /* Join over all rows in table;  Return number of found rows */
 86             TABLE *table= first->table();
 87 
 88             if (table->sort.has_filesort_result())
 89             {
 90               /* Using filesort */
 91               join->send_records= table->sort.found_records;
 92             }
 93             else
 94             {
 95               table->file->info(HA_STATUS_VARIABLE);
 96               join->send_records= table->file->stats.records;
 97             }
 98           }
 99           else 
100           {
101             join->do_send_rows= 0;
102             if (join->unit->fake_select_lex)
103               join->unit->fake_select_lex->select_limit= 0;
104             DBUG_RETURN(NESTED_LOOP_OK);
105           }
106       }
107       DBUG_RETURN(NESTED_LOOP_QUERY_LIMIT);      // Abort nicely
108     }
109     else if (join->send_records >= join->fetch_limit)
110     {
111       /*
112         There is a server side cursor and all rows for
113         this fetch request are sent.
114       */
115       DBUG_RETURN(NESTED_LOOP_CURSOR_LIMIT);
116     }
117   }
118   DBUG_RETURN(NESTED_LOOP_OK);
119 }

View Code

将QEP_TAB中的fields通过send_data间接将数据发送给客商端。

二、show global status的主导数据

地点花了十分的大的篇幅讲了show global status的日前已知的变迁进程

1.all_status_vars的来源

(1)我们得以在sql/sql_show中找到它的概念

 1 Status_var_array all_status_vars(0);

all_status_vars是SHOW_VA巴博斯 SLK级(详见第生机勃勃章2(21))的数组

 1 typedef std::vector<st_mysql_show_var> Status_var_array; 

(2)all_status_vars加载了数据库的操作景况和全数存款和储蓄引擎的图景

由此以下多少个函数:

1)在sql/mysqld.cc加载了数据库的操作意况

图片 128图片 129

1   /*
2     Add server status variables to the dynamic list of
3     status variables that is shown by SHOW STATUS.
4     Later, in plugin_init, and mysql_install_plugin
5     new entries could be added to that list.
6   */
7   if (add_status_vars(status_vars))
8     return 1; // an error was already reported

View Code

2)在sql/sql_plugin.cc中的plugin_initialize加载了独具存款和储蓄引擎的景况

图片 130图片 131

1   if (plugin->plugin->status_vars)
2   {
3     if (add_status_vars(plugin->plugin->status_vars))
4       goto err;
5   }

View Code

2.气象加载

(1)数据库的操作意况

1)status_vars

能够直接看看加载的是status_vars,这里做个计数,

除此而外三个array,还有DUG_OFF的3项,以及EMBEDDED_LIBRARY,为125项。

图片 132图片 133

  1 SHOW_VAR status_vars[]= {
  2   {"Aborted_clients",          (char*) &aborted_threads,                              SHOW_LONG,               SHOW_SCOPE_GLOBAL},
  3 #ifndef EMBEDDED_LIBRARY
  4   {"Aborted_connects",         (char*) &show_aborted_connects,                        SHOW_FUNC,               SHOW_SCOPE_GLOBAL},
  5 #endif
  6 #ifdef HAVE_REPLICATION
  7 #ifndef DBUG_OFF
  8   {"Ongoing_anonymous_gtid_violating_transaction_count",(char*) &show_ongoing_anonymous_gtid_violating_transaction_count, SHOW_FUNC, SHOW_SCOPE_GLOBAL},
  9 #endif//!DBUG_OFF
 10   {"Ongoing_anonymous_transaction_count",(char*) &show_ongoing_anonymous_transaction_count, SHOW_FUNC, SHOW_SCOPE_GLOBAL},
 11 #ifndef DBUG_OFF
 12   {"Ongoing_automatic_gtid_violating_transaction_count",(char*) &show_ongoing_automatic_gtid_violating_transaction_count, SHOW_FUNC, SHOW_SCOPE_GLOBAL},
 13 #endif//!DBUG_OFF
 14 #endif//HAVE_REPLICATION
 15   {"Binlog_cache_disk_use",    (char*) &binlog_cache_disk_use,                        SHOW_LONG,               SHOW_SCOPE_GLOBAL},
 16   {"Binlog_cache_use",         (char*) &binlog_cache_use,                             SHOW_LONG,               SHOW_SCOPE_GLOBAL},
 17   {"Binlog_stmt_cache_disk_use",(char*) &binlog_stmt_cache_disk_use,                  SHOW_LONG,               SHOW_SCOPE_GLOBAL},
 18   {"Binlog_stmt_cache_use",    (char*) &binlog_stmt_cache_use,                        SHOW_LONG,               SHOW_SCOPE_GLOBAL},
 19   {"Bytes_received",           (char*) offsetof(STATUS_VAR, bytes_received),          SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 20   {"Bytes_sent",               (char*) offsetof(STATUS_VAR, bytes_sent),              SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 21   {"Com",                      (char*) com_status_vars,                               SHOW_ARRAY,              SHOW_SCOPE_ALL},
 22   {"Com_stmt_reprepare",       (char*) offsetof(STATUS_VAR, com_stmt_reprepare),      SHOW_LONG_STATUS,        SHOW_SCOPE_ALL},
 23   {"Compression",              (char*) &show_net_compression,                         SHOW_FUNC,               SHOW_SCOPE_SESSION},
 24   {"Connections",              (char*) &show_thread_id_count,                         SHOW_FUNC,               SHOW_SCOPE_GLOBAL},
 25 #ifndef EMBEDDED_LIBRARY
 26   {"Connection_errors_accept",   (char*) &show_connection_errors_accept,              SHOW_FUNC,               SHOW_SCOPE_GLOBAL},
 27   {"Connection_errors_internal", (char*) &connection_errors_internal,                 SHOW_LONG,               SHOW_SCOPE_GLOBAL},
 28   {"Connection_errors_max_connections",   (char*) &show_connection_errors_max_connection, SHOW_FUNC,           SHOW_SCOPE_GLOBAL},
 29   {"Connection_errors_peer_address", (char*) &connection_errors_peer_addr,            SHOW_LONG,               SHOW_SCOPE_GLOBAL},
 30   {"Connection_errors_select",   (char*) &show_connection_errors_select,              SHOW_FUNC,               SHOW_SCOPE_GLOBAL},
 31   {"Connection_errors_tcpwrap",  (char*) &show_connection_errors_tcpwrap,             SHOW_FUNC,               SHOW_SCOPE_GLOBAL},
 32 #endif
 33   {"Created_tmp_disk_tables",  (char*) offsetof(STATUS_VAR, created_tmp_disk_tables), SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 34   {"Created_tmp_files",        (char*) &my_tmp_file_created,                          SHOW_LONG,               SHOW_SCOPE_GLOBAL},
 35   {"Created_tmp_tables",       (char*) offsetof(STATUS_VAR, created_tmp_tables),      SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 36   {"Delayed_errors",           (char*) &delayed_insert_errors,                        SHOW_LONG,               SHOW_SCOPE_GLOBAL},
 37   {"Delayed_insert_threads",   (char*) &delayed_insert_threads,                       SHOW_LONG_NOFLUSH,       SHOW_SCOPE_GLOBAL},
 38   {"Delayed_writes",           (char*) &delayed_insert_writes,                        SHOW_LONG,               SHOW_SCOPE_GLOBAL},
 39   {"Flush_commands",           (char*) &refresh_version,                              SHOW_LONG_NOFLUSH,       SHOW_SCOPE_GLOBAL},
 40   {"Handler_commit",           (char*) offsetof(STATUS_VAR, ha_commit_count),         SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 41   {"Handler_delete",           (char*) offsetof(STATUS_VAR, ha_delete_count),         SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 42   {"Handler_discover",         (char*) offsetof(STATUS_VAR, ha_discover_count),       SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 43   {"Handler_external_lock",    (char*) offsetof(STATUS_VAR, ha_external_lock_count),  SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 44   {"Handler_mrr_init",         (char*) offsetof(STATUS_VAR, ha_multi_range_read_init_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},
 45   {"Handler_prepare",          (char*) offsetof(STATUS_VAR, ha_prepare_count),        SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 46   {"Handler_read_first",       (char*) offsetof(STATUS_VAR, ha_read_first_count),     SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 47   {"Handler_read_key",         (char*) offsetof(STATUS_VAR, ha_read_key_count),       SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 48   {"Handler_read_last",        (char*) offsetof(STATUS_VAR, ha_read_last_count),      SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 49   {"Handler_read_next",        (char*) offsetof(STATUS_VAR, ha_read_next_count),      SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 50   {"Handler_read_prev",        (char*) offsetof(STATUS_VAR, ha_read_prev_count),      SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 51   {"Handler_read_rnd",         (char*) offsetof(STATUS_VAR, ha_read_rnd_count),       SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 52   {"Handler_read_rnd_next",    (char*) offsetof(STATUS_VAR, ha_read_rnd_next_count),  SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 53   {"Handler_rollback",         (char*) offsetof(STATUS_VAR, ha_rollback_count),       SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 54   {"Handler_savepoint",        (char*) offsetof(STATUS_VAR, ha_savepoint_count),      SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 55   {"Handler_savepoint_rollback",(char*) offsetof(STATUS_VAR, ha_savepoint_rollback_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},
 56   {"Handler_update",           (char*) offsetof(STATUS_VAR, ha_update_count),         SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 57   {"Handler_write",            (char*) offsetof(STATUS_VAR, ha_write_count),          SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 58   {"Key_blocks_not_flushed",   (char*) offsetof(KEY_CACHE, global_blocks_changed),    SHOW_KEY_CACHE_LONG,     SHOW_SCOPE_GLOBAL},
 59   {"Key_blocks_unused",        (char*) offsetof(KEY_CACHE, blocks_unused),            SHOW_KEY_CACHE_LONG,     SHOW_SCOPE_GLOBAL},
 60   {"Key_blocks_used",          (char*) offsetof(KEY_CACHE, blocks_used),              SHOW_KEY_CACHE_LONG,     SHOW_SCOPE_GLOBAL},
 61   {"Key_read_requests",        (char*) offsetof(KEY_CACHE, global_cache_r_requests),  SHOW_KEY_CACHE_LONGLONG, SHOW_SCOPE_GLOBAL},
 62   {"Key_reads",                (char*) offsetof(KEY_CACHE, global_cache_read),        SHOW_KEY_CACHE_LONGLONG, SHOW_SCOPE_GLOBAL},
 63   {"Key_write_requests",       (char*) offsetof(KEY_CACHE, global_cache_w_requests),  SHOW_KEY_CACHE_LONGLONG, SHOW_SCOPE_GLOBAL},
 64   {"Key_writes",               (char*) offsetof(KEY_CACHE, global_cache_write),       SHOW_KEY_CACHE_LONGLONG, SHOW_SCOPE_GLOBAL},
 65   {"Last_query_cost",          (char*) offsetof(STATUS_VAR, last_query_cost),         SHOW_DOUBLE_STATUS,      SHOW_SCOPE_SESSION},
 66   {"Last_query_partial_plans", (char*) offsetof(STATUS_VAR, last_query_partial_plans),SHOW_LONGLONG_STATUS,    SHOW_SCOPE_SESSION},
 67 #ifndef EMBEDDED_LIBRARY
 68   {"Locked_connects",          (char*) &locked_account_connection_count,              SHOW_LONG,               SHOW_SCOPE_GLOBAL},
 69 #endif
 70   {"Max_execution_time_exceeded",   (char*) offsetof(STATUS_VAR, max_execution_time_exceeded),   SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},
 71   {"Max_execution_time_set",        (char*) offsetof(STATUS_VAR, max_execution_time_set),        SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},
 72   {"Max_execution_time_set_failed", (char*) offsetof(STATUS_VAR, max_execution_time_set_failed), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},
 73   {"Max_used_connections",     (char*) &Connection_handler_manager::max_used_connections,        SHOW_LONG,        SHOW_SCOPE_GLOBAL},
 74   {"Max_used_connections_time",(char*) &show_max_used_connections_time,               SHOW_FUNC,               SHOW_SCOPE_GLOBAL},
 75   {"Not_flushed_delayed_rows", (char*) &delayed_rows_in_use,                          SHOW_LONG_NOFLUSH,       SHOW_SCOPE_GLOBAL},
 76   {"Open_files",               (char*) &my_file_opened,                               SHOW_LONG_NOFLUSH,       SHOW_SCOPE_GLOBAL},
 77   {"Open_streams",             (char*) &my_stream_opened,                             SHOW_LONG_NOFLUSH,       SHOW_SCOPE_GLOBAL},
 78   {"Open_table_definitions",   (char*) &show_table_definitions,                       SHOW_FUNC,               SHOW_SCOPE_GLOBAL},
 79   {"Open_tables",              (char*) &show_open_tables,                             SHOW_FUNC,               SHOW_SCOPE_ALL},
 80   {"Opened_files",             (char*) &my_file_total_opened,                         SHOW_LONG_NOFLUSH,       SHOW_SCOPE_GLOBAL},
 81   {"Opened_tables",            (char*) offsetof(STATUS_VAR, opened_tables),           SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 82   {"Opened_table_definitions", (char*) offsetof(STATUS_VAR, opened_shares),           SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 83   {"Prepared_stmt_count",      (char*) &show_prepared_stmt_count,                     SHOW_FUNC,               SHOW_SCOPE_GLOBAL},
 84   {"Qcache_free_blocks",       (char*) &query_cache.free_memory_blocks,               SHOW_LONG_NOFLUSH,       SHOW_SCOPE_GLOBAL},
 85   {"Qcache_free_memory",       (char*) &query_cache.free_memory,                      SHOW_LONG_NOFLUSH,       SHOW_SCOPE_GLOBAL},
 86   {"Qcache_hits",              (char*) &query_cache.hits,                             SHOW_LONG,               SHOW_SCOPE_GLOBAL},
 87   {"Qcache_inserts",           (char*) &query_cache.inserts,                          SHOW_LONG,               SHOW_SCOPE_GLOBAL},
 88   {"Qcache_lowmem_prunes",     (char*) &query_cache.lowmem_prunes,                    SHOW_LONG,               SHOW_SCOPE_GLOBAL},
 89   {"Qcache_not_cached",        (char*) &query_cache.refused,                          SHOW_LONG,               SHOW_SCOPE_GLOBAL},
 90   {"Qcache_queries_in_cache",  (char*) &query_cache.queries_in_cache,                 SHOW_LONG_NOFLUSH,       SHOW_SCOPE_GLOBAL},
 91   {"Qcache_total_blocks",      (char*) &query_cache.total_blocks,                     SHOW_LONG_NOFLUSH,       SHOW_SCOPE_GLOBAL},
 92   {"Queries",                  (char*) &show_queries,                                 SHOW_FUNC,               SHOW_SCOPE_ALL},
 93   {"Questions",                (char*) offsetof(STATUS_VAR, questions),               SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 94   {"Select_full_join",         (char*) offsetof(STATUS_VAR, select_full_join_count),  SHOW_LONGLONG_STATUS,    SHOW_SCOPE_ALL},
 95   {"Select_full_range_join",   (char*) offsetof(STATUS_VAR, select_full_range_join_count), SHOW_LONGLONG_STATUS, SHOW_SCOPE_ALL},
 96   {"Select_range",             (char*) offsetof(STATUS_VAR, select_range_count),       SHOW_LONGLONG_STATUS,   SHOW_SCOPE_ALL},
 97   {"Select_range_check",       (char*) offsetof(STATUS_VAR, select_range_check_count), SHOW_LONGLONG_STATUS,   SHOW_SCOPE_ALL},
 98   {"Select_scan",           (char*) offsetof(STATUS_VAR, select_scan_count),              SHOW_LONGLONG_STATUS,   SHOW_SCOPE_ALL},
 99   {"Slave_open_temp_tables",   (char*) &show_slave_open_temp_tables,                   SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
100 #ifdef HAVE_REPLICATION
101   {"Slave_retried_transactions",(char*) &show_slave_retried_trans,                     SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
102   {"Slave_heartbeat_period",   (char*) &show_heartbeat_period,                         SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
103   {"Slave_received_heartbeats",(char*) &show_slave_received_heartbeats,                SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
104   {"Slave_last_heartbeat",     (char*) &show_slave_last_heartbeat,                     SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
105 #ifndef DBUG_OFF
106   {"Slave_rows_last_search_algorithm_used",(char*) &show_slave_rows_last_search_algorithm_used, SHOW_FUNC,     SHOW_SCOPE_GLOBAL},
107 #endif
108   {"Slave_running",            (char*) &show_slave_running,                            SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
109 #endif
110 #ifndef EMBEDDED_LIBRARY
111   {"Slow_launch_threads",      (char*) &Per_thread_connection_handler::slow_launch_threads, SHOW_LONG,         SHOW_SCOPE_ALL},
112 #endif
113   {"Slow_queries",             (char*) offsetof(STATUS_VAR, long_query_count),         SHOW_LONGLONG_STATUS,   SHOW_SCOPE_ALL},
114   {"Sort_merge_passes",        (char*) offsetof(STATUS_VAR, filesort_merge_passes),    SHOW_LONGLONG_STATUS,   SHOW_SCOPE_ALL},
115   {"Sort_range",               (char*) offsetof(STATUS_VAR, filesort_range_count),     SHOW_LONGLONG_STATUS,   SHOW_SCOPE_ALL},
116   {"Sort_rows",                (char*) offsetof(STATUS_VAR, filesort_rows),            SHOW_LONGLONG_STATUS,   SHOW_SCOPE_ALL},
117   {"Sort_scan",                (char*) offsetof(STATUS_VAR, filesort_scan_count),      SHOW_LONGLONG_STATUS,   SHOW_SCOPE_ALL},
118 #ifdef HAVE_OPENSSL
119 #ifndef EMBEDDED_LIBRARY
120   {"Ssl_accept_renegotiates",  (char*) &show_ssl_ctx_sess_accept_renegotiate,          SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
121   {"Ssl_accepts",              (char*) &show_ssl_ctx_sess_accept,                      SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
122   {"Ssl_callback_cache_hits",  (char*) &show_ssl_ctx_sess_cb_hits,                     SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
123   {"Ssl_cipher",               (char*) &show_ssl_get_cipher,                           SHOW_FUNC,              SHOW_SCOPE_ALL},
124   {"Ssl_cipher_list",          (char*) &show_ssl_get_cipher_list,                      SHOW_FUNC,              SHOW_SCOPE_ALL},
125   {"Ssl_client_connects",      (char*) &show_ssl_ctx_sess_connect,                     SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
126   {"Ssl_connect_renegotiates", (char*) &show_ssl_ctx_sess_connect_renegotiate,         SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
127   {"Ssl_ctx_verify_depth",     (char*) &show_ssl_ctx_get_verify_depth,                 SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
128   {"Ssl_ctx_verify_mode",      (char*) &show_ssl_ctx_get_verify_mode,                  SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
129   {"Ssl_default_timeout",      (char*) &show_ssl_get_default_timeout,                  SHOW_FUNC,              SHOW_SCOPE_ALL},
130   {"Ssl_finished_accepts",     (char*) &show_ssl_ctx_sess_accept_good,                 SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
131   {"Ssl_finished_connects",    (char*) &show_ssl_ctx_sess_connect_good,                SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
132   {"Ssl_session_cache_hits",   (char*) &show_ssl_ctx_sess_hits,                        SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
133   {"Ssl_session_cache_misses", (char*) &show_ssl_ctx_sess_misses,                      SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
134   {"Ssl_session_cache_mode",   (char*) &show_ssl_ctx_get_session_cache_mode,           SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
135   {"Ssl_session_cache_overflows", (char*) &show_ssl_ctx_sess_cache_full,               SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
136   {"Ssl_session_cache_size",   (char*) &show_ssl_ctx_sess_get_cache_size,              SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
137   {"Ssl_session_cache_timeouts", (char*) &show_ssl_ctx_sess_timeouts,                  SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
138   {"Ssl_sessions_reused",      (char*) &show_ssl_session_reused,                       SHOW_FUNC,              SHOW_SCOPE_ALL},
139   {"Ssl_used_session_cache_entries",(char*) &show_ssl_ctx_sess_number,                 SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
140   {"Ssl_verify_depth",         (char*) &show_ssl_get_verify_depth,                     SHOW_FUNC,              SHOW_SCOPE_ALL},
141   {"Ssl_verify_mode",          (char*) &show_ssl_get_verify_mode,                      SHOW_FUNC,              SHOW_SCOPE_ALL},
142   {"Ssl_version",              (char*) &show_ssl_get_version,                          SHOW_FUNC,              SHOW_SCOPE_ALL},
143   {"Ssl_server_not_before",    (char*) &show_ssl_get_server_not_before,                SHOW_FUNC,              SHOW_SCOPE_ALL},
144   {"Ssl_server_not_after",     (char*) &show_ssl_get_server_not_after,                 SHOW_FUNC,              SHOW_SCOPE_ALL},
145 #ifndef HAVE_YASSL
146   {"Rsa_public_key",           (char*) &show_rsa_public_key,                           SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
147 #endif
148 #endif
149 #endif /* HAVE_OPENSSL */
150   {"Table_locks_immediate",    (char*) &locks_immediate,                               SHOW_LONG,              SHOW_SCOPE_GLOBAL},
151   {"Table_locks_waited",       (char*) &locks_waited,                                  SHOW_LONG,              SHOW_SCOPE_GLOBAL},
152   {"Table_open_cache_hits",    (char*) offsetof(STATUS_VAR, table_open_cache_hits),    SHOW_LONGLONG_STATUS,   SHOW_SCOPE_ALL},
153   {"Table_open_cache_misses",  (char*) offsetof(STATUS_VAR, table_open_cache_misses),  SHOW_LONGLONG_STATUS,   SHOW_SCOPE_ALL},
154   {"Table_open_cache_overflows",(char*) offsetof(STATUS_VAR, table_open_cache_overflows), SHOW_LONGLONG_STATUS,SHOW_SCOPE_ALL},
155   {"Tc_log_max_pages_used",    (char*) &tc_log_max_pages_used,                         SHOW_LONG,              SHOW_SCOPE_GLOBAL},
156   {"Tc_log_page_size",         (char*) &tc_log_page_size,                              SHOW_LONG_NOFLUSH,      SHOW_SCOPE_GLOBAL},
157   {"Tc_log_page_waits",        (char*) &tc_log_page_waits,                             SHOW_LONG,              SHOW_SCOPE_GLOBAL},
158 #ifndef EMBEDDED_LIBRARY
159   {"Threads_cached",           (char*) &Per_thread_connection_handler::blocked_pthread_count, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL},
160 #endif
161   {"Threads_connected",        (char*) &Connection_handler_manager::connection_count,  SHOW_INT,               SHOW_SCOPE_GLOBAL},
162   {"Threads_created",          (char*) &show_num_thread_created,                       SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
163   {"Threads_running",          (char*) &show_num_thread_running,                       SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
164   {"Uptime",                   (char*) &show_starttime,                                SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
165 #ifdef ENABLED_PROFILING
166   {"Uptime_since_flush_status",(char*) &show_flushstatustime,                          SHOW_FUNC,              SHOW_SCOPE_GLOBAL},
167 #endif
168   {NullS, NullS, SHOW_LONG, SHOW_SCOPE_ALL}
169 };

View Code

2)com_status_vars

鉴于大家看过函数PFS_status_variable_cache::manifest的加载进程,大家明白碰着array类型需要一而再加载,对于com_status_vars,总结下为147项。

图片 134图片 135

  1 SHOW_VAR com_status_vars[]= {
  2   {"admin_commands",       (char*) offsetof(STATUS_VAR, com_other),                                          SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
  3   {"assign_to_keycache",   (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_ASSIGN_TO_KEYCACHE]),         SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
  4   {"alter_db",             (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_ALTER_DB]),                   SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
  5   {"alter_db_upgrade",     (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_ALTER_DB_UPGRADE]),           SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
  6   {"alter_event",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_ALTER_EVENT]),                SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
  7   {"alter_function",       (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_ALTER_FUNCTION]),             SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
  8   {"alter_instance",       (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_ALTER_INSTANCE]),             SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
  9   {"alter_procedure",      (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_ALTER_PROCEDURE]),            SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 10   {"alter_server",         (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_ALTER_SERVER]),               SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 11   {"alter_table",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_ALTER_TABLE]),                SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 12   {"alter_tablespace",     (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_ALTER_TABLESPACE]),           SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 13   {"alter_user",           (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_ALTER_USER]),                 SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 14   {"analyze",              (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_ANALYZE]),                    SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 15   {"begin",                (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_BEGIN]),                      SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 16   {"binlog",               (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_BINLOG_BASE64_EVENT]),        SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 17   {"call_procedure",       (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CALL]),                       SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 18   {"change_db",            (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CHANGE_DB]),                  SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 19   {"change_master",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CHANGE_MASTER]),              SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 20   {"change_repl_filter",   (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CHANGE_REPLICATION_FILTER]),  SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 21   {"check",                (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CHECK]),                      SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 22   {"checksum",             (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CHECKSUM]),                   SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 23   {"commit",               (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_COMMIT]),                     SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 24   {"create_db",            (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CREATE_DB]),                  SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 25   {"create_event",         (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CREATE_EVENT]),               SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 26   {"create_function",      (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CREATE_SPFUNCTION]),          SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 27   {"create_index",         (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CREATE_INDEX]),               SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 28   {"create_procedure",     (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CREATE_PROCEDURE]),           SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 29   {"create_server",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CREATE_SERVER]),              SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 30   {"create_table",         (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CREATE_TABLE]),               SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 31   {"create_trigger",       (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CREATE_TRIGGER]),             SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 32   {"create_udf",           (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CREATE_FUNCTION]),            SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 33   {"create_user",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CREATE_USER]),                SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 34   {"create_view",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_CREATE_VIEW]),                SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 35   {"dealloc_sql",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_DEALLOCATE_PREPARE]),         SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 36   {"delete",               (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_DELETE]),                     SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 37   {"delete_multi",         (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_DELETE_MULTI]),               SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 38   {"do",                   (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_DO]),                         SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 39   {"drop_db",              (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_DROP_DB]),                    SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 40   {"drop_event",           (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_DROP_EVENT]),                 SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 41   {"drop_function",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_DROP_FUNCTION]),              SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 42   {"drop_index",           (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_DROP_INDEX]),                 SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 43   {"drop_procedure",       (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_DROP_PROCEDURE]),             SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 44   {"drop_server",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_DROP_SERVER]),                SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 45   {"drop_table",           (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_DROP_TABLE]),                 SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 46   {"drop_trigger",         (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_DROP_TRIGGER]),               SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 47   {"drop_user",            (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_DROP_USER]),                  SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 48   {"drop_view",            (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_DROP_VIEW]),                  SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 49   {"empty_query",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_EMPTY_QUERY]),                SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 50   {"execute_sql",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_EXECUTE]),                    SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 51   {"explain_other",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_EXPLAIN_OTHER]),              SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 52   {"flush",                (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_FLUSH]),                      SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 53   {"get_diagnostics",      (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_GET_DIAGNOSTICS]),            SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 54   {"grant",                (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_GRANT]),                      SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 55   {"ha_close",             (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_HA_CLOSE]),                   SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 56   {"ha_open",              (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_HA_OPEN]),                    SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 57   {"ha_read",              (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_HA_READ]),                    SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 58   {"help",                 (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_HELP]),                       SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 59   {"insert",               (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_INSERT]),                     SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 60   {"insert_select",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_INSERT_SELECT]),              SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 61   {"install_plugin",       (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_INSTALL_PLUGIN]),             SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 62   {"kill",                 (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_KILL]),                       SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 63   {"load",                 (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_LOAD]),                       SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 64   {"lock_tables",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_LOCK_TABLES]),                SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 65   {"optimize",             (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_OPTIMIZE]),                   SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 66   {"preload_keys",         (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_PRELOAD_KEYS]),               SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 67   {"prepare_sql",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_PREPARE]),                    SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 68   {"purge",                (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_PURGE]),                      SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 69   {"purge_before_date",    (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_PURGE_BEFORE]),               SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 70   {"release_savepoint",    (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_RELEASE_SAVEPOINT]),          SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 71   {"rename_table",         (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_RENAME_TABLE]),               SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 72   {"rename_user",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_RENAME_USER]),                SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 73   {"repair",               (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_REPAIR]),                     SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 74   {"replace",              (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_REPLACE]),                    SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 75   {"replace_select",       (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_REPLACE_SELECT]),             SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 76   {"reset",                (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_RESET]),                      SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 77   {"resignal",             (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_RESIGNAL]),                   SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 78   {"revoke",               (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_REVOKE]),                     SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 79   {"revoke_all",           (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_REVOKE_ALL]),                 SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 80   {"rollback",             (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_ROLLBACK]),                   SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 81   {"rollback_to_savepoint",(char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_ROLLBACK_TO_SAVEPOINT]),      SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 82   {"savepoint",            (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SAVEPOINT]),                  SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 83   {"select",               (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SELECT]),                     SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 84   {"set_option",           (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SET_OPTION]),                 SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 85   {"signal",               (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SIGNAL]),                     SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 86   {"show_binlog_events",   (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_BINLOG_EVENTS]),         SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 87   {"show_binlogs",         (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_BINLOGS]),               SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 88   {"show_charsets",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_CHARSETS]),              SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 89   {"show_collations",      (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_COLLATIONS]),            SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 90   {"show_create_db",       (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_CREATE_DB]),             SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 91   {"show_create_event",    (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_CREATE_EVENT]),          SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 92   {"show_create_func",     (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_CREATE_FUNC]),           SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 93   {"show_create_proc",     (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_CREATE_PROC]),           SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 94   {"show_create_table",    (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_CREATE]),                SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 95   {"show_create_trigger",  (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_CREATE_TRIGGER]),        SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 96   {"show_databases",       (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_DATABASES]),             SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 97   {"show_engine_logs",     (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_ENGINE_LOGS]),           SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 98   {"show_engine_mutex",    (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_ENGINE_MUTEX]),          SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
 99   {"show_engine_status",   (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_ENGINE_STATUS]),         SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
100   {"show_events",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_EVENTS]),                SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
101   {"show_errors",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_ERRORS]),                SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
102   {"show_fields",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_FIELDS]),                SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
103   {"show_function_code",   (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_FUNC_CODE]),             SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
104   {"show_function_status", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STATUS_FUNC]),           SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
105   {"show_grants",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_GRANTS]),                SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
106   {"show_keys",            (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_KEYS]),                  SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
107   {"show_master_status",   (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_MASTER_STAT]),           SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
108   {"show_open_tables",     (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_OPEN_TABLES]),           SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
109   {"show_plugins",         (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_PLUGINS]),               SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
110   {"show_privileges",      (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_PRIVILEGES]),            SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
111   {"show_procedure_code",  (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_PROC_CODE]),             SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
112   {"show_procedure_status",(char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STATUS_PROC]),           SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
113   {"show_processlist",     (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_PROCESSLIST]),           SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
114   {"show_profile",         (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_PROFILE]),               SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
115   {"show_profiles",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_PROFILES]),              SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
116   {"show_relaylog_events", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_RELAYLOG_EVENTS]),       SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
117   {"show_slave_hosts",     (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_SLAVE_HOSTS]),           SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
118   {"show_slave_status",    (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_SLAVE_STAT]),            SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
119   {"show_status",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STATUS]),                SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
120   {"show_storage_engines", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STORAGE_ENGINES]),       SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
121   {"show_table_status",    (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLE_STATUS]),          SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
122   {"show_tables",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLES]),                SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
123   {"show_triggers",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TRIGGERS]),              SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
124   {"show_variables",       (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_VARIABLES]),             SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
125   {"show_warnings",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_WARNS]),                 SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
126   {"show_create_user",     (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_CREATE_USER]),           SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
127   {"shutdown",             (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHUTDOWN]),                   SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
128   {"slave_start",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SLAVE_START]),                SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
129   {"slave_stop",           (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SLAVE_STOP]),                 SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
130   {"group_replication_start", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_START_GROUP_REPLICATION]), SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
131   {"group_replication_stop",  (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_STOP_GROUP_REPLICATION]),  SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
132   {"stmt_execute",         (char*) offsetof(STATUS_VAR, com_stmt_execute),                                   SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
133   {"stmt_close",           (char*) offsetof(STATUS_VAR, com_stmt_close),                                     SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
134   {"stmt_fetch",           (char*) offsetof(STATUS_VAR, com_stmt_fetch),                                     SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
135   {"stmt_prepare",         (char*) offsetof(STATUS_VAR, com_stmt_prepare),                                   SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
136   {"stmt_reset",           (char*) offsetof(STATUS_VAR, com_stmt_reset),                                     SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
137   {"stmt_send_long_data",  (char*) offsetof(STATUS_VAR, com_stmt_send_long_data),                            SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
138   {"truncate",             (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_TRUNCATE]),                   SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
139   {"uninstall_plugin",     (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_UNINSTALL_PLUGIN]),           SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
140   {"unlock_tables",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_UNLOCK_TABLES]),              SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
141   {"update",               (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_UPDATE]),                     SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
142   {"update_multi",         (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_UPDATE_MULTI]),               SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
143   {"xa_commit",            (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_XA_COMMIT]),                  SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
144   {"xa_end",               (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_XA_END]),                     SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
145   {"xa_prepare",           (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_XA_PREPARE]),                 SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
146   {"xa_recover",           (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_XA_RECOVER]),                 SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
147   {"xa_rollback",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_XA_ROLLBACK]),                SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
148   {"xa_start",             (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_XA_START]),                   SHOW_LONG_STATUS, SHOW_SCOPE_ALL},
149   {NullS, NullS, SHOW_LONG, SHOW_SCOPE_ALL}
150 };

View Code

(2)全体存款和储蓄引擎的意况

储存引擎是这么申明的,

以存款和储蓄引擎perfschema为例:

图片 136图片 137

 1 mysql_declare_plugin(perfschema)
 2 {
 3   MYSQL_STORAGE_ENGINE_PLUGIN,
 4   &pfs_storage_engine,
 5   pfs_engine_name,
 6   "Marc Alff, Oracle", /* Formerly Sun Microsystems, formerly MySQL */
 7   "Performance Schema",
 8   PLUGIN_LICENSE_GPL,
 9   pfs_init_func,                                /* Plugin Init */
10   pfs_done_func,                                /* Plugin Deinit */
11   0x0001 /* 0.1 */,
12   pfs_status_vars,                              /* status variables */
13   NULL,                                         /* system variables */
14   NULL,                                         /* config options */
15   0,                                            /* flags */
16 }
17 mysql_declare_plugin_end;

View Code

能够观察

图片 138图片 139

 1 #ifndef MYSQL_DYNAMIC_PLUGIN
 2 #define __MYSQL_DECLARE_PLUGIN(NAME, VERSION, PSIZE, DECLS)                   
 3 MYSQL_PLUGIN_EXPORT int VERSION= MYSQL_PLUGIN_INTERFACE_VERSION;                                  
 4 MYSQL_PLUGIN_EXPORT int PSIZE= sizeof(struct st_mysql_plugin);                                    
 5 MYSQL_PLUGIN_EXPORT struct st_mysql_plugin DECLS[]= {
 6 #else
 7 #define __MYSQL_DECLARE_PLUGIN(NAME, VERSION, PSIZE, DECLS)                   
 8 MYSQL_PLUGIN_EXPORT int _mysql_plugin_interface_version_= MYSQL_PLUGIN_INTERFACE_VERSION;         
 9 MYSQL_PLUGIN_EXPORT int _mysql_sizeof_struct_st_plugin_= sizeof(struct st_mysql_plugin);          
10 MYSQL_PLUGIN_EXPORT struct st_mysql_plugin _mysql_plugin_declarations_[]= {
11 #endif
12 
13 #define mysql_declare_plugin(NAME) 
14 __MYSQL_DECLARE_PLUGIN(NAME, 
15                  builtin_ ## NAME ## _plugin_interface_version, 
16                  builtin_ ## NAME ## _sizeof_struct_st_plugin, 
17                  builtin_ ## NAME ## _plugin)

View Code

一言九鼎的数据结构是struct st_mysql_plugin

图片 140图片 141

 1 /*
 2   Plugin description structure.
 3 */
 4 
 5 struct st_mysql_plugin
 6 {
 7   int type;             /* the plugin type (a MYSQL_XXX_PLUGIN value)   */
 8   void *info;           /* pointer to type-specific plugin descriptor   */
 9   const char *name;     /* plugin name                                  */
10   const char *author;   /* plugin author (for I_S.PLUGINS)              */
11   const char *descr;    /* general descriptive text (for I_S.PLUGINS)   */
12   int license;          /* the plugin license (PLUGIN_LICENSE_XXX)      */
13   int (*init)(MYSQL_PLUGIN);  /* the function to invoke when plugin is loaded */
14   int (*deinit)(MYSQL_PLUGIN);/* the function to invoke when plugin is unloaded */
15   unsigned int version; /* plugin version (for I_S.PLUGINS)             */
16   struct st_mysql_show_var *status_vars;
17   struct st_mysql_sys_var **system_vars;
18   void * __reserved1;   /* reserved for dependency checking             */
19   unsigned long flags;  /* flags for plugin */
20 };

View Code

制造的那一个函数通过cmake与mysql_mandatory_plugins项关系,在通过函数plugin_init加载到系统中

在文件cmake/plugin.cmake中

图片 142图片 143

 1   SET(THIS_PLUGIN_REFERENCE " builtin_${target}_plugin,")
 2     IF(ARG_NOT_FOR_EMBEDDED)
 3       SET(THIS_PLUGIN_REFERENCE "
 4 #ifndef EMBEDDED_LIBRARY
 5   ${THIS_PLUGIN_REFERENCE}
 6 #endif
 7 ")
 8     ENDIF()
 9     SET(PLUGINS_IN_THIS_SCOPE
10       "${PLUGINS_IN_THIS_SCOPE}${THIS_PLUGIN_REFERENCE}")
11 
12     IF(ARG_MANDATORY)
13       SET (mysql_mandatory_plugins  
14         "${mysql_mandatory_plugins} ${PLUGINS_IN_THIS_SCOPE}" 
15         PARENT_SCOPE)
16     ELSE()
17       SET (mysql_optional_plugins  
18         "${mysql_optional_plugins} ${PLUGINS_IN_THIS_SCOPE}"
19         PARENT_SCOPE)
20     ENDIF()

View Code

1.perfschema存款和储蓄引擎

在storage/perfschema/ha_perfschema.cc有pfs_status_vars总共30项

图片 144图片 145

 1 static struct st_mysql_show_var pfs_status_vars[]=
 2 {
 3   {"Performance_schema_mutex_classes_lost",
 4     (char*) &mutex_class_lost, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL},
 5   {"Performance_schema_rwlock_classes_lost",
 6     (char*) &rwlock_class_lost, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL},
 7   {"Performance_schema_cond_classes_lost",
 8     (char*) &cond_class_lost, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL},
 9   {"Performance_schema_thread_classes_lost",
10     (char*) &thread_class_lost, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL},
11   {"Performance_schema_file_classes_lost",
12     (char*) &file_class_lost, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL},
13   {"Performance_schema_socket_classes_lost",
14     (char*) &socket_class_lost, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL},
15   {"Performance_schema_memory_classes_lost",
16     (char*) &memory_class_lost, SHOW_LONG_NOFLUSH, SHOW_SCOPE_GLOBAL},
17   {"Performance_schema_mutex_instances_lost",
18     (char*) &show_func_mutex_instances_lost, SHOW_FUNC, SHOW_SCOPE_GLOBAL},
19   {"Performance_schema_rwlock_instances_lost",
20     (char*) &global_rwlock_container.m_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
21   {"Performance_schema_cond_instances_lost",
22     (char*) &global_cond_container.m_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
23   {"Performance_schema_thread_instances_lost",
24     (char*) &global_thread_container.m_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
25   {"Performance_schema_file_instances_lost",
26     (char*) &global_file_container.m_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
27   {"Performance_schema_file_handles_lost",
28     (char*) &file_handle_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
29   {"Performance_schema_socket_instances_lost",
30     (char*) &global_socket_container.m_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
31   {"Performance_schema_locker_lost",
32     (char*) &locker_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
33   /* table shares, can be flushed */
34   {"Performance_schema_table_instances_lost",
35     (char*) &global_table_share_container.m_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
36   /* table handles, can be flushed */
37   {"Performance_schema_table_handles_lost",
38     (char*) &global_table_container.m_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
39   /* table lock stats, can be flushed */
40   {"Performance_schema_table_lock_stat_lost",
41     (char*) &global_table_share_lock_container.m_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
42   /* table index stats, can be flushed */
43   {"Performance_schema_index_stat_lost",
44     (char*) &global_table_share_index_container.m_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
45   {"Performance_schema_hosts_lost",
46     (char*) &global_host_container.m_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
47   {"Performance_schema_users_lost",
48     (char*) &global_user_container.m_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
49   {"Performance_schema_accounts_lost",
50     (char*) &global_account_container.m_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
51   {"Performance_schema_stage_classes_lost",
52     (char*) &stage_class_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
53   {"Performance_schema_statement_classes_lost",
54     (char*) &statement_class_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
55   {"Performance_schema_digest_lost",
56     (char*) &digest_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
57   {"Performance_schema_session_connect_attrs_lost",
58     (char*) &session_connect_attrs_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
59   {"Performance_schema_program_lost",
60     (char*) &global_program_container.m_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
61   {"Performance_schema_nested_statement_lost",
62     (char*) &nested_statement_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
63   {"Performance_schema_prepared_statements_lost",
64     (char*) &global_prepared_stmt_container.m_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
65   {"Performance_schema_metadata_lock_lost",
66     (char*) &global_mdl_container.m_lost, SHOW_LONG, SHOW_SCOPE_GLOBAL},
67   {NullS, NullS, SHOW_LONG, SHOW_SCOPE_GLOBAL}
68 };

View Code

2.innodb囤积引擎

在storage/innobase/handler/ha_innodb.cc中有innodb_status_variables,这里除了DUB为51项

图片 146图片 147

  1 static SHOW_VAR innodb_status_variables[]= {
  2   {"buffer_pool_dump_status",
  3   (char*) &export_vars.innodb_buffer_pool_dump_status,      SHOW_CHAR, SHOW_SCOPE_GLOBAL},
  4   {"buffer_pool_load_status",
  5   (char*) &export_vars.innodb_buffer_pool_load_status,      SHOW_CHAR, SHOW_SCOPE_GLOBAL},
  6   {"buffer_pool_resize_status",
  7   (char*) &export_vars.innodb_buffer_pool_resize_status,  SHOW_CHAR, SHOW_SCOPE_GLOBAL},
  8   {"buffer_pool_pages_data",
  9   (char*) &export_vars.innodb_buffer_pool_pages_data,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 10   {"buffer_pool_bytes_data",
 11   (char*) &export_vars.innodb_buffer_pool_bytes_data,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 12   {"buffer_pool_pages_dirty",
 13   (char*) &export_vars.innodb_buffer_pool_pages_dirty,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 14   {"buffer_pool_bytes_dirty",
 15   (char*) &export_vars.innodb_buffer_pool_bytes_dirty,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 16   {"buffer_pool_pages_flushed",
 17   (char*) &export_vars.innodb_buffer_pool_pages_flushed,  SHOW_LONG, SHOW_SCOPE_GLOBAL},
 18   {"buffer_pool_pages_free",
 19   (char*) &export_vars.innodb_buffer_pool_pages_free,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 20 #ifdef UNIV_DEBUG
 21   {"buffer_pool_pages_latched",
 22   (char*) &export_vars.innodb_buffer_pool_pages_latched,  SHOW_LONG, SHOW_SCOPE_GLOBAL},
 23 #endif /* UNIV_DEBUG */
 24   {"buffer_pool_pages_misc",
 25   (char*) &export_vars.innodb_buffer_pool_pages_misc,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 26   {"buffer_pool_pages_total",
 27   (char*) &export_vars.innodb_buffer_pool_pages_total,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 28   {"buffer_pool_read_ahead_rnd",
 29   (char*) &export_vars.innodb_buffer_pool_read_ahead_rnd, SHOW_LONG, SHOW_SCOPE_GLOBAL},
 30   {"buffer_pool_read_ahead",
 31   (char*) &export_vars.innodb_buffer_pool_read_ahead,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 32   {"buffer_pool_read_ahead_evicted",
 33   (char*) &export_vars.innodb_buffer_pool_read_ahead_evicted, SHOW_LONG, SHOW_SCOPE_GLOBAL},
 34   {"buffer_pool_read_requests",
 35   (char*) &export_vars.innodb_buffer_pool_read_requests,  SHOW_LONG, SHOW_SCOPE_GLOBAL},
 36   {"buffer_pool_reads",
 37   (char*) &export_vars.innodb_buffer_pool_reads,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 38   {"buffer_pool_wait_free",
 39   (char*) &export_vars.innodb_buffer_pool_wait_free,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 40   {"buffer_pool_write_requests",
 41   (char*) &export_vars.innodb_buffer_pool_write_requests, SHOW_LONG, SHOW_SCOPE_GLOBAL},
 42   {"data_fsyncs",
 43   (char*) &export_vars.innodb_data_fsyncs,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
 44   {"data_pending_fsyncs",
 45   (char*) &export_vars.innodb_data_pending_fsyncs,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 46   {"data_pending_reads",
 47   (char*) &export_vars.innodb_data_pending_reads,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 48   {"data_pending_writes",
 49   (char*) &export_vars.innodb_data_pending_writes,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 50   {"data_read",
 51   (char*) &export_vars.innodb_data_read,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
 52   {"data_reads",
 53   (char*) &export_vars.innodb_data_reads,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
 54   {"data_writes",
 55   (char*) &export_vars.innodb_data_writes,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
 56   {"data_written",
 57   (char*) &export_vars.innodb_data_written,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
 58   {"dblwr_pages_written",
 59   (char*) &export_vars.innodb_dblwr_pages_written,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 60   {"dblwr_writes",
 61   (char*) &export_vars.innodb_dblwr_writes,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
 62   {"log_waits",
 63   (char*) &export_vars.innodb_log_waits,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
 64   {"log_write_requests",
 65   (char*) &export_vars.innodb_log_write_requests,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 66   {"log_writes",
 67   (char*) &export_vars.innodb_log_writes,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
 68   {"os_log_fsyncs",
 69   (char*) &export_vars.innodb_os_log_fsyncs,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
 70   {"os_log_pending_fsyncs",
 71   (char*) &export_vars.innodb_os_log_pending_fsyncs,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 72   {"os_log_pending_writes",
 73   (char*) &export_vars.innodb_os_log_pending_writes,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 74   {"os_log_written",
 75   (char*) &export_vars.innodb_os_log_written,          SHOW_LONGLONG, SHOW_SCOPE_GLOBAL},
 76   {"page_size",
 77   (char*) &export_vars.innodb_page_size,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
 78   {"pages_created",
 79   (char*) &export_vars.innodb_pages_created,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
 80   {"pages_read",
 81   (char*) &export_vars.innodb_pages_read,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
 82   {"pages_written",
 83   (char*) &export_vars.innodb_pages_written,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
 84   {"row_lock_current_waits",
 85   (char*) &export_vars.innodb_row_lock_current_waits,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 86   {"row_lock_time",
 87   (char*) &export_vars.innodb_row_lock_time,          SHOW_LONGLONG, SHOW_SCOPE_GLOBAL},
 88   {"row_lock_time_avg",
 89   (char*) &export_vars.innodb_row_lock_time_avg,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 90   {"row_lock_time_max",
 91   (char*) &export_vars.innodb_row_lock_time_max,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
 92   {"row_lock_waits",
 93   (char*) &export_vars.innodb_row_lock_waits,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
 94   {"rows_deleted",
 95   (char*) &export_vars.innodb_rows_deleted,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
 96   {"rows_inserted",
 97   (char*) &export_vars.innodb_rows_inserted,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
 98   {"rows_read",
 99   (char*) &export_vars.innodb_rows_read,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
100   {"rows_updated",
101   (char*) &export_vars.innodb_rows_updated,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
102   {"num_open_files",
103   (char*) &export_vars.innodb_num_open_files,          SHOW_LONG, SHOW_SCOPE_GLOBAL},
104   {"truncated_status_writes",
105   (char*) &export_vars.innodb_truncated_status_writes,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
106   {"available_undo_logs",
107   (char*) &export_vars.innodb_available_undo_logs,        SHOW_LONG, SHOW_SCOPE_GLOBAL},
108 #ifdef UNIV_DEBUG
109   {"purge_trx_id_age",
110   (char*) &export_vars.innodb_purge_trx_id_age,           SHOW_LONG, SHOW_SCOPE_GLOBAL},
111   {"purge_view_trx_id_age",
112   (char*) &export_vars.innodb_purge_view_trx_id_age,      SHOW_LONG, SHOW_SCOPE_GLOBAL},
113   {"ahi_drop_lookups",
114   (char*) &export_vars.innodb_ahi_drop_lookups,           SHOW_LONG, SHOW_SCOPE_GLOBAL},
115 #endif /* UNIV_DEBUG */
116   {NullS, NullS, SHOW_LONG, SHOW_SCOPE_GLOBAL}
117 };

View Code

innodb_status_variables_export,这里的调用的函数是为了呈现innodb_status_variables的内容。

图片 148图片 149

1 static SHOW_VAR innodb_status_variables_export[]= {
2     {"Innodb", (char*) &show_innodb_vars, SHOW_FUNC, SHOW_SCOPE_GLOBAL},
3     {NullS, NullS, SHOW_LONG, SHOW_SCOPE_GLOBAL}
4 };

View Code

这么一同353项,这样大家找到了有关于show global status全体的项目,大功告成了,接下去提取相关数据结构,相关数据结构的数据是或不是实时更新就好了。

3.皮之不存毛将焉附数据结构

在sql/sql_class.h

图片 150图片 151

 1 typedef struct system_status_var
 2 {
 3   /* IMPORTANT! See first_system_status_var definition below. */
 4   ulonglong created_tmp_disk_tables;
 5   ulonglong created_tmp_tables;
 6   ulonglong ha_commit_count;
 7   ulonglong ha_delete_count;
 8   ulonglong ha_read_first_count;
 9   ulonglong ha_read_last_count;
10   ulonglong ha_read_key_count;
11   ulonglong ha_read_next_count;
12   ulonglong ha_read_prev_count;
13   ulonglong ha_read_rnd_count;
14   ulonglong ha_read_rnd_next_count;
15   /*
16     This number doesn't include calls to the default implementation and
17     calls made by range access. The intent is to count only calls made by
18     BatchedKeyAccess.
19   */
20   ulonglong ha_multi_range_read_init_count;
21   ulonglong ha_rollback_count;
22   ulonglong ha_update_count;
23   ulonglong ha_write_count;
24   ulonglong ha_prepare_count;
25   ulonglong ha_discover_count;
26   ulonglong ha_savepoint_count;
27   ulonglong ha_savepoint_rollback_count;
28   ulonglong ha_external_lock_count;
29   ulonglong opened_tables;
30   ulonglong opened_shares;
31   ulonglong table_open_cache_hits;//缓存使用数?
32   ulonglong table_open_cache_misses;//缓存丢失数?
33   ulonglong table_open_cache_overflows;
34   ulonglong select_full_join_count;
35   ulonglong select_full_range_join_count;
36   ulonglong select_range_count;
37   ulonglong select_range_check_count;
38   ulonglong select_scan_count;
39   ulonglong long_query_count;     //慢查询数
40   ulonglong filesort_merge_passes;
41   ulonglong filesort_range_count;
42   ulonglong filesort_rows;
43   ulonglong filesort_scan_count;//全表扫描数?
44   /* Prepared statements and binary protocol. */
45   ulonglong com_stmt_prepare;
46   ulonglong com_stmt_reprepare;
47   ulonglong com_stmt_execute;
48   ulonglong com_stmt_send_long_data;
49   ulonglong com_stmt_fetch;
50   ulonglong com_stmt_reset;
51   ulonglong com_stmt_close;
52 
53   ulonglong bytes_received; //接受数据量
54   ulonglong bytes_sent;  //发送数据量
55 
56   ulonglong max_execution_time_exceeded;
57   ulonglong max_execution_time_set;
58   ulonglong max_execution_time_set_failed;
59 
60   /* Number of statements sent from the client. */
61   ulonglong questions;               //总请求数
62 
63   ulong com_other;
64   ulong com_stat[(uint) SQLCOM_END];//查询数,更新数,删除数,插入数
65 
66   /*
67     IMPORTANT! See last_system_status_var definition below. Variables after
68     'last_system_status_var' cannot be handled automatically by add_to_status()
69     and add_diff_to_status().
70   */
71   double last_query_cost;
72   ulonglong last_query_partial_plans;
73 
74 } STATUS_VAR;

View Code

在inlcude/my_sqlcommand.h

图片 152图片 153

  1 enum enum_sql_command {
  2   SQLCOM_SELECT,  // 查询数
  3   SQLCOM_CREATE_TABLE,
  4   SQLCOM_CREATE_INDEX,
  5   SQLCOM_ALTER_TABLE,
  6   SQLCOM_UPDATE,    //更新数
  7   SQLCOM_INSERT,    //插入数
  8   SQLCOM_INSERT_SELECT,
  9   SQLCOM_DELETE,   //删除数
 10   SQLCOM_TRUNCATE,
 11   SQLCOM_DROP_TABLE,
 12   SQLCOM_DROP_INDEX,
 13   SQLCOM_SHOW_DATABASES,
 14   SQLCOM_SHOW_TABLES,
 15   SQLCOM_SHOW_FIELDS,
 16   SQLCOM_SHOW_KEYS,
 17   SQLCOM_SHOW_VARIABLES,
 18   SQLCOM_SHOW_STATUS,
 19   SQLCOM_SHOW_ENGINE_LOGS,
 20   SQLCOM_SHOW_ENGINE_STATUS,
 21   SQLCOM_SHOW_ENGINE_MUTEX,
 22   SQLCOM_SHOW_PROCESSLIST,
 23   SQLCOM_SHOW_MASTER_STAT,
 24   SQLCOM_SHOW_SLAVE_STAT,
 25   SQLCOM_SHOW_GRANTS,
 26   SQLCOM_SHOW_CREATE,
 27   SQLCOM_SHOW_CHARSETS,
 28   SQLCOM_SHOW_COLLATIONS,
 29   SQLCOM_SHOW_CREATE_DB,
 30   SQLCOM_SHOW_TABLE_STATUS,
 31   SQLCOM_SHOW_TRIGGERS,
 32   SQLCOM_LOAD,
 33   SQLCOM_SET_OPTION,
 34   SQLCOM_LOCK_TABLES,
 35   SQLCOM_UNLOCK_TABLES,
 36   SQLCOM_GRANT,
 37   SQLCOM_CHANGE_DB,
 38   SQLCOM_CREATE_DB,
 39   SQLCOM_DROP_DB,
 40   SQLCOM_ALTER_DB,
 41   SQLCOM_REPAIR,
 42   SQLCOM_REPLACE,  //覆盖数
 43   SQLCOM_REPLACE_SELECT,
 44   SQLCOM_CREATE_FUNCTION,
 45   SQLCOM_DROP_FUNCTION,
 46   SQLCOM_REVOKE,
 47   SQLCOM_OPTIMIZE,
 48   SQLCOM_CHECK,
 49   SQLCOM_ASSIGN_TO_KEYCACHE,
 50   SQLCOM_PRELOAD_KEYS,
 51   SQLCOM_FLUSH,
 52   SQLCOM_KILL,
 53   SQLCOM_ANALYZE,
 54   SQLCOM_ROLLBACK,
 55   SQLCOM_ROLLBACK_TO_SAVEPOINT,
 56   SQLCOM_COMMIT,
 57   SQLCOM_SAVEPOINT,
 58   SQLCOM_RELEASE_SAVEPOINT,
 59   SQLCOM_SLAVE_START,
 60   SQLCOM_SLAVE_STOP,
 61   SQLCOM_START_GROUP_REPLICATION,
 62   SQLCOM_STOP_GROUP_REPLICATION,
 63   SQLCOM_BEGIN,
 64   SQLCOM_CHANGE_MASTER,
 65   SQLCOM_CHANGE_REPLICATION_FILTER,
 66   SQLCOM_RENAME_TABLE,
 67   SQLCOM_RESET,
 68   SQLCOM_PURGE,
 69   SQLCOM_PURGE_BEFORE,
 70   SQLCOM_SHOW_BINLOGS,
 71   SQLCOM_SHOW_OPEN_TABLES,
 72   SQLCOM_HA_OPEN,
 73   SQLCOM_HA_CLOSE,
 74   SQLCOM_HA_READ,
 75   SQLCOM_SHOW_SLAVE_HOSTS,
 76   SQLCOM_DELETE_MULTI,
 77   SQLCOM_UPDATE_MULTI,
 78   SQLCOM_SHOW_BINLOG_EVENTS,
 79   SQLCOM_DO,
 80   SQLCOM_SHOW_WARNS,
 81   SQLCOM_EMPTY_QUERY,
 82   SQLCOM_SHOW_ERRORS,
 83   SQLCOM_SHOW_STORAGE_ENGINES,
 84   SQLCOM_SHOW_PRIVILEGES,
 85   SQLCOM_HELP,
 86   SQLCOM_CREATE_USER,
 87   SQLCOM_DROP_USER,
 88   SQLCOM_RENAME_USER,
 89   SQLCOM_REVOKE_ALL,
 90   SQLCOM_CHECKSUM,
 91   SQLCOM_CREATE_PROCEDURE,
 92   SQLCOM_CREATE_SPFUNCTION,
 93   SQLCOM_CALL,
 94   SQLCOM_DROP_PROCEDURE,
 95   SQLCOM_ALTER_PROCEDURE,
 96   SQLCOM_ALTER_FUNCTION,
 97   SQLCOM_SHOW_CREATE_PROC,
 98   SQLCOM_SHOW_CREATE_FUNC,
 99   SQLCOM_SHOW_STATUS_PROC,
100   SQLCOM_SHOW_STATUS_FUNC,
101   SQLCOM_PREPARE,
102   SQLCOM_EXECUTE,
103   SQLCOM_DEALLOCATE_PREPARE,
104   SQLCOM_CREATE_VIEW,
105   SQLCOM_DROP_VIEW,
106   SQLCOM_CREATE_TRIGGER,
107   SQLCOM_DROP_TRIGGER,
108   SQLCOM_XA_START,
109   SQLCOM_XA_END,
110   SQLCOM_XA_PREPARE,
111   SQLCOM_XA_COMMIT,
112   SQLCOM_XA_ROLLBACK,
113   SQLCOM_XA_RECOVER,
114   SQLCOM_SHOW_PROC_CODE,
115   SQLCOM_SHOW_FUNC_CODE,
116   SQLCOM_ALTER_TABLESPACE,
117   SQLCOM_INSTALL_PLUGIN,
118   SQLCOM_UNINSTALL_PLUGIN,
119   SQLCOM_BINLOG_BASE64_EVENT,
120   SQLCOM_SHOW_PLUGINS,
121   SQLCOM_CREATE_SERVER,
122   SQLCOM_DROP_SERVER,
123   SQLCOM_ALTER_SERVER,
124   SQLCOM_CREATE_EVENT,
125   SQLCOM_ALTER_EVENT,
126   SQLCOM_DROP_EVENT,
127   SQLCOM_SHOW_CREATE_EVENT,
128   SQLCOM_SHOW_EVENTS,
129   SQLCOM_SHOW_CREATE_TRIGGER,
130   SQLCOM_ALTER_DB_UPGRADE,
131   SQLCOM_SHOW_PROFILE,
132   SQLCOM_SHOW_PROFILES,
133   SQLCOM_SIGNAL,
134   SQLCOM_RESIGNAL,
135   SQLCOM_SHOW_RELAYLOG_EVENTS,
136   SQLCOM_GET_DIAGNOSTICS,
137   SQLCOM_ALTER_USER,
138   SQLCOM_EXPLAIN_OTHER,
139   SQLCOM_SHOW_CREATE_USER,
140   SQLCOM_SHUTDOWN,
141   SQLCOM_ALTER_INSTANCE,
142   /* This should be the last !!! */
143   SQLCOM_END
144 };

View Code

(1)global_status_var

那是三个大局的数据类型,他只在被询问,重新初始化线程时从各类线程计算。

(2)线程中的status_var

那是每一个线程中全局的数据类型,它在做各类操作时被实时更新。

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:Server依期自动抓取耗费时间SQL并归档数据发邮件

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。