RDS MySql

第四讲、RDS MySql

RDS是[关系型数据库]务（Relational Database Service）的简称，是一种即开即用、稳定可靠、可弹性伸缩的在线数据库服务。具有多重安全[防护措施]和完善的[性能监控]体系，并提供专业的[数据库备份]、恢复及[优化方案]，使您能专注于应用开发和业务发展。

一、选型

1、版本选择

RDS MySQL 5.5 / 5.6 因MySQL 5.5 [已停止更新维护]，MySQL 5.6即将于2024年02月05日停止更新维护，阿里云不负责后续的内核更新维护，仅提供产品服务技术支持，建议购买5.7及以上版本。
RDS MySQL 5.7 目前最新的稳定版本，较5.6版本相比，增加了AliSQL很多自研功能，例如[Statement Outline]、[Statement Queue]、[Fast Query Cache]、[Performance Insight]等，以及多项性能改进，目前也是使用用户最多的版本。
RDS MySQL 8.0 目前也趋于稳定，较之前的版本相比，提供了更丰富的自研功能，例如[Native Flashback]、[Returning]、[Binlog in Redo]、[Recycle Bin]等，以及多项性能改进,目前大多数有漏洞审查的企业大都选择了8.0。

2、架构选择

[基础版]：只有一个节点可用，不存在高可用性，如果测试环境或个人使用，可给予成本的考虑使用此版本。
[Serverless 版本]：弹性伸缩实例，其规格随业务需求量随时调整，如果基于成本控制的开发环境，可以考虑此版本。
[高可用版]：经典场景，该版本有一个备节点随时作为Standby可以进行切换，缺点是对客不可见，保证数据库的高可用性，如果需要读写分离，Master实例下面可以挂最多不超过10个只读节点。
集群版：计算和存储的分离设计，可实现failover到switchover的动作，业务量大时建议用集群版代替高可用版，同时可启用MGR保障实例的RPO=0。

如果是生产环境，强烈建议使用高可用版或集群版，防止单点故障，对业务造成长时间影响。

二、自动安装

1.掌握setup安装方式

2.掌握手动配置方式

3.理解目录结构

MySQL版本说明

MySQL 版本分为四种：Alpha版、Beta版、RC版(Release Candidate)、GA版(Generally Available)。

1. Alpha版 Alpha版软件，这是软件工程对软件开发过程软件版本定义使用的版本说明。Alpha是内部测试版,一般不向外部发布,会有很多Bug.除非你也是测试人员,否则不建议使用.是希腊字母的第一位,表示最初级的版本，alpha 就是α。

2. Beta版

Beta版软件，这也是软件工程中对软件开发测试版本控制的版本说明。Beta一般是Alpha后面的版本。该版本相对于α版已有了很大的改进，消除了严重的错误，但还是存在着一缺陷，需要经过多次测试来进一步消除。这个阶段的版本会一直加入新的功能。beta 就是β。

3. RC版

RC版，RC即Release Candidate的简写。这是Beta后面的版本，一般RC版并没有新增功能，而是修复了一些反馈的Beta中存在的BUG。所以RC版更接近最终发行版即稳定版(GA版)

4. GA版

GA版，GA即Generally Available的简写。这就是软件最终的发行版。这个版本一般BUG相对较少。这个发行版也可以叫稳定版。

Release版

Release版，在有些软件存在，在MySQL中一般没有这个版本。该版本意味“最终版本”，在前面版本的一系列测试版之后，终归会有一个正式版本，是最终交付用户使用的一个版本。该版本有时也称为标准版。一般情况下，Release不会以单词形式出现在软件封面上，取而代之的是符号(R)。

根据应用场景划分版本

1. MySQL Community Server

MySQL Community Server是社区版本，开源免费，但不提供官方技术支持。遵循GPL协议。MySQL Community Server也是我们通常用的MySQL的版本。根据不同的操作系统平台细分为多个版本。

2. MySQL Enterprise Edition

MySQL Enterprise Edition企业版本，需付费，可以试用30天。

3. MySQL Cluster

MySQL Cluster集群版，开源免费。可将几个MySQL Server封装成一个Server。MySQL Cluster CGE 高级集群版，需付费。

4. MySQL Workbench（GUI TOOL）

MySQL Workbench（GUI TOOL）一款专为MySQL设计的ER/数据库建模工具。它是著名的数据库设计工具DBDesigner4的继任者。

MySQL Workbench又分为两个版本：

①、社区版（MySQL Workbench OSS）

②、商用版（MySQL Workbench SE）

选择版本

首先选择社区版的GA版(稳定版)
选择发行时间6-10个月以上的GA版
选择最近几个月没有修复重大BUG的版本，软件工程原理修复了较大BUG则说明还隐含较多的BUG
最好向后较长时间没有更新的发行版
考虑开发人员开发程序使用的版本是否兼容选择的版本
选择的版本最好是内部运行3-6个月，然后在不重要的非核心业务运行3-6个月
向DBA大佬请教

下载MySQL

在下载页面 https://dev.mysql.com/downloads/mysql/ 中最下面找到 MySQL Community Server 的下载地选项

1565857798120

选择操作系统，然后选择最下面的下载版本。这里直接选择 Windows (x86, 64-bit), ZIP Archive 版本。

注意：演示安装程序时使用的是： mysql-installer-web-community 。这种程序是一个安装向导，安装过程需要联网下载 MySQL

安装 MySQL 服务器

1. 同意许可

1565914364015

2. 选择安装内容

1565914431458

这里只选择安装 MySQL 服务器，然后点击 Next

3. 选择独立MySQL服务器

1565914701237

4. 配置

这一步使用默认即可，注意 Port 端口号为：3306。在以后访问 MySQL 服务器的时候是需要这个端口号，因此端口号绝对不要记错。

图中默认是将网络访问许可添加到防火墙规则的，请不要取消

1565914963484

因为客户端还没有全部支持 8 版本的验证方式，所以我们选择 MySQL 5.X 的验证方式

1565916548245

1565915080681

输入管理员账号 root 的密码

1565915164831

这里会检查密码强度，这里因为是做演示使用了弱密码

安装 MySQL 服务并配置启动方式

1565915276022

从图上可以看到，默认的服务名是 MySQL80 ，随着系统开机自动启动。在这里可修改这2个选项。但是需要注意的是，服务名在启动服务的时候是需要的。

应用配置并启动服务

1565915359915

如果全部都是绿色对勾，就说明配置正常应用并且正常启动 MySQL 服务器了

使用自带客户端连接服务器

MySQL 服务器自带了 CLI 客户端，其中一个使用 Unicode 编码进行连接，可以防止出现乱码

1565916872280

启动任意一个客户端，在提示下输入密码

1565916918306

密码验证通过后，即可使用 MySQL

1565916952682

查看 MySQL 服务器的版本

在提示符下，输入 SQL:

select version(); --这个SQL是用于查看安装的MySQL服务器版本

1565917105633

安装环境变量

环境变量的设置是为了让我们在任意位置，通过 cmd 可以直接访问 mysql.exe程序。如果没有这个需求就不用配置了。

1. 打开环境变量面板

1565919001768

1565919176888

点击左边高级系统设置

1565919246981

点击环境变量

1565919273735

选择下面系统变量中的 Path，双击或者点击编辑按钮

1565919317098

在最下面添加 MySQL 服务器的安装路径，然后在最后加上 bin 文件夹。因为可执行程序都是存放在这里的。

配置完成后，点击确定按钮。然后重新打开一个 cmd 程序，输入 mysql ，如果有提示就证明配置正确

1565919444230

MySQL 图形客户端

自带的客户端在使用上有着诸多不便，我们需要有图形的客户端。但是 MySQL 服务器并没有自带图形客户端。我们使用的图形客户端大多是收费软件，根据需要自行选择即可。

