mysql技术梳理思维导图模板_ProcessOn思维导图、流程图

主从复制

通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；保持一致

作用

主数据库出现问题，可以切换到从数据库。

可以进行数据库层面的读写分离。

可以在从数据库上进行日常备份。

解决问题

数据分布：随意开始或停止复制，并在不同地理位置分布数据备份

负载均衡：降低单个服务器的压力

高可用和故障切换：帮助应用程序避免单点失败

升级测试：可以用更高版本的MySQL作为从库

流程

主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；

从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；

从：sql执行线程——执行relay log中的语句；

解决方案

使用mysql-proxy代理

降低性能，不支持事务

使用AbstractRoutingDataSource+aop+annotation在dao层决定数据源

mybatis

通过mybatis plugin拦截sql语句，所有的insert/update/delete都访问master库，所有的select 都访问salve 还需要重写一下DataSourceTransactionManager，将read-only的事务扔进读库，其余的有读有写的扔进写库

使用AbstractRoutingDataSource+aop+annotation在service层决定数据源

可以支持事务.

数据库优化

结构优化

将字段很多的表分解成多个表

增加中间表

需要经常联合查询的表

增加冗余字段

表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

cpu飙升

看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行

找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成

kill 掉这些线程

进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL

每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升

分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

大表优化

限定数据的范围

读/写分离

主库负责写，从库负责读

缓存

使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存

分库分表

垂直分区

数据表列的拆分；某些列常用，另外一些列不常用

使得行数据变小，在查询时减少读取的Block数，减少I/O次数

会出现冗余，需要管理冗余列，并会引起Join操作；对于应用层来说，逻辑算法增加开发成本

水平分区

数据表行的拆分，水平拆分最好分库

优点

降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询次数

支持非常大的数据量存储，应用端改造也少

缺点

分片事务难以解决，跨界点Join性能较差，逻辑复杂

通常查询时需要多个表名，查询所有数据都需UNION操作

数据库分片

客户端代理

分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现

中间件代理

在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。

问题

事务支持

数据库本身的分布式事务管理

应用程序去协助控制

跨库join

分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。

跨节点的count,order by,group by以及聚合函数问题

与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并

可以并行执行，因此很多时候它的速度要比单一大表快很多

ID问题

自生成的ID无法保证在全局上是唯一的

在插入数据之前需要先获得ID,以便进行SQL路由，常见的主键生成策略

UUID

UUID非常的长，除占用大量存储空间外，最主要的问题是在索引上，在建立索引和基于索引进行查询时都存在性能问题。

跨分片的排序分页

排序字段非分片字段

在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序

SQL语句优化

优化WHERE子句

在 where 及 order by 涉及的列上建立索引

避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

避免在 where 子句中使用!=或<>操作符

避免在 where 子句中使用or 来连接条件

in 和 not in 也要慎用

避免在 where 子句中使用参数

避免在 where 子句中对字段进行表达式操作

避免在where子句中对字段进行函数操作

优化查询过程中的数据访问

查询不需要的数据

使用limit解决

多表关联返回全部列

指定列名

总是返回全部列

避免使用SELECT *

重复查询相同的数据

可以缓存数据

优化长难的查询语句

使用尽可能小的查询是好的

将一个大的查询分为多个小的相同的查询

分解关联查询

化特定类型的查询语句

count(*)会忽略所有的列，不要使用count(列名)

增加汇总表

使用缓存

优化关联查询

ON或者USING子句中是否有索引

GROUP BY和ORDER BY只有一个表中的列

优化子查询

优化GROUP BY和DISTINCT，使用索引来优化，是最有效的优化方法

不需要ORDER BY，进行GROUP BY时加ORDER BY NUL

优化

explain

查看语句的执行计划

id

表示一个查询中各个子查询的执行顺序越大优先级越高，越先被执行

type

ALL 扫描全表数据

index 遍历索引索引物理文件全扫描，速度非常慢

range 索引范围查找

ref 使用非唯一索引查找数据

consts 主键或者唯一索引

possible_keys

可能使用的索引

key

在查询中实际使用的索引

大表数据查询

优化shema、sql语句+索引

第二加缓存，memcached, redis

主从复制，读写分离

垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统

水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

超大分页

MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下

先快速定位需要获取的id段，然后再关联

SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

分页

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15

mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行

慢查询日志

用于记录执行时间超过某个临界值的SQL日志

配置项：slow_query_log

优化

是否load了额外的数据

查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列

使用索引的情况

数据量是否太大

横向或者纵向的分表

自增ID还是UUID

使用自增ID，不要使用UUID

自增ID，那么只需要不断向后排列即可

UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动

没有主键，InnoDB会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。

定义为not null

null值会占用更多的字节，且会在程序中造成很多与预期不符的情况。

关联查询

