数据库

MySQL-排序order by

​ 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> show create table user\G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(50) NOT NULL COMMENT '名字',
  `age` tinyint unsigned NOT NULL COMMENT '年龄',
  `sex` enum('M','W') NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select * from user;
+----+----------+-----+-----+
| id | name     | age | sex |
+----+----------+-----+-----+
|  1 | zhangsan |  16 | M   |
|  2 | lisi     |  13 | M   |
|  3 | laoliu   |  24 | W   |
|  4 | jianzhe  |  14 | W   |
|  5 | wangwei  |  21 | M   |
+----+----------+-----+-----+
5 rows in set (0.03 sec)

使用order by 排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain select * from user order by age;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.02 sec)

mysql> explain select * from user order by name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

我们发现，这两个sql的排序方式都是Using filesort,这种排序方式呢，在数据量小时，MySQL可能会在内存中进行排序，但是，排序的数据量大时，这种排序可能会在磁盘上进行，但总的来说，这种排序方式速度并不理想，需要MySQL使用额外的方式进行排序。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> explain select name from user order by name;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | name | 202     | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select name from user order by age;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select age from user order by age;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)

从上面这两个我们可以看出，第一个查询name使用name排序，第二个同样是查询name,但他使用age排序，前者是直接使用了索引进行排序Using index，而后者却使用了Using filesort，我们可以判断，order by排序的性能不仅与待排序的字段有关，还跟要查询的字段有关。这涉及到了具体索引和非具体索引的搜索过程

问题汇总

MySQL-阻塞问题排查

这里以解决**DROP INDEX 阻塞** 的问题为例：

1. 确认问题背景

  1. 操作内容：DROP INDEX 需要获取 表级独占锁，可能被其他事务或连接阻塞。
  2. 常见原因：
    • 未提交的事务持有表的共享锁或意向锁。
    • 长时间运行的查询或未关闭的连接阻塞表操作。
    • 高隔离级别（如 SERIALIZABLE）增加了锁的持有范围。

2. 检查锁和连接状态

2.1 检查当前活跃的连接和状态

执行以下命令，查看是否有阻塞的事务或长时间 Sleep 的连接：

1
SHOW PROCESSLIST;
  • 关注重点：
    • Command 列显示 Sleep 或 Query 的连接。
    • State 列中是否有 Locked 或 Waiting for table metadata lock。
    • Time 列中时间较长的连接可能是问题的来源。

例如：

PixPin_2024-11-26_17-15-18

2.2 查看锁的详细信息

使用以下命令检查 InnoDB 锁的状态：

1
SHOW ENGINE INNODB STATUS\G;
  • 重点部分：
    • TRANSACTIONS：查看哪些事务持有锁或导致锁等待。
    • LOCK WAIT：查看具体锁的类型和影响的表。

3. 检查是否有未提交的事务

  1. 确认当前是否有未提交的事务：

    1
    SELECT * FROM information_schema.innodb_trx\G;
    • trx_state = LOCK WAIT：事务正在等待锁。
    • trx_started 时间：长时间运行的事务可能是问题的来源。

  2. 解决方法：

    • 提交事务：

      1
      COMMIT;

    • 或回滚事务：

      1
      ROLLBACK;

4. 终止无效的连接

对于长时间处于 Sleep 状态或导致阻塞的连接，可以使用以下步骤终止：

  1. 找到连接 ID：

    1
    SHOW PROCESSLIST;

  2. 使用 KILL命令终止：

    1
    KILL [connection_id];

5. 降低隔离级别

高隔离级别（如 SERIALIZABLE）可能会对表范围加锁，阻止其他事务进行操作。

  1. 将隔离级别调整为 

    1
    REPEATABLE READ

    或更低：

    1
    SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

  2. 如果事务中设置了 AUTOCOMMIT = 0，确认是否可以使用更低的隔离级别完成操作。

6. 尝试释放表级锁

如果表被 metadata lock 阻塞，可以尝试以下方法：

  1. 查看 information_schema 中的锁信息：

    1
    SELECT * FROM information_schema.metadata_locks\G
    1. 终止相关会话或操作。

7. 再次尝试删除索引

清理阻塞后，重新执行删除索引的操作：

1
DROP INDEX name_index ON user;

总结排查步骤

  1. 检查连接和事务状态：
    • 使用 SHOW PROCESSLIST 和 SHOW ENGINE INNODB STATUS\G 找出阻塞的连接或事务。
    • 找到长时间运行或未提交的事务。
  2. 清理阻塞连接：
    • 使用 KILL 终止不必要的连接。
  3. 调整隔离级别：
    • 降低隔离级别到 REPEATABLE READ 或更低，减少锁冲突。
  4. 提交或回滚事务：
    • 提交或回滚可能占用资源的事务。
  5. 删除索引：
    • 确认所有锁释放后，执行 DROP INDEX。
数据库

MySQL优化

MySQL需要优化的地方有哪些？

  1. SQL和索引优化
  2. 应用上的优化
  3. MySQL server优化

一、SQL和索引优化

  1. 慢查询日志：开启 MySQL 的慢查询日志，捕捉执行时间较长的 SQL 语句。

  2. EXPLAIN 语句：对于发现的慢查询，使用 EXPLAIN 分析查询的执行计划，查看查询是否利用了索引，是否有全表扫描等问题。

二、应用上优化

  1. 连接数据库方面：引入线程池，减少创建连接次数
  2. 引入缓存: 使用redis缓存热点数据

三、MySQL Server优化

1、配置上的优化

SHOW STATUS ,SHOW VARIABLES和show engine innodb status\G：检查数据库的状态和配置项，查看是否存在如锁争用、资源使用不当等问题。

innodb_buffer_pool_size：确保 InnoDB 的 Buffer Pool 足够大,一般设置为系统内存的 60%-80%。这能缓存更多的数据页，减少磁盘 I/O 操作，尤其是当查询和修改的数据量较大时。

1
SET GLOBAL innodb_buffer_pool_size = 4G;  -- 设置为4GB

innodb_log_buffer_size：确保日志缓冲区足够大，避免事务日志在写入时的等待。对于高事务量的系统，可以将其设置为更大的值。

1
SET GLOBAL innodb_log_buffer_size = 128M;  -- 设置为128MB

innodb_flush_log_at_trx_commit：为了提高性能，可以考虑将其设置为 2，减少每次事务提交时的磁盘同步，但这会增加崩溃恢复的风险。

1
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

并发连接数量和超时时间设置，MySQL Server作为一个服务器，可以设置客户端的最大连接量和连接超时时间，如果数据库连接统计数量比较大，这两个参数的值需要设置大一些。在配置文件（my.cnf或my.ini）最下面，添加配置：max_connections=2000，然后重启MySQLServer，设置生效

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
mysql> show global variables like '%timeout%';
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| connect_timeout                   | 10       |
| delayed_insert_timeout            | 300      |
| have_statement_timeout            | YES      |
| innodb_flush_log_at_timeout       | 1        |
| innodb_lock_wait_timeout          | 50       |
| innodb_rollback_on_timeout        | OFF      |
| interactive_timeout               | 28800    |
| lock_wait_timeout                 | 31536000 |
| mysqlx_connect_timeout            | 30       |
| mysqlx_idle_worker_thread_timeout | 60       |
| mysqlx_interactive_timeout        | 28800    |
| mysqlx_port_open_timeout          | 0        |
| mysqlx_read_timeout               | 30       |
| mysqlx_wait_timeout               | 28800    |
| mysqlx_write_timeout              | 60       |
| net_read_timeout                  | 30       |
| net_write_timeout                 | 60       |
| replica_net_timeout               | 60       |
| rpl_stop_replica_timeout          | 31536000 |
| rpl_stop_slave_timeout            | 31536000 |
| slave_net_timeout                 | 60       |
| ssl_session_cache_timeout         | 300      |
| wait_timeout                      | 28800    |
+-----------------------------------+----------+
23 rows in set (0.06 sec)

