mysql面试题

数据库

数据库(Database)是按照数据结构来组织、存储和管理数据的软件

三范式

三范式是数据库设计中的三个基本原则，主要用于优化数据库结构，减少数据冗余，提高数据存储和操作的效率。

第一范式：每一列都是原子性的，不能再被拆分成更小的单位。（比如地址字段包含了省市区县多个信息，这个字段就违反了第一范式，正确的做法是将地址字段拆分为多个字段）

第二范式：非主键列必须完全依赖于主键，不能仅与主键的一部分相关。比如订单表中有订单编号和商品信息（商品编号，商品名称等），商品信息不完全依赖订单编号，而是依赖于商品详情的主键，是违反第二范式的。

第三范式：非主键列不能依赖于非主键列。商品信息表，其中包含单价、数量和总价等字段，总价字段是通过单价和数量计算得出的，总价依赖于单价和数量这两个非主键字段，这违反了第三范式。可以将总价作为一个计算字段或者将其存储在另一个表中，而不是直接存储在商品信息表中。

Mysql基础

Mysql简介

MySQL是一种开放源代码的关系型数据库管理系统，常作为企业的主要数据库，

说明：

使用 C和 C++编写

使用sql来操作数据库

sql:Structured Query Language：结构化语言

MySQL架构

  • 连接池：最上层负责和客户端进行连接，比如jdbc，odbj这样的数据库连接的API，在这一层有连接池的概念，类似于线程池，连接池可以同时处理很多个数据库请求。同时这一层有SSL的安全概念，可以确保连接是安全的。
  • SQL接口：当SQL语句进入MySQL后，会先到SQL接口中，这一层是封装层，将传过来的SQL语句拆散，将底层的结果封装成SQL的数据格式。
  • 解析器：这一层负责将SQL语句进行拆分，验证，如果语句有问题那么就返回错误，如果没问题就继续向下执行。
  • 优化器：对SQL查询的结果优化处理，产生多种执行计划，最终数据库会选择最优化的方案去执行，尽快返会结果。比如select a，b from c where d。在这里会先查询c表中符合d的数据并将他们的ab项进行投影，返回结果，并不会直接把整张表查出来。
  • 缓存：对要查询的SQL语句进行hash后缓存，如果下一次是相同的查询语句，则在SQL接口之后直接返回结果。
  • 存储引擎：MySQL有很多种存储引擎，每一种存储引擎有不同的特性，他们负责组织文件的存放形式，位置，访问文件的方法等等。比较常用的有innoDB，MyISAM，MAMORY等。
  • 文件系统：存放数据的文件。

mysql中重要的物理文件

  • .frm文件：.frm文件是MySQL表结构定义文件，包含了表的结构信息，如字段名、数据类型等。每个表都对应一个.frm文件。
  • .ibd文件：.ibd文件是InnoDB存储引擎的数据文件，包含了表的数据和索引信息。每个InnoDB表都有一个对应的.ibd文件。（如果开启了共享表空间，可能一个文件对应多个表）
  • 对于MyISAM存储引擎，数据文件和索引文件是分开存储的。数据文件以.MYD为后缀，索引文件以.MYI为后缀。
  •  ibdata文件：ibdata文件是InnoDB存储引擎的共享表空间文件，用于存储InnoDB表的数据和索引信息。多个InnoDB表可以共享同一个ibdata文件。
  • 日志文件：MySQL使用多种日志文件来记录数据库操作，包括二进制日志文件（binlog）、错误日志文件（error log）、慢查询日志文件（slow query log）等。
  • 参数配置文件：MySQL的参数配置文件（如my.cnf）包含了数据库的配置信息，如端口号、缓冲池大小、日志文件路径等。
  • 数据库名称在物理文件中通常是以文件夹的形式存在，每个数据库对应一个文件夹，文件夹的名称就是数据库的名称。在这个文件夹中，会包含该数据库的数据文件、日志文件、索引文件等物理文件。

 

Mysql主键介绍

  • 主键是表中每一行数据的唯一标识
  • mysql没有强制要求每个表必须建立主键，但一般我们建立表时都会建立主键，以便对数据进行操作和管理
  • 常用的主键生成策略有：自增，uuid

自增主键和uuid主键比较

自增在存储和读取速度比uuid快

自增：数字型，占用空间小，易排序，在程序中传递方便。

uuid：字符串型，占的空间大，不易排序

innodb 的非主键索引都将存一个主键，uuid 相比整数 id，索引大小增加很多；

uuid 主键比较肯定比 整数慢，另外非主键索引查找最终还要引用一次主键查找；

innodb 主键索引和数据存储位置相关（簇类索引），uuid 主键可能会引起数据位置频繁变动，严重影响性能。

非主键索引搜索需要检索两遍索引：首先检索非主键索引获得主键，然后用主键到主索引中检索获得记录。

（innodb 中的主键是聚簇索引，Innodb的底层存储是B+Tree，如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的。为了存储和查询性能应该使用自增长id做主键。对于InnoDB的主索引，数据会按照主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力）

 

为什么自增主键比uuid主键存储速度快：

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页

如果使用uuid，由于每次插入主键的值近似于随机，新纪录可能被插到现有索引的中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据

 

自增主键比uuid占用空间小

自增主键是数字型，bigint占用8个字节，uuid为16位字符串占用16+1=17个字节

 

创建主键的规则

mysql强制的：主键在本表中是唯一的，主键不能为null

其他好习惯：

主键应当是对用户没有意义的。

主键应该是单列的，以便提高连接和筛选操作的效率

不要对主键做更新操作

主键应当有计算机自动生成。

 

mysql外键介绍

外键是在一个表B中存储另一个表A的主键，并指明这个字段是该表的一个外键，用来约束两个表的数据，保证数据的一致性

① 两个表必须是InnoDB表，MyISAM表暂时不支持外键

② 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；

 

产生的影响：对一个表的修改都要维护另一个表的数据否则就会报错

1、如果表A有关联的表B数据，删除表A的数据就会报错提示

2、如果表B插入数据的外键值在表A中没有该值也会报错提示

 

为表添加外键的语法：

外键的定义语法：

alter table 表名 add constraint 外键名称 foreign key(外键字段名称) references 外表表名(主键字段名称)

 

为什么不推荐使用外键

都是在应用层使用程序来保证数据一致性，不使用外键

原因：

1性能：如果一张表里有两个外键字段。那么，每次往表里插入数据，就必须往两个外键对应的表里查询是否有对应数据。

2并发：在使用外键的情况下，每次修改数据都需要去另外一个表检查数据,需要获取额外的锁。若是在高并发大流量事务场景，使用外键更容易造成死锁。

 

Null值与空值

如果字段定义为not null如果没有设置默认值，那么插入时就必须指定值

空值就是空的字符串’’

空值('')的长度是0，是不占用空间的；而的NULL长度是NULL，其实它是占用空间的

对字段做count统计时，如果字段值为null值，那么它不会参与统计，为空值会参与统计

null值的判断是is null和is not null 空值是用=’’

Null值与索引

InnoDB存储引擎可以为允许null值的字段加上索引，字段中有null值时查询也可以走索引

对于is null和is not null查询也可以走索引

虽然MySQL可以在含有null的列上使用索引，但不代表null和其他数据在索引中是一样的。最好限制not null

 

临时表

mysql中临时表有两种：外部临时表，内部临时表

外部临时表：

通过create temporary table 创建的临时表，这种临时表称为外部临时表。这种临时表只对当前用户可见，当前会话结束的时候，该临时表会自动关闭。这种临时表的命名与非临时表可以同名（同名后非临时表将对当前会话不可见，直到临时表被删除）。

 

内部临时表：

这种临时表会被MySQL自动创建并用来存储某些操作的中间结果。

这种内部表对用户来说是不可见的，但是通过EXPLAIN或者SHOW STATUS可以查看MYSQL是否使用了内部临时表用来帮助完成某个操作。

但是使用内部临时表需要创建表以及中间数据的存取代价，所以用户在写SQL语句的时候应该尽量的去避免使用临时表。

使用explain 如果看到Extra下面是Using temporary表示查询过程中创建了临时表

什么时候会产生临时表：

ORDER BY和GROUP BY的条件不一样时

子查询且mysql没办法优化时

UNION查询

等

sql长度限制

mysql中，in语句中参数个数是不限制的。不过对整段sql语句的长度有了限制，默认是4M

 

数据类型

整型：

tinyint(m) 1个字节

smallint(m) 2个字节

mediumint(m) 3个字节

int(m) 4个字节

bigint(m) 8个字节

不同的类型的取值范围不同，int(m)里的m是表示SELECT查询结果集中的显示宽度，只有在设置了zerofill用0来填充，才能够看到效果，表示：当数字的位数不够m时用0填充

 