交叉连接(笛卡尔积)

select r.*,s.* from r,s

内连接

select r.*,s.* from r inner join s on r.c=s.c

左连接

select r.*,s.* from r left join s on r.c=s.c

右连接

select r.*,s.* from r right join s on r.c=s.c

全表连接 mysql不支持

select r.*,s.* from r full join s on r.c=s.c

SELECT * FROM A LEFT JOIN B ON A.id=B.id UNION SELECT * FROM A RIGHT JOIN B ON A.id=B.id

事务

四大特性(ACID)

原子性

要么全部完成，要么完全不起作用

一致性

多个事务对同一个数据读取的结果是相同的

隔离性

各并发事务之间数据库是独立的

持久性

数据的改变是持久的

读

脏读

A读，B读，A回滚，B不正确

不可重复读

A读1，B写2，A读2

幻读

A读2条，B删了1条，A读1条

四个隔离级别

READ-UNCOMMITTED(读取未提交)

允许读取尚未提交的数据

3

READ-COMMITTED(读取已提交)

允许读取并发事务已经提交

阻止脏读

REPEATABLE-READ(可重复读)

多次读取结果都是一致

阻止脏读和不可重复读

SERIALIZABLE(可串行化)

依次逐个执行

隔离机制的实现基于锁机制和并发调度（MVVC（多版本并发控制），通过保存修改的旧版本信息）

Mysql 默认采用的 REPEATABLE_READ隔离级别，分布式事务SERIALIZABLE(可串行化) Oracle 默认采用的 READ_COMMITTED隔离级别

锁

Read Uncommitted

不需要加共享锁，不会跟被修改的数据上的排他锁冲突

Read Committed

加共享锁，语句执行完以后释放共享锁

Repeatable Read

需要加共享锁，必须等待事务执行完毕以后才释放共享锁

SERIALIZABLE

锁定整个范围的键，并一直持有锁，直到事务完成

粒度

表级锁

开销小，加锁快

锁定粒度大，发出锁冲突的概率最高，并发度最低

MYISAM与INNODB

行级锁

开销大，加锁慢

锁定粒度最小，发生锁冲突的概率最低，并发度也最高

INNODB

页级锁

开销和加锁时间界于表锁和行锁之间

一次锁定相邻的一组记录

并发度一般

类别

共享锁读锁

可以同时加上多个

排他锁写锁

只可以加一个

其他的排他锁，共享锁都相斥

锁

悲观锁

数据库中的锁机制

多写的场景下

乐观锁

使用版本号机制或CAS算法实现

写比较少的情况下（多读场景）

基础知识

三大范式

第一范式列不可再分

第二范式非主键完全依赖主键，不能部分依赖

第三范式非主键只依赖主键，不依赖非主键

权限表

user 用户账号信息，全局

db 账号各数据库的操作权限

table_priv 表级操作权限

column_priv 列级操作权限

host 给定主机

binlog

statement 修改数据的sql：减少日志量、解决io，需保存上下文，函数之类无法被复制

row 记录每一行的改动：全部记下来，信息多日志量大

mixed 普通用statement，无法使用用row

MySQL主从复制，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的

数据恢复：通过使用 mysqlbinlog工具来使恢复数据

数据类型

整数类型

TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数

加上UNSIGNED表示无符号

指定长度：INT(11)只影响显示字符，需要和UNSIGNED ZEROFILL才有意义

int(20)

显示字符的长度。20表示最大显示宽度为20，但仍占4字节存储，存储范围不变，int(1)和int(20)存储和计算均一样

不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报表展示

实数类型

DECIMAL可以用于存储比BIGINT还大的整型，能存储精确的小数。

而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算。

计算时FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串进行处理。

FLOAT类型4字节，DOUBLE类型8字节

字符串类型

VARCHAR

VARCHAR用于存储可变长字符串，它比定长类型更节省空间。

VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时，使用1字节表示，否则使用2字节表示。

VARCHAR存储的内容超出设置的长度时，内容会被截断。

CHAR

CHAR是定长的，根据定义的字符串长度分配足够的空间。

CHAR会根据需要使用空格进行填充方便比较。

CHAR适合存储很短的字符串，或者所有值都接近同一个长度。

CHAR存储的内容超出设置的长度时，内容同样会被截断。

长度固定，所以存取速度要比varchar快很多，甚至能快50% 长度固定，所以会占据多余的空间，是空间换时间的做法

综合

对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。

对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。

使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。

尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。

性能角度（char更快）和节省磁盘空间角度（varchar更小

chart(10)和varchar(10) 表示存储数据的大小，即表示存储多少个字符

char(10)表示存储定长的10个字符，不足10个就用空格补齐，占用更多的存储空间

