MySQL进阶 - 易错知识点整理（待更新）

MySQL进阶 - 易错知识点整理（待更新）

本文根据CSDN MySQL进阶技能树整理的易错知识点，参考资料MySQL八股文连环45问,你能坚持第几问？

一、MySQL日志与事务

• MySQL事务的基本原理和总体流程，参考MySQL日志、事务原理 – undolog、redolog、binlog、两阶段提交，一文读懂MySQL的RedoLog写入机制和配置，详解MySQL事务（超详细）
  Note：

  • MySQL事务原理：在事务中更新数据时并不会每次在执行一次操作时第一时间将数据写入到数据库中，因为直接访问数据库是比较耗时的。
    
    实际上，在更新数据时
    • 先保存当前数据状态（通过undo log以链表形式保存）
    • 接着将操作写入内存；
    • 接着再写入redo log；
    • 接着在redo log prepare完成后生成对应的binlog；
    • binlog先被执行，redolog后被执行；
    • 如果redo log操作成功后事务会变更为commit状态，如果redolog或者binlog操作失败，则会使用undolog进行事务的回滚。

  

• MySQL中binlog与redolog的区别，参考MySQL日志、事务原理 – undolog、redolog、binlog、两阶段提交
  Note：

  • 1）redo log 介绍：

    • a）redolog是InnoDB存储引擎的日志（物理日志），InnoDB 存储引擎 通过参数 innodb_flush_log_at_trx_commit 为 redo log提供三种刷盘策略；
    • b）它的数据结构类似于循环队列，队列中的每个元素是一个文件（当前文件写满了会写入下一个文件中，而redolog刷盘到磁盘后文件中相应文件也会被删除）；
    • c）当事务提交时，会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。
      

  • 2）binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；redo log 是 Innodb 存储引擎实现的日志；

  • 3）binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。redo log 是循环写，日志空间大小是固定的，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

  • 4）binlog 用于备份恢复、主从复制；redo log 用于掉电等故障恢复。不可以使用 redo log 文件恢复，只能使用 binlog 文件恢复。 因为 redo log 文件是循环写，是会边写边擦除日志的，只记录未被刷入磁盘的数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件里擦除。binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据，都可以恢复，所以如果不小心整个数据库的数据被删除了，得用 binlog 文件恢复数据。

• MySQL事务中基于binlog和redo log的事务的两阶段提交流程，参考MySQL日志、事务原理 – undolog、redolog、binlog、两阶段提交，binlog、redolog、undolog，MySQL 重做日志 redo log
  Note：
  基于binlog和redo log的事务的两阶段提交（redo log prepare 和 redo log commit，而在redo log prepare后commit前将操作写入到binlog中）：

  • 1）redolog影响的是主库，而binlog涉及主从复制，影响的是从库。

  • 2）假设没有redo log两阶段，直接提交redo log，则在执行一条更新语句时，此时宕机了，有两种情况会存在问题：

    • a）redolog刷盘成功，但binlog没有：主库由于有redolog的存在，能够恢复，而binlog中并没有相关的更新语句，导致从库中丢失了本次更新
    • b）binlog刷盘成功，但redolog没有：从库由于有binlog的存在，记录了更新，binlog 会被复制到从库，从库执行了这条更新语句，而主库的redolog还没刷盘成功，导致崩溃后没法恢复，主库丢失了本次更新。
      

  • 3）如果使用两阶段提交，就不会存在问题：

    • a）redolog在prepare时MySQL宕机了，重启时发现binlog和redolog prepare不完整 + binlog不完整 ，会回滚事务；
    • b）binlog在写入时MySQL宕机了，重启后通过XID检查binlog是否完整，如果XID不完整则回滚事务，如果binlog完整则redolog prepare也完整，事务如果更新完数据即会变成commit状态

    具体流程如下：
    

  • Note：成功更新完数据后事务会变为commit状态，这里注意commit是个状态而不是一个动作，因为log在写入磁盘的过程中也有可能发生异常，断电等问题，导致在写redo log的时候没有写完（这相当于事务没有commit成功），此时MySQL下次再恢复的时候就没有必要考虑这个事务的完整性，**因为状态并不是commit，都写入磁盘上才表示redo log写成功，状态才变成commit

