MySQL高级篇--笔记

已于 2023-10-17 11:59:39 修改
阅读量195 收藏 1
点赞数
分类专栏： MySQL 文章标签： mysql 数据库 笔记
于 2023-05-19 14:42:56 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45609613/article/details/130659357
版权

MySQL高级部分

MySQL5 和MySQL8 区别

  • MySQL8之前 默认字符集为 latin1，utf8字符集指向的是 utf8mb3 不设置utf8会导致插入中文数据报错
  • MySQL 8.0以后默认字符集utf8，指向的是 utf8mb4

MySQL字符集

在这里插入图片描述
服务器级别 service
数据库级别 database
表级别 创建表的时候设置的

CREATE TABLE 表名 (列的信息)
 [[DEFAULT] CHARACTER SET 字符集名称]
 [COLLATE 比较规则名称]]
ALTER TABLE 表名
 [[DEFAULT] CHARACTER SET 字符集名称]
 [COLLATE 比较规则名称]

列级别 创建列的时候设置的

CREATE TABLE 表名(
 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
 其他列...
);
ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

MySQL目录

  • MySQL数据库文件的存放路径：/var/lib/mysql/
  • 相关命令目录：/usr/bin（mysqladmin、mysqlbinlog、mysqldump等命令）和/usr/sbin
  • 配置文件目录：/usr/share/mysql-8.0（命令及配置文件），/etc/mysql（如my.cnf）

MySQL逻辑架构及SQL执行流程

在这里插入图片描述

总的来说，一条SQL的执行过程就是：客户端通过连接器建立连接，这个操作进行权限验证，通过连接器之后会先前往缓存，根据sql作为key去查询，查到直接返回，否则前往解析器，经过分析器对sql语句的分析、解析，得到一个mysql可以理解的语法，随后进入优化器，mysql会根据你查询的条件进行适当的优化，之后在经过执行器，这就真正的开始前往存储引擎查询数据，最后将查询到的数据返回给客户端，顺便写入缓存（不一定，MySQL8.0以后没有缓存）。

我们拆开来看，一共有下面这几步

第一步：连接器
建立连接，同时校验账号密码及IP。权限(库权限，表权限，列权限)

第二步：查询缓存（过于鸡肋，MySQL8以后删掉了）
连接器得工作完成后，客户端就可以向 MySQL 服务发送 SQL 语句了，MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句。

如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。
如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。
对于更新比较频繁的表，查询缓存的命中率很低的，因为只要一个表有更新（增删改）操作，那么这个表的查询缓存就会被清空

第三步：解析器
在正式执行 SQL 查询语句之前， MySQL 会先对 SQL 语句做解析，这个工作交由由解析器来完成，解析器会做两件事情。

1，词法分析 MySQL 会根据你输入的字符串识别出关键字出来，构建出 SQL 语法树，这样方面后面语法分析获取 SQL 类型、表名、字段名、 where 条件等等。
在这里插入图片描述

2，语法分析 根据上面分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。(如果我们输入的 SQL 语句语法不对，或者数据表或者字段不存在，都会在解析器这个阶段报错。)

第四步：优化器
此阶段主要是进行SQL语句的优化会根据执行计划进行最优的选择，匹配合适的索引，选择最佳的执行方案。
截止到现在，还没有真正的读表，而是产出了一个执行计划。于是进入到执行器阶段
第五步：执行器
根据表的引擎定义，调用存储引擎API对表进行读写操作

存储引擎

在这里插入图片描述

索引

索引数据结构（B+树）

在这里插入图片描述 为了方便理解，把数据页的上层叫做目录页
最下层数据页也就是叶子节点，存放数据（只在聚簇索引中存放）
每一页数据页存放16KB大小的数据，里面的数据按照顺序排列，上层目录页的黄色表示数据页最小数据，粉色表示数据页的页码（地址指向），而再上一层其实就是为了防止数据量很大的时候，只能在第二层去循环查找到属于哪一个目录页（循环的目录页次数过多），在用二分法去找到具体的数据页

常见的索引

  • 聚簇索引（主键索引） 包含数据
    就是上面B+树的图~

在这里插入图片描述
隐士的聚簇索引有什么用。针对的是哪一列呢？？？

  • 二级索引（辅助索引、非聚簇索引）
    在这里插入图片描述
    这种索引的方式去查找数据，只能通过索引列的值找到对应的主键ID，而查询不到其他列的相关数据，所以需要通过ID再去聚簇索引查找一遍。这个过程叫做 回表 也就是通过非聚簇索引查找完整的数据需要查找2次B+树（实际上不一定非得回表，还是要看查询语句查询的列是否被全部被索引命中）

  • 联合索引
    实际上就是在二级索引的数据列里在增加一列， 并且图中的列应该包含主键ID进行唯一性的区分。

