Hexo

点滴积累 豁达处之

0%

总结

Posted on In
Mysql

Mysql知识点总结

全盘了解Mysql

mysql体系结构

1
建立连接   查询缓存  解析器  优化器  执行引擎

mysql逻辑架构

1
连接层  服务层  存储引擎层  文件系统

mysql中的目录和文件详解

1
2
3
4
5
6
bin 目录： 放置可执行文件
data目录： 放置日志文件以及数据库
include目录： 放置头文件
lib目录： 放置库文件
share目录： 用于存放字符集、语言等信息
my.ini: 数据库使用的配置文件

mysql中的系统库

数据库事务相关

事务特性

1
ACID 原子性  一致性   持久性  隔离性

事务并发会带来的问题

隔离级别

1
2
3
4
读未提交      可能出现脏读
读已提交      可能出现不可重复读和幻读
可重复读      可能出现幻读
串行化

Mysql中表设计和数据类型优化

数据库三范式

反范式设计

字段数据类型优化

1
2
3
1, 在满足业务的情况下，更小的就更好
2，能用简单类型的就用简单类型  
3，字段尽量不使用null值

数据库命名规范

高性能索引

索引入门

索引的基本概念
1
2
索引的本质：是一种数据结构 
索引的作用：加速查询（高效获取数据）
hash 是否适合
1
不适合   不适合范围查询    自适应hash索引
innodb引擎支持的索引类型
1
B+数  hash 全文索引

B+数

演变和数据结构本身
1
2
3
减少IO   16k   矮胖   巡道 旋转
myisam 数据和索引分开
innodb 主键索引 数据和索引存一起, 数据放在叶子节点上
为什么B+数最合适
1
减少IO   16k   矮胖
磁盘和B+数
1
巡道 旋转

索引辨析

聚簇索引
1
innodb 的主键索引   存放了表的全部数据
辅助索引
1
存放构成索引的列   以及主键id    可能需要回表
覆盖索引
1
搜索的字段在索引中能查出来
联合索引
1
多个列构成的索引   最左前缀原则
倒排索引
自适应hash索引
1
innodb内部使用   热点数据缓存

深入思考索引在查询中的使用

1
一个索引就是一个B+数  一个查询只能使用一个索引    索引是为了加速查询

高性能的索引 创建策略

1
离散性高

三星索引

Mysql性能优化

调优金字塔

1
2
3
架构调优   sql级别调优   操作系统
------->  代价上升  效果下降
<----------  代价下降  效果上升

慢查询日志

1
重构查询    切分查询

Explain 执行计划

1
sqltype   type（访问类型  system  const  ref  range  all） extra(useing temp    useing index )
MRR
1
批量回表   读取部分二级索引中的id  排序  统一进行回表
ICP
1
索引条件下推    先过滤再回表

高性能的索引 使用策略

1
2
3
4
5
6
7
索引不要做操作
尽量全词匹配
最佳左前缀法则
like使用要当心
字符类型加引号
优化count
优化 limit

分区表

1
2
横向切分
hash   list

mysql 的底层执行原理

索引合并

1
2
某些情况下会使用多个索引
交集合并  并集合并

连接查询

连接过程

连接的执行

1
2
嵌套循环连接
基于块的嵌套循环连接

mysql的查询成本计算

1
2
可以解释为啥sql没有走索引
驱动表查询一次   被驱动表查询多次

innodb中的统计数据

mysql查询重写规则

innodb 引擎底层原理

innodb记录存储结构

1
可变长度字段长度  null值列记录   头信息   rowID  tx_id  roll_tx  数据列值

innodb数据页结构

innodb表空间

1
2
系统表空间
rowid   txid  全局统一id
双写
1
2
3
4
5
6
7
三大特性之一
一页16k  只写了8k  此时系统奔溃(部分写失效)
恢复：
检验数据页是否完整

redo log 是针对整页的  double write 是针对不完整页的
redo log写盘时   一份写到共享表空间的双写缓冲区   一份写到真正的磁盘   如果发现不完整则用双写缓冲区的数据覆盖到磁盘

innodb中的buffer pool

LRU链表的管理
1
2
3
空闲列表
flush列表
lru链表   分代   新老部分

事务的原理

redo日志

1
保证持久性

undo日志

1
保证原子性

MVCC

1
2
3
4
5
6
7
8
9
10
11
多版本并发控制
undo 日志版本链

对象：
trx_list: 活动事务id列表
min_id : 最小id
max_id: 最大id(后续id)
create_tx_id  创建事务id

read view (读已提交时，每次读都会创建read view  可重复读时，第一次读会创建read view)
事务id在第一次增、删、改 才创建

mysql中的锁

行级锁

1
2
3
间隙锁   二级索引上
当前读  / 快照读
for update  为当前读 会加锁

表级锁