系统地认识开源的MySQL数据库-免费源码丞旭猿

作为程序员，我们在项目中会使用到许多种类的数据库，根据业务类型、并发量和数据要求等选择不同类型的数据库，比如MySQL、Oracle、SQLServer、SQLite、MongoDB和Redis等。今天我们就来系统的介绍一下这其中的关系型数据库MySQL，这也是在很多开源系统中使用比较多的，因为其体积小、速度快、并且开源等的特征吸引很多开发人员的青睐。不管是自用还是商用，一些业务不太复杂的系统使用MySQL都是一个不错的选择。

接下来我们主要通过编写SQL语句的方式来讲解SQL的一些知识点，首先我们创建两张表，用户表（user）和岗位表（post），设计几个简单的字段，后续的SQL语句都是基于这两张表。以下是这两张表的结构。

1、基础概念

我们先来了解一些MySQL中常涉及的一些基础概念。

1.1、MySQL存储引擎

1.1.1、什么是存储引擎

MySQL中的数据是用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎。存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式。

1.1.2、MySQL有哪些存储引擎

MySQL的存储引擎包括： InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE 和 FEDERATED等。其中 MylSAM 和 lnnoDB 是比较常用的两种。

MyISAM的主要特点是拥有较高的插入，查询速度，但不支持事务，支持表锁（即使操作一条记录也会锁住整个表，不适合高并发的操作）；而InnoDB引擎提供对数据库事务的支持，并且还提供了行级锁和外键的约束（操作时只锁某一行，不对其它行有影响，适合高并发的操作），InnoDB也是MySQL5.5版本后默认数据库存储引擎。

我们用一张图来对比一下两者的区别。

1.1.3、怎么查询当前数据的存储引擎

可以使用数据库管理工具客户端查看（表类型会显示），也可以使用查询语句进行查询。使用show engines 查询当前数据库支持的存储引擎，使用show variables like %storage_engine% 查询l数据库默认的存储引擎。前面也说到了，MySQL5.5之前的默认存储引擎是MyISAM，MySQL5.5版本后默认数据库存储引擎改为了InnoDB【我们可以通过select version() 查询mysql的版本】

1.2、MySQL事务

1.2.1、什么是事务？

事务就是一组独立不可分割的工作单元，事务中的操作要么全部执行成功，要么全部执行失败，没有其他的中间状态。具体地说，就是在一个事务中执行多个操作时，要么所有的事务都被提交（commit），那么这些修改就永久保存下来；要么数据库放弃所做的所有修改，整个事务回滚（rollback）到最初状态。

1.2.2、事务的四大特性

事务的四个特性分别是：原子性、一致性、隔离性和持久性，即所谓的ACID。

• 原子性：将事务看作是不可再分的原子，一旦开始事务，要么全部执行，要么全部取消，是不存在中间状态的。即把一个事务看成一个整体，牵一发而动全身。

• 一致性：在事务开始之前和之后，数据从一个合法性状态变换到另一个合法性状态，数据库的完整性约束没有被破坏。例如买早餐，顾客付了10块钱。老板收到了10块钱（这里不考虑手续费的问题），顾客和老板加起来的总额不会变化，不多也不少。

• 隔离性：每个读写事务对其他事务的操作对象能相互分离。例如事务T1和事务T2的操作不能相互干扰。

• 持久性：事务一旦提交，它对数据库中数据的改变就是永久性，接下来的其他操作和数据库故障不应该对其有任何影响。持久性是通过事务日志来保证的。日志包括了重做日志和回滚日志。

1.2.3、事务的实现

在介绍存储引擎的时候，也说到了InnoDB是支持事务的，那么接下来我们就以InnoDB为例来说明。

数据库通常借助日志来实现事务，常见的有undo log 和 redo log，这两种都能保证事务特性，undo log实现事务原子性，redo log实现事务的持久性。

1.2.3.1、redo log(重做日志)

每当操作时，在磁盘数据变更之前，将操作写入redo log，这样当系统崩溃重启后可以继续执行。

