mysql知识点看这一篇就够了！（和通用那篇的区别是更偏向mysql的特殊性）

原创 已于 2025-06-12 00:35:44 修改
· 728 阅读 · 1 ·
版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库 #哈希算法

于 2023-04-09 10:16:52 首次发布

有本书好像很好 《MySQL实战45讲》林晓斌网名丁奇

基础篇

执行一条 select 语句，期间发生了什么

字段

text和varchar区别

varchar(n)中括号中n代表字符的个数，并不代表字节个数，所以当使用了中文的时候(utf8)意味着可以插入m个中文，但是实际会占用m*3个字节。

text 的最大长度为 65,535 字节，与 varchar 相同。
mediumtext 的最大长度约为 16 兆字节。
longtext 的最大长度约为 4 gb。

另外，从官方文档中可以得知当varchar大于某些数值的时候其会自动转换为text，大概规则如下：

大于varchar（255）变为 tinytext
大于varchar（500）变为 text
大于varchar（20000）变为 mediumtext
所以对于过大的内容使用varchar和text没有太多区别。

索引会是影响性能的最关键因素，而对于text来说只能添加前缀索引，并且前缀索引最大只能达到1000字节。而貌似varhcar可以添加全部索引，但是经过测试其实也不是。由于会进行内部的转换，所以long varchar其实也只能添加1000字节的索引，如果超长了会自动截断。

使用varchar可以选择大小，并为存储设置上限；使用text则不能设置上限，固定长度65535（16k）。【设置上限和变长存储不浪费空间是不一样的】
使用varchar可以设置默认值；text不可以设置默认值。
使用varchar可以进行排序查询；text不可以排序查询。
使用varchar超过255长度都会隐式的转为text处理。
涉及text列的查询可能会导致在磁盘而不是内存中创建临时表。使用基于磁盘的表会显着降低性能，这意味着使用 text 的查询可能会慢得多。
使用text字段时需要将text字段单独放到一个表中。

存储引擎

InnoDB

InnoDB 是 MySQL 默认的事务型存储引擎，只要在需要它不支持的特性时，才考虑使用其他存储引擎。

InnoDB 采用 MVCC 来支持高并发，并且实现了四个标准隔离级别(未提交读、提交读、可重复读、可串行化)。其默认级别时可重复读（REPEATABLE READ），在可重复读级别下，通过 MVCC + Next-Key Locking 防止幻读。

主索引时聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对主键查询有很高的性能。

InnoDB 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读，能够自动在内存中创建 hash 索引以加速读操作的自适应哈希索引，以及能够加速插入操作的插入缓冲区等。

InnoDB 支持真正的在线热备份，MySQL 其他的存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合的场景中，停止写入可能也意味着停止读取。

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

支持全文索引、空间数据索引

federated——跨节点join

一、 什么是federated引擎
mysql中的federated类似于oracle中的dblink。

federated是一个专门针对远程数据库的实现，一般情况下在本地数据库中建表会在数据库目录中生成相对应的表定义文件，并同时生成相对应的数据文件。

但是通过federated引擎创建的表只是在本地有表定义文件，数据文件则在远程数据库中。

针对federated存储引擎表的查询会被发送到远程数据库的表上执行，本地是不存储任何数据的。

  1. 本地可以比远端数据库的字段少，但是不能多。因为多的话字段就没有地方存储了，还记得么，federated只在本地存储了表结构。

  2. 远程的数据库必须是mysql。

  3. 不支持事务。

  4. 不支持表结构修改，远端表结构变了本地不知道(并不会主动通知本地，不理解，搞个监听器很难吗)，一旦远端修改了表结构就gg了。

  5. 创建时远程数据服务器必须已经存在此表。

  6. 一个federated表指向另一个federated表是有可能的，但是要避免死循环。

  7. 不使用查询缓存。

  8. 远程服务器gg，federated表gg。

二、 如何使用

  1. 开启federated引擎
    mysql默认是没有打开federated存储引擎的。
    使用 show engines 命令查看当前的引擎支持状况：
    在mysql的配置文件中 ：
    在这里插入图片描述

