mysql面试题总结

  1. Mysql中的myisam与innodb的区别？
  2. InnoDB存储引擎的四大特性？
  3. 什么是事务？
  4. 数据库事务的四大特性？
  5. 不考虑事务的隔离性，会发生几种问题？
  6. MySQL数据库提供的四种隔离级别？
  7. 有多少种日志？
  8. 事务是如何通过日志来实现的？
  9. 数据库的乐观锁和悲观锁是什么？
  10. 什么是存储过程？有哪些优缺点？
  11. 存储过程与触发器的区别？
  12. 索引是什么？有什么作用以及优缺点？
  13. 说一说MySQL数据库几个基本的索引类型？
  14. 使用索引查询一定能提高查询的性能吗？为什么？
  15. 为数据表建立索引的原则有哪些？
  16. 什么情况下应不建或少建索引？
  17. 什么是mysql联合索引？
  18. 说一说 B+树索引、哈希索引？
  19. B树和B+树的区别？
  20. 为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？
  21. 聚集索引和非聚集索引区别?
  22. 说一说drop、delete与truncate的区别？
  23. drop、delete与truncate分别在什么场景之下使用？
  24. 超键、候选键、主键、外键分别是什么？
  25. mysql为什么用自增列作为主键？
  26. MySQL中的varchar和char的区别以及varchar(50）中的50代表的涵义？
  27. 什么是视图？视图的使用场景有哪些？
  28. 数据库三大范式？
  29. sql优化
  30. 非关系型数据库和关系型数据库区别，优势比较?
  31. 什么是 内连接、外连接、交叉连接、笛卡尔积等?
  32. SQL语言分类
  33. like %和-的区别
  34. count(*)、count(1)、count(column)的区别
  35. 你们数据库是否支持emoji表情，如果不支持，如何操作？
  36. 你是如何监控你们的数据库的？你们的慢日志都是怎么查询的？

 

 

1、Mysql中的MyISAM与InnoDB的区别？

（1）InnoDB存储引擎支持事务，而MyISAM不支持事务；

（2）InnoDB支持行级锁，而MyISAM只支持表级锁；

（ InnoDB行锁是通过给索引加锁实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表级锁！行级锁在每次获取锁和释放锁的操作需要比表级锁消耗更多的资源。

MySQL表级锁有两种模式：表共享读锁和表独占写锁。就是说对MyIASM表进行读操作时，它不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写操作；而对MyISAM表的写操作，会阻塞其他用户对同一表的读和写操作。）

（3）InnoDB支持外键，而MyISAM不支持外键；

（4）InnoDB不保存数据库表中表的具体行数，而MyISAM会保存；

（ 也就是说，执行 select count(*) from table 时，InnoDB要扫描一遍整个表来计算有多少行，而MyISAM只需要读出保存好的行数即可（内部维护了一个计算器，可以直接调取）。【注】：当count(*)语句包含where条件时，两种表的操作是一样的。也就是上述介绍到的InnoDB使用表锁的一种情况。）

 

　　对于select ,update ,insert ,delete 操作：

　　如果执行大量的SELECT，MyISAM是更好的选择（因为MyISAM不支持事务，使得MySQL可以提供高速存储和检索，以及全文搜索能力）；

　　如果执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表（因为InnoDB支持事务，在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了）。

2、InnoDB存储引擎的四大特性？

插入缓冲、二次写、自适应哈希索引、预读

（1）插入缓冲：

　　一般情况下，主键是行唯一的标识符。通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此，插入聚集索引一般是顺序的，不需要磁盘的随机读取。因为，对于此类情况下的插入，速度还是非常快的。

　　如果索引是非聚集的且不唯一，在进行插入操作时，数据的存放对于非聚集索引叶子节点的插入不是顺序的，这时需要离散地访问非聚集索引页，由于随机读取的存在而导致了插入操作性能下降。（这是因为B+树的特性决定了非聚集索引插入的离散性。）

　　插入缓冲对于非聚集索引的插入和更新操作，不是每一次直接插入索引页中，而是先判断插入的非聚集索引页是否在缓存池中。如果在，则直接插入；如果不在，则先放入一个插入缓冲区中，好似欺骗数据库这个非聚集的索引已经插入到叶子结点了，然后再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对非聚集索引执行插入和修改操作的性能。

　　　　插入缓冲的使用要满足两个条件：

  • 索引是辅助索引
  • 索引不是唯一的
    • （辅助索引不能是唯一的，因为在把它插入到插入缓冲时，我们并不去查找索引页的情况。如果去查找肯定又会出现离散读的情况，插入缓冲就失去了意义。）

　　　　存在的问题：

　　　　　　在写密集的情况下，插入缓冲会过多的占用缓冲池内存，默认情况下最大可以占用1/2的缓冲池内存。

（2）二次写

当数据库宕机时，可能发生数据库正在写一个页面，而这个页只写了一部分的情况，我们称之为部分写失效。当写入失效发生时，先通过页的副本来还原该页，再重做日志，这就是两次写。

doublewrite步骤：

  1. 当一系列机制（main函数触发、checkpoint等）触发数据缓冲池中的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页拷贝到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次、每次1MB顺序写入共享表空间的物理磁盘上。
  2. 然后马上调用fsync函数，同步脏页进磁盘。在这个过程中，doublewrite页的存储是连续的，因此写入磁盘为顺序写，性能很高在完成doublewrite页的写入后，再将doublewrite buffer中的页写入到各个表空间文件中，此时的写入则是离散的。

　　如果操作系统在将页写入磁盘的过程中崩溃了，在恢复过程中，InnoDB存储引擎可以从共享表空间中的doublewrite中找到该页的一个副本，将其拷贝到表空间文件，再应用重做日志，就完成了恢复过程。因为有副本所以也不担心表空间中数据页是否损坏。

（3）自适应哈希索引

　　InnoDB存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称为自适应的。自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快，而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

　　（4）预读

　　InnoDB 提供了两种预读的方式，一种是 Linear read ahead，由参数innodb_read_ahead_threshold控制，当你连续读取一个 extent 的 threshold 个 page 的时候，会触发下一个 extent 64个page的预读。另外一种是Random read-ahead，由参数innodb_random_read_ahead控制，当你连续读取设定的数量的page后，会触发读取这个extent的剩余page。
　　InnoDB 的预读功能是使用后台线程异步完成的。

3. InnoDB如何保证事务的四大特性？

　　MySQL的存储引擎InnoDB使用重做日志(redo log)保证一致性与持久性，回滚日志(undo log)保证原子性，使用各种锁来保证隔离性。

4. MySQL中的重做日志（redo log），回滚日志（undo log），以及二进制日志（binlog）？

MySQL中有六种日志文件，分别是：

重做日志（redo log）
回滚日志（undo log）
二进制日志（binlog）
错误日志（errorlog）
慢查询日志（slow query log）
一般查询日志（general log）
中继日志（relay log）

其中重做日志和回滚日志与事务操作息息相关，二进制日志也与事务操作有一定的关系。

事务是如何通过日志来实现的？

　　Undo 记录某 数据 被修改 前 的值，可以用来在事务失败时进行 rollback；
　　Redo 记录某 数据块 被修改 后 的值，可以用来恢复未写入 data file 的已成功事务更新的数据。
即，

  • Redo Log 保证事务的持久性
  • Undo Log 保证事务的原子性（在 InnoDB 引擎中，还用 Undo Log 来实现 MVCC）
　　比如某一时刻数据库 DOWN 机了，有两个事务，一个事务已经提交，另一个事务正在处理。数据库重启的时候就要根据日志进行前滚及回滚，把已提交事务的更改写到数据文件，未提交事务的更改恢复到事务开始前的状态。即通过 redo log 将所有已经在存储引擎内部提交的事务应用 redo log 恢复，所有已经 prepared 但是没有 commit 的事务将会应用 undo log 做回滚。

重做日志（redo log）：

　　redo log在事务没有提交前，会记录每一个修改操作变更后的数据。主要是防止在发生故障的时间点，尚有脏页未写入磁盘。在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。（作用）

　　在事务提交前，只要将 Redo Log 持久化即可，不需要将数据持久化。当系统崩溃时，系统可以根据redo Log的内容，将所有数据恢复到最新的状态。（持久化：先将重做日志写入缓存，再刷新(fsync)到磁盘）

　　重做日志是物理日志，记录的是对于每个页的修改。事务开始后Innodb存储引擎先将重做日志写入缓存（innodb_log_buffer）中。然后会通过以下三种方式将innodb日志缓冲区的日志刷新到磁盘。

  1. Master Thread每秒一次执行刷新Innodb_log_buffer到重做日志文件。
  2. 每个事务提交时会将重做日志刷新到重做日志文件。
  3. 当重做日志缓存可用空间少于一半时，重做日志缓存被刷新到重做日志文件

　　当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化。

1、内容：

      物理格式的日志，记录的是物理数据页面的修改的信息，其redo log是顺序写入redo log file的物理文件中去的。

2、redo log是什么时候写盘的？

　　是在事物开始之后逐步写盘的。

　　事务开始之后就产生redo log，redo log的写盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中。（先将重做日志写入缓存，将日志缓冲区的日志刷新到磁盘，写入磁盘的方式有上面3种）

【注】即使某个事务还没有提交，Innodb存储引擎仍然每秒会将重做日志缓存刷新到重做日志文件。这一点是必须要知道的，因为这可以很好地解释再大的事务的提交（commit）的时间也是很短暂的。

3、什么时候释放：

      当对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。

回滚日志（undo log）：

　　保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。（作用）

　　事务发生异常需要回滚，这时就需要回滚日志。回滚日志不同于重做日志，它是逻辑日志，对数据库的修改都逻辑的取消了。当事务回滚时，它实际上做的是与先前相反的工作。对于每个INSERT，InnoDB存储引擎都会完成一个DELETE；对于每个UPDATE，InnoDB存储引擎都会执行一个相反的UPDATE。

　　未提交的事务和回滚了的事务也会产生重做日志。InnoDB存储引擎会重做所有事务包括未提交的事务和回滚了的事务，然后通过回滚日志回滚那些未提交的事务。使用这种策略需要回滚日志在重做日志之前写入磁盘，使得持久化变得复杂起来。为了降低复杂度，InnoDB存储引擎将回滚日志作数据，记录回滚日志的操作也会记录到重做日志中。这样回滚日志就可以像数据一样缓存起来，而不用在重写日志之前写入磁盘了。

1、内容：

      逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redo log的。

2、什么时候产生？

      事务开始之前，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性

3、什么时候释放？

      当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间。

 

二进制日志（bin log）：

1、作用：

      用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。 用于数据库的基于时间点的还原。

2、内容：

逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。

      但又不完全是sql语句这么简单，而是包括了执行的sql语句（增删改）反向的信息，也就意味着delete对应着delete本身和其反向的insert；update对应着update执行前后的版本的信息；insert对应着delete和insert本身的信息。

在使用mysqlbinlog解析binlog之后一些都会真相大白。
因此可以基于binlog做到类似于oracle的闪回功能，其实都是依赖于binlog中的日志记录。

3、什么时候产生：

      事务提交的时候，一次性将事务中的sql语句（一个事物可能对应多个sql语句）按照一定的格式记录到binlog中。这里与redo log很明显的差异就是redo log并不一定是在事务提交的时候刷新到磁盘，redo log是在事务开始之后就开始逐步写入磁盘。

      因此对于事务的提交，即便是较大的事务，提交（commit）都是很快的，但是在开启了bin_log的情况下，对于较大事务的提交，可能会变得比较慢一些。这是因为binlog是在事务提交的时候一次性写入的造成的，这些可以通过测试验证。

4、什么时候释放：

binlog的默认是保持时间由参数expire_logs_days配置，也就是说对于非活动的日志文件，在生成时间超过expire_logs_days配置的天数之后，会被自动删除。

 

binlog与redolog的区别？

　　在MySQL数据库中还有一种二进制日志，其用来基于时间点的还原及主从复制。从表面上来看其和重做日志非常相似，都是记录了对于数据库操作的日志。但是，从本质上来看有着非常大的不同。 首先重做日志是在InnoDB存储引擎层产生的，而二进制日志是在MySQL数据库的上层产生的。其次，两种日志记录的内容形式不同。二进制日志是一种逻辑日志，其记录的是对应的SQL语句。而重做日志是物理日志，记录的是每个页的修改。此外，两种日志记录写入磁盘的时间点不同，二进制日志只在事务提交完成后进行一次写入，重做日志在事务进行时不断地写入。

 
 
 

3、什么是事务？

　　事务就是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

4、数据库事务的四大特性？

　　原子性、一致性、隔离性、持久性  （ACID）

  • 原子性：是指整个数据库事务是不可分割的单位。只有使事务中的所有数据库操作都成功，才算整个事务成功。如果事务中任何一个sql语句执行失败，那么已经执行的sql语句也必须撤销，事务状态退回到执行事务之前的状态。
  • 一致性：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态。在事务开始之前和事务结束之后，事务的完整性约束没有被破坏。
  • 隔离性：一个事务的影响在该事务提交前对其他事物都不可见。——这通过锁来实现
  • 持久性：事务一旦提交，其结果就是永久性的。

（隔离性由锁来实现；原子性、一致性和持久性通过数据库的redo和undo来完成。）

5、不考虑事务的隔离性，会发生几种问题？

　　通过锁可以实现事务隔离性的要求，使得事务可以并发地工作。因为事务隔离性的要求，锁会带来3种问题：丢失更新、脏读、不可重复读。

丢失更新：

　　指一个事务正在访问修改数据，与此同时另一个事务也在访问修改此数据，两个事务互相不知道对方的存在。假如在是事务A修改数据前事务B已经修改过1次数据，那么事务A最终只能查询到假数据，丢失了更新操作。

解决方案：

悲观锁的方式： 加锁，建议最后一步更新数据的时候加上排它锁，不要在一开始就加锁。执行到了最后一步更新，首先做一下加锁的查询确认数据有没有没改变，如果没有被改变，则进行数据的更新，否则失败。 一定要是做加锁的查询确认，因为如果你不加锁的话，有可能你在做确认的时候数据又发生了改变。

乐观锁的方式：使用版本控制实现。

脏读：

一个事务读取了另一个事务未提交的数据，那这个读取就是脏读。

解决方法 ： 把数据库的事务隔离级别调整到read commited。

不可重复读：

　　不可重复读是指在一个事务内多次读同一数据，在这个事务还没有结束时，另外一个事务也访问并修改该同一数据，那么在第一个事务的两次读数据之间，由于第二个事务的修改，第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读。

　　如何避免：InnoDB存储引擎中，通过使用Next-Key Lock算法来避免不可重复读的问题。在Next-Key Lock算法下，对于索引的扫描，不仅仅是锁住扫描到的索引，而且还能锁住这些索引覆盖的范围。因此对于这个范围内的插入都是不允许的。InnoDB存储引擎的默认事务隔离级别是READ REPEATABLE，采用Next-Key Lock算法，就避免了不可重复读的现象。

解决办法：把数据库的事务隔离级别调整到 REPEATABLE READ ， 读取时候不允许其他事务修改该数据，不管数据在事务过程中读取多少次，数据都是一致的。

幻读：

　　是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

如何避免：Repeatable read及以上级别通过间隙锁来防止幻读的出现，即锁定特定数据的前后间隙让数据无法被插入。

6、MySQL数据库提供的四种隔离级别？

  • read uncommitted（读未提交）
  • read committed（读已提交）
  • repeatable read（可重复读）：InnoDB的默认隔离级别
  • serializable（串行）

　　

7、有多少种日志？

  • 错误日志    ：记录出错信息，也记录一些警告信息或者正确的信息。
  • 查询日志    ：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
  • 慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。
  • 二进制日志：记录对数据库执行更改的所有操作。
  • 中继日志
  • 事务日志

8、事务是如何通过日志来实现的？

  • InnoDB中，事务日志通过重做（redo）日志文件和InnoDB存储引擎的日志缓冲来实现。当开始一个事务时,会记录该事务的一个LSN（日志序列号），当事务执行时，会往InnoDB的日志缓冲里插入事务日志，当事务提交时，必须将innoDB存储引擎的日志缓冲写入磁盘。也就是在写数据前，需要先写日志。这种方式称为预写日志方式。

　InnoDB通过预写日志的方式来保证事务的完整性。这意味着磁盘上存储的数据页和内存缓冲池中的数据页是不同步的，对于内存缓冲池中页的修改，先是写入重做日志文件，然后再写入磁盘，因此是一种异步的方式。

  • 事务有时还需要撤销，这就需要undo。对数据库进行修改时，数据库不但会产生redo，而且会产生一定量的undo，如果你执行的事务或语句由于某种原因失败了，或者如果你用一条rollback语句请求回滚，就可以利用这些undo信息将数据回滚到修改之前的样子。

9、数据库的乐观锁和悲观锁是什么？

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽掉一切可能违反数据完整性的操作，在读取的时候就对数据进行加锁， 在该用户读取数据的期间，其他任何用户都不能来修改该数据，但是其他用户是可以读取该数据的， 只有当自己读取完毕才释放锁。

乐观锁：假定不会发生并发冲突，只在提交的时候检查是否发生并发冲突。

　　事务和锁的存在都是为了更好地解决并发访问造成的数据不一致性问题。乐观锁和悲观锁都是为了解决并发控制问题，乐观锁可以看做一种在最后提交时检测冲突的手段，而悲观锁是一种避免冲突的手段。

（1）乐观锁：假设不会发生并发冲突，只在提交的时候检查是否发生并发冲突。可以使用版本号机制和CAS算法实现。

　　版本号机制：一般在数据表中加一个数据版本号version字段，表示数据被修改的次数，当数据被修改时version值加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若当前读取到的version值与第一次读取到的数据库version值相等时才更新，否则重试更新操作，直到更新成功。

　　例子：

假设数据库中帐户信息表中有一个 version 字段，当前值为 1 ；而当前帐户余额字段（ balance ）为 100 。

  1. 操作员A此时将其读出（ version=1 ），并从其帐户余额中扣除 50（100-50 =50）；
  2. 在操作员A操作的过程中，操作员B也读入此用户信息（ version=1 ），并从其帐户余额中扣除20 （100-20=80 ）。
  3. 操作员A完成了修改工作，将数据版本号加一（ version=2 ），连同帐户扣除后余额（ balance=50 ），提交至数据库更新，此时由于提交数据版本大于数据库记录当前版本，数据被更新，数据库记录 version 更新为 2 ；
  4. 操作员B完成了操作，也将版本号加一（ version=2 ）试图向数据库提交数据（ balance=80 ），但此时比对数据库记录版本时发现，操作员 B 提交的数据版本号为 2 ，数据库记录当前版本也为 2 ，不满足 “ 当前最后更新的version与操作员第一次的版本号相等 “ 的乐观锁策略，因此，操作员B的提交被驳回。

　　CAS机制：即compare and swap（比较与交换），无锁编程，在不使用锁的情况下实现多线程之间的变量同步，也就是在没有线程被阻塞的情况下实现变量的同步，因此也叫非阻塞同步。

　　CAS过程是这样：它包含3个参数：内存值V（要更新变量的值），旧的预期值A，要修改的值B。当且仅当预期值A的值等于内存值V时，才会将内存值V修改为B，否则不会执行任何操作（V值和A值不同，则说明已经有其他线程做了更新）。一般情况下是一个自旋操作，即不断的重试。

　　例子：

  1. 假设 t1，t2 线程同时更新同一变量56的值。
  2. 因为t1和t2线程都同时去访问同一变量56，所以他们会把主内存的值完全拷贝一份到自己的工作内存空间，所以t1和t2线程的预期值都为56。
  3. 假设t1在与t2线程竞争中线程t1能去更新变量的值，而其他线程都失败。（失败的线程并不会被挂起，而是被告知这次竞争中失败，并可以再次发起尝试）。t1线程去更新变量值改为57，然后写到内存中。此时对于t2来说，内存值变为了57，与预期值56不一致，就操作失败了（想改的值不再是原来的值）。

乐观锁的优势和劣势 ：
优势：如果数据库记录始终处于悲观锁加锁状态，可以想见，如果面对几百上千个并发，那么要不断的加锁减锁，而且用户等待的时间会非常的长， 乐观锁机制避免了长事务中的数据库加锁解锁开销，大大提升了大并发量下的系统整体性能表现。所以如果系统的并发非常大的话，悲观锁定会带来非常大的性能问题，所以建议就要选择乐观锁定的方法， 而如果并发量不大，完全可以使用悲观锁定的方法。乐观锁也适合于读比较多的场景。
劣势： 乐观锁只能在提交数据时才发现业务事务将要失败，如果系统的冲突非常的多，而且一旦冲突就要因为重新计算提交而造成较大的代价的话，乐观锁也会带来很大的问题。而且乐观锁也无法解决脏读的问题 。

（2）悲观锁：假定会发生并发冲突，在读取的时候就对数据进行加锁， 在该用户读取数据的期间，其他任何用户都不能来修改该数据，但是其他用户是可以读取该数据的， 只有当自己读取完毕才释放锁。

　　在数据库中可以使用Repeatable Read的隔离级别（可重复读）来实现悲观锁，它完全满足悲观锁的要求（加锁）。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。

　　　　悲观锁的优势和劣势 ：
　　　　优势：  能避免冲突的发生 。
　　　　劣势 ：开销较大，而且加锁时间较长，对于并发的访问性支持不好。

两种锁的使用场景：

　　如果冲突很少，或者冲突的后果不会很严重，那么通常情况下应该选择乐观锁，因为它能得到更好的并发性；

　　如果冲突太多或者冲突的结果对于用户来说痛苦的，那么就需要使用悲观策略，它能避免冲突的发生。

　　一般乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候；悲观锁适用于多写的情况，多写的情况一般会经常产生冲突。

 

数据库中的乐观锁，悲观锁

面试必备之乐观锁与悲观锁

9. 共享锁与排它锁？

　　共享锁和排它锁是具体的锁，是数据库机制上的锁。

  • 共享锁（读锁）： 在同一个时间段内，多个用户可以读取同一个资源，读取的过程中数据不会发生任何变化。读锁之间相互不阻塞， 多个用户可以同时读，但是不能允许有人修改。 
  • 排它锁（写锁）： 在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作，只能由这一个用户来写，其他用户既不能读也不能写。

 

加锁会有粒度问题，从粒度上从大到小可以划分为 ：

  • 表锁：开销较小，一旦有用户访问这个表就会加锁，其他用户就不能对这个表操作了，应用程序的访问请求遇到锁等待的可能性比较高。
  • 页锁：是MySQL中比较独特的一种锁定级别，锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
  • 行锁：开销较大，能具体的锁定到表中的某一行数据，但是能更好的支持并发处理， 会发生死锁。

10、什么是存储过程？什么是触发器？

存储过程：

（1）定义：

存储过程是一组SQL命令集合，经过预编译存放在系统中。也就是将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，以后只要调用它就可以完成相应的功能。 

（2）存储过程的种类：

　　存储过程一般分为“系统存储过程”与“用户存储过程”。系统存储过程一般以sp_开头，用户不可以编辑修改，只能调用；用户存储过程是用户编写的处理数据的存储过程。

（3）存储过程的创建和使用： 

create procedure proc1 --创建一个存储过程
as
begin
    --在存储过程中处理SQL
    select * from bank
end

【注】如果有参数，存储过程的参数是在as关键字之前，as之后的是局部变量，2种变量在存储过程中都可以使用，但是命名时不可以冲突。例：

create procedure proc2
@mobile varchar(50),@sendMsg varchar(50)
as
begin
    print @mobile  ---输出mobile这个参数
end

（4）存储过程与一般的SQL语句有什么区别呢? （存储过程的优点： ）

  1. 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 
  2. 当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 
  3. 存储过程可以重复使用,可减少数据库开发人员的工作量 。
  4. 安全性高,可设定只有某此用户才具有对指定存储过程的使用权。

　　缺点：对于简单的sql语句没必要使用存储过程，存储过程适合用于对数据库进行复杂的操作。

触发器：

（1）定义：

　　触发器（Trigger）是个特殊的存储过程，它不是由用户主动发起调用的，而是当发生某一事件而触发，由系统自动调用。比如当用户在数据库中新增一条商品记录，我们希望同时在库存中做登记，而库存登记不是人工去录入，是在发生新增商品记录这一事件时发生，由系统自动完成录入，这个工作就可以交给一个特殊的存储过程来完成，这个存储过程就是触发器。

（2）触发器的工作机制：　

　　触发器是建在表上的，当这个表发生新增、修改、删除操作时，如果这个表上有触发器，就会被自动调用。在这个事件的过程中，系统会产生一个临时表，这个临时表只有一行记录：

  • 当执行新增操作时，临时表的名字叫inserted
  • 当执行删除操作时，临时表的名字叫deleted
  • 当执行修改操作时，会同时产生2个临时表，一个是inserted，存放的是新的数据，一个是deleted，存的是旧的数据

　　当需要触发器连带操作登记库存时就可以从inserted表或者deleted表中获得变量，更新到库存表中数据。

（3）作用：维护表的完整性，记录表的修改来审计表的相关信息。分为：

  • DML触发器：当数据库服务器中发生数据操作语言事件时执行的存储过程，分为：After触发器和instead of触发器。
  • DDL触发器：特殊的触发器，在响应数据定义语言（DDL）语句时触发，一般用于数据库中执行管理任务。DDL触发器是响应create、after、或drop开头的语句而激活。

触发器用处还是很多的，比如校内网、开心网、Facebook，你发一个日志，自动通知好友，其实就是在增加日志时做一个后触发，再向通知表中写入条目。因为触发器效率高。

（4）创建触发器的SQL语法：

create trigger 触发器名称   --触发器名称
on 表名                 --建在那个表上
for insert|update|delete    --是插入事件处理还是修改事件处理还是删除事件处理
as                       --以下是触发器基本格式
begin
end

调用存储过程：call procedure_name(参数,参数...)

（5）触发器优点：

  • 自动执行：触发器不用像存储过程一样需要手动调用，是自动触发的，只有当对表进行更新，删除等操作的时候会立即触发。
  • 级联更新：触发器可以通过数据库中的相关表进行层叠更改，这比直接将代码写在前端的做法更安全合理。
  • 强化约束：触发器可以引用其他表的列，能够实现比check约束更为复杂的约束。
  • 跟踪变化：触发器可以阻止数据库中未经允许的指定更新和变化。
  • 强制业务逻辑：触发器可用于执行管理任务，并强制影响数据库的复杂业务规则。

　　缺点：不同数据库，语法差别很大，移植困难，换了数据库，需要重新编写；不好管理，把过多业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱，一般存储过程适用于个别对性能要求较高的业务。

 

参考：什么是存储过程、触发器的解释

　　sql的存储过程与触发器的区别，以及各自的优缺点

　　SQL高级--储存过程和触发器

11、存储过程与触发器的区别？与函数的区别？

存储过程与触发器：

它们都是sql语句集，不同的是：

存储过程是需要用户调用的（通过存储过程名字直接调用），而触发器不是由用户主动发起调用的，而是当发生某一事件而触发，由系统自动调用。在insert、delete和update命令之前或之后自动调用sql命令或者存储过程。

 

函数：

 函数：MySQL中提供了许多内置函数，还可以自定义函数（实现程序员需要sql逻辑处理）

自定义函数创建语法：

　　创建：CREATE FUNCTION 函数名称(参数列表) 　

　　　       RETURNS 返回值类型 　函数体

 　　修改： ALTER FUNCTION 函数名称 [characteristic ...]

　　 删除：DROP FUNCTION [IF EXISTS] 函数名称

　　 调用：SELECT 函数名称(参数列表)

 

存储过程和函数的区别：

  1.  一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。
  2. 对于存储过程来说可以返回参数，而函数只能返回值或者表对象。
  3.  存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。

12、索引是什么？有什么作用以及优缺点？

索引是对数据库表中一或多个列的值进行排序的结构，利用索引可快速访问数据库表的特定信息。

　　举个例子：假设有一张数据表Emplyee，该表有三列：Employee_name，Employee_age，Employee_address，表中有几万条记录。现在要执行下面这条查询语句：Select * from Employee where Employee_name='Jesus'。

　　如果没有数据库索引功能，数据库系统会全表扫描，逐行的遍历整张表，对于每一行都要检查其Employee_Name字段是否等于“Jesus”。而数据库索引功能索引的最大作用就是加快查询速度，它能从根本上减少需要扫表的记录/行的数量。

优点：

  • 索引加快数据库的检索速度
  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；
  • 加速表和表之间的连接；
  • 使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点：

  • 创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；
  • 索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；
  • 当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

13、说一说MySQL数据库几个基本的索引类型？

普通索引、唯一索引、主键索引、联合索引、全文索引。

  • 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
  • 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。 为表定义主键将自动创建主键索引。（数据库表某列或列组合，其值唯一标识表中的每一行。该列称为表的主键。）
  • 联合索引：指对表上的多个列做索引。只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则。
  • 全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。目前只有char、varchar，text 列上可以创建全文索引。

https://www.cnblogs.com/sdfgdrg/p/10095891.html

14、使用索引查询一定能提高查询的性能吗？为什么？

通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价：

　　索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT、DELETE、UPDATE将为此多付出4,5 次的磁盘I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况：

  • 如果某个字段的取值范围很广，几乎没有重复，即高选择性，则此时使用B+树索引是最适合的，例如姓名。
  • 基于一个范围的检索，一般查询返回结果集小于表中记录数的20%。（MySQL数据库的优化器会预估查询可能得到的行，如果大于某一个值，则B+树会选择全表的扫描。这个值一般在20%（即当取出的数据量超过表中数据的20%，优化器就不会使用索引））

15、为数据表建立索引的原则有哪些？

  • 在最频繁使用的、用以缩小查询范围的字段上建立索引。
  • 在频繁使用的、需要排序的字段上建立索引。

16、什么情况下应不建或少建索引？

  • 对于那些在查询中很少使用或者参考的列不应该创建索引。（既然这些列很少使用到，因此有索引或者无索引并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。）
  • 对于那些只有很少数据值的列也不应该增加索引。（由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。）
  • 对于那些定义为text, image和bit数据类型的列不应该增加索引。（这是因为，这些列的数据量要么相当大，要么取值很少。）
  • 当修改性能远远大于检索性能时，不应该创建索引。（这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。）

17、什么是mysql联合索引？

 　　联合索引是指对表上的多个列做索引。在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

最左前缀匹配原则：

　　最左优先，在检索数据时从联合索引的最左边开始匹配。

 

　　对列col1、列col2和列col3建一个联合索引：KEY test_col1_col2_col3 on test(col1,col2,col3);

　　联合索引 test_col1_col2_col3 相当于建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。

（1）SELECT * FROM test WHERE col1="1" AND clo2="2" AND clo4=|"4"

　　上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。

（2）索引的字段可以是任意顺序的，如：

　　SELECT * FROM test WHERE col1=“1” AND clo2=“2”
　　SELECT * FROM test WHERE col2=“2” AND clo1=“1”

　　这两个查询语句都会用到索引(col1,col2)，mysql创建联合索引的规则是首先会对联合合索引的最左边的，也就是第一个字段col1的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个字段col2进行排序。其实就相当于实现了类似 order by col1 col2这样一种排序规则。

　　有人会疑惑第二个查询语句不符合最左前缀匹配：首先可以肯定是两个查询语句都保函索引(col1,col2)中的col1、col2两个字段，只是顺序不一样，查询条件一样，最后所查询的结果肯定是一样的。既然结果是一样的，到底以何种顺序的查询方式最好呢？此时我们可以借助mysql查询优化器explain，explain会纠正sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。

（3）如果只查询col2：SELECT * FROM test WHERE col2=2;

　　第一个col字段是绝对有序的，而第二字段就是无序的了。所以通常情况下，直接使用第二个字段col2进行条件判断是用不到索引的。当然是col2字段的索引数据也是有序的情况下才能使用咯，什么时候才是有序的呢？在col1字段是等值匹配的情况下，cid才是有序的。这也就是mysql索引规则中要求复合索引要想使用第二个索引，必须先使用第一个索引的原因。（而且第一个索引必须是等值匹配）。

为什么要使用联合索引？

  • 减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
  • 覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
  • 效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！

https://segmentfault.com/a/1190000015416513

　　从本质上来说，联合索引还是一颗B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。

　　对于查询 SELECT * FROM TABLE WHERE a=xxx and b=xxx，显然可以使用(a,b)这个联合索引。对于单个的a列查询 SELECT * FROM TABLE WHERE a=xxx 也是可以使用(a,b)索引。但对于b列的查询 SELECT * FROM TABLE WHERE b=xxx 不可以使用这颗B+树索引。因为叶节点上的b值为1,2,1,4,1,2，显然不是排序的，因此对于b列的查询使用不到(a,b)的索引。

 　　联合索引的第二个好处是，可以对第二个键值进行排序。例如，在很多情况下我们都需要查询某个用户的购物情况，并按照时间排序，去除最近3次的购买记录，这是使用联合索引可以避免多一次的排序操作，因为索引本身在叶节点已经排序了。

　　【注】：对于相同的第一个键值的数据，第二个键值是排好序的。

　　　　　　对于单个列a的查询往往使用单个键的索引，因为其叶节点包含单个键值，能存放的记录更多。

18、说一说 B+树索引、哈希索引？

Hash索引和B+树索引的特点：

  • Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位;

  • B+树索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问。

Hash索引与B+树索引区别？

  • 如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
  • 从示意图中也能看到，如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
  • 同理，哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
  • 哈希索引也不支持多列联合索引的最左匹配规则；
  • B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。
https://www.zhihu.com/question/263964996

19、B树和B+树的区别？

B+树是一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶节点中，各叶结点指针进行连接。

（平衡二叉树AVL：首先符合二叉查找树的定义（最结点的值比根节点小，右结点的值比根结点大），其次必须满足任何节点的左右两个子树的高度最大差为1。）

  • B树  ：每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为nul，叶子结点不包含任何关键字信息。
  • B+树：所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接，所有的非终端结点可以看成是索引部分。

20、为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？

（1）B+的磁盘读写代价更低

　　B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

（2）B+tree的查询效率更加稳定

　　由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

21、聚集索引和非聚集索引区别?

　　数据库中的B+索引可以分为聚集索引和辅助聚集索引。不管是聚集索引还是非聚集的索引，其内部都是B+树的，即高度平衡的，叶节点存放着所有的数据，聚集索引与非聚集索引不同的是，叶节点存放的是否是一整行的信息。

  • 聚集索引(clustered index)：

　　聚集索引就是按照每张表的主键构造一颗B+树，并且叶节点中存放着整张表的行记录数据，因此也让聚集索引的叶节点成为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。

　　聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。

  • 非聚集索引(nonclustered index)（也叫辅助索引）：

　　对于辅助索引(非聚集索引)，叶级别不包含行的全部数据。聚集索引键来告诉InnoDB存储引擎，哪里可以找到与索引相对应的行数据。辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

　　非聚集索引指定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。

根本区别：

聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。

22、说一说drop、delete与truncate的区别？

  • drop直接删掉表。
  • truncate删除表中数据，再插入时自增长id又从1开始。
  • delete删除表中数据，可以加where字句。

（1）truncate和delete只删除数据，而drop则删除整个表（结构和数据）。

（2）delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。truncate table则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

（3）执行速度：drop> truncate >delete

（4）delete语句是dml，这个操作会放到rollback segement中，事务提交之后才生效。如果有相应的trigger（触发器），执行的时候将被触发；

　　 truncate、drop是ddl，操作立即生效，原数据不放到rollback segment中，不能回滚，操作不触发trigger。

（5）当表被truncate后，这个表和索引所占用的空间会恢复到初始大小， delete操作不会减少表或索引所占用的空间。

（5）应用范围：truncate只能对table；delete可以是table和view

如果直接删除一个表drop，对数据量很大的表，这个过程会占用比较长的时间，如果先truncat后drop table：1、可以降低操作失败的风险；2、可以降低数据字典锁占用的时间，降低系统开销。

23、drop、delete与truncate分别在什么场景之下使用？

  • 不再需要一张表的时候，用drop
  • 想删除部分数据行时候，用delete，并且带上where子句
  • 保留表而删除所有数据的时候用truncate

24、超键、候选键、主键、外键分别是什么？

 超键   ：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键：是最小超键，即没有冗余元素的超键。

主键    ：数据库表中对存储数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

外键    ：在一个表中存在的另一个表的主键称此表的外键。

25、mysql为什么建议用自增列作为主键？

　　如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。

  1. 使用自增列(INT/BIGINT类型)做主键，这时候写入顺序是自增的，和B+数叶子节点分裂顺序一致；
  2. 该表不指定自增列做主键，同时也没有可以被选为主键的唯一索引(上面的条件)，这时候InnoDB会选择内置的ROWID作为主键，写入顺序和ROWID增长顺序一致；
  3. 除此以外，如果一个InnoDB表又没有显示主键，又有可以被选择为主键的唯一索引，但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况)，该表的存取效率就会比较差。

