[大数据]MySQL 经验总结

mysql

1.创建变量

如果想获取查询语句中的一个字段值可以用select给变量赋值,如下：
select @num=字段名 from 表名 where ……
mysql中变量不用事前申明，在用的时候直接用”@变量名”使用就可以了。
第一种用法：set @num=1; 或set @num:=1; //这里要使用变量来保存数据，直接使用@num变量
第二种用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……
注意上面两种赋值符号，使用set时可以用”=”或”:=”，但是使用select时必须用”:=赋值”

set @var=0;
select *,@var:=date_task_id  from date_details_task where task_num=1 and task_type=5 and finish_num !=1 and DATE_FORMAT(create_time,'%Y-%m-%d') in ('2020-11-21')

2.创建存储过程

2.1：WHILE循环

-- 如果有存储过程，删除该存储过程
drop PROCEDURE  if EXISTS test_two;
DELIMITER //
-- 创建存储过程
CREATE PROCEDURE test_two ()
BEGIN
DECLARE i INT DEFAULT 10;-- DECLARE 声明只能在begin...end 和存储过程语句中，否则创建变量直接用set
	WHILE i < 100 DO
		SET i = i + 1;
		select i;
	END WHILE;
END;
//
-- 调用存储过程
CALL test_two ();
DELIMITER ;


创建存储过程 批量插入数据库

-- 如果有存储过程，删除该存储过程
drop PROCEDURE  if EXISTS test_two2;
DELIMITER //
-- 创建存储过程
CREATE PROCEDURE test_two2 ()
BEGIN
DECLARE i INT DEFAULT 1;-- DECLARE 声明只能在begin...end 和存储过程语句中，否则创建变量直接用set
	WHILE i <=90 DO
		INSERT INTO `yushu_jdb`.`user_vip_module_power` ( `vip_level`, `module`, `num_limit`, `create_time`, `update_time`) VALUES ( 1, 1, 1, NOW(), NOW());
    set i=i+1;
	END WHILE;
END ;
//


-- 调用存储过程
CALL test_two2();
DELIMITER ;

TRUNCATE table user_vip_module_power

2.2：repeat循环

-- 第三种 repeat 循环:直到条件满足退出循环
  /*repeat 循环语法
  repeat
      循环体
  until 条件 end repeat;
  */
  drop procedure if exists sum55;#删除存储过程
  DELIMITER //
  create procedure sum55(a int)
  begin
       declare sum int default 0;
      declare i int default 1;
       repeat -- 循环开始
           set sum=sum+i;
           set i=i+1;
       until i>a end repeat; -- 循环结束
       select sum; -- 输出结果
  end;
  //
call sum55(100);-- 执行存储过程
DELIMITER ;

2.3：loop循环

-- 第二种 loop 循环
  /*loop 循环语法：
  loop_name:loop
          if 条件 THEN -- 满足条件时离开循环
               leave loop_name;  -- 和 break 差不多都是结束循环
       end if；
  end loop;
  */
-- 删除存储过程
drop procedure if exists  sums;
DELIMITER //
create procedure sums(a int)
begin
        declare sum int default 0;
        declare i int default 1;
        loop_name:loop -- 循环开始
            if i>a then 
                leave loop_name;  -- 判断条件成立则结束循环  好比java中的 break
            end if;
            set sum=sum+i;
            set i=i+1;
        end loop;  -- 循环结束
        select sum; -- 输出结果
end;
//
 -- 执行存储过程
call sums(100);
DELIMITER ;

delimiter //  # 定义//为一句sql的结束标志，取消;的所代表的意义
drop procedure if exists test;  # 如果存在名字为test的procedure则删除
create procedure test()  # 创建（创建函数使用的关键字为function 函数名()）
begin
    declare old_pro varchar(30);  # 声明变量
    declare temp_id int;
    declare flag int default 0;
    # 这是重点，定义一个游标来记录sql查询的结果(此处的知识点还有SQL的模糊查询，见补充)
    declare s_list cursor for select id, province from temp_table where like "%省"; 
    # 为下面while循环建立一个退出标志，当游标遍历完后将flag的值设置为1
    declare continue handler for not found set flag=1;
    open s_list;  # 打开游标
    # 将游标中的值赋给定义好的变量，实现for循环的要点
        fetch s_list into temp_id, old_pro;
        while flag <> 1 do
            # sql提供了字符串的切分，有left、right、substring、substring_index
            # 在T-SQL中，局部变量必须以@作为前缀，声明方式set，select还有点差别
            set @temp_s = substring_index(old_pro, "省", 1);
            # 根据id的唯一性，利用当前查询到的记录中的字段值来实现更新
            update temp_table set province=@temp_s where id=temp_id;
            # 游标往后移（此处的游标是不是让你想起了C里面的指针）
            fetch s_list into temp_id, old_pro;
        end while;
        #select * from temp_table;
    close s_list;  # 关闭游标
end
//
delimiter ;  # 重新定义;为一句sql的结束标志，取消//的所代表的意义
call test(); # 调用

/**
SQL具有四种匹配模式
1、%：表示零个或多个字符；
2、_：表示任意单个字符；
3、[]：表示括号内所列字符中的任意一个（类似正则）；
4、[^]：取反（类似正则）；
注：若匹配中包含通配符则使用“[]”将特殊字符括起来即可（相当于转义）
*/

3.三种注释写法

#MySql--三种注释写法
#需要特别注意  --   这种注释后面要加一个空格

#  
/* */
-- 空格

4.case when then else end 写法

#CASE 子句可以用于任何可以使用表达式的地方。【when后面无逗号】
#如果省略了ELSE子句而且没有匹配的条件,结果为null,可能会查出错误的结果。使用 end case 替代 end 来终止case
#写法一：只适合单值匹配，不适合多值匹配 即不能使用or 如： when (0 0r 1) then .... 错误写法不
# 因为 0 or 1的结果是1 ，就会变成case type when 1 then .... 如果type选项中没有1 就会一直执行else
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
#写法二：适合单值匹配，也适合多值匹配
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

/*
	先写一个子查询，目的是为了找出date_task_id相同的4条数据中，其中两条数据的之和
	子查询使用到了两种case when then else end 写法，when不能与or连用，但可以与and 连用
	为了找出第一个数据与(其中两条数据的之和)的差值，在外面又嵌套了一个查询
*/
select *,(flowClick-addCartAndGoodsUp) as '差值' from (
  select date_task_id,
    sum(case when task_type=2 then task_num  else 0 end ) as flowClick,
	sum(case task_type when 3 then task_num when 4 then task_num else 0 end ) as addCartAndGoodsUp,
   sum(case when task_type=5 then task_num  else 0 end) as shopUp
 from date_details_task where  date_task_id=155128
) as r 


#由于分组不支持中文别名，只支持英文别名,如果需要显示中文别名，需要再外面多加一层
select a.num as '数量', a.trainType as '快车类型' from 
(select count(id) as num,
 (case 
	when type=0 or type=7 then '快车-普通点击' 
  when type=1 or type=10 then '快车-加入购物车'
	when type=2 or type=11 then '快车-加车并提单' 
	when type=3 or type=12 then '快车-关注商品' 
	when type=4 or type=13 then '快车-关注店铺' 
	when type=5 or type=8 then '快车-展现提升任务' 
	when type=6 or type=9  then '快车-点击提升任务'
else '' end) as trainType
from aaaa GROUP BY trainType 
) a
 ;

5.if相关方法

IF(expr1,expr2,expr3)
#如果if表达式1为真(表达式1不等于0和表达式1不等于null),返回表达式2，否则表达式3
select IF(2 >3 or 3>2,1,0) from dual;

IFNULL(expr1,expr2)
#如果表达式1不为null,返回表达式1否则返回表达式2,类似于三元运算符

NULLIF(expr1,expr2)
#如果表达式1=表达式2返回null,否则返回表达式1
#类似于CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
#返回类型与第一个参数一致

6.if语句

#以if开头，以 end if 结束
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF;
#semicolon 分号，delimiter分隔符

7.创建方法

CREATE
    [DEFINER = user]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
proc_parameter:
    [ IN | OUT | INOUT ] param_name type

CREATE
    [DEFINER = user]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    Valid SQL routine statement

7.1示例

#将默认的分隔符;改为//
DELIMITER //
#RETURNS关键字只能在方法中使用,这是硬性规定的，它声明了返回值类型,方法体必须有RETURN关键字
#如果返回类型不一致，会强制转换
CREATE FUNCTION SimpleCompare(n INT, m INT) RETURNS VARCHAR(20)

  BEGIN
    DECLARE s VARCHAR(20);

    IF n > m THEN SET s = '>';
    ELSEIF n = m THEN SET s = '=';
    ELSE SET s = '<';
    END IF;

    SET s = CONCAT(n, ' ', s, ' ', m);

    RETURN s;
  END 
  //
#分隔符改回来
DELIMITER ;
#像调用其它sql语句一样调用自己写方法
select SimpleCompare(1,2);

8.删除重复数据只保留最小id

#先查出要删除的数据
select *  from  qrcode WHERE
id NOT IN (SELECT min(id) FROM qrcode GROUP BY type HAVING count(id) >=1)

#将要查询的数据使用group_concat函数用串联起来
select GROUP_CONCAT(id) from qrcode WHERE 
id  IN (SELECT min(id) FROM qrcode GROUP BY type HAVING count(id) >=1) order by type

9.INSERT INTO ... SELECT标准的SQL语法

MySQL服务器不支持SELECT ... INTO TABLE 的Sybase SQL扩展。
但是支持SELECT ... INTO 变量

相反，MySQL Server支持 INSERT INTO ... SELECT标准的SQL语法，这基本上是相同的

INSERT INTO table1 (fld_id) 
SELECT table2.fld_order_id FROM table2 WHERE table2.fld_order_id > 100;
   
也可以使用   
CREATE TABLE ... SELECT
CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

10.SQL查询每个用户首次购买商品

SELECT g.name,g.product FROM goods g 
INNER JOIN (SELECT MIN(time) AS firstTime FROM  goods GROUP BY name ) tmp 
ON tmp.firstTime=g.time;

11.update

11.1update多表连接更新

#更新两表联查中满足条件的值
UPDATE items,#items表
       (SELECT id, retail / wholesale AS markup, quantity FROM items)
       AS discounted #查询部分作为第二张表
    SET items.retail = items.retail * 0.9
    WHERE discounted.markup >= 1.3
    AND discounted.quantity < 100
    AND items.id = discounted.id;
#示例二：一定要起别名
update (select id from task where end_date is null) as t,task t1 
set t1.end_date =STR_TO_DATE(DATE_FORMAT(t1.start_date,'%Y-%m-%d'),'%Y-%m-%d') 
where t.id=t1.id

11.2update … where exists

#使用update ... where exists ，exists子查询要多套一层
#否则会出现 You can't specify target table 'aaaa' for update in FROM clause
#错误的意思是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)
#当需要使用用到多张表的条件，但是查询的结果字段又只包含在某张表里的时候可以使用exists语句
update aaaa aa set aa.json_str ='{}'
where exists (select * from (select t.id from aaaa t where t.id =1) a where a.id=aa.id)

12.内连接inner join 和左连接left join

12.1 内连接

#在两个表的内部联接中，第一个表的每一行与第二个表的每一行合并（联接）。
#假设第一张表中有n1行，第二张表中有n2行，则INNER JOIN产生n1 × n2行的所有组合-称为笛卡尔乘积或叉积。
#不加where条件或on条件就会产生笛卡尔积
#inner join...on 会将两表中都有数据筛选出来，只存在在一张表中的数据会过滤掉
#以下等效
 SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;
 SELECT * FROM t1 JOIN t2 ON t1.id = t2.id; -- 默认的 JOIN 是 INNER JOIN
 SELECT * FROM t1 CROSS JOIN t2 ON t1.id = t2.id;  -- join/cross join/inner join 同义词
       
-- 你可以使用 using 关键词，如果关联的条件是相同名称：ON t1.id = t2.id 可以写成 USING (id);
SELECT * FROM t1 INNER JOIN t2 USING (id);
SELECT * FROM t1 INNER JOIN t2 WHERE t1.id = t2.id;  -- 使用where替代on
# inner join 简写      
 SELECT * FROM t1, t2 WHERE t1.id = t2.id;-- 使用逗号操作符+where
        

12.2左连接和右连接

#INNER JOIN (ON或USING）一起产生在两个表中都找到的行。
#OUTER JOIN可以产生在一个表中存在但不在另一个表中的行。
#OUTER JOIN有两种：
	#LEFT JOIN产生左表中的所有行，但可能不在右表中；
	#RIGHT JOIN产生的行位于右表中的所有行，但可能不在左侧表中。
#在中LEFT JOIN，当左表中的行与右表不匹配时，仍会选择该行，但会与右表中所有NULL的“假”记录组合在一起。
SELECT t1.id, t1.desc FROM t1 LEFT JOIN t2 USING (id) WHERE t2.id IS NULL;
# 报错 where 语句不能使用在outer join中
SELECT * FROM t1 LEFT JOIN t2 WHERE t1.id = t2.id; -- 报错 where 语句不能使用在outer join中
       

13.等值连接和非等值连接

14.自连接

15.jpa

15.1 jpa调用存储过程

@Query(value = " call p_production_plan_check(:inParam1,:inParam2,:inParam3,:inParam4)", nativeQuery = true)
  List<ProductionPlan> pPlanCheck(@Param("inParam1") String calStart,@Param("inParam2") String calEnd,@Param("inParam3") String workshopcode,@Param("inParam4") String orderno)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q7z1Rupc-1654503907313)(C:\Users\xiyatu\AppData\Roaming\Typora\typora-user-images\1618303156997.png)]

15.2 jpa多条件 in /and /or 分页

 public void SynchronousDzgjData(){
        List<DzgjVipPayOrder> resultList = dzgjVipPayOrderRepository.findAll(((root, query, cb) -> {
            List<Predicate> list = new ArrayList<>();
            if (StringUtils.isNotBlank(dateTime)) {
                LocalDateTime dayOfMonthStart = DateTimeUtil.getDateStart(DateTimeUtil.getFirstDayOfMonth(DateTimeUtil.fromString2LocalDateTime(dateTime)));
                LocalDateTime dayOfMonthEnd = DateTimeUtil.getDateEnd(DateTimeUtil.getlastDayOfMonth(DateTimeUtil.fromString2LocalDateTime(dateTime)));
                list.add(cb.between(root.get("payTime"), dayOfMonthStart, dayOfMonthEnd));
            }
            //首单
            list.add(cb.equal(root.get("firstPay"), 1));
            获取订单状态大于等于1的，0未支付
            list.add(cb.greaterThanOrEqualTo(root.get("status"), 1));
            使用 jpa in
            list.add(root.get("userId").in(userIdList));
            // 指定按照创建时间排序
           query.orderBy(cb.desc(root.get("payTime")));
            //如果需要or连接 只需要再new 一个List 放or的参数,最终放入list
            //记得将条件放入if判断中，否则会出现0=1的情况
            //list.add(cb.or(orlist.toArray()))
            return cb.and(list.toArray(new Predicate[list.size()]));
        }));
    }

 /**
     * 京店宝【获取退款申请列表】
     * 多条件查询注意加if判断，否则会出现0=1的情况
     * @param pageNo
     * @param pageSize
     * @param refundType
     * @param search
     * @return
     */
    public CommonResponse<PageResponse<JdbRefundVO>> getJdbRefundList(Integer pageNo, Integer pageSize, Integer refundType, String search) {
        try {
            //封装请求参数
            PageRequest pageRequest = new PageRequest(pageNo - 1, pageSize);
            //分页查询
            Page<JdbRefund> page = jdbRefundRepository.findAll((root, query, cb) -> {
                List<Predicate> predicateList = new ArrayList<>();
                List<Predicate> predicateOrList = new ArrayList<>();
                if (StringUtils.isNotBlank(search)) {
                    predicateOrList.add(cb.like(root.get("name"), String.format("%%%s%%", search)));
                    predicateOrList.add(cb.like(root.get("phone"), String.format("%%%s%%", search)));
                    predicateOrList.add(cb.like(root.get("jdbAccount"), String.format("%%%s%%", search)));
                }
                //or 组合
                if (predicateOrList.size() > 0) {
                    predicateList.add(cb.or(predicateOrList.toArray(new Predicate[predicateOrList.size()])));
                }
                //and 组合
                if (refundType != null && refundType != -1) {
                    predicateList.add(cb.equal(root.get("checkStatus"), refundType));
                }
                //先按照审核未审核的排，之后再按照创建时间排
                query.orderBy(cb.asc(root.get("checkStatus")), cb.desc(root.get("createTime")));

                return cb.and(predicateList.toArray(new Predicate[predicateList.size()]));
            }, pageRequest);
            if (page != null && page.hasContent()) {
                List<JdbRefundVO> list = page.getContent().stream().map(jdbRefund -> mapToJdbRefundVO(jdbRefund)).collect(Collectors.toList());
                return new CommonResponse<>(new PageResponse<>((int) page.getTotalElements(), pageNo, list));
            }
            return new CommonResponse<>(new PageResponse<>(0, pageNo, Collections.emptyList()));
        } catch (Exception e) {
            ExceptionUtil.err(getClass(), e);
            return ExceptionUtil.errResponse();
        }
    }

15.3 jpa 多条件、分页、分组 dto/vo --使用spring-data-jpa

@query 原生sql

     /**
     * \n#pageable\n
     * \n--  #pageable\n
     * order by ?#{#pageable} 固定写法
     * 多条件、分页、分组、使用jpa返回Dto 很麻烦，慎用
     *
     * @param userId
     * @param dateTime
     * @param pageable
     * @return
     */
    @Query(value = "SELECT cast(DATE_FORMAT(create_time,'%Y-%m-%d') AS char)  as `date`, cast(ratio_agent as char) as ratio, cast(IFNULL(SUM(IF(order_type=1,amount,-amount)),0.00) AS char) as amount ,cast(IFNULL(SUM(commission_agent),0.00) AS char) as commission FROM tao_order where  user_id =:userId AND DATE_FORMAT(create_time,'%Y-%m')= DATE_FORMAT(:dateTime,'%Y-%m') group by DATE_FORMAT(create_time,'%Y-%m-%d') order by create_time desc \n#pageable\n",
            countQuery = "select count(0) FROM tao_order where  user_id =:userId AND DATE_FORMAT(create_time,'%Y-%m')= DATE_FORMAT(:dateTime,'%Y-%m') group by DATE_FORMAT(create_time,'%Y-%m-%d') ",
            nativeQuery = true)
    Page<Object[]> getAgentDetailList(@Param("userId") Long userId, @Param("dateTime") String dateTime, Pageable pageable);

16.mybatis plus

16.1 创建PaginationInterceptor bean对象

package cn.jingdianbao.user.commons.config;

import com.baomidou.mybatisplus.enums.DBType;
import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/**
 * 类名: MybatisPlusConfig
 * 描述:
 * 日期: 2021/4/12-16:10
 *
 * @author 林建辉
 */
@Configuration
public class MybatisPlusConfig {
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor().setDialectType(DBType.MYSQL.getDb());
    }
}

16.2在数据源中设置mybatis plus分页插件

//创建数据源并设置mybatis分页插件
package cn.jingdianbao.user.commons.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import com.baomidou.mybatisplus.MybatisConfiguration;
import com.baomidou.mybatisplus.entity.GlobalConfiguration;
import com.baomidou.mybatisplus.mapper.LogicSqlInjector;
import com.baomidou.mybatisplus.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean;
import com.baomidou.mybatisplus.toolkit.GlobalConfigUtils;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;


/**
 *
 *
 * Description 数据源配置
 */
@Configuration
public class DataSourceConfiguration {


    @Autowired
    private Environment environment;

    @Autowired
    private PaginationInterceptor paginationInterceptor;

    @Bean(name = "dataSource")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DruidDataSource dataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * 手动配置事务管理器
     *
     * @param dataSource
     * @return
     */
    @Bean
    public DataSourceTransactionManager transactionManager(@Qualifier("dataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * 配置sessionFactory
     *
     * @param dataSource
     * @return
     * @throws Exception
     */
    @Bean(name = "jdbSqlSessionFactory")
    @Primary
    public SqlSessionFactory jdbSqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        MybatisSqlSessionFactoryBean mybatisSqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
        mybatisSqlSessionFactoryBean.setDataSource(dataSource);
        //配置枚举
        mybatisSqlSessionFactoryBean.setTypeEnumsPackage(environment.getProperty("mybatis-plus.type-enums-package"));
        mybatisSqlSessionFactoryBean.setGlobalConfig(getGlobalConfiguration());
        //映射文件
        mybatisSqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        MybatisConfiguration configuration = new MybatisConfiguration();
        //下划线隐射驼峰
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setCacheEnabled(true);
        mybatisSqlSessionFactoryBean.setConfiguration(configuration);
        // 关键代码 设置 MyBatis-Plus 分页插件 by linjianhui
        Interceptor[] plugins = {paginationInterceptor};
        mybatisSqlSessionFactoryBean.setPlugins(plugins);

        return mybatisSqlSessionFactoryBean.getObject();
    }

    private GlobalConfiguration getGlobalConfiguration() {
        GlobalConfiguration globalConfiguration = GlobalConfigUtils.defaults();
        //设置sql逻辑拦截
        globalConfiguration.setSqlInjector(new LogicSqlInjector());
        //设置逻辑删除字段值，已删除为1
        globalConfiguration.setLogicDeleteValue("1");
        //设置逻辑删除字段值，未删除为1
        globalConfiguration.setLogicNotDeleteValue("0");
        //字段策略 0:"忽略判断",1:"非 NULL 判断",2:"非空判断"
        globalConfiguration.setFieldStrategy(2);
        return globalConfiguration;
    }

    @Bean(name = "jdbSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate jdbSqlSessionTemplate(@Qualifier("jdbSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }


}

16.3mybatis plus分页查询条件

 /**
     * 描述：
     *
     * @param invitationCodeList
     * @param sourceTypeList
     * @param pageNo
     * @param pageSize
     * @param friendAccount
     * @param startTime
     * @param endTime
     * @return
     * @author 林建辉
     * @date: 2021/4/12 17:01
     */
    public CommonResponse<PageResponse<UserInfoData>> getInviteListByInvitationCodeListAndSourceTypeList(List<String> invitationCodeList, List<Integer> sourceTypeList, Integer pageNo, Integer pageSize, String friendAccount, String startTime, String endTime) {
        //分页对象
        Page<UserAuth> page = new Page<>(pageNo, pageSize);
        //查询条件
        Wrapper<UserAuth> warpper = Condition.create()
                .in(UserAuth.INVITATION_CODE, invitationCodeList)
                .in(UserAuth.SOURCE_TYPE, sourceTypeList)
                .like(StringUtils.isNotBlank(friendAccount), UserAuth.PHONE_NUMBER, friendAccount, SqlLike.DEFAULT);
        //在这种情况下，第二个重载lt(boolean condition, R column, Object val)会有错误，改为if判断模式
        if (StringUtils.isNotBlank(startTime)) {
            warpper.ge(UserAuth.CREATE_TIME, DateTimeUtil.fromString2LocalDate(startTime, DateTimeUtil.FORMAT_DATE).atStartOfDay());
        }
        if (StringUtils.isNotBlank(endTime)) {
            warpper.le(UserAuth.CREATE_TIME, DateTimeUtil.fromString2LocalDate(endTime, DateTimeUtil.FORMAT_DATE).atTime(LocalTime.MAX));
        }
        //按照时间倒序排
        warpper.orderBy(UserAuth.CREATE_TIME, false);

        page.setRecords(userAuthMapper.selectPage(page, warpper));
        if (page.getTotal() <= 0) {
            return new CommonResponse<>(new PageResponse<>(0, pageNo, Collections.emptyList()));
        }
        //不会为空
        List<UserInfoData> resultList = page.getRecords().stream().map(this::getUserInfoData).collect(Collectors.toList());

        return new CommonResponse<>(new PageResponse<>(page.getTotal(), pageNo, resultList));
    }

16.4 mybatis plus and or

 List<KeywordGroupTagInfo> list = keywordGroupTagInfoService.list(new QueryWrapper<KeywordGroupTagInfo>()
                .eq(KeywordGroupTagInfo.DATE, date == null ? LocalDate.now().toString() : date.toString())
                .eq(KeywordGroupTagInfo.OS_TYPE, osType == null ? 1 : 1)
                .eq(skuId != null, KeywordGroupTagInfo.SKU_ID, skuId)
                .eq(keywordId != null, KeywordGroupTagInfo.KEYWORD_ID, keywordId)
                .and(qw->qw.eq(KeywordGroupTagInfo.VISIT_NUM, 0)
                        .or().eq(KeywordGroupTagInfo.ADD_CART_NUM, 0)
                        .or().eq(KeywordGroupTagInfo.AGE_GROUP, "[]"))
                );

16.4 mybatis plus update

  /*  String oldSkuId = monitor.getSkuId();
            monitor.setKeyword(keyword);
            monitor.setSkuId(skuId);
            jdbSubSkuMonitorMapper.updateById(monitor);
            jdbSubSkuMonitorHistoryMapper.update(null, new UpdateWrapper<JdbSubSkuMonitorHistory>()
                    .eq(JdbSubSkuMonitorHistory.MONITOR_ID, monitor.getId()).eq(JdbSubSkuMonitorHistory.SKU_ID, oldSkuId)
                    .set(JdbSubSkuMonitorHistory.SKU_STATUS, -1));*/

17.JdbcTemplate 批量操作

在我们做后端服务Dao层开发，特别是大数据批量插入的时候，这时候普通的ORM框架(Mybatis、hibernate、JPA)就无法满足程序对性能的要求了。当然我们又不可能使用原生的JDBC进行操作，那样尽管效率会高，但是复杂度会上升。
综合考虑我们使用Spring中的JdbcTemplate和具名参数namedParameterJdbcTemplate来进行批量操作。
JdbcTemplate提供的主要方法:
	execute方法：可以用于执行任何SQL语句，一般用于执行DDL语句；
	update方法及batchUpdate方法：update方法用于执行新增、修改、删除等语句；batchUpdate方法用于执行批	 处理相关语句；
	query方法及queryForXXX方法：用于执行查询相关语句；
	call方法：用于执行存储过程、函数相关语句。

17.2NamedParameterJdbcTemplate

public void batchSave(){
  List<Object[]> batchArgs=new ArrayList<Object[]>();
  batchArgs.add(new Object[]{1,"小明",21});
  batchArgs.add(new Object[]{2,"小红",22});
  batchArgs.add(new Object[]{3,"露西",23});
  String sql = "insert into user (username,password) values (?,?)";
  jdbcTemplate.batchUpdate(sql, batchArgs);
}

以上基本实现了批量插入功能，但是当数据库字段比较多的时候，再以?占位符的形式编码的话就可能不是那么好一 一对应了，这里spring还提供了SimpleJdbcTemplate(Spring3.1+ 以后被标记为过时，到Spring 4.3则被完全移除，后面这个完全能满足需求)和NamedParameterJdbcTemplate模板引擎。
NamedParameterJdbcTemplate
相信使用过Hibernate的同学都知道，HQL中可以使用？或者:*的方式在外部配置查询参数。在 Spring JDBC 框架中，也提供了一种绑定 SQL 参数的方式，使用具名参数(named parameter)。
我们只需要在使用NamedParameterJdbcTemplate类中使用@Autowired进行注入即可：

17.2NamedParameterJdbcTemplate

17.2.1 批量更新插入batchUpdate

@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

public void batchSave(){
 List<User> list = new ArrayList<User>();
 //新增用户
 list.add(new AppStudent(1,"张三",21));
 list.add(new AppStudent(1,"李四",22));
 list.add(new AppStudent(1,"王二麻子",23));
 //批量转数组
 SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
 String sql = "INSERT INTO app_student(class_id,name,age) VALUES (:classId,:name,:age)";
 namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
}

17.2.2 多条件分页和in

 public ResponseList<List<JdbOrderVo>> getOrderAll(int id, int pageNo, int pageSize, String startDate, String
            endDate, String search, int userId) {
        //判断是否是BD角色
        Boolean isBD = AuthUtil.isContainsRole(Constants.BD);
        //sql 参数map对象
        MapSqlParameterSource sqlParam = new MapSqlParameterSource();
        StringBuilder sql = new StringBuilder();
        sql.append(" select * from jdb_order where 1=1 ");
        if (isBD) {
            sqlParam.addValue("userId", userId);
            sql.append(" and user_id=:userId ");
        }
        if (StringUtils.isNotBlank(search)) {
            sqlParam.addValue("search", search);
            sql.append(" and account=:search ");
        }
        if (StringUtils.isNotBlank(startDate)) {
            sqlParam.addValue("startDate", startDate);
            sql.append(" and DATE_FORMAT(buy_time,'%Y-%m-%d')>=:startDate ");
        }
        if (StringUtils.isNotBlank(endDate)) {
            sqlParam.addValue("endDate", endDate);
            sql.append(" and DATE_FORMAT(buy_time,'%Y-%m-%d') <=:endDate ");
        }

        Integer totalCount = namedParameterJdbcTemplate.queryForObject(String.format("select count(0) from (%s) a", sql.toString()), sqlParam, Integer.class);

        sql.append(" order by buy_time desc,id desc limit :index,:pageSize ");
        sqlParam.addValue("index", (pageNo - 1) * pageSize);
        sqlParam.addValue("pageSize", pageSize);

        Log.getSql().info("京店宝订单管理列表sql:\r\n{},参数map:\r\n{}", sql.toString(), sqlParam);
        List<JdbOrder> jdbOrderList = namedParameterJdbcTemplate.query(sql.toString(), sqlParam, new BeanPropertyRowMapper<>(JdbOrder.class));
        List<JdbOrderVo> resultList = getResult(jdbOrderList);

        return new ResponseList(200, "", pageSize, totalCount, pageNo, resultList);

    }