聚簇索引与非聚簇索引的区别是什么？

InnoDB的B+树索引的注意事项

1.根页面位置万年不动
学习的时候为了方便理解是按照从下往上的方式创建数据页，但实际过程是按照从上往下的方式创建数据页，根节点永远不变

每当为某个表创建一个B+树索引（聚集索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有数据，也没有目录项记录
随后向表中插入数据时，先把用户记录存储到这个根节点中
当根节点中的可用空间用完时继续插入，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个页a进行页分裂的操作，得到另一个新页，如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页）

2.内节点中目录项记录的唯一性： 在非聚簇索引中，可能某个数据页里某列有相同的值，这时需要带上一个主键ID做区分
3.一个数据页最少存储2条记录：

  • 页的大小：INNODB使用固定大小的数据页来存储数据，通常为16KB。为了充分利用每个数据页的空间，最少存储两条记录可以避免过多的碎片化。
  • 页的读取和写入：当数据库需要读取或写入数据时，通常是以数据页为单位进行操作。最少存储两条记录可以减少I/O操作的次数，提高数据的读取和写入效率。
  • 防止数据页频繁分裂。数据页分裂是指当一个数据页已经满了，需要插入新的记录时，需要将该数据页分裂成两个数据页，这个操作会带来一定的性能和存储开销。通过在一个数据页中至少存储2条记录，可以减少数据页分裂的频率，因为在插入新记录时，已经有足够的空间可用。这有助于提高数据库的性能和效率。
    在这里插入图片描述

B-Tree和B+Tree区别

在这里插入图片描述

  1. B+树内节点不存储数据，所有 data 存储在叶节点导致查询时间复杂度固定为 log n。而B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)。
  2. B+树叶节点两两通过链表方式相连大大增加区间（范围）访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找（主要是部分数据可能还存在上层节点 ，增加了IO次数）。
  3. B+树更适合外部存储。由于内节点无 data 域，每个节点能索引的范围更大更精确（B+数的非叶子节点不存放data，目录页（数据页）可以存放更多元素，更矮胖，减少io次数）

怎么解释数据页是双向链表方式连接？
其实数据页的头部信息就存放了上一页和下一页的相关信息
在这里插入图片描述

创建和删除索引

  • 创建
  1. 创建表的时候创建索引
CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC |
DESC]

CREATE TABLE test1(
id INT NOT NULL,
name varchar(30) NOT NULL,
XXX(索引类型 如唯一索引UNIQUE等，也可以不加，表示普通索引)INDEX uk_idx_id（索引名）(id)（字段名）
);
  1. 在已经存在的表上创建索引
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]
  • 删除
ALTER TABLE table_name DROP INDEX index_name;

适合创建索引场景

  1. 字段的数值有唯一性的限制
  2. 频繁作为 WHERE 查询条件的字段
  3. 经常 GROUP BY 和 ORDER BY 的列
    同时创建GROUP BY 和 ORDER BY 的列索引时，要 将GROUP BY的列放前面
  4. UPDATE、DELETE 的 WHERE 条件列
  5. DISTINCT 字段需要创建索引
  6. 多表 JOIN 连接操作时，创建索引注意事项
    首先， 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
    其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。
    最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的类型必须一致 ，不能一个为 int 另一个为 varchar 类型。 类型不一致会涉及到函数转换，导致索引失效
  7. 使用列的类型小的创建索引
  8. 使用字符串前缀创建索引
  9. 区分度高(散列性高)的列适合作为索引
  10. 使用最频繁的列放到联合索引的左侧
    这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。
  11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

不适合创建索引场景

  1. 在where中使用不到的字段，不要设置索引
  2. 数据量小的表最好不要使用索引
  3. 有大量重复数据的列上不要建立索引（其实就是散列性不高的列）
  4. 避免对经常更新的表创建过多的索引
  5. 不建议用无序的值作为索引
    例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字
    符串等。
  6. 删除不再使用或者很少使用的索引
  7. 不要定义冗余或重复的索引

慢查询日志使用

