MySQL高级

下载地址

https://dev.mysql.com/downloads/mysql/

环境准备

查看是否安装过MySQL

rpm -qa | grep -i mariadb

如果有需要卸载

rpm -e --nodeps mariadb-libs

检查/tmp临时目权限

chmod -R 777 /tmp 

执行安装命令前，先执行查询命令检查依赖

rpm -qa|grep libaio
rpm -qa|grep net-tools
# 如果libaio, net-tools 不存在使用yum安装一下就可以了
yum install -y perl-Module-Install.noarch

上传安装包到/opt/software/mysql目录下, 然后解压安装

tar xvf mysql-8.0.25-1.el7.x86_64.rpm-bundle.tar
rpm -ivh mysql-community-common-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.25-1.el7.x86_64.rpm
# 这个没有就不装
#rpm -ivh mysql-community-icu-data-files-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.25-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.25-1.el7.x86_64.rpm

查看MySQL的版本

# mysql --version
mysql  Ver 8.0.25 for Linux on x86_64 (MySQL Community Server - GPL)

服务的初始化

mysqld --initialize --user=mysql

修改密码

查看新密码
临时密码是: n6icAtvaZ=9r

cat /var/log/mysqld.log
2023-07-04T11:31:52.757613Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: n6icAtvaZ=9r
# 或者使用grep查找一下临时密码
# grep 'temporary password' /var/log/mysqld.log

修改密码

# 启动mysql
systemctl start mysqld
# 登录mysql
mysql -uroot -p
# n6icAtvaZ=9r

# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

修改开机自启动

默认是修改开机自启动

[root@hadoop200 mysql]# systemctl list-unit-files|grep mysqld.service
mysqld.service                                enabled

# 开启
systemctl enable mysqld.service

# 关闭
systemctl disable mysqld.service

忘了密码

vi /etc/my.cnf
[mysqld]
skip_grant_tables=1

mysql -uroot
use mysql;
update user set authentication_string='' where user='root';
alter user 'root'@'localhost' identified by '123456';

# 接着再注释掉
# skip_grant_tables=1

授权

授予全部权限

# 创建一个用户
create user xiamu identified by '123456';

# 查询所有的用户
select user from mysql.user;

# 赋予所有权限给xiamu
grant all privileges on *.* to xiamu;

# 此时xiamu
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)

# 回收所有权限
revoke all privileges on *.* from xiamu;

# 此时xiamu
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

查看用户当前权限

show grants;

授予部分权限

grant SELECT, INSERT, UPDATE, DELETE, CREATE on xxxx.* to 'xiamu';

# 此时xiamu
create table xxoo(id int);
mysql> show tables;
+----------------+     
| Tables_in_xxxx |     
+----------------+     
| xxoo           |     
+----------------+     
1 row in set (0.00 sec)

开启远程连接

# 修改加密规则 
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
# 更新一下用户的密码 
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
#刷新权限
FLUSH PRIVILEGES;

ALTER USER 'xiamu' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
ALTER USER 'xiamu' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;

select user, host from mysql.user;

update mysql.user set host = '%' where user = 'root';
FLUSH PRIVILEGES;

sql_mode

sql_mode设置是否允许一些非法操作，比如允许一些非法数据的插入,数据查询等。
在生产环境必须将这个值设置为严格模式，所以开发、测试、生产环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。

select @@GLOBAL.sql_mode;
-- 全局的sql_mode
select @@SESSION.sql_mode;
-- 会话级别sql_mode

错误开发演示

CREATE TABLE mytbl2(id INT,NAME VARCHAR(16),age INT,dept INT);
INSERT INTO mytbl2 VALUES(1,'zhang3',33,101);
INSERT INTO mytbl2 VALUES(2,'li4',34,101);
INSERT INTO mytbl2 VALUES(3,'wang5',34,102);
INSERT INTO mytbl2 VALUES(4,'zhao6',34,102);
INSERT INTO mytbl2 VALUES(5,'tian7',36,102);

#查询每个部门年龄最大的人
select name,dept,max(age) from mytbl2 group by dept;
# ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxxx.mytbl2.NAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 

# 在当前会话关闭sql_mode
SET SESSION sql_mode = '';

# 再次查询, 此时的name会显示错误, 但会不报错了
select name,dept,max(age) from mytbl2 group by dept;

正确的语句：
SELECT m.* FROM mytbl2 m INNER JOIN(SELECT dept,MAX(age) maxage FROM mytbl2 GROUP BY dept) AS ab 
ON ab.dept=m.dept AND m.age=ab.maxage

设置当前窗口中设置sql_mode

设置当前窗口中设置sql_mode
SET GLOBAL sql_mode = 'modes...';
SET SESSION sql_mode = 'modes...';

MyISAM 不支持事务, 但是效率极高, 不支持行级锁, 只支持表锁, 适合存储海量不规则低价数据
InnoDB 支持行级锁, 支持事务, 适合存储有逻辑性的数据, 关系型的数据

MySQL8中SQL执行原理

确认profiling是否开启

select @@profiling;
set profiling = 1;
select @@session.profiling;

mysql> select * from employees;
mysql> select * from employees;
mysql> show profiles;
+----------+------------+----------------------------+
| Query_ID | Duration   | Query                      |
+----------+------------+----------------------------+
|        1 | 0.00018550 | select @@session.profiling |
|        2 | 0.00018150 | SELECT DATABASE()          |
|        3 | 0.00132150 | show tables                |
|        4 | 0.00055875 | select * from employees    |
|        5 | 0.00045575 | select * from employees    |
+----------+------------+----------------------------+
5 rows in set, 1 warning (0.00 sec)

mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000081 |
| Executing hook on transaction  | 0.000006 |
| starting                       | 0.000010 |
| checking permissions           | 0.000008 |
| Opening tables                 | 0.000037 |
| init                           | 0.000007 |
| System lock                    | 0.000010 |
| optimizing                     | 0.000006 |
| statistics                     | 0.000018 |
| preparing                      | 0.000016 |
| executing                      | 0.000208 |
| end                            | 0.000005 |
| query end                      | 0.000005 |
| waiting for handler commit     | 0.000009 |
| closing tables                 | 0.000009 |
| freeing items                  | 0.000012 |
| cleaning up                    | 0.000011 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

# 指定查询对应的sql的profile
mysql> show profile for query 5;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000081 |
| Executing hook on transaction  | 0.000006 |
| starting                       | 0.000010 |
| checking permissions           | 0.000008 |
| Opening tables                 | 0.000037 |
| init                           | 0.000007 |
| System lock                    | 0.000010 |
| optimizing                     | 0.000006 |
| statistics                     | 0.000018 |
| preparing                      | 0.000016 |
| executing                      | 0.000208 |
| end                            | 0.000005 |
| query end                      | 0.000005 |
| waiting for handler commit     | 0.000009 |
| closing tables                 | 0.000009 |
| freeing items                  | 0.000012 |
| cleaning up                    | 0.000011 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 4;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000091 |
| Executing hook on transaction  | 0.000007 |
| starting                       | 0.000009 |
| checking permissions           | 0.000006 |
| Opening tables                 | 0.000138 |
| init                           | 0.000006 |
| System lock                    | 0.000009 |
| optimizing                     | 0.000005 |
| statistics                     | 0.000011 |
| preparing                      | 0.000015 |
| executing                      | 0.000181 |
| end                            | 0.000005 |
| query end                      | 0.000004 |
| waiting for handler commit     | 0.000024 |
| closing tables                 | 0.000007 |
| freeing items                  | 0.000014 |
| cleaning up                    | 0.000029 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)


# 查询对应的cpu io阻塞信息
mysql> show profile cpu, block io for query 5;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000081 | 0.000000 |   0.000072 |            0 |             0 |
| Executing hook on transaction  | 0.000006 | 0.000000 |   0.000005 |            0 |             0 |
| starting                       | 0.000010 | 0.000000 |   0.000010 |            0 |             0 |
| checking permissions           | 0.000008 | 0.000000 |   0.000007 |            0 |             0 |
| Opening tables                 | 0.000037 | 0.000000 |   0.000037 |            0 |             0 |
| init                           | 0.000007 | 0.000000 |   0.000006 |            0 |             0 |
| System lock                    | 0.000010 | 0.000000 |   0.000010 |            0 |             0 |
| optimizing                     | 0.000006 | 0.000000 |   0.000006 |            0 |             0 |
| statistics                     | 0.000018 | 0.000000 |   0.000018 |            0 |             0 |
| preparing                      | 0.000016 | 0.000000 |   0.000016 |            0 |             0 |
| executing                      | 0.000208 | 0.000000 |   0.000208 |            0 |             0 |
| end                            | 0.000005 | 0.000000 |   0.000005 |            0 |             0 |
| query end                      | 0.000005 | 0.000000 |   0.000005 |            0 |             0 |
| waiting for handler commit     | 0.000009 | 0.000000 |   0.000009 |            0 |             0 |
| closing tables                 | 0.000009 | 0.000000 |   0.000009 |            0 |             0 |
| freeing items                  | 0.000012 | 0.000000 |   0.000012 |            0 |             0 |
| cleaning up                    | 0.000011 | 0.000000 |   0.000011 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
17 rows in set, 1 warning (0.00 sec)

mysql> show variables like '%storage_engine%%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.00 sec)

mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

关于csv存储引擎

mysql> create database dbtest1;
Query OK, 1 row affected (0.01 sec)

