今日分享开始啦，请大家多多指教~

mysql索引结构：

mysql索引使用B+tree，为什么使用B+tree呢，首先，使用索引是为了加快查找的速度，B+tree的查找时间复杂度为log(n).那为什么不用o(1)的hashMap呢。mysql是有使用hashMap结构的hash索引的，但大部分情况下，我们使用的索引并不是hash索引，主要是hash索引这种结果在处理 !=, > ，< 这种范围查询时，需要全表扫描，时间复杂度为o(n)。

为什么不使用B tree？ BTree和B+Tree的区别是，B+Tree的非叶子结点只保存索引，不保存数据，这样一个节点保存的数据更多，树的高度更低，在读取索引时，可以省IO(其实，这里降低树高度基本没啥用，因为往往我们的Tree的每个节点的度都很大，BTree和B+Tree高度基本差不多)。

另一个主要的作用是，由于数据节点都在叶子结点上，而每个叶子结点又使用双向链表链接，这样，在处理范围查询时，只需要查定一个下界，然后在叶子结点上遍历即可，且天然有序。

主键索引和二级索引

以Innodb为例：主键索引是和数据文件放在一块的，即数据文件在叶子结点上。对于主键索引，找到了主键索引，主键索引对应的value即为数据row。而非主键索引被称为二级索引的原因是，非主键索引的value存放的是主键的值，我们在使用非主键索引查找时，需要先根据索引找到主键，然后根据主键去找数据row。

根据主键再去找数据row的过程称为回表。因为对于这样的索引，不直接和数据关联，所以称为二级索引。对于二级索引，如果我们查找的列，已经全部在索引列里了，这时候就不需要回表了，这种索引成为覆盖(covered)索引。

主键索引也叫聚簇索引，二级索引也叫非聚簇索引。

explain索引优化

当我们想对一个sql语句进行优化时，可以用explain查看当前sql的执行计划。对于explain的输出，几个重要的如下。

Type：

• const： 用主键匹配
• eq_ref: 两个表join的时候，join的key是两个表的主键。这时候，对于前一个表的每一行，后一张表只需要扫描一行 One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.
• ref: 通过非唯一索引扫描，通常不需要进行排序时，只要通过ref或者最左前缀匹配就可以了。
• range: range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators.
• index： 使用索引，但比如在扫描之后 还需要order by. 这时候，需要扫描整个索引树。
• all： 全表扫。
• 通常情况下，我们优化的目标到ref就可以了。

extra：

• use index： 仅需要使用索引，不需要回表。
• use where： 通过where子句过滤，where子句过滤存储引擎返回的结果。
• use filesort 需要使用排序。

建立索引和索引匹配的原则

对于联合索引，比如(row1,row2,row3)这种，mysql按照最左前缀匹配的时候，相当于给我们建了(row1),(row1,row2),(row1,row2,row3)三个索引。因此当有联合索引的时候，不再需要单独建立额外的单列索引。

数据查询时，where自己后面的顺序无所谓，mysql会自动帮你优化。

where 后面在使用or 查询的时候，大部分情况下不会走到索引。所以，对于这种查询，可以使用union来优化In many cases, MySQL won't be able to use an index to apply an OR condition, and as a result, this query is not index-able.Therefore, we recommend to avoid such OR conditions and consider splitting the query to two parts, combined with a UNION DISTINCT (or even better, UNION ALL, in case you know there won't be any duplicate results）

建索引时，范围字段放在联合索引的最后，因为按照最左前缀匹配原则，碰到范围字段就终止匹配了，后面的字段不会去匹配。

区分度大的字段在建索引时放在前面。 区分度公式：count(distinct col)/count(*)，就是一个字段当选择了一个值时，要能过滤掉大部分字段。

mysql NULL

• NULL is not data type
• NULL is a value place holder for optional table fields.
• MySQL treats the NULL value differently from other data types. The NULL values when used in a condition evaluates to the false Boolean value.
• Performing arithmetic operations on NULL values always returns NULL results.
• The comparison operators such as [, =, etc.] cannot be used to compare NULL values.
• ‘+ - * / = != ’这些操作在作用于NULL时，永远返回NULL，在返回NULL做条件判断时返回false。
• 对于NULL的判断，使用is NULL 和is not NULL来判断，使用= ， !=,都不会得到你想要的结果。
• 建表时，尽量所有字段都设置为非NULL，设为NULL时，mysql还需要额外使用字段来标记是否为NULL。