怎么查看慢sql呢？

  1. 开启慢查询日志参数
    mysql > set global slow_query_log='ON'；
    在这里插入图片描述
    可以看到慢查询分析已经开启，同时文件保存在 /var/lib/mysql/atguigu02-slow.log 文件中

  2. 修改long_query_time阈值（多少秒算慢sql，记录到日志中）
    查看mysql中默认是多少秒 mysql > show variables like '%long_query_time%';
    在这里插入图片描述
    测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并
    执行下述语句
    mysql > set global long_query_time = 1;
    mysql> set long_query_time=1;

  3. 慢查询日志分析工具：mysqldumpslow
    查看mysqldumpslow的帮助信息
    mysqldumpslow --help
    命令的具体参数如下：
    -a: 不将数字抽象成N，字符串抽象成S （显示正常的入参）
    -s: 是表示按照何种方式排序：
    c: 访问次数
    l: 锁定时间
    r: 返回记录
    t: 查询时间
    al:平均锁定时间
    ar:平均返回记录数
    at:平均查询时间 （默认方式）
    ac:平均查询次数
    -t: 即为返回前面多少条的数据；在这里插入图片描述

  4. 关闭慢查询日志
    开启后记得要关闭，一般生产环境如果不开启，毕竟开启慢查询日志对性能有影响

EXPLAIN 分析

列名描述
id在一个大的查询语句中每个SELECT关键字都对应一个 唯一的id
select_typeSELECT查询类型
table表名
partitions匹配的分区信息
type针对单表的访问方法（system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，好的sql查询至少达到range级别 ，最好能达到ref）
possible_keys可能使用的索引
key实际使用的索引(如果没有使用索引，则为null)
key_len实际使用的索引的长度 （主要对于联合索引，看命中的索引个数，当然是命中越多越好？？？ ）
ref显示哪些列或常量被用于查找索引列上的值 ，有可能是一个常数，函数等
rows预估的需要读取的记录条数（越小越好）
filtered某个表经过搜索条件过滤后剩余记录条数的百分比（相对越大越好）
Extra额外信息

key_len
计算方法：
key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度： 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就 是该固定值，对于指定字符集的变长类型的索引列来说，比如VARCHAR(100) 100位字符，使用的字符集是 utf8，那么该列实际占用的最大存储空间就 是 100 x 3 = 300 个字节。
如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多 1 个 字节。
对于可变长度（varchar）字段来说，都会有 2 个字节的空间来存储该变长列的实际长度

type

  • system：（非InnoDB）MyISAM引擎
    当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如 MyISAM、Memory，那么对该表的访问方法就是 system
  • const：唯一查询，主键或者唯一二级索引列与常数进行等值匹配
  • eq_ref（关联查询，被驱动表条件为唯一索引）
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id

在这里插入图片描述

  • ref（普通索引查询）
    普通索引列允许为null的情况下，数量必然是不受限制的，所以我们采用 key IS NULL 这种形式的搜索条件最多只能使用 ref 的访问方法，而不是 const 的访问方法
EXPLAIN SELECT * FROM s1 WHERE order_no = 'a';

在这里插入图片描述

  • ref_or_null（普通二级索引+null值）
explain SELECT * FROM order_exp_cut WHERE order_no= 'abc' OR order_no IS NULL;
  • index_merge（使用多个索引）
EXPLAIN SELECT * FROM s1 WHERE order_no = 'a' OR insert_time = '2021-03-22 18:36:47';
  • unique_subquery（主键列子查询）
EXPLAIN SELECT * FROM s1 WHERE id IN (SELECT id FROM s2 where s1.insert_time = s2.insert_time) OR order_no = 'a';
  • index_subquery（普通列子查询）
EXPLAIN SELECT * FROM s1 WHERE order_no IN (SELECT order_no FROM s2 where s1.insert_time = s2.insert_time) OR order_no = 'a';
  • range（范围）
EXPLAIN SELECT * FROM s1 WHERE order_no IN ('a', 'b', 'c');
EXPLAIN SELECT * FROM s1 WHERE order_no > 'a' AND order_no < 'b';
  • index（扫描整个索引并使用索引覆盖）
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
  • all（全表扫描）
EXPLAIN SELECT * FROM s1;

索引优化和查询优化

首先都有哪些维度可以进行数据库优化呢？
1.索引失效，没有充分利用索引 — 创建索引
2.关联查询join太多表 — SQL优化
3.服务器调优及参数调优（缓冲。线程池，连接数等）—调整my.cnf
4.分库分表，缓解数据库压力

索引失效

这种失效场景只是规则，具体还是得看优化器的成本计算（后续覆盖索引有例子）
1. 最左前缀法则（针对联合索引。最左边的列 必须出现在where条件中）
使用复合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

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

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';	索引生效，是根据右边进行模糊搜索，但是左边还能走索引 
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';  索引失效,函数作用在了索引列上 
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;	索引失效，实际上做了隐士转换。将int类型转换成varchar类型
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';	索引生效

3. 范围条件右边的列索引失效(范围列不失效，所有一般将范围列放在联合索引最右边)

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId>20 AND student.name = 'abc';
索引只作用了age和classId.  int类型4字节，非空1字节。  key_len: 4+1+4+1=10

在这里插入图片描述

4. 不等于(!= 或者<>)索引失效
5. null可以使用索引，is not null无法使用索引
6. like以通配符%开头索引失效
7. OR 前后存在非索引的列，索引失效（or两边的字段必须都有索引，否则都失效）
8. 数据库和表的字符集统一使用utf8mb4
统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不
同的 字符集 进行比较前需要进行 转换 会造成索引失效。

关联查询优化

join 的语法（左联，右联，内联，外联）这里就不说了。主要说下join的执行过程：

EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.a=t2.a)

首先说一下驱动表和被驱动表，用EXPLAIN 查看，驱动表在上面，被驱动表在下面

  1. 从表t1中读入一行数据 R；
  2. 从数据行R中，取出a字段到表t2里去查找；
  3. 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
  4. 重复执行步骤1到3，直到表t1的末尾循环结束。

总结：这个过程是先遍历表t1，然后根据从表t1中取出的每行数据中的a值，去表t2中查找满足条件的记录。MySQL5.5版本之前，这就是Nested Loop Join（嵌套循环），在MySQL5.5以后的版本中，MySQL通过引入BNLJ（缓存块嵌套循环连接） 算法来优化嵌套执行，当被驱动表的连接条件字段没有建立索引时，mysql每次读取被驱动表数据之前会将驱动表的多条数据一次性存入缓冲区中，然后再全表扫描被驱动表。
通过这种方式，我们可以发现，其实可以在被驱动表中的join连接字段上加入索引，这样就不需要全表扫码t2表了，减少内层表数据的匹配次数，这是INLJ（索引嵌套循环连接）

从MySQL的8.0.20版本开始将废弃BNLJ，因为从MySQL8.0.18版本开始就加入了hash join，默认都会使用hash join 。
对于被连接的数据子集较小的情况，Nested Loop是个较好的选择。
Hash Join 是做大数据集连接时的常用方式，优化器使用两个表中较小（相对较小）的表利用 join key在内存中建立散列表，然后扫描较大的表并探测散列表，找出与hash表匹配的行。

优化

  • 整体效率比较：INLJ （索引方式）> BNLJ（缓存方式） > SNLJ（嵌套循环方式）
  • 保证被驱动表的JOIN字段已经创建了索引（减少内层表的循环匹配次数）
  • 需要JOIN 的字段，数据类型保持绝对一致。
  • LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。
  • INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。
  • 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
  • 增大join_buffer_size的大小（一次缓存的数据越多，那么内存表的扫描次数就越少）。
  • 减少驱动表不必要的字段查询（字段越少，join buffer所缓存的数据就越多）。

排序优化

优化建议：

  1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
  2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列，如果不同就使用联合索引。
  3. 无法使用 Index 时，需要对 FileSort 方式进行调优

具体是否使用到索引还得根据实际数据量等情况才得知，下面主要是规则：

  • order by时不用limit，可能会索引失效，主要看是否需要回表，如果需要回表，那可能不如直接将数据取出到内存中，再进行排序；
  • order by的顺序错误，也会索引失效— 这里指的是order by的多个字段与联合索引顺序不一致，导致失效
  • order by规则不一致，也会失效。这里的规则指的是字段对应的升序降序（这里字段应该是指多个字段，一个字段的话就不存在相反不相反了）。但是如果都相反的话，又会使用上索引。是因为，虽然每个数据之间是单向连接，但是数据页是双向链表连接的，可以反过来读取数据页

覆盖索引

select的数据列从索引中就能够取得，不必根据索引里的id再次回表读取数据文件获取其他数据列，有效减少IO次数。

判断是否使用了覆盖索引：当一个查询所查询的列都在索引字段上，且在查询分析器EXPLAIN的Extra列可以看到“Using index” ，则可判定为覆盖索引。
在这里插入图片描述
按照之前所知道的，出现<> ，！= 这种情况的时候，是不会使用索引的。但是，在select的列在索引列里都包含时，这种情况下优化器就会计算成本，发现走索引的成本更低，就出现了索引没有失效的场景

