MySQL面试题总结2

死锁

死锁：一组互相竞争资源的线程因互相等待导致“永久”阻塞的现象

产生条件：

• 互斥条件
• 请求保持条件
• 不可抢占条件
• 循环等待条件

出现死锁以后，可以通过jstack命令去导出线程的dump日志， 然后从dump日志里面定位到具体死锁的程序代码。

避免死锁：打破其中任意一个条件就可以避免死锁

• 互斥条件是锁本身的特性，无法被破坏
• 对于请求保持条件，可以在第一次执行时，一次性申请所有的共享资源
• 对于不可抢占条件，占用了部分资源的线程在进一步申请其他资源时，如果没申请到，主动释放它占有的资源
• 对于循环等待条件，可以按照顺序来申请资源，避免循环等待。

MySQL的性能优化（pass）

• 硬件和操作系统层面的优化
  • 硬件：CPU，可用内存大小，磁盘读写速度，网络带宽
  • 操作系统：应用文件句柄数，操作系统配置
• 架构设计层面的优化
  • 主从集群/主主集群：保证服务的高可用性（单个Mysql服务容易单点故障）
  • 读写分离设计：避免冲突
  • 分库分表：降低单表数据量，提高sql查询效率
  • 针对热点问题，使用redie缓存
• MySQL程序配置优化
  • 缓冲池大小配置等
• SQL优化
  • 慢SQL的定位和排查：慢查询日志和分析工具得到有问题的sql列表
  • 执行计划分析 explain关键字查看当前sql的执行计划，重点关注type key rows filterd字段，定位SQL执行慢点的根本原因
  • 使用show profile工具：得到资源开销情况（IO开销，CPU开销，内存开销等）

SQL优化

• 基于索引来进行数据扫描
• 避免索引列上使用函数或者运算，导致索引失效
• where子句的like %尽量放右侧
• 使用索引扫描，联合索引中的列从左到右，命中率越多越好
• 尽可能使用SQL语句用到的索引完成排序，避免使用文件排序的方式
• 查询有效的列信息，少用*代替列信息
• 永远用小结果集驱动大结果集

分库分表

常见的分库分表中间件

• sharding-jdbc (当当) 基于jdbc驱动
• TDDL淘宝
• MyCat 基于Proxy
• Oceanus（58 同城数据库中间件）
• vitess（谷歌开发的数据库中间件）
• Atlas（Qihoo 360）

分库分表可能遇到的问题

• 事务问题：分布式事务
• 跨节点join：一个问题分两次查询实现
• 跨节点的count，order by，group by以及聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并
• 数据迁移，容量规划，扩容问题
• ID问题，考虑UUID
• 跨分片的排序分页问题

应用场景

• 分库：读写访问量过高，可能出现数据库连接不够用的情况，分库提升并发性能
• 分表：单表存储的数据库非常大，并发量不高，数据连接够用，单数据写入和查询的性能出现瓶颈。分表减少单表存储的数据量，提升读写效率

怎么查看执行中的SQL加了什么锁呢？/ 如何查看事务的加锁情况呢？

有这两种方法：

• 使用infomation_schema数据库中的表获取锁信息
• 使用show engine innodb status 命令

MyISAM和InnoDB的区别

• 数据存储方式不同：MyISAM中数据和索引是分开存储的，InnoDB是把索引和数据存储在同一文件中
• 对于事物的支持不同，MyISAM不支持事务，而InnoDB支持ACID特征的事物处理
• MyISAM不支持外键，InnoDB支持外键
• 对锁的支持不同：MyISAM只支持表锁，而InnoDB支持行锁，表锁，间隙锁，临键锁

使用建议

• 如果需要支持事务，选择InnoDB，不需要事务选择MyISAM
• 大部分表操作都是查询，选择MyISAM，有读有写选择InnoDB

数据库索引

为什么选择B+树，而不是用二叉树？

