MYSQL数据库: 插件式的存储引擎架构，将查询处理及其他的系统任务，以及数据的存储提取相分离。可根据也无需求选择相应的存储引擎。

 InnoDB引擎：

innodb主键是聚簇索引，采用b+树结构，非叶节点存的是主键和指向子节点的指针，叶子节点存的就是整体行数据，整体都是有序的，通过主键扫描根据树查找，最终落到叶子节点，命中然后返回。

数据和索引在一起存储的索引方式叫做聚簇索引，一张表只能有一个聚簇索引。(假设没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有的话则会隐式定义一个主键作为聚簇索引。)

非聚簇索引(二级索引)保存的是主键id值，这一点和myisam保存的是数据地址是不同的。

Mysql分层:

1 连接层

2 服务层

3 引擎层

4 存储层

事务Transaction：一系列操作统称事务；

事务的特性：原子性，一致性，隔离性，持久性

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性： 两个事物无法看到对方的中间状态的

@read uncommited  读未提交

脏读

原因：主要针对select，用户A更改了数据并未提交，用户B在select时候能查到用户A未提交的数据。

解决：设置隔离级别为读提交，利用快照读只读已经完成提交的数据。

@read commited    读提交

不可重复读

原因：主要针对update，用户A查询了数据后，用户B更新了数据值，等用户A再次查询数据时发现两次数据值不一样。

解决：设置隔离级别为可重复读，利用快照读，当A事务启动后不允许再修改数据，保证了可重复读，避免不了幻读

@repeatable read  可重复读

幻读

原因：主要针对insert delete,用户A查询了数据后，用户B插入了一条新数据或者删除了一条数据。等用户A再次读的时候发现数据数量两次不一样。

有一个操作是查询有没有这条数据，如果没有就插入一条。如果A B两个事物同时执行这个操作，A 发现没有数据，然后插入了一条，再A插入之前 B也发现了也插入一条。

解决：设置隔离级别为串行化，一个一个的事务施行，执行效率极差，开销贼大。

@serializable   串行执行

Mysql如何保证ACID

A原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql

C一致性一般由代码层面来保证

I隔离性由MVCC来保证

D持久性由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复

 

每次更新操作，会把老版本存入undo_log，undo log日志存着事务版本链

（MVCC叫做多版本并发控制，实际上就是保存了数据在某个时间节点的快照。

我们每行数实际上隐藏了两列，当前事务的版本号，上一个事务版本的地址，每开始一个新的事务，版本号都会自动递增。） 

MVCC主要用作读提交级别和可重复读级别，主要区别于生成ReadView策略不同，

有一个东西叫ReadView列表，维护了当前活跃着的读写事务，也就是通过ReadView判断当前事务该读什么

如果是读提交级别，当前事务select会重新生成一个readView，读到的也就是最新提交了的的数据

如果是可重复读级别，当前事务select会复用第一次select时候的readView，读到的还是那时候的readView

间隙锁

间隙锁是可重复读级别下才会有的锁，结合MVCC和间隙锁可以解决幻读的问题。

Spring事务支持

事物的传播性： 多个事务调用时，控制事务之间方法传播。

1. required 当没有事务时，创建一个事务，如果有就加入到这个事务。（默认）
2. supports 支持当前事务，如果没有当前事务，就按非事务执行
3. required_new 新建事务，如果当前有事务，就将当前事务挂起

事务执行方式：

（1）基于 TransactionProxyFactoryBean的声明式事务管理
（2）基于 @Transactional 的声明式事务管理
（3）基于Aspectj AOP配置事务

数据库查询性能下降 查询sql慢的原因:

1 查询语句写的烂，没用索引

2 索引失效

3 关联查询太多join

解决方式：

1 观察 看看生产中慢SQL的情况

2 开启慢查询日志  设置阈值 ，把超过5秒的慢SQL抓取出来

3 用explain进行分析

4 show profile

5 sql数据库参数调优

