mysql应用速查

mysql优化实践

为和“数据库设计”笔记区分，本篇侧重于记录“事后”的优化方法。在“数据库设计”中记录建库时的注意事项

explain 关键字，explain+sql语句，会输出几列分析的数据，比如:

select_type	table	type	rows
	本行分析结果对应的表名	ALL：全表查询	查询的行数
参数解释：

2、select_type：

simple：不需要union的操作或者是不包含子查询的简单select语句。

primary：需要union操作或者含有子查询的select语句。

union：连接两个select查询，第一个查询是dervied派生表，第二个及后面的表select_type都是union。

dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响。

union result：包含union的结果集。

subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery。

dependent subquery：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响。

derived：from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select。

3、table

表名，如果是用了别名，则显示别名

4、type

依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。

system：表中只有一行数据或者是空表。

const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。

eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref。

ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。

fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。

ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。

unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值。

index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。

index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引。

index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

6、key：查询真正使用到的索引。

7、key_len：用于处理查询的索引长度。

9、rows：执行计划中估算的扫描行数，不是精确值。

11、extra：该字段信息较多，这里就不一一叙述了。

在实际的使用过程中我们需要重点去关注type、key、key_len、rows、extra这几个参数，type要努力优化到range级别，all要尽量少的出现，在查询的过程中要尽量使用索引，提高效率，在extra里面出现Using filesort, Using temporary是不太好的，要去优化提高性能。

sql语句优化笔记

先查id，再根据id查内容，不要直接Select *–对于数值型id，在分页基础上优化

使用 limit, offset，当 offset 变大的时候执行效率会越来越低。
因为 select 在执行过程中，对于存储引擎返回的记录，经过 server 层的 WHERE 条件筛选之后，符合条件的前 offset 条记录，会被直接抛弃，直到符合条件的第 offset + 1 条记录，才开始发送给客户端，发送了 limit 条记录之后，查询结束。
LIMIT OFFSET 分页慢的主要原因还是offset偏移量大了之后，会多读取很多无效的数据，伴随着回表取数，然后丢弃的消耗。可以考虑join子查询的方式，主查询只取主键id使用索引覆盖的特性提升效率，然后通过主键 join 主表（用到INLJ算法优化）
大数据量（超过1kw）就上ES吧，不然太折腾了，还不能持久。

1. 语法回顾
   先来简单的回顾一下 select 语句中 limit, offset 的语法，MySQL 支持 3 种形式：

• LIMIT limit: 因为没有指定 offset，所以 offset = 0，表示读取符合 WHERE 条件的第 1 ~ limit 条记录。
• LIMIT offset, limit: 我们常用的就是这种了。
• LIMIT limit OFFSET offset: 这种不常用。

offset 和 limit 的值都不能为负数，在源码里这两个属性定义的是无符号整数，并且在解析阶段就做了限制，如果为负数，直接报语法错误了。

2. 语法解析阶段
   在读取数据的过程中，对于符合条件的前 offset 条记录，会直接忽略，不发送给客户端，从符合条件的第 offset + 1 条记录开始，发送 limit 条记录给客户端。

所以，server 层实际上需要从存储引擎读取 offset + limit 条记录，源码里也是这么实现的，语法解析阶段，在验证了 offset 和 limit 都是大于等于 0 的整数之后，就把 offset + limit 的计算结果保存到一个叫做 select_limit_cnt 的属性里，offset 也会保存到一个叫做 offset_limit_cnt 的属性里。

3. 发送数据阶段
   来到发送数据阶段，此时的记录已经通过了 WHERE 条件的筛选，接下来就是判断这条记录是不是要发送给客户端。

第 1 步

因为 offset 已经保存到 offset_limit_cnt 中了，先来判断 offset_limit_cnt 是否大于 0，如果大于 0，这条记录就会被抛弃了，不发送给客户端；如果等于 0，记录就具备了发送给客户端的资格了，然后接着进入第 2 步。

在抛弃记录之前，还会干一件事：对一个叫做 send_records 的属性进行加 1 操作，就是假装这条记录已经发送了（为什么这样干？第 2 步会用到这个属性）。

offset_limit_cnt 是保证不会小于 0 的，所以在这一步只需要判断是大于 0 还是等于 0 就可以了。

第 2 步

来到这一步，记录就具备了发送给客户端的资格了，至于要不要发，就看客户端想不想要它了，而客户端想不想要它，取决于 select_limit_cnt。