索引下推

索引下推 Index Condition Pushdown(ICP) 是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。
在这里插入图片描述

在这里插入图片描述
ICP的使用条件：
① 只能用于二级索引(secondary index)
②explain显示的执行计划中type值（join 类型）为 range 、 ref 、 eq_ref 或者 ref_or_null 。
③ 并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录
到server端做where过滤。
④ ICP可以用于MyISAM和InnnoDB存储引擎
⑤ MySQL 5.6版本的不支持分区表的ICP功能，5.7版本的开始支持。
⑥ 当SQL使用覆盖索引时，不支持ICP优化方法。

简单来说，就是通过索引找到对应数据后，再把where条件后，联合索引有的列继续判断过滤一部分数据后，再去回表查询，减少IO次数

数据库设计

三大范式

第一范式：有主键，具有原子性，字段不可分割
这user表就不满足第一范式，因为地址还可以再拆分成省市区

CREATE TABLE `user` (
  `user_id` varchar(50) NOT NULL COMMIT '人员编号',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `mobile` varchar(11) DEFAULT NULL COMMENT '电话',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `address` varchar(50) DEFAULT NULL COMMENT '地址（如湖南省长沙市岳麓区XXX街道XXX）'
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

第二范式：确保每列都和主键完全依赖
在这里插入图片描述
第三范式：确保每列都和主键列直接相关，而不是间接相关
部门信息表 ：每个部门有部门编号（dept_id）、部门名称、部门简介等信息。
员工信息表 ：每个员工有员工编号、姓名、部门编号。列出部门编号后就不能再将部门名称、部门简介
等与部门有关的信息再加入员工信息表中。

在这里插入图片描述

调优

第1步：选择适合的 数据库管理系统 DBMS（数据量很大可以考虑选择收费的SQL service， pgSQL等）
第2步：优化表设计（三大范式等）

  • 增加冗余字段
  • 优化数据类型
  • 对整数类型数据进行优化
  • 对于非负型的数据（如自增ID、整型IP）来说，要优先使用无符号整UNSIGNED来存储。因为无符号存储的数值范围更大。
  • 既可以使用文本类型也可以使用整数类型的字段，要选择使用整数类型
  • 避免使用TEXT、BLOB数据类型
  • 避免使用ENUM类型
  • 使用TIMESTAMP存储时间
  • 用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
    第3步：优化逻辑查询（对SQL语句的优化，子查询等）
    第4步：优化物理查询（对索引的优化，使用上索引）
    第5步：使用 Redis 或 Memcached 作为缓存
    第6步：库级优化
  1. 分库
  2. 经典的数据库拆分方案，主库负责写，从库负责读。
    在这里插入图片描述

在这里插入图片描述

事务

事务四大特性

  • 原子性（Atomicity）：事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。
  • 一致性（Consistency）：事务必须使数据库从一个一致状态变换到另外一个一致状态，常见例子，银行转账场景。
  • 隔离性（Isolation）：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
  • 持久性（Durability）：一个事务一旦提交成功，它对数据库中数据的改变将是永久性的，接下来的其他操作或故障不应对其有任何影响。

事务分类

个人理解：
显式事务：关闭自动提交事务属性，使用手动开启事务（START TRANSACTION 或者 BEGIN），提交事务方式（COMMIT）
隐式事务：通过自动提交事务的方式执行增删改操作

事务并发问题

  • 脏读
    对于两个事务 Session A、Session B，Session A 读取了已经被 Session B 更新但还没有被提交的字段。
    之后若 Session B 回滚 ，Session A 读取的内容就是错误的数据。
  • 不可重复读
    对于两个事务Session A、Session B，Session A 读取了一个字段，然后 Session B 更新了该字段。 之后
    Session A 再次读取同一个字段， 值就不同了。那就意味着发生了不可重复读。
  • 幻读（ Phantom ）
    对于两个事务Session A、Session B, Session A 从一个表中读取了一个字段, 然后 Session B 在该表中插
    入    了一些新的行。 之后, 如果 Session A 再次读取同一个表, 就会多出几行。那就意味着发生了幻读。

MySQL隔离级别

  • READ UNCOMMITTED ：读未提交
    在该隔离级别，所有事务都可以看到其他未提交事务的执行结
    果。不能避免脏读、不可重复读、幻读。
  • READ COMMITTED ：读已提交，
    它满足了隔离的简单定义：一个事务只能看见已经提交事务所做
    的变化。可以避免脏读，但不可重复读、幻读问题仍然存在。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。
  • REPEATABLE READ ：可重复读 MySQL默认隔离级别，（MySQL在此隔离级别下就可以解决幻读）
    事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。因为针对的是某条数据，所以可以避免脏读、不可重复读，但幻读问题仍然存在。这是MySQL的默认隔离级别。
  • SERIALIZABLE ：可串行化
    确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止
    其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避免脏读、不可重复读和幻读。
    在这里插入图片描述
    实现方式：

事务隔离级别的实现基于 锁机制 和 MVCC，不同的隔离级别对锁的使用是不同的
select语句均是快照读（MVCC解决）
delete / update / select …for update 等语句是加锁实现的

读已提交： 把释放锁调整到事务提交之后，在事务提交前，其他事务是无法对该行数据进行任何操作（包括读)，效率极低，于是MySQL使用了一个并发版本控制机制，叫做MVCC。