浮点数：

float(m,d) 单精度浮点型，4字节

double(m,d) 双精度浮点型，8字节

最多可以存储M个数字，m个数字中最多有d个是小数位

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457

聚合计算的时候也会损失精度

不指定m和d会按照实际精度值显示

 

定点类型：

decimal(a,b)

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。28个有效位，对最后一位四舍五入。

定点数以字符串形式存储

若插入的值未指定小数部分或者小数部分不足b位则会自动补到b位小数，若插入的值小数部分超过了b为则会发生截断，截取前b位小数(四舍五入截取)。a值得是整数部分加小数部分的总长度

插入的值超出范围会报错

定点数的存储空间是根据其精度决定的。

 

字符串：

char(n) 固定长度，最多255个字符

varchar(n) 可变长度，最多65535个字符

tinytext 可变长度，最多255个字符

text 可变长度，最多65535个字符

mediumtext 可变长度，最多2的24次方-1个字符

longtext 可变长度，最多2的32次方-1个字符

 

一个字符占用多少字节：

UTF-8一个汉字占用3个字节，英文占用1个字节。

 

char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格，varchar不限于此。 

char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字符（4*3=12字节）

 

varchar(n)的n代表可以放入的字符个数，varchar是存入的实际字节数+1个字节（n<=255）或2个字节(n>255)，因为是边长所以需要多出1到2个字节用于记录数据的长度

所以varchar(4),存入3个字符将占用3*3+1=10个字节。 

char比varchar查询效率高，因为char的长度是固定的，而varchar长度不固定磁盘读取时每次都要读取长度

char和varchar插入数据如果超过了括号中的n就会报错

 

text是实际字节数+2个字节。

超过255字符的只能用varchar或者text

能用varchar的地方不用text

text不能指定默认值，text不能指定长度

varchar可创建整体索引也可以创建前缀索引（如果你没有指定前缀长度，MySQL会尝试为整个字段值创建索引。如果你指定了前缀长度，那么无论插入的内容有多长，索引都只会基于字段值的前N个字符。），text只能创建前缀索引。varchar查询速度快于text,在都创建索引的情况下，text的索引似乎不起作用。

 

二进制类型Blob

Blob是以二进制方式存储，不分大小写。只能整体读出

 

日期时间类型：

date 日期 '2008-12-2' 3字节

time 时间 '12:25:36' 3字节

datetime 日期时间 '2008-12-2 22:06:44' 8字节

timestamp 自动存储记录修改时间 4字节

若定义一个字段为timestamp，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。

 

数据类型的选择

空间够用的情况下采用从小原则，节省空间效率高

 

mysql连接数

MySQL的max_connections参数用来设置最大连接（用户）数。每个连接MySQL的用户均算作一个连接。

MySQL无论如何都会保留一个用于管理员（SUPER）登录的连接，用于管理员连接数据库进行维护操作，即使当前连接数已经达到了max_connections。因此MySQL的实际最大可连接数为max_connections+1；

这个参数实际起作用的最大值（实际最大可连接数）为16384，即该参数最大值不能超过16384，即使超过也以16384为准；
增加max_connections参数的值，不会占用太多系统资源。系统资源（CPU、内存）的占用主要取决于查询的密度、效率等；
该参数设置过小的最明显特征是出现”Too many connections”错误；

 

设置规则：

实际连接数是最大连接数的85%较为合适，所以最大连接数我们可以根据实际连接数去设置

可不可以设置太大：设置太大，就有可能同时存在过多的存活连接， 数据库会因为保持了大量的连接而使服务器资源耗尽而变得无法响应，因为每多一个connection，MySQL就会创建一个线程来维护这个连接，而且CPU在线程间切换需要重新加载对应线程的上下文信息，当 connections 数量不断增加时，CPU 不断在各个 connections 的查询中切换。

 

查看最大连接数：

show variables like '%max_connections%';

查看正在使用的连接数：

show global status like 'Max_used_connections';

设置最大连接数：

set GLOBAL max_connections=1024; --重启mysql会失效

修改mysql配置文件my.cnf，在[mysqld]段中添加或修改max_connections值（需要重启mysql,永久设置方法）：

max_connections=512

mysql的存储引擎

什么是存储引擎

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型(即存储和操作此表的类型)。

可用SHOW ENGINES 查看MySQL使用的引擎

 

常用的存储引擎有那些

MyISAM

查询速度快，但不支持事务，适合于多读取插入，少更新删除的操作表。

 

InnoDB

提供了具有提交、回滚和崩溃恢复能力的事务安全，对比MyISAM的存储引擎

你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，可靠性要求比较高，应该使用InnoDB表

存储引擎是针对数据库还是表

针对的表，新建表的时候可以指定使用的存储引擎

InnoDB 存储引擎存储原理

InnoDB表数据的存储是按照主键的值来组织的；

redo log是重做日志；undo log是撤销日志

数据查询：

逻辑原理：

  • 解析器解析sql,将SQL查询分解成多个组成部分，例如SELECT、FROM、WHERE等子句，并检查其语法是否正确。有语法错误，MySQL会返回一个错误消息。
  • 预处理：解析别名、存储过程和函数等。这一阶段还可能包括用户权限检查
  • 优化：查询优化器，负责确定查询的最佳执行计划。会考虑多种因素，如表的大小、索引的存在、连接顺序等，以选择最高效的查询执行方式。可能会重写或修改原始查询，以便更有效地执行。
  • 查询执行：按执行计划进行执行，执行过程可能涉及从磁盘读取数据、在内存中排序数据、执行连接操作等。
  • 结果返回：将执行结果返回给客户端

物理原理：

innodb查询数据时先去缓冲池中查询，缓存池中是以页为单位进行缓存索引和数据，如果缓冲池没有就从磁盘io获取，并把对应的页放入到缓冲池，缓存池通过 LRU 算法对数据页进行管理，也就是最频繁使用的数据页排在列表前面，不经常使用的排在队尾，当缓冲池满了的时候会淘汰掉队尾的页。从磁盘新读取到的数据页并不会放在队列头部而是放在中间位置，这个中间位置可以通过参数进行修改。缓冲池也可以设置多个实例，数据页根据哈希算法决定放在哪个缓冲池。

 

数据更新：

更新时先查看缓冲池Buffer Pool有没有对应的页，如果有直接更近缓冲池中的数据，返回结果；

如果缓冲池没有，如果是唯一索引的页信息，需要从磁盘获取到数据放入缓冲池，之后更新，如果非唯一索引的页信息，会把修改操作写入到changebuffer中，返回结果，changebuffer的数据会在读该页时合并到缓冲池中。

写数据的物理操作还会写入到redolog重做日志中，redolog也是先写内存之后根据触发条件（事务提交前会写入文件）或定时刷到磁盘（默认一秒），写操作还会写undolog撤销日志，会在事务提交前需要写入磁盘。undolog的写入操作也会记录到redolog中.

 

下面我们理一下一条　update 语句的执行过程：

update person set age = 30 where id = 1;

1.分配事务 ID ，开启事务，获取锁，没有获取到锁则等待。

2.执行器先通过存储引擎找到 id = 1 的数据页，如果缓冲池有则直接取出，没有则去主键索引上取出对应的数据页放入缓冲池。

3.在数据页内找到 id = 1 这行记录，取出，将 age 改为 30 然后写入内存

4.生成 redolog undolog 到内存，redolog 状态为 prepare

5.将 redolog undolog 写入文件并调用 fsync

6.server 层生成 binlog 并写入文件调用 fsync

7.事务提交，将 redolog 的状态改为 commited 释放锁

 

数据的删除

在 InnoDB 中，delete 操作，并不会真的把数据删除，mysql 实际上只是给删除的数据打了个标记，标记为删除，表文件在磁盘上所占空间不会变小，被标记删除的空间是可以被复用的，新的数据插入会进行覆盖被标记的空间.

trunct操作在底层实现上直接设置数据文件和索引文件为无效可重用，并创建新的文件用来存储数据和索引，无效数据的空间以后可以被其他数据覆盖使用。

innodb页的概念

对于操作系统，它涉及到虚拟地址空间和物理内存地址之间的映射关系。操作系统通过将虚拟地址空间和物理内存地址建立映射，使得CPU能够间接地访问物理内存地址。虚拟地址空间被划分为多个单位，这些单位就被称为页。每个页的大小称为页面大小，常见的页面大小有512Byte、4K、8K等，但这个大小并非固定，它可以根据需要进行调整。

对于innodb的页是innodb磁盘管理的最小单位（innodb中的页概念）。默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K；

B+tree索引结构的每个节点的大小是1页

但如果表中一行的数据长度超过了16k，这时候就会出现行溢出，溢出的行是存放在另外的地方，存放该溢出数据的页叫uncompresse blob page。

 