mysql> show variables like '%connect%';
+-----------------------------------------------+----------------------+
| Variable_name                                 | Value                |
+-----------------------------------------------+----------------------+
| character_set_connection                      | utf8mb4              |
| collation_connection                          | utf8mb4_0900_ai_ci   |
| connect_timeout                               | 10                   |
| connection_memory_chunk_size                  | 8192                 |
| connection_memory_limit                       | 18446744073709551615 |
| disconnect_on_expired_password                | ON                   |
| global_connection_memory_limit                | 18446744073709551615 |
| global_connection_memory_tracking             | OFF                  |
| init_connect                                  |                      |
| max_connect_errors                            | 100                  |
| max_connections                               | 151                  |
| max_user_connections                          | 0                    |
| mysqlx_connect_timeout                        | 30                   |
| mysqlx_max_connections                        | 100                  |
| performance_schema_session_connect_attrs_size | 512                  |
+-----------------------------------------------+----------------------+
15 rows in set (0.11 sec)
问题汇总

MySQL数据库无法插入中文解决

MySQL数据库无法插入中文解决

一、数据库修改

先修改数据库的字符集编码

1
ALTER DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

再修改表的字符集编码

1
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

二、配置 IntelliJ IDEA 的数据库连接：

设置连接的编码格式 mybatis操作数据库配置文件

1
2
3
4
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/your_database_name?useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=your_password
数据库

MySQL-日志

  1. 错误日志：mysqld服务运行错误过程中出现的cordump error exception

  2. 查询日志:记录所有的sql，包括增删改查，一般在调试的时候开启

    1
    mysql> show global variables like "%genera%";

  3. 二进制日志：数据恢复，主从复制。除select外

  4. 慢查询日志

相关日志

查看服务打开状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> show variables like 'log_%';
+----------------------------------------+----------------------------------------+
| Variable_name                          | Value                                  |
+----------------------------------------+----------------------------------------+
| log_bin                                | ON                                     |
| log_bin_basename                       | /var/lib/mysql/binlog                  |
| log_bin_index                          | /var/lib/mysql/binlog.index            |
| log_bin_trust_function_creators        | OFF                                    |
| log_bin_use_v1_row_events              | OFF                                    |
| log_error                              | /var/log/mysql/error.log               |
| log_error_services                     | log_filter_internal; log_sink_internal |
| log_error_suppression_list             |                                        |
| log_error_verbosity                    | 2                                      |
| log_output                             | FILE                                   |
| log_queries_not_using_indexes          | OFF                                    |
| log_raw                                | OFF                                    |
| log_replica_updates                    | ON                                     |
| log_slave_updates                      | ON                                     |
| log_slow_admin_statements              | OFF                                    |
| log_slow_extra                         | OFF                                    |
| log_slow_replica_statements            | OFF                                    |
| log_slow_slave_statements              | OFF                                    |
| log_statements_unsafe_for_binlog       | ON                                     |
| log_throttle_queries_not_using_indexes | 0                                      |
| log_timestamps                         | UTC                                    |
+----------------------------------------+----------------------------------------+
21 rows in set (0.21 sec)

打开my.ini或my.cnf,在后面加上上面的参数，保存后重启mysql服务就行了

1
2
3
4
5
6
7
8
9
10
11
#Enter a name for the error log file. Otherwise a default name will be used.
log-error=err.log
#Enter a name for the query log file. Otherwise a default name will be used.
#log=
#Enter a name for the slow query log file. Otherwise a default name will be
used.
#log-slow-queries=
#Enter a name for the update log file. Otherwise a default name will be used.
#log-update=
#Enter a name for the binary log. Otherwise a default name will be used.
#log-bin=

在linux root下重启mysqld服务：service mysqld restart

二进制日志

二进制日志(BINLOG)记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言) 语句，但是不包括数据查询语句。语句以“事件”的形式保存，它描述了数据的更改过程。 此日志对于灾难时的数据恢复起着极其重要的作用。

1、启用和配置二进制日志

要启用 binlog，需要在 MySQL 配置文件 my.cnf（或 my.ini）中添加以下配置：

1
2
[mysqld]
log-bin=mysql-bin
  • log-bin：指定二进制日志文件的前缀名（例如 mysql-bin）。MySQL 会创建一系列带编号的文件，如 mysql-bin.000001。
  • server-id：在启用复制时，必须为每个 MySQL 实例设置唯一的 server-id。
  • expire_logs_days：设置 binlog 文件的过期时间（以天为单位）。如设置为 7，那么超过 7 天的 binlog 会被自动删除。

2、二进制日志格式

MySQL 提供了三种 binlog 格式：

  • STATEMENT（语句模式）：记录 SQL 语句的执行，主服务器记录执行的 SQL 语句，从服务器重复执行这些 SQL 语句来保持同步。可能存在“SQL 语句不一致”的问题，尤其是有非确定性查询时。
  • ROW（行模式）：记录每一行数据的具体变化（例如哪个字段被修改、修改成了什么值）。该模式最为精确，能够保证主从一致性，但日志文件的体积较大。
  • MIXED（混合模式）：结合了 STATEMENT 和 ROW 模式。对于确定性语句（如没有副作用的 INSERT），使用 STATEMENT 模式；对于可能不一致的语句，则使用 ROW 模式。

可以在 MySQL 配置文件中设置 binlog 格式：

1
2
[mysqld]
binlog_format = ROW

3、查看二进制日志文件

1
2
show binary logs;
show master logs;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000021 |       157 | No        |
| binlog.000022 |       157 | No        |
| binlog.000023 |       157 | No        |
| binlog.000024 |       201 | No        |
| binlog.000025 |       157 | No        |
| binlog.000026 |       157 | No        |
| binlog.000027 |     24566 | No        |
| binlog.000028 |      2991 | No        |
| binlog.000029 |      6672 | No        |
| binlog.000030 |       180 | No        |
| binlog.000031 |       180 | No        |
| binlog.000032 |      2028 | No        |
| binlog.000033 |       854 | No        |
| binlog.000034 |      2992 | No        |
| binlog.000035 | 104857809 | No        |
| binlog.000036 | 104857839 | No        |
| binlog.000037 | 104858229 | No        |
| binlog.000038 | 104858037 | No        |
| binlog.000039 | 104858047 | No        |
| binlog.000040 | 104858047 | No        |
| binlog.000041 | 104858057 | No        |
| binlog.000042 | 104858061 | No        |
| binlog.000043 | 104858277 | No        |
| binlog.000044 | 104858121 | No        |
| binlog.000045 | 104857801 | No        |
| binlog.000046 | 104857801 | No        |
| binlog.000047 |  82853815 | No        |
| binlog.000048 |      5534 | No        |
| binlog.000049 |       157 | No        |
| binlog.000050 |       504 | No        |
| binlog.000051 |       180 | No        |
| binlog.000052 |       180 | No        |
| binlog.000053 |       826 | No        |
| binlog.000054 |       201 | No        |
| binlog.000055 |       157 | No        |
| binlog.000056 |      1791 | No        |
| binlog.000057 |       201 | No        |
| binlog.000058 |       157 | No        |
| binlog.000059 |       971 | No        |
| binlog.000060 |       201 | No        |
| binlog.000061 |      2140 | No        |
| binlog.000062 |       201 | No        |
| binlog.000063 |       157 | No        |
| binlog.000064 |       551 | No        |
| binlog.000065 |       157 | No        |
| binlog.000066 |       599 | No        |
| binlog.000067 |       157 | No        |
| binlog.000068 |       157 | No        |
| binlog.000069 |       157 | No        |
| binlog.000070 |       201 | No        |
| binlog.000071 |       460 | No        |
| binlog.000072 |       157 | No        |
| binlog.000073 |       201 | No        |
| binlog.000074 |       157 | No        |
| binlog.000075 |       157 | No        |
| binlog.000076 |       201 | No        |
| binlog.000077 |       157 | No        |
| binlog.000078 |       201 | No        |
| binlog.000079 |       201 | No        |
| binlog.000080 |       157 | No        |
+---------------+-----------+-----------+
60 rows in set (0.01 sec)

