【数据库学习】关系数据库总结

1，数据库平台（Database）

数据库管理系统（DataBase- Management System，DBMS） ：是系统软件，是数据库系统的核心。
数据库有两种类型，分别是 关系型数据库 和 非关系型数据库（NoSQL（Not Only SQL )）。

1）postgres

按行存储，有行锁。

2）mysql

3）Access

4）sql server

5）sqlite

6）neo4j

非关系数据库。图数据库。

7）Oracle

8）clickHouse

9）hive

10）Elasticsearch原理

2，概念

1）数据库

数据库是长期存储在计算机内(永久存储)、有组织的、可共享的大量数据的集合。
数据库中存储的是数据及数据之间的关系。

1. 正常情况读写文件系统比数据库快一到两个数据级；
2. 数据库的优势是体现的大量数据的查询、统计以及并发读写，不是在速度上。
3. 数据的最小存取单位是数据项。

2）数据库模式定义语言DDL(Data Definition Language)：

是用于描述数据库中要存储的现实世界实体的语言。一个数据库模式包含该数据库中所有实体的描述定义。这些定义包括结构定义、操作方法定义等。

3）关系完整性

1>实体完整性（主键不为NULL）

所谓的实体完整性就是指关系(所谓的关系就是表)的主码不能取空值；
比如学生表的主码通常是取学号为主码

2>参照完整性（外键不为NULL）

是指参照关系中每个元素的外码要么为空(NULL)，要么等于被参照关系中某个元素的主码；
参照关系也称为外键表，被参照关系也称为主键表。

3>用户定义的完整性（属性约束）

属性的范围约束；
唯一性约束；

4）关系运算

1. 集合运算符
2. 关系运算符
3. 算术比较符
4. 逻辑运算符
   非与或

集合运算符	备注
并（∪）
差（-）
交（∩）
笛卡尔积（×）	A={a,b}, B={0,1,2}，A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}

关系运算符	说明
选择（限制、σ）	在关系R中选择满足给定条件的诸元组。
投影（π）	关系R上的投影是从R中选择出若干属性列组成新的关系。 投影之后可既改变行，又改变元组的数量。
连接（θ连接、⋈）	从两个关系的笛卡尔积中选取属性间满足一定条件的元组。（连接由乘积（笛卡尔积）、选择、投影组成）
除运算（➗）	RS÷S表示：“在R和S的联系RS中，找出与S中所有的元组有关系的R元组”。

5）依赖

依赖类型	定义	举例
部分函数依赖	设X,Y是关系R的两个属性集合，存在X→Y，若X’是X的真子集，存在X’→Y，则称Y部分函数依赖于X。	AB->C，A->C ==》C部分依赖于AB。
完全函数依赖	设X,Y是关系R的两个属性集合，X’是X的真子集，存在X→Y，但对每一个X’都有X’!→Y，则称Y完全函数依赖于X。	AB->C，但是AB单独得不出C ==》C完全依赖于AB.
传递函数依赖	设X,Y,Z是关系R中互不相同的属性集合，存在X→Y(Y !→X),Y→Z，则称Z传递函数依赖于X。	A->B,B->C且这个推导不可逆，那么成C传递依赖于A
多值依赖	设R(U)是属性集U上的一个关系模式。X，Y，Z是U的子集,并且Z=U-X-Y。关系模式R(U)中多值依赖X→→Y成立，当且仅当对R(U)的任一关系r,给定的一对(x，z)值有一组Y的值，这组值仅仅决定于x值而与z值无关。	有这样一个关系 <仓库管理员，仓库号，库存产品号> ，假设一个产品只能放到一个仓库中，但是一个仓库可以有若干管理员，那么对应于一个 <仓库管理员，库存产品号>有一个仓库号，而实际上，这个仓库号只与库存产品号有关，与管理员无关，就说这是多值依赖。

6）范式

各个范式联系：
5NF⊂4NF⊂BCNF⊂3NF⊂2NF⊂1NF

