MySQL专栏知识思维导图模板_ProcessOn思维导图、流程图

1 索引使用有哪些注意事项呢

哪些情况会失效

在索引列上做运算（比如使用函数，不过从Mysql8开始，增加了函数索引可以解决这个问题）

在组合索引中不符合最左匹配法则

隐式转换（索引列是字符串类型，但是在sql查询中没有使用引号）

在索引列使用不等于号、not查询

使用like通配符匹配后缀%xxx，不符合最左匹配法则

使用or连接查询的时候，or语句前后没有同时使用索引

不适合哪些场景

数据量少

更新比较频繁

区分度低的字段（比如性别）

一些潜规则

覆盖索引

查询的列能从索引中获取数据就是覆盖索引。（主键索引，叶子节点保存数据；辅助索引，叶子节点保存主键值）

回表

无法从索引拿到想要查询的值，需要进一步查询主键索引达到查询数据的目的，这个也称之为回表

索引数据结构（B+树）

详情见：

最左前缀原则

索引下推

MySQL5.6后引入的特性，通过索引筛选完条件才回表，减少回表的次数

2 遇到过死锁问题吗，你是如何解决的

是什么

程序出现死锁，是因为在多线程环境里面两个或两个以上的线程同时满足

互斥条件

请求保持条件

不可抢占条件

循环等待条件

怎么做

出现死锁以后，可以通过jstack命令去导出线程的dump日志， 然后从dump日志里面定位到具体死锁的程序代码。 因为互斥条件是锁本身的特性，所以不能破坏， 通过修改程序代码去破坏其余三个条件里面的任意一个，就可以解决死锁问题

3 日常工作中你是怎么优化 SQL 的？

硬件和操作系统层面的优化

关注服务本身承载的体量，然后提出合理的指标要求 （CPU、可用内存大小、磁盘读写速度、网络带宽、应用文件句柄数、操作系统网络的配置）

架构设计层面的优化

搭建Mysql主从集群

读写分离设计

引入分库分表机制（待研究）

通过分库可以降低单个服务器节点的IO压力， 通过分表的方式可以降低单表数据量，从而提升sql查询的效率

针对热点数据，可以引入更为高效的分布式数据库

比如Redis、MongoDB

MySQL程序配置优化

怎么做

一般是通过Mysql中的配置文件my.cnf来完成的

最大连接数

binlog日志，默认是不开启

缓存池bufferpoll的默认大小配置

注意点

配置的作用域

会话级别

全局

是否支持热加载

SQL优化

慢SQL的定位和排查（通过慢查询日志和慢查询日志分析工具得到有问题的SQL列表，工具有mysqldumpslow）

执行计划分析（使用explain，重点关注 type key rowsfilterd）

使用show profile工具

Show Profile是MySQL提供的可以用来分析当前会话中，SQL语句资源消耗情况的工具。 在当前会话中.默认情况下处于show profile是关闭状态，打开之后保存最近15次的运行结果。 通过profile工具进行详细分析.可以得到SQL执行过程中所有的资源开销情况，如IO开销，CPU开销，内存开销等

常见优化的规则

SQL查询一定要基于索引来进行数据扫描

避免索引列上使用函数或者运算，导致索引失效

where 字句中like %号，尽量放置在右边

尽量满足联合索引的最左匹配法则

尽可能使用SQL语句用到的索引完成排序，避免使用文件排序的方式

查询有效的列信息即可，少用 *

永远用小结果集驱动大结果集

4 说说分库与分表的设计（待研究如何使用框架实现）

基础学习

拆分的方案

水平分库

以字段为依据，按照一定策略（hash、range 等），将一个库中的数 据拆分到多个库中

水平分表

以字段为依据，按照一定策略（hash、range 等），将一个表中的数 据拆分到多个表中

垂直分库

以表为依据，按照业务归属不同，将不同的表拆分到不同的库中

垂直分表

以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和 扩展表）中

三种应用场景

只分库不分表。（当数据库的读写访问量过高，还有可能会出现数据库连接不够用的情况）

只分表不分库（当单表存储的数据量非常大的情况下，并且并发量也不高，数据库的连接也还够用）

既分表又分库（结合前面的两种情况，如果同时满足前面的两个条件，也就是数据连接也不够用，并且单表的 数据量也很大）

常用的分库分表中间件

