第02篇_Mysql

第01章_MySql概述

第一节 MySql简介

1. 什么是MySql?

MySQL是一个开源的关系型数据库管理系统(RDBMS) ，主要应用在互联网WEB领域。

发展历程简述如下：

• 1995 年，由瑞典 MySQL AB 公司开发(创始人Michael Widenius)。
• 2003 年，MySql发布4.0版本，与InnoDB引擎正式结合。
• 2008 年，被 Sun 公司收购。
• 2009 年，Sun 又被 Oracle 公司收购。
• 2010 年，MySql发布5.5版本，InnoDB引擎成为MySql的默认存储引擎。
• 2016 年9月，MySql发布里程碑版本MySql8.0.0。

扩展：

1. MySQL6.x 版本之后分为 社区版 和 商业版。
2. MySQL 的创造者担心 MySQL 有闭源的风险，因此创建了 MySQL 的分支项目 MariaDB。

第二节 安装部署

1. Windwos版本安装

详见部署文档！

2. Linux版本安装

详见部署文档！

3. 远程访问配置

​x
1
-- 修改root用户权限
2
use mysql;
3
update user set Host='%' where User='root';  -- % 指的是所有地址
4
FLUSH PRIVILEGES; -- 刷新权限
5

6
-- 创建登录用户
7
create user 'hyx'@'%' identified by 'hyx123456';
8

9
-- 给登录用户授权
10
GRANT ALL PRIVILEGES ON [数据库名].[表名] TO '[用户名]'@'[连接地址]' WITH GRANT OPTION; -- 授予连接访问权限
11
GRANT SELECT, UPDATE, DELETE, INSERT ON dxg .* TO 'username'@'%' WITH GRANT OPTION; -- 授予操作增删改
12
FLUSH PRIVILEGES; -- 刷新权限
13

14
-- 本机跳过密码登录（忘记root密码）
15
sudo /usr/sbin/mysqld --skip-grant-tables --user=mysql &
16
mysql -u root

4. 错误日志

错误日志默认存放在 /var/log/mysqld.log，记录了 mysqld 启动和停止，以及服务器在运行过程中产生的错误信息。

xxxxxxxxxx
10
1
mysql> show variables like '%log_error%';
2
+----------------------------+----------------------------------------+
3
| Variable_name              | Value                                  |
4
+----------------------------+----------------------------------------+
5
| binlog_error_action        | ABORT_SERVER                           | 
6
| log_error                  | /var/log/mysqld.log                    |
7
| log_error_services         | log_filter_internal; log_sink_internal |
8
| log_error_suppression_list |                                        |
9
| log_error_verbosity        | 2                                      |
10
+----------------------------+----------------------------------------+

第三节 客户端

1. 命令行客户端

xxxxxxxxxx
13
1
# 登录mysql
2
mysql -h 106.53.120.230 -P 3306 -u root -phyx123456 test01
3

4
# 执行SQL命令(-e)
5
C:\Users\Administrator>mysql -h 106.53.120.230 -P 3306 -u root -pHyx147741 test01 -e "select * from stu"
6
Warning: Using a password on the command line interface can be insecure.
7
+----+-------+-----+
8
| id | name  | age |
9
+----+-------+-----+
10
|  1 | tom   |   1 |
11
+----+-------+-----+
12
C:\Users\Administrator>
13

注意：

1. 将MySql的bin路径(如：D:\MySql\mysql-8.0.22-winx64\bin)配置到PATH变量。

2. 其它客户端

• 官方客户端：MySQL Workbench。
• 通用GUI客户端：SQLyog、DataGrip、Navicat、Dbeaver等。

第四节 体系结构

1. Mysql软件架构

1) 架构分层

• 连接层：提供连接线程，进行身份认证和权限验证等，其中最大连接数可通过%max_connections%变量查看。
• 服务层：解析SQL语句，进行语法和语义分析，生成执行计划，以及执行存储过程等一些跨存储引擎的功能。
• 引擎层：负责数据的存储和提取，以及索引的维护。
• 存储层：将数据(REDOLOG、UNDOLOG、BINLOG、数据块、索引、错误日志、查询日志、慢查询日志等)写入磁盘。

2) SQL执行流程

• 接收SQL语句：MySQL通过3306端口监听并接收客户端的SQL语句。
• 初步权限校验：检查用户是否有权限执行该SQL语句及访问相关对象。
• 查询缓存（MySQL 8.0之前）：查询缓存中是否存在所需数据，若命中则直接返回结果。
• 词法与语法分析：检查SQL语句的语法和语义，生成执行计划。
• 执行计划优化：根据统计信息和用户提示优化执行计划。
• 精细权限校验：对操作对象和数据进行详细权限校验。
• 执行器执行：调用存储引擎执行SQL查询或更新操作。
• 记录日志（InnoDB引擎）：采用两阶段提交，分别记录redo log和binlog日志。
• 返回执行结果：将执行结果返回给客户端。

为什么要采用两阶段提交日志？

• binlog 主要用于崩溃恢复，redo log 主要用于主从复制，如果两者不一致，则会造成主备机数据不一致。
• 日志的两阶段提交指先记录 redo log 为 prepare 状态，再提交 binlog ，最后才提交 redo log 。
• 如果出现崩溃，则检查 binlog 是否完整，完整则提交 redo log ，否则回滚 redo log 。

2. 存储引擎介绍

存储引擎是数据存储和提取的核心，插件式的存储引擎架构，使得MySql可以应用在多种不同场景中，并发挥良好作用。

1) 指定存储引擎

存储引擎是表级别的，也称为表类型，一般在创建表时指定，如未指定则为当前数据库的默认存储引擎。

xxxxxxxxxx
31
1
-- 查询当前数据库支持的存储引擎
2
SHOW ENGINES; 
3
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
4
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
5
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
6
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
7
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
8
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
9
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
10
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
11
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
12
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
13
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
14
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
15
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
16

17
-- 查看默认存储引擎
18
show variables like '%storage_engine%';
19

20
-- 创建表时指定存储引擎
21
CREATE TABLE MY_INNODB(ID INT, NAME VARCHAR(10)) ENGINE = InnoDB;
22
CREATE TABLE MY_MYISAM(ID INT, NAME VARCHAR(10)) ENGINE = MyISAM;
23
CREATE TABLE MY_MEMORY(ID INT, NAME VARCHAR(10)) ENGINE = Memory;
24