范式	满足最低要求	定义
1NF	字段不可再分，原子性	如果一个关系模式R的所有属性都是不可分的基本数据项，则R∈1NF
2NF	消除部分子函数依赖：一个表只能说明一个事物; 即要求数据库表中的每个实例或行必须可以被唯一地区分。	若R∈1NF，且每一个非主属性完全函数依赖于码，则R∈2NF。
3NF	消除传递依赖，即消除非主属性对键的传递依赖：每列都与主键有直接关系，不存在传递依赖。任何非主属性不依赖于其它非主属性。	若R∈3NF，则每一个非主属性既不部分依赖于码，也不传递依赖于码。
BCNF(修正第三范式、扩充第三范式 消除主属性对键的传递依赖）	所有非主属性对每一个码都是完全函数依赖； 所有主属性对每一个不包含它的码，也是完全函数依赖； 没有任何属性完全函数依赖于非码的任何一组属性。
4NF		关系模式R<U,F>∈1NF，如果对于R的每个非平凡多值依赖X->->Y（Y∉X），X都含有码，则称R<U,F>∈4NF

3，行存储和列存储对比

1）行存储（Row-Based）

1. 传统关系数据库按行存储。
2. 行按顺序写入磁盘。
3. 查询时容易产生冗余数据，大数据情况下影响数据处理效率。
4. 举例：
   MongoDB是文档型的行存储；
   Lexst是二进制型的行存储。

2）列存储（Column-Based）

1. 按列存储，由于每一列的类型一样且value也可能一样，极大的增加压缩比，每次io数据量更大，从而提高检索统计性能。
2. 每一列由一个线程来处理，即查询的并发处理性能高。
3. 列族（column group，Bigtable系统中称为locality group）:即将多个经常一起访问的数据列的各个值存放在一起。如果读取的数据列属于相同的列族，列式数据库可以从相同的地方一次性读取多个数据列的值，避免了多个数据列的合并。列族是一种行列混合存储模式，这种模式是行存储与列存储的结合。
4. 举例：Hadoop的HBase、

3）对比

1>数据写入：推荐行存储

1. 行存储的写入是一次完成。如果这种写入建立在操作系统的文件系统上，可以保证写入过程的成功或者失败，数据的完整性因此可以确定。
2. 列存储由于需要把一行记录拆分成单列保存，写入次数明显比行存储多（意味着磁头调度次数多，而磁头调度是需要时间的，一般在1ms~10ms)，再加上磁头需要在盘片上移动和定位花费的时间，实际时间消耗会更大。所以，行存储在写入上占有很大的优势。
3. 数据修改是对磁盘上的记录做删除标记。行存储是在指定位置写入一次，列存储是将磁盘定位到多个列上分别写入，这个过程仍是行存储的列数倍。所以，数据修改也是以行存储占优。

2>数据读取：推荐列存储

1. 数据读取时，行存储通常将一行数据完全读出，如果只需要其中几列数据的情况，就会存在冗余列，出于缩短处理时间的考量，消除冗余列的过程通常是在内存中进行的。
2. 列存储每次读取的数据是集合的一段或者全部，不存在冗余性问题。极大的减少缓冲池使用率，减少I/O。
3. 两种存储的数据分布。
   由于列存储每一列类型一致，数据解析比较简单。
   相比之下，行存储时每一行记录中保存了多种类型的数据，数据解析需要在多种数据类型之间频繁转换，这个操作很消耗CPU，增加了解析的时间。
4. 列存储更容易压缩，极大的缩小了磁盘读取时间、网络传输时间。

4）场景

1. OLTP(Online transaction processing，在线/联机事务处理)：==》主要是对数据库中的数据进行增删改查，适合行存储。
   操作主体一般是产品的用户。

2. OLAP(Online analytical processing，联机分析处理，数据仓库)：==》查询统计，适合列存储。
   通过分析数据库中的数据来得出一些结论性的东西。比如给老总们看的报表，用于进行市场开拓的用户行为统计，不同维度的汇总分析结果等等。操作主体一般是运营、销售和市场等团队人员。

4，数据库语句

SQL 语言是非过程化的语言，易学习。

1）三级模式结构

三级模式结构：外模式、模式和内模式。
三级模式结构有效地组织、管理数据，提高了数据库的逻辑独立性和物理独立性 ==》使数据库达到了数据独立性。

1>模式（schema，逻辑模式）

a）概念

一个数据库只有一个模式。区分不同数据库下的相同表名（相当于编程时的命名空间）。

举例：
一个公司的系统，分2个子系统，分别为财务系统和人力资源系统.
这2个子系统, 共用一个数据库。
那么 财务系统的表, 可以放在财务的模式（schema）.
人力资源系统的表，放在人力资源系统的模式里面。
这2个子系统，能够互相访问对方的表。
但是又不因为 表重名 的问题，影响对方。

