MySQL-杂项

-------------------------1、开启并行复制

  1.  mysql> stop slave sql_thread; 
  2.  mysqlset global slave_parallel_type='LOGICAL_CLOCK'; 
  3. mysqlset global slave_parallel_workers=8;
  4.  mysqlset global binlog_transaction_dependency_tracking=COMMIT_ORDER;
  5.  mysqlstart slave sql_thread;

-------------------------2、一主一从报错,测试环境

从库执行show slave status\G

先停止主从复制关系(从库执行)

stop slave;

主库执行

复制代码
mysql> SHOW MASTER STATUS\g
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000036 |      197 |              |                  | c261769d-e2ac-11ed-93ef-000c29ee4d81:1-34 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW MASTER STATUS\g
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000037 |      197 |              |                  | c261769d-e2ac-11ed-93ef-000c29ee4d81:1-34 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
复制代码

从库执行

change master to master_log_file='mysql-bin.000037',master_log_pos=197;
start slave;
show slave status\G
show master status\G

-------------------------3、索引

  • 索引是一种能提高数据库查询效率的数据结构。它可以比作一本字典的目录,可以帮你快速找到对应的记录。
  • 索引一般存储在磁盘的文件中,它是占用物理空间的。
  • 正所谓水能载舟,也能覆舟。适当的索引能提高查询效率,过多的索引会影响数据库表的插入和更新功能。

2. MySQL索引有哪些类型

 

 

数据结构维度

  • B+树索引:所有数据存储在叶子节点,复杂度为O(logn),适合范围查询。
  • 哈希索引:  适合等值查询,检索效率高,一次到位。
  • 全文索引:MyISAMInnoDB中都支持使用全文索引,一般在文本类型char,text,varchar类型上创建。
  • R-Tree索引: 用来对GIS数据类型创建SPATIAL索引

物理存储维度

  • 聚集索引:聚集索引就是以主键创建的索引,在叶子节点存储的是表中的数据。(Innodb存储引擎)
  • 非聚集索引:非聚集索引就是以非主键创建的索引,在叶子节点存储的是主键和索引列。(Innodb存储引擎)

逻辑维度

  • 主键索引:一种特殊的唯一索引,不允许有空值。
  • 普通索引:MySQL中基本索引类型,允许空值和重复值。
  • 联合索引:多个字段创建的索引,使用时遵循最左前缀原则。
  • 唯一索引:索引列中的值必须是唯一的,但是允许为空值。
  • 空间索引:MySQL5.7之后支持空间索引,在空间索引这方面遵循OpenGIS几何数据模型规则。

3. 索引什么时候会失效?

  • 查询条件包含or,可能导致索引失效
  • 如果字段类型是字符串,where时一定用引号括起来,否则索引失效
  • like通配符可能导致索引失效。
  • 联合索引,查询时的条件列不是联合索引中的第一个列,索引失效。
  • 在索引列上使用 mysql 的内置函数,索引失效。
  • 对索引列运算(如,+、-、*、/),索引失效。
  • 索引字段上使用(!= 或者 < >,not in)时,可能会导致索引失效。
  • 索引字段上使用is null, is not null,可能导致索引失效。
  • 左连接查询或者右连接查询查询关联的字段编码格式不一样,可能导致索引失效。
  • mysql 估计使用全表扫描要比使用索引快,则不使用索引。

4. 哪些场景不适合建立索引?

  • 数据量少的表,不适合加索引
  • 更新比较频繁的也不适合加索引
  • 区分度低的字段不适合加索引(如性别)
  • where、group by、order by等后面没有使用到的字段,不需要建立索引
  • 已经有冗余的索引的情况(比如已经有a,b的联合索引,不需要再单独建立a索引)

5. 为什么要用 B+树,为什么不用二叉树?

可以从几个维度去看这个问题,查询是否够快,效率是否稳定,存储数据多少, 以及查找磁盘次数,为什么不是二叉树,为什么不是平衡二叉树,为什么不是 B 树,而偏偏是 B+树呢?

为什么不是一般二叉树?

如果二叉树特殊化为一个链表,相当于全表扫描。平衡二叉树相比于二叉查找 树来说,查找效率更稳定,总体的查找速度也更快。

为什么不是平衡二叉树呢?

我们知道,在内存比在磁盘的数据,查询效率快得多。如果树这种数据结构作 为索引,那我们每查找一次数据就需要从磁盘中读取一个节点,也就是我们说 的一个磁盘块,但是平衡二叉树可是每个节点只存储一个键值和数据的,如果 是 B 树,可以存储更多的节点数据,树的高度也会降低,因此读取磁盘的次数 就降下来啦,查询效率就快啦。

那为什么不是 B 树而是 B+树呢?

  • B+树非叶子节点上是不存储数据的,仅存储键值,而 B 树节点中不仅存储 键值,也会存储数据。innodb 中页的默认大小是 16KB,如果不存储数据,那 么就会存储更多的键值,相应的树的阶数(节点的子节点树)就会更大,树就 会更矮更胖,如此一来我们查找数据进行磁盘的 IO 次数有会再次减少,数据查 询的效率也会更快。
  • B+树索引的所有数据均存储在叶子节点,而且数据是按照顺序排列的,链 表连着的。那么 B+树使得范围查找,排序查找,分组查找以及去重查找变得 异常简单。

6. 一次B+树索引树查找过程

假设有以下表结构,并且初始化了这几条数据

