数据库调优

一，数据库设计

数据库应该怎么设计？有什么规范吗？，有什么步骤吗？

    1数据设置一般步骤:

        1.1.需求分析

                需求分析要具体了解项目具体业务，那些模块，以及模块之间的相互联系。

        1.2.概要的设计

                知道了具体业务，就可以画一个E-R的图，把具体的表、表的字段、表之间的关系给具体实现出来。

        1.3.详细设计

                有了E-R图，就可以实际实现数据库、表、表的字段数据结构给实现创建出来。

    2.设计库有什么规范：

        1.三大范式

            1.1.原子性

                保证表每一个字段的原子性，不可拆分，比如：name字段，有很大部分就直接把姓和名字存储在一个字段，如果碰到业务需要拿到姓或者名字就比较麻烦。

            1.2.主键与列列相关

                保证一张表应该是描述一个对象。比如：一个用户表有了用户ID，又出现了部门信息部门ID，造成了一个部门可能出现多个ID的情况。应该把部门信息分出去为一个部门表。

            1.3.主键与列直接关联

                保证一个表中出现的字段应该是直接属于该表信息。比如：用户表出现了部门名称信息。虽然在查询用户所在部门名称时候很快，直接查询用户表就可以了，但是如果部门名称进行了修改，就得把用户表所有该部门的名称进行修改，非常的麻烦。

        2.面向对象的思想来创建数据库表

            创建数据库表也应该从向与面向对象思想，把表当成一个对象，表的每一列都代表的对象所有的属性。

            

            从而应对多种不同关系

            2.1.一对一

                    经常一对一的关系直接存储在一张表中。

            2.2.一对多

                    一对多的关系，一般是用主外键进行关联。比如：班级——学生，就是一对多的，应每条学生表有一个用于关联班级的字段和班级表主键相关联。

            3.3.多对多

                    多对多关系，一般使用中间表。比如：学生——课程，多个学生可能有多个不同的课程。应该创建一个中间表，把学生的ID和课程的ID再加一个主键生成一列数据存储。

---

二，数据库设计优化