Navicat for MySQL（看名字只能连接MySQL）
Navicat premium（可以连接各种数据库）
DataGrip （功能强大，但是安装包体积也大，有300多M，需要JRE才能运行）
SQLyong
phpMyAdmin ( 网页版，需要有PHP环境 )
workbench

.......

这里选择的是 Navicat premium ，这是一款收费软件。默认可以全功能试用 30 天。

安装过程略.... ，一路下一步即可

打开 Navicat，建立一个连接

1565917821244

从图上可以看到，这款程序支持很多数据库，这里我们选择 MySQL

1565917892944

连接名：这个名字是让使用者知道是连接哪个数据库的
主机：localhost 和 IP: 127.0.0.1 是等价的，这2个写法都是默认连接本机的服务器。如果要连接其他服务器，输入目标的 IP 地址即可
端口：在安装的时候，默认是 3306 ，这里就不用改。如果配置的时候改变了端口号，这里就需要输入修改后的值
用户名和密码：可以使用管理员账号root，或者是安装时创建的其他账号。

输入完成后，可以点击左侧连接测试按钮，查看连接是否成功。没有问题的话，点击确定，设置就会进行保存。

数据库编码问题

MySQL支持多种不同的编码，我们常用的是UTF8和UTF8MB4，如果是已经存在的数据库，根据数据库编码存储格式即可，如果是新创建的数据库，还是建议使用UTF8MB4。

UTF8支持长度3的汉字，但是不支持长度4的汉字，是不完全的UTF8解决方案。MySQL团队并没有对其进行修改，而是使用UTF8MB4支持了完整的UTF8。

MySQL常用的连接参数

连接参数

参数名称	参数说明	缺省值	最低版本要求
user	数据库用户名（用于连接数据库）
password	用户密码（用于连接数据库）
useUnicode	是否使用Unicode字符集，如果参数characterEncoding设置为gb2312 或 utf8 ，本参数值必须设置为true	false	1.1g
characterEncoding	当useUnicode设置为true时，指定字符编码。比如可设置为gb2312 或 utf8mb4	false	1.1g
autoReconnect	当数据库连接异常中断时，是否自动重新连接？	false	1.1
autoReconnectForPools	是否使用针对数据库连接池的重连策略	false	3.1.3
failOverReadOnly	自动重连成功后，连接是否设置为只读？	true	3.0.12
maxReconnects	autoReconnect设置为true时，重试连接的次数	3	1.1
initialTimeout	autoReconnect设置为true时，两次重连之间的时间间隔，单位：秒	2	1.1
connectTimeout	和数据库服务器建立socket连接时的超时，单位：毫秒。 0表示永不超时，适用于JDK 1.4及更高版本		3.0.1
socketTimeout	socket操作（读写）超时，单位：毫秒。 0表示永不超时		3.0.1
allowMultiQueries	mysql驱动开启批量执行sql的开关	false
allowMultiQueries	mysql驱动开启批量执行sql的开关	false
serverTimezone	mysql8增加的时区属性，开发时必须指定

常用的：

useUnicode=true

characterEncoding=utf8 / characterEncoding=utf8mb4 如果是新创建的数据库，请用 utf8mb4 编码

serverTimezone=Asia/Shanghai 开发的时候指定上海时区，连接MySQL8必填参数

三、配置安装

2.1 点击下载压缩包

解压文件，进入\mysql-8.0.31-winx64 文件夹中解压完全后的目录

MySQL安装目录中包括启动文件、配置文件、数据库文件和命令文件，具体如下。

bin目录：存放一些客户端程序和可执行脚本。

data目录：存放一些日志文件以及数据库。

docs目录：存储一些版本信息。

include目录：存放一些头文件。

lib目录：存放一些库文件。

share目录：存放错误消息文件、字符集等。

2.2 创建my.ini

my.ini是MySQL数据库中使用的配置文件，修改这个文件可以达到更新配置的目的。

my.ini存放路径

windows （win7 以后）

1569803752984

my.ini 文件配置说明