可重复读： MySQL采取MVCC解决这个问题，在一个事务中第一次执行查询会创建一个Read View，之后的查询会复用之前的Read View。这样就保证了同一个事务多次读取数据时数据的一致性。

MySQL在可重复读隔离级别下解决幻读
MySQL的InnoDB 默认隔离级别是可重复读，它默认开启了间隙锁（只有RR隔离级别下才有间隙锁），使用临键锁（间隙锁 + 行锁）的方式来加锁，解决幻读。

MVCC对于 读已提交 和 可重复读 两种隔离级别差别就是：读已提交级别下，同一个事务下，每次查询都会创建一个 Read View，而可重复读只会复用第一次创建的Read View。

MySQL事务日志

redo log

redo log是事务持久性的保证
下面为一个update事务中，redo日志的整体流程
在这里插入图片描述

① 第 1 步：先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝；
② 第 2 步：生成一条重做日志并写入 redo log buffer，记录的是数据被修改后的值；
③ 第 3 步：当事务 commit 时，将 redo log buffer 中的内容刷新到 redo log file，对 redo log file采用追加写的方式；
④ 第 4 步：定期将内存中修改的数据刷新到磁盘中

redo log的刷盘策略

针对这种情况，InnoDB给出 innodb_flush_log_at_trx_commit 参数，该参数控制commit提交事务时，如何将 redo log buffer 中的日志刷新到 redo log file 中。它支持三种策略：
设置为0 ：表示每次事务提交时不进行刷盘操作。（系统默认master thread每隔1s进行一次重做日志的同步）
设置为1 ：表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ）
设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os操作系统自己决定什么时候同步到磁盘文件。

undo log

undo log是事务原子性的保证。在事务中更新数据 的前置操作其实是要先写入一个 undo log 。
下面是一个update事务中，redo 和 undo log的流程图
在这里插入图片描述

undo log的删除

  • 针对于insert undo log：
    因为insert操作的记录，只对事务本身可见，对其他事务不可见。故该undo log可以在事务提交后直接删除，不需要进行purge操作。
  • 针对于update undo log：
    该undo log可能需要提供MVCC机制，因此不能在事务提交时就进行删除。提交时放入undo log链表，等
    待purge线程进行最后的删除。

锁

表锁

上锁方式：

  1. 隐式上锁（默认，自动加锁自动释放）
    select //上读锁
    insert、update、delete //上写锁
  2. 显式上锁（手动）
    lock table tableName read;//读锁
    lock table tableName write;//写锁
  3. 解锁（手动）
    unlock tables;//所有锁表
行锁

上锁方式：

  1. 隐式上锁（默认，自动加锁自动释放）
    select //不会上锁
    insert、update、delete //上写锁
  2. 显式上锁（手动）
    select * from tableName lock in share mode;//读锁
    select * from tableName for update;//写锁

