阿里云数据库

很多阿里云ECS用户都会考虑是使用ECS自建数据库还是使用阿里云的RDS云数据库，如何选择？有必要使用云数据库吗？阿里云百科网告诉你使用阿里云RDS云数据库的理由：

RDS的高可用性

RDS提供了主备双节点的实例，双节点可以在同一地域的不同可用区，MySQL实例的双节点还可以在不同地域，当主实例出现故障时可快速切换到备实例，保障了RDS的稳定性。

RDS的高安全性

RDS在数据的存取上加入了中间层，所有请求都会经过中间层，而且有SQL注入的请求都会被中间层拦截掉。在底层数据写入上，RDS采用了最高安全级别的写入，保证在主机异常掉电的情况下数据不会出现丢失。以此来保障数据库的高安全性。

RDS运维优化

用户无需考虑数据库运维成本，企业无需配备专业的DBA数据库运维，阿里云提供监控、告警、备份、恢复、调优手段，RDS源码团队持续对MySQL进行源码优化，在标准的基准测试中性能和稳定性上都是高于社区版本的。

MySQL读写分离

读写分离适用于对数据库有少量写请求，但有大量读请求的应用场景下，阿里云MySQL 5.6高可用版或金融版可以开通读写分离功能，RDS用户无需为读写分离技术买单。

综上，阿里云RDS的优势在于为用户提供高可用性、高安全性的云数据库服务，用户不用操心数据库的运维、安全以及调优问题，企业将数据库问题交给阿里云专业的RDS团队，告别技术壁垒，花少量钱解决更多问题。

摘要：在阿里云SQL Server最佳实践系列在线直播中，阿里云数据库专家汪建明总结了7大问题并结合案例为大家分享了阿里云SQL Server高CPU使用率问题排查的实践经验。

本期直播回顾链接：http://yq.aliyun/webinar/play/237 更多系列精彩课程直播，尽在 云数据库经典案例和最佳实践专场，等待你的参与！

以下内容均根据演讲视频以及PPT整理而成。

演讲者简介

汪建明（花名：风移），近10年SQL Server数据库DBA经验。曾就职于新蛋中国6年、新蛋美国3年半。现任阿里云数据库专家，负责SQL Server产品线。

分享议程

本文将按照SQL Server高CPU使用率问题排查的7个方面进行分享：

缺失索引 (Missing Indexes)索引碎片 (Indexes Fragmentation)数据类型转换 (Data Conversion)非SARG查询 (Non-SARG Query)参数嗅探 (Parameter Sniffing)统计信息 (Statistics)TOP CPU查询 (TOP SQL)

一、缺失索引 (Missing Indexes)

为什么索引缺失会降低SQL Server的CPU使用率？

真正排查出的高CPU使用率的第一大因素就是Missing Indexes，那么为什么索引的缺失是SQL Server CPU使用率的第一大杀手呢？要回答这个问题就需要首先回答什么是索引。索引的结构其实是基表的某一列或者某几列数据的投影，并且这些列的数据是按照升序或者降序排列完毕之后的特殊结构，这个特殊结构使得查询的性能会更加高效，特别是对于经常会使用到的查询语句。既然索引特殊的结构已经排序完成了，那么在进行检索的时候效率就会非常高，可以很快地定位到数据所在的位置，这样就能够大大降低SQL Server本身的IO的消耗，IO的消耗降低之后CPU的使用率自然也会下降。

发现缺失索引的方法

如何发现哪些表中又缺失了哪些索引呢？第一种方法就是DTA (Database Tuning Advisor)。第二种方法就是执行计划中存在索引缺失的警告，也就是当执行某一条语句的时候，执行计划会报出一个警告提示这里缺少一个索引，这个时候就可以将缺失的索引找出来并创建它。第三种方法就是访问系统的动态视图，大致有sys.dm_db_missing_index_group_stats、sys.dm_db_missing_index_group_stats以及sys.dm_db_missing_index_details这三个视图，具体怎样去使用大家可以查阅帮助文档。

