sql 索引优化记录

项目代码是在外部购买过来的，因此很多细节方面有缺失，就比如sql方面其实速度特别的慢，之前的公司也没完善的sql这块的措施，所以在忙完手上的事情之后开始了对于老项目sql 这块的优化

首先是针对慢sql的定位，在定位上，我们用的是阿里云的sql执行的监控，但是这上面的数据他没有显示出相对应的sqlId，因此对于sql 的定位上就很困难，基于此，就结合着网上的文章，写了个基于mybaits的插件来记录慢sql的日志记录

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

import java.text.DateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Properties;
import java.util.regex.Matcher;
@Slf4j
@Intercepts(value = {
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class SqlExecuteTimeCountInterceptor implements Interceptor {

    private final static int EXECUTE_TIME = 0*1000;
    private static Logger logger = LoggerFactory.getLogger(SqlExecuteTimeCountInterceptor.class);

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        long startTime = System.currentTimeMillis();
        Object proceed = invocation.proceed();
        try {
            long endTime = System.currentTimeMillis();
            long timeCount = endTime - startTime;
            if (timeCount >= EXECUTE_TIME) {
                // 获取xml中的一个select/update/insert/delete节点，主要描述的是一条SQL语句
                MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
                Object parameter = null;
                // 获取参数，if语句成立，表示sql语句有参数，参数格式是map形式
                if (invocation.getArgs().length > 1) {
                    parameter = invocation.getArgs()[1];
                }
           /* if (SqlCommandType.SELECT.equals(mappedStatement.getSqlCommandType())) {
            }*/
                // 获取到节点的id,即sql语句的id
                String sqlId = mappedStatement.getId();
                // BoundSql就是封装myBatis最终产生的sql类
                BoundSql boundSql = mappedStatement.getBoundSql(parameter);
                // 获取节点的配置
                Configuration configuration = mappedStatement.getConfiguration();
                // 获取到最终的sql语句
                String sql = getSql(configuration, boundSql, sqlId);

                logger.info("执行慢SQL执行耗时[ {} ms]：sql = [ {} ]", timeCount,sql );

            }

        } catch (Exception e) {
            logger.error("记录慢查询sql出现异常", e);
        }
        // 执行完上面的任务后，不改变原有的sql执行过程
        return proceed;
    }

    /**
     * 封装了一下sql语句，使得结果返回完整xml路径下的sql语句节点id + sql语句
     *
     * @param configuration configuration
     * @param boundSql      boundSql
     * @param sqlId         sqlId
     * @return java.lang.String
     */
    public static String getSql(Configuration configuration, BoundSql boundSql, String sqlId) {
        String sql = showSql(configuration, boundSql);
        StringBuilder str = new StringBuilder(100);
        str.append(sqlId);
        str.append(":");
        str.append(sql);
        return str.toString();
    }

    /**
     * 如果参数是String，则添加单引号， 如果是日期，则转换为时间格式器并加单引号；
     * 对参数是null和不是null的情况作了处理
     *
     * @param obj obj
     * @return java.lang.String
     */
    private static String getParameterValue(Object obj) {
        String value = null;
        if (obj instanceof String) {
            value = "'" + obj.toString() + "'";
        } else if (obj instanceof Date) {
            DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
            value = "'" + formatter.format(new Date()) + "'";
        } else {
            if (obj != null) {
                value = obj.toString();
            } else {
                value = "";
            }

        }
        return value;
    }

    /**
     * 进行？的替换
     *
     * @param configuration
     * @param boundSql
     * @return
     */
    public static String showSql(Configuration configuration, BoundSql boundSql) {
        // 获取参数
        Object parameterObject = boundSql.getParameterObject(); 
        List<ParameterMapping> parameterMappings = boundSql
                .getParameterMappings();
        // sql语句中多个空格都用一个空格代替
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        if (!CollectionUtils.isEmpty(parameterMappings) && parameterObject != null) {
            // 获取类型处理器注册器，类型处理器的功能是进行java类型和数据库类型的转换<br>// 如果根据parameterObject.getClass(）可以找到对应的类型，则替换
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); 
            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(parameterObject)));

            } else {
                // MetaObject主要是封装了originalObject对象，提供了get和set的方法用于获取和设置originalObject的属性值,主要支持对JavaBean、Collection、Map三种类型对象的操作
                MetaObject metaObject = configuration.newMetaObject(parameterObject);
                for (ParameterMapping parameterMapping : parameterMappings) {
                    String propertyName = parameterMapping.getProperty();
                    if (metaObject.hasGetter(propertyName)) {
                        Object obj = metaObject.getValue(propertyName);
                        sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        // 该分支是动态sql
                        Object obj = boundSql.getAdditionalParameter(propertyName); 
                        sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
                    } else {
                        //打印出缺失，提醒该参数缺失并防止错位
                        sql = sql.replaceFirst("\\?", "缺失");
                    }
                }
            }
        }
        return sql;
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {

    }
}

