路由器

电脑

手机

网络

MySQL面试问题

来源：www.45fan.com  2019-03-29 11:36:05

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

定义：索引是存储引擎用于快速查找数据的一种数据结构。

MySQL数据库基本的索引类型：普通索引、唯一索引、主键索引和全文索引。

普通索引：允许被索引的数据列包含重复的列

唯一索引：不允许被索引的数据包含重复的列

主键索引：在一张表中只能定义一个主键索引，主键索引用于唯一标识一条记录（主键索引必然属于唯一索引），使用关键字PRIMARY KEY创建

索引的目的是什么

  • 快速访问数据表中的特定信息，提高检索速度
  • 创建唯一性索引，保证数据库表中每一行数据的唯一性
  • 加速表和表之间的连接
  • 使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序时间

索引对数据库系统的负面影响

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

建立索引的原则

  • 频繁使用的字段
  • 选择性高的字段
  • 需要排序的字段

主键、外键和唯一索引的区别

主键：唯一标识一条记录，不能有重复，不允许为空。一个表只能有一个主键

外键：表的外键是另一个表的主键，外键可以有重复的，可是为空值。一个表可以有多个外键

唯一索引：用来提高查询排序的速度，没有重复值，但允许有一个空值。一个表可以有多个唯一索引。

MySQL底层实现，MySQL有什么引擎

MySQL底层采用B+树实现，B+树可以看作2-3树的一种扩展，B+树是每个节点允许存在多个元素，并且每个节点有多个孩子的多叉查找树，B+树的非叶子节点存储的是导航信息，不包含实际的值，而叶子节点存储了实际的值，所有的叶子节点和相邻的节点采用链表连接，便于区间查找和遍历。

关于叶子节点存储的值，在InnoDB中非主键索引中，存储的是主键的信息，而聚簇索引（主键索引）中存储的是实际数据。而在MyISAM中叶子节点存储的是指向实际数据存储位置的指针。MySQL在5.5版本采用的是MyISAM作为默认的数据库引擎，之后就被更优秀的InnoDB引擎替代。

InnoDB和MyISAM区别，InnoDB替代了MyISAM，那么MyISAM是否一无是处。

MyISAM支持全文索引，不支持事务，不支持外键，只支持表级锁，不支持行级锁。

InnoDB支持事务，支持全文索引，支持外键，支持行级锁。

采用MyISAM（可被压缩，需要的存储空间较小）的场景：

  1. 读多写少，R/W>100:1
  2. 并发不高
  3. 表数据量小

什么是事务，事务特性

事务指的是满足ACID特性的一组操作。

Atomicity 原子性：事务被视为不可分割的最小单元，要么全部提交成功，要么全部失败回滚。

Consistency 一致性：数据库在事务执行前后保持一致的状态。

Isolation 隔离性：一个事务所做的修改在最终提交之前，对其他事务是不可见的。

Durability 持久性：一旦事务提交，则其修改将会被永久保存到数据库中。

如何设计一个高并发的系统

  1. 数据库的优化，包括合理的事务隔离级别、SQL语句优化、索引优化
  2. 使用缓存，尽量减少数据库IO
  3. 分布式数据库、分布式缓存
  4. 服务器的负载均衡

锁的优化策略

  1. 读写分离
  2. 分段加锁
  3. 减少锁持有的时间
  4. 多个线程尽量以相同的顺序去获取资源

优化SQL的方法

MySQL数据库优化的八种方式(经典必看)

  • 选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置NOTNULL，例如’省份’、'性别’最好适用ENUM

  • 使用连接(JOIN)来代替子查询

  • 适用联合(UNION)来代替手动创建的临时表

  • 事务处理

  • 锁定表、优化事务处理

  • 适用外键，优化锁定表

  • 建立索引

如何进行SQL优化

1. 选择正确的存储引擎

以 MySQL为例，包括有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。

MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。但是它支持行级锁 ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务、外键等。

2. 优化字段的数据类型

记住一个原则，越小的列会越快。如果一个表只会有几列罢了（比如说字典表，配置表），那么，我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。

如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多。当然，你也需要留够足够的扩展空间。

3. 为搜索字段添加索引

索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么最好是为其建立索引，除非你要搜索的字段是大的文本字段，那应该建立全文索引。

4. 避免使用SELECT * 从数据库中读取多余的信息

避免使用Select 从数据库里读出越多的数据，那么查询就会变得越慢。

并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。

即使你要查询数据表的所有字段，也尽量不要用*通配符，善用内置提供的字段排除定义也许能给带来更多的便利。

5. 使用ENUM而不是VARCHAR

ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。

这样一来，用这个字段来做一些选项列表变得相当的完美。

例如，性别、民族、部门和状态之类的这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。

6. 尽可能使用NOT NULL

NULL值需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。

除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。

当然，并不是说就不能使用NULL值了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。

7. 固定长度的表会提高性能

如果表中所有字段都是固定长度的，整个表都会被认为是static的。固定长度的表会提高性能，因为这些字段固定后，MySQL很容易计算下一行数据的偏移量，读取速度自然会变快。另外固定长度的表也很容易被缓存和重建。

唯一的副作用是固定长度的字段会浪费一些空间。另外表中一旦有了VARCHAR、TEXT、BLOB这些类型字段之一，就不再是固定长度静态表了。

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

