在调优MySQL数据库和监控数据库时,很多朋友不知道如何下手,怎么来确定是不是参数设置的合理,下面给出一些如何计算指标,来确定数据库参数是否设置合理,希望给大家一些方法,去做MySQL数据库优化,最好对照一下方法,看看自己的生产库配置是否有问题。

创建于2016-12-24 16:12:19

 

 


Preface

在show global status;查看各个值。

 

 

 

存储过程

    概念:

       1.存储过程是在数据库管理系统中保存的、预先编译的、能实现某种功能的SQL程序,它是数据库应用中运用比较广泛的

        一种数据对象。

       2.存储过程是SQL语句和控制语句的预编译集合,保存在数据库里可由应用程序调用执行,而且允许用户声明变量、逻辑

          控制语句及其他强大的编程功能。

       3.存储过程可包含逻辑控制语句和数据操作语句,它可以接收参数、输出参数、返回单个或多个结果集及返回值。

       4.存储过程可以只包含一条select语句,也可以包含一系列使用控制流的SQL语句。存储过程可以包含个别或全部的控制

        流语句。

 

    特点:

       1.模块化程序设计:

          只需创建一次存储过程并将其存储在数据库中,以后即可在程序中反复调用该存储过程。

       2.执行速度快,效率高:

          如果某操作需要大量的T-SQL代码或需要重复执行,则存储过程将比T-SQL批处理代码的执行速度更快。因为存储

          过程只在创建的时候编译一次,而批处理代码每次执行的时候都需要编译。

       3.减少网络流量:

          使用存储过程后,一个需要数百行T-SQL代码的操作,由一条执行过程代码的单独语句即可实现,而不需要再网络

          中发送数百行代码。

       4.具有良好的安全性:

          即使对于没有直接执行存储过程中语句的权限的用户,也可授予他们执行该存储过程的权限。不同的用户使用不同

          的存储过程。

       5.存储过程分为两类:

          系统存储过程用户自定义的存储过程

 

    MySQL use
character set & collation to organize the different charater.It provides
a flexible way in setting individual character set on a database,a table
even on a single column of table.Each character set has a series of
collations with one default collation.We can generally see the character
set in MySQL as the combination of code page & character
encoding.

平均查看是否频繁打开表

系统存储过程

    概括:SQL
Server提供系统存储过程,它们是一组预编译的T-SQL语句。系统存储过程提供了管理数据库和更新表的机制,并

       充当从系统表中检索信息的快捷方式。

 

    常用的系统存储过程:

       SQL
Server的系统存储过程的名称以“sp_”开头,并存放在Resource数据库中。下面列出一些常用的系统存储过程↓

常用的系统存储过程 
系统存储过程  说明
sp_databases  列出服务器上的所有数据库信息,包括数据库名称和数据库大小
sp_ helpdb 报告有关指定数据库或所有数据库的信息 
sp_renamedb  更改数据库的名称 
sp_ tables 返回当前环境下可查询的表或视图的信息 
sp_ columns 返回某个表或视图的列信息,包括列的数据类型和长度等 
sp_help  查看某个数据库对象的信息,如列名、主键、约束、外键、索引等 
sp_helpconstraint  查看某个表的约束 
sp_helpindex  查看某个表的索引 
sp_stored_procedures  显示存储过程的列表 
sp_password  添加或修改登录账户的密码 
sp_helptext  显示默认值、未加密的存储过程、用户定义的存储过程、 触发器和视图的实际文本

 

    

 

 

 

 

 

 

 

 

 

 

    

    

    调用执行存储过程:

       语法:    exec[ute]
 [返回变量=]  存储过程名  

                      [@参数1=]参数值1  [output]  |
 [default],

 

                      ……,

 

                      
 [@参数n=]参数值n  [output]  |  [default]

 

       其中,exec是execute的简写

       如果执行存储过程的语句时批处理中的第一个语句,则可以省略execute关键字。

       如果有返回值的话,可以用一个变量接收。但是在存储过程内return只能返回数值类型

       output表明参数是输出参数,default表示参数的默认值。

       如果不按照参数顺序传递参数值,则要指定参数名。

       一旦某个参数按照”@参数名=参数值”格式传递数据,那么该参数之后的其他参数都必须以同样的格式传递参数

       值。

 

    常用的扩展存储过程:

       扩展存储过程是SQL
