MYSQL数据库

---

前言

作为一个数据相关工作者，在数据处理的整个链路中，往往MYSQL数据库是作为数据来源之一，对其的学习有利于后续数据清洗、数据监控、数据应用，而且对于设计数据产品也是一个非常好的学习资料。因此整理了以下MYSQL数据库，也希望可以与各位共同学习。

一、MYSQL简介

mysql是一个关系型数据库，在业务系统中常作为数据交互的介质。其拥有开源和付费两个版本，一般在数据体量不大的情况下，会采用MYSQL数据库，其性能较佳，若是数据量较大，可以考虑采用oracle。

二、事务

事务是一组操作的集合，事务会将所有的操作作为一个整体一起向系统提交或撤销操作请求，这些操作要么同时成功要么同时失败。由于事务的并发，会产生数据一致性问题，主要通过锁的手段来解决。

三、视图&存储过程

视图和存储过程都是包装SQL语句的一种方式。视图主要是通过单sql语句进行信息过滤，而存储过程包含了类似其他语言的各种操作手段，包括循环、条件、函数。

3.1视图

视图是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。视图只保存了查询SQL逻辑，不保存查询结构。

视图可以互相引用，且提供了检查选项来选择检查的方式。CASCADED和LOCAL选择，CASCADED检查依赖的所有视图是否满足条件，LOCAL只检查对应的依赖视图的条件。比如视图V4依赖V3，V3依赖V2，视图V4加了CASCADED限制，则需要检查V3和V2的条件，若是视图V4加了LOCAL限制，则只需要检查V3条件。

视图可以进行更新，但是更新有条件，就是必须粒度和基础表一样。如果不一样，则更新报错，不可更新包括但不限于聚合函数或者窗口函数（sum\min\max\count）、disitnct、groupby、having、union。

视图的优先包括简单、安全、数据独立。

• 简单：视图可以简化用户对数据理解和操作。将常用的查询定义为视图，减少操作时间。
• 安全：数据库可以授权，但不能授权到数据库制定行和列上。通过视图用户只能查询和修改他们所见到的数据
• 数据独立：视图可帮助用户屏蔽真实表结构带来的影响

3.2存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务间的传输，对于提高数据处理的效率是有好处的。存储函数是有入参和返回值的存储过程，参数类型只能为INT类型。

四、存储引擎&innoDB引擎

4.1存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库，所以存储引擎也可被称为表类型。

在mysql中，有多种存储引擎，目前较多的是采用InnoDB：

• InnoDB：MySQL默认的存储引擎，支撑事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，该存储引擎比较合适

• myisam：应用以读和插入为主，很少的更新和删除操作，对事务的完整性、并发性要求不高。

• memory：所有数据保存在内存中，访问速度块，通常用于临时表及缓冲。

4.2 InnoDB引擎

主要分为缓冲结构In-Mermory Structures和磁盘结构On-Disk Structures。

4.2.1缓冲结构

缓冲结构分为缓冲池、更改缓冲池、日志缓冲区，且有一个自适应hash索引来加快查询速度。

自适应hash引擎主要用于优化缓冲池数据的查询，如果观察到hash索引可以提升速度，会建立hash索引，加快查询。（hash的查询不能范围查询）

缓冲池，实际操作中，会先查询缓冲池的数据，若是数据存在，直接操作，若是有数据没有，则从磁盘中加载。缓冲的数据更新到磁盘的频率可以设置，按次或者按时间都可以，若是并发比较高，可以设置固定时间。缓冲池的存在是为了降低对磁盘IP，加快处理速度，但是缓冲池一般可以存储的空间相对较少，因此一般主要存储操作比较频繁的数据。缓冲池以页为单位，分为”空闲页，未被使用“、”使用页，数据没有被修改“、”脏页，被使用且数据被修改“。

更改缓冲池，针对非唯一的二级索引页，若是这些索引查询非在缓冲池中，也不会直接去操作磁盘，而是将数据变更在更改缓冲池，后续再将数据更新到缓冲池中。因为非唯一的二级索引页，其插入相对比较随机，其删除和更新对索引页的影响比较大，直接操作磁盘，会产生大量磁盘IO。因此单独有一块内存进行保存操作，后续通过一定的策略合并到缓冲池中。因此非唯一的二级索引页产生的IO较大，而且频率较低，重要程度相对较弱，直接在缓冲池或者磁盘，会影响其他事务。

日志缓冲区，保存要写入磁盘的日志数据。内存之间的交互一般是采用日志文件，易解析。

4.2.2磁盘结构