2>外模式（子模式，用户模式）

外模式通常是模式的子集，一个数据库可以有多个外模式，但一个应用程序只能有一个外模式。
外模式是保证数据库安全性的一个有力措施：用户只能访问外模式的数据，其余数据不可见。

3>内模式（存储模式）

一个数据库只有一个内模式。
内模式是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式。

数据库管理系统在三级模式之间提供了两层映像：
外模式/模式映像（保证数据的逻辑独立性）
模式/内模式映像（保证了物理独立性）

2）数据库授权

①授权GRANT

    GRANT <权限>
    ON <对象类型>  <对象名>
    TO <用户>
    [WITH GRANT OPTION]  // 如果指定了WITH GRANT OPTION子句，则获得某种权限的用户还可以把这种权限再授予其他用户，允许用户传递权限，但是不允许循环授权。

举例：

例1：把查询Student表的权限授给用户U1
GRANT SELECT
ON TABLE Student
TO U1;

例2：把全部操作权限授予用户U2和U3
GRANT ALL PRIVILEGES
ON TABLE Student,Course
TO U2,U3;

例3：把查询权限授予所有用户
GRANT SELECT
ON TABLE SC
TO PUBLIC;

③权限的收回 REVOKE

REVOKE <权限>
ON <对象类型>  <对象名>
FROM <用户>

举例：

例6：收回所有用户对表sc的查询权限
REVOKE SELECT
ON TABLE SC
FROM PUBLIC;

③对用户模式的授权

由DBA（数据库管理员，Database Administrator，简称DBA）在创建用户时实现。

CREATE USER <username>
[WITH] [DBA|RESOURCE|CONNECT]

只有系统的超级用户才有权创建一个新的数据库用户
新创建的用户有三种权限：DB，|RESOURCE，CONNECT

④数据库角色创建及授权

CREATE ROLE <角色名>

给角色授权：

GRANT <权限>
ON <对象类型>  对象名
TO <角色>

将一个角色授予其他的角色或用户

GRANT <角色1>
TO <角色3>
[WITH ADMIN OPTION]//如果指定了WITH ADMIN OPTION 子句，则获得某种权限的角色或用户还可以把这种权限再授予其他角色

角色权限的收回

REVOKE <权限>
ON <对象类型>  <对象名>
FROM <角色>

⑤DENY 拒绝账户访问

在安全系统中创建一项，以拒绝给当前数据库内的安全帐户授予权限并防止安全帐户通过其组或角色成员资格继承权限。

DENY { ALL | statement [ ,...n ] }
TO security_account [ ,...n ]

和授权区别：
不授权是没有权限，但是如果这个用户属于某个角色，这个角色有了权限，那么这个用户可以从角色继承这个权限。如果选择了deny，即使这个用户属于某个具有权限的角色，他也没有权限。

5，并发控制

为了保证事务的隔离性和一致性，DBMS需要对并发操作进行正确调度。

1）并发操作带来的数据不一致性

①更新丢失

②读“脏”数据

事务T1修改数据，T2读取数据，T1由于某种原因被撤销，则数据修改回原值，但T2读取的数据是之前修改的数据，即脏数据、不正确的数据。

③不可重复读

事务T1读数据后，T2修改了数据，T1无法再现上一次读取的结果。

④幻读

事务T1读数据后，T2新增或者删除了数据，T1无法再现上一次读取的结果。

2）并发控制技术

悲观锁：封锁 ==>不同平台锁机制不同。
乐观锁：版本号、时间戳

3）封锁问题

①活锁

i>饥饿

考虑一台打印机分配的例子，当有多个进程需要打印文件时，系统按照短文件优先的策略排序，该策略具有平均等待时间短的优点，似乎非常合理，但当短文件打印任务源源不断时，长文件的打印任务将被无限期地推迟，导致饥饿以至饿死。

ii>活锁概念

与饥饿相关的另外一个概念称为活锁，在忙式等待条件下发生的饥饿，称为活锁。

a）忙式等待：不进入等待状态的等待。
b）阻塞式等待：进程得不到共享资源时将进入阻塞状态，让出CPU 给其他进程使用。
c）忙等待和阻塞式等待的相同之处：
在于进程都不具备继续向前推进的条件，不同之处在于处于忙等待的进程不主动放弃CPU，尽管CPU 可能被剥夺，因而是低效的；而处于阻塞状态的进程主动放弃CPU ，因而是高效的。