26、MySQL中的varchar和char的区别以及varchar(50）中的50代表的涵义？

　　char是一种固定长度的类型，varchar是一种可变长度的类型。

varchar(50)中50的含义：
　　　　最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度。ppp-

int(20)中20的含义：

　　　是指显示字符的长度
　　　但要加参数的，最大为255，比如它是记录行数的id,插入10笔资料，它就显示00000000001 ~~~00000000010，当字符的位数超过11,它也只显示11位，如果你没有加那个让它未满11位就前面加0的参数，它不会在前面加0.
　　20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；

mysql为什么这么设计：
　　　对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样。

27、什么是视图？视图的使用场景有哪些？

 　　视图（View）是一个命名的虚表，它由一个查询来定义，可以当作表使用。

视图有什么用（应用场景）：

1、当一个查询你需要频频的作为子查询使用时，视图可以简化代码，直接调用而不是每次都去重复写这个东西。
2、系统的数据库管理员，需要给他人提供一张表的某两列数据，而不希望他可以看到其他任何数据，这时可以建一个只有这两列数据的视图，然后把视图公布给他。

创建视图sql语句：

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

视图与表的区别：

      1、视图是已经编译好的sql语句，而表不是。
      2、视图没有实际的物理记录，而表有。
      3、表是内容，视图是窗口。
      4、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能由创建的语句来修改。
      5、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
      6、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。 
      7、视图的建立和删除只影响视图本身，不影响对应的基本表。
      8、不能对视图进行update或者insert into操作。

28、数据库三大范式？

第一范式（1NF）
　　（在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。）

　　所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，第一范式就是无重复的列。强调的是列的原子性，即列不能够再分成其他几列。

第二范式（2NF）
　　满足第二范式（2NF）必须先满足第一范式（1NF）。另外包含两部分内容，一是表必须有主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

第三范式（3NF）
　　满足第三范式（3NF）必须先满足第二范式（2NF）第三范式就是属性不依赖于其它非主属性。非主键列必须直接依赖于主键，不能存在传递依赖。

29、sql优化

 1. explain sql 分析sql语句，这个语句可以打印出的各种item的意义：

  • select_type ：表示查询中每个select子句的类型
  • type ：表示MySQL在表中找到所需行的方式，又称“访问类型”
  • possible_keys ：指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。
  • key ：显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。
  • key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
  • ref ：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 
  • Extra ：包含不适合在其他列中显示但十分重要的额外信息

2. 查询语句不同元素（where、jion、limit、group by、having等等）执行先后顺序?

查询中用到的关键词主要包含6个，并且他们的顺序依次为 select--from--where--group by--having--order by--limit。

其中select和from是必须的，其他关键词是可选的。

（使用having字句对分组后的结果进行筛选，所以having只能用在group by之后；

　limit 起始记录位置：取记录的条数对记录进行选取，主要用来实现分页功能）

30、非关系型数据库和关系型数据库区别，优势比较?

非关系型数据库的优势：

  • 性能：NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。
  • 可扩展性：同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

关系型数据库的优势：

  • 复杂查询：可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
  • 事务支持：使得对于安全性能很高的数据访问要求得以实现。

其他：

1.对于这两类数据库，对方的优势就是自己的弱势，反之亦然。

2.NOSQL数据库慢慢开始具备SQL数据库的一些复杂查询功能，比如MongoDB。

3.对于事务的支持也可以用一些系统级的原子操作来实现例如乐观锁之类的方法来曲线救国，比如Redis set nx。

31、什么是 内连接、外连接、交叉连接、笛卡尔积等?

  • 内连接: 只连接匹配的行
  • 左外连接: 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行
  • 右外连接: 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行

例如1：
SELECT a.,b. FROM luntan LEFT JOIN usertable as b ON a.username=b.username

例如2：
SELECT a.,b. FROM city as a FULL OUTER JOIN user as b ON a.username=b.username

  • 全外连接: 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。
  • 交叉连接: 生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配

例如：
SELECT type,pub_name FROM titles CROSS JOIN publishers ORDER BY type

1.以A，B两张表为例
A left join B
选出A的所有记录，B表中没有的以null 代替
right join 同理

2.inner join
A,B的所有记录都选出，没有的记录以null代替

3.cross join (笛卡尔积)
A中的每一条记录和B中的每一条记录生成一条记录
例如A中有4条，B中有4条，cross join 就有16条记录

32、SQL语言分类

  • 数据查询语言DQL ：基本结构是由SELECT子句、FROM子句、WHERE子句组成的查询块。
  • 数据操纵语言DML ：1）插入INSERT 2) 更新：UPDATE 3) 删除：DELETE
  • 数据定义语言DDL ：用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如：CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
          • DDL操作是隐性提交的！不能rollback
  • 数据控制语言DCL ：用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。ROLLBACK/COMMIT
  • (1) 显式提交
    • 用COMMIT命令直接完成的提交为显式提交。其格式为：SQL>COMMIT；
  • (2) 隐式提交
    • 用SQL命令间接完成的提交为隐式提交。这些命令是：ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
  • (3) 自动提交
    • 若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，系统将自动进行提交。其格式为：SQL>SET AUTOCOMMIT ON；

