MySQL大型总结，妈妈再也不用担心面试问我MySQL知识啦！

MySQL总结

索引

数据结构

• B+树索引

  • 数据量相同的情况下B+树比B树更加的”矮胖“，相应会减小IO次数、所有查询都要查找到叶子节点，查询性能稳定；所有叶子节点形成有序链表，便于范围查询。

• B树索引

  • 叶子节点和内节点都存储数据，同样大小的磁盘页可以容纳的节点元素有限制、B树的查找性能并不稳定

• hash索引

  • 查找单个数据比较快（复杂度为o1）但是不适合范围查找

• 二叉树索引

  • 一个节点只有一度，就是只有一个子节点，那读取树的一层就是一次I/O操作，I/O操作频繁并且性能欠佳、空间浪费

• 红黑树索引

  • 同二叉树一样的，I/O操作频繁、空间浪费、性能欠佳

• 有序数组

  • 有序数组在等值查询和范围查询场景中的性能都非常优秀，但是插入慢

索引类型

• 普通索引

  • 是最基本的索引，它没有任何限制

• 唯一索引

  • 索引列的值必须唯一，但允许有空值

• 主键索引

  • 特殊的唯一索引，一个表只能有一个主键，不允许有空值

• 组合索引

  • 指多个字段上创建的索引，
  • 最左前缀匹配：(key1,key2,key3)，相当于创建了（key1）、(key1,key2)和(key1,key2,key3)三个索引，这就是最左匹配原则

• 全文索引

  • 主要用来查找文本中的关键字，而不是直接与索引中的值相比较：FULLTEXT (content)

InnoDB索引

• 聚簇索引

  • 按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的记录数据

• 辅助索引

  • 非主键索引，叶子节点=键值+书签。Innodb存储引擎的书签就是相应行数据的主键索引值。

• 覆盖索引

  • select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖

    • 常见的方法是：将被查询的字段，建立到联合索引里去。

FAQ

• InnoDB

  • InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。
  • 聚簇索引查询会更快，因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值，查到主键的值以后，还需要再通过主键的值再进行一次查询，PS：非覆盖索引情况下！

• 索引下推

  • 可以在有like条件查询的情况下，减少回表次数。

• 查询优化

  • 一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。

SQL优化

索引

• 是否命中索引

  • 避免判断null值
  • 避免不等值判断
  • 避免使用or逻辑
  • 慎用in和not in
  • 避免对字段进行函数操作
  • 避免隐式转换

• 合理使用索引

  • 在差别较大的列上建立索引（比如性别 sex 就不适合做索引）
  • 合理控制索引的数量
  • 对于唯一性校验的可以使用唯一索引、不要求数据唯一的使用普通索引

明确查询目标

• 避免使用 select *
• 如果只查询一行就使用 limit 1
• 避免频繁创建和删除临时表

操作优化

• 使用操作优化

  • 尽量避免大事务操作
  • 尽量减少使用数据库锁造成死锁
  • 避免使用左右连接 用内存进行操作

• 数据结构优化

  • 尽可能的使用 varchar 代替 char
  • 尽量使用数字型字段

explain查看执行计划

• id:选择标识符

  • SQL执行的顺序的标识，SQL从大到小的执行， id如果相同，从上往下顺序执行

• select_type:表示查询的类型。

  • 示查询中每个select子句的类型

• table:输出结果集的表

  • 显示这一步所访问数据库中表名称

• partitions:匹配的分区

• type:表示表的连接类型

  • ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
  • index：与ALL区别为index类型只遍历索引树
  • range:只检索给定范围的行，使用一个索引来选择行
  • ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
  • eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配
  • const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
  • NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，

• possible_keys:表示查询时，可能使用的索引

  • MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

• key:表示实际使用的索引

  • key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

• key_len:索引字段的长度

  • 表示索引中使用的字节数

• ref:列与索引的比较

  • 列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

• rows:扫描出的行数(估算的行数)

  • 估算出结果集行数

• filtered:按表条件过滤的行百分比

• Extra:执行情况的描述和说明

MySQL 优化器

• 使不使用索引的依据到底是什么？成本

  • 读取二级索引记录的成本
  • 将二级索引记录执行回表操作，也就是到聚簇索引中找到完整的用户记录的操作所付出的成本。

• MySQL优化器在真正执行查询之前，对于每个可能使用到的索引来说，都会预先计算一下需要扫描的二级索引记录的数量

• 如果这个条数占整个记录条数的比例特别大，那么就趋向于使用全表扫描执行查询，否则趋向于使用这个索引执行查询

MySQL总结

数据库事务

ACID

• 原子性（Atomicity）

  • 利用Innodb的undo log。

• 一致性（Consistency）

  • A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性

• 隔离性（Isolation）

  • 利用的是锁和MVCC机制。

• 持久性（Durability）

  • 利用Innodb的redo log（重做日志）。

隔离级别

