MySql整理

varchar 和 char 的区别

1.varchar（5）可变长度，当字符长度未达到5则使用字符长度。
2.char(5)固定长度，当字符长度未达到5则使用空格代替。

 

简单说一说delete、truncate、drop的区别

1.delete：是删除部分表数据，删除后会产生数据空洞。
2.truncate：快速删除表数据，不删除表结构，重置主键自增。
3.drop：卸载整个表，包括表数据、表结构、索引。

 

什么是内联接、左外联接、右外联接

1.内联接：返回两个表中满足匹配条件的行，实验=或<>。
2.左联接：以左表为基准关联右表的数据，返回左表所有的行，以及右表满足关联条件的行。
3.右连接：同左联接

 

删除表数据后表的大小却没有变动,这是为什么?

1.delete并不是真正的物理删除，而是逻辑删除，delete操作会把当前的数据占用空间腾处理，以便后面插入的数据可以复用。
2.些可以被复用但尚未被使用的磁盘空间，在表中就形成了所谓的 ‌“空洞”‌
3.想要真正删除数据就只能通过重建表

mysql的InnoDB引擎和Myisam引擎的区别

 

1.存放方式上：InnoDB的数据存放在两个文件上.frm(表结构) 、.IDB(索引和数据），Myisam存放在三个文件上.frm（表结构）、.MYD（行数据）、.MYI（索引值），InnoDB少一个文件项目Myisam就减少IO操作。
2.B+树的存储上：InnoDB叶子节点存放的是索引值和数据行，Myisam叶子节点存放的是索引值，Myisam数据查询的时候需要根据叶子节点的索引值再去读取行数据文件才能加载出对应的数据。
3.索引区别：InnoDB根据非聚簇索引查询时需要找到叶子节点对应的聚簇索引才能找到对应的行数据，Myisam不依赖聚簇索引，Myisam的主键索引和普通索引数据结构是一样的都是B+树都可以直接根据索引值直接读取到数据行。
4.事务上的区别：InnoDB支持事务，默认使用行锁，支持表锁，适合并发并发写入，Myisam不支持事务，仅支持表锁，适合读多写少的操作。

 

为什么mysql要选择B+树来存储索引

 

1.磁盘IO层面

B+树在IO层面可以在数据查找的时候减少磁盘IO的数据和更多的读取到数据从而提升查询效率。

1）其余的数据结构如哈希表、二叉树、红黑树、B-树
2）哈希表本质是hash散列表精准查询效率高，但是在范围查询的时候效率低因为要一个一个比对，且存在hash碰撞的问题。
3）二叉树、平衡二叉树、红黑树，根节点下只能有两个叶子节点，数据量大的数据树的高度会很高，进行数据查询的数据会增加IO次数。
4）B-树存放发是行数据和相邻页子节点的指针，行数据可能会很大导致B-树在3、4层的时候存放不了太多的数据，假设每行数据大小是1KB，一次磁盘IO读取16条数据，3次IO读取到16*16*16 = 4096条数据，数据量大的时候树的高度会增加。

 

2.数据容量

1.B+数非叶子节点存储索引值和其他子节点的指针叶子节点存储数据记录的引用（聚簇索引）和相邻叶子节点的指针。
2.三层B+数据大于存储2000w的数量，B+树非叶子节点索引值最大占用8字节，数据指针占用6字节，mysql一次磁盘独写大于加载16KB的数据，相当于一次加载1168行数据，假设一行数据大小是1KB，一页大概存储16KB/1KB=16条数据，如果B+树三层大概是1168*1168*16 = 2200w的数据。

 

索引有哪些分类

1.按照物理结构：聚簇索引和非聚簇索引。
2.按照逻辑结构：主键索引、唯一索引、普通索引、联合索引

 

聚簇索引与非聚簇索引

1.mysql在innodb引擎下存储数据必须要和索引列绑定在一起，因为B+树的节点中要存放索引值，索引mysql数据库在建表的时候一般都有主键索引，没有主键索引找唯一索引，没有唯一索引mysql会在数据行后自动插入一个rowId.
2.聚簇索引是与数据列绑定的。
3.非聚簇索引是不与数列绑定的。

 

如何设计性能优良的索引

1、索引列占用的空间越小越好。（索引列小可以提升磁盘IO效率，B+树可存储更多的数据，索引维护成本更小）
2、​最后使用有序的字段做主键索引。（UUID、身份证这写会影响范围查询和造成页分裂）
​3、选择索引列的时候尽量选择离散度高的列作为索引列，离散度的计算公式count(distinct(column_name)) / count(*)，这个值越大，那么越适合做索引
4、频繁更新的字段,不要创建索引。(会增加索引的维护成本，修改时要修改数据行还有修改索引，B+树频繁重组，造成碎片空间)
5、在where后的order by字段上添加索引
6、在join on的条件字段上添加索引
7、索引的个数不要过多，会增加索引的维护成本
​8、索引列在设计的时候最好不为NULL
​9、可以使用列前缀作为索引列

 

什么情况下会造成索引失效

1.不遵循最联合左原则
2.like使用左侧模糊查询导致全局扫描
3.索引列使用函数或表达式

‌主要原因‌：索引（如B+树）是按照列的原始值有序存储的，当你在查询条件中对索引列进行函数调用（如 YEAR(birthdate)）或运算（如 price * 0.9），MySQL无法直接利用索引树的有序结构进行快速查找
‌常见导致失效的场景‌包括：
‌函数操作‌：例如 SELECT * FROM users WHERE YEAR(birthdate) = 1990;
‌运算表达式‌：例如 SELECT * FROM products WHERE price * 0.9 < 50;

4.字段类型不匹配

mysql中查询条件值和索引的类型不匹配时会进行隐式转换，如果sql中的字段是varchar类型，但是查询条件给的是123456，mysql会看到值是整数，进而转换成数字，拿数字和整数进行匹配进而索引失效

5.使用!=、<>、NOT IN、NOT LIKE等否定条件时，通常会导致索引失效。

MySQL的索引是为了快速查找满足条件的记录，而否定条件通常意味着要查找的范围太大。MySQL优化器可能判断使用索引的代价大于全表扫描，因此选择不使用索引

6.0R条件导致索引失效：优化器决策成本过高或索引合并局限性。

优化器决策成本过高：如使用多个不同列时，无索引或索引分布不均匀或无知，优化器可能会觉的走全表扫描效率更够
索引合并局限性：如果有多个查询条件，需要多次索引并合并结果，成本可能超过了全表扫描

 

7.索引列数据分布不均匀导致索引失效
8.order by导致索引失效：

1.排序条件不包含索引有可能失效
2.排序顺序和索引顺序不一致
3.排序包含函数

 

如何查看SQL语句是否使用索引

 

1.通过在SQL语句前添加EXPLAIN关键字，可以查看MySQL如何执行该查

2.重点字段信息

‌1）type‌访问表数据的方式
‌2）possible_keys‌：查询可能使用的索引
3‌）key‌：实际使用的索引，如果该列为空，说明没有使用索引
4‌）rows‌：预估需要扫描的行数，数值越小性能越
5）extra扩展信息

3.type访问表数据的方式

按性能从好到差排序为：system > const > eq_ref > ref > range > index > ALL
‌system‌：表仅一行数据（系统表特例）
‌const‌：通过主键或唯一索引等值查询，最多返回一行
‌eq_ref‌：表连接时使用主键或唯一索引关联（如JOIN ... ON a.id=b.id）
‌ref‌：使用非唯一索引的等值查询
‌range‌：索引范围扫描（BETWEEN、IN等操作）
‌index‌：全索引扫描（比ALL快，但仍需遍历索引树）
‌ALL‌：全表扫描（性能最差）

4.extra扩展信息

‌Using index‌：覆盖索引，无需回表
‌Using where‌：存储引擎返回数据后需服务器层过滤，查询使用了索引，但是where子句的条件无法完全在储引擎筛选出来，需要在服务层在进行过滤
‌Using filesort‌：表示排序操作主要在 ‌MySQL服务器层‌进行。
‌Using temporary‌：创建临时表（常见于GROUP BY）
‌Using index condition‌：使用索引条件下推(ICP)优化：表示查询‌部分利用了索引‌但需要进一步筛选数据
‌Using join buffer‌：使用连接缓存

具体参考：https://www.cnblogs.com/sunnycc/p/19163629

回表、索引覆盖、最左匹配原则、索引下推

1.回表：mysql在数据查询的时候会根据普通索引查询出数据行，然后根据数据行对应的聚簇索引去查找对应的数据。
2.索引覆盖：查询的数据索引值能完全覆盖，无需回表。 （explain的索引扩展参数的‌Using index‌就是表示索引覆盖）
3.最左原则：在使用联合索引的时候必须要有联合索引的第一字段，否则不走索引，因为创建索引是已第一字段进行排序，其余字段在第一字段排序的基础上进行二次排序。
4.索引下推：查询的数据有一部分走了索引，但是需要在mysql的存储引擎中进行二次过滤。（explain的索引扩展参数的‌‌Using index condition‌就是表示索引覆盖）

‌

Using index‌和Using index condition‌的区别

1.using where:数据在存储引擎过滤后需要在mysql服务层再进行过滤，存储引擎过滤的条件可以是索引列也可以不是。
2.using where condition:数据通过索引列查询出后直接在存储引擎进行过滤，执行效率高于using where。
3.两者的区别：在于是否在服务层进行二次过滤

 

 

什么是事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位。事务是逻辑上的一组操作，要么都执行，要么都不执行。

 

事务的四大特性ACID

原子性：在进行事务操作的适合多步操作，要么同时成功要么同时失败，不可分割‌。
一致性：事务执行前后，数据库必须保持逻辑一致状态‌，官方解释“事务必须使数据库从一个一致性状态变换到另外一个一致性状态”
以转账为例子，A向B转账，假设转账之前这两个用户的钱加起来总共是2000，那么A向B转账之后，不管这两个账户怎么转，A用户的钱和B用户的钱加起来的总额还是2000，这个就是事务的一致性
隔离性：多个事务之间的操作不互相影响，Mysql的隔离性是通过MVCC和锁实现的
持久化：事务提交后，数据必须要持久化保存。

备注：原子性、隔离性、持久化都是为了实现一致性

 

事务并发带来的问题

脏读：一个事务读取到另外一个事务未提交的数据。举个例子，假设张三正在执行一个事务，在事务未提交之前，他读取了李四的事务中的某个数据。然而，如果李四随后回滚了他的事务，那么张三所读取到的数据实际上是不存在的或是无效的

不可重读：同一事务内多次读取同一数据，因其他事务的修改（UPDATE/DELETE）导致结果不一致‌
幻读：同一事务内多次读取同一数据，因其他事务插入（INSERT）新数据导致结果集行数变化‌

备注：不可重复读和换读的区别：一个是针对的数据结果，一个针对的是数据集。

事务的隔离级别

读未提交（read uncommitted)： 允许事务读取到其他事务未提交的数据，会出现脏读。
读已提交 (read committed)： 允许事务读取到其他事务已提交的数据，oracle的默认隔离级别，解决脏读，会出现不可重复读。
可重复读 (repeatable read)：一个事务多次读取同一数据得到的结果一致，解决不可重复读，但是可能会出现幻读。
串行化 (rerializable)：读写均加表锁，让事务一个一个串行执行。

 