存储过程是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数（如果该存储过程带有参数）来执行它。

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

相对于直接使用SQL 语句，在应用程序中直接调用存储过程有以下好处：
(1)减少网络通信量。
调用一个行数不多的存储过程与直接调用SQL 语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL 语句，那么其性能绝对比一条一条的调用SQL 语句要高得多。
(2)执行速度更快。
有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。
(3)更强的适应性。
由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。
(4) 分布式工作。
应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

缺点：
1.如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐了。
2.可移植性差。由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。

谈谈三大范式，什么时候使用反范式设计

三大范式

  1. 第一范式：!NF要求字段属性具有原子性，不可再分解。是对属性的原子性约束。
  2. 第二范式：2NF要求非主属性完全函数依赖于键码。是对记录的唯一性约束，要求记录有唯一标识。
  3. 第三范式：3NF要求非主属性不传递函数依赖于键码。是对字段冗余性的约束，即任何字段不能由其他字段派生出来。

范式化设计的优缺点

优点：减少数据冗余，使得更新速度快，表格体积小

缺点：对于查询需要多个表进行关联的情况，降低了查询效率，难以进行索引优化。

反范式化设计

优点：可以减少表的关联，更好地进行索引优化

缺点：数据冗余以及数据异常，数据的修改需要更多的成本。

什么是视图，以及视图的使用场景

视图是一种虚拟的表，具有和物理表相同的功能。

可以对视图进行增，改，查操作。视图通常是有一个表或者多个表的行或列的子集。

对视图的修改不影响基本表。相比多表查询，它使得我们获取数据更容易。

MySQL锁总结

MySQL锁总结

InnoDB意向锁详解

drop、delete和truncate的区别及使用场景

相同点：

drop、delete、truncate都是删除表的内容

不同点：

  • delete：删除表的内容，表的结构还存在，不释放空间，可以回滚恢复。
  • drop：删除内容和结构，释放空间，没有备份表之前要慎用。
  • truncate：删除表的内容，表的结构还在，可以释放空间，没有备份表之前要慎用。

解释MySQL外连接、内连接的区别

内联接：仅显示两个联接表中的匹配行的联接

左外联接：包括第一个命名表（“左表”，出现在JOIN子句的最左边）中的所有行。不包括右表中的不匹配行。

右外联接：包括右表中的所有行。不包括左表中的不匹配行。

对于大流量的网站,您采用什么样的方法来解决各页面访问量统计问题？

对于当今大流量的网站，每天几千万甚至上亿的流量,是如何解决访问量问题的呢?以下是一些总结的方法:
第一，确认服务器硬件是否足够支持当前的流量。
普通的P4服务器一般最多能支持每天10万独立IP，如果访问量比这个还要大，那么必须首先配置一台更高性能的专用服务器才能解决问题，否则怎么优化都不可能彻底解决性能问题。

第二，优化数据库访问。
服务器的负载过大，一个重要的原因是CPU负荷过大，降低服务器CPU的负荷，才能够有效打破瓶颈。而使用静态页面可以使得CPU的负荷最小化。前台实现完全的静态化当然最好，可以完全不用访问数据库，不过对于频繁更新的网站，静态化往往不能满足某些功能。
缓存技术就是另一个解决方案，就是将动态数据存储到缓存文件中，动态网页直接调用这些文件，而不必再访问数据库，WordPress和Z-Blog都大量使用这种缓存技术。
如果确实无法避免对数据库的访问，那么可以尝试优化数据库的查询SQL.避免使用Select *from这样的语句，每次查询只返回自己需要的结果，避免短时间内的大量SQL查询。

第三，禁止外部的盗链。
外部网站的图片或者文件盗链往往会带来大量的负载压力，因此应该严格限制外部对于自身的图片或者文件盗链，好在目前可以简单地通过refer来控制盗链，Apache自己就可以通过配置来禁止盗链，IIS也有一些第三方的ISAPI可以实现同样的功能。当然，伪造refer也可以通过代码来实现盗链，不过目前蓄意伪造refer盗链的还不多，可以先不去考虑，或者使用非技术手段来解决，比如在图片上增加水樱

第四，控制大文件的下载。
大文件的下载会占用很大的流量，并且对于非SCSI硬盘来说，大量文件下载会消耗CPU，使得网站响应能力下降。因此，尽量不要提供超过2M的大文件下载，如果需要提供，建议将大文件放在另外一台服务器上。目前有不少免费的Web2.0网站提供图片分享和文件分享功能，因此可以尽量将图片和文件上传到这些分享网站。

第五，使用不同主机分流主要流量
将文件放在不同的主机上，提供不同的镜像供用户下载。比如如果觉得RSS文件占用流量大，那么使用FeedBurner或者FeedSky等服务将RSS输出放在其他主机上，这样别人访问的流量压力就大多集中在FeedBurner的主机上，RSS就不占用太多资源了。

第六，使用流量分析统计软件。
在网站上安装一个流量分析统计软件，可以即时知道哪些地方耗费了大量流量，哪些页面需要再进行优化，因此，解决流量问题还需要进行精确的统计分析才可以。我推荐使用的流量分析统计软件是GoogleAnalytics（Google分析）。

 
 
推荐阅读