MySql 索引

• InnoDB存储引擎下支持：B+树索引，全文索引，哈希索引

• B+树索引分为聚簇索引 和 非聚簇索引

聚簇索引 / 聚集索引

• InnoDB使用了聚簇索引，就是将表的主键构建一颗B+树， 并且将整张表的行记录数据放在B+树的叶子节点上，这也就是所谓的索引即数据，数据即索引。每一张表只能有一个主键，因此也只能有一个聚簇索引
• 聚簇索引优点：
  • 能通过聚簇索引直接获取完整的整行数据
  • 对于主键的排序查找，范围查找非常快
• 当没有定义主键的时候，MySql会使用唯一性索引作为构建B+树，如果两者都没有 MySql也会创建一个隐含的列作为主键，利用这个主键来建立聚簇索引。

辅助索引/非聚簇索引

• 聚簇索引只能搜索的条件是主键的时候才用处大。因为B+树中数据都是按照主键进行排序的。
• 对于其他列数据，一般建立多个非聚簇索引，每建立一个非聚簇索引都是一颗B+树
• 非聚簇索引的叶子节点上不包含行记录的全部数据。叶子结点除了包括该索引定义的几个键值以外，每个叶子结点中还包含了一个书签（bookmark）。该书签用来高速InnoDB存储引擎哪里可以找到这个索引相对于的行数据，因此InnoDB存储引擎的非聚簇索引存储的书签就是对应行数据的聚簇索引的索引键，也就是对应表主键信息。

回表查询

• 当通过非聚簇索引 查询数据时候，InnoDB先遍历非聚簇索引 通过叶子结点指针获取只想主键索引的主键，然后通过聚簇索引来找到一个完整的行记录。这个过程被称为回表。也就是根据辅助索引的值查询一条完整的记录需要用到两颗B+树（一次非聚簇索引 ，一次聚簇索引）

非聚簇索引为啥不放整个行数据

• 非聚簇索引可以键多个，如果每一个非聚簇索引 都构建一个全量数据的B+树，太占用内存
• 并且一次写操作我们必须更新所有包含数据的索引，这样性能也会降低。

联合索引

• 建立联合索引也就是多个列组合起来进行索引，一个联合索引只会建立一个B+树，例如index(node, b) 构建了两个字的的联合索引，包含两个意思
  • 先吧各个记录安note列进行排序
  • 排序时如果note相同情况下，采用b列进行排序

哈希索引

• InnoDB 支持自适应哈希索引。B+树查找次数取决于B+树高度，生产环境B+树一般3～4 层，也就是3～4次IO，当InnoDB内部检测到有一个索引经常用到，那么任务是热点数据。InnoDB内部会自己创建一个hash索引，又称为自适应哈希索引（Adaptive Hash index，AH）。创建后，下次明中这个索引直接通过hash算法推倒出数据地址。就能通过近似O(1)时间复杂度查询到数据。
• InnoDB存储引擎使用哈希函数采用除法散列方式， 冲突机制采用链表方式。
• 自适应哈希索引是InnoDB内部自身创建，DBA不能干预

高性能索引创建策略

• 索引的列类型尽量设计的最小话：在业务允许的范围内，我们尽可能用更小类型的字段，能用int 就不用bigint。因为数据类型越小，查询时候进行的比较操作越快（高速缓存存储的数据就多一点），索引占用空间少，一个数据页就可以放入更多记录减少磁盘I/O次数。加快读写速度。
• 创建索引应该选择离散性高的列： 离散性指 不重复索引值/数据总数 的比值，例如主键的离散性就是1 ，也就是每一行都不一样，在比如性别离散性差，即使用了这个索引理论上也只能筛选出一半
• 前缀索引：针对blob，text，很长的varchar 这种很长的字段，mysql不支持索引全部长度，我们可以用前缀索引
• 索引创建只为搜索，排序，或者分组的列创建，除非你要使用覆盖索引

三星索引

• 三星索引概念就是最好的索引设计
  • 索引相关的记录叶子结点在相邻位置，也就是扫描结点最少情况（25%）
  • 索引中数据顺序于查询顺序一致（25%）
  • 索引中列包含查询所有列（50%）

查询语句执行流程

