1.sql的执行过程 (1,2)

查询语句的执行过程：

  • 连接器：用户登录校验，有长连接和短连接两种，长连接指连接成功后客户端有持续的请求，短连接则是连接后有较少的几次查询就断开，一般长连接默认连接时长是8小时，但如果长连接太多导致占用内存太大会被系统强行杀掉会导致MySQL异常重启。可以在使用长连接的程序里使用过后断开连接，也可以通过mysql_reset_connection命令重新初始化连接资源。
  • 查询缓存：数据结构为key：value形式。不建议开启查询缓存，然后通过SQL_CACHE指定开启某条查询语句的缓存即可select SQL_CACHE * from T where ID=10；
  • 分析器：分析你的语句语法是否有问题。
  • 优化器：优化器会帮你优化语句，决定使用哪个索引以及表连接的顺序，当然也有可能会有负优化的情况（这个后面讲索引时再细讲）。
  • 执行器：执行语句前会先判断一下对该表有没有查询权限。

更新语句的执行过程：

  • 首先跟查询语句的执行一样需要使用到连接器进行连接，前面说到的不建议使用查询缓存的原因是在做更新操作的时候被操作的表的缓存会被清空。然后是分析器进行分析，优化器决定是否优化使用索引，然后执行器进行执行。
  • 更新操作的流程中会涉及到两个重要的日志模块，这里简单的讲解一下：
  • 一个是redo log(重做日志)，一个是bin log(归档日志)，MySQL大体分为Server层和引擎层而在最开始没有InnoDB的时候MySQL只有bin log，redo log是InnoDB特有的。redo log是物理日志，记录的是你更新操作的结果，它是有固定的空间给你循环写。bin log是逻辑日志，记录的是你的执行语句，它是追加写入到日志文件，不会覆盖之前的记录。
  • 简单讲下redo log的一些操作细节，它可以配置一块固定大小的空间，然后每当有更新操作的时候会先将更新结果存到这块空间里并更新到内存，等这块空间被写满了或系统比较空闲时就便会将空间里的数据同步到硬盘上，然后从头又继续在这块空间上写，这样循环写。
  • bin log的操作是执行器先查询当前要更新的数据页是否在内存中，是直接返回给执行器，否则则需要从磁盘读到内存中再返回，执行器拿到数据后进行更新操作，再调用引擎接口写入新数据。引擎将新数据更新到内存中，并将这个更新操作记录更新到redo log里，这时候redo log为预备(prepare)状态，执行器生成bin log并写入磁盘，最后执行器调用引擎的提交事务接口，将刚才写入的redo log修改成提交(commit)状态。
  • 在这里还有个叫两阶段提交的东西，在前面我们看到在操作bin log的时候，它是会去操作两次redo log的，第一次是将更新的操作记录更新到redo log中并将redo log的状态修改为prepare状态，第二次是在bin log写入到磁盘后将redo log的状态修改为commit状态。这个两阶段提交是为了保证两个日志模块的记录是同步的。