iii>举例

事务T1请求封锁R，T2请求封锁R，T3请求封锁R……
T1释放R之后，系统批准了T3的请求，然后是T4……请求，T2可能永远等待下去。（在整个过程中，事务T2 在不断的重复尝试获取锁R）。

iv>与死锁区别

活锁的时候，进程是不会阻塞的，这会导致耗尽CPU 资源，这是与死锁最明显的区别。
处于活锁的实体是在不断的改变状态，所谓的“活”， 而处于死锁的实体表现为等待；活锁有一定几率解开，而死锁是无法解开的。

v>避免方式

采用先来先服务策略。

②死锁

i>概念

是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去，此时称系统处于死锁状态或系统产生了死锁。

ii>举例

T1请求封锁R1，T2请求封锁R2，然后T1又请求封锁R2，T1一直等待T2释放R2，此时，T2请求封锁R1，T2将一直等待T1释放R1。

iii>死锁原因

在数据库中，产生死锁的原因主要是：
两个或多个事务都已封锁了一些数据对象，然后又都请求其他事务已封锁的数据对象，从而出现死等待。

产生死锁的四个必要条件：
（1） 互斥条件：一个资源每次只能被一个进程使用。
（2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
（3） 不可剥夺条件: 进程已获得的资源，在末使用完之前，不能强行剥夺。
（4） 环路等待条件: 若干进程之间形成一种头尾相接的循环等待资源关系。
只要系统发生了死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死
锁。

iv>死锁预防

预防死锁的发生只需破坏死锁产生的四个必要条件之一即可。

1. 破坏互斥条件
   如果允许系统资源都能共享使用，则系统不会进入死锁状态。但有些资源根本不能同时访问，如打印机等临界资源只能互斥使用。所以，破坏互斥条件而预防死锁的方法不太可行，而且在有的场合应该保护这种互斥性。
2. 破坏不剥夺条件
   当一个已保持了某些不可剥夺资源的进程，请求新的资源而得不到满足时，它必须释放已经保持的所有资源，待以后需要时再重新申请。这意味着，一个进程已占有的资源会被暂时释放，或者说是被剥夺了，或从而破坏了不可剥夺条件。
   该策略实现起来比较复杂，释放已获得的资源可能造成前一阶段工作的失效，反复地申请和释放资源会增加系统开销，降低系统吞吐量。这种方法常用于状态易于保存和恢复的资源，如CPU 的寄存器及内存资源，一般不能用于打印机之类的资源。
3. 破坏请求和保持条件
   釆用预先静态分配方法，即进程在运行前一次申请完它所需要的全部资源，在它的资源未满足前，不把它投入运行。一旦投入运行后，这些资源就一直归它所有，也不再提出其他资源请求，这样就可以保证系统不会发生死锁。
   这种方式实现简单，但缺点也显而易见，系统资源被严重浪费，其中有些资源可能仅在运行初期或运行快结束时才使用，甚至根本不使用。而且还会导致“饥饿”现象，当由于个别资源长期被其他进程占用时，将致使等待该资源的进程迟迟不能开始运行。
4. 破坏环路等待条件
   为了破坏循环等待条件，可釆用顺序资源分配法。首先给系统中的资源编号，规定每个进程，必须按编号递增的顺序请求资源，同类资源一次申请完。也就是说，只要进程提出申请分配资源Ri，则该进程在以后的资源申请中，只能申请编号大于Ri 的资源。
   这种方法存在的问题是，编号必须相对稳定，这就限制了新类型设备的增加；尽管在为资源编号时已考虑到大多数作业实际使用这些资源的顺序，但也经常会发生作业使甩资源的顺序与系统规定顺序不同的情况，造成资源的浪费；此外，这种按规定次序申请资源的方法，也必然会给用户的编程带来麻烦。

都不好用，一般采用死锁的诊断和解除。

v>死锁的诊断和解除

a)超时法
如果一个事务等待时间超时，则认为发生死锁。（可能误判）
b)事务等待图法
事务等待图是一个有向图，反映了事务的等待情况。如果图中出现回路，就表示出现了死锁。

处理方案是：选择一个处理代价最小的事务，将其撤销并释放所有锁。
a) 从死锁进程处剥夺资源
b) 终止部分或全部进程

4) 两段锁协议（Two-Phase Locking――2PL）