mysql数据库的日志文件及其作用

 

binlog: 记录的是对数据的‌逻辑操作‌,用于数据备份、主从复制，读写分离，事务提交后写入。
errorlog: 错误日志。
relaylog: 中继日志，在做主从的适合master会产生binlog然后IO Thread会把binlog写入到slave的relaylog里，然后slave的sql Thread会把relaylog的数据写入到slave中。
slowlog: 慢日志，记录慢sql语句。
redolog:‌ 物理日志，记录的是对数据文件的‌物理操作‌，核心作用是确保事务的持久性，mysql会将内存中的log buff写到redlog，然后在将redlog记录的数据写到磁盘，这样即使在断电后也能通过redoglog保证断电前内存和磁盘的数据是一致的。
undolog: 历史版本数据，用于数据回滚和多版本控制，undolog在事务提交后且不需要时系统会自动清理。insert事务提交后undolog日志会立马清除，update事务操作会不会立马清除，需要判断是否还要别的事务在引用历史版本的清除，有则不删除，无则进行清理，同理delete也是。

 

mysql的两阶段提交

1.mysql的两阶段提交发送在redolog和binlog写入阶段，本质是保证redolog和binlog数据一致性。
2.两阶段提交分为prepare和commit两个阶段。

prepare阶段：mysql会把事务操作记录到redolog文件中，并把状态置为prepare。
commit阶段：mysql会在事务提交后把数据记录到binlog文件中，并把redlog中的状态置为commit。