• READ-UNCOMMITTED(读取未提交)

  • 读取未提交的事务，存在：脏读、不可重复读、幻读问题

• READ-COMMITTED(读取已提交)

  • 读取已经提交的事务，存在：不可重复读、幻读问题

• REPEATABLE-READ(可重复读)

  • 同一事务的多个实例在并发读取数据时，会看到同样的数据行。，存在：幻读问题

• SERIALIZABLE(可串行化)

  • 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。

MVCC

• 实现原理

  • 版本链

    • db_trx_id 最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID。
    • db_roll_pointer 回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
    • db_row_id 隐含的自增ID 如果数据表没有主键，InnoDB会自动以db_row_id产生一个聚簇索引。

  • undo日志

    • 用途

      • 保证事务进行rollback时的原子性和一致性，当事务进行回滚的时候可以用undo log的数据进行恢复。
      • 用于MVCC快照读的数据，在MVCC多版本控制中，通过读取undo log的历史版本数据可以实现不同事务版本号都拥有自己独立的快照数据版本。

    • 类型

      • insert undo log 代表事务在insert新记录时产生的undo log , 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
      • update undo log（主要） 事务在进行update或delete时产生的undo log ; 不仅在事务回滚时需要，在快照读时也需要； 所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

  • Read View

    • 释义：

      • 主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

    • 可见性判断条件

      • db_trx_id < up_limit_id || db_trx_id == creator_trx_id（显示） 如果数据事务ID小于read view中的最小活跃事务ID，则可以肯定该数据是在当前事务启之前就已经存在了的,所以可以显示。 或者数据的事务ID等于creator_trx_id ，那么说明这个数据就是当前事务自己生成的，自己生成的数据自己当然能看见，所以这种情况下此数据也是可以显示的。
      • db_trx_id >= low_limit_id（不显示） 如果数据事务ID大于read view 中的当前系统的最大事务ID，则说明该数据是在当前read view 创建之后才产生的，所以数据不显示。如果小于则进入下一个判断
      • db_trx_id是否在活跃事务（trx_ids）中 不存在：则说明read view产生的时候事务已经commit了，这种情况数据则可以显示。 已存在：则代表我Read View生成时刻，你这个事务还在活跃，还没有Commit，你修改的数据，我当前事务也是看不见的。

• 具体查询操作：RR下

  • SELECT

    • 1、InnoDB只查找版本早于当前事务版本的数据行（也就是，行的事务编号小于或等于当前事务的事务编号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
    • 2、删除的行要事务ID判断，读取到事务开始之前状态的版本，只有符合上述两个条件的记录，才能返回作为查询结果。

  • INSERT

    • InnoDB为新插入的每一行保存当前事务编号作为行版本号。

  • DELETE

    • InnoDB为删除的每一行保存当前事务编号作为行删除标识。

  • UPDATE

    • InnoDB为插入一行新记录，保存当前事务编号作为行版本号，同时保存当前事务编号到原来的行作为行删除标识。

• RC、RR级别下的InnoDB快照读区别

  • 1：RC是半一致性读，每次快照读都会新生成一个快照和Read View, 这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因

• FAQ

  • 只工作在RC、RU下：RU下会存在读取未提交的事务存在冲突、序列化不符合多版本控制的思想
  • MVCC不能解决写-写 更新丢失问题，可以通过MVCC+锁机制实现

锁机制

• 锁类型

  • 表级锁

    • Myisam和Memory引擎使用的是表级锁

  • 行级锁

    • innodb引擎使用的是行级锁

  • 页级索

    • BDB引擎使用的是页级索

• InnoDB七种类型的锁：

  • 共享/排它锁(Shared and Exclusive Locks)

    • 共享锁之间不互斥，简记为：读读可以并行
    • 排他锁与任何锁互斥，简记为：写读，写写不可以并行

  • 意向锁(Intention Locks)

    • 未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向，是一个表级别的锁

  • 记录锁(Record Locks)

    • 封锁索引记录

  • 间隙锁(Gap Locks)

    • 封锁索引记录中的间隔！不包含索引区间

  • 临键锁(Next-key Locks)

    • 记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间 在RR隔离级别下使用next-key可以解决幻读问题

  • 插入意向锁(Insert Intention Locks)

    • 专门针对insert操作：多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

  • 自增锁(Auto-inc Locks)

    • 如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

• FAQ

  • 间隙锁死锁

常见的日志文件

binlog

• 记录了数据库表结构和表数据变更，可以用于：复制和恢复数据

  • MySQL在公司使用的时候往往都是一主多从结构的，从服务器需要与主服务器的数据保持一致，这就是通过binlog来实现的
  • 数据库的数据被干掉了，我们可以通过binlog来对数据进行恢复。

redo log

• 用于记录在某个页上做了什么修改

• 两阶段提交？

  • 准备好数据之后先写redo log并将bin log处与prepare阶段：后写入bin log；在写入redo log并设置为commit状态
  • 简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