[mysqld]
; 设置3306端口
port=3306
; 设置mysql的安装目录
basedir="C:/Program Files/MySQL/MySQL Server 8.0/"
; 设置mysql数据库的数据的存放目录
datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data
; 允许最大连接数
max_connections=200
; 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
; 服务端使用的字符集默认为UTF8
character-set-server=utf8
; 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
; 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
; 设置mysql客户端默认字符集
default-character-set=utf8
[client]
; 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

2.3以管理员身份打开cmd

-- 在MySQL安装目录的 bin 目录下执行命令；记录密码
mysqld --initialize --console
-- 命令执行后,去services.msc查看服务
mysqld --install

2.4 启动服务

-- 启动服务
net start mysql   # 或鼠标右键启动
-- 停止服务
net stop mysql

2.5 登录服务器

mysql -uroot -p
Enter password:******    #此处输入密码

2.6 修改初始密码

-- 可以先试下show databases;命令有问题；WITH mysql_native_password by前不加
>ALTER USER 'root'@'localhost' IDENTIFIED  BY ‘password';
-- 刷新权限
>FLUSH PRIVILEGES;

2.7 开启 root 远程访问

打开 mysql 数据库的连接，然后执行下面的sql

把root的host字段设置成 %，表示所有ip都可以连接

-- 创建用户
create user root@'%' identified by '123456';
-- 下面是修改方式
# update user set host='%' where user='root';

刷新权限
```
flush privileges;
```

四、运维规范

4.1 硬件规范

CPU：选择多核心处理器，以提高MySQL的并发处理能力。常见的选择有Intel Core i7或Xeon系列。
内存：选择至少NGB的内存，以确保MySQL有足够的空间来处理大量的查询。如果预计数据库的负载较高，可以考虑增加内存容量。
存储：选择固态硬盘（SSD），以提高MySQL的读写性能和数据可靠性。SSD相比传统的机械硬盘具有更快的读写速度和更低的故障率。
网络：选择高速网络连接，以确保数据库的快速响应。常见的选择有千兆以太网接口。

4.2 表运维规范

基于阿里云其他相关产品和工具结合使用的基础上，建议大家为每张表都创建主键，没有明确的业务主键属性的话，可以创建额外非业务属性的主键列。目前阿里云也提供了隐式主键功能，该功能在新版架构中已默认开启，但存量实例表需要变更才能使用，保证row模式Binlog可以快速应用，避免主备/从延迟。
自增ID的使用情况，需要根据业务情况使用，需要注意的是，数据库重启或者变配操作，可能会导致自增ID重置归零，需要重新。
阿里云对单表的大小和数据量和社区版MySQL一样，受限于OS，为了保证良好的性能优势，建议控制单表数据量在 2000w 条以内，大小 10GB 以内。如果由于表数量大约超过 1w 而出现性能下降时，大概率是因为 InnoDB 操作数据文件导致的，可以增加table_open_cache 和 table_definition_cache 两个参数的大小来优化下。需要注意的是，单库表数量不建议超过60w，否则无法进行数据备份，且超过5w无法进行单库表恢复。
RDS MySQL 不支持 MyISAM 引擎和 Memory 引擎，若有 Memory 引擎的需求建议使用云数据库 Redis、Tair。

4.3、安全运维规范

建议业务使用内网地址访问，白名单/安全组小范围开放。
禁止使用弱密码，建议使用强密码定期更换，提升数据库实例安全性。
按需授予业务用户相关最小化权限，一般只需要 select/DML 相关库级别权限即可。
账号的使用时，建议较专业的DBA在控制台维护高权限账号，业务和其他账号使用命令行方式维护和配置，只允许特定 IP 或 IP 段业务访问。这样配置的原因是RDS控制台配置的账号都是user@%权限，允许IP访问范围太大，因此建议账号分离。
如果内网访问时，建议ECS客户端机器和RDS实例位于同一账号/地域/VPC下，跨VPC或VPC-经典网络不通，目前RDS MySQL不建议使用经典网络以及IPV6网段的网络了。
考虑到 RDS MySQL 的稳定和安全性，RDS MySQL限制了 super、shutdown、file 权限，有时在数据库中执行 set global xxx时，会报错：#1227-Access denied;you need(at least one of)the SUPER privilege (s) for this operation.
解决：可以在 RDS 控制台的实例管理页的数据库管理 > 参数设置功能完成参数修改。

