MySQL整体分为三层：客户端、MySQL服务、文件系统。
-
客户端：是各种编程语言的connector。
-
MySQL服务：内部包含各种组件，实现各种功能。例如：连接器、缓冲池、分析器、优化器、执行器、存储引擎等。
-
文件系统：数据存储与日志。
2.客户端
2.1 数据库连接策略
1.主从数据库链接
-
主从链接选择方式：开启事务则自动选择主库链接，其他情况自动选择从库链接。
-
写数据自动选择主库，读数据默认选中从库，也可以手动选择主库。多个从库使用随机策略选择。
2.2 数据库写策略
1.预防SQL注入
-
mysql_real_escape_string()函数转义 SQL 语句中使用的字符串中的特殊字符。
-
使用PDO预处理prepare方式。
2.预防死锁
-
以相同的顺序访问表和行。避免逐步加锁导致锁等待，从而造成死锁。
-
大事务拆小。并且事务中禁止调用外部接口，因为外部接口响应时间过长时，会导致事务超时，也提升了死锁的几率。
-
注意where条件的强制类型转换和函数计算，会导致扫描全表，从而锁住全表的每一条数据。
-
尽量使用主键作为条件更新数据，普通索引会有gap锁（间隙锁），锁住当前数据的相邻数据，死锁几率增大。
3.并发问题
-
预防离线更新丢失
-
事务中禁止调用外部接口。因为事务中调用外部接口之后，出现数据回滚，会导致外部接口接收到数据，而本系统没有数据。
-
-
预防不一致读导致的更新丢失
-
更新数据时对比版本号（通常是数据的update_time），因为如果不对比版本号，那么可能会出现数据在读取之后发生了变化，而更新的时候用了变化之前的数据。从而导致数据更新丢失。
-
-
预防本地数据更新丢失
-
事务中SQL异常必须处理，如果不处理，那么会重新连接数据库，从而导致最后提交（commit）的数据只有异常之后的数据，前半部分数据丢失。
-
禁止使用嵌套事务，MySQL已经开启事务后，再开启事务（start transaction），会隐式的提交（commit）上一个事务。如果下一个事务回滚，会导致只提交了前半部分数据，后半部分数据丢失。
-
4.写数据效率问题
-
单条写优化为批量写（可以提升10倍的速度，从100条/s提升到1000条/s），受硬盘转速和连接资源消耗影响。
-
批量添加数据示例：INSERT INTO {table} ({$field}) VALUES (),();
-
批量更新数据示例：UPDATE {table} SET {field} = CASE {primaryKey} WHEN {key} THEN {val} END WHERE {where}。
-
2.3 数据库读策略
1.where条件必须命中索引，预防不能命中索引的常见场景：
-
字段允许为null，负向查询时不能命中索引（此字段负向查询还会把null排除，完整的悲剧）。
-
where条件的强制类型转换，不能命中索引，会扫描全表。
-
前导模糊查询不能使用索引。例如：like '%xx';
-
在where条件里进行函数计算不能命中索引。例如：YEAR(date)
-
组合索引的where条件，需满足最左前缀的原则。
2.查询结果处理
-
如果明确只有一条数据，那么使用limit 1。因为limit 1能告诉数据库主动停止游标移动。
-
计算放到业务层，可以节省数据的CPU，例如：CURDATE()。
-
一次性不宜返回太多数据。返回太多数据会增加MySQL查询和业务层内存压力。
3.MySQL服务
3.1 连接器
1.连接器负责跟客户端建立连接、获取权限、维持和管理连接。
-
数据库链接验证：权限信息会放入内存，数据库连接由user表里的Host/user/password三个列进行验证。
-
SQL请求验证：验证有没有权限对数据库进行操作如：（create , drop , select , delete等等。）
2.长连接和短连接的选择
-
长连接能节省连接时通讯造成的资源浪费，但是链接过多后，会导致mysql占用内存较大，从而可能被系统强行杀掉（OOM）。
-
短链接没有占用内存较大的风险，但是会频繁建立链接，从而消耗资源。
-
因此长连接和短连接需根据具体情况选择
-
普通的页面访问建议使用短连接
-
脚本处理大量数据可适度选择用长连接（注意用完之后mysql_reset_connection释放链接资源。）
-
3.2 分析器
1.分析器分析SQL语法
-
词法分析：分析SQL语句中的字符串分别代表什么意思。
-
语法分析：分析SQL语句是否满足MySQL语法。
3.3 优化器
1.优化器生成执行计划，选择索引
-
选择索引：优化器是在表里面有多个索引的时候，决定使用哪个索引。
-
在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
3.4 执行器
1.执行语句
-
开始执行的时候，要先判断用户对这个表T有没有查询权限，如果没有，就会返回权限的错误。
-
如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。
3.5 存储引擎
1.了解存储引擎
-
存储引擎是数据库存储数据到文件或者内存中的一种技术，不同的存储引擎具有不同的存储机制、索引技巧、锁水平。
-
MySQL常用的存储引擎有InnoDB、MyISAM、Memory，其中InnoDB使用范围最广。
-
InnoDB：支持ACID事务，支持行级锁定，支持外键，是聚集索引，数据文件和索引是绑在一起的。
-
MyISAM: 不支持ACID事务，不支持外键，是非聚集索引，数据文件和索引是分离的，索引保存的是数据文件的指针。
-
Memory：文件数据保存在内存中。
-
-
根据不同的数据存储场景，选择不同的存储引擎。例如：需要事务的写数据场景，使用InnoDB。不需要事务，读频繁的读数据场景可以使用MyISAM。
-
数据表中设置ENGINE=InnoDB，即可使用InnoDB存储引擎。
2.InnoDB的性能优化技巧
-
合理设置写缓冲池大小，以此解决磁盘读写性能瓶颈问题。通过innodb_buffer_pool_size可以配置缓冲池大小，innodb_change_buffer_max_size配置写缓冲池的占比。
-
大量数据操作修改为批量操作，以此解决硬盘转速瓶颈，并减少客户端和服务端的通信开销。例如 : INSERT INTO yourtable VALUES (1, 2), (5, 5);
-
配置定期刷盘:
-
innodb_flush_log_at_trx_commit=0，每隔1s将日志缓冲区的数据刷到系统缓冲区和磁盘。性能最好，但数据库崩溃有1s的数据丢失可能。
-
innodb_flush_log_at_trx_commit=1，每次事务提交都刷系统缓冲区和落盘，Innodb默认配置。性能较低，但能保证事务ACID特性。
-
innodb_flush_log_at_trx_commit=2，每次事务提交都刷系统缓冲区，但是每隔1s批量刷盘。批量优化的最佳选择，只要系统不崩溃，数据就不会丢失。（系统崩溃的概率，远小于数据库服务崩溃的概率）
-
-
配置redo log缓冲，优化其性能。可通过innodb_log_buffer_size配置。
3.6 缓冲池
1.了解缓冲池
-
缓冲池是一种把数据和索引缓冲到内存中的一种机制。数据放到内存后，可以避免每次操作进行磁盘IO，从而提升MySQL的性能。
-
MySQL缓冲池是按照局部性原理，进行预读，一次读取一页数据（一般是4k）。使用LRU算法管理，LRU算法是用链表进行管理的，遵循最晚淘汰原则。
-
MySQL缓冲池相关的配置有：
-
innodb_buffer_pool_size可以配置缓冲池大小。
-
innodb_old_blocks_pct配置老生代占整个LRU链长度。
-
innodb_old_blocks_time配置老生代停留时间窗口。
-
innodb_change_buffer_max_size 配置写缓冲占整个缓冲池的比例，默认25%。
-
innodb_change_buffering 配置哪些写操作启用写缓冲池，可以配置all/none/insert/delete。
-
2.缓冲池的相关疑问？
-
写缓冲写入动作
-
如果是缓存池中的页，那么会有一次内存操作和一次磁盘顺序写操作。如果修改不在缓冲池的页，那么会有一次磁盘随机读操作、一次内存操作、一次磁盘顺序写操作。
-
-
写缓冲如何保证数据一致性？
-
数据库异常崩溃，能够从redo log中恢复数据。
-
写缓冲不只是一个内存结构，它也会被定期刷盘到表空间。
-
数据读取时，会将数据合并到缓冲池。
-
3.7 redo log日志
1.了解redo log日志
-
redo log被称为重做日志，其用于保证事务的ACID特性，同时也能提升数据库的写性能。
-
数据库遭遇故障重启时，可以使用redo log进行数据恢复，从而保证事务的ACID特性。
-
redo log有3层架构：日志缓冲区log buffer、系统缓冲区OS cahce、磁盘的日志文件log file。可以通过innodb_flush_log_at_trx_commit配置来调整顺序写和批量写：
-
innodb_flush_log_at_trx_commit=0，每隔1s将日志缓冲区的数据刷到系统缓冲区和磁盘。性能最好，但数据库崩溃有1s的数据丢失可能。
-
innodb_flush_log_at_trx_commit=1，每次事务提交都刷系统缓冲区和落盘，Innodb默认配置。性能较低，但能保证事务ACID特性。
-
innodb_flush_log_at_trx_commit=2，每次事务提交都刷系统缓冲区，但是每隔1s批量刷盘。批量优化的最佳选择，只要系统不崩溃，数据就不会丢失。（系统崩溃的概率，远小于数据库服务崩溃的概率）
-
3.8 binlog 日志
1.了解binlog日志
-
binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。包括索引文件和内容文件。
-
binlog可以用于：恢复数据、复制数据（主从复制）、审计数据。
-
恢复数据：应用于增量备份的数据恢复过程。当遇到数据误删或者服务异常造成数据丢失时，可以binlog重放到异常之前以此来恢复数据。
-
复制数据：主库有一个log dump线程，将binlog传给从库，从库有两个线程，一个I/O线程，一个SQL线程，I/O线程读取主库传过来的binlog内容并写入到relay log,SQL线程从relay log里面读取内容，写入从库的数据库。
-
审计数据：
-
4.数据库设计
4.1 数据库设计的基概
1.单库架构：一个库进行数据存储。
2.分组架构：
-
一主多从，主从同步，读写分离数据库架构。
-
一主多从结构，可以线性提升数据库读性能。通过读写分离消除读写锁冲突，从而提升数据库写性能。
-
主从之间通过binlog进度数据同步。多个数据库实例数据结构完全相同，实质是将数据进行复制。
-
这种架构，可以支持生产环境千万级别的数据。
3.分片架构：
-
水平切分数据，多个数据库实例构成一个数据库集群。(画外音：数据表还有垂直切分，把大表拆成多个小表)
-
分片可以很容易将数据迁移到不同的数据库实例，拥有更好的扩展性。
-
水平切分可以依据主键有两种策略：
-
范围法：以业务主键为依据，划分为多个范围的数据。
-
哈希法：通过对业务主键进行哈希取摸，查询不同的数据。
-
-
分片架构可以线性提升数据库的写性能，降低单库数据容量。
4.分组+分片结构：
-
一主多从结构，加上水平切分的对个数据库实例，构成一个数据库集群。
-
可以线性提升数据库写性能、读性能，同时保证读高可用。（提供灾备数据库）
-
这种架构，可以支持生产环境亿级别的数据。
4.2 数据库安全方案
1.全量备份+增量备份
-
定期将数据库文件进行全量备份，例如：每月将库文件通过tar -czvf进行备份。
-
定期将binlog进行增量备份。
-
数据恢复过程
-
首先，将最近一次全量备份找到，拷贝回来解压、应用。
-
然后，将每一天的增量备份找到，拷贝回来依次重放，直至删库之前的binlog。
-
2.1小时延时从库（灾备数据库）
-
每隔1小时同步一次主库，同步完成后断开。
-
数据恢复过程
-
首先，从没有删除数据的从库中恢复数据。
-
然后，将1小时延时的binlog找到重放。
-
3.双份1小时延时从库
-
为预防删库时刚好是1小时延时从库同步的一小段时间，可以使两个从库岔开半小时。
-
数据恢复过程
-
首先，从没有删除数据的从库中恢复数据。
-
然后，将1小时延时的binlog找到重放。
-
5.MySQL常用知识
5.1 MySQL死锁问题
1.什么是死锁？
-
死锁是指两个或两个以上的线程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。
2.MySQL是怎么出现死锁的？
-
首先了解MySQL的加锁情况
-
InnoDB使用的是聚集索引，普通索引的叶子节点存储主键值，主键索引的叶节点存储行数据。对主键进行操作会锁住整行数据，对普通索引操作会锁住索引和行数据，如果没有索引会锁住整个表。
-
锁和隔离级别也有关系，可重复读（RR）级别下，给索引上的记录添加上X锁，此外，还在非唯一索引与相邻两个索引的区间加上锁。
-
-
对两条数据，在两个事务执行不同顺序的写入操作。例如：事务1（A->B），事务2（B->A），会出现事务1等待B的资源，事务2等待A的资源。
-
不同索引锁冲突，使用范围条件做写入条件，则可能会出现在聚簇索引上加锁顺序不一致，从而造成死锁。
-
gap锁（间隙锁）冲突，可重复读（RR）级别下，会给相邻索引的区间加上锁。
3.如何解决死锁问题？
-
避免死锁的方法：
-
以相同的顺序访问表和行。
-
大事务拆小。
-
降低隔离级别。
-
为表添加索引，避免不走索引而导致数据每行都加锁的情况。（这里有个常见问题，where条件类型转换，会导致扫描全表，从而锁住全部记录）
-
-
分析、定位死锁：
-
SHOW ENGINE INNODB STATUS; 分析最近的锁日志
-
Explain执行计划，可分析对应SQL语句的扫描情况。
-
innodb_lock_wait_timeout 设置事务等待的超时时间。
-
4.理解死锁的好处。
-
减少事务等待，避免死锁而造成服务资源浪费，提升服务性能。
-
降低数据回滚频率，提升系统可用性。
5.2 Explain分析SQL的执行计划
1.type扫描方式
-
system: 从系统表查询数据，这些数据已经加载到了内存，不需要磁盘IO。
-
const: 命中索引并且被连接的部分是一个常量。
-
eq_ref: join查询，命中主键或者非空唯一索引，等值链接。
-
ref: 命中非普通索引。
-
range: 索引范围扫描。
-
index: 索引范围内的全表扫描。
-
all: 全表扫描。
2.Extra
-
Using where SQL使用了where条件过滤数据。
-
Using index SQL所需数据都在一棵索引树上，无需访问实际的行记录。
-
Using index condition SQL命中了索引，但不是所有列数据都在索引树上，需要访问实际的行记录。
-
Using filesort SQL所需的结果集，需要对所有记录进行文件排序。
-
Using temporary SQL需要建立临时表来暂存中间结果集。
-
Using join buffer SQL需要进行嵌套循环计算。
5.3 聚集索引和普通索引
1.聚集索引
-
聚集索引，叶子节点存的是整行数据，直接通过这个聚集索引的键值找到某行。数据行和相邻的键值紧凑地存储在一起，因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚集索引。
2.普通索引
-
非聚集索引，叶子节点存的是字段的值，通过这个非聚集索引的键值找到对应的聚集索引字段的值，再通过聚集索引键值找到表的某行。
3.回表查询
-
先通过普通索引定位到主键值，然后通过聚集索引定位到行记录。此称为回表查询。
4.索引覆盖
-
只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表。就是Using index 索引覆盖。
5.联合索引
-
多个字段上建立索引，遵循最左侧原则。
5.4 热备和冷备
1.热备
-
热备就是逻辑备份。是通过访问MySQL服务，来获得数据库结构和数据被内容进行备份的。
-
热备的备份力度更细，备份粒度可以全库、单库、全表、单表。可移植性也很好，可跨平台使用。不过速度较慢。
-
热备通过将数据库信息转化为逻辑格式（SQL）进行备份。
2.冷备
-
冷备就是物理备份。是通过数据文件和日志文件拷贝来进行备份的。
-
冷备的优点是速度比较快，文件大小也比较小。缺点是可移植性比较差，为保证数据一致性，往往要求数据库处理离线状态。
1.索引是一种加速查询的数据结构，常见的索引有两类：哈希索引、树索引。
2.为什么选择树索引而不是哈希索引？
-
哈希索引单条数据查询的复杂度是O(1)，但是分组、排序、比较的复杂度有可能退化为O(n)。
-
树索引的时间复杂度都是O(lg(n))。
3.为什么要使用B+树
-
二叉树，当数据量大时树的高度会比较高，一次查询可能会多次磁盘IO。从而导致查询会比较慢。
-
B+树即M叉树，在B树上做了改进。数据只存储在同一层的叶子节点上，并增加了链表，获取所有节点，不再需要中序遍历。B+树每个节点可以设置为一页（4k），可以利用计算机系统的局部性原理，三层树结构可以存储4G的索引数据。
-
B+树的优点：
-
很适合磁盘存储，能够充分利用局部性原理，磁盘预读（计算机系统磁盘读取是按页读取的，一页4kb）。
-
很低的树高度，能够存储大量数据。
-
索引本身占用内存很小。
-
-