数据库优化-一章

总：

1.从数据库表设计层面优化数据库，最好遵循数据库三范式，特殊情况可以反范式设计

2.表建好后可以考虑用 逻辑优化 还是 物理优化

3.除了对于sql进行优化还可以用redis请外援来优化查询速度

分：

1.1 数据库创建字段时的注意事项

        创建表字段的时候，尽量使用数字类型的单位创建字段，查询速度会快很多。如果是字符串单位，字符串的长度能短则短，知道某字段的长度是固定的话，使用char，长度未知使用varChar2，最好给字符串长度定义一个范围

1.2 遵循建表三范式

       数据库的基本范式是一级包含一级的，比如第一范式有的第二范式一定有，第二范式有的第三范式一定有

        第一范式：原子性、字段不可再分割。基本上所有的建表都会遵循第一范式，这也很好理解，就是一个表中的每个字段应当是最小单位，不会再往下细分，比如一个字段x，不会被分成x1和x2来共同解释x字段是干嘛的

        第二范式：表中的非主属性和主属性应当有直接的依赖关系。

        举个不符合第二范式的例子：有一个篮球比赛的表，表中有两个主属性：球员编号、比赛编号。非主属性有：球员年龄、身高、姓名（这都是球员相关信息）还有比赛场地、时间（这些都是比赛场地信息）

        如果不符合第二范式的表，在使用的过程当中会遇到一些问题：

        1.数据冗余：当一个球员要参加多场比赛的时候，比赛那么球员的信息会重复，如果一场比赛有多个球员参加，那么比赛的信息会重复。

        2.新增数据困难：如果已经确定了有哪些人要参加比赛，但是比赛的时间场地还没有确定下来，将无法插入数据。

        3.删除数据困难：如果说要删除某个球员的商场信息，但同时这个球员对应的比赛信息是唯一的，却没有对比赛信息做单独的保存，删除这个球员信息的时候会将比赛场地等信息也删除。

        4.修改数据困难： 如果说比赛场地或者球员信息在确认后突然更改，那么将对所有的历史数据进行修改

        规范的第二范式应该是创建三张表，球员信息表，比赛信息表，他们俩中间的关联表。

        如果说第一范式是说一个字段只能描述一个点，那么第二范式则是说一张表只能描述一件事情

        第三范式：在满足第二范式的同时，任何非主属性都不应该有传递依赖关系。

        举例说明：一张表有球员信息、球队名称、球队教练信息。这是不满足第三范式的一张表。

原因是球队教练是和球队名称挂钩做直接关联的，那么球队教练其实和球员信息属于传递依赖关系，这样会导致如果后头教练换了，要对他曾经存在的所有球员信息都做一边历史处理。

        正确做法应该是键三张表。个人理解是对于第二范式的更加严格控制

1.3 特殊情况反范式创建表

        有些时候会因为开发和需求需要，做出一些反范式的建表方式来方便开发和查询时间。比如说在项目中需求需要做一个界面的查询sql，会做多表关联查询，但是这样的查询时间太长了，可以创建一个落地表，但是在落地表中会造成一个表描述了多件事情，可以用存过对这张落地表进行维护，但是这样的查询效率会大大提升。

2.1 逻辑优化

        指的是对sql写法进行优化，从而达到加快查询sql的执行效率

举例1：在做子查询的时候，有in和exit两种子查询方式，如果子查询的表内容较少可以用in，反之用exit效率会更快。

原因如下：in子查询的执行顺序是先执行子查询，然后通过子查询结果集与主查寻得筛选字段做笛卡尔积匹配，匹配到则放入最终结果集中。

                exit子查询的执行顺序是先执行主查询，再执行子查询，根据主查询的结果集做loop循环子查询匹配。

        所以在实际应用开发过程中，会根据实际情况来判断用什么子查询方式会更快，如果子表数据量少的时候建议用in子查询、如果子查询数据量大的时候用exit子查询。

举例2：如果查询目标表中创建了索引，假设表中有个字段ID，这个字段有创建索引，对ID进行模糊查询的时候，可以用substr函数去分割字符串然后做模糊查询，但是对索引字段做函数计算或处理都会使索引失效，所以可以用等价替换的方式，使用ID like 'XXX%' 的方式查询，使索引继续生效。

2.2 物理优化

        除了对于sql进行优化，还可以通过创建索引的方式对表字段进行优化，如果表中数据量特别大，并且经常使用某些字段作为查询条件的时候，可以对这些字段创建索引来减少查询时间，但是索引有些基本的注意事项：

        1.索引应该创建在经常作为查询条件的字段上，那些不会用作查询条件的字段不建议创建索引，因为创建索引也需要占资源

        2.如果字段在表中的重复率太高不用创建索引，比如性别字段，创建了也作用不大。

        3.如果使用复合索引，最好考虑一下创建索引的顺序，例如我们对X、Y、Z创建联合索引，那么这时候顺序是xyz、还是yzx，这可能会对查询速度有影响。

        4.如果索引字段在where条件中做了表达式计算，会使索引失效。

        5.对于需要频繁修改的表创建索引也需要谨慎一点，应为对内容进行修改还需要对索引进行维护，这需要一定的维护成本，比如说如果要往表中批量新增或修改大量的数据，建议先将索引删掉，修改完表数据然后再创建索引。

3.1 如果数据库还不满足还可以请外援Redis

        如果经常查某个表，而表数据很多，而且用户的查询频率很高，我们我可以将数据放到redis中供用户查询，这样会大量提升数据的响应时间，从而达到优化的效果

MySQL主从复制

        概述：底层基于mysql自带的二进制文件，此文件会记录主库的所有ddl和dml语句，从库会复制这个日志文件然后同步数据，主从复制是mysql自带的功能，不需要第三方工具帮忙。

        

实际优化

        信息量大的sql，在查询条件当中不可使用函数处理做比较，一个是会造成全表扫描，在一个每条数据都会经历函数处理，导致速度很慢。

        查询过程：先f5查看sql执行效率，分辨耗时长的地方在哪块代码，然后一个个删除条件判断具体哪个条件导致sql慢，针对性解决。

        例子：处理一段大量数据的查询sql，简单的单表查询，由于查询条件中，根据时间做了函数处理，导致每一条数据查询的时候，都根据条件走函数筛选，速度很慢，但其实筛选出来的结果都一样，因为都是根据sysdate来筛选的，于是尝试将函数单独写一次查询，然后直接将函数出来的结果当作条件，速度提升十倍。