• 系统表空间： InnoDB 数据字、双写缓冲、写缓冲和回滚日志的存储位置，如果关闭独立表空间，它也将存储所有表数据和索引。在磁盘上，Change Buffer是system tablespace（系统表空间）的一部分，当数据库宕机时，索引的变更会被缓冲到磁盘的Change Buffer区域

• 独立表空间：InnoDB 会为每个数据库单独创建子文件夹，数据库文件夹内为每个数据表单独建立一个表空间文件 table.ibd 用于存放每个表的数据、索引和插入缓冲 Bitmap 页，同时创建一个 table.frm 文件用于保存表结构信息。其他类型的信息，如回滚信息、插入缓冲索引页、系统事务信息、二次写缓冲等仍存放于系统表空间内

• 通用表空间：通用表空间存在的目的是为了在系统表空间与独立表空间之间作出平衡。系统表空间与独立表空间中的表可以向通用表空间移动、
  

• undo表空间：用于存放一个或多个 undo log 文件。默认undo log 文件是存储在系统表空间中，若是自定义写在重洗日志空间，则系统表空间不再存储

• 临时表空间：用于独立保存临时表数据及其回滚信息

• 双写缓冲区：InnoDB在数据页从缓冲池刷新到磁盘前，先将数据也写入双写缓冲区文件中。 如果在页面写入过程中发生操作系统，存储子系统或mysqld进程崩溃，则InnoDB可以在崩溃恢复期间从doublewrite缓冲区中找到该页面的良好副本

• 重做日志：用来实现事务的持久性。该日志文件由两个部分组成：重做日志缓冲和重做日志文件。前者是在内存中，后者是在磁盘中。当事务提交之后会把所有修改信息都存到该日志中，用于在刷新脏页到磁盘时，发送错误时，进行数据恢复使用。

五、索引&日志&触发器

索引是为了加快查询速度，并提供优化SQL的路径之一。日志和触发器都是为了记录数据的变化，提高数据的抗风险能力且提供数据变更的可查询方法。

5.1 索引

索引是帮助mysql高效获取数据的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这些数据结构就是索引。

• Hash：哈希索引是采用一定的hash算法，将键值换算成新的hash指，映射到对应的槽位上，然后存储在hash表中

  • hash索引只能用于对等比较（=，in），不支持范围查询（between，<>）
  • 无法利用索引完成排序操作
  • 查询效率高，通常只需要一次检索就可以，效率通常高于B+Tree索引

• B+Tree：MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

  • 相对于二叉树，层级更少，搜索效率高
  • 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，导致一页中存储的键值减少，指针跟着减少，同样保存大量数据，只能增加树的高度，导致性能降低。
    

索引根据查询方式和存储方式都可以进行分类。

查询方式分为主键索引、唯一索引、常规索引、全文索引。

• 主键索引：针对表中主键创建的索引，只能有一个
• 唯一索引：避免同一个表中某数据列中的值重复，可以有多个
• 常规索引：快速定位特定数据，可以有多个
• 全文索引：查找的是文本中的关键词，而不是比较索引中的值，可以有多个

存储方式分为聚集索引和二级索引，二级索引的值是聚集索引的主键id。

• 聚集索引：将数据存储和索引放到了一起，索引结构的叶子节点保存了行数据。一般情况下其索引是主键id
• 二级索引：将数据与索引分开储存，索引结构的叶子节点关联的是对应的主键，若是需要获取行整条数据，需要通过聚集索引，也就是回表查询，其查询效率相当于两次索引查询。

索引的建立是为了提高查询速度，其中mysql提供了一些sql查询的性能语句，包括增删改查的查询频次、慢查询日志、show profiles时间消耗、explain查询索引的引用。

索引设计原则：

• a. 针对数据量较大，且查询比较频繁的表建立索引
• b. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
• c. 尽量选择区分度高的列作为索引，尽量建议唯一索引，区分度越高，使用索引的效率越高
• d.如果是字符串类型的字段，字段的长度越长，可以针对于字段的特点，建立前缀索引，一般采用区分度最好的TOPn字符长度，其索引效率高占用空间小
• e. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时间可以覆盖所有，节省存储空间，避免回表，提高查询效率
• f. 控制所有的数量，索引非多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
• g. 如果索引列不能存储NULL值，建表时使用NOT null约束它，当优化器知道每列是否包含NULL值时，可以更好确定哪个索引最有效由于查询

5.2 日志

一共有四种日志可以提供日常的问题查找和使用优化：错误日志、二进制日志、查询日志、慢查询日志。

• 查询日志：记录了客户端所有操作语句，二进制日志不包含查询数据的SQL语句。默认情况下，查询日志时未开启状态，需要进行设置

