MySQL面经

数据类型

数值类型

有 tinyint、smallint、mediumint、int、bigint，分别为1 字节、2 字节、3 字节、4 字节、8 字节的整数类型。（任何整数类型都可以加上 UNSIGNED 属性，表⽰⽆符号整数。指定长度不会限制数据的合法长度，仅限制了显⽰长度。）还有包括 FLOAT、DOUBLE、DECIMAL 在内的⼩数类型。

字符串类

概念

包括 varchar、char、text、blob (VARCHAR(n) 和 CHAR(n) 中的 n 并不代表字节个数，⽽是代表字符的个数)

CHAR 和 VARCHAR 区别(char:0-255|varccr:0-255/1 or -64k/2)

(1)⾸先可以明确的是 CHAR 是定长的(使⽤空格对字符串右边进⾏尾部填充,检索时会删除)，⽽ VARCHAR 是可以变长（据开头使⽤额外 1~2 个字节存储字符串长度，在结尾使⽤ 1 字节表⽰字符串结束）。

(2)再者，在存储⽅式上，CHAR 对英⽂字符（ASCII）占⽤ 1 字节，对⼀个汉字使⽤⽤ 2 字节。⽽ VARCHAR 对每个字符均使⽤ 2 字节。

(3)对于经常变更的数据来说，CHAR ⽐ VARCHAR更好，因为 CHAR 不容易产⽣碎⽚。

(4)对于⾮常短的列或固定长度的数据（如 MD5），CHAR ⽐ VARCHAR 在存储空间上更有效率。

时间类型

常⽤于表⽰⽇期和时间类型为 datetime、date和 timestamp （timestamp 效率高于 datetime）

数据库设计

三大范式

第一范式：原子性，不可再分
第二范式：第一范式基础上与主键直接关联（列字段依赖主键，可以通过主键所代表的表对象定义关联字段）
第三范式：满足一二范式基础上，列字段不能冗余（可以通过表关联展示的字段，应放到关联表中）

范式化的表减少了数据冗余，数据表更新操作快、占⽤存储空间少；查询时通常需要多表关联查询，更难进⾏索引优化

反范式的过程就是通过冗余数据来提⾼查询性能，可以减少表关联和更好进⾏索引优化；存在⼤量冗余数据，并且数据的维护成本更⾼

索引

类别：物理上

聚簇索引指索引的键值的逻辑顺序与表中相应⾏的物理顺序⼀致，即每张表只能有⼀个聚簇索引，也就是我们常说的主键索引；

⾮聚簇索引的逻辑顺序则与数据⾏的物理顺序不⼀致。

应用

• 普通索引：MySQL 中的基本索引类型，没有什么限制，允许在定义索引的列中插⼊重复值和空值，纯粹为了提⾼查询效率。通过 ALTER TABLE table_name ADD INDEX index_name (column) 创建；
• 唯⼀索引：索引列中的值必须是唯⼀的，但是允许为空值。通过 ALTER TABLE table_name ADD UNIQUE index_name (column) 创建；
• 主键索引：特殊的唯⼀索引，也成聚簇索引，不允许有空值，并由数据库帮我们⾃动创建；
• 组合索引：组合表中多个字段创建的索引，遵守最左前缀匹配规则；
• 全⽂索引：只有在 MyISAM 引擎上才能使⽤，同时只⽀持 CHAR、VARCHAR、TEXT 类型字段上使⽤。

优缺点

优点：

• 通过创建唯⼀性索引，可以保证数据库表中每⼀⾏数据的唯⼀性。
• 可以⼤⼤加快数据的检索速度，这也是创建索引的最主要的原因。
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性⽅⾯特别有意义。
• 在使⽤分组和排序⼦句进⾏数据检索时，同样可以显著减少查询中分组和排序的时间。
• 通过使⽤索引，可以在查询的过程中，使⽤优化隐藏器，提⾼系统的性能。

缺点：

• 创建和维护索引需要耗费时间，这种时间随着数据量的增加⽽增加，这样就降低了数据的维护速度。
• 索引需要占物理空间，除了数据表占数据空间之外，每⼀个索引还要占⼀定的物理空间。如果要建⽴聚簇索引，那么需要的空间就会更⼤。

