mysql面试

MySQL面试题

三大范式

第一范式(1NF)：字段不可再分，保持原子性

第二范式(2NF)：表的每一个字段都与主键有关系

第三范式(3NF)：任何非主属性不可依赖于其他非主属性，必须与主属性有直接关系

Sql编写顺序与执行顺序

SELECT 字段

FROM 表名

WHERE 条件

GROUP BY 分组字段列表

HAVING 分组后条件列表

ORDER BY 排序字段列表

LIMIT 分页参数

Sql执行顺序

⑧select ⑨distinct(去重) ⑥聚合函数

①from 表1

③inner join | left join | right join 表2

②on(连接条件) 表1.字段 = 表2.字段

④where 查询条件

⑤group by(分组) 字段

⑦having 分组过滤条件

⑩order by(排序) 字段

⑪limit(分页) 0,10

注意：

select比group by晚执行，因为mysql是行式存储，假如group by的字段不在select中，那么先执行select会导致group by字段被丢弃，group by时要回表查询

关联查询

• left join(左联接) ：返回包括左表中的所有记录和右表中联结字段相等的记录
• right join(右联接) ：返回包括右表中的所有记录和左表中联结字段相等的记录
• inner join(等值连接)： 只返回两个表中联结字段相等的行

常用聚合函数

1. sum(列名) 求和
2. max(列名) 最大值
3. min(列名) 最小值
4. avg(列名) 平均值
5. first(列名) 第一条记录
6. last(列名) 最后一条记录
7. count(列名) 统计记录数不包含null值 count(*)包含null值。

in和exists的区别

in()： 适合子表(子查询)比主表数据小的情况。
exists()： 适合子表(子查询)比主表数据大的情况。

char和varchar的区别

1. char设置多少长度就是多少长度，varchar可以改变长度，所以char的空间利用率不如varchar的空间利用率高。
2. 因为长度固定，所以存取速度要比varchar快。因为它是固定长度的，所以数据的存储位置是已知的，这可以减少查找时间
3. char适用于固定长度的字符串，比如身份证号、手机号等，varchar适用于不固定的字符串。

drop、truncate、delete的区别

drop是删除表结构和数据，包括索引和权限，释放空间，不可以回滚

truncate是保留表结构，清空整个表数据，不可以回滚。如果有自增id，再次添加id从1开始

delete删除部分表数据或整个表数据，可以回滚。如果是自增id，再次添加id从上次最大的id+1开始递增

什么是事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

事务四大特性

1. 原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
2. 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
3. 一致性：事务完成时，必须使所有的数据都保持一致状态
4. 持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

并发事务问题

1. 脏读：一个事务读取到另一个事务还没有提交的数据
2. 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读（第二次查的时候，有别的事务提交修改了数据）
3. 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影

事务隔离级别

1. Read uncommitted（读未提交）可能会出现脏读、不可重复读、幻读

2. Read commited（读已提交）可能会出现不可重复读和幻读

3. Repeatable Read（读可重复读）可能会出现幻读

4. Serializable（可序列化）不会出现问题

索引是什么

索引是帮助mysql高效获取数据的数据结构（有序）。相当于目录，能更快的查找数据，是一个文件，占用物理空间

索引的优缺点

优点：提高数据检索的效率，降低数据库的IO成本，通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

缺点：索引列也要占用空间，索引提高了查询效率，同时也降低了更新表的速度，对表insert,update.delete时效率降低。

索引分类

主键索引：针对于表中主键创建的索引，只能有一个

唯一索引：索引的值必须唯一，允许定义索引的字段为空值。

常规索引：基本索引类型，允许定义索引的字段为空值和重复值

全文索引：全文索引查找的是文本中的关键词，而不是比较索引中的值

存储形式

存储形式分为聚集索引和二级索引

聚集索引：将数据存储与索引放到了一块，索引结构的叶子结点保存了行数据，一张表只能有一个聚集索引，一般是主键索引。

二级索引：将数据与索引分开存储，索引结构的叶子结点关联的是对应的主键

B+Tree索引结构优点

• 与二叉树相比，B+Tree层级更少，搜索效率高

• 与B-Tree相比，B-Tree无论是叶子结点还是非叶子结点，都会保存数据，而B+Tree只有叶子结点是数据，层级会更少，效率也就更高
• 相对于hash索引，B+Tree支持范围匹配及排序操作

联合索引的最左前缀法则

创建一个包含多个字段的联合索引，如果查询时使用该联合索引，where条件必须包含联合索引最左边的列。

索引设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引
2. 针对于常作为查询条件（where）、排序（order by)、分组（group by)操作的字段建立索引
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表查询，提高查询效率
6. 要控制索引的数量，索引不是多多益善，因为索引越多，维护索引结构的代价也就越大，会影响增删改的效率
7. 如果索引列不能存储NULL值，请在创建表是使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

