mysql将一个表多条记录合并成一条_mysql源码 ，精通mysql底层实现

面试的时候，你是不是经常被问到？Mysql写 sql你是怎么优化的？

一条sql查询几十秒还没出结果

有的人会说加索引，查看执行计划。

是的索引是帮助mysql高效获取数据的排好序的数据结构。

那你有没有思考过，为什么做了这些操作，原来秒级别的操作瞬间变为毫秒级别的了？

先看看数据结构，我们知道的数据结构有

二叉树，红黑树，hash表，B-tree

二叉树：

二叉树的概念：一棵二叉树是节点的一个有限集合，该集合或者为空，或者由一个根节点加上两棵左子树和右子树组成

二叉树的特性：

1、每个节点最多有两棵子树，即二叉树不存在度大于2的节点

2、二叉树的子树有左右之分，其子树的次序不能颠倒

红黑树：

特性：节点是红色或黑色， 根节点一定是黑色每个叶节点都是黑色的空节点(NIL节点，每个红节点的两个子节点都是黑色的(从每个叶子到跟的所有路径上不能有两个连续的红节点)(即对于层来说除了NIL节点，红黑节点是交替的，第一层是黑节点那么其下一层肯定都是红节点，反之一样)从任一节点到其每个叶子节点的所有路径都包含相同数目的黑色节点，  正是由于这些原因使得红黑树是一个平衡二叉树

hash表

hash函数，又称为散列函数，但是这个hash函数并没有什么统一标准，它的核心思想就是就是把任意长度的输入(又叫做预映射pre-image)通过散列算法变换成固定长度的输出，该输出就是散列值。

这种转换是一种压缩映射，也就是，散列值的空间通常远小于输入的空间，不同的输入可能会散列成相同的输出，所以不可能从散列值来确定唯一的输入值。简单的说就是一种将任意长度的消息压缩到某一固定长度的消息摘要的函数，这个消息可能是字符、数组、字符串等等。

拥有这样的hash存储结构的数据结构称为散列表，或者叫哈希表。

哈希表一般基于数组实现，特定情况下结合链表，但是数组是哈希表基础数据结构。

B-tree

B-tree，即B树，而不要读成B减树，它是一种多路搜索树(并不是二叉的)：

1.定义任意非叶子结点最多只有M个儿子；且M>2；

2.根结点的儿子数为[2, M]；

3.除根结点以外的非叶子结点的儿子数为[M/2, M]；

4.每个结点存放至少M/2-1(取上整)和至多M-1个关键字；(至少2个关键字)

5.非叶子结点的关键字个数=指向儿子的指针个数-1；

6.非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；

7.非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；

8.所有叶子结点位于同一层

我们的数据存放在磁盘上面，如果你简单的做查询操作，每查一行记录，都会在磁盘上做一次Io操作，如果你有几万条数据，要找最后一条数据，需要几万次io操作，显然是不可取得。

加索引，其实就是用的底层的数据结构。

Key value元素，key 是索引元素，value是索引所在行的那个磁盘文件地址指针。如果mysql底层使用的是二叉树，明显比全表扫描提高了一倍。但是如果你的数据依次递增，二叉树变成链表，如果查找几十万元素的最后一个元素，也是全表扫描，显然对业务优化没一点影响。

 如果mysql底层使用的是红黑树结构，jdk1.8之后把hasnmap优化成了红黑树，mysql底层没有使用红黑树的原因。虽然红黑树有平衡功能，数据多了，自旋自动平衡，问题是层级太多，万级数据

假设有200层，计算机会有200次的io操作。所以，mysql开发人员没有使用红黑树。

层级越少，io操作越少，显然B-tree树结构是最合适的。竖向增加的同时也要横向扩展 ，根节点有多个，从根节点去找数据，明显加快了速度。Mysql 对b树做了改造，b+tree树

，叶子节点才存储数据，非叶子节点存储索引，在每层可以都出现，其实就是二分查找算法，在内存上操作，是非常快的，其实多路平衡二叉树，几千万的数据，数据层高度还是小于等于4的。

横向数据也不能太大，mysql是16k横向数据

一个索引假设8字节，指针占6字节，一次io操作会加载1170个索引，几千万数据，在叶子接节点存储的失数据，假设一条数据是1kb 我们叶子节点可以存储16个数据元素，三层查找1170*1170*16 大概能放2000万的索引，2次io就能查找到数据。

MYiSAM

MyISAM引擎使用B+Tree作为索引结构。叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引(Primary key)示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引(Secondary key)在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一棵B+树，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的我们打开msql的安装路径，找到datal路径，我们的每个数据库都会对应一个文件夹。打开文件夹，就是每个表对应的多个文件

Frm 存储表结构，myd 存储数据，myi文件存放索引。

Innodb

InnoDB是事务安全的MySQL存储引擎。最早由Innobase Oy公司开发，从MySQL 5.5版本开始是默认的表存储引擎(之前版本InnoDB存储引擎仅在Windows下为默认的存储引擎)。该存储引擎是第一个完整支持ACID事务的MySQL存储引擎，其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读，同时被设计用来最有效地利用以及使用内存和CPU

innoDB存储引擎有多个内存块，这些内存块组成一个内存池，主要负责如下工作：

维护所有进程、线程需要访问的多个内部数据结构

缓存磁盘上的数据，方便快速读取，同时在对磁盘文件的数据修改之前在这里缓存

重做日志(redo log)缓冲

后台线程的主要作用：

负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据

将已修改的数据文件刷新到磁盘文件

Buffer Pool是InnoDB在主内存中用于缓存查询到的表和索引数据的区域，它能够使InnoDB直接从内存中处理经常使用的数据，从而加快处理速度。在专用MySQL服务器上，多达80%的物理内存通常分配给缓冲池。

为提高大批量数据的读取效率，Buffer Pool被划分为多个页，每个页包可以含多条行记录。为了提高缓存管理的效率，Buffer Pool被实现为页面的列表，很少使用的数据页使用变种的LRU算法从缓存中老化。

预读或者表、索引的扫描都会造成大量数据页被读入缓冲池，这些页可能会在短时间内被读取若干次，然后从此不再访问，因此，数据页的大量插入到新页头部可能会将热点数据迅速老化移除出缓冲池。这种情况叫做缓冲池搅动。为解决该问题，MySQL提供了配置参数#innodb_old_blocks_time 用来指定该页在放入缓冲池后第一次读之后一定时间内(时间窗口，单位毫秒)读取不会被标识为年轻，也就是不会被移动到列表头部 。

相关参数

innodb_old_blocks_pct 该参数为旧页列表占LRU列表的百分比，默认为37即3/8，取值范围为[5,95]。

innodb_old_blocks_time 指定第一次访问页面之后的时间窗口(以毫秒为单位)，在此期间，可以访问页面，而无需移动页到LRU新页列表的前端。innodb_old_blocks_time的默认值是1000。增加这个值会使越来越多的块从缓冲池中老化得更快。当然你可以用监视器去监视缓存池的状态。

日志缓冲是存储要写入磁盘日志文件的数据的内存区域。日志缓冲的数据会定期刷新到磁盘。大的日志缓冲区可以确保大事务无需在事务提交前将重做日志写入磁盘。因此如果应用程序的事务较多，增加日志缓冲区的大小可以减少磁盘的I/O。

相关参数：

innodb_log_buffer_size 日志缓冲区大小，默认为16MB，单位是byte

innodb_flush_log_at_trx_commit 控制如何将日志缓冲区的内容写入并刷新到磁盘

指定SELECT缓存

select语句中可以通过 SQL_CACHE 和 SQL_NO_CACHE 指定缓存或不缓存该查询结果

SQL_CACHE

如果查询结果是可缓存的，并且 query_cache_type 的值是ON或DEMAND，则查询结果将被缓存

SQL_NO_CACHE

服务器不使用查询缓存。它既不检查查询缓存以查看结果是否已缓存，也不缓存查询结果。

SELECT SQL_CACHE id, name FROM customer;

SELECT SQL_NO_CACHE id, name FROM customer;

变更缓冲

Change buffer的主要目的是将对二级索引的数据操作缓存下来，以此减少二级索引的随机IO，并达到操作合并的效果。在MySQL5.5之前的版本中，由于只支持缓存insert操作，所以最初叫做insert buffer，只是后来的版本中支持了更多的操作类型缓存，才改叫change buffer 。

当执行插入、更新或删除操作时，会触发与表有关的索引的操作，表的索引也会进行相应的插入、更新或删除，这样会影响到对源表的数据的操作速度。当对表进行DML操作时，非聚集索引的值通常是无序的，如果索引所在的页不在缓冲池中，就需要从磁盘中读取该页到缓冲池中进行更新，然后刷新到磁盘，如果有大量的DML操作，就会产生大量的磁盘随机读取，影响性能。

Change Buffer的作用，就是当变更的二级索引的相关页不在缓冲池中时，将二级索引的更改缓存到Change Buffer区，当该索引的页被加载到缓冲池中时，根据Change Buffer区中的更改对其进行更新合并，更新的页之后被刷新到磁盘。InnoDB主线程在服务器接近空闲时以及在缓慢关闭期间合并缓冲的更改。

通过Change Buffer，可以减少磁盘读写，对于具有大量DML操作的应用程序来说该功能很有用。不过Change Buffer也有弊端，那就是它占用了Buffer Pool的空间，从而减少了缓存数据页的可用内存。

innodb_change_buffering 该参数可以指定哪些操作可以使用change buffer

InnoDB存储结构

InnoDB的逻辑存储结构如上，所有数据都被逻辑地存储在表空间中，其中表空间又由段(segment)、区(extent)、页(page)组成，在一些文档中页也被称为块(block)。

表空间

InnoDB存储引擎中所有数据都是存储在表空间中

系统表空间

系统表空间以ibdata1命名，在安装数据库初始化数据时就是系统在创建一个ibdata1的表空间文件，它会存储所有数据的信息以及回滚段(undo)的信息。MySQL5.6之后，undo表空间可以通过参数单独设置存储位置。

独立表空间

设置参数innodb_file_per_table=1可以使每张表都有一个独立的表空间文件，而不用存储在ibdata1中。值得注意的是独立表空间存放的是B+树数据、索引和插入缓冲等信息，其他数据还是存放在ibdata1中。目前默认使用的就是独立表空间的存储方式。

临时表空间

MySQL5.7把临时表的数据从系统表空间中抽离出来，形成自己的独立表空间，并把临时表的相关检索信息保存在系统信息表的information_schema库下的innodb_temp_table_info表中。

通用表空间

与系统表空间类似，通用表空间是可以存储多个表的数据的共享表空间。多个表放在同一个表空间中，可以减少存储开销。

段

表空间由段组成，常见的段有数据段，回滚段，索引段等。因为InnoDB表是索引组织的，所以索引即数据，数据即索引，数据段就是B+树的叶子节点(Leaf node segment)，索引段即B+树的非叶子节点(Non-leaf node segment)。

区

区由连续的页组成，是物理上连续的一块空间，每个区的大小固定是1MB。

页

页是InnoDB最小的的物理存储分配单位，有数据页和回滚页等。一般情况下，一个区由64个页组成，页的默认大小是16KB。从MySQL5.6开始可以自定义调低页的大小为8KB、4KB，MySQL5.7开始可以调高页的大小为32KB或64KB。

行

InnoDB引擎是面向列的(row-oriented)，也就是说数据是按行进行存储的。每个页存放的行记录按照行格式进行存放。

行记录格式

在InnoDB 1.0.X之前，InnoDB存储引擎提供了Compact和Redundant两种格式来存放行记录数据。Redundant是mysql5.0版本之前的行记录存储方式，之后仍然支持这个格式是为了兼容之前版本的格式，5.1之后很少用到了，因为Compact的结构设计比它好，因为compact格式消耗的磁盘空间和备份耗时更小，Redundant相比之下大了一些。compact格式更适用于大多数的业务场景。

在InnoDB 1.0.X版本开始又引入了新的文件格式(file format)，以前支持Compact和Redundant格式称为Antelope文件格式，新引入的文件格式称为Barracuda文件格式。Barracuda文件格式下拥有两种新的行记录格式：Compressed和Dynamic，同时，Barracuda文件格式也包括了Antelope所有的文件格式。

我们同样打开mysql目录，发现使用innodb引擎的表只对应两个文件，frm ibd文件存储数据和索引。什么是聚集索引。叶子节点包含所有的数据记录。而mysam是数据和索引分开存储。

Indob没建立主键索引，会使用其他列作为作为主键索引。没有唯一性约束的话，它会建立隐藏列，来维护整张表的所有数据。一般采用整型和自增的形式。(比较大小速度块横向比较，不建议使用uuid)自增的形式效率会高一点。

除了用b树 还可以用hash作为底层索引数据结构(一次磁盘io操作，找单条数据方便，根据具体业务采用什么数据结构)。

联合索引底层什么样的？

多个字段放在一个key里面，联合字段怎么来实现排序，先看第一个字段，如果第一个相等 看第二个字段。最左前缀原则

Mysql读写分离技术(Mysql主从架构

)

主从复制：当主服务器有写入(insert / update / delete)语句时候，从服务器自动获取

读写分离： insert / update / delete 语句操作一台服务器， select 操作另一个服务器

从库生成两个线程，一个 I/O 线程，一个 SQL 线程；

I/O 线程去请求主库的 binlog(概念： binlog 即二进制日志，它记录了数据库上的所有改变并以二进制的形式保存在磁盘中，它可以用来查看数据库的变更历史、数据库备份和恢复、Mysql 的复制(主从数据库的复制)打开  配置文件 vi /etc/my.cnf 并修改，列表：show  master  logs ;

清空：reset  master ;

刷新：flush  logs ;

查看指定日志： show  binlog  events  in  '日志键名

，show binary logs  show master status)。

简单概括：binlog 日志就是记录 MySQL 数据库增 / 删 / 改 SQL语句

作用：实现主从复制、灾难恢复

) ，并将得到的 binlog 日志写到 relay bin  log (中继日志)文件中；