索引是一种数据结构  底层 B树(多路搜索树)

 

什么是索引， ： 排好序的快速查找数据结构就是索引     1排序  2查找

索引的目的： 1降低查找成本  2降低排序成本

缺点:  影响增删改速率  每次增删改都改更新索引

提高检索效率，单纯为检索而生

索引本身都很大，以索引文件的形式存储在磁盘

当一个表中有大量记录时，为表中的某一个字段建立索引，不用从头遍历整个表来查找，而是可以通过索引来快速查找。

索引会增加数据库存储空间，每次修改表数据时索引也要修改. 所以适合查找多修改少的操作

索引中不能包含空值的列，如果组合索引中有一个是空值那么整条索引都是无效的

索引种类

唯一索引：索引对应的列是唯一的，不允许有空值

主键索引：

组合索引：索引中包含多个字段

总的来说就这么一句话!

连接查询：

左连接 left join      select *from A left join B on  A.a = B.b where ?  左表的全部+左右共有利用补null)  select *from A left join B on A.a = B.b where B.b is null 左表全部去掉左右共有

右连接 right join     select *from A right join B on A.a = B.b where ?  右表的全部+左右共有

利用补null)_ select *from A right join B on A.a = B.b where A.a is null 右表全部去掉左右共有

内连接 inner join      select * from A inner join B on A.a = B.b  where ?  左右的共有

全连接 full outer join    select *from A full outer join B on A.a = B.b where? 左右全连接

左连接+右连接+union并联去重 = 全连接 (mysql不支持直接全连接)

Explain全解:

 

*id:  

相当于表读取的优先级      id相同 执行顺序由上到下   id不同 id越大优先级越高

select_type :

select类型   simple  primary  subquery derived union unionresult

*type:

访问类型排列  system > const > eq_ref > ref > range > index > All (一般range就很牛逼)

range(用索引检测给定范围内between and < > in)

index(用索引只遍历索引树查询，进行全表扫描)

All(全表扫描)

possible_keys:

本次查询可能用到的索引

*key:

本次查询实际用到的索引

key_len:

索引字段的最大可能长度，而不是索引实际长度

ref:

先使用到了索引的哪一个字段 const表示一个常量

*rows:

大致估算出查询所用的行数(越少越好)

*extra:

using Filesort: 产生原因排序查询order by时 不能完全按照索引的顺序排序。没办法mysql自己又创建一次排序

using Temporary: 产生临时表大量降低数据库性能, group by 时没按照索引顺序，

using Index : 表明要查询的列完全被索引覆盖——覆盖索引    

索引优化：  

~范围后的查找会导致索引失效(一楼二楼三楼的共有索引，当二楼是个范围查询，三楼会失效，导致索引用不到，新创建内部表(using Filesort))

~左连接索引加在右表，右连接加左表；

~多用小表驱动大表

~优先优化括号内查询

索引失效原因：

1最佳左前缀法则，（索引为多列时，不能跳过左边直接查右边）

2使用函数使索引失效， 查询语句中不要对索引列 使用计算函数 例如left

3 范围之后全失效， 多列索引下 (左边的列是范围查询则右边的索引失效)

4 索引包括运算，   SELECT book_id FROM BOOK WHERE book_id + 1 = 5; book_id失效

5 隐式类型转换 ， SELECT * FROM tradelog WHERE tradeid=110717;    tradeId 的类型是 varchar(32), 而此 SQL 用 tradeid 一个数字类型进行比较，发生了隐形转换，会隐式地将字符串转成整型。导致索引列是函数的一部分。

6 隐式编码转换， 联表查询时 两表的编码不同，作相等条件时发生了编码转换函数

7 使用了 SELECT *,导致虽然加了索引 还是要回表查询  mysql认为回表查询比全表扫描代价更大， 所以走了全表扫描 (解决：使用覆盖索引，使用limit10 减小数据量)

8 使用 !=或<>时 is null 或 is not null 会进行全表扫描 造成索引失效 少用or