五、SQL实践

5.1 sql分类

关系型数据库操作数据需要使用 SQL 语言（结构化查询语言 Structured Query Language）。SQL 语言主要分为

名称	作用
数据定义语言（Data Definition Language，DDL）	用于创建、修改和删除数据库对象，如CREATE TABLE、ALTER TABLE、DROP TABLE等。DDL语句会自动提交事务
数据操纵语言（Data Manipulation Language，DML	用于操纵数据库，包括INSERT、UPDATE、DELETE、Select等;
数据控制语言（Data query Language，DQL）	用于执行SELECT查询操作
数据控制语言（Data Control Language，DCL）	用于执行授予权限和撤销权限的操作，包括GRANT（授予权限）、REVOKE（撤销权限）两条命令。DCL语句会自动提交事务
事务控制语言（Transactional Control Language，TCL）	用于维护数据的一致性，包括COMMIT（提交事务）、ROLLBACK（回滚事务）和SAVEPOINT（设置保存点）3条语句

#查看版本号，上面是在cmd下可以，mysql的bin目录下就可以。
c:>mysql -V
c:>mysql --version
	mysql  Ver 8.0.31 for Win64 on x86_64 (MySQL Community Server - GPL)
-- 下面是进入到mysql环境
c:>mysql> select version();
	+-----------+
	| version() |
	+-----------+
	| 8.0.31    |
	+-----------+
#进入到mysql;
#-h:表示的主机，后面跟主机的ip地址;-u:表示登录用户；-p:表示密码
	1.mysql -uroot -p,输入密码;
	2.mysql -uroot -proot  ，直接把密码放在p后面;
	3.mysql -h127.0.0.1 -uroot -proot
#查看数据库
  show databases;
#创建数据库
	create database 数据库名;
#删除数据库
    drop database 数据库名;
#退出 
  exit
  或
  quit

5.2 SQL操作

5.3 服务器

查看当前连接情况
select count(*), db from information_schema.PROCESSLIST group by db;
查看MySQL的最大连接数
show variables like '%max_connections%';
查看MySQL服务器响应的最大连接数
show global status like 'Max_used_connections';
 show engines;   -- 查看引擎
show variables  like  '%storage_engine%' ;  -- 数据库默认引擎

5.4 导入导出

六、优化

在进行MySQL的优化之前，必须要了解的就是MySQL的查询过程，很多查询优化工作实际上就是遵循一些原则，让MySQL的优化器能够按照预想的合理方式运行而已。

注：优化有风险，涉足需谨慎

优化可能带来的问题？

优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统；
优化手段本来就有很大的风险，只不过你没能力意识到和预见到；
任何的技术可以解决一个问题，但必然存在带来一个问题的风险；
对于优化来说解决问题而带来的问题，控制在可接受的范围内才是有成果；
保持现状或出现更差的情况都是失败！

优化的需求？

稳定性和业务可持续性，通常比性能更重要；
优化不可避免涉及到变更，变更就有风险；
优化使性能变好，维持和变差是等概率事件；
切记优化，应该是各部门协同，共同参与的工作，任何单一部门都不能对数据库进行优化！

所以优化工作，是由业务需要驱使的！

优化什么？

在数据库优化上有两个主要方面：即安全与性能。

安全->数据可持续性；
性能->数据的高性能访问。

优化的范围有哪些？

存储、主机和操作系统方面：

主机架构稳定性；
I/O规划及配置；
Swap交换分区；
OS内核参数和网络问题。

应用程序方面：

应用程序稳定性；
SQL语句性能；
串行访问资源；
性能欠佳会话管理；
这个应用适不适合用MySQL。

数据库优化方面：