undo log

• 提供回滚

  • 可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

• MVCC

  • 更新失败时回滚

FAQ

• 1：bin log和redo log区别

  • Binlog是server层的日志，主要做mysql功能层面的事情，redo是innodb独有的，binlog是所有引擎都可以使用的
  • redo是物理日志，记录的是在某个数据页上做了什么修改，binlog是逻辑日志，记录的是这个语句的原始逻辑
  • redo是循环写的，空间会用完，binlog是可以追加写的，不会覆盖之前的日志信息

开发规范

一、基础规范

• （1）必须使用InnoDB存储引擎

• （2）必须使用UTF8字符集

• （3）数据表、数据字段必须加入中文注释

• （4）禁止使用存储过程、视图、触发器、Event

  • 解读：高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU计算还是上移吧

• （5）禁止存储大文件或者大照片

  • 解读：为何要让数据库做它不擅长的事情？大文件和照片存储在文件系统，数据库里存URI多好

二、命名规范

• 线上环境、开发环境、测试环境数据库内网域名遵循命名规范
• 库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用
• 表名t_xxx，非唯一索引名idx_xxx，唯一索引名uniq_xxx

三、表设计规范

• 单实例表数目必须小于500

• 单表列数目必须小于30

• 表必须有主键，例如自增主键

  • a）主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用
  • b）主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率
  • c） 无主键的表删除，在row模式的主从架构，会导致备库夯住

• 禁止使用外键，如果有外键完整性约束，需要应用程序控制

  • 解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先

• 分解关联查询的意义

  • 1. 让缓存的效率更高
  • 2. 较少锁的竞争
  • 3. 更容易对数据库进行拆分
  • 4. 优化器很弱，只有一下几种join
    • nested loop join就是分别从两个表读一行数据进行两两对比，复杂度是n^2。
    • block nested loop join是分别从两个表读很多行数据，然后进行两两对比，复杂度也是n^2，只是少了些函数调用等overhead。
    • index nested loop join是从第一个表读一行，然后在第二个表的索引中查找这个数据，索引是B+树索引，复杂度可以近似认为是nlogn，比上面两个好很多，这就是要保证关联字段有索引的原因。
    • 如果有hash join，就不用做这种限制了，用第一个表（小表）建hash table，第二个表在hash table中查找匹配的项，复杂度是n。缺点是hash table占的内存可能会比较大，不过也有基于磁盘的hash join，实现起来比较复杂。

四、字段设计规范

• 必须把字段定义为NOT NULL并且提供默认值

  • a）null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化
  • b）null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多
  • c）null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识
  • d）对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录

• 禁止使用TEXT、BLOB类型

  • 解读：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能

• 禁止使用小数存储货币

  • 解读：使用整数吧，小数容易导致钱对不上

• 必须使用varchar(20)存储手机号

  • a）涉及到区号或者国家代号，可能出现±()
  • c）varchar可以支持模糊查询，例如：like“138%”

• 禁止使用ENUM，可使用TINYINT代替

五、索引设计规范

• 单索引字段数不允许超过5个

  • 解读：字段超过5个时，实际已经起不到有效过滤数据的作用了

• 禁止在更新十分频繁、区分度不高的属性上建立索引

  • a）更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能
  • b）“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似

• 建立组合索引，必须把区分度高的字段放在前面

  • 能够更加有效的过滤数据

• 建立组合索引，必须把区分度高的字段放在前面

六、SQL使用规范

• 禁止使用SELECT *，只获取必要的字段，需要显示说明列属性

• 禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性

  • 容易在增加或者删除字段后出现程序BUG

• 禁止使用属性隐式转换

  • 解读：SELECT uid FROM t_user WHERE phone=13800000000 会导致全表扫描，而不能命中phone索引，猜猜为什么？（自行百度！）

• 禁止在WHERE条件的属性上使用函数或者表达式

  • 会导致全表扫描

• 禁止负向查询，以及%开头的模糊查询

  • a）负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描
  • b）%开头的模糊查询，会导致全表扫描

• 禁止大表使用JOIN查询，禁止大表使用子查询

  • 会产生临时表，消耗较多内存与CPU，极大影响数据库性能

• 禁止使用OR条件，必须改为IN查询

  • 旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮助实施查询优化呢？

• 应用程序必须捕获SQL异常，并有相应处理

• 同表的增删字段、索引合并一条DDL语句执行，提高执行效率，减少与数据库的交互。

  • 大数据量高并发的互联网业务，极大影响数据库性能的都不让用，不让用哟。

• 禁止使用text等大数据字段，因为text等字段每次读取都要从磁盘读取耗时较长

• 由于MySQL是单进程多线程模型，一个SQL语句无法利用多个cpu core去执行，这也就决定了MySQL比较适合OLTP(特点：大量用户访问、逻辑读，索引扫描，返回少量数据，SQL简单)

思维导图