索引数据结构

Hash：底层就是 Hash 表，进⾏查询时调⽤ Hash 函数获取到相应的键值（对应地址），然后回表查询获得实际数据

• Hash 进⾏等值查询更快，但⽆法进⾏范围查询。因为经过 Hash 函数建⽴索引之后，索引的顺序与原顺序⽆法保持⼀致，故不能⽀持范围查询。同理，也不⽀持使⽤索引进⾏排序
• Hash 不⽀持模糊查询以及多列索引的最左前缀匹配,因为 Hash 函数的值不可预测，如 AA 和 AB 的算出的值没有相关性
• Hash 任何时候都避免不了回表查询数据
• 虽然在等值上查询效率⾼，但性能不稳定，因为当某个键值存在⼤量重复时，产⽣ Hash 碰撞，此时查询效率反⽽可能降低

B+ 树：底层是多路平衡查找树，每次查询从根节点出发，到叶⼦节点才获得所查键值，最后查询判断是否需要回表查询

• B+ 树本质是⼀棵查找树，⾃然⽀持范围查询和排序
• 在符合某些条件（聚簇索引、覆盖索引等）时候可以只通过索引完成查询，不需要回表
• 查询效率⽐较稳定，因为每次查询都是从根节点到叶⼦节点，且为树的⾼度。

事务

定义

数据库的事务是⼀个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执⾏的结果必须使数据库从⼀种⼀致性状态变到另⼀种⼀致性状态。事务是逻辑上的⼀组操作，要么都执⾏，要么都不执⾏。

特性

• 原⼦性： 事务是最⼩的执⾏单位，不允许分割。事务的原⼦性确保动作要么全部完成，要么完全不起作⽤
• ⼀致性： 事务执⾏前后，数据保持⼀致，多个事务对同⼀个数据读取的结果是相同的
• 隔离性： 并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务之间数据库是独⽴的
• 持久性： ⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发⽣故障也不应该对其有任何影响

sql优化

善用explain查看sql执行计划，复杂sql能优化就优化，能走索引就走索引，表太大考虑分库分表【按时间|分片键等拆分】，对于热点数据可以走缓存【eg：数据字典、菜单、权限等】
1. select务必指明字段名称，避免使用select *【增加不必要返回值，增加CPU、IO、内存、带宽负载】
2. 字段设计长度要合理，字段类型合理，定长使用char
3. 不能使用order by rand（）【随机返回–每行记录再排序，造成指数级性能下降】
4. 杜绝select count(*)【count（1）效率更高，查所有】【count（字段）查非NULL】【有主键count主键】
5. 优化分页方式，记录上次最大主键，然后where id>max(last count id)
6. 优化update语句，减少不必要更新
7. 大数量表的关联查询，尽量过滤数据，减少联合查询
8. 大量数据使用批处理
9. 字符串尽量设置默认值，原因同7
10. 对慢sql查询进行优化，避免全表扫描，考虑对where或order by列建索引
11. 单个表索引数量尽量在6个以下【否则影响insert ，update效率】
12. 分库分表，sharingjdbc不支持ifnull的写法【eg：IFNULL（sum（num），0）】

避免索引失效情况发生

1. 避免where语句中非空判定（不走索引）【判NULL会走索引】
2. 尽量不使用%XX进行模糊查询（不走索引）【XX%会走索引】
3. 避免where语句对字段进行表达式操作（索引失效）【eg：where num*3 = 12】
4. 避免隐式类型转换，会导致索引失效【eg：where name = ‘0’，where name = 0】
5. 范围查询语句可能导致部分索引失效，如between，<，>可能造成后面字段索引失效
6. 使用or关键字时，一个字段没有索引则索引失效，or两部有范围查询，索引失效
7. 非主键索引用not，<>，！=不会进行索引【可以改为a>0 or a <0】
8. 联合索引需要满足最左匹配原则，（index1，index2），index1走索引
9. order by主键走索引，非主键且查询内容包含非索引列，不走索引
10.

数据库查询空的区别