内存；
数据库结构（物理&逻辑）；
实例配置。

不管是设计系统、定位问题还是优化，都可以按照这个顺序执行。

优化维度

数据库优化维度有四个：

硬件、系统配置、数据库表结构、SQL及索引。

优化选择：

优化成本：硬件>系统配置>数据库表结构>SQL及索引。
优化效果：硬件<系统配置<数据库表结构<SQL及索引。

优化思路

**定位问题点吮吸：**硬件-->系统-->应用-->数据库-->架构（高可用、读写分离、分库分表）。

**处理方向：**明确优化目标、性能和安全的折中、防患未然。

硬件优化

主机方面：

根据数据库类型，主机CPU选择、内存容量选择、磁盘选择：

平衡内存和磁盘资源；
随机的I/O和顺序的I/O；
主机 RAID卡的BBU（Battery Backup Unit）关闭。

CPU的选择：

CPU的两个关键因素：核数、主频

根据不同的业务类型进行选择：

CPU密集型：计算比较多，OLTP - 主频很高的cpu、核数还要多
IO密集型：查询比较，OLAP - 核数要多，主频不一定高的

内存的选择：

OLAP类型数据库，需要更多内存，和数据获取量级有关。

OLTP类型数据一般内存是Cpu核心数量的2倍到4倍，没有最佳实践。

存储方面：

根据存储数据种类的不同，选择不同的存储设备；
配置合理的RAID级别（raid5、raid10、热备盘）；
对与操作系统来讲，不需要太特殊的选择，最好做好冗余（raid1）（ssd、sas、sata）。
raid卡：

主机raid卡选择：
- 实现操作系统磁盘的冗余（raid1）；
- 平衡内存和磁盘资源；
- 随机的I/O和顺序的I/O；
- 主机raid卡的BBU（Battery Backup Unit）要关闭。

网络设备方面

使用流量支持更高的网络设备（交换机、路由器、网线、网卡、HBA卡）

注意：以上这些规划应该在初始设计系统时就应该考虑好。

服务器硬件优化

自带管理设备：远程控制卡（FENCE设备：ipmi ilo idarc）、开关机、硬件监控。
第三方的监控软件、设备（snmp、agent）对物理设施进行监控。
存储设备：自带的监控平台。EMC2（hp收购了）、日立（hds）、IBM低端OEM hds、高端存储是自己技术，华为存储。

系统优化

CPU：

基本不需要调整，在硬件选择方面下功夫即可。

内存：

基本不需要调整，在硬件选择方面下功夫即可。

SWAP：

MySQL尽量避免使用swap。

阿里云的服务器中默认swap为0。

IO ：

raid、no lvm、ext4或xfs、ssd、IO调度策略。

Swap调整(不使用swap分区)

/proc/sys/vm/swappiness的内容改成0（临时），/etc/sysctl. conf上添加vm.swappiness=0（永久）

这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。

当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。

数据库优化

按方向分类：

SQL优化方向：执行计划、索引、SQL改写。
架构优化方向：高可用架构、高性能架构、分库分表。

按照分类

设计：存储引擎，字段类型，范式与逆范式
功能：索引，缓存，分区分表。
架构：主从复制，读写分离，负载均衡。
合理SQL：测试，经验。

数据库参数优化

调整

实例整体（高级优化，扩展）：

thread_concurrency：# 并发线程数量个数
sort_buffer_size：# 排序缓存
read_buffer_size：# 顺序读取缓存
read_rnd_buffer_size：# 随机读取缓存
key_buffer_size：# 索引缓存
thread_cache_size：# (1G—>8, 2G—>16, 3G—>32, >3G—>64)

连接层（基础优化）

设置合理的连接客户和连接方式：

max_connections # 最大连接数，看交易笔数设置
max_connect_errors # 最大错误连接数，能大则大
connect_timeout # 连接超时
max_user_connections # 最大用户连接数
skip-name-resolve # 跳过域名解析
wait_timeout # 等待超时
back_log # 可以在堆栈中的连接数量