行锁类型：

  1. 记录锁
    记录锁是封锁记录，记录锁也叫行锁，例如：select * from user where id = 1 for update;它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。
  2. 间隙锁
    间隙锁基于非唯一索引，它锁定一段范围内的索引记录。使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据select * from user where id < 10 for update;即所有在 [1，10）区间内的记录行都会被锁住，所有id 为 1、2、3、4、5、6、7、8、9 的数据行的插入会被阻塞

在这里插入图片描述
select …for update正常情况会是记录锁，但是这条记录不存在，导致直接锁了上下区间，也就是8-12 开区间

在这里插入图片描述
3. 临键锁
select * from student where id <=8 and id > 3 for update
感觉就是带了一个闭区间··

悲观锁

顾名思义，就是对于数据的处理持悲观态度，总认为会发生并发冲突，获取和修改数据时，别人会修改数据。所以在整个数据处理过程中，需要将数据锁定。
悲观锁的实现，通常依靠数据库提供的锁机制实现，比如mysql的排他锁，select … for update来实现悲观锁。
如：select * from student where id =8
需注意，MySQL会将扫描到的所有行全部加锁，而不是id为8的行，所以最好是加索引

乐观锁

顾名思义，就是对数据的处理持乐观态度，乐观的认为数据一般情况下不会发生冲突，只有提交数据更新时，才会对数据是否冲突进行检测。
乐观锁的实现不依靠数据库提供的锁机制，需要我们自已实现，实现方式一般是记录数据版本，一种是通过版本号，一种是通过时间戳。给表加一个版本号或时间戳的字段，读取数据时，将版本号一同读出，数据更新时，将版本号加1。

MVCC

写的比较好理解，先参考这个吧~~~
https://blog.csdn.net/qq_36389060/article/details/124242769?ops_request_misc=&request_id=&biz_id=102&utm_term=MVCC&utm_medium=distribute.pc_search_result.none-task-blog-2allsobaiduweb~default-3-124242769.nonecase&spm=1018.2226.3001.4187

海边看浪丶
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
数据库 MySQL 学习笔记高级篇.md
09-01
数据库 MySQL 学习笔记高级篇.md
MySQL高级学习笔记（二）
qq_44733777的博客
02-04 1198
索引的创建与设计原则
MySQL学习笔记（B站网课：MySQL_基础+高级篇- 数据库 -sql -mysql教程_mysql视频_mysql入门_尚硅谷）已完结
m0_61672065的博客
09-13 1123
##本单元目标 一、为什么要学习数据库 二、数据库的相关概念 DBMS、DB、SQL 三、数据库存储数据的特点 四、初始MySQL MySQL产品的介绍 MySQL产品的安装★ MySQL服务的启动和停止★ MySQL服务的登录和退出★ ...
MySQL学习笔记：高级篇
Frank的技术世界
04-13 861
1. 存储引擎 1.1 InnoDB表引擎 1.2 MyISAM表引擎 1.3 其他的表引擎 2. 索引 2.1 索引对性能的影响 2.2 索引的使用场景 2.3 索引的类型 2.4 MySQL索引的创建原则 2.5 MySQL索引的注意事项 3. 查询优化 3.1 查找分析SQL查询慢的原因 3.2 优化查询过程中的数据访问 3.3 优化长难的查询语句 3.4 优化特定类型的查询语...
MySQL高级篇复盘笔记（一）【存储引擎、索引、SQL优化、视图、触发器、MySQL管理】
Java技术一点通
05-28 2382
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。建表时指定存储引擎CREATE TABLE 表名(字段 1 字段 1 类型 [ COMMENT 字段 1 注释 ] , . . . . . . 字段n 字段n类型 [ COMMENT 字段n注释 ]) ENGINE = INNODB [ COMMENT 表注释 ];查询当前数据库支持的存储引擎。
mysql高级篇学习笔记
java
07-28 6310
尚硅谷宋红康康师傅mysql高级篇学习
MySQL高级--个人笔记
代码的知行者
12-22 1465
视频课程：尚硅谷MySQL数据库高级，mysql优化，数据库优化_哔哩哔哩_bilibili Mysql 逻辑架构简介 1. 整体架构图 和其它数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引 擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。 各层介绍： 1.1 连接层 最上层是一些客户端和连接服务，包含本地 sock 通信和大多
MySQL笔记【高级篇】
[爪洼ing]的博客
12-14 5442
一、MySQL架构介绍 1、环境准备 1、安装MySQL，自己采用的Docker镜像安装MySQL # 1、拉取镜像 docker pull mysql:5.7 # 2、创建实例并启动 docker run -p 3306:3306 --name mysql \ -v /root/mysql/log:/var/log/mysql \ -v /root/mysql/data:/var/lib/mysql \ -v /root/mysql/conf:/etc/mysql \ -e MYSQL_ROOT_P
MySQL高级学习笔记
陈颜的博客
01-21 1176
文章目录MySQL基础篇学习笔记SQL性能下降的原因SQL的执行顺序索引索引的优劣势索引的分类索引的创建索引结构判断是否需要创建索引EXPLAINEXPLAIN之idEXPLAIN之select_typeEXPLAIN之tableEXPLAIN之typeEXPLAIN之possible_keysEXPLAIN之keyEXPLAIN之key_len MySQL基础篇学习笔记 SQL性能下降的原因 查询语句写的烂 索引失败 关联查询太多join（设计缺陷或不得已的需求） 服务器调优及各个参数设置（缓冲、线
Mysql学习笔记高级篇
lucky_myj的博客
08-26 282
Mysql学习笔记高级篇 一、查询数据-单表 1、单表查询指定字段 select field1,field2,…fieldn from table_name; 2、单表查询所有字段 select * from table_name; 3、避免重复数据查询 select distinct field from table_name; 4、数学自则运算数据查询 select name, sal12 from employee; (注：employee雇员表中name和sal字段分别为雇员姓名，月薪) 5、带格
Mysql高级
weixin_51146806的博客
03-24 356
Zset(有序集合） 不同的是每个元素都会关联一个double类型的分数（score）。redis正是通过分数来为集合中的成员进行从小到大的排序。 score相同：按字典顺序排序 有序集合的成员是唯一的,但分数(score)却可以重复。 ############### zrange 查看 zadd 添加 ########################### 127.0.0.1:6379> zadd myset 1 one #添加一个值 (integer) 1 127.0.0.1:6379>
尚硅谷MySQL高级_笔记
10-05
尚硅谷MySQL高级的视频学习笔记，由本人整理
【MySQL高级篇笔记-性能分析工具的使用 (中) 】
06-03
EXPLAIN的使用
MySQL入门到高级-宋红康版.zip
08-15
MySQL入门到高级-宋红康版 ----- 毕业设计，课程设计，项目源码均经过助教老师测试，运行无误，欢迎下载交流 ----- 下载后请首先打开README.md文件（如有），某些链接可能需要魔法打开。 ----- 毕业设计，课程设计，...
尚硅谷MySQL全套教程包含MySql核心技术篇和MySQL高级篇
08-10
MYSQL教程 MYSQL教程MYSQL教程 MYSQL教程尚硅谷MySQL全套教程包含MySql核心技术篇和MySQL高级篇。数据库视频教程，全套视频教程。
MySQL基础
CSDN_Admin0的博客
01-26 1820
文章目录MySQL基础1，数据库相关概念1.1 数据库1.2 数据库管理系统1.3 常见的数据库管理系统1.4 SQL2，MySQL2.1 MySQL安装2.1.1 下载2.1.2 安装(解压)2.2 MySQL卸载2.3 MySQL配置2.3.1 添加环境变量2.3.2 新建配置文件2.3.3 初始化MySQL2.3.4 注册MySQL服务2.3.5 启动MySQL服务2.3.6 修改默认账户密码2.4 MySQL登陆和退出2.4.1 登陆2.4.2 退出2.5 My
MySQL 高级篇
小默学习笔记
05-10 657
文章目录MySQL 逻辑架构简介存储引擎简介索引优化 MySQL 逻辑架构简介 MySQL 的逻辑架构：https://blog.csdn.net/z_ryan/article/details/82260663 从上到下依次是连接层，服务层，引擎层，存储层 假设现在有一个select 查询请求 第一步就是和数据库链接（连接层）， 链接完毕将查询请求给服务层，服务层对查询进行优化（服务层） 优化的结...
『MySQL 实战 45 讲』20 - 幻读是什么，幻读有什么问题？
Gavinjou大笨象的博客
05-04 1049
3. 当执行 select * from t where d=5 for update 的时候，6 个记录加上了行锁，还同时加了 7 个间隙锁，这样就确保无法再插入新的记录。
mysql 按字段查询重复的数据
最新发布
退役熬夜选手的博客
05-06 188
如果您只想识别具有重复值的列名，则方法 1 或 2 可能就足够了。如果您需要有关重复值的更多信息，例如每个值的计数，则方法 3 或 4 可能更好。主键是唯一索引的一种特殊类型，它还标识表中的每个行。在 MySQL 中，可以使用多种方法来查询按字段重复的数据。您可以使用 DISTINCT 关键字来删除结果中的重复行。该查询将为每个行返回列名的值，以及该值在同一列中出现的次数。该查询将返回所有具有重复值的列名的值，以及每个值的计数。该查询将返回所有具有重复值的列名的值。该查询将返回所有具有重复值的列名的值。
宋红康mysql高级篇笔记
05-13
宋红康的MySQL高级篇笔记是MySQL学习中非常重要的一本书籍，本书作者对MySQL数据库的高级应用进行了深入浅出的讲解，内容包括数据库设计、索引优化、高级查询、高可用性和备份恢复等方面。他的MySQL高级篇笔记所涉及...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值