数据库

阅读量：4097 次

发布时间：2019-05-25

本文共 19795 字，大约阅读时间需要 65 分钟。

Oracle的Rownum和Rowid

rownum和rowid都是伪列，但是两者的根本是不同的。

rownum是根据sql查询出的结果给每行分配一个逻辑编号，所以你的sql不同也就会导致最终rownum不同。

但是rowid是物理结构上的，在每条记录insert到数据库中时，都会有一个唯一的物理记录。

rowid具有唯一性，查询时效率是很高的。

ACID

原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）

事务前后不破坏关系数据的完整性以及业务逻辑上的一致性。

隔离性（Isolation）

当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。

持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的。

Mysql使用常见总结

MySQL行溢出与最大行

MySQL每个表有4096个列的硬限制,MySQL表的最大行大小限制为65535字节，这里不包括TEXT、BLOB。

单个字段如果大于65535，则转换为TEXT 。

int、char、varchar的查询效率

无索引：全表扫描不会因为数据较小就变快，而是整体速度相同，int/bigint作为原生类型稍快12%。

有索引： char与varchar性能差不多，int速度稍快18%。

在数据存储、读写方面，整数与等长字符串相同，varchar额外多了一个字节所以性能可能会些许影响(1/n)。

在数据运算、对比方面，整数得益于原生支持，因此会比字符串稍快一丁点。

若采用索引，所谓整数、字符串的性能差距更是微乎其微。

几乎性能相当，mysql中区别性能的是采用哪种索引方式，而不是索引的数据类型。

MySQL的btree索引和hash索引的区别

hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像btree(B-Tree)索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 hash 索引的查询效率要远高于 btree(B-Tree) 索引。

虽然 hash 索引效率高，但是 hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些。

（1）hash 索引仅仅能满足=，<=>，IN，IS NULL或者IS NOT NULL查询，不能使用范围查询。

由于 hash 索引比较的是进行 hash 运算之后的 hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 hash 算法处理之后的 hash 值的大小关系，并不能保证和hash运算前完全一样。

（2）hash 索引无法被用来避免数据的排序操作。

由于 hash 索引中存放的是经过 hash 计算之后的 hash 值，而且hash值的大小关系并不一定和 hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

Mysql数据库隔离级别，如何实现？

●读未提交 (脏读)

●读已提交（出现不可重复读）

●可重读（解决不可重复读，出现幻读），Mysql默认

●串行

不可重复读重点在于update和delete，而幻读的重点在于insert。

如何实现隔离级别

事务隔离性，本质上解决事务的读写冲突的，在mysql中，读操作被实现为一种叫做“非锁定读取“的技术。

数据库里有共享锁和排它锁，读操作就是共享锁，只有共享锁和共享锁是非互斥的，那么如果一个读操作要在一个正在被修改的数据上进行，那么是无法加上获取该行对象的共享锁的，这便是数据库串行化隔离界别的实现方式，但是在其余的隔离界别下，我们不需要这么严格的隔离，我们允许了读操作可以在正在被修改的数据上即加上了排它锁的行数据上进行，这就是所谓的”非锁定读取“概念。

那么，读取的结果是什么？这个还需要明白另一点，就是我们数据库MVCC和undo日志。

我们每一次的修改操作，并不是直接对行数据进行操作，比如我们设置id为3的行的A属性为10，并不是直接修改表中的数据，而是新加一行，同时数据表其实还有一些隐藏的属性，比如每一行的事务id，所以每一行数据可能会有多个版本，每一个修改过它的事务都会有一行，并且还会有关联的undo日志，表示这个操作原来的数据是什么，可以用它做回滚。那么为什么要这么做？因为如果我们直接把数据修改了，那么其他事务就用不了原先的值了，违反了事务的一致性。

那么一个事务读取某一行的数据到底返回什么结果呢？取决于隔离级别。

1、如果是Read Committed，那么返回的是最新的事务的提交值，所以未提交的事务修改的值是不会读到的，这就是Read Committed实现的原理。

2、如果是Read Repeatable级别，那么只能返回发起时间比当前事务早的事务的提交值和比当前事务晚的删除事务删除的值。这其实就是MVCC方式。

正常的锁一般是锁一行，称为是Record锁，而”Next Lock“则是锁一个范围内的行。

3、关于可串行化的隔离界别，也很简单，严格的按照加锁协议来就可以，该级别可以避免上述所有的问题。

-事务在读取数据时，必须先对其加表级共享锁，直到事务结束才释放；

-事务在更新数据时，必须先对其加表级排他锁，直到事务结束才释放。

4、READ_UNCOMMITED

-事务对当前被读取的数据不加锁；

-事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级共享锁，直到事务结束才释放。

共享锁和排它锁

●MYISAM是表级锁，Innodb是行级锁。

●MyISAM在执行查询前，会自动执行表的加锁、解锁操作，一般情况下不需要用户手动加、解锁，但是有的时候也需要显示加锁。

●加了共享锁的对象，可以继续加共享锁，不能再加排他锁。加了排他锁后，不能再加任何锁。

锁的互斥关系总结

1、当单事务没有竞争的时候：先加共享锁或者排它锁都没有问题；

2、当多事务出现竞争的时候：

（1）事务1加排它锁，其他事务不能加排它锁/共享锁；

（2）事务1加共享锁，其他事务还可以添加共享锁，这时候没有事务可以添加排它锁。

MVCC多版本并发控制

是为了解决幻读的问题而出现的。

MVCC只在READ COMMITED 和 REPEATABLE READ 两个隔离级别下工作。

在innodb中“MVCC多版本一致性读”功能的实现是基于undo-log的。

undolog

1	开始事务
2	记录数据行数据快照到undo log
3	更新数据
4	将undo log写到磁盘
5	将数据写到磁盘
6	提交事务

Innodb隐藏列

Innodb通过undo log保存了已更改行的旧版本的信息的快照。

innodb存储的最基本row中包含一些额外的存储信息：

DATA_TRX_ID：最新修改此行记录的事务ID

DATA_ROLL_PTR：指向本数据行undo log，之前版本的数据就存于这里

DELETE BIT：标识此记录是否被删除

DB_ROW_ID：若指定了主键，则主键生成聚集索引，若未指定，以该列自动生成聚集索引。

MVCC的工作过程

SELECT

InnoDB 会根据两个条件来检查每行记录：

InnoDB只查找版本(DB_TRX_ID)早于当前事务版本的数据行（行的系统版本号<=事务的系统版本号,这样可以确保数据行要么是在开始之前已经存在了，要么是事务自身插入或修改过的）

行的删除版本号(DB_ROLL_PTR)要么未定义（未更新过），要么大于当前事务版本号（在当前事务开始之后更新的）。这样可以确保事务读取到的行，在事务开始之前未被删除。

INSERT

InnoDB为新插入的每一行保存当前系统版本号作为行版本号

DELETE

InnoDB为删除的每一行保存当前的系统版本号作为行删除标识

UPDATE

InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识

总结

一般认为MVCC的流程：

1、每行数据都存在一个版本，每次数据更新时都更新该版本

2、修改时Copy出当前版本随意修改，各个事务之间无干扰

3、保存时比较版本号，如果成功，则覆盖原纪录；失败，则放弃copy；

Innodb的实现方式：

1、事务以排他锁的形式修改原始数据

2、把修改前的数据存放于undo log，通过回滚指针与主数据关联

3、修改成功（commit）啥都不做，失败则恢复undo log中的数据（rollback）

Next-key Lock

区间锁（Gap Locks）仅仅锁住一个索引区间(开区间)。在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。

record lock + gap lock=next-key lock, 左开右闭区间。

默认情况下，innodb使用next-key locks来锁定记录。

但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

Gap Lock中存在一种插入意向锁（Insert Intention Lock），在insert操作时产生。在多事务同时写入不同数据至同一索引间隙的时候，并不需要等待其他事务完成，不会发生锁等待。(都是插入，没有for update)

总结：

1、事务1添加S锁，如果此时没有其他事物，事务1还可以添加X锁；

2、事务1添加S锁，同时有其他事物添加S锁，这时哪个事务也不能添加X锁或者直接使用update；

快照读和当前读（使用了mvcc和next-key解决了幻读）

在mysql中，提供了两种事务隔离技术，第一个是mvcc，第二个是next-key技术。这个在使用不同的语句的时候可以动态选择。不加lock in share mode之类的就使用mvcc。否则使用next-key。mvcc的优势是不加锁，并发性高。缺点是不是实时数据。next-key的优势是获取实时数据，但是需要加锁。

1. 快照读(snapshot read) mvcc

简单的select操作(不包括 select ... lock in share mode, select ... for update)

2.当前读(current read) next-key

select ... lock in share mode

select ... for update

insert

update

delete

意向锁

①在mysql中有表锁，

LOCK TABLE my_tabl_name READ; 用读锁锁表，会阻塞其他事务修改表数据。

LOCK TABLE my_table_name WRITe; 用写锁锁表，会阻塞其他事务读和写。

②Innodb引擎又支持行锁，行锁分为：

共享锁，一个事务对一行的共享只读锁。

排它锁，一个事务对一行的排他读写锁。

③这两中类型的锁共存的问题

考虑这个例子：

事务A锁住了表中的一行，让这一行只能读，不能写。

之后，事务B申请整个表的写锁。

如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。

数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。

数据库要怎么判断这个冲突呢？

step1：判断表是否已被其他事务用表锁锁表

step2：判断表中的每一行是否已被行锁锁住。

注意step2，这样的判断方法效率实在不高，因为需要遍历整个表。

于是就有了意向锁。

（IS、IX）

在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。

在意向锁存在的情况下，上面的判断可以改成

step1：不变

step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。

注意：申请意向锁的动作是数据库完成的，就是说，事务A申请一行的行锁的时候，数据库会自动先开始申请表的意向锁，不需要我们程序员使用代码来申请。

Mysql保证事务失败回滚

为了支持事务，Innbodb引入了下面几个概念：

redo log

当客户端执行每条SQL（更新语句）时，redo log会被首先写入log buffer；当客户端执行COMMIT命令时，log buffer中的内容会被视情况刷新到磁盘。redo log在磁盘上作为一个独立的文件存在，即Innodb的log文件。

undo log

与redo log相反，undo log是为回滚而用，具体内容就是copy事务前的数据库内容（行）到undo buffer，在适合的时间把undo buffer中的内容刷新到磁盘。undo buffer与redo buffer一样，也是环形缓冲，但当缓冲满的时候，undo buffer中的内容会也会被刷新到磁盘；与redo log不同的是，磁盘上不存在单独的undo log文件，所有的undo log均存放在主ibd数据文件中（表空间），即使客户端设置了每表一个数据文件也是如此。

事务修改行的流程：

1、用排他锁锁定该行；

2、将修改后的数据记录到redo log；

3、把修改前数据存到undo log（定时删除老数据）；

4、更新缓存中的数据（修改当前行的值，填写事务编号，使回滚指针指向undo log中的修改前的行），如果这时候出错就抛出异常进行回滚；

5、事务提交：

<1>清理undo段信息；

<2>释放锁；

<3>undo，redo写入到磁盘持久化；

<2>缓存数据刷新到磁盘。

<3>当事务正常提交时Innbod只需要更改事务状态为COMMIT即可，不需做其他额外的工作，而Rollback则稍微复杂点，需要根据当前回滚指针从undo log中找出事务修改前的版本，并恢复。

Innodb引擎的undo日志是记录在表空间中单独的回滚段中。当mysql做update和delete操作的时候，实际的后台都是先把旧记录“删”了，如果是update和insert再把新记录“插入”进去。

这里的删不是真的删除，而是标识它被删除了。而插入也不一定是真的插入，很多情况下是原地覆盖原来的记录。

Mysql中SQL执行顺序

●from

●on

●join

●where

●group by

●avg,sum...

●having

●select

●distinct

●order by

●limit

Mysql中的锁

●MyISAM和MEMORY采用表级锁(table-level locking)

●BDB采用页面锁(page-level locking)或表级锁，默认为页面锁

●InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

加了共享锁的对象，可以继续加共享锁，不能再加排他锁。加了排他锁后，不能再加任何锁。

三级封锁协议

●一级封锁协议:事务T在修改数据R之前必须对其加X锁，直到事务结束才释放。一级封锁协议可以防止修改丢失，并保证事务T是可恢复的。在一级封锁协议中，如果仅仅是读数据不对其进行修改，是不需要加锁的，所以它不能保证可重复读和不读“脏”数据。

●二级封锁协议是:一级封锁协议加上事务T在读取数据R之前必须先对其加S锁，读完后即可释放S锁。二级封锁协议除防止丢失修改，还可进一步防止读“脏”数据。在二级封锁协议中，由于读完数据即可释放S锁，所以它不能保证可重复读。

●三级封锁协议:一级封锁协议加上事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。三级封锁协议可以防止丢失修改，读“脏"数据和不可重复读。

Mysql索引

索引的作用

●提高数据检索效率；

●降低数据排序成本；

索引语句

（1）使用ALTER TABLE语句创建索引。

语法如下：

alter table table_name add index index_name (column_list) ;

alter table table_name add unique (column_list) ;

alter table table_name add primary key (column_list) ;

（2）使用CREATE INDEX语句对表增加索引。

能够增加普通索引和UNIQUE索引两种。其格式如下：

create index index_name on table_name (column_list) ;

create unique index index_name on table_name (column_list) ;

（3）删除索引。

删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理，其格式如下：

drop index index_name on table_name ;

alter table table_name drop index index_name ;

alter table table_name drop primary key ;

树

度数：在树中，每个节点的子节点（子树）的个数就称为该节点的度（degree）。

阶数：（Order）阶定义为一个节点的子节点数目的最大值。（自带最大值属性）

二叉查找树

任意节点，它的左子树如果不为空,那么左子树上所有节点的值都小于根节点的值；

任意节点，他的右子树如果不为空，那么右子树上的所有节点的值大于根节点的值。

二叉查找树有些缺陷，因为它对树的左右子树的高度没有任何限制。

平衡二叉树(AVL)

二叉平衡树要求节点的左右子树的高度不要相差超过1。

由二叉平衡树的特性可以看到，查询的效率同样很高，同时又避免了二叉查找树出现链表那种极端情况。

红黑树

红黑树为平衡二叉树的一种。

特性：

1、节点是红色或黑色

2、根节点一定是黑色

3、每个叶节点都是黑色的空节点(NIL节点)

4、每个红节点的两个子节点都是黑色的(从每个叶子到根的所有路径上不能有两个连续的红节点)(即对于层来说除了NIL节点，红黑节点是交替的，第一层是黑节点那么其下一层肯定都是红节点，反之一样)

5、从任一节点到其每个叶子节点的所有路径都包含相同数目的黑色节点

在原树上插入20

可以看到，插入以后树已经不是一个平衡的二叉树，而且并不满足红黑树的要求，因为20和21均为红色，这种情况下就需要对红黑树进行变色，21需要变为黑色，22就会变成红色，如果22变成红色，则需要17和25都变成黑色

、

而17变成黑色显然是不成立的，因为如果17变为黑色，那么13就会变为红色，不满足二叉树的规则，因此此处需要进行另一个操作---------左旋操作

左旋：下图就是一个左旋的例子，一般情况下，如果左子树深度过深，那么便需要进行左旋操作以保证左右子树深度差变小

对于上图由于右子树中17变为黑色以后需要把13变成红色，因此进行一次左旋，将17放在根节点，这样既可保证13为红色，左旋后结果

而后根据红黑树的要求进行颜色的修改

进行左旋后，发现从根节点17，到1左子树的叶子节点经过了两个黑节点，而到6的左叶子节点或者右叶子节点要经历3个黑节点，很显然也不满足红黑树，因此还需要进行下一步操作，需要进行右旋操作

右旋：与左旋正好相反

就是根据红黑树的定义，进行左旋、右旋、变色进行满足。

这便是红黑树的一个变换，它主要用途有很多，例如java中的TreeMap以及JDK1.8以后的HashMap在当个节点中链表长度大于8时都会用到。

总结：

1、红黑树引入了“颜色”的概念。引入“颜色”的目的在于使得红黑树的平衡条件得以简化。

2、红黑树能够以O(log2 n)的时间复杂度进行搜索、插入、删除操作。此外，由于它的设计，任何不平衡都会在三次旋转之内解决。

3、在实际的系统中，例如，需要使用动态规则的防火墙系统，使用红黑树而不是散列表被实践证明具有更好的伸缩性。

索引分类

从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。

从应用层次来分：普通索引，唯一索引，复合索引

根据中数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引，非聚集索引。

聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。

非聚簇索引：不是聚簇索引，就是非聚簇索引

所谓的btree结构也好，或其他的类似结构也好，把握一个原则，接近二分法的查询效率，因为如果做一个完全有序的队列，那么插入，删除，修改需要做的操作开销太大了，所以才会有人设计树形结构，兼顾查询和更新操作。

查询效率的关键是有序，二分，反过来理解就是，无需遍历所有数据，即可实现快速的定位。

索引的底层实现

mysql默认存储引擎innodb只显式支持B-Tree( 从技术上来说是B+Tree)索引，对于频繁访问的表，innodb会透明建立自适应hash索引，即在B树索引基础上建立hash索引，可以显著提高查找效率，对于客户端是透明的，不可控制的，隐式的。

Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

Hash索引主要是Memory存储引擎使用。Hash检索效率高于B-tree，但是Hash索引适用范围比较窄。不能使用部分索引，总是全表扫描。

B-Tree(平衡多路查找树)索引

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。因此在讲B-Tree之前先了解下磁盘的相关知识。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：

mysql> show variables like 'innodb_page_size';

而系统一个磁盘块(innodb中的页)的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。m阶的B-Tree是满足下列条件的数据结构:

1. 每个节点最多有m个孩子。

2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。

3. 若根节点不是叶子节点，则至少有2个孩子

4. 所有叶子节点都在同一层，且不包含其它关键字信息

5. 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）

6. 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1

7. ki(i=1,…n)为关键字，且关键字升序排序。

8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

根据根节点找到磁盘块 1，读入内存。【磁盘 I/O 操作第 1 次】

比较关键字 29 在区间（17,35），找到磁盘块 1 的指针 P2。

根据 P2 指针找到磁盘块 3，读入内存。【磁盘 I/O 操作第 2 次】

比较关键字 29 在区间（26,30），找到磁盘块 3 的指针 P2。

根据 P2 指针找到磁盘块 8，读入内存。【磁盘 I/O 操作第 3 次】

在磁盘块 8 中的关键字列表中找到关键字 29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

B-Tree存在的问题：

（1）每个节点中有key，也有data，而每一个页的存储空间是有限的，如果data数据较大时就会导致每个节点（即一个页）能存储的key的数量很小.

（2）当存储的数据量很大时，同样1会导致B-Tree的深度较大，增加查询时的磁盘I/O次数，进而影响查询效率

B+Tree索引（MySQL使用B+Tree）

B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度，变化点：

（1）数据是存在叶子节点中的，非叶子节点只存储key

（2）数据节点之间是有指针指向的

相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子结点，而且所有叶子节点（即数据节点之间是一种链式环结构），因此可以对B+Tree进行两种查找运算

（1）对于主键的范围查找和分页查找

（2）从根节点开始，进行随机查找

数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

为什么索引结构默认使用B-Tree，而不是hash，二叉树，红黑树？

hash：虽然可以快速定位，但是没有顺序，IO复杂度高。

二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树：树的高度随着数据量增加而增加，IO代价高。

Fulltext

●Fulltext就是全文索引，只有Myisam存储引擎支持。（仅有char/varchar/text三种类型可建立fulltext全文索引）

R-tree

●R-tree索引主要用来解决空间数据检索的问题，仅有Myisam支持，主要用于范围查找。

innodb存储引擎

自增主键索引

自增主键索引就是使用了B+树，索引文件同时也是数据文件,存储了整张表的数据。也就是说，平时我们执行sql按照主键查询的时候，那么只需要从这个索引文件获取数据即可。这种索引也叫聚集索引，原因是所有数据是按照主键聚集的。

辅助索引（非自增主键索引,也可以叫非聚集索引）

这种索引文件的叶子节点存储了键值和书签。键值说的就是列的值，书签就是对应记录的主键的值，如果按照某个辅助索引来查询数据的时候，如果没有用到覆盖索引，那么就得分两步走：

1、先从辅助索引文件中获取到数据对应的主键；

2、根据主键从聚集索引中获取真实数据。

MyISAM存储引擎

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。这种称为非聚集索引,这个命名其实是对应上面innodb的叫法而已。

索引的缺点

索引对于增删改操作的性能影响比较大。

insert可能会：1、重构btree树，2、跨分页。

delete删除操作影响较小；

update:只有当update的这个字段，涉及到索引时，才需要维护索引

当大量批量操作的时候，会影响效率。

解决办法：

1、尽量保证操作在索引之前；

2、是否能够考虑在操作前，先删除索引；

最左前缀匹配原则

最左前缀匹配原则，原理：

1、如果是完全的字段，Myql会自动调整顺序；

2、如果是部分字段，必须满足前缀和顺序；

3、对于范围条件查询，MYSQL无法再使用范围后面的其他索引列了。但对多个等值条件查询则没有这样的限制。

4、BETWEEN”实际上相当于“IN”，也就是说emp_no实际是多值精确匹配

https://www.cnblogs.com/duanxz/p/5244736.html

Mysql优化目录

Linux系统及Mysql软件级别

操作系统：

●Linux足够的内存，及使用SSD+RAID阵列

●网络配置tcp对列数，超时时间等 sycsctl.cong

●ulimit 增加打开文件描述符数

●NTP的时间同步准确性

●注意下iptables的限制

●内网带宽足够

Mysql软件配置My.cnf：

●table_cache 表缓冲区

●query/key/read/sort/join/thread_buffer_size 各种缓冲区大小设置

●max_connections最大连接数

系统监控：

加装各种Mysql监控装置，便于及时发现和解决问题。

数据库设置

●表的垂直/水平拆分

●字段尽量使用最小的存储类型去替代，如可以用Int就不要用varchar

●尽量为字段设置默认值，不要为null

●将text/blog字段类型和基础数据表做分离

●分布式环境的唯一id考虑，尽量使用long，而非varchar

●数据库关联表尽量做字段冗余设计（反范式），减少查询中的数据关联

●数据库表字段不要过多，不要超过20个

●char查询效率高，但是消耗存储空间：varchar相反

●分库/分表，有基于业务拆分、有基于压力拆分

索引优化

索引的使用：

●更新频繁的、唯一性差的、不出现在where中的一般不用做索引。

●一般在where/group by/order by/on中出现的字段。

●联合索引：离散度大的放到前面，where要按照顺序使用

●索引会降低Insert/update速度，因为会重建索引

●mysql单表查询时只能使用一个索引

●冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引。

查询优化

●开启慢查询日志（使用Mysqldumpslow分析），show processlist等的监控；

●通过explain执行计划去分析慢查询sql

●尽量将大的查询拆分成多个小查询(IN代替JOIN)，让缓存更高效，更容易对数据库进行拆分

●SLECT不要使用*

●IN使用EXISTS代替，尽量不要使用IN和NOT IN ，IN的使用场景是在确定有限集合 IN (1,2,3,4,5)

简而言之，一般式：外表大，用IN；内表大，用EXISTS。

not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；

而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

●IN取代OR

●Like尽量不要用前%

●让驱动表尽可能的小

●WHERE/ORDER BY中字段的索引顺序的保持

●GROP BY ,ORDER BY的字段尽量在一个表中，便于优化

●WHERE中尽量不适用运算符（表达式、函数操作）

●尽量保持WHERE中不使用<>或!=操作

●COUNT(*)是所有行数，COUNT(具体字段)是表示这个字段不为空的行数

●SUM属性为NULL，SUM值也是NULL。

●COUNT/SUM的列建立索引。

●尽量用UNION ALL替代UNION

基于业务的优化

●使用no sql代替DB

●请求合并发送

●大翻页的处理，使用业务数据id代替，limit

●雪崩出现后的串行访问机制

●使用延迟访问（例如等待几秒钟）来解决主从同步的时间延迟问题

InnoDB引擎索引长度受限

https://blog.csdn.net/weixin_39372979/article/details/80825606

InnoDB单列索引长度不能超过767bytes，联合索引还有一个限制是长度不能超过3072。

InnoDB一个page的默认大小是 16 k。由于是Btree组织，要求叶子节点上一个page至少包含两条记录（否则就退化链表了）。所以一个记录最多不能超过 8 k。又由于InnoDB的聚簇索引结构，一个二级索引要包含主键索引，因此每个单个索引不能超过 4 k（极端情况，pk和某个二级索引都达到这个限制）。由于需要预留和辅助空间，扣掉后不能超过 3500 ，取个“整数”就是(1024*3)。

单列索引限制

默认情况下，InnoDB 引擎单一字段索引的长度最大为 767 字节，同样的，前缀索引也有同样的限制。当使用 UTF-8 字符集，每一个字符使用 3 字节来存储，767＝256*3－1，在 TEXT 或者 VARCHAR 类型的字段上建立一个超过 255 字符数的前缀索引时就会遇到问题。至于为什么字符长度限制在 256 内，我猜是为提高索引效率，应为varchar类型需要额外的字节保留其长度信息，256 就将其限定在一个字节了。但是在5.5以后，开始支持4个字节的uutf8。255×4>767, 于是增加了一个参数叫做innodblargeprefix。这个参数默认值是OFF，当改为ON时，允许列索引最大达到 3072 字节。要求表的 row_format 需要使用 compressed 或者 dynamic

大字段索引的解决办法

如果确实需要在单个很大的列上创建索引，或者需要在多个很大的列上创建联合索引，而又超过了索引的长度限制，解决办法是在建索引时限制索引prefix的大小：

例如：create index yarn_app_result_i4 on yarn_app_result (flow_exec_id(100), another_column(50));

这样，在创建索引时就会限制使用的每个列的最大长度。如上的例子中，在创建联合索引时，最多使用列flow_exec_id中前100个字符创建索引，最多使用another_column中前50个字符创建索引。这样子，就可以避免索引长度过大的问题。

数据库范式

1．第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

2．第二范式(确保表中的每列都和主键相关)

可以适当违反

3．第三范式(确保每列都和主键列直接相关,而不是间接相关)

数据库分表分库原则

第一步，首选垂直拆分

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面。

比如淘宝中期开始的数据库端按照业务垂直拆分：按照业务交易数据库、用户数据库、商品数据库、店铺数据库等进行拆分。

采用垂直拆分优点：1. 拆分后业务清晰，拆分规则明确。2. 系统之间整合或扩展容易。3. 数据维护简单。缺点：1. 部分业务表无法join，只能通过接口方式解决，提高了系统复杂度。2. 受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高。3. 事务处理复杂。

第二步：其次才是水平拆分

水平拆分的典型场景就是大家熟知的分库分表。

垂直拆分后遇到单机瓶颈，可以使用水平拆分。相对于垂直拆分的区别是：垂直拆分是把不同的表拆到不同的数据库中，而水平拆分是把同一个表拆到不同的数据库中。

相对于垂直拆分，水平拆分不是将表的数据做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中。

拆分原则

1.优先考虑缓存降低对数据库的读操作。

2.再考虑读写分离，降低数据库写操作。

3.最后开始数据拆分,切分模式：首先垂直（纵向）拆分、再次水平拆分。

4.首先考虑按照业务垂直拆分。

5.再考虑水平拆分：先分库(设置数据路由规则，把数据分配到不同的库中)

6.最后再考虑分表，单表拆分到数据1000万以内。

（1）能不分就不分，1000 万以内的表，不建议分片，通过合适的索引，读写分离等方式，可以很好的解决性能问题。

（2）分片数量尽量少，分片尽量均匀分布在多个 DataHost 上，因为一个查询 SQL 跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量。

（3）分片规则需要慎重选择，分片规则的选择，需要考虑数据的增长模式，数据的访问模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片、枚举分片、一致性 Hash分片，这几种分片都有利于扩容。

（4）尽量不要在一个事务中的 SQL 跨越多个分片，分布式事务一直是个不好处理的问题。

（5）查询条件尽量优化，尽量避免 Select * 的方式

水平拆分在多对对关系的处理的实践

场景：学生选课，好友关系

弱关系好友（粉丝）

一条弱关系的产生，会产生两条记录，一条关注记录，一条粉丝记录。

guanzhu(uid,guanzhu_uid) fensi(uid,fensi_id)

强关系好友（QQ）

1、friend(uid,uid2)

假设要查询uid=2的所有好友，只需在uid1和uid2上建立索引，然后：

select * from friend where uid1=2unionselect * from friend where uid2=2

即可得到结果。

2、强好友关系是弱好友关系的一个特例，A和B必须互为关注关系（也可以说，同时互为粉丝关系），即也可以使用关注表和粉丝表来实现：

guanzhu(uid, guanzhu_uid);fensi(uid, fensi_uid);

数据冗余是实现多对多关系水平切分的常用实践

对于强好友关系的两类实现：

friend(uid1, uid2)表

数据冗余guanzhu表与fensi表（后文称正表T1与反表T2）

在数据量小时，看似无差异，但数据量大时，数据冗余的优势就体现出来了：

friend表，数据量大时，如果使用uid1来分库，那么uid2上的查询就需要遍历多库

正表T1与反表T2通过数据冗余来实现好友关系，{1, 2}{2,1}分别存在于两表中，故两个表都使用uid来分库，均只需要进行一次查询，就能找到对应的关注与粉丝，而不需要多个库扫描

数据冗余，是多对多关系，在数据量大时，数据水平切分的常用实践。

如何进行数据冗余

方法一：服务同步冗余

业务方调用服务，新增数据

服务先插入T1数据

服务再插入T2数据

服务返回业务方新增数据成功

方法二：服务异步冗余

业务方调用服务，新增数据

服务先插入T1数据

服务向消息总线发送一个异步消息（发出即可，不用等返回，通常很快就能完成）

服务返回业务方新增数据成功

消息总线将消息投递给数据同步中心

数据同步中心插入T2数据

方法三：线下异步冗余

数据的双写不再由好友中心服务来完成，而是由线下的一个服务或者任务来完成，如上图1-6流程：

业务方调用服务，新增数据

服务先插入T1数据

服务返回业务方新增数据成功

数据会被写入到数据库的log中

线下服务或者任务读取数据库的log

线下服务或者任务插入T2数据

总结

好友业务是一个典型的多对多关系，又分为强好友与弱好友

数据冗余是一个常见的多对多业务数据水平切分实践

冗余数据的常见方案有三种

（1）服务同步冗余

（2）服务异步冗余

（3）线下异步冗余

数据冗余会带来一致性问题，高吞吐互联网业务，要想完全保证事务一致性很难，常见的实践是最终一致性。

最终一致性的常见实践是，尽快找到不一致，并修复数据，常见方案有三种

（1）线下全量扫描法

（2）线下增量扫描法

（3）线上实时检测法

垂直拆分

当一个表属性很多时，如何来进行垂直拆分呢？如果没有特殊情况，拆分依据主要有几点：

（1）将长度较短，访问频率较高的属性尽量放在一个表里，这个表暂且称为主表

（2）将字段较长，访问频率较低的属性尽量放在一个表里，这个表暂且称为扩展表

如果1和2都满足，还可以考虑第三点：

（3）经常一起访问的属性，也可以放在一个表里

分库分表

如何进行分库分表，目前互联网上有许多的版本，比较知名的一些方案：

•阿里的TDDL，DRDS和cobar；

•京东金融的sharding-jdbc；

•民间组织的MyCAT；

•360的Atlas；

•美团的zebra

但是这么多的分库分表中间件方案，归总起来，就两类： client模式和proxy模式。

client模式

proxy模式

无论是client模式，还是proxy模式，几个核心的步骤是一样的：SQL解析，重写，路由，执行，结果归并。

对亿级的表的处理

1、先分表，按照不同业务类型、范围分片、枚举分片、一致性 Hash分片，这几种分片都有利于扩容；

2、再分库，相关的放到一个库中；

3、最后分区（一般是查询索引列，range/hash）

4、定时数据迁移

Mysql读写分离方案

1 程序修改mysql操作类

优点：直接和数据库通信，简单快捷的读写分离和随机的方式实现的负载均衡，权限独立分配

缺点：自己维护更新，增减服务器在代码处理

druid+mybatis 多数据源及读写分离的处理

2 amoeba

参考官网：http://amoeba.meidusa.com/

优点：直接实现读写分离和负载均衡，不用修改代码，有很灵活的数据解决方案

缺点：自己分配账户，和后端数据库权限管理独立，权限处理不够灵活

Amoeba for MySQL

Amoeba for MySQL致力于MySQL的分布式数据库前端代理层，它主要在应用层访问MySQL的时候充当query 路由功能，专注分布式数据库 proxy 开发。座落与Client、DB Server(s)之间。对客户端透明。具有负载均衡、高可用性、Query过滤、读写分离、可路由相关的query到目标数据库、可并发请求多台数据库合并结果。在Amoeba上面你能够完成多数据源的高可用、负载均衡、数据切片的功能。目前在很多企业的生产线上面使用。

3 mysql-proxy

参考 mysql-proxy。

优点：直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号

缺点：字符集问题，lua语言编程，还只是alpha版本，时间消耗有点高

如果你可以安装软件，那amoeba是不错的，mysql-proxy不太建议，目前只有alpha版本，效率还不太理想，amoeba目前在阿里巴巴是内部项目，正在生产环境使用的。

4.采用mycat中间件方式

Mysql主从同步延迟

主从同步

(1)、三种主要实现粒度

详细的主从同步主要有三种形式：statement、row、mixed

1)、statement: 会将对数据库操作的sql语句写道binlog中

2)、row: 会将每一条数据的变化写道binlog中。

3)、mixed: statement与row的混合。Mysql决定何时写statement格式的binlog, 何时写row格式的binlog。

mysql在并行复制上的逐步优化演进：

mysql5.5 -> 不支持并行复制，对大伙的启示：升级mysql吧

mysql5.6 -> 按照库并行复制，对大伙的启示：使用“多库”架构吧

mysql5.7 -> 按照GTID并行复制，和原来的日志相比，多了last_committed和sequence_number。

last_committed表示事务提交时，上次事务提交的编号，如果具备相同的last_committed，说明它们在一个组内，可以并发回放执行。

延迟原理

谈到MySQL数据库主从同步延迟原理，得从mysql的数据库主从复制原理说起，mysql的主从复制都是单线程的操作，主库对所有DDL和 DML产生binlog，binlog是顺序写，所以效率很高，slave的Slave_IO_Running线程到主库取日志，效率很比较高，下一步，问题来了，slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施。DML和DDL的IO操作是随机的，不是顺的，成本高很多，还可能可slave上的其他查询产生lock争用，由于Slave_SQL_Running也是单线程的，所以一个DDL卡主了，需要执行10分钟，那么所有之后的DDL会等待这个DDL执行完才会继续执行，这就导致了延时。有朋友会问：“主库上那个相同的DDL也需要执行10分，为什么slave会延时？”，答案是master可以并发，Slave_SQL_Running线程却不可以。