主库会生成一个 log dum 线程，用来给从库 I/O 线程传 binlog；

SQL 线程，会读取 relay bin log 文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致

Binlog  不小心把数据库删除了，只要binlog还在，数据库还能恢复

Relaylog 我们用show命令查看日志文件内容

配置主服务器

步骤1：修改配置文件，开启 binlog 日志

步骤2：修改配置文件，设置一个 server_id = 数字(注：数字必须唯一

 步骤3：重启服务

配置从服务器

步骤1：修改配置文件，设置一个 server_id = 数字(注：数字必须唯一)步骤2：重启服务

步骤3：通过 change  master 语句指定同步主位置

弊端：牺牲一致性 加更多的延迟 网络断掉了，数据不一致，我们采取半同步的方法 等待把数据插入relog日志

一主多从，双主 主从从 环形多主，一主一从，级联同步企业中常用的mysql主从复制

是什么？

一主多从  成本最低，读写分离用的最多的，但是从节点增加多了,主服务器压力增大。用最简单架构方案解决最复杂的架构问题。

写的多的时候 我们采用双主的形式

级联同步 一个master 给一个或多个slave  一个slave在根多个slave 解决master节点压力太大的问题。

问题是中间slave挂了。

Atlas代理实现读写分离(读写分离、从库负载均衡、自动分表、IP过滤、SQL语句黑白名单、DBA可平滑上下线DB、自动摘除宕机的DB。)

Atlas是一个位于前端应用与后端MySQL数据库之间的中间件，它使得应用程序员无需再关心读写分离、分表等与MySQL相关的细节，可以专注于编写业务逻辑，同时使得DBA的运维工作对前端应用透明，上下线DB前端应用无感知。

所有读写都交给atlas  lvs

安装与卸载

# rpm -i Atlas-2.2.1.el6.x86_64.rpm # 安装

# rpm -e Atlas-2.2.1.el6.x86_64.rpm # 卸载

安装后的目录：/usr/local/mysql-proxy/ 当中。

进入配置文件

# vim /usr/local/mysql-proxy/conf/test.cnf

#Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔

proxy-backend-addresses = 192.168.56.102:3306

注意：如果主库和代理在一个服务器，可使用127.0.0.1

proxy-backend-addresses = 127.0.0.1:3306

#Atlas后端连接的MySQL从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔

#proxy-read-only-backend-addresses = 192.168.56.104:3306@1

#用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密。用户密码所有主库和从库都必须一至。(/iZxz+0GRoA= 明文：123456)

pwds = root:/iZxz+0GRoA=

#Atlas监听的工作接口IP和端口

proxy-address = 0.0.0.0:1234

# cd /usr/local/mysql-proxy/bin

# ./mysql-proxy test start # 启动

# ./mysql-proxy test stop # 关闭

基于docker编排mysql集群

使用Docker创建Cluster步骤

在centos窗口中 执行如下命令：

docker pull percona/percona-xtradb-cluster:5.7.20

修改.env

# default environment arguments for docker-compose.yml# set master data dir

MASTER_DATA=./master-data# set slave data dir

SLAVE_DATA=./slave-data# set master & slave root password

MASTER_PASSWD=P@ssw0rd# set slave root passwor

SLAVE_PASSWD=P@ssw0rd# set replicas mysql account name

REPL_NAME=replicas# set replicas mysql password

REPL_PASSWD=replicasPasswd

.启动两个节点，执行docker-compose up -d

进入bin目录，执行脚本 

1.重启MySQL集群后从节点无法正常恢复解决。

执行bin目录下的reset-slave.sh, 之后 连接数据库尝试，问题已经解决。

Mysql mvvc

MVCC (Multi-Version Concurrency Control) (注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)是一种基于多版本的并发控制协议，只有在InnoDB引擎下存在。MVCC是为了实现事务的隔离性，通过版本号，避免同一数据在不同事务间的竞争，你可以把它当成基于多版本号的一种乐观锁。当然，这种乐观锁只在事务级别未提交锁和已提交锁时才会生效。MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能。具体见下面介绍

InnoDB在每行数据都增加两个隐藏字段，一个记录创建的版本号，一个记录删除的版本号。

　　在多版本并发控制中，为了保证数据操作在多线程过程中，保证事务隔离的机制，降低锁竞争的压力，保证较高的并发量。在每开启一个事务时，会生成一个事务的版本号，被操作的数据会生成一条新的数据行(临时)，但是在提交前对其他事务是不可见的，对于数据的更新(包括增删改)操作成功，会将这个版本号更新到数据的行中，事务提交成功，将新的版本号更新到此数据行中，这样保证了每个事务操作的数据，都是互不影响的，也不存在锁的问题。

SELECT：　　当隔离级别是REPEATABLE READ时select操作，InnoDB必须每行数据来保证它符合两个条件：1、InnoDB必须找到一个行的版本，它至少要和事务的版本一样老(也即它的版本号不大于事务的版本号)。这保证了不管是事务开始之前，或者事务创建时，或者修改了这行数据的时候，这行数据是存在的。2、这行数据的删除版本必须是未定义的或者比事务版本要大。这可以保证在事务开始之前这行数据没有被删除。符合这两个条件的行可能会被当作查询结果而返回。

INSERT：

InnoDB为这个新行记录当前的系统版本号。DELETE：

InnoDB将当前的系统版本号设置为这一行的删除ID。UPDATE：

InnoDB会写一个这行数据的新拷贝，这个拷贝的版本为当前的系统版本号。它同时也会将这个版本号写到旧行的删除版本里。

　　这种额外的记录所带来的结果就是对于大多数查询来说根本就不需要获得一个锁。他们只是简单地以最快的速度来读取数据，确保只选择符合条件的行。这个方案的缺点在于存储引擎必须为每一行存储更多的数据，做更多的检查工作，处理更多的善后操作。MVCC只工作在REPEATABLE READ和READ COMMITED隔离级别下。READ UNCOMMITED不是MVCC兼容的，因为查询不能找到适合他们事务版本的行版本；它们每次都只能读到最新的版本。SERIABLABLE也不与MVCC兼容，因为读操作会锁定他们返回的每一行数据。

对于删除操作，mysql底层会记录被删除的数据行的删除事务id ，对于更新操作，mysql底层会新增一行形同数据，并记录好对应的创建事务id

在id为12的事务里执行查询操作，mysql底层会带上过滤条件，创建事务id<=max(当前事务id(12)，快照点已提交最大事务id)，删除事务id>max(当前事务id(12)，快照点已提交最大事务id)

注意：begin/start/transaction 命令并不是一个事物的起点，在执行他们之后的第一个操作innodb表的语句，事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动来分配事务id的。

底层用大量的日志以及数据版本链来做的

事务id  数据指针