通过mysqlbinlog工具（mysql原生自带的工具）可以快速解析大量的binlog日志文件

1
shell> mysqlbinlog mysql-bin.000001

PixPin_2024-12-04_17-20-31 PixPin_2024-12-04_17-21-01

  • 可以看见，图片下面有个建表语句

    1
    2
    3
    4
    5
    6
    create table user(
        id int unsigned primary key Auto_increment comment "id",
        nikename varchar(50) unique not null comment "昵称",
        age tinyint unsigned not null default 18,
        sex enum('male','female')
    )

  • 上面还有个at 810,810表示position,我们可以指定从哪个position开始恢复数据和结束数据

  • at 810下面是时间，我们同样可以指定时间开始恢复数据

4、数据恢复

1
2
3
shell> mysqlbinlog --no-defaults --database=school --base64-output=decode-rows -v --start-datetime='2021-05-01 00:00:00' --stop-datetime='2021-05-10 00:00:00' mysql-bin.000001 | more
    
shell> mysqlbinlog --start-position=775 --stop-position=1410 mysql-bin.000003 |mysql -u root -p

例如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
# at 612
#241204 17:46:14 server id 1  end_log_pos 726 CRC32 0x274047b7  Query   thread_id=15    exec_time=0     error_code=0    Xid = 63
SET TIMESTAMP=1733305574/*!*/;
/*!80016 SET @@session.default_table_encryption=0*//*!*/;
create database testcc
/*!*/;
# at 726
#241204 17:46:56 server id 1  end_log_pos 803 CRC32 0xa2f57962  Anonymous_GTID  last_committed=3        sequence_number=4       rbr_only=no     original_committed_timestamp=1733305617157030       immediate_commit_timestamp=1733305617157030     transaction_length=204
# original_commit_timestamp=1733305617157030 (2024-12-04 17:46:57.157030 CST)
# immediate_commit_timestamp=1733305617157030 (2024-12-04 17:46:57.157030 CST)
/*!80001 SET @@session.original_commit_timestamp=1733305617157030*//*!*/;
/*!80014 SET @@session.original_server_version=80040*//*!*/;
/*!80014 SET @@session.immediate_server_version=80040*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 803
#241204 17:46:56 server id 1  end_log_pos 930 CRC32 0x07dd25eb  Query   thread_id=15    exec_time=1     error_code=0    Xid = 68
use `testcc`/*!*/;
SET TIMESTAMP=1733305616/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table user(name varchar(20))
/*!*/;
# at 930
#241204 17:47:17 server id 1  end_log_pos 1009 CRC32 0x57b8a615         Anonymous_GTID  last_committed=4        sequence_number=5       rbr_only=yesoriginal_committed_timestamp=1733305637724198   immediate_commit_timestamp=1733305637724198     transaction_length=294
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1733305637724198 (2024-12-04 17:47:17.724198 CST)
# immediate_commit_timestamp=1733305637724198 (2024-12-04 17:47:17.724198 CST)
/*!80001 SET @@session.original_commit_timestamp=1733305637724198*//*!*/;
/*!80014 SET @@session.original_server_version=80040*//*!*/;
/*!80014 SET @@session.immediate_server_version=80040*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1009
#241204 17:47:17 server id 1  end_log_pos 1086 CRC32 0xafff30b0         Query   thread_id=15    exec_time=0     error_code=0
SET TIMESTAMP=1733305637/*!*/;
BEGIN
/*!*/;
# at 1086
#241204 17:47:17 server id 1  end_log_pos 1142 CRC32 0xb84eb2b4         Table_map: `testcc`.`user` mapped to number 97
# has_generated_invisible_primary_key=0
# at 1142
#241204 17:47:17 server id 1  end_log_pos 1193 CRC32 0x9bd9ee20         Write_rows: table id 97 flags: STMT_END_F

BINLOG '
JSVQZxMBAAAAOAAAAHYEAAAAAGEAAAAAAAEABnRlc3RjYwAEdXNlcgABDwJQAAECA/z/ALSyTrg=
JSVQZx4BAAAAMwAAAKkEAAAAAGEAAAAAAAEAAgAB/wAIemhhbmdzYW4ABGxpc2kg7tmb
'/*!*/;
# at 1193
#241204 17:47:17 server id 1  end_log_pos 1224 CRC32 0x7ffbc10c         Xid = 69
COMMIT/*!*/;
# at 1224
#241204 17:48:41 server id 1  end_log_pos 1301 CRC32 0x055f777d         Anonymous_GTID  last_committed=5        sequence_number=6       rbr_only=nooriginal_committed_timestamp=1733305721708032    immediate_commit_timestamp=1733305721708032     transaction_length=187
# original_commit_timestamp=1733305721708032 (2024-12-04 17:48:41.708032 CST)
# immediate_commit_timestamp=1733305721708032 (2024-12-04 17:48:41.708032 CST)
/*!80001 SET @@session.original_commit_timestamp=1733305721708032*//*!*/;
/*!80014 SET @@session.original_server_version=80040*//*!*/;
/*!80014 SET @@session.immediate_server_version=80040*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1301
#241204 17:48:41 server id 1  end_log_pos 1411 CRC32 0x7d2caeb7         Query   thread_id=15    exec_time=0     error_code=0    Xid = 73
SET TIMESTAMP=1733305721/*!*/;
drop database testcc
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

我创建了一个testcc数据库，然后删除，我需要恢复这个数据库

1
2
root@zjz-VMware-Virtual-Platform:~# mysqlbinlog --start-position=612 --stop-position=1301 /var/lib/mysql/binlog.000080 | mysql -u root -p
Enter password:

5、数据备份

使用mysql自带的mysqldump工具导出数据

1
2
3
4
5
6
root@zjz-VMware-Virtual-Platform:~# mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
root@zjz-VMware-Virtual-Platform:~#

备份testcc的user表

1
mysqldump -u root -p testcc user > ~/user.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
root@zjz-VMware-Virtual-Platform:~# mysqldump -u root -p testcc user > ~/user.sql
Enter password:
root@zjz-VMware-Virtual-Platform:~# ls
snap  user.sql
root@zjz-VMware-Virtual-Platform:~# cat user.sql
-- MySQL dump 10.13  Distrib 8.0.40, for Linux (x86_64)
--
-- Host: localhost    Database: testcc
-- ------------------------------------------------------
-- Server version       8.0.40-0ubuntu0.24.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user` (
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user`
--

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES ('zhangsan'),('lisi');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-12-04 17:55:54
root@zjz-VMware-Virtual-Platform:~#

还有常用的导出：

1
mysql -u root -p -D school -e "select *from user where age>18" > ~/user.txt

PixPin_2024-12-04_18-14-47

6、导入数据

加载数据,假设在zjz用户下有一份sql数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
zjz@zjz-VMware-Virtual-Platform:~$ cat user.sql
-- MySQL dump 10.13  Distrib 8.0.40, for Linux (x86_64)
--
-- Host: localhost    Database: testcc
-- ------------------------------------------------------
-- Server version       8.0.40-0ubuntu0.24.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `user`
--
create database testcc;
use testcc;

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user` (
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user`
--

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES ('zhangsan'),('lisi');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-12-04 17:55:54
zjz@zjz-VMware-Virtual-Platform:~$