所以，在这一步要判断已发送记录数量（send_records）和需要发送记的录数量（select_limit_cnt）之间的关系，如果已发送记录数量大于等于需要发送的记录数量，则结束查询，否则就接着进入第 3 步。

第 3 步

在这里，记录等待着被发送给客户端。
等待的是网络缓冲区。

4. 最佳实践
   既然在 offset 变大之后，使用 limit, offset 效率越来越低，那应该怎么办呢？
   以一个 SQL 为例：

select * from t2
where i1 > 90000000 limit 8888, 10

为了取到 10 条记录，要先找到 8888 条记录，然后取到需要的 10 条，前面 8888 条记录都白找了，太浪费了，可以这样修改一下：

select * from t2 
where i1 > 90000000 and id > LAST_MAX_ID
limit 10

LAST_MAX_ID 是上一次执行 SQL 时读取到的主键 ID 的最大值，如果是第一次执行语句，LAST_MAX_ID = 0。

不过这种方案也有个问题，不支持跳着翻页，只支持顺序翻页（就是每次都点下一页的这种）。

如果要支持跳着翻页，怎么办？

只用 MySQL 有点不够用了,还需要利用Redis，可以把符合条件的记录的主键 ID 都读取出来，存入到 Redis 的有序集合（zset）中，用 zset 相应的函数读取到某一页应该展示的数据对应的那些主键 ID，然后用这些主键 ID 去 MySQL 中查询对应的数据，从而用间接的实现了分页功能。

当然，这个方案也是有适用场景的，比如，这个方案明显就不适用于这些场景：符合条件的记录非常非常多导致存主键 ID 到 Redis 要占用很大的内存、记录更新频繁导致存主键 ID 的缓存经常被清除。如果碰到更复杂的场景，就要结合业务具体情况具体分析了。

根据最左前缀原则
字段的基数大，选择性好，可对该字段单独建立索引，
字段基数很小，选择性不好。

传入的过滤条件：
where ***

没有 station_nu 字段，使用不到复合索引 IXFK_arrival_record 的 product_id，station_no，sequence，receive_time 这几个字段。

优化器

当查询出数据以后，会返回给执行器。执行器一方面将结果写到查询缓存里面，当你下次再次查询的时候，就可以直接从查询缓存中获取到数据了。另一方面，直接将结果响应回客户端。

mysql的编写过程和解析过程

① 编写过程

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

② 解析过程

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

索引的优化实践

没有索引，100万条数据花了一秒左右，查了90多万行
100个用户登录就要等100秒才能完成，这样好吗？这样不好
创建了索引后，0.01秒就可以，查的行数变少了。

建立联合索引效率会更高，尤其是在数据量较大，单个列区分度不高的情况下
在多表连接、where条件、排序、分组的字段上建立索引
Where条件上不要使用运算函数，以免索引失效
阿里巴巴Java开发手册建议，单表索引数量控制在5个以内，组合索引字段数不允许超过5个

其他建议：

• 禁止给表中的每一列都建立单独的索引
• 每个Innodb表必须有个主键
• 要注意组合索引的字段的顺序
• 优先考虑覆盖索引
• 避免使用外键约束

索引失效

常见的索引失效的场景有哪些？

• 以 % 开头的 LIKE 查询
• 创建了组合索引，但查询条件不满足 ‘最左匹配原则’。如：创建联合索引 (type,status,uid)，但是使用右边的 status 和 uid 作为查询条件。
• 查询条件中使用 or，且 or 的条件中有一个列没有索引，则or中的其他列也不会走索引
• 类型不匹配：MySQL 的策略是将字符串转换为数字之后再比较。函数作用于表字段，索引失效。
• 后半句作用于表什么意思

建议

尽量少用or，同时尽量用union all 代替union
可以使用union all或者union代替or，而这两者的区别是union是将两个结果合并之后再进行唯一性的过滤操作(合并重复数据)，效率会比union all低很多。而union all要求两个数据集没有重复的数据（因为不会合并两个查询结果中的相同数据，因此会产生重复行）。
例子：

#OR：
select ename, job, from t_emp where job='manager' or job='saleman';
#可以改成：
select ename, job, from t_emp where job='manager'
union all
select ename, job, from t_emp where job='saleman';

优化关联查询
在大数据场景下，表与表之间通过一个冗余字段来关联，要比直接使用JOIN有更好的性能。如果确
实需要使用关联查询的情况下，需要特别注意的是：