（1）redolog的作用

MySQL在innodb引擎下，所做的增删改查都是先去buffer pool缓冲池（内存区域）里面操作，然后再把数据写入磁盘，因为增删改都是在内存操作，这样就存在系统异常导致数据丢失的情况，redolog就是为了解决系统异常导致内存修改丢失的问题。

（2）redolog如何保证数据不丢失

所有的操作都是以事务为单位的，在事务未执行完毕的时候数据库异常导致数据丢失是正常的，因为事务未提交成功。在事务提交的时候，我们把redolog从内存刷入到磁盘中去，从而保证修改不丢失，如果写入磁盘失败，那事务也将提交失败。

（3）为什么不直接把修改更新到磁盘？

MySQL操作数据是在内存中完成的，然后再把内存中的数据页写入到磁盘中。不直接操作磁盘目的就是为了提高性能。虽然redolog落盘的时候也是入磁盘，但它是顺序写入，而直接对磁盘上数据修改是随机写入，顺序写的速度要远远快于随机写。

1.2.3.2、undo log(回滚日志)

当一个事务执行一半无法继续执行时，可以根据回滚日志将之前的修改恢复到变更之前的状态。undo log 是 innodb 实现，总的来说提供两个作用：回滚和多版本控制(MVCC)。是事务特性的重要组成部分，在数据发生更新操作时候（INSERT、DELETE、UPDATE）时会产生 undo 记录。先于 redo log 被记录。

（1）提供回滚操作

我们在进行数据更新操作的时候，不仅会记录redo log，还会记录undo log，如果因为某些原因导致事务回滚，那么这个时候MySQL就要执行回滚（rollback）操作，利用undo log将数据恢复到事务开始之前的状态。如我们执行下面两条SQL语句（一条更新、一条删除）：

此时undo log会记录两条对应的SQL语句，update对应的是更新之前的数据，delete对应的是insert 语句【反向操作的语句】。在操作出现异常时，可以使用undo log日志来实现回滚操作，将数据还原回去，以保证事务的一致性。

我们来看以下执行的过程（以第一条更新语句为例）：

第一步：从磁盘读取到user_id=1的记录，放到内存

第二步：记录undo log 日志

第三步：记录redo log (预提交状态)

第四步：修改内存中user_id=1的记录

第五步：记录binlog，记录完整SQL

第六步：提交事务，写入redo log (commit状态)

我们根据上面的流程来看，如果在上面的某一个阶段数据库崩溃，如何恢复数据。

在第一二三步执行时数据库崩溃：此时数据还没有发生任何变化，故没有任何影响，不需要做任何操作。

第四步修改内存中的记录时数据库崩溃：此时事务没有commit，所以这里要进行数据回滚，此时通过undo log进行数据回滚。

第五步写入binlog时数据库崩溃：这里和第四步一样的逻辑，此时事务没有commit，所以这里要进行数据回滚，会通过undo log进行数据回滚。

第六步事务提交时数据库崩溃：如果数据库在这个阶段崩溃，那其实事务还是没有提交成功，但是这里并不能像之前一样对数据进行回滚，因为在提交事务前 binlog可能成功写入磁盘了，所以这里要根据两种情况来做决定。如果binlog存在事务记录：那么就”认为”事务已经提交了，这里可以根据redo log对数据进行重做；如果binlog不存在事务记录，那么这种情况事务还未提交成功，所以会对数据进行回滚。

（2）提供多版本控制(MVCC)【undo log实现多版本并发控制（MVCC）】

MVCC，即多版本控制。在MySQL数据库InnoDB存储引擎中，用undo Log来实现多版本并发控制(MVCC)。当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据版本是怎样的，从而让用户能够读取到当前事务操作之前的数据【快照读】。

1.3、MySQL索引

1.3.1、什么是索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