mysql> use dbtest1;
Database changed
mysql> create table csv_demo(id int not null, name char(20) not null) engine = csv;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table csv_demo;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table
                         |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| csv_demo | CREATE TABLE `csv_demo` (
  `id` int NOT NULL,
  `name` char(20) COLLATE utf8mb4_general_ci NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> show variables like '%datadir%';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| datadir       | /www/server/data/ |
+---------------+-------------------+
1 row in set (0.00 sec)

## 插入两条数据
mysql> select * from csv_demo;
Empty set (0.00 sec)

mysql> insert into csv_demo(id, name) values(1, 'atguigu01'), (2, 'atguigu2');
Query OK, 2 rows affected (0.01 sec)  
Records: 2  Duplicates: 0  Warnings: 0

[root@iZf8z0puua6p4won7xep5rZ data]# ll
total 226064
drwxr-x--- 2 mysql mysql     4096 Sep  4 21:13 atguigudb
drwxr-x--- 2 mysql mysql     4096 Sep  6 19:43 dbtest1
[root@iZf8z0puua6p4won7xep5rZ data]# cd dbtest1/
[root@iZf8z0puua6p4won7xep5rZ dbtest1]# ls
csv_demo_374.sdi  csv_demo.CSM  csv_demo.CSV
[root@iZf8z0puua6p4won7xep5rZ dbtest1]# cat csv_demo.CSV 
1,"atguigu01"
2,"atguigu2"

索引

mysql> select * from employees;

聚簇索引是根据主键的索引
非聚簇索引 是根据非主键的其他字段的索引
在一个表中, 会有一个聚簇索引, 但是可以存在多个聚簇索引

二级索引(辅助索引, 非聚簇索引)
非聚簇索引中, 通过c2查找到对应的主键c1, 然后再把c1到聚簇索引中再次搜索(回表操作)

聚簇索引中存储是真实所有的数据, 非聚簇索引存储的不是并不是所有数据, 而是 某一个字段c2 和 主键 c1 两个字段的数据, 并不会将表中的所有字段都存储到该索引中来, 这样冗余的程度太大了, 假设有十个二级索引, 每个二级索引都存储一遍所有的数据, 那就冗余了很多

联合索引的概念, 其实就是将 c1 和 c2 两个字段的同时作为数据页的索引组成的B+树

存储文件格式

InnoDB存储的文件格式是.ibd
MyISAM存储的文件格式是: .myi .myd

查看页的大小

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

Supremum 上确届
infimum 下确届

区分B+树 B树 Hash之间的区别

查看Mysql8默认的行格式:

## 查看默认的行格式
mysql> select @@innodb_default_row_format;
+-----------------------------+
| @@innodb_default_row_format |
+-----------------------------+
| dynamic                     |
+-----------------------------+
1 row in set (0.00 sec)     
mysql> use dbtest1;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_dbtest1 |
+-------------------+
| csv_demo          |
+-------------------+
1 row in set (0.00 sec)

mysql> create table emp1(id int, name varchar(15)) row_format=compact;
Query OK, 0 rows affected (0.02 sec)

mysql> show table status like 'emp1'\G
*************************** 1. row ***************************
           Name: emp1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2023-09-08 10:29:21
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: row_format=COMPACT
        Comment:
1 row in set (0.00 sec)

mysql> create table emp2(id int, name varchar(15)) row_format=compact;
Query OK, 0 rows affected (0.02 sec)

mysql> show table status like 'emp2'\G
*************************** 1. row ***************************
           Name: emp2
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2023-09-08 10:32:38
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: row_format=COMPACT
        Comment:
1 row in set (0.00 sec)

## 修改行格式
mysql> alter table emp1 row_format=compact;
Query OK, 0 rows affected (0.04 sec)  
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status like 'emp1'\G
*************************** 1. row ***************************
           Name: emp1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2023-09-08 10:34:48
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: row_format=COMPACT
        Comment:
1 row in set (0.00 sec)

记录真实的数据

mysql> use dbtest1;
Database changed



CREATE TABLE mytest(
col1 VARCHAR(10),
col2 VARCHAR(10),
col3 CHAR(10),
col4 VARCHAR(10)
)ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;



INSERT INTO mytest
VALUES('a','bb','bb','ccc'); 
INSERT INTO mytest
VALUES('d','ee','ee','fff');
INSERT INTO mytest
VALUES('d',NULL,NULL,'fff');

mysql> exit
Bye
[root@iZf8z0puua6p4won7xep5rZ mysql]# cd /www/server/data/dbtest1/
## 采用hexdump工具查看Hex进制
[root@iZf8z0puua6p4won7xep5rZ dbtest1]# hexdump -C mytest.ibd  | less

00010070  73 75 70 72 65 6d 75 6d  03 02 01 00 00 00 10 00  |supremum........|
00010080  2c 00 00 00 00 02 09 00  00 00 00 0b 1a 81 00 00  |,...............|
00010090  00 f4 01 10 61 62 62 62  62 20 20 20 20 20 20 20  |....abbbb       |
000100a0  20 63 63 63 03 02 01 00  00 00 18 00 2b 00 00 00  | ccc........+...|
000100b0  00 02 0a 00 00 00 00 0b  1b 82 00 00 00 f5 01 10  |................|
000100c0  64 65 65 65 65 20 20 20  20 20 20 20 20 66 66 66  |deeee        fff|
000100d0  03 01 06 00 00 20 ff 98  00 00 00 00 02 0b 00 00  |..... ..........|
000100e0  00 00 0b 20 81 00 00 00  f7 01 10 64 66 66 66 00  |... .......dfff.|
000100f0  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
00013ff0  00 00 00 00 00 70 00 63  9e f8 d0 17 01 1d b7 15  |.....p.c........|
00014000  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|

测试表的

## 创建失败
create table varchar_size_demo0(
  c varchar(65535) # 65533 + 2个字节的变长字段的长度 + 1NULL值标识
) charset = ascii row_format = compact;

create table varchar_size_demo(
  c varchar(65532) # 65533 + 2个字节的变长字段的长度 + 1NULL值标识
) charset = ascii row_format = compact;

create table varchar_size_demo1(
  c varchar(65533) not null # 65533 + 2个字节的变长字段的长度 
) charset = ascii row_format = compact;

## 创建失败
create table varchar_size_demo2(
  c varchar(65534) not null # 65533 + 2个字节的变长字段的长度
) charset = ascii row_format = compact;

说明了每张表的可变字段最大就是65533, 这是极限
每个字段的最大长度是65535 = 65533 + 2个字节的变长字段的长度 + 1NULL值标识
如果创建表的时候标记了not null, 那 1NULL值标识 就不会有

页的上层结构

独立表空间

## 创建一张表
mysql> create table temp(id int, name varchar(15));
Query OK, 0 rows affected (0.02 sec)

## 查看这张空表的大小
[root@iZf8z0puua6p4won7xep5rZ dbtest1]# ll
total 332
-rw-r----- 1 mysql mysql   2522 Sep  6 19:43 csv_demo_374.sdi
-rw-r----- 1 mysql mysql     35 Sep  6 20:21 csv_demo.CSM    
-rw-r----- 1 mysql mysql     27 Sep  6 19:49 csv_demo.CSV    
-rw-r----- 1 mysql mysql 114688 Sep  8 10:34 emp1.ibd        
-rw-r----- 1 mysql mysql 114688 Sep  8 10:32 emp2.ibd        
-rw-r----- 1 mysql mysql 114688 Sep  8 11:05 mytest.ibd      
-rw-r----- 1 mysql mysql 114688 Sep  8 15:13 temp.ibd  


114688 / 1024 = 112
112 / 16 = 7
7页
在mysql8.0中将 .frm .ibd两个文件合并成了一个 .ibd

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

当innodb_file_per_table的值是ON, 说明意为每张表保存为一个.ibd文件

创建索引

1.隐式创建索引

隐式的方式创建索引, 在声明有主键约束, 唯一性约束, 外键约束的字段上, 会自动的添加相关的索引

CREATE DATABASE dbtest2;

USE dbtest2;

CREATE TABLE dept(
  dept_id INT PRIMARY KEY AUTO_INCREMENT,
  dept_name VARCHAR(20)
);

CREATE TABLE emp(
  emp_id INT PRIMARY KEY AUTO_INCREMENT,
  emp_name VARCHAR(20) UNIQUE,
  dept_id INT,
  CONSTRAINT emp_dept_id_kf FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);

2.显式创建索引

(1) 创建普通索引

# 显式的方式创建: 
# 创建普通索引

CREATE TABLE book(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
# 声明索引
INDEX idx_bname(book_name)
);

(2) 创建唯一索引

# (2) 创建唯一索引
# 声明有唯一索引的字段, 在添加数据时, 要保证唯一性, 但是可以添加null
CREATE TABLE book1(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
# 声明索引
UNIQUE INDEX uk_idx_cmt(COMMENT)
);

SHOW INDEX FROM book1;

INSERT INTO book1(book_id, book_name, COMMENT)
VALUES(1, 'MySQL高级', '适合有数据库开发经验的人员学习');

INSERT INTO book1(book_id, book_name, COMMENT)
VALUES(2, 'MySQL高级', NULL);

SELECT * FROM book1;

(3) 主键索引

# (3) 主键索引
# 通过定义主键约束的方式定义主键索引
CREATE TABLE book2(
  book_id INT PRIMARY KEY,
  book_name VARCHAR(100),
  AUTHORS VARCHAR(100),
  info VARCHAR(100) ,
  COMMENT VARCHAR(100),
  year_publication YEAR
)

SHOW INDEX FROM book2;

# 通过删除主键约束的方式删除主键索引
ALTER TABLE book2  
DROP PRIMARY KEY;

(4) 创建单列索引

# (4) 创建单列索引
CREATE TABLE book3(
  book_id INT,
  book_name VARCHAR(100),
  AUTHORS VARCHAR(100),
  info VARCHAR(100) ,
  COMMENT VARCHAR(100),
  year_publication YEAR,
  # 声明索引
  UNIQUE INDEX idx_bname(book_name)
)

SHOW INDEX FROM book3;

(5) 创建联合索引

# (5) 创建联合索引
CREATE TABLE book4(
  book_id INT,
  book_name VARCHAR(100),
  AUTHORS VARCHAR(100),
  info VARCHAR(100) ,
  COMMENT VARCHAR(100),
  year_publication YEAR,
  # 声明索引
  INDEX mul_bid_bname_info(book_id, book_name, info)
)

SHOW INDEX FROM book4;

# 分析
EXPLAIN SELECT * FROM book4 WHERE book_id = 1001 AND book_name = 'mysql';

EXPLAIN SELECT * FROM book4 WHERE book_name = 'mysql';

只使用 book_id 和 book_name 查询的时候, 索引生效了

只使用 book_name 查询的时候, 索引失效了

(6) 创建全文检索

# (6) 创建全文检索
CREATE TABLE test4(
  id INT NOT NULL,
  NAME CHAR(30) NOT NULL,
  age INT NOT NULL,
  info VARCHAR(255),
  FULLTEXT INDEX futxt_idx_info(info)
);

SHOW INDEX FROM test4;

添加索引(表已经存在, 后来添加索引)

## 3.表已经创建成功


# (1) ALTER TABLE ... ADD ...

CREATE TABLE book5(
  book_id INT,
  book_name VARCHAR(100),
  AUTHORS VARCHAR(100),
  info VARCHAR(100) ,
  COMMENT VARCHAR(100),
  year_publication YEAR
)

SHOW INDEX FROM book5;

ALTER TABLE book5 ADD INDEX idx_cmt(COMMENT);

ALTER TABLE book5 ADD UNIQUE uk_idx_bname(book_name);

ALTER TABLE book5 ADD INDEX mul_bid_bname_info(book_id, book_name, info);

# (2) CREATE INDEX ... ON ...

CREATE TABLE book6(
  book_id INT,
  book_name VARCHAR(100),
  AUTHORS VARCHAR(100),
  info VARCHAR(100) ,
  COMMENT VARCHAR(100),
  year_publication YEAR
)

SHOW INDEX FROM book6;

CREATE INDEX idx_cmt ON book6(COMMENT);

CREATE INDEX uk_idx_bname ON book6(book_name);

CREATE INDEX mul_bid_bname_info ON book6(book_id, book_name, info);

索引的删除

注意点: 添加AUTO_INCREMENT约束字段的唯一索引是不能够被删除的

# 02-索引的删除

SHOW INDEX FROM book5;

# (1): ALTER TABLE ... DROP INDEX ...

ALTER TABLE book5 DROP INDEX idx_cmt;

# (2): DROP INDEX ... ON ...

DROP INDEX uk_idx_bname ON book5;

# 测试: 删除联合索引中的相关字段, 索引也会随之被删除掉
# 当删除联合索引中的字段, 相对应的索引也会随之被删除掉
# 删除book_name字段
ALTER TABLE book5 DROP COLUMN book_name;

ALTER TABLE book5 DROP COLUMN book_id;

ALTER TABLE book5 DROP COLUMN info;

当删除联合索引中的字段, 相对应的索引也会随之被删除掉

降序索引

CREATE TABLE ts1(a INT,b INT,INDEX idx_a_b(a ASC,b DESC));

SHOW CREATE TABLE ts1;
/*
CREATE TABLE `ts1` (
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL,
  KEY `idx_a_b` (`a`,`b` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
*/


DELIMITER //
CREATE PROCEDURE ts_insert()
BEGIN
   DECLARE i INT DEFAULT 1;
   WHILE i < 800
   DO
       INSERT INTO ts1 SELECT RAND()*80000,RAND()*80000;
       SET i = i + 1;
   END WHILE;
    COMMIT;
END //
DELIMITER ;
#调用
CALL ts_insert();


SELECT COUNT(*) FROM ts1;

# 优化测试
EXPLAIN SELECT * FROM ts1 ORDER BY a, b DESC LIMIT 5;
/*
mysql> EXPLAIN SELECT * FROM ts1 ORDER BY a, b DESC LIMIT 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ts1
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_a_b
      key_len: 10
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.01 sec)
*/

# 不推荐
EXPLAIN SELECT * FROM ts1 ORDER BY a DESC, b DESC LIMIT 5;
/*
mysql> EXPLAIN SELECT * FROM ts1 ORDER BY a DESC, b DESC LIMIT 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ts1
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_a_b
      key_len: 10
          ref: NULL
         rows: 799
     filtered: 100.00
        Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)
*/

隐藏索引

# 2. 隐藏索引
# (1) 创建表时, 隐藏索引
CREATE TABLE book7(
  book_id INT,
  book_name VARCHAR(100),
  AUTHORS VARCHAR(100),
  info VARCHAR(100),
  COMMENT VARCHAR(100),
  year_publication YEAR,
  # 创建不可见的索引
  INDEX idx_cmt(COMMENT) invisible
)

SHOW INDEX FROM book7;

EXPLAIN SELECT * FROM book7 WHERE COMMENT = 'mysql...';



# (2) 创建表以后, 添加索引的可见性与不可见行
ALTER TABLE book7
ADD UNIQUE INDEX uk_idx_bname(book_name) invisible;

# 此时创建的是可见性的(默认)
CREATE INDEX idx_year_pub ON book7(year_publication);

# 此时创建的是不可见性的
CREATE INDEX idx_info ON book7(info) invisible;

# 修改索引的可见性
ALTER TABLE book7 ALTER INDEX idx_year_pub invisible; # 可见 -> 不可见

ALTER TABLE book7 ALTER INDEX idx_cmt visible; #不可见 -> 可见

EXPLAIN SELECT * FROM book7 WHERE year_publication = '2022';

# 了解: 使隐藏索引对查询优化器可见
SELECT @@optimizer_switch\G
/*
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
*/
# 可以发现use_invisible_indexes=on的值是on, 说明优化器会去使用隐藏索引进行优化(隐藏索引对优化器可见)
# 当use_invisible_indexes的值修改成off, 之后优化器不会使用这个隐藏索引进行优化(隐藏索引对优化器不可见)

SET SESSION optimizer_switch="use_invisible_indexes=on";
/*
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=off,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
*/
EXPLAIN SELECT * FROM book7 WHERE year_publication = '2022';

## use_invisible_indexes=on的情况, 过滤的时候使用了索引
mysql> EXPLAIN SELECT * FROM book7 WHERE year_publication = '2022';
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | book7 | NULL       | ref  | idx_year_pub  | idx_year_pub | 2       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

## use_invisible_indexes=off的情况, 过滤的时候没有使用索引
mysql> EXPLAIN SELECT * FROM book7 WHERE year_publication = '2022';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | book7 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

哪些情况适合创建索引

## 04-索引的设计原则

CREATE DATABASE atguigudb1;
USE atguigudb1;
# 1. 创建学生表和课程表
CREATE TABLE `student_info` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`student_id` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`course_id` INT NOT NULL ,
`class_id` INT(11) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `course` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`course_id` INT NOT NULL ,
`course_name` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

#函数1：创建随机产生字符串函数
DELIMITER //
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;

# 报错的解决方法
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
SET GLOBAL log_bin_trust_function_creators = ON;

#函数2：创建随机数函数
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;



# 存储过程1：创建插入课程表存储过程
DELIMITER //
CREATE PROCEDURE insert_course( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO course (course_id, course_name ) VALUES
(rand_num(10000,10100),rand_string(6));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;

# 存储过程2：创建插入学生信息表存储过程
DELIMITER //
CREATE PROCEDURE insert_stu( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student_info (course_id, class_id ,student_id ,NAME ) VALUES
(rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;

# 插入数据
CALL insert_course(100);
CALL insert_stu(1000000);


# 2. 哪些情况适合创建索引
# (1) 字段的数值有唯一性的限制


# (2) 频繁作为 WHERE 查询条件的字段
# 查询当前 student_info 表中的索引
SHOW INDEX FROM student_info;

# student_id 字段上没有索引的:
SELECT course_id, class_id, NAME, create_time, student_id
FROM student_info
WHERE student_id  = 123110; # 325ms

# 添加索引, 需要花费一定时间
ALTER TABLE student_info ADD INDEX idx_sid(student_id);

# student_id 字段上有索引的:
SELECT course_id, class_id, NAME, create_time, student_id
FROM student_info
WHERE student_id  = 123110; # 118ms
# 这个案例中可见, 给 student_id 添加了索引之后使用 where 进行查询, 速度快了将近三倍


# (3) 经常 GROUP BY 和 ORDER BY 的列

SHOW INDEX FROM student_info;

# student_id 字段上有索引的: 
SELECT student_id, COUNT(*) AS num
FROM student_info
GROUP BY student_id LIMIT 100; #68ms

# 删除 idx_sid 索引
DROP INDEX idx_sid ON student_info;

# student_id 字段上没有索引的: 
SELECT student_id, COUNT(*) AS num
FROM student_info
GROUP BY student_id LIMIT 100; #709ms

# 再测试
SHOW INDEX FROM student_info;

# 添加单列索引
ALTER TABLE student_info
ADD INDEX idx_sid(student_id);


SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC
LIMIT 100; #4.308s

# 修改sql_mode
# select @@sql_mode;

# 添加联合索引
ALTER TABLE student_info 
ADD INDEX idx_sid_cre_time(student_id, create_time DESC);

SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC
LIMIT 100; #0.352s

# 再进一步: 
ALTER TABLE student_info
ADD INDEX idx_cre_time_sid(create_time DESC, student_id);

DROP INDEX idx_sid_cre_time ON student_info;

SHOW INDEX FROM student_info;

SELECT student_id, COUNT(*) AS num FROM student_info 
GROUP BY student_id 
ORDER BY create_time DESC
LIMIT 100; #3.374s

# (4) UPDATE, DELETE 的 WHERE 条件列

# 先查看一下是否有跟 name 相关的索引
SHOW INDEX FROM student_info;

UPDATE student_info SET student_id = 10002
WHERE NAME = '462eed7ac6e791292a79'; # 0.859 s

# 添加 name 索引 
ALTER TABLE student_info ADD INDEX idx_name(NAME);

UPDATE student_info SET student_id = 10001
WHERE NAME = '462eed7ac6e791292a79'; # 0.119 s


# (5) DISTINCT 字段需要创建索引

# 查看索引
SHOW INDEX FROM student_info;

# 删除 student_id 索引
DROP INDEX idx_sid ON student_info;

SELECT DISTINCT(student_id) FROM student_info; # 0.100s 0.51 0.101

# 添加student_id 索引

ALTER TABLE student_info ADD INDEX idx_sid(student_id DESC);

SELECT DISTINCT(student_id) FROM student_info; # 0.194s 0.70 0.094

SELECT COUNT(*) FROM student_info

# 这里最后一次测试是采用的降序索引, 可能是随机生成偏大的值更多一些


# (6) 多表 JOIN 连接操作时，创建索引注意事项

# 首先， 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
# 其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
# 最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。比如 course_id 在student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

SHOW INDEX FROM student_info;


# 此时查询是没有索引的
SELECT s.course_id, NAME, s.student_id, c.course_name
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE NAME = '462eed7ac6e791292a79'; # 0.109s

# 创建索引
CREATE INDEX idx_sid ON student_info(student_id);

# 再次查询
SELECT s.course_id, NAME, s.student_id, c.course_name
FROM student_info s JOIN course c
ON s.course_id = c.course_id
WHERE NAME = '462eed7ac6e791292a79'; # 0.044s



# (7) 使用列的类型小的创建索引

# (8) 使用字符串前缀创建索引

# (9) 区分度(散列值高)的列适合作为索引

# (10) 使用最频繁的列放到联合索引的左侧

SELECT * FROM student_info 
WHERE student_id = 10013 AND count_id = 100;

# 补充: 在多个字段都要创建索引的情况下, 联合索引由于单值索引

哪些情况不适合创建索引

# 3. 哪些情况不适合创建索引
# (1) 在 where 中使用不到的字段, 不要设置索引

# (2) 数据量小的表最好不要使用索引

# (3) 在大量重复数据的列上不要建议索引
# 结论: 当数据重复度大, 比如 `高于10%` 的时候, 也不需要对这个字段使用索引

# (4) 避免对经常更新的表创建过多的索引

# (5) 不建议用无序的值作为索引

系统性能参数

## 查看连接次数
mysql> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 89    |
+---------------+-------+
1 row in set (0.00 sec)  

## 服务器上限时间
mysql> show status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 63580 |
+---------------+-------+
1 row in set (0.00 sec)  

## 重启服务器
[root@iZf8z0puua6p4won7xep5rZ ~]# systemctl restart mysqld

mysql> show status like 'uptime';
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    8
Current database: *** NONE ***

+---------------+-------+     
| Variable_name | Value |
+---------------+-------+
| Uptime        | 10    |
+---------------+-------+
1 row in set (0.00 sec)

## 慢查询次数
mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.01 sec)  

## 行数, 对应的增删改查
mysql> show status like 'innodb_rows_%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 0     |
| Innodb_rows_inserted | 0     |
| Innodb_rows_read     | 0     |
| Innodb_rows_updated  | 0     |
+----------------------+-------+
4 rows in set (0.00 sec) 

mysql> select * from course;
...
100 rows in set (0.01 sec)

## 再次查看
mysql> show status like 'innodb_rows_%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_deleted  | 0     |
| Innodb_rows_inserted | 0     |
| Innodb_rows_read     | 100   |
| Innodb_rows_updated  | 0     |
+----------------------+-------+
4 rows in set (0.00 sec)

统计SQL的查询成本：last_query_cost

mysql> show status like 'last_query_cost';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| Last_query_cost | 10.999000 |
+-----------------+-----------+
1 row in set (0.01 sec)  

mysql> select * from student_info where student_id > 199000;
...
4866 rows in set (0.45 sec)

mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 5046.624662 |
+-----------------+-------------+
1 row in set (0.01 sec)

mysql> select * from student_info where student_id > 199900;
...
493 rows in set (0.00 sec)

mysql> show status like 'last_query_cost';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| Last_query_cost | 277.659847 |
+-----------------+------------+
1 row in set (0.00 sec)

这两次查询的时间, 一次是 0.05s , 另外一次是 0.00s ,
但是第一次查询需要查的页是 5046 页
第二次需要查 277 页

last_query_cost反应了查询的复杂度

定位执行慢的 SQL：慢查询日志

## 查看是否开启慢查询日志, 此时是开启的
mysql> show variables like '%slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.01 sec)  

## 如果没有开启, 可以使用如下命令开启日志
mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.00 sec)

## 查看慢查询日志存储的位置
mysql> show variables like '%slow_query_log%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /www/server/data/mysql-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)

## 慢查询时间
mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

## 修改 global级别 慢查询时间
mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

## 修改会话级别
mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.00 sec)       

mysql> set long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)

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

## 慢查询次数
mysql> show global status like '%slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.01 sec)  


## 使用数据库
mysql> use atguigudb1;
Database changed

## 创建表
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

## 创建存储过程
DELIMITER //
CREATE PROCEDURE insert_stu1( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, NAME ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;

## 调用存储过程
#调用刚刚写好的函数, 4000000条记录,从100001号开始
CALL insert_stu1(100001,4000000);


## 查询mock数据量
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|  4000000 |
+----------+
1 row in set (1.23 sec)

## 查看一下表结构
mysql> desc student;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int         | NO   | PRI | NULL    | auto_increment |
| stuno   | int         | NO   |     | NULL    |                |
| name    | varchar(20) | YES  |     | NULL    |                |
| age     | int         | YES  |     | NULL    |                |
| classId | int         | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

## 随机查询一条数据
mysql> select * from student where stuno = 3453451;
+---------+---------+--------+------+---------+
| id      | stuno   | name   | age  | classId |
+---------+---------+--------+------+---------+
| 3353450 | 3453451 | APwOJN |   97 |     515 |
+---------+---------+--------+------+---------+
1 row in set (1.04 sec)

## 通过随机查询出来的学号查对应的 name
mysql> select * from student where name = 'APwOJN';
+---------+---------+--------+------+---------+
| id      | stuno   | name   | age  | classId |
+---------+---------+--------+------+---------+
|   36674 |  136675 | aPWoJn |   50 |     425 |
| 2134138 | 2234139 | APwOJN |   93 |     301 |
| 3353450 | 3453451 | APwOJN |   97 |     515 |
| 3371124 | 3471125 | aPWoJn |   49 |     381 |
| 3617398 | 3717399 | APwOJN |   96 |     458 |
| 3630847 | 3730848 | APwOJN |   98 |     545 |
+---------+---------+--------+------+---------+
6 rows in set (1.11 sec)

## 查看一下有几次慢查询
mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 4     |
+---------------+-------+
1 row in set (0.00 sec)

## 找到存储日志文件的地方
mysql> show variables like '%datadir%';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| datadir       | /www/server/data/ |
+---------------+-------------------+
1 row in set (0.00 sec)

## 查看慢查询日志存储的位置
mysql> show variables like '%slow_query_log%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | ON                              |
| slow_query_log_file | /www/server/data/mysql-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)

## 进入日志文件目录
[root@iZf8z0puua6p4won7xep5rZ ~]# cd /www/server/data

[root@iZf8z0puua6p4won7xep5rZ data]# ll | grep mysql-slow.log
-rw-r----- 1 mysql mysql      3677 Sep 10 17:37 mysql-slow.log

# 我也不知道为啥 command not found
[root@iZf8z0puua6p4won7xep5rZ bin]# mysqldumpslow --help
-bash: mysqldumpslow: command not found

## 搜索一下 mysqldumpslow
[root@iZf8z0puua6p4won7xep5rZ bin]# find / -name mysqldumpslow
/www/server/mysql/bin/mysqldumpslow

[root@iZf8z0puua6p4won7xep5rZ bin]# /www/server/mysql/bin/mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default       
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)  
  -l           don't subtract lock time from total time

[root@iZf8z0puua6p4won7xep5rZ bin]# /www/server/mysql/bin/mysqldumpslow -s t -t 5 /www/server/data/mysql-slow.log

Reading mysql slow query log from /www/server/data/mysql-slow.log
Count: 1  Time=282.91s (282s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost       
  CALL insert_stu1(N,N)

Count: 1  Time=84.57s (84s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[14.150.187.223]  
  CALL insert_stu(N)

Count: 1  Time=4.23s (4s)  Lock=0.00s (0s)  Rows=100.0 (100), root[root]@[14.150.187.223]
  select student_id, count(*) as num from student_info
  group by student_id
  order by create_time desc
  limit N

Count: 1  Time=3.28s (3s)  Lock=0.00s (0s)  Rows=100.0 (100), root[root]@[14.150.187.223]
  SELECT student_id, COUNT(*) AS num FROM student_info
  GROUP BY student_id
  ORDER BY create_time DESC
  LIMIT N

Count: 1  Time=1.23s (1s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select count(*) from student

## 这里显示的参数是 N 和 S
## N 表示一个数字 S 表示字符串
## 添加 -a 参数

[root@iZf8z0puua6p4won7xep5rZ bin]# /www/server/mysql/bin/mysqldumpslow -a -s t -t 5 /www/server/data/mysql-slow.log

Reading mysql slow query log from /www/server/data/mysql-slow.log
Count: 1  Time=282.91s (282s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost       
  CALL insert_stu1(100001,4000000)

Count: 1  Time=84.57s (84s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@[14.150.187.223]  
  CALL insert_stu(1000000)

Count: 1  Time=4.23s (4s)  Lock=0.00s (0s)  Rows=100.0 (100), root[root]@[14.150.187.223]
  select student_id, count(*) as num from student_info
  group by student_id
  order by create_time desc
  limit 100

Count: 1  Time=3.28s (3s)  Lock=0.00s (0s)  Rows=100.0 (100), root[root]@[14.150.187.223]
  SELECT student_id, COUNT(*) AS num FROM student_info
  GROUP BY student_id
  ORDER BY create_time DESC
  LIMIT 100

Count: 1  Time=1.23s (1s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select count(*) from student


## 重启恢复慢查询默认时长
mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

[root@iZf8z0puua6p4won7xep5rZ bin]# systemctl restart mysqld

mysql> show variables like '%long_query_time%';
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    8
Current database: atguigudb1

+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

## 删除慢查询日志文件
[root@iZf8z0puua6p4won7xep5rZ data]# pwd
/www/server/data

[root@iZf8z0puua6p4won7xep5rZ data]# rm mysql-slow.log 
rm: remove regular file ‘mysql-slow.log’? y

## 重置所有日志文件
[root@iZf8z0puua6p4won7xep5rZ data]# mysqladmin -uroot -p flush-logs 
## 重置慢查询的日志文件, 这里我们只删除了慢查询的日志文件, 所以也只需要恢复慢查询的日志文件就可以了
[root@iZf8z0puua6p4won7xep5rZ data]# mysqladmin -uroot -p flush-logs slow
-bash: mysqladmin: command not found

## 总感觉宝塔安装的mysql多多少少有点问题
[root@iZf8z0puua6p4won7xep5rZ data]# /www/server/mysql/bin/mysqladmin -uroot -p flush-logs slow

[root@iZf8z0puua6p4won7xep5rZ data]# ll | grep mysql-slow
-rw-r----- 1 mysql mysql       172 Sep 10 18:56 mysql-slow.log

查看 SQL 执行成本：SHOW PROFILE

## 查看变量
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)  

## 修改
mysql> set profiling = 'ON';
Query OK, 0 rows affected, 1 warning (0.00 sec)

## 再次查询
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set (0.00 sec)  

## 随便查询两条 sql
mysql> select * from student where stuno = 3453451;
+---------+---------+--------+------+---------+
| id      | stuno   | name   | age  | classId |
+---------+---------+--------+------+---------+
| 3353450 | 3453451 | APwOJN |   97 |     515 |
+---------+---------+--------+------+---------+
1 row in set (1.29 sec)

mysql> select * from student where name = 'APwOJN';
+---------+---------+--------+------+---------+
| id      | stuno   | name   | age  | classId |
+---------+---------+--------+------+---------+
|   36674 |  136675 | aPWoJn |   50 |     425 |
| 2134138 | 2234139 | APwOJN |   93 |     301 |
| 3353450 | 3453451 | APwOJN |   97 |     515 |
| 3371124 | 3471125 | aPWoJn |   49 |     381 |
| 3617398 | 3717399 | APwOJN |   96 |     458 |
| 3630847 | 3730848 | APwOJN |   98 |     545 |
+---------+---------+--------+------+---------+
6 rows in set (1.10 sec)

## 查看 profilies
mysql> show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration   | Query                                       |
+----------+------------+---------------------------------------------+
|        1 | 0.00007275 | set prifiling = 'ON'                        |
|        2 | 0.00149175 | show variables like 'profiling'             |
|        3 | 1.28369100 | select * from student where stuno = 3453451 |
|        4 | 1.09853075 | select * from student where name = 'APwOJN' |
+----------+------------+---------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

## 此时查看的的针对最近执行过的 sql , 也就是 Query_ID = 4
mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000118 |
| Executing hook on transaction  | 0.000005 |
| starting                       | 0.000011 |
| checking permissions           | 0.000010 |
| Opening tables                 | 0.000051 |
| init                           | 0.000006 |
| System lock                    | 0.000011 |
| optimizing                     | 0.000013 |
| statistics                     | 0.000026 |
| preparing                      | 0.000024 |
| executing                      | 1.098165 |
| end                            | 0.000018 |
| query end                      | 0.000005 |
| waiting for handler commit     | 0.000011 |
| closing tables                 | 0.000013 |
| freeing items                  | 0.000025 |
| cleaning up                    | 0.000021 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

## 查询 Query_ID = 3 的profile
mysql> show profile for query 3;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.003663 |
| Executing hook on transaction  | 0.000013 |
| starting                       | 0.000010 |
| checking permissions           | 0.000007 |
| Opening tables                 | 0.005940 |
| init                           | 0.000013 |
| System lock                    | 0.000008 |
| optimizing                     | 0.000018 |
| statistics                     | 0.000017 |
| preparing                      | 0.000017 |
| executing                      | 1.273858 |
| end                            | 0.000022 |
| query end                      | 0.000008 |
| waiting for handler commit     | 0.000024 |
| closing tables                 | 0.000021 |
| freeing items                  | 0.000029 |
| cleaning up                    | 0.000026 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)

## 查看 cpu, io阻塞信息
mysql> show profile cpu, block io for query 3;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.003663 | 0.000525 |   0.000000 |         3448 |             0 |
| Executing hook on transaction  | 0.000013 | 0.000007 |   0.000000 |            0 |             0 |
| starting                       | 0.000010 | 0.000010 |   0.000000 |            0 |             0 |
| checking permissions           | 0.000007 | 0.000007 |   0.000000 |            0 |             0 |
| Opening tables                 | 0.005940 | 0.002924 |   0.003022 |            0 |             0 |
| init                           | 0.000013 | 0.000007 |   0.000000 |            0 |             0 |
| System lock                    | 0.000008 | 0.000007 |   0.000001 |            0 |             0 |
| optimizing                     | 0.000018 | 0.000016 |   0.000001 |            0 |             0 |
| statistics                     | 0.000017 | 0.000016 |   0.000002 |            0 |             0 |
| preparing                      | 0.000017 | 0.000015 |   0.000001 |            0 |             0 |
| executing                      | 1.273858 | 1.054954 |   0.176194 |       186928 |            80 |
| end                            | 0.000022 | 0.000013 |   0.000002 |            0 |             0 |
| query end                      | 0.000008 | 0.000007 |   0.000000 |            0 |             0 |
| waiting for handler commit     | 0.000024 | 0.000021 |   0.000003 |            0 |             0 |
| closing tables                 | 0.000021 | 0.000020 |   0.000002 |            0 |             0 |
| freeing items                  | 0.000029 | 0.000026 |   0.000002 |            0 |             0 |
| cleaning up                    | 0.000026 | 0.000023 |   0.000003 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
17 rows in set, 1 warning (0.00 sec)

分析查询语句：EXPLAIN

官网地址: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

## 输出每列的列名
mysql> EXPLAIN SELECT 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

数据准备

# 05-EXPLAIN的使用

EXPLAIN SELECT * FROM student_info;

SELECT * FROM student_info LIMIT 10;

EXPLAIN DELETE FROM student_info WHERE id = 2;
# 说明了 EXPLAIN 仅仅只是查看了这个执行计划, 并没有真正的执行

# DESCRIBE 跟 EXPLAIN 效果一样
DESCRIBE DELETE FROM student_info WHERE id = 2;



USE atguigudb1;

## 数据准备
CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;


CREATE TABLE s2 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

# 创建函数
DELIMITER //
CREATE FUNCTION rand_string1(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;

# 如果报错1418, This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
SET GLOBAL log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

# 创建存储过程
DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s1 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s2 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;


# 调用存储过程
CALL insert_s1(10001,10000);
CALL insert_s2(10001,10000);

# 验证数据是否插入成功
SELECT COUNT(*) FROM s1;
SELECT COUNT(*) FROM s2;

EXPLAIN各列作用

#1. table：表名
#查询的每一行记录都对应着一个单表
EXPLAIN SELECT * FROM s1;

## 不一定谁写在前面, 谁就是驱动表
## 查询出来的结果中, 上面的叫驱动表, 下面的被驱动表
#s1:驱动表  s2:被驱动表
EXPLAIN SELECT * FROM s1 INNER JOIN s2;

## 有几个select, 就说明有几个id
#2. id：在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
 SELECT * FROM s1 WHERE key1 = 'a';


 SELECT * FROM s1 INNER JOIN s2
 ON s1.key1 = s2.key1
 WHERE s1.common_field = 'a';


 SELECT * FROM s1 
 WHERE key1 IN (SELECT key3 FROM s2);


 SELECT * FROM s1 UNION SELECT * FROM s2;


 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
 
 
 EXPLAIN SELECT * FROM s1 INNER JOIN s2;
 
 
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
 
 ## 优化器将子查询重写成了连接插叙, 提升效率, 所以id都是1
 ######查询优化器可能对涉及子查询的查询语句进行重写,转变为多表查询的操作########
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');
 
 #Union去重
 EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
 
 
 EXPLAIN SELECT * FROM s1  UNION ALL SELECT * FROM s2;
 
 
 ## 不去重, 所以没有临时表
 #3. select_type：SELECT关键字对应的那个查询的类型,确定小查询在整个大查询中扮演了一个什么角色
 
 # 查询语句中不包含`UNION`或者子查询的查询都算作是`SIMPLE`类型
 EXPLAIN SELECT * FROM s1;
 
 
 #连接查询也算是`SIMPLE`类型
 EXPLAIN SELECT * FROM s1 INNER JOIN s2;
 
 
 #对于包含`UNION`或者`UNION ALL`或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个
 #查询的`select_type`值就是`PRIMARY`
 
 #对于包含`UNION`或者`UNION ALL`的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询
 #以外，其余的小查询的`select_type`值就是`UNION`
 
 #`MySQL`选择使用临时表来完成`UNION`查询的去重工作，针对该临时表的查询的`select_type`就是
 #`UNION RESULT`
 EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
 
 EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
 
 # 大的查询 包含一个 子查询, 大查询就是 `PRIMARY`
 #子查询：
 #如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是不相关子查询。
 #该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`SUBQUERY`
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
 
 
 #如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是相关子查询，
 #则该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`DEPENDENT SUBQUERY`
 EXPLAIN SELECT * FROM s1 
 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';
 #注意的是，select_type为`DEPENDENT SUBQUERY`的查询可能会被执行多次。
 
 
 #在包含`UNION`或者`UNION ALL`的大查询中，如果各个小查询都依赖于外层查询的话，那除了
 #最左边的那个小查询之外，其余的小查询的`select_type`的值就是`DEPENDENT UNION`。
 EXPLAIN SELECT * FROM s1 
 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');
 
 
 #对于包含`派生表`的查询，该派生表对应的子查询的`select_type`就是`DERIVED`
 EXPLAIN SELECT * 
 FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1;
 
 
 #当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，
 #该子查询对应的`select_type`属性就是`MATERIALIZED`
 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); #子查询被转为了物化表
 
 
 
 # 4. partition(略)：匹配的分区信息
 
 
 # 5. type：针对单表的访问方法
 
 #当表中`只有一条记录`并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，
 #那么对该表的访问方法就是`system`。
 CREATE TABLE t(i INT) ENGINE=MYISAM;
 INSERT INTO t VALUES(1);
 
 EXPLAIN SELECT * FROM t;
 
 #换成InnoDB
 CREATE TABLE tt(i INT) ENGINE=INNODB;
 INSERT INTO tt VALUES(1);
 EXPLAIN SELECT * FROM tt;
 
 
 #当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是`const`
 EXPLAIN SELECT * FROM s1 WHERE id = 10005;
 
 EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;
 
 
 #在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的
 #（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则
 #对该被驱动表的访问方法就是`eq_ref`
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
  
  
 #当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是`ref`
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
 
 
 #当对普通二级索引进行等值匹配查询，该索引列的值也可以是`NULL`值时，那么对该表的访问方法
 #就可能是`ref_or_null`
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;
 
 
 #单表访问方法时在某些场景下可以使用`Intersection`、`Union`、
 #`Sort-Union`这三种索引合并的方式来执行查询
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
 
 
 #`unique_subquery`是针对在一些包含`IN`子查询的查询语句中，如果查询优化器决定将`IN`子查询
 #转换为`EXISTS`子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的`type`
 #列的值就是`unique_subquery`
 EXPLAIN SELECT * FROM s1 
 WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a';
 
 
 #如果使用索引获取某些`范围区间`的记录，那么就可能使用到`range`访问方法
 EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
 
 #同上
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';
 
 ## 覆盖索引(索引覆盖): 即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。
 ## 这里使用的是联合索引, 是不需要通过主键回表查找的, 所以是一个覆盖索引
 #当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是`index`
 EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
 
 
 #最熟悉的全表扫描
 EXPLAIN SELECT * FROM s1;
 
 
 #6. possible_keys和key：可能用到的索引 和  实际上使用的索引
 
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';
 

 
#7.  key_len：实际使用到的索引长度(即：字节数)
# 帮你检查`是否充分的利用上了索引`，`值越大越好`,主要针对于联合索引，有一定的参考意义。
 EXPLAIN SELECT * FROM s1 WHERE id = 10005;


 EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;


 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';


 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';

 
 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';

 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
 
 EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a';
 
#练习：
#varchar(10)变长字段且允许NULL  = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

#varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

#char(10)固定字段且允许NULL    = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)

#char(10)固定字段且不允许NULL  = 10 * ( character set：utf8=3,gbk=2,latin1=1)
 
 
 
 # 8. ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息。
 #比如只是一个常数或者是某个列。
 
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
 
 
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
 
 
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);
 
 
 # 9. rows：预估的需要读取的记录条数
 # `值越小越好`
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';
 
 
 
 # 10. filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比
 
 #如果使用的是索引执行的单表扫描，那么计算时需要估计出满足除使用
 #到对应索引的搜索条件外的其他搜索条件的记录有多少条。
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';
 
 
 #对于单表查询来说，这个filtered列的值没什么意义，我们`更关注在连接查询
 #中驱动表对应的执行计划记录的filtered值`，它决定了被驱动表要执行的次数(即：rows * filtered)
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';
 
 
 #11. Extra:一些额外的信息
 #更准确的理解MySQL到底将如何执行给定的查询语句
 
 
 #当查询语句的没有`FROM`子句时将会提示该额外信息
 EXPLAIN SELECT 1;
 
 
 #查询语句的`WHERE`子句永远为`FALSE`时将会提示该额外信息
 EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
 
 
 #当我们使用全表扫描来执行对某个表的查询，并且该语句的`WHERE`
 #子句中有针对该表的搜索条件时，在`Extra`列中会提示上述额外信息。
 EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
 
 
 #当使用索引访问来执行对某个表的查询，并且该语句的`WHERE`子句中
 #有除了该索引包含的列之外的其他搜索条件时，在`Extra`列中也会提示上述额外信息。
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
 
 
 #当查询列表处有`MIN`或者`MAX`聚合函数，但是并没有符合`WHERE`子句中
 #的搜索条件的记录时，将会提示该额外信息
 EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';
 
 EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'NlPros'; #NlPros 是 s1表中key1字段真实存在的数据
 
 #select * from s1 limit 10;
 
 #当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以
 #使用覆盖索引的情况下，在`Extra`列将会提示该额外信息。比方说下边这个查询中只
 #需要用到`idx_key1`而不需要回表操作：
 EXPLAIN SELECT key1,id FROM s1 WHERE key1 = 'a';
 
 
 #有些搜索条件中虽然出现了索引列，但却不能使用到索引
 #看课件理解索引条件下推
 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
 
 
 #在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为
 #其分配一块名叫`join buffer`的内存块来加快查询速度，也就是我们所讲的`基于块的嵌套循环算法`
 #见课件说明
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
 
 
 #当我们使用左（外）连接时，如果`WHERE`子句中包含要求被驱动表的某个列等于`NULL`值的搜索条件，
 #而且那个列又是不允许存储`NULL`值的，那么在该表的执行计划的Extra列就会提示`Not exists`额外信息
 EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
 
 
 #如果执行计划的`Extra`列出现了`Using intersect(...)`提示，说明准备使用`Intersect`索引
 #合并的方式执行查询，括号中的`...`表示需要进行索引合并的索引名称；
 #如果出现了`Using union(...)`提示，说明准备使用`Union`索引合并的方式执行查询；
 #出现了`Using sort_union(...)`提示，说明准备使用`Sort-Union`索引合并的方式执行查询。
 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
 
 
 #当我们的`LIMIT`子句的参数为`0`时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息
 EXPLAIN SELECT * FROM s1 LIMIT 0;
 
 
 #有一些情况下对结果集中的记录进行排序是可以使用到索引的。
 #比如：
 EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
 
 
 #很多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）
 #进行排序，MySQL把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：`filesort`）。
 
 #如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的`Extra`列中显示`Using filesort`提示
 EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
 
 
 #在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们
 #在执行许多包含`DISTINCT`、`GROUP BY`、`UNION`等子句的查询过程中，如果不能有效利用索引来完成
 #查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行
 #计划的`Extra`列将会显示`Using temporary`提示
 EXPLAIN SELECT DISTINCT common_field FROM s1;
 
 #EXPLAIN SELECT DISTINCT key1 FROM s1;
 
 #同上。
 EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
 
 #执行计划中出现`Using temporary`并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，所以
 #我们`最好能使用索引来替代掉使用临时表`。比如：扫描指定的索引idx_key1即可
 EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;
 
#json格式的explain
EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 
WHERE s1.common_field = 'a';
 

EXPLAIN的进一步使用

EXPLAIN四种输出格式
传统格式 ，JSON格式 ，TREE格式 以及 可视化输出

传统格式

mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE
    -> s2.common_field IS NOT NULL;
+----+-------------+-------+------------+------+---------------+----------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref                | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+--------------------+------+----------+-------------+
|  1 | SIMPLE      | s2    | NULL       | ALL  | idx_key1      | NULL     | NULL    | NULL               | 9895 |    90.00 | Using where |
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | atguigudb1.s2.key1 |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+--------------------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

JSON格式

mysql> EXPLAIN FORMAT=JSON SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE
    -> s2.common_field IS NOT NULL\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "4130.67"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "s2",
          "access_type": "ALL",
          "possible_keys": [
            "idx_key1"
          ],
          "rows_examined_per_scan": 9895,
          "rows_produced_per_join": 8905,
          "filtered": "90.00",
          "cost_info": {
            "read_cost": "123.20",
            "eval_cost": "890.55",
            "prefix_cost": "1013.75",
            "data_read_per_join": "15M"
          },
          "used_columns": [
            "key1",
            "common_field"
          ],
          "attached_condition": "((`atguigudb1`.`s2`.`common_field` is not null) and (`atguigudb1`.`s2`.`key1` is not null))"
        }
      },
      {
        "table": {
          "table_name": "s1",
          "access_type": "ref",
          "possible_keys": [
            "idx_key1"
          ],
          "key": "idx_key1",
          "used_key_parts": [
            "key1"
          ],
          "key_length": "303",
          "ref": [
            "atguigudb1.s2.key1"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 8905,
          "filtered": "100.00",
          "using_index": true,
          "cost_info": {
            "read_cost": "2226.37",
            "eval_cost": "890.55",
            "prefix_cost": "4130.67",
            "data_read_per_join": "15M"
          },
          "used_columns": [
            "key1"
          ]
        }
      }
    ]
  }
}
1 row in set, 1 warning (0.00 sec)

