MySQL面试题

1、四个特性及含义

四个基本要素（acid）：原子性、一致性、隔离性、持久性
原子性(Actomicity)：整个事务中的操作要么全完成，要么全部不完成，不能滞留在中间某个环节，事务在执行过程发生错误，会被回滚（Rollback）到事务开始前的状态，就像没执行过一样
一致性(Consistent)：在事务开始和结束之后，数据库的完整性约束没有被破坏
隔离性(Isolation):使事务在给定的时间内执行的唯一操作，为了防止事务之间的混淆，必须串行化或序列化请求，使得在同一时间内仅有一个请求用于同一数据
持久性(Durable):在事务完成以后，该事务所对数据库所做的更改持久保存到数据库之中，并不会被回滚

2、drop、delete、truncate的区别

drop直接删掉表、truncate删除表中的数据，再插入数据自增长id又从1开始、delete删除表中的数据，可以加where语句
（1）delete语句删除表中的某一行。并同时将改行的删除操作作为事务记录在日志中，以便后续进行回滚；truncate则是一次性删除表中所有的数据并不会单独把操作记录日志保存，是不能恢复的
（2）表和索引所占的空间：truncate之后空间会恢复初始大小，delete操作不会减少表索引所占的空间，drop将释放所有的占用空间
（3）一般而言 drop>truncate>delete
（4）truncate和delete只删除数据，drop直接删除整个表
（5）在没有备份的情况下，慎用drop和truncate
（6）truncate速度快、效率高

3、索引

索引是数据库管理系统中一个排序的数据结构，已助快速查询、更新数据库表中数据，创建语句alter table tablename add index(id);
优点：
（1）创建唯一索引，可以保证每一行数据的唯一性
（2）大大加快检索速度
（3）加速表之间的连接
（4）使用分组和排序字句进行数据检索时，可以显著减少查询中分组和排序的时间
缺点：
（1）建立索引需要占用物理空间
（2）对表的数据进行增删改查时，索引也要动态维护，降低了数据的维护速度
（3）创建和维护索引耗费时间，时间随着数据量的增加而增加
不需要创建索引
（1）查询中很少使用或者是参考的列
（2）数据量很少的列
（3）对于那些定义text、image和bit数据类型
（4）修改性能远远大于检索性能，修改性能和检索性能是互相矛盾的

4、 行级锁、表级锁、乐观锁、悲观锁

加锁原因：防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对更新的数据加必要的锁来解决
（1）表级锁：每次操作都锁定在整张表，开销少，加锁块，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低
（2）行级锁:每次操作锁定一行数据，开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率最低，并发度最高
（3）页面锁：开销和加锁时间介于两者之间，会出现死锁，并发度一般
（4）悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
（5）乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性
乐观锁更适合解决冲突概率极小的情况，而悲观锁则适合解决并发竞争激烈的情况，尽量使用行锁,缩小加锁粒度，以提高并发处理能力，即使加行锁的时间比表锁要长

5、 并发事务处理带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户
（1）更新丢失:当两个或多个事务选择同一行，然后基于最初的值选定该行进行更新该行时，由于事务之间没有联系，就会发生丢失更新的问题，如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一个文件，就可以避免这类问题
（2）脏读：一个事务在对一条数据进行修改，在事务完成并提交前，记录就处于不一致的状态，这时另外一个事务也来读取同一条记录，如果不加以控制，第二个事务读取了这些“脏”数据，并据此做进一步处理，就会产生未提交数据的依关系
（3）不可重复读:一个事务在读取某些数据后的某个时间后，再次读取的时候，发现数据发生改变，或者某些记录被删除了
（4）幻读:一个事务按相同的查询条件重复读取以前检索过的数据，却发现了其它事务插入了满足其查询条件的新数据
跟新丢失可以通过加锁来解决，“脏读”、“不可重复读”、“幻读”属于数据库一致性问题，由数据库提供一定的事务隔离机制来解决

6、事务隔离方式实现

在读取数据前，对其进行加锁，阻止事务对数据进行修改
不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照提供一定级别的一致性读取，这种方式也叫多版本并发控制简称MVCC或MCC

7、事务隔离度级别

数据库的隔离越严格，并发副作用越小，付出的代价也就越大，隔离的实质就是在一定程度上使事务“串行化”,这显然与“并发”是矛盾的

隔离级别	读取数据一致性	脏读	不可重复读	幻读
未提交读(Read uncommitted)	最低级别，只能保证读取不损坏数据	是	是	是
已提交读（Read committed）	语句级	否	是	是
可重复读（Repeatable read）	事务级	否	否	是
可序列化（Serializable）	最高级别，事务级	否	否	否