• MySQL事务中undolog日志的作用，参考binlog、redolog、undolog
  Note：

  • InnoDB事务修改数据之前会记录undo log并且持久化，undo log通过回滚事务指针形成了链表。undo log 有三个使用场景：

    • 1）通过rollback主动回滚事务；
    • 2）MVCC（多版本并发控制） ；
    • 3）崩溃恢复未完成的事务通过undo log回滚。

  • undo log日志有两种 insert 类型 和 update 类型，insert 类型记录了主键id，update 类型记录了修改前的数据。

• 通用查询日志（记录MySQL的所有连接和语句，默认是关闭；设置 general_log = 1, 配置general_log_file路径），可参考MySQL 数据库管理之 — 日志查询

• 慢查询日志（记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便提醒优化；设置slow_log = 1, 配置slow_log_file路径），可参考MySQL 慢查询，MySQL 数据库管理之 — 日志查询

• 错误日志（记录当MySQL启动、停止或运行时发生的错误信息；配置log_error路径），可参考MySQL 数据库管理之 — 日志查询

• 二进制日志（记录了数据的更改，可用于数据恢复；配置log_bin路径），可参考MySQL 数据库管理之 — 日志查询，MySQL二进制日志 - 恢复数据

二、备份与恢复

• 备份和恢复（数据库备份可以分为物理备份和逻辑备份），可参考MYSQL的备份和恢复，MySQL 数据库管理之 — 备份与恢复
  Note：
  • 造成数据丢失的原因：程序错误；人为操作错误；运算错误磁盘故障；灾难（如火灾、地震)和盗窃。
  • 物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。这种类型的备份适用于在出现问题的时候需要快速恢复的大型重要数据库。
    物理备份又可以成为冷备份（脱机备份）、热备份（连接备份）和温备份
    ① 冷备份 (脱机备份) ：是在关闭数据库的时候进行的（tar）
    ② 热备份 (联机备份) ：数据库处于运行状态，依赖于数据库的日志文件（mysqlhotcopy mysqlbackup）
    ③ 温备份 ：数据库锁定表格(不可写入但可读)的状态下进行备份操作（mysqldump）
  • 逻辑备份是对数据库逻辑组件的备份，表示为逻辑数据库结构。这种类型的备份适用于可以编辑数据值或表结构。可分为完全备份、差异备份和增量备份。
    ①完全备份：每次对数据库进行完整的备份
    ②差异备份：备份自从上次完全备份之后被修改过的文件
    ③增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份
• 灾难恢复，可参考MYSQL的备份和恢复，MySQL 数据库管理之 — 备份与恢复，MySQL之—崩溃-修复损坏的innodb: innodb_force_recovery
  Note：
  增量恢复类型
  • 一般恢复： 将所有备份的二进制日志内容全部恢复
  • 断点恢复：
    • 基于位置恢复： 数据库在某一时间点可能既有错误的操作也有正确的操作，可以基于精准的位置跳过错误的操作；在发生错误节点之前的一个节点，上一次正确操作的位置点停止。
    • 基于时间点恢复：跳过某个发生错误的时间点实现数据恢复；在错误时间点停止，在下一个正确时间点开始。

三、用户和权限

• 创建用户1（GRANT可创建带有明文密码的用户，可赋予用户在某表上的增/删/查的权限），创建用户2，可参考详细介绍MySQL中的用户与权限管理
  Note：
  • CREATE USER 'kangshifu'@'localhost' IDENTIFIED BY '123456';，主机名为localhost，密码为123456
  • GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';
    • ALL PRIVILEGES：赋予所有权限
    • ON *.*：指定权限针对所有库和表
    • joe@'localhost’表示joe用户，@后面接跟制的主机（比如192.168.136.128），可以是P、P段、域名以及%，%表示任何地方。
• 用户授权（grant select on table employee to joe），可参考详细介绍MySQL中的用户与权限管理
• 创建角色（create role：为相同权限的用户统一用一个角色来管理），可参考详细介绍MySQL中的用户与权限管理
  Note：
  • 引入角色的目的是方便管理拥有相同权限的用户，比如：

    #Joe 现在是团队的 DBA，公司数据分析组有 Fred、Alice、James、Jone 四位成员，现在Joe需要给数据分析组授权，允许他们 查询 MySQL 8 服务器 goods 数据库中的所有表
    create role analysis;
    grant analysis to fred, alice, james, jone;
    grant select on goods.* to analysis;
    flush privileges;
    

  • 当我们需要对业务重新整合的时候，可能就需要对之前创建的角色进行清理，删除一些不会再使用的角色，比如DROP ROLE analysis, manager。
