001-mysql-01

事务的基本要素(ACID)

1、原子性（Atomicity）：事务开始后所有操作，要么全部完成，要么全部不完成，不可能停滞在中间环节。事务执行过程中出错，会回滚（Rollback）到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位 2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到 3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账 4、持久性（Durability）：事务完成后，该事务所对数据库所作的更改将被保存到数据库之中，不能回滚

事务隔离级别

第一种隔离级别：Read uncommitted(读未提交) 如果一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读此行数据，该隔离级别可以通过“排他写锁”，但是不排斥读线程实现。这样就避免了更新丢失，却可能出现脏读，也就是说事务B读取到了事务A未提交的数据

解决了更新丢失，但还是可能会出现脏读

第二种隔离级别：Read committed(读提交) 如果是一个读事务(线程)，则允许其他事务读写，如果是写事务将会禁止其他事务访问该行数据，该隔离级别避免了脏读，但是可能出现不可重复读。事务A事先读取了数据，事务B紧接着更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。

解决了更新丢失和脏读问题

第三种隔离级别：Repeatable read(可重复读取)-MySql的默认隔离级别 可重复读取是指在一个事务内，多次读同一个数据，在这个事务还没结束时，其他事务不能访问该数据(包括了读写)，这样就可以在同一个事务内两次读到的数据是一样的，因此称为是可重复读隔离级别，读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务(包括了读写)，这样避免了不可重复读和脏读，但是有时可能会出现幻读。(读取数据的事务)可以通过“共享读锁”和“排他写锁”实现。(幻读指的是A事务做范围查询，B事务插入了新的数据，导致A事务第二次范围查询和第一次不一致)

解决了更新丢失、脏读、不可重复读、但是还会出现幻读

第四种隔离级别：Serializable(可序化) 提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行，如果仅仅通过“行级锁”是无法实现序列化的，必须通过其他机制保证新插入的数据不会被执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也是最高的，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻读

解决了更新丢失、脏读、不可重复读、幻读(虚读)

数据库实现事务隔离的方式，基本可以分为以下两种。

  • 一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。

  • 另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（ＭultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。

MYSQL空间划分

MySQL表中的所有数据被存储在一个空间内，称之为表空间，表空间内部又可以分为段(segment)、区(extent)、页(page)、行(row)。

表空间是由不同的段组成的，常见的段有：数据段，索引段，回滚段等等，在 MySQL中，数据是按照B+树来存储，因此数据即索引，因此数据段即为B+树的叶子节点，索引段为B+树的非叶子节点

区是由连续页组成的空间，每个区的固定大小为1MB,为保证区中页的连续性，InnoDB会一次从磁盘中申请4~5个区，在默认不压缩的情况下，一个区可以容纳64个连续的页。

页是InnoDB存储引擎的最小管理单位，每页大小默认是16KB

行对应的是表中的行记录，每页存储最多的行记录也是有硬性规定的最多16KB/2-200,即7992行

MVCC多版本并发控制

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

在学习MVCC多版本并发控制之前，我们必须先了解一下，什么是MySQL InnoDB下的当前读和快照读?

  • 当前读 像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

  • 快照读 像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外)

select * from table where ?;

当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。 下面语句都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。

select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;

说白了MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 所以MVCC可以为数据库解决以下问题

  • 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能

  • 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

redo log、binlog、undo log

binlog 用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。

undo日志用于记录事务开始前的状态，用于事务失败时的回滚操作；redo日志记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。例如某一事务的事务序号为T1，其对数据X进行修改，设X的原值是5，修改后的值为15，那么Undo日志为<T1, X, 5>，Redo日志为<T1, X, 15>。

binlog是属于MySQL Server层面的，又称为归档日志，属于逻辑日志。

redo log是InnoDB存储引擎层的日志，又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。

undo是InnoDB存储引擎层的日志，用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

mysql的层级结构

MySQL 可以分为 Server 层和存储引擎层两部分。

1、Server层 主要包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

2、Store层 存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory等多个存储引擎。

mysql主从同步的工作过程

1、Master节点将数据的改变记录成二进制日志（bin log），当Master上的数据发生改变时，则将其改变写入二进制日志中。 2、Slave节点会在一定时间间隔内对Master的二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O线程请求 Master的二进制事件。 3、同时Master节点为每个I/O线程启动一个dump线程，用于向其发送二进制日志，并保存至Slave节点本地的中继日志（Relay log）中，Slave节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，即解析成 sql 语句逐一执行，使得其数据和 Master节点的保持一致，最后I/O线程和SQL线程将进入睡眠状态，等待下一次被唤醒。

Mysql主从复制的四种同步方式

1、异步复制(Async Replication)（默认同步方式）

主库将更新写入Binlog日志文件后，不需要等待数据更新是否已经复制到从库中，就可以继续处理更多的请求。Master将事件写入binlog，但并不知道Slave是否或何时已经接收且已处理。在异步复制的机制的情况下，如果Master宕机，事务在Master上已提交，但很可能这些事务没有传到任何的Slave上。假设有Master->Salve故障转移的机制，此时Slave也可能会丢失事务。MySQL复制默认是异步复制，异步复制提供了最佳性能。

2、同步复制( sync Replication ) 主库将更新写入Binlog日志文件后，需要等待数据更新已经复制到从库中，并且已经在从库执行成功，然后才能返回继续处理其它的请求。同步复制提供了最佳安全性，保证数据安全，数据不会丢失，但对性能有一定的影响。

3、半同步复制( semi-sync Replication) 主库提交更新写入二进制日志文件后，等待数据更新写入了从服务器中继日志中，然后才能再继续处理其它请求。该功能确保至少有1个从库接收完主库传递过来的binlog内容已经写入到自己的relay log里面了，才会通知主库上面的等待线程，该操作完毕。

4、增强半同步复制（lossless Semi-Sync Replication、无损复制） 增强半同步是在MySQL 5.7引入，其实半同步可以看成是一个过渡功能，因为默认的配置就是增强半同步，所以，大家一般说的半同步复制其实就是增强的半同步复制，也就是无损复制。增强半同步和半同步不同的是，等待ACK时间不同rpl_semi_sync_master_wait_point = AFTER_SYNC（默认） 半同步的问题是因为等待ACK的点是Commit之后，此时Master已经完成数据变更，用户已经可以看到最新数据，当Binlog还未同步到Slave时，发生主从切换，那么此时从库是没有这个最新数据的，用户看到的是老数据。 增强半同步将等待ACK的点放在提交Commit之前，此时数据还未被提交，外界看不到数据变更，此时如果发送主从切换，新库依然还是老数据，不存在数据不一致的问题。

MySQL锁

MySQL锁可以按使用方式分为：乐观锁与悲观锁。按粒度分可以分为表级锁，行级锁，页级锁。

**表级锁：**开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 **行级锁：**开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 **页面锁：**开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。

间隙锁GAP

当我们用范围条件检索数据而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”。

InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。

例子：假如emp表中只有101条记录，其empid的值分别是1,2,...,100,101

select * from emp where empid > 100 for update;

上面是一个范围查询，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁

InnoDB使用间隙锁的目的有2个：

  • 为了防止幻读(上面也说了，Repeatable read隔离级别下再通过GAP锁即可避免了幻读)

  • 满足恢复和复制的需要：MySQL的恢复机制要求在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！

MyISAM 锁

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作 （UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。在示例中，显式加锁基本上都是为了演示而已，并非必须如此。

在用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及到表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的 情况下也基本如此，MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁（Deadlock Free）的原因。

MyISAM并发插入（Concurrent Inserts）

上文提到过MyISAM表的读和写是串行的，但这是就总体而言的。在一定条件下，MyISAM表也支持查询和插入操作的并发进行。 MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

  • 当concurrent_insert设置为0时，不允许并发插入。

  • 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。

  • 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

MyISAM的锁调度

前面讲过，MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个 MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM 的调度行为。

  • 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。

  • 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。

  • 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

Innodb中的行锁与表锁

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决 定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

InnoDB的行锁模式及加锁方法

InnoDB实现了以下两种类型的行锁。

  • **共享锁（s）：又称读锁。**允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

  • **排他锁（Ｘ）：又称写锁。**允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。

  • 意向共享锁（IS）：事务打算给数据行加共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

  • 意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。 意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。 事务可以通过以下语句显式给记录集加共享锁或排他锁：

  • 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。

  • 排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE。

用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

innodb行锁简介

  1. 行锁类型

LOCK_S：共享锁

LOCK_X: 排他锁

  1. GAP类型

LOCK_GAP：只锁间隙

LOCK_REC_NO_GAP:只锁记录

LOCK_ORDINARY: 锁记录和记录之前的间隙

LOCK_INSERT_INTENTION: 插入意向锁，用于insert时检查锁冲突