B树是一种多路平衡树，用这种存储结构来存储大量数据，它的整个高度会相比二叉树来说，会矮很多。
对于数据库来说，树的高度能够决定磁盘IO的次数，树的高度余越低，磁盘IO次数越少，对于性能的提升就越大。这也是为什么采用B树作为索引存储结构的原因。
MySQL的InnoDB存储引擎，它用了一种增强的B树结构，也就是B+树来作为索引和数据的存储结构。
1、B+树的所有数据都存储在叶子节点，非叶子节点只存储索引。
2、叶子节点中的数据使用双向链表的方式进行关联。

B+树相比B树的优点

1/ 磁盘IO效率方面：B+树非叶子节点不存储数据，只存储索引信息，所以每一层能够存储的索引数量会增加，意味着B+树在层高相同的情况下存储的数据量要比B树要多，使得磁盘IO次数更少。
2/从范围查询效率方面： B+树的所有存储在叶子节点的数据使用了双向链表来关联，所以在查询的时候只需查两个节点进行遍历就行，而B树需要获取所有节点，所以B+树在范围查询上效率更高。
3/从全表扫描方面：B+树的叶子节点存储所有数据，所以B+树的全局扫描能力更强一些，因为它只需要扫描叶子节点。但是B树需要遍历整个树。
4/自增ID方面：基于B+树的这样一种数据结构，如果采用自增的整型数据作为主键， 还能更好的避免增加数据的时候，带来叶子节点分裂导致的大量运算的问题。

B+树索引和Hash索引的区别

• B+树可以进行范围查询，Hash 索引不能。
• B+树支持联合索引的最左前缀原则，Hash 索引不支持。
• B+树支持 order by 排序，Hash 索引不支持。
• Hash 索引在等值查询上比 B+树效率更高。
• B+树使用 like 进行模糊查询的时候，like 后面（比如%开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。

聚集索引与非聚集索引

聚集索引就是基于主键创建的索引，除了主键索引以外的其他索引，称为非聚集索引，也叫做二级索引。
由于在InnoDB引擎里面，聚集索引就是按照每张表的主键来构建一颗B+树，然后叶子节点里面存储了这个表的每一行数据记录。 所以基于InnoDB这样的特性，聚集索引并不仅仅是一种索引类型，还代表着一种数据的存储方式。 同时也意味着每个表里面必须要有一个主键，如果没有主键，InnoDB会默认选择或者添加一个隐藏列作为主键索引来存储这个表的数据行。
一般情况是建议使用自增id作为主键，这样的话id本身具有 连续性使得对应的数据也会按照顺序存储在磁盘上，写入性能和检索性能都很高。否则，如果使用 uuid这种随机id，那么在频繁插入数据的时候，就会导致随机磁盘IO，从而导致性能较低。 需要注意的是，InnoDB里面只能存在一个聚集索引，原因很简单，如果存在多个聚集索引，那么意味着这个表里面的数据存在多个副本，造成磁盘空间的浪费，以及数据维护的困难。
如果是基于非聚集索引来查询一条完整的记录，那么查询得到的是主键索引，需要回表查询得到完整记录。

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

• 优化 shema、sql 语句+索引
• 可以考虑加缓存，memcached, redis，或者 JVM 本地缓存
• 主从复制，读写分离
• 分库分表

假如我Myql的表很大，进行分页的时候，limit 1000000 加载很慢的话，你是怎么解决的 呢？

limit m，n ;其实去扫描m+n条数据，然后过滤掉前面的m条数据，当m越大，那么需要扫描的数据 也就越多，性能也会越来越慢。 针对这种情况，有以下几种方案可以进行一定的优化。

• 1.如果id是趋势递增的，那么每次查询都可以返回这次查询最大的ID，然后下次查询，加上大于 上次最大id的条件，这样会通过主键索引去扫描，并且扫描数量会少很多很多。因为只需要扫描where 条件的数据
  select id，name from employee where id>1000000 limit 10
• 2.先limit出来主键ID，然后用主表跟查询出来的ID进行inner join 内连接，这样，也能一定上提速， 因为减少了回表，查询ID只需要走聚集索引就行。
• 3.当然，如果mysql级别优化不了了。我们也可以对分页数据进行缓存，比如Redis缓存，数据进行 变动的时候，做好缓存依赖即可。
• 4.在业务允许的情况下限制页数：建议跟业务讨论，因为绝大多数用户都不会往后翻太多页。因为越往后，一般用户行为触及不到，比如你去看淘宝，不会去翻后面几百页的数据，所以，业务层面也可以做一些让步，比如不做后面几百页的数据。

一个 6 亿的表 a，一个 3 亿的表 b，通过外间 tid 关联， 你如何最快地查询出满足条件的第 50000 到第 50200 中的这 200 条数据记录。

1、如果 A 表 TID 是自增长,并且是连续的,B 表的 ID 为索引select * from a,bwhere a.tid = b.id and a.tid>500000 limit 200;
2、如果 A 表的 TID 不是连续的,那么就需要使用覆盖索引.TID 要么是主键,要 么是辅助索引,B 表 ID 也需要有索引。 select * from b , (select tid from alimit 50000,200) a where b.id = a .tid;

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

• 数据库自增长序列或字段。
• UUID
• Redis 生成 ID
• Twitter 的 snowflake 算法
• 利用 zookeeper 生成唯一 ID
• MongoDB 的 ObjectId

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

事务的四大特性ACID

A	Atomicity	原子性	事务作为一个整体被执行，要么全部被执行，要么全不执行 ，通过事务的提交回滚来实现的
C	Consistency	一致性	指在事务开始之前和事务结束以后，数据不会被破坏，假如 A 账户给 B 账户转 10 块钱，不管成功与否，A 和B 的总金额是不变的
I	Isolation	隔离性	多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其它事务运行效
D	Durability	持久性	一旦数据提交成功，该事务对数据库所作的操作更改，将持久地保存在数据库之中。

其中I就是事务的隔离级别，是为了解决多个并行事务竞争导致的数据安全问题的一种规范。
具体来说，多个事务竞争可能会产生三种不同的现象。
【脏读】假设有两个事务T1/T2同时在执行，T1事务有可能会读取到T2事务未提交的数据，但是未提交的事务T2可能会回滚，也就导致了T1事务读取到最终不一定存在的数据产生脏读的现象。
【不可重复读】假设有两个事务T1/T2同时执行，事务T1在不同的时刻读取同一行数据的时候结果可能不一样，例如第二次读到T2修改提交后到数据，从而导致不可重复读的问题。
【幻读】假设有两个事务T1/T2同时执行，事务T1执行范围查询的过程中，会先锁定查询的记录，但是并没有锁定整个表， 事务T2插入了一条属于事务T1范围内的数据并且提交了，这时候在事务T1再查询发现多出来了一条数据，就产生了幻读。

所以在SQL标准中定义了四种 隔离级别，隔离级别从低到高分别是：
读未提交，在这种隔离级别下，可能会产生脏读、不可重复读、幻读。
读已提交（RC)，在这种隔离级别下，可能会产生不可重复读和幻读。
可重复读（RR），在这种隔离级别下，可能会产生幻读
串行化，在这种隔离级别下，多个并行事务串行化执行，不会产生安全性问题。

隔离级别高，性能越差。
在Mysql里面，InnoDB引擎默认的隔离级别是RR（可重复读），通过MVCC和LBCC来解决欢度问题。其中LBCC通过临键锁解决的是当前读情况下的幻读，MVCC依赖于：三个隐藏字段、Undo log和Read View，解决的是快照读的幻读问题。

MVCC流程

MVCC其实就是查询的时候，会去生成一个readView的class结构， 里面会保存一些事务ID数据，比如当前存活的事务ID有哪些，然后跟数据的事务ID进行比对，来决定数据是否展示。 RR 只有在第一次查询的时候会生成一个readView，后面的查询比对的都是第一个查询的时候的结果， 所以后面查不到新的值。
RC是每次查询都会生成一个新的readView，这样每次查询我就能拿到最新的数据了，也就产生了不可重复读跟幻读问题 。
而LBCC是通过锁的形式去解决不可重复读跟幻读问题，就是我在改数据同一条或者同一批数据的时候，其他事务不能进行改动。但是RR相比于RC多了一个间隙锁，所谓间隙锁，就是会根据你锁的条件会锁定一个区间，所以没有幻读。