• MySql包括Server层和存储引擎层
• Server层包括，连接器，查询缓存，分析器，优化器，执行器
  • 连接器：负责和客户端建立连接
  • 查询缓存：MySql获取到查询请求后，会先查询缓存，如果之前已经执行过一样的语句结果会以Key-value的形式存储到内存中，key是查询语句，value是查询结果。缓存明中的话可以很快完成查询，但是大多是情况不能明中，不建议用缓存，因为缓存失效非常频繁，任何对表的更新都会让缓存晴空，所以对一个进程更改的表而言，查询缓存基本不可用，除非是一张配置表。可以通过配置来决定释放开启查询缓存，并且MySql8.0 之间删除了查询缓存功能
  • 分析器：词法分析，识别语句中表名，列名，语法分析，判断Sql是否满足MySql语法
  • 优化器：在有多个索引的情况下，决定使用哪个索引，或者多表联合查询的时候，表的连接顺序这么执行等
  • 执行器：执行器先判断权限，有权限才会去调用存储引擎对应的查询接口，默认InnoDB

更新语句执行流程

• 更新语句同样要经过查询语句的整个过程，连接器，缓存，分析器，优化器，执行器，执行引擎这步骤。但是于查询不一样的是，更新语句需要涉及到两个重要的日志模块：redo log （重做日志），binlog（归档日志）

重要日志：redo log

• MySql对写操作的一个优化，在写操作时，不会立刻刷到磁盘中，而是利用WAL技术Write-Ahead Logging，先写日志在写磁盘
• 记录更新时候，InnoDB先将记录写入到redo log，并更新缓存，到此更新就算完成，同时InnoDB会在适当时候将缓存中的数据更新到磁盘，一般是系统空闲时候刷。
• 异常情况下，因为InnoDB中redo log固定大小，一般是4个文件，每个1G，总共4G，类似一个环形数组，从头开始写，写到末位后再覆盖写。

• 如上维护两个指针，一个write pos，表示单前写位置，check point 表示要擦除位置，当write pos 追上checkpoint时候，那么InnoDB就不得不进行一次缓存刷新
• redo log作用是在数据库异常的时候，之前一段时间的提交也可以不丢失，crash-safe

重要日志：bin log

• redo log是属于InnoDB引擎层的主要负责存储相关，bing log是MySql 中Server层的，主要做mySql功能上的事情。
• 两种日志不同：
  • redo log是InnoDB特有的，bingLog是mySql Server自己实现，所有存储引擎都可以用
  • redo log存储的是物理日志，记录的是做什么修改，binlog是逻辑日志，记录的就是这条sql
  • redo log 是循环写的，binlog是追加写，问题到一定大小会重开一个新文件写。

更新操作执行流程

• 更新操作总共三个大步骤

  • 找数据：先找内存中是否存在数据，不存在则从磁盘读入内存
  • 修改数据：拿到数据后安sql要求修改数据，得到新数据调用引擎接口写入到新行（缓存）
  • 两阶段提交写日志：
    • 引擎将数据跟新到内存，同时记录redo log，此时redo log 属于prepare阶段，返回已经执行成功可以提交事物
    • 执行器生成操作的binlog，并将binlog写磁盘
    • 执行器调用引擎提交事物接口，将redo log中prepare状态改为 提交（commit）

• 两阶段提交

  • 因为InnoDB 于mySql server 可以看出是两个系统不同操作，两阶段提交的方式保证binlog 于redolog逻辑保持一致

• 日志写磁盘

  • redo log 用于保证crash-safe，可以通过参数设置每次事物的redo log 都持久化到磁盘，这样抱着mysql异常情况数据不丢
  • binlog也可以通过配置形式设置每次事物binlog都持久化到磁盘，这样抱着异常重启后binlog不丢

MySql 事物隔离级别

• 数据库经常存在多个事物同时执行情况，不同隔离级别下执行结果可能就不同，会存在 脏读（dirty read），不可重复度（non-repeatable read），幻读（phantom read）问题。为解决这些问题才提出了“隔离级别”概念
• 隔离级别越严格，性能越差：
  • 读未提交：指一个事务还没有提交时，他做的变更就能被别的事务看到
  • 读提交：一个事务提交之后，他做的变更才能被其他事务看到
  • 可重复读：一个事务执行过程中看到的数据总是跟这个事务启动时候看到的数据是一致的
  • 串行化：：对于同一行记录，写操作会加写锁，读加读锁，当读写冲突，后访问的就等待前面执行完在执行。