3.数据恢复处理
如果在写入redolog前程序崩溃，此时redolog和binlog中都没有数据，满足数据一致性。
如果在prepare阶段后程序崩溃，此时redolog是没有提交的，此时拿着redolog中prepare状态的事务Id去binlog中寻找，binlog中是没有的，事务回滚。
如果在commit阶段程序崩溃，此时redolog和binlog中都有数据，事务正常提交。

mysql中redolog、binlog、undolog的写入顺序是什么

1.在聊日志写入顺序的时候需要结合mysql的二阶段提交。
2.事务开始后会先把修改前的历史版本数据写入到undolog中，用于事务失败后的回滚。
3.事务执行阶段会把修改记录持续写入redolog中，状态记录为repare。
4.事务提交阶段会先把事务的逻辑操作写入到binlog中，然后将redolog中的状态改为commit阶段。
5.所有写入顺序应该是undolog、redolog(prepare阶段)、binlog、redolog（commit阶段）

 

mysql的当前读和快照读是什么

当前读：mysql在做读取的时候读取到的是最新的数据，而且其他事务不能操作否则会影响结果，这样都会提前进行加锁处理，insert,update,delete ,select for update
快照读：读取到的是历史数据而不是最新的数据，当有一个事务A和事务B时，A做了一次查询操作，B对A事务查询的数据做了修改然后提前，这时A再次查询看到的数据还是一样的，这就是快照读。

 