Mysql如何解决幻读问题

在RR(也就是可重复读)的事务隔离级别下，InnoDB采用了MVCC机制来解决幻读问题。 MVCC就是一种乐观锁的机制，它通过对不同事务生成不同的快照版本，通过UNDO版本链进行管理并且在MVCC里面，规定了高版本能够看到低版本的事务变更，低版本看不到高版本的事务变更从 而实现了不同事务之间的数据隔离，解决了幻读的问题。 但是在当前读的情况下，是直接读取内存的数据，跳过了快照度，所以还是会出现幻读问题。
我认为可以通过两个方式来解决。
第一种是尽量避免当前读的情况
第二种是引入LBCC的方式

悲观锁和乐观锁

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

要安全的修改同一行数据，就要保证一个线程在修改时其它线程无法更新这行记录。一般有悲观锁和 乐观锁两种方案

• 使用悲观锁

悲观锁思想就是，当前线程要进来修改数据时，别的线程都得拒之门外~ 比如， 可以使用 select * from User where name=‘jay’ for update

以上这条 sql 语句会锁定了 User 表中所有符合检索条件（name=‘jay’）的记录。本次事务提交 之前，别的线程都无法修改这些记录。

• 使用乐观锁

乐观锁思想就是，有线程过来，先放过去修改，如果看到别的线程没修改过， 就可以修改成功，如果别的线程修改过，就修改失败或者重试。
实现方式：乐观锁一般会使用版本号机制或 CAS 算法实现。

CAS

面试题：select for update 有什么含义，会锁表还是锁行还是其他

select 查询语句是不会加锁的，但是 select for update 除了有查询的作用外， 还会加锁，而且是悲观锁。至于加了是行锁还是表锁，这就要看是不是用了索引/主键啦。 没用索引/主键的话就是表锁，否则就是是行锁。

Mysql事务的底层实现原理

Mysql里面的事务，满足ACID特性

A表示原子性，要么都成功，要么都失败。
通过undo log实现，在事务执行的过程中，把修改之前的数据快照保存到undo log版本链中，如果事务执行过程中出错或者发生回滚，系统通过undo log日志返回事务开始的状态。

C表示一致性，表示数据的完整性约束没有被破坏。通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。

I表示事物的隔离性，也就是多个并行事务对同一个数据进行操作的时候，如何避免多个事务 的干扰导致数据混乱的问题。
InnoDB提供了四种隔离级别的实现。分别是： RU（未提交读） RC（已提交读） RR（可重复读） Serializable（串行化） InnoDB默认的隔离级别是RR（可重复读）。
使用了MVCC机制解决了脏读和不可重复读的问题， 然后使用了行锁/表锁的方式解决了幻读的问题。

D表示持久性，也就是只要事务提交成功，那对于这个数据的结果的影响一定是永久性 的。 不能因为宕机或者其他原因导致数据变更失效。
理论上来说，事务提交之后直接把数据持久化到磁盘就行了，但是因为随机磁盘IO的效率确实很低， 所以InnoDB设计了Buffer Pool缓冲区来优化，也就是数据发生变更的时候先更新内存缓冲区，然后在合适的时机再持久化到磁盘。 那在持久化这个过程中，如果数据库宕机，就会导致数据丢失，也就无法满足持久性了。 所以InnoDB引入了redo log文件，这个文件存储了数据被修改之后的值，当我们通过事务对数据进行变更操作的时候，除了修改内存缓冲区里面的数据以外，还会把本次修改的值追加到 redo log里面。 当提交事务的时候，直接把redo log日志刷到磁盘上持久化，一旦数据库出现宕机，在Mysql重启在以后可以直接用redo log里面保存的重写日志读取出来，再执行一遍从而保证持久性。

