速通MySQL

1 概论

1. 【强制】MySQL的整体架构。

    MySQL的逻辑架构分为4层，分别是网络连接层、服务层、存储引擎层、物理层。

1) 网络连接层。

    主要负责连接管理。MySQL服务器上维护了一个线程池（即数据库连接池），每个客户端对应服务器上的一个线程。

2) 服务层。

    MySQL的核心层，包括查询缓存、解析器、预处理器、查询优化器。

    查询缓存：在进行查询之前，服务器会先检查查询缓存，如果能找到对应的查询，则直接返回缓存中的结果集。

    解析器：根据查询语句构造出一个解析树。主要用于语法分析。

    预处理器：进行语义分析。

    查询优化器：将解析树转化为执行计划。一般情况下，一条查询可以有多种执行方式，最终返回相同的结果，优化器就是找到其中最优的执行计划。

3) 存储引擎层。

    负责数据的存储和提取。通过提供一系列接口来屏蔽不同引擎之间的差异。

4) 物理层。

    数据文件。

2. 【强制】MySQL存储引擎。

    MySQL中最常用的存储引擎有InnoDB和MyISAM，两者的区别在于：

     ● InnoDB支持事务，而MyISAM不支持事务；

     ● InnoDB支持聚集索引和非聚集索引，MyISAM只支持非聚集索引；

     ● InnoDB支持表、行级锁，而MyISAM只支持表级锁；

     ● InnoDB必须有唯一索引，而MyISAM可以没有。

3. 【强制】数据库锁。

1) 从锁的粒度划分，可以将锁分为表锁、行锁和页锁。

    行级锁：只针对当前操作的行进行加锁。开销大、加锁慢，可能会出现死锁，但锁的粒度最小，发生锁冲突的概率最低，并发度也最高。

    表级锁：对当前操作的整张表加锁。实现简单，资源消耗较少。

    页级锁：粒度介于行级锁和表级锁之间，每次锁定相邻的一组记录。可能会出现死锁，并发度一般。

2) 从使用性质划分，可以分为共享锁、排他锁和更新锁。

    共享锁：S锁，又称读锁，用于所有的只读操作。加读锁时允许其他事务加读锁，不允许加写锁。读取结束后立即释放，无需等待事务结束。

    排他锁：X锁，又称写锁，用于写操作。加写锁时，不允许其他事务加读锁或写锁。直到事务结束才释放。使用 select * from table_name for update 创建写锁。

    更新锁：U锁，预定要对资源加写锁，允许其他事务读，但不允许再加更新锁或写锁。当被读取的页要被更新时，升级为写锁。更新锁的作用是避免使用共享锁造成死锁。

3) 从主观上划分，可以分为乐观锁和悲观锁。

    乐观锁：认为资源是不会被修改的，所以不加锁读取数据，仅当更新时用版本号机制等确认资源是否已被修改。即CAS操作。

    悲观锁：认为资源一定会被其他事务修改，所以每次操作前都要上锁。

4. 【推荐】数据库日志。

1) redo log：重做日志。

    保证事务的持久性。重做日志记录事务执行后的状态，用来恢复未写入data file的已提交事务数据。

2) undo log：回滚日志。

    保证数据的原子性。回滚日志保存了事务发生前的一个版本的数据，可以用于回滚，同时可以提供多版本并发控制下的读，即非锁定读。

3) binlog：二进制日志。

    常用于主从同步或数据同步中，也可用于数据库基于时间点的还原。

4) error log：错误日志。

    记录着MySQL的启动和停止，以及服务器在运行过程中发生的错误的相关信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出。

5) general query log，普通查询日志。

    记录服务器接收到的每一个命令，无论命令是否正确，会带来不小的开销，因此默认是关闭的。

6) slow query log，慢查询日志。

    记录执行时间过长和没有使用索引的查询语句（默认10s），只记录执行成功的语句。

7) relay log，中继日志。

在从节点中存储接收到的binlog日志内容，用于主从同步。

2 索引

1. 【强制】B+树索引。

1) B+树。

    非叶子结点不存储数据，只存储索引；

    叶子结点之间用指针连接，形成链表。

2) 为什么不用B树？

    MySQL从磁盘读取数据的方式是按页读取，一个磁盘页的默认大小是16KB。

    B+树的非叶子结点只存索引不存真实数据，单个内存页可以存储更多的索引，存储相同的数据量，B+树的高度会比B树更低，磁盘I/0操作的次数也会相对较少。

    B+树的叶子结点之间用链表有序连接，所以扫描全部数据只需要扫描一遍叶子结点，利于扫库和范围查询，而B树没有维护叶子结点之间的指针，只能进行中序遍历，所以B+树的效率更高。