25
-- 查看某表的存储引擎
26
SHOW CREATE TABLE MY_MYISAM;
27

28
-- 修改表的存储引擎
29
-- 注意：该操作会逐行复制数据，耗时长，会加读锁，请谨慎执行
30
ALTER TABLE mytable ENGINE = InnoDB;
31

2) 存储引擎特点

• InnoDB：兼顾高可靠性和高性能的通用存储引擎，为 MySQL 5.5 之后的默认存储引擎。
• MyIASM：支持全文索引、压缩、空间函数（GIS）等特性，但不支持事务和行级锁，而且崩溃后无法安全恢复。
• Memory：数据存储在内存中，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

3) 存储引擎选择

在选择存储引擎时，应根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，可以根据实际情况选择多种存储引擎进行组合：

• InnoDB：如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
• MyISAM： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
• MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

3. 二进制日志

二进制日志(BINLOG)记录了所有的 DDL语句和 DML语句，但不包括DQL语句，主要用于主从复制和灾难时的数据恢复。

xxxxxxxxxx
12
1
-- 查询BINLOG配置
2
mysql> show variables like '%log_bin%';
3
+---------------------------------+-----------------------------+
4
| Variable_name                   | Value                       |
5
+---------------------------------+-----------------------------+
6
| log_bin                         | ON                          |  -- 是否打开
7
| log_bin_basename                | /var/lib/mysql/binlog       |  -- 存放目录，以000001等序号拆分
8
| log_bin_index                   | /var/lib/mysql/binlog.index |  -- 记录了当前服务器关联的binlog文件有哪些
9
| log_bin_trust_function_creators | OFF                         |
10
| log_bin_use_v1_row_events       | OFF                         |
11
| sql_log_bin                     | ON                          |
12
+---------------------------------+-----------------------------+

1) BINLOG格式

MySQL服务器中提供了多种格式来记录二进制日志，具体格式及特点如下：

xxxxxxxxxx
7
1
-- 查询BINLOG格式
2
mysql> show variables like '%binlog_format%';
3
+---------------+-------+
4
| Variable_name | Value |
5
+---------------+-------+
6
| binlog_format | ROW   |
7
+---------------+-------+

注意：

1. 如果要修改BINLOG的日志格式，需要在 /etc/my.cnf 中配置 binlog_format 参数并重启服务器。

2) 查看BINLOG

BINLOG是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具 mysqlbinlog 来查看。

xxxxxxxxxx
7
1
mysqlbinlog [ 参数选项 ] logfilename
2

3
参数选项：
4
    -d 指定数据库名称，只列出指定的数据库相关操作
5
    -o 忽略掉日志中的前n行命令
6
    -v 将行事件(数据变更)重构为SQL语句
7
    -vv 将行事件(数据变更)重构为SQL语句，并输出注释信息

3) 删除BINLOG

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：

也可以在mysql的配置文件中配置二进制日志的过期时间，设置了之后，二进制日志过期会自动删除。

xxxxxxxxxx
6
1
mysql> show variables like '%binlog_expire_logs_seconds%';
2
+----------------------------+---------+
3
| Variable_name              | Value   |
4
+----------------------------+---------+
5
| binlog_expire_logs_seconds | 2592000 |
6
+----------------------------+---------+

第02章_实例管理

第一节 数据库

1. 查询和选中数据库

xxxxxxxxxx
9
1
-- 查询所有数据库
2
show databases; 
3

4
-- 选中数据库
5
use test01;
6

7
-- 查询当前选中数据库
8
select database();
9

2. 创建和删除数据库

xxxxxxxxxx
6
1
-- 创建数据库并指定字符集和排序规则(utf8mb4_zh_0900_as_cs表示区分重音和大小写)
2
create database if not exists test01 default charset utf8mb4 default collate utf8mb4_zh_0900_as_cs;
3

4
-- 删除数据库
5
drop database if exists test01;
6

注意：

1. 数据文件存储目录可通过show variables like 'datadir参数查看。

3. 设置数据库参数

xxxxxxxxxx
22
1
-- 查看所有参数
2
SHOW VARIABLES;
3

4
-- 查看单个参数
5
SHOW VARIABLES LIKE '%max_connections%';  -- 查看最大连接数
6

7
-- 查看生效的全局参数（部分参数区分会话/全局）
8
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
9

10
-- 设置会话级别参数（仅当前连接生效）
11
SET SESSION transaction_isolation = 'READ-COMMITTED';
12
-- 简化写法（省略 SESSION，默认会话级别）
13
SET transaction_isolation = 'READ-COMMITTED';
14

15
-- 设置全局级别参数（所有新连接生效，已有连接不生效）
16
SET GLOBAL max_connections = 1000;
17

18
-- 修改配置文件（需重启，依次加载，后加载会覆盖先加载）
19
[root@vm-20-5-centos ~]# mysql --help | grep -A 1 "Default options"
20
Default options are read from the following files in the given order:
21
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
22

注意：

1. 参数列表：https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html。
2. 动态参数（无需重启）：https://dev.mysql.com/doc/refman/8.0/en/dynamic-system-variables.html。

4. 系统数据库

1) 系统库简介

Mysql数据库安装完成后，默认会创建下面四个系统数据库：

2) mysql（用户与权限管理）

mysql 库是权限管理的核心，最常用的操作是查询和管理用户和权限：

xxxxxxxxxx
14
1
-- 1. 查询所有用户（核心表：user）
2
SELECT user, host, authentication_string FROM mysql.user;
3
-- 说明：user=用户名，host=允许访问的主机（%=任意主机），authentication_string=加密后的密码
4

5
-- 2. 查询用户的权限（核心表：db）
6
SELECT user, host, db, select_priv, insert_priv FROM mysql.db;
7
-- 说明：select_priv/insert_priv 表示该用户对对应数据库的查询/插入权限（Y=有，N=无）
8

9
-- 3. 刷新权限（修改权限后必须执行）
10
FLUSH PRIVILEGES;
11

12
-- 4. 重置 root 密码（应急场景，需先停止 MySQL 并跳过权限启动）
13
UPDATE mysql.user SET authentication_string = PASSWORD('新密码') WHERE user = 'root' AND host = 'localhost';
14
FLUSH PRIVILEGES;

3) information_schema（元数据查询）