1. 确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A
   和表B用列c关联的时候，如果优化器关联的顺序是A、B，那么就不需要在A表的对应列上
   创建索引。没有用到的索引会带来额外的负担，一般来说，除非有其他理由，只需要在关联
   顺序中的第二张表的相应列上创建索引（具体原因下文分析）。
2. 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有
   可能使用索引来优化。

索引原理

3.1 InnoDB索引实现
InnoDB数据页由7个组成部分，各个数据页可以组成一个双向链表。而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表。每个数据页都会为存储在它里面的记录生成一个页目录。在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

页和记录的关系示意图如下：

索引同样存储在数据页中，只不过目录项中的两个列是主键和页号。
那InnoDB怎么区分一条记录是普通的用户记录还是目录项记录呢？是根据记录头信息里的record_type属性，它的各个取值代表的意思如下：
0：普通的用户记录
1：目录项记录
2：最小记录
3：最大记录

• 似乎可以先了解数据页

3.2 查找步骤
整体结构如下：

现在如果我们想根据主键值查找一条用户记录大致需要3个步骤，以查找主键值为20的记录为例：
1、确定目录项记录页。
2、通过目录项记录页确定用户记录真实所在的页。
3、在页中定位到具体的记录。

四、InnoDB中的索引分类
4.1 聚簇索引
上边介绍的B+树索引。它有两个特点：
1、根据记录主键值的大小进行记录和页的排序
这包括三个方面的含义：
页内的记录是按照主键的大小顺序排成一个单向链表。
各个存放用户记录的页也是根据主键大小顺序排成一个双向链表。
存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。

2、B+树的叶子节点存储的是完整的用户记录

我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。

4.2 二级索引
上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的。
那如果我们想以别的列作为搜索条件该咋办呢？
难道只能从头到尾沿着链表依次遍历记录么？
我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。比方说我们用c2列的大小作为数据页、页中记录的排序规则，再建一棵B+树，效果如下图所示：

但是但是这个B+树的叶子节点中的记录只存储了c2和c1（也就是主键）两个列，所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。

由于主键值具有唯一性，二级索引不具有唯一性，那么 新的问题来了：

在上图中，如果我们想新插入一行记录，其中c1、c2、c3的值分别是：9、1、‘c’。

那么在修改这个为c2列建立的二级索引对应的B+树时便碰到了个大问题：

由于页3中存储的目录项记录是由c2列 + 页号的值构成的，页3中的两条目录项记录对应的c2列的值都是1，而我们新插入的这条记录的c2列的值也是1，那我们这条新插入的记录到底应该放到页4中，还是应该放到页5中啊？懵逼了。

为了让新插入记录能找到自己在那个页里，我们需要保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。
所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：1、索引列的值2、主键值3、页号。

我们为c2列建立二级索引后的示意图实际上应该是这样子的：

4.3 联合索引
我们可以同时为多个列建立索引，比方说我们想让B+树按照c2和c3列的大小进行排序，这个包含两层含义：

1、先把各个记录和页按照c2列进行排序；

2、在记录的c2列相同的情况下，采用c3列进行排序。

分页优化

比如带上上次最大id、create_time，而不用先查找前几条数据了。

• 但是即使这一列没有索引，能不能减少搜索量呢？

其他优化

虽然 MySQL5.6 引入了物化特性，但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成 JOIN。

UPDATE operation o 
SETstatus = 'applying'
WHERE  o.id IN (SELECTid
                FROM   (SELECT o.id, 
                               o.status 
                        FROM   operation o 
                        WHERE  o.group = 123
                               AND o.status NOTIN ( 'done' ) 
                        ORDERBY o.parent, 
                                  o.id 
                        LIMIT1) t);

+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| id | select_type        | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                                               |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| 1  | PRIMARY            | o     | index |               | PRIMARY | 8       |       | 24   | Using where; Using temporary                        |
| 2  | DEPENDENT SUBQUERY |       |       |               |         |         |       |      | Impossible WHERE noticed after reading const tables |
| 3  | DERIVED            | o     | ref   | idx_2,idx_5   | idx_5   | 8       | const | 1    | Using where; Using filesort                         |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+

重写为 JOIN 之后，子查询的选择模式从 DEPENDENT SUBQUERY 变成 DERIVED，执行速度大大加快，从7秒降低到2毫秒。

UPDATE operation o 
       JOIN  (SELECT o.id, 
                            o.status 
                     FROM   operation o 
                     WHERE  o.group = 123
                            AND o.status NOTIN ( 'done' ) 
                     ORDERBY o.parent, 
                               o.id 
                     LIMIT1) t
         ON o.id = t.id 
SETstatus = 'applying'

MySQL 对待 EXISTS 子句时，仍然采用嵌套子查询的执行方式。去掉 exists 更改为 join，能够避免嵌套子查询，

日志

三大日志：二进制日志( binlog )和事务日志(包括redo log 和 undo log )

Redo log

MySQL 8.0 以前，Redo 日志是串行写入 log buffer 的，多个用户线程想要同时往 log buffer 里写日志，那是不行的，必须排队等待（获取互斥锁），拿到互斥锁之后，才能往 log buffer 里写日志。

MySQL 8.0 中，串行写入变为并行写入，log buffer 由乡间小道变成了单向 8 车道的高速公路，多个用户线程可以同时往 log buffer 里写入 Redo 日志，效率大大提升。

Redo 日志从产生到刷盘，一共会经历 4 个阶段（产生、写 log buffer、写日志文件、刷盘）

本来 InnoDB 接收到插入、修改、删除这样的 DML 语句，以及创建表 & 索引、修改表结构这样的 DDL 语句，修改 Buffer Pool 中的数据页之后就完事了。

因为要保证数据不丢失，事情就变的复杂了，修改了数据页不算完，还要生成 Redo 日志，生成了也不算完，还要把它写入 Redo 日志文件。

产生

以一条非常简单的插入语句为例，这个语句包含自增列，并且只插入一条记录，我们假设插入过程中不会造成索引页分裂，也不会产生溢出页。

Redo 日志并不会每产生一条就马上写入 log buffer，而是一组 Redo 日志攒到一起往 log buffer 里写。

那就需要有一个地方临时存放日志组中不同时间点产生的日志了，这个地方就是 mtr 中的 m_log 链表。

mtr 是 Mini-Transaction 的缩写，是一组不可分隔的操作组成的一个整体，就像前面插入语句的例子中，保存表中自增列的最大值和插入记录就是一组不可分隔的操作，必须放入一个 mtr。
m_log 链表是由一个一个 block 组成的链表，block 大小为 512 字节，每产生一条日志，就追加到 m_log 的 block 中，如果一个 block 写满了，就再申请一个 block 接着写。

Undo Log

当我们对记录做了变更操作时就会产生一条Undo记录。
它的作用就是保护事务在异常发生的时候或手动回滚时可以回滚到历史版本数据，能够让你读取过去某一个时间点保存的数据。通俗易懂地说，它只关心过去的数据。

经典问题

Mysql技巧与经验

比较范围不确定时不使用in

如果使用，需要保证范围是确定且有限的（如指定in的条件，而不是 XXX in(select id from XXX where XXX)这种范围为select子句，in比较的数量可能不固定）
in 是不能命中索引的，改成EXISTS查询效率提高

select * from t1
where NOT  EXISTS (select phone from t2 where t1.phone =t2.phone)

in的替代方案：
1、用 EXISTS 或 NOT EXISTS 代替

select * from test1
   where EXISTS (select * from test2 where id2 = id1 )

select * FROM test1
where NOT EXISTS (select * from test2 where id2 = id1 )

2、用JOIN 代替

select id1 from test1
   INNER JOIN test2 ON id2 = id1

select id1 from test1
   LEFT JOIN test2 ON id2 = id1
   where id2 IS NULL

LEFT JOIN + right id is null

• 建表时最好不要有null?比如hibernate包装类型可能默认为null，比较相等时可能null不和任何相等
• 不知道如果要连接三个表的时候又该怎么办？拆成多次从应用层访问数据层，一次次搞筛选条件？

Mybatis批量插入

有三种批量插入方式：

• java代码中用foreach循环逐条插入，性能最低
• MybatisPlus批量插入
• 在xml文件中用<foreach>标签拼接sql语句，但要注意一条sql语句有大小限制，版本8.0.15为4M,sql语句过长会导致报错

附录

explain 其他参数

• ref：常数等值查询显示const，连接查询则显示表的关联字段。-

• possible_keys：查询可能使用到的索引。

• id：数字越大越先执行，一样大则从上往下执行，如果为NULL则表示是结果集，不需要用来查询。

• filtered：表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例。