不要盲目地创建缺失的索引

在创建缺失索引时一定不要盲目，一定要确保创建的缺失索引是有效的，这样做的第一个原因是创建索引会导致一定的存储开销，因为索引的数据结构也会占用数据文件空间。第二个原因是DML操作会导致索引的维护成本增加，因为索引的结构是基于表的某列或者某几列组合出来的数据结构，这个数据结构的一致性一定是随着基表的数据变化而变化的，当我们进行Delete、Insert以及Update操作的时候也需要去维护索引的数据结构，因为需要保证索引结构数据与基表数据的一致性，所以就会带来索引维护成本的上升。

这部分的相关Demo请参考云栖社区的博客：https://yq.aliyun/articles/72265

二、索引碎片 (Indexes Fragmentation)

刚才提到了索引缺失会导致CPU使用率的升高，而另外一个问题是：是不是索引创建以后CPU的使用率就一定会降低呢？或者是说在索引不缺失的情况下，CPU的使用率就一定不会上升呢？这两个问题的答案都是否定的。这里涉及的话题就是索引碎片，这里的索引碎片可以理解为索引数据页中的一些空隙，这应该如何理解呢？假如某一个页里面是满的，比如是8K，如果存在25%的空隙，那么真正有效的数据只有75%，举个简单的例子比如某个表格的索引数据有100个页，但是碎片率是25%，所以这100个换页面里面只有75个页面的数据是有效的。所以在索引的碎片率非常高的情况下，索引的效率就会非常低，因为其IO的使用率也会非常低。

Rebuild Indexes

解决索引碎片的方法其实很简单，也就是进行一个Rebuild Indexes的操作，做完这个操作之后统计信息会被更新，相应的执行计划中的缓存信息也会被清空，当相同的语句再过来的时候，SQL Server就会重新进行执行计划的评估和选择，并获得更好的执行计划。

注意事项

Rebuild Indexes操作的方式能够很容易地解决索引碎片问题，但是还是存在三个地方需要大家注意。因为做Rebuild Indexes操作的时候会导致数据日志文件的增长，那么基于SQL Server日志文件的技术比如Database mirroring、Log shipping以及alwayson等，这些基于日志的技术都会导致进程变得很慢，因为日志文件会在短时间内出现暴涨的情况，所以这里需要提醒大家注意这个问题，在后面也会分享如何解决这些问题。

如何去做Rebuild Indexes

我们所需要基于的原则是一定在100%的需要时才去做Rebuild Indexes，那些使用率比较低的，哪怕是碎片率很高的表也不会太过于关注，比如一些很小的表或者是heap的表，对于很小的表而言，SQL Server在做执行计划的时候发现表格很小则会走Table Scan而不是Index Seek或者Index Scan的操作。第二个原则是在Rebuild Indexes的时候一定要去对每一个索引级别进行索引碎片率的检查，而不要盲目对整个表级Rebuild Index。第三个原则是当发现索引的碎片率处于不同的级别的时候选择的处理方法也是不一样的，如果碎片率在10%以下，那么就不需要去做Rebuild Indexes操作，如果索引碎片率在10%到30%之间，应该选择做reorganize操作，当索引碎片率大于30%，可以做Rebuild Indexes操作。这里还请主要，使用SQL Server的版本，如果是企业版本，请选择ONLINE＝ON选项，以较小Rebuild Index对应用程序对影响。

还有一点需要提醒大家的就是在做Rebuild Indexes操作的时候一定要选择在业务的低峰期，因为Rebuild Indexes是一个IO密集型的操作，所以会非常消耗IO。除此之外，当存在Database mirror或者Log shipping以及alwayson的时候，如何做Rebuild Indexes才能够使影响最小呢？这里使用的技术是table partition，可以在大表上面建立table partition，然后逐个partition去做Rebuild Indexes，因为每个partition都会对于数据进行切分，切分之后数据量就会变得更小，这样产生的影响也会变得更小。