1. 数据库不存在这样的数据，返回N/A
2. 列中存在NULL，返回NULL
3. 列中字段为空串，返回空串
4. 做一次函数运算，没有数据也会返回NULL【eg：select sum(num)】

mysql事务隔离级别

1. READ-UNCOMMITTED(读取未提交)：最低隔离级别，允许脏读【可能读取其它事务中未提交的数据】，可能导致脏读、幻读或不可重复读
2. READ-COMMITED(读取已提交)：只能读取已提交的数据，可能导致幻读或不可重复读【oracle等默认该级别】
3. REPEATABLE-READ(可重复读)：对同字段多次读取结果一致，除非被当前事务修改，可能导致幻读【mysql默认该级别】
4. SERIALIZABLE(可串行化)：最高级别隔离，服从ACID，所有事务逐个执行，但是效率低下，一般不推荐、
概念：
幻读：一个事务在前后两次查询同一个范围时，后一次查询出现前一次查询未出现的行。在可重复读隔离下，普通查询是快照读，无法看到其它事务插入的数据，所以幻读在“当前读”下才会发生
可重复读：核心是一致性读，保证多次读取同一个数据时，数据都一致，禁止读取其它事务未提交的数据，会造成幻读

mysql为什么是InnoDB

聚焦索引是指数据库表行中的数据的物理顺序和键值的逻辑（索引）顺序相同，一个表只能有一个聚簇索引，因为一个表的物理顺序只有一种情况，所以，对应的聚簇索引只能有一个。聚簇索引的叶子结点就是数据节点，既存储索引值，又在叶子结点存储行数据。
InnoDB创建表后生成的文件：frm：创建表的语句 idb：表数据+索引文件

mysql索引底层使用B+树

1. 哈希能够提供O(1)的单数据操作性能，但是范围查询与排序只能全表查询
2. B树可以在非叶子结点存储数据，但也导致查询连续数据带来更多随机IO
3. B+树的叶节点可以通过指针连接，减少顺序遍历时额外的随机IO；B树节点里存的是数据，B+树节点存储的时索引（地址），所以B树一个节点无法存储很多数据，但是B+树一个节点能存储很多索引，B+树叶子节点存储所有数据。B+树的叶子结点是数据阶段使用链表串联，便于范围查询

慢sql原因

1. 善于使用explain分析sql的执行
2. 最常见是没有索引或没有用到索引或索引失效
3. 服务器内存不足
4. 带宽、网络慢
5. 单次查询数据量过大，例如一次10w条
6. 大数据是否使用不必要的列项
7. 是否存在未释放的锁或死锁

explain关键属性

1. type：连接类型，一个好的sql至少到range级别，杜绝all级别出现
2. key：索引名称【all：全表，index：索引全扫描，range：范围索引扫描，ref-：非唯一索引或唯一索引前缀扫描，eq_ref-：类似ref使用的是唯一索引与主键关联查询，const/system：单条记录将匹配行中其它列作为常数处理，如主键或唯一索引查询】
3. key_len：索引长度
4. rows：扫描行数，为预估值
5. extra：详细说明【不友好值：Using filesort，Using temporary】

mysql索引

普通索引：无限制，允许重复与空
唯一索引：值必须唯一，可以为空
主键索引：主键的唯一索引
组合索引：多字段索引，只有查询使用最左边的列项才会索引，使用遵循最左前缀集合
全文索引：仅MylSAM引擎可用，仅char，varchar，text使用，在全文中查询到某个关键字所在
空间索引：

mysql主从复制

原理：
1. 主库将变更写入binlog日志
2. 从库连接后，从一个IO线程将主库binlog拷贝到本地，写入relay中继日志
3. 从库一个sql线程从中继日志读取binlog
4. 从库执行binlog内容，即sql执行
主从延迟：
1. 从库太多
2. 从库硬件配置比主库差
3. 慢sql过多
4. 网络延迟
5. 主库读写压力大

数据库锁

1. 表级锁：开销小，加锁快，不会出现死锁；锁定粒度大，发生锁冲突概率最高，并发度低
2. 行级锁：开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突概率最低，并发度最高
3. 页面锁：开销和加锁时间界于表锁和行锁之间，会出现死锁，并发度一般