33、like %和-的区别

　　% 通配符：表示任何字符出现任意次数 (可以是0次)。
　　_ 通配符：表示只能匹配单个字符，不能多也不能少，就是一个字符。

使用通配符进行模糊查询需要用 like操作符，例：

　　SELECT * FROM products WHERE products.prod_name like '%es%';

　　SELECT * FROM products WHERE products.prod_name like '_es';

【注】如果在使用like操作符时，后面的没有使用通用匹配符效果是和 = 是一致的。

　　　SELECT * FROM products WHERE products.prod_name like '1000'：只能匹配的结果为1000，而不能匹配像JetPack 1000这样的结果。

34、count(*)、count(1)、count(column)的区别

  • count(*)  ：对行的数目进行计数，包含NULL

  • count(column) ：对特定的列的值具有的行进行计数，不包含NULL值。

  • count(1)这个用法和count(*)的结果是一样的，包含null。

性能问题:

  1. 任何情况下SELECT COUNT(*) FROM tablename是最优选择;
  2. 尽量减少SELECT COUNT(*) FROM tablename WHERE COL = ‘value’ 这种查询;
  3. 杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出现。
  • 如果表没有主键，那么count(1)比count(*)快。
  • 如果有主键，那么count(主键,联合主键)比count(*)快。
  • 如果表只有一个字段，count(*)最快。