这部分的相关Demo请参考云栖社区的博客：https://yq.aliyun/articles/72348

三、数据类型隐式转换 (Data Conversion Implicitly)

很多同学不了解数据类型的转换，特别是数据类型的隐式转换。在这里和大家简单分享一下。

什么是数据类型隐式转换

SQL Server在做数据类型比较的时候一定要确保比较运算符两端的数据类型是一致的，比如等于、On子句、或者大于等于以及小于等于，一定要确保比较运算符两端的数据类型是一致的，这样才能进行比较。如果数据类型不一致的话，SQL Server会自动地进行数据类型的隐式转换，这个隐式转换对于用户而言是比较隐蔽的，用户可能是毫无感知的，但是数据库系统却在背后做了大量的隐式转换工作，而且这些工作都是比较会消耗系统性能的，从而导致了高CPU的使用率。

数据类型转换原则

SQL Server数据类型转换的规则其实很简单，就是将数据类型从低优先级转向高优先级，比如char和int数据类型，int的数据类型的优先级要比char高的，那么在做这两者数据类型的比较的时候就需要转换char数据类型到int数据类型上面来，然后再进行比较。

这里需要提醒大家的是一旦SQL Server对数据类型进行了隐式转换，并且隐式转换发生在正式表的基表上面的时候，是无法进入Index Seek的，而是会使用性能非常差的Index Scan，这样就会使得SQL Server的IO使用率大大升高，IO使用率的增加导致CPU的使用率升高，这就是隐式数据转换导致高CPU使用率的理论基础。

那么如何避免数据类型的隐式转换呢？

第一个方法就是Review表的数据类型设计，因为在反范式理论中有一个方法是在同一个字段表达同一个含义为了避免多表join的时候采用反范式的设计，在多个表中存储相同含有的字段，在这种情况下一定要保证这些字段的数据类型是一致的，在后面进行查询或者是执行on子句进行join的时候，SQL Server就不需要在后台进行数据类型的隐式转换工作了。

第二个方法就是当where语句里面使用了像“Where column = 常量”这种传入参数的时候，一定要确保传入的参数的数据类型和基表中这个字段的数据类型是一致的，这样才不会导致数据类型的隐式转换。其实经常会遇到的问题就是用户传入的参数的数据类型比基表字段的数据类型的优先级更高，这时SQL Server就需要在后台自动转换基表字段的数据类型，如果基表有一亿条数据，那么SQL Server就需要对这一亿条数据的列数据类型进行转换并进行比较，这样对于IO的消耗会非常大，进而会导致CPU使用率的上升。

第三个方法就是去检查执行计划，在执行计划中可以通过一个CONVERT_IMPLICT关键字知道是否做了XML隐式数据类型转换。第四个方法是搜索执行计划的缓存，可以拿到缓存的XML文件，在XML文件中会有隐式数据类型转换的关键字。

以上就是由于隐式数据类型转换导致高CPU使用率的场景，这样的场景也是非常多见的，这部分的相关Demo请参考云栖社区的博客：https://yq.aliyun/articles/72420

四、非SARG查询 (Non-SARG Query)

原因

Non SARG是什么呢？其实是由于即使基表的某些列上建立了索引，SQL Sever的查询优化器也必须要去扫描所有的行，这样就会导致了Non SARG查询。

通常情况

上面讲的可能比较理论，通常情况下，是在where字句中，在数据库基表的字段上使用函数，比如像Convert、Cast、以及数据类型隐式转换等，对于时间进行操作的函数，比如取时间差Datediff、对时间进行加减的Dateadd以及取年的Year，以及Upper、Lower大小写转化的函数，对字符串进行操作的Rtrim、Substring、Left以及像Like的完全模糊匹配、Isnull函数以及用户自定义函数等。

大家在写SQL的where等语句对于基表进行函数运算的时候一定要注意这里面存在一个Non SARG查询会导致CPU使用率的上升。这部分的相关Demo请参考云栖社区的博客：https://yq.aliyun/articles/72482