导入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
mysql> SOURCE /home/zjz/user.sql;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.04 sec)

Database changed
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.08 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| chat               |
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
| test               |
| testcc             |
+--------------------+
8 rows in set (0.00 sec)

mysql>
数据库

MySQL-存储引擎

各存储引擎区别

PixPin_2024-11-14_16-02-48

  1. 锁机制：表示数据库在并发请求访问的时候，多个事务在操作时，并发操作的粒度。
  2. B-树索引和哈希索引：主要是加速SQL的查询速度。
  3. 外键：子表的字段依赖父表的主键，设置两张表的依赖关系。
  4. 事务：多个SQL语句，保证它们共同执行的原子操作，要么成功，要么失败，不能只成功一部分，失败 需要回滚事务。
  5. 索引缓存和数据缓存：和MySQL Server的查询缓存相关，在没有对数据和索引做修改之前，重复查询 可以不用进行磁盘I/O（数据库的性能提升，目的是为了减少磁盘I/O操作来提升数据库访问效率），读 取上一次内存中查询的缓存就可以了。

MyISAM 存储数据的特点

  1. 每个表由三个文件组成：

    • .frm文件：存储表的结构定义。
    • .MYD文件：存储数据。
    • .MYI文件：存储索引。

    数据和索引是存储在不同的文件中的，表的数据文件和索引文件是独立的。

innodb 存放数据特点

  1. 每张表由两个文件组成
    • 数据和索引都存放在同一个文件中 .ibd
    • 表结构单独存放在一个文件中 .frm
数据库

MySQL-limit

Clip_2024-11-11_11-53-02

从图中我们可以看出，我们使用explain查看了sql的执行计划，由于我们将name字段设置为unique,所以MySQL自动为name字段设置了索引，所以只查询了一行，即rows等于1，但是，由于age没有设置索引，尽管age=13在第二行，他也是查询了全部行,即整表查询，这就导致了效率很低，如果不使用索引，那么该如何提高搜索速度呢，答案就是使用limit，LIMIT 子句限制返回的行数，减少MySQL的处理量

Clip_2024-11-11_11-59-04

如图所示，limit在使用explain检查时，貌似并没有起作用，这是因为explain只是一个估算值，他并不能检查MySQL内部对sql的优化，让我们增加数据量就可以很直观的发现差异

创建测试表t_user

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show create table t_user\G
*************************** 1. row ***************************
       Table: t_user
Create Table: CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.06 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#使用存储过程插入数据
DELIMITER $
CREATE PROCEDURE add_t_user(IN n INT)
BEGIN
    DECLARE i INT;
    SET i = 0;

    WHILE i < n
        DO
            INSERT INTO t_user VALUES (NULL, CONCAT(i + 1, '@gmail.com'), i + 1);
            SET i = i + 1;
        END WHILE;
END $
DELIMITER ;
CALL add_t_user(2000000)

测试结果如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
mysql> select count(*) from t_user;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (0.31 sec)

mysql> select * from t_user where password = '1000000';
+---------+-------------------+----------+
| id      | email             | password |
+---------+-------------------+----------+
| 1309956 | 1000000@gmail.com | 1000000  |
+---------+-------------------+----------+
1 row in set (1.24 sec)

mysql> select * from t_user where password = '1000000' limit 1;
+---------+-------------------+----------+
| id      | email             | password |
+---------+-------------------+----------+
| 1309956 | 1000000@gmail.com | 1000000  |
+---------+-------------------+----------+
1 row in set (0.84 sec)

mysql> select * from t_user limit 1000000,10;
+---------+------------------+----------+
| id      | email            | password |
+---------+------------------+----------+
| 1000001 | 154985@gmail.com | 154985   |
| 1000002 | 845017@gmail.com | 845017   |
| 1000003 | 154986@gmail.com | 154986   |
| 1000004 | 845018@gmail.com | 845018   |
| 1000005 | 154987@gmail.com | 154987   |
| 1000006 | 845019@gmail.com | 845019   |
| 1000007 | 154988@gmail.com | 154988   |
| 1000008 | 845020@gmail.com | 845020   |
| 1000009 | 154989@gmail.com | 154989   |
| 1000010 | 845021@gmail.com | 845021   |
+---------+------------------+----------+
10 rows in set (0.56 sec)

mysql>

可以发现，limit的查询速度明显比普通查询速度快得多，但是我们会发现这并不稳定，因为limit偏移是需要时间的，随着数据量的增加，limit偏移所耗费的时间只越来越长，所以，我们一般的写法如下面这种,其实是有缺陷的，我们肯定是不希望limit还要遍历前面的数据，我们需要消除前面偏移的时间。

1
select * from t_user limit (pageno - 1) * pagenum, pagenum

所以，我们应该改为如下这种，当然，并不一定是id，我们使用id是因为id有索引，还可以使用其他有索引的字段

1
select * from t_user where id > 上一页最后一条数据的id值 limit 10;

测试结果如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> select * from t_user limit 1000000,10;
+---------+------------------+----------+
| id      | email            | password |
+---------+------------------+----------+
| 1000001 | 154985@gmail.com | 154985   |
| 1000002 | 845017@gmail.com | 845017   |
| 1000003 | 154986@gmail.com | 154986   |
| 1000004 | 845018@gmail.com | 845018   |
| 1000005 | 154987@gmail.com | 154987   |
| 1000006 | 845019@gmail.com | 845019   |
| 1000007 | 154988@gmail.com | 154988   |
| 1000008 | 845020@gmail.com | 845020   |
| 1000009 | 154989@gmail.com | 154989   |
| 1000010 | 845021@gmail.com | 845021   |
+---------+------------------+----------+
10 rows in set (0.56 sec)

mysql> select * from t_user where id > 1000000 limit 10;
+---------+------------------+----------+
| id      | email            | password |
+---------+------------------+----------+
| 1000001 | 154985@gmail.com | 154985   |
| 1000002 | 845017@gmail.com | 845017   |
| 1000003 | 154986@gmail.com | 154986   |
| 1000004 | 845018@gmail.com | 845018   |
| 1000005 | 154987@gmail.com | 154987   |
| 1000006 | 845019@gmail.com | 845019   |
| 1000007 | 154988@gmail.com | 154988   |
| 1000008 | 845020@gmail.com | 845020   |
| 1000009 | 154989@gmail.com | 154989   |
| 1000010 | 845021@gmail.com | 845021   |
+---------+------------------+----------+
10 rows in set (0.01 sec)

这样，我们查询的速度就很稳定了。

数据库

MySQL-内连接inner join 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
mysql> use school;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from exams;
+-----+-----+------------+-------+
| uid | cid | time       | score |
+-----+-----+------------+-------+
|   1 |   1 | 2021-04-09 |    99 |
|   1 |   2 | 2021-04-10 |    80 |
|   2 |   2 | 2021-04-10 |    90 |
|   2 |   3 | 2021-04-12 |    85 |
|   3 |   1 | 2021-04-09 |    56 |
|   3 |   2 | 2021-04-10 |    93 |
|   3 |   3 | 2021-04-12 |    89 |
|   3 |   4 | 2021-04-11 |   100 |
|   4 |   4 | 2021-04-11 |    99 |
|   5 |   2 | 2021-04-10 |    59 |
|   5 |   3 | 2021-04-12 |    94 |
|   5 |   4 | 2021-04-11 |    95 |
+-----+-----+------------+-------+
12 rows in set (0.01 sec)