mysql架构

锁

为了解并发问题，引入锁，mysql中锁分为读锁和写锁，即share lock和exclusive lock。顾名思义，share lock之间不互斥，share lock和exclusive lock之间互斥，exclusive lock之间互斥。mysql 提供行锁row lock和表锁 table lock的multiple granularity locking。

对于表锁，mysql提供一种意图锁的机制，意图锁也是分为两种，intention share lock和intention exclusive lock。对于intention lock

• Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.
• Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.
• Intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.
• intention lock之间并不互斥，intention lock只是告诉你有人对表中的某些行在上锁。

mysql row lock是在存储引擎层实现的，不同的存储引擎可能有不同的实现方式。

事务

事务是指一批操作，要么全部成功，要么全部失败。

数据库事务的ACID特性

• atomicity原子性：即一个事务已一个原子的操作执行，是一个不可分隔的最小单元，事务中的操作，要么全部执行成功，要么全部失败。
• consistency 一致性：数据库总是从一个一致的状态转移到另一个一致的状态
• isolation: 隔离性：一个事务中的修改，在什么时候对另一个事务可见
• durability: 持久性： 提交的事务不会丢失

隔离级别

隔离级别是对不同的事务而言的。

• read uncommitted：一个事务中未提交的修改也对另外的事务可见，在这里隔离级别下，会出现脏读，即事务1未提交的修改可能被别的事务可见。
• read committed： 一个事务提交commit后的修改才对另一个事务可见。但是可能会出现不可重复读的问题，即在一个事务1中，连续select两次，得到的结果不同，因为在这中间，可能记录被别的事务修改了。
• repeatable read：一个事务中，多次select的结果总是相同的，但可能出现幻读的情况，即虽然对于同一行的结果，始终是相同的，但可能别的事务在insert别的行，导致一个事务中间看到的记录是不同的。
• serializable：事务串行执行。

怎么解决不可重复读问题？在一个事务开始时，对涉及到的row加上行锁即可以保证另一个事务无法修改这一行。但是这解不了幻读的问题，因为别的事务可能insert的是别的行。这时候，需要引入gap lock。不仅锁这一个row，还锁这个row的前后间隙。

具体怎么锁，根据查询条件是走唯一索引还是非唯一索引，是走等值匹配还是范围匹配有不同的gap lock lock的范围，但一个原则就是：保证你这个语句的查询范围内的数据不会被其他事务insert进去。

事务的两段锁：

在事务的执行过程中，随时可以进行锁定，但只有事务执行完毕commit或者rollBack的时候，才会释放锁。

MVCC 多版本并发控制

前面说锁分为读锁和写锁，这是一种悲观锁，MVCC是一种乐观锁，通过版本号控制，读副本的方式，来使得select读不用加锁，每次都读副本，同时保证读到的都是事务开始之前写入的数据。

因为大多数数据库操作都是读多写少的，通过MVCC，读操作不用加锁，减少了锁冲突的概率，提高吞吐。select和事务又有啥关系呢？对于mysql，默认是auto-commit模式，如果不显示地开启一个事务，每个查询都被当作一个事务来执行。

mvcc怎么实现的

• 每条记录后面增加两个version，创建version和删除version。
• 对于select语句，只筛选那些创建version小于等于事务version（保证查询到的记录在当前事务开始之前就已经存在了），且删除version在当前version之后的（保证记录在当前事务开始的时候，未被删除）。
• insert 语句：插入新一行，创建version等于当前事务version。
• update语句：新插入一行，创建version等于当前事务version，之前行的删除version设置为当前事务version。
• delete语句： 当前行的删除version设置为当前事务version。

快照读和当前读

select语句读的是快照，通过读快照，在RR级别也不会有幻读，对于select for update这种当前读，通过next-key lock解决幻读问题。

今日份分享已结束，请大家多多包涵和指点！