两段锁协议规定所有的事务应遵守的规则：
① 在对任何数据进行读、写操作之前，首先要申请并获得对该数据的封锁。
② 在释放一个封锁之后，事务不再申请和获得其它任何封锁。
即事务的执行分为两个阶段：
第一阶段是获得封锁的阶段，称为扩展阶段。
第二阶段是释放封锁的阶段，称为收缩阶段。

定理：若所有事务均遵守两段锁协议，则这些事务的所有交叉调度都是可串行化的。
对于遵守两段协议的事务，其交叉并发操作的执行结果一定是正确的。值得注意的是，上述定理是充分条件，不是必要条件。一个可串行化的并发调度的所有事务并不一定都符合两段锁协议，存在不全是2PL的事务的可串行化的并发调度。
同时我们必须指出，遵循两段锁协议的事务有可能发生死锁。

此时事务T1 、T2同时处于扩展阶段，两个事务都坚持请求加锁对方已经占有的数据，导致死锁。
为此，又有了一次封锁法。一次封锁法要求事务必须一次性将所有要使用的数据全部加锁，否则就不能继续执行。因此，一次封锁法遵守两段锁协议，但两段锁并不要求事务必须一次性将所有要使用的数据全部加锁，这一点与一次性封锁不同，这就是遵守两段锁协议仍可能发生死锁的原因所在。

5）事务

7，常见概念模型

1）DFD 数据流图（Data Flow Diagram）

2）ER图 实体-联系图（Entity-Relationship Diagram）

实体：矩形表示
属性：椭圆表示，并用直线与实体连接
联系：菱形表示，用直线与实体连接，同时在边上标上联系的类型（1：1，1：n，m:n)。

一个联系转化为一个关系模式，与该联系相连的各实体的码以及联系的属性转化为关系的属性，该关系的码则有三种情况：
若联系为1：1，则每个实体的码均是该关系的后选码。
若联系为1：n，则关系的码为n端实体的码。
若联系为m：n，则关系的码为诸实体码的组合。

8，数据库安全

1）SQL注入

①概念

在SQL 语句在拼接的情况下，用户输入为一部分sql语句。

②解决方法

i> 对特殊字符进行过滤、转义或者使用预编译的sql 语句绑定变量

SQL执行时，2种方式：
①字符串处理（拼接），然后执行SQL
用户输入的时候，可以通过输入sql语句来进行SQL注入。
②传参，执行SQL -->交给SQL引擎**（推荐）**
用prepareStatement,参数用set 方法进行填装。

