MYSQL知识点梳理

1. SQL基础

1.1 增删改查

• Insert（insert into table_name (column1,column2…）values (value1,value2…)
• Select
  • Join(交叉连接、内连接、左连接、右连接)
    • Cross join ：若A表有m行，B表有n行，返回结果m*n行，返回所有可能结果
    • Inner join：需要满足过滤条件
    • Left join：以左表的数据为基础，匹配右表中的每一行，若匹配成功，就将左右表的行组成新的一条数据返回，若匹配不成功，以左表＋null值的方式返回一条数据
    • Right join：以右表的数据为基础，匹配左表中的每一行 ，若匹配成功，将左右表的行组成新的一条数据返回，若匹配不成功，以右表+null值的方式返回一条数据
  • Having用于group by之后，过滤分组后数据，where过滤结果集数据
• Delete
  • Delete from table_name where … :删除行
  • Truncate table_name ：清空表
  • Drop table_name :删除整个表数据和表结构
• Update（update table_name set…）

连表查询中，先根据on过滤从表的数据，主表的数据将全部保留，生成临时表—>再根据where条件对临时表过滤—>聚合函数进行运算—>having根据运算后的临时表进行过滤

1.2 连表查询优化

https://zhuanlan.zhihu.com/p/582824436?utm_id=0

MYSQL怎么做join查询（关联查询算法）：

• 简单嵌套循环连接（非默认）：从驱动表A中取出一条数据，循环扫描表B，匹配成功放入result
• 块嵌套循环连接（mysql8之后废弃）：使用join buffer缓存驱动表A的部分数据，批量匹配
• Hash join（mysql8后默认）：适用于没有建立索引的大表，优化器在两个表中选择较小的表利用join key在内存中建立散列值，然后扫描较大的表
• 索引嵌套循环匹配：前提是被驱动表关联字段建立了索引，驱动表匹配条件与被驱动表索引匹配

优化：

• 确保被驱动表的连接字段建立了索引
• 使用left join或right join用小表驱动大表
• 避免使用select *，会用到join buffer
• 在sql查询计划的extra中避免出现using join buffer，代表没有使用索引
• 关联查询效率>子查询效率
• 避免使用三张表以上的关联查询

1.3 三大范式

• 1NF：原子性，存储的数据应该具有不可再分性
• 2NF：满足1NF的条件下，满足唯一性，非主键值必须完全依赖主键值，不能部分依赖
• 3NF，满足2NF的条件下，满足独立性，非主键值不能依赖另一个非主键值
  为什么反范式：
  范式化的设计会避免数据冗余，但会经常出现连表查询，范式化设计会增加表的数量，反范式化可以提高查询性能

2. 存储引擎

2.1 MYSQL基本架构

2.2 不同存储引擎区别

MYSQL默认存储引擎INNODB与MYISAM区别：

• innodb支持行级别锁，myisam只支持表级，在并发场景下innodb性能更高
• innodb支持事务，定义了四个隔离级别，支持MVCC
• innodb支持外键
• innodb有自带的redolog日志，可以实现崩溃恢复

当事务要求较低、对只读操作或写操作很少、需要频繁备份的场景用myisam

3. 索引

https://blog.csdn.net/agonie201218/article/details/131825718

索引（等价于目录） MYSQL中数据以数据页为最小存储单元，每页大小16KB；MYSQL默认主键自增->原因：加快查询效率，若不自增->造成页分裂（例如：往数组中间插入数据，其他数都往后移）

3.1 索引分类及数据结构

分类：

• 按照数据结构分类：B+树索引、hash索引（等值查询很快，不适用范围查询、排序等）、全文索引
• 按照物理存储分类：聚簇索引（索引只挂主键，叶子结点放整行数据）、二级索引（叶子结点放主键id）
• 按照字段特性分类：唯一索引（非空不重复）、普通索引、主键索引、前缀索引
• 按照字段个数分类：单列索引、联合索引

数据结构：B+树：特点：

• 矮胖：减少IO->千万级别数据量只需要3-4层高度
• 叶子结点用双向链表连接：便于范围查询、顺序查询
• 非叶子结点不存储数据：查询效率稳定，节省空间

3.2 联合索引

**索引覆盖：**一种现象：当查询的字段在索引中全部覆盖，不用回表（在普通索引中查到的数据不完全，拿到主键id再去主键索引查到全部数据）

全表扫描和回表哪个更快：看数据量，数据量大回表更快 优化器做决策

**最左匹配原则：**举例：有name、age联合索引，select …where age=… 不符合最左匹配原则，因为name全局有序，age局部有序

走不走索引不一定，若优化器认为走索引更快，跳过name查age，这种现象为索引跳跃（8.0新特性）

联合索引的最左匹配原则，在遇到范围查询的时候（比如>、<），就会停止匹配，也就是范围查询的字段用到了联合索引，后面的字段无法用到联合索引
例：select * from table where a > 1 and b = 2
原因：联合索引中a>1的记录是有序的，但此范围里b的值是无序的，因此b无法用到联合索引 但遇到>= <= between
like前缀匹配的范围查询，并不会停止匹配 例：select * from table where a >= 1 and b = 2
原因：联合索引中a>=1有序，b无序，但在a=1时，b是有序的

索引下推： 使用索引过滤数据 例：select * from table
where name > ‘王五’ and age > 22（name age联合索引）

在MYSQL5.6之前，执行顺序如下：先查询name > ‘王五’，找到id回表查询整行数据再根据age过滤
缺点：不符合正常逻辑，应该用索引过滤数据->索引下推

3.3 索引失效

• 经过计算或特殊处理无法走索引
  • 不符合最左前缀匹配原则
  • 索引列进行了计算或者隐式转换（！= is not null）
  • where子句中or左或右条件列不是索引列
  • 模糊查询like % 或者like %A%，有可能会失效（当查询的都是索引列，也可能会走二级索引）
• MYSQL经过判断后认为不走索引效率更高
  • 查询范围太大

3.4 索引设计准则

• 一个表不宜建立太多索引
• where后面经常使用的字段应建立索引
• Group by 和order by 后经常使用的字段建立索引
• 经常更改的字段不宜建立索引
• 选择区分度高的字段建立索引

效率：count（）=count（1）>count（主键）>count（字段）
count（）和count（1）统计所有数据行，不忽略null值
count（主键），主键不能为null，直接累加行数据，count（字段）需要把值取出来判断是否为null

4. 事务隔离级别和MVCC

4.1 事务特性（ACID）

事务：一次操作要么全部成功要么全部失败

• 原子性：事务中的操作要么都发生要么都失败
  • 依靠undolog回滚日志实现
• 一致性：数据前后数据完整性一致
  • 依靠其他三个特性实现
• 隔离性：多个并发事务之间相互隔离
  • 依靠锁或者MVCC实现
• 持久性：一个事务一旦提交对数据库中数据的改变就是永久的
  • 依靠redolog重做日志实现

4.2 事务隔离级别

• 读未提交（RU/Read Uncommitted）
  • 脏读：一个事务读到了另一个事务未提交的数据
• 读已提交（RC/Read Committed）
  • 不可重复读：多次读取数据结果不一致
• 可重复读（RR/Repeatable Read）
  • 幻读：多次读取 数据量不一致
• 串行化（S/Serializable）
  • 慢，性能低

4.3 MVCC

MVCC： 多版本（一条数据的版本链）并发（场景）控制（数据读取的结果）-> 解决不可重复读
版本链： undolog 和MYSQL针对每条数据行的隐藏字段（trx_id：最近修改事务id，roll_pointer，指向上一个版本的事务版本记录地址）

RR和RC区别： RC在每一次查询的时候都生成ReadView，RR只在第一次查询的时候生成

4.4 怎么解决幻读

MYSQL在RR隔离级别下彻底解决幻读了吗？ ->>>并没有完全解决
怎么解决：

• 快照读：普通的select，根据undolog版本链查
  • 通过MVCC，其他事务可以插入数据，但读不到
• 当前读：insert、update、delete、select for update、查出最新的数据
  • Select … for update通过加next-key lock锁，别的事务在锁范围内修改数据，会阻塞
    异常情况：
• 快照读MVCC：
  • 事务A查询id=5生成readview，此时没有数据
  • 事务B插入id=5的数据并且提交
  • 事务A update这条数据，此时这条数据形成新的版本链的隐藏列事务id改为A的事务id
  • 事务A再select就可以查到这条数据，产生幻读
• 当前读next-key lock：
  • 事务A先用普通select，先执行普通快照读语句
  • 事务B插入数据并提交
  • 事务A用当前读语句select …for update，产生幻读

5. 日志

5.1 三种日志

binlog->不支持事务和宕机恢复->出现redolog->怎么解决->记录数据页变化->可以前滚

• Binlog（逻辑日志）
  • 二进制日志，server层产生，记录所有数据的更改，追加写，不覆盖已有文件
  • 三种格式：
    • statement：记录sql语句，但动态函数主从执行结果不一样
    • row：记录数据结果变化，但若批量执行update，太多数据变化会导致binlog文件太大
    • mixed：混合使用
  • 用来数据备份和主从复制(开启两个线程，IO线程：负责网络传输数据，sql线程：解析binlog进行重放)
  • 缺点：
    • 不支持事务：binlog只能记录对哪张表的哪些数据做修改，不能记录数据页的修改，颗粒度不够（例：一条update影响三个数据页，执行过程突然宕机，binlog不知道执行到哪）
    • 效率不够高：解析执行sql耗时长
    • 不支持宕机恢复：不能前滚
• redolog（前滚日志）
  • 物理日志，innodb存储引擎层产生，记录磁盘数据页的变更，循环写
  • 服务宕机可用来同步数据
• undolog（回滚日志）
  • 用来做事务版本链

主从复制的原理：

• 主库将数据写入binlog，从库连接主库
• 从库开启IO线程向主库请求更新的binlog
• 主库开启binlog dump线程发送binlog，从库IO线程接受，写入中继日志
• 从库开启sql线程回放中继日志

5.2 Buffer pool和WAL（write ahead log）

例：update … Where id in (1,500000)
这些数据都是分布在不同的磁盘，造成大量磁盘IO，效率低
解决办法：

• 内存缓冲池buffer pool：写磁盘改成写内存，再把更新异步刷到磁盘
• WAL机制：磁盘的随机写改日志的顺序写
  • 带来问题->遇到事务回滚日志和内存数据不一致->解决->日志文件加入检查点
    暂

5.3 两阶段提交

MYSQL执行update语句时，日志和磁盘文件的修改顺序？如何保证一致性？

• 首先必须先修改undolog：更新数据先备份
• 先改redolog再改binlog：改完redolog宕机，主节点重启后根据redolog恢复数据，从节点只能通过binlog同步数据，导致从节点少数据
• 先改binlog再改redolog：从节点多数据
  解决方法：两阶段提交
  

处于prepare阶段的redolog既可以前滚也可以回滚，主要取决于binlog
原因：redolog记录数据页的变更，binlog一般是记录sql，MYSQL没办法仅凭数据页的变更推断出binlog日志是怎么记的

6. 锁

6.1 锁的分类

• 全局锁：用于全库逻辑备份
• 表级锁：
  • 表锁：lock tables，锁表
  • 元数据锁（MDL）：不用显示调用，自动加锁，crud加MDL读锁，改表结构加MDL写锁
  • 意向锁：对表中记录上行锁时，先对表上意向锁，可以快速判断表里是否有记录被加锁
• 行级锁：
  • 记录锁：对一行数据上锁，锁的是索引
  • 间隙锁：对一个范围上锁，不包含记录本身
  • Next-key lock ：记录锁+间隙锁

加锁规则：RC只有记录锁、RR有上述三种锁 当查询的值为主键或是唯一索引，有值时加行锁，没有值加间隙锁 当查询的值是普通索引，加间隙锁
当查询条件有索引，且是范围查询，加间隙锁

6.2 死锁优化

• 业务场景
  • 正常情况下用户首先用微信登录，查询手机号没有数据，然后进行插入，此时支付宝id为空
  • 再用支付宝进行登录，查询手机号有数据，update支付宝id
• 问题场景
  • 用户同一时刻用两个渠道登录，用select for update语句
• 出现问题
  • 死锁，sql日志排查dead lock
• 原因
  • 间隙锁不互斥，但是间隙锁和插入语句互斥
• 怎么解决
  • 降低隔离级别：默认RR，降低到RC
    • RC效率高
    • 从业务逻辑上避免幻读问题
  • 分布式锁
    • 先读后写一定有并发，数据库悲观锁性能差，用分布式锁Redis

7. MYSQL调优

7.1 架构调优

• 读写分离
• 主从架构
• 缓存（双写一致性）
• 分库分表

7.2 硬件调优

• 内存 Buffer pool 占比75%
• CPU数量 例：8核32G
• MYSQL版本 5.6到8.0有优化
• 连接池数量：最优CPU核心数*2

7.3 SQL优化

• 索引
  • 最左匹配原则
  • 索引覆盖、索引下推
  • 主键使用自增主键、避免页分裂
  • 长字段使用前缀索引
• SQL语句优化
  • 避免使用select *，全表扫描很慢
  • 不要重复查询，可以一次查询之后把数据当参数传递到其他方法
  • limit语句：如果limit 100000,20 很慢 ->> where id > 100000 limit 20
  • 多插入的情况用批量插入
  • 连表查询 小表驱动大表
  • 连接查询大于子查询
  • 字段类型选择，例：日期，尽量使用timestamp
    • DateTime：占8字节，精确到毫秒
    • Timestamp：占4个字节，精确到秒
    • Date：日期，占3个字节
  • 字段长度：能用tinyint就不用int，比如枚举类型

7.4 explain关键字

mysql> explain SELECT * FROM dept_emp WHERE emp_no IN (SELECT emp_no FROM dept_emp GROUP BY emp_no HAVING COUNT(emp_no)>1);
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys   | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | dept_emp | NULL       | ALL   | NULL            | NULL    | NULL    | NULL | 331143 |   100.00 | Using where |
|  2 | SUBQUERY    | dept_emp | NULL       | index | PRIMARY,dept_no | PRIMARY | 16      | NULL | 331143 |   100.00 | Using index |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+

• id：代表查询语句的序列号，序列号越大优先级越高，序号一样谁在前谁先执行，id为null最后执行
• type字段（效率从低到高）
  • ALL：全表扫描
  • Index：扫描了整个索引树，如使用了覆盖索引
  • Range：对索引列进行范围查询
  • ref：使用普通索引作为查询条件
  • eq_ref：连表查询时，被驱动表使用的主键或唯一索引
  • const：使用主键或唯一索引
  • system：const的一种特例
• extra字段
  • Using filesort：对结果用的是外部索引排序，不是按照索引次序从表里读取
  • Using temporary：需要创建一张内部临时表来处理，比如order by，group by
  • Using index condition：查询的列不完全被索引覆盖，用了索引下推
  • Using where Using index：没有满足最左前缀原则，但走了索引
  • Using index：索引覆盖，不用回表

7.5 慢sql日志

在生产环境或者压测时开启
查看慢sql日志是否开启（默认关闭）：SHOW VARIABLES LIKE ‘%slow_query_log%’；
开启指令：set global slow_query_log=1;
设置没有走索引的语句也记录：show VARIABLES like ‘%log_queries_not_using_indexes%’;
查看默认阈值：SHOW VARIABLES LIKE ‘%long_query_time%’;

Show processList：
查看当前mysql有没有压力，在执行哪些语句，执行了多久，有没有慢sql在执行，可以找出有问题的线程，kill掉有问题的线程

8. 分库分表

8.1 什么是分库分表分区

为什么要分库分表分区？

• 分库：主要解决的是并发量大的问题，数据库的连接数是有限的，当QPS过高导致数据库连接数不足，可以考虑分库
• 分表：主要解决的是数据量大的问题，单表数据量大导致查询和存储的性能太低，一般超过500W行或者2GB考虑分表
• 分区：数据量变大时，先考虑分区再考虑分表
  • innodb中表存储主要依赖两个文件，一个是.frm，存储表结构，一个是.ibd，存储表数据
  • innodb会将每个分区存储在不同的.ibd文件里
  • 表面看还是一张表，只不过数据存储在不同的位置（同一个.frm文件）
    拆分方法：
• 水平分库：把一个库的数据拆分到多个库中
• 垂直分库：根据不同的业务拆分不同的表到不同库中
• 水平分表：把一张表的数据拆分到多个表中
• 垂直分表：根据不同业务划分不同字段到不同的表中

8.2 怎么分库分表

• 开源工具：Apache ShardingSphere、mycat
• 分表字段选择：根据业务，避免数据倾斜
• 分表算法：
  • 直接取模：比如分128个表，直接对128取模
  • hash取模：若字段为字符串，可以先去哈希再取模
  • 一致性哈希：前两者如果如果需要二次扩容，表的数量发生变化需要重新取模，涉及数据迁移。一致性哈希可以按照常用的hash算法来将对应的key哈希到一个具有2^32次方个节点的空间中，形成成一个顺时针首尾相接的闭合的环形。所以当添加一台新的数据库服务器时，只有增加服务器的位置和逆时针方向第一台服务器之间的键会受影响。

8.3 分库分表产生的问题

分表后全局ID怎么生成：

• UUID：不是自增，查询慢
• 基于单表做主键自增：存在单点问题，一旦他挂了整个数据库就无法写入了
• 基于多个表+步长做主键自增：比如第一个表主键范围设置0-1999，第二个表设置2000-3999…，步长2000
• 雪花算法：常用，具有全局、唯一、高可用特点

分库分表后产生的问题：

• 不支持跨库事务
• 不能跨多表分页查询、排序
• 二次分表、一致性ID