Mysql数据查询的一次优化

一、产生背景

gateway系统有一张接口的请求入参和请求响应信息保存表-gw_risult，目前数据量已经达到一千多万，
现在要求对数据库敏感字段进行加密，那张表有身份号id_no,因此需要对此字段就行加密，对现有数据以及未来数据。

二、解决方案

分三个步骤来解决：

1. 数据库双写，给gw_risult表新增加密字段id_no_encrpty，开发好加解密插件，修改插入语句，利用插件对敏感字段加密插入。

（简洁方法是修改数据库表实体类Bean中idNo的set方法，同时set idNoEncrpty）

2. 刷新历史数据，利用jdbcTemplate中setDataSource方法动态修改数据源（多系统多个表情况使用），动态拼写查询和更新sql语句，
   比如：

  SELECT id,id_noFROM gw_risult where (id_no is not null && id_no!= '' && (id_no_encrpty is null || id_no_encrpty= '')) 
  limit 50;

  目前表已经有了id_no字段索引，又新增了id_no_encrpty 字段索引，发现在查询的时候走了id_no_encrpty 索引，用执行计划命令看扫描行数有三百多万行，
  查询非常慢。慢的原因是确实有大量的id_no_encrpty为空的情况，因为id_no为空id_no_encrpty必然为空，导致扫描了很多无效数据。

    explain SELECT id,id_no FROM gw_risult where id_no is not null 

  通过上面执行计划看如果走id_no 的索引其实只扫描了一百多万行数据，并且这个查询走的还是覆盖索引，速度比较快的，但
  是没搞明白MySQL执行器为什么会选择id_no_encrpty字段索引。
  本来想在建一个id_no 和 id_no_encrpty的联合索引，删除id_no_encrpty，在历史数据刷新完之后再重新建立，由于数据量很大，建索引还是会很慢，
  这个方式很麻烦。突然想到可以让查询sql语句强迫使用某个索引，这样就解决了问题。
 （以后还是要通过分区或者分表来解决这个数量，因为以后数据量越来越大）

     SELECT id,id_noFROM gw_risult force index (id_no ) where (id_no is not null && id_no!= ''
     && (id_no_encrpty is null || id_no_encrpty= ''))  limit 50;

3. 切换敏感字段查询，把原来的要加密的字段的查询修改成新增加密字段的查询，利用插件查询自动解密。代码切换完之后，再删除对应数据库字段。