String sql= "insert into userlogin values(?,?)";
PreparedStatement ps=conn.prepareStatement(sql);
for(int i=1;i<100;i++){
ps.setInt(1, i);
ps.setInt(2, 8888);
ps.executeUpdate();
ps.close();
conn.close();

ii> 当sql 语句运行出错时，不要把数据库返回的错误信息全部显示给用户，以防止泄漏服务器和数据库相关信息

iii>检查变量的数据类型和格式

只要是有固定格式的变量，在SQL 语句执行前，应该严格按照固定格式去检查，确保变量是我们预想的格式，这样很大程度上可以避免SQL 注入攻击。
例如：对于where id={$id}这种形式，数据库里所有的id 都是数字，那么就应该在SQL 被执行前，检查确保变量id 是int 类型。

iv>所有的SQL 语句都封装在存储过程中

所有的SQL 语句都封装在存储过程中，这样不但可以避免SQL 注入，还能提高一些性能。

9，分布式数据库

1）概念

分布式数据库是一个物理上分散的而逻辑上集中的数据集。
它有三大特点： 数据分布性 逻辑关联性 站点自治性

2）五个基本原则

1. 资源的重复性
   指分布式系统中硬件，软件以及数据的冗余配置。
2. 物理上的分布性
   从硬件，软件以及数据上看都是相互独立地分布。
3. 高层操作系统（或者分布式操作系统）
   高层操作系统负责对分布性的资源进行统一的控制，它使一个简单的硬件堆积转变为一个统一协调的工作系统。
4. 系统的透明性
   透明性是分布式系统的灵魂，实现不同层次的透明性是分布式系统必须解决的关键问题之一。
5. 协作的自治性
   每一节点都是一个完整的处理系统，同时又是合作的。 简而言之：分布式系统是一个多节点的，处理或数据分布的，在统一下提高综合处理能力的协作体。

3）待解决问题

不完整系统状态信息
时间延迟
通信的代价
负载均衡

4）分类（从控制方式角度）

①紧耦合式DDBMS

全局控制信息放在一个称为中心站点的站点上。所有的全局访问都必须通过中心站点来确定远程数据片的位置。
优点：容易实现数据的一致性和完整性。
缺点：易产生访问瓶颈，系统效率不高，可靠性较差。

②联邦式DDBMS

每个站点都包含全局控制信息的一个副本，都可以接受全局访问。任何对远程数据的请求，都可以通过广播方式传播到其他节点。
优点：具有较好的可靠性和可用性，并行性好，更容易适应旧有的系统集成和异构分布式数据库系统的建立。
缺点：保持数据的一致性很困难，实现难度大。

③组合式DDBMS

是上述方案的折衷，它把站点分为两类，一类具有全局控制信息，称为主节点，可以接受全局任务，另一类没有全局信息，只能为主节点提供数据服务。
优点：灵活性较好，易于实现层次控制结构。
缺点：设计复杂。

5）分布透明性

即在分布式数据库系统中用户不必关心数据的分布情况。分为三个层次：

①分片透明性

它是分布式数据库系统的最高透明性层次，它向用户完全屏蔽了DDB的分片信息。这样的透明性保持了高水平的数据独立性。

②位置透明性

用户的应用程序不需要关心数据分片的具体存储站点，当数据库的数据片的存储站点发生改变时，只需改变对应的GRS/NRS映射就可以保持全局表示模式不发生改变

③数据模型透明性

它向用户屏蔽的只是本站点的具体数据库存储及其管理情况。 在异构的情况下，这种透明性避免了用户对不同数据模型的转换的实现。
本地透明性是3种透明方式中最低的。

6）数据分割方法

①水平分割

把全局关系的元组分割成一些子集，这些子集被称为数据分片或段（Fragment）。
水平分割可以通过关系运算“选择”来定义。

水平分片是对全局关系执行“选择”操作，把具有相同性质的元组进行分组，构成若干个不相交的子集.水平分片的方法可归为初级分片和导出分片两类。

②垂直分割

把全局关系按照属性组（纵向）分隔成一些数据分片或段。
垂直分割可以通过关系运算“投影”来定义。

③混合分割

可把水平分割和垂直分割这两种方法结合起来使用，产生混合式数据分片。

④数据分片应遵循的原则

若R={R1，R2，…，Rn}满足：
1)完整性(completeness)条件：
如果分片 a∈R，则必有a∈Ri，i=l，2，…，n
2)可重构(reconstructed)条件：
R=∪ Ri，(水平分片)或R=∞Ri，(垂直分片)
3)不相交(disjoint)条件：
Ri∩ Rj=φ,i≠j,I,j：=1,2,…,，n(水平 分片)
Ri∩Rj=主键属性,I,j=1，2,…,n(垂直分片)

7）分布式数据库和集中式区别

分布式（distributed）是指在多台不同的服务器中部署不同的服务模块，通过远程调用协同工作，对外提供服务。
集群（cluster）是指在多台不同的服务器中部署相同应用或服务模块，构成一个集群，通过负载均衡设备对外提供服务。

10，数据库优化

1）优化SQL 语句

①explain

通过explain(查询优化神器)用来查看SQL 语句的执行效果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。
通常我们可以对比较复杂的尤其是涉及到多表的SELECT 语句，把关键字EXPLAIN 加到前面，查看执行计划。例如：explain select * from news;

explain语法：

explain select … from … [where ...] 

② 用具体的字段列表代替*

任何地方都不要使用select * from t ，不要返回用不到的任何字段。

③ 不在索引列做运算或者使用函数

④ 查询尽可能使用limit 减少返回的行数，减少数据传输时间和带宽浪费。

2）优化表的数据类型

① 使用procedure analyse()函数对表进行分析

该函数可以对表中列的数据类型提出优化建议。能小就用小。表数据类型第一个原则是：使用能正确的表示和存储数据的最短类型。这样可以减少对磁盘空间、内存、cpu 缓存的使用。
使用方法：select * from 表名procedure analyse();

② 对表进行拆分

通过拆分表可以提高表的访问效率。有2 种拆分方法：
1.垂直拆分
把主键和一些列放在一个表中，然后把主键和另外的列放在另一个表中。如果一个表中某些列常用，而另外一些不常用，则可以采用垂直拆分。
2.水平拆分
根据一列或者多列数据的值把数据行放到二个独立的表中。