mysql> select * from course;
+-----+-----------------+--------+
| cid | cname           | credit |
+-----+-----------------+--------+
|   1 | C++基础课程     |      5 |
|   2 | C++高级课程     |     10 |
|   3 | C++项目开发     |      8 |
|   4 | C++算法课程     |     12 |
+-----+-----------------+--------+
4 rows in set (0.01 sec)

mysql> select * from student;
+-----+----------+-----+-----+
| uid | name     | age | sex |
+-----+----------+-----+-----+
|   1 | zhangsan |  18 | M   |
|   2 | gaoyang  |  20 | W   |
|   3 | chenwei  |  22 | M   |
|   4 | linfeng  |  21 | W   |
|   5 | liuxiang |  19 | W   |
+-----+----------+-----+-----+
5 rows in set (0.01 sec)

mysql> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| uid   | int unsigned     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)      | NO   |     | NULL    |                |
| age   | tinyint unsigned | NO   |     | NULL    |                |
| sex   | enum('M','W')    | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> desc course;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| cid    | int unsigned     | NO   | PRI | NULL    | auto_increment |
| cname  | varchar(50)      | NO   |     | NULL    |                |
| credit | tinyint unsigned | NO   |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> desc exams;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| uid   | int unsigned | NO   | PRI | NULL    |       |
| cid   | int unsigned | NO   | PRI | NULL    |       |
| time  | date         | NO   |     | NULL    |       |
| score | float        | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql>

现在需要查看zhangsan同学的详细信息，选修课程id为2的信息和对应的成绩，需要进行多表查询

分析：

  1. zhangsan同学的详细信息

    1
    select uid,name,age,sex from student where uid = 1;

  2. zhangsan选修的课程信息

    1
    select cid,cname,credit from course where cid = 2;

  3. 成绩信息

    1
    select uid,cid,time,score from exams where uid = 1 and cid = 2;

现在，我们需要将上面的三条sql语句结合起来，进行多表查询.

首先，我们需要知道的是，内连接是需要区分大表和小表的，通过数据量进行区分，数据量大的就为大表，数据量小的就为小表，小表总是先进行全局扫描，然后拿着扫描得到的数据到大表中匹对（所以，小表使用索引的意义是不大的），最后得到需要的数据

下面是内连接的常用方式：

1
2
3
select emp.name dept.name from emp 
inner join dept on emp.dept_id = dept.id
inner join ...;

需要注意的点是：inner join左边的表并不是随意取的，假设将表出现的顺序按A,B,C…这样的顺序起别名，例如这里的emp别名为A,后面的dept表别名为B，后面以此类推，这里的A，必须要能够跟后续出现的表有关联，有联系，例如：A表应该要有B表，C表关联的字段，这样才能建立联系，如有B,C表的id字段，这样才能使用on建立联系。上面的student,course,exams三张表，exams 表中有 uid 字段，可以与 student 表关联；同时，它也有 cid 字段，可以与 course 表关联。因此，exams 表作为起点，可以有效地将 student 和 course 表通过 uid 和 cid 连接起来。所以这里的exams表应该为A表,至于B,C表的顺序并没有要求，SQL 查询优化器在执行查询时会自动决定最优的执行顺序

所以，最终的sql语句如下：

1
2
3
4
select c.uid,c.name,c.age,c.sex, b.cid,b.cname,b.credit,a.time,a.score from exams a
inner join course b on a.cid = b.cid
inner join student c on a.uid = c.uid
where a.uid = 1 and a.cid = 2;

测试结果：

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select c.uid,c.name,c.age,c.sex, b.cid,b.cname,b.credit,a.time,a.score from exams a
    -> inner join course b on a.cid = b.cid
    -> inner join student c on a.uid = c.uid
    -> where a.uid = 1 and a.cid = 2;
+-----+----------+-----+-----+-----+-----------------+--------+------------+-------+
| uid | name     | age | sex | cid | cname           | credit | time       | score |
+-----+----------+-----+-----+-----+-----------------+--------+------------+-------+
|   1 | zhangsan |  18 | M   |   2 | C++高级课程     |     10 | 2021-04-10 |    80 |
+-----+----------+-----+-----+-----+-----------------+--------+------------+-------+
1 row in set (0.02 sec)

mysql>

如果是查询zhangsan同学的详细信息，所有选修课程的信息和对应的成绩，则sql应该如下：

1
2
3
4
select c.uid,c.name,c.age,c.sex, b.cid,b.cname,b.credit,a.time,a.score from exams a
inner join course b on a.cid = b.cid
inner join student c on a.uid = c.uid
where a.uid = 1;

测试结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select c.uid,c.name,c.age,c.sex, b.cid,b.cname,b.credit,a.time,a.score from exams a
    -> inner join course b on a.cid = b.cid
    -> inner join student c on a.uid = c.uid
    -> where a.uid = 1;
+-----+----------+-----+-----+-----+-----------------+--------+------------+-------+
| uid | name     | age | sex | cid | cname           | credit | time       | score |
+-----+----------+-----+-----+-----+-----------------+--------+------------+-------+
|   1 | zhangsan |  18 | M   |   1 | C++基础课程     |      5 | 2021-04-09 |    99 |
|   1 | zhangsan |  18 | M   |   2 | C++高级课程     |     10 | 2021-04-10 |    80 |
+-----+----------+-----+-----+-----+-----------------+--------+------------+-------+
2 rows in set (0.02 sec)

mysql>

如果是要查看课程id为2,且成绩大于等于90的信息

1
2
3
4
select c.uid,c.name,c.age,c.sex, b.cid,b.cname,b.credit,a.time,a.score from exams a
inner join course b on a.cid = b.cid
inner join student c on a.uid = c.uid
where a.cid = 2 and a.score >= 90;

测试结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select c.uid,c.name,c.age,c.sex, b.cid,b.cname,b.credit,a.time,a.score from exams a
    -> inner join course b on a.cid = b.cid
    -> inner join student c on a.uid = c.uid
    -> where a.cid = 2 and a.score >= 90;
+-----+---------+-----+-----+-----+-----------------+--------+------------+-------+
| uid | name    | age | sex | cid | cname           | credit | time       | score |
+-----+---------+-----+-----+-----+-----------------+--------+------------+-------+
|   2 | gaoyang |  20 | W   |   2 | C++高级课程     |     10 | 2021-04-10 |    90 |
|   3 | chenwei |  22 | M   |   2 | C++高级课程     |     10 | 2021-04-10 |    93 |
+-----+---------+-----+-----+-----+-----------------+--------+------------+-------+
2 rows in set (0.00 sec)

mysql>

通过课程分组，计算每个课程的选课人数,按降序排序

1
2
3
4
select b.cid,b.cname,b.credit,count(*) number from exams a
inner join course b on a.cid = b.cid
group by a.cid
order by number desc;

测试结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select b.cid,b.cname,b.credit,count(*) number from exams a
    -> inner join course b on a.cid = b.cid
    -> group by a.cid
    -> order by number desc;
+-----+-----------------+--------+--------+
| cid | cname           | credit | number |
+-----+-----------------+--------+--------+
|   2 | C++高级课程     |     10 |      4 |
|   3 | C++项目开发     |      8 |      3 |
|   4 | C++算法课程     |     12 |      3 |
|   1 | C++基础课程     |      5 |      2 |
+-----+-----------------+--------+--------+
4 rows in set (0.00 sec)

mysql>

通过课程分组，计算每个课程成绩大于等于90分的选课人数,按降序排序

1
2
3
4
5
select b.cid,b.cname,b.credit,count(*) number from exams a
inner join course b on a.cid = b.cid
where a.score >= 90
group by a.cid
order by number desc;

测试结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select b.cid,b.cname,b.credit,count(*) number from exams a
    -> inner join course b on a.cid = b.cid
    -> where a.score >= 90
    -> group by a.cid
    -> order by number desc;
