三大范式
- 每个列都不可以再拆分
- 在第一范式基础上，非主键完全依赖于主键，而不能是依赖主键的一部分
- 在第二范式基础上，非主键列只依赖于主键，不依赖于其他非主键
MyISAM与InnoDB的区别
| MyISAM | InnoDB | |
|---|---|---|
| 记录存储顺序 | 按记录插入顺序保存 | 按主键大小有序插入 |
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 锁支持 | 表级锁 | 行级锁、表级锁，锁定力度小并发能力强 |
| 查询能力 | 更优 | |
| 增删改 | 更优 | |
| select count | 更快，内置计数器 | |
| 索引实现方式 | B+树，堆表 | B+树，索引组织表 |
| 哈希索引 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
InnoDB引擎四大特性
- 插入缓冲
- 二次写
- 自适应哈希索引
- 预读
存储引擎选择
默认InnoDB即可，若是读写插入为主使用MyISAM，更新删除操作频繁或者保证数据完整性，并发量高，支持事务和外键使用InnoDB
索引
索引是对数据表里所有记录的指针，是一种数据结构，以协助快速查询更新数据库表中数据。通常使用B树或者B+树。索引是一个文件，占据物理空间。
优点
- 大大加快数据的检索速度
- 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统性能。
缺点
- 时间方面：创建索引和维护索引要耗费时间。当对表中的的数据进行增删改时，索引也要动态维护，降低增删改效率
- 空间方面：索引要占物理空间
索引类型
- 主键索引
- 唯一索引
- 普通索引
- 全文索引
索引基本原理
- 把创建了索引的列的内容进行排序
- 对排序结果生成倒排表
- 在倒排表内容上拼上数据地址链
- 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据
创建索引原则
- 最左前缀匹配原则
- 较频繁作为查询条件的字段才去创建索引
- 更新频繁字段不适合创建索引
- 若是不能有效区分数据的列不适合做索引
- 尽量的拓展索引而不是新建
- 定义有外键的数据列一定要建立索引
- 对于那些查询中很少涉及到的列，重复值比较多的列不要简历索引
- 对于定义为text、image、bit的数据类型不要建立索引
B树与B+树的区别
- B树种，可以将键和值存放在内部节点和叶子节点；B+树种，内部节点都是键，没有值，叶子节点同时存放键值
- B+树叶子节点都有一条链相连，B树各自独立
B树的好处
B树在内部节点同时存放键值，因此将频繁查询的数据放在靠近根节点的地方会大大提高热点数据的查询效率
B+树的好处
B+树内部节点只放键，因此一次读取，可以在内存页中读取更多的键，有利于缩小查找范围。
事务
四大特性ACID
- 原子性：事务为最小单位，不可分割
- 一致性：事务前后，数据保持一致
- 隔离性：并发访问时，事务不被其他事务干扰
- 持久性：一个事务提交，数据的改变是永久的
隔离级别
- 读未提交：不加锁
- 读已提交（Oracle默认）：读加锁，读完释放
- 可重复读（MySQL默认）：读加锁，但事务不提交不释放锁
- 串行化：一直持有锁，直到事务完成
行级锁和表级锁
- 行级锁：开销大，加锁慢，会出现死锁，锁粒度小，发生锁冲突概率低，并发高
- 表级锁：开销小，加锁快，不会出现死锁；锁力度大，发生锁冲突概率高，并发低
MySQL的锁
- 共享锁：读锁，可以多个
- 排它锁：写锁，只能一个，与其他锁互斥
死锁
两个或多个事务在同一资源商相互占用，并请求锁定对方的资源，导致循环
解决办法
- 如果不能程序会并发存取多个表，尽量约定以相同的顺序访问表
- 在同一个事务中，尽可能做到一次锁定所需要的所有资源
- 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁减少死锁概率
乐观锁和悲观锁
确保多个事务在同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性
悲观锁
假定会发生冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来直到事务提交
实现方式
使用数据库中的锁机制
乐观锁
假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过verison方式进行锁定
实现方式
一般会使用版本号机制或CAS算法实现
使用场景
乐观锁适用于多读写少的情况下，加大吞吐量
悲观适用于多写的情况
SQL优化
Explain命令查看语句的执行计划，能知道sql语句的执行详细情况，其中包含是否使用索引，什么索引，使用的索引的相关信息等
- 查询部分数据使用limit
- 多表关联返回指定列名
- 避免使用select *
- 重复查询相同数据：缓存数据
- 根据范式改变库表结构
- 一个大查询分解成多个小查询
- 执行单个查询减少锁竞争
- 使用count（*）会忽略所有列，直接统计所有列，不要使用count（列名）
- 避免使用or、！=、<>，会导致索引失效，全表扫描
- in和not in慎用，对于连续的数值使用between
- 前置模糊查询也会导致索引失效
- 不要在where中进行函数操作
SQL生命周期
- 建立连接
- 请求sql
- 解析并生成执行计划，执行
- 读取数据到内存并进行逻辑处理
- 发送结果
- 关闭连接
大表数据查询优化
- 优化shema、sql语句+索引
- 加缓存
- 主从复制，读写分离
- 垂直拆分，分布式系统
- 水平切分
慢查询日志
开启慢查询日志
配置项：slow_query_log
show variables like 'slow_query_log' #通过命令查看是否开始慢sql日志
set GLOBAL slow_query_log = on #开启慢sql日志，会在datadir产生一个xxx-slow.log文件
可以设置临界时间，超过这个时间的会被记录到日志中
SQL查询慢原因
- 没有索引或者索引使用不正确
- 隐式转换：发生隐式转换时，MySQL选择执行计划并不能利用到合适的索引而选择全表扫描
- in包含多个类型
- 判断符号左边是字符串类型,右边是数字
- join时where左右两边字段类型不一致
- 数据巨大
- 锁等待
- 并发更新同一行:并发执行update时,会判断会不会由于自己的加入导致死锁。
- 数据分布不均匀
- SQL姿势不合理：使用大分页深度查询“LIMIT 149420,20”
