MySql

MySql语句

去重 distinct

1
select * distinct from user_info

限制查询结果的条数 limit

接受一个参数 (返回最大的记录行数目),两个参数(偏移量,返回最大的记录行数目)

1
2
3
4
5
6
select * from user_info limit 10
查询前十个
select * from user_info limit 10,15
查询第11-20,偏移了10个
select * from user_info limit 10,-1
查询

## 存储引擎

innoDB :

支持 事务 外键

myISAM

很少的更新和删除 对并发要求不高,被nosql的mongodb取代

memory

通常临时表以及缓存,被nosql的redis取代

索引

索引属于存储引擎层

B+Tree索引

二叉树缺点 :顺序插入会形成链表,查询性能大大降低

红黑树: 大数据情况下,层级较深,检索速度慢

B-Tree(多路平衡查找树,五阶每个节点最多存储4个key,五个指针)

B+Tree(叶子节点存储数据,每个叶子节点通过指针指向下一个,从而形成一个单向链表

mysql中增加了指向相邻叶子节点的链表指针,形成了带有顺序指针的B+Tree,双向链表)

Hash索引

底层数据结构使用后hash表实现,只有精确匹配索引列的查询才有效,不支持范围查询

将键值通过hash算法换算成新的hash值,映射到相应的槽位上,然后存储在hash表中,hash冲突通过链表解决

full_text

索引分类

聚集索引

仅有一个,叶子节点保存这一行的数据

二级索引

叶子节点保存聚集索引的数据

SQL性能分析

Sql执行频率

SHOW GLOBAL STATUS　LIKE　＇名称＿＿＿＿＿＿＿＇

进行模糊查询，可以查询到不同命令的查询次数

慢查询日志开启

vi /etc/my.cnf

slow_query_log=1开启慢查询日志

long_query__time=2将查询时间超过2的设定为慢查询

SQL性能分析

profile详情

select @@have_profiling

set profiling = 1;

show profiles 查询所有命令耗时情况

show profile for query query_id 查询指定query_id的SQL语句各个阶段的耗时情况

show profile cpu for query query_id 多展示一个cpu的使用情况

explain执行计划

explain/desc select 字段列表 FROM 表明 WHERE 条件 展示mysql如何执行select语句信息

id 越大越先执行,从上往下执行

type 表示连接类型,性能由好到差为NULL,system,const,eq_ref,ref,range,index,all

possible_key 可能用到的索引

keys 用到的索引

key_len 索引的长度

rows 预估执行查询的行数

filtered 表示返回结果的行数占需读取行数的百分比,越大性能越好

索引的使用

1. 最左前缀法则：联合索引需要最左边的字段,如果中间缺失字段,那缺失字段之后的字段全部失效
2. 范围查询：出现范围查询，范围查询右侧的列索引失效
3. 索引列运算：在索引列进行运算索引失效，性能会降低
4. 字符串要加引号：不加 ‘ ‘ 会进行类型转换,导致索引失效
5. 模糊查询：如果对如果前面模糊（%模糊）索引失效，后面（模糊%）不失效
6. or连接的条件：or条件中包含不在索引字段的语句,索引不生效
7. 数据分布影响：如果mysql评估使用索引比全表更慢，索引不生效
8. SQL提示：use index 建议使用某个索引，ignore忽略某个索引，force index强制使用某个索引
9. 覆盖索引：减少*的使用，尽量使用在索引中能找到的列性能更高效，否则需要回表查询再次进行聚集索引性能低
10. 前缀索引：只将字符串的一部分前缀建立索引。create index id_xxxx on table_name(column(n)) 根据选择性（不重复的索引值和数据表的记录总数的比值 count(distinct xxx)/count(*)）
11. 单列索引和联合索引：涉及到多个查询条件时，建议建立联合索引，而非单列索引

索引设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引。
2. 针对于常作为查询条件(where)、排序(orderby)、分组(groupby)操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值，请在创建表时使用NOTNULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

SQLJOINS

ON 和 WHERE 在 SQL 中都用于设置条件，但它们在连接（JOIN）操作中的作用是不同的。

1. ON：在 JOIN 操作中，ON 关键字用于设置连接条件。例如，在你的查询中，ON Employees.id=EmployeeUNI.id 是连接 Employees 表和 EmployeeUNI 表的条件，即只有当两个表中的 id 相等时，才会将这两个表的行连接在一起。
2. WHERE：在 JOIN 操作中，WHERE 关键字用于过滤结果集，即只返回满足特定条件的行。如果你在查询中使用 WHERE Employees.id=EmployeeUNI.id，那么只有当 Employees 表和 EmployeeUNI 表中的 id 相等时，才会在结果集中返回这些行。