这个只是一个引用，去在圈中的文件中:
在这里插入图片描述

在[mysqld]节点下添加一行 federated：
在这里插入图片描述

重启mysql即可：

再连接到myql， show engines查看是否开启

  1. 创建federated表
    先在A服务器创建一张表：

– A服务器

drop database if exists foo_db ;
create database foo_db default charset utf8 ;
use foo_db;
drop table if exists t_foo_table ;
create table t_foo_table(
    id int primary key auto_increment ,
    username varchar(20) not null ,
    passwd varchar(20) not null 
)charset=utf8;

然后在B服务器创建另一张表，结构和之前创建的一模一样，不同的只是多了指定engine和connection：

-- B服务器
drop database if exists foo_db ;
create database foo_db default charset utf8 ;
use foo_db;
drop table if exists t_foo_table ;
create table t_foo_table(
    id int primary key auto_increment ,
    username varchar(20) not null ,
    passwd varchar(20) not null 
)charset=utf8 engine=federated connection="mysql://root:@10.26.200.134/foo_db/t_foo_table";

connection的格式：

mysql://username:passwd@hostname/db_name/table_name

在A服务器往表中插入一条数据：

insert into t_foo_table (username, passwd) values ("foo", "bar");

在B服务器能查询到

同理在B服务器插入，在A服务器也能查得到，实际上是发送到A服务器执行，数据也是存储在A服务器上的。

  1. 借助于federated表跨节点join
    在B数据库再创建一个表：
-- 要连接的表
drop table if exists t_bar ;
create table t_bar(
    id int primary key auto_increment ,
    barbar varchar(20) not null default "" 
)charset utf8;

插入数据：

insert into t_bar (id,barbar) values (1, "barbar");
insert into t_bar (id,barbar) values (2, "barbarbar");
insert into t_bar (id,barbar) values (3, "barbarbarbar");
insert into t_foo_table (username, passwd) values ("foofoooofoo", "barbaaaaarbar");
insert into t_foo_table (username, passwd) values ("foofooooofoo", "barbaaaarbar");
insert into t_foo_table (username, passwd) values ("foofooooofoo", "barbaaaarbar");

连表查询：

select * from t_bar as t1
join t_foo_table as t2 on t1.id=t2.id ;

查询结果：

OK，查询结果是从两台服务器的两个节点中取出来的。

三、 总结
使用federated有很大的局限性，而且总感觉很不靠谱的样子，讲道理的话应该使用中间件来实现的。

而且好像是不推荐用这种远程join的模式

memory

内存型

InnoDB 和 MyISAM 的比较

  • 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
  • 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
  • 外键：InnoDB 支持外键。
  • 备份：InnoDB 支持在线热备份。
  • 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
  • 其它特性：MyISAM 支持压缩表和空间数据索引。

‌不同的表可以使用不同的存储引擎‌么

在MySQL中，不同的表可以使用不同的存储引擎‌。存储引擎是表级别的设置，而非数据库级别。创建表时可以通过ENGINE子句指定存储引擎（如InnoDB、MyISAM等）。如果未显式指定，表将采用默认引擎，通常由MySQL配置文件决定‌

‌MySQL中不同引擎的表可以进行JOIN操作‌。在MySQL中，不同引擎的表可以通过SQL语句进行JOIN操作，因为JOIN操作是在查询执行时进行的，而不是在数据存储时决定的。无论表使用哪种存储引擎，只要它们具有相同的表结构（例如，具有相同的列和数据类型），就可以进行JOIN操作。

不同存储引擎对JOIN操作的影响
‌性能影响‌：不同的存储引擎可能会对查询性能产生影响。例如，InnoDB和MyISAM在处理大量数据和复杂查询时表现不同，但这对JOIN操作本身没有直接影响。
‌事务支持‌：如果JOIN操作涉及到不支持事务的存储引擎（如MyISAM），则整个事务将无法使用。因此，在设计数据库和查询时需要考虑存储引擎的事务支持情况。

