数据库的优化

1.1、创建并使用正确的索引

索引会大大增加表记录的DML(INSERT,UPDATE,DELETE)开销，正确的索引可以让性能提升100，1000倍以上，不合理的索引也可能会让性能下降100倍，因此在一个表中创建什么样的索引需要平衡各种业务需求。

1、表的主键、外键必须有索引； 
2、数据量超过300的表应该有索引； 
3、经常与其他表进行连接的表，在连接字段上应该建立索引； 
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引； 
5、索引应该建在选择性高的字段上； 
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

插入数据对索引的影响

给people表创建了索引后，表中的所有数据将按照字母表顺序进行分块处理，假如分成两个块，当往数据中插入一条name为Bill的数据时，数据表要做更多的操作，例如先要在Charles前插入Bill，然后数据块1中的数据往数据块2中移，数据块2又满了，得新增数据块3。因此，由于插入数据的不可知性，对于插入操作来说，索引造成的操作开销是普通表的l两倍以上。

修改数据对索引的影响

将原来name为James的数据修改为Winne时，修改的数据会反应到数据表中，但是索引项不会被删除，原索引项只会被标记为不可用，新的索引重新添加到相应的数据块中。这虽然减小了数据库操作的开销，但是带来了空间上的浪费。

删除数据对索引的影响

删除只会把相应的索引项打上删除标记，表明该索引项不可用，但是依然形成了对存储空间的浪费。

因此小数据量的表不宜使用索引，频繁使用插入、修改、删除等操作的数据表也不宜使用索引

 

1.2、只通过索引访问数据

有些时候，我们只是访问表中的几个字段，并且字段内容较少，我们可以为这几个字段单独建立一个组合索引，这样就可以直接只通过访问索引就能得到数据，一般索引占用的磁盘空间比表小很多，所以这种方式可以大大减少磁盘IO开销。

如：select id,name from company where type='2';

如果这个SQL经常使用，我们可以在type,id,name上创建组合索引

create index my_comb_index on company(type,id,name);

有了这个组合索引后，SQL就可以直接通过my_comb_index索引返回数据，不需要访问company表。

2.2、只返回需要的字段

通过去除不必要的返回字段可以提高性能，例：

调整前：select * from product where company_id=?;

调整后：select id,name from product where company_id=?;

 

优点：

1、减少数据在网络上传输开销

2、减少服务器数据处理开销

3、减少客户端内存占用

3、减少交互次数

3.1、batch DML

数据库访问框架一般都提供了批量提交的接口，jdbc支持batch的提交处理方法，当你一次性要往一个表中插入1000万条数据时，如果采用普通的executeUpdate处理，那么和服务器交互次数为1000万次，按每秒钟可以向数据库服务器提交10000次估算，要完成所有工作需要1000秒。如果采用批量提交模式，1000条提交一次，那么和服务器交互次数为1万次，交互次数大大减少。采用batch操作一般不会减少很多数据库服务器的物理IO，但是会大大减少客户端与服务端的交互次数，从而减少了多次发起的网络延时开销，同时也会降低数据库的CPU开销。

4.3、减少比较操作

我们SQL的业务逻辑经常会包含一些比较操作，如a=b，a<b之类的操作，对于这些比较操作数据库都体现得很好，但是如果有以下操作，我们需要保持警惕：

Like模糊查询，如下所示：

a like ‘%abc%’

 

Like模糊查询对于数据库来说不是很擅长，特别是你需要模糊检查的记录有上万条以上时，性能比较糟糕，这种情况一般可以采用专用Search或者采用全文索引方案来提高性能。