MySQL8.0中的跳跃范围扫描优化Skip Scan Range Access Method介绍

在MySQL8.0以前,索引使用规则有一项是索引左前缀,假如说有一个索引idx_abc(a,b,c),能用到索引的情况只有查询条件为a、ab、abc、ac这四种,对于只有字段b的where条件是无法用到这个idx_abcf索引的。这里再强调一下,这里的顺序并不是在where中字段出现的顺序,where b=2 and 1=1 也是可以利用到索引的,只是用到了(a,b)这两个字段

针对这一点, 从MySQL 8.0.13开始引入了一种新的优化方案,叫做 Skip Scan Range,翻译过来的话是跳跃范围扫描。如何理解这个概念呢?我们可以拿官方的SQL示例具体讲一下(https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

我们这里创建了一个t1表,其中主键为(f1,f2),这里是两个字段。执行完这个sql语句后表里有160条记录,执行计划为

mysql> EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |   53 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

这里可以看到 type 为 rang,说明用到了范围查询,key为 PRIMARY, Extra中 Using where; Using index for skip scan

说明确实用到了新特性 skip scan。

那么在MySQL内部这个 skip scan 它又是如何执行的呢,我们可以理解以下几步

  1. 先统计一下索引前缀字段 f1 字段值有几个唯一值,这里一共有1 和2
  2. 对其余索引部分上的f2> 40条件的每个不同的前缀值执行子范围扫描

对于详细的执行流程如下:

  1. 获取f1的第一个唯一值(f1=1)
  2. 组合能用到索引的sql语句(f1=1 AND f2>40)
  3. 执行组合后的sql语句,进行范围扫描,并将结果放入记录集
  4. 重复上面的步骤,获取f1的第二个唯一值(f1=2)
  5. 组合能用到索引的sql语句(f1=2 AND f2>40)
  6. 执行组合后的sql语句,进行范围扫描,并将结果放入记录集
  7. 全部执行完毕,返回记录集给客户端

不错,原理很简单,就是将f1字段拆分成不同的值,将每个值带入到适合左前缀索引的SQL语句中,最后再合并记录集并返回即可,类似UNION操作。够简单吧!

但有同学可能会问,是所有的查询都不会执行这个优化吗?答案是否定的,主要还要看左前缀有字段值的分散情况,如果值过多的话,性能还是比较差的。系统会进行全表扫描,这里就需要单独为这个字段创建一个单独的索引。

skip scan特性虽好,但也有一些使用条件。

skip scan触发条件

(1)必须是联合索引

(2)只能是一个表

(3)不能使用distinct或group by ;

(4)SQL不能回表,即select列和where条件列都要包含在一个索引中

(5)默认optimizer_switch=’skip_scan=on’开启;

一文理解MySQL中的page页

在介绍InnoDB中的页的时候,很有必要先让大家了解一下InnoDB中的存储结构

从InnoDB存储引擎的逻辑结构看,所有数据都被逻辑地存放在一个空间内,称为表空间(tablespace),而表空间由段(sengment)、区(extent)、页(page)组成。 在一些文档中extend又称块(block)。

一、表空间(table space)

表空间(Tablespace)是一个逻辑容器,表空间存储的对象是段,在一个表空间中可以有一个或多个段,但是一个段只能属于一个表空间。数据库由一个或多个表空间组成,表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。

在 InnoDB 中存在两种表空间的类型:共享表空间和独立表空间。如果是共享表空间就意味着多张表共用一个表空间。如果是独立表空间,就意味着每张表有一个独立的表空间,也就是数据和索引信息都会保存在自己的表空间中。独立的表空间可以在不同的数据库之间进行迁移。可通过命令

Continue reading

MySQL8.0中的几项新特性(整理)

新特新解读 | MySQL 8.0 对 count(*)的优化

新特性解读 | MySQL 8.0 正则替换

新特性解读 | MySQL 8.0 资源组

新特性解读 | MySQL 8.0 Temptable 引擎介绍

新特性解读 | MySQL 8.0 窗口函数详解

新特性解读 | MySQL 8.0 json到表的转换

新特性解读 | MySQL 8.0.16 在组复制中启用成员自动重新加入

新特性解读 | MySQL 8.0 直方图

新特性解读 | MySQL 8.0 索引特性1-函数索引

新特性解读 | MySQL 8.0 索引特性2-索引跳跃扫描

新特性解读 | MySQL 8.0 索引特性3 -倒序索引

新特性解读 | MySQL 8.0 索引特性4-不可见索引

新特性解读 | MySQL 8.0.16 组复制通讯协议的设置

新特性解读 | MySQL 8.0.16 组复制新功能:消息碎片化

新特性解读 | MySQL 8.0 新增 HINT 模式

社区投稿 | MySQL 8.0.16 开始支持 check 完整性

新特性解读 | MySQL 8.0 通用表达式

新特性解读 | MySQL 最新的release notes

新特性解读 | MySQL 5.7升级到MySQL 8.0的注意事项

新特性解读 | 自动验证 MySQL 配置正确性的新选项

新特性解读 | MySQL 8.0 动态权限

一文读懂 MySQL 的隔离级别和锁的关系

MySQL 中的隔离四种隔离级别与锁的关系一直挺模糊的,看了好多文章感觉着都不是很好理解,今天在“爱可生开源社区”看到一篇文章,感觉着挺容易理解的。

READ UNCOMMITTED 未提交读,可以读取未提交的数据。

READ COMMITTED 已提交读,对于锁定读(select with for update 或者 for share)、update 和 delete 语句, InnoDB 仅锁定索引记录,而不锁定它们之间的间隙,因此允许在锁定的记录旁边自由插入新记录。Gap locking 仅用于外键约束检查和重复键检查。

REPEATABLE READ 可重复读,事务中的一致性读取读取的是事务第一次读取所建立的快照。

SERIALIZABLE 序列化

文中主要对 RR 和 RC 两种常用的隔离级别做了不同情况的说明,对于 SERIALIZABLE 序列化 和 READ UNCOMMITTED 未提交读,由于很好理解所以未在文中体现。对于 RR 和 RC 主要区别是 RR 存在 Gap Lock间隙锁,而RC则没有Gap Lock间隙锁,所以在互联网中绝大部分是采用了RC 隔离级别,而未使用MySQL中默认的RR级别。对于锁的介绍请参考:https://blog.haohtml.com/archives/17758

MySQL5.7中Undo回收收缩相关参数

在MySQL5.7以前,ibdata1文件会逐渐增大(ibdata1文件包含哪些信息?),非常占用系统空间,特别是一些云数据来说,磁盘非常的贵,想要回收空间,只能进行一次导出和导入操作,来重新生成undo 表空间,从MySQL5.7开始,有了在线回收undo表空间的功能,主要由以下几个参数设置。

innodb_undo_directory = .
为undo文件存储路径。如果没有指定默认值(NULL),则undo表空间则存放到mysql的data目录里(datadir选项)。配置此项可以用undo从ibdata文件里分离出来。单独存储。

innodb_undo_logs = 128
(默认值 128)undo rollback segment 回滚段个数,为 innodb_rollback_segments 参数选项的别名,最大值为128,其中32个为使用临时表空间 ibtmp1 保留,1个为系统表空间使用,剩余的95个为 undo tablespaces 使用。
当 innodb_rollback_segments<=32的时候,系统将自动分配1个rollback segment给系统表空间,32个分给临时表空间。
此选项以后版本将移除!

innodb_undo_tablespaces = 0
(MySQL5.7默认值为0,MySQL8默认值为2)undo文件个数,此值需要在MySQL Server 初始化的时候指定,一经设定,以后将无法修改,否则重启后会提示部分Undo 文件找不到。默认值为0, 此时无法进行Undo回收操作,回收undo表空间至少需要为2个才可以, 需保证其中一个进行回收收缩时,另一个为可用状态。保存路径为 innodb_undo_directory选项设置,undo文件名规则为 undoN。文件大小受innodb_page_size选项影响。
此选项以后版本将移除!

innodb_undo_log_truncate = OFF
(默认值NO)参数设置为ON,即开启在线回收undo日志文件,支持动态设置,当超过 innodb_max_undo_log_size 时被进行收缩,至少需要两个undo文件,即innodb_undo_tablespace>=2

innodb_max_undo_log_size = 1073741824
(默认1GB)当超过阈值时,会触发truncate回收动作,truncate后空间缩小到10MB

innodb_purge_rseg_truncate_frequency = 128
(默认值128), 控制回收undo log的频率。 指定purge操作被唤起多少次之后才释放rollback segments。当undo表空间里面的rollback segments被释放时,undo表空间才会被truncate。由此可见,该参数越小,undo表空间被尝试truncate的频率越高。 。

联想思考:
ibdata文件包含哪些信息?
undo logs、change buffer、doublewrite buffer、表数据、索引数据,如果启用了 innodb_file_per_table 选项的话,则表数据和索引数据则存储到相应表的.ibd 文件里),参考:
https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_ibdata_file

