一.数据库的优化

1.水平切分

根据业务的不同，将原先拥有很多字段的表拆分为两个或者多个表，这样的代价我个人觉得很大，原来对这应这个表的关系，开始细分，需要一定的重构，而且随着数据量的增多，极有可能还要增加水平切分

2.垂直切分

数据表结构，将数据分散在多个表中

1.数据库的的分表分库

参考:https://blog.csdn.net/winy_lm/article/details/50708493

1.分表

对于访问极其频繁且数据量巨大的表单来说，我们首先要做的就是减少表单的表单的数据量，以便减少数据查询所需要的时间，提高数据库的吞吐，这就是所谓的分表！

2.分表策略

在分表之前，首先需要选择适当的分表策略，使得数据能够较为均衡地分不到多张表中，并且不影响正常的查询！

用户 id 直接 mod 分成表的数目大小，将大表拆成小表

3.分库和分库策略

对数据库进行拆分，从而提高数据库写入能力，这就是所谓的分库!

用户 id 直接 mod 分成表的数目大小，将大表拆成小表

4.分库分表

场景：有的时候既要面临高并发的访问情况，也会面临大量数据的存储问题，这时需要即对数据库采用分库策略，也要采用分表策略，以便扩展系统的并发处理能力，同时提高单表的查询性能，这就是分库分表。

5.分库分表策略

1.中间变量：user_id % (分库数量*每个库当中表的数量)
2.库：取整（中间变量/每个库的表数量）
3.表：中间变量%每个库的表数量

假设将原来的单库单表order拆分成256个库，每个库包含1024个表，那么按照前面所提到的路由策略，对于user_id=262145 的访问，路由的计算过程如下：

1.  中间变量 = 262145 % (256 * 1024) = 1
2.  库 = 取整 (1/1024) = 0
3.  表 = 1 % 1024 = 1
这就意味着，对于user_id=262145 的订单记录的查询和修改，将被路由到第0个库的第1个order_1表中执行！！！

2.主从复写/读写分离

面试考点：https://blog.csdn.net/darkangel1228/article/details/80004222

读写分离：读从库，写主库，spring配置两个数据库，通过AOP（面向切面编程），在写或读方法前面进行判断得到动态切换数据源。

数据库的主从复写

1.异步复制

调用原理:从库生成两个线程，一个I/O线程，一个SQL线程；
1.当主库更新数据后,会实时的写入到二进制日志文件中
2.从库的IO线程,实时的监听主库的二进制文件,如果二进制文件发送了改变则启动线程进行读取修改后的内容.
3.通过IO线程将读取的二进制文件写入到中继日志中.
4.Sql线程实时读取中继日志中的消息,进行数据库的"更新操作"

image.png

2.异步复制可能出现的问题

主库宕机后，数据可能丢失
从库只有一个sql Thread，主库写压力大，复制很可能延时

3.半同步复制----解决了数据丢失问题

事务在主库写完binlog后需要从库返回一个已接受，才放回给客户端；

image.png

4.同步复制-----解决从库复写延迟的问题

所谓的同步复制，意思是master的变化，必须等待slave-1,slave-2,...,slave-n完成后才能返回。 这样，显然不可取，也不是MySQL复制的默认设置。比如，在WEB前端页面上，用户增加了条记录，需要等待很长时间。

从库创造多个SQL解析线程来对binlog日志进行解析，但是在从库当中对数据的修改还是串型的，可以通过如下的方式设置SQL解析线程的数量为10：

set global slave_parallel_workers=10;

二.数据库范式