MVCC是什么

MVCC全称为Multi-Version Concurrency Control，即多版本并发控制它的核心目的是实现数据库的隔离级别（读已提交‌和‌可重复读‌的隔离级别） ，提高数据库并发性能，通过更好的方式处理读写冲突，做到即使有读写冲突时也能不加锁，实现非阻塞并发读。

 

MVCC实现原理

 mvcc三大组件：隐藏字段、undolog、read view

 

隐藏字段

InnoDB引擎中每行数据包含三个隐藏字段

DB_ROW_ID:隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段
DB_TRX_ID：最近更新事务ID，记录插入这条记录或最后一次修改该记录的事务ID，
DB_ROLL_PTR:回滚指针，指向undolog的旧版本数据，用于MVCC多本版和事务回滚

undolog: 被称之为回滚日志，表示在进行insert，delete，update操作的时候产生的方便回滚的日志。

​ 当进行insert操作的时候，产生的undolog只在事务回滚的时候需要，并且在事务提交之后可以被立刻丢弃当进行update和delete操作的时候，产生的undolog不仅仅在事务回滚的时候需要，在快照读的时候也需要，所以不能随便删除，只有在快照读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

​ 下面我们来看一下undolog生成的记录链

​ 1、假设有一个事务编号为1的事务向表中插入一条记录，那么此时行数据的状态为：

