MySQL结构体系

https://cloud.tencent.com/developer/article/1857505

数据库引擎

MyISAM：表锁，并发量较小，不支持事务，不支持外键（性能更优，占用的存储空间少，MYISAM拥有全文索引的功能，这可以极大地优化LIKE查询的效率，查询性能高）

InnoDB：行锁，并发量较大，支持事务，支持外键。是聚集索引，使用B加Tree作为索引结构。

  1. 如果要执行大量 select 操作，应该选择 MyISAM
  2. 如果要执行大量 insert 和 update 操作，应该选择 InnoDB
  3. 大尺寸的数据集趋向于选择 InnoDB 引擎，因为它支持事务处理和故障恢复。数据库的大小决定了故障恢复的时间长短，InnoDB 可以利用事务日志进行数据恢复，这会比较快。主键查询在 InnoDB 引擎下也会相当快，不过需要注意的是如果主键太长也会导致性能问题。

Tips

安装mysql 一般用压缩包安装，而不用exe安装

mysql是数据库管理系统

锁

mysql锁

行锁与表锁

当插入数据时，就锁定表，这叫做”锁表”；当更新数据时，就锁定行，这叫做”锁行”。

mysql的行锁是基于索引加载的

表锁响应的是非索引字段，即全表扫描，

更新数据库数据时，如果没有触发索引，则会锁表，锁表后再对表做任何变更操作都会导致锁冲突，所以表锁的锁冲突概率较高。

连表查询

7种 用基础的3种left join、right join、inner join搞定7种

MySQL的两张表的七种Join查

自连接

自连接查询就是以类似多表对比的方式，实现对同一张表内数据进行复杂的关系表示或关系处理。

自连接查询

子查询

子查询

MySQL函数

常用函数

聚合函数

MD5加密

SQL语句书写

1
2
3
4
5
6
7
8

DESC `cms_help`  --显示表的结构
SHOW CREATE TABLE `cms_help` --查看创建数据表的语句 
SHOW CREATE DATABASE `mall`  --查看创建数据库的语句 
ALTER --对表的列进行操作
TRUNCATE TABLE 'test'
DELETE FROM 'test'
--都能删除数据,不会删除表结构，TRUNCATE重新设置自增类，计数器归零，不会影响事务
SELECT DISTINCT --去重

DELETE删除的问题，重启数据库

InnoDB 自增从1开始（存在内存中，断电即失）

MyISAM 继续上一个自增（存在文件中，不会丢失）

modify不能用来字段重命名

change不能修改字段类型和约束

外键一般用程序是实现，一切外键概念必须在应用层解决

分页limit 起始值 页面的大小和排序order by asc/desc

image-20210620214341616

分库分表

垂直分库

垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用。

它带来的提升是：

  • 解决业务层面的耦合，业务清晰
  • 能对不同业务的数据进行分级管理、维护、监控、扩展等
  • 高并发场景下，垂直分库一定程度的提升IO、数据库连接数、降低单机硬件资源的瓶颈

业务场景：原有的SELLER_DB(卖家库)，分为了PRODUCT_DB(商品库)和STORE_DB(店铺库)，并把这两个库分散到不同服务器

水平分库

水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。

它带来的提升是：

  • 解决了单库大数据，高并发的性能瓶颈。
  • 提高了系统的稳定性及可用性。

业务场景：经过垂直分库后，数据库性能问题得到一定程度的解决，但是随着业务量的增长，PRODUCT_DB(商品库)单库存储数据已经超出预估。粗略估计，目前有8w店铺，每个店铺平均150个不同规格的商品，再算上增长，那商品数量得往1500w+上预估，并且PRODUCT_DB(商品库)属于访问非常频繁的资源，单台服务器已经无法支撑。此时该如何优化？尝试水平分库，将店铺ID为单数的和店铺ID为双数的商品信息分别放在两个库中。

垂直分表

垂直分表定义：将一个表按照字段分成多表，每个表存储其中一部分字段。

用户在浏览商品列表时，只有对某商品感兴趣时才会查看该商品的详细描述。因此，商品信息中商品描述字段访问频次较低，且该字段存储占用空间较大，访问单个数据IO时间较长；商品信息中商品名称、商品图片、商品价格等其他字段数据访问频次较高。

由于这两种数据的特性不一样，因此他考虑将商品信息表拆分如下：

将访问频次低的商品描述信息单独存放在一张表中，访问频次较高的商品基本信息单独放在一张表中

它带来的提升是：

1.为了避免IO争抢并减少锁表的几率，查看详情的用户与商品信息浏览互不影响

2.充分发挥热门数据的操作效率，商品信息的操作的高效率不会被商品描述的低效率所拖累。

通常我们按以下原则进行垂直拆分:

1 把不常用的字段单独放在一张表;

2 把text，blob等大字段拆分出来放在附表中;

3 经常组合查询的列放在一张表中;

水平分表

水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。

它带来的提升是：

  • 优化单一表数据量过大而产生的性能问题
  • 避免IO争抢并减少锁表的几率

业务场景：按照水平分库的思路对他把PRODUCT_DB_X(商品库)内的表也可以进行水平拆分，其目的也是为解决单表数据量大的问题

主从复制，读写分离

主从复制原理（数据层面的东西）

在不同的服务器上配置数据库，并通过修改配置文件构成主从复制

读写分离（shardingJDBC：业务层面的东西）

读只会使用2台从服务器

写只会在主服务器

分库分表查询语句会查询所有库和所有表的数据

shardingJDBC还提供分布式事务

1
2
3
4
5
6
7

分库分表后，本地事务已经不能保证事务，需要分布式事务
## 本地事务
@Transactional（rollbackFor = Exception.class）
## Sharding提供的分布式事务
## 扣钱-->强一致性 ； 订单 --> 弱一致性，柔性
@Transactional（rollbackFor = Exception.class）
@ShardingTransactionType(TransactionType.XA)

事务以及隔离级别

事务是一系列操作的集合，这些操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的最小工作单元。 事务（原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）） 创建一个名为transfer1的显式事务，该事务将学生李勇校园卡账户上的500元转到张立的校园卡账户上，然后用select语句查看campus_card表检查是否转账成功。要求：（1）先基于学生姓名获得其学号信息；（2）转账前需要先检查李勇的校园卡账户是否有足够的待转金额，如果若李勇账户余额不足则打印输出‘余额不足，不能转账.’然后将该事务回滚。

事务ACID理解

索引（有序数组、Hash、二叉搜索树、B+树）

MySQL索引背后的数据结构及算法原理

创建索引可以大大提高系统的性能。 第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 第二，可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。

创建索引和维护索引要耗费时间，这种时间随着数据 量的增加而增加。 第二，索引需要占物理空间

主键索引 顾名思义该类索引由表的主键组成，从左到右由小到大排序。一个 Innodb 存储表只有一张主键索引表（聚集索引）。 普通索引 最为平常的一种索引，没有特别限制。 唯一索引 该索引的字段不能有相同值，但允许有空值。 组合索引 由多列字段组合而成的索引，往往是为了提升查询效率而设置。 聚集索引就是按照每张表的主键构造一棵 B+树，叶子节点存放的是表的完整行记录。非聚集索引的叶子节点不包含行记录的全部数据。Innodb 存储引擎的非聚集索引的叶子节点的内容为主键索引值。

索引不是越多越好

不要对经常变动的数据加索引

小数据量的表不需要加索引

索引一般加在常用来查询的字段上

函数或表达式会使索引失效

最左前缀原理（查询条件没有指定索引第一列，用不到索引；询条件用到了索引中列的精确匹配，但是中间某个条件未提供，会出现“坑”；通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀）

使用辅助索引或“隔离列”的优化方法，将emp_no与from_date之间的“坑”填上。

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引

“BETWEEN”实际上相当于“IN”是多值匹配，谨慎地区分多值匹配和范围匹配

索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值，择性的取值范围为(0, 1]，选择性越高的索引价值越大

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

存储过程

性能好；将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。 exec 执行存储过程

触发器

响应一个特殊表格中的某些事件而自动执行的程序代码 触发器是一种特殊类型的存储过程，不由用户直接调用。创建触发器时会对其进行定义，以便在对特定表或列作特定类型的数据修改时执行。 保证数据完整性的一种方法 DML触发器(Insert,delete,update)、DDL(create,drop,alter)触发器和登录触发器 当用户从course表删除元组时检查被删除元组的课程号（cno）是否在学生选课表sc中出现过，如果出现过说明该课程已被学生选，为了保证数据库的完整性，这时应该将表sc中对应该课程号的选课信息给删除。 过多的触发器使得数据逻辑变得复杂。触发器的功能逐渐在代码逻辑或事务中替代实现，更符合OO思想。建议：使用触发器需慎重。

视图

视图是一个虚拟的表，是一个表中的数据经过某种筛选后的显示方式，视图由一个预定义的查询select语句组成； 特点：视图中的数据并不属于视图本身；视图的数量没有限制，但是命名不能和视图以及表重复，具有唯一性。视图可以被嵌套，一个视图中可以嵌套另一个视图。 视图不能索引，不能有相关联的触发器和默认值 好处：①简化数据操作：视图可以简化用户处理数据的方式。 ②着重于特定数据：不必要的数据或敏感数据可以不出现在视图中。 ③视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问权限。

数据库设计

1、需求分析：了解用户的数据需求、处理需求、安全性及完整性要求； 2、概念设计：通过数据抽象，设计系统概念模型，一般为E-R模型； 3、逻辑结构设计：设计系统的模式和外模式，对于关系模型主要是基本表和视图； 4、物理结构设计：设计数据的存储结构和存取方法，如索引的设计； 5、系统实施：组织数据入库、编制应用程序、试运行； 6、运行维护：系统投入运行，长期的维护工作。

范式

关系型数据库设计：三大范式的通俗理解

  1. 第一范式：要求数据库表的每一列都是不可分割的原子数据项。
  2. 第二范式：（2NF）属性完全依赖于主键
  3. 第三范式：（3NF）属性不依赖于其它非主属性，在2NF基础上消除传递依赖 BCNF 在第三范式的基础上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖。

数据迁移

情景:把一个数据库的一些表迁移到另外一个新的数据库的表中. 同时, 在迁移过程中, 修改一些数据. 此处, 修改数据, 举几个例子:

  1. Y/N, Yes/No 改成 TRUE/FALSE
  2. 添加一些新列, 并填充默认值
  3. 原来的列, 名字全部修改. 比如 Event ID 改成 XXEvent ID
  4. 筛选归类. 比如原来某列的数据是1-100的分数, 迁移后数据转换成"不及格"(<60),“及格”(60<x<70), 优良..等等
  5. 拆分原来的列. 原来的列里日期数据是DD/MM/YYYY HH:mm:ss, 拆成两列, 一列是MM/DD/YYYY, 另一列是HH:mm:ss 等等等等 现在只能拿到 CSV 格式数据. 一个 CSV 是一个表格, 有很多很多表格. 一个表格大概4W 到40W 行不等. 光是一个 CSV 大小,就有100来兆. 请问除了用 excel, 有什么好的办法或者软件或者服务可以处理? 数据迁移工具

SQL注入

preparedstatement防止sql注入的本质，把传递进来的参数当作字符

假设其中存在转义字符，比如说‘会被直接转义

数据库连接池

聊聊索引失效？失效的原因是什么？

InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身

索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。只能根据前缀进行比较。

1、左模糊匹配：前缀都是模糊的，排序了也没有，需要全表扫描

2、有计算表达式：id+1=10，它只排序id的值，而不排序id+1，虽然这个优化比较好做，但估计不做，为啥呢？自个揣摩

3、有mysql内置函数：length(name)=6，它只排序name的值，而不排序length(name)

4、聚合索引：比如（a,b,c）它是在排序a的基础上再排序b再排序c，如果只过滤b、c，那也需要全表扫描

5、索引隐式转换：MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。

比如你一个phone 字段存的是varchar 但过滤时传的是数字，那么mysql就会自行将 where phone = 1300000001转换成CAST(phone AS signed int) = 1300000001; 此情形就和3一样了

6、有or字段；假设过滤的条件一个是主键，一个是普通列。由于or的含义只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

https://juejin.cn/post/7056640917016412197

Mysql-MVCC多版本并发控制详解

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。

mvcc 是多版本并发控制，通过生成记录的历史版本解决幻读问题，并提高数据库的性能，无锁实现读写并发操作。

mvcc 的实现主要是通过三个隐藏字段，undo log以及readView 实现的。

三个隐藏字段分别是隐藏主键，事务ID，回滚指针。

undo log是各个事务修改同一条记录的时候生成的历史记录，方便回滚，同时会生成一条版本链。

readView是事务在进行快照读的时候生成的记录快照，用于判断数据的可见性。

readView 可见性判断规则。

https://juejin.cn/post/7020422614552150052