摘要自：[https://www.cnblogs.com/manqing321/p/9967109.html]

image.png
数据库范式是用来定义数据库规范化的。
image.png

1.1NF

概念是：数据库表每一项都是不可再分的项；用一句简单的话说，就是所有的属性都是单一的，你不能一个格里面写俩属性，也没地儿写啊不是。图解：

image.png
这个（姓名，学号，年龄，基本信息）就不符合基础级别(1NF)，基本信息是可以再分的，因为数据表中，一格是不能同时写上多个数据的。

2.2NF

二范式(2NF)是建立在一范式的基础上的。概念是数据表的实例或者属性必须被唯一的区分。同样用简单的话说，就是你不能把所有的信息放一块儿吧。总得根据关键信息把表分开吧。不然查起来多麻烦。就像学生信息表，班长拿了一张学生信息表，里面写的电话QQ微信身份证班级学号，再加点和学校有关的差不多就够了。但是学生的信息太多了，兴趣爱好，性格特长，家庭成员，等等等等信息，放在同一张表是不可能的。图解：

image.png
我们可以看到这张表已经满足一范式了。他的属性都被分得明明白白的。

不同的信息，他们的身份证号码不同，学号也不同。我们就可以说，身份证和学号是这张表的码。

出生日期，籍贯，这之类的信息，主要是由身份证来区分的。而班级信息基本是靠学号区分。这就是说，如果我想查班级信息，我拿到学号就够了，身份证号码对于我想查的班级信息，并没有必要。我们就称这种表存在不完全依赖。去掉这种不完全依赖的办法，就是以学号，身份证号码，将上面的一大张表，拆成两张，一张以学号作码，一张以身份证做码。这样就去掉了不完全依赖，就符合第二范式了。拆开之后，应该是这样：

image.png

3.3NF消除传递依赖

image.png

上面的“课程表，教材表”符合2NF，但不符合3NF，一个老师一定能确定一个老师职称，则存在传递依赖：
（学生，课程）->老师->职称

由此引入的问题：
修改异常：老师升级了，变教授了，要改数据库，表中有N条，改了N次……
删除异常：没人选这个老师的课了，老师的职称也没了记录……
插入异常：新来一个老师，还没分配教什么课，他的职称记到哪？……

继续拆分课程表，分为2张表：课程表、教师表
课程表（姓名，课程，教师，教室，上课时间）
教师表（教师，职称）
教材表（课程，教材）

image.png

三.数据库连接池

1.数据库连接池原理

参考自：https://blog.csdn.net/shuaihj/article/details/14223015
连接池的工作原理主要由三部分组成，分别为连接池的建立、连接池中连接的使用管理、 连接池的关闭。

1.连接池的建立

一般在系统初始化的时候，数据库连接池建立，并根据系统的配置创建数据库连接对象，连接池当中的连接和关闭不能随意的开关，不忍十分消耗数据库的性能，java 中提供了很多容器类可以方便的构 建连接池，例如 Vector、Stack 等。

2.连接池的管理和使用

连接池管理策略是连接池机制的核心，连接池的分配和释放十分消耗系统的性能，连接池的管理策略是：
当客户请求数据库连接时，首先查看连接池当中是否有空闲连接，有就分配给他，没有就看连接池当中的连接数是否已经达到最大值；如果没有就重新创建一个；如果达到最大，就等待；如果超出最大等待时间，则抛出异常给客户。当客户释放数据库连接时，先判断该连接的 引用次数是否超过了规定值，如果超过就从连接池中删除该连接，否则保留为其他客户服务。
该策略保证了数据库连接的有效复用，避免频繁的建立、释放连接所带来的系统资源开销

3.连接池的关闭

连接池的关闭。当应用程序退出时，关闭连接池中所有的连接，释放连接池相关的资 源，该过程正好与创建相反

像打开关闭数据库连接这种和数据库的交互可能是很费时的，尤其是当客户端数量增加的时 候，会消耗大量的资源，成本是非常高的。可以在应用服务器启动的时候建立很多个数据库 连接并维护在一个池中。连接请求由池中的连接提供。在连接使用完毕以后，把连接归还到 池中，以用于满足将来更多的请求。

在查询连接池当中的最大连接数是否已经到达的时候，要求在配置文件当中设置rmoveAbandoned=true,当快要达到最大的连接数量的时候，会先将在某段时间内(设置的rmoveAbandonedTimeout)没有使用的Connection释放

image.png

四.MySQL大数据量分页查询方法及其优化

转载自：[https://uule.iteye.com/blog/2422189]

select * from table limit m,n
-- 其中m是指记录开始的index，表示每次开始的索引。默认从0开始，表示第一条记录
-- n是指从第m+1条开始，取n条。
select * from tablename limit 2,4
-- 即取出第3条至第6条，4条记录

在mysql中limit可以实现快速分页，但是如果数据到了几百万时我们的limit必须优化才能有效的合理的实现分页了，否则可能卡死你的服务器哦。
如 * from table limit 0,10 这个没有问题 当 limit 200000,10 的时候数据读取就很慢。
limit10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行，问题就在这里。
LIMIT 451350,30 扫描了45万多行，怪不得慢的都堵死了。

1.子查询优化法(数据要连续)

1.原始 slq 语句:select * from Member limit 10000,100
2.先找出第一条数据:select MemberID fro m Member limit 100000,1
3.然后大于等于这条数据的 id 就是要获取的数据:select * from Member where MemberID >= (select MemberID fro m Member limit 100000,1) limit 100

-- 缺点:数据必须是连续的，可以说不能有 where 条件，where 条件会筛选数据，导致数据失去连续性

2.使用 id 限定优化（假设数据表的id是连续递增）

1.原始 sql 语句:select* from orders_history where type=2 limit 100000,100.
2.这种方式假设数据表的id是连续递增的，则我们根据查询的页数和查询的记录数可以算出查询的id的范围，可以使用 id between and 来查询:
3.select * from orders_history where type=2 and id between 1000 000 and 1000100 limit 100;

3.反向查找优化

1.当偏移超过一半记录数的时候，先用排序，这样偏移就反转了,共有100w数据，查询90w到100w时间的数据，可以先倒序，然后在查找。
2.缺点:order by 优化比较麻烦，要增加索引，索引影响数据的修改效率，并且要知道总记录数 ，偏移大于数据的一半

image.png

五.其他

1.char varchar text 区别

1、 char长度固定， 即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定。长度范围是0~255
2、 varchar可变长度，可以设置最大长度；适合用在长度可变的属性。 65535
3、 text不设置长度， 当不知道属性的最大长度时，适合用text。65535
4.  按照查询速度： char最快， varchar次之，text最慢。 text 的 65535 字节全部用来存储数据，varchar 则会占用 1-3 个字节去存储数据大小。

2.drop delete truncate区别

 delete 和 truncate 只删除表的数据不删除表的结构
 速度,一般来说: drop> truncate >delete
 delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;

如果有相应的 trigger,执行的时候将被触发. truncate,drop 是 ddl, 操作立即生效,原数据不放到 rollback segme nt 中,不能回滚. 操作不触发 trigger.
drop 直接删掉表
truncate 删除表中数据，再插入时自增长 id 又从 1 开始 delete 删除表中数据，可以加 where 字句。

3.视图

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有 一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比 多表查询。

4.存储过程与触发器

参考；https://blog.csdn.net/pengxiang1998/article/details/80462998

存储过程：为了完成特殊功能的SQL集，进过编译后存储在数据库当中，用户通过给定的存储过程的名字来调用它并传递参数(如过有参数)，

image.png

存储过程的优缺点？

> **优点：**
> 
> 1）存储过程是预编译过的，执行效率高。
> 
> 2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。
> 
> 3）安全性高，执行存储过程需要有一定权限的用户。
> 
> 4）存储过程可以重复使用，可减少数据库开发人员的工作量。
> 
> **缺点：**移植性差

触发器：当满足某个条件的时候，触发器里面定义的SQL语句就会执行，它是自动执行的，

创建触发器：
语法：
create  trigger 触发器名称  触发的时机  触发的动作  on 表名 for each row 触发器状态。

参数说明：

触发器名称:  自己定义

触发的时机： before /after  在执行动作之前还是之后

触发的动作 :指的激发触发程序的语句类型<insert ,update,delete>

触发器创建语法四要素：
1.监视地点(table) 
2.监视事件(insert/update/delete) 
3.触发时间(after/before) 
4.触发事件(insert/update/delete)

image.png

编写触发器，在每天12点以后，不允许修改雇员工资和奖金。

代码：

create or replace triggeremp_time1
before update of sal,comm   //针对表中某一属性列作用用of
on emp
begin
if
to_char(sysdate,'HH24')>'12'
then raise_application_error(-20008,'当前时间无法对emp表进行修改工资或奖金的操作操作');//报错处理
end if;
end ;