（当数据发生更新和删除操作的时候都只是设置一下老记录的deleted_bit，并不是真正的将过时的记录删除，因为为了节省磁盘空间，innodb有专门的purge线程来清除deleted_bit为true的记录，如果某个记录的deleted_id为true，并且DB_TRX_ID相对于purge线程的read view 可见，那么这条记录一定时可以被清除的）

​ 2、假设有第二个事务编号为2对该记录的name做出修改，改为lisi

​ 在事务2修改该行记录数据时，数据库会对该行加排他锁

​ 然后把该行数据拷贝到undolog中，作为 旧记录，即在undolog中有当前行的拷贝副本

​ 拷贝完毕后，修改该行name为lisi，并且修改隐藏字段的事务id为当前事务2的id，回滚指针指向拷贝到undolog的副本记录中

​ 事务提交后，释放锁

​ 3、假设有第三个事务编号为3对该记录的age做了修改，改为32

​ 在事务3修改该行数据的时，数据库会对该行加排他锁

​ 然后把该行数据拷贝到undolog中，作为旧纪录，发现该行记录已经有undolog了，那么最新的旧数据作为链表的表头，插在该行记录的undolog最前面

​ 修改该行age为32岁，并且修改隐藏字段的事务id为当前事务3的id，回滚指针指向刚刚拷贝的undolog的副本记录

​ 事务提交，释放锁

​ 从上述的一系列图中，大家可以发现，不同事务或者相同事务的对同一记录的修改，会导致该记录的undolog生成一条记录版本线性表，即链表，undolog的链首就是最新的旧记录，链尾就是最早的旧记录。

read view

 

读视图，MVCC多版本控制的核心，主要用于控制读已提交、可重复读事务下数据的可见性
trx_list:表示read view生成时系统正在活跃的事务列表。
up_limit_id:表示trx_list中最小活跃事务列表
low_limit_id:表示read view生成时要生成的下个事务id。

 

可见性算法

1、首先比较DB_TRX_ID < up_limit_id,如果小于，则当前事务能看到DB_TRX_ID所在的记录，如果大于等于进入下一个判断
2、接下来判断DB_TRX_ID >= low_limit_id,如果大于等于则代表DB_TRX_ID所在的记录在Read View生成后才出现的，那么对于当前事务肯定不可见，如果小于，则进入下一步判断
3、判断DB_TRX_ID是否在活跃事务中，如果在，则代表在Read View生成时刻，这个事务还是活跃状态，还没有commit，修改的数据，当前事务也是看不到，如果不在，则说明这个事务在Read View生成之前就已经开始commit，那么修改的结果是能够看见的。

MVCC的整体处理流程

实验一

现象：使用命令行开多窗口验证当事务2修改进行事务提交，事务1是可以看到事务2的修改记录的根据read view分析
1.当事务1查询的时候DB_TRX_ID是2，次数DB_TRX_ID>up_limit_id所以进行规则2
2.根据规则2判断DB_TRX_ID<low_limit_id所以进行规则3
3.根据规则3判断DB_TRX_ID不在trx_id中所以是可见的
结论：根据可见性算法分析和我们多窗口得到的结果是一致的

 

实验二

现象：使用命令行开多窗口验证当事务2修改进行事务提交，事务1第二次是看不到事务2的修改记录的
1.当事务1查询的时候DB_TRX_ID是2，次数DB_TRX_ID>up_limit_id所以进行规则2
2.根据规则2判断DB_TRX_ID<low_limit_id所以进行规则3
3.根据规则3判断DB_TRX_ID在trx_id中所以是不可见的
结论：根据可见性算法分析和我们多窗口得到的结果是一致的

猜想：两次实验结果中read view的数据没变，只是查询时间不一致，最终导致两次结果不一致，猜测是什么控制了read view的生成实际，答案是事务的隔离级别控制了read view的生成时机

read view的生成时机：在RC隔离级别下，是每个快照读都会生成并获取最新的Read View,而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是同一个Read View