一、业务代码上的优化

1.在后台管理上，我们有个功能是用手机号来查询用户下面的订单信息的，而且该功能是支持模糊查询的。

​ 该功能是通过like 来实现的，但是在索引上，使用like 的话，一旦like 在开头就会导致索引失效，这个在和产品商量沟通了之后 该功能改造成了：

通过在输入手机号的时候就调用接口，就通过用户输入的数字返回所有满足这些数据的手机号，然后让用户输入完整的手机号，使得该模糊查询成为了 等值查询

2 在业务上还会有很多地方会用到in的场景，因为涉及到接口复用的情况，那么对于一些超管账号，因为能查询到全部，这个时候如果是用in的话，那么效率特别特别低。所以这个时候可以舍弃in的条件

二.sql的优化

在针对sql 的优化上，就是使用explain 在加上 原sql 通过

我的话一般性就看3个指标

key /rows 和 type 访问类型

基本上遇到的问题都在key 上面，

1.遇到的关于key过多导致mysql 选择的key不合理

因此mysql 在内部自动选择key上就会出现问题，很可能选择了相对耗时的key

这种情况我一般都是用force index 来解决问题

这种问题的排查上 我一般是通过 show INDEX FROM t1来查看里面包含了哪些的key，然后该sql会返回一个字段

cardinality字段mysql的索引选择上会根据 show index 中的cardinality字段，这个字段的含义就是 数值越大代表着重复的越多，所以在索引上这个数值越小越好

2，针对条件的使用函数上

这个问题真的特别的难发现，当时的场景是我们有个userId 是 字符串类型的，然后我们在进行sql 查询的

使用的是 select * form t1 where userId = 1; 但是这条sql 确实一直没有走索引，这个就很迷惑，后续就是在一一进行对比，在返回的查看表结构上发现了 数据类型对不上，然后通过修改 where userId = '1' 发现就走索引了，

后续在查看资料的时候发现 因为如果是字段类型 然后查询使用 数字，那么会通过函数的方式把数字转字符串，所以索引失效 mysql内部的执行逻辑是 select * from user where CAST(phone AS signed int) = 1，是如果是数字类型 然后用字符串查询，那么mysql 会自动转化走索引

mysql内部的执行逻辑是 select * from user where id = CAST(“1” AS signed int)

可以优化的点：

1.limit 优化

因为项目存在的时间也很久了，订单表这些的就存在着特别多的数据了，其实这种情况下就就可以考虑limit 的分页优化了，因为MySQL会先扫描满足条件的1000010行，扔掉前面的1000000行，返回后面的10行。所以offset越大的时候，扫描的行就越多，效率也就越慢了。
如果主键id有序，可以用子查询优化
举例 select * from tb_test
where val = 4 and id >= (select id from tb_test where val = 4 limit 100000, 1)
limit 5;

但是一方面也确实是比较懒散，而且这样的需求是在后管端，公司对于后管端接口的响应速度其实还是满容忍的，改一下的话也比较麻烦，所以这一块暂时没去优化

2.sql 查询优化

这个是因为在一些简单的sql上我们使用的是mybatis-plus自带的查询，但是因为一些表的字段过多并且前端所需的字段并没有那么多，那么其实就导致了每次的查询上会返回不需要的字段，这种在大数据的查询上其实也蛮费力的，当然也是因为没人去催，所以也暂时搁置了

理论上优化的点

这种其实就纯属于理论这块的了，这块就纯纯的贴出网上的教程了:

1.小表驱动大表优化 
2.争取索引覆盖
3.避免使用子查询：
	SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name = 'chackca');
	这条sql 是先查外表再匹配内表，而不是先查询内表再匹配外表，这就导致外表数据量很大查询特别慢，当然在5.6版本之后针对select 会自动转化为 join 查询，但是只有select 才有效
4.用in 来替换or 
	之前的时候因为会针对不同的状态值作不同的判断条件查询状态，这就导致了sql 中会出现
	SELECT * FROM t WHERE id = 10 OR id = 20 OR id = 30;
	后来索性直接 写了针对不同的条件写了多条sql 这样就可以用in 来代替 or，当然加入in 里面的数据是连续的情况下。比如 in （1,2,3） 这个时候就可以用between 

5.最佳左前缀法则
6.不在索引列上做任何操作 如 where id + 1 = 10，可以转换成 where id = 10 -1，这样就可以走索引
7.mysql在使用负向查询条件(!=、<>、not in、not exists、not like)的时候无法使用索引会导致全表扫描。