化简版本：

• 原子性：是使用 undo log 来实现的，如果事务执行过程中出错或者用户执行 了rollback，系统通过 undo log 日志返回事务开始的状态。
• 持久性：使用 redo log 来实现，只要 redo log 日志持久化了，当系统崩溃，可通过redo log 把数据恢复。
• 隔离性：通过锁以及 MVCC,使事务相互隔离开。
• 一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。

总结：事务的实现原理的核心本质就是如何保证ACID的，在InnDB里面用到了MVCC、 行锁表锁、UNDO_LOG、REDO_LOG等机制来保证。

Innodb 的事务与日志的实现方式

innodb 两种日志： redo 和 undo。

日志的存放形式

• redo：在页修改的时候，先写到 redo log buffer 里面， 然后写到 redo log 的 文件系统缓存里面(fwrite)，然后再同步到磁盘文件（ fsync）。
• Undo：在 MySQL5.5 之前， undo 只能存放在 ibdata 文件里面， 5.6 之后， 可以通过设置 innodb_undo_tablespaces 参数把 undo log 存放在 ibdata 之外。

事务是如何通过日志来实现的

• 因为事务在修改页时， 要先记 undo， 在记 undo 之前要记 undo的 redo， 然后修改数据页， 再记数据页修改的redo。 Redo（里面包括 undo ✁修改） 一 定要比数据页先持久化到磁盘。
• 当事务需要回滚时，因为有 undo，可以把数据页回滚到前镜像的状态，崩溃恢复时，如果 redo log 中事务没有对应的 commit 记录，那么需要用 undo 把该事 务修改回滚到事务开始之前。
• 如果有 commit 记录，就用 redo 前滚到该事务完成时并提交掉。

mysql 中 in 和 exists 的区别

select * from A where deptId in (select deptId from B);

如果 B 的数据量小于 A，使用 in，如果 B 的数据量大于 A，选择 exists

读写分离常见方案？

• 应用程序根据业务逻辑来判断，增删改等写操作命令发给主库，查询命令发给备库。

• 利用中间件来做代理，负责对数据库的请求识别出读还是写，并分发到不同的数据 库中。（如：amoeba，mysql-proxy）

MySql的主从延迟，如何解决？

主从复制原理，简言之，就三步曲，如下：

• 主数据库有个 bin-log 二进制文件，纪录了所有增删改 Sql 语句。（binlog 线程）

• 从数据库把主数据库的 bin-log 文件的 sql 语句复制过来。（io 线程）

• 从数据库的relay-log 重做日志文件中再执行一次这些 sql 语句。（Sql 执行 线程）
  

主从复制分了五个步骤进行：
步骤一：主库的更新事件(update、insert、delete)被写到 binlog
步骤二：从库发起连接，连接到主库。
步骤三：此时主库创建一个 binlog dump thread，把binlog 的内容发送到从库。
步骤四：从库启动之后，创建一个 I/O 线程，读取主库传过来的 binlog 内容并写入到 relay log
步骤五：还会创建一个 SQL 线程，从 relay log 里面读取内容，从Exec_Master_Log_Pos 位置开始执行读取到的更新事件，将更新内容写入到slave的db

主从同步延迟的原因
一个服务器开放Ｎ个链接给客户端来连接的，这样有会有大并发的更新操作, 但是从服务器的里面读取 binlog 的线程仅有一个，当某个 SQL 在从服务器上执行的时间稍长或者由于某个 SQL 要进行锁表就会导致，主服务器的 SQL 大量积压，未被同步到从服务器里。这就导致了主从不一致， 也就是主从延迟。

主从同步延迟的解决办法

• 主服务器要负责更新操作，对安全性的要求比从服务器要高，所以有些设置参数可以修改，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置等。

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

• 把一台从服务器当度作为备份使用， 而不提供查询， 那边他的负载下来了， 执行 relay log 里面的 SQL 效率自然就高了。

• 增加从服务器喽，这个目的还是分散读的压力，从而降低服务器负载。