每个行锁由锁类型和GAP类型组成 例如： LOCK_X|LOCK_ORDINARY 表示对记录和记录之前的间隙加排他锁 LOCK_S|LOCK_GAP 表示只对记录前的间隙加共享锁

锁的兼容性： 值得注意的是，持有GAP的锁（LOCK_GAP和LOCK_ORDINARY)与其他非LOCK_INSERT_INTENTION的锁都是兼容的，也就是说，GAP锁就是为了防止插入的。

innodb 锁分裂、继承与迁移

这里的锁分裂和合并，只是针对innodb行锁而言的，而且一般只作用于GAP类型的锁。

  • 锁分裂

    插入的记录的间隙存在GAP锁，此时此GAP需分裂为两个GAP

    锁继承

    删除的记录前存在GAP锁，此GAP锁会继承到要删除记录的下一条记录上

    锁迁移

    B数结构变化，锁信息也会随之迁移. 锁迁移过程中也涉及锁继承。

InnoDB 聚簇索引 (主键索引)（Clustered Index (Primary Index)）

聚簇索引与其说是索引，不如说是InnoDB用来存储记录的数据容器更为恰当。

InnoDB中的聚簇索引采用B+Tree组织起来，每个节点都是一个Page（InnoDB存储记录的最小单位）；非叶节点存 Key 的值和指向孩子节点的指针，叶子节点则存储记录和指向相邻叶节点的指针（所有叶节点构成一个双向链表），下面是一个简单的示意图：

InnoDB根据Key值顺序存储记录，相邻的Node彼此通过指针连接，这样有两个好处：

  1. 这样相关的记录会存储的比较近，读取相关记录的时候只需要Load少数Page就好。例如上图若要读取key 5和key 6的记录，只要加载page 5就好。

  2. 执行范围查询时不用整棵B+树都扫描一遍，只要找到最小的key值所在的叶节点Page，一直往后读即可。例如上图查询key值在5〜10的记录，只要找到key 5所在的page 5，然后一直往后读值到key 10所在的page 7为止。

当然排好序的记录也有一些缺点，如果插入key值无序的记录时容易造成性能问题。例如上图如果插入一条key为13的记录时，MySQL会直接写入到page 8；但若是插入一条key为9的记录时会导致page 7发生页拆分（Page Split），这种情况下MySQL会在Page之间移动记录，继而影响性能。

MySQL并不会把Page的全部空间都用完，相反，它会保留一部分空间为日后添加或更新使用

由上述原因可以知道，聚簇索引是如何影响范围查询、插入记录的性能以及Page空间的使用率的，所以一个恰当的聚簇索引键值（Clustered Key）很重要。MySQL选择聚簇索引键值的规则如下：

  1. 有Primary Key，选Primary Key；

  2. 没有Primary Key，选第一个NOT NULL的UNIQUE Key；

  3. 若都没有，则InnoDB生成一个auto increment的隐藏字段做聚簇索引键值。

InnoDB二级索引（Secondary Index）

二级索引同样使用B+Tree数据结构，不同的是叶节点只存储二级索引的键值和聚簇索引键值（通常是Primary Key），聚簇索引键值是用于回表查询该条记录。

注意到上图中二级索引键值的顺序和聚簇索引键值顺序通常不同，所以二级索引做范围查询读取记录的性能通常不如聚簇索引高效（回表操作会有大量的随机IO）。因为二级索引会存储聚簇索引的键值，因此储聚簇索引键值的大小也会影响二级索引的大小，所以在选择聚簇索引键值时需要注意这点。

另外当SELECT的字段被二级索引覆盖的话，MySQL就不需要再回表查询了，这样执行速度更快。

索引是个复杂的主题，但通过了解索引底层的运行原理可以帮助我们更精准的使用索引，基本原则如下：

  1. InnoDB读取记录是以Page为单位，加载一个Page只为读取一条记录是很浪费且低效的行为。挑选好Primary Key，可以利用访问局部性(Locality of Reference)提高性能（让相关的记录集中在几个Page里面，这样InnoDB加载一个Page就可以读取到多条记录）。

  2. 使用二级索引读取记录需要进行回表操作，正如同上面第一点提到的，加载一个Page读取一条记录是低效的。因此二级索引覆盖所有需要的字段对性能会有显著提升。

innodb和myisam对比

  1. Innodb **支持事务处理与外键和行级锁.**而MyISAM不支持

  2. InnoDB（主键索引）使用的聚簇索引、索引就是数据，顺序存储，因此能缓存索引，也能缓存数据

    MyISAM（堆组织表）使用的是非聚簇索引、索引和文件分开，随机存储，只能缓存索引

    MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

    MyISAM的查询性能会比InnoDB强

  3. MyISAM读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读

    InnoDB 读写阻塞与事务隔离级别相关