　　count(1)跟count(主键)一样，只扫描主键；count(*)跟count(非主键)一样，扫描整个表。明显前者更快一些。

35、你们数据库是否支持emoji表情，如果不支持，如何操作？

如果是utf8字符集的话，需要升级至utf8_mb4方可支持。

36、你是如何监控你们的数据库的？你们的慢日志都是怎么查询的？

监控的工具有很多，例如zabbix，lepus，我这里用的是lepus。

  • slow_query_log        ：慢查询开启状态。
  • slow_query_log_file ：慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）。
  • long_query_time      ：查询超过多少秒才记录。

 

 开启慢日志查询：set global slow_query_log='ON';

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？ 

　　这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，可以使用命令修改，也可以在my.cnf参数里面修改。运行时间大于long_query_time（非大于等于），才会记录到慢查询日志里。

 

33、谈谈mongodb,mysql的区别和具体应用场景

MongoDB是神马？

　　MongoDB是非关系型数据库，是一个基于分布式文件存储的数据库。（文档型数据库：可以存放xml、json、bson类型的数据。）同时MongoDB是由C++语言编写。旨在为WEB应用提供可扩展的高性能数据存储解决方案。 是非关系数据库当中功能最丰富，最像关系数据库的。

　　MongoDB 将数据存储为一个文档，数据结构由键值(key=>value)对组成。MongoDB 文档类似于 JSON 对象。字段值可以包含其他文档，数组及文档数组。

