数据库优化

数据库优化

• 优化方案：

1. 查找，定位较慢得查询，优化
2. 创建索引：创建合适的索引，就可以先在索引中查询，查询到以后直接找对应的记录。
3. 分表：当一张表的数据较多，或者一张表的某些字段的值比较多并且很少使用时，采用水平分表和垂直分表来优化。
4. 读写分离：当一台服务器不能满足需求时，使用读写分离得方式进行集群。
5. 缓存：使用redis来进行缓存

• 查找慢查询并定位
  在项目自验项目转测试之前，在启动mysql数据库时开启慢查询，并且把执行慢的语句写到日志中，再运行一定的时间后，查找日志。找到对应的sql。
  使用explain慢查询语句来详细分析语句的问题。
  需要知道详细步骤以及定位的结果

• 数据库表在设计时需要遵循范式
  表的范式：先满足1范式，再2范式，最后3范式
  1NF：表的列具有原子性，不能够再分割。
  2NF：表中的记录是唯一的——通过主键来实现
  3NF：表中没有冗余字段，如果表中的信息能够被推导出来，那么就不应该单独设计一个字段来存放——外键

反3NF: 没有冗余的数据库未必最好，有时为了提高效率，可以适当保留冗余。

• 选择合适的存储引擎
  MyISAM: 对事务要求不高，主要是添加和查询时适用。
  INNODB：事务要求高，保存的都是重要数据
  Memory：数据频繁变化，不需要入库且频繁查询和修改。
  区别：

1. MyISAM不支持事务而innoDB支持
2. MyISAM不用支持事务，不用考虑步骤，则查询，添加速度快。
3. MyISAM支持全文索引，innoDB不支持。
4. 锁机制：MyISAM支持表锁，innoDB支持行锁（为了事务处理）。
5. 外键：MyISAM不支持，innoDB支持。

• 索引：
  索引是帮助DBMS高效获取数据的数据结构
  分类：普通索引/唯一索引/主键索引/全文索引

1. 普通索引：允许重复值。
2. 唯一索引：不能重复，其他同普通索引。
3. 主键索引：随着主键的创建而创建，唯一且不能为null。
4. 全文索引：针对表中的文本域（char，varchar, text）进行索引需要使用sql语句中的match 和against语句才能生效
   根据个人的需求以及表中值的特性选择索引。

索引使用的小技巧：
索引缺点：

1. 占用磁盘空间
2. 对dml（插入，修改，删除）效率有影响。
   使用场景：
3. 肯定是where条件使用，即在查询时使用索引，不查询不用索引。
4. 字段内容不是唯一的几个值，不然怎么做索引。
5. 字段内容不是频繁变化，每次变化都要维护索引，效率很低。

具体技巧：

1. 创建多列索引（复合索引），如果不是使用复合索引的第一列，那么该索引不会生效。

alter table dept add index my_ind(dname, loc) --dname, 左边的列；loc，右边的列
explain select * from dept where dname = 'aaa' --会使用复合索引
explain select * from dept where loc = 'aaa' -- 不会使用复合索引 my_ind

2. like查询时，如果通配符在前：%aaa ，不会使用，如果通配符在后 aaa%, 会使用索引。
   如果一定要前面使用通配符，要用全文索引 sphinx

3. 如果存在or字符，且or的条件中存在没有使用索引的情况，那么即便其他条件可以使用索引，索引也不会生效。

4. 如果列类型是字符串，无论实际存储是否是字符串，都要使用 ‘’ 将其括起，否则不能使用索引。

5. 如果mysql认为全表扫描比使用索引快，则不使用索引。

• 分表：
  水平分表：按行
  垂直分表：按列
  根据经验：当mysql表数据达到百万级别时，查询效率就会降低，使用水平分表，能够大幅降低这种问题。
  水平分表：按照行分表
  垂直分表：如果一张表中某个字段的值很多（长文本，二进制等），而且只有在很少的情况下会查询，此时可以将字段多个单独放到一张表，然后通过外键进行关联。

水平分表策略：

1. 按时间分表：当数据有很强的时效性时，且用户很少查询一定时间之前的数据时使用。
2. 按区间范围分表：就是按数据数量分表。100W一张表，以此类推。
3. hash分表：通过原始目标的ID或者名称，通过一定的hash算法，计算出存储表的表名，然后访问相应的表。

• 数据库的读写分离：
  一台数据库支持的最大并发连接数是有限的，如果用户的并发访问太多，一台服务器不满足要求，就可以集群处理。mysql集群处理技术最常用的就是读写分离。

1. 主从同步：
   数据库最终会把数据持久化到磁盘，如果集群，必须确保每个数据库服务器的数据是一致的。能改变数据库数据的操作都住数据库写（增，删，改）。其他的数据从主数据库上同步数据。
2. 读写分离
   使用负载均衡来实现写的操作都往著数据库去，而读的操作往从服务器去。

• 缓存：
  在持久层（DAO）和数据库（db）之间添加一个缓存层，如果用户访问的数据已经缓存时，用户访问时直接从缓存获取，不用访问数据库，缓存放在内存中，访问速度很快。

作用：减少数据库压力，减少访问时间。
Java中常用的缓存：

1. Hibernate的二级缓存，该缓存不能完成分布式缓存。
2. 使用redis来作为中央缓存，在DAO和DB中间添加缓存层。用来统一存放集群的缓存。

• 数据库语句优化：
  DDL优化：

1. 禁用索引提高导入数据性能：

 -- 去除key
 alter table test3 DISABLE keys; 
 --批量插入
 insert into test3 select * from test;
 -- 恢复key
 alter table test3 ENABLE keys;

2. 关闭唯一校验：

set unique_checks = 0 --关闭
set unique_checks = 1 -- 开启

3. 修改事务提交方式（导入）

set autocommit=0 --关闭
--批量插入
set autocommit=1 --开启

DML优化
变多次提交为一次

• 批量插入百万级数据