数据库优化笔记

如何定位及优化SQL语句的性能问题？创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因？

对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划，MySQL提供了explain命令来查看语句的执行计划（oracle 通过EXPLAIN PLAN查看）。 我们知道，不管是哪种数据库，或者是哪种数据库引擎，在对一条SQL语句进行执行的过程中都会做很多相关的优化，对于查询语句，最重要的优化方式就是使用索引。 而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等

执行计划包含的信息 id 有一组数字组成。表示一个查询中各个子查询的执行顺序;
id相同执行顺序由上至下。
id不同，id值越大优先级越高，越先被执行。
id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。
select_type 每个子查询的查询类型，一些常见的查询类型

type(非常重要，可以看到有没有走索引) 访问类型
ALL 扫描全表数据
index 遍历所有索引，索引全扫描
range 索引范围查找，常用语<,<=,>=,between,in等操作
ref 使用非唯一索引查找数据或唯一索引前缀扫描
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询，在join查询中使用PRIMARY KEYorUNIQUE NOT NULL索引关联。
index_subquery 在子查询中使用 ref
unique_subquery 在子查询中使用 eq_ref
ref_or_null 对Null进行索引的优化的 ref
fulltext 使用全文索引
type由上至下，效率越来越高
possible_keys 可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。

key 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。
rows : 扫描的行数，行数越少，效率越高
执行效率：
ALL < index < range< ref < eq_ref < const < system。最好是避免ALL和index
1.查询优化
在保证在实现功能的基础上，尽量减少对数据库的访问次数；
通过搜索参数，尽量减少对表的访问行数,最小化结果集，从而减轻网络负担；
能够分开的操作尽量分开处理，提高每次的响应速度；
在数据窗口使用SQL时，尽量把使用的索引放在选择的首列；
在查询时，不要过多地使用通配符如SELECT * FROM T1语句，要用到几列就选择几列如：SELECT COL1,COL2 FROM T1；在可能的情况下尽量限制尽量结果集行数如：SELECT TOP 300 COL1,COL2,COL3 FROM T1,因为某些情况下用户是不需要那么多的数据的。
具体要注意的：

1.1.应尽量避免在 where 子句中对字段进行 null 值判断，避免使用!=或<>操作符，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
1.2.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
1.3.in 和 not in 也要慎用，因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
1.4 模糊查询
2一般分页查询

SELECT * FROM table LIMIT offset,rows

分页查询方式会从数据库第一条记录开始扫描，所以越往后，查询速度越慢，而且查询的数据越多，也会拖慢总查询速度
3使用子查询优化
这种方式先定位偏移位置的 id，然后往后查询，这种方式适用于 id 递增的情况

select * from orders_history where type=8 limit 100000,1;
select id from orders_history where type=8 limit 100000,1;
select * from orders_history where type=2 and id between 1000000 and 1000100 limit 100;

这种查询方式能够极大地优化查询速度，基本能够在几十毫秒之内完成。限制是只能使用于明确知道id的情况
4. MyISAM和InnoDB区别
两者的对比：

是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
是否支持外键： MyISAM不支持，而InnoDB支持。
是否支持MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一
5. 并发事务带来哪些问题
脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现**多了一些（少了一些）原本不存在的记录，**就好像发生了幻觉一样，所以称为幻读。

不可重复读 主要是说多次读取一条记录, 发现该记录中某些列值被修改过。
而 幻读 主要是说多次读取一个范围内的记录(包括直接查询所有记录结果或者做聚合统计), 发现结果不一致
事务隔离级别有哪些?MySQL的默认隔离级别是?
READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。（项目一般使用它）
REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。(默认)在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking（next-key锁其实包含了记录锁和间隙锁，即锁定一个范围，并且锁定记录本身） 防止幻读。
SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
6. 如何解决事务的并发问题
数据库通过锁机制解决并发访问的问题。
根据锁定对象不同：分为行级锁和表级锁；
根据并发事务锁定的关系上看：分为共享锁定和独占锁定，共享锁定会防止独占锁定但允许其他的共享锁定。而独占锁定既防止共享锁定也防止其他独占锁定。为了更改数据，数据库必须在进行更改的行上施加行独占锁定，insert、update、delete和selsct for update语句都会隐式采用必要的行锁定。
但是直接使用锁机制管理是很复杂的，基于锁机制，数据库给用户提供了不同的事务隔离级别，只要设置了事务隔离级别，数据库就会分析事务中的sql语句然后自动选择合适的锁。
6.1 MyISAM和InnoDB存储引擎使用的锁：
MyISAM采用表级锁(table-level locking)。
InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁。
表级锁和行级锁对比：
表级锁： MySQL中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。
行级锁： MySQL中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。
虽然使用行级索具有粒度小、并发度高等特点，但是表级锁有时候也是非常必要的：