　　它可以存储比较复杂的数据类型。Mongo最大的特点是它支持的查询语言非常强大，其语法有点类似于面向对象的查询语言，几乎可以实现类似关系数据库单表查询的绝大部分功能，而且还支持对数据建立索引。

　　mongodb与mysql不同，mysql的每一次更新操作都会直接写入硬盘，但是mongo不会，作为内存型数据库，数据操作会先写入内存，然后再会持久化到硬盘中去 ，但MongoDB采用的预分配空间的方式来防止文件碎片，所以MongoDB的数据文件很大。

　　MongoDB的特点是：

　　（1）面向文档（2）高性能（3）高可用（4）易扩展（5）丰富的查询语言

　　MongoDB 缺点：

　　　　① MongoDB 不支持事务操作(最主要的缺点)
　　　　② MongoDB 占用空间过大
　　　　③ MongoDB 没有如 MySQL 那样成熟的维护工具，这对于开发和IT运营都是个值得注意的地方

　　存储方式：虚拟内存+持久化。

　　持久化方式：MongoDB 的所有数据实际上是存放在硬盘的，所有要操作的数据通过 mmap 的方式映射到内存某个区域内。然后，MongoDB 就在这块区域里面进行数据修改，避免了零碎的硬盘操作。

mongodb,mysql的区别？