开源社区的Sharding-JDBC(client 模式,ps:新版本也有proxy模式) 美团点评的Zebra (client 模式) 民间组织的MyCAT (proxy模式,基于cobar演变) 美团点评DBProxy(proxy模式,基于360Atlas,目前只支持mysql5.5和5.6) 360的Atlas (proxy模式,基于mysql官方mysql proxy) 阿里的 Cobar (proxy 模式)

分库分表可能遇到的问题

事务问题（需要用分布式事务，待研究）

跨节点 Join 的问题（分两次查询实现）

跨节点的 count，order by，group by，排序分页以及聚合函数问题 （分别在各个节点上得到结果后在应用程序端进行合并）

数据迁移，容量规划，扩容等问题

ID 问题（数据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单 可以考虑 UUID）

分布式ID需要满足哪些条件

全局唯一

高性能

高可用

好接入

趋势递增

分布式ID都有哪些生成方式

还有很多概念，详情见

5 InnoDB 与MyISAM 的区别

4个区别

1 数据存储的方式不同

MyISAM中的数据和索引是分开存储的，（因为索引和数据是分离的， 所以在进行查找的时候，先从索引文件中找到数据的磁盘位置， 再到数据文件中找到索引对应的数据内容）

InnoDB是把索引和数据存储在同一个文件里面

2 对于事务的支持不同，MyISAM不支持事务，而InnoDB支持ACID特性的事务处理

3 对于锁的支持不同，MyISAM只支持表锁，而InnoDB可以根据不同的情况，支持行锁，表 锁，间隙锁，临键锁

4 MyISAM不支持外键，InnoDB支持外键

使用场景

如果需要支持事务，那必须要选择InnoDB； 如果大部分的表操作都是查询，可以选择MyISAM

6 索引为什么要用 B+树，而不用二 叉树

是什么

B树是一种多路平衡树，B树的高度比二叉树矮很多，因此磁盘 IO数少很多，查询效率提高了

B+树是基于B树做的变种，做了这些优化：

B+树的所有数据都存储在叶子节点，非叶子节点只存储索引

叶子节点中的数据使用双向链表的方式进行关联

总结：主要是2个特性

B+树叶只有子节点存储数据

叶子节点的数据使用了双向链表关联

理由

B+树非叶子节点不存储数据，所以每一层能够存储的索引数量会增加，意味着B+树在层高相同的情 况下存储的数据量要比B树要多，使得磁盘IO次数更少

在Mysql里面，范围查询是一个比较常用的操作，而B+树的所有存储在叶子节点的数据使用了双向 链表来关联，所以在查询的时候只需查两个节点进行遍历就行，而B树需要获取所有节点，所以B+树 在范围查询上效率更高

在数据检索方面，由于所有的数据都存储在叶子节点，所以B+树的IO次数会更加稳定一些

因为叶子节点存储所有数据，所以B+树的全局扫描能力更强一些，因为它只需要扫描叶子节点。但 是B树需要遍历整个树

另外，基于B+树这样一种结构，如果采用自增的整型数据作为主键，还能更好的避免增加数据的时 候，带来叶子节点分裂导致的大量运算的问题

7 聚集索引与非聚集索引的区别

是什么

聚集索引：基于主键创建的索引

非聚集索引：除了主键索引以外的其他索引

聚集索引的特性

聚集索引并不仅仅是一种索引类型，还代表着一种数据的存储方式

表数据对应的物理文件是按B+树来组织的索引结构，聚集索引按照表的主键构建B+树，叶子节点存储数据。

因此，如果没有主键，InnoDB会默认选择或者添加一个隐藏 列作为主键索引来存储这个表的数据行

一般情况是建议使用自增id作为主键，这样的话id本身具有 连续性使得对应的数据也会按照顺序存储在磁盘上，写入性能和检索性能都很高。否则，如果使用 uuid这种随机id，那么在频繁插入数据的时候，就会导致随机磁盘IO，从而导致性能较低

InnoDB里面只能存在一个聚集索引

如果存在多个聚集索引，那么意 味着这个表里面的数据存在多个副本，造成磁盘空间的浪费，以及数据维护的困难

总结：回表

主键索引表示的是一种数据存储结构，所以如果是基于非聚集索引来查询一条 完整的记录，最终还是需要访问主键索引来检索

8 limit 1000000 加载很慢的话， 你是怎么解决的呢

1 如果id是趋势递增，则用上一次查询到的最大id去where，例子：select id，name from employee where id>1000000 limit 10

2 先查id，再用主表与id进行inner join（减少回表）

3 mysql级别无法优化，则考虑加入缓存