MyISAM分为：

静态 MyISAM（如果数据表中的各数据列的长度都是预先固定好的，服务器将自动选择这种表类型）、

动态 MyISAM 如果数据表中出现 varchar、text 或 BLOB 字段时，服务器将自动选择这种表类型

压缩 MyISAM 以上说到的两种类型的表都可以用 myisamchk 工具压缩。这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先时行解压缩。

B+树适合作为索引的结构 以及索引原理

mysql使用了 B+索引：

B树：有序数组+平衡多叉树； B+树：有序数组*链表*+平衡多叉树；

为什么平衡二叉树不适合作为索引呢？

索引是存在于索引文件中，是存在于磁盘中的。因为索引通常是很大的，因此无法一次将全部索引加载到内存当中，因此每次只能从磁盘中读取一个磁盘页的数据到内存中。而这个磁盘的读取的速度较内存中的读取速度而言是差了好几个级别。

注意，我们说的平衡二叉树结构，指的是逻辑结构上的平衡二叉树，其物理实现是数组。然后由于在逻辑结构上相近的节点在物理结构上可能会差很远。因此，每次读取的磁盘页的数据中有许多是用不上的。因此，查找过程中要进行许多次的磁盘读取操作。

平衡二叉树不适合作为索引。那么什么才适合作为索引—B树

平衡二叉树没能充分利用磁盘预读功能，而B树是为了充分利用磁盘预读功能来而创建的一种数据结构，也就是说B树就是为了作为索引才被发明出来的的。

B树的每个节点可以存储多个关键字，它将节点大小设置为磁盘页的大小，充分利用了磁盘预读的功能。每次读取磁盘页时就会读取一整个节点。也正因每个节点存储着非常多个关键字，树的深度就会非常的小。进而要执行的磁盘读取操作次数就会非常少，更多的是在内存中对读取进来的数据进行查找。

B树的查询，主要发生在内存中，而平衡二叉树的查询，则是发生在磁盘读取中。因此，虽然B树查询查询的次数不比平衡二叉树的次数少，但是相比起磁盘IO速度，内存中比较的耗时就可以忽略不计了。因此，B树更适合作为索引。

比B树更适合作为索引的结构是B+树。MySQL中也是使用B+树作为索引。它是B树的变种，因此是基于B树来改进的。为什么B+树会比B树更加优秀呢？

B树：有序数组+平衡多叉树； B+树：有序数组链表+平衡多叉树；

B+树的关键字全部存放在叶子节点中，非叶子节点用来做索引，而叶子节点中有一个指针指向一下个叶子节点。做这个优化的目的是为了提高区间访问的性能。而正是这个特性决定了B+树更适合用来存储外部数据。

数据库索引采用B+树的主要原因是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。
B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。

为什么选用B+树作为数据库的索引结构：

  1. B+树的中间节点不保存数据，是纯索引,但是B树的中间节点是保存数据和索引的,相对来说,B+树磁盘页能容纳更多节点元素，更“矮胖”；

  2. B+树查询必须查找到叶子节点，B树只要匹配到即可不用管元素位置，因此b+树查找更稳定（并不慢）；

  3. 对于范围查找来说，B+树只需遍历叶子节点链表即可，B树却需要重复地中序遍历,在项目中范围查找又很是常见的 增删文件（节点）时，效率更高，因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

(a) Inodb存储引擎 默认是 B+Tree索引

(b) MyISAM 存储引擎 默认是Fulltext索引；

(c)Memory 存储引擎 默认 Hash索引；

Hash索引

mysql中，只有Memory(Memory表只存在内存中，断电会消失，适用于临时表)存储引擎显示支持Hash索引，是Memory表的默认索引类型，尽管Memory表也可以使用B+Tree索引。Hash索引把数据以hash形式组织起来，因此当查找某一条记录的时候，速度非常快。但是因为hash结构，每个键只对应一个值，而且是散列的方式分布。所以它并不支持范围查找和排序等功能。

B树和B+树的区别

B树（B-tree）和B+树（B-plus tree）都是树状数据结构，通常用于在数据库和文件系统中实现对有序数据的高效操作。它们的主要区别在于节点的设计和数据存储方式。

