数据库复习指南数据库复习指南

索引分析工具：explain
数据库优化。表很大之后，insert变得很慢，如何优化？

缓存；
分库分表；
索引；
增加机器

Mysql查询很慢，如何定位原因？处理慢查询有三个主要的技巧：《MySQL排错指南》 P23
1. 调优查询本身；
通过强大的查询调优工具EXPLAIN。这个工具为用户提供了服务器实际上如何执行查询的详细信息。通过EXPLAIN能够看到经过查询优化器优化后的select语句的真正的样子，从中找出可以优化的可能性。
2. 调优表（包括添加索引）；
在与WHERE，JOIN和GROUP BY语句相关的列上添加索引可以加速查询。
3. 调优服务器。
个人体会：凡是涉及数据库，一般就几个核心的点：
1. 事务
2. 索引
3. 分布式（分库分表）
当没有思路时，可以想想这三点，看能不能沾上边。
索引失效的场景
- 联合索引没有头索引或者中间断开
- 索引列使用计算
- like前缀索引写成了%kk
- 索引是字符串，但是传的值不带单引号，导致全表扫描；
- 索引字段上使用不等于
Innodb 和 Myisam 区别：

事务的支持
行锁表锁
聚集索引，非聚集索引

如果有人问你数据库的原理，叫他看这篇文章
一概念 1. SQL join 语句
2. 数据库的三大范式
1. 第一范式 1NF

表中所有属性域都是**原子的，不可再分**。- 一个若干名字的集合是非原子的，例如Children属性是一些名字的集合，不满足第一范式；- 组合属性也不是原子的，例如address可以分为city+street，不满足第一范式；

2. 第二范式 2NF

在满足1NF基础上，要求表中非主键列依赖于主键，而不是依赖于主键的一部分，考虑联合主键。

3. 第三范式 3NF

要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

理解数据库范式:张大胖学数据库

3. RAID
RAID0，RAID1，RAID3，RAID5，RAID6
二事务 1. ACID
2. 事务的隔离级别

Lock-Based Concurrency Control
理解：隔离级别用锁来实现
写数据时加上X锁，直到事务结束，读的时候不加锁。
写数据的时候加上X锁，直到事务结束，读的时候加上S锁，读完数据立刻释放。
写数据的时候加上X锁，直到事务结束，读数据的时候加S锁，也是直到事务结束。
锁的范围
Multi-Version Concurrency Control MVCC方式：只在读已提交和可重复读级别下，其他两个级别不兼容；
[图片上传失败...(image-a536c9-1552459199375)]

Read Uncommited：可以读取未提交记录。此隔离级别，不会使用，忽略。
Read Committed (RC)
快照读忽略，本文不考虑。
针对当前读，RC隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。
Repeatable Read (RR)
快照读忽略，本文不考虑。
针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。
Serializable：从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。

三索引 1. 理解：数据库索引&数据结构
二叉树，B树，B+树
四存储引擎InnoDB 1. MySQL存储引擎InnoDB1
2. MySQL存储引擎InnoDB2-锁
innodb什么时候使用行级锁和什么时候使用表级锁
众多资料中都说innodb使用的是行级锁，但实际上是有限制的。只有在你增删改查时匹配的条件字段带有索引时，innodb才会使用行级锁，在你增删改查时匹配的条件字段不带有索引时，innodb使用的将是表级锁。因为当你匹配条件字段不带有所引时，数据库会全表查询，所以这需要将整张表加锁,才能保证查询匹配的正确性。在生产环境中我们往往需要满足多人同时对一张表进行增删改查，所以就需要使用行级锁，所以这个时候一定要记住为匹配条件字段加索引。
提到行级锁和表级锁时我们就很容易联想到读锁和写锁，因为只有触发了读写锁，我们才会谈是进行行级锁定还是进行表级锁定。那么什么时候触发读锁，就是在你用select 命令时触发读锁，什么时候触发写锁，就是在你使用update,delete,insert时触发写锁，并且使用rollback或commit后解除本次锁定。
3. Mysql 中 MyISAM 和 InnoDB 的区别有哪些？
InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
五 Mysql性能优化第四章：Scheme和数据类型的优化 1. 选择优化的数据结构

选择正确存储数据的最小数据类型，占用更少磁盘、内存、CPU；
简单数据类型的操作需要更少CPU周期；
整数》字符串（字符集和校验规则）
时间〉字符串。
32位整数存IP，而不是字符串。
尽量避免Null
空间还好，mysql用单独的位来存储null值。
可为null的列被索引时，每个索引记录需要一个额外的字节。

2. 特定问题
太多的列：
从行缓冲中拷贝过来的列转换成数据结构的代价是很高的，例如数千个字段。
Alert table 开销：
mysql修改表的大部分操作是用新的结构创建一个空表，从旧表中查出所有数据再删除旧表。
3. 提高读查询的速度
额外的索引
增加冗余列
汇总表（每小时统计一次）
缓存表：优化搜索和查询，需求是很多不同的索引组合来加速查询，创建一张只包含主表部分列的缓存表。
第五章：索引优化 1. 索引查找特定行，避免全表扫描；
2. 索引可以让查询锁定更少的行；
3. InnoDB在二级索引上使用共享锁，在访问主键索引需要排他锁；
[图片上传失败...(image-b4f0a2-1552459199375)]
第六章：查询优化 1. 优化访问

查询不需要的记录，加select
多表关联时返回不必要的数据
select*，取出全部列
重复查询相同的数据，考虑索引。

六分布式数据库 OLTP VS OLAP

OLTP：On-Line-Transaction Processing联机事务处理
举例说明，进行一次交易，资金从A转账到B，这整个过程就是一次交易事务。如果过程中有任何的系统错误，交易会回滚A账户中的金额到操作前的状态。
在OLTP场景中用户并发操作很多，要求系统实时进行数据操作的响应，在查询时往往也只是会检索一条或几条明确的目标数据，以实现业务的交互。
OLTP：On-Line-Analytical Processing联机分析处理
主要针对数据的分析汇总操作，如我们的业务系统中每天都需要出售日报，这个操作需要对当天所有数据进行汇总，并进行计算，以得到全天收入，产品销售排名，分时段的销售量，甚至与过去30天及去年当天进行对比，这样的操作都属于OLAP。

分布式事务 XA 场景：异地转账，下单去库存（订单系统和库存系统两地）
重点：所有参与的事务要么都提交，要么都回滚。
[图片上传失败...(image-a3432b-1552459199375)])
资源管理器：通常一个数据库就是一个资源管理器；mysql数据库
事务管理器：协调参与全局事务的各个事务，需要和全局事务的所有资源管理器进行通信；连接mysql服务器的客户端
分布式事务使用两阶段提交的方式：2PC
第一阶段：参与全局事务的节点prepare，告诉事务管理器他们准备好提交了。
第二阶段：事务管理器告诉资源管理器执行 rollback 还是 commit。
考虑各种异常情况
分库分表 1. 分库代价