索引与数据结构

看另一篇文章

事务

看另一篇博客

锁

看我的另一篇博客

acid和mvcc

看另一篇

分库分表分区

见另一篇博客

mysql的日志有哪些？

见另一篇博客

主从复制 读写分离

主从复制

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

  • binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
  • I/O 线程 ：负责从主服务器上读取- 二进制日志，并写入从服务器的中继日志（Relay log）。
  • SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

在这里插入图片描述

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

  • 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
  • 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
  • 增加冗余，提高可用性。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。
在这里插入图片描述

MySQL 主从复制还有哪些模型？

主要有三种：

同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。
异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

JSON

JSON Path语法

// 比较简单，举两个例子
set @jsonSource = {
	"aaa": [
		{"ccc":"1"},
		{"ccc":"2"},
	],
	"bbb":"bbb的值"
};
@jsonSource-> "$.bbb"   // 结果为 bbb的值
@jsonSource-> "$.aaa[*].ccc" // 结果为 ["1","2"]

使用场景

在实际业务中经常会使用到 JSON 数据类型，在查询过程中主要有两种使用需求：

  • 在 where 条件中有通过 json 中的某个字段去过滤返回结果的需求
  • 查询 json 字段中的部分字段作为返回结果（减少内存占用）
  • 自动验证存储在JSON列中的JSON文档 。无效的文档会产生错误。

性能分析

有一个表tmp_test_course大概有10万条记录，有个json字段叫outline，存了一对多关系(保存了多个编码，例如jy1577683381775)，我们需要在这10万条数据中检索特定类型的数据，目标总数据量：2931条

以下是4种情况的执行结果

  • 全文索引: 11.6ms
  • json函数查询：82.6ms
  • like查询: 136ms

由于json的字段太大，无法加索引，所以上面的json和like结果是没加索引的情况。但是好像新版 对于JSON类型的列，可以使用MySQL的JSON函数来创建索引。通过创建索引可以大大提高查询效率。可以使用以下语句创建JSON类型的索引：

ame (JSON_EXTRACT(JSON_COLUMN, ‘$.key’));
全文索引只支持CHAR、VARCHAR和TEXT，我们需要把JSON字段定义改一下

结论：全文索引 > json函数查询 > like查询

数据量越大，全文索引速度越明显，就10万的量，查询速度大概比直接查询快了20倍左右，如果是百万或千万级别的表，提升差距会更加大，所以有条件还是老老实实用全文索引吧

函数

JSON_CONTAINS

JSON_CONTAINS(target, candidate[, path])

如果在 json 字段 target 指定的位置 path，找到了目标值 condidate，返回 1，否则返回 0

如果只是检查在指定的路径是否存在数据，使用JSON_CONTAINS_PATH()

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SET @j2 = '1';

mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
|                             1 |
+-------------------------------+

mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.b') |
+-------------------------------+
|                             0 |
+-------------------------------+

mysql> SET @j2 = '{"d": 4}';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
|                             0 |
+-------------------------------+

mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.c') |
+-------------------------------+
|                             1 |
+-------------------------------+

JSON_CONTAINS_PATH

如果在指定的路径存在数据返回 1，否则返回 0
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';

mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+

mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+

mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+

mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
+----------------------------------------+
|                                      0 |
+----------------------------------------+

实际使用：

$conds = new Criteria();
$conds->andWhere('dept_code', 'in', $deptCodes);
if (!empty($aoiAreaId)) {
    $aoiAreaIdCond = new Criteria();
    $aoiAreaIdCond->orWhere("JSON_CONTAINS_PATH(new_aoi_area_ids,'one', '$.\"$aoiAreaId\"')", '=', 1);
    $aoiAreaIdCond->orWhere("JSON_CONTAINS_PATH(old_aoi_area_ids,'one', '$.\"$aoiAreaId\"')", '=', 1);
    $conds->andWhere($aoiAreaIdCond);
}

获取指定路径的值