数据库连接池

首先，数据库连接池是一种池化技术，池化技术的核心思想是实现资源的复用，避免资源重复创建销毁的开销。 而在数据库的应用场景里面，应用程序每次向数据库发起CRUD操作的时候，都需要创建连接 ，在数据库访问量较大的情况下，频繁的创建连接会带来较大的性能开销。 而连接池的核心思想，就是应用程序在启动的时候提前初始化一部分连接保存到连接池里面，当应用需要使用连接的时候，直接从连接池获取一个已经建立好的链接。 避免了每次连接的建立和释放带来的开销。

数据库连接池好处：

• 资源重用 (连接复用)
• 更快的系统响应速度
• 新的资源分配手段
• 统一的连接管理，避免数据库连接泄漏

MySQL的基础架构图

Mysql 逻辑架构图主要分三层：

第一层负责连接处理，授权认证，安全等等

第二层负责编译并优化 SQL

第三层是存储引擎。

一条 SQL 语句在 MySQL 中如何执行的？

查询语句：

• 先检查该语句是否有权限
• 如果没有权限，直接返回错误信息
• 如果有权限， 在 MySQL8.0 版本以前，会先查询缓存。
• 如果没有缓存，分析器进行词法分析，提取 sql 语句 select 等的关键元素。然后 判断sql 语句是否有语法错误，比如关键词是否正确等等。
• 优化器进行确定执行方案
• 执行器进行权限校验，如果没有权限就直接返回错误信息，如果有权限就会调用数据库引擎接口，返回执行结果。

（重点）MySQL中的锁！！！

如果按锁粒度划分，有以下 3 种：

• 表锁： 开销小，加锁快；锁定力度大，发生锁冲突概率高，并发度最低;不会出现 死锁。
• 行锁： 开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发 度高。
• 页锁： 开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和 行锁之间，并发度一般

什么是内连接、外连接、交叉连接、笛卡尔积

• 内连接（inner join）：取得两张表中满足存在连接匹配关系的记录。
• 外连接（outer join）：取得两张表中满足存在连接匹配关系的记录，以及某张表 （或两张表）中不满足匹配关系的记录。
• 交叉连接（cross join）：显示两张表所有记录一一对应，没有匹配关系进行筛选， 也被称为：笛卡尔积。

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

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

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

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

说数据库的三大范式

• 第一范式：数据表中的每一列（每个字段）都不可以再拆分。
• 第二范式：在第一范式的基础上，分主键列完全依赖于主键，而不能是依赖于主键 的一部分。
• 第三范式：在满足第二范式的基础上，表中的非主键只依赖于主键，而不依赖于其 他非主键。

InnoDB引擎的 4 大特性

• 插入缓冲（insert buffer)
• 二次写(double write)
• 自适应哈希索引(ahi)
• 预读(read ahead)

索引有哪些优缺点？索引有哪几种类型？

在Mysql中的InnoDB引擎中，采用了B+树的结构来实现索引和数据的存储。可以大大减少数据检索时的磁盘IO次数，从而提升数据查询的性能 B+树索引在进行范围查找的时候，只需要找到起始节点，然后基于叶子节点的链表结构往下读取即可，查询效率较高。
通过唯一索引约束，可以保证数据表中每一行数据的唯一性

当然，索引的不合理使用，也会有带来很多的缺点。
数据的增加、修改、删除，需要涉及到索引的维护，当数据量较大的情况下，索引的维护会带来较大的性能开销。 一个表中允许存在一个聚簇索引和多个非聚簇索引，但是索引数不能创建太多，否则造成的索引维护成本过高。

索引有哪几种类型？

• 主键索引: 数据列不允许重复，不允许为 NULL，一个表只能有一个主键。
• 唯一索引: 数据列不允许重复，允许为 NULL 值，一个表允许多个列创建唯一索引。
• 普通索引: 基本的索引类型，没有唯一性的限制，允许为 NULL 值。
• 全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。
• 覆盖索引：查询列要被所建的索引覆盖，不必读取数据行
• 组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并

