面试—MySQL

目录

多表查询

事务

存储引擎

索引结构

索引分类

SQL性能优化

索引失效

视图

存储过程

触发器

MySQL锁

全局锁

表锁

行锁

多表查询

分类

1. 内连接

   只返回两个表符合条件的数据，相关联的数据（两个表的交集）

2. 外连接

   • 左外连接以左表为基础，返回左表的数据以及右表中匹配的数据，如果查询不到右边数据，将列数据填为NULL

   左外连接，查询左表的数据以及交集数据

   右外连接，查询右表的数据以及交集数据

3. 自连接

   将一个表看作两个表进行连接操作，可以是内连接也可以是外连接

联合查询

将查询结果合并成起来，查询的语句必须表字段一致

子查询

将查询的结果作为条件再次查询，嵌套查询

事务

• 事务的四大特性

1. 原子性，事务中的指令必须全部成功或者全部失败

2. 隔离性，事务具有隔离性，执行事务不会被其他指令干扰

3. 一致性，事务完成后，数据必须保证一致

4. 持久性，事务一旦提交或者回滚，数据就会永久性发生变化

• 并发事务

1. 脏读，一个事务读取到了另一个事务还未提交更新的数据

2. 不可重复读，一个事务先后读取同一个数据，数据不一致（另一个事务对数据进行了更新）

3. 幻读，一个事务先查询数据发现不存在，但是在插入时数据已经存在（另一个事务先进行了插入）

• 事务的隔离级别

1. 读未提交（Read uncommitted），事务会读取到其他事务还未提交的数据，可能会造成

   脏读（事务A读取到事务B还未提交更新的数据，或事务B回滚数据，事务A读取到是错误数据）

   不可重复读

   幻读

2. 读提交（Read committed），事务先后读取同一数据，数据可能不一致，可能造成

   不可重复读（事务A读取数据，一段时间后再次读取，但是事务B已经对数据进行了更新，此时事务A两次读取的数据不一样

   幻读（事务A想要执行插入数据，先去查询这个数据是否存在，查询到不存在，执行插入操作。但是事务B此时插入了这个数据，事务A无法在插入）

3. 可重复读（Repeatable Read）（默认）

   MySQL默认隔离级别

   事务在先后读取数据，得到的数据是一致的，但是存在幻读问题

4. 串行化（Serializable）

   最高隔离机制，事务不会受其他事务影响，按顺序执行事务。事务必须等待前一个事务执行完毕

存储引擎

• MySQL体系结构

  连接层，当客户端发送连接请求时，进行连接处理（建立连接）

  服务层，存储最近查询的语句和结果，存入到数据缓存中。对SQL语句进行解析

  引擎层，负责数据的存储和处理

  存储层，磁盘IO操作，将数据存储到磁盘中

• MySQL中的存储引擎

  1. InnoDB（Mysql默认引擎）

     • 支持事务，支持行锁，支持外键

     • 使用B+树索引结构

     • 存储逻辑分为：表空间>段>区（64个页）>页（索引中存放数据和指针 16K）>行（行数据）

  2. MyISAM

     • 不支持事务，不支持行锁，不支持外键，访问速度快，主要以读取数据和写入数据为主，很少对数据进行删除和修改

     MyISAM的索引和数据分开存储在不同的文件中，数据按插入顺序排列，顺序读取时访问速度快

     底层是B+树的索引结构，通过索引查找数据时更高效

     不支持事务，不用处理复杂的事务管理

     只支持表级锁，写入操作时会锁定整个表

  3. Memory

     • 内存存放是Hash索引，数据保存在内存中，访问速度快，常用于临时表和缓存

索引结构

索引结构主要是B+树索引和Hash索引

1. 当树为二叉树时，顺序插入会形成一个链表，查询效率低

2. 红黑树时，层级较深，检索速度慢

3. B树的插入、检索效率都高

   以5阶为例，当一个页中存满4个元素时，插入元素会将这5个元素中的中间元素向上分裂。直到上面的页也存满，继续分裂

4. B+树，基于B树，但叶子节点存放数据（单向链表），而非叶子节点只是起到一个索引数据的作用

   B树的页中存放的是数据和指针，而B+树只在叶子节点存放数据，在相同数据量下，层级比B+树高，检索的效率不如B+树

