读完这篇，你再也不怕mysql面试了！

基础概念

什么时候Mysql？

mysql 是一个关系型数据库管理系统，属于Oracle旗下产品。是一款开源免费的产品。

什么是关系型数据库？

关系型数据库是依据关系模型来创建的数据库。
所谓关系模型就是“一对一、一对多、多对多”等关系模型，关系模型就是指二位表格模型，因此关系型数据库就是由二维表机器之间的关系组成的一个数据组织。

常见的关系型数据库有哪些？

Oracle、DB2、Mysql、SQL Server等

关系型数据库的特点是什么？

安全（因为存储在磁盘中，不会说突然断电数据就没有了）

什么是非关系型数据库？

基于非关系模型的数据库。

非关系型数据库的分类有哪些？

• 列模型：存储的数据是一列一列的，以一列作为一个记录，这种模型，数据即索引，IO很快，主要是一些分布式数据库。Hbase
• 键值对模型：存储的是一个一个的键值对。redis、MemcacheDB
• 文档类模型：以文档来存储数据，有点类似键值对。MongoDB

非关系型数据库特点有哪些？

效率高，因为是存储在内存中
不安全，断电会丢失数据，但redis可以同步数据到磁盘中，现在很多非关系型数据库都开始支持存储到磁盘。

数据库设计三范式是什么？

• 第一范式：每个列都不可以再拆分。
• 第二范式：在第一范式的基础上，非主键列完全依赖与主键列，而不能依赖于主键的一部分。
• 第三范式：符合第二范式，非主键列只依赖主键，不依赖与其他非主键。
  事实上，我们经常会为了性能妥协数据库的设计。

Mysql 的binglog…TODO

Mysql 有哪些数据类型？

• 整数类型，Tinyint、smallint、mediumint、int、bigint
  分别代表 1 字节、2字节、3字节、4字节、8字节。任何数据都可以加上unsigned 属性，表示非负整数。例如：int(11) 表示长度为11的int类型，长度
  在大多数场景下没有意义，它不会限制值的合法范围，只会影响显示字符的个数
• 实数类型，float、double、decimal。
• 字符串类型，varchar、char、text、blobvarchar用于存储可变长字符串，比定常类型更节省空间。varchar 使用额外1或者2字节存储字符串长度，列长度小于255字节时，使用1字节，否则使用2字节。varchar 存储的内容超出设置的长度时，内容会被截断。char 是定长，适合存储很短的字符串，存储内容超出设置的长度时，内容会被截断。
• 枚举类型
• 日期和时间类型，尽量使用timestamp ，空间效率高于datetime，如果要保存微秒，可使用bigint 存储。

mysql 数据库执行 sql 的原理

• 客户端连接连接器

  连接器是负责客户端跟 mysql 服务端建立连接，以及验证身份和权限。

• 连接器查询缓存文件，如果命中，就直接返回结果

  缓存文件很容易失效，只要对表做增删改操作，缓存会清空

  mysql 8 以后取消了缓存功能

• 否则分析器会对 sql 进行语法分析

• 优化器对 sql 检测，是否可以优化，是否用到索引，以及索引选择

• 执行器操作存储引擎（存储引擎提供读写接口），返回数据

mysql 的事务

ACID

一致性是最终结果

保证原子性的原理

mysql 利用 undo 日志，记录了执行过的 sql 语句，当需要回滚时，找到该 sql ，回滚。

事务的并发问题产生的后果

• 脏读

  事务1读取到了事务2更新后的数据，事务2回滚，事务1读到的数据就是脏数据。

  读未提交解决了脏读问题。

• 不可重复读

  事务 1 多次读取同一个数据，事务2再次期间对数据做了修改，事务1读取到的结果不一样

  不可重复读解决了这个问题，原理是行锁。

• 幻读

  事务1读取一批数据，再次期间，事务2插入一条数，此时，事务1事务还没结束，事务1再次读取，发现

  多了一条。产生幻觉的现象。

  可重复读解决了幻读问题。这里其实有一个锁的操作，事务1在读取的时候，锁住了符合条件的数据，在事务

  实行完毕之前，锁不会被释放。这里的锁表锁。

