MySQL 平时学习记录

官方文档是最好的资料, 查询资料时可以使用搜索功能，但有时候搜出来的结果很难找到自己想要查询的内容，这时候可以在这个页面全局搜索关键词，逐一查看。

卍，小知识点记录列表：

※，----------------------------

update影响的行为0时，update_time是不会变化的。
类型为varchar，查询时使用int类型，会影响索引的使用！很多慢查询都是此原因导致的。
from子句中使用了子查询，则这个子查询生成的表叫做 derived table(派生表),必须有别名。where子句中使用了子查询，这个子查询生成的表不是派生表，无需指定别名，where子句中使用了子查询则这个查询叫做半连接(semi-join)，参见下面关于半连接的叙述。

※， select * from tb_name where 1【不为零即可】;则会显示所有记录，select * from tb_name where 0;则不显示任何记录

假设数据库中的一个字段aaa的值有1,2,3。当查询语句为 select * from tb_name where aaa & 1; 查询过程为：每条记录中的aaa字段和1做位与运算&;结果不为0则显示，为零则不显示。比如aaa为1或3 会查询出来，aaa为2 则不会查询出来。

一个应用：1代表女；2代表男；有个字段为鞋码按性别分类【sex】，分为男、女以及男女通用。查询时若要查出所有的女性鞋码，一个优雅的　　方法是：鞋码中的女设置为1，男设为2，男女通用为3。

则查询所有女性鞋码：select * from tb_name where sex & 1；　查询所有男性鞋码：select * from tb_name where sex & 2;

※，druid dataSource。

MySQL服务端有一个配置项，作用是某个连接和服务端保持的最长时间，默认是8小时。如果一个连接和MySQL服务端连接超过8小时，MySQL服务端就会强制断开此连接。这个连接就是不可用的了。在使用@Transaction注解时要注意，注解使用不当，可能导致会出现这种错误。使用注解后，整个方法中的MySQL连接始终是同一个而且一直处于工作状态(不是idle，无法通过testWhileIdle配置检测其是否有效),这样如果超过了8小时，MySQL会强制断开此连接，导致此连接不再可用。

Java Druid连接池的一些配置参数作用如下

testOnBorrow: 在从连接池中取到一个连接时，先检测这个连接是否仍然是有效的连接，如果不是则会new一个新的连接。

testWhileIdel: 在连接池中的某个连接处于空闲时，检测是是否仍然有效，如果无效则回收，并new一个新的连接，一般设置testWhileIdel为true，设置了这个，testOnBorrow就可以设为false了

※，MySQL查询条件where中使用函数时，务必小心！使用了函数后很有可能就无法使用索引了。

如果你必须往条件语句里加函数进行计算，务必考虑对查询时长的影响，并且explain一下看看是否使用了索引。

WHERE UNIX_TIMESTAMP(add_time) > 12345这样的条件下，当存在add_time索引时它并不会被启用。但这并不是绝对的，比如你有个tinyint字段，建立索引后进行WHERE abs(age) > 50它又能使用索引。所以并不是所有情况下使用函数都会影响DB使用索引的选择。

个人主张MySql做好数据存取的职责，数据计算的事交给编程语言；在大并发查询的情况下，后面还有一堆查询语句等着处理，你却让MySql把时间花在数据计算上，这根本不值得，赶紧让MySql把数据交给编程语言层面，让编程语言进行计算，然后MySql就能马上处理下一个查询语句了。

※，半连接（semi-join）:

参考文章

半连接主要场景：检查一个结果集（外表）的记录是否在另外一个结果集（子表）中存在匹配记录，半连接仅关注”子表是否存在匹配记录”，而并不考虑”子表存在多少条匹配记录”，半连接的返回结果集仅使用外表的数据集，查询语句中IN或EXISTS语句常使用半连接来处理。半连接查询需要满足一定的条件，比如子查询中不能有带有limit的 order by语句等，具体叙述可查看官网。

