MySQL
基础知识
局部性原理
程序和数据的访问都有聚集成群的倾向，在一个时间段内仅使用其中一小部分（称空间局部性），或者最近访问过的程序代码和数据，很快又会被访问的可能性很大(称时间局部性)。
磁盘预读
磁盘预读的长度一般为页(page)的整数倍
- 页(page)是存储器的逻辑块，操作系统往往将主存和磁盘存储区分割为连续的大小等的块，每个存储块称为一页（在许多操作系统中，页大小通常为4k），主存和磁盘以页为单位交换数据。
MySQL索引
- 索引的目的是为了加速查询
- 索引是存在磁盘中而不是内存中
- 索引的文件存储形式与存储引擎有关
索引文件的结构
hash
哈希表本质上是散列表，可以完成索引的存储，每次在添加索引的时候需要计算指定列的hash值，取模运算后计算出下标，将元素插入下标位置即可。
适合场景：
- 等值查询
表中的数据是无序数据，范围查找的时候比较浪费时间，需要挨个进行便利操作
在企业中多数的查询是范围查询，因此hash表不是特别适合
hash表在使用的时候需要将全部数据加载到内存，比较耗费内存的空间，这点上来说，使用hash表也不是很合适
二叉树
- B树
- B+树
索引的分类
mysql索引的五种类型： 主键索引、唯一索引、普通索引和全文索引、组合索引。通过给字段添加索引可以提高数据的读取速度，提高项目的并发能力和抗压能力
主键索引
主键索引是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键
唯一索引
索引列的所有值都只能出现一次，即必须唯一，值可以为空
普通索引
基本的索引类型，值可以为空，没有唯一性的限制（覆盖索引）
覆盖索引： sql语句中只select索引字段
覆盖索引可避免回表操作
全文索引
MyISAM支持，Innodb在5.6之后支持
全文索引的索引类型为FULLTEXT。全文索引可以在
varchar、char、text类型的列上创建组合索引
多列值组成一个索引，专门用于组合搜索(最左匹配原则)
最左匹配原则：优先匹配前一个字段，例如用
name和age作为组合索引，使用name+age组合查询时会先匹配name再匹配age,如果没有name，那么age也无法参与匹配
索引维护
索引在插入新的值的时候，为了维护索引的有序性，必须要维护
在维护索引的时候需要分以下几种情况：
如果插入一个比较大的值，直接插入即可，几乎没有成本
如果插入的是中间的某一个值，需要逻辑上移动后续的元素，空出位置
如果需要插入的数据页满了，就需要单独申请一个新的数据页，然后移动部分数据过去，叫做页分裂，此时性能会受影响，同时空间的使用率也会降低，除了页分裂之外还包括页合并
尽量使用自增主键作为索引
MySQL整体结构
不同存储引擎，数据文件和索引文件存放的位置是不同的，因此有了分类：
聚簇索引
数据和文件放在一起：
innodb.frm后缀文件存放的是表结构.ibd后缀文件存放的是数据文件和索引文件
注意： mysql的innodb存储引擎默认情况下会把所有的数据文件放到表空间，不会为每一个单独的表保存一份数据文件，如果需要将每一个表单独使用文件保存，需要执行语句： set global innodb_file_per_table = on;
非聚簇索引
数据和索引单独一个文件:
MyISAM.frm: 存放表结构
.MYI: 存放索引数据
.MYD: 存放实际数据
面试题
数据库中最常见的慢查询优化方法是什么？
为什么加索引能优化慢查询
你知道哪些数据结构可以提高查询速度
这些数据结构既然都能优化查询速度，MySQL为何选择使用B+树？
索引下推
在回表前把数据做一次筛选
常见树结构： 多叉树、二叉树、AVL树、红黑树
树结构中，左子树必须小于根节点，右子树必须大于根节点
多叉树(从左到右是有序) –> 二叉树(二分查找) –> AVL树(平衡树) –> 红黑树
二叉树
AVL树：AVL树是一棵严格意义上的平衡树，最高子树和最低子树高度之差不能超过1，因此在进行元素插入的时候会进行1到N次的旋转，严重影响插入的性能
红黑树： 红黑树是基于AVL树的一个升级，损失了部分查询的性能来提升插入的性能，在红黑树中，最低子树跟最高子树之差小于2倍即可，例如最低子树是4层，那么最高子树不能高于8层。在插入时不需要进行N多次的旋转操作，而且还加入了变色的特性来满足插入和查询性能的平衡
二叉树及其N多的变种都不能支撑索引，因为其树的深度无法控制，或者插入数据的性能比较低
B树
B树的特点：
- 所有键值分布在整棵树上
- 搜索有可能在非叶子节点(一个节点下面没有左右子树称为叶子节点，否则为非叶子节点)结束，在关键字全集内做一次查找性能逼近二叉树
- 每个节点最多拥有m（阶）个子树
- 根节点至少有2个子树
- 分支节点至少拥有m/2棵子树（除根节点和叶子节点外都是分支节点）
- 所有叶子节点都在同一层，每个节点最多可以有m-1个key，并且以升序排列
B+树
B+树是在B树的基础之上做的一种优化，变化如下：
- B+树每个节点可以包含更多的节点，这么做的原因有两个，第一是为了降低树的高度；第二是将数据范围变为多个区间，区间越多数据检索越快
- 非叶子节点存储Key，叶子节点存储key和数据
- 叶子节点两两指针相互连接（符合磁盘的预读特性），顺序查询性能更高
注意事项：
- InnoDB是通过B+树结构对主键创建索引，然后叶子节点中存储记录，如果没有主键，那么会选择唯一键，如果没有唯一键，那么会生成一个6位的row_id来作为主键
- 如果创建索引的键是其他字段，那么在叶子节点中存储的是该记录的主键，然后再通过主键索引找到对应的记录，此过程称为回表
MySQL存储引擎
| MyISAM | InnoDB | |
|---|---|---|
| 索引类型 | 非聚簇索引 | 聚簇索引 |
| 支持事务 | 否 | 是 |
| 支持表锁 | 是 | 是 |
| 支持行锁 | 否 | 是 |
| 支持外键 | 否 | 是 |
| 支持全文索引 | 是 | 是(5.6后支持) |
| 适合操作类型 | 大量select | 大量insert、delete、update |
MySQL架构
客户端 –> 连接器（管理连接，权限验证） –> 查询缓存 –> 分析器（词法分析，语法分析） –> 优化器（执行计划，索引选择） –> 执行器（操作引擎，返回结果） –> 存储引擎（存储数据，提供读写接口）
连接器、查询缓存、分析器、优化器、执行器属于服务端
连接器
连接器负责跟客户端建立连接，获取权限、维持和管理连接
- 用户名密码验证 - 查询权限信息，分配对应权限 - 可以使用`show processlist`语句查看现在的连接 - 如果太长时间没有动静就会自动断开，通过`wait_timeout`控制，默认8小时连接可以分为两类：
- 长连接： 推荐使用，但要周期性断开长连接 - 短连接查询缓存
当执行查询语句的时候，会先去查询缓存中查看结果，之前执行过的sql语句及其结果可能以
key-value的形式存储在缓存中，如果能找到则直接返回，如果找不到，就继续执行后续的阶段不推荐使用查询缓存：
- 查询缓存的失效比较频繁，只要表更新，缓存就会清空
- 缓存对应新更新的数据命中率比较低
分析器
词法分析：MySQL需要把输入的字符串进行识别每个分代表什么意思。把
字符串T识别成表名T；把字符串ID识别成列ID语法分析： 根据语法规则判断这个SQL语句是否满足MySQL的语法，如果不符合就会报错
You have an error in your SQL synta
优化器
在具体执行SQL语句之前，要先经过优化器的处理： 当表中有多个索引的时候，决定用哪个索引；当SQL语句需要做多表关联的时候，决定表的连接顺序等等
不同的执行方式对SQL语句的执行效率影响很大
RBO： 基于规则的优化
CBO： 基于成本的优化
执行器
执行语句，返回结果
存储引擎
存储数据
MySQL日志
Redo日志 —— InnoDB存储引擎的日志文件
当发生数据修改的时候InnoDB引擎会先将记录写到redo log中，并更新内存，此时更新就算是完成了，同时InnoDB引擎会在何时的时机将记录操作到磁盘中
Redolog是固定大小的，是循环写的过程
有了Redolog后，InnoDB就可以保证及时数据库发生异常重启，之前的记录也不会丢失，叫做crash-safe
Undo log
Undo log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用Undo log来实现多版本并发控制（简称MVCC）
在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo log）。然后进行数据修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo log中的备份将数据恢复到事务开始之前的状态
Undo log是逻辑日志，可以理解为：
- 当delete一条记录时，undo log中会记录一条多赢的insert记录
- 当insert一条记录时，Undolog中会记录一条对应的delete记录
- 当update一条数据时，它记录一条对应相反的update记录
Redo log 和 Undo log 都是InnoDB独有的日志，MyISAM中没有。Redo log叫前滚日志，保证数据库回到发生异常(比如突然断电)前的持久化状态；Undo log叫回滚日志，是记录数据更新后的持久化状态。
Binlog —— 服务端的日志文件
Binglog是服务端(server层)的日志，主要做mysql功能层面的事情
与Redo日志的区别：
- Redo日志是InnoDB独有的，Binlog是所有引擎都可以使用的
- Redo是物理日志，记录的是在某个数据页上做了什么修改；Binlog是逻辑日志，记录的是这个语句的原始逻辑
- Redo是循环写的，空间会用完；Binlog是可以追加写的，不会覆盖之前的日志信息
MySQL默认不开启Binlog
Binlog会记录所有的逻辑，并且采用追加写的方式，一般在企业中数据库会有备份系统，可以定期执行备份，备份的周期可以自己设置。
恢复数据的过程：
- 找到最近一次的全量备份数据
- 从备份的时间点开始，将备份的binlog取出来，重放到要恢复的那个时刻
MySQL执行计划
使用explain + SQL语句来模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的
执行计划中包含的信息（属性）
id
select查询的序列号包含一组数字，表示查询中执行select子句或者操作表的顺序
id号分为三种情况：
如果id相同，那么执行顺序从上到下
如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同和不同的同时存在，相同的可以认为是一组，从上往下顺序执行。在所有组中，id值越大优先级越高，越先执行
select_type
SIMPLE: 简单查询PRIMARY: 最外层查询UNION: 若第二个语句在union之后则标记为unionDEPENDENT UNION: 跟union关联，外层查询依赖内层查询结果UNION RESULT: 从union表中获取结果selectSUBQUERY: 子查询DEPENDENT SUBQUERY: 子查询是结果集合是DEPENDENT SUBQUERY,单个值是SUBQUERYDERIVED: from子句中出现的子查询，也叫做衍生类UNCACHEABLE SUBQUERY: 表示使用子表查询结果不能被缓存UNCACHEABLE UNION: 表示union的查询结果不能被缓存table
对应正在访问哪一个表、表名或者别名，可能是临时表挥着union合并结果集
- 如果是具体表名，则表明从实际的物理表中获取数据，当然也可以是表的别名
- 表名是drivedN的形式，表示使用了id为N的查询产生的衍生表，当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id
partitions
分区
type
访问类型，表示当前sql语句执行时是以何种方式访问我们的数据，访问类型有很多，效率从高到低分别是：
sysytem > const > eq_ref > ref > fulltext > ref_of_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般情况下，得保证查询至少达到
range级别，最好能达到refALL: 全表扫描，一般情况下，出现这种sql语句而且数据量比较大的话，那么久需要进行优化
index： 全索引扫描，比All效率好。主要两种情况，一种是当前的查询是覆盖索引，即我们需要的数据在索引中就可以索取；第二种者是使用了索引进行排序，这样就避免数据的重排序
range： 表示利用索引联合查询的时候限制了范围，在指定范围内进行查询
index_subquery: 利用索引关联子查询，不需要再扫描全表
unique_subquery
- index_merge
- ref_of_null
- fulltext
ref
使用了非唯一性索引进行查找eq_ref
const
这个表至多有一个匹配行sysytem
表里面只有一行记录（等于系统表）
possible_keys
显示可能会用到的索引
key
实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠
key_len
索引中使用的字节数
ref
显示索引的哪一列被使用了，如果可能的话，是一个常数
rows
根据表的统计信息及索引使用情况，大致估算出找出所需记录读取的行数，此参数很重要，直接反映的sql找了多少数据，在完成目的的情况下，越少越好
extra
包含额外的信息
MySQL的锁机制
锁是计算机协调多个进程或线程并发访问某一资源的机制。
不同的存储引擎支持不同的锁机制。
MyISAM和MERMORY存储引擎采用的是表级锁(table-level locking)InnoDB存储引擎支持行级锁(row-level locking)，也支持表级锁，但默认情况下采用行级锁
表级锁： 开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突的概率最高，并发度最低
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
仅从锁的角度来说，表级锁更适合以查询为主，只有少量按索引条件更新数据的应用，如web应用；行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理(OLTP)系统
OLTP: ONLINE TRANSIATION PROCESS 联机事务处理
OLAP: ONLINE ANALYSIS PROCESS 联机分析系统
MyISAM表锁
MySQL的表级锁有两种模式： 表共享读锁(Table Read Lock)、表独占写锁(Table Write Lock)
对MyISAM表的读操作，不会阻塞其他用户对同一张表的读请求，但会阻塞对同一表的写请求；对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间、以及写操作之间是串行的
MyISAM写阻塞读
当一个线程获得对一个表的写锁之后，只有持有锁的线程可以对表进行更新操作。其他线程的读写操作都会等待，直到锁释放为止。
lock table xxx write;（锁表语句，给表xx加写锁）
…执行一些操作…
unlock tables;(释放锁)
MyISAM读阻塞写
一个session使用lock table给表加读锁，这个session可以锁定表中的记录，但更新和访问其他表都会提示错误，同时另一个session可以查询表中的记录，但更新就会出现锁等待。
lock table xxx read; （锁表语句，给表加读锁）
unlock tables;（释放锁）
注意： MyISAM在执行查询语句之前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此用户一般不需要使用命令来显示加锁。
InnoDB锁
- 事务及其ACID属性
事务是由一组SQL语句组成的逻辑处理单元，事务具有四属性，通常称为事务的ACID属性。
原子性(Actomicity): 事务是一个原子操作单位，其对数据的修改，要么全都执行，要么全都不执行
一致性(Consistent): 在事务开始和完成时，数据都必须保持一致状态
隔离性(Isolation): 数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行
持久性(Durable): 事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持
并发事务带来的问题
相对于串行处理来说，并发事务处理能力大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多用户的并发操作，但与此同时会带来一下问题：
- 脏读： 一个事务正在对一条记录做修改，在这个事务提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系，这种现象被形象地叫做脏读
- 不可重复读： 一个事务正在读取某些已经发生了改变或某些记录已经被删除了!这种现象叫做“不可重复读”
- 幻读： 一个事务按相同的查询条件重新读取以前检所过的数据，却发现其他事务插入了满足其条件的新数据，这种现象就称为”幻读“
以上出现的问题都是数据库读一致性的问题，可以通过事务的隔离机制来进行保证
InnoDB的行锁模式及加锁情况
共享锁(s): 又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能在对A加S锁，而不能加X锁，知道T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何改动
排它锁(x): 又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集 共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁
MySQL InnoDB引擎默认的修改数据语句：
update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何类型锁。如果加排他锁可以使用select ... for upadte;语句，加共享锁可以使用select ... lock in share mode;语句。所以加排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ... from ...查询数据，因为普通查询没有任何锁机制
InnoDB行锁实现方式
给索引上的索引加说来实现行锁。这一点上MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着： 只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁
总结
对于MyISAM的表锁：
- 共享读锁(s)之间是兼容的，但共享读锁(s)与排他写锁(X)之间，以及排他写锁(X)之间是互斥的，也就是说读和写是串行的。
- 在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表查询和超如的锁争用问题
- MyISAM默认的锁调度机制是写优先，但这不一定适合所有应用，用户可以通过设置
LOW_PRIORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用 - 由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突
对于InnoDB表锁：
- InnoDB行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁
- 在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同
用户可以通过设计和SQL调整等措施减少锁冲突和死锁：
- 尽量使用较低的隔离级别： 精心设计索引并尽量使用索引访问数据，使加锁更加准确，从而减少锁冲突的机会
- 选择合理的事务大小，小失误发生锁冲突的几率也更小
- 给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时在请求排他锁，这样容易产生死锁
- 不同的程序访问同一组表时，应尽量约定相同的顺序访问各表，对一个表而言尽可能以固定的顺序存取表中的行，这样可以大大减少死锁的机会
- 尽量使用相等条件范文数据，这样可以避免间隙锁对并发插入的影响；不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
- 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能