mysql left 查询 优化的思考

mysql left 查询 优化的思考：

 

1.对左表添加条件限制需在where条件中添加，不能放到on后面
2.对右表添加条件限制需放到on后面，在where条件中添加会有数据行数差异出现问题。

 

原sql语句：

SELECT m.*,o.OrderSourceName
FROM table_1 m
Left JOIN table_2 o ON m.CustomerId = o.CustomerId AND m.OrderSourceId = o.OrderSourceId
WHERE m.CustomerId=1 AND m.MarketId=1 AND m.Date>=20220328 AND m.Date<=20220411
ORDER BY m.Id DESC

 

mysql优化：
1. left 查询，可以先根据查询条件查询出主表的id等left join 条件数据，缩小数据的范围，在left 查询
2. m.CustomerId=1 AND m.MarketId=1 表里的数据都一样可以去掉，以免影响索引查询；
3. 查询条件单一的，可以强制索引force index(idx_date)

 

优化后的语句：从10分钟到优化后的12秒
select m.Id,m.CustomerId,m.OrderSourceId,o.OrderSourceName from
(
select Id, CustomerId,OrderSourceId from table_1 force index(idx_date) WHERE Date>='20220328' AND Date<='20220411'
) m
Left JOIN table_2 o
ON m.CustomerId = o.CustomerId AND m.OrderSourceId = o.OrderSourceId
ORDER BY Id DESC limit 1,20

 ---------------------------------------------------

用法： Explain+SQL语句。

 

用于分析建立索引后SQL的性能分析。

select_type属性

select_type属性	含义
SIMPLE	简单的 select 查询,查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
DERIVED	在FROM列表中包含的子查询被标记为DERIVED(衍生) MySQL会递归执行这些子查询, 把结果放在临时表里。
SUBQUERY	在SELECT或WHERE列表中包含了子查询
DEPEDENT SUBQUERY	在SELECT或WHERE列表中包含了子查询,子查询基于外层
UNCACHEABLE SUBQUERY	无法使用缓存的子查询
UNION	若第二个SELECT出现在UNION之后，则被标记为UNION； 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT	从UNION表获取结果的SELECT

type

查询访问的类型，重要指标，指示出索引是否发挥作用。

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

其中const是常数性能，eq_ref是索引后唯一，ref是索引+遍历混合，一般需要这个等级的type才比较高效。后面的，range是指定了范围，例如between，ALL是原始遍历。

possible_keys，key，key, key_len, rows

• possible_keys,指示出相关的所有索引，但只会使用最快的，所以需要从后面几个关键属性中分析。
• key，实际使用的索引
• key_len，实际使用索引长度
• rows，实际查询行数

extra

其他重要信息，例如使用了where，group by，join等等。

Sql索引匹配策略

 

由于只在部分属性上建立索引，随之而来的问题就是，多个属性的索引的匹配顺序，类型转换，通配符匹配等等情况下的索引匹配策略。

不适合建立索引的情况

• 记录太少，没有必要
• 增删多，查询少，维护索引每个操作都需要logN
• where条件里用不到的属性不必要索引
• 过滤性不好的属性不需要索引，例如男女，数据无区分度

索引匹配策略

开头提到的诸多比较复杂的场景，MySql中其实是一刀切，选了非常简单的策略。

• 按建立索引时的属性顺序进行匹配，分层索引。建立索引时的顺序很重要，因为MySql中的匹配顺序就是按照该顺序进行最大匹配的，若是中间有属性没匹配到，则后面的索引不会生效
• 对建立索引的属性的所有操作，都会导致索引失效，例如类型转换，函数调用
• 对某个索引的属性进行范围操作，将使得后边的索引失效
• 不等于，not in将使得索引失效
• 通配符无法使用索引，索引尽量把通配符写在字符串后面，索引会匹配到通配符为止。
• or无法使用索引，应该使用union或union all代替，ps，union all是无去重的版本，但是速度快很多，如果知道不会有重复，应该使用后者
• 尽量覆盖索引，不要select *

 ---------------------------------------------------

依次从最优到最差分别为：
system > const > eq_ref > ref >range > index > all
一般来说，保证查询达到range级别，最好达到ref

 

system: 只有一条数据匹配时，是const的特列
const:只有一条数据匹配时
eq_ref:主键索引，唯一索引，找到自己返回
ref: 普通索引，把索引数据都排查一遍
range: in,between, < ,>,>=
index:扫描全部索引--内存操作
all：扫描全表--硬盘

 

------------

MySQL数据库cpu飙升到100%的话怎么处理？

当 cpu 飙升到 100%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的。

如果不是，找出占用高的进程，并进行相关处理。

如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确，index 是否缺失，或者实在是数据量太大造成。

一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)， 等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。

也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升， 这种情况就需要跟应用一起来分析为何连接数会激增， 再做出相应的调整，比如说限制连接数等。