如果数据库中没有索引，此时查找的时候就需要把整个表遍历一遍。比如如果想找id为1的图书信息，没有索引的查找过程就相当于一个”顺序表查找”，数据量少的时候查询效率差异倒不是很明显，一旦数据量比较大，达到千万亿级别的时候，查询就会特别的慢。而如果加了索引，查询效率可以提升几倍设置成百上千倍，当然这也不是完全绝对的，还取决与开发人员编写的SQL语句性能。

1.3.2、MySQL有哪些索引类型

（1）普通索引【INDEX】

普通索引是最基本的索引，它没有任何限制，一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入。普通索引仅加速查询。

（2）唯一索引【UNIQUE INDEX】和主键索引【PRIMARY KEY】

唯一索引与普通索引类似，不同的就是：索引列的值必须唯一，这里要注意的是，索引值是否允许为空，要分情况说明，多数可允许有空值，但主键索引是一类特殊的唯一索引，不可为空。如果是组合索引，则列值的组合必须唯一。简单来说：唯一索引是加速查询 + 列值唯一。

（3）组合索引

组合索引(也叫复合索引或者联合索引)，即一个索引包含多个列。组合索引指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。可以说：组合索引是多列值组成的一个索引，专门用于组合搜索，其效率大于索引合并。

严格来说，组合索引不算是单独的一类，因为组合索引也可以是普通索引或者唯一索引，只要索引建立在多列上，普通索引和唯一索引都是可以在多列上建立组合索引的。

（4）全文索引【FULLTEXT】

全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar、text 列上可以创建全文索引。值得一提的是，在数据量较大时候，先将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

1.3.3、MySQL如何分析索引是否生效

最简单的方式就是通过客户端连接管理工具，使用它的解释功能，查看possible_keys 字段值。

或者直接在查询语句前面添加关键词explain，对于explain查询出来的结果，还是看possible_keys 用到的索引是不是查询字段的索引。

1.3.4、MySQL索引为什么加快数据的检索速度

很多人知道使用索引可以提高查询效率，但是可能不太了解为什么可以加快数据的检索速度。MySQL索引默认使用的数据结构是B+树，B+树可以理解是扁且宽的（矮胖）。也就是层数少，每层的节点数目很多。但是每层的节点多归多，却不存储数据，只起到索引效果，所有的数据都存在叶子节点上头。MySQL的数据是直接存储在磁盘上的，而对于从磁盘查找数据来说，需要经历寻道、寻址、数据传输三个阶段，使用B+树，层数少，只在叶子节点存数据的特点就能极大的保证磁盘IO次数少，从而效率高。

有一些人可能会有疑问，为什么设计的是B+树，而不是B树或者哈希表？我们举一个例子说明，在需要根据范围查询时，比如我需要查询某一段时间内的数据，使用B树和哈希表在实现范围查询时效率都比较低。B+树的叶子节点使用了指针顺序（链表）从小到大地连接在一起，B+树叶节点两两相连可大大增加区间访问性，只要遍历叶子节点就可以实现整棵树的遍历，而B树的叶子节点是相互独立的，每个节点 key（索引）和 data 在一起，则无法查找区间；再说哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。

虽然索引有它的优势，但不是创建的越多越好，比如我们就不适合把每一列字段都建立索引，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。另外索引需要占用物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间

1.4、MySQL存储过程和函数

1.4.1、什么是存储过程和函数

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化开发，减少数据在数据库和应用服务器之间的传输，可以提高数据处理效率。

1.4.2、存储过程和函数的区别

存储过程和函数的区别在于函数必须有返回值，而存储过程没有；存储过程的参数可以使用IN、OUT、INPUT类型，而函数的参数只有IN类型。如果有函数从其他类型的数据库迁移到MySQL，那么就可能需要将函数改造成存储过程。

1.4.3、为什么使用存储过程和函数

在完成一个逻辑操作时，有时会执行多条SQL语句，此外这些SQL语句的执行顺序也不是固定的，它会根据条件的变化而变化。在执行过程中，这些需要根据前面SQL语句的执行结果有选择的执行后面的SQL语句。为了解决该问题，MySQL软件提供了数据库对象存储过程和函数。