　　（1）MongoDB 非关系型数据库，MySql是关系型数据库

　　（2）MongoDB存储方式：虚拟内存+持久化； MySql在不同的引擎上有不同 的存储方式。

　　（3）MongoDB查询语句：是独特的Mongodb的查询方式； MySqlMySql查询语句是使用传统的sql语句，拥有较为成熟的体系，成熟度很高。

　　（4）mysql的每一次更新操作都会直接写入硬盘，但是mongo的数据操作会先写入内存，然后再会持久化到硬盘中去 。（MongoDB数据是存储在硬盘上的，只不过需要经常读取的数据会被加载到内存中，将数据存储在物理内存中，从而达到高速读写。）

　　（5）mysql缺点就是在海量数据处理的时候效率会显著变慢。在适量级的内存的Mongodb的性能是非常迅速的。

　　（6）MongoDB 不支持事务操作，mysql的innodb和bdb存储引擎支持事务。（注：myisam不支持事务）

 

34.一条sql语句的执行过程？

先简单介绍一下一些组件的基本作用：

  • 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
  • 查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
  • 分析器: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
  • 优化器： 按照 MySQL 认为最优的方案去执行。
  • 执行器: 执行语句，然后从存储引擎返回数据。

 

一条查询语句的执行顺序：

１.客户端通过TCP连接发送连接请求到mysql连接器，连接器会对该请求进行权限验证及连接资源分配。