8、innodb和myisam的区别

（1）innodb支持事务，myisam不支持
（2）innodb支持行级锁，myisam支持表级锁
（3）innodb支持外键，myisam不支持
（4）innodb支持MVCC，myisam不支持
（5）innodb不支持全文索引，myisam支持
应用场景：
（1）MyISAM：做很多count计算、插入不频繁、查询非常频繁、没有事务、查询快
（2）InnoDB:对事务要求比较高、可靠性要求高、表更新相当频繁、并发写入高
DELETE操作：
（1）MyISAM:先drop表，然后重建表
（2）InnoDB:一行一行删除
查询表的行数不同：
（1）MyISAM:只是简单的读出保存好的行数
（2）InnoDB:不保存具体行数，执行count(*)时要扫描一整个表来计算有多少行

9、innodb引擎的4大特性

（1）插入缓存（insert buffer）
（2）二次写（double write）
（3）自适应哈希索引
（4）预读

10、MyISAM会比InnoDB的查询要快

InnoDB在做SELECT的时候，要维护的东西比MyISAM引擎多
（1）数据块：InnoDB要缓存，MyISAM只缓存索引块
（2）InnoDB寻址要映射到块再到行，MyISAM记录的直接是文件的OFFSET,定位比InniDB要快
（3）InnoDB还要维护MVCC一致

11、varchar和char的区别以及varchar(50)的含义

（1）区别：char是一种固定长度的类型，varchar则是一种可变长度的类型
（2）含义：最多存放50个字符，varchar(50)和varchar(200)存储hello所占空间一样，但后者在排序时会消耗更多的内存，因为order by col采用fixed_length计算col长度
（3）int(20)的含义：是指显示字符的长度，最大为255，仍占4字节存储，存储范围不变

12、Having与where的区别

where与having都是用来筛选的
having是用来筛选组，where是用来筛选记录，通俗点讲：where搜索条件在分组操作之前应用，having搜索条件在进行分组操作之后应用

13、什么是存储过程？有哪些优缺点？

存储过程是一个预编译的有名称的代码块。
优点：执行效率比较高，降低网络通信量，提高通信速率，一定程度上确保数据安全
缺点：开发过程中维护有一定难度。

14、数据库三范式是什么?

答：第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。
第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段x → 非关键字段y

性能优化

1、为查询缓存优化查询

2、explain select 语句

explain 显示了 mysql 如何使用索引处理 select 语句及连接表，可以帮助选择更好的索引和写出更优化的查询语句
explain select * from table a;

3、当只要一行数据时使用 LIMIT 1

当查询表的时候，已经知道只会有一条结果，加上 LIMIT 1 后，MySQL 引擎会在查找到一条数据之后停止搜索
select 1 from user where id = 1 limit 1;

4、为搜索字段建索引

5、避免使用 select *

6、为每张表设置一个主键

应该为每张表都设置一个 ID 作为主键，而且最好是 INT 型的，并设置 auto_increment 自增长，使用 VARCHAR 作为主键会是性能下降；但是关联表的外键除外

7、使用 ENUM 而不是 VARCHAR

ENUM 类型是非常快和紧凑的，实际上，其保存的是 TINYINT，其外表上显示为一个字符串，当知道一些字段的取值是有限而且固定的，应该使用 ENUM

8、使用 procedure analyse() 取得建议

procedure analuse() 会让 MYSQL 帮你去分析字段和其实际数据，并会给一些建议，数据越多，建议就越准确

9、尽可能使用 not null

null 也需要额外的空间，并且在进行比较的时候，你的程序会变得更复杂

10、PreparedStatement

可以提升性能和安全

11、查询数据量大无缓冲查询

当使用脚本执行 SQL 语句的时候，程序会直到这个 SQL 语句有返回结果，然后才会往下继续执行，可以使用无缓冲查询来改变行为。尤其是那些会产生大量结果的查询，不需要等待所有结果返回，只需要第一行的数据返回的时候，就可以马上开始工作于查询结果了

12、使用固定长度的表

固定长度的表会提高性能，因为MySQL搜寻的会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键，但是定长字段会浪费一些空间，无论是否在使用，都要分配那么多的空间

13、垂直分割

将一个表中不常用的字段放在另一张表

14、拆分大的 DELETE 或 INSERT 语句

使用 limit 来限制

15、选择正确的存储引擎

对于查询非常频繁，使用 MYISAM 效率高一些，但是写入数据比较频繁的话，使用 InnoDB ，一般选用 InnoDB