Mysql
2021-02-01 14:37:05 50 举报AI智能生成
mysql基础知识点
作者其他创作
大纲/内容
逻辑架构
连接层
负责处理客户端的连接以及权限的认证
服务层
查询缓存
分析器
优化器
执行器
引擎层
存储引擎
存储层
复制硬件设备的数据存储
四个特性（ACID）
原子性（Atomicity）
句要么全执行，要么全不执行，是事务最核心的特性，事务本身就是以原子性来定义的；实现主要基于undo log日志实现的。
持久性（Durability）
保证事务提交后不会因为宕机等原因导致数据丢失；实现主要基于redo log日志。
隔离性（Isolation）
保证事务执行尽可能不受其他事务影响；InnoDB默认的隔离级别是RR，RR的实现主要基于锁机制、数据的隐藏列、undo log和类next-key lock机制。
一致性（Consistency）
事务追求的最终目标，一致性的实现既需要数据库层面的保障，也需要应用层面的保障。
事务
两个操作
提交：commit，将事务执行结果写入数据库。
回滚：rollback，回滚所有已经执行的语句，返回修改之前的数据。
锁机制
按照粒度划分：行锁、页锁、表锁
按照使用方式划分：共享锁、排它锁
按照思想划分：悲观锁、乐观锁
行锁
1，Record Lock：单个行记录上的锁。
2，Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
3，Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。
读写锁
共享锁(shared lock),又叫做"读锁"
读锁是可以共享的，或者说多个读请求可以共享一把锁读数据，不会造成阻塞。
排他锁(exclusive lock),又叫做"写锁"
写锁会排斥其他所有获取锁的请求，一直阻塞，直到写入完成释放锁。
通过读写锁，可以做到读读可以并行，但是不能做到写读，写写并行 事务的隔离性就是根据读写锁来实现的
并发读写问题
脏读
不可重复读
分支主题
幻读
分支主题
隔离级别
分支主题
MVCC
保证隔离性
在同一时刻，不同事务可以读取到不同版本的数据，从而可以解决脏读和不可重复读的问题。
InnoDB的内核，会对所有row数据增加三个内部属性
(1)DB_TRX_ID，6字节，记录每一行最近一次修改它的事务ID；
(2)DB_ROLL_PTR，7字节，记录指向回滚段undo日志的指针；
(3)DB_ROW_ID，6字节，单调递增的行ID；
日志系统
重做日志（redo log）
保证持久性
write_pos标志到了日志结尾
checkpoint标志到了日志开头
当write_pos追上checkpoint时，表示redo log日志已经写满。这时不能继续执行新的数据库更新语句，需要停下来先删除一些记录，执行checkpoint规则腾出可写空间。
checkpoint规则：checkpoint触发后，将buffer中脏数据页和脏日志页都刷到磁盘。
脏数据：指内存中未刷到磁盘的数据。
缓冲池（buffer pool）
在内存中分配的一个区域，包含了磁盘中部分数据页的映射，作为访问数据库的缓冲。
当请求读取数据时，会先判断是否在缓冲池命中，如果未命中才会在磁盘上进行检索后放入缓冲池；
当请求写入数据时，会先写入缓冲池，缓冲池中修改的数据会定期刷新到磁盘中。这一过程也被称之为刷脏 。
当数据修改时，除了修改buffer pool中的数据，还会在redo log中记录这次操作
如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复
redo log日志可分为两个部分
一是存在易失性内存中的缓存日志redo log buff
二是保存在磁盘上的redo log日志文件redo log file。
既然redo log也需要存储，也涉及磁盘IO为啥还用它？
redo log 的存储是顺序存储，而缓存同步是随机操作。
缓存同步是以数据页为单位的，每次传输的数据大小大于redo log。
二进制日志（binlog）
分支主题
回滚日志（undo log）
利用undo log中的信息将数据回滚到修改之前的样子
保证原子性
主从复制
主从复制的原理实际上就是通过bin log日志实现的
bin log日志中保存了数据库中所有SQL语句，通过对bin log日志中SQL的复制，然后再进行语句的执行即可实现从数据库与主数据库的同步。
主从复制的过程主要是靠三个线程进行的
一是运行在主服务器中的发送线程，用于发送binlog日志到从服务器
二是从服务器上的I/O线程用于读取主服务器发送过来的binlog日志内容，并拷贝到本地的中继日志中
三是从服务器上SQL线程用于读取中继日志中关于数据更新的SQL语句并执行，从而实现主从库的数据一致。
分支主题
好处
1. 通过复制实现数据的异地备份，当主数据库故障时，可切换从数据库，避免数据丢失。
2. 可实现架构的扩展，当业务量越来越大，I/O访问频率过高时，采用多库的存储，可以降低磁盘I/O访问的频率，提高单个机器的I/O性能。
3. 可实现读写分离，使数据库能支持更大的并发。
4. 实现服务器的负载均衡，通过在主服务器和从服务器之间切分处理客户查询的负荷。
调优
排除缓存干扰
执行SQL的时候，记得加上SQL_NO_CACHE 跑SQL
Explain
analyze table tablename 重新统计索引信息
force index 强制走正确的索引
possiable_key：可能走的索引
key：实际走的索引
extra:额外的
回表的话Extra是null
using index condition 索引下推 在存储引擎中就对各个索引拉取数据至mysql server
using index;索引覆盖
using where:使用where条件进行过滤
using filesort:利用文件进行排序，（没使用索引排序）
key_len：索引字段长度
type
覆盖索引
减少树的搜索次数，显著提升查询性能
联合索引
最左匹配原则
索引下推
在存储引擎中就对各个索引拉取数据至mysql server
唯一索引普通索引选择难题
change buffer
唯一索引的更新就不能使用change buffer，只有普通索引可以使用
change buffer用的是buffer pool里的内存
使用场景： 适合写多读少（账单类、日志类的系统）
不适合： 写入之后马上会做查询
前缀索引
可以将字符串的前一部分创建索引，使用select count(*) left(city,n)统计前缀字段数量，
跟group by city统计count(*)数量差不多的时候，取前n个字段创建索引
条件字段函数操作
索引失效场景
对索引字段做函数操作 where id + 1 = 10000
隐式类型转换 字符串范围值也走索引 name>'aaa'走索引 name>123不走索引；； 相当于加了 CAST( id AS signed int) 函数
组合索引用or不会走索引；单列索引用or才会走索引。
如果一个表只有组合索引字段没有其他字段，用or会走索引；
id和name都是索引，where id = 1 and name='张三'; 这种查询方式会使查询速度变慢。
更新十分频繁，区分度不高的字段不已建立索引。区分度在80%之上就可以建立索引
sex status不要创建索引
创建索引的列不允许为null，可能会得到不符合逾期的结果。
单表索引建议控制在5个以内。太多的话占空间大，io多，速度慢，5是《高性能mysql》书中建议的。
组合索引字段数不允许超过5个。书中推荐的
flush
场景
1、InnoDB的redo log写满了，这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写。
2、系统内存不足，当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
3、MySQL认为系统“空闲”的时候，只要有机会就刷一点“脏页”。
4、MySQL正常关闭，这时候，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。
配置参数
innodb_io_capacity
告诉InnoDB你的磁盘能力，这个值建议设置成磁盘的IOPS，磁盘的IOPS可以通过fio这个工具来测试
innodb_flush_neighbors
innodb_flush_neighbors=0这个参数可以不产生连带制，在MySQL 8.0中，innodb_flush_neighbors参数的默认值已经是0了
0 条评论
下一页