Server提供的各类系统存储过程中的一类,允许使用其他编程语言创建外部存储过程,为数据库用

       户提供从SQL
Server实例到外部程序的接口,以便进行各种维护活动。通常以”xp_”作为前缀,以DLL形式单独存在。

       语法:    exec
 xp_cmdshell  DOS命令  [no_output]

              其中,exec表示调用存储过程,no_output为可选参数,设置执行DOS命令后是否输出返回信息。

 

 

    In the
early version of MySQL(eg. version 5.5) ,latin1 is the default character
set which does not support Chinese characters.There’re some other
commonly used character set such as GBK,UTF-8.MySQL 5.7 chooses UTF-8 as
default character set nowadays in order to support more characters of
different languages.

Opened_tables/Uptime  = xx/s

用户自定义的存储过程

    组成:

       1.输入参数和输出参数。

       2.在存储过程中执行的T-SQL语句。

         3.存储过程的返回值。

 

    语法:

       1.创建语法:

             create
 proc[edure]  存储过程名

                [{@参数1
 数据类型}  [=默认值]  [output],

                ……,

                 {@参数n
 数据类型}  [=默认值]  [output]

                ]

             as

                SQL语句

       2.删除语法:

             drop
 proc[edure]  存储过程名

       3.判断存储过程是否存在,如果存在,则删除

             if
 exists(select  *  from sysobjects where name = 存储过程名)

                drop
 proc[edure]  存储过程名

             go

    语法释义:

       1.创建:

             proc是procedure的简写,参数的默认值可选,不写则没有默认值,调用执行时必须带有此参数。output

             表示是输出参数,即执行存储过程后把返回值存放在输出参数中。就像C#中的引用传递一样。输出参数也

             可以设置默认值。不写output则为输入参数,就像C#中的值传递一样。

             另外需注意,存储过程中参数的默认值不能是系统函数。比如参数是时间类型,默认值不能是

             GETDATE()

 

 

    Messy
code is a general issue about character set.It always occur in our MySQL
databases if we do not use character rules appropriately.Worse,it leads
to data loss in some cases what is really a big trouble we should
avoid.

299304/21062119= 0.0142/s 
 –每秒不到1次的打开表,说明都是在内存里缓存了,比较理想

处理错误信息

    概念:在存储过程中,可以使用print语句显式用户定义的错误信息,但这些是临时的,并不保存错误信息。使用raiserror语句

       可以指定严重级别并设置系统变量记录所发生的错误。

 

    raiserror语句:

       语法:

             raiserror
 ({msg_id  |  msg_str}  {,  severity,  state}  [with  option
[…n]])

       语法释义:

             msg_id:  在sysmessages系统表中指定的用户定义错误信息。

             msg_str: 
用户定义的特定信息,最长为255个字符。

             severity: 
 与特定信息关联,表示用户定义的严重性级别。用户可使用的级别为0~18级;19~25级是

                   
 为sysadmin固定角色的成员预留的,并且需要指定with
 log选项;20~25级被认为是致命

                     错误。

             state:   表示错误的状态,是1~255中的值。

             option:  
错误的自定义选项,可以是下列任一值

                     a. log:    在Microsoft SQL
Server数据库引擎实例的错误日志和应用程序日志中记

                            录错误。

                     b.
nowait:  将消息立即发送给客户端。

                     c. seterror: 
将@@error值和error_nummber值设置为msg_id或50000,不用考虑

                            严重级别。

 

 

 

Introduce

平均查看是否频繁打开文件

 

Opened_files/Uptime = xx/s

    MySQL