 public Map<Integer, Double> monthMoneyListUserIdForJdb(List<Integer> userIdList, String yearMonth) {

        Map<Integer, Double> resultMap = new HashMap<>();
        if (CollectionUtils.isEmpty(userIdList)) {
            return resultMap;
        }
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT DATE_FORMAT(create_time, '%Y-%m') as yearMonth, IFNULL(SUM(buy_money), 0) as month ,user_id AS id FROM jdb_order ");
        sql.append("  WHERE DATE_FORMAT(create_time, '%Y-%m') =:yearMonth ");
        sql.append("  AND user_id in(:userIdList)  AND is_performance=1 GROUP BY user_id");
        LogUtils.SQL.info("OrderService.monthMoneyListUserId.sql====={},yearMonth={},userIdList={}", sql.toString(), yearMonth, userIdList);
        MapSqlParameterSource sqlParam = new MapSqlParameterSource("yearMonth", yearMonth).addValue("userIdList", userIdList);
        List<Db> dbList = namedParameterJdbcTemplate.query(sql.toString(), sqlParam, new BeanPropertyRowMapper<>(Db.class));
        if (CollectionUtils.isNotEmpty(dbList)) {
            dbList.stream().map(db -> resultMap.put(db.getId(), db.getMonth())).collect(Collectors.toList());
        }
        return resultMap;
    }

//单线程分页低效率插入
public void addManagePresentVipRecordPhone() {
        int from = 0;
        int size = 100;
        List<ManageJdbVipGift> records = manageJdbVipGiftMapper.listRecord(null, from, size);
        while (!CollectionUtils.isEmpty(records)) {
            for (ManageJdbVipGift manageJdbVipGift : records) {
                UserAuth user = userService.getUserByParam(new UserAuth().setUserId(manageJdbVipGift.getUserId()).setSourceType(OsType.jingdianbao.getValue()));
                if (user != null) {
                    manageJdbVipGiftMapper.updatePhone(manageJdbVipGift.getId(), user.getPhoneNumber());
                }
            }
            from += size;
            records = manageJdbVipGiftMapper.listRecord(null, from, size);
        }
    }

`yushu-diagnosis`.

sync_

18.月份不足两位补零

 //月份不足2位补0
 String YMonth = String.format("%s-%02d", year, i);

19.创建连续日期

select d.currDate,IF(b.money is not null, b.money,0) from (select date_format(date_add('2021-01-01',interval t.help_topic_id month),'%Y-%m') as currDate  from mysql.help_topic t where t.help_topic_id<=11) d left join 
(SELECT SUM(recharge_money) as money,DATE_FORMAT(recharge_time,'%Y-%m') as ym FROM order_info  WHERE user_id=5837 AND is_performance=1 group by ym) b on d.currDate=b.ym


### show create table mysql.help_topic
CREATE TABLE `help_topic` (
  `help_topic_id` int(10) unsigned NOT NULL,
  `name` char(64) NOT NULL,
  `help_category_id` smallint(5) unsigned NOT NULL,
  `description` text NOT NULL,
  `example` text NOT NULL,
  `url` text NOT NULL,#有test字段，不要select *
  PRIMARY KEY (`help_topic_id`),
  UNIQUE KEY `name` (`name`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='help topics'

20.创建随机函数

select FLOOR(RAND() * 1000)

示例

-- 查询任务,下单数据不能全部为0
set @var=0;
select * from (
	select *,
	@var:=date_task_id, 
	(select max(task_num)-(select sum(task_num) from date_details_task where  	   date_task_id=@var and task_type !=2)  from  date_details_task where date_task_id=@var ) as other
	from date_details_task where task_num=1 and task_type=5 and finish_num !=1 and DATE_FORMAT(create_time,'%Y-%m-%d') in ('2020-11-21')
) as a where a.other <=0

select *,ROW_NUMBER() OVER(Order by a.薪水) AS 排名  from table as a order by a.薪水

set @a=0;
select concat('第',(@a:=@a+1),'名') as sort ,姓名,薪水 from 表 order by 薪水;

sql 语句优化

1：查询表中的信息

show full COLUMNS from task ;
[desc|DESCRIBE] task;

• Key

  列是否被索引：

  • 如果Key为空，则该列要么不被索引，要么仅作为多列非唯一索引中的第二列被索引。
  • 如果Key为 PRI，PRIMARY KEY则列为a或为多列中的列之一PRIMARY KEY。
  • 如果Key为UNI，则该列为UNIQUE索引的第一列 。（ UNIQUE索引允许多个 NULL值，但是您可以NULL通过检查Null字段来判断该列是否允许。）
  • 如果Key为MUL，则该列是非唯一索引的第一列，在该列中允许多次出现给定值。

  如果超过一个Key值适用于表中给定的列，Key 显示一个具有最高优先级，顺序 PRI，UNI， MUL。

  甲UNIQUE索引可被显示为 PRI，如果它不能包含 NULL值并没有 PRIMARY KEY在表中。甲 UNIQUE索引可能会显示为 MUL如果若干列形成复合 UNIQUE索引; 尽管各列的组合是唯一的，但各列仍可以容纳多次出现的给定值。

• Default

  列的默认值。这是 NULL如果列有一个明确的默认NULL，或者如果列定义不包括DEFAULT条款。

• Extra

  有关给定列的任何其他可用信息。在以下情况下，该值是非空的：

  • auto_increment具有AUTO_INCREMENT属性的列。
  • on update CURRENT_TIMESTAMP对于 TIMESTAMP或 DATETIME有列ON UPDATE CURRENT_TIMESTAMP 属性。
  • VIRTUAL GENERATED或VIRTUAL STORED用于生成的列。
  • DEFAULT_GENERATED 对于具有表达式默认值的列。

表列信息也可从 INFORMATION_SCHEMA COLUMNS表中获得。请参见 第26.8节“ INFORMATION_SCHEMA列表”。有关隐藏列的扩展信息仅可使用 SHOW EXTENDED COLUMNS；它不能从COLUMNS表中获得。

2:in、exists、left join 小表驱动大表

类似于嵌套循环：
for(int i=5;.......){
     for(int j=1000;......){
     }
}
如果小的循环在外层，对于数据库连接来说就只连接5次，进行5000次操作，如果1000在外，则需要进行1000次数据库连接，从而浪费资源，增加消耗。这就是为什么要小表驱动大表。

in后面跟的是小表，exists后面跟的是大表。
简记：in小，exists大。

in、exists、left join 可以相互转换
IN/ALL/ANY/SOME 子查询不支持limit语句

2.1使用exists替代left join

explain SELECT count(DISTINCT a.id)
FROM task a
    # left JOIN date_task b ON a.id = b.task_id
WHERE a.os_type = 5
  AND a.status IN (2, 3, 4, 5, 6, 7)
 #and b.task_date >= '2021-05-01' and '2021-06-01'> b.task_date
 and EXISTS(select 1 from date_task b where a.id = b.task_id and b.id=575097)
 and EXISTS(select 1 from date_task b where a.id = b.task_id and b.task_date >= '2021-06-01' and '2021-07-01'> b.task_date)

create index status_sort_time_idex ON task (sort_time desc,status desc);
drop index  status_sort_time_idex on task;
show index from task;
#distinct 会造成创建临时表，using temporary
explain 
select distinct a.* from task a left join date_task b on a.id = b.task_id where a.os_type=1 and a.status in (2,3,4,5,6,7) order by sort_time desc limit 10

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6cFB5eYP-1654503907316)(C:\Users\xiyatu\AppData\Roaming\Typora\typora-user-images\1607771280293.png)]

3：mysql对T-SQL标准的扩展

1. 支持group by 引用别名，这在sql92和sql99中是非法的，

   这意义这mysql的select语句在在group by 之前完成

2. 在禁用ONLY_FULL_GROUP_BY 的情况下，允许group by 非聚合函数列

3. 如果你并不关心address的值，在only_full_group_by 开启的模式下，