information_schema 是日常开发中使用频率最高的系统库，通过它可以查询数据库、表、列的结构信息：

xxxxxxxxxx
24
1
-- 1. 查询当前实例下所有数据库
2
SELECT schema_name FROM information_schema.schemata;
3

4
-- 2. 查询指定数据库（如 test_db）下所有表
5
SELECT table_name FROM information_schema.tables WHERE table_schema = 'test_db';
6

7
-- 3. 查询指定表（如 test_db.t_user）的所有列信息（字段名、类型、是否非空、主键等）
8
SELECT 
9
  column_name, data_type, is_nullable, column_key, column_comment 
10
FROM information_schema.columns 
11
WHERE table_schema = 'test_db' AND table_name = 't_user';
12

13
-- 4. 查询表的索引信息
14
SELECT 
15
  index_name, column_name, non_unique 
16
FROM information_schema.statistics 
17
WHERE table_schema = 'test_db' AND table_name = 't_user';
18

19
-- 5. 查询数据库占用磁盘空间（单位：MB）
20
SELECT 
21
  table_schema AS '数据库名',
22
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS '占用空间(MB)'
23
FROM information_schema.tables 
24
GROUP BY table_schema;

4) performance_schema（性能监控）

performance_schema主要用于排查性能问题，如慢查询、锁等待、线程阻塞等，默认部分功能关闭，生产环境建议开启。

xxxxxxxxxx
15
1
-- 1. 开启性能监控（临时生效，重启后失效）
2
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';
3
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
4

5
-- 2. 查询当前活跃的线程及执行的SQL（排查慢查询/阻塞）
6
SELECT 
7
  thread_id, processlist_id, processlist_user, processlist_host, processlist_info 
8
FROM performance_schema.threads 
9
WHERE processlist_state IS NOT NULL;
10

11
-- 3. 查询锁等待信息（排查死锁/阻塞）
12
SELECT 
13
  object_schema, object_name, waiting_thread_id, waiting_event 
14
FROM performance_schema.events_waits_current 
15
WHERE waiting_event IS NOT NULL;

注意：

1. 频繁查询 performance_schema 会有轻微性能开销，生产环境建议按需查询，而非持续轮询。

5) sys（简化性能分析）

sys 库将 performance_schema 的复杂表封装成易用的视图，适合运维和开发人员快速分析问题。

xxxxxxxxxx
11
1
-- 1. 查看慢查询SQL（按执行时间排序）
2
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;
3

4
-- 2. 查看表的访问频率（排查热点表）
5
SELECT * FROM sys.schema_table_statistics ORDER BY rows_read DESC LIMIT 10;
6

7
-- 3. 查看死锁记录
8
SELECT * FROM sys.innodb_lock_waits;
9

10
-- 4. 查看数据库连接数（按用户/主机分组）
11
SELECT * FROM sys.host_summary_by_connection_type;

第二节 用户

1. 查询用户信息

xxxxxxxxxx
13
1
-- 查询所有用户
2
select * from mysql.user; 
3
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
4
| Host      | User             | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                                                  | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
5
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
6
| %         | huangyuanxin     | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$h,%'~n•••DU•4hh+M)A#KBoQY1nl33KDQfKgZX2h67je9w9SoodtkNR82sYTVVB | N                | 2022-04-09 18:08:53   |              NULL | N              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
7
| %         | root             | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *39F2DB45932B8608195CB4C4CE5B1C0FFC443199                              | N                | 2021-10-22 11:38:46   |                 0 | N              | Y                | Y              |                   NULL |                NULL | NULL                     | NULL            |
8
| localhost | huangyuanxin     | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |            •3=•#B#t%gjWkLAymjthb7ZnVGsrX8Z1wBvPkJycMZjBXR4uKDu4 | N                | 2022-04-09 18:05:22   |              NULL | N              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
9
| localhost | mysql.infoschema | Y           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N                | 2021-09-01 12:20:14   |              NULL | Y              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
10
| localhost | mysql.session    | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | Y          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N                | 2021-09-01 12:20:14   |              NULL | Y              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
11
| localhost | mysql.sys        | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N                | 2021-09-01 12:20:14   |              NULL | Y              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
12
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
13

注意：

1. Mysql中使用Host和User来标识一个用户，其中Host表示允许登录的机器(localhost只能本地访问,%则无限制)，User表示登录名。

2. 修改用户信息

xxxxxxxxxx
11
1
-- 创建新用户
2
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; 
3

4
-- 修改用户密码(并指定加密方式)
5
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ; 
6

7
-- 删除用户
8
DROP USER '用户名'@'主机名' ;  
9

10
-- 查询默认加密方式
11
show variables like 'default_authentication%';

注意：

1. MySQL 8.0中默认的身份认证插件是caching_sha2_password，替代了之前的mysql_native_password。

第三节 权限

1. 查询权限信息

Mysql 中常用的权限有 ALL/ALL PRIVILEGES(所有权限)、CREATE(创建库/表)、DROP(删除库/表/视图)、ALTER(修改表)、INSERT(插入)、DELETE(删除)、UPDATE(修改)、SELECT(查询)等。

xxxxxxxxxx
9
1
-- 查询用户权限
2
mysql> SHOW GRANTS FOR 'root'@'%';
3
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4
| Grants for root@%                                                                                                                                                                                                                                                                                                                                                                                                                      |
5
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION                                       |
7
| GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`%` WITH GRANT OPTION |
8
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9

2. 授权与回收

xxxxxxxxxx
6
1
-- 授予权限 
2
grant all on db01.* to 'huangyuanxin'@'%';  -- GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'
3

4
-- 回收权限
5
revoke all on db01.* from 'huangyuanxin'@'%';  -- REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'
6

第四节 事务

1. 事务基础概念

1) 什么是事务？

事务是一组独立不可分割的工作单元，事务中的操作要么全部执行，要么都不执行。

2) 事务的四大特性