• 二进制日志：记录了DML操作所有的记录
  

• 错误日志：记录了mysqlid启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息，当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

• 慢查询日志：慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有SQL语句日志，默认未开启。long_query_time默认10秒，可以精确到微秒。

5.3 触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器这种特性可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作。使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的，且只支撑行级数据级触发，也就是会记录发生变化的每行数据的前后数据情况。

• INSERT型触发器：NEW表示将要或者已经新增的数据
• UPDATA型触发器：OLD表示修改之前的数据，NEW表示将要或已经修改后的数据
• DELETE型触发器：OLD表示将要或者已经删除的数据

六、主从复制&分库分表&锁

为了保障数据库的修改和查询性能，一般可以采用以下三步。

1. 第一步：采用分布式缓冲redis、memcached等降低对数据库的读操作。
2. 第二步：如果缓冲使用过后，数据库访问量还是非常大，可以考虑数据库读、写分离原则。
3. 第三步：当我们使用读写分离、缓冲后，数据库的压力还是很大的时候，这就需要使用到数据库拆分了。

6.1 主从复制

主从复制主要是提高抗风险能力，使数据迁移的时候可以在从库中进行，避免业务受到主库影响，在主库失败的情况的可以进行第二主库的使用，避免业务停滞。

主从复制：建立两个服务器且设置命名相同的数据库，设置一个数据库是主库，一个是从库。主库是DML操作，而从库只能查询。主库的DML操作都会写入binlog的二进制日志，然后通过IOthread进程写入到从库的Relay Log二进制日志表中，SQLthread进程会从Relay Log日志中，将变更的信息更新到从库数据库中。

双主双从：建立四个服务器且设置命名相同的数据库。设置两个主库两个从库。设置M1作为第一主库，若是M1失败了，将启动M2作为主库。当M1作为主库的时候，可以进行DML操作，其他是查询。当M2作为主库的时候，可以进行DML操作，S2进行查询操作。数据库允许进行的操作类型，可以通过参数表进行设置。

6.2分库分表

分库分表是为了降低IO的压力，提高查询和修改性能。方法分为垂直拆分和水平拆分。在数据库层面一般采用的是垂直拆分，通过业务场景进行表的分类，分散到不同的数据库里面。在表层面，一般采用水平拆分，拆分方式主要通过查询的场景来进行。

垂直拆分：数据库的垂直拆分，主要是将对应数据库，通过业务分类，将所有表拆分到几个数据库里面，不同数据库的表不一致。若是有部分表是常用的且多个场景使用的，设置为全局表，这样可以在不同数据库生成一样的表。数据表的垂直拆分，主要也是根据使用场景不同，将表拆分成多个数据库表，表的字段都不一样，合并去重是全量字段。

水平拆分：数据库的水平拆分，主要是将对应数据库，以字段为依据，将数据库拆分成多个数据库。数据表的水平拆分，主要也是根据使用场景不同，将表通过字段和策略拆分成多个数据库表，表的字段都一样，数据合并是全量数据。

6.3 锁

锁是计算机协调多个事务并发导致的资源抢夺和数据一致性异常的手段。锁的粒度越大，影响范围越广，性能越差但是数据一致性越好。

锁分为全局锁、表级锁、行锁。全局锁，指的是当有一个事务操作的时候，整个数据库不能再并行操作，需要进行排队，典型的场景是数据备份。表级锁和行锁的含义和全局锁相似，只是范围不一样，分表是表和行粒度。

表锁有针对数据本身的表锁、元数据锁、意向锁：

• 表锁分为表共享读锁和表独占写锁。读锁指的是本身事务进行读操作的时候，本身业务不可以进行写，其他事务可以读但也不可以写。写锁本身事务可以进行读和写操作，但是其他事务不可以进行读和写的操作。
• 元数据锁，也就是当表有业务进行时，不可以进行DDL操作。
• 意向锁：当有业务进行数据操作的时候，若是有行锁，则对表进行意向锁

行锁时通过索引项来实现的，而不是针对记录。主要分为三类：

• 行锁：锁定每个行记录的锁，防止其他事务对此行进行update和delete。
• 间隙锁：索引索引记录间隙（不含该记录），确保所有记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。主要是在索引查询的时候，对应的条件没有索引，将临键锁改为间隙锁。
• 临键锁：行锁和间隙锁组合。一般情况下，在RR隔离级别下都使用临键锁，防止幻读。 -

后言

除了以上内容之外，对于数据工作者来说，数据库的管理、表的设计、数据的增删改查都是必要技能，特别是对于数据应用的伙伴来说，SQL的查询和优化是非常重要，这一模块后续会进行补充。