2. 【强制】Hash索引。

    结构是数组+链表，对索引的key进行一次hash计算就可以定位数据，适用于等值查询。

    一般情况下，Hash索引的查询效率比B+树索引更高，但是Hash索引有以下缺点：

     ● 无法进行范围查询和排序；

     ● 不支持模糊查询和多列索引；

     ● 在某个键值大量重复时，产生严重的hash碰撞，查询效率会大大降低。

3. 【强制】聚集索引和非聚集索引。

    （聚集索引有点儿像字典上的拼音查找，非聚集索引有点儿像偏旁查找）

1) 聚集索引。

    索引和数据放在同一个文件中。

    聚集索引的键值的顺序与表中相应行的物理顺序一致，每张表只能有一个聚集索引。

    在InnoDB中，只有主键索引是聚集索引，如果没有主键，则挑选一个唯一值建立聚集索引，如果没有唯一键，则隐式地生成一个键来建立聚集索引。

2) 非聚集索引。

    索引和数据放在不同的文件中。

    非聚集索引的逻辑顺序与数据行的物理顺序不一致。

4. 【强制】创建索引的原则。

1) 为常常作为查询条件的字段建立索引；

2) 为经常需要排序、分组和联合操作的字段建立索引；

3) 数据量较小的表不建议使用索引，如数量级在百万以内，查询花费的时间可能比遍历索引的时间还要短；

4) 限制索引的数目。删除不再使用或很少使用的索引。

5. 【强制】索引失效的情况。

1) select * 时不使用任何索引；模糊查询通配符以%开头时，索引失效。

2) 当查询字段发生类型转换时，索引失效。

3) where条件使用多列索引但不遵守最左前缀原则时，索引失效。

    最左前缀原则：最左优先，以最左边为起点的任何连续的索引都能匹配上，同时遇到范围查询（<、>、between、like）就会停止匹配。如建立(a, b, c, d)索引，查询条件 a = 1 and b = 2 或 b = 2 and a = 1 都可以匹配到索引，优化器会自动调整 a, b 的顺序；如果查询条件只有 b = 2 是匹配不到索引的，因为违背了最左前缀原则；如果是 a = 1 and b = 2 and c > 3 and d = 4，其中d是用不到索引的，因为c是一个范围查询，它之后的字段会停止匹配。

4) where条件中有计算或使用了函数时，索引失效；使用not in和not exists时，索引失效。

5) 使用order by不加where或limit限制，或不遵守最左前缀原则时，索引失效。

6) 使用or关键字时，前面和后面的字段都要符合索引规则，否则所有的索引都会失效。

3 事务

1. 【强制】数据库事务的ACID特性。

1) A：Atomicity，原子性。

    整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。

2) C：Consistency，一致性。

    事务必须始终保持系统一致的状态，即在任意时间点，多个事务对同一数据的读取结果是一致的。

3) I：Isolation，隔离性。

    隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。

4) D：Durability，持久性。

    在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

2. 【强制】数据库的隔离级别。

    SQL标准定义了四种隔离级别，MySQL全部都支持。

1) 读未提交（READ UNCOMMITTED）。

    不加锁，任何事务对数据的修改都会第一时间暴露给其它事务。可能会发生脏读、不可重复读、幻读。

2) 读已提交（READ COMMITTED）。

    一个事务只能读到其它事务已经提交过的数据。不会发生脏读，可能会发生不可重复读、幻读。

3) 可重复读（REPEATABLE READ）。

    事务不会读到其它事务对已有数据的修改，即使其它事务已提交，也就是说，事务开始时读到的已有数据是什么，在事务提交前的任意时刻，这些数据的值都是一样的。不会发生脏读、不可重复读，可能会发生幻读。

4) 串行化（SERIALIZABLE）。

    将事务的执行变为顺序执行，后一个事务执行必须等待前一个事务结束。不会发生脏读、不可重复读、幻读。

3. 【强制】脏读、不可重复读、幻读。

1) 脏读。

    读未提交隔离等级下，A事务（未提交）修改一条数据后，B事务能直接读取到修改后的数据，若A事务发生回滚，B事务就读到了脏数据。

2) 不可重复读。

    读已提交和读未提交隔离等级下，B事务读取一条数据后，A事务（已提交）修改了这条数据，B事务再次读这条数据时，读到了修改后的数据——B事务两次读取数据的结果不一致。

3) 幻读。

    可重复读、读已提交、读未提交隔离等级下，A事务（已提交）插入了一条新数据，B事务在A事务提交前后读到表中的数据总数不一样。

