数据库总结

索引

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

MySQL数据库基本的索引类型

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

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

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

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

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

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

• 外键：表的外键是另一个表的主键，外键可以有重复的，可以为空值（但是无意义，不推荐使用）。一个表可以有多个外键

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

索引的目的

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

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

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

建立索引的原则

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

MySQL

MySQL底层实现

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

• 关于叶子节点存储的值，在InnoDB中非主键索引中，存储的是主键的信息，而聚簇索引（主键索引）中存储的是实际数据。而在MyISAM中叶子节点存储的是指向实际数据存储位置的指针。

MySQL引擎

• MySQL在5.5版本采用的是MyISAM作为默认的数据库引擎，之后就被更优秀的InnoDB引擎替代。

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

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

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

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

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

事务的定义及其特性

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

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

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

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

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

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

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

锁的优化策略

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

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语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数（如果该存储过程带有参数）来执行它。

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

优点:

相对于直接使用SQL 语句，在应用程序中直接调用存储过程有以下好处：

1. 减少网络通信量。

调用一个行数不多的存储过程与直接调用SQL 语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL
语句，那么其性能绝对比一条一条的调用SQL 语句要高得多。

2. 执行速度更快。

有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。

3. 更强的适应性。

由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。

4. 分布式工作。

应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

缺点：

1. 如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐了。

2. 可移植性差。由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。

三大范式

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

范式化设计的优缺点

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

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

反范式化设计优缺点

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

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

视图及其使用场景

定义：

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

使用场景：

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

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

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

相同点：

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

不同点：

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

MySQL外连接、内连接的区别

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

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

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

对于大流量的网站解决各页面访问量统计问题的方法

1. 确认服务器硬件是否足够支持当前的流量。

普通的P4服务器一般最多能支持每天10万独立IP，如果访问量比这个还要大，那么必须首先配置一台更高性能的专用服务器才能解决问题，否则怎么优化都不可能彻底解决性能问题。

2. 优化数据库访问。

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

3. 禁止外部的盗链。

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

4. 控制大文件的下载。

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

4. 使用不同主机分流主要流量

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

5. 使用流量分析统计软件。

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

数据库相关问题

Q1：主键，外键，超键，候选键；

主键	对表中数据进行唯一标识的数据列的组合；不能缺失；不能空值；
外键	该列为另一表的主键；
超键	关系中能唯一标识数据的属性；
候选键	不含多余属性的超键；

Q2：数据库事务的四个特征及含义；

原子性	要么全部完成，要么不完成，若发生错误会进行回滚操作；
一致性	开始到结束后，数据库完整性约束没收到破坏；（实体完整性，参照完整性，用户定义的完整性）
隔离性	事务与事务之间相隔离，串行化执行；
持久性	事务完成对数据的影响是永久的；

Q3：视图的作用，可以更改吗；

视图是虚拟的表；只包含动态检索数据的查询，不包含数据；简化操作，隐藏细节，保护数据；对视图的更新会作用于基表，一般不更新；

Q4：drop,delete和truncate ;

drop	表级的删除；不能回滚；
truncate	清空表；不记录单行删除日志；无法恢复；只能对于TABLE操作；不能在带FOREIGN KEY约束的表（被引用的表）中使用；计数从头开始；
delete	配合where删除数据；会记录日志用于回归；会触发触发器；不减少索引和表的空间；

Q5：索引的工作原理和其种类；

索引的实现通常采用B树或B+树，加快查询速度也消耗更多空间；
唯一索引	不允许任何两行具相同值
主键索引	唯一索引的一种
聚集索引	行的物理顺序和键值的索引顺序相同

普通索引	无限制
全文索引	针对较大的数据生成全文索引很耗时间空间
组合索引	最左前缀原则：若对多列建立组合索引，若第二列未使用索引，则第三列也不会使用

InnoDB	主索引：InnoDB的数据文件本身；辅助索引：相应记录主键的值
MyISAM	索引与数据分离；辅助索引：与主索引无区别；

Q6：连接的种类

内部联接（等值联接）	INNER JOIN
外部联接（包含在相关表中没有关联行的行）LEFT OUTER JOIN /RIGHT OUTER JOIN

Q7：数据库范式

1NF	每一列都是不可分割的基本数据项，同一列无二值；无重复的域；
2NF	实例依赖于主键部分；
3NF	属性不依赖于其他非主属性；

Q8：存储过程与触发器的区别

存储过程和触发器都是SQL语句集；触发器不可用CALL调用，而是在用户执行某些语句后自动调用；

Q9：分表与分区

分表	真正的分表，每张表对应三个文件；提高MYSQL的并发能力；
分区	表中的数据分成多个区块；突破磁盘的读写能力；

Q10：数据库隔离级别

脏读	一个事务读取了另一个事务未提交的数据
不可重复读	在一次事务范围内，读取同一数据产生了不同的值
虚读	读取整体的数据后，因其他事务对数据的更新，再次查询时结果不同
串行化	3种均可避免
可重复读（默认）	避免1,2
读已提交	1
读未提交	无

Q11：MYSQL的两种存储引擎

MYISAM	不支持事务，不支持外键，表锁；插入数据时锁定整个表，查行数时无需整表扫描
INNODB	支持事务，外键，行锁，查表总行数时，全表扫描；

Q12：MYSQL索引算法

HASH	适合等值查找，不适合范围，不能排序
BTREE	适合范围查找，无hash冲突

Q13：聚集索引和非聚集索引

聚集索引	数据按索引顺序存储，节点存储的是真实数据
非聚集索引	节点存储的是指向真正数据的指针

Q14：索引的优缺点

优点	提高查询效率
缺点	降低了更新效率

Q15：两种存储引擎索引的区别

Innodb	主索引的数据文件本身就是索引文件；辅助索引记录主键的值；
MyISAM	主索引数据文件和索引文件分离；与主索引无区别；

Q16：数据库的主从复制

一个服务器作为主服务器，一个或多个服务器作为从服务器，主服务器将更新写到二进制日志，当一个从服务器连接到主服务器时，通知主服务器读取日志，接收从那时起发生的所有更新。解决：数据分布，负载平衡，备份，高可用性和容错性

基于语句	在主服务器上执行的语句，在从服务器上也执行
基于行	将改变的内容复制过去
混合类型	语句复制失败时采用行的形式

Q17：数据库连接池

为数据库连接建立一个缓冲池，防止过于大量的连接的建立与管理；

Q18：存储过程

存储过程是一些预编译的SQL语句，执行效率较高

Q19：乐观锁和悲观锁

乐观锁	假定不会发生并发冲突，只在提交时检查，若有其他数据更新了数据，则回滚；使用数据版本标示数据（时间戳，版本号）
悲观锁	假定会发生并发冲突，屏蔽一切破坏数据库一致性的操作，主要用于数据争用激烈的环境，以及锁成本低于回滚成本时；排他锁；