用->、->>的查询效率比json_extract、json_unquote慢很多，虽然它们好像是一样的，不知道什么原因。

column->path、column->>path

-> vs ->> Whereas the -> operator simply extracts a value, the ->> operator in addition unquotes the extracted result.
->在field中使用的时候结果带引号，->>的结果不带引号

mysql> SELECT * FROM jemp WHERE g > 2;
+-------------------------------+------+
| c                             | g    |
+-------------------------------+------+
| {"id": "3", "name": "Barney"} |    3 |
| {"id": "4", "name": "Betty"}  |    4 |
+-------------------------------+------+
2 rows in set (0.01 sec)

mysql> SELECT c->'$.name' AS name
    ->     FROM jemp WHERE g > 2;
+----------+
| name     |
+----------+
| "Barney" |
| "Betty"  |
+----------+
2 rows in set (0.00 sec)

mysql> SELECT JSON_UNQUOTE(c->'$.name') AS name
    ->     FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)

mysql> SELECT c->>'$.name' AS name
    ->     FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)

#特别注意：->当做where查询是要注意类型的，->>是不用注意类型的
#下面二者结果不一样
select * from member where info->"$.id" = 1;
select * from member where info->"$.id" = "1";

实际使用：

$retTask = AoiAreaTaskOrm::findRows(['status', 'extra_info->>"$.new_aoi_area_infos" as new_aoi_area_infos', 'extra_info->>"$.old_aoi_area_infos" as old_aoi_area_infos'], $cond);

update

该UPDATE语句使用任何的三个功能 JSON_SET()， JSON_REPLACE()或 JSON_REMOVE()更新列。列值的直接赋值（例如，UPDATE mytable SET jcol='{“A”：10，“b”：25}’）不能作为部分更新执行。

给JSON字段添加索引

生成列虚拟索引方式

说明：8.0和5.7都支持在生成列上添加索引

JSON 不能直接对列进行索引。要创建间接引用此类列的索引，可以定义一个生成列，该列提取应建立索引的信息，然后在生成的列上创建索引，如下所示：


mysql>CREATE TABLE jemp (
    ->     c JSON,
    ->     g INT GENERATED ALWAYS AS (c->"$.id"),
    ->     INDEX i (g)
    -> );
Query OK, 0 rows affected (0.01 sec)
 
#查看表结构：

#8.0表结构：
*************************** 1. row ***************************
       Table: jemp