ibdata文件爆增原因有哪些?
事务未提交、大事务、启用了共享表空间、磁盘io过慢导致check point远远落后。等等

如何避免ibdata文件一直爆增的问题?
尽量短事务、增加 purge 线程、加速purge频率(innodb_purge_truncate_frequency)、监控 information_schema.Innodb_trx 表,设置长事务阈值,超过就报警 / 或者 kill

参考:
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_max_undo_log_size

undo表空间回收https://dev.mysql.com/doc/refman/5.7/en/innodb-undo-tablespaces.html

MySQL利用 INFORMATION_SCHEMA.PROFILING 分析SQL性能

MySQL5.7中有一个系统默认库 information_schema , 里面有些表如 PROFILING,、OPTIMIZER_TRACE、 PROCESSLIST、INNODB_TRX等,其中 PROFILE 对于我们分析sql有很大的帮助,在此以前我们需要使用 SHOW PROFILE 命令,不过此命令以后将被废弃。下面我们就介绍一下如何使用此表。

从 MySQL8.0开始, 这个表也开始被废弃了,以后分析性能问题直接使用另一个系统库 performance_schema 里的相关表(setup_actors)就可以了。到时候 show profiles 和show profile两个命令也不能用了。

1.在使用此表前,我们需要开户性能检测功能。

mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> SET profiling = 1;
Query OK, 0 rows affected (0.00 sec)

默认情况下是 OFF/0 状态。在我们分析完,最好关闭以减少服务器压力。

相关查询命令

show VARIABLES like 'profil%'
-------------------------------
profiling	ON
profiling_history_size	15

2. 了解 information_schema.profiling 表的常用字段

官方文档:https://dev.mysql.com/doc/refman/8.0/en/profiling-table.html

1QUERY_ID 查询ID, 用于标记不同的查询
2SEQ 一个查询内部执行的步骤 , 从2开始
3STATE 步骤的状态
4DURATION 持续时间
5CPU_USER 用户空间的cpu 使用量
6CPU_SYSTEM 内核空间的cpu 使用量
7CONTEXT_VOLUNTARY上下文主动切换
8CONTEXT_INVOLUNTARY上下文被动切换
9BLOCK_OPS_IN阻塞输入操作
10BLOCK_OPS_OUT阻塞输出操作
11MESSAGES_SENT消息发送
12MESSAGES_RECEIVED消息接受
13PAGE_FAULTS_MAJOR主分页错误
14PAGE_FAULTS_MINOR次分页错误
15SWAPS swap 发生的次数
16SOURCE_FUNCTIONMySQL源码执行函数
17SOURCE_FILE源码文件
18SOURCE_LINE源码行数

以下是我执行了一个join语句的输出,从结果中我们可以分析出哪个步骤执行的时间最长,进行相应的优化即可。

我们可以根据DURATION 列的值来分析哪一个模块消耗的时间多来进行相应的优化。

推荐使用 OPTIMIZER_TRACER 来分析 SQL 执行过程
https://dev.mysql.com/doc/internals/en/optimizer-tracing.html ,每种参数用法可参考:
https://www.cnblogs.com/DataArt/p/10232831.html

MySQL中group_concat函数详解

函数语法:

group_concat( [DISTINCT]  要连接的字段   [Order BY 排序字段 ASC/DESC]   [Separator ‘分隔符’] )


下面举例说明:

select * from goods;  

+——+——+
| id| price|
+——+——+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+——+——+
6 rows in set (0.00 sec)


  1. 以id分组,把price字段的值在同一行打印出来,逗号分隔(默认)

select id, group_concat(price) from goods group by id;  

+——+——————–+
| id| group_concat(price) |
+——+——————–+
|1 | 10,20,20|
|2 | 20 |
|3 | 200,500|
+——+——————–+
3 rows in set (0.00 sec)


2. 以id分组,把price字段的值在一行打印出来,分号分隔 
select id,group_concat(price separator ‘;’) from goods group by id;  