provides a lot of parameters to specify character set in various
dimentionality.We should know clearly about the principle and function
of each parameter to avert underlying messy code issue.Let’s see details
about it.

732146117/21062119= 34.7613/s     –比较频繁
,可能是如下参数比较小,缓存少导致

 

+——————-+——-+

Procedure

| Variable_name     | Value |

 

+——————-+——-+

Check
the character set supported by MySQL(version 5.7).

| innodb_open_files | 3000  |

 1 (root@localhost mysql3306.sock)[(none)]>show character set;
 2 +----------+---------------------------------+---------------------+--------+
 3 | Charset  | Description                     | Default collation   | Maxlen |
 4 +----------+---------------------------------+---------------------+--------+
 5 | big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
 6 | dec8     | DEC West European               | dec8_swedish_ci     |      1 |
 7 | cp850    | DOS West European               | cp850_general_ci    |      1 |
 8 | hp8      | HP West European                | hp8_english_ci      |      1 |
 9 | koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
10 | latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
11 | latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
12 | swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
13 | ascii    | US ASCII                        | ascii_general_ci    |      1 |
14 | ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
15 | sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
16 | hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
17 | tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
18 | euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
19 | koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
20 | gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
21 | greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
22 | cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
23 | gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
24 | latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
25 | armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
26 | utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
27 | ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
28 | cp866    | DOS Russian                     | cp866_general_ci    |      1 |
29 | keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
30 | macce    | Mac Central European            | macce_general_ci    |      1 |
31 | macroman | Mac West European               | macroman_general_ci |      1 |
32 | cp852    | DOS Central European            | cp852_general_ci    |      1 |
33 | latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
34 | utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
35 | cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
36 | utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
37 | utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
38 | cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
39 | cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
40 | utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
41 | binary   | Binary pseudo charset           | binary              |      1 |
42 | geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
43 | cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
44 | eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
45 | gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
46 +----------+---------------------------------+---------------------+--------+
47 41 rows in set (0.00 sec)
48 
49 //There're 41 results of the supported character set.
50 //Each character set has a default collation.
51 //Maxlen is the max bytes of corresponding character set(eg. utf8mb4 supports 4 bytes).

| open_files_limit  | 65535 |

 

+——————-+——-+

**Check
character set parameters of current MySQL server.**

 

 1 (root@localhost mysql3306.sock)[(none)]>show variables like 'character%';
 2 +--------------------------+----------------------------------------------------------------+
 3 | Variable_name            | Value                                                          |
 4 +--------------------------+----------------------------------------------------------------+
 5 | character_set_client     | utf8                                                           |
 6 | character_set_connection | utf8                                                           |
 7 | character_set_database   | utf8                                                           |
 8 | character_set_filesystem | binary                                                         |
 9 | character_set_results    | utf8                                                           |
10 | character_set_server     | utf8                                                           |
11 | character_set_system     | utf8                                                           |
12 | character_sets_dir       | /usr/local/mysql-5.7.21-linux-glibc2.12-x86_64/share/charsets/ |
13 +--------------------------+----------------------------------------------------------------+
14 8 rows in set (0.00 sec)
15 
16 //character_set_client:It's used by client when connect to servers for requesting data.
17 //character_set_connection:It's used for those literals not have a character set introducer for conversion. 
18 //character_set_database:It's used by default database.The value of "character_set_server" will be inherited if it is not specified.
19 //character_set_filesystem:It's used to interpret string literals refer to file names.
20 //character_set_results:It's used to return query results to the client.
21 //character_set_server:It's the default character set of server.
22 //character_set_system:It's used by server for storing identifiers,the value is utf8 forever.
23 //character_sets_dir:It's the directory where contains the xml files of installed character set.

平均查看是否频繁打开表结构

 

Opened_table_definitions/Uptime = xx/s

The relationship of  above
character set parameters shows below.

32631/21062119= 0.0015/s   
–每秒不到1次的打开表结构,说明都是在内存里缓存了,比较理想

图片 1

 

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注