前言
一、MySQL是什么？
MySQL是目前最流行的关系型数据库之一，MySQL因为其速度、可靠性和适应性而备受关注。
二、MySQL常用的存储引擎
1、ISAM：
优点：读取操作的速度很快。
缺点： 没有事务、没有索引、没有字段管理。
2、MyISAM：
优点：ISAM扩展格式、提供ISAM里所没有的索引和字段管理、表格锁定的机制、插入数据快，空间和内存使用比较低。
缺点：重要缺陷就是不能在表损坏后恢复数据、适用于完整性、并发性要求比较低。
3、HEAP：
优点：只驻留在内存里的临时表格、速度比ISAM和MyISAM快。
缺点：管理的数据不稳定、关机前没保存将全部丢失。
4、InnoDB：
优点：对数据库ACID事务的支持、实现了SQL标准的四种隔离级别、支持崩溃修复能力和并发控制、提供了行级锁和外键约束、MySQL运行时Innodb会在内存中建立缓冲池，用于 缓冲数据和索引。
缺点：不支持FULLTEXT类型的索引，而且它没有保存表的行数。
5、MEMORY：
优点：数据全部放在内存中、处理速度快。
缺点：需要足够的内存、内存出现异常就会影响数据、安全性不高、不能建立太大的表。
三、InnoDB内存架构（5.5.5以上默认Engine是Innodb，其他版本默认是MyISAM）
基于磁盘的存储引擎，数据都是存在磁盘上的。InnoDB将数据从磁盘加载到内存，有一个最小的逻辑单位——页，每一次读取一页的数据到内存。每一页为16KB的大小。
InnoDB会将一次读取页的数据存放到一块专用的内存中（buffer pool），下一次读取数据的时候会先去buffer pool里查看，是否已经在里面了，如果在，则直接读取，无需再去磁盘读取，以减少IO带来的性能消耗。当要修改数据的时候，就直接修改buffer pool里的数据，此时内存中的数据和磁盘里的数据就不一致了，内存中的页就叫做脏页。InnoDB后台有专门的线程，会专门把脏页的数据刷到磁盘（刷脏）。修改了一批数据之后，一次性把这些数据刷到磁盘，减少了磁盘IO。架构图：
内存结构：
异常恢复图示：
Log Buffer保存页中修改的记录，防止刷脏到一半导致数据丢失甚至是损坏。支持crash safe崩溃恢复的功能，redo log的数据缓存在Log Buffer中，redo log有一个固定的大小，超过这个大小会把之前的数据覆盖。
四、InnoDB磁盘结构
分为以下几个空间：
系统表空间：默认情况下，所有的表都会公用系统表空间。
独占表空间： 开启之后，一个表占用一个空间，记录数据和索引的信息。
通用表空间
临时表空间
Redo log：持久性。
Undo 表空间：原子性，记录事务发生之前的状态，用于回滚操作，是逻辑日志。
双写缓冲：
当InnoDB往OS写数据的时候，如果写了一半，挂了就会导致部分写失效，会造成数据丢失，数据丢失就可能导致文件损坏，此时拿着损坏的文件做奔溃恢复是没有意义的，所以使用了双写缓冲的技术，分别记录到内存和磁盘里，由于是顺序写入，所以对性能不会带来太大的开销。如果写一半挂了，就可以拿这个页的副本（缓冲）来还原内容，然后再用redo log去恢复数据。
主从复制原理：
更新语句执行流程：
五、索引
一、本质：是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中的数据，类似书的目录。
二、innoDB索引类型：
1、普通索引：没有限制。
2、唯一索引：字段值不允许重复。
3、主键索引：是一种特殊的唯一索引，在唯一索引的条件下增加了不允许这个字段的值为null。
4、全文索引：大文本中匹配字符可以使用全文索引，只有文本类型的字段才可以创建（CHAR VARCHAR text）。
三、思考：用什么数据结构存储索引是最合适的呢？
1、平衡二叉树（AVL Tree）存索引
索引存在磁盘中，访问一次磁盘块就要进行一次IO操作，InnoDB一次读取会读取16K的数据，但是上图展示，一个磁盘块存放的数据远远达不到16K的大小，浪费了大量的空间。并且访问一个磁盘块就要操作一次IO，数据大的时候就要操作很多次，大大降低了性能。
解决方案： 让一个节点存储更多的数据，让节点的度数增多，就由二叉树变为“多叉树”（多路平衡查找树）。
2、多路平衡查找树（Balanced Tree，B树）
如果是无序的插入，会导致不断的分裂与合并，B树的效率已经很高了，但是InnoDB的索引还不是用这种数据结构，而是用了B+树。
3、加强版多路平衡查找树（B+Tree）
数据只存储在叶子节点上，叶子节点之间是一条链表，这就保证了当区间检索的时候，无需回到根节点再往下找，而是直接通过这个链表就可以找到所有的数据
假设一条记录1KB，一个叶子节点存储16条数据，一个bigint为8个字节+指针6个字节=14字节
特点：
1、B Tree能解决的问题，B+Tree都能解决
2、扫库、扫表能力更强
3、磁盘读写能力更强
4、排序能力更强
5、效率更加稳定
InnoDB 索引使用的数据结构：
1、hash
时间复杂度是O（1）
不足：
无序，排序很复杂。
需要进行hash计算，根据key计算value的方式，只能进行等值查询，不能进行区间查询。
若重复的值很多，就会导致很多的hash冲突。
我们并不能自己去创建hash的索引，只用innoDB自己创建的才可以。
2、B+Tree
我们在InnoDB引擎下创建的索引就是使用的B+Tree。
四、InnoDB索引：
.frm：表结构定义的文件，所有的表都有这个文件。
只有一个文件（除了.frm）
.idb：数据和索引都存在这里。
主键索引：
辅助索引：
除了主键索引之外的索引叫做辅助索引或者二级索引。
聚集索引：决定数据存放的顺序的索引。
1、如果有主键索引，根据主键作为聚集索引查找值。
2、如果没有主键，根据找到的第一个唯一索引且不为null的作为聚集索引查找数据。
3、没有索引，InnoDB为每一列都设置了一个隐藏的熟悉ROWID，使用ROWID作为聚集索引。
五、MyISAM索引：
一个表有两个个文件（除了.frm）
.MYI：索引文件。
.MYD：数据文件。
主键索引：
辅助索引：
六、索引的创建和使用原则
一、列的离散度：
公式：count(distinct(column_name)):count(*)；列的不重复的数比所有行数。
从上公式可以看出，列的不重复率越高，离散度就越高。在离散度不高的字段上建立索引，由于重复值比较多，所以扫描的行数也会比较多，当重复值很高的时候，查询的时候可能会放弃索引。所以，索引应该尽可能创建在离散多高的列上。
二、联合索引的最左匹配原则：
在建立联合索引的时候，要将使用最多的字段放在最左边，而且中间不能中断；比如说你建立了一个联合索引A B C，那么查询的时候，使用A，A B，A B C做查询条件是可以使用到索引的（A B C的顺序没关系，OPTIMIZER优化器会自动调整这个顺序），但是如果用B，C，B C，A C做查询条件，那么将用不到索引。
三、回表：
当使用主键索引的时候，只需要遍历一棵B+树，但是如果使用了辅助索引，则需要遍历两棵B+树，多扫描一棵B+树的过程就叫做回表。所以回表会带来额外的性能消耗。
覆盖索引：当除了创建了主键索引还创建了其他的字段的索引的时候，如果查询的字段刚好在这个索引里存在，则可以直接获取字段值，而不需要去回表，这个现象就称为覆盖索引。举个例子：建立了一个联合索引，字段非别是A和B，然后查询：select A,B from xxx where A='xxx' and B='xxx'; 由于A和B就在这个辅助索引树中，所以无需回表。但是如果是select B from xxx where B='xxx';这种情况，优化器去遍历索引的时候，开销比较小的话，也会使用索引。EXPLAIN解析查询语句中出现Using index就说名使用到了覆盖索引
四、创建索引的技巧：
1、在用于where判断，order排序和join的（on）字段上创建索引。
2、索引的个数不要过多，过多索引占用空间，合理创建索引。
3、区分度（散列度）低的字段，例如性别，不要创建索引。
4、频繁更新的值，不要作为主键或者索引，更新会导致B+树的调整，带来大量磁盘页的碎片
5、复合索引把散列值（区分度）高的值放在前面
6、创建复合索引，而不是修改单列索引
7、过长字段，怎么建立索引？
可以使用前缀索引，截取数据的前缀来作为索引。长度、存储空间和区分度之间的关系，取的前缀越长，那么占用的存储空间就越大，取的前缀太短，又会导致区分度过低
8、为什么不建议用无序的值（例如身份证号、UUID）作为索引？
无序的值插入，如果当前页写满了，会导致页的分裂，B+树数据结构的分裂。最好使用自增有序的列作为索引
五、什么时候用不到索引？
1、索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式。
2、字符串不加引号，出现隐式转换。
3、like条件中前面带%。
4、负向查询能用到索引吗？（如：<>， !=， NOT IN）
不一定，取决于优化器。通过计算公式，计算出的结果，如果使用索引效率高则使用，使用索引效率不高则不使用。
七、事务
一、四大特性
1、原子性A：逻辑单位，一系列操作不可拆分，要么都成功要么都失败。InnoDB通过undo log实现事务回滚
2、一致性C：数据在操作之前和操作之后是一致的。比如转账，A转1000块给B，A减少了1000而B只增加了500，这就不满足一致性了
3、隔离性I：多个事务之间互不干扰
4、持久性D：不会因为数据库重启或者奔溃而导致数据丢失。（InnoDB使用redo log实现奔溃后数据恢复，保证数据的持久性）
二、并发带来的问题
脏读：一个事务读取到了另一个事务未提交的数据，导致前后读取的数据不一致。
不可重复读： 一个事务读到了另一个事务提交的数据，两次读取的数据不一致。
幻读：一个事务查询数据，另一个事务提交了一条，第一个事务再次查询多出来了数据，像是出现了幻觉一样。
总结：事务并发的三大问题都是读一致性的问题，必须由数据库提供一定的事务隔离机制来解决。
三、事务隔离级别（）SQL92标准）
未提交读：未解决任何并发问题。
已提交读：解决脏读问题。
可重复读：解决不可重复读问题。
串行化：解决所有问题。
MySQL InnoDB对事务隔离级别的支持程度：
四、事务隔离级别解决方案
1、在读取数据之前加锁，阻止其他事务对数据进行修改（LBCC）
InnoDB锁类型：
行锁级别：
1、共享锁（读锁）：多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改，修改可能会造成死锁。
加锁方式：select * from xxx where id=1 LOCK IN SHARE MODE;
释锁方式：commit/rollback;
2、排它锁（写锁）：排它锁不能与其他锁并存，如一个事务获取 一个数据行的排它锁，其他事务就不能再获取该行的锁（共享锁、排它锁），只有该获取排它锁的事务是可以对数据行进行读取和修改。
加锁方式：
自动：delete/update/insert 默认加锁
手动：select * from xxx where id=1 FOR UPDATE;
释锁方式：commit/rollback;
行锁原理：锁住的是索引。
行锁算法：
1、区间划分
2、记录锁，锁定记录
3、间隙锁，锁定范围
锁定了一个区间后，在这个区间操作数据是不能成功的，解决了幻读的问题。
4、临键锁，锁定范围加记录
表锁级别：
意向共享锁。
意向排它锁。
意向锁是有存储引擎自己维护的，用户无法手动操作意向锁。
意向共享锁和意向排它锁是加表锁的一个标志，一旦这个表有这个标志则加表锁一定失败，无需再去检索全表，来查找是否有数据行被别的事务所锁定，提高了性能
生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定的级别的一致性读取（MVCC）。
每次开启一个事务都会记录一个版本号，在我这个事务创建之前的数据可以查看到，在这个事务之后做的删除新增修改的数据在这个事务中查看不到。
五、事务隔离级别的选择：
1、RR的间隙锁会导致锁定范围的扩大
2、条件列未使用到索引，RR锁表，RC锁行
3、RC的“半一致性”读可以增加update操作的并发性
4、InnoDB事务隔离级别的实现：
八、mysql优化
一、MySQL优化思路与工具：
1、优化层次
2、连接数
连接数越多，带来服务端上下文切换的额外消耗，所以不是连接数越多越好。一般设置为服务器所在机器的 核心数*2+1。所以要合理配置连接数。（默认是151，最大是10万）
连接数（too many connections）超过了服务器设置的max connections的值：
服务端处理：增加连接数。
客户端处理：释放连接数、使用连接池。
3、架构优化
单台服务器：
服务端：
1、单台数据库满足不了读写访问的需求，使用数据库集群部署。
2、集群带来的问题，多个数据库节点的数据如何保持一致？
复制技术（replication）：
、
、
写的请求都通过master节点，读的请求通过slaver节点
基于主从复制的读写分离：
一定程度的减轻单台服务器的访问的压力，但是会有主从复制一致性的问题（有一定的延迟）。
客户端：
配置多数据源，可以使用一些中间件（如MyCat）帮助选择数据源 分库分表：
单表数据过大：
垂直分库：
减少数据库并发访问压力。
水平分库分表：解决存储瓶颈。
带来的问题：数据源的选择。可以使用一些中间件（如MyCat）
4、慢查询日志
默认关闭（因为会消耗一些性能）
mysqldumpslow工具来统计慢查询日志
profile工具也可以查看查询的相关信息，show profiles;查看所有查询的情况；show profile;查看最后执行的一条的情况；show profile for id;查看查询出来的指定一条的执行情况
mysql命令：show processlist;查看每一个连接的情况，每一个客户端的连接，对于服务端来说就是一个线程，这个命令就可以查看到每一个线程的状态。
5、执行计划
explain关键字分析执行情况，从id大的开始执行，如果id一样则从上往下执行。数据量会影响执行顺序，由笛卡尔积决定执行顺序，是一种小表驱动大表的思想。 explain关键字分析相关字段说明：
（1）select_type查询类型：
SIMPLE：简单查询，不包含子查询的查询。
PRIMARY：包含子查询的主查询。
SUBQUERY：内存的子查询。
DERIVED：衍生查询，用到临时表的查询，会把数据临时存在临时表中的查询。
UNION：用到了UNION的查询。
UNION RESULT：代表的是UNION的结果。
（2）type
const：使用了主键索引或唯一索引查到一条数据（好的类型）。
system：查询系统表的时候只有一条数据（用得少）。
eq_ref：关联查询的时候用到了主键索引或唯一索引，对于前面的每个结果动能匹配到后面的结果的时候，除了const之外最好的访问类型。
ref：查询用到了非唯一索引。
range （优化最好能达到range及以上的级别）：对索引的范围访问（between and ,> ,< ），一定用到了索引。
index：拿到索引的所有的值。
all：全表扫描，没有用到索引。
null：没有访问表，直接得到结果。
（3）possible_keys：可能用到的索引。
（4）key：实际用到的索引。
（5）key_len：使用了索引的长度（了解）。
（6）ref：执行过滤的时候使用了哪一个常量或者哪一个字段一起去筛选。
（7）rows：预估要扫描多少行数据（不是精确的值）。
（8）filtered：存储引擎返回数据给SERVER之后，SERVER还要做过滤，剩下有多少满足条件，是一个百分比，高一点比较好（说明在存储引擎层就做好了过滤）。
（9）Extra（额外的信息）
Using INDEX：用到了覆盖索引。
Using where：返回给SERVER层的时候，不是索所用的记录都符合条件，还要在SERVER过滤。
Using filesort：不能使用索引排序，还要用到其他额外的做排序（需要优化）。
Using temporary：用到临时表，需要把结果缓存在内存里。如：对非索引列使用distinct的时候；对非索引列做group by的时候，等等。
6、存储引擎与表结构的优化
存储引擎：
查询插入非常多的用MyIsam。
对数据的一致性要求高的，有并发的、更新多的操作用InnoDB。
临时数据可以用memory。
表结构：
数据量大的表，可以按日期拆分表。
字段定义原则：可以存储数据的最小数据类型。
NOT NULL：一定不允许为null的数据，定义成NOT NULL，或者定义default默认数据。
外键、触发器、视图开发人员不建议使用，约束尽量在代码里做好约束。
大文件、图片：存到文件服务器上，数据库只存URI，一般是相对地址。
总结：
上图所示：从上往下成本增加，收效减小。
不能忽略业务上的优化：
1、如支付宝鼓励用余额宝支付，而不是银行卡。因为使用内部的数据库会更快，用外部接口性能消耗更大。
2、服务降级，高峰期关闭其他业务，已达到降低数据库压力的效果。
3、双十一提前预售，实现分流。
4、mysql满足不了需求了可以使用搜索引擎（ES）大数据，NoSQL等。