+-----+-----------------+--------+--------+
| cid | cname           | credit | number |
+-----+-----------------+--------+--------+
|   4 | C++算法课程     |     12 |      3 |
|   2 | C++高级课程     |     10 |      2 |
|   1 | C++基础课程     |      5 |      1 |
|   3 | C++项目开发     |      8 |      1 |
+-----+-----------------+--------+--------+
4 rows in set (0.01 sec)

mysql>

cid=2这门课程考试成绩的最高分的学生信息和课程信息

1
2
3
4
5
6
7
8
9
10
select c.uid, c.name, c.age, c.sex, b.cid, b.cname, b.credit, a.score
from exams a
inner join course b on a.cid = b.cid
inner join student c on a.uid = c.uid
where a.cid = 2
  and a.score = (
      select max(score)
      from exams
      where cid = 2
  );

测试结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> select c.uid, c.name, c.age, c.sex, b.cid, b.cname, b.credit, a.score
(score)
      from exams
      where cid = 2
  );    -> from exams a
    -> inner join course b on a.cid = b.cid
    -> inner join student c on a.uid = c.uid
    -> where a.cid = 2
    ->   and a.score = (
    ->       select max(score)
    ->       from exams
    ->       where cid = 2
    ->   );
+-----+---------+-----+-----+-----+-----------------+--------+-------+
| uid | name    | age | sex | cid | cname           | credit | score |
+-----+---------+-----+-----+-----+-----------------+--------+-------+
|   3 | chenwei |  22 | M   |   2 | C++高级课程     |     10 |    93 |
+-----+---------+-----+-----+-----+-----------------+--------+-------+
1 row in set (0.12 sec)

+-------+
| score |
+-------+
|    80 |
|    90 |
|    93 |
|    59 |
+-------+
4 rows in set (0.00 sec)

mysql>

每门课程考试的平均成绩+课程信息

1
2
3
4
select b.cid,b.cname,b.credit,ROUND(AVG(a.score), 2) average_score from exams a
inner join course b on a.cid = b.cid
group by a.cid
order by average_score desc;

测试结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select b.cid,b.cname,b.credit,ROUND(AVG(a.score), 2) average_score from exams a
    -> inner join course b on a.cid = b.cid
    -> group by a.cid
    -> order by average_score desc;
+-----+-----------------+--------+---------------+
| cid | cname           | credit | average_score |
+-----+-----------------+--------+---------------+
|   4 | C++算法课程     |     12 |            98 |
|   3 | C++项目开发     |      8 |         89.33 |
|   2 | C++高级课程     |     10 |          80.5 |
|   1 | C++基础课程     |      5 |          77.5 |
+-----+-----------------+--------+---------------+
4 rows in set (0.01 sec)

mysql>

厉害点的用法：

在学习limit时，我们知道limit的偏移是需要时间的，我们知道可以使用下面这种方式过滤调偏移的时间，这种方式是最理想的

1
select * from t_user where id > 上一页最后一条数据的id值 limit 10;

问题是，我们有时候并不知道最后一条数据的索引值导致无法使用where,那么我们该如何提高搜索效率呢？

例如下面这种情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
mysql> select count(*) from t_user;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+
1 row in set (3.07 sec)

mysql> desc t_user;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| email    | varchar(255) | YES  |     | NULL    |                |
| password | varchar(255) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.11 sec)

mysql> select * from t_user limit 1000000, 10;
+---------+------------------+----------+
| id      | email            | password |
+---------+------------------+----------+
| 1000001 | 154985@gmail.com | 154985   |
| 1000002 | 845017@gmail.com | 845017   |
| 1000003 | 154986@gmail.com | 154986   |
| 1000004 | 845018@gmail.com | 845018   |
| 1000005 | 154987@gmail.com | 154987   |
| 1000006 | 845019@gmail.com | 845019   |
| 1000007 | 154988@gmail.com | 154988   |
| 1000008 | 845020@gmail.com | 845020   |
| 1000009 | 154989@gmail.com | 154989   |
| 1000010 | 845021@gmail.com | 845021   |
+---------+------------------+----------+
10 rows in set (0.78 sec)

mysql> select id from t_user limit 1000000, 10;
+---------+
| id      |
+---------+
| 1000001 |
| 1000002 |
| 1000003 |
| 1000004 |
| 1000005 |
| 1000006 |
| 1000007 |
| 1000008 |
| 1000009 |
| 1000010 |
+---------+
10 rows in set (0.27 sec)

mysql>

总所周知，select的字段越多，查询的速度越慢，例如这里只查询id和查询全部数据，两种的时间差是非常明显的。在这个优化查询逻辑的例子中，所以我们的目标是：在查询全部字段的情况下，实现与仅查询 id 相似的效率。通过使用 INNER JOIN 和子查询，我们可以利用索引来加速查询，减少 LIMIT 偏移带来的时间开销。

我们的想法：

  1. 利用索引查询 ID：首先，通过 LIMIT 和 OFFSET 来快速获取目标记录的 id 列表。因为 id 列通常有索引，所以仅查询 id 的效率会非常高，即使偏移量很大（例如 LIMIT 1000000, 10）。
  2. 使用 INNER JOIN 加速主表查询：然后，我们将这些 id 列表作为一个小的临时表（即子查询结果），用 INNER JOIN 与原表进行连接。由于我们只连接到已知的 id 集合，这个过程非常高效，并且可以避免扫描大数据集。
  3. 只读取必要的数据行：通过这种方法，我们能够在查询全部字段的同时，仅从原表中检索那些与 id 列表匹配的行，避免了全表扫描。
1
2
select * from t_user a
inner join (select id from t_user limit 1000000,10) b on a.id = b.id;

测试结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select * from t_user a
    -> inner join (select id from t_user limit 1000000,10) b on a.id = b.id;
