MySql知识

#A表比B表大
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)

#B表比A表大
SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)

#json字段更新
UPDATE car SET emission_standard = '[]' WHERE JSON_CONTAINS(emission_standard, 'null');

explain（极客时间）

id：每个执行计划都有一个 id，如果是一个联合查询，这里还将有多个 id。

select_type：表示 SELECT 查询类型，常见的有 SIMPLE（普通查询，即没有联合查询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERY（子查询）等。

table：当前执行计划查询的表，如果给表起别名了，则显示别名信息。

partitions：访问的分区表信息。

type：表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标，结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。

system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据。

eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件。

ref：非唯一索引扫描，还可见于唯一索引最左原则匹配扫描。

range：索引范围扫描，比如，<，>，between 等操作。

index：索引全表扫描，此时遍历整个索引树。

ALL：表示全表扫描，需要遍历全表来找到对应的行。

possible_keys：可能使用到的索引。

key：实际使用到的索引。

key_len：当前使用的索引的长度。

ref：关联 id 等信息。

rows：查找到记录所扫描的行数。

filtered：查找到所需记录占总扫描记录数的比例。

Extra：额外的信息。

SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]

type参数：
| ALL：显示所有开销信息
| BLOCK IO：阻塞的输入输出次数
| CONTEXT SWITCHES：上下文切换相关开销信息
| CPU：显示CPU的相关开销信息 
| IPC：接收和发送消息的相关开销信息
| MEMORY ：显示内存相关的开销，目前无用
| PAGE FAULTS ：显示页面错误相关开销信息
| SOURCE ：列出相应操作对应的函数名及其在源码中的调用位置(行数) 
| SWAPS：显示swap交换次数的相关开销信息

MySQL索引原理及慢查询优化

https://tech.meituan.com/2014/06/30/mysql-index.html

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0.

一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录.

利用存储过程多次插入数据

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

直接储存

BEGIN
    declare i int;  # declare语句是在复合语句中声明变量的指令
    set i=1;
    while i<=2000000 do
       insert into `user`(u_name,u_password,u_mail,u_phone,u_sex,u_headImg) values('赵六','000000','zhaoliu@163.com','18800000000',0,'oss.file.com/images/zhaoliu.png');
        set i=i+1;
    end while;
END

优化特定类型的查询

优化COUNT()查询

如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数（不统计NULL）。最好使用COUNT(*)，它会忽略所有的列而直接统计所有的行数。

优化关联查询

确保ON或者USING字句中的列上有索引。

确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。

当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会变成笛卡尔积，不同类型的关联可能会生成不同的结果等。

优化子查询

关于子查询优化我们给出的最重要的优化建议就是尽可能使用关联查询代替

优化GROUP BY和DISTINCT

使用索引优化

如果需要对关联查询做分组（GROUP BY），并且是按照查找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率会更高。

优化LIMIT分页

一个非常常见又令人头疼的问题就是，在偏移量非常大的时候，查询的代价非常高。优化的一个简单方法就是尽可能地使用索引覆盖扫描，而不是查询所有的列，然后再根据需要做一次关联操作再返回所需的列。

优化UNION查询

除非确实需要消除重复的行，否则就一定要使用上UNION ALL，这一点很重要。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。