• 查看用户权限（SHOW GRANTS FOR 'username'@'hostname';），可参考查看用户权限
• 撤销权限（收回 fred 对 trade 表的查询权限：revoke select on trade from fred;），可参考MySQL撤销（revoke）用户权限
• 修改口令（MySQL 8.0用alter不用update），删除用户（drop），可参考MySQL修改用户密码，mysql如何查看用户及其权限
  Note：
  • MySQL 8.0以下版本：update mysql.user set password = password('newPassword') where user = 'root' and host = 'localhost';
  • MySQL 8.0及以上版本：Fred忘记密码Joe帮忙修改，Joe 准备将这个账户的口令初始化为goods123fred ， 并设置为登录后修改新口令，语句如下alter user 'fred'@'%' identified by 'goods123fred' password expire;
• 限制用户使用资源alter user .. with ..，可参考MySQL 资源限制
  Note：
  • MAX_QUERIES_PER_HOUR：某用户每小时可以执行的查询次数
    MAX_UPDATES_PER_HOUR：某用户每小时可以执行的修改语句次数
    MAX_CONNECTIONS_PER_HOUR：某用户每小时最多可以连接多少次
    MAX_USER_CONNECTIONS：某用户可同时连接到数据库的会话数量
  • Joe 需要限制数据分析组（role analysis）的用户, 每小时查询次数不能超过10000次：alter user analysis with MAX_QUERIES_PER_HOUR 10000;

四、深入索引

参考MySQL索引的概念以及七种索引类型介绍，MySQL的索引（普通索引、唯一索引，主键索引、组合索引、全文索引、空间索引）相关操作

• 唯一约束（Joe 需要确保同一个类型(category_id)下没有重名(name)的商品，可以建立由两个字段构成的、带唯一约束的联合索引: alter table goods add unique index (category_id, name);），可参考MySQL索引的创建与使用
• 全值匹配（现在有大量根据商品名获取价格的查询select price from goods where name = '...''，可以通过为查询的字段（name和price）增加索引来优化sql查询：alter table goods add index (name, price);），可参考全值匹配（可以优化查询、修改语句）
• 左匹配（关于联合索引在查询性能中的优化，如果发现有大量查询 select id, category_id, name, price from goods where name=? and category_id=?， 性能很可以通过将该查询改写为 select id, category_id, name, price from goods where category_id=? and name=?;来优化），可参考Mysql最左匹配原则
  Note：
  • 索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量是多个，比如(a,b,c)这3个字段构成的联合索引，B+树如下图所示。可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2；而在a相等时，b是有序的。
    
  • 全值匹配查询时（同时用到a,b,c，则可通过(a,b,c)联合索引来查询，where子句几个搜索条件顺序调换不影响查询结果，因为Mysql中有查询优化器，会自动优化查询顺序）：

    select * from table_name where a = '1' and b = '2' and c = '3' 
    select * from table_name where b = '2' and a = '1' and c = '3' 
    select * from table_name where c = '3' and b = '2' and a = '1' 
    ......
    

  • 最左连续匹配原则：
    下面这些语句，都从最左边a开始连续匹配，因此可以用到索引

    select * from table_name where a = '1' 
    select * from table_name where a = '1' and b = '2'  
    select * from table_name where a = '1' and b = '2' and c = '3'
    

    下面这些语句，没有从最左边a开始匹配，最后查询没有用到索引，用的是全表扫描

    select * from table_name where  b = '2' 
    select * from table_name where  c = '3'
    select * from table_name where  b = '1' and c = '3' 
    

    下面这些语句，没有从最左边连续匹配，则只会用到a列的索引，b列和c列都没有用到。

    select * from table_name where a = '1' and c = '3' 
    

  • 其他一些原则：
    select * from test where a <10;会走索引，但是select * from test where a >10，原因是预分析sql的时候认为a>10的数据量太大，不如全表扫描，就会不走索引。
• 组合索引（联合索引），可参考MySQL索引的创建与使用，Mysql最左匹配原则
• 空间索引（几何索引），参考MySQL使用空间索引问题，MySQL的索引（普通索引、唯一索引，主键索引、组合索引、全文索引、空间索引）相关操作
  Note：
  • MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型
    空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。
  • 假设有个shop表

    create table shop (
    id int primary key auto_increment,
    location GEOMETRY
      -- ...
    )
    

    给 location 字段加上几何索引：

    alter table shop modify location  GEOMETRY not null;
    alter table shop add INDEX geo_index(location);
    