9 模糊查询like  %加右边时索引不失效

10 order by没按照索引顺序,group by 没按照索引顺序

11 尽量使用覆盖索引  减少使用select *

小表驱动大表

select * from A where a in(select a from B);      in 是包含    exists是被包含于

select *from A where exists(select X from B where A.a = B.a);  exists相当于两次for循环，括号内为内部循环，当外部为小表 内部为大表时，小表驱动大表效率最佳，否则用in

orderBy排序优化：  尽量用index 避免filesort

双路排序->单路排序

1.避免使用select *          2.尝试使用sort_buffer_size            3.尝试提高max_length_for_sort_data

groupBy优化:

实质先排序，后分组

1.避免使用select *          2.尝试使用sort_buffer_size            3.尝试提高max_length_for_sort_data   4.能用where少用having

慢查询日志:

设置一个阈值,比其大的Sql全部捕捉

开启慢查询:  set global  slow_query_log 1

set global slow_query_time 5  //超过五秒查出来

日志分析工具  mysqldumpslow               mysqldumpslow  -s -r -t  10  slow.log

showProfile  查询出最近使用的语句  进行sql分析  默认关闭，保存最近15次运行结果

showProfile  cpu,block  io for query  3;     对第三条进行诊断

诊断结果超标：converting heap to MyISAM  查询结果太大，内存不够用了，往磁盘上搬了

creating  tmp  table 创建临时表   拷贝数据到临时表再删除

Copying to tmp table on disk   把内存中临时表复制到磁盘

locked  锁了

数据库锁：

读锁--- 共享锁 

写锁--- 排它锁

表锁-- 偏读          表锁会锁定整张表并且阻塞其他用户对该表的所有读写操作，比如alter修改表结构的时候会锁表。

行锁-- 偏写       行锁又可以分为乐观锁和悲观锁，悲观锁可以通过for update实现，乐观锁则通过版本号实现。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁

用法:

show  open  tables          查看所有表上是否有锁

lock  table 表名   read/write

unlock  tables

分库分表问题

1. 为什么要分库分表？   大量请求打到mysql上扛不住，分库分表 分到数据库压力

2. 分库分表中间件？  sharding-jdbc  mycat

3. 垂直拆分与水平拆分？ 

水平拆分就是表结构不动，把数据分给多个表存储。根据关键字段 例如orderId进行哈希模运算，存入不同的表

垂直拆分就是表结构拆分，根据字段的访问频率，把访问频率高的字段与低的字段分开

4.如何做不停机的分库分表迁移

方案一： 长时间停机分库分表   写临时代码，通过数据库中间件，迁移到多库多表

方案二： 不停机双写方案  增量数据既写入老的单库单表  又通过中间件写入新的分库分表，  后台代码将老的单库单表不断地往新库新表同步，并不断比较  进行持久多轮 直到两边完全一致

5. 分库分表主键Id统一问题

1. 设定步长，比如1-1024张表我们分别设定1-1024的基础步长，这样主键落到不同的表就不会冲突了。
2. 分布式ID，自己实现一套分布式ID生成算法或者使用开源的比如雪花算法这种
3. 分表后不使用主键作为查询依据，而是每张表单独新增一个字段作为唯一主键使用，比如订单表订单号是唯一的，不管最终落在哪张表都基于订单号作为查询依据，更新也一样。
4. 单独用一个表来记录自增主键id，用这个自增主键存入分库分表，强行写入id

读写分离问题

1. 主从复制原理: 

 IO线程及 从库写入到relay日志到同步到本地binlog日志都是单线程的

1. 主从同步延迟问题

主库是并发写入  从库是串行同步，会造成主从同步延迟问题

2. 防止同步中途主库挂掉 导致丢数据问题  

采用半同步复制（记录同步成功的位置），主库写入一条binlog强制同步到从库，从库写完relay日志返回一个ack给主库证明同步成功

mysql底层存储及IO过程理解