五、参数嗅探 (Parameter Sniffing)

SQL Server中的参数嗅探是什么？

参数嗅探其实是一个非常有意思的话题，那什么是参数嗅探呢？归根结底，导致参数嗅探的原因是由于SQL Server对执行计划的编译和缓存的过程导致的。想要理解这句话就明白SQL Server是如何执行一条查询语句的，当SQL Server的服务端接收到一条SQL语句之后，首先要进行语法检查，之后进行语义分析，再之后进行编译，选择最优的执行计划路径，并将所得到的结果缓存到执行计划缓存中。

参数嗅探的问题所在

而问题就出在编译的过程中该如何编译这些查询语句上，因为某些查询语句是有参数的，当编译的时候一定是根据当时传入的参数的值编译一个最好的执行计划，但是当随着时间的推移，数据发生了变更就可能导致统计信息发生变化甚至可能发生数据倾斜的情况，如果发生了这样的情况，那么之前缓存的执行计划就可能不是最优的了，因为之前传入的参数可能是另外一个值，对应的统计信息可能就不是最优的解法了，这就是导致参数嗅探的一个原因。

参数嗅探的解决方案

刚才谈到了导致参数嗅探的原因是执行计划的编译和缓存过程，那么如何解决这个问题呢？很简单的一个思路就是：既然之前缓存的执行计划不是最优的，那么就清空这个缓存。这里为大家提供几种方法，但是其中也有不太推荐的方法。

1.第一种就是重启整个操作系统，因为操作系统重启了，内存当然就清空了，那么执行计划的缓存也会被清空，这时候SQL Server启动起来之后，查询语句提交到SQL Server服务端，当然会重新编译、使用最新的执行计划，这样可以解决参数嗅探的问题，但是问题在于这样的做法动作太大了，有点像使用大炮打蚊子，所以这样做思路是对的，但是方法却不恰当，所以这一种是不太推荐大家使用的。

2.第二种方法就是重启SQL Server服务，其实这样也可以解决问题，这个方法会导致SQL Server短暂停机和不可服务。这样的做法比第一种稍微好一点，但是也不是推荐的方法。

3.第三种方法是使用DBCC FREEPROCCACHE命令来清空执行计划的缓存，这种方法比第二种方法又稍微好一点，但是这样还是会清空所有执行计划的缓存，但仍旧不是最好的方法，这样有点像是“宁可错杀一千，也不放过一个”的思维模式，因为真正出现问题就是某一个或者某几个执行计划的缓存，如果把所有的执行计划都清空了是可以解决这样的问题，但是也会产生“错杀其他的999人”的问题，所以这也不是最好的解决方法。

4.最好的解决方法就是针对于特定的查询语句或者存储过程去清空特定的执行计划缓存。

5.另外一种是使用Query Hits Option，这将会告诉SQL Server在执行存储过程或者查询语句时每次都会进行重新编译，而不进行缓存，这也是一种思路。

6.还有一种就是更新统计信息，这个方法的原因是执行计划的编译和最优路径的选择基础数据就是统计信息，那么将统计信息更新之后相应的查询语句的执行计划缓存会被清空，下一次执行的时候会重新进行编译通过最新的统计信息获取最新的执行计划。

7.最后一个方法就是刚才提到的创建缺失索引或者删除不必要的、多余的以及重复的索引。

以上这些都是解决参数嗅探的方法，这部分的相关Demo请参考�...

前言：

最近，因为安全部门的需要，需要对之前的数据进行查询。怎么办？有三种办法

第一种办法：找到备份文件，然后选择覆盖性恢复。（若你不需要现在的数据，那么此方法可行）

第二种办法：保留现在的数据，那么选择数据实例克隆，把备份的数据克隆到新实例上面去。（注意，这里是新实例，新实例就意味着要给钱，有钱的话可以选择这种方法）

第三种办法：把数据库下载下来，然后进行恢复到自己本地建立的数据库。（此方法可行，节约成本）。下面，我就给大家介绍，我在数据库恢复的道路上所踩过的坑，一个坑接着一个坑。希望大家不要再走我的路了。