• 全文索引（主要是为了快速检索大文本数据中的关键字的信息。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引，基于倒排索引，类似于搜索引擎。MyISAM存储引擎支持全文索引，InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引；ALTER TABLE table_name ADD FULLTEXT (column);），参考MySQL索引的概念以及七种索引类型介绍
• 对索引进行隐藏（在删除掉某个索引之前不确定是否有程序还在使用它，可以通过alter table shop alter index description invisible ;将索引隐藏，观察确认没有影响后再执行drop index）
• 建立表达式索引（如果关于date(payment_date)查询次数比较多时，可以建立表达式索引:alter table payment add index idx_payment_date((date(payment_date)));）

五、SQL高级技巧

• 获取连续区间（利用with recursive temp(id,pid) as ...定义带查询字段id,pid的递归函数，最终temp返回的是一张带id,pid的虚表），树结构溯根，可参考mysql 递归函数with recursive的用法，MySQL8-WITH RECURSIVE递归查询父子集
  Note：
  • with recursive temp(id,pid) as (..clause..)中的clause一般伴随着union或union All一起使用，参考SQL UNION 和 UNION ALL 操作符
  • 生成斐波那契队列：

    WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
    (
      SELECT 1, 0, 1
      UNION ALL
      SELECT n + 1, next_fib_n, fib_n + next_fib_n
        FROM fibonacci WHERE n < 10
    )
    SELECT * FROM fibonacci;
    ---
    +------+-------+------------+
    | n    | fib_n | next_fib_n |
    +------+-------+------------+
    |    1 |     0 |          1 |
    |    2 |     1 |          1 |
    |    3 |     1 |          2 |
    |    4 |     2 |          3 |
    |    5 |     3 |          5 |
    |    6 |     5 |          8 |
    |    7 |     8 |         13 |
    |    8 |    13 |         21 |
    |    9 |    21 |         34 |
    |   10 |    34 |         55 |
    +------+-------+------------+
    

  • 树结构溯根：

    with recursive t(id, pid, val) as (
    select id, pid, val
    from node
    where id = $1
    union all
    select node.id, node.pid, node.val
    from node
             join t on node.id = t.pid)
             
    select node.id, node.pid, node.val
    from node
             join t on node.id = t.id;
    

• 排名函数rank():TopK问题，比如给出每个部门工资最高的前5个员工的信息；rank()常伴随着order by和partition by一起使用，参考Mysql常用函数之Rank 排名函数
• 透视表，可参考Sql 实现数据透视表功能，MySQL之数据分组与数据透视表，MySQL extract()函数
• 写入和冲突（try ... catch ... finally）
• 事务（4大属性:原子性/一致性/隔离性/持久性; 4大隔离级别:Read U / Read C / Repeat Read / Serializable); 不同隔离级别产生的问题:脏读/不可重复读/幻读，参考详解MySQL事务（超详细）
  Note：
  • 脏读:
    对于两个事务 T1和T2, T1 读取了已经被 T2 更新（update） 但还没有被提交（commit）的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的（ T2 update $\to$ T1 read $\to$ T2 rollback ）.
  • 不可重复读:
    对于两个事务T1和T2,T1 读取了一个字段, 然后 T2 更新（update）了该字段之后, T1再次读取同一个字段, 值就不同了（T1 read $\to$ T2 update $\to$ T1 read）.
  • 幻读:
    对于两个事务T1和T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行之后, 如果 T1 再次读取同一个表, 就会多出几行（T1 read $\to$ T2 insert $\to$ T1 read）

六、过程化编程

• 判断：IF（if ... then ... end if）
• 循环：Loop（[label] Loop...end Loop [label]，其中ITERATE [label]相当于continue，LEAVE [label]相当于break）
• 循环：REPEAT（类似do while，即REPEAT UNTIL语句中不管是否满足给定条件，首先会执行一次statements），参考mysql repeat循环语句
• 循环：WHILE（WHILE ... DO ... END WHILE）
  Note：
  • REPEAT循环语句

    REPEAT
    Statements;
    UNTIL expression
    END REPEAT
    

  • WHILE语法结构：

    [label:] WHILE search_condition DO
    statement list
    END WHILE [label]
    

• 游标简介（游标就像指针一样，可以定位操作查询的数据，通过CLOSE 游标名关闭游标，用OPEN 游标名打开游标），游标的使用

七、设计优化

八、运维与架构

九、查询优化

十、写优化

十一、命令行工具

十二、服务器优化