mysql核心概念

一、基本架构

1、连接器

连接器负责跟客户端建立连接，获取权限、维持和管理连接

用户名、密码校验
查询权限信息，分配对应的权限
可以使用show precesslist查看现在的连接情况
如果太长时间没有动静，就会自动断开，通过wait_timeout控制，默认8小时

连接可以分为两类

长连接，推荐使用，但是要周期性的断开长连接
短连接

2、查询缓存

当执行查询语句的时候，会先去查询缓存中查看结果，之前执行过的sql语句及其结果可能以key-value的形势存储在缓存中，如果能找到则直接返回，找不到，就继续执行后续的阶段

不推荐使用查询缓存：

查询缓存的失效比较频繁，只要表更新，缓存就会清空
缓存对应新的更新数据命中率比较低

3、分析器

词法分析：mysql需要把输入字符串进行识别每个部分代表什么意思

把字符串T识别成表明T
把字符串id识别成列id

语法分析：根据语法规则判断这个sql语句是否满足mysql语法，如果不符合就会报错You have an error in your SQL synta

4、优化器

在具体执行sql语句之前，要先经过优化器的处理

当表中有多个索引的时候，决定用哪个索引
当sql语句需要做多表关联的时候，决定表的链接，顺序

不同的执行方式对sql语句的执行效率影响很大，目前有两种优化策略

RBO：基于规则的优化
CBO：基于成本的优化

show status like 'last_query_cost'查询最后一条语句使用的成本，预估的数据页值

5、总结

连接器：管理连接，权限校验
分析器：词法分析，语法分析
优化器：执行计划，索引选择
执行器：操作引擎，返回结果
存储引擎：存储数据，提供读写接口

二、数据结构

1、hash

1 2	基于哈希表的实现，只有精确匹配索引所有列的查询才有效在mysql中，只有memory的存储引擎显式支持哈希索引

2、二叉树

1 2	如果数据有序出现单边增长的情况，会单边倾斜出现链表结构会应为树的深度过深而造成io次数变多，影响数据读取效率

3、红黑树（二叉平衡树）

1
2

在二叉树的基础上多了树平衡，当出现单边失衡时，会进行转换，形成节点，但是数据量大时，层级会很深
会应为树的深度过深而造成io次数变多，影响数据读取效率

4、B树

对顶级节点进行横向扩展，叶节点具有相同的深度，节点存放数据
特点：
	1、所以键值分布在整棵树中
	2、搜索有可能在非叶子节点结束，在关键字全集内做一次查找，性能逼近二分查找
	3、每个节点最多拥有m个子树
	4、根节点至少有2个子树
	5、分支节点至少拥有m/2颗子树（除根节点和叶子节点外都是分支节点）
	6、所有叶子数据存储在每个节点上节点都在同一层、每个节点最多可以有m-1个key，并且以升序排列
缺点：数据存储在每个节点上，而mysql每次读取16K的数据，每个磁盘区块只有4K，当节点因存储数据而造成数据量变大的情况下，每次可读取的索引值也就变少了

5、B+树

1	非叶子结点不存储data，数据只在叶子结点存储，这样mysql每次io读取的记录数能够得到较大提升

三、执行引擎

MyISAM与Innodb对比如下

区别	Innodb	MyISAM
事务	支持	不支持
外键	支持	不支持
索引	即支持聚簇索引又支持非聚簇索引	只支持非聚簇索引
行锁	支持	不支持
表锁	支持	支持
存储文件	frm，ibd	frm,myi,myd
具体行数	每次必须要全表扫描统计行数	通过变量保存行数（查询不能带条件）
适合操作类型	大量insert、delete、update	大量select

如何选择？

1、是否需要支持事务，如果需要选择innodb，如果不需要选择myisam

2、如果表的大部分请求都是读请求，可以考虑myisam，如果既有读也有写，使用innodb

现在mysql的默认存储引擎已经变成了Innodb,推荐使用innodb

四、索引

1、结构类型

1.1、聚簇索引

innodb使用该索引结构，数据文件和索引文件存放在一起

1.2、非聚簇索引

myisam使用该索引结构，数据文件和索引文件分开存放

2、使用类型

主键索引、唯一索引、普通索引、组合索引、全文索引

3、相关概念

3.1、回表

1	当使用普通索引时，叶子结点查询到主键，根据主键找到主键索引叶子结点的数据

3.2、索引覆盖

1	一般在组合索引的使用情况下出现的几率比较大，查询的列与索引的列相对应，可以在索引叶子结点找到匹配的数据，不需要再次回表查询

3.3、索引下推

1	高级别版本，如果多个条件进行查询，会一起带到执行引擎进行判断过滤，不需要匹配一个索引之后再回到server层进行判断，减少io交互

3.4、最左匹配

1	组合索引在使用时，需要注意必须包含该组合最左边的列，否则无法使用该索引

4、常见优化

1、全值匹配
2、最左匹配
3、不在索引条件上做函数操作
4、范围查找条件右边的列不会使用到索引
5、尽量使用覆盖索引
6、‘不等于’操作会导致全表扫描
7、is null或is not null无法使用索引
8、like 全模糊无法使用索引，应使用右模糊
9、参数类型应与数据库类型匹配
10、or会让索引失效
11、join语句的关联条件尽量使用索引

5、order优化

5.1、双路排序

读取排序字段，在buffer进行排序，再从磁盘读取其他查询字段

5.2、单路排序

4.1之后，一次性读取排序字段和查询字段，在buffer排序之后直接返回，一次性读取两类数据使用更容易超出buffer总大小