创建索引有什么原则呢？

• 最左前缀匹配原则
  • 最左优先，在创建多列索引时，要根据业务需求，where 子句中使用最频繁的一列放在最左边
  • 当我们创建一个组合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、 (k1,k2)和(k1,k2,k3)三个索引
• 频繁作为查询条件的字段才去创建索引
• 频繁更新的字段不适合创建索引
• 索引列不能参与计算，不能有函数操作
• 优先考虑扩展索引，而不是新建索引，避免不必要的索引
• 在 order by 或者group by 子句中，创建索引需要注意顺序
• 区分度低的数据列不适合做索引列(如性别）
• 定义有外键的数据列一定要建立索引。
• 对于定义为 text、image 数据类型的列不要建立索引。
• 删除不再使用或者很少使用的索引

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

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

InnoDB中的锁

共享/排他锁

InnoDB 实现了两种标准的行级锁：共享锁（简称 S 锁）、排他锁（简称 X 锁）。

共享锁：简称为 S 锁，在事务要读取一条记录时，需要先获取该记录的 S 锁。
排他锁：简称 X 锁，在事务需要改动一条记录时，需要先获取该记录的 X 锁。

S锁和S锁直接不冲突，S锁和X锁，X锁和X锁直接都冲突

如果一个事务给表已经加了 S 锁， 则：
别的事务可以继续获得该表的 S 锁， 也可以获得该表中某些记录的 S 锁。
别的事务不可以继续获得该表的 X 锁， 也不可以获得该表中某些记录的 X 锁。

如果一个事务给表加了 X 锁，那么
别的事务不可以获得该表的 S 锁， 也不可以获得该表某些记录的 S 锁。 别的事务不可以获得该表的 X 锁， 也不可以继续获得该表某些记录的 X 锁。

意向锁
它的作用是告诉其他事务，当前事务已经在某个行上持有了锁，其他事务如果想要在该行上持有锁则必须要先获得该行的意向锁。
解决在innoDB引擎中，表锁和行锁的冲突问题（自动加）
意向锁是一种不与行级锁冲突的表级锁。未来的某个时刻， 事务可能要加共享或者排它锁时，先提前声明一个意向。注意一下，意向锁， 是一个表级别的锁哈。
为什么需要意向锁呢？ 或者换个通俗的说法，为什么要加共享锁或排他锁时的 时候，需要提前声明个意向锁呢呢？
因为InnoDB 是支持表锁和行锁共存的，如果一个事务 A 获取到某一行的排他锁，并未提交，这时候事务 B 请求获取同一个表的表共享锁。因为共享锁和排 他锁是互斥的，因此事务 B 想对这个表加共享锁时，需要保证没有其他事务持 有这个表的表排他锁，同时还要保证没有其他事务持有表中任意一行的排他锁。 然后问题来了，你要保证没有其他事务持有表中任意一行的排他锁的话，去遍 历每一行？这样显然是一个效率很差的做法。为了解决这个问题，InnoDb 的设计大叔提出了意向锁。

意向锁分为两类：

• 意向共享锁：简称 IS锁，当事务准备在某些记录上加 S 锁时，需要现在表级别加 一个 IS锁。

• 意向排他锁：简称 IX锁，当事务准备在某条记录上加上 X 锁时，需要现在表级别 加一个 IX锁。

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

count，其实是一个聚合函数，聚合数据的总数。 不同的存储引擎实现的方式可能又稍许不一样，我们以默认的innoDB存储引擎为例。 其实就是根据where条件去进行扫描，得到扫描结果后，一行一行去判断，如果count括号里的不是 null，那么累计值+1，否则不加，最后返回一个累计的总数。 所以，括号里面的值在一定程度是会影响性能的。
count(*) ，也就是整条数据，由于整条数据肯定不会为null，所以，专门做了优化，不会去取全部字段。直接扫描多少数据统计数据即可。
count(1) 扫描到一条数据，直接返回一个数字1，不会取扫描的字段，也不会去判断是否为null。
这2个也是我们用得最多的，性能最好的。

count(字段)，有2种情况。
1.首先去看字段是否能走索引，如果没索引，那么扫描数据不会走索引树，比有索引的字段慢。
2.字段是否可为null，如果可为null，那么必须要去判断扫描出来的数据是否为null，为null不统计， 所以不可为null的比可为null的要快。

SQL 约束有哪几种呢？

• NOT NULL: 约束字段内容一定不能为 NULL。
• UNIQUE: 约束字段唯一性，一个表允许有多个 Unique 约束。
• PRIMARY KEY: 约束字段唯一，不可重复，一个表只允许存在一个。
• FOREIGN KEY: 用于预防破坏表之间连接✁动作，也能防止非法数据插入外键。
• CHECK: 用于控制字段值范围。

varchar(50)中 50 的涵义

• 字段最多存放 50 个字符
• 如 varchar(50) 和 varchar(200) 存储 “jay” 字符串所占空间是一样的，后者在 排序时会消耗更多内存

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

• int(20) 表示字段是 int 类型，显示长度是 20
• char(20)表示字段是固定长度字符串，长度为 20
• varchar(20) 表示字段是可变长度字符串，长度为 20

drop、delete 与truncate 的区别

UNION 与UNION ALL 的区别

• Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

• Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

UNION 的效率高于 UNION ALL

SQL 的生命周期？

• 服务器与数据库的立连接
• 数据库进程拿到请求 sql
• 解析并生成执行计划，执行
• 读取数据到内存，并进行逻辑处理
• 通过步骤一的连接，发送结果到客户端
• 关掉连接，释放资源

一条 Sql 的执行顺序

主键使用自增 ID 还是 UUID，为什么？

如果是单机✁话，选择自增 ID；如果是分布式系统，优先考虑 UUID 吧，但 还是最好自己公司有一套分布式唯一 ID 生产方案吧。

• 自增 ID：数据存储空间小，查询效率高。但是如果数据量过大,会超出自增长✁值 范围，多库合并，也有可能有问题。
• uuid：适合大量数据插入和更新操作，但是它无序，插入数据效率慢，占用 空间大。

mysql 自增主键 用完了怎么办？

自增主键一般用 int 类型，一般达不到最大值，可以考虑提前分库分表

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

CPU是整个电脑的核心计算资源，对于一个应用进程来说，CPU的最小执行单元是线程。 导致CPU飙高的原因有几个方面

• CPU上下文切换过多
  • 对于CPU来说，同一时刻下每个CPU核心只能运行一个线程，如果有多个线 程要执行，CPU只能通过上下文切换的方式来执行不同的线程。
  • 上下文切换需要做两个事情
    • 保存运行线程的执行状态
    • 让处于等待中的线程执行
    • 这两个过程需要CPU执行内核相关指令实现状态保存，如果较多的上下文切换会占据大量CPU资源， 从而使得cpu无法去执行用户进程中的指令，导致响应速度下降。 在Java中，文件IO、网络IO、锁等待、线程阻塞等操作都会造成线程阻塞从而触发上下文切换
• CPU资源过度消耗
  • 程序中创建了大量的线程
  • 有线程一直占用CPU资源无法被释放， 比如死循环！ CPU利用率过高之后，导致应用中的线程无法获得CPU的调度，从而影响程序的执行效率！

既然是这两个问题导致的CPU利用率较高，于是我们可以通过top命令，找到CPU利用率较高的进程，在通过Shift+H找到进程中CPU消耗过高的线程，这里有两种情况。

• CPU利用率过高的线程一直是同一个，说明程序中存在线程长期占用CPU没有释放的情况，这种情 况直接通过jstack获得线程的Dump日志，定位到线程日志后就可以找到问题的代码。
• CPU利用率过高的线程id不断变化，说明线程创建过多，需要挑选几个线程id，通过jstack去线程 dump日志中排查。 最后有可能定位的结果是程序正常，只是在CPU飙高的那一刻，用户访问量较大，导致系统资源不够。