数据库复习指南

索引分析工具：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)]

  <http://hedengcheng.com/?p=771>

  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. 分库代价

• join操作，多机无法使用

• 单机事务变成多机事务（XA，自己业务代码来实现）

• 成本，多机

2. 分表：垂直分表+水平分表

• count

• order by排序操作无法完成，需要业务代码完成或者中间件