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

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

一、MySQL日志

• 通用查询日志（记录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;
    1
    2
    3
    4
    5

  • 当我们需要对业务重新整合的时候，可能就需要对之前创建的角色进行清理，删除一些不会再使用的角色，比如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' 
    ......
    1
    2
    3
    4

  • 最左连续匹配原则：
    下面这些语句，都从最左边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'
    1
    2
    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' 
    1
    2
    3

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

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

  • 其他一些原则：
    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
      -- ...
    )
    1
    2
    3
    4
    5

    给 location 字段加上几何索引：

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

• 全文索引（主要是为了快速检索大文本数据中的关键字的信息。字段长度比较大时，如果创建普通索引，在进行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 |
    +------+-------+------------+
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23

  • 树结构溯根：

    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;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12

• 排名函数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
    1
    2
    3
    4

  • WHILE语法结构：

    [label:] WHILE search_condition DO
    statement list
    END WHILE [label]
    1
    2
    3

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

七、设计优化

八、运维与架构

九、查询优化

十、写优化

十一、命令行工具

十二、服务器优化