mysql基础、优化

基础

if

IF( expr1 , expr2 , expr3 )
expr1 的值为 TRUE，则返回值为 expr2 
expr1 的值为FALSE，则返回值为 expr3

distinct

distinct多列：

select distinct a,b,c from tableA;
 
注意此时是将a,b,c三列所有不同的组合全部列出来，而不仅仅只是distinct a
相当于以下语句：

 select a,b,c from tableA group by a,b,c

and 、or

and优先级比or高

先执行vend_id=1003 and prod_price>=10，再执行or vend_id=1002

先执行or，加括号：

case when

根据时间统计季度销售额：

select
     sum( case  when  time between  "2021-01-01" and "2021-03-31"  then sales  end) as season1,
     sum(case  when  time between  "2021-04-01" and "2021-06-30"  then sales  end)  as season2,
     sum(case  when  time between  "2021-07-01" and "2021-09-30"  then sales  end ) as season3,
     sum(case  when  time between  "2021-10-01" and "2021-12-31"  then sales  end ) as season4
from userinfo2

case when主要用于查询出的字段上，进行结果区分；

select sex,
(CASE 
		WHEN sex = '1' THEN '男'
         WHEN sex = '2' THEN '女'
ELSE '其他' END) as sexname
from sys_student
=======或者====
select sex,
(CASE sex
		WHEN   '1'  THEN  '男'
         WHEN  '2'  THEN  '女'
ELSE '其他' END) as sexname
from sys_student 

https://www.cnblogs.com/pingzizhuanshu/p/14241179.html
https://blog.csdn.net/xu2034029667/article/details/116403437

行转列、列转行

行转列：

这是表中数据，要转成的效果是：

（典型的行转列）
SELECT 
name,
SUM(CASE SUBJECT WHEN '语文' THEN score end) as '语文',
SUM(CASE SUBJECT WHEN '数学' THEN score end) as '数学',
SUM(CASE SUBJECT WHEN '英语' THEN score end) as '英语' 
FROM student_grade
GROUP BY name

case when 行转列 列转行
https://www.jianshu.com/p/1c6fb0df9f58

列转行：

1. 使用case when 实现列转行：

2. 使用union all
   

SELECT userid,'语文' AS course,cn_score AS score FROM tb_score1
UNION ALL
SELECT userid,'数学' AS course,math_score AS score FROM tb_score1
UNION ALL
SELECT userid,'英语' AS course,en_score AS score FROM tb_score1
UNION ALL
SELECT userid,'政治' AS course,po_score AS score FROM tb_score1
ORDER BY userid

explain 查询索引情况

explain+ SQL语句：
id ：编号
select_type ：查询类型
table ：表
type ：访问类型
possible_keys ：预测用到的索引
key ：实际使用的索引
key_len ：实际使用索引的长度
ref ：表之间的引用
rows ：通过索引查询到的数据量
Extra ：额外的信息

id关键字：id值越大越优先；id值相同，从上往下顺序执行。
select_type：查询类型

1. simple：简单查询，不包含子查询，不包含union查询。
2. primary：包含子查询的主查询(最外层)
3. subquery：包含子查询的主查询(非最外层)
4. derived：衍生查询(用到了临时表)
5. union：union之后的表称之为union表，如上例 union result：告诉我们，哪些表之间使用了union查询

type ：访问类型，结果值从好到坏依次是：system > const > eq_ref > ref > range > index > ALL

1. system：表只有一行记录（等于系统表）
2. const类型的特例，基本不会出现，可以忽略不计const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引
3. eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描
4. ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质是也是一种索引访问
5. range：只检索给定范围的行，使用一个索引来选择行（至少要这个级别）
6. index：Full Index Scan，索引全表扫描，把索引从头到尾扫一遍
7. ALL：Full Table Scan，遍历全表以找到匹配的行
8. possible_keys：查询过程中有可能用到的索引
9. key：实际使用的索引，如果为NULL，则没有使用索引
10. rows，根据表统计信息或者索引选用情况，大致估算出找到所需的记录所需要读取的行数。
11. filtered：它指返回结果的行占需要读到的行(rows列的值)的百分比。表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。

sql解析顺序

编写过程

select dinstinct …from …join …on …where …group by …having …order by …limit …

解析过程

from … on… join …where …group by …having …select dinstinct …order by …limit …

sql执行过程

连接器

1）负责与客户端的通信，是半双工模式，这就意味着某一固定时刻只能由客户端向服务器请求或者服务器向客户端发送数据，而不能同时进行。