总的来说，ON 关键字定义了如何连接两个表，而 WHERE 关键字定义了如何过滤这些连接后的结果。在某些情况下，使用 ON 和 WHERE 可能会得到相同的结果，但在其他情况下，结果可能会有所不同，特别是在执行外连接（如 LEFT JOIN 或 RIGHT JOIN）时。

• 内连接（INNER JOIN）只返回两个表中都有匹配的行。
• 左连接（LEFT JOIN）返回左表（这里是 Employees 表）的所有行，如果右表（这里是 EmployeeUNI 表）中没有匹配的行，则结果集中的对应列将为 NULL。
• WHERE 子句返回满足特定条件的行，这里的条件是 Employees.id 等于 EmployeeUNI.id。

##

mysql串讲

MySQL 基础知识

熟练使用MySQL，了解数据库原理以及索引、日志、事务、MVCC，有一定sql调优经验。

MySQL 架构层次

这次呢我们再换一个切入的角度来了解MySQL，我们知道从Mysql的物理直观层面主要是连接器，服务器还有存储引擎三层，顶层就只是对于MySQL键位连接，安全的一些内容，核心自然在其他两位身上，服务器就是在操作sql嘛，那底层的话就是存储引擎层了，存储引擎就是拿到结果进行存储，统一封装接口供给上层去使用就可以了。

存储引擎

其内部就是存储引擎，重头戏肯定是innodb,其他的话，MyISAM不支持事务，而Memory速度快，innodb的话会更全面一点。innodb的话我们通常使用show engine innnodb status可以看其当前状态。这个命令提供了大量的关于 InnoDB内部操作的信息，包括事务、锁、缓冲池（16kb大小的缓冲页）、日志文件、错误日志、死锁检测和其他性能相关的数据。

事务与日志

操作的过程中，查就直接拿，增删改就需要日志操作了，首先我们可以把一条sql当作一个事务，所以日志和事务是有很大关联的。首先为了保证事务的原子性，有一个两阶段提交，也就是sql语句不是立马提交的，有一个提交方式。

两阶段提交

我们首先来说这个两阶段提交，因为事务提交成功是以binlog成功写入为标志，有两个阶段，一个是准备阶段，一个是提交阶段。准备阶段是把事务操作记录到redolog中设置为准备阶段，第二阶段是把事务操作记录到binglog中，设置为提交。这个redolog是顺序追加到磁盘的。如果缓冲池中的数据在刷新到磁盘时发生错误（如磁盘故障），这并不会影响已经记录到redo日志中的事务。InnoDB存储引擎可以通过重放redo日志来恢复数据，确保数据的一致性。

binlog 的作用

另外binglog变更日志首先是存在任何存储引擎的，在数据一致性当中，可以通过cannal伪装成MySQL的从节点（Slave），订阅主节点的binlog去达到缓存一致性。如果数据库发生故障，可以通过binlog来恢复数据，因为它记录了所有数据更改的历史。在主从复制中，从节点(Slave)通过读取主节点(Master)的binlog来复制数据更改，从而保持与主节点的数据同步。

Undo 日志与 MVCC

除此之外Undo日志在InnoDB存储引擎中扮演着双重角色，它不仅用于事务回滚，保证事务的原子性，还在实现MVCC时形成了一个版本链，确保了事务的隔离性。当执行SELECT语句时，MVCC机制会根据当前事务的一致性快照来决定返回记录的哪个版本，这个快照包含了事务开始时的活跃事务列表，通过遍历版本链并检查事务ID，来控制数据版本的可见性。

锁机制

此外，为了解决幻读问题，InnoDB结合了MVCC和Next-Key锁，这种锁机制会根据查询条件自动退化成记录锁或间隙锁，其中记录锁用于精确匹配的查询条件，而间隙锁则用于范围查询，确保事务在操作过程中数据的一致性和完整性。

事务与隔离级别

上面我们似乎提到了日志交以及提交方式，还有事务锁等mysql中的关键字，那我们应该来归纳一下他们之间的联系。提交方式决定了事务何时结束，从而影响了锁的持续时间，日志是事务变更的手段，锁是控制并发的工具，事务定义了操作的范围和边界。那事务的话遵循ACID的特性也就是原子，一致，持久性以及隔离性。

在mysql的隔离级别中，主要有四种情况：