B树（B-tree）:

  1. 节点结构： B树的每个节点都包含键值和对应的子节点，同时也可能包含数据。所有节点都参与查找，包括非叶子节点。

  2. 键值： 非叶子节点上的键值对应到其子树的范围。

  3. 查找路径： B树的查找路径从根节点到叶子节点，中途可能包含非叶子节点。

  4. 范围查询： B树相对较适合范围查询，因为每个节点都包含数据。

B+树（B-plus tree）:

  1. 节点结构： B+树的非叶子节点仅包含键值，而叶子节点包含键值和对应的数据。数据仅存储在叶子节点上。

  2. 叶子节点： 所有叶子节点通过指针连接形成有序链表。

  3. 查找路径： B+树的查找路径仅从根节点到叶子节点，中途不包含非叶子节点。

  4. 范围查询： B+树非常适合范围查询，因为有序链表便于进行范围查询。

区别总结:

  • B树的非叶子节点包含键值和数据，而B+树的非叶子节点仅包含键值。

  • B树的查找路径可能包含非叶子节点，而B+树的查找路径仅从根节点到叶子节点。

  • B树相对更适合点查询，而B+树更适合范围查询。

  • B+树的有序链表结构使得范围查询非常高效。

  • B+树的内部节点不存储数据，这有助于减少树的高度，提高查询效率。

总体而言，B+树在数据库和文件系统中更为常见，特别适合需要范围查询和高效顺序访问的应用。

B+树的叶子节点都可以存哪些东西

可能存储的是整行数据，也有可能是主键的值。B+树的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引。

什么是聚簇索引

总结下，聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。当表中有聚簇索引时，它的数据行实际上存放在索引的叶子页中。

什么是覆盖索引

  • 一个查询语句的执行只用从索引中就能够取得，不必回表读取。也可以称之为实现了索引覆盖。

为什么不推荐使用select *

  • 增加了不必要的网络开销。在查询巨量时，数据传输是十分耗时的。

  • 我们在上面已经提到了覆盖索引。显然使用select *导致无法使用覆盖索引。

什么是最左匹配原则

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时**遇到范围查询(>、<、between、like)**就会停止匹配。最左匹配原则都是针对联合索引来说的。

例如：b = 2 如果建立(a,b)顺序的索引，是匹配不到(a,b)索引的；但是如果查询条件是a = 1 and b = 2或者a=1(又或者是b = 2 and b = 1)就可以，因为优化器会自动调整a,b的顺序。再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。

我们都知道索引的底层是一颗B+树，那么联合索引当然还是一颗B+树，只不过联合索引的健值数量不是一个，而是多个。构建一颗B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建B+树。 例子：假如创建一个（a,b)的联合索引，那么它的索引树是这样的

同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

注意： 但是这不意味着不按照索引顺序写的查询条件就会100%导致不走索引。MySQL的性能优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。

查询在什么时候不走（预期中的）索引

  • 模糊查询 %like

  • 索引列参与计算,使用了函数

  • 非最左匹配

  • where对null判断

  • where不等于

  • or操作有至少一个字段没有索引

  • 需要回表的查询结果集过大（超过配置的范围）

MySQL 5.6中，对索引做了哪些优化

Index Condition Pushdown**（索引下推）**

people表中（zipcode，lastname，firstname）构成一个索引

SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';

如果没有使用索引下推技术，则MySQL会通过zipcode='95054'从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。

如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054'的索引，然后根据lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接reject掉。有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

MySQL 中 SET和ENUM 的用法是什么？

  1. Set类型：集合，列可赋予多个集合成员。其值来自创建表时规定的允许的串集中选择，可包括串集中任意或所有成员，每个值成员之间以逗号隔开。

  2. Enum类型：枚举，列可赋予某个枚举成员。其值来自创建表时规定的允许的枚举中选择，只能选择其中一个。

SQL 语法如下：

CREATE TABLE `test` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `myset` set('a','b','c') DEFAULT '',
  `myenum` enum('a','b','c') DEFAULT NULL,
  PRIMARY KEY (`Id`)
)
插入Set和Enum
INSERT INTO `test` VALUES (1,'a,b','a');
INSERT INTO `test` VALUES (2,'b,a,b','b');

CHAR 和 VARCHAR 的区别？

CHAR 和 VARCHAR 类型在存储和检索方面有所不同。

CHAR 列长度固定为创建表时声明的长度，长度值范围是 1 到 255。

当 CHAR 值被存储时，它们被用空格填充到特定长度，检索 CHAR 值时需删除尾随空格。

上一页mysql下一页002-mysql-02

最后更新于