我们使用存储过程，往往是一些比较复杂的业务，并且把业务使用存储过程来实现，也方便后期的维护。

1.4.4、如何编写存储过程

（1）创建存储过程

基本语法：

create procedure sp_name() begin ......... end

（2）调用存储过程：

基本语法：call sp_name()

注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

（3）删除存储过程

基本语法：drop procedure sp_name

注意事项：不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

（4）显示所有存储过程基本信息

基本语法：show procedure status

该语句会显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等。

（5）显示某一个mysql存储过程的详细信息

基本语法：show create procedure sp_name

我们来看一个示例：

1.4.5、项目中如何调用存储过程

我们以dao层和mybatis中XML交互为例（其他层如controller和service跟正常的业务没有任何差异）。

dao层可以传map作为 in 的请求参数：void call_myp2(Map map);

mybatis中编写xml语句调用存储过程，入参使用in，出参使用out。

1.5、MySQL字符编码

这里把字符编码单独拿出来讲解，也是因为曾经踩过坑，所以拿出来说明避免更多的人入坑。我们常用的字符集是latin1、gbk/gb2312、utf8/utf8mb4。

为了避免乱码问题，我们一般会选用utf8/utf8mb4，而这两者一般情况下都是没有差异的，都能正常使用。但小编遇到一个问题，使用utf8mb4可以但utf8不行，就是存储含有表情包（如火星文,moji表情等）的昵称时，使用utf8会抛出异常，表情包被识别为\xF0\x9F\x8D\x89Ch。

后面分析得知，moji表情是字符,不是图片,属于unicode，发生上面的异常也是数据库根本不认识\xF0\x9F\x8D\x89Ch…,在字符集中没有匹配到,导致\xF0\x9F\x8D\x89Ch…直接入库,发生异常。

所以我们要配置一个合适的字符集,使数据库支持这个字符，我们从字符的长度来进行分析，UTF8使用可变长度字节来存储 Unicode字符，目前可见字符集都只需要3个字节，包含了所有字符。但是问题出在unicode6系列编码上，它们需要4个字节，这部分就是有名的emoji。如果我们的数据库使用utf8，是无法存储emoji表情的。正好utf8mb4一个字符最多能存4字节，相对于utf-8来说能支持更多的字符集，也能满足存储moji表情的需求。

特别注意的是从MySQL 5.5.3 版本才开始提供了utf8mb4字符集，支持四字节的字符。另外JDBC驱动版本要求mysql-connector版本高于5.1.13。

1.6、MySQL视图和表

视图和表是MySQL数据库中两个需要掌握的基础知识，他们相似又有很大差异，表是真实存在的，占用内存。而视图是虚拟的，不占数据库空间。视图是数据库中的查询sql的语句，可以理解为保存后的查询结果，它保存的是真实表经查询后的结果。

比如我们在上面建了两张表：用户表（user）和岗位表（post），表中存储的数据是真实存在与磁盘当中的（我们也可以反向思考如果表中的数据不是真实存在的，那数据库就没有意义了）。那为什么有了表还需要视图呢？当我们需要对数据库中的一张或多张表的某些字段的进行查询时，需要编写一些关联SQL语句把我们需要的字段单独列出来，但我们又不想每次都去输入查询的SQL语句，那我们就可以定义一个视图，保留这个查询的SQL语句，下次就可以直接使用，注意是知识保留SQL语句并不保留SQL语句查询出来的结果数据。如果表中的数据发生了变化，从视图中查询出的数据也会随之发生改变。同样使用视图语句进行增删改也会影响表中的数据。

基本语法：

创建视图：create view view_name as select * from table_name;

查询视图：select * from view_name;

删除视图：drop view view_name;

举个例子：我们使用语句创建视图：create view myview as select user_id,user_name from user;然后再查询视图 select * from myview;就可以看到数据。

1.7、MySQL数据分区

1.7.1、数据分区的概念