• 脏读的隔离级别是读未提交，这个隔离级别并不会进行加锁。
• 不可重复读的隔离级别是读已提交，这个情况一般会对于读取的数据上加共享锁，并在事务提交后释放这些锁，这可以防止脏读，但可能导致不可重复读，因为其他事务可能会在当前事务期间修改数据。
• Mysql默认的是可重复读，也就是会出现幻读的情况，mysql会使用间隙锁来锁定索引记录。
• 最后是序列化，隔离级别是串行化，这里主要是通过排他锁来进行，但会显著降低性能，所以提出了多版本并发控制工具MVCC。

MySQL 事务机制总结

MySQL通过结合提交方式、日志、锁和隔离级别，提供了一个复杂但高效的机制来保证事务的ACID特性，同时优化了并发性能和数据一致性。

死锁分析与解决

所以这些内容深入了解之后我们就可以回答一些场景题，例如如何解决死锁——mysql执行了事务，不是立马处理的，他有二阶段提交当时，隔离级别对应不同锁的策略和使用范围。锁去控制并发，所以死锁先看提交方式，再看隔离级别，然后看看innodb状态，里面仔细看看分析分析再从底层解决，innodb检测机制关闭，关闭表锁，再往上解决，减少小事务，考虑禁用表级锁定。应用层就重试机制，最小化锁范围，定期检查SHOW ENGINE INNODB STATUS。

服务器层

再来说说上层，mysql存储引擎层上一层也就是服务器层在8.0之前是有一个缓存机制的，会查询是否有查询语句查询过，也就是缓存命中，但是在8.0之后取消了因为任何对数据库的写操作（如INSERT、UPDATE、DELETE) 都会导致相关的查询缓存失效所以没什么用。

SQL 解析与优化

服务器层核心是是语法解析器，用来解析sql语句，顺序分别是form定表on过滤join链接表，where条件过滤，group by分组还有having, 然后是select选择指定列，最后去重，排序，限制行数解析完之后，再到优化器，分析查询语句，生成执行计划，关键字是explain，得到的字段有每个操作ID，查询的类型表名，连接类型还有索引，以及必须检查的行数等。

SQL 优化

这里就涉及到了sql优化，在子查询中模糊查询的常见用法有3种：

• 模糊匹配后面任意字符：like '张%'
• 模糊匹配前面任意字符：like '%张'
• 模糊匹配前后任意字符：like '%张%'

但是这三种只有第一种可能会使用到，因为%匹配任意字符，所以只要%后面有索引中的字符，索引就可以被用来快速定位。

还有 LIMIT offset,size 语句进行分页查询时，如果offset值较大，确实可能会导致SQL执行变慢。这是因为LIMIT语句需要从存储引擎层获取更多的数据，然后由服务器层进行过滤，只保留最后需要的size条记录，就比如limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。

索引优化

索引分主键索引（聚簇索引，叶子节点存放的是整行数据）和二级索引（叶子节点存放的是主键的值）把查询条件，转移回到主键索引树，那就可以减少回表次数啦。转移到主键索引树查询的话，查询条件得改为主键id了，之前SQL的 update_time 这些条件咋办呢？抽到子查询那里面。即从二级索引中查到主键后，再根据主键去主键索引中查找实际数据。

其实对于慢sql优化，我们一般查询执行计划，建立正确的索引，避免不必要的连接，限制查询结果集大小，以及冗余字段：假设有商品表和订单表，显示每个订单对应的商品名称和单价。如果每次查询都需要通过商品表来查询，显然会影响查询性能。在这种情况下，可以在订单表中添加冗余字段，将商品名称和单价存储在订单表中，以便每次查询时直接使用冗余字段的值，不涉及联表查询，提高查询性能。

冗余字段的注意事项

（需要注意的是必须记得商品名称在订单表中，在更新商品名称时，将这个冗余的字段也加入到更新的程序中）订单表（修改后）订单ID，商品ID，数量，商品名称【冗余字段】，单价【冗余字段】，但是，这种设计有一个重要的问题需要考虑：数据的一致性。因为商品名称和单价现在存储在两个地方，所以当你更新商品信息时，你需要确保两个地方的数据都被更新。这通常需要在应用程序层面实现逻辑来保证数据的一致性。

索引底层与优化

可以看到，其实不使用es优化最大的就是使用索引，索引的底层是B+树，叶节点存放键值，叶子节点存放数据指针并用链表相连接，而索引失效的原因在函数，运算是一定会失效的，但对索引的列操作是不一定失效，模糊查询也不一定。