4 与业务沟通，作分页限制。（如你去看淘宝，不会去翻后面几百页的数据，所以，业务 层面也可以做一些让步，比如不做后面几百页的数据）

9 如何选择合适的分布式主键方案呢？

详情见

10 事务的隔离级别有哪些？MySQL 的默认隔离级别是什么？

是什么

事务隔离级别，是为了解决多个并行事务竞争导致的数据安全问题的一种规范

并行事务竞争带来的3种现象

脏读

不可重复读

幻读

这3种现象在实际应用中，可能有些场景不能接受某些现象的存在， 所以在SQL标准中定义了4种隔离级别

读未提交

可能产生脏读、不可重复读、幻读

读已提交RC

可能产生不可重复读、幻读

可重复读RR

网上很多都说可能产生幻读，但由于MVCC机制，RR下没有幻读

串行化

多个并行事务串行化执行，不会产生安全性问题

11 在高并发情况下，如何做到安全的修改同一行数据？

使用悲观锁（select... for update）

使用乐观锁（版本号或CAS机制）

12 SQL 优化的一般步骤是什么，怎么看执行计划（explain）， 如何理解其中各个字段的含义

1 慢SQL的定位和排查

2 使用explain查看当前sql的执行计划

3  使用MySQL的show profile工具

Show Profile是MySQL提供的可以用来分析当前会话中，SQL语句资源消耗情况的工具，可用于 SQL调优的测量。在当前会话中.默认情况下处于show profile是关闭状态，打开之后保存最近15次 的运行结果 针对运行慢的SQL，通过profile工具进行详细分析.可以得到SQL执行过程中所有的资源开销情况. 如IO开销,CPU开销,内存开销等. 咕泡教育 咕泡教育 咕

13 select for update 有什么含义，会锁表还是锁行还是其他

会加锁。而且它是悲观锁。至于加了是行锁还是表锁，这就要看是不是用了索引/主键。 没用索引/主键的话就是表锁，否则就是是行锁

14 MySQL 事务的四大特性以及实现原理

详情见 

4个特性

原子性

通过undo log记录旧版本的信息，用于事务回滚

持久性

通过redo log实现

redo log存在的背景

MySQL 的数据存在磁盘上，随机磁盘IO效率很低， 因此引入了一个缓冲池Buffer。 然而当数据库宕机导致缓冲池中的数据无法写入磁盘，即造成数据丢失。 因此引入redo log，当数据变更时，除了写入buffer还得写入redo log。（先写redo log，再写入buffer，类似于先写库再写缓存） 当MySQL宕机，可以从redo log恢复数据

为什么将redo log的数据写到磁盘比将Buffer中的数据写到磁盘快？

1 IO方式不同

Buffer中的数据持久化是随机写的IO

redo log是追加模式，属于顺序IO（KafKa也是采用顺序IO机制操作）

2 写入的单位不同

Buffer持久化数据是以数据页page为单位的，MySQL默认配置大小为16k，一个数据页上一个小小的数据修改，都需要把整个页的数据写入

redo log只需要写入真正的部分，无效的IO就大大减小了

redo log什么时候同步到磁盘里去？

redo log没有同步到磁盘之前是在缓存区中，它叫redo log缓冲区。 MySQL宕机也没有关系，因为事务没有执行完，即事务没有提交，恢复好数据库后可以通过undo log走回滚操作。 redo log的持久化机制可以通过innodb_flush_log_at_trx_commit。

1 值为0时，表示当事务提交时，并不将缓冲区的redo log写入到磁盘的日志文件，而是等待主线程每秒刷新

2 值为1时，表示当事务提交时将缓冲区中的redo log同步写入到磁盘中， 保证一定会写入成功 。同步写入的速度很快，比Buffer同步快很多。

3 值为2时，表示事务提交时将缓冲区的redo log异步写入到磁盘中。不能保证在commit时一定会将redo log写入磁盘中去

隔离性

是什么

不同事务之间不能相互影响

怎么实现隔离性

写-写操作

通过锁解决

读-写操作

通过MVCC解决（不同隔离级别下会出现脏读、不可重复读、幻读）

一致性

一致性是事务追求的最终目标，ACID中三种特性都是为了实现最终的一致性。

总结

通过undo log、redo log、事务隔离级别、MVCC机制、锁实现ACID

15 如果某个表有近千万数据，CRUD比较慢，如何优化

分库分表

涉及如何分片、事务、跨节点join、分库分表中间件等等

优化索引