mysql 的事务隔离级别

读未提交、不可重复读、可重复读（默认）、串行化（最高级别-共享锁）

select @@tx_isolation

MVCC

多版本并发控制

mvcc 对读操作不加锁，依靠读取上一个版本的数据，提高性能。

对于事务开始的每条数据，隐士的增加两列，一列保存当前事务id 和行事务id, 第一次事务开始之前相同

某一个事务开始时，会增加当前事务id, 当其他事务读取该数据时，比较两个id 是否一致，否则读取保存的

上一个版本的快照数据。

mysql 存储引擎

• myisam 表锁
• innodb - 5.5 默认 ，行锁、表锁
  • b + tree
  • hash （redis 、mem）

索引数据结构

• 二叉树
• 红黑树 - 自旋、平衡
• B 树
• B + tree - 叶子节点链接

索引

• 主键索引和非主键索引

  回表…

• 唯一索引和普通索引

• 全文索引

• 复合索引

• 聚簇索引 - 物理索引、聚簇数据，隐式 rowid

• 非聚簇索引

调优

1：首先排除数据库缓存的干扰，在 mysql 8 之前，mysql 支持缓存
，因为存在缓存，执行sql 无论如何都是执行很快的
当然第一次执行慢，使用 noCache 排除缓存干扰
其次，借助 explain 查看执行计划去分析
或者 force index 强制

2：explain

• type ： 表明sql 效率的级别是否用到了索引
  一般有 null ，all 是不走索引的，system 和const 是走索引的
• extra : 额外的信息说明
  extra 一般会有 using where 说明使用了where 条件过滤 ，一般在where 后面的字段加索引
  如果是 using index , 说明sql 所需要的字段都在一棵索引树上，无序访问实际的行记录，这类语句性能比较好
  如果是 using index condition ,说明确实命中了索引，但不是所有的数据列都在索引树上，还要访问实际的行记录，这类语句性能也比较高，但是不如 using index，不用回表。
  如果是 using filesort ,说明得到结果集，需要对所需记录进行文件排序
  这类sql 性能较差，需要优化
  比如： 在 order by 后面的列没有加索引，就会出现 using fildsort , 索引在order by 后面的字段加索引，避免每次都走全量排序
  如果是using trmporary ，说明查询过程mysql 创建了临时表，可能是做了子查询，修改为join 查询，或者group by 和order by 同时使用，但是作用在不同字段上。
  如果是 using join buffeer, 说明进行了嵌套循环查询，比如两个表做了 join ，关联字段没有建立索引。

3：数据库设计的三范式

• 列原子性
• 有主键，依赖
• 直接依赖

4：选择合适的字段

​ varchar|char

5：join

​ select * from
​ customerInfo
​ where customerId not in (select customerId from salesInfo )

使用 join 优化 ：
select * from
customerInfo a
left join salesInfo b on a.customerId = b.customerId
where b.customerId is null

join 之所以效率高一些，mysql 不需要再内存中创建临时表来完整这个逻辑上需要两步的操作。

6：模糊查询

a like “x%” // 走索引
a like “%x%” // 全表扫描
a like “%x” // 全表扫描

7：索引失效

1. 只要列中包含了 null , 或者复合索引只要有一列为 null,索引都会失效，所以在设计数据库时不要让字段默认值为null，普通索引，查询条件为is null、is not null 索引会失效
2. like 前面有% 会失效，后面有% 不会失效
3. or 前后只要一个使用索引，索引会失效
4. 复合索引没有遵守最左匹配原则
5. 在索引字段上使用 not、<>、！= 索引会失效
6. 索引字段的函数计算
7. 索引字段是字符串，查询时不加单引号，索引会失效
   8：having 和 where
   where 可以使用索引，不能使用聚集函数
   having 不能使用索引 ，可以使用聚集函数

mysql 书籍

高性能 mysql
MySQL实战
mysql 技术内幕