mysql面试

以下是自己整理以及参考网上资料所得的一些mysql面试题

sql优化

1.尽量把查询级别(type)优化到ref

2.尽量使用索引覆盖(extra为using index)，即select的字段和where的字段全部都包含在索引中

3.索引往小表加，往常用的字段加，小表驱动大表(Nest Loop Join)

4.不要在索引上进行操作(计算，类型转换，函数)，否则索引失效，注意，如果是整形类型的索引，带引号去查不会使索引失效，如果是字符串类型的索引，不带引号查则会使索引失效

5.不使用or，like尽量以常量开头，不以%开头，否则索引失效

6.避免extra出现using fileSort，使用order by时最好where哪些字段就order by 那些字段，或者where字段和order by的字段都在复合索引中，且按照复合索引的顺序进行

7.避免extra出现using temporary，使用group by时最好where哪些字段就group by 那些字段，或者where字段和group by的字段都在复合索引中，且按照复合索引的顺序进行

8.复合索引应遵循最佳左前缀规则，不跨列使用

9.复合索引，如果左侧字段失效，则右边索引字段全部失效

10.in可能会导致索引失效，使用复合索引时，将in查询的字段放到后面

11.复合索引如果使用！=，is null is not null，自身以及右侧索引字段失效

12.复合如果使用>,<,like,in，右侧索引字段失效

13.开启慢查询日志(测试环境)，结合explain观察索引使用情况以及扫描的行数对sql进行优化

14.优化字段的数据类型，不如能用smallint的就没必要用Int,能用varchar(10)的没必要用vatchar(100)

15.选择合适的存储引擎

16.避免使用select *，字段尽量采用固定长度、not null

事务

ACID：

原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

innodb中事务的隔离级别

强推：http://www.zsythink.net/archives/1233/ 

1.读未提交(当前事务能读取到另一个事务修改的，尚未提交的数据)可能出现脏读、幻读、不可重读(不一定可重读)

2.读已提交(当前事务能读取到另一个事务修改的，已提交的数据)->可能出现幻读、不可重读(不一定可重读)

3.可重复读(默认隔离级别，即使另一个事务修改并提交数据，当前事务也读取不到)->可能出现幻读，当前事务执行更新操作操作到了“隐藏”行，使得隐藏数据“现形”

4.串行化->不会出现幻读，但数据库基本失去并发能力了

事务的隔离性是由锁来实现的

非锁定读只存在于读已提交和可重复读

可重复读：同时开启两个事务，事务A的修改即使提交了(假如新增了一条数据)，事务B未提交之前是查询不到新增的数据的
幻读：在同一个事务中，执行两次同样的sql,第二次的sql会返回之前不存在的行，或者之前出现的数据不见了，这种现想称为幻读(例如同时开启两个事务，A事务新增一条数据并且提交了事务，B事务虽然查询不到该条数据，但是如果执行更新操作，可能
会更新到这条隐藏的数据，再次查询数据的时候就会把这条隐藏的数据查询出来了)

串行化：同样使用上面幻读的例子，串行化之所以不会出现幻读，关键步骤是在A事务新增数据后，对表进行加锁，同时因为在串行化不存在非锁定读，获取不到读锁会直接报错，就不会出现幻读的情况了

读提交：同样使用上面可重复读的例子，与可重复读不同的是，如果事务A的修改提交了，事务B未提交之前就能查询到新增或修改的数据了

不可重读：不一定可以重读，相对幻读不同的是，幻读是针对新增或者减少了数据行数，不可重读是针对某行的数据修改了

脏读：当前事务能够看到其它事务尚未提交的修改

 

存储引擎

innodb：行锁，加锁的开销大，容易死锁，支持事务

myisam：表锁，并发能力低，不容易死锁，不支持事务

memory、merge、archive、csv

 

MySQL数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？

a. 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
b. 选择合适的表字段数据类型和存储引擎，适当的添加索引。
c. mysql库主从读写分离。通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能，参考https://www.jianshu.com/p/cfd9bfd6e7f2。
d. 找规律分表，减少单表中的数据量提高查询速度。(分表有两种类型，分别为横向分表和纵向分表，纵向分表即在数据库表设计的时候，根据某种规则将一个表划分成多个表，比如按照数据的更新、读取频率划分，将更新频率高的字段划分到一个表，读取频率高的字段划分到另外一个表，不同的表可以采用对于自身更合适的存储引擎；对于横向分表，即根据某个规则将一张表在表结构不变的基础上分为多张，每张表的数据量减少，性能将会有所提升)
e。添加缓存机制，比如memcached，apc等。
f. 不经常改动的页面，生成静态页面。
g. 书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.

 

视图：

视图简介：

视图可以看作定义在SQL Server上的虚拟表。视图正如其名字的含义一样，是另一种查看数据的入口。

常规视图本身并不存储实际的数据，而仅仅是由SELECT语句组成的查询定义的虚拟表 。

从数据库系统内部来看，视图是由一张或多张表中的数据组成的，从数据库系统外部来看，视图就如同一张表一样，对表能够进行的一般操作都可以应用于视图。

例如查询，插入，修改，删除操作等，但插入、修改、删除等的操作其实对于原始数据表的操作。

视图的作用：

1、视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。

2、视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）

3、从而加强了安全性，使用户只能看到视图所显示的数据。

4、视图还可以被嵌套，一个视图中可以嵌套另一个视图。

语法：

CREATE VIEW test_view
AS
SELECT 语句

 

红黑树(在二叉排序树的基础上进行变色、旋转)，以下是在二叉排序树的基础上新增的特性：

参考https://blog.csdn.net/qq_36610462/article/details/83277524

1.节点是红色或黑色。

2.根节点是黑色。

3.每个叶子节点都是黑色的空节点（NIL节点）。

4 每个红色节点的两个子节点都是黑色。(从每个叶子到根的所有路径上不能有两个连续的红色节点)

5.从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。

 

索引的数据结构：

索引使用二叉树作为数据结构的弊端：若列数据是单列增长的话，索引相当于链表了，查找速度不能提高

hash表作为数据结构的弊端：无法使用范围查找   优点：精确查找时每次只需要查找一次

innodb为什么使用B+树作为索引的数据结构：参考https://www.cnblogs.com/tiancai/p/9024351.html