详解如下：

1、下载RDS数据库备份文件。下载之前先确认数据库备份文件是走内网地址还是外网地址。

PS:若网络速度快，可以选择外网。我选择的内网，因为我是把RDS的数据恢复至ECS服务器上。内网速度可以达到30M/S

这里需要注意：

服务器的内外网IP需要添加到数据库的白名单当中去。

使用命令wget 下载文件

wget -c '' -O .tar.gz

第2条命令中数据备份文件内网或外网下载地址需要用单引号给引起来，后面的O是大写，注意是大写。

2、下载备份还原工具Percona XtraBackup 2.2.9，不建议使用高版本。

最好按照Percona XtraBackup官方的操作手册来

官网提供了yum安装方式和RPM安装方式。我使用的是YUM安装

下载Percona XtraBackup的yum资源文件。

执行资源文件安装：yum install http://percona/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

执行安装：rpm -ivh percona-release-0.1-3.noarch.rpm

执行安装 yum -y install percona-xtrabackup-22

3、安装阿里云官方rds_backup_extract.sh文件

这里需要去阿里云官方下载，下载完毕后上传即可：

PS：上传后记得把文件的权限改为可执行即可

4、YUM方式安装MySQL

由于centos 7上没有MYSQL的资源文件，因此，我们首先得安装资源文件。

wget http://repo.mysql/mysql-community-release-el7-5.noarch.rpm

rpm -ivh mysql-community-release-el7-5.noarch.rpm

执行安装命令 yum -y install mysql-server

安装完毕后设置密码

依次输入以下命令：

mysql -uroot (由于没有设置密码，因此这里可以不用输入密码)

切换数据库

use mysql;

更改密码

update user set password=password('您的新密码') where user ='root';

上面命令执行完成后，那么MYSQL就基本完成了，最后，我们设置root用户可以从任何地址访问

具体命令为：grant all privileges on *.* to root@'%'identified by '您的密码'

5、恢复RDS文件

到上面第一点的文件目录下执行以下命令：

./rds_backup_extract.sh -f .tar.gz -C /home/mysql/data

执行以下命令查看情况:

ls -l /home/mysql/data

命令执行成功后，系统会返回如下结果，其中蓝色字体为生成备份文件时RDS实例所包含的数据库。

恢复解压后的数据库备份文件

innobackupex --defaults-file=/home/mysql/data/backup-myf --apply-log /home/mysql/data

PS：在执行上面命令之前，先修改backup-my.conf文件，按照以下规则进行注释：

[mysqld]

innodb_checksum_algorithm=innodb

# innodb_log_checksum_algorithm=innodb

innodb_data_file_path=ibdata1:200M:autoextend

innodb_log_files_in_group=2

innodb_log_file_size=1572864000

# innodb_fast_checksum=false

innodb_page_size=16384

# innodb_log_block_size=512

innodb_undo_directory=.

innodb_undo_tablespaces=0

# rds_encrypt_data=false

# innodb_encrypt_algorithm=aes_128_ecb

若出现以下结果，代表数据恢复成功：

恢复成功，别激动，后面还有。

6、修改备份文件权限：

执行命令：chown -R mysql:mysql /home/mysql/data

7、启动MYSQL进程

mysqld_safe --defaults-file=/home/mysql/data/backup-myf --user=mysql --datadir=/home/mysql/data &

8、连接进入mysql -uroot

9、恢复完成后，表mysql.user中不包含在RDS实例中创建的用户，需要新建。在新建用户前，执行如下命令。

delete from mysql.db where user’root’ and char_length(user)>0;

delete from mysql.tables_priv where user’root’ and char_length(user)>0;flush privileges;

10、注意，由于备份文件是之前的用户，想要root用户有权限，那么需要执行以下命令对数据文件进行修复。

mysql_upgrade mysql -uroot

11、到此，基本就全部成功了。