索引失效情况

• 不要在索引列上进行运算操作，否则索引会失效。select * from tb_user where substring(phone,10,2) = ‘15’;
• 字符串类型字段使用时，不加引号，索引会失效
• or连接的条件，如果有其中一个没有索引，那么索引不会生效
• 模糊查询时，尾部模糊匹配，索引会生效，头部模糊匹配，索引失效，避免使用’%'开头的like的模糊查询。

如何优化数据库

1. SQL优化
2. 加缓存
3. 分表
4. 读写分离

22.SQL优化
①不要用select *，要使用具体字段。
②使用数值代替字符串，比如：0=唱，1=跳，2=rap。
③避免返回大量数据，采用分页最好。
④使用索引，提升查询速度，不宜建太多索引，不能建在重复数据比较多的字段上。
⑤批量插入比单条插入要快，因为事务只需要开启一次，数据量太小体现不了。
⑥避免子查询，优化为多表连接查询。
⑦尽量使用union all替代union，因为union会自动去重。

SQL优化

主键优化

1. 满足业务需求的情况下，尽量降低主键的长度
2. 插入数据时，尽量选择顺序插入，选择自增主键
3. 尽量不要使用uuid做主键活着是其他自然主键，如身份证号，因为是无序的，而且长度较长
4. 业务操作时，避免对主键的修改

语句优化

1. 不要用select *，要使用具体字段
2. 避免子查询，优化为多表连接查询。mysql执行子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗。
3. 尽量使用union all替代union，因为union会自动去重
4. 批量插入比单条插入要快，因为事务只需要开启一次
5. 避免返回大量数据，采用分页最好
6. count优化，按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*****)，所以尽量使用 count(*)
7. 如果使用like语句，尾部模糊匹配可以使用索引
8. 不使用NOT IN 和<>操作

建立索引

1. 尽量建立联合索引，where、group by 、order by可以使用索引提高效率，同时遵循最左前缀法则
2. 索引不是越多越好，联合索引的字段也不是越多越好，索引本身占用存储空间

MylSAM和InnoDB、Memory的区别

MylSAM： mysql5.5之前的存储引擎，是表锁（悲观锁）级别的，不支持事务和外键。
InnoDB： mysql5.5之后的存储引擎，是行锁（乐观锁）级别的，支持事务和外键。
Memory： 内存数据库引擎，因为在内存操作，所以读写很快，但是Mysql服务重启，会丢失数据，不支持事务和外键。

为什么要加锁

当多个用户并发存取数据时，在数据库中会产生多个事务同时存取同一数据的情况，若不对并发操作加以控制就可能导致读取和存储的数据不正确，破坏数据的一致性，加锁的目的就是为了保证数据库的完整性和一致性。

按照锁的粒度来分，数据库锁有哪些？

在关系型数据库中可以分为：表级锁，页级锁，行级锁

表级锁：是mysql中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，实现简单，资源消耗较少，大部分mysql引擎支持，如InnoDB，Myisam。表级锁的开销小，加锁快，不会出现死锁；但锁的粒度大，发生锁冲突的概率高，并发度最低。表级锁又分为共享锁（读锁）与排他锁（写锁）。

按照锁的类别来分，数据库有：

共享锁：又称为读锁，当用户要进行数据的读取时，对数据加上共享锁，共享锁可以加上多个

排他锁：又称为写锁，当用户要进行数据的写入时，对数据加上排他锁，排他锁只可以加一个，它与其他排他锁，共享锁都相斥。

页级锁：页级锁是mysql中所粒度介于表级锁和行级锁之间的一种锁。表级锁速度快，但冲突多，行级锁冲突少，但速度慢。页级锁折中介于两者之间，一次锁定相邻的一组记录。BDB引擎支持页级锁。开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

行级锁：行级锁是mysql中所粒度最小的一种锁，表示对当前操作的行进行加锁，锁的粒度小，加锁开销大，锁冲突概率最低，并发度最高。InnoDB引擎支持。

行级共享锁S：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁	
行级排他锁X：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁

乐观锁和悲观锁

数据库的管理系统中的并发控制的任务是确保多个事务同时存取数据中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性，乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：一般会使用版本号机制或CAS算法实现。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

什么是死锁？如何解决？
死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。通俗来讲：有一块蛋糕，两个人一人抢到一半，并且这两个人还在要求对方将自己手里的另一半也给自己，相互拉扯。

常见的解决死锁的方法

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁的机会。

2、在同一事务中尽可能做到一次锁定所需要的所有资源，减少死锁产生的概率。

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

如果业务处理不好可以用分布式事务锁或者使用乐观锁