varchar(10)表示存储10个变长的字符，存储多少个就是多少个空格也按一个字符存储，这一点是和char(10)的空格不同的，char(10)的空格表示占位不算一个字符

密码散列，盐，用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。

日期和时间类型

尽量使用timestamp，空间效率高于datetime，

用整数保存时间戳通常不方便处理。

如果需要存储微秒，可以使用bigint存储。

关键字

in 和 exists

in

外表和内表作hash 连接

exists

对外表作loop循环，每次loop循环再对内表进行查询。

两个表大小相当，那么用in和exists差别不大

子查询表大的用exists，子查询表小的用in

not in，那么内外表都进行全表扫描，没有用到索引 not exists的子查询依然能用到表上的索引

UNION与UNION ALL

UNION ALL，不会合并重复的记录行

效率 UNION 高于 UNION ALL

drop、delete与truncate

Delete

可回滚

表结构还在，删除表的全部或者一部分数据行

删除速度慢，需要逐行删除

Truncate

不可回滚

表结构还在，删除表中的所有数据

删除速度快

Drop

不可回滚

从数据库中删除表，所有的数据行，索引和权限也会被删除

删除速度最快

不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

引擎

Innodb引擎

ACID事务的支持

行级锁，并发量高

外键的约束

INSERT、UPDATE、DELETE

B+树索引，Innodb 是索引组织表，聚簇索引，主键索引叶子节点存储着行数据，非主键索引的叶子节点存储的是主键和其他带索引的列数据

哈希索引

按主键大小有序插入

更多的内存和存储，会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引

MyIASM引擎

可被压缩，存储空间较小

SELECT，读写插入为主

B+树索引，myisam 是堆表，非聚簇索引，叶子节点存储的是行数据地址，再寻址一次

全文索引

按记录插入顺序保存

以文件的形式存储，在跨平台的数据转移中会很方便

索引

优缺点

优点

加快检索速度

使用优化隐藏器，提高系统的性能

缺点

创建索引和维护索引要耗费时间

动态的维护，会降低增/改/删的执行效率

占物理空间

使用场景

where

order by 没有用外部排序，有只要取出索引表某个范围内的索引对应的数据

explain select……

possible_keys 可能

key 实际用到

索引覆盖

直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描）

类型

主键索引: 不允许重复，不允许为NULL，一个表一个

唯一索引: 不允许重复，允许为NULL值，一个表可多个

普通索引: 没有唯一性的限制，允许为NULL值

全文索引

数据结构

b+树

不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符

非终端结点可以看成是索引部分

叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针数据对象的插入和删除仅在叶节点上进行

2个头指针，一个是树的根节点，一个是最小关键码的叶节点

天然支持范围，有序

避免不了回表查询数据，聚簇索引、覆盖索引直接通过索引

hash

只能用于对等比较

等值查询更快

创建原则

最左前缀匹配原则一直向右匹配直到遇到范围查询

较频繁作为查询条件

更新频繁字段不适合

不能有效区分数据的列不适合

有外键的数据列一定要

尽量的扩展索引，不要新建索引

创建方式

CREATE TABLE

ALTER TABLE

CREATE INDEX 不能创建PRIMARY KEY索引

删除索引 alter table 表名 drop KEY 索引名

百万级别删除

产生额外的对索引文件的操作，会降低增/改/删的执行效率

可以先删除索引

删除其中无用数据

删除完成后重新创建索引

B树和B+树的区别

B树

内部节点同时存储键和值，提高热点数据的查询效率

B+树

内部节点只存放键，一次性读入内存中可以查找的关键字也就越多，IO读写次数降低；

由一条链相连，范围查询；

必须走一条从根节点到叶节点的路查询效率相当

聚簇索引与非聚簇索引

聚簇索引

数据存储与索引放到了一块

非聚簇索引

将数据存储于索引分开结构，回表查询

在InnoDB中

只有主键索引是聚簇索引

如果没有主键，则挑选一个唯一键建立聚簇索引。

如果没有唯一键，则隐式的生成一个键来建立聚簇索引。

在聚簇索引之上创建的索引称之为辅助索引，总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值

非聚簇索引不一定会回表查询，全部命中了索引不必

联合索引

想要命中索引，需要按照建立索引时的字段顺序挨个使用

将查询需求频繁或者字段选择性高的列放在前面

其他

视图

由基本表(实表)产生的表(虚表)

建立和删除不影响基本表

更新(添加，删除和修改)直接影响基本表

来自多个基本表时，不允许添加和删除数据

缺点

性能

修改限制

存储过程预编译的SQL语句

优点

预编译过的，执行效率高

直接存放于数据库中，减少网络通讯

安全性高，有一定权限

可以重复使用

缺点

调试麻烦

移植问题

重新编译问题

用户需求的增加会导致数据结构的变化

触发器一段代码，当触发某个事件时，自动执行这些代码