在索引的分类中，平常使用的有唯一索引，组合索引以及普通索引，据索引与数据行的存储方式，索引还可以分为聚簇索引和非聚簇索引。组合索引是遵循最左匹配原则，如果使用了组合索引中的多个列，那么查询条件必须从索引的最左边开始，直到查询条件与索引列完全匹配。

索引覆盖

在sql优化中索引覆盖它是指查询中使用的所有列都包含在索引中，这样查询就可以直接从索引中获取数据，而不需要访问数据行。这通常发生在覆盖索引查询中，可以显著提高查询性能。所以在实际应用中，为了提高查询性能，我们可以根据查询模式来设计索引，尽量让查询能够使用索引覆盖。

整理后的内容按照逻辑进行了合理分段，保持了原文的所有技术细节和信息。

熟练使用MySQL，了解数据库原理以及索引、日志、事务、MVCC，有一定sql调优经验

这次呢我们再换一个切入的角度来了解MySQL

我们知道从Mysql的物理直观层面主要是连接器，服务器还有存储引擎三层，顶层就只是对于MySQL键位连接，安全的一些内容，核心自然在其他两位身上，服务器就是在操作sql嘛，那底层的话就是存储引擎层了，存储引擎就是拿到结果进行存储，统一封装接口供给上层去使用就可以了，其内部就是存储引擎，重头戏肯定是innodb,其他的话，MyISAM不支持事 务，而Memory速度快，innodb的话会更全面 一点，innodb的话我们通常使用show engine innnodb status可以看其当前状态。这个命令提供了大量的关于 InnoDB内部操作的信息，包括事务、锁、缓冲池（16kb大小的缓冲页）、日志文件、错误日志、死锁检测和其他性能相关的数据。操作的过程中，查就直接拿，增删改就需要日志操作了，首先我们可以把一条sql当作一个事务，所以日志和事务是有很大关联的，首先为了保证事务的原子性，有一个两阶段提交，也就是sql语句不是立马提交的，有一个提交方式，我们首先来说这个两阶段提交，因为事务提交成功是以binlog成功写入为标志，有两个阶段，一个是准备阶段，一个是提交阶段，准备阶段是把事务操作记录到redolog中设置为准备阶段，第二阶段是把事务操作记录到binglog中，设置为提交，这个redolog是顺序追加到磁盘的。如果缓冲池中的数据在刷新到磁盘时发生错误（如磁盘故障），这并不会影响已经记录到redo日志中的事务。InnoDB存储引擎可以通过重放redo日志来恢复数据，确保数据的一致性。另外binglog变更日志首先是存在任何存储引擎的，在数据一致性当中，可以通过cannal伪装成MySQL的从节点（Slave），订阅主节点的binlog去达到缓存一致性，如果数据库发生故 障，可以通过binlog来恢复数据，因为它记录了所有数据更改的历史。在主从复制中，从节点(Slave)通过读取主节点(Master)的binlog 来复制数据更改，从而保持与主节点的数据同步。除此之外Undo日志在InnoDB存储引擎中扮演着双重角色，它不仅用于事务回滚，保证事务的原子性，还在实现MVCC时形成了一个版本链，确保了事务的隔离性。当执行SELECT语句时，MVCC机制会根据当前事务的一致性快照来决定返回记录的哪个版本，这个快照包含了事务开始时的活跃事务列表，通过遍历版本链并检查事务ID，来控制数据版本的可见性。此外，为了解决幻读问题，InnoDB结合了MVCC和Next- Key锁，这种锁机制会根据查询条件自动退化成记录锁或间隙锁，其中记录锁用于精确匹配的查询条件，而间隙锁则用于范围查询，确保事务在操作过程中数据的一致性和完整性。上面我们似乎提到了日志交以及提交方式，还有事务锁等mysql中的关键字，那我们应该来归纳一下他们之间的联系，提交方式决定了事务何时结束，从而影响了锁的持续时间，日志是事务变更的手段，锁是控制并发的工具，事务定义了操作的范围和边界。那事务的话遵循ACID的特性也就是原子，一致，持久性以及隔离性，在mysql的隔离级别中，主要有四种情况，脏读的隔离级别是读未提交，这个隔离级别并不会进行加锁。不可重复读的隔离级别是读已提交，这个情况一般会对于读取的数据上加共享锁，并在事务提交后释放这些锁，这可以防止脏读，但可能导致不可重复读，因为其他事务可能会在当前事务期间修改数据。Mysql默认的是可重复读，也就是会出现幻读的情况，mysql会使用间隙锁来锁定索引记录，最后是序列化，隔离级别是串行化，这里主要是通过排他锁来进行，但会显著降低性能，所以提出了多版本并发控制工具MVCC，所以 MySQL通过结合提交方式、日志、锁和隔离级别，提供了一个复杂但高效的机制来保证事务的ACID特性，同时优化了并发性能和数据一致性。所以这些内容深入了解之后我们就可以回答一些场景题，例如如何解决死锁一—mysql执行了事务，不是立马处理的，他有二阶段提交当时，隔离级别对应不同锁的策略和使用范围。锁去控制并发，所以死锁先看提交方式，再看隔离级别，然后看看innodb状态，里面仔细看看分析分析再从底层解决，innodb检测机制关闭，关闭表锁，再往上解决，减少小事务，考虑禁用表级锁定。应用层就重试机制，最小化锁范围， 定期检查定期检查SHOW ENGINE INNODB STATUS；。再来说说上层，mysql存储引擎层上一层也就是服务器层在8.0之前是有一个缓存机制的，会查询是否有查询语句查询过，也就是缓存命中，但是在8.0之后取消了因为任何对数据库的写操作（如INSERT、UPDATE、DELETE) 都会导致相关的查询缓存失效所以没什么用，服务器层核心是是语法解析器，用来解析sql语句，顺序分别是form定表on过滤join链接表，where条件过滤，group by分组还有having, 然后是select选择指定列，最后去重，排序，限制行数解析完之后，再到优化器，分析查询语句，生成执行计划，关键字是explain，得到的字段有每个操作ID，查询的类型表名，连接类型还有索引，以及必须检查的行数等，这里就涉及到了sql优化，在子查询中模糊查询的常见用法有3种，模糊匹配后面任意字符：like’张%’，模糊匹配前面任意字符：like’%张’，模糊匹配前后任意字符：like’%张%。但是这三种只有第一种可能会使用到，因为%匹配任意字符，所以只要%后面有索引中的字符，索引就可以被用来快速定位。还有 LIMIT offset,size 语句进行分 页查询时，如果offset值较大，确实可能会导致SQL执行变慢。这是因为LIMIT语句需要从存储引擎层获取更多的数据，然后由服务器层进行过滤，只保留最后需要的size条记录，就比如limit 100000,10，就会扫描100010行，而1imit 0.10，只扫描10行。索引分主键索引（聚簇索引，叶子节点存放的是整行数据）和二级索引（叶子节点存放的是主键的值）把查询条件，转移回到主键索引树，那就可以减少回表次数啦。转移到主键索引树查询的话，查询条件得改为主键id了，之前SQL的 update_time 这些条件咋办呢？抽 到子查询那里面。即从二级索引中查到主键后，再根据主键去主键索引中查找实际数据。其实对于慢sql优化，我们一般查询执行计划，建立正确的索引，避免不必要的连接，限制查询结果集大小，以及冗余字段：假设有商品表和订单表，显示每个订单对应的商品名称和单价。如果每次查询都需要通过商品表来查询，显然会影响查询性能。在这种情况下，可以在订单表中添加冗余字段，将商品名称和单价存储在订单表中，以便每次查询时直接使用冗余字段的值，不涉及联表查询，提高查询性能。（需要注意的是必须记得商品名称在订单表中，在更新商品名称时，将这个冗余的字段也加入到更新的程序中）订单表（修改后）订单ID，商品ID，数量，商品名称【冗余字段】，单价【冗余字段】，但是，这种设计有一个重要 的问题需要考虑：数据的一致性。因为商品名称和单价现在存储在两个地方，所以当你更新商品 信息时，你需要确保两个地方的数据都被更新。这通常需要在应用程序层面实现逻辑来保证数据的一致性。可以看到，其实不使用es优化最大的就是使用索引，索引的底层是B+树，叶节点存放键值，叶子节点存放数据指针并用链表相连接，而索引失效的原因在函数，运算是一定会失效的，但对索引的列操作是不一定失效，模糊查询也不一定，在索引的分类中，平常使用的有唯一索引，组合索引以及普通索引，据索引与数据行的存储方式，索引还可以分为聚簇索引和非聚簇索引。组合索引是遵循最左匹配原则，如果使用了组合索引中的多个列，那么查询条件必须从索引的最左边开始，直到查询条件与索引列完全匹配。在sql优化中索引覆盖它是指查询中使用的所有列都包含在索引中，这样查询就可以直接从索引中获取数据，而不需要访问数据行。这通常发生在覆盖索引查询中，可以显著提高查询性能。所以在实际应用中，为了提高查询性能，我们可以根据查询模式来设计索引，尽量让查询能够使用索引覆盖。