2.事务以及锁 (3,6,7,8）

事务隔离：

  1. 其中事务的隔离性就是下面我们要讲的，事务的隔离性呢被分为几个隔离级别从低到高分别为：读未提交、读提交、可重复读、串行化。下面简单讲解下这几个隔离级别
  2. 读未提交：执行当前事务时能读到其他还未提交的事务的数据
  3. 读提交：执行当前事务时只能读到其他已提交的事务的数据
  4. 可重复读：执行当前事务时，只会读到该事务启动时的数据状态，并不会受到其他已提交事务的影响。
  5. 串行化：每个事务要按先后顺序一个一个排队进行执行。
  6. 简单讲下事务隔离的实现：实际上每次更新操作的时候同时会记录一条回滚操作，用于回滚使用，当你有多次更新操作时，也会记录下多条回滚记录，如果你想回滚到更新操作之前的数据就需要依次执行刚刚记录下的回滚记录，其实这当中的多个数据版本其实也就是数据库的多版本并发控制。
  7. 事务的四大特性：ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）相信大家都很熟练吧。

可重复读隔离级别与读提交隔离级别的区别：

  1. 在MySQL里，有两个“视图”的概念：
  2. 一个是view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是create view … ，而它的查询方法与表一样。
    另一个是InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持RC（Read Committed，读提交）和RR（Repeatable Read，可重复读）隔离级别的实现。
  3. InnoDB引擎的行数据有多个版本，每个版本都有自己的row trx_id(事务id)，通过事务id和一致性视图确认数据版本的可见性。
  4. 对于可重复读，查询只承认在事务启动前就已经提交完成的数据；
  5. 对于读提交，查询只承认在语句启动前就已经提交完成的数据；

全局锁、表锁、元数据锁(MDL)

  1. 全局锁顾名思义就是让整个数据库处于只读状态，在全库备份的时候就可以使用到全局锁。当然还有其他的可以实现：
  2. 例如使用由MVCC所支持的single-transaction来确保拿到一致性视图，该方式需要有事务的引擎的支持才可以，如InnoDB。
  3. 通过用set global readonly = true的方式让全库也处于只读状态，但该方式的readonly的值可能会被用于其他的逻辑，还有就是在异常处理机制上该方式在发生异常后还是会一直保持只读状态，这会导致整个库一直处于只读。
  4. 表级锁有两种：一种是表锁，一种是元数据锁（MDL），前一种需要显式使用，后一种则在访问表的时候会自动加上，并且要等事务提交后才会释放锁。
  5. 在使用MDL锁的时候建议将会互斥的读写锁、写锁尽量放到事务的最后面来操作，避免因事务过长而阻塞了其他业务操作。

行锁、死锁

  1. 行锁是由引擎层实现的，而InnoDB引擎便支持行锁，行锁相对于全局锁和表级锁是一种颗粒度更小的锁，顾名思义就是只针对表中的一行记录的锁。在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
  2. 什么是死锁？
    例如：当前有两个事务（A事务、B事务）要执行，这两个事务要修改相同的两条数据（A数据、B数据），当A事务开始执行时先对A数据进行修改然后再修改B数据，当A事务执行完对A数据的修改后，这时候B事务开始对B数据进行操作，然后再对A数据进行操作，这时候A事务在操作完A数据后需要等待B事务的提交才能继续对B数据进行操作，而B事务则需要等待A事务的提交才能继续对A数据的操作，从而产生了死锁。
  3. 死锁的处理：第一种是设置超时时间，超过该时间则退出。第二种是开启死锁检测，当出现死锁后会回滚死锁链条中的某一个事务，但这种如果并发的线程量较多的时候检测所消耗的性能较高，时间复杂度为 O(n)

3.索引(4,5,9,10,11)

索引模型、主键索引、索引维护

  1. 三种索引模型：哈希表、有序数组、搜索树
  2. 哈希表索引模型的更新速度比较快，但由于是无序的，所以查询速度比较慢。
  3. 有序数组索引模型的话因为他是有序的，所以可以通过二分法进行快速的查询，但由于更新数据时需要将后面的数据往后挪动，成本比较高。
  4. 二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。
  5. 在InnoDB中索引模型使用的是B+树，相较于二叉树来说B+树的叶子节点可以存放的数据量较大，树层的数量会少很多，有利于提高查询效率。
  6. 在InnoDB中，每一个索引都是一颗索引树。下面讲下主键索引和普通索引：
  7. 主键索引也被称为聚簇索引，主键索引中包含了整行的数据信息。
  8. 普通索引也被称为二级索引，当你通过二级索引去查找数据时如果普通索引树上没有你要的数据时，该操作需要再去搜索主键索引树（也被称为回表），而通过主键索引进行查询的话则不用回表。

索引的维护：

B+树在维护索引的有序性的时候，如果当前数据页已经满了的话，则需要申请一个新的数据也，然后挪动部分数据过去，该过程被称为页分裂，当然如果你是删除数据的话也有逆过程，那就是页合并。数据页的分裂和合并会对性能造成影响。当然还有索引的大小也会影响到性能，因为非主键索引的叶子节点上都是主键的值，所以主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

索引优化建议：

  1. 在使用索引时尽量使用被索引覆盖的这样可以减少回表的次数。再之尽量匹配最左前缀原则，在MYSQL5.6之后引入的索引下推也是为了减少回表次数。

普通索引与唯一索引的区别

  1. 查询时候：
    普通索查到满足条件的数据后还需要碰撞到一个不满足条件的记录后才会停止检索。
    唯一索引则查到到满足条件的数据后就停止了检索。

  2. 更新时候：
    普通索引中会用到change buffer，下面说说的好处，以及局限性

  3. 好处：change buffer可以减少merge过程的触发，从而减少随机访问IO的次数。并且change buffer会通过redo log以顺序写的方式持久化到硬盘中。

  4. 局限性：唯一索引不能用change buffer，而普通索引可以，但是需要看使用场景，如果是在更新操作后就需要立刻进行查询则不适用，如果是那种读多写少的则就能体验出change buffer的价值。

MySQL也会选错索引

  1. 前面有聊到MySQL中是优化器在帮你选择最优的索引，但优化器也存在选错索引的可能性。由于索引统计信息是采样统计得来的只能统计出大概且索引更新机制有存在同步不及时的情况，也可能是由于排序为索引字段而优化器认为使用该索引可以不需要做排序，而导致的选错索引，当然还有其他情况也会导致选错索引。
  2. 我们可以设置慢查询日志的阈值，当有查询语句的查询时间超过了该阈值则会被记录到慢查询日志中，从而可以去优化具体的问题所在。
  3. 对于由于索引统计信息不准确导致的问题，你可以用analyze table来解决。
  4. 而对于优化器误判的情况，可以强制使用指定索引（force index），也可以修改执行语句来引导优化器，还可以通过修改或删除表的索引来绕过这个问题。

字符串字段创建索引的几种方式

  1. 直接创建完整索引，这样可能比较占用空间；
  2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
  3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
  4. 创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。