执行顺序
from确定主表 join确定子表 on 确定主表跟子表间条件 确定条件之后where确定主表数据过滤条件
拿到数据后进行一个分组 分组后查询聚合函数 聚合函数后用having过滤 然后才是select拿出数据 distinct过滤 order by 按什么顺序 limit 拿几个
数据库三范式
1保证每一列的原子性 比如班级名字 分别班级加名字
2满足1后 每一列有唯一性 依赖主键
3满足2后 外键引用id 不引入其他能通过id查到的数据
一般业务都会打破的
引擎方面
5.5之前 MYISAM 大锁 没事务
5.5之后 InnoDB 支持行锁 有事务
sql优化的话 首先排除偶然情况的前提下
去排除sql语句慢的
排查方法 工具的话 类似运维的SkyWalking 通过检测接口的时间跟接口内具体sql的时候去找到对应的sql语句
Mysql层面
1 通过explain关键字
2 show status like
3 show profile命令分析 (我觉得开销很大)
慢查询日志 自己配置开
找出之后就是分析了 分析的话
1通过explain分析 是否命中索引 索引的类型
2通过mysql的type来确定类型 如果all这种太大的话
考虑通过索引优化去变得更小
索引优化 一般就是建立联合索引覆盖索引 减少回表查询
实际上覆盖索引的话 比如abc 实际上是会创建a b c ab ac bc (不用过多了解)
所以如果我对我的索引列操作的话 索引就会失效 但是失效并不是没命中索引 只是没命中我们定义的覆盖索引abc 也是优化了的 只是没达到效果
索引的话就会有 索引结构类型 B+ HASH R-Tree空间 S-Full-text全文
索引类型 普通 唯一 主键 联合 全文 空间
B+ B区别
B+非子结点不存数据 只存指针 所以相对B的话B+的第一阶段树更多 所以阶级就更短
那么从根节点到数据的时间就更短 所以就意味着 速度更快
B+底层是双向循环链表 左右跟头尾都是相连的
所以支持任何角度的范围查询
索引失效
常用场景
1前面用or后面没用
2 后面列明用了范围查询
3 列名属性不一了解
8以上的字符串转数字 8一下 会先去做判断 比如"12345"=12345 如果返回1 字符串转为数字 也就是失效了 因为不存在12345这个列名
如果返回0 数字转为字符串 这个时候列明没变 自然也就不会失效了
4字符串不加引号
5索引上运算
本质就是对索引列进行操作导致的失效
索引设计原则
数据量要大
查询高的设立联合索引 也就是where后面的
联合索引中区分度高的在前 低的在后
如果字段内容长 考虑采用前缀索引
创建表的时候
从字段类型下手 varchar 跟char tinyint int bigint
sql语句的优化
union all代替union 少一次过滤
联表使用innerjoin 如果一定要左右表 小表为主表 不然太大了(小表的话比如for循环嵌套 小的在外 也就是小的表有几行数据 就要几个数据库连接 相对其大表在外的话节省了很多次数据库连接)
日志文件 redolog undolog binlog
redolog 记录物理页的变化 基于INnoDB引擎的
undolog记录逻辑页的变化 事务回滚
binlog 纯文本文件 主从复制的时候可以用
binlog没大小限制 redolog有会覆盖 binlog是一个一个写
redolog保证了持久性 undolog保证了一致性跟原子性 隔离性通过排他锁或者mvcc
mysql底层
sql接口
线程直接发送的sql mysql是不知道什么意思的 所以要通过sql接口
sql解析器
从sql接口拿到sql 进行解析 也就是让mysql读懂我们的sql语句
sql优化器 二选一
1 提出所有user表 通过id过滤 拿到name
2 提出所有user表 通过name过滤 拿到id
我们操作了数据 就会将磁盘的数据读取到innodb的 bufferpool(内存)
通过我们的落盘策略 1 定时将bufferpool写入redolog文件 2满了写
分库分表 从上往下考虑
一般采用分区够了 程序员不感知 同张表
水平分表 不拆分字段 就是拆分存储的id 比如1-10000 10001-20000
垂直 拆分字段 影响大 一般不用(冷热数据用)
水平分库类似取模id redis分片集群 但是无法动态扩容
水平要借助中间件 sharding-sphere或者mycat等
MVCC包含1隐藏字段2undolog日志3readView视图
隐藏字段包含事务id跟回滚指针 指向的就是上个事务id的版本
内部版本类似链表一样通过事务的id相连
在读已提交 也就是RC中 readView是会根据其他事务实时更新的
但是RR中 也就是可重复读中 是事务开始的时候是怎么样的 他就是怎么样的