• 原子性(Automatic)：事务中的所有的操作要么是全部成功要么是全部失败，不会存在事务中操作部分失败，部分成功的情况。
• 一致性(Consistent)：执行事务前操作的数据的状态和执行事务后所操作数据的状态是一致的。 例如：我们操作数据库以一个事务操作从A用户的账户中转账200元到B用户的账户中，操作前A账户有1000元，B账户有200元，其中涉及到两个操作从A账户扣减200元，往B账户增加200元，在事务的操作成功提交后，A账户有800元，B账户有400元。事务操作前A、B账户的总额为1200元，事务操作后A、B账户的总额仍是1200元。A、B的总额在事务的操作前后都是一致的。如果在执行这个事务的操作的过程中出现错误，则事务不成功，整个事务操作都是失败的，不存在部分成功部分失败的情况，则在事务操作后的A、B账户的总额仍是1200元。
• 隔离性(Isolation)：通常来说事务的操作在最终提交前，对其他事务的来说都是不可见的。但是隔离性在不同的隔离级别下却不一定总是满足以上的描述。
• 持久性(Duration)：一个事务的操作在提交成功操作后，数据库中所做的修改就是永久性的，即使是数据库系统故障崩溃，事务的修改都不会丢失。

3) 事务的隔离级别

• 读未提交(Read uncommitted)：在一个事务操作的过程中对于一个数据做了修改还未提交的时候，其他的事务可以立即读取到这个事务对于该数据的修改。在此种情况下会出现脏读的现象，即因为在事务尚未提交的时候其他事务读取到了该事务修改的数据，若该事务提交失败出现回滚，则该数据修改并没有生效，其他事务读取到的是无效值。

  

• 读已提交(Read committed)：一个事务操作过程中的修改必须等到事务的修改提交成功后，才可以被其他事务读取到。在此种情况下能解决脏读现象问题，因为事务在读取到的数据都是其他事务已经操作提交成功的，不会出现读取到提交失败的脏数据。但是会出现不可重复读的问题，因为事务在操作的过程中，如果两次读取的另一个事务操作修改的数据，一次读取是在事务提交前，另一次读取是在事务提交后，就会出现在同一个事务中多次读取到的数据的值是不一致的。

  

• 可重复读(Repeatable read)：一个事务在操作的过程中，读取到的数据在整个事务的操作过程中是不会变的，即使是在这个事务的过程中，其他的事务对这个事务读取的数据做了修改。这个隔离级别可以解决不可重读的问题，但在这个事务隔离级别中会出现幻读的现象，即在事务读取某个范围的数据记录时，另一个事务在这个范围内数据插入了一行新数据并提交成功，导致在这个插入操作前后，当前事务两次查询出来的记录数是不一致的。

  

• 串行化(Serializable)：数据库中对于同一条数据的事务操作都是串行进行的，对于同一条数据的操作不存在并行执行的情况，在这个隔离级别下，便不存在上述提到的所有问题了。

注意：

1. Oracle仅支持 读已提交(默认) 和 串行化 这两个事务隔离级别，而Mysql的InnoDB引擎都支持，默认为Repeatable Read。
2. 不可重复读一般因并发事务的UPDATE产生，幻读是不可重复读的一种特例，一般因并发事务的DELETE或INSERT产生。
3. 在 InnoDb 引擎中，RR + MVCC + ReadView可以防止部分幻读(当前读时会出现幻读)，再临键锁可以完全防止幻读。

2. 事务控制命令

1) 事务的开始和结束

在 InnoDB 引擎中，默认是自动开启事务的，事务一般开始于session中的第一条DML语句，直到出现下列情形之一则提交或回滚事务：

• 手动commit或rollback。
• 执行DDL语句或DCL语句，会提交之前的事务，如 create、alter、set autocommit = 1、begin等。
• 关于MYSQL复制的语句，也会提交之前的事务，如：START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等。
• 一些特殊语句，也会提交之前的事务，如：LOAD DATA、LOCK TABLES、UNLOCK TABLES、ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、 LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等。
• 用户正常退出会话则提交(exit)，异常退出会话则回滚(如直接关闭mysql窗口)。
• 机器故障或执行shutdown immediate命令则回滚。

注意：

1. 如果前一个事务未提交，则再次begin，默认会提交之前的事务，再开启一个新事物。

2) 事务控制命令

xxxxxxxxxx
29
1
-- 查看事务隔离级别
2
SELECT @@TRANSACTION_ISOLATION; -- 5.7.20之前用 @@tx_isolation
3
+-------------------------+
4
| @@TRANSACTION_ISOLATION |
5
+-------------------------+
6
| REPEATABLE-READ         |
7
+-------------------------+
8

9
-- 设置事务隔离级别
10
-- SESSION：只对当前会话的后续事务有效  GLOBAL：只对后续会话的事务有效，对当前会话无效  无：只对当前会话的下一事务有效
11
SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |  READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
12

13
-- 开启事务
14
START TRANSACTION(或BEGIN)
15

16
-- 提交事务
17
COMMIT;
18

19
-- 回滚事务
20
ROLLBACK; 
21

22
-- 保存点
23
set autocommit = 0;       -- 关闭自动提交
24
savapoint s1;             -- 设置保存点 s1
25
savapoint s2;             -- 设置保存点 s2
26
rollback  to savapoint s2; -- 回滚到指定保存点
27
release savapoint s1;      -- 释放保存点
28
commit;                    -- 提交全部事务
29

第五节 锁

1. 全局锁

1) 全局锁简介

全局锁就是对整个数据库加锁，加锁后数据库处于只读状态，后续的DML语句和DDL语句，以及尚未提交的事务都将会被阻塞。

其典型的应用场景就是做全库的逻辑备份，先对所有的表进行锁定，然后获取全局事务快照，从而保证数据的业务准确性。

扩展：为什么全库逻辑备份，需要加全局锁呢？

1. 例如我们有库存表和订单表，在备份库存表后，将要备份订单表时，客户下单，在同一个事务扣减库存，并新增订单。
2. 当未加全局锁时，库存表备份的是该事务之前的数据，但订单表备份是该事务提交之后的数据，库存和订单就对不上了。
3. 但如果加了全局锁，在备份库存表前，已经获取到了全局事务快照，即使有新订单，也不会进行备份，保证数据一致性。

2) 全局加锁和解锁

xxxxxxxxxx
9
1
-- 加全局锁
2
flush tables with read lock;
3

4
-- 备份操作
5
mysqldump --single-transaction -h106.53.120.230 -P3306 -uroot -pHyx147741 test01 > D:\test01.sql
6

7
-- 释放锁
8
unlock tables; 
9

注意：