③ 使用中间表来提高查询速度

创建中间表，表结构和源表结构完全相同，转移要统计的数据到中间表，然后在中间表上进行统计，得出想要的结果。

3）硬件优化

很多时候就是单纯的数据量太大导致的sql查询慢，这种情况两种处理方式：一是优化业务逻辑；二是优化硬件。

通过命令可以查看磁盘读写性能是否达到瓶颈：sar -d 1 100，通过top命令可以查看cup、mem是否达到瓶颈。具体命令参见：shell 命令（mac、linux)及shell脚本

①CPU 的优化

选择多核和主频高的CPU。

②内存的优化

使用更大的内存。将尽量多的内存分配给MYSQL 做缓存。

③磁盘I/O 的优化

第一次sql查询的时候，查询速度受磁盘I/O读写速率影响，并把数据更新到缓存。第二次进行相同的查询时，由于命中缓存速度会大大提升。

i>使用磁盘阵列

RAID 0 没有数据冗余，没有数据校验的磁盘陈列。实现RAID 0至少需要两块以上的硬盘，它将两块以上的硬盘合并成一块，数据连续地分割在每块盘上。
RAID1 是将一个两块硬盘所构成RAID 磁盘阵列，其容量仅等于一块硬盘的容量，因为另一块只是当作数据“镜像”。
使用RAID-0+1 磁盘阵列（又称RAID 1+0，RAID10）。RAID 0+1 是RAID 0 和RAID 1 的组合形式。它在提供与RAID 1 一样的数据安全保障的同时，也提供了与RAID 0 近似的存储性能提高了磁盘吞吐量（IO性能）。

ii>调整磁盘调度算法

选择合适的磁盘调度算法，可以减少磁盘的寻道时间。

4）MySQL 自身的优化

对MySQL 自身的优化主要是对其配置文件my.cnf 中的各项参数进行优化调整。如指定MySQL 查询缓冲区的大小，指定MySQL 允许的最大连接进程数等。

5）应用优化

①使用数据库连接池

②使用查询缓存

它的作用是存储select 查询的文本及其相应结果。如果随后收到一个相同的查询，服务器会从查询缓存中直接得到查询结果。查询缓存适用的对象是更新不频繁的表，当表中数据更改后，查询缓存中的相关条目就会被清空。

6）大访问量的优化

①使用优化查询的方法

（见上面）

②主从复制，读写分离

i>主从复制（master，slave）:

通过配置两台（或多台）数据库的主从关系，可以将一台数据库服务器的数据更新同步到另一台服务器上。网站可以利用数据库的这一功能，实现数据库的读写分离，从而改善数据库的负载压力。一个系统的读操作远远多于写操作，因此写操作发向master，读操作发向slaves 进行操作（简单的轮循算法来决定使用哪个slave）。
利用数据库的读写分离，Web 服务器在写数据的时候，访问主数据库（Master），主数据库通过主从复制机制将数据更新同步到从数据库（Slave），这样当Web 服务器读数据的时候，就可以通过从数据库获得数据。这一方案使得在大量读操作的Web 应用可以轻松地读取数据，而主数据库也只会承受少量的写入操作，还可以实现数据热备份，可谓是一举两得的方案。

③负载均衡(Load Balance，简称LB)

7）数据库分表、分区、分库

分表见上面描述。
分区就是把一张表的数据分成多个区块，这些区块可以在一个磁盘上，也可以在不同的磁盘上，分区后，表面上还是一张表，但数据散列在多个位置，这样一来，多块硬盘同时处理不同的请求，从而提高磁盘I/O 读写性能，实现比较简单。包括水平分区和垂直分区。
分库是根据业务不同把相关的表切分到不同的数据库中，比如web、bbs、blog 等库。

8）表空间优化（缩表）

表存储结构的变更导致旧的表空间没用释放，即产生了磁盘碎片。
方案：mysql有optimize table，pg有vacuum。

1>场景

1. delete语句删除数据。
2. 聚集索引进行了插入操作。

9）慢查询优化

1. 主键查询 ==>无法优化
2. 是否使用了索引 ==》可能也没命中，或者索引建立有问题
3. select * ==》 修改为具体的列，去掉多余的列
4. 数据量太大 ==》横向或纵向的分表、分库

17，应用

1）数据库连接：JDBC与JdbcTemplate