MySQL

• 一、数据库的设计、备份与还原
• • 1.1 数据库的设计
  • • 1.1.1 多表之间的关系
    • 1.1.2 数据库设计的范式
  • 1.2 数据库的备份与还原
• 二、MySQL优化
• • 2.1 优化什么
  • 2.2 优化的范围
  • 2.3 优化维度
  • 2.4 硬件优化
  • 2.5 系统配置优化
  • 2.6 数据库优化
  • 2.6 SQL优化
  • • 2.6.1 选取最适用的字段属性
    • 2.6.2 使用连接（JOIN）来代替子查询(Sub-Queries)
    • 2.6.3 使用联合(UNION)来代替手动创建的临时表
    • 2.6.4 事务
    • 2.6.5 锁定表
    • 2.6.6 使用外键
    • 2.6.7 使用索引
    • 2.6.8 优化的查询语句

一、数据库的设计、备份与还原

1.1 数据库的设计

1.1.1 多表之间的关系

• 分类

  • 1.一对一(了解)：
    • 如：人和身份证
    • 分析：一个人只有一个身份证，一个身份证只能对应一个人
  • 2.一对多(多对一)：
    • 如：部门和员工
    • 分析：一个部门有多个员工，一个员工只能对应一个部门
  • 3.多对多：
    • 如：学生和课程
    • 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

• 实现关系

  • 1.一对多(多对一)：
    • 如：部门和员工
    • 实现方式：在多的一方建立外键，指向一的一方的主键。
  • 2.多对多：
    • 如：学生和课程
    • 实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
  • 3.一对一(了解)：
    • 如：人和身份证
    • 实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。

1.1.2 数据库设计的范式

• 概念

  设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求
  设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
  目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

• 分类

• 第一范式（1NF）：每一列都是不可分割的原子数据项
• 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）
  • 几个概念：
    • 1.函数依赖：A–>B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
      例如：学号–>姓名。 （学号，课程名称） --> 分数
    • 2.完全函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
      例如：（学号，课程名称） --> 分数
    • 3.部分函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
      例如：（学号，课程名称） – > 姓名
    • 4.传递函数依赖：A–>B, B – >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A
      例如：学号–>系名，系名–>系主任
    • 5.码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
      例如：该表中码为：（学号，课程名称）
      * 主属性：码属性组中的所有属性
      * 非主属性：除过码属性组的属性
• 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

1.2 数据库的备份与还原

• 语法：
  • 备份： mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
  • 还原：
    1. 登录数据库
    2. 创建数据库
    3. 使用数据库
    4. 执行文件。source 文件路径

二、MySQL优化

2.1 优化什么

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

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

2.2 优化的范围

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

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

应用程序方面：

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

数据库优化方面：

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

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

2.3 优化维度

数据库优化维度有四个： 硬件、系统配置、数据库表结构、SQL及索引。

• 优化选择
  • 优化成本：硬件>系统配置>数据库表结构>SQL及索引。
  • 优化效果：硬件<系统配置<数据库表结构<SQL及索引。
• 优化思路
  • 定位问题点吮吸： 硬件–>系统–>应用–>数据库–>架构（高可用、读写分离、分库分表）。
  • 处理方向： 明确优化目标、性能和安全的折中、防患未然。

2.4 硬件优化

主机方面：

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

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

CPU的选择：

• CPU的两个关键因素：核数、主频
• 根据不同的业务类型进行选择：

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

内存的选择：

• OLAP类型数据库，需要更多内存，和数据获取量级有关。
• OLTP类型数据一般内存是Cpu核心数量的2倍到4倍，没有最佳实践。

存储方面：

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

主机raid卡选择：

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

网络设备方面：

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

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

• 服务器硬件优化

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

2.5 系统配置优化

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。

2.6 数据库优化

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

• 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

2.6 SQL优化

2.6.1 选取最适用的字段属性

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

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

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

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

2.6.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不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

2.6.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

2.6.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;

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

2.6.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进行插入、更新或者删除的操作。

2.6.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。如例中所示。

2.6.7 使用索引

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

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

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

2.6.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进行自动类型转换，因为转换过程也会使索引变得不起作用。

每日一点点进步
不进则退