16 如何写 sql 能够有效的使用到复合索引

要关注查询 Sql 条件的顺序，确保最左匹配原则

17 exist和in的区别

select * from A where deptId in (select deptId from B）；in先查B表

select * from A where exists (select 1 from B where A.deptId = B.deptId);exists先查A表

总结

按照小表驱动大表的原则，B数据量比A小则用in，A数据量比小则用exist

18 数据库自增主键可能遇到什么问题

使用自增主键对数据库做分库分表，可能出现诸如主键重复等的问题。解决方案的 话，简单点的话可以考虑使用 UUID

会产生表锁，从而引发问题

自增主键可能用完问题

19 MVCC底层原理

详情见

20 数据库中间件了解过吗，sharding，jdbc，mycat

sharding-jdbc 目前是基于 jdbc 驱动，无需额外的 proxy，因此也无需关注proxy 本身的高可用

Mycat 是基于 Proxy，它复写了 MySQL 协议，将 Mycat Server 伪装成一个MySQL 数据 库，而 Sharding-JDBC 是基于 JDBC 接口的扩展，是以 jar 包的形式提供轻量级服务的

21 MYSQL 的主从延迟，你怎么解决？

主从复制原理

流程图

1 主库的更新事件(update、insert、delete)被写到 binlog

2 从库发起连接，连接到主库

3 此时主库创建一个 binlog dump thread，把binlog 的内容发送到从库

4 从库启动之后，创建一个 I/O 线程，读取主库传过来的 binlog 内容并写入到 relay log

5 从库创建一个 SQL 线程，从 relay log 里面读取内容，从Exec_Master_Log_Pos 位置开始执行读取到的更新事件，将更新内容写入到slave 的db

延迟的原因

根据原理，延迟的原因必定是binlog产生的速度大于消费binlog的速度，导致主库有一定的SQL积压未同步到从库，从而主从不一致，即主从延迟

解决办法

思路：降低主库binlog产生的速度，或者加大从库消费bin log的速度（降低从库的负载）

主库方面

1 提高同步性。如果对数据一致性要求高，主服务器有些设置参数可以修改， 比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置等

从库方面

2 选择更好的硬件设备作为 slave

3 把一台从服务器当度作为备份使用， 而不提供查询（降低从库的负载）

4 增加从服务器（降低从库的负载）

22 说一下大表查询的优化方案

1 优化schema，sql语句，索引

2 加缓存

3 主从复制，读写分离

4 分库分表

23 什么是数据库连接池?为什么需要数据库连接池呢?

是什么

数据库连接池是一种池化技术，池化技术的核心思想是实现资源的复用，避免资源重复创建销毁的开销

优点

资源重用 (连接复用)

更快的系统响应速度

新的资源分配手段

统一的连接管理，避免数据库连接泄漏

24 一条 SQL 语句在 MySQL 中如何执

流程图

1 先检查该语句是否有权限

如果没有权限，直接返回错误信息

如果有权限，在 MySQL8.0 版本以前，会先查询缓存

2 词法分析（如果没有缓存，分析器进行词法分析，提取 sql 语句 select 等的关键元素。然后 判断sql 语句是否有语法错误，比如关键词是否正确等等）

3 优化器确定执行方案

4 进行权限校验，如果没有权限就直接返回错误信息，如果有权限就会调用数据库引 擎接口，返回执行结果

25 InnoDB引擎中的索引策略，了解过吗？

覆盖索引

最左前缀原则

索引下推（索引下推优化是 MySQL 5.6 引入的， 可以在索引遍历过程中，对索引中包含的字 段先做判断，直接过滤掉不满足条件的记录，减少回表次数。）

26 一条 sql 执行过长的时间，你如何优化，从哪些方面入手？

查看是否涉及多表和子查询，优化 Sql 结构，如去除冗余字段，是否可拆表

优化索引结构，看是否可以适当添加索引

数量大的表，可以考虑进行分离/分表（如交易流水表）

explain 分析 sql 语句，查看执行计划，优化 s

数据库主从分离，读写分离

查看mysql 执行日志，分析是否有其他方面的问题

27 MYSQL数据库服务器性能分析的方法命令有哪些

Show status, 一些值得监控的变

Bytes_received 和 Bytes_sent 和服务器之间来往的流量。 Com_*服务器正在执行的命令。 Created_*在查询执行期限间创建的临时表和文件。 Handler_*存储引擎操作。 Select_*不同类型的联接执行计划。 Sort_*几种排序信息

Show profiles 是MySql 用来分析当前会话 SQL 语句执行的资源消耗

28 Mysql中有哪几种锁，列举一下

加锁机制

乐观锁

悲观锁

兼容性

共享锁

排他锁

锁粒度

表锁

开销小，加锁快；锁定力度大，发生锁冲突概率高，并发度最低;不会出现死锁

页锁

开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

行锁

开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

锁模式

记录锁

gap锁（间隙锁）

next-key锁（记录锁+gap锁）

意向锁

插入意向锁

29 mysql的内连接、左连接、右连接有什么区别

Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集

left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录

right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有 匹配的记录

30 索引有哪些优缺点？

优点

通过B+树的结构来存储数据，磁盘IO次数大大减少。范围查询时，只需要找到起始节点，然后基于叶子节点的链表结构往下读取即 可，查询效率较高。

通过唯一索引约束，可以保证数据表中每一行数据的唯一性

缺点

当数据量较大的情况下，索引的维护会带来较大的性能开销

创建索引的时候，需要考虑到索引字段值的分散性，如果字段的重复数据过多，创建索引反而会带来性能降低

总结

任何技术方案都会有两面性，大部分情况下，技术方案的选择更多的是看中它的优势和当前问题的匹配度

31 创建索引有什么原则呢

最左前缀匹配原则

频繁作为查询条件的字段才去创建索引，频繁更新的字段不适合创建索引

索引列不能参与计算，不能有函数操作

优先考虑扩展索引，而不是新建索引，避免不必要的索引

在 order by 或者group by 子句中，创建索引需要注意顺序

区分度低的数据列不适合做索引列(如性别）

对于定义为 text、image 数据类型的列不要建立索引

删除不再使用或者很少使用的索引

32 百万级别或以上的数据，你是如何删除的？

先删除索引，然后批量删除其中无用数据，删除完成后重新创建索引

33 count(1)、count(*) 与 count(列名) 的区别

是什么

count是一个聚合函数，聚合数据的总数。以默认的innoDB存储引擎为例，根据where条件去进行扫描，得到扫描结果后，一行一行去判断，如果count括号里的不是null，那么累计值+1，否则不加，最后返回一个累计的总数

区别

count(*)：不会去取全部字段。直接扫描多少数据统计数据即可

count(1)：扫描到一条数据，直接返回一个数字1，不会取扫描的字段，也不会去判断是否为null

count(列)：会判断列值是否为null，为null不统计

34 varchar(50)中 50 的涵义

字段最多存放 50 个字符

35 mysql 中int(20)和 char(20)以及 varchar(20)的区别

int(20) 表示字段是 int 类型，显示长度是20

char(20)表示字段是固定长度字符串，长度为20

varchar(20) 表示字段是可变长度字符串，长度为 20

36 MySQL 数据库 cpu 飙升的话，要怎么处理呢？

原因

上下文切换过多 （切换主要执行2个动作：保存运行线程的状态；唤醒等待中的线程）。文件IO、网络IO、锁等待、线程阻塞等操作都会造成线程阻塞从而触发上下文切换

CPU资源过度消耗。（在程序中创建了大量的线程，或者有线程一直占用CPU资源无法被释放，比如死循环）

怎么解决 

以通过top命令，找到CPU利用率较高的进程，在通过Shift+H找到进程中CPU消耗过高的线程

CPU利用率过高的线程一直是同一个，说明程序中存在线程长期占用CPU没有释放的情况，这种情 况直接通过jstack获得线程的Dump日志，定位到线程日志后就可以找到问题的代码

CPU利用率过高的线程id不断变化，说明线程创建过多，需要挑选几个线程id，通过jstack去线程 dump日志中排查

最后有可能定位的结果是程序正常，只是在CPU飙高的那一刻，用户访问量较大，导致系统资源不够

37 你们数据库是否支持 emoji 表情存储，如果不支持，如 何操作？

更换字符集 utf8-->utf8mb4

38 Mysql如何解决幻读问题

是什么

幻读，就是一个事务前后两次读取到的数据条数不一致

RR事务隔离级别下，引入了MVCC和LBCC这两种方式来解决幻读问题

怎么解决 

RR隔离级别

引入MVCC和锁机制（行锁、表锁、间隙锁、next-key锁）解决

RC隔离级别

MVCC仍会存在幻读，通过锁机制解决

总结：如果在一个事务里面存在当前读的情况下，MVCC还是会存在幻读问题，通过锁机制解决