首页  思维导图  详情

A_108_MySql

2021-04-09 10:49:26   0  举报





AI智能生成

全面、高效的知识图谱：A_108_MySql!! 全面又深度的提升认知，达到实际应用的目的! 建议先纵观全局，掌握好大方向。再根据自己的需要，针对性的学习某一个点，最后做到逐步由点及面。

高效工作方式

组织结构

风格样式

流程图

系统架构

作者其他创作

大纲/内容

优化案例

获取大数据表的总行数

存储过程

优点

减少数据库连接次数

无需重新编译，而SQL需要经过解析编译再执行

提高代码安全，减少SQL注入

缺点

移植性较差

开发调试复杂

SQL本身是一种结构化查询语言，但不是面向对象的的，本质上还是过程化的语言，面对复杂的业务逻辑，过程化的处理会很吃力

高可用

主从同步

主从不一致如何处理（存在时延）

1. 若业务可接受则可以忽略

2. 强制读主，高可用主库，用缓存提高性能

3. 在缓存中记录哪些记录发生过写请求，来路由读主还是读从

读写分离

主从同步延时

1. 分库，将一个主库分为多个主库，每个主库的写并发压力减少了几倍，此时主从延时可以忽略不计

2. 打开MySQL支持的并行复制，多个库并行复制。若某个库的写并发很高，单库达到2k/s，则并行复制还是无意义

3. 重写代码，插入数据后立马查询可能查不到

监控

主从延迟监控

精确做法，判断主从二进制文件的偏移量

简单做法，通过查看从节点中的Seconds_Behind_Master查看

主从数据一致性检测

TPS/QPS

Innodb线程堵塞

MySql

事务

原子性（A）

一致性（C）

隔离性（I）

持久性（D）

隔离级别

读未提交（Read UnCommit）

读已提交（Read Commited）

可重复读（Repetable Read）

串行化（Serialazable）

存储引擎

InnoDB

并发控制

锁

共享锁

排他锁

共享锁之间不互斥，简记为：读读可以并行排他锁与任何锁互斥，简记为：写读，写写不可以并行

数据多版本

redo日志

用于保障已提交事务的ACID特性

1. 事务提交的数据先写到redo日志里（此时变成了顺序写），再定期将数据刷到磁盘上，即随机写变为顺序写

2. 若数据库崩溃，还没来得及刷盘的数据，在数据库重启后，会重做redo日志里的内容，以保证已提交事务对数据产生的影响都刷到磁盘上。

undo日志

当事务回滚时，或者数据库奔溃时，可使用undo日志来恢复数据

对insert操作，undo日志记录新数据的PK(ROW_ID)，回滚时直接删除

对于delete/update操作，undo日志记录旧数据row，回滚时直接恢复；

用于保障未提交事务不会对数据库的ACID特性产生影响

回滚端 rollback segment

行数据的三个内部属性

DB_TRX_ID，6字节，记录每一行最近一次修改它的事务ID

DB_ROLL_PTR，7字节，记录指向回滚段undo日志的指针

DB_ROW_ID，6字节，单调递增的行ID

InnoDB为何能够做到这么高的并发

锁类型

锁定索引记录+间隔，防止幻读

记录锁 Record Locks

间隙锁 Gap Locks

插入意向锁

临键锁 Next-Key Locks

自增锁 Auto-inc Locks

共享/排它锁(Shared and Exclusive Locks)

意向锁(Intention Locks)

分类

意向锁协议(intention locking protocol)

事务要获得某些行的S锁，必须先获得表的IS锁

事务要获得某些行的X锁，必须先获得表的IX锁

MyISAM

不支持外键

七种连接

索引

定义

优势

劣势

单值索引

唯一索引

复合索引

覆盖索引

索引结构

B树索引

Hash索引

全文索引

R-Tree 索引

对于分组、排序、范围查找的查询哈希型的索引，时间复杂度会退化为O(n)，而树型的“有序”特性，依然能够保持O(log(n)) 的高效率

数据结构

B树

叶子节点，非叶子节点，都存储数据

中序遍历，可以获得所有节点

B+树

非叶子节点不再存储数据，数据只存储在同一层的叶子节点上

叶子之间，增加了链表，获取所有节点，不再需要中序遍历

对比

1. 范围查找，定位min与max之后，中间叶子节点，就是结果集，不用中序回溯

2. 叶子节点存储实际记录行，记录行相对比较紧密的存储，适合大数据量磁盘存储；非叶子节点存储记录的PK，用于查询加速，适合内存存储；

3. 非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同内存的情况下，B+树能够存储更多索引；

性能分析

mysql 查询优化器

常见瓶颈

CPU

explain

能做什么

表的读取顺序

数据读取操作的操作类型

可能用到哪些索引\t

实际用到哪些索引

表之间的引用

每张表有多少行被优化器查询

列名解析

id相同

id 不同

ID相同又不同

select_type

Simple

Primary

SubQuery

Derived

Union

Union result

table

type

system

const

equ_ref

ref

range

index

all

possible_keys

key

key_len

rows

Extra

Using filesort

Using temporary

Using index

using where

using join buffer

impossible where

select table optimized away

distinct

哪些情况需要创建索引

主键自动创建唯一索引

频繁作为查询条件的字段应该创建索引

查询中与其他表关联的字段，外键关系创建索引

频繁更新的字段不合适建索引

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

查询中统计或分组的字段

查询截取分析

查询优化

永远是小表驱动大表

order by

若排序的字段不在索引列上，则mysql会启用两种filesort算法

双路排序

单路排序

优化策略

增大sort_buffer_size参数

增大 max_length_for_sort_data 参数

group by

慢查询日志

开启 set global slow_query_log=1;

阀值 long_query_time

日志分析工具 mysqldumpslow