- Mysql架构
- 1.0 单机架构-》2.0主从架构-》3.0分库分表架构-》4.0云数据库
- mysql体系架构
- 客户端连接器、系统管理和控制工具、连接池、SQL接口、解析器、查询优化器、缓存、可插拔存储引擎、系统文件、日志
- 日志文件:
- 错误日志：默认开启，show variables like '%log_error%'
- 查询日志：默认关闭
- 二进制日志：可以用作恢复
- 慢查询日志：记录执行时间超时的sql，默认10s
- 配置文件
- .ini
- 数据文件
- data文件夹下
- db.opt 字符集
- ...
- pid文件
- socket文件
- 运行机制
- 建立连接SQL->查询缓存-》解析器-》解析树-》预处理器-》新解析树-》查询优化器-》执行计划-》查询执行引擎-》innodb/myisam
- 1.建立连接
- 全双工：同时发送和接收数据，例如打电话
- 半双工：要么发送，要么接收
- show processlist查看用户正在执行的线程信息
- 2.查询缓存
- 如果有相同的SQL直接返回结果
- 缓存结果和Sql语句
- 3.解析器、查询优化器
- 等价变换
- 优化count/min/max函数
- Innodb min找索引左边
- in 先二分查找再排序
- 4.执行引擎机制
- 返回结果过多，增量返回
- MYSQL存储引擎
- innoDB和Myisam对比
- innodb：支持事务，可以提交回滚回复能力，事务安全
- myisam：不支持事务和外键，访问速度快
- （缓存，读操作）innodb_buffer_pool_size可以调整为总内存的60%-80%
- 写操作缓存：chaange bugger，占用25%的bufferpool
- 更新记录时在bufferpool中存在直接修改，如果不存在，则在changebuffer进行内存操作，减少磁盘Io
- logbuffer:日志缓存
- 日志文件刷新时间时间为1秒，最多丢失1秒的数据
- tablespaces（磁盘结构）:
- 系统表空间
- 独立表空间
- 通用表空间
- undo:撤销日志空间
- 临时表空间
- Doublewrite buffer:双写缓存，
- 撤销和回滚
- mysql5.7版本可以安装时自由指定undo文件大小和数量
- 临时表空间可以根据会话存储
- innodb线程模型
- Master thread:负责调度其他线程，优先级最高，将缓存池的数据刷新到磁盘
- IO thread：异步io，4个读4个写线程
- purge thread：清理undo日志
- page cleaner thread:将脏数据刷到磁盘
- 文件存储结构
- tablespace(ibd文件):
- segment（段）->
- 管理多个extent
- extent（区）->
- 包含64个页大小为1M
- page（页）->
- 大小为16k
- row（行）
- 包含了记录字段，事务id,滚动指针，字段指针
- page是文件的最基本单位，由page header,page trailer,page body组成
- innodb文件存储格式
- binlog：记录所有数据库表结构变更以及表数据修改的二进制日志
- 主从复制
- 数据恢复
- 三种记录模式1，ROW 2.STATMENT 3.MIXED
- 事件触发机制：log event保存在binlog_cache_mngr数据结构中
- 在事务提交阶段产生log event
- mysqlbinlog --start-position=xxxx --stop-positon=xxx
- xxx | mysql - uroot -pxxx
- binlog和redo log的区别
- 1.redolog是属于innodb引擎功能，binlog是mysql server自带功能，是二进制
- 2.redo log是物理日志 ，记录状态内容，binlog是逻辑日志，更新过程
- 3.redo log是循环写入，空间大小固定，binlog是追加写入，递增
- 4.redo log是作为服务器异常宕机事务数据恢复，binlog是可以作为主从复制和人为删除数据恢复使用,但是没有自动恢复能力
- MYSQL索引
- 所以可以提升查询速度，影响where查询，以及order by排序
- 有B tree结构，有hash结构，fulltext全文索引,R tree索引
- 1.普通索引
- create index on tablename(字段名)
- 2.唯一索引
- create unique index<索引名称>on tablename(字段名)
- 3.主键索引
- 4.复合索引
- 尽量减少复合索引的字段，窄索引更有效
- 5.全文索引
- 必须在字符串和文本字段建立
- 在数据量较少时可以使用Like模糊查询,数据量大时效率低。5.6之后myisam和Innodb都支持，5.6之前innodb不支持
- 用match和against关键字使用
- 根据最大和最小匹配参数位数来匹配（innodb:3-84,myisam:4-84）
- 有切词符切分后的内容匹配(按syntax字符切割)
- like可以加入*号用boolean值匹配（默认等值匹配）
- 二分查找法（有序的数据）
- 优点：等值查询、范围查询性能优秀
- 缺点：更新数据，新增数据，删除数据维护成本高
- Left和right指针，
- 相加再除2，大于目标值right移动，小于left移动，直到锁定目标值
- Hash结构
- 自适应哈希索引:一次性查找就能定位数据，等值查询效率高
- B+tree结构
- 非叶子节点不存储节点数据，只存储索引值，叶子节点包含所有的索引值和数据，叶子节点用指针连接
- 从根节点开始二分查找，如果没有找到，再到子节点二分查找
- 聚集索引：主键索引，Innodb必须有主键索引，B+的叶子节点就是主键索引排序
- 辅助索引：先通过辅助索引树找到id再通过主键索引找到数据
- EXPLAIN:执行计划
- select_type:查询类型，
- SIMPLE：简单查询，没有子查询
- PRIMARY:表示最外层
- UNION：表示查询是UNION的第二个或者后续的查询
- DEPENDENT UNION：UNION使用了外层的查询结果
- UNION RESULT：UNION结果
- SUBQUERY：子查询
- DEPENDENT SUBQUERY：子查询依赖外层查询结果
- type:可以判断查询方式，是全表扫描还是索引
- all:全表扫描
- index:基于索引的全表扫描
- range:范围查找
- ref:表示使用非唯一索引进行单值查询
- eq_ref:多表Join查询中主表只匹配附表一条记录
- consf:主键或者唯一索引，等值查询
- null:不访问表，直接查找
- 以上类型从上至下效率依次增强
- key_len:表示查询使用的字节数量。可以判断是否全部使用了组合索引
- rows：扫描多少行
- keys:索引
- Extra:using index是用了索引
- 回表查询：using where
- 先通过辅助索引定位主键，再通过聚合索引定位数据就叫回表
- 覆盖索引：
- 用覆盖索引可以不用回表，方法是将查询的字段建立组合索引
- 复合索引只能从最左边的字段开始匹配
- like模糊查询，索引是否能起作用
- 只有右模糊起作用
- 如果某列有Null值，索引是否有效：是有效的
- 排序中，where条件中用索引字段范围查询不走索引
- 当有where等值查询和Order by用组合索引
- 查询优化：
- 开启慢查询日志
- long_query_time：慢查询的阀值
- 提高索引的过滤性：索引字段值多
- 设置虚拟列
- 慢查询的原因：全表扫描，全索引扫描、索引过滤性不好、频繁回表
- 分页查询优化：
- show profiles;显示sql执行时间
- 查询数据量小于100条时，查询时间基本不变，记录量越大查询时间越长
- 偏移量超过100，查询时间花费多
- 1.可以通过覆盖索引，只查询索引字段
- 2.利用子查询优化
- MYSQL的事务和锁
- ACID
- 原子性：每一个事务是一个逻辑操作单元，要么全成功，要么全不成功
- 每一个事务，都会修改bufferpool，再更新磁盘。如果中途数据库挂了可以通过redo和undo日志恢复
- 一致性：事务开始和事务结束后，数据的完整性未被破坏，分为约束一致性和数据一致性
- 隔离性：事务不会互相干扰，隔离性从低到高为读未提交、读提交、可重复读、串行化
- 持久性：事务一旦提交，改变是永久性的
- 更新丢失：多个事务同时更新同一条记录，会产生更新丢失
- 脏读：一个事务读取到了另一个事务未提交的数据
- 不可重复读：一个事务中多次读取同一行记录不一致
- 幻读：一个事务多次同条件查询结果不一致，多了或者少了记录
- 互斥锁：支持并发，阻塞式
- 读写锁：读和读之间不加锁
- MVCC：多版本控制，空间换时间，可以支持读写和写读的并行，每次修改之前生成一个副本可以支持其他事务读
- 乐观锁和悲观锁
- MYSQL默认可重复读的隔离级别
- 锁机制
- 表锁，行锁，页锁（INNODB没有）
- 从操作分类（行锁）
- 读锁：
- 写锁：
- 性能分
- 乐观锁和悲观锁
- Recorde lock:单行记录锁
- gaplock：间隙锁，锁住当前行和前后的间隙不能插入数据
- Next-key lock：行锁和间隙锁，如果碰到唯一健，降级为单行锁
- 悲观锁
- 表级锁：
- 表级读锁，不允许增删改操作，写锁当前连接可以做任何操作
- 共享锁（行锁中的读锁）lock in share mode：只能读取不能修改
- 排他锁（行锁中的写锁）for update：除了当前连接都不允许访问
- ！！如果字段未加索引，会锁表
- 乐观锁：
- 冲突检测，使用version或者时间戳字段
- Mybatis有optimisticlocker插件
- 死锁：
- 1.表死锁：A用户访问A表，又访问B表，用户B访问B表，又访问A表。A由于B锁住B表，B用户又等待A表锁释放。
- 是由于程序逻辑问题
- 2.行死锁
- 执行没有索引的查询，引发全表扫描，全表锁定，多个事务执行容易死锁和阻塞。
- 优化sql、防重放、乐观锁
- show engine innodb status
- MYSQL分库分表
- 垂直拆分：表过多或者字段过多，拆分后业务清晰，容易维护，缺点是冗余列，事务管理复杂
- 水平拆分：表数据过多，按规则将数据打散，高并发性能好，表结构相同，只用路由规则，缺点是拆分规则难以抽象，join性能差，分布式事务一致性，扩容相对难
- 分库分表主键策略
- UUID/COMB/SNOWFLAKE/数据库id表/redis
- 分片策略
- 范围分片、hash取模、一致性hash
- 扩容方案
- 横向扩容，数据迁移、分片规则改变、数据同步和一致性
- 1.停机扩容
- 2.平滑扩容：double数据库
- Mysql性能优化
- 成本：SQL《表结构《系统配置《硬件升级
- 效果：SQL》表结构》系统配置》硬件
- 修改内存池大小my.cnf