MySQL 深入解析与优化指南

MySQL 架构概述

MySQL 采用三层架构设计：

1. 连接器层：负责客户端连接管理、权限验证等
2. 服务器层：包含查询解析、优化、执行等核心功能
3. 存储引擎层：负责数据存储和检索，InnoDB 是最常用的引擎

InnoDB 存储引擎深入

核心特性

• 支持事务（ACID 特性）
• 行级锁定
• 外键支持
• MVCC（多版本并发控制）
• 崩溃恢复能力

关键命令

1
SHOW ENGINE INNODB STATUS;  -- 查看InnoDB详细状态信息

存储结构

• 使用16KB大小的缓冲页管理数据
• 数据按页组织，采用B+树索引结构

事务与日志机制

两阶段提交

1. 准备阶段：事务操作记录到redo log，标记为准备状态
2. 提交阶段：事务操作记录到binlog，标记为提交状态

日志类型

• redo log：物理日志，记录页修改，用于崩溃恢复
• binlog：逻辑日志，记录数据变更，用于复制和时间点恢复
• undo log：逻辑日志，用于事务回滚和MVCC实现

事务隔离与并发控制

隔离级别

1. 读未提交：可能脏读
2. 读已提交：防止脏读，可能不可重复读
3. 可重复读（MySQL默认）：防止不可重复读，可能幻读
4. 串行化：完全隔离，性能最低