SQL层（基础优化）

query_cache_size：查询缓存 >>> OLAP类型数据库,需要重点加大此内存缓存，但是一般不会超过GB。

对于经常被修改的数据，缓存会立马失效。

我们可以实用内存数据库（redis、memecache），替代他的功能。

存储引擎层（innodb基础优化参数）

default-storage-engine
innodb_buffer_pool_size # 没有固定大小，50%测试值，看看情况再微调。但是尽量设置不要超过物理内存70%
innodb_file_per_table=(1,0)
innodb_flush_log_at_trx_commit=(0,1,2) # 1是最安全的，0是性能最高，2折中
binlog_sync
Innodb_flush_method=(O_DIRECT, fdatasync)
innodb_log_buffer_size # 100M以下
innodb_log_file_size # 100M 以下
innodb_log_files_in_group # 5个成员以下,一般2-3个够用（iblogfile0-N）
innodb_max_dirty_pages_pct # 达到百分之75的时候刷写内存脏页到磁盘。
log_bin
max_binlog_cache_size # 可以不设置
max_binlog_size # 可以不设置
innodb_additional_mem_pool_size #小于2G内存的机器，推荐值是20M。32G内存以上100M

SQL优化

1. 选取最适用的字段属性

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。

另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。

对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

2. 使用连接（JOIN）来代替子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：

DELETE  FROM  customerinfo WHERE  CustomerID  NOT in (SELECT customerid FROM salesinfo)

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）..替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT  *  FROM  customerinfo WHERE  customerid  NOT IN (SELECT customerid FROM salesinfo)

**如果使用连接（JOIN）..来完成这个查询工作，速度将会快很多。**尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：

SELECT  *  FROM  customerinfo LEFT JOIN salesinfo ON customerinfo.customerid  = salesinfo.customerid WHERE  salesinfo.customerid   IS NULL

连接（JOIN）..之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

3. 使用联合(UNION)来代替手动创建的临时表

MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。

SELECT  name,phone  FROM  client UNION
SELECT  name,birthdate  FROM  author  UNION
SELECT  name,supplier FROM product

4. 事务

尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。

在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

BEGIN;
  INSERT   INTO   salesinfo   SET   customerid=14;
  UPDATE   inventory   SET   quantity =11   WHERE   item='book';
COMMIT;

事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。

5. 锁定表

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。

其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

LOCK TABLE inventory WRITE SELECT quantity  FROM   inventory   WHERE Item='book';
...
UPDATE   inventory   SET   Quantity=11   WHERE  Item='book';UNLOCKTABLES

这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其它的访问来对inventory进行插入、更新或者删除的操作。

6. 使用外键

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的customerid映射到salesinfo表中customerid，任何一条没有合法customerid的记录都不会被更新或插入到salesinfo中。

CREATE  TABLE   customerinfo( customerid   int primary key) engine = innodb;

CREATE  TABLE   salesinfo( salesid int not null,customerid  int not null, primary key(customerid,salesid),foreign key(customerid)  references  customerinfo(customerid) on delete cascade)engine = innodb;

注意例子中的参数「on delete cascade」。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATE TABLE语句中加上engine=INNODB。如例中所示。

7. 使用索引

索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。

那该对哪些字段建立索引呢？

一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况

例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。

8. 优化的查询语句

绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

下面是应该注意的几个方面。

首先，最好是在相同类型的字段间进行比较的操作

在MySQL3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。

其次，在建有索引的字段上尽量不要使用函数进行操作

例如，在一个DATE类型的字段上使用YEAE()函数时，将会使索引不能发挥应有的作用。所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。

第三，在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的

例如下面的查询将会比较表中的每一条记录。

SELECT  *  FROM  books  WHERE  name  like   "MySQL%"

但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：

SELECT  *  FROM  books  WHERE  name >=  "MySQL"  and  name  <"MySQM"

最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。

标签：RDS,数据库,MySQL,使用,查询,版本,mysql,MySql
From： https://blog.51cto.com/teayear/12090826