1. 上述操作流程仅作参考，实际上，mysqldump备份时会自动加全局锁，无需手动添加。
2. 默认情况下，mysqldump在整个备份过程中，都将无法执行写操作，造成业务停摆。
3. 可以通过添加--single-transaction参数，让它仅在生成全局事务快照时加锁，并且也能保证数据一致性。

2. 表级锁

表级锁即对整张表加锁，容易发生锁冲突，并发性能较低。表级锁可分为表锁、元数据锁(DML)、意向锁。

1) 表锁

xxxxxxxxxx
7
1
-- 加表锁
2
LOCK TABLES TB_USER READ;    -- 读模式：当前事务加读锁后，其它事务可以读，但不能写
3
LOCK TABLES TB_USER WRITE;   -- 写模式：当前事务加写锁后，其它事务既不可以读，也不可以写
4

5
-- 释放锁
6
UNLOCK TABLES;
7

2) 元数据锁

元数据锁(DML)即对表结构加锁，当执行DQL或DML操作时，将会加读锁，当执行DDL语句时，将会加写锁，可通过如下SQL语句查看：

xxxxxxxxxx
14
1
-- 查询元数据锁
2
mysql> SELECT OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_DURATION 
3
         FROM PERFORMANCE_SCHEMA.METADATA_LOCKS;
4
+-------------------+--------------------+----------------+---------------------+---------------+
5
| object_type       | object_schema      | object_name    | lock_type           | lock_duration |
6
+-------------------+--------------------+----------------+---------------------+---------------+
7
| TABLE             | test01             | TB_USER        | SHARED_READ_ONLY    | TRANSACTION   |
8
| TABLE             | performance_schema | metadata_locks | SHARED_READ         | TRANSACTION   |
9
| SCHEMA            | performance_schema | NULL           | INTENTION_EXCLUSIVE | TRANSACTION   |
10
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ         | STATEMENT     |
11
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ         | STATEMENT     |
12
| COLUMN STATISTICS | performance_schema | metadata_locks | SHARED_READ         | STATEMENT     |
13
+-------------------+--------------------+----------------+---------------------+---------------+
14

常见SQL操作加的元数据锁如下：

注意：

1. 如果插入语句在生成自增值时，能够确定插入的行数，则会使用轻量级锁，直接自增N，无需AUTO_INC锁，可提高并发效率。

3) 意向锁

意向锁用于优化表锁的加锁操作，当未引入意向锁时，如果要加表锁，则必须逐行检查每一行数据，防止与行锁冲突。

当引入意向锁后，SQL操作在加行锁的同时，也会在表级加意向锁，后续若要添加表锁，只需与表级的意向锁判断兼容性即可。

查询意向锁的SQL语句如下：

xxxxxxxxxx
4
1
-- 查询意向锁
2
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data 
3
  from performance_schema.data_locks;
4

注意：

1. 普通select语句并不会添加意向锁，只有特殊的select语句才会加 IS 锁或 IX 锁。
2. 意向锁之间，即使是 IX 锁和 IX 锁，也不会相互排斥。

3. 行级锁

1) 行级锁简介

在InnoDB引擎中，通过对索引加锁，实现了行级锁效果，常见SQL语句所加的行级锁如下：

注意：

1. 在 MySql 中，只有InnoDB引擎存在行锁，其它引擎都只有表锁，每次DML操作都会锁表。
2. InnoDB引擎是对索引加锁，而非对记录加锁，如果SQL未走索引，则会对整个表加表锁。
3. 在Oracle中，行级锁仅支持写模式加锁，不支持读模式加锁，而InnoDB引擎都支持。
4. 对于 NOWAIT，如果请求的行被其他事务锁定时，语句立即返回。对于 SKIP LOCKED，从返回的结果集中移除被锁定的行。

查询行级锁及相关的意向锁SQL如下：

xxxxxxxxxx
3
1
-- 查询行级锁和意向锁
2
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data
3
  from performance_schema.data_locks;

根据不同的事务隔离级别和SQL索引情况，行级锁又分如下：

• 1) 记录锁(RecordLock)：对单行索引加锁，可防止其它事务修改该行数据。
• 2) 间隙锁(GapLock)：对索引行之间的间隙加锁，可防止其它事务在该间隙中插入数据。
• 3) 临键锁(NextKeyLock)：行锁和间隙锁的组合，不仅锁住该行索引，也锁住该行之前的间隙，用于解决当前读情形的幻读问题。

注意：

1. 间隙锁只会在RR级别的当前读才会添加，且必须为范围查询/更新或等值查询但未命中记录或非唯一索引的等值查询等。
2. 间隙锁的唯一目的是防止其他事务在间隙插入新记录，它不阻塞其它事务对间隙的更新操作，即使这并没有业务意义。
3. 临键锁是左开右闭区间，在使用 < 或<=进行范围查询时，可能会额外锁定右边界记录。

2) 加锁流程

在RR隔离级别下，默认使用临键锁(NextKeyLock)扫描，以防止幻读。

• 当扫描的索引是唯一索引，并进行等值匹配，且数据恰好存在时，将优化为行锁，仅对该行索引加锁。
• 当扫描的索引是唯一索引，并进行等值匹配，但数据不存在时，将优化为间隙锁，对前后两行索引之间的间隙加锁。
• 当扫描的索引是唯一索引，但进行的是范围匹配时，将会一路加临键锁，直到不满足条件的第一个值为止。
• 当扫描的索引是非唯一索引，并进行等值匹配时，将会一路加临键锁，直到最后一个值不满足查询需求，在此之间加间隙锁。
• 当不走索引时，将升级为表锁，对所有记录加锁。

4. 示例说明