MVCC 机制

• 通过版本链实现非锁定读
• 解决读-写冲突，提高并发性能
• 结合Next-Key锁解决幻读问题

SQL 优化实践

查询优化技巧

1. 索引使用：

   • 遵循最左匹配原则
   • 避免索引列上的函数和运算
   • 模糊查询尽量使用like '前缀%'形式

2. 分页优化：

   1
   2
   3
   4
   5
   -- 低效写法
   SELECT * FROM table LIMIT 100000, 10;
      
   -- 优化写法（使用索引覆盖）
   SELECT * FROM table WHERE id > 100000 LIMIT 10;
   

3. 避免不必要连接：

   • 考虑使用冗余字段减少联表查询
   • 确保连接条件上有适当索引

执行计划分析

使用EXPLAIN分析查询：

• 关注type列（连接类型）
• 检查key列（实际使用的索引）
• 注意rows列（预估检查行数）

死锁处理策略

1. 分析SHOW ENGINE INNODB STATUS输出
2. 考虑调整事务大小和持续时间
3. 优化应用逻辑，减少锁竞争
4. 在应用层实现重试机制
5. 必要时调整隔离级别

实际应用建议

1. 索引设计：
   • 根据查询模式创建合适索引
   • 考虑使用覆盖索引减少回表
   • 组合索引注意列顺序
2. 数据一致性：
   • 使用事务保证关键操作原子性
   • 冗余字段需维护同步更新
3. 性能监控：
   • 定期检查慢查询日志
   • 监控锁等待和死锁情况

通过深入理解MySQL内部机制，结合合理的优化策略，可以显著提升数据库性能和可靠性。

可见性判断算法

判断一行数据是否对当前事务可见的完整流程：

1. 检查行记录的 DB_TRX_ID：
   • 如果 DB_TRX_ID == 当前事务ID → 可见（是自己修改的）
   • 如果 DB_TRX_ID < m_up_limit_id → 可见（事务已提交）
   • 如果 DB_TRX_ID >= m_low_limit_id → 不可见（事务在ReadView之后开始）
2. 检查活跃事务列表：
   • 如果 DB_TRX_ID 在 m_ids 中 → 不可见（事务未提交）
   • 否则 → 可见（事务已提交）
3. 版本链回溯：
   • 如果不可见，通过 DB_ROLL_PTR 找到 Undo Log 中的旧版本
   • 重复上述判断直到找到可见版本或到达链尾

Mysql基本问题

1.DDL,DML分别是什么怎么写?

DDL是Data Definition Language 用于操作数据库结构,例如表,索引,视图

create ,alter ,drop,index

DML是Data Manipulation Language 用于对数据表中的数据进行增删查改操作

select,insert,update,delete

2.Mysql的三大范式?反范式?

三大范式