事务更新大表中的大部分数据直接使用表级锁效率更高；
事务比较复杂，使用行级索很可能引起死锁导致回滚。
垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；
水平分区
保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。
水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库 。
水平分表分库
RANGE
从0到10000一个表，10001到20000一个表；

HASH取模
一个商场系统，一般都是将用户，订单作为主表，然后将和它们相关的作为附表，这样不会造成跨库事务之类的问题。 取用户id，然后hash取模，分配到不同的数据库上。美团的分库分表的方案是32个数据库实例.

地理区域
比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此。

时间
按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。
分库分表后面临的问题
事务支持
分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价； 如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

多库结果集合并（group by，order by）

跨库join（水平分库能join，垂直分库不行）

分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表， 结果原本一次查询能够完成的业务，可能需要多次查询才能完成。

粗略的解决方法：
（1）全局表：基础数据，所有库都拷贝一份。
（2） 字段冗余：这样有些字段就不用join去查询了。
（3）系统层组装：分别查询出所有，然后组装起来，较复杂。

分库分表之后,id 主键如何处理？
因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要一个全局唯一的 id 来支持。
生成全局 id 有下面这几种方式：
UUID：不适合作为主键，因为太长了，并且**无序不可读，查询效率低。**比较适合用于生成唯一的名字的标示比如文件的名字。
数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
**利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。**但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
Twitter的snowflake算法 ：Github 地址：https://github.com/twitter-archive/snowflake。
美团的Leaf分布式ID生成系统 ：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据库、Zookeeper等中间件。感觉还不错。美团技术团队的一篇文章：https://tech.meituan.com/2017/04/21/mt-leaf.html 。
推荐方法：
需要一个单独的机制来负责生成唯一ID，生成出来的ID也可以叫做分布式ID，或全局ID。下面来分析各个生成分布式ID的机制。

7.1 数据库自增ID:
这种生成分布式ID的机制，需要一个单独的Mysql实例，虽然可行，但是基于性能与可靠性来考虑的话都不够，业务系统每次需要一个ID时，都需要请求数据库获取，性能低，并且如果此数据库实例下线了，那么将影响所有的业务系统。
7.2 雪花算法
让负责生成分布式ID的每台机器在每毫秒内生成不一样的ID
核心思想是：分布式ID固定是一个long型的数字，一个long型占8个字节，也就是64个bit
分为三部分：
标识部分（1bit）、时间戳部分（41bit）、工作机器id（10bit）、序列号部分（12bit）

利用数据库集群并设置相应的步长（Flickr方案）
优点：高可用、ID较简洁。 缺点：需要单独的数据库集群。

Twitter Snowflake
优点：高性能高可用、易拓展。 缺点：需要独立的集群以及ZK。

带有业务属性的方案： > 时间戳+用户标识码+随机数

分库分表工具推荐：
sharding-jdbc（当当）
sharding-jdbc 是一款轻量级 Java 框架，**以 jar 包形式提供服务，是属于客户端产品不需要额外部署，**它相当于是个增强版的 JDBC 驱动
**sharding-jdbc的兼容性也非常强大，**适用于任何基于 JDBC 的 ORM 框架，如：JPA， Hibernate，Mybatis，Spring JDBC Template 或直接使用的 JDBC。
完美兼容任何第三方的数据库连接池，如：DBCP， C3P0， BoneCP，Druid， HikariCP 等，几乎对所有关系型数据库都支持。
只需配置待分库分表的数据表即可

Oracle，SqlServer中都是选择读已提交(Read Commited)作为默认的隔离级别，为什么Mysql不选择读已提交(Read Commited)作为默认隔离级别，而选择可重复读(Repeatable Read)作为默认的隔离级别呢？

这个是有历史原因的，从主从复制开始讲起！
主从复制，是基于什么复制的？
是基于binlog复制的。binlog是一个记录数据库更改的文件

binlog有几种格式？
statement:记录的是修改SQL语句
row：记录的是每行实际数据的变更
mixed：statement和row模式的混合

Mysql在5.0这个版本以前，binlog只支持STATEMENT这种格式！而这种格式在读已提交(Read Commited)这个隔离级别下主从复制是有bug的，因此Mysql将可重复读(Repeatable Read)作为默认的隔离级别！