2）验证用户名和密码是否正确（数据库mysql的user表中进行验证），如果错误返回错误通知（deAcess nied for user ‘root’@‘localhost’（using password：YES）），如果正确，则会去 mysql 的权限表（mysql中的 user、db、columns_priv、Host 表，分别存储的是全局级别、数据库级别、表级别、列级别、配合 db 的数据库级别）查询当前用户的权限。

缓存（Cache）

也称为查询缓存，存储的数据是以键值对的形式进行存储，如果开启了缓存，那么在一条查询sql语句进来时会先判断缓存中是否包含当前的sql语句键值对，如果存在直接将其对应的结果返回，如果不存在再执行后面一系列操作。如果没有开启则直接跳过。
　　缓存失效场景：

1、查询语句不一致。前后两条查询SQL必须完全一致。

2、查询语句中含有一些不确定的值时，则不会缓存。比如 now()、current_date()、curdate()、curtime()、rand()、uuid()等。

3、不使用任何表查询。如 select ‘A’;

4、查询 mysql、information_schema 或 performance_schema 数据库中的表时，不会走查询缓存。

5、在存储的函数，触发器或事件的主体内执行的查询。

6、如果表更改，则使用该表的所有高速缓存查询都变为无效并从缓存中删除，这包括使用 MERGE 映射到已更改表的表的查询。一个表可以被许多类型的语句改变，如 insert、update、delete、truncate rable、alter table、drop table、drop database。

通过上面的失效场景可以看出缓存是很容易失效的，所以如果不是查询次数远大于修改次数的话，使用缓存不仅不能提升查询效率还会拉低效率（每次读取后需要向缓存中保存一份，而缓存又容易被清除）。所以在 MYSQL5.6默认是关闭缓存的，并且在 8.0 直接被移除了。当然，如果场景需要用到，还是可以使用的。

分析器

对客户端传来的 sql 进行分析，这将包括预处理与解析过程，并进行关键词的提取、解析，并组成一个解析树。具体的解析词包括但不局限于 select/update/delete/or/in/where/group by/having/count/limit 等，如果分析到语法错误，会直接抛给客户端异常：ERROR:You have an error in your SQL syntax.

比如：select * from user where userId =1234;

在分析器中就通过语义规则器将select from where这些关键词提取和匹配出来,mysql会自动判断关键词和非关键词，将用户的匹配字段和自定义语句识别出来。这个阶段也会做一些校验:比如校验当前数据库是否存在user表，同时假如User表中不存在userId这个字段同样会报错：unknown column in field list.

优化器

进入优化器说明sql语句是符合标准语义规则并且可以执行。优化器会根据执行计划选择最优的选择，匹配合适的索引，选择最佳的方案。比如一个典型的例子是这样的：

表T,对A、B、C列建立联合索引(A,B,C)，在进行查询的时候，当sql查询条件是:select xx where B=x and A=x and C=x.很多人会以为是用不到索引的，但其实会用到,虽然索引必须符合最左原则才能使用,但是本质上,优化器会自动将这条sql优化为:where A=x and B=x and C=X,这种优化会为了底层能够匹配到索引，同时在这个阶段是自动按照执行计划进行预处理,mysql会计算各个执行方法的最佳时间,最终确定一条执行的sql交给最后的执行器。

执行器

执行器会调用对应的存储引擎执行 sql。主流的是MyISAM 和 Innodb。

https://www.cnblogs.com/mengxinJ/p/14045520.html#_label2_1_0

索引字段

mysql需要加索引的字段：

1. 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引
2. 经常与其他表进行连接的表，在连接字段上应该建立索引
3. 分组字段或者排序字段应该创建索引
4. 选择性高的字段上应该建立索引。
5. 更新频繁的字段不适合创建索引，不会出现在 where 子句中的字段不应该创建索引。
6. 占用存储空间少的字段更适合选作索引的关键字。例如，与字符串相比，整数字段占用的存储空间较少，因此，较为适合选作索引关键字。
7. 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。

https://blog.csdn.net/weixin_39977886/article/details/114334531

sql优化

explain优化项

• type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
• key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。
• key_len列，索引长度。
• rows列，扫描行数。该值是个预估值。
• extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。

SQL语句中IN包含的值不应过多(对于连续的数值，能用between就不要用in)

MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from t where num in(1,2,3) 对于连续的数值，能用between就不要用in了；再或者使用连接来替换。

SELECT语句务必指明字段名称

SELECT*增加很多不必要的消耗（CPU、IO、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。

当只需要一条数据的时候，使用limit 1