数据分区是一种物理数据库的设计技术，它的目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。分区并不是生成新的数据表，而是将表的数据均衡分摊到不同的硬盘，表实际上还是一张表。分区可以做到将表的数据均衡到不同的地方，提高数据检索的效率，降低数据库的频繁IO压力值。

我们可以理解是将同一张表中不同规则的记录分配到不同的物理文件中。比如我们在表中某一字段设置按日分区，在按照日期进行查询时，就会到对应的分区表中查找数据，缩小了查询范围，大大提升了查询效率。

1.7.2、如何进行分区

我们要注意在创建分区要在刚开始创建表的时候同时创建分区，我们以按年分区为例：

2、常用函数

2.1、CONCAT函数

CONCAT(str1,str2) 函数用于将多个字符串连接成一个字符串，返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

比如我们执行：select CONCAT(My, S, QL); 返回结果 MySQL。我们执行：select CONCAT(My, NULL, QL);返回结果是 NULL

2.2、 REPLACE函数

REPLACE(str,str1,str2)，把str字符串中出现字符串str1的全部替换为字符串str2。注意MySQL里面全部替换是REPLACE，而不是REPLACEALL，不要弄混。

2.3、ISNULL函数

ISNULL(expr) 函数判断expr是否为空，如expr为null，那么isnull()的返回值为1，否则返回值为0。注意isnull()和isnull(“”)是返回0的。

2.4、IFNULL函数

ifnull(expr1,expr2)表达式，表示如果expr1不为NULL，则IFNULL()的返回值为expr1; 否则其返回值为expr2。

2.5、group by(分组)、having （过滤）和order by（排序）

group by(分组)、having （过滤）和order by（排序）这三个函数我们单独使用时，是非常好理解的，我们这里主要讨论他们一起联合使用，很多人容易弄混淆。我们常常将他们组合在一起用,完成分组+排序的功能。

我们首先了解几个知识点：having一般是和group by一起使用；在sql命令格式使用的先后顺序上，group by 先于 order by；order by不会对group by 内部进行排序，如果group by后只有一条记录，那么order by 将无效。要查出group by中最大的或最小的某一字段使用 max或min函数；group by、having、order by的使用顺序：group by 、having、order by。

我们来写个例子，查询user表中按照年龄（user_age）分组，重复次数大于1，并且按照重复次数由大到小排列：

3、MySQL常用场景

3.1、MySQL的批量插入

这个在之前的一篇文章《MyBatis 批量插入使用 foreach 循环插入的优化，使用分片多线程》中已经详细的介绍过了。通过foreach 循环插入，大量数据时配合分片批量插入。我们再延伸一个，在插入时，已存在的数据进行更新不再重复插入，使用MySQL的 ON DUPLICATE KEY UPDATE。

3.2、MySQL中删除重复数据只保留一条

我们以user表为例，删除user_name同名的数据，只保留一条。简单的处理方式就是通过HAVING查询重复的，然后保留id最大的那一条数据。

3.3、mysql 多表关联更新

对于多表关联更新的操作需要慎重，建议在更新前，先使用 SELECT 语句查询验证更新的数据与自己期望的是否一致。多表联合更新有很多种方式，可以使用INNER JOIN、LEFT JOIN，也可以使用子查询，下面就编写一个示例。

UPDATE USER uINNER JOIN post p ON u.user_id = p.user_id SET p.post_name = u.user_name;

4、MySQL常用技巧

4.1、数据库执行脚本生成实体类信息

示例：

4.2、获取汉字首拼音（包含特殊符号）函数

4.3、MySQL生成数据字典

示例：

4.4、Excel生成insert语句

有时候需要将Excel中数据导入到Mysql数据库中，此时，可以利用insert into脚本，整理sql数据，直接将数据插入到mysql数据库中。

示例：在excel单元格中对应的行填入公式：=”INSERT INTO user(user_name, user_age, user_phone) VALUES (“&A1&”, “&B1&”,”&C1&”);”

主键user_id为自增，创建时间create_time默认取系统当前时间，所以这两个字段不需要赋值。