5. 而InnoDB采用的就是B+树，同时对B+树的叶子节点进行优化，由单向链表优化为双向链表

• Hash索引

  将键值对通过哈希函数计算出哈希值，映射到Hash表的槽位上

  如果出现哈希冲突，使用链地址法，插入链表末端

  1. 当根据一个字段查找时，会先计算这个字段的哈希值，根据哈希值找到哈希表中对应数据的位置

  2. 查找效率比B+树高，但是只能用于对等比较（=，in），不能范围查询（between，<，>）

索引分类

1. 主键索引，针对字段中的主键

2. 唯一索引，避免字段中的数据重复

3. 常规索引，快速定位特点数据

4. 全文索引，查找文本中的关键字

• 在InnoDB中，主要是

1. 聚集索引，主要是对主键，叶子节点存放的是行数据

2. 二级索引，将数据和索引分开存储，叶子节点关联的是行的主键

3. 再查找时，使用回表查询，如果根据二级索引区查找，会先查找叶子节点，对应的主键值，在进行对聚集索引根据主键值进行查找行数据

SQL性能优化

1. SQL执行频率

   show global status like 'Com_______;'

   可以查看数据库的增删改查访问频次

2. 慢查询日志

   在mysql的配置文件中开启慢查询日志，设置慢日志时间

   当sql语句执行时间超过慢日志时间就会被记录

3. profile详情

   show profile可以查看每条SQL的耗时基本情况

4. explain执行计划（常用）

   在sql语句前加explain关键字，可以查看sql语句可能用到的索引、实际用的索引等信息

索引失效

1. 查询不遵循最左前缀法则

   创建的联合索引，在查询时必须保证最左边的字段被查询

   在联合索引中，从哪个字段断开，索引就从哪开始失效

2. 范围查询

   在联合索引中，出现了范围查询，则范围右侧的列索引失效

3. 索引列运行

   在索引列上进行运算操作，索引会失效

4. 模糊查询

   如果是尾部模糊查询，则不会失效（软件%）

   如果是头部模糊查询，则索引失效（%工程）

5. or连接的条件

   or连接的两个条件，如果是索引列和非索引列，则索引全部都失效

6. 数据分布影响

   如果全表扫描的效率更好，则索引也会失效

视图

• 视图是一个虚拟存在的表，基于一个表或多个表的查询结果创建，可以将复杂的查询语句封装到一个视图中（将查询多张表的复杂语句），用户只需对视图简单查询就可以得到结果（无需每次编写复杂语句）

• 可以限制用户对敏感的数据进行访问，限制用户查看的权限

• 如果底层的表的结构发送变化（字段、数据类型），只要视图可以正确的获取数据，就可以不对视图修改

存储过程

• 存储过程是一组执行特点功能的SQL语句集，经过编译后存储在数据库中

• 存储过程在数据库服务器上执行，客户端只需发送执行存储过程的请求就行，不需要发送大量SQL语句

• 将复杂的业务逻辑写入存储过程中，业务逻辑发生变化，只需对存储过程修改，不需要修改多个地方的SQL语句

• 存储过程相当于一个方法

触发器

• 触发器是针对特点的数据库表

• 事件触发时对表的增删改操作

• 可以在触发器触发之前或者之后，执行一些操作（一组SQL语句）

MySQL锁

全局锁

• 对整个数据库加锁，锁定数据库中所有的表，加锁后整个数据库都是只读状态

表锁

• 对整个表进行加锁，分为表共享锁和表独占锁

1. 表共享锁（read lock）

   加锁后，其他事务不可以对表执行写入操作，只能读取

2. 表独占锁（write lock）

   加锁后，其他事务不可以多表执行写入和读取操作，相当于阻塞其他事务

行锁

• 对表中的一行数据加锁

• 当一个事务对一行数据加共享锁时，其他事务可以对这行数据加共享锁，不可以加排他锁。表示多个事务可以对一行数据进行读取，不能修改

• 当一个事务对一行数据加排他锁时，其他事务可以对这行数据不可以加共享锁，也不可以加排他锁。表示一行数据只能由一个事务修改，其他事务只能等锁释放才能进行操作