   告诉mysql接受该查询可以使用any_value()

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

4：mysql 索引

4.1：创建索引

#MySQL 8.0.13和更高版本支持索引表达式，可以索引未直接存储在表中的值，表达式必须要用()括起来
#示例：
CREATE INDEX part_of_name ON customer (name(10));#列前缀索引长度不要超过786个字符
CREATE INDEX idx1 ON t1 ((col1 + col2));
CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1))));
ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);
#实际示例
ALTER TABLE `t_order`
ADD INDEX `user_id_idx` (`user_id`) USING BTREE,#暂时未用到
ADD INDEX `invitation_code_idx` (`invitation_code`) USING BTREE,#暂时未用到
ADD INDEX `osType_createTime_idx` (`os_type` asc,`create_time` desc) USING BTREE ,#create_time用于排序
ADD INDEX ((DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s')) DESC)#方法索引

#创建索引方式一：InnoDB默认的索引存储类型是BTREE
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...
    
#语法解析
#索引类型
index_type: USING {BTREE | HASH}
#索引列：可以指定索引的长度和索引的排序方式    
key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
  | ENGINE_ATTRIBUTE [=] 'string'
  | SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
    
#创建索引方式二：InnoDB默认的索引存储类型是BTREE
#CREATE INDEX不能用于创建一个PRIMARY KEY; 使用 ALTER TABLE代替
 ALTER TABLE tb_name  ADD {INDEX | KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...

4.2：查看索引和删除索引

show index from table_name
drop INDEX index_name on table_name

SHOW INDEX 返回以下字段：

Table
表的名称。
Non_unique
如果索引不能包含重复项，则为0；如果可以，则为1。
Key_name
索引名称。如果索引是主键，则名称始终为PRIMARY。
Seq_in_index
索引中的列序号，从1开始。
Column_name
列名。另请参见该Expression列的说明 。
Collation
列在索引中的排序方式。它可以具有值 A（升序），D （降序）或NULL（未排序）。
Cardinality
索引中唯一值数量的估计。要更新此数字，请运行ANALYZE TABLE或（对于MyISAM表） 运行myisamchk -a。
Cardinality
基于存储为整数的统计信息进行计数，因此即使对于小型表，该值也不一定精确。基数越高，MySQL在进行联接时使用索引的机会越大。
Sub_part
索引前缀。也就是说，NULL如果对整个列都进行了索引，则如果仅对该列进行部分索引，则为索引字符的数量 
注意
前缀限制以字节为单位。然而，前缀长度为索引规范CREATE TABLE，ALTER TABLE和CREATE INDEX语句解释为非二进制串类型的字符数（CHAR， VARCHAR， TEXT对于二进制串类型），并且字节数（BINARY， VARBINARY， BLOB）。为使用多字节字符集的非二进制字符串列指定前缀长度时，请考虑到这一点。

有关索引前缀的更多信息，请参见 第8.3.5节“列索引”和 第13.1.15节“ CREATE INDEX语句”。

Packed
指示密钥的包装方式。NULL如果不是这样。
Null
包含YES该列是否包含NULL值，''如果不包含 。
Index_type
使用的索引方法（BTREE， FULLTEXT，HASH， RTREE）。
Comment
关于索引的信息未在其自己的列中描述，例如disabled是否禁用了索引。
Index_comment
COMMENT创建索引时，为索引 提供的任何注释均带有 属性。
Visible
索引是否对优化器可见。请参见 第8.3.12节“不可见索引”。
Expression
MySQL 8.0.13和更高版本支持功能性关键部分（请参见 Functional Key Parts），这会影响Column_name和 Expression列：
对于非功能键部分， Column_name指示由键部分索引的列，并且 Expression为 NULL。
对于功能键部件， Column_name列为 NULL并 Expression指示键部件的表达式。

4.3示例:有where、有and or 、有like、有order by 有limit

#索引的创建要进行针对化设计
#删除索引 drop INDEX indexName on task
explain SELECT *
FROM task
WHERE user_id = 34723
	AND deleted = 0
	AND os_type = 5
	AND (shop_name LIKE '%戴浦（DAIPU）京东自营旗舰店%'
		OR live_name LIKE '%戴浦（DAIPU）京东自营旗舰店%')
 ORDER BY sort_time DESC
LIMIT 0, 10

#显示索引
show INDEX from task;
 
#添加多一个索引
ALTER TABLE `task` ADD INDEX `userId_sortTime_idx` (`user_id` asc, `sort_time` desc) USING BTREE ;

5：like优化

#第一种方式加入指定前缀,并且为该字段添加索引：yushu-，使之成为单值匹配：String.format("yushu-%s",search);
SELECT * FROM user_auth WHERE user_name LIKE 'yushu-%word%'
#第二种方式：使用各种函数，但有时候会更慢
SELECT `column` FROM `table` WHERE 0<LOCATE('keyword', `column`)
SELECT `column` FROM `table` WHERE POSITION('keyword' IN `column`)
SELECT `column` FROM `table` WHERE 0<INSTR(`column`, 'keyword' )
#第三种方式
   使用覆盖索引

6.优化过程optimizer trace

# 1. 打开optimizer trace功能 (默认情况下它是关闭的):
SET optimizer_trace="enabled=on";
SELECT ...; # 这里输入你自己的查询语句
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# 当你停止查看语句的优化过程时，把optimizer trace功能关闭
SET optimizer_trace="enabled=off";

7.性能瓶颈定位

#我们可以通过show命令查看MySQL状态及变量，找到系统的瓶颈：
 show status ——显示状态信息（扩展show status like ‘XXX’）
 show variables ——显示系统变量（扩展show variables like ‘XXX’）
 show innodb status ——显示InnoDB存储引擎的状态
 show processlist ——查看当前SQL执行，包括执行状态、是否锁表等
mysqladmin variables -u username -p password——显示系统变量
mysqladmin extended-status -u username -p password——显示状态信息
#我常用的主要有show status和show processlist。
#链接：https://www.jianshu.com/p/3c79039e82aa

8.sql mode

#查看你mysql 版本 #mysql 不区分大小写
SELECT VERSION();
#展示变量 show [global] variables like '%mode';
show  variables like '%mode';
#查看全局sql mode 
select @@global.sql_mode;
#ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
#
# 将only_full_group_by 去掉，全局有效，不要设置成'',设置成'' 可能继续生效, 但该方法在重启mysql服务后会失效，重启服务后会失效
set global sql_mode='strict_trans_tables,no_zero_in_date,no_zero_date,error_for_division_by_zero,no_engine_substitution';

#关闭连接工具，重新打开运行,ok

执行计划EXPLAIN

explain 
select id ,sku_id from task where id in (select task_id from date_task where task_date >= '2020-12-01' and task_date < '2020-12-11') limit 1000

EXPLAIN输出列

select_type类型

表名也可以是以下值之一：

• <unionM,N>：该行是指具有和id值的行 的 M并集 N。
• <derivedN>：该行是指用于与该行的派生表结果id的值 N。派生表可能来自（例如）FROM子句中的子查询
• <subqueryN>：该行是指该行的物化子查询的结果，其id 值为N。请参见 第8.2.2.2节“通过物化来优化子查询”。

type类型

该type列 EXPLAIN输出介绍如何联接表。在JSON格式的输出中，这些作为access_type属性的值找到。以下列表描述了连接类型，从最佳类型到最差类型：

• system

  该表只有一行（=系统表）。这是const联接类型的特例 。

• const

  该表最多具有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量。 const表非常快，因为它们只能读取一次。

  const在将aPRIMARY KEY或 UNIQUEindex的所有部分与常数值进行比较时使用。在以下查询中，tbl_name可以用作const 表：

  SELECT * FROM tbl_name WHERE primary_key=1;
  
  SELECT * FROM tbl_name
    WHERE primary_key_part1=1 AND primary_key_part2=2;
  
  

• eq_ref

  对于先前表中的行的每种组合，从此表中读取一行。除了 system和 const类型，这是最好的联接类型。当联接使用索引的所有部分并且索引为a PRIMARY KEY或UNIQUE NOT NULLindex时使用。

  eq_ref可以用于使用=运算符进行比较的索引列 。比较值可以是常量，也可以是使用在此表之前读取的表中列的表达式。在以下示例中，MySQL可以使用 eq_ref联接进行处理 ref_table：

  SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column=other_table.column;
  
  SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column_part1=other_table.column
    AND ref_table.key_column_part2=1;
  
  

• ref

  对于先前表中的行的每种组合，将从该表中读取具有匹配索引值的所有行。ref如果联接仅使用键的最左前缀，或者如果键不是aPRIMARY KEY或 UNIQUE索引（换句话说，如果联接无法根据键值选择单个行），则使用。如果使用的键仅匹配几行，则这是一种很好的联接类型。

  ref可以用于使用=或<=> 运算符进行比较的索引列 。在以下示例中，MySQL可以使用 ref联接进行处理 ref_table：

  SELECT * FROM ref_table WHERE key_column=expr;
  
  SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column=other_table.column;
  
  SELECT * FROM ref_table,other_table
    WHERE ref_table.key_column_part1=other_table.column
    AND ref_table.key_column_part2=1;
  
  

• fulltext

  使用FULLTEXT 索引执行联接。

• ref_or_null

  这种连接类型类似于 ref，但是除了MySQL对包含NULL值的行进行了额外的搜索之外。此联接类型优化最常用于解析子查询。在以下示例中，MySQL可以使用 ref_or_null联接进行处理ref_table：

  SELECT * FROM ref_table
    WHERE key_column=expr OR key_column IS NULL;
  
  

  请参见第8.2.1.15节“ IS NULL优化”。

• index_merge

  此联接类型指示使用索引合并优化。在这种情况下，key输出行中的列包含所用索引的列表，并key_len包含所用索引 的最长键部分的列表。有关更多信息，请参见 第8.2.1.3节“索引合并优化”。

• unique_subquery

  此类型替换 以下形式的eq_ref某些 IN子查询：

  value IN (SELECT primary_key FROM single_table WHERE some_expr)
  
  

  unique_subquery 只是一个索引查找函数，它完全替代了子查询以提高效率。

• index_subquery

  此连接类型类似于 unique_subquery。它替代IN子查询，但适用于以下形式的子查询中的非唯一索引：

  value IN (SELECT key_column FROM single_table WHERE some_expr)
  
  

• range

  使用索引选择行，仅检索给定范围内的行。的key 输出行中的列指示使用哪个索引。将key_len包含已使用的时间最长的关键部分。该ref列 NULL适用于此类型。

  range当一个键列使用任何的相比于恒定可使用 =， <>， >， >=， <， <=， IS NULL， <=>， BETWEEN， LIKE，或 IN()运营商：

  SELECT * FROM tbl_name
    WHERE key_column = 10;
  
  SELECT * FROM tbl_name
    WHERE key_column BETWEEN 10 and 20;
  
  SELECT * FROM tbl_name
    WHERE key_column IN (10,20,30);
  
  SELECT * FROM tbl_name
    WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
  
  

• index

  该index联接类型是一样的 ALL，只是索引树被扫描。发生这种情况有两种方式：

  • 如果索引是查询的覆盖索引，并且可用于满足表中所需的所有数据，则仅扫描索引树。在这种情况下，Extra列显示为 Using index。仅索引扫描通常比索引扫描更快， ALL因为索引的大小通常小于表数据。
  • 使用对索引的读取来执行全表扫描，以按索引顺序查找数据行。 Uses index没有出现在 Extra列中。

  当查询仅使用属于单个索引一部分的列时，MySQL可以使用此联接类型。

• ALL

  对来自先前表的行的每个组合进行全表扫描。如果该表是未标记的第一个表 const，则通常不好，并且在所有其他情况下通常 非常糟糕。通常，您可以ALL通过添加索引来避免这种情况，这些 索引允许基于早期表中的常量值或列值从表中检索行。

Extra类型

该Extra列 EXPLAIN输出包含MySQL解决查询的额外信息。以下列表说明了可以在此列中显示的值。每个项目还针对JSON格式的输出指示哪个属性显示Extra值。对于其中一些，有一个特定的属性。其他显示为message 属性的文本。

如果你想使你的查询尽可能快，看出来Extra的列值Using filesort和Using temporary，或在JSON格式的EXPLAIN输出，用于 using_filesort和 using_temporary_table性能等于 true。

• Backward index scan（JSON： backward_index_scan）

  优化器能够在InnoDB表上使用降序索引 。与一起显示 Using index。有关更多信息，请参见 第8.3.13节“降序索引”。

• Child of '*table*' pushed join@1（JSON：message 文本）

  将该表引用为 table可以下推到NDB内核的联接中的子级。启用下推联接时，仅适用于NDB群集。有关ndb_join_pushdown更多信息和示例，请参见服务器系统变量的描述 。

• const row not found（JSON属性： const_row_not_found）

  对于诸如之类的查询，该表为空。 SELECT ... FROM *tbl_name*

• Deleting all rows（JSON属性： message）

  对于DELETE，某些存储引擎（例如MyISAM）支持一种处理程序方法，该方法以一种简单而快速的方式删除所有表行。Extra如果引擎使用此优化，则显示此值。

• Distinct（JSON属性： distinct）

  MySQL正在寻找不同的值，因此在找到第一个匹配的行后，它将停止为当前行组合搜索更多的行。

• FirstMatch(*tbl_name*) （JSON属性：first_match）

  半联接FirstMatch联接快捷方式策略用于tbl_name。

• Full scan on NULL key（JSON属性： message）

  当优化器无法使用索引查找访问方法时，这会作为子查询优化的后备策略而发生。

• Impossible HAVING（JSON属性： message）

  该HAVING子句始终为false，不能选择任何行。

• Impossible WHERE（JSON属性： message）

  该WHERE子句始终为false，不能选择任何行。

• Impossible WHERE noticed after reading const tables（JSON属性： message）

  MySQL已经读取了所有 const（和 system）表，并注意到该WHERE子句始终为false。

• LooseScan(*m*..*n*) （JSON属性：message）

  使用半连接的LooseScan策略。 m和 n是关键部件号。

• No matching min/max row（JSON属性： message）

  没有行满足查询的条件，例如 。 SELECT MIN(...) FROM ... WHERE *condition*

• no matching row in const table（JSON属性：message）

  对于具有联接的查询，存在一个空表或没有满足唯一索引条件的行的表。

• No matching rows after partition pruning（JSON属性： message）

  对于DELETE或 UPDATE，在分区修剪后，优化器未发现任何要删除或更新的内容。它的含义类似于Impossible WHERE forSELECT语句。

• No tables used（JSON属性： message）

  该查询没有FROM子句，或者有 FROM DUAL子句。

  对于INSERT或 REPLACE语句， EXPLAIN在没有任何SELECT 部分时显示此值。例如，出现的EXPLAIN INSERT INTO t VALUES(10)原因是，它等同于 EXPLAIN INSERT INTO t SELECT 10 FROM DUAL。

• Not exists（JSON属性： message）

  MySQL能够对LEFT JOIN 查询进行优化，并且在找到符合LEFT JOIN条件的一行后，不检查该表中的更多行是否为上一行组合。这是可以通过这种方式优化的查询类型的示例：

  SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
    WHERE t2.id IS NULL;
  
  

  假设t2.id定义为 NOT NULL。在这种情况下，MySQL使用的值 扫描 t1并查找行 。如果MySQL在中找到匹配的行 ，它将知道它 永远不会是 ，并且不会扫描具有相同值的其余行。换句话说，对于in中的每一行，MySQL只需进行一次查找，无论in中实际匹配多少行。 t2``t1.id``t2``t2.id``NULL``t2``id``t1``t2``t2

  在MySQL 8.0.17和更高版本中，这还可以指示WHERE形式为的 条件 或 已在内部转换为反联接的条件。这将删除子查询，并将其表放入最顶层查询的计划中，从而提供了改进的成本计划。通过合并半联接和反联接，优化器可以更自由地对执行计划中的表进行重新排序，在某些情况下，可以使计划更快。 NOT IN (*subquery*)``NOT EXISTS (*subquery*)

  通过检查或 执行的结果后 的Message列，您可以查看何时对给定查询执行反联接转换 。 SHOW WARNINGS``EXPLAIN``EXPLAIN FORMAT=TREE

  笔记

  反连接是半连接的补充 。该反连接返回的所有行 对此有 没有行 相匹配 。 *table_a* JOIN *table_b* ON *condition*table_atable_bcondition

• Plan isn't ready yet （JSON属性：无）

  EXPLAIN FOR CONNECTION当优化器尚未完成为在命名连接中执行的语句创建执行计划时， 就会出现此值。如果执行计划输出包括多行，则Extra取决于优化程序确定完整执行计划的进度，其中的任何一行或所有行都可以具有此 值。

• Range checked for each record (index map: *N*)（JSON属性： message）

  MySQL没有找到合适的索引来使用，但是发现一些索引可以在已知先前表中的列值之后使用。对于上表中的每个行组合，MySQL检查是否可以使用range或 index_merge访问方法来检索行。这不是很快，但是比完全没有索引的联接要快。适用标准如 第8.2.1.2节“范围优化”和 第8.2.1.3节“索引合并优化”中所述，除了上表的所有列值都是已知的并且被视为常量。

  索引从1开始编号，其顺序SHOW INDEX与表中显示的顺序相同。索引图值 N是指示哪些索引为候选的位掩码值。例如，值0x19（二进制11001）表示考虑了索引1、4和5。

• Recursive（JSON属性： recursive）

  这表明该行适用于SELECT递归公用表表达式的递归 部分。请参见第13.2.15节“ WITH（公用表表达式）”。

• Rematerialize（JSON属性： rematerialize）

  Rematerialize (X,...)在EXPLAINtable的行中 显示T，其中X是任何横向派生的表，当T读取新行时会触发其重新实现。例如：

  SELECT
    ...
  FROM
    t,
    LATERAL (derived table that refers to t) AS dt
  ...
  
  

  每当t顶级查询处理新的一行时，都会重新实现派生表的内容，以使它保持最新 。

• Scanned *N* databases（JSON属性： message）

  这表示在处理INFORMATION_SCHEMA表查询时服务器执行了多少目录扫描 ，如第8.2.3节“优化INFORMATION_SCHEMA查询”中所述。的值N可以是0、1或 all。

• Select tables optimized away（JSON属性：message）

  优化器确定1）最多应返回一行，以及2）要生成这一行，必须读取确定的一组行。当在优化阶段可以读取要读取的行时（例如，通过读取索引行），则在查询执行期间无需读取任何表。

  当查询被隐式分组（包含聚合函数但没有GROUP BY子句）时，满足第一个条件 。当每个使用的索引执行一次行查找时，满足第二个条件。读取的索引数决定了要读取的行数。

  考虑以下隐式分组查询：

  SELECT MIN(c1), MIN(c2) FROM t1;
  
  

  假设MIN(c1)可以通过读取一个索引行MIN(c2) 来检索，并且可以通过从另一索引中读取一行来进行检索。即，对于每一列c1和 c2，存在其中列是索引的第一列的索引。在这种情况下，将返回一行，这是通过读取两个确定性行产生的。

  Extra如果要读取的行不是确定性的，则不会出现 此值。考虑以下查询：

  SELECT MIN(c2) FROM t1 WHERE c1 <= 10;
  
  

  假设这(c1, c2)是一个覆盖指数。使用此索引，c1 <= 10必须扫描所有具有的行以找到最小值 c2。相比之下，请考虑以下查询：

  SELECT MIN(c2) FROM t1 WHERE c1 = 10;
  
  

  在这种情况下，第一个索引行c1 = 10包含最小值c2 。只读取一行就可以产生返回的行。

  对于维护每个表的行数准确的存储引擎（例如MyISAM，但不是 InnoDB），对于缺少该子句或始终为true且没有 子句的查询，Extra 可能会出现此值。（这是一个隐式分组查询的实例，其中存储引擎影响是否可以读取确定数量的行。） COUNT(*)``WHERE``GROUP BY

• Skip_open_table， Open_frm_only， Open_full_table（JSON属性： message）

  这些值指示适用于INFORMATION_SCHEMA 表查询的文件打开优化。

  • Skip_open_table：不需要打开表文件。该信息已经可以从数据字典中获得。
  • Open_frm_only：仅数据字典需要读取以获取表信息。
  • Open_full_table：未优化的信息查找。表信息必须从数据字典中读取并通过读取表文件来读取。

• Start temporary，End temporary（JSON属性： message）

  这表明临时表用于半联接重复淘汰策略。

• unique row not found（JSON属性： message）

  对于诸如之类的查询，没有行满足 索引或表中的条件。 SELECT ... FROM *tbl_name*``UNIQUE``PRIMARY KEY

• Using filesort（JSON属性： using_filesort）

  MySQL必须额外进行一遍，以找出如何按排序顺序检索行。排序是通过根据联接类型遍历所有行并存储与该WHERE子句匹配的所有行的排序键和指向该行的指针来完成的。然后对键进行排序，并按排序顺序检索行。请参见 第8.2.1.16节“按优化排序”。

• Using index（JSON属性： using_index）

  仅使用索引树中的信息从表中检索列信息，而不必进行其他查找以读取实际行。当查询仅使用属于单个索引的列时，可以使用此策略。

  对于InnoDB具有用户定义的聚集索引的表，即使列中Using index不存在 该索引也可以使用Extra。如果typeis index和 keyis就是这种情况 PRIMARY。

  显示有关EXPLAIN FORMAT=TRADITIONAL和的 有关所使用的任何覆盖索引的信息 EXPLAIN FORMAT=JSON。从MySQL 8.0.27开始，它也显示为EXPLAIN FORMAT=TREE。

• Using index condition（JSON属性： using_index_condition）

  通过访问索引元组并首先对其进行测试以确定是否读取完整的表行来读取表。这样，除非有必要，否则索引信息将用于延迟（“下推”）整个表行的读取。请参见 第8.2.1.6节“索引条件下推优化”。

• Using index for group-by（JSON属性：using_index_for_group_by）

  与Using index表访问方法类似，Using index for group-by 表示MySQL找到了一个索引，该索引可用于检索aGROUP BY或 DISTINCT查询的所有列，而无需对实际表进行任何额外的磁盘访问。此外，以最有效的方式使用索引，因此对于每个组，仅读取少数索引条目。有关详细信息，请参见 第8.2.1.17节“优化组”。

• Using index for skip scan（JSON属性：using_index_for_skip_scan）

  指示使用“跳过扫描”访问方法。请参阅 跳过扫描范围访问方法。

• Using join buffer (Block Nested Loop)， Using join buffer (Batched Key Access)， Using join buffer (hash join)（JSON属性：using_join_buffer）

  来自较早联接的表被部分读取到联接缓冲区中，然后从缓冲区中使用它们的行来执行与当前表的联接。 (Block Nested Loop)表示使用块嵌套循环算法，(Batched Key Access)表示使用批处理密钥访问算法，并(hash join)表示使用哈希联接。即，EXPLAIN缓冲输出的前一行中的表中的键 ，并从出现行所在的表中批量提取匹配的行 Using join buffer。

  在JSON格式的输出，值 using_join_buffer总是之一 Block Nested Loop，Batched Key Access或hash join。

  从MySQL 8.0.18开始，可以使用哈希联接。MySQL 8.0.20或更高版本的MySQL中未使用“块嵌套循环”算法。有关这些优化的更多信息，请参见第8.2.1.4节“哈希联接优化”和“ 块嵌套循环联接算法”。

  有关批量密钥访问算法的信息，请参阅批量密钥访问联接。

• Using MRR（JSON属性： message）

  使用多范围读取优化策略读取表。请参见第8.2.1.11节“多范围读取优化”。

• Using sort_union(...)，Using union(...)，Using intersect(...)（JSON属性： message）

  这些指示了特定算法，该算法显示了如何针对index_merge联接类型合并索引扫描 。请参见第8.2.1.3节“索引合并优化”。

• Using temporary（JSON属性： using_temporary_table）

  为了解决查询，MySQL需要创建一个临时表来保存结果。如果查询包含GROUP BY和 ORDER BY子句以不同的方式列出列，通常会发生这种情况。

• Using where（JSON属性： attached_condition）

  甲WHERE子句用于限制来匹配下一个表或发送到客户端的行。除非您专门打算从表中获取或检查所有行，否则如果查询中的Extra值不是 Using where且表联接类型为ALL或 ，则 查询中可能会出错index。

  Using where在JSON格式的输出中没有直接对应的内容；该 attached_condition属性包含使用的任何WHERE条件。

• Using where with pushed condition（JSON属性：message）

  此产品适用于NDB 表只。这意味着NDB Cluster正在使用条件下推优化来提高在非索引列和常量之间进行直接比较的效率。在这种情况下，条件被“下推”到群集的数据节点，并同时在所有数据节点上进行评估。这样就无需通过网络发送不匹配的行，并且在可以但不使用条件下推的情况下，可以将此类查询的速度提高5到10倍。有关更多信息，请参见 第8.2.1.5节“发动机状况下推优化”。

• Zero limit（JSON属性： message）

  该查询有一个LIMIT 0子句，不能选择任何行。

口诀

建表SQL

索引情况

一、全值匹配我最爱



二、最佳左前缀法则 
1、where条件列的，从索引的最左前列开始，且不跳过索引中的列

2、违背原则：未以最左前列开始，索引失效

3、违背原则：跳过索引中间列（age），只引用了部分索引

结论： where条件要满足 最佳左前缀法则。 口诀：带头大哥不能死，中间兄弟不能断


三、不在索引列上做任何操作（计算、函数、自动or手动类型转换），会导致索引失效而转向全表扫描 

即使满足最左前缀原则，但where条件中使用了函数后，索引失效


四、存储引擎不能使用索引中范围条件后边的列 


结论：组合索引中的列name用来检索，age只且到索引排序（没用到索引检索），而age后边的列pos就会失效了。
疑问：若使用最左边列做范围查找，那么后边的几个条件还会用到索引吗？


五、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
1、select*,从表中检索数据 

2、按需要查字段，从索引中检索数据

3、按需要查字段（查询列可只用索引列中的一部分），范围查询、未使用范围查询、查询部分索引列分析对比

结论：满足原则五时，用范围查找时，查询类型type：ref，而不是range，参考原则四的说明

六、mysql在使用不等于（！=或者<>)的时候无法使用索引会导致全表扫描
案例如下：

七、is null,is not null也无法使用索引

八、like以通配符开头（'%abc')mysql索引失效，变成全表扫描的操作（注：以通配符结尾('abc%')不会失效）
1、以通配符开头，全表扫描，索引失效
2、未以通配符开头，只是通配符结尾，查询类型：range范围查找（未全表扫描），索引可用


3、问题：如何解决like '%字符串%'时索引失效的方法?? 答：使用用覆盖索引来解决
案例分析如下
3.1 表结构：

3.2 未建索引（只设置了主键为id）
结论：全表扫描，未使用索引


3.2.2 只查一个id字段的情况，也未用到索引

3.2.3 以下几种情况都未使用到索引，执行后都是全表扫描


3.3 创建索引

3.3.1 重新分析

用覆盖索引 ，只查name,age

只查主键id，覆盖索引查询，索引有效

只查name或只查age，覆盖索引查询，索引有效


查询列的个数超过索引列的个数时（id不考虑在内），索引失效


总结：用覆盖索引来解决 '%字符串%'查询的问题

九、字符串 不加号索引失效
不加引号时，mysql会给name字段做隐式的类型转换（将整数类型转换成字符串型），违反了第三条原则：索引列上无计算，所以索引会失效


十、少用or，用它来连接时会索引失效


小结：不要因为以上各种情况的索引失效而感到束手束脚，在实际应用中，也难免会出现索引失效的情况，sql最优并不代表满足以上所有原则 

口诀：
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上无计算，范围之后全失效；
like百分加右边，覆盖索引不写星*
不等空值还有or，索引失效要少用；
字符串里有引号， 高级SQL也不难

二、面试题讲解
题目SQL


1、顺序问题
1.1、where条件有带头大哥，中间兄弟顺序乱 ，索引有效

1.2、where条件带头大哥往后站，中间兄弟顺序乱，索引有效


1.3 原因：mysql有自带的sql优化器，where查询条件顺序问题并不会导致索引失效

2、范围之后全失效（C3只用来做排序）

3、where条件中C4列做范围查找，C1，C2，C3，C4索引列都生效


4、where条件有带头大哥，但中间兄弟断，sql用了order by,索引部分失效

4.1 条件中有C4
用到了索引中的c1,c2用作查找，只用到c3做排序，未用到索引中的C4

4.2 条件中没有c4,分析结果跟4.1相同

4.3 中间兄弟C3断，索引在排序中失效，出现了filesort

5 、filesort的 问题
5.1 只用到c1一个索引，排序用了c2,c3(按序），未出现filesort
5.2 只用到c1一个索引，排序用了c3，c2(乱序），出现了filesort
小结：查找和排序列的顺序最好按照索引列的顺序来

5.3 用到c1，c2两个索引，排序用了c2,c3(按序），未出现filesort

5.4 用到c1，c2两个索引，排序用了c3，c2(乱序），未出现filesort
原因 ：因为在查找条件中用了c2,所以order by排序时，c2已经是一个常量了，换句话说只要按[order by c3,常量]来排序，并不会产生filesort

6、group by分组前必排序，所以索引使用原则和索引优化法则基本跟order by 相同，违反法则时会产生临时表和filesort

三、一般性建议

优化指南

怎么加快查询速度，优化查询效率，主要原则就是应尽量避免全表扫描，应该考虑在where及order by 涉及的列上建立索引。

　　建立索引不是建的越多越好，原则是：

　　第一：一个表的索引不是越多越好，也没有一个具体的数字，根据以往的经验，一个表的索引最多不能超过6个，因为索引越多，对update和insert操作也会有性能的影响，涉及到索引的新建和重建操作。

　　第二：建立索引的方法论为：

多数查询经常使用的列；
很少进行修改操作的列；
索引需要建立在数据差异化大的列上
　　利用以上的基础我们讨论一下如何优化sql.

　　1、sql语句模型结构优化指导

　　　　a. ORDER BY + LIMIT组合的索引优化

　　　　　　如果一个SQL语句形如：SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort] LIMIT [offset],[LIMIT];

　　　　　　这个SQL语句优化比较简单，在[sort]这个栏位上建立索引即可。

　　　　b. WHERE + ORDER BY + LIMIT组合的索引优化

　　　　　　如果一个SQL语句形如：SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [VALUE] ORDER BY [sort] LIMIT [offset],[LIMIT];

　　　　　　这个语句，如果你仍然采用第一个例子中建立索引的方法，虽然可以用到索引，但是效率不高。更高效的方法是建立一个联合索引(columnX,sort)

　　　　c. WHERE+ORDER BY多个栏位+LIMIT

　　　　　　 如果一个SQL语句形如：SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;

　　　　　　 对于这个语句，大家可能是加一个这样的索引:(x,y,uid)。但实际上更好的效果是(uid,x,y)。这是由MySQL处理排序的机制造成的。

　　2、复合索引(形如(x,y,uid)索引的索引)

　　　　先看这样一条语句这样的：select * from users where area =’beijing’ and age=22;

　　 　 如果我们是在area和age上分别创建索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果area，age两列上创建复合索引的话将带来更高的效率。

    　　  在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

　　　 例如我们建立了一个这样的索引（area,age,salary），那么其实相当于创建了（area,age,salary）,(area,age),(area)三个索引，这样称为最佳左前缀特性。

　　3、like语句优化

SELECT id FROM A WHERE name like '%abc%'
 　　由于abc前面用了“%”，因此该查询必然走全表查询，除非必要，否则不要在关键词前加%，优化成如下

SELECT id FROM A WHERE name like 'abc%'
 　　4、where子句使用 ！= 或 <> 操作符优化

　　在where子句中使用 ！= 或 <>操作符，索引将被放弃使用，会进行全表查询。

 　　如SQL:SELECT id FROM A WHERE ID != 5 优化成：SELECT id FROM A WHERE ID>5 OR ID<5

　　5、where子句中使用 IS NULL 或 IS NOT NULL 的优化

　　在where子句中使用 IS NULL 或 IS NOT NULL 判断，索引将被放弃使用，会进行全表查询。

　　 如SQL:SELECT id FROM A WHERE num IS NULL 优化成num上设置默认值0，确保表中num没有null值，然后SQL为：SELECT id FROM A WHERE num=0

　　6、where子句使用or的优化

　　很多时候使用union all 或 nuin(必要的时候)的方式替换“or”会得到更好的效果。where子句中使用了or,索引将被放弃使用。

　　 如SQL:SELECT id FROM A WHERE num =10 or num = 20 优化成：SELECT id FROM A WHERE num = 10 union all SELECT id FROM A WHERE num=20

　　7、where子句使用IN 或 NOT IN的优化

　　in和not in 也要慎用，否则也会导致全表扫描。

　　 方案一：between替换in

　　 如SQL:SELECT id FROM A WHERE num in(1,2,3) 优化成：SELECT id FROM A WHERE num between 1 and 3

　　 方案二：exist替换in

　　 如SQL:SELECT id FROM A WHERE num in(select num from b ) 优化成：SELECT num FROM A WHERE num exists(select 1 from B where B.num = A.num)

　　 方案三：left join替换in

　　 如SQL:SELECT id FROM A WHERE num in(select num from B) 优化成：SELECT id FROM A LEFT JOIN B ON A.num = B.num

　　8、where子句中对字段进行表达式操作的优化

　　不要在where子句中的“=”左边进行函数、算数运算或其他表达式运算，否则系统将可能无法正确使用索引。

　　 如SQL:SELECT id FROM A WHERE num/2 = 100 优化成：SELECT id FROM A WHERE num = 100*2

　　 如SQL:SELECT id FROM A WHERE substring(name,1,3) = 'abc' 优化成：SELECT id FROM A WHERE LIKE 'abc%'

　　 如SQL:SELECT id FROM A WHERE datediff(day,createdate,'2016-11-30')=0 优化成：SELECT id FROM A WHERE createdate>='2016-11-30' and createdate<'2016-12-1'

　　 如SQL:SELECT id FROM A WHERE year(addate) <2016 优化成：SELECT id FROM A where addate<'2016-01-01'

　　9、任何地方都不要用 select * from table ，用具体的字段列表替换"*"，不要返回用不到的字段　　

　　10、使用“临时表”暂存中间结果

　　　　采用临时表暂存中间结果好处：

　　　　　　（1）避免程序中多次扫描主表，减少程序执行“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。

　　　　　　（2）尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

　　　　　　（3）避免频繁创建和删除临时表，以减少系统资源的浪费。

　　　　　　（4）尽量避免向客户端返回大数据量，若数据量过大，应考虑相应需求是否合理。

　　11、limit分页优化

　　　　当偏移量特别时，limit效率会非常低

　　　　SELECT id FROM A LIMIT 1000,10   很快

　　　　SELECT id FROM A LIMIT 90000,10 很慢

　　　　优化方法：

　　　　方法一：select id from A order by id limit 90000,10; 很快，0.04秒就OK。 因为用了id主键做索引当然快

　　　　方法二：select id,title from A where id>=(select id from collect order by id limit 90000,1) limit 10;

     　　 方法三：select id from A order by id  between 10000000 and 10000010;

　　12、批量插入优化

INSERT into person(name,age) values('A',14) INSERT into person(name,age) values('B',14) INSERT into person(name,age) values('C',14)
　　　　可优化为：

INSERT into person(name,age) values('A',14),('B',14),('C',14),
　　13、利用limit 1 、top 1 取得一行

　　　　有时要查询一张表时，你知道只需要看一条记录，你可能去查询一条特殊的记录。可以使用limit 1 或者 top 1 来终止数据库索引继续扫描整个表或索引。

　　　　如SQL：SELECT id FROM A LIKE 'abc%' 优化为：SELECT id FROM A LIKE 'abc%' limit 1

　　14、尽量不要使用 BY RAND()命令

　　　　BY RAND()是随机显示结果，这个函数可能会为表中每一个独立的行执行BY RAND()命令，这个会消耗处理器的处理能力。

　　　　如SQL：SELECT * FROM A order by rand() limit 10 优化为：SELECT * FROM A WHERE id >= ((SELECT MAX(id) FROM A)-(SELECT MIN(id) FROM A)) * RAND() + (SELECT MIN(id) FROM A) LIMIT 10

　　15、排序的索引问题　

　　 　Mysql查询只是用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求情况下不要使用排序操作；

　　　尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

　　16、尽量用 union add 替换 union

　　　union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union

　　17、避免类型转换

　　　这里所说的“类型转换”是指where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换。人为的上通过转换函数进行转换，直接导致mysql无法使用索引。如果非要转型，应该在传入参数上进行转换。

       　例如utime 是datetime类型，传入的参数是“2016-07-23”，在比较大小时通常是 date（utime）>"2016-07-23",可以优化为utime>"2016-07-23 00：00：00"

　　18、尽可能使用更小的字段　　　　　　 　　

　　　　MySQL从磁盘读取数据后是存储到内存中的，然后使用cpu周期和磁盘I/O读取它，这意味着越小的数据类型占用的空间越小，从磁盘读或打包到内存的效率都更好，但也不要太过执着减小数据类型，要是以后应用程序发生什么变化就没有空间了。

　　　　修改表将需要重构，间接地可能引起代码的改变，这是很头疼的问题，因此需要找到一个平衡点。

　　19、Inner join 和 left join、right join、子查询

　　第一：inner join内连接也叫等值连接是，left/rightjoin是外连接。

　　　　 SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;

　　　　 SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;

　　　　 SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;

　　 　　经过来之多方面的证实inner join性能比较快，因为inner join是等值连接，或许返回的行数比较少。但是我们要记得有些语句隐形的用到了等值连接，如：

　　　　SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;

　　　　推荐：能用inner join连接尽量使用inner join连接

　　 第二：子查询的性能又比外连接性能慢，尽量用外连接来替换子查询。

　　　　Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);

　　　　A表的数据为十万级表，B表为百万级表，在本机执行差不多用2秒左右，我们可以通过explain可以查看到子查询是一个相关子查询(DEPENDENCE SUBQUERY);Mysql是先对外表A执行全表查询，然后根据uuid逐次执行子查询，如果外层表是一个很大的表，我们可以想象查询性能会表现比这个更加糟糕。

      　　一种简单的优化就是用innerjoin的方法来代替子查询，查询语句改为：

  　　　Select* from A inner join B ON A.uuid=B.uuid using(uuid) where b.uuid>=3000;  这个语句执行测试不到一秒；

　　第三：使用JOIN时候，应该用小的结果驱动打的结果（left join 左边表结果尽量小，如果有条件应该放到左边先处理，right join同理反向），同时尽量把牵涉到多表联合的查询拆分多个query (多个表查询效率低，容易锁表和阻塞)。如：

　　Select * from A left join B A.id=B.ref_id where  A.id>10;可以优化为：select * from (select * from A wehre id >10) T1 left join B on T1.id=B.ref_id;

　　20、exist 代替 in

SELECT * from A WHERE id in (SELECT id from B)
SELECT * from A WHERE id EXISTS(SELECT 1 from A.id= B.id)
　　in 是在内存中遍历比较

　　exist 需要查询数据库，所以当B的数据量比较大时，exists效率优于in.

　　in()只执行一次，把B表中的所有id字段缓存起来，之后检查A表的id是否与B表中的id相等，如果id相等则将A表的记录加入到结果集中，直到遍历完A表的所有记录。

　　In 操作的流程原理如同一下代码

    List resultSet={};

    Array A=(select * from A);
    Array B=(select id from B);

    for(int i=0;i<A.length;i++) { for(int j=0;j<B.length;j++) { if(A[i].id==B[j].id) { resultSet.add(A[i]); break; } } } return resultSet; 
　　可以看出，当B表数据较大时不适合使用in()，因为它会B表数据全部遍历一次

　　如：A表有10000条记录，B表有1000000条记录，那么最多有可能遍历10000*1000000次，效率很差。

　　再如：A表有10000条记录，B表有100条记录，那么最多有可能遍历10000*100次，遍历次数大大减少，效率大大提升。

　　结论：in()适合B表比A表数据小的情况

　　exist()会执行A.length()次，执行过程代码如下

    List resultSet={};
    Array A=(select * from A);
    for(int i=0;i<A.length;i++) {
       if(exists(A[i].id) { //执行select 1 from B where B.id=A.id是否有记录返回  resultSet.add(A[i]); } } return resultSet;
　　当B表比A表数据大时适合使用exists()，因为它没有那么多遍历操作，只需要再执行一次查询就行。

　　如：A表有10000条记录，B表有1000000条记录，那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。

　　如：A表有10000条记录，B表有100000000条记录，那么exists()还是执行10000次，因为它只执行A.length次，可见B表数据越多，越适合exists()发挥效果。

　　再如：A表有10000条记录，B表有100条记录，那么exists()还是执行10000次，还不如使用in()遍历10000*100次，因为in()是在内存里遍历比较，而exists()需要查询数据库，

　　我们都知道查询数据库所消耗的性能更高，而内存比较很快。

　　结论：exists()适合B表比A表数据大的情况

　　 当A表数据与B表数据一样大时，in与exists效率差不多，可任选一个使用。