数据库应该从哪方面优化呢？那些操作避免可以减少可以达到优化数据库的目的？

    1.命名风格

        以前刚开始，喜欢用中文拼英,汉字来进行命名，首先说一下拼英命名的缺陷，在拼英命名，在别人看数据库表字段时候会头很大，别说别人，在过了一段时间自己看都看不懂，这样很不易维护，所以要避免。中文命名是,是很多数据库对中文支持不友好，会出现很多问题，所以中文也不应该出现命名。最好的命名是已英文进行命名，以及最好加一个前缀表明范围，比如tbl_base_user,tbl表示数据表，bash表示基础，user表示用户，连贯的意思就是基础的用户表，现在我在公司都是这样命名的，也比较容易理解。

    2.主键

        自增ID

                使用自增ID的好处就是，自己会自动增加，在sqlserver中默认使用聚集索引，空间小，也可以做一些特殊的业务， 坏处，数据导入麻烦，可能出现ID不一样或者ID冲突，或者表联查的时候可能出现sql语句错误然而却能查出错误的数据，在分表合并中会出现id重复的问题。

        GUID

                使用GUID的好处是，ID是唯一的，不会出现josn错误，数据导入方便，可以无限增长。 坏处，空间大，GUID没有意义，没有聚集索引。

        使用场景

                个人认为在数据量小的情况下可以使用自增ID，速度快，空间小。

                在数据量大，可能会涉及到分表的情况下建议使用GUID，可以保证ID的唯一性，先对于表汇总，以及数据导入都有很大的方便。

    3.外键

        外键

            外键一旦创建，从表创建数据时，对应的主表必须存在，要不然会创建失败，主表删除，对应从表的对应的信息都会被删除。好处，可以保证数据的完整性，可以做数据校验，级联删除。坏处，导入麻烦，必须保证主表数据先导入并且从表数据关联主表信息存在。

        虚拟外键

            虚拟外键，就是在业务逻辑上存在两表之间有关联，但是并不会创建外。好处，保证程序稳定，坏处，导入导出，以及数据修改比较麻烦。

        使用场景

            个人认为，外键尽量少使用，因为创建外键还是对数据库的性能会损耗，除一些业务要求严谨的情况，多数使用虚拟外键，在大型项目中，数据库往往是项目的瓶颈，所以减少外键使用可以达到优化数据库的目的。

    4.数据库事务

        数据库事务，就保证多条sql语句执行的时候，要么都成功，要么都失败。这是数据库非常重要的功能。

            开启事务的好处

                4.1.原子性：要么都成功，要么都失败。

                4.2.一致性：事务执行完，数据都是正确的。

                4.3.隔离性：在事务执行中，会将操作的表进行锁表，其他事务sql语句在该事务执行之后才能进行执行。

                4.4.持久性：在事务执行提交之后，就会把数据存储到磁盘文件中。

            开启事务的坏处

                在并发量大的情况下，会产生死锁，如何避免。

                4.5.要固定的执行sql语句的顺序。

                4.6.事务尽量简短，不要出现大量sql语句操作,并且在开启事务中，不要出现等待等一些见减慢事务操作的行为。

                4.6.在大批量操作的情况下，可以使用队列，保证数据处理一条一条执行，避免了并发的出现。

    5.存储过程

        存储过程就是一组特定的sql语句组合，通过编译存储到数据库中，优势，让编程代码减少，可以将业务直接在数据库处理，数据计算传输方便，让后端代码变得优雅。缺点，增大了数据库的工作量。后期维护管理不方便，不适用与复杂的业务。

        使用场景：

            个人认为，在大型项目中，并发量大去情况下最好不要使用存储过程，首先维护管理难度大，git，svn管理不到，增大了数据库的负荷，数据库优化应该尽量少使用存储过程。

    6.触发器

        触发器是一种特殊的存储过程，主要通过事件自动出发，可以分为after/instead of

            after：在触发器出发之后执行，

             instead of：在触发器执行之前执行。

        使用场景

            个人认为，应该避免使用，第一，后期人员维护难度大，第二，数据库负荷大，第三，有别的手段代替触发器，比如一般触发器，做数据校验，或者日志记录，这些都可以在后端实现，这样数据库减少了负担，达到数据库的优化。

    7.游标

        游标类型于数据的数据的赛选，通常配合着存储过程使用，一般使用在特别复杂的结果集进行加工。

        使用场景

            个人认为，尽量少使用游标，会影响数据库的性能，除非 特别复杂的结果集进行加工。

    8.视图

        视图就是一个虚拟表，把查询出的结果集存储到虚拟表中，好处，就是方便查询，坏处，就是不能新增删除修改。

        使用场景

            个人认为，视图维护管理也是不方便，也是降低了数据库的性能，能尽量少用就少用。

    9.函数

        函数类似于，自定义的方法，用于数据的计算等操作。好处，可以直接在数据库进行数据处理操作，坏处，增大了数据库的负荷。

        使用场景

            个人认为，在小型项目中可以使用，如果是中大型项目中主要是数据库瓶颈比较多，一些数据计算都可以在后端解决。

    10,特殊的字段

            比如创建时间，创建人，修改时间，修改人，数据状态，时间戳等。这些字段的添加，好处，可以对数据更好的管理，可以跟踪数据的操作，以及一些业务的场景，比如：数据删除，现在一般都是软删除，也就是实际上在数据库中并没有删除那条数据，只是把该条数据的状态修改，可以用于数据的恢复，时间戳，可以做数据的一致性，保证在操作数据之前数据是最新的等等。坏处，添加了几个字段，增大了数据空间。

        使用场景

            个人认为，在一些表中涉及到增删改查频繁操作的都可以添加，在一些数据变动频率非常小，或者中间表就可以不用添加。

    11.软删除

            就是在删除操作时候并不会删除该条数据，只是把该条数据的状态改为删除的状态，好处，用于误删除的恢复方便，以及一些特殊的业务，比如历史删除文件，历史删除好友等等。

三，数据库高并发优化