TREE格式

mysql> EXPLAIN FORMAT=tree SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE
    -> s1.common_field = 'a'\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join  (cost=1360.08 rows=990)
    -> Filter: ((s1.common_field = 'a') and (s1.key1 is not null))  (cost=1013.75 rows=990)
        -> Table scan on s1  (cost=1013.75 rows=9895)
    -> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index condition: (cast(s1.key1 as double) = cast(s2.key2 as double))  (cost=0.25 rows=1)

1 row in set, 1 warning (0.00 sec)

可视化输出
需要安装 workbench ,然后点击 explain 按钮, 不需要在执行的 SQL 前面添加 EXPLAIN

 EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE
s2.common_field IS NOT NULL;

分析优化器执行计划：trace

SET optimizer_trace="enabled=on",end_markers_in_json=on;

set optimizer_trace_max_mem_size=1000000;

select * from student where id < 10;

mysql> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************        
                            QUERY: select * from student where id < 10
                            TRACE: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `student`.`id` AS `id`,`student`.`stuno` AS `stuno`,`student`.`name` AS `name`,`student`.`age` AS `age`,`student`.`classId` AS `classId` from `student` where (`student`.`id` < 10)"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "(`student`.`id` < 10)",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`student`.`id` < 10)"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`student`.`id` < 10)"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`student`.`id` < 10)"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`student`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`student`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 3704946,
                    "cost": 376357
                  } /* table_scan */,
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": true,
                      "key_parts": [
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "skip_scan_range": {
                    "potential_skip_scan_indexes": [
                      {
                        "index": "PRIMARY",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      }
                    ] /* potential_skip_scan_indexes */
                  } /* skip_scan_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "PRIMARY",
                        "ranges": [
                          "id < 10"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": true,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 9,
                        "cost": 1.47851,
                        "chosen": true
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "PRIMARY",
                      "rows": 9,
                      "ranges": [
                        "id < 10"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 9,
                    "cost_for_plan": 1.47851,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`student`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 9,
                      "access_type": "range",
                      "range_details": {
                        "used_index": "PRIMARY"
                      } /* range_details */,
                      "resulting_rows": 9,
                      "cost": 2.37851,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 9,
                "cost_for_plan": 2.37851,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`student`.`id` < 10)",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`student`",
                  "attached": "(`student`.`id` < 10)"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`student`",
                "original_table_condition": "(`student`.`id` < 10)",
                "final_table_condition   ": "(`student`.`id` < 10)"
              }
            ] /* finalizing_table_conditions */
          },
          {
            "refine_plan": [
              {
                "table": "`student`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

MySQL监控分析视图-sys schema

索引情况

#1. 查询冗余索引
select * from sys.schema_redundant_indexes;
#2. 查询未使用过的索引
select * from sys.schema_unused_indexes;
#3. 查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schema='dbname' ;

表相关

# 1. 查询表的访问量
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from
sys.schema_table_statistics group by table_schema,table_name order by io desc;
# 2. 查询占用bufferpool较多的表
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10;
# 3. 查看表的全表扫描情况
select * from sys.statements_with_full_table_scans where db='dbname';

语句相关

#1. 监控SQL执行的频率
select db,exec_count,query from sys.statement_analysis
order by exec_count desc;
#2. 监控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1;
#3. 监控使用了临时表或者磁盘临时表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by (tmp_tables+tmp_disk_tables) desc;

IO相关

#1. 查看消耗磁盘IO的文件
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

Innodb 相关

#1. 行锁阻塞情况
select * from sys.innodb_lock_waits;

索引优化与查询优化

## 06-索引优化与查询优化

# 1.数据准备
CREATE DATABASE atguigudb2;

USE atguigudb2;

# 建表

CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


SET GLOBAL log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。


#随机产生字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;
#假如要删除
#drop function rand_string;


#用于随机产生多少到多少的编号
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;
#假如要删除
#drop function rand_num;



#创建往stu表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_stu( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, NAME ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
#假如要删除
#drop PROCEDURE insert_stu;


#执行存储过程，往class表添加随机数据
DELIMITER //
CREATE PROCEDURE `insert_class`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO class ( classname,address,monitor ) VALUES
(rand_string(8),rand_string(10),rand_num(1,100000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
#假如要删除
#drop PROCEDURE insert_class;


#执行存储过程，往class表添加1万条数据
CALL insert_class(10000);

#执行存储过程，往stu表添加50万条数据
CALL insert_stu(100000,500000);



DELIMITER //
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name FROM
information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND
seq_in_index=1 AND index_name <>'PRIMARY' ;
#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=2 ;
#若没有数据返回,程序继续,并将变量done设为2
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @str = CONCAT("drop index " , _index , " on " , tablename );
PREPARE sql_str FROM @str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END //
DELIMITER ;

CALL proc_drop_index("dbname","tablename");

# 验证数据mock完毕
SELECT COUNT(*) FROM class;

SELECT COUNT(*) FROM student;

# 2. 索引失效案例
# (1) 全值匹配我最爱

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME='abcd';

# 第一次查询 0.132s
SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME='abcd';

# 添加单列索引
CREATE INDEX idx_age ON student(age);

# 第二次查询 0.045s
SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME='abcd';

# 添加联合索引
CREATE INDEX idx_age_classid ON student(age, classId);

# 第三次查询 0.016s
SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME='abcd';

# 添加联合索引
CREATE INDEX idx_age_classid_name ON student(age, classId, NAME);

# 第四次查询 0.015s
SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME='abcd';



SHOW INDEX FROM student;

# (2) 最佳左前缀法则
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid = 1 AND student.name = 'abcd';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid=4 
AND student.age=30 AND student.name = 'abcd';

DROP INDEX idx_age ON student;

DROP INDEX idx_age_classid ON student;

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.name = 'abcd';


# (3) 主键插入顺序

# (4) 计算、函数、类型转换(自动或手动)导致索引失效

# 创建一个关于 name 的索引
CREATE INDEX idx_name ON student(NAME);

SHOW INDEX FROM student;

# 此语句比下一条要好! (能够使用上索引)
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';


# 创建索引
CREATE INDEX idx_sno ON student(stuno);
SHOW INDEX FROM student;

# 没有使用上索引, 因为发生了计算
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
# 使用上了索引
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;

# 使用了函数, 索引失效
EXPLAIN SELECT id, stuno, NAME FROM student WHERE SUBSTRING(NAME, 1,3)='abc';




# (5) 类型转化导致索引失效

# 未使用上索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME=123;

# name 是字符串类型, 发生了隐式转换, 调用了函数, 所以索引失效了
# 使用上索引了
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME='123';


# (6) 范围条件右边的列索引失效
# 查看索引
SHOW INDEX FROM student;

# 调用存储过程删除索引
CALL proc_drop_index('atguigudb2', 'student');

# 创建索引
CREATE INDEX idx_age_classId_name ON student(age, classId, NAME);

# 使用上了 idx_age_classId_name 索引, 但是 key_len 是 10, 说明字节数是 10 
# 字节数是 10 的原因, 说明没有使用这个联合索引的三个索引, 只使用了其中的两个 age name
# age 是 int 类型 非空 , int = 4字节 , null 需要 1 字节 , 加起来就是 5 字节
# classId 是 int 类型 非空 , int = 4字节 , null 需要 1 字节 , 加起来就是 5 字节
# classId + age = 10 字节
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc';

# 效果跟上一条一样
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;

# 创建索引, 但是 name 在 classId 前面
CREATE INDEX idx_age_name_cid ON student(age, NAME, classId);

# 再次查询, 这时使用的索引是 idx_age_name_cid , 此时的 key_len = 73
# 因为 varchar 需要 2 个字节来存储值的长度
# name = 20(字符) * 3(innodb) + 1(存储非空) + 2(存储长度) = 63(字节)
# age + classId + name = 73(字节)
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc';


### 总结: 应用开发范围查询, 例如: 金额查询, 日期查询往往都是范围查询.
### 应将查询条件放置 where 语句最后
### 创建的联合索引中, 务必把范围涉及到的字段写到最后



# (7) 不等于(!= 或者 <>)索引失效
CREATE INDEX idx_name ON student(NAME);

SHOW INDEX FROM student;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc';

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc';


# (8) is null 可以使用索引, is not null 无法使用索引
# 使用上了索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;

# 没有使用上索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

### 结论: 最好在设计数据表的时候就将 字段设置为 NOT NULL 约束, 比如你可以将 INT 类型的
### 字段, 默认值设置为 0 , 将字符类型的默认值设置为空字符串('')
### 拓展: 同理, 在查询中使用 not like 也无法使用索引, 导致全表扫描


# (9) like以通配符%开头索引失效
# 使用上了索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%';

# 没有使用上索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';


# (10) OR 前后存在非索引的列, 索引失效

SHOW INDEX FROM student;

# 调用存储过程, 删除索引
CALL proc_drop_index('atguigudb2', 'student');

# 创建索引
CREATE INDEX idx_age ON student(age);

# 没有使用上索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

CREATE INDEX idx_cid ON student(classid);

# 使用上了索引 idx_age,idx_cid
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;


# (11) 数据库和表的字符集统一使用utf8mb4

### 统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不
### 同的 字符集 进行比较前需要进行 转换 会造成索引失效。

练习及一般性建议

关联查询优化

# 3. 关联查询优化

# 情况1: 左外连接
# 分类
CREATE TABLE IF NOT EXISTS `type` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
#图书
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);

#向分类表中添加20条记录
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));


#向图书表中添加20条记录
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));


EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

# 添加索引
CREATE INDEX Y ON book(card);

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

CREATE INDEX X ON `type`(card);

## 左外连接中, 右边的表是不一定是被驱动表, 左边的表不一定是驱动表
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

DROP INDEX Y ON book;

##### 左外连接情况, 在两个字段中, 只存在一个索引, 有索引的在上面
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

# 情况2：内连接

DROP INDEX X ON `type`;

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

#添加索引
CREATE INDEX Y ON book(card);

EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

CREATE INDEX X ON `type`(card);

##### 内连接情况, 在两个字段中, 每个都有一个索引, 由优化器决定谁在上面
##### 此时 type 在上面, type 是驱动表, book 是被驱动表
# 结论：对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

##### 向 驱动表 添加数据, 这里的驱动表是 type , 所以给 type 添加数据
##### 如果驱动表是 book , 那就给 book 添加数据
# 向分类表中添加20条记录
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO TYPE(card) VALUES(FLOOR(1 + (RAND() * 20)));