+---------+------------------+----------+---------+
| id      | email            | password | id      |
+---------+------------------+----------+---------+
| 1000001 | 154985@gmail.com | 154985   | 1000001 |
| 1000002 | 845017@gmail.com | 845017   | 1000002 |
| 1000003 | 154986@gmail.com | 154986   | 1000003 |
| 1000004 | 845018@gmail.com | 845018   | 1000004 |
| 1000005 | 154987@gmail.com | 154987   | 1000005 |
| 1000006 | 845019@gmail.com | 845019   | 1000006 |
| 1000007 | 154988@gmail.com | 154988   | 1000007 |
| 1000008 | 845020@gmail.com | 845020   | 1000008 |
| 1000009 | 154989@gmail.com | 154989   | 1000009 |
| 1000010 | 845021@gmail.com | 845021   | 1000010 |
+---------+------------------+----------+---------+
10 rows in set (0.30 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
mysql> explain select * from t_user a inner join (select id from t_user limit 1000000,10) b on a.id = b.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 1000010 |   100.00 | NULL        |
|  1 | PRIMARY     | a          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | b.id |       1 |   100.00 | NULL        |
|  2 | DERIVED     | t_user     | NULL       | index  | NULL          | PRIMARY | 4       | NULL | 1977287 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
3 rows in set, 1 warning (0.07 sec)

mysql>

通过explain我们可以发现，这条sql首先进行了小表的扫描，扫描的行数大概是1000000行，将返回的十条数据作为一张临时表,然后提供给主表查询数据，主表使用索引的方式查询

inner jorn的过滤条件放在where后面和放在on连接条件里的效果是一致的,MySQL可能将and优化为where以使用索引

1
2
3
4
5
6
7
select a.*,b.* from exams a inner join
student b on a.uid = b.uid
and b.uid = 3;

select a.*,b.* from exams a inner join
student b on a.uid = b.uid
where b.uid = 3;

测试结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
mysql> select a.*,b.* from exams a inner join
    -> student b on a.uid = b.uid
    -> where b.uid = 3;
+-----+-----+------------+-------+-----+---------+-----+-----+
| uid | cid | time       | score | uid | name    | age | sex |
+-----+-----+------------+-------+-----+---------+-----+-----+
|   3 |   1 | 2021-04-09 |    56 |   3 | chenwei |  22 | M   |
|   3 |   2 | 2021-04-10 |    93 |   3 | chenwei |  22 | M   |
|   3 |   3 | 2021-04-12 |    89 |   3 | chenwei |  22 | M   |
|   3 |   4 | 2021-04-11 |   100 |   3 | chenwei |  22 | M   |
+-----+-----+------------+-------+-----+---------+-----+-----+
4 rows in set (0.00 sec)

mysql> select a.*,b.* from exams a inner join
    -> student b on a.uid = b.uid
    -> and b.uid = 3;
+-----+-----+------------+-------+-----+---------+-----+-----+
| uid | cid | time       | score | uid | name    | age | sex |
+-----+-----+------------+-------+-----+---------+-----+-----+
|   3 |   1 | 2021-04-09 |    56 |   3 | chenwei |  22 | M   |
|   3 |   2 | 2021-04-10 |    93 |   3 | chenwei |  22 | M   |
|   3 |   3 | 2021-04-12 |    89 |   3 | chenwei |  22 | M   |
|   3 |   4 | 2021-04-11 |   100 |   3 | chenwei |  22 | M   |
+-----+-----+------------+-------+-----+---------+-----+-----+
4 rows in set (0.00 sec)
    
mysql> explain select a.*,b.* from exams a inner join student b on a.uid = b.uid where b.uid = 3;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | b     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | a     | NULL       | ref   | PRIMARY       | PRIMARY | 4       | const |    4 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)

mysql> explain select a.*,b.* from exams a inner join student b on a.uid = b.uid and b.uid = 3;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | b     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | a     | NULL       | ref   | PRIMARY       | PRIMARY | 4       | const |    4 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.06 sec)
数据库

MySQL-分组group by 

1
2
3
mysql> select name,age from user group by age;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.user.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql>

使用group by进行分组时，不应该查询别的字段，例如这里的name,因为MySQL不知道要显示哪个用户，我们只需要查询分组的字段即可，或者进行一些统计计算，如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
mysql> select * from user;
+----+----------+-----+-----+
| id | name     | age | sex |
+----+----------+-----+-----+
|  1 | zhangsan |  16 | W   |
|  2 | lisi     |  21 | M   |
|  3 | laoliu   |  24 | W   |
|  4 | jianzhe  |  16 | W   |
|  5 | wangwei  |  21 | M   |
+----+----------+-----+-----+
5 rows in set (0.00 sec)

mysql> select age from user group by age;
+-----+
| age |
+-----+
|  16 |
|  21 |
|  24 |
+-----+
3 rows in set (0.00 sec)

mysql> select age,count(age) from user group by age;
+-----+------------+
| age | count(age) |
+-----+------------+
|  16 |          2 |
|  21 |          2 |
|  24 |          1 |
+-----+------------+
3 rows in set (0.00 sec)

mysql> select age,sum(age) from user group by age;
+-----+----------+
| age | sum(age) |
+-----+----------+
|  16 |       32 |
|  21 |       42 |
|  24 |       24 |
+-----+----------+
3 rows in set (0.02 sec)

mysql> select age,sum(age) from user group by age having age > 20;
+-----+----------+
| age | sum(age) |
+-----+----------+
|  21 |       42 |
|  24 |       24 |
+-----+----------+
2 rows in set (0.00 sec)

mysql> select age,sum(age) from user where age > 20 group by age;#推荐使用where,age有索引的话where可以使用索引
+-----+----------+
| age | sum(age) |
+-----+----------+
|  21 |       42 |
|  24 |       24 |
+-----+----------+
2 rows in set (0.00 sec)

mysql> select age,sex,count(*) from user group by age,sex;
+-----+-----+----------+
| age | sex | count(*) |
+-----+-----+----------+
|  16 | W   |        2 |
|  21 | M   |        2 |
|  24 | W   |        1 |
+-----+-----+----------+
3 rows in set (0.00 sec)

mysql>

mysql> select age,sex,count(id) from user group by age,sex order by age desc; 
+-----+-----+-----------+
| age | sex | count(id) |
+-----+-----+-----------+
|  24 | W   |         1 |
|  21 | M   |         2 |
|  16 | W   |         2 |
+-----+-----+-----------+
3 rows in set (0.01 sec)

mysql>

其实，从上面可以看出，其实order by内部实际上是进行了排序的，我们使用explain查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> explain select age from user group by age;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select name from user group by name;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | name          | name | 202     | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql>

可以看到，在使用没有索引的字段排序时，MySQL使用的是Using temporary，使用临时表进行排序，而使用有索引的name排序时，是使用索引树进行排序的，显然，group by在分组时的效率与索引有关系

数据库

MySQL-外连接

左右连接并不区分大小表，left join会先整表扫描左边的表，再扫描右边的表；同理right join会先扫描右边的表，再扫描左边的表；内连接的话是不确定的，会根据数据量，先扫描整表扫描小表，在扫描右表。

外连接的过滤条件放在where和on后边是不同的，中间的过滤条件应该放在on连接，确保使用的是外连接，最后的过滤应该放在where后边，对结果集进行的进一步筛选。像搜索不存在，带有一定限制条件的场景，限制条件一般加到on后边，where一般判断null即可。这个过滤条件的位置一定要注意

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
mysql> select * from student;
+-----+----------+-----+-----+
| uid | name     | age | sex |
+-----+----------+-----+-----+
|   1 | zhangsan |  18 | M   |
|   2 | gaoyang  |  20 | W   |
|   3 | chenwei  |  22 | M   |
|   4 | linfeng  |  21 | W   |
|   5 | liuxiang |  19 | W   |
|   6 | weiwie   |  20 | M   |
+-----+----------+-----+-----+
6 rows in set (0.00 sec)

mysql> select * from exams;
+-----+-----+------------+-------+
| uid | cid | time       | score |
+-----+-----+------------+-------+
|   1 |   1 | 2021-04-09 |    99 |
|   1 |   2 | 2021-04-10 |    80 |
|   2 |   2 | 2021-04-10 |    90 |
|   2 |   3 | 2021-04-12 |    85 |
|   3 |   1 | 2021-04-09 |    56 |
|   3 |   2 | 2021-04-10 |    93 |
|   3 |   3 | 2021-04-12 |    89 |
|   3 |   4 | 2021-04-11 |   100 |
|   4 |   4 | 2021-04-11 |    99 |
|   5 |   2 | 2021-04-10 |    59 |
|   5 |   3 | 2021-04-12 |    94 |
|   5 |   4 | 2021-04-11 |    95 |
+-----+-----+------------+-------+
12 rows in set (0.00 sec)

mysql> select * from course;
+-----+-----------------+--------+
| cid | cname           | credit |
+-----+-----------------+--------+
|   1 | C++基础课程     |      5 |
|   2 | C++高级课程     |     10 |
|   3 | C++项目开发     |      8 |
|   4 | C++算法课程     |     12 |
+-----+-----------------+--------+
4 rows in set (0.02 sec)

mysql> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| uid   | int unsigned     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50)      | NO   |     | NULL    |                |
| age   | tinyint unsigned | NO   |     | NULL    |                |
| sex   | enum('M','W')    | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.10 sec)

