- # MySQL 聚簇索引，非聚簇索引
- # MySQL 聚集索引，非聚集索引
- # MySQL 索引及底层实现
- # MySQL 索引B树和B+树是解决什么样的问题的，怎样演化过来，之间区别？
- # MySQL 是怎么存储的，树的深度，和存储的page，一个节点存了多少数据，怎么规定大小，与磁盘页对应？
- # MySQL 加索引方式
- # MySQL 的联合索引
- # 最左原则
- # 索引失效的场景
- # 如何查看执行计划，
explain sql都看什么？ - # 锁的类型
- # Next-Key Lock
- # 悲观锁/乐观锁
- # redo log，undo log，bin log主从复制
- # MySQL 事务是什么？
- # 事务的特性是什么？
- # 事务的实现原理是什么？
- # 脏写、脏读、不可重复读、幻读分别是什么？
- # MySQL 的隔离级别及实现
- # MVCC是什么？
- # MySQL 怎样防止幻读？
- # MySQL 根据possible_keys怎么判断使用哪个索引key？
- # 使用innodb引擎，数据表: t_name, 字段: a,b,c,d，SQL1:
select id from t_name where a=1 and b=2;，SQL2:select c from t_name where a=1 and b=2 and d=4;，两个 sql 如何创建索引？是否需要回表？ - # MySQL 中控制内存分配的参数有哪些？
- # 数据库异步/全同步/半同步复制
- # 分库分表
- 聚簇索引：聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引，修改聚簇索引其实就是修改主键；因为无法同时把数据行放在两个不同的地方，所以一个表只能有一个聚族索引；
- 非聚簇索引：索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询，非聚簇索引也叫做辅助索引；
- 聚集索引：索引中键值的逻辑顺序决定了表中相应行的物理顺序（索引中的数据物理存放地址和索引的顺序是一致的）；
- 非聚集索引：索引的逻辑顺序与磁盘上的物理存储顺序不同。非聚集索引的键值在逻辑上也是连续的，但是表中的数据在存储介质上的物理顺序是不一致的，即记录的逻辑顺序和实际存储的物理顺序没有任何联系。索引的记录节点有一个数据指针指向真正的数据存储位置。
索引（Index）是帮助数据库高效获取数据的数据结构。索引是在基于数据库表创建的，它包含一个表中某些列的值以及记录对应的地址，并且把这些值存储在一个数据结构中。 最常见的就是使用哈希表、B+树作为索引。
如果用二叉树作为索引的实现结构，会让树变得很高，增加硬盘的I/O次数，影响数据查询的时间。如果二叉树发生了不平衡的情况会导致树的高度更大，查询效率更低。如果允许树有多个分叉即一个节点有多个节点的话，同时保证树是平衡的，那么树的高度就可以变得很低。 B树的出现就是为了解决这个问题，B树的英文是Balance Tree，也就是平衡的多路搜索树，它的高度远小于平衡二叉树的高度。在文件系统和数据库系统中的索引结构经常采用B树来实现。
一个M阶的B树（M>2）有以下的特性：
- 根节点的儿子数的范围是[2,M]。
- 每个中间节点包含k-1个关键字和k个孩子，孩子的数量=关键字的数量+1，k的取值范围为[ceil(M/2), M]。
- 叶子节点包括k-1个关键字（叶子节点没有孩子），k的取值范围为[ceil(M/2), M]。
- 假设中间节点节点的关键字为：Key[1], Key[2], …, Key[k-1]，且关键字按照升序排序，即Key[i]<="" li="">
- 所有叶子节点位于同一层。
B+树基于B树做出了改进，B+树和B树的差异在于以下几点：
- 有 k 个孩子的节点就有k个关键字。也就是孩子数量=关键字数，而B树中，孩子数量=关键字数+1。
- 非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最小）。
- 非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而B树中，非叶子节点既保存索引，也保存数据记录。
- 所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。
B+树的优点：
- B+树查询效率更稳定。因为B+树每次只有访问到叶子节点才能找到对应的数据，而在B树中，非叶子节点也会存储数据，这样就会造成查询效率不稳定的情况，有时候访问到了非叶子节点就可以找到关键字，而有时需要访问到叶子节点才能找到关键字。
- B+树的查询效率更高，这是因为通常B+树比B树更矮胖（阶数更大，深度更低），查询所需要的磁盘I/O也会更少。同样的磁盘页大小，B+树可以存储更多的节点关键字。
- 不仅是对单个关键字的查询上，在查询范围上，B+树的效率也比B树高。这是因为所有关键字都出现在B+树的叶子节点中，并通过有序链表进行了链接。而在B树中则需要通过中序遍历才能完成查询范围的查找，效率要低很多。
记录是按照行来存储的，但是数据库的读取并不以行为单位，否则一次读取（也就是一次I/O操作）只能处理一行数据，效率会非常低。因此在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页（Page）。
- 页（Page）中可以存储多个行记录（Row），InnoDB中的页大小默认是16KB；
- 区（Extent）是比页大一级的存储结构，在InnoDB存储引擎中，一个区会分配64个连续的页。因为InnoDB中的页大小默认是16KB，所以一个区的大小是64*16KB=1MB。
- 段（Segment）由一个或多个区组成，区在文件系统是一个连续分配的空间（在InnoDB中是连续的64个页），不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。
- 表空间（Tablespace）是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。
MySQL的InnoDB存储引擎采用B+树作为索引，而索引又可以分成聚集索引和非聚集索引（二级索引），这些索引都相当于一棵B+树，一棵B+树按照节点类型可以分成两部分：
- 叶子节点，B+树最底层的节点，节点的高度为0，存储行记录。
- 非叶子节点，节点的高度大于0，存储索引键和页面指针，并不存储行记录本身。
在一棵B+树中，每个节点都是一个页，每次新建节点的时候，就会申请一个页空间。同一层上的节点之间，通过页的结构构成一个双向的链表（页文件头中的两个指针字段）。非叶子节点，包括了多个索引行，每个索引行里存储索引键和指向下一层页面的页面指针。最后是叶子节点，它存储了关键字和行记录，在节点内部（也就是页结构的内部）记录之间是一个单向的链表，但是对记录进行查找，则可以通过页目录采用二分查找的方式来进行。 如果页大小是16KB，那么平均每条数据的大小不能超过8KB，因为超过8KB一页只能存储一条数据，那么B+树就会退化成链表。 MySQL每个B+树节点最大存储容量：16KB （指针+数据+索引）。假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14=1170个(主键+指针)
ALTER TABLE \table_name` ADD INDEX ( `column` )`
两个或更多个列上的索引被称作联合索引，联合索引又叫复合索引。 对于复合索引：MySQL 从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。
最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
- or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效;
- 复合索引未用左列字段，即不是使用第一列索引，索引失效;
- like以%开头，当like前缀没有%，后缀有%时，索引有效;
- 需要类型转换;
- where中索引列有运算，或者索引列使用了函数;
- where中在索引字段上使用not，<>，!=。（不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。key<>0 改为 key>0 or key<0。）
- 如果mysql觉得全表扫描更快时（数据少）;
- 在索引列上使用 IS NULL 或 IS NOT NULL操作。（索引是不索引空值的，所以这样的操作不能使用索引）
使用explain <sql语句> 查看执行计划。
- select_type：SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT
- type：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
- key：实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）；查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中
在MySQL中，行级锁并不是直接锁记录，而是锁索引。 只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。
- 行级锁：行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。
- 表级锁：表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
- 页级锁：页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁。
- 记录锁(Record Locks) 所谓记录，就是指聚簇索引中真实存放的数据；
- 间隙锁(Gap Locks) 间隙指的是两个记录之间逻辑上尚未填入数据的部分；
- 临键锁(Next-Key Locks) 临键指的是间隙加上它右边的记录组成的左开右闭区间；
- 乐观锁（Optimistic Locking）认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序上，我们可以采用版本号机制或者时间戳机制实现。
- 悲观锁（Pessimistic Locking）也是一种思想，对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性。
- bin log：用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。 binlog 是 mysql 的逻辑日志，并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。
- redo log：为了保证持久性，即只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态。但是1. 因为 Innodb 是以 页 为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！2. 一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！因此 MySQL 设计了 redo log ， 具体来说就是只记录事务对数据页做了哪些修改。redo log 包括两部分：一个是内存中的日志缓冲( redo log buffer )，另一个是磁盘上的日志文件( redo log file )。 mysql 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file 。
- Undo log：数据库事务四大特性中有一个是 原子性 ，具体来说就是原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。实际上， 原子性底层就是通过 undo log 实现的。 undo log 主要记录了数据的逻辑变化，比如一条 INSERT 语句，对应一条 DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。同时， undo log 也是 MVCC 实现的关键
事务是数据库管理系统(DBMS)执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。
- 原子性：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作。
- 一致性：数据库总是从一个一致性状态转换到另一个一致状态。
- 隔离性：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。
- 持久性：一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。
- 原子性：通过 undo log 来实现的；
- 一致性：通过原子性，持久性，隔离性来实现的；
- 隔离性：通过 读写锁+MVCC 来实现的；
- 持久性：通过 redo log 来实现的；
- 脏写：指一个事务修改了其它事务未提交的数据；
- 脏读：指一个事务读到了其它事务未提交的数据；
- 不可重复读：指的是在一个事务执行过程中，读取到其它事务已提交的数据，导致两次读取的结果不一致；
- 幻读：指的是在一个事务执行过程中，读取到了其他事务新插入数据，导致两次读取的结果不一致；
- READ UNCOMMITED (未提交读)：脏读、不可重复读、幻读均可能出现；事务对当前被读取的数据不加锁，都是当前读；事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级共享锁，直到事务结束才释放。
- READ COMMITED (提交读)：不可重复读、幻读均可能出现；事务对当前被读取的数据不加锁，且是快照读；事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁（Record），直到事务结束才释放。通过快照，在这个级别MySQL就解决了不可重复读的问题。
- REPEATABLE READ (可重复读)：在innoDB下解决了所有问题；事务对当前被读取的数据不加锁，且是快照读；事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加排他锁（Next-Key），直到事务结束才释放。通过间隙锁，在这个级别MySQL就解决了幻读的问题。
- SERIALIZABLE (可重复读)：解决了所有问题；事务在读取数据时，必须先对其加表级共享锁 ，直到事务结束才释放；事务在更新数据时，必须先对其加表级排他锁 ，直到事务结束才释放。
MVCC的英文全称是Multiversion Concurrency Control，中文翻译过来就是多版本并发控制技术。从名字中也能看出来，MVCC是通过数据行的多个版本管理来实现数据库的并发控制，简单来说它的思想就是保存数据的历史版本。这样我们就可以通过比较版本号决定数据是否显示出来，读取数据的时候不需要加锁也可以保证事务的隔离效果。
MVCC可以解决以下问题：
- 读写之间阻塞的问题，通过MVCC可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力。
- 降低了死锁的概率。这是因为MVCC采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行。
- 解决一致性读的问题。一致性读也被称为快照读，当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果。
Next-Key Lock
possible_keys指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。
# 使用innodb引擎，数据表: t_name, 字段: a,b,c,d，SQL1: select id from t_name where a=1 and b=2;，SQL2: select c from t_name where a=1 and b=2 and d=4;，两个 sql 如何创建索引？是否需要回表？
ALERT TABLE t_name ADD INDEX (\a`,`b`,`d`)`
SQL1不需要回表，SQL2需要回表。
- 线程独享的内存
- join_buffer_size：用于普通索引扫描、范围索引扫描和不使用索引因而执行全表扫描的联接的缓冲区的最小大小。当使用批处理密钥访问时，join_buffer_size的值定义了向存储引擎发出的每个请求中的批处理密钥的大小；
- sort_buffer_size：每个必须执行排序的会话都会分配一个这种大小的缓冲区；
- read_buffer_size：对MyISAM表进行顺序扫描的每个线程为其扫描的每个表分配一个这种大小(以字节为单位)的缓冲区；
- tmp_table_size：内存中内部临时表的最大大小。mysql临时表分为两种，一种是使用create temporary table创建的，称为为外部临时表，一种是因union、order by、group by、distinct等语句产生的，称为内部临时表；
- max_heap_table_size：此变量设置允许用户创建的内存表增长的最大大小；
- 全局共享的内存
- key_buffer_size：MyISAM表的索引块被缓冲并由所有线程共享。
- query_cache_size：为缓存查询结果而分配的内存量
- thread_cache_size：MySQL里面为了提高客户端请求创建连接过程的性能,提供了一个连接池也就是 Thread_Cache池,将空闲的连接线程放在连接池中,而不是立即销毁.这样的好处就是,当又有一个新的请求的时候,mysql不会立即去创建连接 线程,而是先去Thread_Cache中去查找空闲的连接线程,如果存在则直接使用,不存在才创建新的连接线程.
- table_open_cache：为所有线程打开的表的数量。
- binlog_cache_size：在事务处理期间，缓存保存对二进制日志的更改的大小
- innodb_log_buffer_size：InnoDB用来写入磁盘上的日志文件的缓冲区的字节大小。
- innodb_buffer_pool_size：缓冲池的字节大小，InnoDB缓存表和索引数据的内存区域。默认值是134217728字节(128MB)
- 异步复制：MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从库上，如果此时，强行将从提升为主，可能导致新主上的数据不完整；
- 全同步复制：当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响；
- 半同步复制：介于全同步复制与全异步复制之间的一种，主库只需要等待至少一个从库节点收到并且 Flush Binlog 到 Relay Log 文件即可，主库不需要等待所有从库给主库反馈。同时，这里只是一个收到的反馈，而不是已经完全完成并且提交的反馈，如此，节省了很多时间；
- 垂直分表：表中的字段较多，一般将不常用的、 数据较大、长度较长的拆分到“扩展表“。一般情况加表的字段可能有几百列，此时是按照字段进行数竖直切。注意垂直分是列多的情况；
- 水平分表：单表的数据量太大。按照某种规则（RANGE,HASH取模等），切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。这种情况是不建议使用的，因为数据量是逐渐增加的，当数据量增加到一定的程度还需要再进行切分。比较麻烦；
- 垂直分库：一个数据库的表太多。此时就会按照一定业务逻辑进行垂直切，比如用户相关的表放在一个数据库里，订单相关的表放在一个数据库里。注意此时不同的数据库应该存放在不同的服务器上，此时磁盘空间、内存、TPS等等都会得到解决；
- 水平分库：水平分库理论上切分起来是比较麻烦的，它是指将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈；