mysql 优化总结

1. 避免语句隐式转换

• 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回1，这两种情况都不需要做类型转换
• 两个参数都是字符串，会按照字符串来比较，不做类型转换
• 两个参数都是整数，按照整数来比较，不做类型转换
• 十六进制的值和非数字做比较时，会被当做二进制串
• 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
• 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
  所有其他情况下，两个参数都会被转换为浮点数再进行比较

官方文档：http://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

2.左侧避免使用函数，公式

SELECT COUNT(*)
  FROM `tp_users`
 where from_unixtime(`createtime`, '%Y-%m')= '2020-04';

3.覆盖索引，不回表
4.小表驱动大表

SELECT a.token,
b.username,
b.company,
count(*)
FROM `tp_lifecircle_consume` a left join tp_users b on a.token= b.users_token
where a.pay_time between unix_timestamp('2020-04-20 00:00:00')
and unix_timestamp('2020-04-20 23:59:59')
and a.pay_status= 1
and a.agent_id= 15947
group by token;

-- 改写
-- 优化的目标是尽可能减少JOIN中Nested Loop的循环次数
-- 算法参考：https://www.cnblogs.com/wingsless/p/5034583.html

SELECT a.token,
b.username,
b.company,
count(*)
FROM tp_users b
inner join `tp_lifecircle_consume` a on a.token= b.users_token
where a.pay_time between unix_timestamp('2020-04-20 00:00:00')
and unix_timestamp('2020-04-20 23:59:59')
and a.pay_status= 1
and b.belong= 15947
and b.parent_id= 0
group by token;

5.分页场景

• 利用延迟关联或者子查询优化超多分页场景。
• 说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。
• 正例：先快速定位需要获取的 id 段(此过程需要使用到覆盖索引的概念)，然后再关联：

#慢
SELECT * FROM table_01 limit 100000, 20;

#快
SELECT 
  a.* FROM table_01 a
join 
  (select 
      id 
     from table_01 
     where where_condition 
     limit 100000,
           20
    ) b where a.id=b.id

6.count误区

• COUNT函数的用法，主要用于统计表行数。主要用法有COUNT(*)、COUNT(字段)和COUNT(1)。
• 因为COUNT()是SQL92定义的标准统计行数的语法，所以MySQL对他进行了很多优化，MyISAM中会直接把表的总行数单独记录下来供COUNT()查询，而InnoDB则会在扫表的时候选择最小的索引来降低成本。当然，这些优化的前提都是没有进行where和group的条件查询。
  在InnoDB中COUNT(*)和COUNT(1)实现上没有区别，而且效率一样，但是COUNT(字段)需要进行字段的非NULL判断，所以效率会低一些。
• 因为COUNT()是SQL92定义的标准统计行数的语法，并且效率高，所以请直接使用COUNT()查询表的行数！

7. 建组合索引的时候，区分度最高的在最左边。

• 说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where a>? and b=? 那么即使 a
  的区分度更高，也必须把 b 放在索引的最前
• 正例：如果 where a=? and b=? ，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。

8.使用 ISNULL()来判断是否为 NULL 值。
9.在代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。
10.in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内。
11.减少使用or语句，可将or语句优化为union all，然后在各个where条件上建立索引。

select * from table_01 where a = 1 or b=2;

优化为：
select * from table_01 where a = 1 
union all 
select * from table_01 where a = 2;