MySQL支持5中Semi-join策略: 1、Duplicate Weedout 2、First Match3、LooseScan、4、Materialize lookup 5、MaterializeScan。MySQL优化器会自动使用这5种策略来处理半连接的查询。

※，分组后取每个分组中最新的一条(或者按照自定义的规则排序) ：

※，方法一：使用MySQL内置函数，不推荐，因为MySQL函数效率比较低，大概率无法使用索引优化。

select SUBSTRING_INDEX(group_concat(id ORDER BY id desc), ",", 1) as id, exam_id, subject_id, content, state, SUBSTRING_INDEX(group_concat(createTIme ORDER BY id desc), ",", 1) createTime from ei_report_message where exam_id in ("75","77","88") and type = "knowledge" GROUP BY exam_id, subject_id; // 这种方式t也比较笨重，需要对每个字段进行特殊处理，不处理的字段可能不是想要的结果。

※，方法二：★★★★★★★★★推荐

首先需要明白MySQL的执行顺序：

　 1.from

2.where

3.group by…… having

4.聚合函数(max，min， count，sum)

5.select

6.order by

因为先group by后order by，所以我们只要保证先执行order by 再执行group by 就行了那么思路有了咋做呢？非常简单把order by 的结果集提升到from这个第一的级别就行了。SQL如下：

select * from ( select * from ei_report_message where exam_id in ("75","77","88") and type = "knowledge" order by id desc) tmp group by tmp.exam_id, tmp.subject_id;

然而，在MySQL5.7上尝试了一下，并没有成功，group by后取出来的不是每组ID最大的那一条!!! 但是这个语句在MySQL5.6上是有效果的。

要在MySQL5.7上生效，需要在子查询中 order by 后添加 limit。即：

·select * from ( select * from ei_report_message where exam_id in ("75","77","88") and type = "knowledge" order by id desc limit 100) tmp group by tmp.exam_id, tmp.subject_id;·

这样分组后取出来的就是ID最大的那一条了！！！

通过 explain 查看执行计划，可以看到没有 limit 的时候，少了一个 DERIVED 操作。DERIVED用于派生表的SELECT(FROM子句的子查询)，所以没有它就相当于子句里的排序并没有被执行。

※，2

※，3

※，4

※，5

※，6

卍，MySQL 存储过程和触发器和事件(定时器)：

※，概述：

存储过程作用是封装SQL，供其他地方使用(普通SQL可以调用存储过程，触发器也可以调用，事件也可以调用)。
触发器的作用是当插入、更新、删除记录时(更具体的是之前或之后)执行一些操作。
事件(定时器)的作用是在某个时间点或每隔一定时间间隔执行一些操作。

※，trigger：数据库information_schema中的TRIGGERS数据表中记录了数据库中的所有触发器。

例子1：需求：当数据库中date类型的字段 deadline 的值小于等于当前日期时，将另外一个字段status的值置为0.

delimiter $$
CREATE TRIGGER `deadLine` BEFORE UPDATE ON `ei_user_auth` FOR EACH ROW 
BEGIN
    IF new.deadline <= CURRENT_DATE() THEN
        set NEW.status=0;
    END IF;
END;$$
delimiter ;

注：官方一篇文档

new.deadline和new.status中的new是MySQL中的关键字，表示的是更新后的记录，相应的old表示更新前的记录。只有update 触发器才同时有old和new，insert只有new，delete只有old。
只有before update才能更改new.<field>的值，更改后的此值会被后续update语句更新到数据库记录中。after update无法更改new.<field>的值，因为after update的时候，已经执行过update操作了，数据库记录已经被更新了，没有更改的机会了。

※，event：数据库information_schema中的 EVENTS 数据表中记录了数据库中的所有定时任务。

例子1：需求：每天检查一下数据库，当数据库中 date类型的字段 deadline的值小于等于当前日期时，将另外一个字段status的值置为0.