页中的数据之间的联系是一个根据大小比较后从小指到大的单向链表。

每个数据页都有上一个和下一个页的编号，所以所有的数据页会组成一个双链表.

 

InnoDB会为把页中的记录划分为若干个组，每个组的最后一个记录的地址偏移量作为一个槽，存放在Page Directory中，所以在一个页中根据主键查找记录是非常快的，分为两步：

  • 通过二分法确定该记录所在的槽。
  • 遍历组里各个记录。

 

为什么默认16k

因为16k就可以满足3层的树可以存储2千万左右的数据，符合大多数企业的需求.太大每次读取数据数据太多会占用更多的内存，太小就可能要进行更多的次数来获取数据。

innodb缓冲池Buffer Pool

缓存的内容：

缓存最热的数据页和索引页

缓冲池的使用：

InnoDB将记录按页的形式进行管理，缓冲池中也是以页为单位进行管理，对于页的修改先修改到缓冲池中，之后以一定频率进行刷新到磁盘中

在数据库的页读取操作时，将页缓存到缓冲池中，下一次如读取相同的页，则无需从磁盘中加载。

缓存池大小通过innodb_buffer_pool_size配置。

使用LRU淘汰策略最近最少用的数据会被最近使用的数据替换掉

 

change buffer

Change Buffer是缓存那些不在buffer pool里的辅助索引的变化的特殊数据结构 。

在辅助索引发生改变时，如果辅助索引在buffer pool里面就会直接进行修改。如果发生变化的辅助索引页不在buffer pool里，则由Change Buffer先缓存这些辅助索引页的变更动作。等下次辅助索引页被读取时，会将数据再将数据合并(merge)恢复到缓冲池中的技术。

 

change buffer既包含内存结构，也包含磁盘结构。

在内存中，Change Buffer是缓冲池的一个组成部分。内存中的change buffer主要是缓冲辅助索引的变更操作，以便对变更操作进行合并，提高对辅助索引修改效率。

在磁盘上，Change Buffer是system tablespace（系统表空间）的一部分，当数据库宕机时，索引的变更会被缓冲到磁盘的Change Buffer区域。

其大小可以用参数innodb_change_buffer_max_size 控制

 

merge的时机：

当下一次查询命中这个页的时候，会先从磁盘中读取数据页到内存中，然后先执行change buffer的merge操作。除了查询操作外，系统有后台线程会定期merge，数据库正常关闭的时候，也会进行merge操作。

 

为什么唯一索引的页修改不能用buffer pool

因为要判断数据的唯一性就要获取到其页数据，而buffer pool不获取页数据，所以不能用

 

 

Redo log buffer：

重做日志首先是写入到重做日志buffer中，之后刷新到磁盘日志文件中，刷新时机：

  • 每秒刷新一次；
  • 事务提交时刷新；（事务提交时先写重做日志，在修改内存页）
  • buffer缓存区空间小于1/2

（如果缓冲区过小则导致频繁的磁盘刷新，降低性能）

大小由innodb_log_buffer_size变量定义， 默认大小为16MB

 

自适应hash索引（Adaptive Hash Index）

InnoDB存储引擎会自动根据访问的频率和模式来自动的为某些热点也建立hash索引，来提供查询效率

AHI是通过缓存池的B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建hash索引。

 

AHI的要求：

1、对页连续访问的模式必须是一样的，即查询条件一样。

2、hash索引只能用来搜索等值的查询，如where age=20

 

mysql日志说明

innodb事务日志包括redo log和undo log。

redo log是重做日志，提供前滚操作，保证数据持久化

undo log是撤销日志，提供回滚操作，保证事务原子性

 

redolog

作用：数据持久化，当数据库异常宕机，重启时能够根据redolog恢复未写入磁盘的数据；

内容：redolog 记录的是数据页的物理操作日志,而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页.

什么时候写：写请求过来后先写redolog到redologbuffer之后把数据写入内存

持久化：redolog默认1s的频率刷到磁盘，logbuffer到内存阀值也会刷到磁盘，事务提交前也会刷到磁盘

循环队列结构：MySQL将redo log分为多个固定大小的文件组成的循环队列。当写满一个redo log文件时，MySQL会自动切换到下一个redo log文件继续写入。

事务回滚：如果redo log的信息未刷到磁盘，写操作都将被撤销；如果刷到了磁盘并不会被删除或清除，真因为故障要恢复数据的时候只会用redo log恢复已经提交事务的数据，回滚的事务做的修改不会被恢复到数据文件。

怎么确定恢复那些数据：恢复到最后检查点，检查点可以确保之前的数据都已经写入磁盘

redolog与数据文件的关系：正常情况下数据文件是在检查点时从缓存中刷新到磁盘上的数据文件；当系统故障需要恢复数据的时候会使用redo log来恢复最后一个检查点之后的数据。

redo log和检查点：检查点时会触发未持久化的数据相关的redo log也进行持久化。redo log的持久化必检查点创建的频率高。

 

undolog

作用：提供回滚（回滚行记录到某个版本）和多个行版本控制(MVCC)

新建时机：在事务执行过程中，当数据被修改时才产生。

记录时机：数据修改（添加修改删除）会记录撤销日志，是逻辑日志,先写undolog，再写redolog

记录内容：旧版本数据、事务标识符（用于区分是那个事务）、sql操作类型（增删改）、被修改的行信息、回滚指针（undolog记录之期间的指针，方便回滚的时候调用、mvcc相关信息（事务可见性相关的信息，保证事务的隔离性））

持久化：事务提交前持久化。undo log 通常存储在系统表空间（5.6版本之前）或独立的 undo 表空间中（5.6版本及以后）

删除规则：在事务提交之后，Undo log并不会立即被删除。它会被放到待清理链表中。MySQL会判断没有其他事务需要用到该版本的信息时，才会清理相应的Undo log。这是因为其他事务可能还在使用这些旧版本的数据，特别是在多版本并发控制（MVCC）的场景下。

用于回滚：日志中记录了修改前的旧版本数据，从事务从后往前来恢复数据

用于mvcc:不可重复读和可重复读隔离级别需要使用mvcc来实现,mvcc的实现需要基于undolog来确保可以读取到想要的数据（因为它保存了各事务的旧版本数据）。

 

mysql的后台进程会对undolog中记录的旧版本和删除标记的记录进行物理删除

 

binlog说明

通过 binlog 来将数据同步到集群内其他的 MySQL 实例。

binlog是记录的操作的sql，redolog 记录的底层某个数据页的物理操作，redolog 是循环写的，而binlog 是追加写的，不会覆盖以前写的数据。binlog 也需要在事务提交前写入文件。

由于事务的原子性，需要保证事务提交的时候 redolog 和 binlog 都写入成功，所以 MySQL 执行层采用了两阶段提交来保证 redolog 和 binlog 都写入成功后才 commit，如果一方失败则会进行回滚。

 

Checkpoint说明

也称为检查点，主要用于将内存中的数据持久化到磁盘上

我们写请求是写数据到缓冲池bufferpool中的，并没有写入磁盘，Checkpoint做的事情就是把内存中的数据刷新到磁盘

如果要使用redolog恢复数据只要恢复上次checkpoint之后的数据就可以了，检查点可以确保从最后一个检查点开始，系统知道所有在此之前的数据页修改都已经被写入数据文件。

 

脏页：缓冲池中更新过的数据（还没有刷新到磁盘）

什么时候刷新脏页数据到磁盘：

  • 定时：每隔几秒或十几秒进行一次刷新，刷新一定比例的脏页数据
  • 缓冲池中脏页数据达到一定比例会触发，不同版本值不一样，如75%
  • mysql关闭时触发全量的脏页刷新到磁盘
  • redo log file 重做日志不可用，剩余容量少于一定值后触发 checkpoint。
  • 等

 

 

Mysql索引

索引的作用

索引是帮助mysql高效获取数据的排好序的数据结构，它的作用就是提高数据查询效率

，如果创建唯一性索引，可以保证数据库表中每一行数据的唯一性

 

索引的优缺点

优点：索引可以加快数据的检索效率

缺点：增删改数据需要维护索引，会占用存储空间

 

索引的分类

按类型分：

PRIMARY 主键。 就是唯一且不能为null。（主键索引）(如果是字符串可以为空字符串)

Normal索引，普通的UNIQUE 唯一索引。 不允许有重复。（普通索引，唯一索引）

FULLTEXT 是全文索引，用于在一篇文章中，检索文本信息的。（全文索引）

 

按字段数量分：

索引分单列索引和组合索引（又叫联合索引又叫复合索引）

 

组合索引是否使用跟where条件中那个在前那个在后没有关系，关键是有没有

key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找

主键索引与唯一索引区别

主键列不允许为null(主键如果是字符串可以为””)，而唯一性索引列允许null(可以多条记录都是null而不冲突)。

innodb存储引擎之外的唯一索引都可以有多个null

mysql的全文索引说明

char、varchar、text类型字段能创建全文索引

使用的固定的停用词进行分词如空格、逗号、减号，使用的数据结构是倒排索引，内置的解析器不支持中文，可以使用 ngram 解析器(中日韩文适用)或 MeCab 解析器(日文适用)

那些地方要添加索引

1、where条件列（如创建时间经常作为条件，就可以加索引）

2、排序列或者分组列

在建立索引的时候，要根据列基数来建立。列基数=列中不同的数据/除以总数据。越接近1表示重复数据越少，越适合建立索引。

对于有很多重复的值的列不需要加索引，比如状态字段，因为相同数据太多走

索引的效率不高mysql就会走全表扫描，这样加索引也没用了

 

 

不走索引的情况

索引列参与了运算不会走索引

索引列参与了函数运算不会走索引

索引列参与右模糊会走索引，左模糊不会走索引

索引列参与了正则表达式不会走索引

Or在查询条件列都有索引时会走索引，否则不走索引

MySQL 的 in 查询在 5.5 以上的版本中存储引擎都是 innodb 的，正常情况下会走索引的，5.5 之后的版本会走索引

< 小于 > 大于 <= 小于等于>=大于等于!= <>不等于，is null,is not null ,in,not in,existis,not exists根据实际查询数据来判断，如果全盘扫描速度比索引速度要快则不走索引。（组合索引有null值也是一样）

删除字段会删除对应的索引吗

会删除对应的索引，删除表也会删除该表的索引(会逻辑删除，之后的数据可以覆盖这些空间)

索引的数据结构

Mysql不同的存储引擎支持的索引数据结构不同，如BTree，RTREE，HASH等

常用的存储引擎都支持BTree索引

我们常用的innodb存储引擎使用的BTree索引的改版B+Tree索引结构

 

B-tree和B+tree索引结构

他们都是平衡二叉树演化而来的（他们都是多叉树）

  • Tree是平衡多路查找树，B-Tree是为磁盘等外存储设备设计的一种平衡查找树。

B-Tree叶子节点具有相同的深度，叶子节点的指针为空，节点中的数据key从左到右递增排序，非叶子节点存储key和data和指向子节点的指针，叶子节点存储data

B+Tree非叶子节点不存储data,只存储key和指针（指向下级节点，每个非叶子节点的指针都是唯一的，并且指向不同的子节点。），叶子节点存储data和指向相邻叶子节点的指针

B-tree每个非叶子节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息和指针信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。B+Tree的高度一般都在1~3层（高度可以为1只有根节点存放数据）

B+Tree中的叶子节点比B-tree多存储了指向两边叶子节点的指针，这样更方便叶子节点的范围遍历。

树高度信息：page level+1；根节点中保存了page level的值

data节点：节点上的数据也是有序的

 

页是InnoDB磁盘管理的最小单位。默认每个页的大小为16KB；

而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

 

图示

B-tree

B+tree

如果让画图，先画结构，之后往里面填充数据

 

查找过程：

查找主键是29的数据

 

首先根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】

比较关键字29在区间[28,+∞），找到磁盘块1的指针P2。

根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】

比较关键字29在区间[28,36），找到磁盘块3的指针P1。

根据P1指针找到磁盘块7，读入内存。【磁盘I/O操作第3次】

在磁盘块7中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。

 

总结：

根据主键从根节点（根页）开始通过二分法逐步查找到存放主键数据区域的指针，找到数据区域后还是使用二分法来查找主键对应的行数据

磁盘存储和页大小

磁盘存储数据最小单元是扇区，一个扇区的大小是512字节，而文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是4k，而对于我们的InnoDB存储引擎也有自己的最小储存单元——页（Page），一个页的大小是16K。

一颗b+Tree能放多少行数据

3层的树约2千万，占用的内存大小大约2千万k=20G左右

假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，一页能存放16384/14=1170个指针

现在很多互联网业务数据一条记录大小通常就是1K左右，一个数据节点16k,存放数据和指向相邻子节点的指针，所以一个节点大约能存放16/1=16条数据

如果b+tree有两层：1170*16=18720条

如果b+tree有三层：1170*1170*16=21902400条

所以B+树高度一般为1-3层，它就能满足千万级的数据存储

io次数与树节点关系

索引树的节点之间并不是使用连续的存储空间进行存储的，所以一般不同节点的数据在空间上是不连续的，所以没多读一个节点（如果这个节点不在内存中）就多一次io次数（对于叶子节点同样适用）

b+tree层数的变化过程

初始阶段的B+树高度为2层

2层变成3层的阈值取决于多个因素，包括每个节点的大小、索引键的大小、数据库配置等，当足够的数据量达到阀值时2层的b+树就会变成3层，变成3层后需要把索引进行重组来适配新的树结构上的节点顺序。

 

聚集索引和辅助索引

聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引（次级索引）与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

主键和聚集索引

如果一个主键被定义了，那么这个主键就是作为聚集索引

如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引

如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。

覆盖索引

使用覆盖索引执行查询时，MySQL只需要在索引中查找需要的数据，并且不需要访问表中的数据行。

如果一个查询能够在一颗索引树上获取到所有数据而无须回表（查询的数据都在索引中），就是做到了覆盖查询

在explain的extra列可以看到using index代表使用的覆盖索引

比如select id,name from user where name='shenjian';

在name的索引树上就可以获取到id和name的值，就不需要再遍历聚集索引树了

回表查询：先定位主键值，再根据主键值定位行记录

HASH索引结构

仅支持Memory引擎。

哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置

hash索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率

  • hash索引可以进行“=”,“IN”查询，不能用于范围查询（由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值跟索引前的值得大小关系很可能不一样）
  • 不能模糊查询（因为是进行整体hash），
  • 不能进行排序查询（因为哈希索引并不是按照索引值顺序存储的）
  • Hash 索引不能利用部分索引键查询，也就是针对组合索引，不支持最左匹配原则(对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值)

Mysql优化

说明explain

执行计划便于查询前预先估计查询究竟要涉及多少行（rows）、连接类型（type）、使用哪些索引（key）、运行时间

在联合查询中，type一般为ref(使用了一般索引)。一般来说，得保证查询至少达到range（使用了in,between,><等）级别，最好能达到ref。

 

在SQL语句前加一个"EXPLAIN"即可

运行上面的sql语句后你会看到，下面的表头信息：

id|select_type|table | type | possible_keys | key | key_len | ref | rows | Extra

id :select查询的序列号，id相同时，执行顺序由上至下，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

select_type:select查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询。

  • SIMPLE：普通查询查询中不包含子查询或者UNION
  • PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
  • SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
  • DERIVED：在FROM列表中包含的子查询被标记为：DERIVED（衍生）
  • UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在 FROM子句的子查询中，外层SELECT将被标记为：DERIVED
  • UNION RESULT：从UNION表获取结果的SELECT被标记为：UNION RESULT

table 

显示这一行的数据是关于哪张表的

type 

这是重要的列，显示连接类型。从最好到最差的连接类型为const、eq_ref、ref、range、index和ALL

在联合查询中，一般为REF。一般来说，得保证查询至少达到range级别，最好能达到ref。

 

说明：不同连接类型的解释（按照效率高低的顺序排序）

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引。如：select "666"

system：表只有一行：system表。这是const连接类型的特殊情况。

const ：通过索引一次就找到了匹配的行。因为只有一行，这个值实际就是常数。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引或唯一索引的非唯一前缀进行的查找

range：扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>、in等的查询

index：扫描全部索引树，通常是因为没有WHERE子句或者WHERE子句的条件不能有效地使用索引。

ALL：扫描全表，这一般比较糟糕，应该尽量避免。

 

index说明：

如：

SELECT * FROM users WHERE age > 0; -- 假设大多数用户的age都大于0。尽管我们age建立了索引，但查询的范围涵盖了所有索引的值或大多数值，MySQL可能决定直接扫描整个索引来获取结果。

 

possible_keys 

显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key 

实际使用的索引。如果没有使用索引，则改列为NULL，如果想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用force index、 ignore index。这个字段的值是从possible_keys中挑选出来的，是MySQL优化器根据统计信息、查询条件、表的大小等因素综合判断后认为最优的索引。

key=primary的话，表示使用了主键；如果为NULL，则没有使用索引。

 

key_len 

索引字段的最大可能长度，并非实际使用长度。表示索引中使用的字节数,在不损失精确性的情况下，长度越短越好,

 

ref 

显示索引的哪一列被使用了，如果可能的话，是一个常数

 

rows 

MYSQL认为必须检查的用来返回请求数据的行数,这个数表示mysql要遍历多少条数据才能找到

 

Extra 

查询的额外信息。坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

 

说明：extra列返回的描述的意义

Distinct ：一旦mysql找到了与行相联合匹配的行，就不再搜索了。

Not exists ：mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。

Range checked for each Record（index map:#） ：没有找到理想的索引，因此对从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。

Using filesort ：看到这个的时候，查询就需要优化了。（MySQL中无法利用索引完成的排序操作称为“文件排序”）当我们试图对一个没有索引的字段进行排序时，就是filesoft。它跟文件没有任何关系，实际上是内部的一个快速排序。 。

Using index ：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。

Using temporary ：看到这个的时候，查询需要优化了。常见于排序和分组查询，这里，mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。

using where ：是使用上了where限制，表示MySQL服务器在存储引擎收到记录后进行“后过滤”（Post-filter），如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集。

 

impossible where 表示用不着where，一般就是没查出来啥。

弄明白了explain语法返回的每一项结果，我们就能知道查询大致的运行时间了，如果查询里没有用到索引、或者需要扫描的行过多，那么可以感到明显的延迟。因此需要改变查询方式或者新建索引。

 

数据库优化方面的经验

表结构优化：

尽量使用自增主键不使用uuid,因为自增主键易排序而且需要的存储空间少

字段尽量设置为not null,null值也会占用空间，而且null的存在会影响索引效率

一张表中字段不要太多，做适当拆分，避免各种业务场景都查同一张表

适当的冗余，对于不变的数据可以进行在其他表冗余，避免太多的关联查询，如订单id和订单编码

 

索引优化：

在常用的where条件，分组及排序条件字段建立索引

 

查询优化：

  • 对查询进行优化，应尽量避免全表扫描，避免使用函数，表达式等不能使用索引的查询方式，使用like查询的话使用右模糊，使用组合索引的话，组合索引面前的字段要在查询条件中；
  • 避免使用select *的方式查询
  • 尽量用关联查询(join)代替子查询(关联查询不需要建立临时表效率会快点)
  • 大数据量分页查询可以用自增的id where id>= and <限制条件代替limit语句（深度分页效率很低）
  • 在数据库设计时不要让字段的默认值为NULL，在进行比较的时候，NULL 会参与字段比较，所以对效率有一部分影响，只要列中包含有NULL值都将不会被包含在索引中（除非是唯一值的域，可以存在一个NULL），复合索引中只要有一列含有NULL值，那么这一列对于此复合索引是无效的

 

为什么连接查询比子查询快

连接查询不需要建立临时表，子查询要建立临时表，查询完毕后，MySQL还需要撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

 

慢查询

分析MySQL语句查询性能的方法除了使用 EXPLAIN 输出执行计划，还可以让MySQL记录下查询超过指定时间的语句，我们将超过指定时间的SQL语句查询称为“慢查询”。

我们可以指定判定慢查询的时间：默认是10s

命令：show variables like "long%";

可以指定记录慢查询日志开关及日志位置：默认是关闭的

命令：show variables like "slow%";

如何处理慢查询问题：

查看慢查询日志，看慢查询触发的时间，和慢查询的sql;

如果是固定的时间，看是不是有定时任务导致那时候数据库压力比较大。看能不能调整定时任务单时间，尽量在mysql压力小的时候执行

如果时间分布比较均匀，就有几种方案进行处理，

  • 首先使用执行计划分析下sql看有没有优化的空间，尽量使用索引。
  • 表结构的索引看能不能优化，增加下索引
  • sql优化不了看能不能将功能才分不用一个sql处理，分成多个高性能的sql在程序中进行对结果处理
  • 看能不能加缓存缓解数据库压力
  • 做数据库服务的读写分离或进行分表等优化方式

mysql的事务

事务概念

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部不成功

事务特性

事务的特性(ACID)：

原子性（Atomicity）：指处于同一个事务中的多条语句是不可分割的。要么全部成功，要么全部失败。

一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。确保了数据库在事务处理过程中的数据正确性、完整性和逻辑一致性。比如：转账。a给b转账，转账前：a+b=2000块，转账后：a+b=2000

隔离性（Isolation）：多线程并发时，一个事务不应该被其他事务所干扰。（重点）

持久性（Durability）：提交的数据应该被永久的保存起来。比如存到磁盘上。

 

事务的原子性原理

MySQL通过undolog（撤销日志）实现了原子性，在将执行SQL语句时，会先写入undolog，再执行SQL语句，若SQL语句执行出错就会根据undolog中的记录来执行回滚操作，由此拥有原子性。

事务的一致性原理

产生并发不一致性问题主要原因是破坏了事务的隔离性

不同的隔离级别对应不同程度的一致性。保证隔离性就需要锁配合其他措施来进行

  • 读未提交：锁住事务涉及的行，操作完数据就释放锁
  • 不可重复度：锁住事务涉及的行，事务提交之后释放锁,使用mvcc来保证可以读到其他事务提交的数据，
  • 可重复读：锁住事务涉及的行，使用MVCC来保证同一个事务读一致性，事务提交之后释放锁
  • 串行化：锁住事务涉及的表

可重复读：采取的是MVCC并发版本控制来解决，MySQL第一次读的时候仍然会保持选择读最新提交事务的数据，当第一次之后，之后再读时，mysql会取第一次读取的数据作为结果。这样就保证了同一个事务多次读取数据时数据的一致性。

在事务中执行查询时，会创建一个一致性视图来确定应该读取哪个版本的数据。如果当前数据版本晚于视图创建时的版本，InnoDB会利用Undo log中的旧版本数据来构造一个符合视图要求的数据快照。对于不可重复读，每次读取都会生成一个视图所以可以读取到其他事务提交的，对于可重复读，只有第一次读的时候生成视图，所以读不到其他事务提交的。

事务的持久性原理

一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。具体实现原理就是在事务commit之前会将，redo log buffer中的数据持久化到硬盘中的redo log file重做日志文件，这样在commit的时候，硬盘中已经有了我们修改或新增的数据，由此做到持久化。

 

是否自动提交事务

MySQL中事务默认自动提交的，每当执行一条SQL，就会提交一个事务

Oracle 中事务默认 不自动提交，需要在执行SQL 语句后 通过commint 手动提交事务

如果事务交给spring管理，那么一个方法是一个事务

事务的隔离级别

事务隔离级别

脏读

不可重复读

幻读

读未提交（read-uncommitted）

是

是

是

不可重复读（read-committed）

否

是

是

可重复读（repeatable-read）

否

否

是

串行化（serializable）

否

否

否

mysql默认的事务隔离级别为repeatable-read(可重复读)

（

1、脏读：所有事务都可以看到其他未提交事务的执行结果：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

 

2、不可重复读：一个事务只能看见已经提交事务所做的改变，一个事务重复读时数据产生了变化：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

3、可重复读：MySQL 的默认事务隔离级别，在一个事务内做同样的查询，查询结果是一样的，

4、幻读：事务A在select获取条件数据后，没有提交事务前，事务B产生了同样条件的数据（如插入或者修改,因为事务A是select所以没有锁相关数据），但事务A提交前获取不到，如果事务A做出修改，修改的条件和查询的条件一样，会发现修改的数据数量比自己查到的多，这就是幻读。

）其实幻读很多时候是我们完全可以接受的。

 

可重复读的原理：

使用mvcc多版本并发控制来实现的

 

串行化说明：

事务顺序执行，可避免脏读、不可重复读、幻读，但效率最差。因为A事务执行时，要完全锁住在事务中涉及的数据表。

查看mysql设置的隔离级别：

select @@transaction_isolation;

幻读实例：

  • 事务A读取where age=10的数据查询到1条，事务B添加或修改后一条数据age也=10，事务A修改name=”xgss”where age=10最后返回成功了两条;发现有新加入的数据也被修改了；
  • 事务A读取where age=10的数据为空，事务B同样的条件读取也为空，事务A添加了一条数据，事务B也添加了一条，最后发现数据库里面为两条

如何处理，可以使用悲观锁select --for update，这样其他线程也select --fror update是会被阻塞，事务提交之后，其他线程再读就读到了最新值

 

select --for update：它不只给查询到行加锁，还加了间隙锁，间隙锁锁住的是符合查询条件的数据的两边数据的间隙，如age=10符合条件，比age小的是6，比age大的是15，那锁住的间隙就是age为（6,15）的区间

间隙锁是Innodb在可重复读提交下为了解决幻读问题时引入的锁机制

什么时候加间隙锁：

当使用唯一索引或主键来搜索唯一行的语句时，不需要间隙锁，select * from t where id = 10 for update; id字段有唯一索引，查询到一条数据就不加间隙锁；没有查询到数据就加间隙锁。

如果查询列没有索引或者非唯一索引会加间隙锁

开启事务进行update或delete时，会加间隙锁，所以我们插入符合update条件的数据时事务要等该事务提交之后进行提交

insert因为没有涉及到查询条件所以不会有间隙锁

mvcc说明

Multi-Version Concurrency Control多版本并发控制，广泛应用于数据库技术，像Oracle,PostgreSQL等也引入了该技术,主要用于事务处理过程中保持数据的一致性和事务之间的隔离性。是通过一种乐观锁的思想，维护数据的多个版本，以减少数据读写操锁的冲突，做到即使有读写冲突时也能做到不加锁，非阻塞并发读而这个读指的就是快照读

核心思想：利用各事务版本的数据快照来实现事务的隔离。

mvcc使用的隔离级别：

读已提交、可重复读。

MySQL中的读未提交和序列化并不需要MVCC机制，读未提交，直接读取别人未提交的数据，而序列化全程用加锁的方式，也用不上MVCC

读已提交：每一次进行普通读操作前都会生成一个读视图

可重复读：只在第一次进行普通的读会生成一个读视图

读视图：是一个用于确定事务在某一时刻能够读取到哪些数据版本的重要数据结构，当事务尝试读取一行数据时，它会根据读视图来判断该行数据的版本是否可见。如果数据的版本在读视图生成之前已经提交，那么该版本对于当前事务来说是可见的；否则，它是不可见的。通过读视图，MVCC能够实现非锁定读，允许多个事务同时读取数据而不会相互阻塞。每个事务都基于其自己的读视图来读取数据。

读视图中的内容：最小活跃事务(未提交的事务)的事务id，当前事务id,下一个要创建的事务id,当前活跃事务id列表。

读视图的用法：小于最小活跃事务id的事务代表是已经提交的可以读，和当前事务id一样的也可以读。其他是没有提交的，不能读。

 

innodb对mvcc的实现：

每当一个事务修改一行数据时，InnoDB 并不是直接修改原始数据，而是生成该行数据的一个新版本，并在新记录上设置上修改该数据的事务ID（称为“版本号”），为了支持回滚操作和提供旧版本数据的访问，InnoDB 会保留被修改行的旧版本数据在 Undo 日志中。新记录的回滚指针会设置成指向undo日志旧版本数据的地方，如果需要回滚事务或读取旧版本数据，InnoDB 可以通过这个指针找到相应的 Undo 日志记录。

  1. 事务成功提交：
    • 如果事务成功提交，那么该事务创建的新版本数据将成为数据库的最新状态，并对其他事务可见。
    • 原始数据版本（如果存在的话）仍然保留在数据库中，但是会被标记为不再活跃的版本，因为最新版本的数据已经提交了。
    • 回滚指针仍然存在于新版本数据中，用于支持后续可能需要的回滚操作或旧版本数据的读取。但是，由于新版本数据已经提交，其他事务通常不会通过回滚指针访问旧版本数据，除非它们需要读取历史数据或进行某种恢复操作。
    • 随着时间的推移，不再活跃的数据版本可能会被数据库的垃圾回收机制清理掉，以释放存储空间。
  2. 事务失败回滚：
    • 如果事务失败并回滚，那么该事务创建的新版本数据不会被提交到数据库，而是会被丢弃。
    • 原始数据版本将保持不变，仍然是数据库的最新状态。

其他事务读取数据时，只能读取比自己事务id小的数据。

事务id和回滚指针都是记录隐藏的列

每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向InnoDB 的事务系统申请的，是按申请顺序严格递增的。

插入时记录创建版本包记录为事务id,

修改时旧的记录的删除版本号设置为事务id,再创建一条新的记录，创建版本号设置为事务id

删除时把记录删除版本号设置为事务版本号

查询时当前事务版本号大于等于创建本版号，小于删除版本号的数据，即只能查询到事务开始时查询到的数据，事务开始后数据变化对它没有影响

undo log说明

当事务开始时，都创建一个新的Undo Log，每个事务都有自己的undo log信息

MySQL服务器在处理事务时，并不会为每个事务创建单独的Undo Log文件。相反，Undo Log记录通常会被存储在共享的Undo表空间或系统表空间中，而不是单独的文件中。

Undo表空间是MySQL用于存储Undo Log的物理空间，它可以是一个或多个文件组成。

MySQL还提供了对Undo Log记录的清理机制。随着时间的推移，当事务提交并且其Undo Log记录不再需要时，这些记录会被标记为可回收，并最终通过垃圾回收机制进行清理，以释放存储空间。

undolog怎么回滚事务的

  1. 获取回滚指针或起始位置：首先，系统需要确定回滚操作的起始点。这通常是通过获取事务的回滚指针或Undo Log的起始位置来实现的。
  2. 逆向扫描Undo Log：从Undo Log的末尾开始，系统按照事务操作的逆序扫描日志记录。这意味着它会首先处理最新（即最后记录）的修改，然后依次处理之前的修改。
  3. 撤销修改：
    • 对于INSERT操作，Undo Log中会记录插入的数据。在回滚时，系统会执行相应的DELETE操作来撤销这些插入的数据。
    • 对于UPDATE操作，Undo Log中会保存修改前的旧值。在回滚时，系统会使用这些旧值将数据还原到之前的状态。
    • 对于DELETE操作，虽然它本身没有对应的撤销操作（因为数据已经被删除），但Undo Log中通常会记录被删除数据的副本或相关信息，以便在需要时可以恢复这些数据。
  4. 跳过已提交事务的修改：在回滚过程中，如果系统遇到属于其他已经提交的事务的Undo Log记录，它会跳过这些记录，只处理当前回滚事务的Undo Log记录。
  5. 处理所有日志记录：系统按照逆序依次处理所有的日志记录，直到达到回滚指针位置或Undo Log的起始位置。这确保了事务的所有修改都被正确地撤销。
  6. 清除或标记已回滚的Undo Log记录：回滚完成后，为了保持数据库的整洁和性能，已经回滚的Undo Log记录可能会被清除或标记为已回滚状态。

undo log在mvcc中的应用

在mvcc提供支持读取一致性视图的作用，可以从undo log中查询到数据的历史版本。

事务对性能的影响

如果使用可重复读的隔离级别，事务提交前，事务涉及的行会被锁住不允许其他事务修改，这样就会影响到性能

 

mysql分区

指定分区列表达式，把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的，每个分区都有自己独立的物理存储位置

通过降低每次操作（读写）的数据数量来提高性能。

分区如何提升性能：

每个分区可以有自己的数据和索引存储，如果查询只涉及一个分区，MySQL可能只需要扫描该分区的索引条目。

分区类型
mysql支持的分区类型包括Range、List、Hash、Key，其中Range比较常用：

  • RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
  • LIST分区：需要先定义一个列表，根据列在哪个范围来分区。
  • HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
  • KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
  • COMPSITE分区：混合多种分区方式来进行分区
  • 自定义分区函数：支持用自定义的分区函数

分区限制

  • 主键或者唯一索引必须包含分区字段，如primary key (id,username),不过innoDB的大主键性能不好。
  • 很多时候，使用分区就不要在使用主键了，否则可能影响性能。
  • 每个表最多1024个分区，而且多分区会大量消耗内存。一般小于100个为好
  • 分区的表不支持外键，相关的逻辑约束需要使用程序来实现。
  • 分区后，可能会造成索引失效，需要验证分区可行性。

主键或者唯一索引必须包含分区字段：

这主要是出于数据一致性和查询效率的考虑，如果主键或唯一索引没有包含分区字段，MySQL 会在创建分区表时报错。

一致性：避免主键或唯一索引的唯一性失效，导致不同分区上有多条索引列值相同的记录

查询效率：包含分区字段的主键或唯一索引可以帮助MySQL更快地定位到数据所在的分区。避免全分区扫描

 

分区表为什么最多1024个分区:

主要是出于性能、管理和稳定性的考虑

 

主键包含分区字段建立分区举例：

CREATE TABLE orders (  
    order_id INT NOT NULL,  
    order_date DATE NOT NULL,  
    customer_id INT,  
    amount DECIMAL(10, 2),  
    PRIMARY KEY (order_id, order_date) -- 主键包含分区键 order_date  
)  
PARTITION BY RANGE (YEAR(order_date)) (  
    PARTITION p0 VALUES LESS THAN (1992),  
    PARTITION p1 VALUES LESS THAN (1993),  
    PARTITION p2 VALUES LESS THAN (1994),  
    -- ... 其他分区定义 ...  
);

 添加分区sql：

ALTER TABLE your_table_name  
ADD PARTITION (PARTITION new_partition_name VALUES LESS THAN (new_value));

删除分区sql:

ALTER TABLE your_table_name  
DROP PARTITION partition_name;

分区表达式


分区的数据迁移
生产环境大多是采用这种方式:新建一个和原来表一样的分区表，然后把数据从原表导出，接着导入新表，最后建立普通索引。

 

分区相比分表的缺点：

分区需要主键或唯一索引包括分区键，这样可能要修改原表结构和做响应的数据处理

 

Mysql分库分表

为什么进行分库分表

对于访问极为频繁且数据量巨大的单表，为了提高数据操作效率，减轻数据库压力，需要进行分表，分库

数据库分库分表概念

分库：

把原来的数据库的数据分为多个数据库：

1、原在一个数据库的一张表分成多个表到多个数据库中

2、原在一个数据库中多张表分到多个数据库中减少一个数据库中表的数量

分表：

将原来的一张表按照一定策略分成多个表存放在一个数据库或多个数据库中

分库分表：

即进行分库又进行分表

如何进行分库分表

可以自定义规则进行分表，利用merge存储引擎来实现分表

分库分表目前有很多的中间件可供选择，常用的有淘宝的中间件Cobar,当当开源的sharding-jdbc，蘑菇街的TSharding

 

分库分表需要考虑的问题：

路由字段

数据迁移

原系统需要修改的sql梳理改造及测试

 

分库分表要注意什么

原本跨表的事务变成了分布式事务；

由于记录被切分到不同的数据库和不同的数据表中，难以进行多表关联查询

不能不指定路由字段对数据进行查询

分库分表之后，如果我们需要对系统进行进一步的扩阵容（路由策略变更），将变得非常不方便，需要我们重新进行数据迁移。

 

分库分表的分布式事务如何处理

两阶段提交/三阶段提交

两阶段：先本地执行事务并确认可以成功，再统一提交事务

三阶段：先确认可以成功，在本地执行事务，再统一提交

（三阶段可以避免两阶段事务提交是事务管理器宕机，导致一直无法提交事务）

补偿事务

针对每个操作，都要注册一个与其对应的确认和补偿（撤销）操作。

本地消息表

本地消息表这种实现方式应该是业界使用最多的，其核心思想是将分布式事务拆分成本地事务进行处理，一个事务的执行最后本地存储一个消息内容为处理中并发送一个消息给下一个事务，下一个事务执行把执行结果消息发送给前面事务，前面的事务来修改状态；（如果消息发送失败可以重试）

分库分表的路由

可以使用一致性hash

mysql锁

锁的概念作用

数据库管理系统中采用锁的机制来管理事务，当多个事务同时修改同一数据时，只允许持有锁的事务修改该数据，其他事务只能“排队等待”，直到前一个事务释放其拥有的锁。

为了保证数据的正确性一致性

锁的类别有那些

按加锁方式有乐观锁，悲观锁

按锁的级别有行级锁，表级锁，页锁

按锁的性质有共享锁，排他锁

加锁方法

加锁的方式分为乐观锁和悲观锁

乐观锁

乐观锁是对其他事务持有乐观态度，乐观的认为其他事务不会对数据做出更改。在去修改数据时才进行判断数据是否修改过。

乐观锁的特点先进行业务操作，不到万不得已不去拿锁。

 

乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持。一般的做法是在需要锁的数据上增加一个版本号，或者时间戳，然后按照如下方式实现：

 

  1. SELECT data AS old_data, version AS old_version FROM …;
  2. 根据获取的数据进行业务操作，得到new_data和new_version
  3. UPDATE SET data = new_data, version = new_version WHERE version = old_version

if (updated row > 0) {

    // 乐观锁获取成功，操作完成

} else {

    // 乐观锁获取失败，回滚并重试

}

 

乐观锁是否在事务中其实都是无所谓的

其底层机制是这样：在数据库内部update同一行的时候是不允许并发的，即数据库每次执行一条update语句时会获取被update行的写锁，直到这一行被成功更新后才释放。

如果更新失败即可认为老版本的数据已经被并发修改掉而不存在了，此时认为获取锁失败，需要回滚整个业务操作并可根据需要重试整个过程。

乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能

悲观锁

悲观锁是对其他事务持有悲观态度，悲观的认为如果不拿到锁其他事务就会对数据做出更改，因此要先确保获取锁成功再进行业务操作。

通常所说的“一锁二查三更新”即指的是使用悲观锁。通常来讲在数据库上的悲观锁需要数据库本身提供支持，即通过常用的select … for update操作来实现悲观锁。当数据库执行select for update时会获取被select中的数据行的行锁，因此其他并发执行的select for update如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果。select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。（当事务释放前另一个线程对冲突的数据进行select for update会在查询的时候阻塞update, insert, delete也会阻塞，事务提交后才可以查询到数据，对普通的select没有影响）

（insert语句如果是产生符合select for update条件的数据会被阻塞，等待释放锁才能插入成功）

 

悲观锁的锁级别：当使用主键或索引的情况下是行锁，如果索引失效是表锁

对性能的影响：在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。

select for update是在事务中显示的给select加锁的方式，默认事务中select是不加锁的

 

锁得性质

读锁/共享锁/s锁

它是共享的，或者说是相互不阻塞的。多个客户端在同一时间可以同时读取同一资源，互不干扰。同一个资源上可以加多个共享锁，共享锁之间不冲突，共享锁和排他锁冲突

对某一资源加共享锁，本事务可以读该资源，其他事务也可以读该资源（也可以再继续加共享锁，即共享锁可多个共存），但无法修改资源。要想修改就必须等所有共享锁都释放完之后。

 

对于一般的Select语句，InnoDB不会加任何锁

可以自己显示的加共享锁

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 平时我们是很少去手动加共享锁的

 

写锁/排他锁/独占锁/x锁

它是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这是为了确保在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源，保证安全。

对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB 会自动给涉及数据集加排他锁

事务可以通过以下语句显式给记录集加共享锁或排他锁：

 

排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE。其他 session 可以查询该记录，但是不能对该记录加共享锁或排他锁，而是等待获得锁

意向共享锁和意向排他锁

意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

意向锁之间是互相兼容的（意向共享锁与意向共享锁，意向共享锁和意向排他锁，意向排他锁和意向排他锁之间都兼容）

意向锁与表级锁的共享锁和表级别的排他锁之间存在不兼容关系

 

意向共享锁（IS）

意向排他锁（IX）

共享锁（S）

兼容

互斥

排他锁（X）

互斥

互斥

意向锁不会与行级的共享 / 排他锁互斥

 

当一个事务遇到自己需要的资源已被排他锁占用的时候，该事务可以在锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。

意向锁时表级别的

select …… for update 是先加意向排他锁，然后加上排他锁

 

意向锁的作用：

意向锁是为了提高锁的兼容性判断效率。

如果另一个任务试图在该表级别上应用共享或排它锁，则受到由第一个任务控制的表级别意向锁的阻塞。第二个任务在锁定该表前不必检查各个页或行锁，而只需检查表上的意向锁。

 

共享锁S和排他锁X都是行锁，事务在对某一行加锁时需要先找到该行，然后判断和已有锁是否兼容，寻找行的过程比较费时，因此mysql采用意向锁的形式将粒度粗化为表级锁，这样在申请上锁时，先用表级意项锁和已有锁比较是否兼容，如果不兼容直接阻塞，否则才去判断行级锁和已有锁的兼容性。

 

 

锁的级别

MySQL有三种锁的级别：页级、表级、行级。

MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；

InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

 

而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，Innodb也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。

行级锁

行锁的劣势：开销大；加锁慢；会出现死锁

行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强

加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁：

 

共享锁：select * from tableName where … + lock in share more

排他锁：select * from tableName where … + for update

当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。

表级锁

表锁的优势：开销小；加锁快；无死锁

表锁的劣势：锁粒度大，发生锁冲突的概率高，并发处理能力低

加锁的方式：自动加锁。查询操作（SELECT），会自动给涉及的所有表加读锁，更新操作（UPDATE、DELETE、INSERT），会自动给涉及的表加写锁。也可以显示加锁；

InnoDB默认采用行锁，在未使用索引字段查询时升级为表锁。

即便你在条件中使用了索引字段，MySQL会根据自身的执行计划，考虑是否使用索引(所以explain命令中会有possible_key 和 key)。如果MySQL认为全表扫描效率更高，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。

 

第一种情况：全表更新。事务需要更新大部分或全部数据，且表又比较大。若使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突。

 

第二种情况：多表查询。事务涉及多个表，比较复杂的关联查询，很可能引起死锁，造成大量事务回滚。这种情况若能一次性锁定事务涉及的表，从而可以避免死锁、减少数据库因事务回滚带来的开销。

页锁

开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发处理能力一般。只需了解一下。

一个页会涉及到多行数据

全局锁

全局锁会把整个数据库实例加锁，命令为 flush tables withs read lock ，

将使数据库处于只读状态，其他数据写入和修改表结构等语句会阻塞，一般在备库上做全局备份使用。

 

锁的释放

事务开始之后申请锁。锁的申请请求是放在一个队列当中的，先申请的先得到。

锁只有在执行commit或者rollback的时候才会释放，并且所有的锁都是在同一时刻被释放。

 

死锁

两个事务互相等待对方释放需要的锁又都不释放就就会造成死锁

MySQL有两种死锁处理方式：

等待，直到超时（innodb_lock_wait_timeout=50s）。

发起死锁检测，主动回滚一条事务，让其他事务继续执行。

由于性能原因，一般都是使用死锁检测来进行处理死锁。

 

MySQL的InnoDB表死锁检测时如果发现用户在操作过程中产生死锁，该处理程序立刻通过撤销操作来撤销其中一个事务，以便使死锁消失。这样就可以使另外一个事务获取对方所占有的资源而执行逻辑操作。

Innodb会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚，而让另外一个较大的事务成功完成。（选择插入更新或者删除的行数最少的事务回滚）

我们如何查看死锁：

通过mysql的命令SHOW ENGINE INNODB STATUS 可以显示最近一次的死锁日志

mysql的死锁检查默认是开启的，会自动处理死锁。我们也可以手动去kill掉其中一个事务来处理死锁。

mysql读写分离

主从复制是什么

【复制】是【读写分离】和【负载均衡】的基础

简单来说，就是主(master)库把对数据改动的操作保存在binlog日志中，而从(slave)库会隔一个时间间隔就会请求binlog日志，主数据库发给从数据库，从数据库根据binlog日志更新自己的数据

(所以读到的数据是有延迟的，这个延迟取决于从(slave)库设置的时间间隔)

binlog记录的操作的sql，在事务提交前保存到磁盘

 

负载均衡是什么

就是一种充分让多台数据库服务器发挥效率的技术，比如有1台数据库负责写，5台数据库负责读。对于这5台读的数据库来说，通过某种方式实现在每台数据库负载都不高的时候，能让读的请求尽量均匀地分布在这5台数据库上，或者某一台很高的时候，尽量把读的请求放到其他负载相对低的数据库上。

读写分离的概念是什么

读写分离就是只在主服务器上写数据，只在从服务器上读数据

基本的原理是让主数据库处理事务性操作，而从数据库处理非事务性操作，然后再采用主从复制来把master上的事务性操作同步到slave数据库中。

为什么做读写分离

提升数据库的并发负载能力

提高数据查询效率，提升用户体验

怎么做主从复制

修改主和从/etc/my.cnf设置服务器id默认是1，一般取IP最后一段

重启两台服务器的mysql

在主服务器上建立帐户并授权slave:

在从服务器Slave上配置主服务器的ip用户密码等

启动从服务器复制功能

也可以应用一定的方法做到不重启主库的前提下进行建立从库

怎么做读写分离

1.基于程序代码内部实现 

在代码中根据select,insert进行路由分类，这类方法也是目前生产环境应用最广泛的，优点是性能好，因为在程序代码中实现，不需要曾加额外的设备作为硬件开支，缺点是需要开发人员来实现，运维人员无从下手。

2.基于中间代理层实现 

代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有三个代表性程序。

 

1）mysql-proxy 为mysql开源项目，通过其自带的lua脚本进行SQL判断，虽然是mysql的官方产品，但是mysql官方不建议将其应用到生产环境

2）Atlas。是由 Qihoo 360, Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。

 

  • Amoeba （变形虫）由陈思儒开发，曾就职与阿里巴巴，该程序由java语言进行开发，阿里巴巴将其应用于生成环境，它不支持事物和存储过程

怎么做负载均衡

比如：同样是读请求如何分布到多台从服务器上去查询，

1：使用程序轮询方式有程序控制具体的在那个服务器上来查询，这种方式每次增加或减少从服务器就要修改程序配置以适应后端的数据库数量不太灵活。

  • 软件（中间层），使用一些专门的软件或硬件来完成这样的工作。常用的软件如：LVS   ，Haproxy ，MaxScale 硬件：F5 这样的硬件设备不管使用那种方式大家一定要记住在具体的生产环境中一定要先经过基准测试。

存储过程

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字和参数值来调用执行它。

语法

DELIMITER //  
CREATE PROCEDURE procedure_name([parameter_list])  
BEGIN  
   -- SQL语句块  
END //  
DELIMITER ;
  • DELIMITER：用来定义存储过程体中的语句结束符。因为MySQL默认的语句结束符是分号(;)，而存储过程中可能包含多条SQL语句，并且每条语句都以分号结束，所以为了避免冲突，我们需要临时改变语句结束符。在存储过程定义结束后，再将结束符恢复为分号。
  • CREATE PROCEDURE：用于创建存储过程。
  • procedure_name：存储过程的名称。
  • [parameter_list]：存储过程的参数列表，参数是可选的。如果存储过程需要参数，可以在这里定义。参数的定义格式为：[IN | OUT | INOUT] param_name param_type，其中IN表示输入参数，OUT表示输出参数，INOUT表示输入输出参数。
  • BEGIN ... END：存储过程的主体，包含要执行的SQL语句块。

优缺点：

优点：

  • 在数据库服务器上预编译和存储，因此执行速度通常比直接执行SQL语句更快。
  • 减少网络流量，因为只需要调用存储过程的名称和传递参数，而不是整个SQL语句。
  • 存储过程可以封装复杂的SQL逻辑，并在多个地方重复使用。

缺点：

  • 由于是在数据库端进行维护的，团队协作的时候容易遗忘维护。
  • 可移植性差，从一种数据库迁移到另一种数据库时可能需要进行大量修改。
  • 相对于应用程序代码，存储过程的调试可能更加复杂和困难（存储过程如果报错提示信息有限，不好确定错误原因）。

存储过程与自定义函数区别

  1. 返回值：
    • 存储过程：不直接返回数据给调用者，而是通常通过输出参数来返回数据。它们主要用于执行操作，比如更新数据、插入数据等，并且可以有多个输出参数。
    • 自定义函数：必须返回一个值给调用者。函数通常用于计算或转换数据，并返回一个单一的结果值。
  2. 调用方式：
    • 存储过程：通过CALL语句来调用。
    • 自定义函数：可以在SQL语句中像内置函数一样使用，比如SELECT my_function(column_name) FROM table_name;。
  3. 定义语法：
    • 存储过程：使用CREATE PROCEDURE语句来定义。
    • 自定义函数：使用CREATE FUNCTION语句来定义。
  4. 使用场景：
    • 存储过程：通常用于执行一系列复杂的操作，这些操作可能涉及多个SQL语句和条件逻辑。它们也常用于封装特定的业务逻辑。
    • 自定义函数：用于在查询中执行简单的计算或转换任务，它们通常用在SELECT、WHERE等子句中以返回单个值。

event事件

event即时间触发器，用于在特定时间或时间间隔，触发相关的SQL语句或存储过程。

查看开启状态：SHOW VARIABLES LIKE 'event_scheduler';

MySQL 5.7 中默认值是 off,MySQL 8.0 中默认值是 on

查看特定数据库的事件：

use xgss;
SHOW EVENTS;

查看事件详情：

SHOW CREATE EVENT event_name;

语法

CREATE
    [DEFINER = user]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO action;

其中，event_name是事件的名称，schedule是事件的调度时间表达式（可以指定事件的触发时间和重复间隔），action是事件要执行的SQL语句或存储过程。

DEFINER用于指定所属用户，默认是当前用户

IF NOT EXISTS 可选，代表如果不存在进行创建，存在就不进行创建

ON COMPLETION [NOT] PRESERVE 可选，代表执行完是否保留，默认不保留（执行完毕后自动drop该事件），如果事件每天都跑，即使设置的是不保留，计划任务执行完毕后也不会自动drop该事件。

ENABLE | DISABLE | DISABLE ON SLAVE: 定义事件创建以后是开启还是关闭，以及在从上关闭。如果是从服务器自动同步主上的创建事件的语句的话，会自动加上DISABLE ON SLAVE。默认是ENABLE

COMMENT 'string' 可选，备注信息

PowerDesigner

PowerDesigner简介

PowerDesigner是一个用于建模设计工具集，利用Power Designer可以制作数据流程图、概念数据模型、物理数据模型，对数据库建模，能够根据物理数据模型生成sql语句，根据数据库表生成物理数据模型等。

概念数据模型

"概念数据模型"－CDM（Conceptual Data Model）

概念数据模型描述的是独立于数据库管理系统(DBMS)的实体定义和实体关系定义

物理数据模型

"物理数据模型"-PDM(Physical Data Model)

物理数据模型是在概念数据模型的基础上针对目标数据库管理系统的具体化。

 

posted @ 2022-10-21 13:35  星光闪闪  阅读(125)  评论(0)    收藏  举报