MySQL基本架构
-
连接层
-
服务层
-
存储引擎层
连接层
-
基本作用
-
当我们客户端要链接到MySQL服务器的3306端口，必须要和服务端建立连接，此时连接层就是做这件事情的
-
-
主要功能
-
在连接层，主要就是负责管理所有的连接，验证客户端的身份和权限（连接器的权限验证主要是验证用户是否有权连接到数据库服务器）
-
-
连接数
-
show variables like 'max connections';--在5.7版本中默认是151个，最大可以设置成100000
-
服务层
-
查询缓存
-
众所周知，缓存十分的鸡肋，当对缓存中的数据进行修改更新时，这个缓存就失效了，于是在5.7的版本将缓存默认进行关闭了，更是在8.0的版本直接废除这个缓存
-
-
解析器
-
基本作用
-
解析SQL语句，判断SQL语句是否符合语法以及后面的权限检测（解析器层的权限验证主要是验证用户对数据库中的各种对象（表、列、视图等）的访问权限，确保用户只能执行其具有权限的操作。）
-
-
分类
-
词法解析
-
将SQL语句打碎，转成一个一个的关键单词，然后交给语法解析器去构建语法树
-
-
语法解析
-
去检查语法是否正确，比如，where是不是写出where1,from写成from1. 表名、列名是否存在、用户是否有操作权限等等。 如果写错了直接抛出错误，否则解析成语法树
-
-
-
-
预处理器
-
作用
-
何为预处理，拿工作中的场景为例。一个查询接口，sql语句都是一样的，但是每次查询的参数都不一样，所以我们只需变更查询的参数即可 简单来说，就是我们在拼接sql语句的时候，将用户的输入和语句拼接成一个sql交给mysql去执行
-
-
好处
-
每次执行语句时解析语句的开销更少，通常数据库应用程序处理大部分几乎相同的语句，仅更改WHERE 查询和删除、SET 更新和 VALUES 插入等子句中的文字或变量值。
-
防止SQL注入攻击-联想到mybatis#跟$符号的区别，与MySQL的预处理同理，#符号就会去解析变成参数，然后进行预处理，能防止sql注入，并且必须传入参数，而$就直接将用户传入的条件进行拼接，出现sql注入的问题
-
-
-
优化器
-
决定SQL语句怎么执行，具体怎么执行会有很多种方式，比如走哪个索引，语句是不是可以优化，做哪些优化，哪种执行方式、路径更快，优化器可以生成最优的执行计划，以最高效的方式执行查询
-
-
执行器
-
根据表设置的存储引擎，调用对应存储引擎的API接口获取数据
-
存储引擎层
-
定义
-
存储引擎就是我们数据真正存放的地方，再往下就是内存或磁盘，在MySQL中支持不同的存储引擎
-
-
类型
-
InnoDB
-
MySQL默认的存储引擎，支持事务，支持外键，支持行级锁，支持崩溃恢复，对数据的完整性和一致性要求比较高，同时使用聚簇索引，将数据存储在索引的叶子节点上，提高了查询性能。
-
-
MylSAM
-
支持表级锁，不支持事务，查询性能比较高，适用于读多写少的场景
-
-
Memory
-
数据存储在内存中，但是不支持持久化，也支持表级锁，查询速度非常之快
-
-
-
选择
-
如果对数据一致性要求比较高，需要事务的支持，则可以选择InnoDB
-
如果数据查多写少，对查询性能要求比较高，则可以选择MylSAM
-
如果需要一个用于查询的临时表，则可以选择Memory
-
InnoDB总体架构
InnoDB磁盘结构
-
我们的innodb存储引擎存储到的数据总归来说还是需要一个位置进行存储的，存到哪里，如何去存，就是由我们的表空间来决定的
-
独立表空间
-
存储内容
-
独立表空间存储单个表的数据和索引，并存储在文件系统的单个数据文件中
-
-
优势
-
相比于共享表空间，独立表空间的元素被删除会释放给操作系统，而共享表空间，表被截断或者删除占用的磁盘大小空间是不变化的
-
性能更好，因为操作的都是一张表的内容，文件大小会更小
-
相比于共享表空间文件大小收到64T的限制，而每一个独立表空间的文件大小都可以是64T，这也为单个表的大小增长提供充足的空间
-
-
缺点
-
正因为没一张表都使用一个独立的空间来存储，所以会有空间的浪费，也会有更多的文件碎片，文件过多也需要更多的文件句柄
-
-
-
共享表空间
-
系统表空间
-
系统表空间主要存储了关于数据库对象的元数据信息、双写缓冲、插入缓冲、回滚段和 undo 日志
-
-
通用表空间
-
与系统表空间类似，通用表空间是能够为多个表存储数据的共享表空间。但是这个空间可以自己进行维护管理。----->指定哪些表被放到哪些通用表空间
-
-
-
临时表空间
-
在我们的SQL中，可能会用到临时表的逻辑，就是在会话中创建一个表供会话使用，会话关闭后，表就失效，而临时表空间就是用来存储这些临时表
-
-
表空间的存储结构
-
图解
-
-
segement段
-
段是表空间的分区，一个表空间包含多个段，常见的段有数据段，索引段，8.0之前还要回滚段
-
-
extent区
-
区来管理页，当页的大小在16K以下，一个区的大小是1M，32K的页区大小为2M，64K大小的页区大小是4M，磁盘释放与分配都是以区为单位，一个extent下最少可以存储64个页
-
-
page页
-
默认大小为16K，一个行最大大概为页的一半
-
-
row行
-
数据存储的最小单位，有四种基本格式
-
①REDUNDANT ：冗余行格式，主要是旧版本mysql的兼容，数据和行索引信息分开存储，某些查询操作会快，但是需要额外的空间，所以是之前老版本的格式设计
-
②COMPACT 减少了存储行间，官网说大约20%，但是增加了cpu的负荷。导致一些查询的性能问题
-
③DYNAMIC动态行格式 ：该行格式允许长度可变，所以会根据情况来决定是否需要更多空间，5.7后的默认行格式
-
④COMPRESSED 压缩行格式 ：对COMPACT进行了压缩，减少了存储空间使用，比如text 长文本 会进行压缩，但是检索的时候，必须 进行解压，牺牲了cpu。
-
-
-
InnoDB内存架构
-
内存缓存结构bufferPool
-
背景
-
数据不管怎么样，都会落到我们的表空间中，如果每次检索都从磁盘去获取数据，想必会很慢，所以innodb为了性能，采取内存缓存机制，在内存中去缓存对应的数据，这个区域叫做bufferpool
-
-
作用
-
缓冲池是在主内存的一块区域，在innodb访问表和索引数据时，允许直接从内存中访问频繁使用的数据，从而加快处理速度
-
-
-
内存与数据交互机制
-
页加载机制
-
内存与磁盘进行交互是通过最小单位页进行交互的，为什么选择页进行交互，而不是选择行或者是段
-
-
取数据的流程
-
假如有1.2.3.4.5.6.7条数据，我们要查询id为2的数据，首先去访问内存，如果内存中没有则从磁盘中取数据，然后加载到内存，如果bufferpool中有数据则直接返回，这样下次在查询id为3.4.5这类数据的时候，就不需要从磁盘进行加载
-
-
预读机制
-
作用
-
预读请求是一种IO请求，用于在缓存池异步预取多个页面，在访问某个页面的时候，预计需要段中其他页面，则将这个段中所有页面加载到缓冲池
-
-
分类
-
线性预读
-
按照访问顺序的页来执行页加载，某个段的里面的页面有多少页按照顺序访问了，那么就将这个段中所有的页预加载到缓冲池中
-
-
随机预读
-
根据缓冲池已有的页进行加载，如果在缓冲池中找到了来自同一个段中的连续13个页，innodb则会发送异步请求去加载这个段中其他的页
-
-
-
-
-
BufferPool内存管理
-
背景
-
假设我们查询的页与预加载的页全部加载到我们的内存中，久而久之内存空间也会容纳不下这么多页，不能无休止的去往我们的缓冲池进行加载，需要对缓冲池中这些页进行一个管理
-
-
管理算法
-
传统LRU
-
最近最少使用，就是使用的最少的页就慢慢的进行淘汰，实际上就是使用链表进行保存，新的页数据添加到链表的头部，那些旧的未被访问的就慢慢的到链表的尾部淘汰出去
-
-
变种LRU
-
背景
-
因为innodb中有预加载机制，可能我们没有访问并且不需要访问的页也一并加载到缓冲池中了，但是短时间淘汰不了，同样的会占用内存空间，效率也不见得有提升
-
-
流程
-
innodb采用了一个及其聪明的淘汰机制，就是针对于那些预加载进来的页，虽然你同区的页被访问但是访问不到你，我也不把你放到链表的头部，而是放到链表的中间，用到的时候在转移到链表的头部去避免淘汰，这样慢慢的同段中没有访问到的页是不是也能更快的被淘汰掉呢
-
-
-
-
-
内存与磁盘同步机制
-
背景
-
很简单，只要设计到内存与磁盘两份数据的，必定会产生一致性的问题，如同redis中的一致性作为重点，mysql的作者也针对内存与磁盘数据的同步专门设定一个机制
-
对于这种同步机制，我们会有一个疑问，就是反正不管如何，我们内存中变化的数据一定是要保存到磁盘的，为什么要出来一个独特的机制来同步这两者，而是直接数据一变动就更新到磁盘更加省心呢
-
第一个就是如果每次更改数据就同步去刷新到磁盘，我们内存中的数据是以页进行保存的，同步到磁盘也势必要同步页，可能只是简单的更改一行数据，却要在磁盘中更新整个页，岂不是杀鸡用牛刀？
-
第二个就是我们不清楚这个需要变更的数据具体在磁盘中的哪一个位置，我们对数据进行变更是一个随机IO，比较消耗性能，所以每次更新buffer pool中的数据就同步到磁盘不可取
-
-
-
流程
-
依据背景，我们针对数据的同步采取的是一个异步刷盘的机制，就是先将更改更新到内存，然后异步去把磁盘中的脏数据刷新
-
-
时机
-
脏页自适应
-
内存数据的脏页达到一定的比例或者一定的数量就会去刷新
-
-
redoLog自适应
-
根据redolog页产生的速度来刷新
-
-
空闲时间刷新
-
只要异步线程是空闲的就去刷新脏页
-
-
-
双写缓冲区
-
背景
-
我们的page页会异步刷新到磁盘，但是一个页的大小是16K，而磁盘和操作系统交互的大小是4K，一个页必须经过多次的异步刷新才能完整的刷新到磁盘，但是如果断电或者是其他的意外情况，那么这个刷新的页不就作废了吗，所以MySQL提供了一个双写的机制，做备份来保证数据的安全
-
-
机制
-
何为双写，就是page页刷新的时候，将这个page页的数据写到别的地方，当出现意外的情况时，就使用其他的备份来保证数据的一致性和持久性
-
-
核心思想
-
双写机制在进行的时候会占用一部分内存和磁盘的空间，同时也会导致一定的性能损失，但是为了保障数据的安全性和可靠性做的一定的权衡
-
-
-
changeBuffer
-
作用
-
更改缓冲区作为一种特殊的数据结构，他会缓存对二级索引页面的更改，稍后会在其他读取操作时将页面加载到缓冲池进行合并，但是这个更改缓冲区不适用于聚簇索引，因为主键索引的更改必须要做个验证，与磁盘进行交互，所以不适用于这种延时的缓存同步
-
-
适用场景
-
更加适合于操作比较多的场景，而不适用于读多的场景，因为读多的场景本身就要从磁盘中加载到内存，所以针对读多的场景change-buffer就比较鸡肋了
-
-
-
-
RedoLog
-
介绍
-
RedoLog又称作重做日志，当我发生异常情况，导致数据丢失的时候，可以从RedoLog日志中找到想要的数据。 因为InnoDB的数据操作是只会实时去操作我们的bufferpool的page页的，然后通过其他的一些异步方式将bufferpool中的数据同步到磁盘，所以， 数据丢失是很容易产生的。那么就需要我们的RedoLog来保证数据的不丢失。它属于InnoDB存储引擎层面实现。
-
-
作用
-
同样也是作为数据一致性与持久性的保障，应对意外情况导致缓存没有同步到磁盘的情况
-
-
格式
-
-
type:操作类型 插入、修改还是删除 spaceId:表空间ID page number：所在的页 data: 修改的前后数据 记录在某个偏移量发生了什么变更的日志格式，也叫做物理日志
-
-
写入方式
-
RedoLog的目的，是去保证我的数据页在内存，但是还没有同步到磁盘的时候，宕机导致的数据丢失。 所以在sql语句提交之前，肯定会保存改动的Redolog到redolog file文件。 当改动数据还在内存，没有同步到磁盘就宕机的时候，会通过RedoLog文件里面找到改动点，进行同步到磁盘。但是RedoLogFile的大小是固定的，写入的日志数量是有限制的，并且它的目的只是去保证数据不丢失，数据落盘了，这些日志就没有用了，所以RedoLog采用的是循环覆盖写的方式。 当RedoLog空间的大小满了的时候，会对以前数据进行覆盖。为了保证被覆盖的数据不会丢失，在Redolog快满的时候，也会去进行刷脏，刷脏后， 对应的Redolog日志就无效可以被覆盖，所以RedoLog的写是循环写的方式。 当数据同步到磁盘后，RedoLog就会进行删除。 简单而言，当32个文件写满之后，又会从第一个文件去循环覆盖写入此时还会有一个疑问，就是为什么不实时的从数据刷新到磁盘，也就是从page页去同步到磁盘，而是使用redoLog
-
-
疑点
-
之前不是提到过异步刷盘的方式来保证数据的一致性吗，为什么又需要rediLog的方式来保证数据的一致性呢，不选择异步实时的数据同步，而是再造一个物理日志进行数据同步
-
第一点就是因为bufferPool跟磁盘交互的最小单位是page，所以，只要page里面改动一条数据，整个page都会进行跟磁盘同步，导致不必要的同步。 而RedoLog只会同步某些数据变动的记录，更加有针对性
-
第二点就是bufferpool改动的数据是随机的，不是顺序的，随机IO的性能比较慢，但是RedoLog是一直往上加，是顺序IO，速度比数据page同步要快。
-
-
-
RedoLogBuffer
-
追求性能在取舍安全
-
在redolog中又添加一层缓存，使得redolog日志不立马写入磁盘，而是追求性能设立一个缓冲区，将redolog的变更延迟刷新到磁盘，去做一个性能优化
-
-
与磁盘同步的方式
-
①每次事务提交时，将日志刷新到磁盘，安全性高，能够保证持久性，默认配置
-
②每秒从内存写到操作系统，并且刷新（fsync()）到硬盘，可能会导致数据丢失
-
③每次写入logbuffer 并且写到操作系统，但是每秒fsync()到磁盘，最终刷新交给操作系统操作，只要操作系统不挂，也能保证持久性，但是操作系统挂了，数据没刷新就会数据丢失
-
-
-
禁用RedoLog
-
redoLog虽然尽可能的去保证了我们数据的一致性，但是，如果为了性能考虑，而不需要保证一致性也可以对RedoLog进行关闭
-
-
索引
基础概念
-
索引用于快速查找具有特定列值的行。就像字典的目录一般，查询帮我们查找到想要的数据，倘如没有索引，我们查找一条数据需要必须从第一行开始，读取整个表然后找到对应的记录，假如表的数据量太大，我们要查询的时间也就越长，而索引就可以快速去根据某个特定的列去找到特定的行
底层实现
-
B+树特点
-
索引底层使用的是B+树，它的显著特点就是矮，宽，叶子双向链表
-
矮使得索引查询效率快，一般只要查询三次就能定位到行数据，而且由于只有叶子节点保存了数据，所以查询效率稳定，查询到叶子节点也就是固定查询到第三层才得到数据，并且也正因为只有叶子节点存放数据，所以非叶子节点每页能够保存的节点也就越多，这颗树也能容纳更多的数据，同时叶子节点采用双向链表的形式，并且是有序的，还能够支持范围
-
-
-
B+树与B树的区别
-
最大的区别就是一个只有叶子节点有数据，一个每个节点都有数据，正因为这一点，如果非叶子节点有数据的话，那么每一页能够容纳的数据量就越小，整颗树能容纳的数据也就越少，同时因为B树非叶子节点有数据，也不太稳定，可能有些数据在第二层就找到了，而有些数据在第三层才找到，这对于我们实际业务的预估也是充满着不确定性的，最后一个小不同就是叶子节点一个没有链表，一个有链表，所以一个支持范围查询，一个不支持
-
-
实际环境下B树索引有多少层
-
一般是2-3层，大约能容纳两千万行数据
-
三个关键数据：页大小：默认16K，行数据大小假设1字节，索引大小，假设16字节
-
这个页大小默认16K，能不能扩大一点容纳更多的数据
-
可行性是有，但是当页容量过大的时候，每一页能够承载的行数据也就越多，在这个页查询速度也会变慢，页的大小底层来讲就是效率和空间的取舍
-
-
-
索引分类
-
聚集索引
-
默认会根据表的主键去创建一个索引树，如果没有主键，则使用非空的唯一字段，如果都没有则使用隐藏的row_id字段作为主键索引，也叫聚集索引、聚簇索引，主键索引的叶子节点保存了完整的行数据
-
-
二级索引
-
也称作非聚集索引，一般我们自己创建的索引都是非聚集索引，与聚集索引最大的区别就是叶子节点保存的是主键，而不是完整的行数据
-
一般我们自己创建索引建议选择去创建联合索引作为二级索引，联合索引的排序规则就是现根据第一个字段排，第一个字段相同按第二个字段排，依次类推
-
-
-
常见概念
-
覆盖索引
-
查询一条记录的时候，通过索引列直接能在叶子节点或者是根节点上就能去直接找到对应的行记录
-
-
回表查询
-
与覆盖索引相反，通过索引列无法在索引树下一次性找到对应的行数据，常见二级索引，叶子节点下为主键，需要通过主键进行一次回表去获得需要的行数据
-
-
索引自身优化之索引下推
-
仅对二级索引生效，直接将不满足索引的条件部分先排除，避免回表的次数
-
select * from tuser where name like '张%' and age = 20 and sex = male这个语句在搜索name和age的联合索引树的时候，不会去看age的值，只是把满足name的条件筛选出来在进行回表查询需要的数据，而5.6版本之后会根据where的条件先去把不满足条件的数据过滤掉，到时候回表查询的时候数据会更少一点
-
-
-
索引优缺点及建立原则
-
优点
-
大大减少需要扫描的数据量，能根据特定的行快速进行定位；寻找数据的时候，将随机IO变成顺序IO，因为索引树的都是有序的；并且对于排序条件的后面，可以减少一次排序的情况
-
-
缺点
-
索引是个数据结构，需要占用磁盘空间；索引需要维护，当对表中数据进行操作的时候，必然要对索引树上的数据进行更改，维护索引降低了操作的效率
-
-
建立原则
-
选择唯一索引：唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录（对于唯一索引来说，一旦找到一个满足条件的记录后，就会立即停止继续检索）。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。 为经常作为查询条件的字段建立索引：如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。 为经常需要排序、分组和联合操作的字段建立索引：经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作（B+ 树的叶子节点已经是有序的） 限制索引的数目：索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。 尽量使用数据量少的索引：如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。 如果索引字段的值确实很长，可以使用前缀索引：例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。 删除不再使用或者很少使用的索引：表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
-
-
不适合建立索引的场景
-
表数据量较少；频繁更新的字段（维护成本高）；区分度比较低的字段（性别）
-
索引失效场景
-
原则
-
索引什么时候会生效，什么时候会失效，只要弄懂一个点：走不走索引，看是不是根据之前的排序规则去排的，如果不是，则是无序的，索引自然就失效了
-
-
非最左匹配
-
联合索引的排序规则，abc先按照a进行排序，a相同之后在根据b排，依次类推，如果直接绕过a则破环了索引原有的排序规则，索引自然就失效
-
-
错误模糊查询
-
B+树按照索引值有序进行排序，根据前缀进行比较，如果模糊查询百分号在前面则无法找根据前缀来排序，也就自然走不到索引
-
-
对索引列进行运算或者使用了函数
-
索引列上保存的是原始值，而不是计算后的值，索引列都不同了，自然无法走到索引
-
-
发生了隐式类型转化
-
值发生变化，同上条
-
-
SQL语句使用不正确
-
where条件后面的or
-
OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要 or 两边有条件列不是索引列，就会进行全表扫描。要解决办法很简单，将 or 两边的字段都设置为索引即可
-
-
使用not条件
-
查询条件为等值或范围查询时， B+ 树可以根据查询条件在叶子节点上去找对应的条目。反过来当查询条件为非时，索引定位就困难了，执行计划此时可能更倾向于全表扫描
-
-
-
范围查询
-
范围查询会使下一个索引列失效（范围查询之后下一列就是无序的了，也就不能走索引了）
-
对索引使用的举一反三（加强使用的理解）
-
1.索引不是越多越好
-
在适合的场景去建立索引，并且尽量使用联合索引，如果无限制的添加索引，那么会占用很大的内存空间，所以索引是有大小限制的 innoDB对限制的定义官网： 一个表最多1017列，二级索引最多只能64个。一个索引会对应一个索引树，数据变更是需要对树进行维护的，那么索引树越多，需要维护的树也就越多，操作数据也会越来越慢，这也是树的特性，可以提升数据检索性能，但是，会降低数据操作效率。 所以，索引不是越多越好，因为会占用内存空间，并且影响数据操作效率。所以创建的索引一定是在业务中有用到，并且尽量使用联合索引
-
-
2.组合索引a,b,c 查询条件c=10 and a = 1 and b >= 30，是否用到索引？
-
联合索引abc，在索引树是先根据a排序，a相同的根据b排，b相同的再根据c排。 最左匹配原则：a where条件中存在，满足，所以肯定能走到索引树，但是不确定是否所有条件都会走。 继续最左，b在条件中也存在，所以b也能走到索引。 但是b是个范围查询，范围查询后，c就是无序的。C必须去遍历 b> =30 的所有数据，c是不能走索引扫描的
-
核心点就是当查询的数据之后如果这个字段是无序就不会走索引
-
-
-
3.我们主键一般建议趋势递增的原因在哪
-
假如主键不默认递增，每一次添加都要去B+树里面进行排序，那么势必会打乱之前排好序的索引树，造成页和树的分裂以及合并，会更加影响操作数据的性能，也就是为什么常说的主键不建议使用UUID的原因：第一个次要原因就是UUID占用的空间比较大，比较浪费空间，第二个就是因为不是趋势递增的，每次添加数据都需要去主键索引树中重新排序，打乱之前排序好的顺序，造成树和页的分裂，影响操作数据的性能。
-
-
4.我们在做InnoDB表结构设计的时候，一些大字段，比如 varchar(5000)，会独立成附表，为什么？
-
数据都是存在我们主键索引的叶子结点的，叶子结点就是一个一个page页的链表，那么如果字段过大，每个page页存的数据也就越少，同样多的数据就需要更多的page页来保存。叶子结点的页越多，那么层级就越高。层级越高，在同样的表数量的情况下，磁盘IO次数就越多，性能也就会越慢。
-
-
5.我们innodb中索引结构为啥要用B+树的结构？
-
B+树相比于其他的树：更矮，更宽，更矮性能就更好，与磁盘交互次数就越少，更宽也是如此 InnoDB索引肯定会有1个clustered索引，默认是主键，然后是非空的唯一索引，最后是隐藏列rowid。 ①效率高：聚集索引的存储方式为叶子节点有完整的数据，而非叶子节点，只存有索引值。使得每页能够存的数据更多，内存跟磁盘交互的单位为页。每页的数据越多，那么就能减少跟磁盘的交互次数，整体上提升速度。 ②稳定：真实数据都在叶子节点，所以sql语句的查询路径都是一样长。 查询稳定。 ③支持范围：叶子节点采用的是链表，支持范围
-
-
6.三层的B+树结构，能支撑多大的数据量（换一个思维角度，就是多大的数据量会去进行分库分表）
-
核心点：就是当违背了选用B+树的一个初衷，也就是矮，所以效率高，当树的高度超过三层之后，查询效率比较慢就要考虑分库分表了 树的高度由很多因素来决定的，数据行大小、索引大小、页的大小。
-
想要索引承载数据量越大，能不能设置页大小很大呢？ 页大小默认16K，其实就是性能和承载量的取舍，页越大，每一页存的数据越多，那么在页里面查询的数据就会很慢，中和查询速度与承载量，16K是有道理的
事务
事务概念
-
事务简单来说就是原子性的一组操作，要么都执行，要么都不执行
事务特性
-
一般来说，事务必须满足四个条件，原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
-
原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
-
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的
-
1.双写保证内存跟磁盘之间同步的数据安全 2.基于RedoLog的数据恢复
-
-
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
-
由事务的隔离级别以及锁来保障
-
-
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
-
1.双写 保证内存同步到磁盘，就算page损坏的情况下也能恢复 2.RedoLog的同步机制设置 3.binlog的同步机制 4.独立表空间或者系统表空间设置
-
-
隔离级别
-
并发一致性问题
-
一个事务读到了另外一个事务中的 “脏数据”，脏数据就是指事务未提交的数据
-
在一个事务内多次读取同一数据集合。在这个事务还没有结束时，另外一个事务也访问该同一数据集合，并做了一些修改操作。因此，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。
-
幻读本质上是属于不可重复读的一种情况，区别在于，不可重复读主要是针对数据的更新（即事务的两次读取结果值不一样），而幻读主要是针对数据的增加或减少（即事务的两次读取结果返回的数量不一样）
-
举个例子：事务 T1 读取某个范围的数据，事务 T2 在这个范围内插入了一些新的数据，然后 T1 再次读取这个范围的数据，此时读取的结果比第一次读取的结果返回的记录数要多
-
-
-
四种隔离级别
-
READ UNCOMMITTED 读取未提交：一个事务还没提交时，它做的变更就能被别的事务看到 这个隔离级别效率最高
-
②READ COMMITTED 读已提交，可以读到已经提交的数据，解决了脏读问题，但是没解决不可重复读问题
-
③REPEATABLE READ 可重复读，解决了不可重复读问题，innoDB默认隔离级别
-
可重复读就是：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。或者简单来说，事务在执行期间看到的数据前后是一致的。
-
-
④SERIALIZABLE 串行化，解决了所有问题，但是性能最慢
-
并发解决方案
-
无锁MVCC
-
有锁LBCC
-
本质
-
LBCC是用来避免并发情况下多事务操作时数据一致性问题，本质：给要修改的数据加锁，当我在操作数据的时候，我去加锁，其他线程不能够再去操作
-
-
加锁载体
-
在InnoDB里面，我们是改动行数据，这个行数据会加锁，但是到底加锁哪些行数据呢，会根据你去操作的条件来决定。 索引即数据，数据即索引（完整的行数据都会存在主键索引里面）加锁就是加的是查询的时候走的索引树的节点，如果操作的数据没有索引，就会锁住该数据所在的行 查询的索引不同，锁的数据也不同，如果锁的是二级索引的节点，也会去找到行数据对应的主键索引加锁。 总结而言：加锁数据是基于索引树去进行加锁的，锁住的也是索引树上的节点
-
-
锁类型
-
类型划分
-
共享锁
-
读锁，也称为S锁、共享锁，加了读锁，其他事务能够再次加读锁。 当我读取一个数据后，我不希望其他事务对数据进行更改，那么就可以采 用读锁
-
-
排他锁
-
写锁，也称为X锁、排它锁，加上排它锁，其他事务不能再去加其他的读锁或者写锁，我们操作数据默认就会加上排它锁
-
-
意向锁
-
如果有行数据加锁，我在表上做个标记，代表表里已经有数据加锁了。这样就不需要遍历了。 意向锁是为了加表锁的时候，去提升加锁的性能。并且每行数据加锁都会默认加意向锁。 意向锁也会根据加锁的锁类型分为意向排它、意向共享
-
-
-
范围划分
-
表锁
-
锁定整个表，其他事务不能对表进行修改操作，只能等待锁释放
-
-
行锁
-
记录锁
-
间隙锁
-
临键锁
-
-
-
-
死锁
-
概念
-
两个或多个事务互相等待对方释放锁而陷入无限等待的状态，从而导致事务无法继续执行
-
-
条件
-
1.互斥 2个事务拿互斥的资源 2.请求和保持 事务在等待其他事务持有的资源时，仍然保持自己所持有的资源不释放 3.不可剥夺 一个事务持有的资源不能被其他事务强制性地抢占 4.循环等待
-
-
解决方案
-
打破造成死锁的四大条件以及官方原语参考回答
-
-
-
-
性能优化
硬件层面优化
从MySQL索引底层数据结构出发
-
我觉得优化MySQL必不可少的一点就是索引，对于反复透彻的理解索引，以及与其他数据结构之前的区别对索引方面的调优至关重要
-
索引数据结构为什么不选择二叉树：存在斜树的情况，退化成链表查询时间复杂度非常高
-
为什么不选择红黑树：虽然红黑树避免二叉树的退化成链表的情况，但是每一个分支能够容纳的页数也只有两页，数据量大的情况下树的高度会非常高，同样查询时间复杂度也会非常高
-
B树和B+树的区别
-
B树和B+树最大的一个区别就是B树的非叶子姐节点也会有数据，而B+树只有叶子节点才会有数据，这对于稳定性，每页数据的容纳量以及B+树独特的指针能做范围查询这三方面拉开差距
-
-
innoDB和MyISAM的区别
-
事务
-
myisam不支持事务，不支持外键，行级锁，而innodb支持事务，支持回滚，支持外键，支持行级锁，还有多版本并发控制MVCC
-
-
存储空间
-
innodb占用的内存空间大，需要更多的内存和磁盘存储，它需要有缓存池用作高速缓存的数据及索引
-
-
缓存
-
myisam仅仅缓存索引，不缓存数据（myisam数据文件和索引文件分开） innodb缓存索引和真实数据，所以，对内存有更高的要求。
-
-
性能
-
select：myisam性能高 update、insert：innodb性能高 清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
-
-
如何选择合适的存储引擎
-
如果执行大量的SELECT，MyISAM是更好的选择。 如果你的数据执行大量的INSERT或UPDATE ，出于性能方面的考虑，应该使用InnoDB表。
-
-
-
Explain详解与索引最佳实践
-
Explain详解
-
TYPE（访问类型）:const(常量查询)>eq_ref(主键关联查询)>ref(最好,普通索引或唯一索引的部分前缀)>range(至少优化到,范围查找)>index(二级索引)>ALL(全表扫描，扫描聚簇索引的全部叶子节点) EXTRA（优化建议） Using index(覆盖索引不是索引是查找方式)：不需要回表，查询数据在联合索引里存在 Using where：使用where语句查询结果，并且查询列未被索引覆盖 Using index condition（索引排序）：查询的列不完全被索引覆盖，where条件中是前导列的范围 -Using filesort（文件排序） 不要在索引上使用任何操作（计算，函数，类型转换），会导致索引失效而转为全表扫描
-
-
索引优化实战一
-
综合例子： 1.联合索引第一个字段用范围不走索引（mysql内部认为可能太多回表不如全表扫描） 2.强制走索引（反向优化） 扫描行数减少但效率不一定提高了，只是决定速度的因素之一 索引下推：最左前缀匹配完成后，再向后推断字段，减少回表数据量。（like ‘zhangsan%’）
-
强制走索引有时候在我们的业务范围内可能并不见的那么的好，也许一个联合索引只有两三个字段，但是我们业务要返回的数据可能有3.4甚至更多的字段，如果为了强制踩索引而去增大dao层的代码量，可能更加容易出现一个业务的安全问题，加大代码冗余，而不强制的踩索引虽然牺牲了一点点点的查询效率，但是大大降低了代码的冗余，也增加了代码的可读性，这些牺牲的查询效率，在实际生产中是可以接收的
-
-
常见sql优化
-
Order by与Group by优化
-
MySQL支持两种方式的排序filesort和index。在执行计划中的extra中using Index指MySQL扫描索引本身完成的排序，使用index比filesort效率更高
-
order by满足两种情况会使用Using index
-
order by语句使用索引最左前列。 使用where子句与order by子句条件列组合满足索引最左前列。
-
-
尽量在索引列上完成排序，遵循索引建立(索引创建的顺序）时的最左前缀法则。如果order by的条件不在索引列上，就会产生Using filesort。
-
group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。
-
-
-
索引设计原则
-
1、代码先行，索引后上（也就是前边提到的反向优化）
-
2、联合索引尽量覆盖条件 比如设计联合索引时，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则
-
3、不要在小基数字段上建立索引 索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。 如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。
-
4、长字符串我们可以采用前缀索引 尽量对字段类型较小的列设计索引，比如说tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。 对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position)。 此时在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。但是假如是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的， group by也是同理
-
5、where与order by冲突时优先where。这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多。
-
6、基于慢sql查询做优化.slow_query_log:慢日志开关 这个开关默认是关闭的，我们可以通过配置set global slow_query_log=1去开启慢日志查询 参数： long_query_time超时时间 超过这个时间才是慢查询，默认10秒 慢查询日志查询出来的慢SQL保存地方： log_output 慢日志分析 表分析，文件分析，就是去配置慢SQL保存的文件路径和是否保存在慢日志表中，然后根据具体的路径去查找相应的慢SQL进行分析
-
-
-
索引优化实战二
-
select * from employees limit 90000,5 ——走全表查询，为了避免回表操作，走主键索引全表查询 select * from employees where id>90000 limit 5 后者比前者有更高的效率，没必要查出来全部的数据
-
select count(1) from employee select count(id) from employee select count(name) from employee select count(* ) from employee 实际sql执行查询效率差不多 字段有索引：count()=count(1)>count(字段)>count(主键id) count(字段)走二级索引，二级索引存储数据比主键索引还少，所以count(字段)>count(主键id) 字段无索引：count()=count(1)>count(主键id)>count(字段)
-
Mysql事务原理与优化最佳实践
-
事务中避免一次性处理太多数据，可以拆分成多个事务分次处理 更新等涉及加锁的操作尽可能放在事务靠后的位置（先insert后update） 能异步处理的尽量异步处理
Mysql锁机制与优化实践
-
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁 合理设计索引，尽量缩小锁的范围 尽可能减少检索条件范围,避免间隙锁 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行 尽可能用低的事务隔离级别
3141
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