# 结论: 对于内连接来说, 在两个表的连接条件都存在索引的情况下, 
# 会选择小表作为驱动表, "小表驱动大表"
##### 此时 TYPE 的数据更多, 有 40 条, type 是大表, type 是被驱动表
##### book 是驱动表
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` INNER JOIN book ON type.card = book.card;

SELECT COUNT(*) FROM book;
SELECT COUNT(*) FROM TYPE;







# JOIN的底层原理

CREATE TABLE a(f1 INT, f2 INT, INDEX(f1))ENGINE=INNODB;

CREATE TABLE b(f1 INT, f2 INT)ENGINE=INNODB;


INSERT INTO a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);

INSERT INTO b VALUES(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

##### 查询优化器将这个左外连接转变成了内连接, 所以 a 变成了 被驱动表
##### 写在 JOIN 左边的不一定是驱动表, 可能会被查询优化器优化
#测试1
EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) WHERE (a.f2=b.f2);

#测试2
EXPLAIN SELECT * FROM a LEFT JOIN b ON(a.f1=b.f1) AND (a.f2=b.f2);

#测试3
EXPLAIN SELECT * FROM a JOIN b ON(a.f1=b.f1) WHERE (a.f2=b.f2);

SHOW VARIABLES LIKE '%optimizer_switch%';

SHOW VARIABLES LIKE '%join_buffer%';

子查询优化

# 4. 子查询的优化
SHOW INDEX FROM class;
DESC class;
#创建班级表中班长的索引
CREATE INDEX idx_monitor ON class(monitor);

#查询班长的信息
EXPLAIN SELECT * FROM student stu1
WHERE stu1.`stuno` IN (
SELECT monitor
FROM class c
WHERE monitor IS NOT NULL
);

EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c 
ON stu1.`stuno` = c.`monitor`
WHERE c.`monitor` IS NOT NULL;

#查询不为班长的学生信息
EXPLAIN SELECT SQL_NO_CACHE a.* 
FROM student a 
WHERE  a.stuno  NOT  IN (
			SELECT monitor FROM class b 
			WHERE monitor IS NOT NULL) 


EXPLAIN SELECT SQL_NO_CACHE a.*
FROM  student a LEFT OUTER JOIN class b 
ON a.stuno =b.monitor
WHERE b.monitor IS NULL;

排序优化

#5. 排序优化
#删除student和class表中的非主键索引
CALL proc_drop_index('atguigudb2','student');
CALL proc_drop_index('atguigudb2','class');

SHOW INDEX FROM student;
SHOW INDEX FROM class;

#过程一：
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid; 

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10; 

#过程二：order by时不limit，索引失效
#创建索引  
CREATE  INDEX idx_age_classid_name ON student (age,classid,NAME);

#不限制,索引失效
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid; 

##### 索引覆盖, 不需要回表
# EXPLAIN  SELECT SQL_NO_CACHE age,classid,name,id FROM student ORDER BY age,classid; 

#增加limit过滤条件，使用上索引了。
EXPLAIN  SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;  

#过程三：order by时顺序错误，索引失效

#创建索引age,classid,stuno
CREATE  INDEX idx_age_classid_stuno ON student (age,classid,stuno); 

#以下哪些索引失效?
EXPLAIN  SELECT * FROM student ORDER BY classid LIMIT 10;

EXPLAIN  SELECT * FROM student ORDER BY classid,NAME LIMIT 10;  

EXPLAIN  SELECT * FROM student ORDER BY age,classid,stuno LIMIT 10; 

EXPLAIN  SELECT * FROM student ORDER BY age,classid LIMIT 10;

EXPLAIN  SELECT * FROM student ORDER BY age LIMIT 10;

#过程四：order by时规则不一致, 索引失效 （顺序错，不索引；方向反，不索引）

EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10;

EXPLAIN  SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10;

EXPLAIN  SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10; 

##### 两个字段都是降序的, 都是反的, 使用上了 idx_age_classid_stuno 索引, Extra Backward index scan
EXPLAIN  SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10;

#过程五：无过滤，不索引

EXPLAIN  SELECT * FROM student WHERE age=45 ORDER BY classid;

EXPLAIN  SELECT * FROM student WHERE  age=45 ORDER BY classid,NAME; 

EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age;

##### 先进行 order by 匹配索引进行排序, 排序完成之后再过滤 classid = 45, 
##### 最后分页取前 10 条记录
EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age LIMIT 10;

##### 创建一个 cid 索引, 可以直接用上
CREATE INDEX idx_cid ON student(classid);
EXPLAIN  SELECT * FROM student WHERE  classid=45 ORDER BY age;


#实战：测试filesort和index排序
CALL proc_drop_index('atguigudb2','student');

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

#方案一: 为了去掉filesort我们可以把索引建成

CREATE INDEX idx_age_name ON student(age,NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

#方案二：
/*
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `stuno` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `classId` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age_name` (`age`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=500001 DEFAULT CHARSET=utf8mb3
*/
CREATE INDEX idx_age_stuno_name ON student(age,stuno,NAME);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

DROP INDEX idx_age_stuno_name ON student;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

CREATE INDEX idx_age_stuno ON student(age,stuno);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME ;

覆盖索引

#6. 覆盖索引
#删除之前的索引
#举例1：
DROP INDEX idx_age_stuno ON student;

CREATE INDEX idx_age_name ON student (age,NAME);

EXPLAIN SELECT * FROM student WHERE age <> 20;


EXPLAIN SELECT age,NAME FROM student WHERE age <> 20;

#举例2：
EXPLAIN SELECT * FROM student WHERE NAME LIKE '%abc';

EXPLAIN SELECT id,age FROM student WHERE NAME LIKE '%abc';

###
SELECT CRC32('hello')
FROM DUAL;

索引条件下推

#7. 索引条件下推(ICP)

#举例1：
USE atguigudb1;

##### AND 前后的过滤条件都是相同的一个字段, 在 key1 字段找到大于 'z' 的叶子节点之后, 直接过滤 '%a' 这个过程是没有进行回表操作的
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

#举例2：
CREATE TABLE `people` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `zipcode` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
  `firstname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
  `lastname` VARCHAR(20) COLLATE utf8_bin DEFAULT NULL,
  `address` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `zip_last_first` (`zipcode`,`lastname`,`firstname`)
) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin;


INSERT INTO `people` VALUES 
('1', '000001', '三', '张', '北京市'), 
('2', '000002', '四', '李', '南京市'), 
('3', '000003', '五', '王', '上海市'), 
('4', '000001', '六', '赵', '天津市');

##### 使用了联合索引, 但是只使用了第一个字段, zipcode
EXPLAIN SELECT * FROM people
WHERE zipcode='000001'
AND lastname LIKE '%张%'
AND address LIKE '%北京市%';

##### 使用了联合索引, 三个字段全部都用上了, 索引下推
EXPLAIN SELECT * FROM people
WHERE zipcode='000001'
AND lastname LIKE '张%'
AND firstname LIKE '三%';


SET optimizer_switch = 'index_condition_pushdown=on';

#创建存储过程，向people表中添加1000000条数据，测试ICP开启和关闭状态下的性能
DELIMITER //
CREATE PROCEDURE  insert_people( max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO people ( zipcode,firstname,lastname,address ) VALUES ('000001', '六', '赵', '天津市');  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT; 
END //

DELIMITER ;

CALL insert_people(1000000);

##### 查看 people 表中的数据量
SELECT COUNT(*) FROM people;

SET profiling = 1;


##### 此时是使用了索引下推 Using index condition
mysql> SELECT * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%'; # 0.221s
+----+---------+-----------+----------+-----------+
| id | zipcode | firstname | lastname | address   |
+----+---------+-----------+----------+-----------+
|  1 | 000001  | 三        | 张       | 北京市    |
+----+---------+-----------+----------+-----------+
1 ROW IN SET (0.22 sec)

##### 不使用索引下推 
mysql> SELECT /*+ no_icp (people) */ * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%'; # 4.166s
+----+---------+-----------+----------+-----------+
| id | zipcode | firstname | lastname | address   |
+----+---------+-----------+----------+-----------+
|  1 | 000001  | 三        | 张       | 北京市    |
+----+---------+-----------+----------+-----------+
1 ROW IN SET (4.92 sec)

mysql> SHOW PROFILES;
+----------+------------+----------------------------------------------------------------------------------------------+
| Query_ID | Duration   | QUERY                                                                                        |
+----------+------------+----------------------------------------------------------------------------------------------+
|        1 | 0.00009650 | SELECT * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%'                         |
|        2 | 0.00019850 | SELECT DATABASE()                                                                            |
|        3 | 0.21733375 | SELECT * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%'                         |
|        4 | 4.92306350 | SELECT /*+ no_icp (people) */ * FROM people WHERE zipcode='000001' AND lastname LIKE '%张%'  |
+----------+------------+----------------------------------------------------------------------------------------------+
4 ROWS IN SET, 1 warning (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 3;
+--------------------------------+----------+
| STATUS                         | Duration |
+--------------------------------+----------+
| STARTING                       | 0.000085 |
| Executing hook ON TRANSACTION  | 0.000006 |
| STARTING                       | 0.000010 |
| checking permissions           | 0.000008 |
| Opening TABLES                 | 0.000167 |
| init                           | 0.000008 |
| System LOCK                    | 0.000010 |
| optimizing                     | 0.000015 |
| statistics                     | 0.000092 |
| preparing                      | 0.000021 |
| executing                      | 0.216816 |
| END                            | 0.000020 |
| QUERY END                      | 0.000005 |
| waiting FOR HANDLER COMMIT     | 0.000010 |
| closing TABLES                 | 0.000012 |
| freeing items                  | 0.000028 |
| cleaning up                    | 0.000022 |
+--------------------------------+----------+
17 ROWS IN SET, 1 warning (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 4;
+--------------------------------+----------+
| STATUS                         | Duration |
+--------------------------------+----------+
| STARTING                       | 0.000096 |
| Executing hook ON TRANSACTION  | 0.000005 |
| STARTING                       | 0.000009 |
| checking permissions           | 0.000006 |
| Opening TABLES                 | 0.000047 |
| init                           | 0.000006 |
| System LOCK                    | 0.000010 |
| optimizing                     | 0.000013 |
| statistics                     | 0.000089 |
| preparing                      | 0.000019 |
| executing                      | 4.922620 |
| END                            | 0.000020 |
| QUERY END                      | 0.000005 |
| waiting FOR HANDLER COMMIT     | 0.000011 |
| closing TABLES                 | 0.000012 |
| freeing items                  | 0.000030 |
| logging slow QUERY             | 0.000044 |
| cleaning up                    | 0.000024 |
+--------------------------------+----------+
18 ROWS IN SET, 1 warning (0.00 sec)

其他查询优化策略

#### 其他查询优化策略, 主键采用升序UUID

SELECT UUID() FROM DUAL;

SET @uuid = UUID();


SELECT @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);

/*
mysql> SELECT UUID() FROM DUAL;
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| 7f4c28c9-52a2-11ee-bdd0-00163e033c74 |
+--------------------------------------+
1 row in set (0.01 sec)

mysql> SET @uuid = UUID();
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);
+--------------------------------------+----------------------------------------+--------------------------------------------------+
| @uuid                                | uuid_to_bin(@uuid)                     | uuid_to_bin(@uuid,TRUE)                          |
+--------------------------------------+----------------------------------------+--------------------------------------------------+
| 81f72a11-52a2-11ee-bdd0-00163e033c74 | 0x81F72A1152A211EEBDD000163E033C74     | 0x11EE52A281F72A11BDD000163E033C74               |
+--------------------------------------+----------------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)
*/

第一范式

第一范式应该满足原子性, 不可拆分

在这如下表中, 一个人可能有两个手机号, 两个手机号存储在了一个字段中, 这是可以拆分的

属性的原子性是主观性的

第二范式

在第一范式的基础上, 所有非主键索引完全依赖主键索引, 不能只依赖一部分

这里 姓名和年龄依赖了球员编号, 比赛时间``比赛场地依赖了比赛编号
得分依赖了球员编号和比赛编号
只有成绩是完全依赖的关系, 所以需要分成 球员表 比赛表 球员比赛关系表

球员归球员表, 比赛归比赛表

第三范式

范式总结

反范式化

表设计规范

10.1 关于库

1. 【强制】库的名称必须控制在32个字符以内，只能使用英文字母、数字和下划线，建议以英文字
   母开头。
2. 【强制】库名中英文 一律小写 ，不同单词采用 下划线 分割。须见名知意。
3. 【强制】库的名称格式：业务系统名称_子系统名。
4. 【强制】库名禁止使用关键字（如type,order等）。
5. 【强制】创建数据库时必须 显式指定字符集 ，并且字符集只能是utf8或者utf8mb4。
   创建数据库SQL举例：CREATE DATABASE crm_fund DEFAULT CHARACTER SET ‘utf8’ ;
6. 【建议】对于程序连接数据库账号，遵循 权限最小原则
   使用数据库账号只能在一个DB下使用，不准跨库。程序使用的账号 原则上不准有drop权限 。
7. 【建议】临时库以 tmp_ 为前缀，并以日期为后缀；
   备份库以 bak_ 为前缀，并以日期为后缀。
   10.2 关于表、列
8. 【强制】表和列的名称必须控制在32个字符以内，表名只能使用英文字母、数字和下划线，建议
   以 英文字母开头 。
9. 【强制】 表名、列名一律小写 ，不同单词采用下划线分割。须见名知意。
10. 【强制】表名要求有模块名强相关，同一模块的表名尽量使用 统一前缀 。比如：crm_fund_item
11. 【强制】创建表时必须 显式指定字符集 为utf8或utf8mb4。
12. 【强制】表名、列名禁止使用关键字（如type,order等）。
13. 【强制】创建表时必须 显式指定表存储引擎 类型。如无特殊需求，一律为InnoDB。
14. 【强制】建表必须有comment。
15. 【强制】字段命名应尽可能使用表达实际含义的英文单词或 缩写 。如：公司 ID，不要使用
    corporation_id, 而用corp_id 即可。
16. 【强制】布尔值类型的字段命名为 is_描述 。如member表上表示是否为enabled的会员的字段命
    名为 is_enabled。
17. 【强制】禁止在数据库中存储图片、文件等大的二进制数据
    通常文件很大，短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随
    机IO操作，文件很大时，IO操作很耗时。通常存储于文件服务器，数据库只存储文件地址信息。
18. 【建议】建表时关于主键： 表必须有主键 (1)强制要求主键为id，类型为int或bigint，且为
    auto_increment 建议使用unsigned无符号型。 (2)标识表里每一行主体的字段不要设为主键，建议
    设为其他字段如user_id，order_id等，并建立unique key索引。因为如果设为主键且主键值为随机
    插入，则会导致innodb内部页分裂和大量随机I/O，性能下降。
19. 【建议】核心表（如用户表）必须有行数据的 创建时间字段 （create_time）和 最后更新时间字段
    （update_time），便于查问题。
20. 【建议】表中所有字段尽量都是 NOT NULL 属性，业务可以根据需要定义 DEFAULT值 。 因为使用
    NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问
    题。
21. 【建议】所有存储相同数据的 列名和列类型必须一致 （一般作为关联列，如果查询时关联列类型
    不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）。
22. 【建议】中间表（或临时表）用于保留中间结果集，名称以 tmp_ 开头。
    备份表用于备份或抓取源表快照，名称以 bak_ 开头。中间表和备份表定期清理。
23. 【示范】一个较为规范的建表语句：
    CREATE TABLE user_info (
    id int unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增主键’,
    user_id bigint(11) NOT NULL COMMENT ‘用户id’,
    username varchar(45) NOT NULL COMMENT ‘真实姓名’,
    email varchar(30) NOT NULL COMMENT ‘用户邮箱’,
    nickname varchar(45) NOT NULL COMMENT ‘昵称’,
    birthday date NOT NULL COMMENT ‘生日’,
    sex tinyint(4) DEFAULT ‘0’ COMMENT ‘性别’,
    short_introduce varchar(150) DEFAULT NULL COMMENT ‘一句话介绍自己，最多50个汉字’,
    user_resume varchar(300) NOT NULL COMMENT ‘用户提交的简历存放地址’,
    user_register_ip int NOT NULL COMMENT ‘用户注册时的源ip’,
    create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
    update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
    CURRENT_TIMESTAMP COMMENT ‘修改时间’,
    user_review_status tinyint NOT NULL COMMENT ‘用户资料审核状态，1为通过，2为审核中，3为未
    通过，4为还未提交审核’,
24. 【建议】创建表时，可以使用可视化工具。这样可以确保表、字段相关的约定都能设置上。
    实际上，我们通常很少自己写 DDL 语句，可以使用一些可视化工具来创建和操作数据库和数据表。
    可视化工具除了方便，还能直接帮我们将数据库的结构定义转化成 SQL 语言，方便数据库和数据表结构
    的导出和导入。
    10.3 关于索引
25. 【强制】InnoDB表必须主键为id int/bigint auto_increment，且主键值 禁止被更新 。
26. 【强制】InnoDB和MyISAM存储引擎表，索引类型必须为 BTREE 。
27. 【建议】主键的名称以 pk_ 开头，唯一键以 uni_ 或 uk_ 开头，普通索引以 idx_ 开头，一律
    使用小写格式，以字段的名称或缩写作为后缀。
28. 【建议】多单词组成的columnname，取前几个单词首字母，加末单词组成column_name。如:
    sample 表 member_id 上的索引：idx_sample_mid。
29. 【建议】单个表上的索引个数 不能超过6个 。
30. 【建议】在建立索引时，多考虑建立 联合索引 ，并把区分度最高的字段放在最前面。
31. 【建议】在多表 JOIN 的SQL里，保证被驱动表的连接列上有索引，这样JOIN 执行效率最高。
32. 【建议】建表或加索引时，保证表里互相不存在 冗余索引 。 比如：如果表里已经存在key(a,b)，
    则key(a)为冗余索引，需要删除。
    10.4 SQL编写
33. 【强制】程序端SELECT语句必须指定具体字段名称，禁止写成 *。
34. 【建议】程序端insert语句指定具体字段名称，不要写成INSERT INTO t1 VALUES(…)。
35. 【建议】除静态表或小表（100行以内），DML语句必须有WHERE条件，且使用索引查找。
36. 【建议】INSERT INTO…VALUES(XX),(XX),(XX).. 这里XX的值不要超过5000个。 值过多虽然上线很
    快，但会引起主从同步延迟。
37. 【建议】SELECT语句不要使用UNION，推荐使用UNION ALL，并且UNION子句个数限制在5个以
    内。
38. 【建议】线上环境，多表 JOIN 不要超过5个表。
39. 【建议】减少使用ORDER BY，和业务沟通能不排序就不排序，或将排序放到程序端去做。ORDER
    BY、GROUP BY、DISTINCT 这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
40. 【建议】包含了ORDER BY、GROUP BY、DISTINCT 这些查询的语句，WHERE 条件过滤出来的结果
    集请保持在1000行以内，否则SQL会很慢。
41. 【建议】对单表的多次alter操作必须合并为一次
    对于超过100W行的大表进行alter table，必须经过DBA审核，并在业务低峰期执行，多个alter需整
    合在一起。 因为alter table会产生 表锁 ，期间阻塞对于该表的所有写入，对于业务可能会产生极
    大影响。
42. 【建议】批量操作数据时，需要控制事务处理间隔时间，进行必要的sleep。
43. 【建议】事务里包含SQL不超过5个。
    因为过长的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等问题。
44. 【建议】事务里更新语句尽量基于主键或UNIQUE KEY，如UPDATE… WHERE id=XX;
    PRIMARY KEY (id),
    UNIQUE KEY uniq_user_id (user_id),
    KEY idx_username(username),
    KEY idx_create_time_status(create_time,user_review_status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’网站用户基本信息’
    否则会产生间隙锁，内部扩大锁定范围，导致系统性能下降，产生死锁。

PowerDesigner

官网地址: https://www.powerdesigner.biz/

双击exe文件运行

注意点: 在建立关联表中, 两个表的字段必须要区分,
比如 student(student_gender) 和 teacher(teacher_gender) 这两张表的两个字段不能都是gender, 在概念模型建立了关联表 转 物理模型时, 会错误的将这两个相同的字段认为是外键关联

删除实体

设置实体的属性

概念模型转换成物理模型

物理模型转换成概念模型

只有物理数据模型可以设置主键自动递增

将物理模型生成sql脚本

分析表、检查表与优化表

使用 mysqlcheck 工具进行表的优化分析

[root@iZf8z0puua6p4won7xep5rZ ~]# mysqlcheck -o atguigudb3 user1 -uroot -p
Enter password: 
atguigudb3.user1
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
[root@iZf8z0puua6p4won7xep5rZ ~]#

数据表的设计规范

#07-数据表的设计规范

#反范式化的举例：

CREATE DATABASE atguigudb3;

USE atguigudb3;

#学生表
CREATE TABLE student(
stu_id INT PRIMARY KEY AUTO_INCREMENT,
stu_name VARCHAR(25),
create_time DATETIME
);

#课程评论表
CREATE TABLE class_comment(
comment_id INT PRIMARY KEY AUTO_INCREMENT,
class_id INT,
comment_text VARCHAR(35),
comment_time DATETIME,
stu_id INT
);

###创建向学生表中添加数据的存储过程
DELIMITER //

CREATE PROCEDURE batch_insert_student(IN START INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2017-01-01 00:00:00');
DECLARE date_temp DATETIME;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = DATE_ADD(date_temp, INTERVAL RAND()*60 SECOND);
INSERT INTO student(stu_id, stu_name, create_time)
VALUES((START+i), CONCAT('stu_',i), date_temp);
UNTIL i = max_num
END REPEAT;
COMMIT;
END //

DELIMITER ;

#调用存储过程，学生id从10001开始，添加1000000数据
CALL batch_insert_student(10000,1000000);

####创建向课程评论表中添加数据的存储过程
DELIMITER //

CREATE PROCEDURE batch_insert_class_comments(IN START INT(10), IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE date_start DATETIME DEFAULT ('2018-01-01 00:00:00');
DECLARE date_temp DATETIME;
DECLARE comment_text VARCHAR(25);
DECLARE stu_id INT;
SET date_temp = date_start;
SET autocommit=0;
REPEAT
SET i=i+1;
SET date_temp = DATE_ADD(date_temp, INTERVAL RAND()*60 SECOND);
SET comment_text = SUBSTR(MD5(RAND()),1, 20);
SET stu_id = FLOOR(RAND()*1000000);
INSERT INTO class_comment(comment_id, class_id, comment_text, comment_time, stu_id)
VALUES((START+i), 10001, comment_text, date_temp, stu_id);
UNTIL i = max_num
END REPEAT;
COMMIT;
END //

DELIMITER ;

#添加数据的存储过程的调用，一共1000000条记录
CALL batch_insert_class_comments(10000,1000000);

#########
SELECT COUNT(*) FROM student;

SELECT COUNT(*) FROM class_comment;

###需求######
SELECT p.comment_text, p.comment_time, stu.stu_name 
FROM class_comment AS p LEFT JOIN student AS stu 
ON p.stu_id = stu.stu_id 
WHERE p.class_id = 10001 
ORDER BY p.comment_id DESC 
LIMIT 10000;


#####进行反范式化的设计######
#表的复制
CREATE TABLE class_comment1
AS
SELECT * FROM class_comment;

#添加主键，保证class_comment1 与class_comment的结构相同
ALTER TABLE class_comment1
ADD PRIMARY KEY (comment_id);

SHOW INDEX FROM class_comment1;

#向课程评论表中增加stu_name字段
ALTER TABLE class_comment1
ADD stu_name VARCHAR(25);

#给新添加的字段赋值
UPDATE class_comment1 c
SET stu_name = (
SELECT stu_name
FROM student s
WHERE c.stu_id = s.stu_id
);

#查询同样的需求
SELECT comment_text, comment_time, stu_name 
FROM class_comment1 
WHERE class_id = 10001 
ORDER BY comment_id DESC 
LIMIT 10000;

数据库的其他优化策略

#08-数据库的其他优化策略
CREATE TABLE `user1` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL,
  `age` INT DEFAULT NULL,
  `sex` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;

#######
SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER //
CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN   
DECLARE i INT DEFAULT 0;  
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))   ;
RETURN i;  
END //
DELIMITER ;

###
DELIMITER //
CREATE PROCEDURE  insert_user( max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO `user1` ( NAME,age,sex ) 
 VALUES ("atguigu",rand_num(1,20),"male");  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT; 
END //
DELIMITER;

##
CALL insert_user(1000);

SHOW INDEX FROM user1;

SELECT * FROM user1;

UPDATE user1 SET NAME = 'atguigu04' WHERE id = 4;

#分析表
ANALYZE TABLE user1;

#检查表
CHECK TABLE user1;

#优化表
CREATE TABLE t1(id INT,NAME VARCHAR(15)) ENGINE = MYISAM;

OPTIMIZE TABLE t1;


CREATE TABLE t2(id INT,NAME VARCHAR(15)) ENGINE = INNODB;

OPTIMIZE TABLE t2;

通用表空间

#### 全局通用表空间
CREATE TABLESPACE atguigu1 ADD DATAFILE 'atguigu1.ibd' file_block_size=16k;


CREATE TABLE test(id INT,NAME VARCHAR(10)) ENGINE=INNODB DEFAULT CHARSET utf8mb4 TABLESPACE atguigu1;

ALTER TABLE test TABLESPACE atguigu1;

DROP TABLESPACE atguigu1;

DROP TABLE test;
# 只有表空间中不存在表的时候, 才能够将表进行删除
DROP TABLESPACE atguigu1;

事务ACID的特性

原子性 一致性 隔离性 持久性

如何使用事务

# 09-事务的基础知识

#1.事务的完成过程
#步骤1:开启事务
#步骤2:一些列的DML操作
#...
#步骤3: 事务结束的状态: 提交的状态(COMMIT), 中止的状态(ROLLBACK)

#2.显式事务

#2.1 如何开启? 使用关键字: start transaction 或 begin

# start transaction 后面可以跟: read only / read write(默认) / with consistent snapshot 

#2.2 保存点(savepoint)

#3.隐式事务

#3.1 关键字: autocommit

SHOW VARIABLES LIKE 'autocommit'; # 默认是ON

UPDATE account SET balance = balance - 10 WHERE id = 1; # 此时这条DML操作是一个独立的事务

UPDATE account SET balance = balance + 10 WHERE id = 2; # 此时这条DML操作是一个独立的事务

#3.2 如何关闭自动提交?
# 方式1: 
SET autocommit = FALSE; 

UPDATE account SET balance = balance - 10 WHERE id = 1; 

UPDATE account SET balance = balance + 10 WHERE id = 2; 

COMMIT; #或rollback;

# 方式2: 我们在 autocommit 为 true 的情况下, 使用 start transaction 或 begin 开启事务, 那么 DML 操作就不会自动提交数据

START TRANSACTION;

UPDATE account SET balance = balance - 10 WHERE id = 1; 

UPDATE account SET balance = balance + 10 WHERE id = 2; 

COMMIT; #或 ROLLBACK;

#4. 案例分析
#SET autocommit = true;
#举例1:

USE atguigudb2;
# 情况1:
CREATE TABLE user3(NAME VARCHAR(15) PRIMARY KEY);

SELECT * FROM user3;

BEGIN;
INSERT INTO user3 VALUES('张三'); # 此时不会自动提交数据
COMMIT;

BEGIN; #开启一个新的事务
INSERT INTO user3 VALUES('张三'); # 此时不会自动提交数据
INSERT INTO user3 VALUES('张三'); # 受主键的影响, 不能添加成功
ROLLBACK;

SELECT * FROM user3;

#情况2：
TRUNCATE TABLE user3; # DDL 操作会自动提交数据, 不受 autocommit 变量的影响

SELECT * FROM user3;

BEGIN;
INSERT INTO user3 VALUES('张三'); # 此时不会自动提交数据
COMMIT;

INSERT INTO user3 VALUES('李四'); # 默认情况下(即 autocommit 为 true), DML 操作也会自动提交数据.
INSERT INTO user3 VALUES('李四'); # 事务的失败的状态

ROLLBACK;

SELECT * FROM user3;


# 情况3:
TRUNCATE TABLE user3;

SELECT * FROM user3;

SELECT @@completion_type;

SET @@completion_type=1;

BEGIN;
INSERT INTO user3 VALUES('张三'); 
COMMIT;

SELECT * FROM user3;

INSERT INTO user3 VALUES('李四'); 
INSERT INTO user3 VALUES('李四'); 

ROLLBACK;

SELECT * FROM user3;

# 举例2: 体会 INNODB 和 MyISAM
CREATE TABLE test1(i INT) ENGINE = INNODB;

CREATE TABLE test2(i INT) ENGINE = MYISAM;

# 针对于 InnoDB 表
BEGIN;
INSERT INTO test1 VALUES(1);
ROLLBACK;

SELECT * FROM test1;

# 针对于 MyISAM 表: 不支持事务
BEGIN;
INSERT INTO test2 VALUES(1);
ROLLBACK;

SELECT * FROM test2;


# 举例3: 体会savepoint

DROP TABLE IF EXISTS user3;
CREATE TABLE user3(NAME VARCHAR(15), balance DECIMAL(10, 2));

BEGIN;
INSERT INTO user3(NAME, balance) VALUES('张三', 1000);
COMMIT;

SELECT * FROM user3;

BEGIN;
UPDATE user3 SET balance = balance - 100 WHERE NAME = '张三';

UPDATE user3 SET balance = balance - 100 WHERE NAME = '张三';

SAVEPOINT s1; # 设置保存点

UPDATE user3 SET balance = balance + 1 WHERE NAME = '张三';

SELECT * FROM user3;

ROLLBACK TO s1; # 回滚到保存点

SELECT * FROM user3;

ROLLBACK; #回滚操作

SELECT * FROM user3;

事务的隔离级别

数据并发的四个问题: 脏写, 脏读, 不可重复读, 幻读

四种隔离级别

READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE

## 5.7.20 之前查看默认隔离级别
mysql> show variables like 'tx_isolation';
Empty set (0.01 sec)

## 5.7.20 之后查看默认隔离级别
mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

## 通过的查看隔离级别方式
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)


##### 设置事务的隔离级别
##### 通过下面的语句修改事务的隔离级别：
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别;
#其中，隔离级别格式：
> READ UNCOMMITTED
> READ COMMITTED
> REPEATABLE READ
> SERIALIZABLE

##### 或者

SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别'
#其中，隔离级别格式：
> READ-UNCOMMITTED
> READ-COMMITTED
> REPEATABLE-READ
> SERIALIZABLE

# 关于设置时使用GLOBAL或SESSION的影响：
# 使用 GLOBAL 关键字（在全局范围影响）：
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
#或
SET GLOBAL TRANSACTION_ISOLATION = 'SERIALIZABLE';
# 则：
# 当前已经存在的会话无效
# 只对执行完该语句之后产生的会话起作用
# 使用 SESSION 关键字（在会话范围影响）：
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
#或
SET SESSION TRANSACTION_ISOLATION = 'SERIALIZABLE';
# 则：
# 对当前会话的所有后续的事务有效
# 如果在事务之间执行，则对后续的事务有效
# 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务
# 小结：
# 数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度，隔离级别越高，数据一致性
# 就越好，但并发性越弱。

##### 设置隔离级别 `读已提交`
mysql> set global transaction_isolation = 'read-committed';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)    

##### 关闭当前会话, 开启一个新的会话
mysql> exit
Bye
[root@iZf8z0puua6p4won7xep5rZ ~]# mysql -p

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

##### 重启 mysqld 服务 , 恢复默认的隔离级别
[root@iZf8z0puua6p4won7xep5rZ ~]# systemctl restart mysqld

mysql> select @@transaction_isolation;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    8
Current database: *** NONE ***

+-------------------------+   
| @@transaction_isolation |   
+-------------------------+   
| REPEATABLE-READ         |   
+-------------------------+   
1 row in set (0.00 sec)     

##### 当前会话立马生效
mysql> set session transaction_isolation = 'read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)

演示 READ UNCOMMITTED 读未提交(脏读):

### 窗口一
mysql> use atguigudb3;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_atguigudb3 |
+----------------------+
| class_comment        |
| class_comment1       |
| student              |
| user1                |
+----------------------+
4 rows in set (0.01 sec)

mysql> CREATE TABLE account(      
    ->   id INT PRIMARY KEY AUTO_INCREMENT,
    ->   NAME VARCHAR(15),
    ->   balance DECIMAL(10, 2)
    -> );

mysql> INSERT INTO account values (1, '张三', '100'), (2, '李四', '0');
Query OK, 2 rows affected (0.01 sec)  
Records: 2  Duplicates: 0  Warnings: 0

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)    

### 窗口二
mysql> use atguigudb3;

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)   

mysql> set session transaction_isolation = 'read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)    


### 窗口一
mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |  100.00 |
|  2 | 李四   |    0.00 |
+----+--------+---------+
2 rows in set (0.00 sec) 

mysql> set session transaction_isolation = 'read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)    

mysql> begin;
mysql> update account set balance = balance - 100 where id = 1;
Query OK, 1 row affected (0.00 sec)     
Rows matched: 1  Changed: 1  Warnings: 0

## 窗口二
##### 事务2 读取到了 事务1(其他事务) 还没有提交的事务的数据, 这就是脏读
mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |    0.00 |
|  2 | 李四   |    0.00 |
+----+--------+---------+
2 rows in set (0.00 sec) 

## 窗口一
mysql> update account set balance = balance + 100 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |    0.00 |
|  2 | 李四   |  100.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

## 窗口二
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |    0.00 |
|  2 | 李四   |  100.00 |
+----+--------+---------+
2 rows in set (0.00 sec) 

##### 还钱操作, 此时这个操作会 `卡住` , 因为窗口一中的事务并还没有提交, 表中的数据被锁住了
mysql> update account set balance = balance - 100 where id = 2;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting 
transaction


## 窗口一
mysql> ROLLBACK;

## 窗口二
# update account set balance = balance - 100 where id = 2; # 如果超时了, 再次执行, 没有超时, 可以不用执行该语句
mysql> update account set balance = balance + 100 where id = 1;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |  200.00 |
|  2 | 李四   | -100.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

## 窗口一
mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |  200.00 |
|  2 | 李四   | -100.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

可以发现这个案例中, 张三李四的金额总和加起来都是100, 但是最后的结果是李四的金额变成了-100

关于脏读的概念: 事务2 读取到了 事务1(其他事务) 还没有提交的事务的数据, 这就是脏读

READ UNCOMMITTED 没有解决脏读的问题, READ COMMITTED 解决了脏读的问题, 但是 READ COMMITTED 存在不可重复读的问题

演示 READ COMMITTED 读已提交

### 窗口一
mysql> truncate table account;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO account values (1, '张三', '100'), (2, '李四', '0');
Query OK, 2 rows affected (0.00 sec)  
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |  100.00 |
|  2 | 李四   |    0.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.01 sec)

mysql> set session transaction_isolation = 'read-committed';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)


### 窗口二
mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |  100.00 |
|  2 | 李四   |    0.00 |
+----+--------+---------+
2 rows in set (0.00 sec) 

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+
1 row in set (0.00 sec)    

mysql> set session transaction_isolation = 'read-committed'; 
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |  100.00 |
|  2 | 李四   |    0.00 |
+----+--------+---------+
2 rows in set (0.00 sec) 

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

### 窗口一
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance = balance - 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   50.00 |
|  2 | 李四   |    0.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

### 窗口二
mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |  100.00 |
|  2 | 李四   |    0.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

### 窗口一
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   50.00 |
|  2 | 李四   |    0.00 |
+----+--------+---------+
2 rows in set (0.00 sec)


### 窗口二
##### 不可重复读, 读了两次相同的sql, 但是出现了不同的数据
##### 另外一个事务 commit , 当前的事务获取到了最新的数据
mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   50.00 |
|  2 | 李四   |    0.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

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

演示 REPEATABLE READ 可重复读

### 窗口一
mysql> set session transaction_isolation = 'repeatable-read';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)  


### 窗口二
mysql> set session transaction_isolation = 'repeatable-read';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)    

### 窗口一
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   50.00 |
|  2 | 李四   |    0.00 |
+----+--------+---------+
2 rows in set (0.00 sec)


### 窗口二
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   50.00 |
|  2 | 李四   |    0.00 |
+----+--------+---------+
2 rows in set (0.00 sec)


### 窗口一
mysql> update account set balance = balance - 10 where id = 1;   
Query OK, 1 row affected (0.00 sec)     
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   40.00 |
|  2 | 李四   |    0.00 |
+----+--------+---------+
2 rows in set (0.00 sec) 

### 窗口二
##### 避免了脏读
mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   50.00 |
|  2 | 李四   |    0.00 |
+----+--------+---------+
2 rows in set (0.00 sec) 


### 窗口一
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   40.00 |
|  2 | 李四   |    0.00 |
+----+--------+---------+
2 rows in set (0.00 sec)


### 窗口二
mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   50.00 |
|  2 | 李四   |    0.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

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

mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   40.00 |
|  2 | 李四   |    0.00 |
+----+--------+---------+
2 rows in set (0.00 sec)

演示 REPEATABLE READ 幻读

### 窗口一
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)    


### 窗口二
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)    

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

mysql> select count(*) from account where id = 3;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)


### 窗口一
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into account(id, name, balance) values(3, '王五', 100);
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   40.00 |
|  2 | 李四   |    0.00 |
|  3 | 王五   |  100.00 |
+----+--------+---------+
3 rows in set (0.00 sec)

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


### 窗口二
mysql> select count(*) from account where id = 3;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> insert into account(id, name, balance) values(3, '王五', 1000);
ERROR 1062 (23000): Duplicate entry '3' for key 'account.PRIMARY'

##### 表中已经有id = 3的记录
##### 只是通过select没有查询到对应的记录
##### 但是通过insert插入的方式是可以知道表中已经存在id = 3的记录

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

MySQL事务日志

1.redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。
2.undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

redo 日志的好处, 特点

### redo log 分成两个部分
### 重做日志的缓冲 (redo log buffer) ，保存在内存中，是易失的
### 重做日志文件 (redo log file) ，保存在硬盘中，是持久的。
mysql> show variables like '%innodb_log_buffer_size%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.00 sec)

### 查看 redo 日志文件存储的位置和文件名
[root@iZf8z0puua6p4won7xep5rZ data]# pwd
/www/server/data
[root@iZf8z0puua6p4won7xep5rZ data]# ll | grep ib
-rw-r----- 1 mysql mysql     98304 Sep 17 19:34 #ib_16384_0.dblwr
-rw-r----- 1 mysql mysql   8486912 Sep 15 15:57 #ib_16384_1.dblwr
-rw-r----- 1 mysql mysql      9256 Sep 17 15:08 ib_buffer_pool
-rw-r----- 1 mysql mysql  10485760 Sep 17 19:34 ibdata1
-rw-r----- 1 mysql mysql  67108864 Sep 17 19:34 ib_logfile0
-rw-r----- 1 mysql mysql  67108864 Sep 17 19:34 ib_logfile1
-rw-r----- 1 mysql mysql  12582912 Sep 17 15:09 ibtmp1
-rw-r----- 1 mysql mysql  25165824 Sep 17 19:32 mysql.ibd

redo的整体流程

关于刷盘策略

### 默认值是 1
mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_log_group_home_dir';
+---------------------------+------------------+
| Variable_name             | Value            |
+---------------------------+------------------+
| innodb_log_group_home_dir | /www/server/data |
+---------------------------+------------------+
1 row in set (0.00 sec)

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

mysql> show variables like 'innodb_log_file_size';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 67108864 |
+----------------------+----------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_undo_directory';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_undo_directory | ./    |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_undo_tablespaces';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_undo_tablespaces | 2     |
+-------------------------+-------+
1 row in set (0.01 sec)

锁

脏读: 对同一个数据 1, A 要读取数据, B 修改了数据 1 变成 2 , 但是 B 还没有提交事物
此时 A 读取到的是 B 未提交的数据 2

不可重复度: 对同一个数据1, A 开始事物读取数据 1, B 修改数据 1 变成 2, B 提交事务, A 事物再次读取数据得到 2
同一个事物下, 读取同一个数据获取的值不一样

幻读: A B 开启事务, A 读取表中 id < 10 的数据, 读出来有 5 条, B 往表中插入了 2 条数据, 然后 B 提交了
A 再次读取表中 id < 10 的数据, 读出来有 7 条, A 一个事务中读取的数据变多了就是幻读

并发问题的解决方案

锁的分类

InnoDB 支持行级锁, 表级锁
MyISAM 只支持表级锁

从数据操作的类型划分: 读锁, 写锁
读锁(readlock) 写锁(writelock)
共享锁(Shared Lock, S Lock, S 锁) 排他锁(Exclusive Lock, X Lock, X 锁)

读锁 相当于 共享锁
写锁 相当于 排他锁

S => S => X
session1 session2 获取 S 锁, session3 获取 X 锁发生阻塞

### session1
mysql> use atguigudb3;
Database changed

mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   40.00 |
|  2 | 李四   |    0.00 |
|  3 | 王五   |  100.00 |
+----+--------+---------+
3 rows in set (0.00 sec) 

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
### 开启S锁 , 相当于共享锁
mysql> select * from account lock in share mode;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   40.00 |
|  2 | 李四   |    0.00 |
|  3 | 王五   |  100.00 |
+----+--------+---------+
3 rows in set (0.00 sec) 

### session2
mysql> use atguigudb3;
Database changed

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

### 开启S锁 , 相当于共享锁
mysql> select * from account lock in share mod
e;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   40.00 |
|  2 | 李四   |    0.00 |
|  3 | 王五   |  100.00 |
+----+--------+---------+
3 rows in set (0.00 sec) 

### session3
mysql> use atguigudb3;
Database changed

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
### 开启X锁, 发生了阻塞
mysql> select * from account for update;

### session1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

### session2
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

### session3
mysql> select * from account for update;     
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   40.00 |
|  2 | 李四   |    0.00 |
|  3 | 王五   |  100.00 |
+----+--------+---------+
3 rows in set (0.00 sec)

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

X => X
session1 获取 X 锁, session3 获取 X 锁, 发生了阻塞

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

mysql> select * from account for update;      
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   40.00 |
|  2 | 李四   |    0.00 |
|  3 | 王五   |  100.00 |
+----+--------+---------+
3 rows in set (0.00 sec)

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

mysql> select * from account for update;     

^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>

### session1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)


### session3
mysql> select * from account for update;     
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   40.00 |
|  2 | 李四   |    0.00 |
|  3 | 王五   |  100.00 |
+----+--------+---------+
3 rows in set (0.00 sec)

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

X => S
session1 获取 X 锁, session3 获取 S 锁, 发生了阻塞

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

mysql> select * from account for update;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   40.00 |
|  2 | 李四   |    0.00 |
|  3 | 王五   |  100.00 |
+----+--------+---------+
3 rows in set (0.00 sec)

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

mysql> select * from account lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

nowait 和 skip locked的用法
nowait 在执行 select 时候, 遇到了锁, 直接返回不等待
skip locked 遇到 select 语句跳过加锁的行, 返回没有上锁的行

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

mysql> select * from account for update;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   40.00 |
|  2 | 李四   |    0.00 |
|  3 | 王五   |  100.00 |
+----+--------+---------+
3 rows in set (0.00 sec)


### session3
mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from account for update nowait;
ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

mysql> select * from account for update skip locked;
Empty set (0.00 sec)

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

### session1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

表级别的S锁 X锁

给表上锁, 解锁的基本操作

mysql> use atguigudb3;
Database changed

mysql> CREATE TABLE mylock(
    ->   id INT NOT NULL PRIMARY KEY auto_increment,
    ->   NAME VARCHAR(20)
    -> ) ENGINE MYISAM;
Query OK, 0 rows affected (0.00 sec)

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

mysql> show open tables;
...

### in_use 都是 0 , 说明这张表目前没有锁
mysql> show open tables where in_use > 0;
Empty set (0.00 sec)

### 添加读锁
mysql> lock tables mylock read;
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables where in_use > 0;
+------------+--------+--------+-------------+
| Database   | Table  | In_use | Name_locked |
+------------+--------+--------+-------------+
| atguigudb3 | mylock |      1 |           0 |
+------------+--------+--------+-------------+
1 row in set (0.00 sec)

### 释放锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables where in_use > 0;
Empty set (0.00 sec)

### 添加写锁
mysql> lock tables mylock write;
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables where in_use > 0;
+------------+--------+--------+-------------+
| Database   | Table  | In_use | Name_locked |
+------------+--------+--------+-------------+
| atguigudb3 | mylock |      1 |           0 |
+------------+--------+--------+-------------+
1 row in set (0.00 sec)

### 释放锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables where in_use > 0;
Empty set (0.00 sec)

### 验证读锁
### session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> lock tables mylock read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mylock;
+----+------+
| id | NAME |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

### 读锁, 不能进行写的操作
mysql> update mylock set name = 'a1' where id = 1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated

### 也不能对其他表进行读的操作
mysql> select * from account;
ERROR 1100 (HY000): Table 'account' was not locked with LOCK TABLES

### session2
mysql> use atguigudb3;
Database changed

### 其他 session 可以对读锁进行读的操作
mysql> select * from mylock;
+----+------+
| id | NAME |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

### 其他 session 不可以对表进行写的操作
mysql> update mylock set name = 'a2' where id = 1;
### 等待状态...

### session1
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables where in_use > 0;
Empty set (0.00 sec)



### 验证写锁
### session1
mysql> lock tables mylock write;
Query OK, 0 rows affected (0.00 sec)

mysql> show open tables where in_use > 0;
+------------+--------+--------+-------------+
| Database   | Table  | In_use | Name_locked |
+------------+--------+--------+-------------+
| atguigudb3 | mylock |      1 |           0 |
+------------+--------+--------+-------------+
1 row in set (0.00 sec)

### 可读
mysql> select * from mylock;
+----+------+
| id | NAME |
+----+------+
|  1 | a2   |
+----+------+
1 row in set (0.00 sec)

### 可写
mysql> update mylock set name = 'a3' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

### 不可读其他表
mysql> select * from account;
ERROR 1100 (HY000): Table 'account' was not locked with LOCK TABLES

### session2
### 不可读
select * from mylock;
### 不可写
update mylock set name = 'a5' where id = 1;

### session1
unlock tables;
show open tables where in_use > 0;

意向锁

意向锁分为意向共享锁(IS)和意向排他锁(IX)两种。
意向共享锁是兼容的共享锁,它表示事务打算给数据行加共享锁。多个事务可以同时获得一个对象的意向共享锁。
意向排他锁是不兼容的共享锁,它表示事务打算给数据行加排他锁。如果一个事务获得了一个对象的意向排他锁,其他事务就不能再获得该对象的任何锁了。
在涉及多个对象的事务中,InnoDB会在语句开始时自动加上意向锁,以控制访问顺序,从而避免死锁的发生。
简单来说,意向锁的作用是显式地展示事务的加锁意向,从而提高并发性能,减少不必要的加锁操作。

### session1
CREATE  TABLE `teacher` (
  `id` int not null,
  `name` varchar(255) not null,
  PRIMARY key (`id`)
) engine=InnoDB default charset=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into `teacher` values
('1', 'zhangsan'),
('2', 'lisi'),
('3', 'wangwu'),
('4', 'zhaoliu'),
('5', 'songhongkang'),
('6', 'leifengyang');

mysql> select * from teacher;
+----+--------------+   
| id | name         |   
+----+--------------+   
|  1 | zhangsan     |   
|  2 | lisi         |   
|  3 | wangwu       |   
|  4 | zhaoliu      |   
|  5 | songhongkang |   
|  6 | leifengyang  |   
+----+--------------+   
6 rows in set (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

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

mysql> select * from teacher where id = 6 for update;
+----+-------------+   
| id | name        |   
+----+-------------+   
|  6 | leifengyang |   
+----+-------------+   
1 row in set (0.00 sec)

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

### 阻塞, 无法创建读锁
mysql> lock tables teacher read;
Query OK, 0 rows affected (0.00 sec)

### session1
### 提交事物
mysql> commit;
Query OK, 0 rows affected (0.00 sec)


### session2
mysql> lock tables teacher read;
Query OK, 0 rows affected (46.46 sec)

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

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


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

mysql> select * from teacher where id = 6 for update;
+----+-------------+
| id | name        |
+----+-------------+
|  6 | leifengyang |
+----+-------------+
1 row in set (0.00 sec)

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

### 阻塞
mysql> select * from teacher where id = 6 for update;

### session1
commit;
### session2
commit;

元数据锁

当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

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

mysql> select * from teacher;
+----+--------------+   
| id | name         |   
+----+--------------+   
|  1 | zhangsan     |   
|  2 | lisi         |   
|  3 | wangwu       |   
|  4 | zhaoliu      |   
|  5 | songhongkang |   
|  6 | leifengyang  |   
+----+--------------+   
6 rows in set (0.00 sec)

### session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
### 阻塞, MDL 读锁跟 MDL 写锁会发生阻塞
mysql> alter table teacher add age int;


### session3
mysql> show processlist;
+----+-----------------+-----------+------------+---------+--------+---------------------------------+---------------------------------+
| Id | User            | Host      | db         | Command | Time   | State                           | Info                            |
+----+-----------------+-----------+------------+---------+--------+---------------------------------+---------------------------------+
|  5 | event_scheduler | localhost | NULL       | Daemon  | 162036 | Waiting on empty queue          | NULL                            |
| 74 | root            | localhost | atguigudb3 | Sleep   |    230 |                                 | NULL                            |
| 78 | root            | localhost | atguigudb3 | Query   |    207 | Waiting for table metadata lock | alter table teacher add age int |
| 81 | root            | localhost | NULL       | Sleep   |     36 |                                 | NULL                            |
| 85 | root            | localhost | NULL       | Query   |      0 | init                            | show processlist                |
+----+-----------------+-----------+------------+---------+--------+---------------------------------+---------------------------------+
5 rows in set (0.00 sec)


### session2
Ctrl C

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

mysql> use atguigudb3;
Database changed
### MDL读锁 跟 MDL读锁 是可以读取数据的
mysql> select * from teacher;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | zhangsan     |
|  2 | lisi         |
|  3 | wangwu       |
|  4 | zhaoliu      |
|  5 | songhongkang |
|  6 | leifengyang  |
+----+--------------+
6 rows in set (12.80 sec)

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

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

### 这里需要一直阻塞住, 不要按Ctrl C
mysql> alter table teacher add age int;


### session3
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
### MDL读锁 => MDL写锁 => MDL读锁, 此时 session3 中的读锁也发生了阻塞
mysql> select * from teacher;


### session1
commit;
### session2
commit;
### session3
commit;

InnoDB中的行锁

记录锁

mysql> use atguigudb3;
Database changed

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.03 sec)

mysql> drop table student;
Query OK, 0 rows affected (0.02 sec)

CREATE TABLE student (
  id INT,
  name VARCHAR(20),
  class VARCHAR(10),
  PRIMARY KEY (id)
) Engine=InnoDB CHARSET=utf8;

insert into student values
(1, '张三', '一班'),
(3, '李四', '二班'),
(8, '王五', '二班'),
(15, '赵六', '二班'),
(20, '钱七', '三班');

mysql> select * from student;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 张三   | 一班   |
|  3 | 李四   | 二班   |
|  8 | 王五   | 二班   |
| 15 | 赵六   | 二班   |
| 20 | 钱七   | 三班   |
+----+--------+--------+
5 rows in set (0.00 sec)

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

mysql> update student set name = '张三1' where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


### session2
mysql> use atguigudb3;
Database changed
mysql> select * from student;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  1 | 张三   | 一班   |
|  3 | 李四   | 二班   |
|  8 | 王五   | 二班   |
| 15 | 赵六   | 二班   |
| 20 | 钱七   | 三班   |
+----+--------+--------+
5 rows in set (0.00 sec)

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

mysql> select * from student where id = 2 lock in share mode;
Empty set (0.01 sec)

mysql> select * from student where id = 3 lock in share mode;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  3 | 李四   | 二班   |
+----+--------+--------+
1 row in set (0.00 sec) 

### 发生阻塞
mysql> select * from student where id = 1 lock in share mode;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>


### session1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

间隙锁

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

mysql> select * from student where id = 8 lock in share mode;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
|  8 | 王五   | 二班   |
+----+--------+--------+
1 row in set (0.00 sec)

mysql>


### session2
### 发生阻塞
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 8 for update;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>


##### session1 对 id=5 的行加共享锁, session2 对 id=5 添加排他锁, 因为数据不存在, 可以直接获取排他锁
##### session3 因为受到了 session2 的排它锁的影响没有办法添加锁, 所以被阻塞
### session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 5 lock in share mode;
Empty set (0.00 sec)

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

mysql> select * from student where id = 5 for update;
Empty set (0.00 sec)

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

mysql> use atguigudb3;
Database changed
mysql> insert into student(id, name, class) values(6, 'tom', '三班');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

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

mysql> select * from student where id = 25 lock in share mode;
Empty set (0.00 sec)

### session3
mysql> insert into student(id, name, class) values(21, 'tom', '三班');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>


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

mysql> select * from student where id = 25 lock in share mode;
Empty set (0.00 sec)

### session3
##### (20, +∞)
mysql> insert into student(id, name, class) values(21, 'tom', '三班');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> insert into student(id, name, class) values(21, 'tom', '三班');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into student(id, name, class) values(26, 'tom', '三班');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into student(id, name, class) values(17, 'tom', '三班');
Query OK, 1 row affected (0.00 sec)

### session1
mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 47208414106696:1096:47208468665760     
ENGINE_TRANSACTION_ID: 99316
            THREAD_ID: 178
             EVENT_ID: 15
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 47208468665760
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 47208414106696:36:4:1:47208468662656   
ENGINE_TRANSACTION_ID: 99316
            THREAD_ID: 178
             EVENT_ID: 15
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 47208468662656
            LOCK_TYPE: RECORD
            LOCK_MODE: X,INSERT_INTENTION
          LOCK_STATUS: WAITING
            LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 47208414104984:1096:47208468653504
ENGINE_TRANSACTION_ID: 328683390815640
            THREAD_ID: 172
             EVENT_ID: 36
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 47208468653504
            LOCK_TYPE: TABLE
            LOCK_MODE: IS
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 47208414104984:36:4:1:47208468650400
ENGINE_TRANSACTION_ID: 328683390815640
            THREAD_ID: 172
             EVENT_ID: 36
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 47208468650400
            LOCK_TYPE: RECORD
            LOCK_MODE: S
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
4 rows in set (0.00 sec)

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

mysql> select * from student where id = 5 for update;
Empty set (0.00 sec)

mysql> insert into student(id, name, class) values(6, 'jerry', '一班');
Query OK, 1 row affected (10.75 sec)

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

mysql> select * from student where id = 5 for update;
Empty set (0.00 sec)

mysql> insert into student(id, name, class) values(6, 'jerry', '一班');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

临键锁

相当于是 记录锁 跟 间隙锁 的结合
(8, 15]
记录锁锁定的是某一条记录
间隙锁锁定的是某一段范围
临键锁就是范围 + 边界的闭区间

代码演示

### session1
mysql> use atguigudb3;
Database changed
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+----+---------+--------+
| id | name    | class  |
+----+---------+--------+
|  1 | 张三1   | 一班   |
|  3 | 李四    | 二班   |
|  8 | 王五    | 二班   |
| 15 | 赵六    | 二班   |
| 17 | tom     | 三班   |
| 20 | 钱七    | 三班   |
+----+---------+--------+
6 rows in set (0.00 sec)

mysql> select * from student where id <= 15 and id > 8 for update;
+----+--------+--------+
| id | name   | class  |
+----+--------+--------+
| 15 | 赵六   | 二班   |
+----+--------+--------+
1 row in set (0.00 sec)

### session2
mysql> use atguigudb3;
Database changed
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 15 lock in share mode;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> select * from student where id = 15 for update;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into student(id, name, class) values(12, 'Tim', '一班');
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql>

### session1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
### session2
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

插入意向锁

插入意向锁是在插入一条记录前, 有INSERT 操作产生的一种间隙锁
插入间隙锁并不会阻止别的事务继续获取该记录上任何类型的锁
比如插入意向锁插入的是11的记录, 并不会影响插入12的记录的插入意向锁

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

mysql> select * from student;
+----+---------+--------+
| id | name    | class  |
+----+---------+--------+
|  1 | 张三1   | 一班   |
|  3 | 李四    | 二班   |
|  8 | 王五    | 二班   |
| 15 | 赵六    | 二班   |
| 17 | tom     | 三班   |
| 20 | 钱七    | 三班   |
+----+---------+--------+
6 rows in set (0.00 sec)

mysql> select * from student where id = 12 for update;
Empty set (0.00 sec)


### session2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
### 阻塞等待
mysql> insert into student(id, name, class) values(11, 'Tim', '一班');


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

### 阻塞等待
mysql> insert into student(id, name, class) values(12, 'Tim', '一班');
Query OK, 1 row affected (6.80 sec)

页锁

从对待锁的态度划分: 乐观锁, 悲观锁

悲观锁

select … for update 是MySQL中悲观锁
select … for update 语句执行过程中所有扫描的行都会被锁上, 因此在MySQL中用悲观锁必须确定使用了索引,
而不是全表扫描, 否则会把整个表锁住

乐观锁

乐观锁, 不采用数据库自身的锁机制, 而是通过查询来实现
乐观锁适用于多读的应用类型, 这样可以提高吞吐量

采用版本号机制或者CAS机制

按加锁的方式划分: 显示锁, 隐式锁

隐式锁

### session1
mysql> use atguigudb3;
Database changed
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+----+---------+--------+
| id | name    | class  |
+----+---------+--------+
|  1 | 张三1   | 一班   |
|  3 | 李四    | 二班   |
|  8 | 王五    | 二班   |
| 15 | 赵六    | 二班   |
| 17 | tom     | 三班   |
| 20 | 钱七    | 三班   |
+----+---------+--------+
6 rows in set (0.01 sec)

mysql> insert into student(id, name, class) values(2, 'Tom', '一班');
Query OK, 1 row affected (0.01 sec)

### session3
mysql> select * from performance_schema.data_lock_waits\G;
Empty set (0.00 sec)

ERROR:
No query specified

### session2
mysql> use atguigudb3;
Database changed
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

### 阻塞
mysql> select * from student lock in share mode;

### session3
### 此时是可以查询到锁的(显示锁)，刚刚是因为隐式锁, 所以查看不到
### 在事务一进行插入的过程中, 如果来了一个事务也进行加锁, 那么之前的事务就会将隐式锁转换成显示锁
mysql> select * from performance_schema.data_lock_waits\G;
*************************** 1. row ***************************
                          ENGINE: INNODB
       REQUESTING_ENGINE_LOCK_ID: 47208414105840:36:4:8:47208468656904
REQUESTING_ENGINE_TRANSACTION_ID: 328683390816496
            REQUESTING_THREAD_ID: 425
             REQUESTING_EVENT_ID: 7
REQUESTING_OBJECT_INSTANCE_BEGIN: 47208468656904
         BLOCKING_ENGINE_LOCK_ID: 47208414104984:36:4:8:47208468650400
  BLOCKING_ENGINE_TRANSACTION_ID: 99332
              BLOCKING_THREAD_ID: 425
               BLOCKING_EVENT_ID: 7
  BLOCKING_OBJECT_INSTANCE_BEGIN: 47208468650400
1 row in set (0.00 sec)

ERROR:
No query specified

显示锁

死锁

### session1
mysql> use atguigudb3;
Database changed
mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   40.00 |
|  2 | 李四   |    0.00 |
|  3 | 王五   |  100.00 |
+----+--------+---------+
3 rows in set (0.01 sec)

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

mysql> update account set balance = balance - 10 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> select * from account;
+----+--------+---------+
| id | NAME   | balance |
+----+--------+---------+
|  1 | 张三   |   40.00 |
|  2 | 李四   |    0.00 |
|  3 | 王五   |  100.00 |
+----+--------+---------+
3 rows in set (0.00 sec)

mysql> update account set balance = balance - 10 where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

### session1
mysql> update account set balance = balance + 10 where id = 3;
Query OK, 1 row affected (22.88 sec)
Rows matched: 1  Changed: 1  Warnings: 0

### session2
mysql> update account set balance = balance + 10 where id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

锁的内存结构

锁的监控

### session1
mysql> show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name                 | Value  |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0      |
| Innodb_row_lock_time          | 481317 |
| Innodb_row_lock_time_avg      | 20054  |
| Innodb_row_lock_time_max      | 51005  |
| Innodb_row_lock_waits         | 24     |
+-------------------------------+--------+
5 rows in set (0.00 sec)


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

mysql> select * from student for update;
+----+---------+--------+
| id | name    | class  |
+----+---------+--------+
|  1 | 张三1   | 一班   |
|  2 | Tom     | 一班   |
|  3 | 李四    | 二班   |
|  8 | 王五    | 二班   |
| 15 | 赵六    | 二班   |
| 17 | tom     | 三班   |
| 20 | 钱七    | 三班   |
+----+---------+--------+
7 rows in set (0.00 sec)

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

### 阻塞等待
mysql> select * from student for update;

### session3
mysql> select * from information_schema.INNODB_TRX\G
*************************** 1. row ***************************
                    trx_id: 99339
                 trx_state: LOCK WAIT
               trx_started: 2023-09-28 16:52:27
     trx_requested_lock_id: 47208414105840:36:4:7:47208468656904
          trx_wait_started: 2023-09-28 16:53:32
                trx_weight: 2
       trx_mysql_thread_id: 387
                 trx_query: select * from student for update
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 2
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: 1
*************************** 2. row ***************************
                    trx_id: 99338
                 trx_state: RUNNING
               trx_started: 2023-09-28 16:51:59
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 386
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 8
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
2 rows in set (0.00 sec)

mysql> select * from performance_schema.data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 47208414105840:1096:47208468659664
ENGINE_TRANSACTION_ID: 99339
            THREAD_ID: 425
             EVENT_ID: 17
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 47208468659664
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 47208414105840:36:4:7:47208468657248
ENGINE_TRANSACTION_ID: 99339
            THREAD_ID: 425
             EVENT_ID: 19
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 47208468657248
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: WAITING
            LOCK_DATA: 1
*************************** 3. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 47208414104984:1096:47208468653504
ENGINE_TRANSACTION_ID: 99338
            THREAD_ID: 424
             EVENT_ID: 24
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 47208468653504
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 4. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 47208414104984:36:4:1:47208468650400
ENGINE_TRANSACTION_ID: 99338
            THREAD_ID: 424
             EVENT_ID: 24
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 47208468650400
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: supremum pseudo-record
*************************** 5. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 47208414104984:36:4:2:47208468650400
ENGINE_TRANSACTION_ID: 99338
            THREAD_ID: 424
             EVENT_ID: 24
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 47208468650400
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 17
*************************** 6. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 47208414104984:36:4:3:47208468650400
ENGINE_TRANSACTION_ID: 99338
            THREAD_ID: 424
             EVENT_ID: 24
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 47208468650400
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 3
*************************** 7. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 47208414104984:36:4:4:47208468650400
ENGINE_TRANSACTION_ID: 99338
            THREAD_ID: 424
             EVENT_ID: 24
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 47208468650400
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 8
*************************** 8. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 47208414104984:36:4:5:47208468650400
ENGINE_TRANSACTION_ID: 99338
            THREAD_ID: 424
             EVENT_ID: 24
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 47208468650400
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 15
*************************** 9. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 47208414104984:36:4:6:47208468650400
ENGINE_TRANSACTION_ID: 99338
            THREAD_ID: 424
             EVENT_ID: 24
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 47208468650400
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 20
*************************** 10. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 47208414104984:36:4:7:47208468650400
ENGINE_TRANSACTION_ID: 99338
            THREAD_ID: 424
             EVENT_ID: 24
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 47208468650400
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 1
*************************** 11. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 47208414104984:36:4:8:47208468650400
ENGINE_TRANSACTION_ID: 99338
            THREAD_ID: 424
             EVENT_ID: 24
        OBJECT_SCHEMA: atguigudb3
          OBJECT_NAME: student
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 47208468650400
            LOCK_TYPE: RECORD
            LOCK_MODE: X
          LOCK_STATUS: GRANTED
            LOCK_DATA: 2
11 rows in set (0.00 sec)

mysql> select * from performance_schema.data_lock_waits\G
*************************** 1. row ***************************
                          ENGINE: INNODB
       REQUESTING_ENGINE_LOCK_ID: 47208414105840:36:4:7:47208468657592
REQUESTING_ENGINE_TRANSACTION_ID: 99339
            REQUESTING_THREAD_ID: 425
             REQUESTING_EVENT_ID: 20
REQUESTING_OBJECT_INSTANCE_BEGIN: 47208468657592
         BLOCKING_ENGINE_LOCK_ID: 47208414104984:36:4:7:47208468650400
  BLOCKING_ENGINE_TRANSACTION_ID: 99338
              BLOCKING_THREAD_ID: 424
               BLOCKING_EVENT_ID: 24
  BLOCKING_OBJECT_INSTANCE_BEGIN: 47208468650400
1 row in set (0.00 sec)

MVCC

日志

通用查询日志

mysql> show variables like '%general%';                                       
+------------------+----------------------------------------------+           
| Variable_name    | Value                                        |           
+------------------+----------------------------------------------+           
| general_log      | OFF                                          |           
| general_log_file | /www/server/data/iZf8z0puua6p4won7xep5rZ.log |           
+------------------+----------------------------------------------+           
2 rows in set (0.02 sec)    

mysql> set global general_log = on;                                           
Query OK, 0 rows affected (0.02 sec) 

mysql> use atguigudb3;                
Database changed                      
mysql> show tables;                   
+----------------------+              
| Tables_in_atguigudb3 |              
+----------------------+              
| account              |              
| class_comment        |              
| class_comment1       |              
| mylock               |              
| student              |              
| teacher              |              
| test_load            |              
| user1                |              
+----------------------+              
8 rows in set (0.00 sec)              
                                      
mysql> select * from account;         
+----+--------+---------+             
| id | NAME   | balance |             
+----+--------+---------+             
|  1 | 张三   |   30.00 |             
|  2 | 李四   |    0.00 |             
|  3 | 王五   |  110.00 |             
+----+--------+---------+             
3 rows in set (0.01 sec)              

### 查看日志
[root@iZf8z0puua6p4won7xep5rZ data]# tail -f iZf8z0puua6p4won7xep5rZ.log   
/www/server/mysql/bin/mysqld, Version: 8.0.24 (Source distribution). starte
d with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
2023-10-07T06:34:02.460374Z       674 Query     show variables like '%gener
al%'
2023-10-07T06:37:57.004236Z       674 Query     SELECT DATABASE()
2023-10-07T06:37:57.004443Z       674 Init DB   atguigudb3
2023-10-07T06:38:02.780805Z       674 Query     show tables
2023-10-07T06:38:10.416730Z       674 Query     select * from account    

### 停止日志

mysql> set global general_log = off;      
Query OK, 0 rows affected (0.01 sec)      
                                          
mysql> select * from student;             
+----+---------+--------+                 
| id | name    | class  |                 
+----+---------+--------+                 
|  1 | 张三1   | 一班   |                 
|  2 | Tom     | 一班   |                 
|  3 | 李四    | 二班   |                 
|  8 | 王五    | 二班   |                 
| 15 | 赵六    | 二班   |                 
| 17 | tom     | 三班   |                 
| 20 | 钱七    | 三班   |                 
+----+---------+--------+                 
7 rows in set (0.00 sec)        

### 删除/刷新日志
### 此时需要在on的环境下执行
mysql> set global general_log = on; 
Query OK, 0 rows affected (0.01 sec)

### 备份一份
[root@iZf8z0puua6p4won7xep5rZ data]# mv iZf8z0puua6p4won7xep5rZ.log iZf8z0p
uua6p4won7xep5rZ.log.old
[root@iZf8z0puua6p4won7xep5rZ data]# pwd
/www/server/data
[root@iZf8z0puua6p4won7xep5rZ data]# 

### 重新生成
[root@iZf8z0puua6p4won7xep5rZ data]# /www/server/mysql/bin/mysqladmin -uroot -p flush-logs
Enter password: 

错误日志

### 查看错误日志所在的路径
mysql> SHOW VARIABLES LIKE 'log_err%';                                  
+----------------------------+----------------------------------------+ 
| Variable_name              | Value                                  | 
+----------------------------+----------------------------------------+ 
| log_error                  | ./iZf8z0puua6p4won7xep5rZ.err          | 
| log_error_services         | log_filter_internal; log_sink_internal | 
| log_error_suppression_list |                                        | 
| log_error_verbosity        | 2                                      | 
+----------------------------+----------------------------------------+ 
4 rows in set (0.01 sec)                                                


[root@iZf8z0puua6p4won7xep5rZ data]# vim iZf8z0puua6p4won7xep5rZ.err
[root@iZf8z0puua6p4won7xep5rZ data]# pwd
/www/server/data


[root@iZf8z0puua6p4won7xep5rZ data]# ll | grep iZf8z0puua6p4won7xep5rZ
-rw-r----- 1 mysql mysql   135308 Oct  7 10:33 iZf8z0puua6p4won7xep5rZ.err     
-rw-r----- 1 mysql mysql      354 Oct  7 15:00 iZf8z0puua6p4won7xep5rZ.log     
-rw-r----- 1 mysql mysql      835 Oct  7 14:45 iZf8z0puua6p4won7xep5rZ.log.old 
-rw-r----- 1 mysql mysql        6 Sep 17 15:09 iZf8z0puua6p4won7xep5rZ.pid     

### 重新生成错误日志
[root@iZf8z0puua6p4won7xep5rZ data]# mv iZf8z0puua6p4won7xep5rZ.err iZf8z0puua6p4won7xep5rZ.err.old
[root@iZf8z0puua6p4won7xep5rZ log]# /www/server/mysql/bin/mysqladmin -uroot -p flush-logs

tips:
此时我这里使用mysql服务是通过宝塔面板直接搭建成的

貌似错误日志路径跟正常搭建的路径不太一样

如果在执行刷新命令出现了error, 可以先执行如下的命令

[root@atguigu01 log]# mysqladmin -uroot -p flush-logs
Enter password:
mysqladmin: refresh failed; error: 'Could not open file '/var/log/mysqld.log' for
error logging.'

[root@atguigu01 log]# install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log

二进制日志