CREATE TABLE `employee` (
  `id` int(11NOT NULL,
  `name` varchar(255DEFAULT NULL,
  `age` int(11DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `sex` int(1DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into employee values(100,'小伦',43,'2021-01-20','0');
insert into employee values(200,'俊杰',48,'2021-01-21','0');
insert into employee values(300,'紫琪',36,'2020-01-21','1');
insert into employee values(400,'立红',32,'2020-01-21','0');
insert into employee values(500,'易迅',37,'2020-01-21','1');
insert into employee values(600,'小军',49,'2021-01-21','0');
insert into employee values(700,'小燕',28,'2021-01-21','1');

执行这条查询SQL,需要执行几次的树搜索操作?可以画下对应的索引树结构图~

select * from Temployee where age=32;

其实这个,这个大家可以先画出idx_age普通索引的索引结构图,大概如下:

再画出id主键索引,我们先画出聚族索引结构图,如下:

这条 SQL 查询语句执行大概流程是这样的:

  • 搜索idx_age 索引树,将磁盘块1加载到内存,由于32<43,搜索左路分支,到磁盘寻址磁盘块2
  • 磁盘块2加载到内存中,由于32<36,搜索左路分支,到磁盘寻址磁盘块4
  • 磁盘块4加载到内存中,在内存继续遍历,找到age=32的记录,取得id = 400.
  • 拿到id=400后,回到id主键索引树
  • 搜索id主键索引树,将磁盘块1加载到内存,因为300<400<500,所以在选择中间分支,到磁盘寻址磁盘块3
  • 虽然在磁盘块3,找到了id=400,但是它不是叶子节点,所以会继续往下找。到磁盘寻址磁盘块8
  • 磁盘块8加载内存,在内存遍历,找到id=400的记录,拿到R4这一行的数据,好的,大功告成。

7. 什么是回表?如何减少回表?

当查询的数据在索引树中,找不到的时候,需要回到主键索引树中去获取,这个过程叫做回表

比如在第6小节中,使用的查询SQL

select * from Temployee where age=32;

需要查询所有列的数据,idx_age普通索引不能满足,需要拿到主键id的值后,再回到id主键索引查找获取,这个过程就是回表。

8. 什么是覆盖索引?

如果我们查询SQL的select * 修改为 select id, age的话,其实是不需要回表的。因为idage的值,都在idx_age索引树的叶子节点上,这就涉及到覆盖索引的知识点了。

覆盖索引是select的数据列只用从索引中就能够取得,不必回表,换句话说,查询列要被所建的索引覆盖。

9. 聊聊索引的最左前缀原则

索引的最左前缀原则,可以是联合索引的最左N个字段。比如你建立一个组合索引(a,b,c),其实可以相当于建了(a),(a,b),(a,b,c)三个索引,大大提高了索引复用能力。

当然,最左前缀也可以是字符串索引的最左M个字符。。比如,你的普通索引树是酱紫:

这个SQL: select * from employee where name like '小%' order by age desc; 也是命中索引的。

 

 

10. 索引下推了解过吗?什么是索引下推

给你这个SQL:

select * from employee where name like '小%' and age=28 and sex='0';

其中,nameage为联合索引(idx_name_age)。

如果是Mysql5.6之前,在idx_name_age索引树,找出所有名字第一个字是“小”的人,拿到它们的主键id,然后回表找出数据行,再去对比年龄和性别等其他字段。如图:

 

 

有些朋友可能觉得奇怪,idx_name_age(name,age)不是联合索引嘛?为什么选出包含“小”字后,不再顺便看下年龄age再回表呢,不是更高效嘛?所以呀,MySQL 5.6就引入了索引下推优化,可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

因此,MySQL5.6版本之后,选出包含“小”字后,顺表过滤age=28

 

 

11. 大表如何添加索引

如果一张表数据量级是千万级别以上的,那么,如何给这张表添加索引?

我们需要知道一点,给表添加索引的时候是会对表加锁的。如果不谨慎操作,有可能出现生产事故的。可以参考以下方法:

  1. 先创建一张跟原表A数据结构相同的新表B
  2. 在新表B添加需要加上的新索引。
  3. 把原表A数据导到新表B
  4. rename新表B为原表的表名A,原表A换别的表名;

12. 如何知道语句是否走索引查询?

explain查看SQL的执行计划,这样就知道是否命中索引了

explainSQL一起使用时,MySQL将显示来自优化器的有关语句执行计划的信息。

一般来说,我们需要重点关注type、rows、filtered、extra、key

1.2.1 type

type表示连接类型,查看索引执行情况的一个重要指标。以下性能从好到坏依次:system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

  • system:这种类型要求数据库表中只有一条数据,是const类型的一个特例,一般情况下是不会出现的。
  • const:通过一次索引就能找到数据,一般用于主键或唯一索引作为条件,这类扫描效率极高,,速度非常快。
  • eq_ref:常用于主键或唯一索引扫描,一般指使用主键的关联查询
  • ref : 常用于非主键和唯一索引扫描。
  • ref_or_null:这种连接类型类似于ref,区别在于MySQL会额外搜索包含NULL值的行
  • index_merge:使用了索引合并优化方法,查询使用了两个以上的索引。
  • unique_subquery:类似于eq_ref,条件用了in子查询
  • index_subquery:区别于unique_subquery,用于非唯一索引,可以返回重复值。
  • range:常用于范围查询,比如:between ... and 或 In 等操作
  • index:全索引扫描
  • ALL:全表扫描

1.2.2 rows

该列表示MySQL估算要找到我们所需的记录,需要读取的行数。对于InnoDB表,此数字是估计值,并非一定是个准确值。

1.2.3 filtered

该列是一个百分比的值,表里符合条件的记录数的百分比。简单点说,这个字段表示存储引擎返回的数据在经过过滤后,剩下满足条件的记录数量的比例。

1.2.4 extra

该字段包含有关MySQL如何解析查询的其他信息,它一般会出现这几个值:

  • Using filesort:表示按文件排序,一般是在指定的排序和索引排序不一致的情况才会出现。一般见于order by语句
  • Using index :表示是否用了覆盖索引。
  • Using temporary: 表示是否使用了临时表,性能特别差,需要重点优化。一般多见于group by语句,或者union语句。
  • Using where : 表示使用了where条件过滤.
  • Using index condition:MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤,而不是在服务层过滤,利用索引现有的数据减少回表的数据。

1.2.5 key

该列表示实际用到的索引。一般配合possible_keys列一起看。

13.Hash 索引和 B+树区别是什么?你在设计索引是怎么抉择的?

  • B+树可以进行范围查询,Hash 索引不能。
  • B+树支持联合索引的最左侧原则,Hash 索引不支持。
  • B+树支持 order by 排序,Hash 索引不支持。
  • Hash 索引在等值查询上比 B+树效率更高。(但是索引列的重复值很多的话,Hash冲突,效率降低)。
  • B+树使用 like 进行模糊查询的时候,like 后面(比如%开头)的话可以起到优化的作用,Hash 索引根本无法进行模糊查询。

14.  索引有哪些优缺点?

优点:

  • 索引可以加快数据查询速度,减少查询时间
  • 唯一索引可以保证数据库表中每一行的数据的唯一性

缺点:

  • 创建索引和维护索引要耗费时间
  • 索引需要占物理空间,除了数据表占用数据空间之外,每一个索引还要占用一定的物理空间
  • 以表中的数据进行增、删、改的时候,索引也要动态的维护。

15. 聚簇索引与非聚簇索引的区别

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。它表示索引结构和数据一起存放的索引。非聚集索引是索引结构和数据分开存放的索引

接下来,我们分不同存存储引擎去聊哈~

MySQLInnoDB存储引擎中, 聚簇索引与非聚簇索引最大的区别,在于叶节点是否存放一整行记录。聚簇索引叶子节点存储了一整行记录,而非聚簇索引叶子节点存储的是主键信息,因此,一般非聚簇索引还需要回表查询。

  • 一个表中只能拥有一个聚集索引(因为一般聚簇索引就是主键索引),而非聚集索引一个表则可以存在多个。
  • 一般来说,相对于非聚簇索引,聚簇索引查询效率更高,因为不用回表。

而在MyISM存储引擎中,它的主键索引,普通索引都是非聚簇索引,因为数据和索引是分开的,叶子节点都使用一个地址指向真正的表数据

-------------------------4、mysql监控方式

对于当前数据库的监控方式有很多,分为数据库自带、商用、开源三大类,每一种都有各自的特色;而对于 mysql 数据库由于其有很高的社区活跃度,监控方式更是多种多样,不管哪种监控方式最核心的就是监控数据,获取得到全面的监控数据后就是灵活的展示部分。

1 、连接数(Connects)

  • 最大使用连接数:show status like ‘Max_used_connections’
  • 当前打开的连接数:show status like ‘Threads_connected’

2 、缓存(bufferCache)

复制代码
复制代码
  • 未从缓冲池读取的次数:show status like ‘Innodb_buffer_pool_reads’
  • 从缓冲池读取的次数:show status like ‘Innodb_buffer_pool_read_requests’
  • 缓冲池的总页数:show status like ‘Innodb_buffer_pool_pages_total’
  • 缓冲池空闲的页数:show status like ‘Innodb_buffer_pool_pages_free’
  • 缓存命中率计算:(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100%
  • 缓存池使用率为:((Innodb_buffer_pool_pages_total-Innodb_buffer_pool_pages_free)/Innodb_buffer_pool_pages_total)*100%
复制代码
复制代码

3、 锁(lock)

  • 锁等待个数:show status like ‘Innodb_row_lock_waits’
  • 平均每次锁等待时间:show status like ‘Innodb_row_lock_time_avg’
  • 查看是否存在表锁:show open TABLES where in_use>0;有数据代表存在锁表,空为无表锁

备注:锁等待统计得数量为累加数据,每次获取得时候可以跟之前得数据进行相减,得到当前统计得数据

4 、SQL

复制代码
复制代码
  • 查看 mysql 开关是否打开:show variables like ‘slow_query_log’,ON 为开启状态,如果为 OFF,set global slow_query_log=1 进行开启
  • 查看 mysql 阈值:show variables like ‘long_query_time’,根据页面传递阈值参数,修改阈值 set global long_query_time=0.1
  • 查看 mysql 慢 sql 目录:show variables like ‘slow_query_log_file’
  • 格式化慢 sql 日志:mysqldumpslow -s at -t 10 /export/data/mysql/log/slow.log 注:此语句通过 jdbc 执行不了,属于命令行执行。意思为:显示出耗时最长的 10 个 SQL 语句执行信息,10 可以修改为 TOP 个数。显示的信息为:执行次数、平均执行时间、SQL 语句

备注:当 mysqldumpslow 命令执行失败时,将慢日志同步到本地进行格式化处理。

复制代码
复制代码

5 、statement

  • insert 数量:show status like ‘Com_insert’
  • delete 数量:show status like ‘Com_delete’
  • update 数量:show status like ‘Com_update’
  • select 数量:show status like ‘Com_select

6 、吞吐(Database throughputs)

  • 发送吞吐量:show status like ‘Bytes_sent’
  • 接收吞吐量:show status like ‘Bytes_received’
  • 总吞吐量:Bytes_sent+Bytes_received

7 、数据库参数(serverconfig)

show variables

8 、慢 SQL

复制代码
复制代码

慢 SQL 指的是 MySQL 慢查询,具体指运行时间超过 long_query_time 值的 SQL。我们常听 MySQL 中有二进制日志 binlog、中继日志 relaylog、重做回滚日志 redolog、undolog 等。针对慢查询,还有一种慢查询日志 slowlog,用来记录在 MySQL 中响应时间超过阀值的语句。慢 SQL 对实际生产业务影响是致命的,所以测试人员在性能测试过程中,对数据库 SQL 语句执行情况实施监控,给开发提供准确的性能优化意见显得尤为重要。那怎么使用 Mysql 数据库提供的慢查询日志来监控 SQL 语句执行情况,找到消耗较高的 SQL 语句,以下详细说明一下慢查询日志的使用步骤:

  • 确保打开慢 SQL 开关 slow_query_log
  • 设置慢 SQL 域值 long_query_time 这个 long_query_time 是用来定义慢于多少秒的才算 “慢查询”,注意单位是秒,我通过执行 sql 指令 set long_query_time=1 来设置了 long_query_time 的值为 1, 也就是执行时间超过 1 秒的都算慢查询,如下:
  • 查看慢 SQL 日志路径
  • 通过慢 sql 分析工具 mysqldumpslow 格式化分析慢 SQL 日志 mysqldumpslow 慢查询分析工具,是 mysql 安装后自带的,可以通过./mysqldumpslow —help 查看使用参数说明

常见用法:

  1. 取出使用最多的 10 条慢查询 ./mysqldumpslow -s c -t 10 /export/data/mysql/log/slow.log
  2. 取出查询时间最慢的 3 条慢查询 ./mysqldumpslow -s t -t 3 /export/data/mysql/log/slow.log

注意:使用 mysqldumpslow 的分析结果不会显示具体完整的 sql 语句,只会显示 sql 的组成结构;假如: SELECT FROM sms_send WHERE service_id=10 GROUP BY content LIMIT 0, 1000; mysqldumpslow 命令执行后显示:Count: 2 Time=1.5s (3s) Lock=0.00s (0s) Rows=1000.0 (2000), vgos_dba[vgos_dba]@[10.130.229.196]SELECT FROM sms_send WHERE service_id=N GROUP BY content LIMIT N, N

mysqldumpslow 的分析结果详解

  • Count:表示该类型的语句执行次数,上图中表示 select 语句执行了 2 次。
  • Time:表示该类型的语句执行的平均时间(总计时间)
  • Lock:锁时间 0s。
  • Rows:单次返回的结果数是 1000 条记录,2 次总共返回 2000 条记录。

通过这个工具就可以查询出来哪些 sql 语句是慢 SQL,从而反馈研发进行优化,比如加索引,该应用的实现方式等。

复制代码
复制代码

常见慢 SQL 排查

复制代码
复制代码
  1. 不使用子查询

SELECT FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’); 子查询在 MySQL5.5 版本里,内部执行计划器是这样执行的:先查外表再匹配内表,而不是先查内表 t2,当外表的数据很大时,查询速度会非常慢。在 MariaDB10/MySQL5.6 版本里,采用 join 关联方式对其进行了优化,这条 SQL 会自动转换为 SELECT t1. FROM t1 JOIN t2 ON t1.id = t2.id; 但请注意的是:优化只针对 SELECT 有效,对 UPDATE/DELETE 子 查询无效, 生产环境尽量应避免使用子查询。

  1. 避免函数索引

SELECT FROM t WHERE YEAR(d) >= 2016; 由于 MySQL 不像 Oracle 那样⽀持函数索引,即使 d 字段有索引,也会直接全表扫描。应改为 > SELECT FROM t WHERE d >= ‘2016-01-01’;

  1. 用 IN 来替换 OR 低效查询

慢 SELECT FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30; 高效查询 > SELECT FROM t WHERE LOC_IN IN (10,20,30);

  1. LIKE 双百分号无法使用到索引

SELECT FROM t WHERE name LIKE ‘%de%’; 使用 SELECT FROM t WHERE name LIKE ‘de%’;

  1. 分组统计可以禁止排序

SELECT goods_id,count() FROM t GROUP BY goods_id; 默认情况下,MySQL 对所有 GROUP BY col1,col2… 的字段进⾏排序。如果查询包括 GROUP BY,想要避免排序结果的消耗,则可以指定 ORDER BY NULL 禁止排序。使用 SELECT goods_id,count () FROM t GROUP BY goods_id ORDER BY NULL;

  1. 禁止不必要的 ORDER BY 排序

SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC; 使用 SELECT count (1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;

复制代码
复制代码

9 、总结

  • 任何东西不应过重关注其外表,要注重内在的东西,往往绚丽的外表下会有对应的负担和损耗。
  • mysql 数据库的监控支持通过 SQL 方式从 performance_schema 库中访问对应的表数据,前提是初始化此库并开启监控数据写入。
  • 对于监控而言,不在于手段的多样性,而需要明白监控的本质,以及需要的监控项内容,找到符合自身项目特色的监控方式。
  • 在选择监控工具对 mysql 监控时,需要关注监控工具本身对于数据库服务器的消耗,不要影响到其自身的使用。

 

--------------------------5、如何查看Mysql 库、表大小

#1.查看所有数据大小

复制代码

#1.查询所有数据的大小
mysql> use information_schema;

mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;
+---------+
| data |
+---------+
| 51.27MB |
+---------+
1 row in set (0.01 sec)

复制代码

#2.查看指定数据库的大小

复制代码

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='world';
+--------+
| data |
+--------+
| 0.58MB |
+--------+
1 row in set (0.00 sec)

复制代码

 

#3.查看指定数据库的某个表的大小

复制代码
复制代码

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='world' and table_name='city';
+--------+
| data |
+--------+
| 0.39MB |
+--------+
1 row in set (0.00 sec)

复制代码

 #4.mysql中的round函数

复制代码

mysql中的round函数和decimal方法使用区别
#1.decimal(10,2),表示最终得到的结果:
整数部分位数+小数部分位数<=10,小数部分位数2,如图示例第一条。
如果保留位数过大,可以使用decimal(13,2)、decimal(15,2)等等。
举个栗子:
select orderid
,driverId
,vipStartTime
,vipEndTime
,case when buy=0 then 0 else cast(actualMoney/buy as decimal(10,2)) end as avg_amount
, month(vipEndTime)-month('2021-02-01 00:00:00') as moncnt
,(case when buy=0 then 0 else cast(actualMoney/buy as decimal(10,2)) end)*( month(vipEndTime)-month('2021-02-28 23:59:59')) as aa

#2、round函数就是返回一个数值,该数值是按照指定的小数位数进行四舍五入运算的结果,round函数的语法是:ROUND(number,num_digits),即:Round(数值,保留的小数位数)
Number:需要进行四舍五入的数字。
Num_digits:指定的位数,按此位数进行四舍五入。
其中,如果 num_digits 大于 0,则四舍五入到指定的小数位。
如果 num_digits 等于 0,则四舍五入到最接近的整数。
如果 num_digits 小于 0,则在小数点左侧进行四舍五入。
=ROUND(3.19, 1) 将 3.19 四舍五入到一个小数位 (3.2)
=ROUND(2.649, 1) 将 2.649 四舍五入到一个小数位 (2.6)
=ROUND(-5.574, 2) 将 -5.574 四舍五入到两小数位 (-5.57)
=ROUND(18.8, -1) 将 18.8 四舍五入到小数点左侧一位 (20)。这个参数-1表示取整到十位数。

复制代码

--------------------------6、InnoDB 事务详解

1.介绍

事务:Transaction (交易)。 伴随着交易类的业务出现的概念(工作模式)
交易?
物换物,等价交换。
货币换物,等价交换。
虚拟货币换物(虚拟物品),等价交换。
现实生活中怎么保证交易“和谐” ,法律、道德等规则约束。
数据库中为了保证线上交易的“和谐”,加入了“事务”工作机制。

 

2.事务控制语句

 

复制代码
#1.标准(显示)的事务控制语句
#开启事务
begin; start transaction;
#提交事务
commit;
# 回滚事务
rollback;
注意:
事务生命周期中,只能使用DML语句(select、update、delete、insert)

#2.事务的生命周期演示:
mysql> use world
mysql> begin;
mysql> delete from city where id=1;
mysql> update city set countrycode='CHN' where id=2;
mysql> commit;

#3.隐式提交:
begin
a
b
begin
SET AUTOCOMMIT = 1
导致提交的非事务语句:
DDL语句: (ALTER、CREATE 和 DROP)
DCL语句: (GRANT、REVOKE 和 SET PASSWORD)
锁定语句:(LOCK TABLES 和 UNLOCK TABLES)
导致隐式提交的语句示例:
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE

#4.隐式回滚
会话窗口被关闭。
数据库关闭 。
出现事务冲突(死锁)。
复制代码

 

3.事务的ACID

 

复制代码
A: 原子性
不可再分性:一个事务生命周期中的DML语句,要么全成功要么全失败,不可以出现中间状态。
主要通过:undo保证的。

C:一致性
事务发生前,中,后,数据都最终保持一致。
CR + DWB

I:隔离性
事务操作数据行的时候,不会受到其他事务的影响。
读写隔离: 隔离级别、MVCC
写写隔离: 锁、隔离级别

D: 持久性
一但事务提交,永久生效(落盘)。
redo保证 ckpt。
复制代码

 

4. 隔离级别

复制代码
#1.作用
实现事务工作期间的“读”的隔离
读? ----》 数据页(记录)的读

#2.级别类型(默认级别)
mysql> show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |     #称之为RR级别
+-----------------------+-----------------+
1 row in set (0.00 sec)

# RU : READ-UNCOMMITTED 读未提交
可以读取到事务未提交的数据。隔离性差,会出现脏读(当前内存读),不可重复读,幻读问题
#########################################
先将隔离级别设置为RU
mysql> set global transaction_isolation='READ-UNCOMMITTED'; 退出数据库重新连接数据库
#A事务:
mysql> select * from city where id <5;
+----+----------------+-------------+----------+------------+
| ID | Name           | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
|  1 | Kabul          | AFG         | Kabol    |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar |     237500 |
|  3 | Herat          | AFG         | Herat    |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh    |     127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update city set population='1000' where id =1;
Query OK, 1 row affected (0.00 sec)

#B事务:
mysql> begin;
mysql> select * from city where id <5;
+----+----------------+-------------+----------+------------+
| ID | Name           | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
|  1 | Kabul          | AFG         | Kabol    |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar |     237500 |
|  3 | Herat          | AFG         | Herat    |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh    |     127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)

mysql> select * from city where id <5;
+----+----------------+-------------+----------+------------+
| ID | Name           | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
|  1 | Kabul          | AFG         | Kabol    |       1000 |     #查到未提交数据
|  2 | Qandahar       | AFG         | Qandahar |     237500 |
|  3 | Herat          | AFG         | Herat    |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh    |     127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)
##########################################################################

# RC : READ-COMMITTED 读已提交(可以用):
可以读取到事务已提交的数据。隔离性一般,不会出现脏读问题,但是会出现不可重复读,幻读问题
#####################################################################
mysql> set global transaction_isolation='READ-COMMITTED'; 退出数据库重新连接数据库
#A事务:
mysql> select * from city where id <5;
+----+----------------+-------------+----------+------------+
| ID | Name           | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
|  1 | Kabul          | AFG         | Kabol    |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar |     237500 |
|  3 | Herat          | AFG         | Herat    |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh    |     127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update city set population='1000' where id =1;
Query OK, 1 row affected (0.00 sec)

#B事务:
mysql> begin;
mysql> select * from city where id <5;
+----+----------------+-------------+----------+------------+
| ID | Name           | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
|  1 | Kabul          | AFG         | Kabol    |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar |     237500 |
|  3 | Herat          | AFG         | Herat    |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh    |     127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)

mysql> select * from city where id <5;
+----+----------------+-------------+----------+------------+
| ID | Name           | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
|  1 | Kabul          | AFG         | Kabol    |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar |     237500 |
|  3 | Herat          | AFG         | Herat    |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh    |     127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)

mysql> select * from city where id <5;
+----+----------------+-------------+----------+------------+
| ID | Name           | CountryCode | District | Population |
+----+----------------+-------------+----------+------------+
|  1 | Kabul          | AFG         | Kabol    |       1000 |  #只有等A事务commit后才可以查到提交数据
|  2 | Qandahar       | AFG         | Qandahar |     237500 |
|  3 | Herat          | AFG         | Herat    |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh    |     127800 |
+----+----------------+-------------+----------+------------+
4 rows in set (0.00 sec)


# RR : REPEATABLE-READ 可重复读(默认) :
防止脏读(当前内存读),不可重复读,幻读问题。需要配合锁机制来避免幻读。
# SE : SERIALIZABLE 可串行化

#3.修改级别
set global transaction_isolation='READ-UNCOMMITTED';设置OK后需要退出重新连接数据库才生效


结论: 隔离性越高,事务的并发读就越差。
结论: 一般情况下RC就够用了。
select for update
复制代码
一、OLTP(TP)
On-Line Transaction Processing 联机事务处理过程(OLTP)
复制代码
复制代码
1.也称为面向交易的处理过程,其基本特征是前台接收的用户数据可以立即传送到计算中心进行处理,并在很短的时间内给出处理结果,是对用户操作快速响应的方式之一。

这样做的最大优点是可以即时地处理输入的数据,及时地回答。也称为实时系统(Real time System)。衡量联机事务处理结果的一个重要指标是系统性能,具体体现为实时请求-响应时间(Response Time),即用户在终端上输入数据之后,到计算机对这个请求给出答复所需要的时间。OLTP是由前台、应用、数据库共同完成的,处理快慢以及处理程度取决于数据库引擎、服务器、应用引擎。

OLTP 数据库旨在使事务应用程序仅写入所需的数据,以便尽快处理单个事务。

2.OLTP特征

2.1 支持大量并发用户定期添加和修改数据。

2.2 反映随时变化的单位状态,但不保存其历史记录。

2.3 包含大量数据,其中包括用于验证事务的大量数据。

2.4 结构复杂。

2.5 可以进行优化以对事务活动做出响应。

2.6 提供用于支持单位日常运营的技术基础结构。

2.7 个别事务能够很快地完成,并且只需访问相对较少的数据。OLTP 旨在处理同时输入的成百上千的事务。

2.8 实时性要求高。

2.9 数据量不是很大。

2.10 交易一般是确定的,所以OLTP是对确定性的数据进行存取。(比如存取款都有一个特定的金额)

2.11 并发性要求高并且严格的要求事务的完整、安全性。(比如这种情况:有可能你和你的家人同时在不同的银行取同一个帐号的款)。
复制代码
复制代码

二、OLAP

复制代码
复制代码
联机分析处理OLAP是一种软件技术,它使分析人员能够迅速、一致、交互地从各个方面观察信息,以达到深入理解数据的目的。
它具有FASMI(Fast Analysis of Shared Multidimensional Information),即共享多维信息的快速分析的特征。
其中F是快速性(Fast),指系统能在数秒内对用户的多数分析要求做出反应;A是可分析性(Analysis),指用户无需编程就可以定义新的专门计算,
将其作为分析的一部 分,并以用户所希望的方式给出报告;
M是多维性(Multi—dimensional),指提供对数据分析的多维视图和分析;
I是信息性(Information),指能及时获得信息,并且管理大容量信息。
复制代码
复制代码

OLAP展现在用户面前的是一幅幅多维视图。

 

 

 

复制代码
复制代码
维(Dimension):是人们观察数据的特定角度,是考虑问题时的一类属性,属性集合构成一个维(时间维、地理维等)。

维的层次(Level):人们观察数据的某个特定角度(即某个维)还可以存在细节程度不同的各个描述方面(时间维:日期、月份、季度、年)。

维的成员(Member):维的一个取值,是数据项在某维中位置的描述。(“某年某月某日”是在时间维上位置的描述)。

度量(Measure):多维数组的取值。(2000年1月,上海,笔记本电脑,0000)。

OLAP的基本多维分析操作有钻取(Drill-up和Drill-down)、切片(Slice)和切块(Dice)、以及旋转(Pivot)等。

钻取:是改变维的层次,变换分析的粒度。它包括向下钻取(Drill-down)和向上钻取(Drill-up)/上卷(Roll-up)。Drill-up是在某一维上将低层次的细节数据概括到高层次的汇总数据,或者减少维数;而Drill-down则相反,它从汇总数据深入到细节数据进行观察或增加新维。

切片和切块:是在一部分维上选定值后,关心度量数据在剩余维上的分布。如果剩余的维只有两个,则是切片;如果有三个或以上,则是切块。

旋转:是变换维的方向,即在表格中重新安排维的放置(例如行列互换)。

数据仓库与OLAP的关系是互补的,现代OLAP系统一般以数据仓库作为基础,即从数据仓库中抽取详细数据的一个子集并经过必要的聚集存储到OLAP存储器中供前端分析工具读取。

OLAP系统按照其存储器的数据存储格式可以分为关系OLAP(RelationalOLAP,简称ROLAP)、多维OLAP(MultidimensionalOLAP,简称MOLAP)和混合型OLAP(HybridOLAP,简称HOLAP)三种类型。

ROLAP
ROLAP将分析用的多维数据存储在关系数据库中并根据应用的需要有选择的定义一批实视图作为表也存储在关系数据库中。不必要将每一个SQL查询都作为实视图保存,只定义那些应用频率比较高、计算工作量比较大的查询作为实视图。对每个针对OLAP服务器的查询,优先利用已经计算好的实视图来生成查询结果以提高查询效率。同时用作ROLAP存储器的RDBMS也针对OLAP作相应的优化,比如并行存储、并行查询、并行数据管理、基于成本的查询优化、位图索引、SQL的OLAP扩展(cube,rollup)等等。

MOLAP
MOLAP将OLAP分析所用到的多维数据物理上存储为多维数组的形式,形成“立方体”的结构。维的属性值被映射成多维数组的下标值或下标的范围,而总结数据作为多维数组的值存储在数组的单元中。由于MOLAP采用了新的存储结构,从物理层实现起,因此又称为物理OLAP(PhysicalOLAP);而ROLAP

HOLAP
由于MOLAP和ROLAP有着各自的优点和缺点(如下表所示),且它们的结构迥然不同,这给分析人员设计OLAP结构提出了难题。为此一个新的OLAP结构——混合型OLAP(HOLAP)被提出,它能把MOLAP和ROLAP两种结构的优点结合起来。迄今为止,对HOLAP还没有一个正式的定义。但很明显,HOLAP结构不应该是MOLAP与ROLAP结构的简单组合,而是这两种结构技术优点的有机结合,能满足用户各种复杂的分析请求。
复制代码
复制代码

 

 三.HTAP

复制代码
复制代码
数据库系统一般可以按照负载类型分成操作型数据库(Operational Support System)和决策型数据库(Decision Support System)。操作型数据库主要用于应对日常流水类业务,主要是面向消费者类的业务;决策型数据库主要应对的是企业报表类,可视化等统计类业务,主要面向企业类的业务。

针对两类系统的数据管理和系统设计方式都有很大差异。 (1)对OLTP的数据模型采用基本的约束E-R图模型,而OLAP的数据模型则需要采用特殊的“星型模型”,数据立方等数据仓库相关的技术。 (2)对OLTP的数据存储通常采用行式组织,而OLAP采用列式组织。 (3)OLTP的业务通常对实时要求比OLAP高很多。 (4)传统的数据库,为了同时支持两类业务。通常采用两个数据源, 分别对两套系统进行优化设计。

OLTP的数据定期会通过etl(提取,转换,加载)工具把数据同步导入OLAP系统中。这就涉及到数据源滞后的问题。 OLAP的数据滞后,导致分析出来的结果时效性不够,对决策支持类系统的要求不够。比如说,双11期间,用户购物的行为和推荐系统的推荐结果之间的时间差越短,越有可能提高销量。

HTAP是混合 OLTP 和 OLAP 业务同时处理的系统,2014年Garnter公司给出了严格的定义:混合事务/分析处理(HTAP)是一种新兴的应用体系结构,它打破了事务处理和分析之间的“墙”。它支持更多的信息和“实时业务”的决策。

直接在单一数据源上不加区分的处理TP和AP的方案,目前还不能有效实现。

当前的方案是进行一个折中。采用快照的方式,分开处理OLTP和OLAP请求。让OLAP的请求在OLTP的最新的一致性快照上执行。同时对外暴露一套接口,从而从逻辑来看是一套系统。虽然内部是分开处理OLTP和OLAP的。

这种折衷方案,重要的一点,就是保证快照是尽可能的保持“新”,快照不能太过滞后OLTP的数据。这就需要系统频繁的做快照操作。

目前两种流行的方案,一个是采用linux的系统快照能力,提供HTAP服务的方案,比如Hyper数据库系统。另一种是类似hana的方案,定期生成增量数据,然后合并到AP系统。如下图。
复制代码
复制代码

 

 

 

OLTP:是对事务要求比较高。
OLAP:偏向数据存储、压缩、分析。

--------------------------7、MySQL进程号、连接ID、查询ID、InnoDB线程与系统线程如何对应 

或者当InnoDB引擎内部有semaphore wait时,想知道具体是哪个线程/查询引起的。多说一下,当有semaphore wait事件超过600秒的话,InnoDB会发出crash信号:

InnoDB: ###### Diagnostic info printed to the standard error stream
2020-12-13T09:41:33.810011Z 0 [ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.
2020-12-13 10:41:33 0x7f3d92a4e700 InnoDB: Assertion failure in thread 139902430013184 in file ut0ut.cc line 917
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
09:41:33 UTC - mysqld got signal 6 ;

因此也要监控InnoDB的semaphore wait状态,一旦超过阈值,就要尽快报警并分析出问题原因,及时杀掉或停止引起等待的查询请求。

1、操作系统进程ID

MySQL是一个单进程多线程的服务程序,用 ps -ef|grep mysqld 就能看到其系统进程ID了。另外,当 my.cnf 配置文件中增加一行 innodb_status_file = 1 时,也会生成带有系统进程ID的innodb status 文件

[root@yejr.run]# ps -ef | grep mysqld
mysql    38801     1  0 Jun13 ?        00:03:30 /usr/local/GreatSQL-8.0.22/bin/mysqld --defaults-file=/mysql/data06/my.cnf

[root@yejr.run]# ls -la innodb_status.38801
-rw-r----- 1 mysql mysql 4906 Jun 14 14:26 innodb_status.38801

文件 innodb_status.pid 的作用是每隔15秒左右输出innodb引擎各种状态信息,和执行 SHOW ENGINE INNODB STATUS 的作用相同。二者的区别在于,前者(文件输出方式)的输出内容长度不受限制,而后者(命令行输出)则最多只显示1MB内容,更多的会被截断。所以务必设置 innodb_status_file = 1 选项。

Standard Monitor output is limited to 1MB when produced using the SHOW ENGINE INNODB STATUS statement. This limit does not apply to output written to server standard error output (stderr).

2、系统线程和MySQL连接ID、查询ID等的关系

从MySQL 5.7开始,performance_schema.threads 表增加 THREAD_OS_ID 列,用于记录MySQL内部线程对应的系统线程ID。

创建一个新连接,并执行下面的SQL:

[root@yejr.run]# mysql -S./mysql.sock -uroot -p mymgr
...
# 查到MySQL的连接ID是25(PROCESSLIST_ID = 25)
[root@yejr.run] [none]> show processlist;
+----+------+-----------+-------+---------+------+-------+------------------+-----------+---------------+
| Id | User | Host      | db    | Command | Time | State | Info             | Rows_sent | Rows_examined |
+----+------+-----------+-------+---------+------+-------+------------------+-----------+---------------+
| 25 | root | localhost | mymgr | Query   |    0 | init  | show processlist |         0 |             0 |
+----+------+-----------+-------+---------+------+-------+------------------+-----------+---------------+

[root@yejr.run] [mymgr]> begin; select *,sleep(1000) from t1 for update;
...  <-- 这个SQL会运行很长时间,方便我们观察

新开一个窗口,查看 pfs.threads 表:

[root@yejr.run] [performance_schema]> SELECT * FROM threads WHERE PROCESSLIST_ID=25\G
*************************** 1. row ***************************
          THREAD_ID: 65  <-- MySQL内部线程ID,也是PFS的内部计数器
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 25  <-- MySQL连接ID
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: mymgr
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 246
  PROCESSLIST_STATE: User sleep
   PROCESSLIST_INFO: select *,sleep(1000) from t1 for update  <-- 正在运行的SQL
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 58412  <-- 对应操作系统的线程ID
     RESOURCE_GROUP: USR_default

运行 ps -Lef 查看对应的系统线程

[root@yejr.run]# ps -Lef | grep 58412  <-- 上面查询pfs.threads看到 THREAD_OS_ID 列的值
mysql    38801     1 58412  0   40 14:46 ?        00:00:00 /usr/local/GreatSQL-8.0.22/bin/mysqld --defaults-file=/mysql/data06/my.cnf

如果设置了 general_log=1,那么也能看到 general_log 里有这样的记录:

[root@yejr.run]# cat yejr.run.log

/usr/local/GreatSQL-8.0.22/bin/mysqld, Version: 8.0.22-13 (Source distribution). started with:
Tcp port: 6001  Unix socket: mysql.sock
#几个列分别是:时间、连接ID、请求类型、详细的SQL
Time                 Id Command    Argument
...
2021-06-14T14:46:47.474393+08:00    25 Query select *,sleep(1000) from t1 for update  <-- 可以看到连接ID是25
...

再查询 pfs.events_statements_current 表:

[root@yejr.run] [performance_schema]> SELECT * FROM events_statements_current WHERE THREAD_ID = 65\G
              THREAD_ID: 65
               EVENT_ID: 8
           END_EVENT_ID: NULL
             EVENT_NAME: statement/sql/select
                 SOURCE: init_net_server_extension.cc:95
            TIMER_START: 82217983305961000
              TIMER_END: 82559992238886000
             TIMER_WAIT: 342008932925000
              LOCK_TIME: 206000000
               SQL_TEXT: select *,sleep(1000) from t1 for update
                 DIGEST: 4d7f4182dff4abc484010b73024b4afb35075e1b6592d14ce895a2b8764b8f46
            DIGEST_TEXT: SELECT * , `sleep` (?) FROM `t1` FOR UPDATE
         CURRENT_SCHEMA: mymgr
...
            SELECT_SCAN: 1
...
       NESTING_EVENT_ID: 6
     NESTING_EVENT_TYPE: TRANSACTION
    NESTING_EVENT_LEVEL: 0
           STATEMENT_ID: 87  <-- 查询ID,每次查询该ID值都会增加

执行 SHOW ENGINE INNODB STATUS\G 查看事务状态:

...
# 事务ID=9322,运行时长=252秒
---TRANSACTION 9322, ACTIVE 252 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
# MySQL连接ID=25,OS线程句柄 = 140442126739200(后面再介绍),查询ID=87(对应上面的 STATEMENT_ID)
MySQL thread id 25, OS thread handle 140442126739200, query id 87 localhost root User sleep
select *,sleep(1000) from t1 for update
...

3、OS thread handle和操作系统线程ID的对应关系

首先,OS thread handle 140442126739200 (OS thread handle是进程内部用于识别各个线程的内部ID),这里是个十进制的数值,需要先转成十六进制(有时候可能会直接用十六进制表示,这个问题也有人提出了 MDEV-17237):

[root@yejr.run] [performance_schema]> select lower(conv(140442126739200, 10, 16));
+--------------------------------------+
| lower(conv(140442126739200, 10, 16)) |
+--------------------------------------+
| 7fbb3b136700                         |
+--------------------------------------+

再利用 pstack 查询该句柄和操作系统线程ID的关联:

[root@yejr.run]# pstack `pidof mysqld` | grep 7fbb3b136700
Thread 2 (Thread 0x7fbb3b136700 (LWP 58412)):

可以看到 LWP = 58412,对应上面的 THREAD_OS_ID 值,LWP是Light-Weight Processes的缩写(轻量级进程)。用 pidstat 也能看到这个LWP:

[root@yejr.run]# pidstat -t -p 38801 | grep 58031
03:45:02 PM  1000         -     58031    0.00    0.00    0.00    0.00     2  |__mysqld

【特别提醒】运行pstack会短暂阻塞mysqld进程,所以请切勿在业务高峰期执行,除非万不得已。

有时候可能会看到类似下面的 innodb status:

SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 15143
--Thread 140585128785664 has waited at buf0flu.cc line 1209 for 237 seconds the semaphore:
SX-lock on RW-latch at 0x7fdb1fbe3f80 created in file buf0buf.cc line 1460
a writer (thread id 140584786024192) has reserved it in mode SX
number of readers 0, waiters flag 1, lock_word: 10000000
Last time read locked in file row0sel.cc line 3758
Last time write locked in file /export/home/pb2/build/sb_0-34537258-1560179931.8/mysql-5.7.27/storage/innobase/fsp/fsp0fsp.cc line 167

在上面这段信息中,线程 Thread 140585128785664 在 buf0flu.cc 代码1209行这里等待了237秒,想要请求 SX-lock 的 RW-latch,被另一个线程 thread id 140584786024192 给阻塞了,它持有的是 SX latch。

这时候,就可以利用pstack反查对应的LWP,再反查出PROCESSLIST_ID,以及正在执行的SQL。pfs.events_statements_current 中只能查到当前执行的SQL,可以通过 pfs.events_statements_history 查看最近执行过的SQL。

--------------------------8、MySQL 8.0 OOM(Out Of Memory)问题优化

1、什么叫OOM

全名称为:Out Of Memory

内存溢出已经是软件开发历史上存在了近40年的“⽼⼤难”问题。在操作系统上运⾏各种软件时,软件所需申请的内存远远超出了物理内存所承受的⼤⼩,就叫内存溢出。
内存溢出产⽣原因多种多样,当内存严重不⾜时,就很容易出现OOM

 

2、产生OOM原因

主要原因就是内存不足,其中performance_schema 功能在带给我们更多性能监控手段的同时,也占用了太多的内存

 

3、OOM优化

复制代码
优化一:innodb_buffer_pool_size 设置为最小128M,建议设置内存的75%-80%

innodb_buffer_pool_size 是MySQL内存占用的大户,首先拿它开刀,设置为最小的128M。

优化二:关闭performance_schema

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+
1 row in set (0.03 sec)

performance_schema 8.0版本默认设置为 ON,建议关闭,即关闭performance_schema 中的各项性能监控

关闭之后,MySQL 8.0 运行一周多时间,没有发生 OOM。performance_schema 功能在带给我们更多性能监控手段的同时,也占用了太多的内存,看来它是引起 MySQL OOM 的主要原因。

MySQL Bug 列表中有一个关于 performance_schema 的 Bug,它会占用大量内存,导致 MySQL 进程 OOM,所以如果内存不大,可以考虑将其关闭,或者升级到最新的 MySQL 版本。

优化三:调低各种buffer,cache参数

各种buffer, cache 参数,根据实际情况进行更改,只要不经常OOM,能稳定运行就好。

比如:

  • innodb_log_buffer_size
  • innodb_sort_buffer_size
  • join_buffer_size
  • key_buffer_size
  • myisam_sort_buffer_size
  • sort_buffer_size
  • binlog_cache_size
优化四:优化SQL

低效的 SQL ,尤其是执行计划中带 filesort 的 SQL,在高并发下,会占用大量的内存。可以通过添加索引,优化表结构,优化 SQL 语句,优化业务逻辑等等,减少慢 SQL 的产生,进而减少内存的占用

复制代码

--------------------------9、批量删除数据命令区别:drop、truncate、delete

批量删除数据有三种常见的方法:                          #####面试题

(1) drop table:当不需要该表时,可以使用该方法。

(2) truncate table:删除所有数据,同时保留表,速度很快。

     可以理解为,drop table然后再create table。

(3) delete from table:可以删除所有数据,也能保留表,但性能较差。也可以带where条件删除部分数据,灵活性强。

     #1

  • truncate是DDL语句,它不存在所谓的“事务回滚”;
  • delete是DML语句,它执行完是可以rollback的。

     #2

  • truncate table返回值是0;
  • delete from table返回值是被删除的行数。

    #3 InnoDB支持一个表一个文件,此时:

  • truncate会一次性把表干掉,且不会激活触发器,速度非常快;
  • delete from table则会一行一行删除,会激活触发器,速度比较慢。

          delete数据,是要记录日志的,truncate表不需要记录日志。

    #4.当表中有列被其它表作为外键(foreign key)时:

  • truncate会是失败;
  • delete则会成功。

         这类数据删除失败很容易定位问题,因为报错提示简单易懂。

    #5. 当表中有自增列时:

  • truncate会使得自增列计数复原;
  • delete所有数据后,自增列计数并不会从头开始。

          所以,delete所有数据后,自增列计数的这个行为,往往不是用户想要的,所以是一个潜在坑。

--------------------------10、记录锁(Record LockRecord)、间隙锁(Gap Lock)、 Next-Key Lock

1. Record LockRecord

Lock 也就是我们所说的记录锁,记录锁是对索引记录的锁,注意,它是针对索引记录,即它只锁定记录这一行数据

复制代码
复制代码
1.将系统变量 innodb_status_output_locks 设置为 默认为OFF

mysql> show variables like 'innodb_status_output_locks';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_status_output_locks | OFF |
+----------------------------+-------+
1 row in set (0.00 sec),

 

mysql> set global innodb_status_output_locks=ON;
Query OK, 0 rows affected (0.00 sec)

 

mysql> show variables like 'innodb_status_output_locks';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_status_output_locks | ON |
+----------------------------+-------+
1 row in set (0.00 sec)

 

2.执行如下 SQL,锁定一行数据,此时会自动为表加上 IX 锁:

mysql> begin;    #开始事务
Query OK, 0 rows affected (0.00 sec)

 

mysql> select * from city where id=1 for update;     
+----+-------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+-------+-------------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1000 |
+----+-------+-------------+----------+------------+
1 row in set (0.00 sec)

 

mysql> show engine innodb status\G;                  #查看 InnoDB 存储引擎的情况
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2022-05-07 19:07:18 139841320953600 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 9 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 1284 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1
OS WAIT ARRAY INFO: signal count 1
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS                                      #主要关注这里的TRANSACTIONS
------------
Trx id counter 18190
Purge done for trx's n:o < 18183 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421316317542832, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421316317542024, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 18189, ACTIVE 4 sec
2 lock struct(s), heap size 1128, 1 row lock(s)
MySQL thread id 8, OS thread handle 139841320953600, query id 25 localhost root starting
show engine innodb status
TABLE LOCK table `world`.`city` trx id 18189 lock mode IX     **************
RECORD LOCKS space id 5 page no 6 n bits 248 index PRIMARY of table `world`.`city` trx id 18189 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0  ********
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000000430e; asc C ;;
2: len 7; hex 02000001140151; asc Q;;
3: len 30; hex 4b6162756c20202020202020202020202020202020202020202020202020; asc Kabul ; (total 35 bytes);
4: len 3; hex 414647; asc AFG;;
5: len 20; hex 4b61626f6c202020202020202020202020202020; asc Kabol ;;
6: len 4; hex 800003e8; asc ;;

--------

TABLE LOCK table `world`.`city` trx id 18189 lock mode IX

这句就是说事务 id 为 18189 的事务,为 city 表添加了意向排他锁(IX)。

RECORD LOCKS space id 5 page no 6 n bits 248 index PRIMARY of table `world`.`city` trx id 18189 lock_mode X locks rec but not gap
Record lock, 这句就是一个锁结构的记录,这里的索引是 PRIMARY,加的锁也是正儿八经的记录锁(not gap)。

LOCKS REC BUT NOT GAP,有为句就说明这是一个记录锁

Record Lock 和我们之前所讲的 S 锁以及 X 锁有什么区别呢?S 锁是共享锁,X 锁是排他锁,当我们加 S 锁或者 X 锁的时候,如果用到了索引,锁加在了某一条具体的记录上,那么这个锁也是一个记录锁(其实,记录锁,S 锁,X 锁,概念有一些重复的地方,但是描述的重点不一样)。

复制代码
复制代码

2. Gap Lock

Gap Lock 也叫做间隙锁,它的存在可以解决幻读问题,另外需要注意,Gap Lock 也只在 REPEATABLE READ 隔离级别下有效。先来看看什么是幻读,我们来看如下一个表格:

    有两个会话,A 和 B,先在会话 A 中开启事务,然后查询 age 为 99 的用户总数,注意使用当前读,因为在默认的隔离级别下,默认的快照读并不能读到其他事务提交的数据,至于快照读和当前读的区别。当会话 A 中第一次查询过后,会话 B 中向数据库添加了一行记录,等到会话 A 中第二次查询的时候,就查到了和第一次查询不一样的结果,这就是幻读(注意幻读专指数据插入引起的不一致)。

    在 MySQL  8.0默认的隔离级别 REPEATABLE READ 下,上图所描述的情况无法复现。无法复现的原因在于,在 MySQL 的 REPEATABLE READ 隔离级别中,它已经帮我们解决了幻读问题,解决的方案就是 Gap Lock。

    之所以出现幻读的问题,是因为记录之间存在缝隙,用户可以往这些缝隙中插入数据,这就导致了幻读问题,如下图:

id 之间有缝隙,有缝隙就有漏洞。前面我们所说的记录锁只能锁住一条具体的记录,但是对于记录之间的空隙却无能无力,这就导致了幻读(其他事务可往缝隙中插入数据)。现在 Gap Lock 间隙锁,就是要把这些记录之间的间隙也给锁住,间隙锁住了,就不用担心幻读问题了,这也是 Gap Lock 存在的意义。

给一条记录加 Gap Lock,是锁住了这条记录前面的空隙,例如给 id 为 1 的记录加 Gap Lock,锁住的范围是 (-∞,1),给 id 为 3 的记录加 Gap Lock,锁住的范围是 (1,3),那么 id 为 10 后面的空隙怎么锁定呢?MySQL 提供了一个 Supremum 表示当前页面中的最大记录,所以最后针对 Supremum 锁住的范围就是 (10,+∞),这样,所有的间隙都被覆盖到了,由于锁定的是间隙,所以都是开区间。

 

复制代码
复制代码
简单的例子:
1.创建一个表:

mysql> CREATE TABLE `T4` (
-> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
-> `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
-> `age` int(11) DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> KEY `age` (`age`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

插入以下数据:

mysql> insert into T4 values('1','xiaoli',99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into T4 values('2','xiaoming',89);
Query OK, 1 row affected (0.00 sec)

mysql> insert into T4 values('3','xiaoliu',79);
Query OK, 1 row affected (0.00 sec)

mysql> select * from T4;
+----+----------+------+
| id | username | age |
+----+----------+------+
| 1 | xiaoli | 99 |
| 2 | xiaoming | 89 |
| 3 | xiaoliu | 79 |
+----+----------+------+
3 rows in set (0.00 sec)

 

2.执行如下 SQL,锁定一行数据,此时也会产生间隙锁

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from T4 force index(age) where age=89 for update;
+----+----------+------+
| id | username | age |
+----+----------+------+
| 2 | xiaoming | 89 |
+----+----------+------+
1 row in set (0.00 sec)

 

3.查看innodb引擎状况

mysql> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2022-05-07 19:56:28 139841320953600 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 22 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 6 srv_active, 0 srv_shutdown, 4210 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3
OS WAIT ARRAY INFO: signal count 3
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 18208
Purge done for trx's n:o < 18205 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421316317542832, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421316317542024, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 18207, ACTIVE 58 sec
4 lock struct(s), heap size 1128, 3 row lock(s)
MySQL thread id 8, OS thread handle 139841320953600, query id 37 localhost root starting
show engine innodb status
TABLE LOCK table `world`.`T4` trx id 18207 lock mode IX
RECORD LOCKS space id 8 page no 5 n bits 72 index age of table `world`.`T4` trx id 18207 lock_mode X   ****记录锁
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000059; asc Y;;
1: len 4; hex 00000002; asc ;;

RECORD LOCKS space id 8 page no 4 n bits 72 index PRIMARY of table `world`.`T4` trx id 18207 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0   *****间隙锁

一个间隙锁的加锁记录,可以看到,在某一个记录之前加了间隙锁。这就是间隙锁。

非常重要注意:Gap Lock 只在 REPEATABLE READ 隔离级别下有效。

复制代码
复制代码

3. Next-Key Lock

既想锁定一行,又想锁定行之间的记录,那么就是 Next-Key Lock 了,换言之,Next-Key Lock 是 Record Lock 和 Gap Lock 的结合体。

Next-Key Lock 的加锁规则:

锁的范围是左开右闭。
如果是唯一非空索引的等值查询,Next-Key Lock 会退化成 Record Lock。
普通索引上的等值查询,向后遍历时,最后一个不满足等值条件的时候,Next-Key Lock 会退化成 Gap Lock。
复制代码
复制代码
举例:
创建一个学生表:

mysql> CREATE TABLE `student` (
-> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
-> `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
-> `score` double NOT NULL,
-> PRIMARY KEY (`id`),
-> UNIQUE KEY `score` (`score`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected, 1 warning (0.02 sec)

插入数据:

mysql> insert into student values ('1','a','90');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values ('2','b','89');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values ('3','c','95');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values ('4','d','80');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values ('5','e','79');
Query OK, 1 row affected (0.01 sec)

 

  查看数据:

mysql> select * from student;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | a | 90 |
| 2 | b | 89 |
| 3 | c | 95 |
| 4 | d | 80 |
| 5 | e | 79 |
+----+------+-------+
5 rows in set (0.00 sec)

执行以下SQL:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student force index(score) where score=90 for update;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | a | 90 |
+----+------+-------+
1 row in set (0.00 sec)

查看引擎状况:

mysql> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2022-05-07 20:14:25 139841320953600 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 40 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 12 srv_active, 0 srv_shutdown, 5274 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 9
OS WAIT ARRAY INFO: signal count 9
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 18231
Purge done for trx's n:o < 18229 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421316317542832, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421316317542024, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 18230, ACTIVE 51 sec
3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 8, OS thread handle 139841320953600, query id 49 localhost root starting
show engine innodb status
TABLE LOCK table `world`.`student` trx id 18230 lock mode IX            ****************
RECORD LOCKS space id 9 page no 5 n bits 72 index score of table `world`.`student` trx id 18230 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0          **************************

由于 score 是唯一非空索引,所以 Next-Key Lock 会退化成 Record Lock,换句话说,这行 SQL 只给 score 为 90 的记录加锁,不存在 Gap Lock,即我们新开一个会话,插入一条 score 为 88 的记录也是 OK 的。

mysql> insert into student values ('6','f','88');
Query OK, 1 row affected (0.00 sec)

由于并不存在 score 为 91 的记录,所以这里会产生一个范围为 (90,95) 的间隙锁,我们执行如下 SQL 可以验证:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student force index(score) where score=91 for update;
Empty set (0.00 sec)

另外一个会话中插入一个90.1的score,出现间隙锁(90~95),90~95数据都添加不进去。

mysql> insert into student values ('7','g','90.1');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into student values ('8','h','90');
ERROR 1062 (23000): Duplicate entry '90' for key 'student.score'

mysql> insert into student values ('9','j','94.9');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> insert into student values ('10','l','95.1');    ********插入95以上数据可以
Query OK, 1 row affected (0.00 sec)

总结:

可以看到,90.1、94.9 都会被阻塞(我按了 Ctrl C,所以大家看到查询终止)。

90、95 则不符合唯一非空索引的条件。

95.1 则可以插入成功

复制代码

--------------------------11、线程

1.mysql 前台线程

复制代码

mysql> show processlist ;
+----+-----------------+-----------+--------------------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+--------------------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 5411 | Waiting on empty queue | NULL |
| 9 | root | localhost | information_schema | Query | 0 | init | show processlist |
+----+-----------------+-----------+--------------------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)

 

mysql> show full processlist;
+----+-----------------+-----------+--------------------+---------+------+------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+--------------------+---------+------+------------------------+-----------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 5430 | Waiting on empty queue | NULL |
| 9 | root | localhost | information_schema | Query | 0 | init | show full processlist |
+----+-----------------+-----------+--------------------+---------+------+------------------------+-----------------------+
2 rows in set (0.00 sec)

 

mysql> select * from information_schema.processlist ;
+----+-----------------+-----------+--------------------+---------+------+------------------------+----------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+-----------------+-----------+--------------------+---------+------+------------------------+----------------------------------------------+
| 9 | root | localhost | information_schema | Query | 0 | executing | select * from information_schema.processlist |
| 5 | event_scheduler | localhost | NULL | Daemon | 5459 | Waiting on empty queue | NULL |
+----+-----------------+-----------+--------------------+---------+------+------------------------+----------------------------------------------+
2 rows in set (0.00 sec)

复制代码

 

2.mysql 后台线程

复制代码

mysql> select * from performance_schema.threads where threads.PROCESSLIST_ID=9\G;
*************************** 1. row ***************************
THREAD_ID: 48                                                        #通过这个THREAD_ID 号 去查哪条语句慢
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 9                                                    #可以通过这个PROCESSLIST_ID进程查到前台进程,9号
PROCESSLIST_USER: root
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: information_schema
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 0
PROCESSLIST_STATE: executing
PROCESSLIST_INFO: select * from performance_schema.threads where threads.PROCESSLIST_ID=9
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 8586                                                  #OS 层面的进程号
RESOURCE_GROUP: USR_default
1 row in set (0.00 sec)

复制代码

3.查看top 来查看哪个进程占用CPU,内存高,通过进程号PID查看到数据库层面到找在执行的语句或者进程

复制代码

top - 22:03:46 up 7:38, 2 users, load average: 0.23, 0.13, 0.08
Tasks: 105 total, 3 running, 102 sleeping, 0 stopped, 0 zombie
%Cpu(s): 0.3 us, 0.3 sy, 0.0 ni, 99.3 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 995896 total, 203868 free, 494160 used, 297868 buff/cache
KiB Swap: 1048572 total, 1048572 free, 0 used. 329464 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
8533 mysql 20 0 1297652 402488 17260 S 0.3 40.4 0:17.79 mysqld
8683 root 20 0 0 0 0 S 0.3 0.0 0:02.26 kworker/0:0
8710 root 20 0 161980 2204 1556 R 0.3 0.2 0:00.05 top
1 root 20 0 125728 4180 2572 S 0.0 0.4 0:03.25 systemd
2 root 20 0 0 0 0 S 0.0 0.0 0:00.01 kthreadd
3 root 20 0 0 0 0 S 0.0 0.0 0:01.01 ksoftirqd/0
5 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/0:0H
6 root 20 0 0 0 0 S 0.0 0.0 0:01.05 kworker/u256:0
7 root rt 0 0 0 0 S 0.0 0.0 0:00.00 migration/0
8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 rcu_bh
9 root 20 0 0 0 0 R 0.0 0.0 0:01.39 rcu_sched
10 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 lru-add-drain
11 root rt 0 0 0 0 S 0.0 0.0 0:00.39 watchdog/0
13 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kdevtmpfs

锁定哪个PID占用高

top - 22:05:41 up 7:40, 2 users, load average: 0.03, 0.09, 0.07
Threads: 38 total, 0 running, 38 sleeping, 0 stopped, 0 zombie
%Cpu(s): 6.2 us, 0.0 sy, 0.0 ni, 93.8 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 995896 total, 203992 free, 494036 used, 297868 buff/cache
KiB Swap: 1048572 total, 1048572 free, 0 used. 329588 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
8533 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.83 mysqld
8536 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.22 mysqld
8537 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.25 mysqld
8539 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.19 mysqld
8541 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.24 mysqld
8542 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.24 mysqld
8543 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.25 mysqld
8544 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.24 mysqld
8545 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.31 mysqld
8546 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.32 mysqld
8547 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.30 mysqld
8548 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.45 mysqld
8549 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.70 mysqld
8550 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:01.93 mysqld
8551 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:01.94 mysqld
8552 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:01.95 mysqld
8553 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:02.01 mysqld
8554 mysql 20 0 1297652 402488 17260 S 0.0 40.4 0:00.40 mysqld

通过PID号查到

mysql> select * from performance_schema.threads where threads.thread_os_id=8533\G;
*************************** 1. row ***************************
THREAD_ID: 1                               #
NAME: thread/sql/main
TYPE: BACKGROUND
PROCESSLIST_ID: NULL
PROCESSLIST_USER: NULL
PROCESSLIST_HOST: NULL
PROCESSLIST_DB: mysql
PROCESSLIST_COMMAND: NULL
PROCESSLIST_TIME: 6457
PROCESSLIST_STATE: NULL
PROCESSLIST_INFO: NULL
PARENT_THREAD_ID: NULL
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: NULL
THREAD_OS_ID: 8533                                #OS层进程号
RESOURCE_GROUP: SYS_default
1 row in set (0.00 sec)

 

#再通过 THREAD_ID: 1 查到线程 

select * from performance_schema.events_statements_history where events_statements_history.THREAD_ID='1'\G;

 

mysql> select * from performance_schema.events_statements_history where events_statements_history.THREAD_ID=48\G;
*************************** 1. row ***************************
THREAD_ID: 48
EVENT_ID: 144
END_EVENT_ID: 144
EVENT_NAME: statement/sql/select
SOURCE: init_net_server_extension.cc:96
TIMER_START: 6950724493749000
TIMER_END: 6950724890011000
TIMER_WAIT: 396262000
LOCK_TIME: 163000000
SQL_TEXT: select * from performance_schema.events_statements_history where THREAD_ID=48
DIGEST: ab6f69218695e267b8f701180da603950ed43ba9bc61078687a72522066428a0
DIGEST_TEXT: SELECT * FROM `performance_schema` . `events_statements_history` WHERE `THREAD_ID` = ?
CURRENT_SCHEMA: information_schema
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 10
ROWS_EXAMINED: 10
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
NESTING_EVENT_LEVEL: 0
STATEMENT_ID: 142

复制代码

 4.Master Thread

复制代码
复制代码
作用:

a. 控制刷新脏页到磁盘(CKPT)
b. 控制日志缓冲刷新到磁盘(log buffer ---> redo)
c. undo页回收
d. 合并插入缓冲(change buffer)
e. 控制IO刷新数量

mySQL> show variables like '%cap%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| admin_ssl_capath | |
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
| mySQLx_ssl_capath | |
| ssl_capath | |
+------------------------+-------+


mySQL> show variables like '%pct%';
+------------------------------------------+-----------+
| Variable_name | Value |
+------------------------------------------+-----------+
| innodb_buffer_pool_dump_pct | 25 |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_pad_pct_max | 50 |
| innodb_idle_flush_pct | 100 |
| innodb_log_spin_cpu_pct_hwm | 50 |
| innodb_max_dirty_pages_pct | 90.000000 |
| innodb_max_dirty_pages_pct_lwm | 10.000000 |
| innodb_old_blocks_pct | 37 |
+------------------------------------------+-----------+

 

#参数解释:
innodb_io_capacity               表示每秒刷新脏页的数量,默认为200。
innodb_max_dirty_pages_pct       设置出发刷盘的脏页百分比(70%~90%),即当脏页占到缓冲区数据达到这个百分比时,就会刷新innodb_io_capacity 个脏页到磁盘。
参数

innodb_adaptive_flushing = ON  (自适应地刷新),该值影响每秒刷新脏页的数量。

#原来的刷新规则是:脏页在缓冲池所占的比例小于innodb_max_dirty_pages_pct时,不刷新脏页;大于innodb_max_dirty_pages_pct时,刷新100个脏页。随着innodb_adaptive_flushing参数的引入,InnoDB存储引擎会通过一个名为buf_flush_get_desired_flush_rate的函数来判断需要刷新脏页最合适的数量。粗略地翻阅源代码后发现buf_flush_get_desired_flush_rate通过判断产生重做日志(redo log)的速度来决定最合适的刷新脏页数量。因此,当脏页的比例小于innodb_max_dirty_pages_pct时,也会刷新一定量的脏页。

#脏页

#1.什么叫脏页:
平时很快的更新操作,都是在写内存和日志。 他并不会马上同步到磁盘数据页,这时内存数据页跟磁盘数据页内容不一致,我们称之为脏页
当内存数据页跟磁盘数据页内容不一致的时候,我们称这个内存页为“脏页”。
内存数据写入到磁盘后,内存和磁盘上的数据页的内容就一致了,称为“干净页”。
一条 SQL 语句,正常执行的时候特别快,偶尔很慢。那这时候可能就是在将脏页同步到磁盘中了
#2.什么时候会引起将脏页同步到磁盘中?
(1) 当 redo log写满了。这时候系统就会停止所有的更新操作,将更新的这部分日志对应的脏页同步到磁盘中,此时所有的更新全部停止,此时写的性能变为0,必须待刷一部分脏页后才能更新,这时就会导致 sql语句 执行的很慢。
(2) 也可能是系统内存不足时,需要将一部分数据页淘汰掉,如果淘汰的是脏页,则需要先将脏页同步到磁盘,空出来的给别的数据页使用。
(3) MySQL 认为系统“空闲”的时候,反正闲着也是闲着反正有机会就同步到磁盘一些数据
(4) MySQL 正常关闭。这时候,MySQL 会把内存的脏页都同步到磁盘上,这样下次 MySQL 启动的时候,就可以直接从磁盘上读数据,启动速度会很快。

复制代码
复制代码

 

5.IO Thread

复制代码
复制代码

#通过以下命令查看IO线程,其中:写线程和读线程分别由innodb_write_threads和innodb_read_threads参数控制,默认都为4。
mySQL> show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2021-12-08 21:47:28 140335511410432 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 5 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 5 srv_active, 0 srv_shutdown, 3438 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: signal count 0
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 16393
Purge done for trx's n:o < 16390 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421810516540824, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421810516539968, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421810516539112, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)

复制代码

--------------------------12、MySQL 执行计划详解

1.执行计划解释

什么是执行计划呢?SQL是一种傻瓜式语言,每一个条件就是一个需求,访问的顺序不同就形成了不同的执行计划。MySQL必须做出选择,一次只能有一种访问路径,一个访问路径就是一个执行计划。
通常一条SQL有多个执行计划,那我们如何选择?MySQL数据库与Oracle一样,使用的是基于开销(cost)的优化器策略,那种执行开销更低,就意味着性能更好,速度更快,MySQL就选择哪一种。

2.执行计划用什么命令去查看SQL执行

复制代码

#1.Explain,可以查看SELECT,DELETE,INSERT,REPLACE,UPDATE语句的执行计划

mysql> help explain;
Name: 'EXPLAIN'
Description:
Syntax:
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
[explain_type]
{explainable_stmt | FOR CONNECTION connection_id}

{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] select_statement

explain_type: {
FORMAT = format_name
}

format_name: {
TRADITIONAL
| JSON
| TREE
}

explainable_stmt: {
SELECT statement
| TABLE statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}

The DESCRIBE and EXPLAIN statements are synonyms. In practice, the
DESCRIBE keyword is more often used to obtain information about table
structure, whereas EXPLAIN is used to obtain a query execution plan

(that is, an explanation of how MySQL would execute a query).

URL: https://dev.mysql.com/doc/refman/8.0/en/explain.html

 

mysql> explain select user,host,plugin from mysql.user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

需要注意的是:
1.在早期的MySQL版本中,使用EXTENDED查看扩展信息,目前默认已经启用了扩展信息的输出,因此该参数显得多余了,在MySQL 8.0中已经移除该参数。
2.在早期版本中,分区信息是使用EXPLAIN PARTITIONS输出的,目前已经默认开启了分区信息的输出,该参数也已经不再需要,在MySQL 8.0中已经移除该参数。
3.不能在同一个EXPLAIN中同时使用EXTENDED和PARTITIONS关键字,这2个关键字都不能与FORMAT关键字一起使用。

FORMAT参数用于选择输出格式,一共有2种输出格式:
-- TRADITIONAL  :以表格显示输出,默认模式
-- JSON              :以json格式输出

 

mysql> explain format=json select user from mysql.user;
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "0.85"
},
"table": {
"table_name": "user",
"access_type": "index",
"key": "PRIMARY",
"used_key_parts": [
"Host",
"User"
],
"key_length": "351",
"rows_examined_per_scan": 6,
"rows_produced_per_join": 6,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "0.25",
"eval_cost": "0.60",
"prefix_cost": "0.85",
"data_read_per_join": "3K"
},
"used_columns": [
"User"
]
}
}

-- 以表格格式输出执行计划,默认方式
EXPLAIN sql_stmt

-- 以json格式输出执行计划
EXPLAIN FORMAT=JSON sql_stmt

#2.Desc

mysql> help desc;
Name: 'DESC'
Description:
Syntax:
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
[explain_type]
{explainable_stmt | FOR CONNECTION connection_id}

{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] select_statement

explain_type: {
FORMAT = format_name
}

format_name: {
TRADITIONAL
| JSON
| TREE
}

explainable_stmt: {
SELECT statement
| TABLE statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}

The DESCRIBE and EXPLAIN statements are synonyms. In practice, the
DESCRIBE keyword is more often used to obtain information about table
structure, whereas EXPLAIN is used to obtain a query execution plan
(that is, an explanation of how MySQL would execute a query).

URL: https://dev.mysql.com/doc/refman/8.0/en/explain.html

mysql> desc select user,host,plugin from mysql.user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

复制代码
复制代码

3.执行计划基础信息

复制代码
首先以TRADITIONAL格式查看执行计划:

mysql> explain select user,host,plugin from mysql.user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

 

再以JSON格式查看执行计划:

mysql> explain format=json select user,host,plugin from mysql.user;

 

可以看到以上两种格式输出的信息基本相同,但是也存在不一样的地方,个人觉得最大的区别在于:josn格式的执行计划把cost给展示出来了,MySQL优化器是基于cost选择执行计划的,查看cost对于调优很重要。但是,在实际的使用过程中,我们往往会以表格的形式查看执行计划,因为表格形式的执行计划较为简练,便于我们查看。

复制代码
复制代码

4.Explain 查出执行计划化各字段解释

复制代码

mysql> explain select user,host,plugin from mysql.user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

 id:查询标识符。在所有组中,id值越大,优先级越高,越先执行,id如果相同,可以认为是一组,从上往下顺序执行;
 
 select_type:select_type可选的参数较多,如下:
  • select值                               含义
    ----------------------             -------------------------------------
    SIMPLE                               简单的select查询,未使用UNION和子查询
    PRIMARY                              查询中包含任何复杂的子部分,则被标记为PRIMARY,PRIMARY为最外层查询,最后执行
    UNION                                第2个SELECT在UNION之后,则被标记为UNION
    DEPENDENT UNION                      含有UNION查询的第二个或最后一个表,依赖外部的查询
    UNION RESULT                         UNION结果
    SUBQUERY                             在SELECT或WHERE中包含的子查询
    DEPENDENT SELECT                     子查询中的第一个SELECT,依赖外部的查询
    DERIVED                              衍生表,衍生表是FROM子句中子查询的内部名称 
    MATERIALIZED                         物化子查询
    UNCACHEABLE SUBQUERY                 子查询,其结果无法缓存,必须针对外部查询的每一行进行评估
    UNCACHEABLE UNION                    在UNION里的第二个或最后一个表属于不可缓存的子查询         

  table: 此次查询访问的表

     ---<unionM,N>:该行是id为M和N的行的并集
     ---<derivedN>:该行是id为N的行的派生表
     ---<subqueryN>:该行是物化子查询的结果

  partitions:该参数用于记录使用的分区信息,NULL表示该表不是分区表

  type:连接类型,见后面"执行计划连接类型type". 索引查询的类型(ALL、index、range、ref、eq_ref、const(system)、NULL)

  possible_keys:在该查询中,MySQL可能使用的索引,如果此列是NULL,则没有相关的索引,在这种情况下,需要检查WHERE字句,以确定是否适合创建索引

  key:MySQL实际使用的索引。在大多数情况下,key中的值都在possible_key里面,但也会出现possible_key不存在该值,但key里面存在的情

  key_len:该列指MySQL决定使用的索引长度。该值体现了在使用复合索引的时候,使用了复合索引的前面哪几个列(需要根据字段长度计算),如果key列为NULL,则该列也为NULL。由于key存储的格式原因,可以为NULL的列的key长度比NOT NULL的列长度大1。

     ref:列显示哪些列或者常量与key中的索引进行比较,以从表中选择行

    rows:MySQL查询需要遍历的行数,对于innodb表,可能并不总是准确的。这里需要特别注意,Oracle数据库的执行计划里面也有rows列,不过代表结果的行数,含义不一样

    filtered:被条件过滤的行数百分比。最大值为100,表示没有行过滤,值从100减小表示过滤增加。rows表示检查的行数,rows * filtered/100表示过滤后的行数,也就是与下表进行连接的行

   Extra:执行计划的额外信息,见后面"执行计划额外信息Extra"

复制代码
复制代码

5.执行计划连接类型type类型讲解

复制代码
复制代码
explain的type列表示表的连接类型,从最佳到最差类型如下:比较常见:(ALL、index、range、ref、eq_ref、const(system)、NULL)
System --> const --> eq_ref --> ref --> fulltext --> ref_or_unll --> index_merge --> unique_subquery --> index_subquery --> range --> index --> ALL

  • system:该表只有一行,这是const连接的特殊情况,平时不会出现,可以不用重点注意
  • const:该表最多只有一个匹配行,该行在查询开始时读取。因为只有一行,所以优化器的其余部分可以将这一行中的值做为常量,因为它值读取一次。const在基于主键或者唯一性索引比较时使用。
  • eq_ref:与驱动表的连接查询,后表(被驱动表)仅读取一行数据,当被驱动表存在主键索引或者unique+not null时使用,eq_ref用于使用"="运算的索引列
  • ref:与驱动表的连接查询,后表(被驱动表)读取一行或多行数据。ref用于仅使用key的最左前缀,或者说key不是PAIMARY KAY或UNIQUE索引。换句话说,如果连接无法根据KEY选择单个行,则使用ref,否则使用eq_ref。ref可以用于使用"="或者"<=>"运算符进行的比较
  • fulltext:使用FULLTEXT进行连接
  • ref_or_null:这种方式类似于ref,但是MySQL会额外搜索包含NULL值的行
  • index_merge:索引合并优化,把多个索引合并为一个使用
  • unique_subquery:该类型替换eq_ref形式下的IN子查询,子查询中最多返回一个值,提高查询效率
  • index_subquery:该类型类似于unique_subquery,它用来替代子查询,子查询中返回值的个数不确 
  • range:使用索引去检索一个范围的行数据,key列是使用到的索引,ref列为NULL。range可以在=,<>,>,>=,<,<=,IS NULL,<=>,BETWEEN,LIKE或IN()index:index与ALL相似,只是索引树被扫描,对应2种情况:
  •     a.索引本省存储相关的列数据,如果索引中的数据可满足查询需求,则仅扫描索引树,在这种情况下,Extra列显示为Using index。
  •     b.使用对索引的读取执行全表扫描,以按顺序查找数据行,在这种情况下,Extra没有出现Using index。
  • ALL:全表扫描,如果驱动表不是以const方式获取数据的,则可以会导致非常糟糕的查询性能。通常可以添加索引来避免权标扫描
复制代码
复制代码

 6. analyze 命令讲解

复制代码
复制代码

#1.analyze 这个语句是会真正执行,而不是预估执行。所以在生产繁忙中慎用,会占用资料

mysql> explain analyze select user,host,plugin from mysql.user;
+--------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------+
| -> Table scan on user (cost=0.85 rows=6) (actual time=0.165..0.189 rows=6 loops=1)
|
+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 

mysql> desc format=tree select user,host,plugin from mysql.user;
+--------------------------------------------+
| EXPLAIN |
+--------------------------------------------+
| -> Table scan on user (cost=0.85 rows=6)
|
+--------------------------------------------+
1 row in set (0.00 sec)

以上两种况可以做为生产与测试对比,可以看出语句真正执行与测试执行的结果对比。

复制代码

--------------------------13、日志配置管理

1.错误日志

复制代码
#配置:
默认位置:
log_error=$DATDDIR/hostname.err
看日志: 主要关注 [ERROR],deadlock
# 默认开了,默认位置 datadir=/data/3306/data/HOSTNAME.err
# 控制参数 log_error=/data/3306/err.log, 
# 注意: 日志和数据分开存储 例如: /mysqllog/3306/mysql-err.log
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
server_id=51
port=3306
socket=/tmp/mysql.sock
log_error=/data/3306/log/mysql-err.log
[root@localhost ~]# mkdir -p /data/3306/log/
[root@localhost ~]# touch /data/3306/log/mysql-err.log
[root@localhost ~]# chown -R mysql.mysql /data/
[root@localhost ~]# /etc/init.d/mysqld restart

#错误日志:收集有以下日志
启动故障
主从故障
死锁
数据库hang,堆栈信息


#主要关注: [ERROR]
[ERROR] [MY-000068] [Server] unknown option ---》 配置文件有问题

 

#日志记录级别
mysql> show variables like '%log_error%';
+----------------------------+----------------------------------------+
| Variable_name | Value |
+----------------------------+----------------------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /data/3306/log/mysql-err.log |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 | #可以调整级别,级别越大,日志越详细,对性能也有影响
+----------------------------+----------------------------------------+

mysql> set global log_error_verbosity=3
1.错误信息;
2.错误信息和告警信息;
3.错误信息、告警信息和通知信息。

#如果mysql启动不了,而且日志中没有记录,可以用mysld & 将日志打印在屏幕上
mysqld & #相当于后台启动,同时日志打印在屏幕上

复制代码

2.二进制日志(binlog)

复制代码
#1.作用
 binlog 作用
  a.记录了MySQL 发生过的修改的操作的日志。,除了show select ,修改操作都会记录binlog
  b.数据恢复、主从、SQL问题排查、审计(工具:binlog2sql my2sql)
  
#2.配置方法
 配置binlog
1. 8.0默认开启binlog 
2. 默认在datadir binlog.0000001
3. 建议日志和数据分开存储
4. 参数 server_id=111 log_bin=路径/文件名前缀

#3.显示配置文档
[root@db01 /data/3306/log14:51:10]# cat /etc/my.cnf
[mysql]
socket=/tmp/mysql.sock
[mysqld]
user=mysql
datadir=/data/3306/data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
server_id=51
log_error=/data/3306/log/mysql-err.log
log_bin=/data/3306/log/mysql-bin
[root@localhost ~]# /etc/init.d/mysqld restart
#4.确认配置binlog启效
[root@db01 /data/3306/log14:52:13]# ll
total 12
-rw-r----- 1 mysql mysql  156 Apr 12 14:51 mysql-bin.000001
-rw-r----- 1 mysql mysql   32 Apr 12 14:51 mysql-bin.index
-rw-r--r-- 1 mysql mysql 2798 Apr 12 14:51 mysql-err.log
复制代码

3.慢日志(slow_log)

复制代码
#1.作用
 记录MySQL工作中,运行较慢的语句。用来定位SQL语句性能问题。

#2.配置方法
 开关:
slow_query_log=1                               #默认为0,表示关闭
slow_query_log_file=/data/3306/log/slow.log    #慢日志路径

维度: #在线设置
set global slow_query_log=1
set global long_query_time=0.5
set global log_queries_not_using_indexes=1
set global log_throttle_queries_not_using_indexes=1000

#3.确认配置文档
[root@db01 /etc/init.d15:19:00]# cat /etc/my.cnf
[mysql]
socket=/tmp/mysql.sock
[mysqld]
user=mysql
datadir=/data/3306/data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
server_id=51
log_error=/data/3306/log/mysql-err.log
log_bin=/data/3306/log/mysql-bin
slow_query_log=1 
slow_query_log_file=/data/3306/log/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1
log_throttle_queries_not_using_indexes=1000
[root@localhost ~]# /etc/init.d/mysqld restart
复制代码

4.general_log

复制代码
#1.作用
 普通日志,会记录所有数据库发生的事件及语句。
 文本格式记录MySQL运行期间,所有的所作语句, 可以做问题诊断和调试
 
#2.配置
参数: general_log , general_log_file= 路径 
general_log=1     #默认关闭状态  0
general_log_file=/data/3306/log/genlog

#3.确认配置生效
[root@db01 /data/3306/log15:39:56]# cat /etc/my.cnf
[mysql]
socket=/tmp/mysql.sock
[mysqld]
user=mysql
datadir=/data/3306/data
basedir=/usr/local/mysql
socket=/tmp/mysql.sock
server_id=51
log_error=/data/3306/log/mysql-err.log
log_bin=/data/3306/log/mysql-bin
slow_query_log=1 
slow_query_log_file=/data/3306/log/slow.log
long_query_time=0.5
log_queries_not_using_indexes=1
log_throttle_queries_not_using_indexes=1000
general_log=1
general_log_file=/data/3306/log/genlog

[root@localhost ~]# /etc/init.d/mysqld restart
复制代码

--------------------------14、用户授权管理

1.授权语法

复制代码
 1 GRANT 权限 ON 权限级别 TO 用户                             #其中英文大写为MySQL 语法,汉字为需要填写
 2 
 3                                                              存储文件
 4 *.* ----全库级别      ---> 管理员                               mySQL.user
 5 test.* ---   单库级别  -----> 业务层面                          mySQL.db
 6 test.t1 ------ 单表级别                                        mySQL.table_priv
 7 select(id,name)---columns  ----进行单表列进行授权(用的少)       mySQL.columns_priv
 8 
 9 #权限列表   
10 mySQL> show privileges;
11 | Privilege                  | Context                               | Comment                       

75 #生产库:
76 管理员:
77 ALL 以上权限中不包含 Grant option
复制代码

2.普通权限授权

mySQL> grant all on *.* to test@'10.0.0.%' ;
mySQL> grant select ,update ,delete ,insert on *.* to lss@'10.0.0.%' ;
mySQL> grant select ,update ,delete ,insert on test.* to test@'10.0.0.%';
mySQL> grant select(id) on test.t1 to user1@'10.0.0.%';

3.角色创建及授权

复制代码
#角色,如果要用角色需要启用才可以,默认是关闭状态,修改系统变量activate_all_roles_on_login,默认为OFF,或用set default role 来激活角色。
mySQL> help  SET DEFAULT ROLE
Syntax:
SET DEFAULT ROLE
    {NONE | ALL | role [, role ] ...}
    TO user [, user ] ...
#创建角色 mysql> create role dev@'10.0.0.%'; Query OK, 0 rows affected (0.04 sec)
#给角色授权 角色不可登录,无密码 mysql> grant select on *.* to dev@'10.0.0.%'; Query OK, 0 rows affected (0.01 sec)
#将角色授权给用户 mysql> grant dev@'10.0.0.%' to test@'10.0.0.%'; Query OK, 0 rows affected (0.01 sec) #查询角色 mysql> select * from mysql.role_edges; +-----------+-----------+----------+---------+-------------------+ | FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION | +-----------+-----------+----------+---------+-------------------+ | 10.0.0.% | dev | 10.0.0.% | test | N | +-----------+-----------+----------+---------+-------------------+ 1 row in set (0.00 sec) #通过表来查询相关用户权限 select * from information_schema.user_privileges; #授权管理员用户 #创建远程登录用户 mySQL> create user test@'10.0.0.%' identified with mySQL_native_password by '123'; Query OK, 0 rows affected (0.03 sec) #创建本地登录用户 mySQL> create user test@'localhost' identified with mySQL_native_password by '123'; Query OK, 0 rows affected (0.00 sec) #授权给用户 mySQL> grant all on *.* to test@'10.0.0.%' ; Query OK, 0 rows affected (0.00 sec) mySQL> grant all on *.* to test@'localhost' ; Query OK, 0 rows affected (0.01 sec) #创建开发用户 mySQL> create user dev_user1@'10.0.0.%' identified with mySQL_native_password by '123'; grant create ,alter,create view,show databases ,show views ,update ,delete ,insert on dev_db.* to dev_user1@'10.0.0.%'; #创建复制用户 mySQL> # 创建主从复制相关用户 mySQL> # repl@'10.0.0.%' ,复制用户 mySQL> create user repl@'10.0.0.%' identified with mySQL_native_password by '123'; Query OK, 0 rows affected (0.01 sec) mySQL> grant replication slave,replication client on *.* to repl@'10.0.0.%'; Query OK, 0 rows affected (0.02 sec)
复制代码

4.生产中用户类型规范

管理员 : ALL  (除Grant option   “To give to other users those privileges you possess ”)
开发 : Create ,Create routine,Create temporary tables,Create view,Delete ,Event,Execute,Insert ,References,Select,Show databases ,Show view ,Trigger,Update
监控 : select , replication slave , client supper
备份 : ALL
主从 : replication slave
业务 : insert , update , delete ,select

5.查看权限

复制代码
show grants for test@'10.0.0.%' ;
+-----------------------------------------+
| Grants for test@10.0.0.%                |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `test`@`10.0.0.%` |
+-----------------------------------------+
1 row in set (0.01 sec)

#通过表来查询权限信息
select * from mysql.user where user='test'; #只能查到用户信息,没有权限信息,需要查mysql.db

select * from mysql.db where user='test';
复制代码

6.回收权限

mySQL> revoke delete on *.* from lss@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
mySQL> show grants for lsso@'10.0.0.%';
mySQL> revoke select(id) on test.t1 from user1@'10.0.0.%';

--------------------------15、用户及安全管理

1.用户的组成

复制代码
 1 查看创建用户命令
 2 mysql> help create user;
 3 Name: 'CREATE USER'
 4 Description:
 5 Syntax:
 6 CREATE USER [IF NOT EXISTS]
 7     user [auth_option] [, user [auth_option]] ...
 8     DEFAULT ROLE role [, role ] ...
 9     [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
10     [WITH resource_option [resource_option] ...]
11     [password_option | lock_option] ...
12     [COMMENT 'comment_string' | ATTRIBUTE 'json_object']
13 
14 user:
15     (see )
16 
17 auth_option: {
18     IDENTIFIED BY 'auth_string'
19   | IDENTIFIED BY RANDOM PASSWORD
20   | IDENTIFIED WITH auth_plugin
21   | IDENTIFIED WITH auth_plugin BY 'auth_string'
22   | IDENTIFIED WITH auth_plugin BY RANDOM PASSWORD
23   | IDENTIFIED WITH auth_plugin AS 'auth_string'
24 }
25 
26 tls_option: {
27    SSL
28  | X509
29  | CIPHER 'cipher'
30  | ISSUER 'issuer'
31  | SUBJECT 'subject'
32 }
33 
34 resource_option: {
35     MAX_QUERIES_PER_HOUR count
36   | MAX_UPDATES_PER_HOUR count
37   | MAX_CONNECTIONS_PER_HOUR count
38   | MAX_USER_CONNECTIONS count
39 }
40 
41 password_option: {
42     PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
43   | PASSWORD HISTORY {DEFAULT | N}
44   | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
45   | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
46   | FAILED_LOGIN_ATTEMPTS N
47   | PASSWORD_LOCK_TIME {N | UNBOUNDED}
48 }
49 
50 lock_option: {
51     ACCOUNT LOCK
52   | ACCOUNT UNLOCK
53 }
54 
复制代码

 

2.创建用户

 

复制代码
 1 create user test@'10.0.0.%' identified by '123456abcd';  
 2 Query OK, 0 rows affected (0.01 sec)
 3 
 4 create user oldguo@'10.0.0.%' identified by '123456abcd';
 5 mySQL> select user,host,authentication_string,plugin from mySQL.user;
 6 +------------------+-----------+------------------------------------------------------------------------+-----------------------+
 7 | user             | host      | authentication_string                                                  | plugin                |
 8 +------------------+-----------+------------------------------------------------------------------------+-----------------------+
 9 | mySQL.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password
10 sha2 : 8.0新的特性  默认密码插件
11 native: 兼容老版本  5.6,5.7版本
12 
13 #可以更改密码插件
14 create user test@'10.0.0.%' identified with mySQL_native_password by '123456abcd';
15 mySQL> select user,host,authentication_string,plugin from mySQL.user;
16 +------------------+-----------+------------------------------------------------------------------------+-----------------------+
17 | user             | host      | authentication_string                                                  | plugin                |
18 +------------------+-----------+------------------------------------------------------------------------+-----------------------+
19 | test             | 10.0.0.%  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257                              | mySQL_native_password
复制代码

 

3.创建密码/修改密码

 

复制代码
#1,创建密码
mySQL> create user oldguo@'10.0.0.%';
Query OK, 0 rows affected (0.02 sec)
mySQL> create user test@'10.0.0.%' identified by '123456abcd';


#2.修改密码
mySQL> alter user user1@'10.0.0.%' identified by '123456abcd';  只修改密码

mySQL>  alter user oldguo@'10.0.0.%' identified with mySQL_native_password by '123456abcd';   修改密码及密码插件
复制代码

 

4.删除用户

复制代码
#生产谨慎!!!!!!!!!!!!!
drop user test@'10.0.0.%';
mySQL> select user,host from mySQL.user where (user='' or host='' or authentication_string='') and user!='root';
+-------+-----------+
| user | host |
+-------+-----------+
| test1 | |
| | localhost |
+-------+-----------+
2 rows in set (0.01 sec)
mySQL> drop user test1@'';
Query OK, 0 rows affected (0.00 sec)
mySQL> drop user ''@'localhost';
Query OK, 0 rows affected (0.01 sec)
复制代码

5.修改用户

alter user oldguo@'10.0.0.%' identified with mySQL_native_password by '123456abcd';
如果是caching_sha2_password 密码插件,因为8.0默认密码插件为caching_sha2_password alter user oldguo@'10.0.0.%' identified by '123456abcd';

6.锁用户

复制代码
 1 #一般不会删除用户
 2 可以先将不用的用户锁住,如果还是有人使用,可以快速解锁
 3 ALTER USER 'test'@'10.0.0.%' ACCOUNT LOCK;
 4 
 5 #解锁用户
 6 ALTER USER 'test'@'10.0.0.%' ACCOUNT UNLOCK;
 7 
 8 #查看用户是否上锁(其中N表示未锁,Y表示已锁用户,mysql 有3个默认用户是带锁)
 9 mySQL> select user,host,authentication_string,plugin, account_locked  from mySQL.user;
10 +------------------+-----------+------------------------------------------------------------------------+-----------------------+----------------+
11 | user     | host      | authentication_string     | plugin       | account_locked |
12 | test     | 10.0.0.%  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257| mySQL_native_password | N         
13 | root             | localhost |                                  | caching_sha2_password | N  
复制代码

 

7.忘记root管理原密码处理方式

复制代码
 1 1. 关闭数据库
 2 /etc/init.d/mySQLd stop
 3 
 4 2. 安全模式启动数据库并后台启动
 5 [root@localhost data]# mySQLd_safe --skip-grant-tables --skip-networking &
 6 --skip-grant-tables   #不加载用户认证授权表
 7 --skip-networking     #关闭TCP协议,只能本地连接
 8 
 9 3. 登陆数据库
10 mySQL
11 
12 4. 刷新授权表
13 flush privileges;        #因为跳过用户证授权表了,所以需要人为加载授权表
14 
15 5. 修改密码
16 mySQL> alter user root@'localhost' identified with mySQL_native_password by '123456abcd';
17 
18 6. 重启数据库到正常模式
19 [root@localhost data]# /etc/init.d/mySQLd restart
复制代码

--------------------------16、预装MySQL前硬件烤机压测

1.进行CPU、MEM烤机压

复制代码
 1 #a. 安装
 2 yum install -y epel-release
 3 yum install -y stress
 4 #参数解析:
 5 -c, --cpu N 产生 N 个进程,每个进程都反复不停的计算随机数的平方根
 6 -i, --io N 产生 N 个进程,每个进程反复调用 sync() 将内存上的内容写到硬盘上
 7 -m, --vm N 产生 N 个进程,每个进程不断分配和释放内存
 8   –vm-bytes B 指定分配内存的大小
 9   –vm-stride B 不断的给部分内存赋值,让 COW(Copy On Write)发生
10   –vm-hang N 指示每个消耗内存的进程在分配到内存后转入睡眠状态 N 秒,然后释放内存,一直重复执行这个过程
11   –vm-keep 一直占用内存,区别于不断的释放和重新分配(默认是不断释放并重新分配内存)
12 -d, --hadd N 产生 N 个不断执行 write 和 unlink 函数的进程(创建文件,写入内容,删除文件)
13 –hadd-bytes B 指定文件大小
14 -t, --timeout N 在 N 秒后结束程序
15 –backoff N 等待N微妙后开始运行
16 -q, --quiet 程序在运行的过程中不输出信息
17 -n, --dry-run 输出程序会做什么而并不实际执行相关的操作
18 –version 显示版本号
19 -v, --verbose 显示详细的信息
20 
21 #b. 烤机CPU
22 [root@slave1 ~]# stress -c 4    # C代表核心数量 (Cores),这个根据实际情况来压测,同时利用top查看cpu
23 #c. 烤机 MEM
24 stress -m 3 --vm-bytes 300M  #内存压测不能写实际内存,可能会压奔溃系统,建议留些例实际1G 可以用800M样子来压测
25 #d. 烤机多参数
26 stress -c 4 -m 2 -d 1
复制代码

2.IO压测

1 FIO是测试IOPS的非常好的工具,用来对磁盘进行压力测试和验证。磁盘IO是检查磁盘性能的重要指标,可以按照负载情况分成照顺序读写,随机读写,混合读写两大类。
2 FIO是一个可以产生很多线程或进程并执行用户指定的特定类型I/O操作的工具,FIO的典型用途是编写和模拟的I/O负载匹配的作业文件。
3 FIO是一个多线程io生成工具,可以生成多种IO模式,用来测试磁盘设备的性能(也包含文件系统:如针对网络文件系统 NFS 的IO测试)。
4 FIO压测可以帮助管理员,提前预知磁盘瓶颈,及时作出扩容建议。也可以作为有效烤机

  FIO应用(IO压测工具)

复制代码
 1 a. 环境准备  #如已创建则忽略
 2 mkdir -p /data
 3 mkfs.xfs /dev/sdb
 4 mount /dev/sdb /data
 5 dd if=/dev/zero of=/data/test bs=16k count=512000
 6 
 7 b. 安装
 8 yum install libaio libaio-devel fio
 9 
10 c. 各类压测
11 # 测试随机写:
12 fio --filename=/data/test --iodepth=4 --ioengine=libaio -direct=1 --\
13 rw=randwrite --bs=16k --size=2G --numjobs=64 --runtime=20 --group_reporting --\
14 name=test-rand-write
15 # 测试顺序读取:
16 fio --filename=/data/test -iodepth=64 -ioengine=libaio --direct=1 --rw=read --\
17 bs=1m --size=2g --numjobs=4 --runtime=10 --group_reporting --name=test-read
18 # 测试顺序写性能
19 fio --filename=/data/test.big -iodepth=64 -ioengine=libaio -direct=1 -rw=write\
20 -bs=1m -size=2g -numjobs=4 -runtime=20 --group_reporting -name=test-write
21 测试随机读:
22 fio --filename=/data/test -iodepth=64 -ioengine=libaio -direct=1 -rw=randread -\
23 bs=4k -size=2G -numjobs=64 -runtime=20 --group_reporting -name=test-rand-read
24 # 16k,70%读取,30%写入:
25 随机:
26 fio --filename=/dev/sdb --direct=1 --rw=randrw --refill_buffers --norandommap --\
27 randrepeat=0 --ioengine=libaio --bs=4k --rwmixread=70 --iodepth=16 --numjobs=16 -\
28 -runtime=60 --group_reporting --name=73test
29 有序:
30 fio --filename=/dev/sdb --direct=1 --rw=rw --refill_buffers --norandommap --\
31 randrepeat=0 --ioengine=libaio --bs=4k --rwmixread=70 --iodepth=16 --numjobs=16 -\
32 -runtime=60 --group_reporting --name=73test
33 
34 #参数解读
35 # 重要参数解读:
36 --filename 需要压测的磁盘或者测试文件。
37 --direct=1 是否绕过文件系统缓存
38 -ioengine=libaio 采用异步或者同步IO
39 -iodepth=64 IO队列深度。一次发起多少个IO请求,一般SSD或者flash可以较大。
40 --numjobs=16 测试并发线程数。在RAID10或Raid5可加大参数。
41 --rwmixread=70 混合读写,read的比例。一般读写比例28或者37。
42 --group_reporting 统计汇总结果展示。
43 --name 起个名。
44 --rw=randrw 测试类型.
45 # 结果解读  主要看IOPS 与BW(吞吐量)延迟时间(毫秒)
46 IOPS
47 write: IOPS=5883, BW=91.9MiB/s (96.4MB/s)(1841MiB/20020msec)          *****
48 lat (usec): min=1677, max=78137, avg=43413.06, stdev=7210.57          *****
49 clat percentiles (usec):
50 | 1.00th=[23462], 5.00th=[25297], 10.00th=[26084], 20.00th=[27657],
51 | 30.00th=[28967], 40.00th=[30278], 50.00th=[31851], 60.00th=[33162],
52 | 70.00th=[34866], 80.00th=[36963], 90.00th=[40109], 95.00th=[42730],
53 | 99.00th=[49021], 99.50th=[51119], 99.90th=[59507], 99.95th=[61604],
54 | 99.99th=[64226
55 #IOPS是IO并发数,也就是存储上对于每秒IO的并发数的支持
56 #BW 吞吐量
57 调整参数压测多次取平均值
58 --numjobs=16 #服务器CPU 核心数,先调整为实际数量,再慢慢往下降去压测,去查看这个IOPS 与BW 是会有增长,延迟是否有增加
59 -iodepth=64 
复制代码

--------------------------17、新特性

1. 密码插件,在8.0中为caching_sha2_password,5.7以前版本加密插件为:mysql_native_password

2. 在8.0中不支持grant直接创建用户并授权,必须先建用户后grant授权。

3. 系统表全部换成事务型的innodb表,默认的MySQL实例将不包含任何MyISAM表,除非手动创建MyISAM表

4. 在8.0版本之前,默认字符集为latin1,utf8指向的是utf8mb3,8.0版本默认字符集为utf8mb4,utf8默认指向的也是utf8mb4

5. MySQL 8.0版本支持在线修改全局参数并持久化,通过加上PERSIST关键字,可以将修改的参数持久化到新的配置文件(mysqld-auto.cnf)中,重启MySQL时,可以从该配置文件获取到最新的配置参数

6. 支持事务性DDL,崩溃可以回滚,保证一致。

7. 保留一份数据字典信息,取消frm数据字典。

8. 原子性DDL - 支持原子性DDL

9. Cache && Buffer的变化 - 取消Query Cache

--------------------------17、存储过程

--------------------------17、存储过程

--------------------------17、存储过程

--------------------------17、存储过程

--------------------------17、存储过程

 

 

 

 

posted @   hanease  阅读(15)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· [翻译] 为什么 Tracebit 用 C# 开发
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· 刚刚!百度搜索“换脑”引爆AI圈,正式接入DeepSeek R1满血版
历史上的今天:
2021-12-26 分布式文件系统KFS基础知识介绍
2021-12-26 Storm基本概念
2021-12-26 MVCC基本原理
点击右上角即可分享
微信分享提示