delimiter $$
create PROCEDURE updateDeadline()
BEGIN
    update ei_user_auth set `status`=0 where deadline <= CURRENT_DATE();
END$$
delimiter ;

create EVENT updateDeadline
ON SCHEDULE EVERY 1 DAY STARTS '2021-04-20 00:03:00'
ON COMPLETION PRESERVE ENABLE
do CALL updateDeadline();

注：第一步创建一个名为 updateDeadline() 的存储过程，第二步创建一个名称为updateDeadline 的定时任务。

ON SCHEDULE EVERY 1 DAY STARTS '2021-04-20 00:03:00' // 每天执行一次，从2021-04-20 00:03:00'开始。也就是每天00:03:00执行一次。
- AT '2021-04-25 09:33:17' // 在特定时间执行一次
- AT '2021-04-25 09:33:17' + INTERVAL 1 DAY // INTERVAL表示距离现在多久，此据表示一天之后的某个时间执行一次
- AT CURRENT_TIMESTAMP + INTERVAL 10 SECOND / / 距离当前时间10秒后执行一次
- EVERY 10 SECOND //每10s执行一次，注意EVERY子句不和 + INTERVAL子句一起使用
- EVERY 1 HOUR STARTS CURRENT_TIMESTAMPE + INTERVAL 1 DAY;// 距离现在1天之后开始执行，然后每一小时执行一次
- EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL '6:15' HOUR_MINUTE. // 每两周执行一次，首次执行于距离现在6小时15分钟之后
- EVERY 2 HOUR STARTS '2021-04-25 09:52:15' ENDS '2021-04-25 09:52:47' // 在第一个日期和第二个日期之间，每两小时执行一次
- EVERY 2 HOUR STARTS CURRENT_TIMESTAMP ENDS date_add(CURRENT_TIMESTAMP, INTERVAL 10 DAY) // 从当前到10天后期间，每2小时执行一次。
ON COMPLETION PRESERVE ENABLE //表示此定时任务创建后立即生效，DISABLE则表示创建后不会生效
- alter event updateDeadline on completion preserve enable; //开启定时任务
- alter event updateDeadline on completion preserve disable; //关闭定时任务
do CALL updateDeadline() //do后为待执行的SQL语句，此处为调用存储过程。

卍，索引介绍：写完SQL只是完成了工作的一半，explain SQL语句分析完毕后才算写完了SQL！

※，参考此转载文章：总结下explain结果的type的其中6中类型：all,index,range,ref, eq_ref ，const，从左至右效率依次增强。

all:“全表扫描”,意味着你的sql语句处于一种最原生的状态，有很大的优化空间。即使幸运地第一次就查到了想要的结果，但是依然要全表扫描，因为不确定是否还有其他符合条件的结果。

index: 另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。有两种情形：①如果需要查找的数据在都在索引中，那么就无需回表取数据，直接用索引树中的数据即可，这叫做索引覆盖，此时explain中extra字段中会有Using Index值。索引覆盖时index类型会比ALL类型要快。②如果需要查找的数据不全在索引中，那么就需要回表取数据，此时extra字段没有返回值。速度应该不会比ALL类型快(因为index要先读索引而且要回表随机取数据)。

官方原话（https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-join-types）：
The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:  If the index is a covering index for the queries and can be used to
satisfy all data required from the table,only the index tree is scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than ALL 
because the size of the index usually is smaller than the table data. A full table scan is performed using reads from the index to look up data rows in index order（这句话表明要回表).
Uses index  does not appear in the Extra column.

range：range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。出现了range，则一定是基于索引的。常见的索引范围扫描的查询关键字有：·between and, >, <, in, or·等。
ref：ref类型表示查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。
eq_ref : eq_ref与ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，
const:通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。

※，force index(index1, index2,...)强制使用索引。ignore index(index1, index2, ...)忽略索引

卍，