mysql-sql

• explain
  分析sql语句的执行情况常用分析字段：

  • key
    用的索引

  • type
    索引的使用类型 大致分为 从上到下排序会靠上性能越好
    system 比较少见，表记录比较少，索引一次命中 主键或者唯一索引 hash
    const eq_ref hash = 直接被查到唯一的数据 直接被查到唯一的数据 b+
    ref range 范围查询在索引上 进行范围查询
    index索引全部扫描
    all 没有用到索引 全表扫描

  • select_type
    代表查询的是否子查询 联合查询
    simple简单查询没有
    primary 子查询 外层
    subQuery 子查询 里层
    union 联合查询

  • extra
    排序和范围查询
    fileSort 文件排序 file temporary临时表 需要优化的没有合理利用索引
    useing where 进行了范围查询
    useing index

  • key_lens
    用到索引的字节长度，一般是越小说明性能越好，因为扫描索引长度小。

  • possible_key
    可能会用到的索引

  • id
    代表一个唯一的一次执行，一个sql子查询或者join查询会分为几次执行。
    id从大到小优先执行。
    https://mp.weixin.qq.com/s/yPA8v5qTMatlw3LCY-orSQ
    http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html

• Optimizer_trace

  • 简单的update a set a=b where bid in (select id where id > 1000) SQL中因为SQL优化器把in 优化成exists导致a表变成了主键扫全表。
    https://juejin.cn/post/7043782238961401887

• icp（index condition pushdown）

  • 5.6之后支持，ab字段联合索引。
    where中 b<5 and b>1 and c >5 and d!='d'，之前只能在联合索引中过滤出b条件数据，icp之后b和a的过滤条件都可以在联合索引中实现。

• 索引失效

  • 比如varchar字段a一下情况索引都会失效：
    where a = 1 || a + 1 = 1 || a like "%a" || length(a) = 10
  • a or b，b没有索引。
  • a in (1,2) 可以换成 union。
  • orderby和where不一致。
  • 联合索引，abc，用到a就可以。
  • join字段类型长度一致。

• 索引(use index.ignore index.force index)

• 单表操作

  • tk.mybatis mybatisplus支持 不用写xml sql
  • 好迁移 其他数据库。
  • 一般业务的压力都是再db，db的资源最为重要了，减少压力，尽量java来处理

• 避免使用 select *，列出需要查询的字段

  • 增加mysql解析的开销 先查这个表有哪些字段。
  • 不能利用索引 如果只差一列 name并且有索引 可以select name 减少io次数。
  • 增加了网络 传输开销。

• 分页查询优化 加where id > 10000条件 下一页 记录上一页的页数。

• TRUNCATE
  concat(TRUNCATE(ord.app_rate/100, 2),'%') AS excelRate1 当app_rate为null时 返回就是null

• mysql 用union 链接两部分数据 显示错误：
  

  
  解决方案：
  两段的select 字段顺序不一样。。

• id in() 最多1000个

• join left b表 on 后面只加 关联条件 其他的过滤放到where里

• timestamp字段 可以在实现updatetime 和createTime
  https://www.cnblogs.com/panxuejun/p/6134993.html
  只能一个timestamp字段。。

• update 字段要用，隔开，用and隔开语法不报错
  UPDATE bz_order.t_order SET order_channel = 62 , agent_user_id = 1642457 , channel_no = 'daxiangplat'
  WHERE order_id IN ('202011281501361402','202011281515532412','202011281519182413','202011281527452416','202011281534411407','202011281543511412');
  多个update 字段要用，隔开，用and隔开语法不报错 但是执行后会字段值 会出错

• IF的用法

SELECT * FROM ae_agent_cust WHERE IF
    (
            DATE_FORMAT( birthday, '%m-%d' ) >= DATE_FORMAT( now( ), '%m-%d' ),
            DATE_FORMAT( birthday, '2020-%m-%d' ) >= DATE_FORMAT( now( ), '%Y-%m-%d' )
                AND DATE_FORMAT( birthday, '2020-%m-%d' ) <= '2021-01-05',
            DATE_FORMAT( birthday, '2021-%m-%d' ) >= DATE_FORMAT( now( ), '%Y-%m-%d' )
                AND DATE_FORMAT( birthday, '2021-%m-%d' ) <= '2021-01-05'
    ) AND agent_user_id = 1576768 ORDER BY birthday;

• 字符串链接需要用concat
• orderby + limit 在orderby字段有相同值的记录会随机排序 建议orderby a,id
  https://mp.weixin.qq.com/s/JQgDEzxUBsq8UXiGwrEc0A
• 是需要判断是否有数据 可以用limit 1 不用count (*)
  https://mp.weixin.qq.com/s/ZQOTKXw3dexVH9v5y6BLpA
  count()=count(1)>count(id)
  count(a)如果有索引那和1一样，没索引排最后。
• join
  的内连接、左连接、右连接有什么区别？
  内连接关键字：inner join；左连接：left join；右连接：right join。
  内连接是把匹配的关联数据显示出来；
  左连接是左边的表全部显示出来，右边的表显示出符合条件的数据；
  右连接正好相反。
  inner join不一定小表驱动大表。比如a.bid = b.id,a表bid没有索引。STRAIGHT_JOIN强行以左表为驱动。
  left join一般是左边驱动右表，但是where中有右表条件可能会优化为inner join。
  join_buffer_size默认256k，可以提高这个，提高效率。
  join_buffer存储的是指定的字段值不是全量。