一.在项目中如何定位慢查询

  • 通过 druid 连接池的内置监控来定位慢 SQL。
  • 通过 MySQL 的慢查询日志查看慢 SQL。
  • 通过 show processlist，查看当前数据库 SQL 执行情况来定位慢 SQL。

二.如何查看sql语句索引命中情况

在 SQL 语句前加上 explain，结果中的 key 就是实际用到的索引

三.说说数据库锁表的场景和解决方法

  1. 产生场景

    锁表通常发生在 DML（ insert 、update 、delete ）语句中，例如：程序 A 对 A 表的 a 数据 进行修改，修改过程中产生错误，没有 commit 也没有 rollback ，这个时候程序 B 对 A 表的 a 数据进行修改，会产生资源正忙的异常，也就是锁表。

    DDL也会引发锁表，例如在 MySql 操作一张大表，利用 alter 语句修改或新增字段的时候，恰巧有一个长事务（包括读）在操作此表，会触发修改等待，造成锁表。

  2. 原因

    当多个事务处理对多个资源同时访问时，若双方已锁定一部分资源但也都需要对方已锁定的资源时，无法在有限的时间内完全获得所需的资源，就会处于无限的等待状态，从而造成其对资源需求的死锁，导致锁表。

  3. MySQL解决办法

    • 执行SQL

      1
      select * from information_schema.processlist where command not in ('Sleep') ORDER BY time desc

    • sql 已经按照阻塞时长从大到小排序，找到耗时长的记录 id ，kill 即可：

      1
      kill pid

四.truncate和delete的区别

1.delete是DML语句，truncate是DDL语句；

2.delete后面可以有where条件，truncate后面不能加where条件；

3.delete操作后，事务提交后才会生效，truncate操作后立即生效，所以truncate要比delete得执行效率高（数据量大的话就比较明显了）；

4.delete操作后，是按行删除，原数据会放到rollback segment中，可进行回滚。truncate执行是立即生效的，不记录行删除信息，而是直接重构表，所以不能回滚；

5.delete执行后，会产生数据碎片，只删除数据，不会改变表空间大小。truncate执行后，数据库表会恢复至初始状态。

五.union与union all的区别

1、union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序;会对获取的结果进行排序操作

2、union all: 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复;不会对获取的结果进行排序操作

union all只是合并查询结果，并不会进行去重和排序操作，在没有去重的前提下，使用union all的执行效率要比union高

六.left join,right join inner join区别

  • LEFT JOIN 左连接，以左侧表为主，右侧表与其相匹配，匹配不上的记录 ，以null 做替补；

  • RIGHT JOIN右连接，以右侧表为主，左侧表与其相匹配，匹配不上得记录，以null做替补;

  • INNER JOIN内连接，取交集。

七.MySQL执行流程

  1. 客户端发起 SQL 查询，首先通过连接器，它会检查用户的身份，包括校验账户密码，权限，然后会查询缓存，如果缓存命中直接返回，如果没有命中再执行后续操作，但是MySQL8.0 之后已经删除了缓存功能；
  2. 接下来到达分析器，主要检查语法词法，比如 SQL 有没有写错，总共有多少关键字；要查询哪些东西；
  3. 然后到达优化器，他会以自己的方式优化我们的 SQL；
  4. 最后到达执行器，调用存储引擎执行 SQL 并返回结果。

八.MySQL语句执行顺序

1
from->on->join->where->group by->having->select->distinct->order by->limit

九.说说数据库设计三范式

  1. 第一范式：任何一张表都应该有自己的主键，并且每一个字段的原子性都是不可再分的。
    • 列唯一，每一个字段具有原子性不可再分；
    • 每一张表都有主键。
  2. 第二范式：在第一范式的基础上，要求所有的非主键字段完全依赖主键，不能产生部分依赖。
    • 行唯一
  3. 第三范式：在第二范式的基础上，所有非主键只能依赖于主键，不能产生传递依赖。
    • 存在外键，比如学生表中可以有班级表的主键进行关联。

十.CHAR 和 VARCHAR 的区别

char是一种固定长度的类型，无论储存的数据有多少都会固定长度，如果插入的长度小于定义长度，则可以用空格进行填充。而varchar是一种可变长度的类型，当插入的长度小于定义长度时，插入多长就存多长。

  1. char最大长度是255字符，varchar最大长度是65535个字节。
  2. char是定长的，不足的部分用隐藏空格填充，varchar是不定长的。
  3. char会浪费空间，varchar会更加节省空间。
  4. char查找效率会很高，varchar查找效率会更低。
  5. 尾部空格：char插入时可省略，varchar插入时不会省略，查找时省略。

