1、MySQL架构
1、逻辑架构
1、最上层：连接管理、授权认证、安全等。每个客户端在服务器进程中都有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程轮流在某个CPU核心或者CPU运行。
2、第二层：MySQL核心功能，包括解析、分析、优化、缓存以及所有内置函数（日期、时间、数学和加密函数），跨存储引擎的功能包括存储过程、触发器、视图等。
3、第三层：存储引擎，负责数据存储和提取。
2、并发控制
1、读写锁
- 读：共享锁
- 写：排他锁
2、锁粒度
- 表锁：开销小
- 行级锁：开销大，最大程度支持并发
3、事务
1、事务特性：ACID
2、SQL定义的四种隔离级别
- 未提交读：事务中的没有提交的修改对其他事务也是可见的；可能出现：脏读、不可重复读、幻读。
- 提交读：事务所做的修改在未提交之前对其他事务时不可见的；也叫不可重复读，两次执行同样的查询可能得到不同的结果。可能出现：不可重复读、幻读。
- 可重复读（MySQL默认事务隔离级别）：保证在同一个事务中多次读取同样记录的结果是一样的。可能出现：幻读（某个事务读取某个范围内的记录另一个事务又在该范围内插入新的记录，当之前的事务再次读取该范围的记录时会产生幻行）。
- 可串行化：最高隔离级别，强制事务串行执行，在读取每一行数据上都加锁。
3、InnoDB采用多版本并发控制（MVCC）解决幻读；InnoDB解决死锁：回滚持有最少行级排他锁的事务。MVCC只能在可重复读和提交读两个隔离级别下工作。MVCC是通过保存数据在某个时间点的快照来实现的。
4、存储引擎
1、InnoDB的间隙锁：锁住一个范围内的索引记录，或者锁住第一条索引记录之前的范围，又或者锁住最后一条索引记录之后的范围。间隙锁可能包含单个索引或者多个索引或者不包含索引都可以。
- 一个事务占用的间隙锁不会阻止另一个事务在同一个间隙上获得间隙锁；
- 如果有唯一索引，利用唯一索引查询某一行的数据不会产生间隙锁；如果没有索引或者是非唯一索引，当查询某一行数据时会产生间隙锁把所查询的行之前的所有行锁住。
- 间隙锁策略防止幻读的出现，间隙锁锁定查询涉及的行和对索引中的间隙进行锁定，以防止幻影行的插入。
2、MyISAM和InnoDB的区别
- InnoDB支持事务，MyISAM不支持事务；
- MyISAM只支持表锁，InnoDB支持表锁行级锁；
- MyISAM不支持外键，InnoDB支持外键；
- MyISAM允许没有主键，InnoDB如果没有设置主键会自动创建默认主键（6字节）；
- MyISAM支持全文搜索，MySQL5.6后InnoDB才支持全文搜索；
- MyISAM有专门的计数器记录数据库表的行数，InnoDB没有。
2、Schema和数据类型
1、选择优化的数据类型
1、更小的通常更好，尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为他们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少。
2、简单的更好，简单数据类型的操作通常需要更少的CPU周期，比如，整型比字符操作代价更低。使用内建类型而不是字符串来存储日期，使用整型存储IP。
3、尽量避免NULL的列，除非真正需要，因为查询中如果包含NULL的列对MySQL来说很难优化
4、TIMESAMP和DATETIME都可以存储相同类型的数据，但是TIMESAMP使用DATETIME一半的存储空间，会根据时区自动更新。
5、VARCHAR存储可变长字符串，下面的情况适合VARCHAR：字符串列的最大长度比平均长度大的多；列的更新很少，所以碎片不是问题；使用了UTF-8这杨复杂的字符集，每个字符都使用了不同的字节数进行存储。
6、CHAR类型是定长的，适合存储很短的字符串，或者所有值都接近同一个长度。
7、BLOB和TEXT未存储很大的数据设计的字符串数据类型，分别采用二进制和字符方式存储。
8、整数时标识列最好的选择，因为很快并且支持自增；尽量避免字符串类型作为标识列，因为耗空间，比数字类型慢。
完全随机的字符串，例如MD5、UUID等产生的字符串会任意分布在很大的空间，因为插入值会随机写到索引的不同位置，导致页分裂、磁盘随机访问，以及聚簇存储引擎产生聚簇索引碎片，使得INSERT变得很慢。
逻辑上相邻的行分布在磁盘和内存的不同地方，所以SELECT语句变得很慢。
随机值导致缓存对所有类型的查询语句效果都很差，因为会使得缓存赖以工作的访问局部性原理失效。
2、MySQL Schema设计中的陷阱
1、太多的列，存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲拷贝数据，然后在服务器层将缓冲内容解码成各个列，太多的列导致解码代价高。
2、太多的关联，如果希望查询执行快速且并发性好，单个查询最好在12个表内关联。
3、范式与反范式
1、范式是设计数据库时遵循的一些规则。
- 1NF：数据表的每一列都是不可再分的基本数据项。
- 2NF：数据表每行必须可以被唯一区分，通常使用一个主键来唯一标识。
- 3NF：非主属性之间不能相互依赖，必须直接依赖候选关键字。
2、反范式
- 增加冗余列：在多个表中增加相同的列，为了避免查询时连接操作；
- 增加派生列：增加的列来自其他表的数据，是由其他表经过计算生成，减少查询时的连接操作；
- 重新组表和分割表
3、范式的优点
- 更新更快；
- 很少或者没有重复数据，所以只需修改更少的数据；
- 表更小，可以更好的存放在内存，执行操作更快；
- 很少多余的数据意味着检索时更少用到DISTINCT或者GROUP BY语句。
缺点：需要关联。
4、ALTER TABLE操作常见场景：
- 先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库切换；
- “影子拷贝”，用要求的表结构创建一张与源表无关的新表，然后通过重命名和删表操作交换两张表。
3、创建高性能索引
索引是存储引擎用于快速找到记录的一种数据结构。
1、索引的类型
1、B-TREE
- B-TREE对索引列是顺序组织存储的，所以适合范围查找；
- B-TREE索引的限制：
- 要按照索引的最左列开始查找，否则无法使用索引；
- 不能跳过索引中的列，比如索引是三个列，无法按照第一列和第三列使用索引；
- 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。
2、哈希索引：基于哈希表实现，只有精确匹配索引所有列的查询才有效。
- 对索引列计算哈希值，存储在索引中，同时在哈希表中存储指向每个数据行的指针；
- 哈希索引的限制：
- 索引数据不是按照索引值顺序存储的，无法用于排序；
- 不支持部分索引列匹配查找；
- 只支持等值比较查询，不支持任何范围查询；
- 如果哈希冲突很多的话，一些索引维护操作代价很高；
- 当查询时出现哈希冲突时，存储引擎必须遍历链表中的所有指针逐行进行比较直到找到符合条件的行。
- InnoDB支持自适应的哈希索引，对某些使用频繁的索引值创建哈希索引；这是存储引擎完全自动、内部的行为；
3、全文索引：查找文本中的关键词。
2、索引的优点
1、大大减少了服务器需要扫描的数据量；
2、帮助服务器避免排序和临时表；
3、将随机IO变为顺序IO；
3、高性能的索引策略
1、独立的列：如果查询中的列不是独立的，MySQL不会使用索引，独立的列就是索引列不能是表达式的一部分，也不能是函数的一部分。
2、索引的选择性：不重复的索引值和数据表总记录数的比值。选择性越高查询效率越高，因为MySQL会在查询时过滤掉很多行。
3、前缀索引：索引开始的部分字符，可以大大节约索引空间，提高索引效率。
缺点：MySQL不能用前缀索引做ORDER BY和Group By，也无法使用前缀索引做覆盖扫描。
常见应用场景：针对很长的十六进制唯一ID使用前缀索引。
4、索引合并策略有时候是一种优化的结果：
- 当服务器对多个索引做相交操作（通常是有多个AND条件）意味着需要的是一个包含所有相关列的多列索引，而不是多个独立的索引；
- 当服务器对多个索引做联合操作时（通常是多个OR条件）通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并。
5、聚簇索引：不是一种单独的索引，而是一种数据存储方式。
InnoDB的聚簇索引实际上是在同一个结构中保存了B+TREE索引和数据行，叶子页包含了行的全部数据，节点页只包含索引列。
优点：
- 把相关的数据保存在一起，比如实现电子邮箱时，根据用户ID来采集数据，只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件；
- 数据访问更快；
- 使用覆盖索引扫描的查询可以直接使用叶子页中的主键值。
缺点： - 插入速度严重依赖插入顺序；
- 更新聚簇索引代价高；
- 插入新行或更新主键导致行移动时可能会有“页分裂”（当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行）；
- 全表扫描变慢；
- 二级索引会很大，二级索引访问需要两次查找，一次找到二级索引的叶子节点获得主键值，一次根据主键值去聚簇索引中查找对应的行。
6、覆盖索引：索引本身就包含所要查询字段的值。
当发起以被索引覆盖的查询，在EXPLAIN的Extra列可以看到“Using index”的信息。
7、MyISAM的压缩索引方法：先完全保存索引块的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余不同后缀部分，把字节数和后缀保存起来。这样后面的索引依赖前面的索引，没法对索引用二分查找，只能从头扫描。
8、InnoDB在二级索引上使用共享锁，但访问主键索引需要排他锁。
9、B-Tree索引可能会碎片化，降低查询效率；表数据存储也可能碎片化，?可以通过OPTIMIZE TABLE或者导出再导入的方式重新整理数据。?对于MyISAM可以通过排序算法重建索引消除碎片。
4、查询性能优化
1、重构查询
1、 分解关联查询
- 让缓存效率更高；
- 执行单个查询减少锁的竞争；
- 在应用层做关联，更容易对数据库拆分，做到高性能和可扩展；
- 减少冗余记录的查找。
2、查询执行的过程
1、客户端根据通信协议发送一条查询给服务器；
- 通信协议是半双工的，同一时刻只能一端发送；
- MySQL连接的状态
- Sleep：线程在等待客户端发送新的请求；
- Query：线程正在执行查询或者正在将结果发送给客户端；
- Locked：服务器层线程在等待表锁；
- Analyzing and statistics：线程在收集存储引擎的统计信息，并生成查询的执行计划；
- Copying to tmp table [on disk]：线程正在执行查询，将结果集复制到一个临时表，三种可能：做GROUP BY、文件排序、UNION操作；
- Sorting result：线程在对结果集排序；
- Sending data：线程在多个状态之间传送数据，或着在生成结果集，或者在向客户端发送数据。
2、服务器先检查查询缓存，如果命中缓存直接返回结果，否则进行下一阶段；
- 检查通过对大小写敏感的哈希查找实现。
3、服务器进行SQL解析、预处理，再由优化器生成对应的执行计划；
- 执行计划是一种数据结构，是一棵指令树，左侧深度优先的树，而不是字节码；
- MySQL查询优化两种策略：静态和动态。区别：MySQL对静态优化只需要做一次，动态优化则在每次执行时都要重新评估。
- MySQL执行关联查询：从一个表开始一直嵌套循环、回溯完成所有表的关联。
4、MySQL根据执行计划调用存储引擎的API执行查询；
5、返回结果给客户端。
3、特定类型查询的优化
1、优化关联查询
- 确保任何GROUP BY和ORDER BY中的表达式只涉及到一个表中的列；
- 确保ON或USING子句上的列有索引。
5、复制
1、复制概述
1、MySQL支持两种复制方式：基于行的复制，基于语句的复制。两种方式都是通过在主库记录二进制日志、在备库上重放日志实现异步的数据复制。
2、复制作用
- 负载均衡，可以将读操作分不到多个服务器上；
- 对备份的技术补充；
- 高可用性和故障切换；
- MySQL升级测试时查询能在备库按照预期执行。
3、复制的流程
主库把数据更改记录到二进制日志中；
每次准备提交事务完成数据更新之前，MySQL按照事务提交的顺序记录二进制日志。
备库通过I/O线程与主库建立TCP/IP连接，将主库上的日志复制到自己的中继日志；
备库读取中继日志中的事件，将其重放到备库数据库上。
2、复制的原理
1、基于语句的复制
主句记录更改数据的查询，备库实际上就是把这些SQL语句再执行一遍。出现问题容易定位。
2、基于行的复制
直接将实际数据记录在二进制日志中。可以更高效的复制数据，但是二进制日志可能会很大，出现问题难定位。不必像基于语句的复制那样执行查询计划，会占用更少的CPU。
6、可扩展性
1、负载均衡
1、负载均衡算法
- 随机：随机从服务器池选择一台服务器处理请求；
- 轮询：以循环的顺序发送请求到服务器；
- 最少连接数：下一个连接请求发送给拥有最少活跃连接的服务器；
- 最快响应：能够最快处理请求的服务器接受下一个连接；
- 哈希：通过连接的源IP地址进行哈希，映射到同一个服务器上；
- 权重：性能好的服务器权重大，分配的请求更多。