这是为了使EXPLAIN中type列达到const类型。
如果我们已经知道得到的数据永远仅仅只有一条的话，那就可以加上 LIMIT 1，让数据库找到一条数据之后就立刻返回结果，这样就大大提升了性能！

如果排序字段没有用到索引，就尽量少排序

给需要排序的字段加索引。

如果限制条件中其他字段没有索引，尽量少用or

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。

尽量用union all代替union

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

union和union all的区别在于对重复数据的处理。

union 在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的集果集进行排序运算，删除重复的记录再返回结果集。实际使用时大部分是不会产生重复的记录。

union all只是简单的将两个结果合并就返回。如果返回的结果集中有重复的数据，那么返回的结果集中就包含有重复数据。

从性能上讲union all 要比union快很多，它没有排序去重的耗时。如果表数据量很大，并且可以确定合并的结果集中不会包含重复数据的话。就使用union all.

不使用ORDER BY RAND()

select id from dynamic order by rand() limit 1000;

上面的SQL语句，可优化为：

select id from dynamic t1 join (select rand() * (select max(id) from dynamic) as nid) t2 on t1.id > t2.nidlimit 1000;

区分in和exists、not in和not exists

in 和 exists区别

select * from 表A where id in (select id from 表B)

上面SQL语句相当于

select * from 表A where exists(select * from 表B where 表B.id=表A.id)

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

in查询也可以考虑是否能用连接查询代替。
关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句？

join

原SQL语句：

select colname … from A表 where a.id not in (select b.id from B表)

高效的SQL语句：

select colname … from A表 Left join B表 on where a.id = b.id where b.id is null

用union all取代in（使用union 则会对结果去重）

SELECT count(id) as id FROM task 
WHERE cid=100015	AND  
sid IN(112310,112316,106959,110780,112324,112331,112317)  
AND  flag='9'

优化后：

select sum(id) as id  from (
SELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=112310  AND  flag=9
union all
SELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=112316  AND  flag=9
union all
SELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=106959  AND  flag=9
union all
SELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=110780  AND  flag=9
union all
SELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=112324  AND  flag=9
union all
SELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=112331  AND  flag=9
union all
SELECT count(*) as id FROM task WHERE AND  cid=123456  AND  sid=112317  AND  flag=9
) t

使用合理的分页方式以提高分页的效率

select id,name from product limit 866613, 20

使用上述SQL语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。

优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。SQL可以采用如下的写法：

select id,name from product where id> 866612 limit 20

分段查询

在一些用户选择页面中，可能一些用户选择的时间范围过大，造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。

如下图这个SQL语句，扫描的行数成百万级以上的时候就可以使用分段查询：

避免在where子句中对字段进行null值判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描。

不建议使用%前缀模糊查询

例如LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

那如何查询%name%？

如下图所示，虽然给secret字段添加了索引，但在explain结果并没有使用：

那么如何解决这个问题呢，答案：使用全文索引。

在我们查询中经常会用到select id,fnum,fdst from dynamic_201606 where user_name like ‘%zhangsan%’; 。这样的语句，普通索引是无法满足查询需求的。庆幸的是在MySQL中，有全文索引来帮助我们。

创建全文索引的SQL语法是：

ALTER TABLE dynamic_201606 ADD FULLTEXT INDEX idx_user_name (user_name);

使用全文索引的SQL语句是：

select id,fnum,fdst from dynamic_201606 where match(user_name) against(‘zhangsan’ in boolean mode);

注意：在需要创建全文索引之前，请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别。

避免在where子句中对字段进行表达式操作

比如：

select user_id,user_project from user_base where age*2=36;

中对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成：

select user_id,user_project from user_base where age=36/2;

避免隐式类型转换

where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型。

对于联合索引来说，要遵守最左前缀法则

举列来说索引含有字段id、name、school，可以直接用id字段，也可以id、name这样的顺序，但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。

必要时可以使用force index来强制查询走某个索引

有的时候MySQL优化器采取它认为合适的索引来检索SQL语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用force index来强制优化器使用我们制定的索引。

注意范围查询语句

对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。

关于JOIN优化

LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B表为驱动表。

注意：

1）MySQL中没有full join，可以用以下方式来解决：

select * from A left join B on B.name = A.name
where B.name is null
union all
select * from B;

2）尽量使用inner join，避免left join：

参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。

3）合理利用索引：

被驱动表的索引字段作为on的限制字段。

4）利用小表去驱动大表：

从原理图能够直观的看出如果能够减少驱动表的话，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。

5）巧用STRAIGHT_JOIN：

inner join是由MySQL选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使用STRAIGHT_JOIN，否则可能造成查询结果不准确。