数据库优化思路

主 键：
一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

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

2.数据库事务的四个特性及含义

数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、
隔离性(Isolation)、持久性(Durability)。

原子性:整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行
过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

隔离性:隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。

持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

3.视图的作用，视图可以更改么？

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询；不包含任何列或数据。使用
视图可以简化复杂的sql操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。

创建视图：create view XXX as XXXXXXXXXXXXXX;

但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

4.drop,delete与truncate的区别

drop直接删掉表 truncate删除表中数据

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

（2） 表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，
而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

（3） 一般而言，drop > truncate > delete

（4） 应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view

（6） truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的
结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留
，但其状态会变为：invalid。

（7） delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务
提交后才生效。如果有相应的 tigger,执行的时候将被触发。

（8） truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback 
segment中，不能回滚

10） Truncate table 表名 速度快,而且效率高,因为:
truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。
但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，
并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除
数据，并且只在事务日志中记录页的释放。

（11） TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的
计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，
请使用 DROP TABLE 语句。

（12） 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 
子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

5.索引的工作原理及其种类

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。
索引的实现通常使用B树及其变种B+树。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）
数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间
(因为索引也要随之变动)。

创建索引可以大大提高系统的性能。
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？因为，
增加索引也有许多不利的方面。
第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立
聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

一般来说，应该在这些列上创建索引：
1.在经常需要搜索的列上，可以加快搜索的速度；
2.在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3.在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4.在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5.在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：
第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，
因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度
和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。
第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么
相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。
当增加索引时，会提高检索性能，但是会降低修改性能。

在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。

唯一索引
唯一索引是不允许其中任何两行具有相同索引值的索引。

数据库范式

1 第一范式（1NF）

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

所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，
即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义
一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）
中表的每一行只包含一个实例的信息。

简而言之，第一范式就是无重复的列。

2 第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足
第一范式（1NF）。

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的
属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间
是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。

第二范式就是非主属性非部分依赖于主关键字。

3 第三范式（3NF）
简而言之，第三范式就是属性不依赖于其它非主属性。（我的理解是消除冗余）

8.数据库优化的思路

1.SQL语句优化

1）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行
全表扫描，如：
select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0

3）很多时候用 exists 代替 in 是一个好的选择

4）用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

2.索引优化

3.数据库结构优化

1）范式优化： 比如消除冗余（节省空间。。）

2）反范式优化：比如适当加冗余等（减少join） 

3）拆分表： 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。
这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间

4.服务器硬件优化

9.存储过程与触发器的区别

触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，
而是在用户执行Transact-SQL语句时自动触发（激活）执行。

（1）、根据服务层面：配置mysql性能优化参数；
（2）、从系统层面增强mysql的性能：优化数据表结构、字段类型、字段索引、分表，分库、读写分离等等。
（3）、从数据库层面增强性能：优化SQL语句，合理使用字段索引。
（4）、从代码层面增强性能：使用缓存和NoSQL数据库方式存储，如MongoDB/Memcached/Redis来缓解
高并发下数据库查询的压力。
（5）、减少数据库操作次数，尽量使用数据库访问驱动的批处理方法。
（6）、不常使用的数据迁移备份，避免每次都在海量数据中去检索。
（7）、提升数据库服务器硬件配置，或者搭建数据库集群。
（8）、编程手段防止SQL注入：使用JDBC PreparedStatement按位插入或查询；正则表达式过滤
（非法字符串过滤）；

MySQL数据库的优化方法

1、选取最适用的字段属性

一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的
性能，我们可以将表中字段的宽度设得尽可能小。

另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候，
数据库不用去比较NULL值。

对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被
当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的
性能。

2、使用连接（JOIN）来代替子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询。可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为
过滤条件用在另一个查询中。

连接（JOIN）..之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要
两个步骤的查询工作。

3、使用联合(UNION)来代替手动创建的临时表

MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个
查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。

使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，
要注意的是所有select语句中的字段数目要相同。

4、事务

尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是
所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。

它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的
一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，
那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供
一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。

5、锁定表

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，
尤其是在很大的应用系统中。

如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万
的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。

6、使用外键

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

7、使用索引
索引是提高数据库性能的常用方法，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，
性能提高更为明显。

8、优化的查询语句

绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有
的作用。

首先，最好是在相同类型的字段间进行比较的操作。

其次，在建有索引的字段上尽量不要使用函数进行操作。

第三，在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以
牺牲系统性能为代价的。

数据库性能优化有哪些方式？

SQL 优化：
1.尽量避免使用 SELECT	*；
2.只查询一条记录时使用 limit 1；
3.使用连接查询代替子查询；
4.尽量使用一些能通过索引查询的关键字。

表结构优化：
1.尽量使用数字类型字段，提高比对效率；
2.长度不变且对查询速度要求高的数据可以考虑使用 char，否则使用 varchar；表中字段过多时可以适当
的进行垂直分割，将部分字段移动到另外一张表；表中数据量过大可以适当的进行水平分割，将部分数据
移动到另外一张表。

其它优化：

对查询频率高的字段适当的建立索引，提高效率；根据表的用途使用合适的数据库引擎；读写分离。