数据库优化

对于一个数据量较大的系统，进行数据库优化是非常有必要的，这对于系统性能提升有很大的帮助。在这以MySQL数据库为例。

索引

对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。比如现在经常根据时间获取数据，那么可以在时间上添加索引，使得添加增加的时间，而获取查询时间上的减少。

数据库查询优化

比如很多查询，并不是单一的查询，可能是N个查询的混合，有很多种方式进行操作：

• 先通过第一个查询，得到一系列的数据，比如所有的用户信息（用户信息较多），然后在一个一个用户查询得到相应的消费情况，最后返回到前台。在这里将多次查询数据库，每一次查询将会有所耗时，而且查询时间会根据用户个数有所增加；
• 通过数据库连表查询，将多张表进行关联，一次性返回用户信息及消费情况，这样单次可能时间长了，但整体上速度上去了。

无疑，第二种查询速度较快。

数据库分区

分区允许根据指定的规则，跨文件系统分配单个表的多个部分。表的不同部分在不同的位置被存储为单独的表。MySQL支持RANGE，LIST，HASH，KEY分区类型，其中以RANGE最为常用：

• Range（范围）–这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区。
• Hash（哈希）–这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
• Key（键值）-上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
• List（预定义列表）–这种模式允许系统通过预定义的列表的值来对数据进行分割。
• Composite（复合模式） –以上模式的组合使用

分区限制

• 只能对数据表的整型列进行分区，或者数据列可以通过分区函数转化成整型列
• 最大分区数目不能超过1024
• 如果含有唯一索引或者主键，则分区列必须包含在所有的唯一索引或者主键在内
• 不支持外键
• 不支持全文索引（fulltext）

所以，按日期进行分区很非常适合，因为很多日期函数可以用。但是对于字符串来说合适的分区函数不太多 。

什么时候使用

• 海量数据表
• 历史表快速的查询，可以采用ARCHIVE+PARTITION的方式。
• 数据表索引大于服务器有效内存
• 对于大表，特别是索引远远大于服务器有效内存时，可以不用索引，此时分区效率会更有效

为了做实验，本人用了相同的数据，数据大概160万条数据，一个做了分区，分区为每7天一个分区，一个没有做了分区，。使用相同的查询，查询如下：

SELECT * from drain_level where record_date_time>'2016-06-07' and record_date_time<'2016-06-08';

未分区查询速度为：0.110s,查出8313条数据；
分区后查询速度为：0.047s，查出8313条数据；
分区后，查询速度为未分区的一半左右。以下是每七天自动生成分区的sql脚本。

drop EVENT IF EXISTS e_Set_Partition;drop procedure if exists Set_Partitions;drop procedure if exists Set_Partition;//输入：需分区的表名，分区字段名，开始时间create procedure Set_Partition(IN table_name varchar(20),IN col_name VARCHAR(20),IN start_time varchar(20)) begin    declare exit handler for sqlexception rollback;    start TRANSACTION;    set @selectstr=CONCAT("select REPLACE(partition_name,'p','') into @P_Name from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA='smartpipe_ims' and table_name='",table_name,"' order by partition_ordinal_position DESC limit 1");     PREPARE selectstatement FROM @selectstr;     EXECUTE selectstatement;      DEALLOCATE PREPARE selectstatement;    IF(@P_Name is NULL) THEN      set @curr_date=date(start_time);      set @sqlstr=concat('ALTER TABLE ',table_name,' PARTITION BY RANGE (to_days(',col_name,'))( PARTITION p',DATE_FORMAT(@curr_date,'%Y%m%d'),' VALUES LESS THAN (',TO_DAYS(date(DATE_ADD(@curr_date, INTERVAL 1 DAY))+0),'))');      PREPARE statement FROM @sqlstr;      EXECUTE statement;      DEALLOCATE PREPARE statement;      COMMIT ;    ELSE        WHILE(TO_DAYS(@P_Name)-TO_DAYS(CURDATE())<10) DO           set @Max_date=date(DATE_ADD(@P_Name, INTERVAL 7 DAY))+0;           set @P_Name=@Max_date;            SET @sqlstr=concat('ALTER TABLE ',table_name,' ADD PARTITION (PARTITION p',@P_Name,' VALUES LESS THAN (',TO_DAYS(date(DATE_ADD(@P_Name, INTERVAL 1 DAY))+0),'))');            PREPARE statement FROM @sqlstr;            EXECUTE statement;            DEALLOCATE PREPARE statement;        END WHILE;      COMMIT ;    END IF;end;//有四张表需要分区create procedure Set_Partitions()begin   call Set_Partition("monitor_status","record_date_time","2016-03-01");   call Set_Partition("drain_rain","record_date_time","2016-03-01");   call Set_Partition("drain_level","record_date_time","2016-03-01");   call Set_Partition("drain_flow","record_date_time","2016-03-01");end;//一开始创建最近的分区call Set_Partitions();call Set_Partitions();//定时任务事件，每7天执行一次CREATE EVENT e_Set_Partition  ON SCHEDULE      EVERY 7 day STARTS NOW()       DO          call Set_Partitions();

使用缓存

在系统运行过程，某一些东西命中率一般较高，比如用户信息等等。这是我们可以使用缓存技术，将比较常用的数据缓存下来。在本次开发中，主要用的是ehcache、Redis。

• ehcache主要用于Spring Data JPA对数据库数据进行缓存，配置也简单，在项目src下加入ehcache.xml文件。文件内容如下：

  <?xml version="1.0" encoding="UTF-8"?><ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:noNamespaceSchemaLocation="ehcache.xsd" updateCheck="false"><diskStore path="java.io.tmpdir/hibernate/electric-charge" /><!-- DefaultCache setting. --><defaultCache     maxEntriesLocalHeap="10000"     eternal="false"    timeToIdleSeconds="300"     timeToLiveSeconds="600"     overflowToDisk="true"    maxEntriesLocalDisk="100000"     memoryStoreEvictionPolicy="LFU"/><cache     name="com.phy.smartpipe.ims"    maxEntriesLocalHeap="1000"     eternal="true"     overflowToDisk="true"    maxEntriesLocalDisk="10000" /></ehcache>

  在Hibernate Jpa配置数据库连接entityManagerFactory中，配置一下：

<prop key="hibernate.cache.use_query_cache" >true</prop>                <prop key="hibernate.cache.provider_configuration" >ehcache.xml</prop>                <prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</prop>                  <prop key="javax.persistence.sharedCache.mode">ENABLE_SELECTIVE</prop>                  <prop key="hibernate.generate_statistics">true</prop>  

对于需要存储的数据库表，添加@Cacheable注解，及在需要缓存的查询上添加一下：

@QueryHints({ @QueryHint(name = "org.hibernate.cacheable", value ="true") })

*redis主要用于保存监测点的实时数据（最新一条），在查找最新的数据时，先从Redis中获取，若没获取到再从数据库（一分钟一条，直接获取一般会好慢）中获取。而对redis的操作主要用Spring Data Redis。具体使用方法可以查看这篇博客：http://blog.csdn.net/luzhihen/article/details/52072025

以上是目前开发中想到的数据库优化措施，根据现在的操作，基本上可以满足每一个接口在3s内响应（目前数据在160w）。