xxxxxxxxxx
305
1
-- ------------------------ 0. 表结构和数据 ---------------------------- --
2
-- 表结构
3
CREATE TABLE `stu` (
4
`id` int NOT NULL PRIMARY KEY AUTO_INCREMENT, -- 主键，唯一索引
5
`name` varchar(255) DEFAULT NULL,
6
`age` int NOT NULL
7
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
8

9
-- 数据
10
INSERT INTO `stu` VALUES (1, 'tom', 1);
11
INSERT INTO `stu` VALUES (3, 'cat', 3);
12
INSERT INTO `stu` VALUES (8, 'rose', 8);
13
INSERT INTO `stu` VALUES (11, 'jetty', 11);
14
INSERT INTO `stu` VALUES (19, 'lily', 19);
15
INSERT INTO `stu` VALUES (25, 'luci', 25);
16

17
-- ------------------------ 1. 普通SELECT语句不加锁 ---------------------------- --
18
-- 开启事务，执行SELECT语句
19
mysql> begin;
20
mysql> select * from stu where id = 1;
21

22
-- 查询data_locks未发现加锁
23
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data 
24
         from performance_schema.data_locks;
25
    
26

27
-- --------------------- 2. 共享锁之间可兼容，共享锁和排他锁互斥 ------------------------- --
28
-- A客户端开启事务，执行SELECT...lock in share mode语句
29
mysql-A> begin;
30
mysql-A> select * from stu where id = 1 lock in share mode;
31
+---------------+-------------+------------+-----------+---------------+-----------+
32
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
33
+---------------+-------------+------------+-----------+---------------+-----------+
34
| test01        | stu         | NULL       | TABLE     | IS            | NULL      |
35
| test01        | stu         | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         | -- 共享模式行锁
36
+---------------+-------------+------------+-----------+---------------+-----------+
37

38
-- B客户端还能继续添加S锁
39
mysql-B> select * from stu where id = 1 lock in share mode;
40
+---------------+-------------+------------+-----------+---------------+-----------+
41
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
42
+---------------+-------------+------------+-----------+---------------+-----------+
43
| test01        | stu         | NULL       | TABLE     | IS            | NULL      |
44
| test01        | stu         | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |  -- B客户端的锁
45
| test01        | stu         | NULL       | TABLE     | IS            | NULL      |
46
| test01        | stu         | PRIMARY    | RECORD    | S,REC_NOT_GAP | 1         |  -- A客户端的锁
47
+---------------+-------------+------------+-----------+---------------+-----------+
48

49
-- 但B客户端不能继续添加X锁
50
mysqlB> update stu set name='hyx' where id = 1;  -- 卡住
51

52

53
-- --------------------- 3. 排他锁和排他锁互斥 ------------------------- --
54
-- A客户端添加排他锁
55
mysql-A> update stu set name='hyx' where id = 1; 
56
+---------------+-------------+------------+-----------+---------------+-----------+
57
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
58
+---------------+-------------+------------+-----------+---------------+-----------+
59
| test01        | stu         | NULL       | TABLE     | IX            | NULL      |
60
| test01        | stu         | PRIMARY    | RECORD    | X,REC_NOT_GAP | 3         |
61
+---------------+-------------+------------+-----------+---------------+-----------+
62

63
-- B客户端卡住，等待A客户端提交或回滚事务
64
mysqlB> update stu set name='hyx' where id = 1; 
65

66

67

68
-- --------------------- 4. 不走索引则锁住全部记录 ------------------------- --
69
mysql> update stu set name='hyx01' where name = 'hyx';   -- name字段无索引
70
+---------------+-------------+-----------------+-----------+---------------+------------------------+
71
| object_schema | object_name | index_name      | lock_type | lock_mode     | lock_data              |
72
+---------------+-------------+-----------------+-----------+---------------+------------------------+
73
| test01        | EMP         | NULL            | TABLE     | IX            | NULL                   |
74
| test01        | EMP         | GEN_CLUST_INDEX | RECORD    | X             | supremum pseudo-record | -- 无穷大也会被加锁
75
| test01        | EMP         | GEN_CLUST_INDEX | RECORD    | X             | 0x0000012AC508         |
76
| test01        | EMP         | GEN_CLUST_INDEX | RECORD    | X             | 0x0000012AC507         | -- 每行记录都会加锁(注：该表删掉了部分)
77
+---------------+-------------+-----------------+-----------+---------------+------------------------+
78

79

80
-- --------------------- 5. 唯一索引上的等值查询(记录存在时) ------------------------- --
81
mysql> select * from stu;
82
+----+-------+-----+
83
| id | name  | age |
84
+----+-------+-----+
85
| 19 | lily  |  19 |
86
+----+-------+-----+
87

88
mysql> update stu set name = 'hyx' where id = 19;
89
+---------------+-------------+------------+-----------+---------------+-----------+
90
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
91
+---------------+-------------+------------+-----------+---------------+-----------+
92
| test01        | stu         | NULL       | TABLE     | IX            | NULL      |  -- 表级意向排他锁
93
| test01        | stu         | PRIMARY    | RECORD    | X,REC_NOT_GAP | 19        |  -- 对应记录加行锁(REC_NOT_GAP)
94
+---------------+-------------+------------+-----------+---------------+-----------+
95

96

97
-- --------------------- 6. 唯一索引上的等值查询(记录不存在时) ------------------------- --
98
mysql-A> select * from stu;
99
+----+-------+-----+
100
| id | name  | age |
101
+----+-------+-----+
102
| 19 | lily  |  19 |
103
| 25 | luci  |  25 |
104
+----+-------+-----+
105

106
mysql-A> update stu set name = 'hyx' where id = 20;
107
+---------------+-------------+------------+-----------+---------------+-----------+
108
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
109
+---------------+-------------+------------+-----------+---------------+-----------+
110
| test01        | stu         | NULL       | TABLE     | IX            | NULL      |  
111
| test01        | stu         | PRIMARY    | RECORD    | X,GAP         | 25        |  -- 由于记录不存在，在该间隙添加间隙锁
112
+---------------+-------------+------------+-----------+---------------+-----------+
113

114
-- 间隙两端的记录可以更新
115
mysql-B> update stu set name = 'hyx' where id = 19;  -- OK
116
mysql-B> update stu set name = 'hyx' where id = 25;  -- OK
117

118
-- 【注意】并且被加锁的间隙也可以更新
119
mysql-B> update stu set name = 'hyx' where id = 20;  -- OK 实际上该更新操作不会修改任何数据，不会造成幻读
120

121
-- 【注意】但是被加锁的间隙不能插入数据
122
mysql-B> insert into stu(id,name,age) values(20,'hyx',18);  -- 卡死，但会添加插入意向锁(INSERT_INTENTION)
123
+---------------+-------------+------------+-----------+------------------------+-----------+
124
| object_schema | object_name | index_name | lock_type | lock_mode              | lock_data |
125
+---------------+-------------+------------+-----------+------------------------+-----------+
126
| test01        | stu         | NULL       | TABLE     | IX                     | NULL      |
127
| test01        | stu         | PRIMARY    | RECORD    | X,GAP,INSERT_INTENTION | 25        |  -- B客户端的间隙锁和插入意向锁
128
| test01        | stu         | NULL       | TABLE     | IX                     | NULL      |
129
| test01        | stu         | PRIMARY    | RECORD    | X,GAP                  | 25        |  -- A客户端的间隙锁
130
+---------------+-------------+------------+-----------+------------------------+-----------+
131

132
-- 当A客户端释放锁后，B客户端即可插入成功
133
mysql-A> rollback;
134
mysql-B> insert into stu(id,name,age) values(20,'hyx',18);  -- 插入成功，并移除INSERT_INTENTION
135

136

137

138
-- --------------------- 7. 唯一索引上的范围查询 ------------------------- --
139
mysql> select * from stu;
140
+----+-------+-----+
141
| id | name  | age |
142
+----+-------+-----+
143
| 11 | jetty |  11 |
144
| 19 | hyx   |  19 |
145
| 20 | hyx   |  18 |
146
| 25 | hyx   |  25 |
147
+----+-------+-----+
148

149
-- 大于
150
mysql> update stu set name = 'hyx' where id > 19; 
151
+---------------+-------------+------------+-----------+---------------+------------------------+
152
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data              |
153
+---------------+-------------+------------+-----------+---------------+------------------------+
154
| test01        | stu         | NULL       | TABLE     | IX            | NULL                   |  
155
| test01        | stu         | PRIMARY    | RECORD    | X             | supremum pseudo-record |  -- 临键锁(25,+∞]
156
| test01        | stu         | PRIMARY    | RECORD    | X             | 25                     |  -- 临键锁(20,25]
157
| test01        | stu         | PRIMARY    | RECORD    | X             | 20                     |  -- 临键锁(19,20]
158
+---------------+-------------+------------+-----------+---------------+------------------------+
159

160
-- 大于等于
161
mysql> update stu set name = 'hyx' where id >= 19; 
162
+---------------+-------------+------------+-----------+---------------+------------------------+
163
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data              |
164
+---------------+-------------+------------+-----------+---------------+------------------------+
165
| test01        | stu         | NULL       | TABLE     | IX            | NULL                   |  
166
| test01        | stu         | PRIMARY    | RECORD    | X,REC_NOT_GAP | 19                     |  -- 【注意】行锁id=19
167
| test01        | stu         | PRIMARY    | RECORD    | X             | supremum pseudo-record |  -- 临键锁(25,+∞]
168
| test01        | stu         | PRIMARY    | RECORD    | X             | 25                     |  -- 临键锁(20,25]
169
| test01        | stu         | PRIMARY    | RECORD    | X             | 20                     |  -- 临键锁(19,20]
170
+---------------+-------------+------------+-----------+---------------+------------------------+
171

172

173
mysql> select * from stu3;
174
+----+-------+-----+
175
| id | name  | age |
176
+----+-------+-----+
177
|  1 | tom   |  18 |
178
|  2 | cat   |  19 |
179
|  3 | rose  |  19 |
180
|  4 | jetty |  19 |
181
|  5 | lily  |  20 |
182
+----+-------+-----+
183

184
-- 小于
185
mysql> update stu3 set name = 'hyx' where id < 3;  
186
+---------------+-------------+------------+-----------+---------------+-----------+
187
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
188
+---------------+-------------+------------+-----------+---------------+-----------+
189
| test01        | stu3        | NULL       | TABLE     | IX            | NULL      |  
190
| test01        | stu3        | PRIMARY    | RECORD    | X             | 1         |  -- 临键锁(-∞,1]  
191
| test01        | stu3        | PRIMARY    | RECORD    | X             | 2         |  -- 临键锁(1,2]
192
| test01        | stu3        | PRIMARY    | RECORD    | X             | 3         |  -- 临键锁(2,3]  (注：额外对id=3加行锁，可能影响业务)
193
+---------------+-------------+------------+-----------+---------------+-----------+
194

195
-- 小于等于
196
mysql> update stu3 set name = 'hyx' where id <=3;
197
+---------------+-------------+------------+-----------+---------------+-----------+
198
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
199
+---------------+-------------+------------+-----------+---------------+-----------+
200
| test01        | stu3        | NULL       | TABLE     | IX            | NULL      |
201
| test01        | stu3        | PRIMARY    | RECORD    | X             | 1         |  -- 临键锁(-∞,1]
202
| test01        | stu3        | PRIMARY    | RECORD    | X             | 2         |  -- 临键锁(1,2]
203
| test01        | stu3        | PRIMARY    | RECORD    | X             | 4         |  -- 临键锁(3,4]  (注：额外对id=4及之前的间隙加锁，可能影响业务)
204
| test01        | stu3        | PRIMARY    | RECORD    | X             | 3         |  -- 临键锁(2,3]
205
+---------------+-------------+------------+-----------+---------------+-----------+
206
9 rows in set (0.01 sec)
207

208
-- 先执行小于再执行等于
209
mysql> update stu3 set name = 'hyx' where id < 3; 
210
mysql> update stu3 set name = 'hyx' where id = 3;
211
+---------------+-------------+------------+-----------+---------------+-----------+
212
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
213
+---------------+-------------+------------+-----------+---------------+-----------+
214
| test01        | stu3        | NULL       | TABLE     | IX            | NULL      |
215
| test01        | stu3        | PRIMARY    | RECORD    | X             | 1         |  -- 临键锁(-∞,1]
216
| test01        | stu3        | PRIMARY    | RECORD    | X             | 2         |  -- 临键锁(1,2]
217
| test01        | stu3        | PRIMARY    | RECORD    | X             | 3         |  -- 临键锁(2,3]  (注：这里只会将3及之前的记录加临键锁，符合业务需要)
218
+---------------+-------------+------------+-----------+---------------+-----------+
219

220

221
-- ------------------ 8. 非唯一索引上的等值查询 ----------------------- --
222
mysql> select * from stu3;
223
+----+-------+-----+
224
| id | name  | age |
225
+----+-------+-----+
226
|  1 | tom   |  18 |
227
|  2 | cat   |  19 |
228
|  3 | rose  |  19 |
229
|  4 | jetty |  19 |
230
|  5 | lily  |  20 |
231
+----+-------+-----+
232

233
-- 创建非唯一索引
234
mysql> create index stu_age_idx on stu3(age);
235

236
-- 非唯一索引的等值查询，可能更新多条记录
237
mysql> update stu3 set name = 'hyx' where age = 19; 
238
+---------------+-------------+-------------+-----------+---------------+-----------+
239
| object_schema | object_name | index_name  | lock_type | lock_mode     | lock_data |
240
+---------------+-------------+-------------+-----------+---------------+-----------+
241
| test01        | stu3        | NULL        | TABLE     | IX            | NULL      | 
242
| test01        | stu3        | stu_age_idx | RECORD    | X             | 19, 2     |  -- 临键锁(18->1,19->2]  (注：额外锁住了第1个匹配值之前的间隙，可能影响业务)
243
| test01        | stu3        | stu_age_idx | RECORD    | X             | 19, 3     |  -- 临键锁(19->2,19->3] 
244
| test01        | stu3        | stu_age_idx | RECORD    | X             | 19, 4     |  -- 临键锁(19->3,19->4] 
245
| test01        | stu3        | PRIMARY     | RECORD    | X,REC_NOT_GAP | 2         |  -- 行锁id=2             (注：在非主键索引上加锁，必须额外锁住主键上的对应行)
246
| test01        | stu3        | PRIMARY     | RECORD    | X,REC_NOT_GAP | 3         |  -- 行锁id=3
247
| test01        | stu3        | PRIMARY     | RECORD    | X,REC_NOT_GAP | 4         |  -- 行锁id=4
248
| test01        | stu3        | stu_age_idx | RECORD    | X,GAP         | 20, 5     |  -- 间隙锁(19->4,20->5)  (注：额外锁住了第1个不匹配值之前的间隙，可能影响业务)
249
+---------------+-------------+-------------+-----------+---------------+-----------+
250

251

252
-- ------------------ 9. 非唯一索引上的范围查询 ----------------------- --
253
mysql> select * from stu3;
254
+----+-------+-----+
255
| id | name  | age |
256
+----+-------+-----+
257
|  1 | tom   |  10 |
258
|  2 | cat   |  20 |
259
|  3 | rose  |  30 |
260
|  4 | jetty |  40 |
261
|  5 | lily  |  50 |
262
+----+-------+-----+
263

264
-- 大于(和唯一索引范围查询表现类似)
265
mysql> update stu3 set name = 'hyx' where age > 30;
266
+---------------+-------------+-------------+-----------+---------------+------------------------+
267
| object_schema | object_name | index_name  | lock_type | lock_mode     | lock_data              |
268
+---------------+-------------+-------------+-----------+---------------+------------------------+
269
| test01        | stu3        | NULL        | TABLE     | IX            | NULL                   | 
270
| test01        | stu3        | stu_age_idx | RECORD    | X             | supremum pseudo-record |  -- 临键锁(50->5,+∞]      
271
| test01        | stu3        | stu_age_idx | RECORD    | X             | 40, 4                  |  -- 临键锁(30->3,40->4]
272
| test01        | stu3        | stu_age_idx | RECORD    | X             | 50, 5                  |  -- 临键锁(40->4,50->5]
273
| test01        | stu3        | PRIMARY     | RECORD    | X,REC_NOT_GAP | 5                      |  -- 行锁id=5   
274
| test01        | stu3        | PRIMARY     | RECORD    | X,REC_NOT_GAP | 4                      |  -- 行锁id=4
275
+---------------+-------------+-------------+-----------+---------------+------------------------+
276

277
-- 大于 && 小于等于(此种情形会额外对限定之外的记录添加“行锁”，影响非常大)
278
mysql> update stu3 set name = 'hyx' where age > 20 and age <= 40;
279
+---------------+-------------+-------------+-----------+---------------+-----------+
280
| object_schema | object_name | index_name  | lock_type | lock_mode     | lock_data |
281
+---------------+-------------+-------------+-----------+---------------+-----------+
282
| test01        | stu3        | NULL        | TABLE     | IX            | NULL      |
283
| test01        | stu3        | stu_age_idx | RECORD    | X             | 30, 3     |  -- 临键锁(20->2,30->3]
284
| test01        | stu3        | stu_age_idx | RECORD    | X             | 40, 4     |  -- 临键锁(30->3,40->4]
285
| test01        | stu3        | stu_age_idx | RECORD    | X             | 50, 5     |  -- 临键锁(40=>4,50->5]  (注：额外对age=50及之前的间隙加锁，可能影响业务)
286
| test01        | stu3        | PRIMARY     | RECORD    | X,REC_NOT_GAP | 3         |  -- 行锁id=3
287
| test01        | stu3        | PRIMARY     | RECORD    | X,REC_NOT_GAP | 4         |  -- 行锁id=4 
288
| test01        | stu3        | PRIMARY     | RECORD    | X,REC_NOT_GAP | 5         |  -- 行锁id=5 
289
+---------------+-------------+-------------+-----------+---------------+-----------+
290

291
-- 大于等于 && 小于
292
mysql> update stu3 set name = 'hyx' where age >= 20 and age < 40;
293
+---------------+-------------+-------------+-----------+---------------+-----------+
294
| object_schema | object_name | index_name  | lock_type | lock_mode     | lock_data |
295
+---------------+-------------+-------------+-----------+---------------+-----------+
296
| test01        | stu3        | NULL        | TABLE     | IX            | NULL      |
297
| test01        | stu3        | stu_age_idx | RECORD    | X             | 20, 2     |  -- 临键锁(10->1,20->2]  (注：额外对20之前的间隙加锁，可能影响业务)
298
| test01        | stu3        | stu_age_idx | RECORD    | X             | 30, 3     |  -- 临键锁(20->2,30->3]
299
| test01        | stu3        | stu_age_idx | RECORD    | X             | 40, 4     |  -- 临键锁(30->3,40->4]
300
| test01        | stu3        | stu_age_idx | RECORD    | X             | 20, 5     |  -- 临键锁(40->4,50->5]
301
| test01        | stu3        | PRIMARY     | RECORD    | X,REC_NOT_GAP | 2         |  -- 行锁id=2
302
| test01        | stu3        | PRIMARY     | RECORD    | X,REC_NOT_GAP | 3         |  -- 行锁id=3
303
| test01        | stu3        | PRIMARY     | RECORD    | X,REC_NOT_GAP | 4         |  -- 行锁id=4
304
+---------------+-------------+-------------+-----------+---------------+-----------+
305