查询的字段总大小小于max_length_for_sort_data的限制值（1024B），且排序字段不是TEXT|BLOB时，会使用单路排序

总结如下：

存在问题：
	sort_buffer的容量有限，如果读取的数据超过了buffer的默认大小262144B（256KB），造成需要多次读取，创建templ文件再进行合并排序，io次数增加，性能下降；
	
优化：
	可以通过调整sort_buffer_size、sort_buffer_size的大小来进行优化
	不管单路还是双路，提高sort_buffer_size都能提高效率，但需要根据系统能力去调整
	提高max_length_for_sort_data的大小会影响使用何种排序算法

6、group优化

1
2
3

group by 实际上是先排序后分组
无法使用到索引列时，可以考虑增大max_length_for_sort_data和sort_buffer_size参数
where优先级高于having，条件尽量在where进行限制

五、局部性原理和磁盘预读

	由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分之一，因此为了提高效率，要尽量减少磁盘 I/O。
	为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。

局部性原理：
	当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。
	
磁盘预读：
	由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高 I/O 效率。
	预读的长度一般为页（4K）（page）的整倍数。

六、页分裂

页可能填充至100%，在页填满了之后，下一页会继续接管新的记录。但如果下一页也满了，数据又需要按顺序插入，就会产生页分裂。

InnoDB的做法是（简化版）：
    创建新页
    判断当前页可以从哪里进行分裂（记录行层面）
    移动记录行
    重新定义页之间的关系
    
等于创建了一个新页，并且把当前页的数据迁移部分到新页中，然后插入在当前页和下一页的中间。

七、页合并

1
2
3

当你删了一行记录时，实际上记录并没有被物理删除，记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。

当页中删除的记录达到MERGE_THRESHOLD（默认页体积的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

八、Sql Join

MySQL是只支持一种Join算法Nested-Loop Join(嵌套循环连接)，并不支持哈希连接和合并连接，不过在mysql中包含了多种变种，能够帮助MySQL提高join执行的效率。

1、Simple Nested-Loop Join

这个算法相对来说就是很简单了，从驱动表中取出R1匹配S表所有列，然后R2，R3,直到将R表中的所有数据匹配完，然后合并数据，可以看到这种算法要对S表进行RN次访问，虽然简单，但是相对来说开销还是太大了。

2、Index Nested-Loop Join

索引嵌套联系由于非驱动表上有索引，所以比较的时候不再需要一条条记录进行比较，而可以通过索引来减少比较，从而加速查询。这也就是平时我们在做关联查询的时候必须要求关联字段有索引的一个主要原因。

这种算法在链接查询的时候，驱动表会根据关联字段的索引进行查找，当在索引上找到了符合的值，再回表进行查询，也就是只有当匹配到索引以后才会进行回表。至于驱动表的选择，MySQL优化器一般情况下是会选择记录数少的作为驱动表，但是当SQL特别复杂的时候不排除会出现错误选择。

在索引嵌套链接的方式下，如果非驱动表的关联键是主键的话，这样来说性能就会非常的高，如果不是主键的话，关联起来如果返回的行数很多的话，效率就会特别的低，因为要多次的回表操作。先关联索引，然后根据二级索引的主键ID进行回表的操作。这样来说的话性能相对就会很差。

3、Block Nested-Loop Join

在有索引的情况下，MySQL会尝试去使用Index Nested-Loop Join算法，在有些情况下，可能Join的列就是没有索引，那么这时MySQL的选择绝对不会是最先介绍的Simple Nested-Loop Join算法，而是会优先使用Block Nested-Loop Join的算法。

Block Nested-Loop Join对比Simple Nested-Loop Join多了一个中间处理的过程，也就是join buffer，使用join buffer将驱动表的查询JOIN相关列都给缓冲到了JOIN BUFFER当中，然后批量与非驱动表进行比较，这也来实现的话，可以将多次比较合并到一次，降低了非驱动表的访问频率。也就是只需要访问一次S表。这样来说的话，就不会出现多次访问非驱动表的情况了，也只有这种情况下才会访问join buffer。

在MySQL当中，我们可以通过参数join_buffer_size来设置join buffer的值，然后再进行操作。默认情况下join_buffer_size=256K，在查找的时候MySQL会将所有的需要的列缓存到join buffer当中，包括select的列，而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。

九、锁

乐观锁

乐观锁并不是数据库自带的，如果需要使用乐观锁，那么需要自己去实现，一般情况下，我们会在表中新增一个version字段，每次更新数据version+1,在进行提交之前会判断version是否一致。

悲观锁

mysql中的绝大部分锁都是悲观锁，按照粒度可以分为行锁和表锁

行锁

共享锁：当读取一行记录的时候，为了防止别人修改，则需要添加S锁
排它锁：当修改一行记录的时候，为了防止别人同时进行修改，则需要添加X锁

	X	S
X	不兼容	不兼容
S	不兼容	兼容

记录锁：添加在行索引上的锁
间隙锁：锁定范围是索引记录之间的间隙，针对可重复读以上隔离级别
临键锁：记录锁+间隙锁

表锁

意向锁：在获取某行的锁之前，必须要获取表的锁，分为意向共享锁，意向排它锁
自增锁：对自增字段所采用的特殊表级锁

锁模式的含义

IX：意向排它锁

X：锁定记录本身和记录之前的间隙

S：锁定记录本身和记录之前的间隙

X,REC_NOT_GAP：只锁定记录本身

S，REC_NOT_GAP：只锁定记录本身

X，GAP：间隙锁，不锁定记录本身

S，GAP：间隙锁，不锁定记录本身

X，GAP,INSERT_INTENTION：插入意向锁