十一.DDL,DML优化手段有哪些,批量插入

  1. DDL:在导入数据时，可以通过禁用索引来提高导入数据性能 。这个操作主要针对有数据的表，追加数据。

  2. DML:变多次事务提交为一次事务提交。批量插入

    1
    insert into test values(1,2),(1,3),(1,4);

十二.MySQL存储引擎

  1. InnoDB存储引擎

    InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。

    • 支持事务，行锁，表锁，不支持全文索引，数据库宕机后可以恢复数据。

  2. MyISAM存储引擎

    MyISAM拥有较高的插入、查询速度，但不支持事物。

    • 不支持外键，数据崩溃后无法恢复，只支持表锁。

  3. MEMORY存储引擎

    不支持事务

    表级锁定

    读写相互阻塞，写入不能读，读时不能写

    只缓存索引

    不支持外键约束

    不支持聚簇索引

    读取数据较快，占用资源少

    不支持MVCC 高并发 (多版本并发控制机制)

    崩溃恢复性较差

    mysql 5.5.5 前的默认数据引擎（show engines; 查看）

    • 不支持外键

    | 功 能 | MYISAM | Memory | InnoDB | Archive |
    | —————— | ————— | ————— | ————— | —————- |
    | 存储限制 | 256TB | RAM | 64TB | None |
    | 支持事物 | No | No | Yes | No |
    | 支持全文索引 | Yes | No | No | No |
    | 支持数索引 | Yes | Yes | Yes | No |
    | 支持哈希索引 | No | Yes | No | No |
    | 支持数据缓存 | No | N/A | Yes | No |
    | 支持外键 | No | No | Yes | No |

十三.MySQL行锁与表锁

  1. 行锁：MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。InnoDB存储引擎默认采用行锁。InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）;二是采用了行级锁。

    特点：行锁开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

  2. 表锁：MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

    特点：表锁开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发最低。

十四.MySQL索引有哪些类型

  1. 普通索引：允许重复的值
  2. 唯一索引：不允许有重复的值
  3. 主键索引：数据库自动为我们的主键创建索引，如果我们没有指定主键，它会根据没有 null 的唯一索引创建主键索引，否则会默认根据一个隐藏的 rowId 作为主键索引
  4. 全文索引，用来对文本域进行索引，比如 text，varchar，只针对 MyISAM 有效

十五.索引方式有哪些

B+树和 hash，Myisam 和 innodb 都不支持 hash。

十六.索引失效的情况有哪些

  1. like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
  2. or语句前后没有同时使用索引。
    • 当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
  3. 组合索引，不是使用第一列索引，索引失效。
  4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引
    • 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
  5. 在索引列上使用 IS NULL 或 IS NOT NULL操作。
    • 索引是不索引空值的，所以这样的操作不能使用索引
  6. 在索引字段上使用not，<>，!=。
    • 不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
  7. 对索引字段进行计算操作、字段上使用函数。
  8. 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。
    • 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

十七.MySQL索引的数据结构,B+树的特点

  1. 数据结构：B+树，HASH。
  2. 特点
    • 非叶子节点仅具有索引作用，也就是说，非叶子节点只能存储Key,不能存储value
    • 树的所有叶节点构成一个有序链表，可以按照key排序的次序依次遍历全部数据。

十八.MySQL主从复制原理

  1. 主要依靠 binlog 来实现的，它记录的是所有的 DDL，DML，TCL 操作
  2. 当主库的数据发生改变时，会将改变记录保存到 binloga2 中
  3. 主库新开一个线程将 binlog 内容发送到从库
  4. 从库会发起一个 I/O 线程请求主库的 binlog，并保存到中继日志中
  5. 从库新开一个 SQL 线程，读取中继日志并解析成具体操作，从而将主库更新的内容写到了从库中

十九.集群模式有哪些,分别有什么优缺点?

  1. 主从复制：一主一从，一主多从，主负责读写，从只能读，一对一或一对多关系。
  2. 哨兵模式：基于主从模式，哨兵实际上是一个单独进程，用来监听主服务是否宕机，如果宕机则将从转为主，并且通知其他的服务器修改配置文件，让它们切换主机。哨兵模式也可配置多哨兵，多哨兵之间互相监控。
  3. 集群模式：集群是将应用复制成多个相同应用，分散在不同服务器，每个服务器都独立运行相同的代码，可以分散服务器压力解决高并发问题，同时也能预防单点节点故障，就是一台服务器故障并不影响其他服务器正常运行，但没有解决单体应用代码臃肿，业务复杂，维护性差等问题。

​