２.建立连接后客户端发送一条语句，mysql收到该语句后，通过命令分发器判断其是否是一条select语句，如果是，在开启查询缓存的情况下，先在查询缓存中查找该SQL是否完全匹配，如果完全匹配，验证当前用户是否具备查询权限，如果权限验证通过，直接返回结果集给客户端，该查询也就完成了。如果不匹配继续向下执行。

3.如果在查询缓存中未匹配成功，则将语句交给分析器作语法分析，MySQL需要知道到底要查哪些东西，如果语法不对，就会返回语法错误中断查询。

4.分析器的工作完成后，将语句传递给预处理器，检查数据表和数据列是否存在，解析别名看是否存在歧义等

5.语句解析完成后，MySQL就知道要查什么了，之后会将语句传递给优化器进行优化（通过索引选择最快的查找方式），并生成执行计划。

6.之后交给执行器去具体执行该语句，在执行之前，会先检查该用户是否具有查询权限，如果有，继续执行该语句。执行器开始执行后，会逐渐将数据保存到结果集中，同时会逐步将数据缓存到查询缓存中，最终将结果集返回给客户端。（如果该SQL执行过程中超过了慢查询阀值，该SQL会被记录到慢查询日志中） 

一条更新语句的执行顺序：

 。。。。。

　　有一条记录需要更新的时候，InnoDB引擎就会先把记录写到 redo log（粉板）里，并更新到内存，这个时候更新就算完成了，同时，InnoDB引擎会在适合的时候，将这个操作记录更新到磁盘里面，而这个更新往往实在系统比较空闲的时候做。

 

MySQL中一条SQL语句的执行过程

一条SQL更新语句是如何执行的

 

 

参考史上最全的数据库面试题，不看绝对后悔

　　【MySQL】20个经典面试题

　　数据库常见面试题（附答案）

posted on 2019-07-18 15:02  Toria  阅读(14484)  评论(0)  编辑  收藏  举报

导航