mysql技术梳理
2024-03-18 15:37:39 19 举报AI智能生成
登录查看完整内容
为你推荐
查看更多
mysql事务 mysql主从复制 mysql数据库优化 mysqlSQL优化 mysql引擎
作者其他创作
大纲/内容
通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；保持一致
主数据库出现问题，可以切换到从数据库。
可以进行数据库层面的读写分离。
可以在从数据库上进行日常备份。
作用
数据分布：随意开始或停止复制，并在不同地理位置分布数据备份
负载均衡：降低单个服务器的压力
高可用和故障切换：帮助应用程序避免单点失败
升级测试：可以用更高版本的MySQL作为从库
解决问题
主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；
从：sql执行线程——执行relay log中的语句；
流程
降低性能， 不支持事务
使用mysql-proxy代理
通过mybatis plugin拦截sql语句，所有的insert/update/delete都访问master库，所有的select 都访问salve还需要重写一下DataSourceTransactionManager， 将read-only的事务扔进读库， 其余的有读有写的扔进写库
mybatis
可以支持事务.
使用AbstractRoutingDataSource+aop+annotation在service层决定数据源
使用AbstractRoutingDataSource+aop+annotation在dao层决定数据源
解决方案
主从复制
将字段很多的表分解成多个表
需要经常联合查询的表
增加中间表
表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。
增加冗余字段
结构优化
进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL
kill 掉这些线程
找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成
看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行
分析为何连接数会激增，再做出相应的调整，比如说限制连接数等
每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升
cpu飙升
限定数据的范围
主库负责写，从库负责读
读/写分离
使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存
缓存
数据表列的拆分；某些列常用，另外一些列不常用
使得行数据变小，在查询时减少读取的Block数，减少I/O次数
会出现冗余，需要管理冗余列，并会引起Join操作；对于应用层来说，逻辑算法增加开发成本
垂直分区
数据表行的拆分，水平拆分最好分库
降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询次数
支持非常大的数据量存储，应用端改造也少
优点
分片事务难以解决 ，跨界点Join性能较差，逻辑复杂
通常查询时需要多个表名，查询所有数据都需UNION操作
缺点
水平分区
分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现
客户端代理
在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。
中间件代理
数据库分片
数据库本身的分布式事务管理
应用程序去协助控制
事务支持
跨库join
与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并
可以并行执行，因此很多时候它的速度要比单一大表快很多
自生成的ID无法保证在全局上是唯一的
UUID非常的长，除占用大量存储空间外，最主要的问题是在索引上，在建立索引和基于索引进行查询时都存在性能问题。
UUID
ID问题
在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序
排序字段非分片字段
跨分片的排序分页
问题
分库分表
大表优化
数据库优化
在 where 及 order by 涉及的列上建立索引
避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
避免在 where 子句中使用!=或<>操作符
避免在 where 子句中使用or 来连接条件
in 和 not in 也要慎用
避免在 where 子句中使用参数
避免在 where 子句中对字段进行表达式操作
避免在where子句中对字段进行函数操作
优化WHERE子句
使用limit解决
查询不需要的数据
指定列名
多表关联返回全部列
避免使用SELECT *
总是返回全部列
可以缓存数据
重复查询相同的数据
优化查询过程中的数据访问
使用尽可能小的查询是好的
将一个大的查询分为多个小的相同的查询
分解关联查询
优化长难的查询语句
count(*)会忽略所有的列，不要使用count(列名)
增加汇总表
使用缓存
化特定类型的查询语句
ON或者USING子句中是否有索引
GROUP BY和ORDER BY只有一个表中的列
优化关联查询
优化GROUP BY和DISTINCT，使用索引来优化，是最有效的优化方法
不需要ORDER BY，进行GROUP BY时加ORDER BY NUL
优化子查询
SQL语句优化
查看语句的执行计划
表示一个查询中各个子查询的执行顺序越大优先级越高，越先被执行
id
ALL 扫描全表数据
index 遍历索引索引物理文件全扫描，速度非常慢
range 索引范围查找
ref 使用非唯一索引查找数据
consts 主键或者唯一索引
type
可能使用的索引
possible_keys
在查询中实际使用的索引
key
explain
优化shema、sql语句+索引
主从复制，读写分离
垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统
大表数据查询
MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下
先快速定位需要获取的id段，然后再关联
超大分页
mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行
分页
用于记录执行时间超过某个临界值的SQL日志
配置项：slow_query_log
查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列
是否load了额外的数据
使用索引的情况
横向或者纵向的分表
数据量是否太大
优化
慢查询日志
使用自增ID，不要使用UUID
自增ID，那么只需要不断向后排列即可
UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动
没有主键，InnoDB会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。
自增ID还是UUID
null值会占用更多的字节，且会在程序中造成很多与预期不符的情况。
定义为not null
交叉连接(笛卡尔积)
内连接
左连接
右连接
SELECT * FROM A LEFT JOIN B ON A.id=B.id UNION SELECT * FROM A RIGHT JOIN B ON A.id=B.id
全表连接mysql不支持
关联查询
要么全部完成，要么完全不起作用
原子性
多个事务对同一个数据读取的结果是相同的
一致性
各并发事务之间数据库是独立的
隔离性
数据的改变是持久的
持久性
四大特性(ACID)
A读，B读，A回滚，B不正确
脏读
A读1，B写2，A读2
不可重复读
A读2条，B删了1条，A读1条
幻读
读
3
允许读取尚未提交的数据
READ-UNCOMMITTED(读取未提交)
阻止脏读
允许读取并发事务已经提交
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 给定主机
权限表
statement 修改数据的sql：减少日志量、解决io，需保存上下文，函数之类无法被复制
row 记录每一行的改动：全部记下来，信息多日志量大
mixed 普通用statement，无法使用用row
MySQL主从复制，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
数据恢复：通过使用 mysqlbinlog工具来使恢复数据
binlog
TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节整数
加上UNSIGNED表示无符号
指定长度：INT(11)只影响显示字符，需要和UNSIGNED ZEROFILL才有意义
显示字符的长度。20表示最大显示宽度为20，但仍占4字节存储，存储范围不变，int(1)和int(20)存储和计算均一样
不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报表展示
int(20)
整数类型
DECIMAL可以用于存储比BIGINT还大的整型，能存储精确的小数。
而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算。
计算时FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串进行处理。
FLOAT类型4字节，DOUBLE类型8字节
实数类型
VARCHAR用于存储可变长字符串，它比定长类型更节省空间。
VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时，使用1字节表示，否则使用2字节表示。
VARCHAR存储的内容超出设置的长度时，内容会被截断。
VARCHAR
CHAR是定长的，根据定义的字符串长度分配足够的空间。
CHAR会根据需要使用空格进行填充方便比较。
CHAR适合存储很短的字符串，或者所有值都接近同一个长度。
CHAR存储的内容超出设置的长度时，内容同样会被截断。
长度固定，所以存取速度要比varchar快很多，甚至能快50%长度固定，所以会占据多余的空间，是空间换时间的做法
CHAR
对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。
对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。
使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。
尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。
性能角度（char更快）和节省磁盘空间角度（varchar更小
char(10)表示存储定长的10个字符，不足10个就用空格补齐，占用更多的存储空间
varchar(10)表示存储10个变长的字符，存储多少个就是多少个空格也按一个字符存储，这一点是和char(10)的空格不同的，char(10)的空格表示占位不算一个字符
chart(10)和varchar(10)表示存储数据的大小，即表示存储多少个字符
密码散列，盐，用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。
综合
字符串类型
尽量使用timestamp，空间效率高于datetime，
用整数保存时间戳通常不方便处理。
如果需要存储微秒，可以使用bigint存储。
日期和时间类型
数据类型
基础知识
外表和内表作hash 连接
in
对外表作loop循环，每次loop循环再对内表进行查询。
exists
两个表大小相当，那么用in和exists差别不大
子查询表大的用exists，子查询表小的用in
not in，那么内外表都进行全表扫描，没有用到索引not exists的子查询依然能用到表上的索引
in 和 exists
UNION ALL，不会合并重复的记录行
效率 UNION 高于 UNION ALL
UNION与UNION ALL
可回滚
表结构还在，删除表的全部或者一部分数据行
删除速度慢，需要逐行删除
Delete
不可回滚
表结构还在，删除表中的所有数据
删除速度快
Truncate
从数据库中删除表，所有的数据行，索引和权限也会被删除
删除速度最快
Drop
不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。
drop、delete与truncate
关键字
ACID事务的支持
行级锁，并发量高
外键的约束
INSERT、UPDATE、DELETE
B+树索引，Innodb 是索引组织表，聚簇索引，主键索引叶子节点存储着行数据，非主键索引的叶子节点存储的是主键和其他带索引的列数据
哈希索引
按主键大小有序插入
更多的内存和存储，会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
Innodb引擎
可被压缩，存储空间较小
SELECT，读写插入为主
B+树索引，myisam 是堆表，非聚簇索引，叶子节点存储的是行数据地址，再寻址一次
全文索引
按记录插入顺序保存
以文件的形式存储，在跨平台的数据转移中会很方便
MyIASM引擎
引擎
加快检索速度
使用优化隐藏器，提高系统的性能
创建索引和维护索引要耗费时间
动态的维护，会降低增/改/删的执行效率
占物理空间
优缺点
where
order by 没有用外部排序，有只要取出索引表某个范围内的索引对应的数据
possible_keys 可能
key 实际用到
explain select……
直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描）
索引覆盖
使用场景
主键索引: 不允许重复，不允许为NULL，一个表一个
唯一索引: 不允许重复，允许为NULL值，一个表可多个
普通索引: 没有唯一性的限制，允许为NULL值
类型
非终端结点可以看成是索引部分
叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针数据对象的插入和删除仅在叶节点上进行
2个头指针，一个是树的根节点，一个是最小关键码的叶节点
天然支持范围，有序
避免不了回表查询数据，聚簇索引、覆盖索引直接通过索引
b+树
只能用于对等比较
等值查询更快
hash
数据结构
最左前缀匹配原则 一直向右匹配直到遇到范围查询
较频繁作为查询条件
更新频繁字段不适合
不能有效区分数据的列不适合
有外键的数据列一定要
尽量的扩展索引，不要新建索引
创建原则
CREATE TABLE
ALTER TABLE
CREATE INDEX 不能创建PRIMARY KEY索引
删除索引 alter table 表名 drop KEY 索引名
创建方式
产生额外的对索引文件的操作，会降低增/改/删的执行效率
可以先删除索引
删除其中无用数据
删除完成后重新创建索引
百万级别删除
内部节点同时存储键和值，提高热点数据的查询效率
B树
内部节点只存放键，一次性读入内存中可以查找的关键字也就越多，IO读写次数降低；
由一条链相连，范围查询；
必须走一条从根节点到叶节点的路查询效率相当
B+树
B树和B+树的区别
数据存储与索引放到了一块
聚簇索引
将数据存储于索引分开结构，回表查询
非聚簇索引
只有主键索引是聚簇索引
如果没有主键，则挑选一个唯一键建立聚簇索引。
如果没有唯一键，则隐式的生成一个键来建立聚簇索引。
在聚簇索引之上创建的索引称之为辅助索引，总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值
非聚簇索引不一定会回表查询，全部命中了索引不必
在InnoDB中
聚簇索引与非聚簇索引
想要命中索引，需要按照建立索引时的字段顺序挨个使用
将查询需求频繁或者字段选择性高的列放在前面
联合索引
索引
由基本表(实表)产生的表(虚表)
建立和删除不影响基本表
更新(添加，删除和修改)直接影响基本表
来自多个基本表时，不允许添加和删除数据
性能
修改限制
视图
预编译过的，执行效率高
直接存放于数据库中，减少网络通讯
安全性高，有一定权限
可以重复使用
调试麻烦
移植问题
重新编译问题
用户需求的增加会导致数据结构的变化
存储过程预编译的SQL语句
触发器一段代码，当触发某个事件时，自动执行这些代码
其他
mysql
0 条评论
回复 删除
下一页