在如今流量巨大的情况下如果对数据库优化？如果针对不同场景进行优化？

    1.负载均衡

        在流量巨大的情况下，可以使用负载均衡作为应对海量数据请求的情况，把数据库复制为多份，通过一些中间件作为数据库的转发。比如：Moebius

            Moebius：比如有一个数据库复制了3个一模一样，在不同的服务器上，通过Moebius中间件，客户端查询请求通过Moebius，它转发到不同的数据库上进行查询，这样就能减少数据库的请求次数，减少数据库的负荷。但是，在操作数据的时候，它会分别操作全部的数据库进行增删改查，所有操作数据库操作             成功，才会返回结果。

        使用场景：

            个人觉得，负载均衡还是有缺陷的，虽然解决了查询时候的数据库的负担，但是在操作数据上，就比较不理想，延迟很大。但是负载均衡部署相对简单，项目上修改不大。适用于，对要求实时性低，数据操作较少的项目中。

    2.读写分离

        读写分离大致意思是，把数据库分为写库，和读库，写库读库数据库结构数据一致，写库主要应对数据操作，读库应对与查询操作。不同数据库都有不同读写分离实现的方式

        sqlserver

            发布订阅

                sqlserver中发布订阅中，主库为写库，从库为读库，数据操作都在主库中操作，查询都在从库，主库和从库共享一个文件夹，主库会将数据操作的log推送到共享文件夹中，从库进行拉取来同步主库数据，发布订阅都又分为多种同步数据方式:

                    快照复制：就是把主库定时做一个快照，通过共享的文件，定时同步主库数据，一般适用于报表业务，对实时性要求低。

                    事务复制：当主库操作数据后，把log推送到共享文件中，然后就会订阅服务就会通知从库直接进行数据备份，优点：实时对数据操作进行同步，缺点，数据同步会有延迟，一般针对实时性要求低的业务，或者在针对实时性高的情况下直接使用主库查询也是能达到数据实时的。

                    合并复制：意思就是把快照复制和事务复制合并起来使用，一般使用场景较少，用事务复制就可以了。

        myslq

            主从复制

                mysql中读写分离和sqlserver中有点不一样，mysql主从复制主要通过binlog共能实现，具体分为3个线程，

                主线程：复制采集binlog数据于同步库IO线程的交互

                IO线程：IO线程将获取到的数据转换为relaylog文件进行存储。

                slave线程：slave线程将relaylog数据读取出来写入从库中

        使用场景

            sqlserver一般都使用读写分离事务复制的方式进行，优点，读写操作分离开来，达到数据库优化，支持不同服务器同步，缺点：数据延迟性，如果在延迟性要求比较高的话可以直接使用主库查询。

            mysql主从复制是用的比较多的方式，优点，读写操作分离开来，达到数据库优化。缺点，对不同服务器同步支持很差，数据延迟性。解决方法同上。

3,分库分表   

    应对超大数据量，进行的数据库优化

    3.1.分表

          分表是如今比较常见对数据库优化的手段之一，主要分为水平分表，垂直分表

            水平分表：

                把数据按照不同的拆分思路分别分在不同的表中，但是表结构都是一样的。数据拆思路主要有：

                    时间拆分：按照时间拆分，比如年月等，按照不同时间段把数据放到不同的表中。

                    地域拆分：按照不同地域进行拆分，比如，小明属于湖南的就给他分到湖南的表中，小红是湖北的就给她分到湖北中。

                    类别拆分：按照不同类别拆分，比如，中国人，美国人，俄国人，等按照不同类型进行拆分

                    缺点：跨表查询，合并查询，

                    解决思路：跨表查询只能使用联查进行，会牺牲一些效率，表合并，可以拆用全文索引手段。

            垂直分表：

                把一张表拆成多张表，在数据库中查询，一张表在磁盘中有一个数据文件，文件越大，查询就会越慢，把表有效的拆分出来可以大大减少数据文件的空间。

                优点：减少了单个sql文件的占用空间，数据查询变快。

                缺点：数据操作变得麻烦，以前只要操作一张表现在变成操作多张表。

                解决思路：垂直拆分只能妥协一下，增删改查只能在原本上更麻烦一些，但是还是能接受的。

   3.2表分区:

         在创建表时候给定分表规则，这里分表是表示数据文件分开，表分区会根据给定的规则进行sql文件的拆分。这样就达到了sql文件不会越来越大，查询的效率也会增大，但是在数据量大的情况下，分出太多的sql文件，在进行查询也会越来越慢。

         解决实录：一般不会使用表分区，可以使用分表来代替

    3.3. 分库

            分库也是如今优化数据库的主要手段之一，主要分为水平分库，垂直分库

                水平分库：

                    把数据库复制多个数据库一样结构的数据库，通过不同拆分思路把数据分别放在不同的数据库中，主要拆分思路：

                        地域拆分：按照地域拆分，把不同地域的数据分别放在不同的数据库中，比如：港口贸易：上海贸易比较多，就把上海贸易分到专门存储上海贸易信息的数据库中，广州贸易也多相同也把广州贸易信息存储到广州的数据库中。

                        时间拆分：按照不同时间段，把不同时间段的数据分别存储到不同数据库中，比如：20年的数据存储到20年的数据中，21年的数据存储到21年的数据库中。

                        缺点：报表。

                        解决思路：做一个报表库专门存储所有库的信息。

                垂直分库：

                    垂直分库就是按照业务把数据库进行拆分，比如,电商项目，可以把订单拆成一个数据库，用户拆成一个数据库，物流拆成一个数据库等等。

                    缺点：如何使用事务

                    解决思路：使用分布式事务解决。

---

总结：

数据库优化方式千千万万，做好数据库设计，合理创建数据表结构，使用好数据结构，针对不同的业务，采取不同的优化手段，一切都要避免对数据库操作的依赖，减少数据库的负担，扬长避短就能做出一个稳定安全高效的项目。