• 1NF:原子性:每一列都是不可再分的最小单位。
• 2NF:消除部份依赖:在 1NF 基础上，非主键字段必须完全依赖于主键，而不能只依赖主键的一部分。
• 3NF:消除传递依赖:非主键字段不能依赖于其他非主键字段，只能依赖主键。

反范式

反范式化是为了性能，在满足业务需求下适当冗余数据，减少关联查询，提高查询效率。

3.MySQL 中的笛卡尔积（为什么要小表驱动大表）

笛卡尔积：当两表连接没有 ON 或 WHERE 条件限制时，所有行组合都会生成 —— 称为笛卡尔积，结果集是 A * B。

例子：

1
SELECT * FROM A, B;  -- 没有任何连接条件，产生 A 表行数 × B 表行数

为什么小表驱动大表（Nested Loop Join）？

在 嵌套循环连接（Nested Loop Join）中，驱动表的每一行都要去被驱动表中查找匹配项，因此：

• 小表放在外层（驱动表），减少查找次数。
• 大表放内层（被驱动表），提升整体效率。

4.MySQL 表的连接方式（七种）

1. INNER JOIN：内连接，仅匹配成功的记录返回。
2. LEFT JOIN：左连接，返回左表全部，右表匹配不上补 NULL。
3. RIGHT JOIN：右连接，返回右表全部，左表补 NULL。
4. FULL JOIN（MySQL 不支持，需 UNION LEFT + RIGHT 实现）：全连接。
5. CROSS JOIN：笛卡尔积。
6. SELF JOIN：自连接，表与自身连接。
7. NATURAL JOIN：自然连接，自动按相同列名匹配（不建议使用，易出错）。

5.count(列名), count(1), count(*) 有什么区别

• count(列名)：统计某列非 NULL 的行数。
• count(1)：统计所有行数，性能与 count(*) 几乎相同。
• count(*)：推荐使用，统计总行数，包括 NULL，语义最明确。

count(*) 是 SQL 标准定义，MySQL 优化最完整。

6.主键用自增还是 UUID？如何选择？

在单机的情况下推荐使用自增ID

在分布式的系统推荐使用优化过的UUID例如雪花算法

雪花算法是什么?有什么好处?会出什么问题?

• 雪花算法是一种生成全局唯一 ID 的算法,它生成的 ID 是一个 64 位的长整型，里面包含了1位符号位 41位时间戳 10位机器标识 12位序列号

• 不依赖数据库生成 ID，分布式节点可以并发生成，性能极高。不会发生重复，适合做数据库主键、订单号等。基于时间戳，ID 大体上是按时间递增的，有利于数据库索引结构（如 B+ 树）。每个节点可独立生成，不需要中心协调。每毫秒支持 4096 个 ID，适用于高并发场景。
• 时钟回拨问题,机器 ID 冲突,ID 太长,跨语言兼容性问题

1. InnoDB 与 MyISAM 的区别

✅ 实际开发建议优先使用 InnoDB，安全性和并发更好。

2. B 树与 B+ 树的区别

3. 一个 B+ 树中能放多少条索引记录？怎么计算？

公式：

1
每层节点数 = floor(页大小 / (索引项大小 + 指针大小))

假设：

• 页大小（Page）= 16KB
• 索引字段（如 BIGINT）= 8 字节
• 指针 = 6 字节（行指针）

计算索引项数量：

1
一页索引项数 = floor(16384 / (8 + 6)) ≈ 1170 条

因此一棵高度为 3 的 B+ 树，理论上最多可存：

1
1170 × 1170 × 1170 ≈ 16 亿条记录

4. MySQL 中事务的特性（ACID）

• A（原子性）：事务要么全成功要么全失败（undo log 保证）。
• C（一致性）：数据从一个一致状态转到另一个一致状态。
• I（隔离性）：并发事务相互隔离（由隔离级别保证）。
• D（持久性）：事务提交后数据永久保存（redo log 保证）。

5. ACID 是怎么保证的？

6. MySQL 事务的隔离级别

从低到高（脏读 -> 幻读越难防）：

1. Read Uncommitted（读未提交）❌会脏读
2. Read Committed（读已提交）Oracle 默认
3. Repeatable Read（可重复读）✅ MySQL 默认
4. Serializable（可串行化）性能差，开销大

7. MySQL 的可重复读是怎么实现的？

答：通过 MVCC（多版本并发控制）+ undo log。

• 每条记录有 隐藏版本号（trx_id）
• 每个事务有 Read View
• 可重复读通过不更新 Read View，实现多次读取同一数据版本

8. 可重复读能否解决幻读问题？

