数据库

1.存储引擎

数据库底层软件组织，不同引擎提供不同存储机制，索引技巧，锁定水平等功能，使用不同的存储引擎可以获得特定功能。
MyIASM InnoDB Memory Archive Federated

show engines

1.1 MyIASM

MySQL默认引擎，没有提供事务支持，行级锁，外键。
插入更新需要锁定整个表，效率低
读取速度快，不占用大量内存，存储资源，ISAM静态索引结构

• 非聚集索引，数据文件分类，保存是数据文件指针，主键索引和辅助索引独立。
• 保存整个表行数
• 支持全文索引

1.2 Memory

Heap堆内存，使用在内存的内容创建表，每个表只实际对应一个磁盘文件，访问非常快，默认使用Hash索引
服务关闭，表内数据丢失，支持散列索引和B树索引，B树索引可以使用部分查询和通配查询，也可以使用><=等操作，散列索引较快但是对于比较较慢

1.3.InnDB

底层为B+树，B树每个节点对应innodb的一个page，page大小固定16k，只有非叶子结点有键值，叶子节点包含完成数据
场景适用：

• 经常更新的表，适合处理多重并发的更新请求
• 支持事务,每一条默认成事务默认提交，影响速度，最好吧多个语句用begin和commit之间组成事务
• 可以从灾难中恢复（bin-log日志）
• 外键约束 只有它支持外键
• 自增列 auto_increment
• 聚集索引：数据文件和索引绑在一起，必须有主键，效率高。但是辅助索引需要俩次查询，先查主键，再通过主键查数据，主键不能过大，否则索引很大
• 不保存表行数 count需要全表扫描
• 不支持全文索引
  四个特性：
• 插入缓冲（insertbuffer)
• 二次写(doublewrite)
• 自适应哈希索引(ahi)
• 预读(readahead)

2.索引

MySQL高效获取数据的数据结构。

2.1 分类

• 主键索引（PRIMARY）：数据列不允许重复,NULL
• 唯一性索引（UNIQUE）：值唯一,不重复

alter table name
add unique (column1,c2)

• 全文索引（FULLTEXT）
• 普通索引（INDEX）
• 联合索引 create index 索引名 on 表名（字段名1，字段名2）

2.2 索引失效

• 不满足最左匹配原则：设置联合索引（c1,c2,c3），where 查询时顺序，只要最左边有c1，c2 c3无论有没有都会走索引
• select *
• 索引列有计算，函数
• 字段类型不同 varchar类=100
• like左边有%
• 列对比：cl = c2
• or 前后都要索引
• not in关键字查询数据范围，而普通索引字段使用了not in关键字查询数据范围，索引会失效。主键字段不会
• not exists 失效
• order by 字段满足最左匹配且后面加了limit关键字， 配合where字段满足最左匹配，排序规则一致
  不加 where limit, 不是联合索引（失效）

2.3 大表加索引

• 在线无锁加索引
  ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
• 影子拷贝
  1、创建一张和原表结构一样的空表，只是表名不一样
  create table tb_name_tmp like tb_name;
  2、把新建的空表非主键索引都删掉，因为这样在往新表导数据的时候效率会很快（因为除了必要的主键以外，不用再去建立其它索引数据了）
  alter tb_name_tmp drop index index_name;
  3、从旧表往主表里导数据，如果数据太大，建议分批导入，只需确保无重复数据就行，因为导入数据太大，会很占用资源(内存，磁盘io, cpu等)，可能会影响旧表在线上的业务。
  insert into tb_name_tmp select * from tb_name where id between start_id and end_id;
  4、数据导完后，再对新表进行添加索引
  create index index_name on tb_name_tmp(column_name);
  5、当大部分数据导入后，索引也建立好了，但是旧表数据量还是会因业务的增长而增长，这时候为了确保新旧表的数据一至性和平滑切换，建议写一个脚本，判断当旧表的数据行数与新表一致时，就切换。
  rename table tb_name to tb_name_tmp1;

2.4 索引原理

把无序的数据变成有序的查询

1. 把创建了索引的列的内容进行排序
2. 对排序结果生成倒排表
3. 在倒排表内容上拼上数据地址链
4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

• B+树算法
  

• n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。

• 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

• 所有的非终端结点可以看成是索引部分，结点中仅含其子树中的大（或小）关键字。

• B+树中，数据对象的插入和删除仅在叶节点上进行。

• B+树有2个头指针，一个是树的根节点，一个是小关键码的叶节点

• Hash算法
  通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法
  将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；
  如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。

2.5 索引下推ICP

索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

• 没有使用ICP的情况下，MySQL的查询：

存储引擎读取索引记录；
根据索引中的主键值，定位并读取完整的行记录；
存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

• 使用ICP的情况下，查询过程：

存储引擎读取索引记录（不是完整的行记录）；
判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；
条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；
存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分

2.5.1 条件

只能用于range、 ref、 eq_ref、ref_or_null访问方法；
只能用于InnoDB和 MyISAM存储引擎及其分区表；
对InnoDB存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;
索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于InnoDB的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。
引用了子查询的条件不能下推；
引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

3.事务

3.1 特性

• 原子性：事务是一个完整操作
• 一致性：事务完成，数据必须一致
• 隔离性：并发事务隔离，事务必须独立
• 永久性：事务完成后，修改永久保持

3.2 并发事务问题

• 脏读: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
• 丢失修改: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
• 不可重复读: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。重点在数据修改
• 幻读: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。重点在记录个数变化

3.3 事务隔离级别

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
• innoDB 事务隔离级别可重读，使用Next-Key Lock算法，避免幻读产生。在分布式事务中用到可串行化隔离级别

4.存储过程

一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过
程是数据库中的一个重要对象。

5.触发器

触发器是一段能自动执行的程序，是一种特殊的存储过程，触发器和普通的存储过程的区别是：
触发器是当对某一个表进行操作时触发。诸如：update、insert、delete 这些操作的时候，系统会自动调用执行该表上对应的触发器。SQL Server 2005 中触发器可以分为两类：DML 触发器和DDL 触发器，其中DDL 触发器它们会影响多种数据定义语言语句而激发，这些语句有create、alter、drop 语句。

6.并发策略

6.1 乐观锁

6.2 悲观锁

• 排它锁 写锁
• 共享锁 读锁

6.3 时间戳

单独加一列时间戳。每次读时，对该字段加一，提交前，进行比对，如果比数据库值大，允许保存，否则不允许。

7.数据库锁

7.1 行级锁

粒度小，开销大
InnoDB是基于索引来完成行锁
select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id不是索引键那么InnoDB将
完成表锁，并发将无从谈起

7.2 表级锁

对整张表加锁

7.3 页级锁

锁定粒度介于行级锁和表级锁中间一种

8.视图

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。
对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

9.范式

第一范式：所有列不可拆分
第二范式：第一+ 非主列完全依赖主键，不依赖其他非主键
第三：第二+ 非主列只依赖主键

10.sql执行过程

11.读写分离

读写分离是依赖于主从复制，而主从复制又是为读写分离服务的。
因为主从复制要求slave不能写只能读（如果对slave执行写操作，那么show slave status将会呈现Slave_SQL_Running=NO，此时你需要按照前面提到的手动同步一下slave）。

11.1 mysql-proxy代理

优点：直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号，mysql官方不建议实际生产中使用
缺点：降低性能， 不支持事务

11.2 使用AbstractRoutingDataSource + aop + annotation在dao层决定数据源

如果采用了mybatis， 可以将读写分离放在ORM层，
比如mybatis可以通过mybatis plugin拦截sql语句，所有的insert/update/delete都访问master库，
所有的select 都访问salve库，这样对于dao层都是透明。
plugin实现时可以通过注解或者分析语句是读写方法来选定主从库。
不过这样依然有一个问题， 也就是不支持事务， 所以我们还需要重写一下DataSourceTransactionManager，
将read-only的事务扔进读库， 其余的有读有写的扔进写库。

11.3 使用AbstractRoutingDataSource+aop+annotation在service层决定数据源

可以支持事务.
缺点：类内部方法通过this.xx()方式相互调用时，aop不会进行拦截，需进行特殊处理

12.主从复制

主从复制：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；
从而使得从数据库的数据与主数据库保持一致。

12.1 作用

• 主数据库出现问题，可以切换到从数据库。
• 可以进行数据库层面的读写分离。
• 可以在从数据库上进行日常备份。
• 数据分布：随意开始或停止复制，并在不同地理位置分布数据备份
• 负载均衡：降低单个服务器的压力
• 高可用和故障切换：帮助应用程序避免单点失败
• 升级测试：可以用更高版本的MySQL作为从库

12.2 工作原理

在主库上把数据更高记录到二进制日志从库将主库的日志复制到自己的中继日志
从库读取中继日志的事件，将其重放到从库数据中基本原理流程，3个线程以及之间的关联
主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的 binlog中；
从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；

Binary log：主数据库的二进制日志
Relay log：从服务器的中继日志
第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到 binlog文件中。
第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。
如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程目的是将这些事件写入到中继日志中。
第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

12.3 Binlog

• row: 不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，
  基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如altertable)，因此这种模式的
  文件保存的信息太多，日志量太大。

• statement:每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少
  了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存
  相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。

• mixed:一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。