mysql> desc exams;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| uid   | int unsigned | NO   | PRI | NULL    |       |
| cid   | int unsigned | NO   | PRI | NULL    |       |
| time  | date         | NO   |     | NULL    |       |
| score | float        | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc course;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| cid    | int unsigned     | NO   | PRI | NULL    | auto_increment |
| cname  | varchar(50)      | NO   |     | NULL    |                |
| credit | tinyint unsigned | NO   |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql>

一、left join

1
select a.*,b.* from student a left join exams b on a.uid = b.uid
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select a.*,b.* from student a left join exams b on a.uid = b.uid;
+-----+----------+-----+-----+------+------+------------+-------+
| uid | name     | age | sex | uid  | cid  | time       | score |
+-----+----------+-----+-----+------+------+------------+-------+
|   1 | zhangsan |  18 | M   |    1 |    1 | 2021-04-09 |    99 |
|   1 | zhangsan |  18 | M   |    1 |    2 | 2021-04-10 |    80 |
|   2 | gaoyang  |  20 | W   |    2 |    2 | 2021-04-10 |    90 |
|   2 | gaoyang  |  20 | W   |    2 |    3 | 2021-04-12 |    85 |
|   3 | chenwei  |  22 | M   |    3 |    1 | 2021-04-09 |    56 |
|   3 | chenwei  |  22 | M   |    3 |    2 | 2021-04-10 |    93 |
|   3 | chenwei  |  22 | M   |    3 |    3 | 2021-04-12 |    89 |
|   3 | chenwei  |  22 | M   |    3 |    4 | 2021-04-11 |   100 |
|   4 | linfeng  |  21 | W   |    4 |    4 | 2021-04-11 |    99 |
|   5 | liuxiang |  19 | W   |    5 |    2 | 2021-04-10 |    59 |
|   5 | liuxiang |  19 | W   |    5 |    3 | 2021-04-12 |    94 |
|   5 | liuxiang |  19 | W   |    5 |    4 | 2021-04-11 |    95 |
|   6 | weiwie   |  20 | M   | NULL | NULL | NULL       |  NULL |
+-----+----------+-----+-----+------+------+------------+-------+
13 rows in set (0.00 sec)

二、right join

1
mysql> select a.*,b.* from student a right join exams b on a.uid = b.uid;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select a.*,b.* from student a right join exams b on a.uid = b.uid;
+------+----------+------+------+-----+-----+------------+-------+
| uid  | name     | age  | sex  | uid | cid | time       | score |
+------+----------+------+------+-----+-----+------------+-------+
|    1 | zhangsan |   18 | M    |   1 |   1 | 2021-04-09 |    99 |
|    1 | zhangsan |   18 | M    |   1 |   2 | 2021-04-10 |    80 |
|    2 | gaoyang  |   20 | W    |   2 |   2 | 2021-04-10 |    90 |
|    2 | gaoyang  |   20 | W    |   2 |   3 | 2021-04-12 |    85 |
|    3 | chenwei  |   22 | M    |   3 |   1 | 2021-04-09 |    56 |
|    3 | chenwei  |   22 | M    |   3 |   2 | 2021-04-10 |    93 |
|    3 | chenwei  |   22 | M    |   3 |   3 | 2021-04-12 |    89 |
|    3 | chenwei  |   22 | M    |   3 |   4 | 2021-04-11 |   100 |
|    4 | linfeng  |   21 | W    |   4 |   4 | 2021-04-11 |    99 |
|    5 | liuxiang |   19 | W    |   5 |   2 | 2021-04-10 |    59 |
|    5 | liuxiang |   19 | W    |   5 |   3 | 2021-04-12 |    94 |
|    5 | liuxiang |   19 | W    |   5 |   4 | 2021-04-11 |    95 |
+------+----------+------+------+-----+-----+------------+-------+
12 rows in set (0.01 sec)

mysql>

explain 分析：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> explain select a.*,b.* from student a right join exams b on a.uid = b.uid;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref          | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
|  1 | SIMPLE      | b     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL         |   12 |   100.00 | NULL  |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | school.b.uid |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain select a.*,b.* from student a left join exams b on a.uid = b.uid;
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref          | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL    | NULL    | NULL         |    6 |   100.00 | NULL  |
|  1 | SIMPLE      | b     | NULL       | ref  | PRIMARY       | PRIMARY | 4       | school.a.uid |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql>

现在有这么一个情景：我们要查询没有考过试的学生

  1. 我们可以使用子连接

    1
    select * from student where uid not in (select uid from exams);
    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql> select * from student where uid not in (select uid from exams);
    +-----+--------+-----+-----+
    | uid | name   | age | sex |
    +-----+--------+-----+-----+
    |   6 | weiwie |  20 | M   |
    +-----+--------+-----+-----+
    1 row in set (0.00 sec)

    mysql>

    但是子链接有个问题，我们使用not in有时候是使用不到索引的，同时可能会产生中间表，这样效率并不高

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    mysql> explain select * from student where uid not in (select uid from exams);
    +----+-------------+---------+------------+------+---------------+---------+---------+--------------------+------+----------+--------------------------------------+
    | id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref                | rows | filtered | Extra
                   |
    +----+-------------+---------+------------+------+---------------+---------+---------+--------------------+------+----------+--------------------------------------+
    |  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL    | NULL    | NULL               |    6 |   100.00 | NULL
                   |
    |  1 | SIMPLE      | exams   | NULL       | ref  | PRIMARY       | PRIMARY | 4       | school.student.uid |    2 |   100.00 | Using where; Not exists; Using index |
    +----+-------------+---------+------------+------+---------------+---------+---------+--------------------+------+----------+--------------------------------------+
    2 rows in set, 1 warning (0.00 sec)

    mysql>

  2. 使用左连接

    1
    select a.* from student a left join exams b on a.uid = b.uid where b.cid is null;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    mysql> select a.* from student a left join exams b on a.uid = b.uid where b.cid is null;
    +-----+--------+-----+-----+
    | uid | name   | age | sex |
    +-----+--------+-----+-----+
    |   6 | weiwie |  20 | M   |
    +-----+--------+-----+-----+
    1 row in set (0.01 sec)

    mysql> explain select a.* from student a left join exams b on a.uid = b.uid where b.cid is null;
    +----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+--------------------------------------+
    | id | select_type | table | partitions | type | possible_keys | key     | key_len | ref          | rows | filtered | Extra
           |
    +----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+--------------------------------------+
    |  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL    | NULL    | NULL         |    6 |   100.00 | NULL
           |
    |  1 | SIMPLE      | b     | NULL       | ref  | PRIMARY       | PRIMARY | 4       | school.a.uid |    2 |    10.00 | Using where; Not exists; Using index |
    +----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+--------------------------------------+
    2 rows in set, 1 warning (0.01 sec)

    mysql>

查看没有参加课程号为3考试的学生

1
2
3
select a.* from student a 
left join exams b on a.uid = b.uid and b.cid = 3 
where b.cid is null;
1
2
3
4
5
6
7
8
9
10
11
mysql> select a.* from student a  left join exams b on a.uid = b.uid and b.cid = 3  where b.cid is null;
+-----+----------+-----+-----+
| uid | name     | age | sex |
+-----+----------+-----+-----+
|   1 | zhangsan |  18 | M   |
|   4 | linfeng  |  21 | W   |
|   6 | weiwie   |  20 | M   |
+-----+----------+-----+-----+
3 rows in set (0.01 sec)

mysql>

explain 查询

1
2
3
4
5
6
7
8
9
10
11
12
mysql> explain select a.* from student a  left join exams b on a.uid = b.uid and b.cid = 3  where b.cid is null;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra
               |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL               |    5 |   100.00 | NULL
               |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | school.a.uid,const |    1 |   100.00 | Using where; Not exists; Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql>