范式

范式就是数据表在设计的时候, 遵循的一些原理, 反应表中属性之间的关系

(1) 第一范式

强调数据库中的每一列属性都不可继续拆分, 例如如果有一列是电话, 可以拆分工作电话和生活电话, 那就不符合第一范式

(2) 第二范式在第一范式的基础上, 强调表中不能存在某些属性对于唯一码部分依赖;

唯一码不是指的的自增id, 而是指得每一行记录由几个属性唯一确定, 这几个属性共同唯一确定一条记录

例如一张表有学号姓名科目成绩

唯一码就是学号 + 科目, 唯一确定的是成绩

这里的姓名对于学号 + 科目就是部分依赖, 因为相同姓名对于不同科目, 是对应同一姓名的.

因此姓名在这里就不符合第二范式, 要想符合, 只能拆成两个表

(3) 第三范式

第三范式是基于第二范式的, 进一步消除某些属性对于唯一码的依赖传递

表明上看唯一码A能唯一确定其它所有列B,C,D

但是B又能确定C,D

因此就发生了依赖传递, 此时应该拆成A, B和B,C,D两张表

索引和锁参考链接

索引和锁

B树

(1) B树有多层, 每层有多个节点, 每层节点包含多个元素, 每个元素包含key和value, 每个节点中不同key是有序的, 每一层节点key也是有序的, 都是从左往右递增. value对应于具体的数据

(2) B树所有层节点中的元素不会重复(元素的key可能会重复, 但是value不会重复)

(3) B树的每个节点对应了磁盘中的一页, 因此每个节点是有大小限制的

(4) B树的阶是有所有节点中, 子节点的最大值决定. 例如B树中一个节点有5个子节点, 在树中最多, B树就是5阶, 阶数非常重要. 这里的每个节点最多有几个子节点是和每个元素的大小有关的, 如果元素小, 那么当前节点能包含的元素就更多, 每个元素就代表有一个子节点. 那么B树的阶就可以更多

(5) 根节点只有2个子节点, 其余节点的子节点数量n满足阶数 / 2 <= n <= 阶数之间.

(6) 每个节点能够包含的元素数量K满足 (阶数 / 2 - 1) <= K <= (阶数 - 1)

(7) B树是自平衡的多叉搜索树, 在增加元素和删除元素时, 如果某个节点的元素数量不符合要求, 新增元素会使得节点分裂(中间节点升级到上层, 递归进行), 删除元素会使得节点合并

核心就是制定节点和节点元素数量的限制, 来保证性能

B+树

在B树的基础上

(1). 除了最后一层, 上面的每层节点都只存储key值, 而不存储value值, 这样每个节点(一个磁盘块)就能存储更多的元素, 也就是相当于对于同样的数据, B+树比B树的阶更高. 上层具有更多的节点的话, B+树就会变得更矮, 查找的路径更短.

(2) 最后一层节点的元素包括所有的元素, 最后一层不同节点之间是连同的, 可以顺序访问

(3) B+树其它的就和B树特点一样了

跳表就是内存形式的B+树, 跳表是redis用的, 数据都是在内存中, 因此照着B+树的思路设计了跳表

B树和B+树如何通过一个节点到达另一个节点

(1) 将根节点根据磁盘地址记载到内存中经历一次IO

(2) 根据根节点的内容查到下一节点的磁盘地址经历一次IO

(3) 根据磁盘地址将内容加载到内存中, 查找到目标元素, 返回.

因此在使用索引的时候, 对于B树经历过几个节点就经历过几次IO, 对于B+树, IO次数永远是树的高度.

mysql中B树和B+树中存储数据的部分, 存的是数据在磁盘中的地址, 还是数据本身?

跟数据引擎有关

InnoDB中

主键id对应的索引树, 叫做聚簇索引, 所有子叶节点存的就是数据本身, 而除了主键id以外其它的索引树, 子叶节点存的是主键id的值, 再通过聚簇索引去查询具体的值.

也就是聚簇索引中最后一层子叶节点的磁盘读取到内存的时候, 每个元素的value就是数据本身

非聚簇索引中最后一层子叶节点中的磁盘读取到内存的时候, 每个元素的value是主键id

MyISAM中

没有聚簇索引, 子叶节点存的值从磁盘读取到内存的时候, 每个元素的value值依旧是一个磁盘地址, 需要再经过一次IO

mysql索引

不同索引的特点和区别

(1) B+树索引

InnoDB使用该索引作为基础索引, 通常情况下说的索引都是指B+树索引而不是哈希索引.

如果每一个列都建立了索引, 那么每一列都对应一个B+树, 每一列每一行的值, 都对应于B+树中每个节点的元素的key值, value值视情况而定

因为有序性, 该索引可以用于查找、排序和分组

适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

(2) 哈希索引

用于精确查找, 无法范围查找

无法用于排序和分组

一般用于补充B+树索引, 例如某个值总是被精确查找, 就会新增一条哈希索引

(3) 全文索引

MyISAM 和InnoDB 存储引擎在 MySQL 5.6.4 版本之后支持

是一种倒排索引用于查找关键字

查找条件使用 MATCH AGAINST，而不是普通的 WHERE

InnoDB中聚簇索引和主键索引的关系?

主键索引可以没有, 但是聚簇索引是一定有的. 因为InnoDB中, 数据都是存储在聚簇索引中的, 如果没有聚簇索引, 那么什么数据都查不到.

聚簇索引详解

聚簇索引与非聚簇索引

什么是全表扫描?

说的全表扫描, 或者回表查询, 其实本意上就是指的就是挨个查询表中的记录行, 本身记录行是存储在文件中的, 那么Mysql在使用的时候, 是去直接读取这个文件吗?

其实不是, 例如InnoDb, 会把所有数据构成聚簇索引, 这里叫索引, 但是实际上可以理解为一个目录, 相当于全表扫描的时候, 就是遍历这个目录的所有子叶节点, 就相当于全表扫描了. 而MyIsam因为子叶节点存的都是数据的地址, 因此全表扫描的时候, 还需要再去读一下对应地址里的数据.

索引使用问题

mysql就单列和多列索引两种

单列索引

无法使用索引的情况:

1. 索引列作为表达式的一部分 select * from xxx where id + 1 = 2;
1. 索引列作为函数的入参, 显式使用 select * from xxx where sum(id) = 1;
1. 索引列作为函数的入参, 隐式使用 select * from xxx where id = "1"; id在mysql定义为int, 参数传递的字符串1, 隐藏了类型转换函数

上述三种情况都不会使用索引

单列索引, 当检索条件是(A, B, C)的时候, 会先判断分别按照A, B, C检索哪个结果最少(前提是A, B, C上都有索引), 假设表中符合条件A的10w条, 符合B的12W条, 符合C的5w条, 那么就按照C得到5W条结果, 然后全表遍历这5W条数据, 得到结果

多列索引

多列索引比单列索引效果好, 因为多列索引, 每一列的索引都会用到, 而如果每一列分别有索引的话, 多个参数的时候, 只会使用其中选择性最强的一列索引, 其它的不会用到.

B+树中的多列索引, 当检索条件是(A, B, C)的时候, 上层非子叶节点的key值只会存储A索引的值, 最后一层叶子节点, 就会出现多个key相同的节点, 然后value值是按列存储的B, C索引的值, 相同的key, 不同的value, 按照value的顺序排序的.

多列索引的好处

如何避免回表查询, 什么是索引覆盖?

(1) 避免回表

如果索引树中已经包含了我们查询的所有字段, 那么只查找索引树就可以得到结果, 就无需再次回到表中继续查询了.

(2) 索引覆盖

索引覆盖由有两种含义:

(1) 如果是非排序的查询, 将所需字段都建立到组合索引中, 避免回表.
(2) 如果是排序查询, 将排序字段加入索引中，保证该索引树的节点包含排序字段。

mysql在对字段进行排序的时候, 会判断SELECT中的字段 + 排序字段的值大小小于等于参数max_length_for_sort_data, 如果小于, 就会有更高的查询性能.

排序字段的大小也会影响排序性能

(3) 多列索引不是顺序查找而是二分查找

InnoDB是顺序查找的吗?

多列索引按照第一列查找到某一个子叶节点后, 在那一页中对于多个每个元素, 因为元素都是有序的, 就按照二分查找的方式进行查找.

多列索引顺序选择问题

多列索引遵循最左前缀, 多列索引是先通过检索第一个索引, 然后在结果中检索第二个索引, 因此索引的顺序选择应该检索能力强的在左边.

现在的mysql都会有优化器, 如果你在查询的时候, 没有按照最左前缀, 优化器会帮你自动优化成符合最左前缀.

IN字段查询应该查询多少个值合适?

IN字段查询多少个值合适在MySQL中有一个配置参数eq_range_index_dive_limit，它的作用是一个等值查询(比如：in查询)，其等值条件数小于该配置参数，则查询成本分析使用扫描索引树的方式分析，如果大于等于该配置参数，则使用索引统计的方式分析。

如果SQL中IN查询字段id的值出现的数量小于eq_range_index_dive_limit，则走索引树扫描分析查询成本，大于等于eq_range_index_dive_limit，则走索引统计的方式分析查询成本。

索引树扫描分析: 得到的成本结果是精确的，这就意味着MySQL可以选择正确的执行计划，保证语句查询的性能
索引统计分析: 查询成本分析过程快，代价低。但是，它的缺点也很明显，由于无需扫描索引树，通过粗略统计索引使用情况，得出查询成本，导致MySQL可能选错执行计划，使得SQL查询性能下降.

因此IN查询的字段，该字段的值不要超过eq_range_index_dive_limit这个参数，让MySQL能够正确选择执行计划，保证SQL查询的性能。

eq_range_index_dive_limit参数的默认值在5.7版本更新为200。

禁用索引

mysql默认情况下是会为主键建立索引的, 索引能够在查询的时候大大提升我们的查询速度, 提升性能. 但是同样的, mysql构建索引同样需要花费时间, 那么在插入数据的时候建立索引就会降低插入的速度. 为了解决这种问题, 我们可以在大批量插入数据库数据的时候, 关闭索引, 然后再开启索引.

(1) 禁用索引

关闭所有索引: alter table table_name DISABLE KEYS;

开启所有索引: alter table table_name ENABLE KEYS;

(2) 在某条sql语句强制使用指定索引或者强制不使用指定索引.

1、mysql强制使用索引:force index(索引名或者主键PRI)

例如:

select * from table force index(PRI) limit 2;(强制使用主键)

select * from table force index(ziduan1_index) limit 2;(强制使用索引"ziduan1_index")

select * from table force index(PRI,ziduan1_index) limit 2;(强制使用索引"PRI和ziduan1_index")

2、mysql禁止某个索引：ignore index(索引名或者主键PRI)

例如:

select * from table ignore index(PRI) limit 2;(禁止使用主键)

select * from table ignore index(ziduan1_index) limit 2;(禁止使用索引"ziduan1_index")

select * from table ignore index(PRI,ziduan1_index) limit 2;(禁止使用索引"PRI,ziduan1_index")

禁用唯一性检查

插入数据时, MySQL会对插入的记录进行唯一性校验(唯一键索引), 这种校验也会降低插入的速度.

禁用唯一性检查: SET UNIQUE_CHECKS = 0;

开启唯一性检查: SET UNIQUE_CHECKS = 1;

InnoDB行级锁的思考

行锁的劣势：开销大；加锁慢；会出现死锁
行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强

行级锁

InnoDB中, 实现行级锁是通过对索引项加锁实现的, 只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁. 具体加锁是在哪个索引树(主键索引, 还是普通索引), 就看sql语句到底命中的是哪个索引了.

如果一条 SQL 语句用到了主键索引，mysql 会锁住主键索引；如果一条语句操作了非主键索引，mysql 会先锁住非主键索引，再锁定主键索引。

　　在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。下面通过一些实际例子来加以说明。

　　（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
　　（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
　　（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
　　（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

三种行级锁

Record Lock: 单个记录上的锁
Gap Lock: 间隙锁，锁定一个范围，但不包括记录本上
Next-Key Lock: Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

例如一个索引有10,11,13,20这四个值。InnoDB可以根据需要使用Record Lock将10，11，13，20四个索引锁住，也可以使用Gap Lock将(-∞,10)，(10,11)，(11,13)，(13,20)，(20, +∞)五个范围区间锁住。Next-Key Locking类似于上述两种锁的结合，它可以锁住的区间有为(-∞,10]，(10,11]，(11,13]，(13,20]，(20, +∞)，可以看出它即锁定了一个范围，也会锁定记录本身。 InnoDB存储引擎的锁算法的一些规则如下所示，后续章节会给出对应的实验案例和详细讲解。

在不通过索引条件查询时，InnoDB 会锁定表中的所有记录。所以，如果考虑性能，WHERE语句中的条件查询的字段都应该加上索引。

InnoDB通过索引来实现行锁，而不是通过锁住记录。因此，当操作的两条不同记录拥有相同的索引时，也会因为行锁被锁而发生等待。

由于InnoDB的索引机制，数据库操作使用了主键索引，InnoDB会锁住主键索引；使用非主键索引时，InnoDB会先锁住非主键索引，再锁定主键索引。

当查询的索引是唯一索引(不存在两个数据行具有完全相同的键值)时，InnoDB存储引擎会将Next-Key Lock降级为Record Lock，即只锁住索引本身，而不是范围。

InnoDB对于辅助索引有特殊的处理，不仅会锁住辅助索引值所在的范围，还会将其下一键值加上Gap LOCK。

InnoDB使用Next-Key Lock机制来避免Phantom Problem（幻读问题）。

行锁加在哪些索引上?

InnoDB存储引擎的行锁是通过锁住索引实现的，而不是记录。这是理解很多数据库锁问题的关键。

由于InnoDB特殊的索引机制，数据库操作使用主键索引时，InnoDB会锁住主键索引；使用非主键索引时，InnoDB会先锁住非主键索引，再锁定主键索引。

如下图所示，a字段是主键, b字段是某个属性. 当InnoDB锁定非主键索引b时，它也会锁住其对应的主键索引，所以锁住b值为2和3的非主键索引，那么与其对应的主键的a值为6,5的主键索引也需要被锁住。

这种情况就可能会出现死锁的情况, 如下图所示:

为啥InnoDb可以实现行级锁?

InnoDB因为聚簇索引的存在, 所有的查询最后一定都是经过聚簇索引的(无论是命中索引, 还是全表扫描), 最后都是通过聚簇索引来获取数据的, 因此行级锁最终都会加在聚簇索引上, 这样就保证了, 不会出现行级锁和全表扫描出现冲突的问题.

死锁问题

当多个事务同时持有和请求同一资源上的锁而产生循环依赖的时候就产生了死锁。即A事务锁定了第x行数据, 然后需要锁定第y行数据, 此时B事务锁定了第y行的数据, 需要锁定第x行的数据. 这样A, B事务就在互相等待锁, 造成死锁.

在InnoDb下, 行级锁更容易出现死锁的问题.

show engine innodb status可以查看到死锁的日志

Mysql如何处理死锁

mysql本身可以自己解决死锁问题.

MySQL有两种死锁处理方式：

等待，直到超时（innodb_lock_wait_timeout=50s）。
发起死锁检测，主动回滚一条事务，让其他事务继续执行（innodb_deadlock_detect=on）。

由于性能原因，一般都是使用死锁检测来进行处理死锁。

死锁检测

死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。

回滚

检测到死锁之后，选择插入更新或者删除的行数最少的事务回滚，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。

如何避免死锁

死锁解决方案 (1) 增加索引

细化索引的粒度, 避免行锁一次性锁太多的数据

(2) 不同事务之间获取锁的顺序尽量保持一致

例如事务A要锁a和b, 那么事务B也是锁a和b(不要先锁b再锁a), 这样B在锁a的时候就会失败.

(3) 如果需要锁定多个资源, 一次性锁定, 要么都锁定成功, 要么直接失败(进入等待)

例如直接一次性锁多个id,

Select * from xxx where id in (xx,xx,xx) for update

explain 的用法

EXPLAIN语句返回MYSLQ的执行计划，通过他返回的信息，我们能了解到MYSQL优化器是如何执行SQL语句的，通过分析他能帮助你提供优化的思路。也就是explain只会返回执行计划, 并不会真正执行.

MYSQL 5.6.3以前只能EXPLAIN SELECT; MYSQL5.6.3以后就可以EXPLAIN SELECT,UPDATE,DELETE

explain 返回的关键字段type

type可能的返回值表示索引的链接类型

从好到坏依次为: system > const > eq_ref > ref > range > index > ALL 如果想利用索引, 最差要求基本是range, 最好能到ref. 这里的好坏其实就是使用索引树的查找的次数, 次数越少性能越好.

system: 表仅有一行(等于系统表)。这是const联接类型的一个特例。

const: 针对唯一性索引, 表示经过一次索引树的查找就可以查找到数据, 针对主键索引, 针对单表查询. 例如 select * from xxx where id = 1;

eq_ref: 也是针对唯一性索引, 但是指的多表查询的情况, 就是在多表查询时, 使用的都是主键索引. 例如select t1, t2 from table1 t1, table2 t2 where t1.id = t2.id;

ref: 非唯一性索引, 也就是普通的索引利用, 可能会查找到一个或多个符合条件的行查询条件是精确查询, 也就是where跟的是具体的值例如 select * from xxx where name = "zhangsan";

range: 当查询条件是某个范围的时候, 在索引树找到所有在这个范围内的数据, 相当于进行了多次const或者ref. 因此性能要差一点例如 select * from xxx where id in (1, 2, 3);

index: 也就是说没有加where条件的时候, 只查询索引列, 这个时候只会全部扫描索引树. 例如 select name from xxx; 这里name是普通索引列,

all: 不使用索引, 直接全表扫描例如 select * from xxxx; 这里没有命中任何有索引列的行

explain其它重要字段

row: 影响的行数

possible_keys: 可能应用的索引

key: 实际使用的索引, 未使用是null

mysql数据引擎区别

InnoDB: 支持事务, 支持行锁, 支持四种隔离级别, 支持聚簇索引

MyISAM: 设计简单, 不支持事务, 不支持行锁, 要求比较低可以使用

MySQL索引

范式