**bug:主从不一致性的问题！**原因其实很简单，就是在master上执行的顺序为先删后插！而此时binlog为STATEMENT格式，它记录的顺序为先插后删！从(slave)同步的是binglog，因此从机执行的顺序和主机不一致！就会出现主从不一致！

解决方案有两种！
(1)隔离级别设为可重复读(Repeatable Read),在该隔离级别下引入间隙锁。当Session 1执行delete语句时，会锁住间隙。那么，Ssession 2执行插入语句就会阻塞住！
间隙锁： 是一个在索引记录之间的间隙上的锁
间隙锁的作用
保证某个间隙内的数据在锁定情况下不会发生任何变化


产生间隙锁的条件（RR事务隔离级别下）：
使用普通索引锁定；
使用多列唯一索引；
使用唯一索引锁定多行记录。
是为了解决幻读问题时引入的锁机制。需要对一定范围内的数据进行加锁，间隙锁就是解决这类问题的。
(2)将binglog的格式修改为row格式，此时是基于行的复制，自然就不会出现sql执行顺序不一样的问题！奈何这个格式在mysql5.1版本开始才引入。因此由于历史原因，mysql将默认的隔离级别设为可重复读(Repeatable Read)，保证主从复制不出问题！

项目中为什么选读已提交(Read Commited)作为事务隔离级别？
一：在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多！

二：在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行

三：在RC隔离级别下，半一致性读(semi-consistent)特性增加了update操作的并发性！
在5.1.15的时候，innodb引入了一个概念叫做“semi-consistent”，减少了更新同一行记录时的冲突，减少锁等待。
所谓半一致性读就是，一个update语句，如果读到一行已经加锁的记录，此时InnoDB返回记录最近提交的版本，由MySQL上层判断此版本是否满足update的where条件。若满足(需要更新)，则MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)！
MySQL的复制原理以及流程
主从复制：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。
主从复制的作用
主数据库出现问题，可以切换到从数据库。
可以进行数据库层面的读写分离。
可以在从数据库上进行日常备份。
MySQL主从复制工作原理
在主库上把数据更新记录到二进制日志
从库将主库的日志复制到自己的中继日志
从库读取中继日志的事件，将其重放到从库数据中
基本原理流程，3个线程以及之间的关联
主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；
从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；
从：sql执行线程——执行relay log中的语句；

MVCC(多版本并发控制)
MVCC是被Mysql中 事务型存储引擎InnoDB 所支持的;
应对高并发事务, MVCC比单纯的加锁更高效;
MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;
其他两个隔离级别够和MVCC不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。
MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;
InnoDB的MVCC是通过在每行记录后面保存三个隐藏的列来实现的
undo-log
Undo log是InnoDB MVCC事务特性的重要组成部分。当我们对记录做了变更操作时就会产生undo记录。Undo记录中存储的是老版本数据，当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着undo链找到满足其可见性的记录。
InnoDB存储引擎在数据库每行数据的后面添加了三个字段
6字节事务ID(DB_TRX_ID)字段：最后一次修改(insert|update)本行记录的事务id
7字节的回滚指针(DB_ROLL_PTR)字段:指写入回滚段(rollback segment)的 undo log record (撤销日志记录记录)。
6字节的DB_ROW_ID字段: 包含一个随着新行插入而单调递增的行ID, 当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。

小结：
一般我们认为MVCC有下面几个特点：
每行数据都存在一个版本（不同的事务session会看到自己特定版本的数据，版本链）每次数据更新时都更新该版本
修改时Copy出当前版本, 然后随意修改，各个事务之间无干扰
保存时比较版本号，如果成功(commit)，则覆盖原记录, 失败则放弃copy(rollback)

而InnoDB实现MVCC的方式是:
事务以排他锁的形式修改原始数据
把修改前的数据存放于undo log，通过回滚指针与主数据关联
修改成功（commit）覆盖原记录，失败则恢复undo log中的数据（rollback）
通过 for update 语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞，排他锁包含 行锁、表锁

例如 : select * from goods where id = 1 for update;

ACID靠什么保证
A原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
C一致性由其他三大特性保证、程序代码要保证业务上的一致性
I隔离性由MVCC来保证
D持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redo log恢复。
（InnoDB redo log 写盘，InnoDB 事务进入 prepare 状态。如果前面 prepare 成功，binlog 写盘，再继续将事务日志持久化到 binlog，如果持久化成功，那么InnoDB 事务则进入 commit 状态(在 redo log 里面写一个 commit 记录)

慢查询的优化首先要搞明白慢的原因是什么？是查询条件没有命中索引？是load了不需要的数据列？还是数据量太大？
所以优化也是针对这三个方向来的，
首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表