MySQL 中 Repeatable Read 可以解决幻读（使用间隙锁、临键锁）

• 条件查询时，如果涉及范围，InnoDB 会加间隙锁防止插入新行

9. InnoDB 如何管理 Page 页？

• InnoDB 以 页（Page）为单位管理磁盘块，大小默认 16KB
• Page 类型：数据页、索引页、undo 页、系统页等
• 页通过双向链表管理（Flush List、Free List）

10. 什么是 Buffer Pool？

• Buffer Pool 是 InnoDB 的 内存缓冲池
• 用于缓存磁盘页（Page），减少磁盘 I/O
• 包含数据页、索引页、undo log 页等

11. 如何判断一个页是否在 Buffer Pool 中？

• InnoDB 会维护页的状态，判断是否命中缓存
• 内部通过 页号 Page ID + 哈希表查找 是否命中
• 也可以通过 Performance Schema 或 SHOW ENGINE INNODB STATUS 分析

1. 索引有哪些类型？使用索引一定提升效率吗？

常见索引类型：

是否一定提升效率？

❌ 不一定。以下情况可能 导致性能下降：

• 表数据太少 → 全表扫描比建索引快
• 选择性差（如性别字段）→ 扫描行数过多
• 使用函数/类型转换 → 索引失效
• 索引列顺序错误 → 违反最左前缀法则

2. 聚簇索引与非聚簇索引的区别？

• 聚簇索引（Clustered Index）：数据和索引存储在一起（叶子节点存储整行数据）
  • 每张表只能有一个
  • InnoDB 默认使用主键作为聚簇索引
• 非聚簇索引（Secondary Index）：叶子节点只存储主键值，需要回表查询

3. 什么是最左前缀法则？

在联合索引中，查询条件必须从最左的列开始，否则索引失效。

例：索引 (a, b, c) 可以使用：WHERE a、WHERE a AND b 不能使用：WHERE b AND c

4. 为什么 LIKE 以 % 开头会失效？覆盖索引不会失效？

• LIKE '%abc'：不能利用索引，因为无法确定前缀位置（索引是有序的）
• LIKE 'abc%'：可以使用索引（前缀匹配）

覆盖索引不会失效的原因：

• 查询字段全部在索引中，无需回表，哪怕条件不能用索引过滤，也会用索引 快速定位数据

5. EXPLAIN 用过吗？有哪些主要字段？

EXPLAIN 是用来分析 SQL 查询语句的执行过程

常见字段：

6. type 字段常见值（按效率高→低）：

7. Extra 常见字段说明：

8. 什么是自适应哈希索引（AHI）？

• InnoDB 会将热数据页转化为 哈希索引，提升点查效率
• 自动启用，存储在 Buffer Pool 中
• 仅适用于 频繁访问的范围内的值

9. 什么是索引下推（Index Condition Pushdown）？

• MySQL 5.6+ 优化方式，在存储引擎层先用索引做部分 WHERE 条件判断
• 减少回表次数

例子：

1
SELECT * FROM user WHERE age > 20 AND name = 'Tom';

→ 如果 age 是索引，会在引擎层判断 age，减少上层判断压力。

10. 什么是覆盖索引？

• 查询的所有字段都在索引中 → 无需回表
• 优化点：减少 I/O，提升性能

例子：

1
2
SELECT id, name FROM user WHERE id = 1;
-- 如果有联合索引 (id, name)，就是覆盖索引

11. 什么是索引跳跃（Index Skip Scan）？

• 非最左前缀也能用索引的情况（只要第一个字段不是唯一值）
• MySQL 会尝试枚举不同值的情况进行跳跃扫描

条件形如：

1
2
3
WHERE b = 10
-- 虽然没有用到联合索引 (a, b) 的最左前缀 a
-- 但 a 的取值有限时，优化器可能会尝试跳跃扫描

Mysql被黑事件

毕设数据库被勒索程序删库记录,mysql数据被清空,我以为是我删除了什么数据,然后我打开Navicat查看,发现多了一个RECOVER_YOUR_DATA,

之前只是了解到binlog可以恢复数据,但是没有实践过.binlog是二进制文件,需要通过mysql官方工具mysqlbinlog进行解密.搜索drop操作,定位到pos点

通过mysqlbinlog设置–stop-position= xxxx 最终进行恢复,然后迅速更改了mysql设置,只要有公共网ip暴露的地址都不可以设置简单密码,也不要用root权限进行登录,最好是设置公钥私钥进行登录

• Previous
  java基础
• Next
  代理模式