Create Table: CREATE TABLE `jemp` (
  `c` json DEFAULT NULL,
  `g` int GENERATED ALWAYS AS (json_extract(`c`,_utf8mb3'$.id')) VIRTUAL,
  KEY `i` (`g`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
#5.7表结构：
*************************** 1. row ***************************
       Table: jemp
Create Table: CREATE TABLE `jemp` (
  `c` json DEFAULT NULL,
  `g` int(11) GENERATED ALWAYS AS (json_extract(`c`,'$.id')) VIRTUAL,
  KEY `i` (`g`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
# 插入数据
mysql >INSERT INTO jemp (c) VALUES
    -> ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
    -> ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
# 查看数据
mysql >select * from jemp;
+-------------------------------+------+
| c                             | g    |
+-------------------------------+------+
| {"id": "1", "name": "Fred"}   |    1 |
| {"id": "2", "name": "Wilma"}  |    2 |
| {"id": "3", "name": "Barney"} |    3 |
| {"id": "4", "name": "Betty"}  |    4 |
+-------------------------------+------+
4 rows in set (0.00 sec)
 
 #如何选择数据
mysql >SELECT c->>"$.name" AS name FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)
 
查看执行计划：
mysql >EXPLAIN SELECT c->>"$.name" AS name FROM jemp WHERE g > 2 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

通过上述查看执行计划，可以看到使用到了我们在生成列上创建的索引；

当EXPLAIN在SELECT包含一个或多个使用->or->> 运算符的一个 或其他SQL语句上使用时 ，这些表达式将使用JSON_EXTRACT()和（如果需要）转换为它们的等效项JSON_UNQUOTE()，如SHOW WARNINGS输出所示：

mysql>EXPLAIN SELECT c->>"$.name" FROM jemp WHERE g > 2 ORDER BY c->"$.name"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)
 
mysql >SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select json_unquote(json_extract(`wjqdb`.`jemp`.`c`,'$.name')) AS `c->>"$.name"` from `wjqdb`.`jemp` where (`wjqdb`.`jemp`.`g` > 2) order by json_extract(`wjqdb`.`jemp`.`c`,'$.name')
1 row in set (0.00 sec)

在MySQL 8.0.21和更高版本中，还可以JSON使用JSON_VALUE()带有表达式的函数在列上创建索引，该表达式可用于优化使用该表达式的查询；

8.0新特性 直接creat index与多值索引

MySQL 8.0新增的一种索引类型：多值索引；从MySQL 8.0.17开始，InnoDB支持多值索引。多值索引是在存储值数组的列上定义的二级索引。“普通”索引对每个数据记录有一个索引记录（1:1）。对于单个数据记录（N:1），多值索引可以有多个索引记录。多值索引旨在为JSON数组建立索引。

多值索引可以在CREATE TABLE、ALTER TABLE或CREATE INDEX语句中创建多值索引。
这要求使用CAST(… AS … ARRAY)索引定义，该定义将JSON数组中相同类型的标量值转换为SQL数据类型数组。

然后，使用SQL数据类型数组中的值透明地生成一个虚拟列。最后，在虚拟列上创建一个功能索引（也称为虚拟索引）。是在SQL数据类型数组的值的虚拟列上定义的功能索引，该索引构成了多值索引。

Mysql 8.0.x版本后，支持对json字段创建索引，直接 create index 即可，需要使用cast方法将json目标字段转换成可以创建索引的类型。有两种情况：

  • 普通索引：每条记录和json字段为1对1关系

  • 多值索引：每条记录和json字段为1对多关系（对应array，或是jsonPath取值结果是array情况）

#普通索引
ADD INDEX index_modify_user((CAST(permission_json->'$.aaa' AS CHAR(64))))
#多值索引 （区别仅在于用cast 方法转化成一个 ARRAY）
ADD INDEX index_modify_user((CAST(permission_json->'$.modify[*]' AS CHAR(64) ARRAY )))
根据JSON索引查询

只有少数语句支持JSON字段索引。在WHERE子句中使用以下函数时，优化程序将使用多值索引来获取记录 ：（当然 你需要先新建索引）

  • MEMBER OF()

  • JSON_CONTAINS()

  • JSON_OVERLAPS()

较为零碎

DDL详解与8.0对DDL的优化（后续项目推荐新版本）

随着业务的发展，用户对系统需求变得越来越多，这就要求系统能够快速更新迭代以满足业务需求，通常系统版本发布时，都要先执行数据库的DDL变更，包括创建表、添加字段、添加索引、修改字段属性等。

在早期的MySQL版本，DDL变更都会导致全表被锁，阻塞表上的DML操作，影响业务正常运行，好的一点就是，随着MySQL版本的迭代，DDL的执行方式也在变化。

在数据量大不大的情况下，执行DDL都很快，对业务基本没啥影响，但是数据量大的情况，而且我们业务做了读写分离，接入了实时数仓，这时DDL变更就是一个的难题，需要综合各方业务全盘考虑。

例如上面提到了，目前我在大数据团队，我们的业务都做了读写分离，同时接入实时数仓，数仓不支持rename操作，这时就可以选择在业务低峰期使用ONLINE DDL的方式执行，对业务系统影响最小，同时不影响数仓。

移除的查询缓存

适合QueryCache的场景
首先，查询缓存QC的大小只有几MB，不适合将缓存设置得太大，由于在更新过程中需要线程锁定QueryCache，因此对于非常大的缓存，可能会看到锁争用问题。那么，哪些情况有助于从查询缓存中获益呢？以下是理想条件：

相同的查询是由相同或多个客户机重复发出的。
被访问的底层数据本质上是静态或半静态的。
查询有可能是资源密集型和/或构建简短但计算复杂的结果集，同时结果集比较小。
并发性和查询QPS都不高。
这4种情况只是理想情况下，实际的业务系统都是有CRUD操作的，数据更新比较频繁，查询接口的QPS比较高，所以能满足上面的理想情况下的业务场景实在很少，我能想到就是配置表，数据字典表这些基本都是静态或半静态的，可以时通过QC来提高查询效率。

不适合QueryCache的场景
如果表数据变化很快，则查询缓存将失效，并且由于不断从缓存中删除查询，从而使服务器负载升高，处理速度变得更慢，如果数据每隔几秒钟更新一次或更加频繁，则查询缓存不太可能合适。

同时，查询缓存使用单个互斥体来控制对缓存的访问，实际上是给服务器SQL处理引擎强加了一个单线程网关，在查询QPS比较高的情况下，可能成为一个性能瓶颈，会严重降低查询的处理速度。因此，MySQL 5.6中默认禁用了查询缓存。

上面为大家介绍了MySQL QueryCache从推出->禁用->废弃->删除的心路历程，设计之初是为了减少重复SQL查询带来的硬解析开销，同时将物理IO转化为逻辑IO，来提高SQL的执行效率，但是MySQL经过了多个版本的迭代，同时在硬件存储发展之快的今天，QC几乎没有任何收益，而且还会降低数据库并发处理能力，最终在8.0版本直接Removd掉了。

缓存的失效很容易，只要对表有任何的更新，这个表的所有查询缓存就会全部被清空，就会出现缓存还没使用，就直接被清空了，或者积累了很多缓存准备用来着，但是一个更新打回原形。

这就导致查询的命中率低的可怕，只有那种只查询不更新的表适用缓存，但是这样的表往往很少存在，一般都是什么配置表之类的。

连接超时——每天要重启下的有意思小错误

这里需要注意的是，我们数据库的客户端太久没响应，连接器就会自动断开了，这个时间参数是wait_timeout控制住的，默认时长为8小时。

断开后重连的时候会报错，如果你想再继续操作，你就需要重连了。

这个有个我看过的书本的案例：

一个在政府里的朋友说，他们的系统很奇怪，每天早上都得重启一下应用程序，否则就提示连接数据库失败，他们都不知道该怎么办。

按照这个错误提示，应该就是连接时间过长了，断开了连接。

数据库默认的超时时间是8小时，而他们平时六点下班，下班之后系统就没有人用了，等到第二天早上九点甚至十点才上班，这中间的时间已经超过10个小时了，数据库的连接肯定就会断开了。

是的，就是超出了超时时间，然后写代码的人也没注意到这个细节，所以才会出现这个问题。

把超时时间改得长一点，问题就解决了。

这种参数其实我们平时不一定能接触到，但是真的遇到问题的时候，知道每个参数的大概用法，不至于让你变成无头苍蝇。

那除了重新链接，还有别的方式么？因为建立链接还是比较麻烦的。

使用长连接。

但是这里有个缺点，使用长连接之后，内存会飙得很快，我们知道MySQL在执行过程中临时使用的内存是管理在连接对象里面的。

只有在链接断开的时候才能得到释放，那如果一直使用长连接，那就会导致OOM（Out Of Memory），会导致MySQL重启，在JVM里面就会导致频繁的Full GC。

那你会怎么解决？

我一般会定期断开长连接，使用一段时间后，或者程序里面判断执行过一个占用内存比较大的查询后就断开连接，需要的时候重连就好了。

还有别的方法么？你这种感觉不优雅呀小老弟。

执行比较大的一个查询后，执行mysql_reset_connection可以重新初始化连接资源。这个过程相比上面一种会好点，不需要重连，但是会初始化连接的状态。

进一步阅读

里面有比如权限系统之类的
https://mp.weixin.qq.com/mp/appmsgalbum?__biz=MzA3ODUxNjk0OQ==&action=getalbum&album_id=1760277454188789768

mysql实战45讲

云闲不收
关注
MySQL数据库学习笔记（六）----MySQL多表查询之外键、表连接、子查询、索引
weixin_34059951的博客
10-22 2106
本章主要内容： 一、外键 二、表连接 三、子查询 四、索引 一、外键： 1、什么是外键 2、外键语法  3、外键的条件 4、添加外键 5、删除外键 1、什么是外键： 主键：是唯一标识一条记录，不能有重复的，不允许为空，用来保证数据完整性 外键：是另一表的主键, 外键可以有重复的, 可以是空值...
「MySQL高级篇」MySQL锁机制&事务
jcc4261的博客
12-06 340
锁锁锁，到哪到离不开这桩琐事，并发琐事，redis琐事，如今是MySQL琐事，这其中琐事，还跟MySQL另一个重要的东西--事务息息相关。这篇将从以下几点，带你解开这把爱情的苦锁：锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，
一篇了解全MVCC
程序员Forlan的博客
10-24 9388
一篇就够，了解透MVCC
sqlserver两表查询关联_详解MySQL两表关联的连接表创建单列索引还是组合索引最优...
weixin_39757893的博客
12-08 610
概述今天主要介绍一下MySQL中两表关联的连接表是如何创建索引的相关内容，下面来看看详细的介绍。MySQL两表关联的连接表创建索引创建数据库的索引，可以选择单列索引，也可以选择创建组合索引。假设用户表(user)与部门表(dept)通过部门用户关联表(deptuser)连接起来，如下图所示：表间关系问题就是，在这个关联表中该如何建立索引呢？针对该表，有如下四种选择:针对于user_uuid建立单列...
MYSQL 常识系统回顾和整理【建表、索引、数据同步、高效查询、排错原则】
大鹏
03-18 1441
参考：使你的 SQL 语句完全优化--Oracle mysql 执行顺序 SQL语句执行顺序分析 首先，SELECT语句的基本语法如下：  　　SELECT selection_list # What columns toselect  　　FROM table_list # Which tables to select rowsfrom  　　WHERE prima
mysql索引
qq_35419179的博客
12-07 519
1.mysql架构 1.1MySQL的客户端／服务器架构 1.2各个模块的含义 1.2.1连接管理 1.2.2解析与优化 查询缓存 MySQL服务器程序会把刚刚处理过的查询请求和结果缓存起来，如果下一次有完全相同的请求过来，直接从缓存中获取。这个查询缓存可以在不同客户端之间共享，也就是说如果客户端A刚刚查询了一个语句，而客户端B之后发送了同样的查询请求，那么客户端B的查询就可以直接使用查询缓存中的数据。 另外，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql 、infor
半个月时间把MySQL重新巩固了一遍，梳理了一篇几万字 “超硬核” 文章！
m0_67402235的博客
07-30 523
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。所以，现在我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。对于数据库而言实际上每一张表都表示是一个数据库的对象，而数据库对象指的就是DDL定义的所有操作，例如。...
MySql学习笔记
weixin_45906989的博客
07-27 1567
MySql学习笔记
【面试】MySQL这些知识点不能错过（终篇）
weixin_49163826的博客
09-25 991
事务 (transaction) 事务是指逻辑上的一组操作，组成这组操作的各个单元，要不全成功要不全失败。 - 支持连续SQL的集体成功或集体撤销。 - 事务是数据库在数据晚自习方面的一个功能。 - 需要利用 InnoDB 或 BDB 存储引擎，对自动提交的特性支持完成。 - InnoDB被称为事务安全型引擎。 -- 事务开启 START TRANSACTION; 或者 BEGIN; 开启事务后，所有被执行的SQL语句均被认作当前事务内的SQL语句。 -- 事
【《高性能 MySQL》笔记】性能优化
最新发布
影三人
03-10 1106
MySQL 性能优化
mysql联合索引
weixin_34320159的博客
10-19 712
命名规则：表名_字段名1、需要加索引的字段，要在where条件中2、数据量少的字段不需要加索引3、如果where条件中是OR关系，加索引不起作用4、符合最左原则 https://segmentfault.com/q/1010000003984016/a-1020000003984281 联合索引又叫复合索引。对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，...
Mysql—索引③：优化篇（不仅仅是索引）
可达鸭的博客
07-12 1827
详解索引优化与查询优化（SQL），讲解了索引失效的情况，join查询中的概念（驱动表、被驱动表），join查询优化，子查询优化，排序优化，groupby优化，分页limit优化，前缀索引，索引下推
[学习打卡]09MySql索引的优化
weixin_45317494的博客
10-06 284
单表索引优化 示例 示例1： 索引： 效果： 示例二： 索引： 效果： 示例三： 索引： 效果： 更改了where之后的条件顺序之后： 索引依旧生效，效果如下： 还是实例三的索引，删除一个条件之后 效果： 只命中了一个字段，只命中了age而没有命中name 同样：替换一个之后 效果： 没有命中索引。 原因： age之后应该是deptid但是没有写所以有了第一次的那个只命中一个索引的 “...
深入理解Mysql - 索引原理详解
liuhenghui5201的专栏
04-10 405
一、什么是数据库索引 数据库索引，是数据库管理系统（DBMS）中一个排序的数据结构，以协助快速查询、更新数据库表中数据。 二、索引存储模型的推演 1，有序数组 过程：按照顺序由小往大或反向查询。 缺点：在开始或中间位置插入时，需要挪动后面全部的节点下标。新增、删除、修改效率低。 2，二叉查找树（BST Binary Search Tree） 特点：左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。投影到平面以后，就是一个有序的线性表。 过程：需要查询的值与根节点比较，大于根节点
Mysql索引
飞鱼计划
02-24 243
相信很多人对于MySQL的索引都不陌生，索引(Index)是帮助MySQL高效获取数据的数据结构。 因为索引是MySQL中比较重点的知识，相信很多人都有一定的了解，尤其是在面试中出现的频率特别高。楼主自认为自己对MySQL的索引相关知识有很多了解，而且因为最近在找工作面试，所以单独复习了很多关于索引的知识。 但是，我还是图样图森破，直到我被阿里的面试官虐过之后我才知道，自己在索引方面的知识，只是个小学生水平。 以下，是我总结的一次阿里面试中关于索引有关的问题以及知识点。 1 索引概念、索引模型
mvcc多版本并发控制
suchahaerkang的博客
10-31 1122
文章目录一、前提概要什么是MVCC?什么是当前读和快照读？当前读，快照读和MVCC的关系MVCC能解决什么问题，好处是？数据库并发场景有三种，分别为：MVCC带来的好处是？小结一下咯二、MVCC的实现原理隐式字段undo日志 一、前提概要 什么是MVCC? MVCC MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。 MVCC在MySQL InnoDB
MVCC及实现原理
热门推荐
GSON的博客
10-15 1万+
！首先声明，MySQL 的测试环境是 5.7 前提概要 什么是 MVCC 什么是当前读和快照读？ 当前读，快照读和 MVCC 的关系 MVCC 实现原理 隐式字段 undo日志 Read View 整体流程 MVCC 相关问题 RR 是如何在 RC 级的基础上解决不可重复读的？ RC, RR 级别下的 InnoDB 快照读有什么不同？ 一、前提概要 1、什么是 MVCC ? MVCCMVCC，全称Multi-Version Concurrency Co.
MySQL查询性能优化——索引的使用(三)
工作、学习、生活分享
01-25 1361
索引的使用、回表、覆盖索引、索引失效情况、索引的设计原则
解说mysql之binlog日志以及利用binlog日志恢复数据
智行晓东的博客
12-12 1155
众所周知，binlog日志对于mysql数据库来说是十分重要的。在数据丢失的紧急情况下，我们往往会想到用binlog日志功能进行数据恢复（定时全备份+binlog日志恢复增量数据部分），化险为夷！ 废话不多说，下面是梳理的binlog日志操作解说： 一、初步了解binlog MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值