事务1第二次的数据的过程：在可重复读的隔离级别下，事务1先查询了一次，事务2进行了数据行的修改，这个时候事务1再次查询，根据可见性算法分析得到两次查询的数据是一样的，第二次查询是根据隐藏字段的DB_ROLL_PTR读取的undolog历史版本链中的数据

 

 

隔离级别与MVCC的匹配关系

隔离级别	MVCC实现	读操作类型	读取版本	适用场景
‌读未提交（Read Uncommitted）‌	不使用MVCC	当前读	最新版本	无隔离，允许脏读
‌读已提交（Read Committed）‌	基础MVCC	快照读	提交版本	避免脏读，允许不可重复读
‌可重复读（Repeatable Read）‌	完整MVCC	快照读	启动时版本	避免脏读和不可重复读
‌序列化（Serializable）‌	无MVCC，使用锁	当前读	最新版本	最高隔离，避免幻读

幻读出现的原因是什么，如何解决

如果在同一个事务同时出现了快照读和当前读，那么就会出现幻读问题，只有快照读的时候不会出现幻读，幻读只能通过加锁解决。

 

 mysql都有那些锁

 

mysql锁大致分为三类：全局锁、表锁、行锁

全局锁

锁定整个数据库实例，使数据库处于只读状态，通过FLUSH TABLES WITH READ LOCK命令加锁，主要用于全库逻辑备份等场景

‌表级锁‌
‌表级锁‌锁定整张表，MyISAM引擎默认使用表锁，InnoDB在显式使用LOCK TABLES命令时也会使用。
表读锁（共享锁）：允许并发读取表数据，但是会阻塞写。
表写锁（排他锁）：阻塞所有表操作。

 

 行锁

 

1.行锁都是加在索引上的，如果表中有聚簇索引直接加在聚簇索引上，如果没有会加在系统默认生成的聚簇索引上

2.假设数据库中有聚簇索引1、5、8、10

记录锁：锁定索引的单条记录，是加在1、5、8、10这个索引上的，避免不可重复读。
间隙锁：锁定的是数值之间的间隙（-∞，1），（1，5），（5，8），（8，10），（10，+∞），防止在此范围插入新的数据，避免幻读。
临建锁：记录锁+间隙锁，定的是数据记录及其间隙（-∞，1]，（1，5]，（5，8]，（8，10]，（10，+∞]，避免幻读。

备注：脏读是通过MVCC多版本控制+事务隔离级别解决的。

 

记录锁可通过什么添加

自动添加：UPDATE、DELETE、INSERT

显示添加：SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE

 

MYSQL的简单架构(后续补充)

 

 

 

SQL语句执行流程

 

mysql是分为三层：客户端、server层、存储引擎
客户端：向mysql发生sql语句的地方，可视化客户端、业务应用客户端。
server：连接器、分析器、优化器、执行器
1.连接器：与mysql服务建立连接、身份验证
2.分析器：词法和语法分析器，词法分析器将mysql语句拆分成各个token，如select、update、where，语法分析器将sql语句生成语法树。
3.优化器：优化器主要用于根据抽象语法树生成不同的执行计划，判断那种执行成本小。同时进行 等量交换、常量交换、逻辑转换等等
4.执行器：执行优化器优化后的执行计划、调用执行引擎接口
5.存储引擎：myIsam、InnoDB，存储引擎决定数据的存储方式，如InnoDB存储在两个文件，myIsam存储在三个文件上

 

分库分表之后,id 主键如何处理

UUID：生成全局唯一字符串标识符，优点是本地生成、无性能瓶颈、全球唯一缺点是存储空间大、查询效率低
数据库自增 id : 通过为每个分表设置不同的起始值和相同的步长来避免ID冲突，这种方案实现简单，但缺点是分表数量固定后难以扩展
利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
Twitter的snowflake算法 ：Github 地址：https://github.com/twitter-archive/snowflake。
美团的Leaf分布式ID生成系统 ：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据库、Zookeeper等中间件。