4 调优

1. 【强制】SQL的执行过程。

1) 连接数据库。

    连接器负责跟连接数据库、获取权限、维持和管理连接，如果用户认证通过，连接器会到权限表里面查询用户拥有的权限，之后该连接的权限验证都依赖于查出来的权限。

    MySQL既支持短连接，也支持长连接，长连接默认8小时断开。

2) 查询缓存。

    连接数据库后，select语句会先去查询缓存，看SQL以前是否被执行过，如果在缓存中能查到，就直接返回缓存的信息，不再执行后面的步骤。

3) 词法分析。

    MySQL识别出SQL语句中的字符串分别是什么，代表什么。

4) 语法分析。

    根据词法分析的结果，判断SQL语句是否满足语法。

5) SQL优化。

    优化器会对SQL的执行顺序，使用哪个索引进行优化，确定SQL的执行方案。

6) SQL执行。

    执行器校验用户权限，如果通过，就打开表，根据表的引擎定义，使用对应引擎提供的接口，执行SQL。

2. 【强制】高性能数据库优化。

    （数据库往往是系统的薄弱环节）

1) 反范式设计。

    将同一个字段冗余地存在多个表里面，目的是减少多表联查，更新该字段时必须同步更新所有的冗余字段。

    冗余数据会导致数据的维护成本变高，所以在平时工作中，我们通常将范式和反范式结合使用。

    附数据库三大范式：

     ● 1NF：字段（或属性）是不可分割的最小单元，即不会有重复的列，体现原子性。

     ● 2NF：满足1NF前提下，存在一个候选码，非主属性全部依赖该候选码，即存在主键，体现唯一性。

     ● 3NF：满足2NF前提下，非主属性必须互不依赖，消除传递依赖。    

2) 不要使用外键，在业务层面做约束。

3) 隔离级别选择读已提交。

4) 字段扩展性。

    使用JSON格式存储可变表单的数据，如果其中某个字段需要建立索引，就把它单独拆出来。

5) 冷热分离。

    把大表拆分成一张主表和一张扩展表，把不常修改的信息存储到扩展表当中。

6) 单表操作。

    尽量不用联表查询，而是采用 单表查询+代码层组装 的方式。

     ● 单表查询SQL的复用率较高，缓存利用率也较高；

     ● 联表查询情况下，表结构变动导致查询SQL需要同步修改的可能性更高；

     ● 两个大表联查的效率可能很低；

     ● 复杂SQL在分库分表时非常麻烦。

7) 一次最多修改200条数据。

3. 【推荐】SQL执行计划。

    使用EXPLAIN命令查看执行计划，只需在查询语句开头增加EXPLAIN关键字即可。

    结果中的重要参数：

     ● id：代表执行select语句或操作表的顺序，如果包含子查询，会出现多个ID。值越大，优先级越高。

     ● select_type：查询类型，区别普通查询、联合查询以及子查询等。

     ● table。

     ● type：查询扫描情况，从最好到最差依次是system、const、eq_ref、ref、range、index、All。

     ● possible_keys：显示可能应用在这张表中的索引，这里查到的索引不一定真正地用到。

     ● key：实际使用到的索引。

     ● key_len：索引中使用的字节数，在不损失准确性的情况下越短越好。

     ● ref：显示索引的哪一列被使用了。

     ● rows：根据表统计信息及索引使用情况，估算出找到所需记录需要读取的行数。

     ● extra。

4. 【推荐】词义辨析。

1) on条件和where条件的区别。

    on条件是在生成临时表时使用的条件，不管是否满足on中的条件，都会返回被驱动表中的记录；

    where条件是在临时表生成以后再进行过滤，不满足条件就全部过滤掉。

2) having条件和where条件的区别。

    having条件的作用是筛选满足条件的组，即在分组后过滤数据，条件中经常包含聚组函数；

    where条件的作用是在对查询结果进行分组前，将不符合条件的行过滤掉，即在分组之前过滤数据。where条件中不能包含聚组函数。

3) union和union all的区别。

    union对两个结果集进行并集操作，不包括重复行，同时进行符合默认规则的排序；

    union all对两个结果集进行并集操作，包括重复行，不进行排序。

5. 【推荐】SQL关键字的执行顺序。

    from - on - join - where - group by - arg func - with - having - select - distinct - order by - limit

5 其他

1. 【推荐】主从复制。

1) 主从复制。

    用来建立一个与主数据库完全一样的数据库环境，即从数据库。主数据库一般是准实时的业务数据库。

2) 主从复制的作用。

     ● 读写分离，使数据库能支撑更大的并发；

     ● 高可用，做数据的热备，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。