+——+———————————-+
| id| group_concat(price separator ‘;’) |
+——+———————————-+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500 |
+——+———————————-+
3 rows in set (0.00 sec)


3. 以id分组,把去除重复冗余的price字段的值打印在一行,逗号分隔
select id,group_concat(distinct price) from goods group by id;  

+——+—————————–+
| id| group_concat(distinct price) |
+——+—————————–+
|1 | 10,20|
|2 | 20 |
|3 | 200,500 |
+——+—————————–+
3 rows in set (0.00 sec)


4. 以id分组,把price字段的值打印在一行,逗号分隔,按照price倒序排列
select id,group_concat(price order by price desc) from goods group by id;  

+——+—————————————+
| id| group_concat(price order by price desc) |
+——+—————————————+
|1 | 20,20,10 |
|2 | 20|
|3 | 500,200|
+——+—————————————+
3 rows in set (0.00 sec)

MySQL中order by 排序必知

在开发过程时,我们经常会遇到 order by 排序操作,那么你知道什么时候MySQL才会进行排序操作,什么时候不需要时间排序操作?,下面我们就从一个很小的例子中了解一下排序场景。

表结构如下:

CREATE TABLE t (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
city varchar(16) NOT NULL,
name varchar(16) NOT NULL,
age int(11) NOT NULL,
addr varchar(128) DEFAULT NULL,
PRIMARY KEY (id),
KEY city (city) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这里只有一个索引city,我们执行一条SQL语句:

SELECT city, name  FROM t WHERE city=’杭州’ ORDER BY name LIMIT 1000;

通过Explain命令查看执行情况

发现Extra字段里有”Using filesort”,说明使用了排序,而排序必用到了sort_buffer, 这是由数据库为了专门进行排序操作而分配的一块内存。
这里的Using index conditon是指ICP特性,请参考:https://blog.haohtml.com/archives/18201

下面我们专门来看一下这条语句的执行流程是如何的?在此以前我们需要了解一个概念,就是索引的特性是有序的,系统搜索时,一旦找到最后一条满足条件的记录后就立即停止,后面的记录则不再进行扫描,这样就可以用来解决全表扫描的性能问题。

Continue reading

MySQL中的半同步复制

MySQL当前存在的三种复制模式有:异步模式、半同步模式和组复制模式。注意:MySQL复制模式没有“同步复制”这一项的,文章中只是为了读者方便理解半同步复制的概念才介绍了同步复制概念https://dev.mysql.com/doc/refman/8.0/en/replication-semisync.html

从MySQL5.5开始,MySQL以插件的形式支持半同步复制。

1. 异步复制(Asynchronous replication)

MySQL默认的复制即是异步的,主库在执行完客户端提交的事务后会立即将结果返给给客户端,并不关心从库是否已经接收并处理,这样就会有一个问题,主如果crash掉了,此时主上已经提交的事务可能并没有传到从上,如果此时,强行将从提升为主,可能导致新主上的数据不完整。

异步复制是MySQL最早的也是当前使用最多的复制模式,异步复制提供了一种简单的主-从复制方法,包含一个主库(master)和备库(一个,或者多个) 之间,主库执行并提交了事务,在这之后(因此才称之为异步),这些事务才在从库上重新执行一遍(基于statement)或者变更数据内容(基于 row),主库不检测其从库上的同步情况。在服务器负载高、服务压力大的情况下主从产生延迟一直是其诟病。工作流程简图如下:

而同步复制(Fully synchronous replication,MySQL中没有此复制概念)指当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回,所以全同步复制的性能必然会收到严重的影响。

2. 半同步复制(Semisynchronous replication)

介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟,这个延迟最少是一个TCP/IP往返的时间。所以,半同步复制最好在低延时的网络中使用。

MySQL5.5 的版本在异步同步的基础之上,以插件的形式实现了一个变种的同步方案,称之为半同步复制(semi-sync replication)。这个插件在原生的异步复制上,添加了一个同步的过程:当从库接收到了主库的变更(即事务)时,会通知主库。主库上的操作有两种:接收到这个通知以后才去commit事务接受到之后释放session。这两种方式是由主库上的具体配置决定的。当主库收不到从库的变更通知超时时,由半同步复制自动切换到异步同步,这样就极大了保证了数据的一致性(至少一个从库),但是在性能上有所下降,特别是在网络不稳定的情况下,半同步和同步之间来回切换,对正常的业务是有影响的。其工作流程简图如下:

Continue reading