导图社区 Mysql知识体系
- 102
- 3
- 1
- 举报
Mysql知识体系
Mysql知识体系包括主从复制和读写分离、Mysql InnoDB的MVCC实现机制、一条SQL的执行过程详解、分库分表等等。
编辑于2022-07-11 14:24:05- mysql知识…
- 相似推荐
- 大纲
Mysql
数据类型
使用VARCHAR(5)和VARCHAR(200)存储"hello"的空间开销是一样的。那么使用更短的列有什么优势吗？ 事实证明有很大的优势。更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或其他操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。 所以最好的策略是只分配真正需要的空间
整型
INT(11)中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的
浮点数
float和double为浮点类型，decimal为高精度小数类型
字符串
主要又char和varchar两种类型，一种是定长的，一种是变长的 varchar会保留字符串末尾的空格，而char会删除
时间和日期
mysql提供了两种相似的日期时间类型：datetime和timetamp datetime，能保存从1001到9999年的日期和时间，精度为秒，使用8字节的存储空间 timestamp，保存从1970年1月1日零点以来的秒数，使用4个字节，只能表示从1970到2038年 默认情况下，如果插入时没有指定timestamp列的值，会将这个值设为当前时间
字符串类型
VARCHAR是最常见的字符串类型。VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是可变的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，MyISAM会将行拆成不同的片段存储；InnoDB则需要分裂页来使行可以放进页内。 当存储CHAR值时，MySQL会删除所有的末尾空格。CHAR值会根据需要采用空格进行填充以方便比较。 CHAR适合存储很短的字符串，或者所有值都接近同一个长度，如密码的MD5值。对于经常变更的数据，CHAR也比VARCHAR更好，因为CHAR不容易产生碎片（行间碎片？）
存储引擎
mysql常用引擎包括：MYISAM，Innodb、Memoroy、MERGE 简述在MySQL数据库中的MyISAM和InnoDB的区别 MyISAM： 不支持事务，但是每次查询都是原子的 支持表级锁，即每次操作是对整个表加锁，存储表的总行数 InnoDB： 支持ACID的事务，支持事务的四种隔离级别 底层是基于B+Tree实现的，在分支节点上只保存键值，不存储数据，数据保存在叶子节点上，在非叶子节点上不涉及磁盘IO，增加读取效率，在叶子节点上进行前后连接，方便范围查询 支持行级锁和外键约束，因此支持写并发，不存储总行数
InnoDB
Innodb：行级锁，提供了具有提交、回滚和崩溃恢复能力的事务安全，支持自动增长列，支持外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些 是Mysql默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其他存储引擎 实现四个标准的隔离级别，默认级别是可重复读。在可重复读隔离级别下，通过多版本并发控制MVCC+间隙锁Next-Key Locking防止幻影读 主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。 支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取
MYISAM
MYISAM：全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间相对较小，对事务完整性没有要求，以select、insert为主的应用基本上可以使用该引擎 MyISAM设计简单，数据以紧密格式存储，对于只读数据，或者表比较小，可以容忍修复操作，则依然可以使用它 不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时加排他锁，但在表有读取操作同时，也可以往表中插入新的记录，这被称为并发插入 可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。 如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作
memory
Memory：全表锁，存储在内存中，速度快，但会占用和数据量成正比的内存空间且数据在mysql重启会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表
merge
MERGE：是一组MYISAM表的组合
索引
索引是帮助Mysql高效获取数据的数据结构。索引是对数据库表中一个或多个列的值及进行排序的结构，建立索引有助于快速获取信息。 Mysql有4种不同的索引： 主键索引（PRIMARY） 唯一索引（UNIQUE） 普通索引（INDEX） 全文索引（FULLTEXT） 索引并不是越多越好，创建索引也需要消耗资源，一是增加了数据库的存储空间，二是在插入和删除时要花费较多的时间维护索引 索引失效：如果条件中有or，即使条件中带有索引也不会生效；最左匹配；like查询中以%开头；如果列类型是字符串，那么一定在条件中将数据使用引号引用起来，否则不使用索引；如果mysql估计全盘扫描要比使用索引快，则不使用索引
Mysql索引
B+Tree索引
是大多数 MySQL 存储引擎的默认索引类型。 因为不再需要进行全表扫描，只需要对树进行搜索即可，因此查找速度快很多。除了用于查找，还可以用于排序和分组。 可以指定多个列作为索引列，多个索引列共同组成键。
InnoDB
InnoDB 的 B+Tree 索引分为主索引和辅助索引。
主索引
主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。 
辅助索引
辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。 
联合索引
其中a是主键，除e为varchar其余为int类型，并创建了一个联合索引idx_t1_bcd(b,c,d)，然后b、c、d三列作为联合索引，在B+树上的结构正如上图所示。联合索引的所有索引列都出现在索引数上，并依次比较三列的大小。上图树高只有两层不容易理解，下面是假设的表数据以及我对其联合索引在B+树上的结构图的改进。  InnoDB会使用主键索引在B+树维护索引和数据文件，然后我们创建了一个联合索引（b，c，d）也会生成一个索引树，同样是B+树的结构，只不过它的data部分存储的是联合索引所在行的主键值（上图叶子节点紫色背景部分），至于为什么辅助索引data部分存储主键值上篇文章也有介绍，感兴趣或还不知道的可以去看一下。 联合索引的查找方式 比如 select * from T1 where b = 12 and c = 14 and d = 3; 也就是T1表中a列为4的这条记录。存储引擎首先从根节点（一般常驻内存）开始查找，第一个索引的第一个索引列为1,12大于1，第二个索引的第一个索引列为56,12小于56，于是从这俩索引的中间读到下一个节点的磁盘文件地址，从磁盘上Load这个节点，通常伴随一次磁盘IO，然后在内存里去查找。当Load叶子节点的第二个节点时又是一次磁盘IO，比较第一个元素，b=12,c=14,d=3完全符合，于是找到该索引下的data元素即ID值，再从主键索引树上找到最终数据。 
页分裂&页合并
mysql的数据是存在于我们的每个数据页上的，如果在主键索引是不连续的话，如果需要放到的数据列已经满了，就会发生页分裂导致会挪动部分数据到新的页上 当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。
B+Tree原理
大多数MySQL存储引擎的默认索引类型 B Tree指的是Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层 B+Tree是基于B Tree和叶子节点顺序访问指针进行实现，它具有B Tree的平衡性，并且通过顺序访问指针来提高区间查询的性能 进行查找操作时，首先在根节点进行二分查找，找到一个key指的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找到key所对应的data 插入删除操作记录会破坏平衡树的平衡，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性 与红黑树的比较 1.更少的查找次数 2.利用计算机预读特性
数据结构
操作
与红黑树比较
b树
B树适合随机访问数据，而b+树更适合顺序性的访问数据
哈希索引
哈希索引能以O(1)时间进行查找，但是失去了有序性，它具有以下限制： 1.无法用于排序与分组 2.只支持精确查找，无法用于部分查找和范围查找 InnoDB存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在B+Tree索引之上再创建一个哈希索引，这样就让B+Tree索引具有哈希索引的一些有点，比如快速的哈希查找
空间数据索引
MyISAM存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询 必须使用GIS相关的函数来维护数据
全文索引
MyISAM存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用match against，而不是普通的where 全文索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射 InnoDB存储引擎在MySQL5.6.4版本中也开始支持全文索引
索引优化
独立的列
在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引
多列索引
在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好 因为如果是两个单独索引的话，需要查出两个主键索引，然后取交集 如果是多列联合索引的话只需要查一次就可以了
前缀索引
前缀索引也叫局部索引，比如给身份证的前 10 位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引。 为什么要用前缀索引？ 前缀索引能有效减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。但前缀索引也有它的缺点，不能在 order by 或者 group by 中触发前缀索引，也不能把它们用于覆盖索引。 什么情况下适合使用前缀索引？ 当字符串本身可能比较长，而且前几个字符就开始不相同，适合使用前缀索引；相反情况下不适合使用前缀索引，比如，整个字段的长度为 20，索引选择性为 0.9，而我们对前 10 个字符建立前缀索引其选择性也只有 0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，就没有创建前缀索引的必要了。
覆盖索引
由于辅助索引只存储主键的值,如果使用辅助索引搜索数据就必须先从辅助索引取到主键的值,再使用主键的值去主键索引上查询,直到找到叶子节点上的数据返回。 ---- 这个也称之为回表 如果辅助索引上已经存在我们需要的数据,那么引擎就不会去主键上去搜索数据了。 ---- 这个就是所谓的"**覆盖索引**" select age,name from test where age = 10
索引下推
当我们使用like查询的时候比如 select * from tuser where name like '张%' and age=10 and ismale=1; 所以这个语句在搜索索引树的时候，只能用 “张” 在MySQL 5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值。 而MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
索引使用原则
1.选择唯一性索引，唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。 2.为经常需要排序、分组和联合操作的字段建立索引 经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。 3.为常用作为查询条件的字段建立索引 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。 4.如果索引的值很长，那么查询的速度会受到影响。尽量使用前缀来索引 如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多 5.限制索引的数目，越多的索引，会使更新表变得很浪费时间。尽量使用数据量少的索引 索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。（7个以内） 6.最左前缀匹配原则 mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。 7.索引列不参与计算，保持列“干净”。 比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本 太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’); 8.尽量的扩展索引，不要新建索引 比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可 9.当单个索引字段查询数据很多，区分度都不是很大时，则需要考虑建立联合索引来提高查询效率 10 . 尽量选择区分度高的列作为索引。 区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就 是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条 记录 11.删除不再使用或者很少使用的索引 表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。 12．尽量使用前缀来索引 如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。 13.如果数据量超过总数据量的 1/5 时 就不会使用索引
索引的优点
大大减少了服务器需要扫描的数据行数。 帮助服务器避免进行排序和分组，也就不需要创建临时表(B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，因为不需要排序和分组，也就不需要创建临时表)。 将随机 I/O 变为顺序 I/O(B+Tree 索引是有序的，也就将相邻的数据都存储在一起)
索引的使用场景
对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效。 对于中到大型的表，索引就非常有效。 但是对于特大型的表，建立和维护索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。 不要在索引列上做操作 建立索引的原则： 1．选择唯一性索引 2．为经常需要排序、分组和联合操作的字段建立索引 3．为常作为查询条件的字段建立索引 4．限制索引的数目 5．尽量使用数据量少的索引 6．尽量使用前缀来索引 7．删除不再使用或者很少使用的索引 8 . 最左前缀匹配原则，非常重要的原则。 9 .=和in可以乱序。 10 . 尽量选择区分度高的列作为索引。
事务
事务ACID
事务是作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交，要么都执行，要么都不执行。事务是一个不可分割的工作逻辑单元。 事务的ACID特性概念简单，但不是很好理解，主要是因为这几个特性不是一种平级关系： 1.只有满足一致性，事务的执行结果才是正确的 2.在无并发的情况下，事务串行执行，隔离性一定能够满足，此时只要能满足原子性，就一定能满足一致性 3.在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性 4.事务满足持久化是为了能应对数据库崩溃的情况
A原子性
事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚 原子性由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql
C一致性
数据库在事务执行前后都保持一致性状态，在一致性状态下，所有事务对一个数据的读取结果都是相同的 一致性，一般由代码层面保证
I 隔离性
一个事务所做的修改在最终提交以前，对其他事务是不可见的 由MVCC机制保证
D 持久性
一旦事务提交，则其所做的修改将会永远保存到数据库中，即使系统发生崩溃，事务执行的结果也不能丢失。 可以使用数据库备份和恢复来实现，在系统发生崩溃时，使用备份的数据库进行数据恢复 由 内存 + redo log来保证，mysql修改数据的同时在内存和redo log中记录这次操作，事务提交的时候通过 redo log 刷盘，宕机的时候可以从redo log中恢复
并发一致性问题
锁
Mysql中有哪几种锁？ 1.表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低 2.行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高 3.页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
锁粒度
全局锁
全局锁就是对整个数据库实例加锁。 MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)
表级锁
表锁
表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表时，必须等前一个事务释放了锁才能对该表进行访问。特点：粒度大，加锁简单，容易冲突。
元数据锁MDL
MDL（Meta Data Locks）锁是MySQL数据库中用来保护数据库对象元数据的一种锁。元数据包括数据库表、视图、存储过程、触发器等。 MySQL使用MDL锁来保证数据库对象的完整性和一致性，并且可以在多个事务之间共享或独占数据库对象。 MDL锁分为两种类型：共享锁和排他锁。共享锁允许多个事务同时读取数据库对象，但是不允许任何事务修改对象。排他锁允许一个事务独占数据库对象，其他事务不能读取或修改这些对象。 MDL锁是MySQL数据库中一个重要的功能，它可以帮助管理数据库并发控制，保证数据一致性和完整性。 以下是MySQL中使用MDL锁的一些常见情况：
意向锁
行级锁
间隙锁
间隙锁是在事务加锁后其锁住的是表记录的一个区间，当表的相邻id之间出现空隙则会形成一个区间，遵循左开右闭的原则。 范围查询并且查询未命中记录，查询条件必须命中索引，间隙锁只会出现REPEATABLE_READ（重复读）的事务级别中 当一个事务执行 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 语句时，InnoDB 可能会使用间隙锁。
临键锁
也属于行锁的一种，并且它是InnoDB的行锁默认算法，总结来说他就是记录锁和间隙锁的组合，临键锁会把查询的记录锁住，同时也会把该范围查询内的所有间隙空间也锁住，再之它会把相邻的下一个区间也锁住。 MySQL通常在以下情况下使用间隙锁： 在执行INSERT INTO SELECT语句时，MySQL会自动使用间隙锁来防止其他事务对相同的空间进行修改。 在使用REPLACE INTO语句插入数据时，MySQL会自动使用间隙锁来防止其他事务对相同的空间进行修改。 在使用INSERT IGNORE语句插入数据时，MySQL会自动使用间隙锁来防止其他事务对相同的空间进行修改。 在使用LOAD DATA INFILE语句批量导入数据时，MySQL会自动使用间隙锁来防止其他事务对相同的空间进行修改。 在执行锁表操作时，MySQL会自动使用间隙锁来防止其他事务对相同的空间进行修改。 间隙锁是MySQL数据库中一种常用的锁类型，它能够有效地帮助管理数据库并发控制，保证数据的一致性和完整性。
记录锁
记录锁也是行锁中的一种，只不过记录锁只能锁住表中的某一条记录，记录锁是说事务在加锁之后数据可以避免在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读物的脏读问题。
页锁
它们通常是在数据库事务中使用的，用于保证事务的原子性和隔离性。 页级锁是Mysql中粒度介于行级锁和表级锁中间的一种锁。 表级锁速度快，但冲突多；行级锁冲突少，但速度慢。 所以取了折衷的页级锁，一次锁定相邻的一组记录。 特点：开销和加锁时间介于表锁和行锁之间，会出现死锁；锁的粒度介于表锁和行锁之间，并发度一般。
共享页
允许多个事务同时读取被锁定的数据，但是不允许任何事务修改数据
排他页锁
允许一个事务独占被锁定的数据，其他事务不能读取或修改这些数据
锁的属性
共享锁
排他锁
基于锁的状态分类
意向共享锁
意向排他锁
隔离级别
MySQL InnoDB存储引擎的默认支持的隔离级别是可重复读 RU：多个事务同时修改一条记录，a事务对其的改动在a事务还没提交时，在b事务中就可以看到a事务对其的改动。 没有解决任何问题，存在脏读，因为他就是读取最新的数据。 RC:多个事务同时修改一条记录，a事务对其的改动在a事务提交之后，在b事务中可以看到a事务对其的改动。 我就读取你已经提交的事务就完事，解决脏读。 RR：多个事务同时修改一条记录，这条记录在a事务执行期间是不变的（别的事务对这条记录的修改不被a事务感知）。 serializable 串行化：多个事务同时访问一条记录（crud），读加读锁，写加写锁，完全退化成了串行的访问，自然不会收到任何其他事务的干扰，性能最低。
读未提交
最低的隔离级别，允许读取其他事务尚未提交的数据变更，可能会导致脏读、幻读或不可重复读 当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。例如：用户A向用户B转账100元，对应SQL命令如下 update account set money=money+100 where name=’B’; (此时A通知B) update account set money=money - 100 where name=’A’; 当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。
有脏读的问题
所谓的脏读，其实就是读到了别的事务回滚前的脏数据。 比如事务B执行过程中修改了数据X，在未提交前，事务A读取了X，而事务B却回滚了，这样事务A就形成了脏读。 也就是说，当前事务读到的数据是别的事务想要修改成为的但是没有修改成功的数据。
读提交(Read committed)
允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生 对于RC隔离级别，每次读取的时候都会产生一个readView，因此无法解决不可重复读以及幻读问题。 mysql默认的隔离级别是可重复读（因为主从复制的问题），但是实际上大多使用的是读提交 为什么使用读提交？ repeatable存在间隙锁会使死锁的概率增大，在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行 那存在的不可重复读问题，与幻读问题不解决吗？
解决了脏读
这个隔离级别下只会读到提交的事务数据，但是会有不可重复读的问题 不可重复读：事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了，就是所谓的不可重复读了。 也就是说，当前事务先进行了一次数据读取，然后再次读取到的数据是别的事务修改成功的数据，导致两次读取到的数据不匹配，也就照应了不可重复读的语义。----因为修改
如何解决脏读的问题的呢？
使用MVCC技术，在每一行加入隐藏的字段（DB_TRX_ID：修改该行的最后一个事务的id，DB_ROLL_PTR：指向当前行的undo log日志，DB_ROW_ID：行标识，DELETE_BIT：删除标志），它实现了不加锁的读操作。 当隔离级别为RC时： 写操作：加行级锁。事务开始后，会在UNDO日志中写入修改记录，数据行中的隐藏列DATA_POLL_PTR存储指向该行的UNDO记录的指针。 读操作：不加锁。在读取时，如果该行被其它事务锁定，则顺着隐藏列DATA_POLL_PTR指针，找到上一个有效的历史记录（有效的记录：该记录对当前事务可见，且DELETE_BIT=0）
可重复读(repeatable-read)
对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生 使用间隙锁（gap lock）防止事务过程中被写入/删除数据引发幻读问题 先说结论，RR（可重复读）的事务隔离级别还是会出现幻读的情况的。
解决了不可重复读的问题
解决了不可重复读,但是有幻读的问题 幻读：事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据，导致事务A再次搜索发现有N+M条数据了，就产生了幻读。 也就是说，幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行 -----因为新增（因为mvcc的原因只会在当前读的情况下出现（select for update））
如何解不可重复读的问题的呢？
使用MVCC技术来实现不加锁的读操作。 写操作：加行级锁。事务开始后，会在UNDO日志中写入修改记录，数据行中的隐藏列DATA_POLL_PTR存储指向该行的UNDO记录的指针。 读操作：不加锁。在读取时，如果该行被其它事务锁定，则顺着隐藏列DATA_POLL_PTR指针，找到上一个有效的历史记录（有效的记录：该记录对当前事务可见，且DELETE_BIT=0）。
为什么RR级别下还会有幻读问题？
在RR级别下就算有间隙锁也不能完全解决幻读问题,在RR和RC级别下，数据库的读分为快照读和当前读: 快照读：单纯的select操作。读取的是快照（ReadView）中的数据，可能是历史数据 当前读：select … for update/in share mode、update、insert、delete。读取的总是当前的最新数据 其实在RR级别下，有下面两种情况是可以产生幻读情况的。 1、事务1 先快照读，事务2新增了一条数据并提交事务，事务1再当前读。 2、事务1 先快照读，事务2新增了一条数据并提交事务，事务1对事务2提交的数据进行了修改，事务1再次快照读。 对于情况2， 事务1的更新操作不属于快照读，因此事务1的更新操作是可以生效的，而当前数据会记录最新修改的记录，最新修改的记录为当前事务自己，所以是能看到的。
两者实现的区别
实际上读已提交和可重复读级别的操作基本相同，而不同之处在于： 行记录对于当前事务的可见性（可见性：即哪个版本的行记录对这个事务是可见的）。 读已提交级别对数据的可见性是该数据的最新记录，可重复读基本对数据的可见性是事务开始时，该数据的记录。
可串行化
最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读
举例说明
我们希望的情况是：两个事务不因该相互干扰  若隔离级别是“读未提交”， 则V1的值就是2。这时候事务B虽然还没有提交，但是结果已经被A看到了。因此，V2、V3也都是2。 若隔离级别是“读提交”，则V1是1，V2的值是2。事务B的更新在提交后才能被A看到。所以， V3的值也是2。 若隔离级别是“可重复读”，则V1、V2是1，V3是2。之所以V2还是1，遵循的就是这个要求：事务在执行期间看到的数据前后必须是一致的。 若隔离级别是“串行化”，则在事务B执行“将1改成2”的时候，会被锁住。直到事务A提交后，事务B才可以继续执行。所以从A的角度看， V1、V2值是1，V3的值是2。
多版本并发控制MVCC
MVCC就是因为大牛们，不满意只让数据库采用悲观锁这样性能不佳的形式去解决读-写冲突问题，而提出的解决方案，所以在数据库中，因为有了MVCC，所以我们可以形成两个组合： MVCC + 悲观锁 MVCC解决读写冲突，悲观锁解决写写冲突 MVCC + 乐观锁 MVCC解决读写冲突，乐观锁解决写写冲突 这种组合的方式就可以最大程度的提高数据库并发性能，并解决读写冲突，和写写冲突导致的问题
什么是MVCC
即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。 MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读 多版本并发控制(Multi-Version Concurrency Control, MVCC)是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。
什么是当前读和快照读
当前读 像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁 快照读 像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
当前读、快照读和MVCC的关系
准确的说，MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这么一个概念。仅仅是一个理想概念 而在MySQL中，实现这么一个MVCC理想概念，我们就需要MySQL提供具体的功能去实现它，而快照读就是MySQL为我们实现MVCC理想模型的其中一个具体非阻塞读功能。而相对而言，当前读就是悲观锁的具体功能实现
MVCC能解决什么问题，好处是
在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题
数据库并发场景
有三种, 分别为： 读-读：不存在任何问题，也不需要并发控制 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读 写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失
MVCC带来的好处是
在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题
MVCC的实现原理
MVCC的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的。
隐式字段
每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段 DB_ROW_ID 6byte, 隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引 DB_TRX_ID 6byte, 最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID DB_ROLL_PTR 7byte, 回滚指针，指向这条记录的上一个版本（存储于rollback segment里） DELETED_BIT 1byte, 记录被更新或删除并不代表真的删除，而是删除flag变了  DB_ROW_ID是数据库默认为该行记录生成的唯一隐式主键；DB_TRX_ID是当前操作该记录的事务ID； 而DB_ROLL_PTR是一个回滚指针，用于配合undo日志，指向上一个旧版本；delete flag没有展示出来
undo日志
undo log主要分为3种： Insert undo log ：插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了。 Update undo log：修改一条记录时，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了。 Delete undo log：删除一条记录时，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了。 删除操作都只是设置一下老记录的DELETED_BIT，并不真正将过时的记录删除。 为了节省磁盘空间，InnoDB有专门的purge线程来清理DELETED_BIT为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的DELETED_BIT为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。 对MVCC有帮助的实质是update undo log ，undo log实际上就是存在rollback segment中旧记录链，它的执行流程如下：
Read View(读视图)
什么是Read View，说白了Read View就是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大) 所以我们知道 Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。 Read View遵循一个可见性算法，主要是将要被修改的数据的最新记录中的DB_TRX_ID（即当前事务ID）取出来，与系统当前其他活跃事务的ID去对比（由Read View维护），如果DB_TRX_ID跟Read View的属性做了某些比较，不符合可见性，那就通过DB_ROLL_PTR回滚指针去取出Undo Log中的DB_TRX_ID再比较，即遍历链表的DB_TRX_ID（从链首到链尾，即从最近的一次修改查起），直到找到满足特定条件的DB_TRX_ID, 那么这个DB_TRX_ID所在的旧记录就是当前事务能看见的最新老版本 （trx_list 未提交事务ID列表，用来维护Read View生成时刻系统正活跃的事务ID up_limit_id 记录trx_list列表中事务ID最小的ID low_limit_id ReadView生成时刻系统尚未分配的下一个事务ID，也就是目前已出现过的事务ID的最大值+1） 可见性算法： 首先比较DB_TRX_ID < up_limit_id, 如果小于，则当前事务能看到DB_TRX_ID 所在的记录，如果大于等于进入下一个判断 接下来判断 DB_TRX_ID 大于等于 low_limit_id , 如果大于等于则代表DB_TRX_ID 所在的记录在Read View生成后才出现的，那对当前事务肯定不可见，如果小于则进入下一个判断 判断DB_TRX_ID 是否在活跃事务之中，trx_list.contains(DB_TRX_ID)，如果在，则代表我Read View生成时刻，你这个事务还在活跃，还没有Commit，你修改的数据，我当前事务也是看不见的；如果不在，则说明，你这个事务在Read View生成之前就已经Commit了，你修改的结果，我当前事务是能看见的
整体流程
当事务2对某行数据执行了快照读，数据库为该行数据生成一个Read View读视图，假设当前事务ID为2，此时还有事务1和事务3在活跃中，事务4在事务2快照读前一刻提交更新了，所以Read View记录了系统当前活跃事务1，3的ID，维护在一个列表上，假设我们称为trx_list  Read View不仅仅会通过一个列表trx_list来维护事务2执行快照读那刻系统正活跃的事务ID，还会有两个属性up_limit_id（记录trx_list列表中事务ID最小的ID），low_limit_id(记录trx_list列表中下一个事务ID，也就是目前已出现过的事务ID的最大值+1)；所以在这里例子中up_limit_id就是1，low_limit_id就是4 + 1 = 5，trx_list集合的值是1,3，Read View如下图  我们的例子中，只有事务4修改过该行记录，并在事务2执行快照读前，就提交了事务，所以当前该行当前数据的undo log如下图所示；我们的事务2在快照读该行记录的时候，就会拿该行记录的DB_TRX_ID去跟up_limit_id,low_limit_id和活跃事务ID列表(trx_list)进行比较，判断当前事务2能看到该记录的版本是哪个。  所以先拿该记录DB_TRX_ID字段记录的事务ID 4去跟Read View的的up_limit_id比较，看4是否小于up_limit_id(1)，所以不符合条件，继续判断 4 是否大于等于 low_limit_id(5)，也不符合条件，最后判断4是否处于trx_list中的活跃事务, 最后发现事务ID为4的事务不在当前活跃事务列表中, 符合可见性条件，所以事务4修改后提交的最新结果对事务2快照读时是可见的，所以事务2能读到的最新数据记录是事务4所提交的版本，而事务4提交的版本也是全局角度上最新的版本
MVCC相关问题
RR是如何在RC级的基础上解决不可重复读的？
在RC级别下,任何读写都是当前读 在RR级别下,只有普通查询是快照读,任何加锁操作都是当前读,例如select ... for update(X锁),select ... lock in share mode(S锁),update/insert/delete(X锁).
RC,RR级别下的InnoDB快照读有什么不同？
正是Read View生成时机的不同，从而造成RC,RR级别下快照读的结果的不同 在RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照及Read View, 将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View，所以对之后的修改不可见； 即RR级别下，快照读生成Read View时，Read View会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于Read View创建的事务所做的修改均是可见 而在RC级别下的，事务中，每次快照读都会新生成一个快照和Read View, 这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因 总之在RC隔离级别下，是每个快照读都会生成并获取最新的Read View；而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View。
性能优化
优化工具
explain
select_type
查询类型。主要用来分辨查询的类型事普通查询还是联合查询还是子查询。 simple：简单的查询，不包含子查询和 union primary：查询中若包含任何复杂的子查询，最外层查询则被标记为 primary union：在 union、union all 和子查询中的第二个和随后的 select 会被标记为 union dependent union：在包含 union 或者 union all 的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的 select_type 的值就是 dependent union union result：从 union 表获取结果的 select 会被标记为 union result sebquery：在 select 或者 where 列表中包含子查询（不在from子句中） dependent sebquery：子查询中的第一个 select（不在 from 子句中），并且取决于外层查询 derived：在 form 列表中包含的子查询被标记为 derived，也叫做派生类 uncacheable sebquery：一个子查询的结果不能被缓存 uncacheable union：表示 union 的查询结果不能被缓存
table
表示 explain 语句正在访问哪个表，表名或者别名，可能是临时表或者 union 合并结果集。如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名；表名是 derivedN 的形式，表示使用了 id 为 N 的查询所产生的衍生表；当有 union result 的时候，表名是union n1,n2 等的形式，n1,n2 表示参与 union 的 id.
partitions
指分区信息。数据库优化有分库、分表、分区；这里的分区值表的分区信息。
type
访问类型，表示以何种方式去访问数据库，最容易想的是全表扫描，即直接暴力的遍历一张表去寻找需要的数据，效率非常低下。 访问的类型有很多，效率从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL system：表只有一行记录（等于系统表），这是 const 类型的特例，平时不会出现，不需要进行磁盘 io const：最多只能匹配到一条数据，通常使用主键或唯一索引进行等值条件查询 eq_ref：当进行等值联表查询使用主键索引或者唯一性非空索引进行数据查找时（实际上唯一索引等值查询 type 不是 eq_ref 而是 const） ref：使用了非唯一性索引进行数据的查找 ref_or_null：对于某个字段既需要关联条件，也需要 null 值的情况下，查询优化器会选择这种访问方式 index_merge：在查询过程中需要多个索引组合使用 unique_subquery：该连接类型类似于 index_subquery，使用的是唯一索引。大多数情况下使用 SELECT 子查询时，MySQL 查询优化器会自动将子查询优化为联表查询，因此 type 不会显示为 index_subquery，而是 eq_ref index_subquery：利用索引来关联子查询，不再扫描全表。但是大多数情况下使用 SELECT 子查询时，MySQL 查询优化器会自动将子查询优化为联表查询，因此 type 不会显示 index_subquery，而是 ref range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了 index 的全索引扫描。适用的操作符：=, <>, >, >=, <, <=, is null, between,like, or, in index：全索引扫描这个比 all 的效率要好，主要有两种情况，一种是当前的查询覆盖索引，即需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免了数据的重排序 all：全表扫描，需要扫描整张表，从头到尾找到需要的数据行。一般情况下出现这样的 sql 语句而且数据量比较大的话那么就需要进行优化
possible_keys
显示查询可能使用哪些索引来查找，即显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
key
这一列显示 mysql 实际采用哪个索引来优化对该表的访问，即实际使用的索引，如果为 null ，则表示没有使用索引
key_len
表示索引中使用的字节数，可以通过 key_len 计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。索引越大占用存储空间越大，这样 io 的次数和量就会增加，影响执行效率 计算规则 1.索引字段，非NOT NULL，加1个字节。 2.定长字段：tinyiny占1个字节、int占4个字节、bitint占8个字节、date占3个字节、datetime占5个字节，char(n)占n个字符。 3.变长字段：varchar(n)占n个字符+2个字节。 4.不同的字符集，一个字符占用的字节数不同： latin1编码，每个字符占用一个字节 gbk编码，每个字符占用两个字节 utf8编码，每个字符占用三个字节 utf8mb4编码，每个字符占用四个字节
ref
显示之前的表在 key 列记录的索引中查找值所用的列或者常量
rows
根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的数据行数，此参数很重要，直接反应 sql 找了多少数据，在完成目的的情况下越少越好
filtered
针对表中符合某个条件（where 子句或者连接条件）的记录数的百分比所做的一个悲观估算
extra
显示不适合在其它列的额外信息，虽然叫额外，但是也有一些重要的信息： using filtersort：说明 mysql 无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置 using index：表示当前的查询是覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现 using where，表示索引被用来执行索引键值的查找，如果没有，表示索引被用来读取数据，而不是真的查找 using where：使用 where 进行条件过滤 using temporary ：建立临时表来保存中间结果，查询完成之后把临时表删除 using join buffer：使用连接缓存 impossible where：where 语句的结果总是 false
show profiles
优化建议
大表如何优化
1.限制数据查找的范围 2.读写分离：主库负责写，从库负责读 3.垂直拆分：根据数据库里面数据表的相关性进行拆分。简单来说垂直拆分就是数据表列的拆分，把一张表列较多的表拆分为多张表 4.水平分区：保持数据表结构不变，通过某种策略储存分片数据，这样每一片数据都在不同的表或者数据库中，达到分布式的目的，水平拆分可以支持非常大的数据量。
优化数据访问
减少请求的数据量 1.只返回必要的列，最好不要使用SELECT *语句 2.只返回必要的行，使用LIMIT语句来限制返回的数据 3.缓存重复查询的数据: 使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。 4.减少服务器端扫描的行数最有效的方式是使用索引来覆盖查询
重构查询方式
1.切分大查询 一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询 2.分解大连接查询让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。 减少锁竞争； 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。 查询本身效率也可能会有所提升。
避免会造成全表扫描的情况
1. 使用null做为判断条件 如：select account from member where nickname = null; 建议在设计字段时尽量将字段的默认值设为0，改为select account where nickname = 0; 2. 左模糊查询Like %XXX% 如：select account from member where nickname like ‘%XXX%’ 或者 select account from member where nickname like ‘%XXX’ 建议使用select account from member where nickname like ‘XXX%’，如果必须要用到做查询，需要评估对当前表全表扫描造成的后果； 3. 使用or做为连接条件 如：select account from member where id = 1 or id = 2; 建议使用union all,改为 select account from member where id = 1 union all select account from member where id = 2; 4.对字段有操作时也会引起全表 遇到过对日期字段做操作引起问题的情况 如select account where salary * 0.8 = 1000 或者 select account where sustring(nickname,1,3) = ‘zhangxiaolong’; 5.使用参数做为查询条件时 如select account from member where nickname = @name
语句优化
SQL语句优化： 1.负向查询不能使用索引 2.前导模糊查询不能使用索引（建议可以考虑使用Lucene等全文索引工具来替代频繁的模糊查询） 3.数据区分不明显的不建议创建索引 4.字段的默认值不要为null 5.在字段上进行计算不能命中索引 6.最左前缀问题 7.如果明确知道只有一条记录则使用limit返回（停止游标移动） 8.不要让数据库帮我们做强制类型转换（会导致全表扫描） 如果需要进行join的字段两表的字段类型要相同（不然不会命中索引） 9.查询语句中不要使用select* 10.尽量减少子查询，使用关联查询（left join，right join，inner join）替代 11.减少使用IN或者NOT IN，可以将子查询in的结果作为一个临时表，left join 做关联 （查询的次数少了一次） 12.or的查询尽量用union或者union all代替（在确认么有重复数据或者不用剔除重复数据时，union all会更好） 13.应尽量避免在where子句中使用!=或者操作符，否则引擎将放弃使用索引而进行全表扫描 14.应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描
实例1
需求查询一个用户中有多个角色，而且角色中包含大区经理这个角色的人的信息 错误使用的： 主表关联的时候将这个查询的结果包含使用In（用户中有多个角色） 其实可以用将 in中的结果作为一个临时表 使用where来关联查询结果
实例2
将多个单列索引改为多列索引 like的时候可以使用到索引下推，来过滤数据 并且不用like的时候也可以减少一次查询索引，去交集的过程
分库分表
水平切分
水平切分又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。 当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓存单个数据库的压力。
垂直切分
垂直切分是将一张表按列切分成多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。 在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。
问题
千万级别数如何导入
停机迁移
load法
load data infile "/var/lib/mysql-files/t_member.txt" into table t_member fields terminated by ',' enclosed by '"' lines terminated by '\r\n';
语句插入法
1.把表的索引干掉 2.把语句写成一条 insert into values() 3.因为mysql不支持那么大的sql语句，需要改一下mysql的配置，设置mysql接收sql语句的大小
热迁移
sql案例
计算用户的平均次日留存率
SELECT count( date2 ) / count( date1 ) AS avg_ret FROM ( SELECT DISTINCT t1.device_id, t1.date AS date1, t2.date AS date2 FROM question_practice_detail AS t1 LEFT JOIN ( SELECT DISTINCT device_id, date FROM question_practice_detail ) AS t2 ON t1.device_id = t2.device_id AND date_add( t1.date, INTERVAL 1 DAY )= t2.date ) AS id_last_next_date
主从复制和读写分离
主从复制 主要涉及三个线程：binlog线程、I/O线程和SQL线程 在主服务器上，把数据的更改记录到binlog的二进制文件中 在从服务器上有两个线程，IO线程和SQL线程，IO线程负责去主库读取binlog文件然后写到自己的relaylog文件中，SQL线程负责读取自己的relaylog文件进行回放写入数据 binlog线程：负责将主服务器上的数据更改写入二进制日志中 I/O线程：负责从主服务器上读取二进制日志，并写入从服务器的中继日志中 SQL线程：负责读取中继日志并重放其中的SQL语句 读写分离 主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作 读写分离能提高性能的原因在于： 1.主从服务器负责各自的读和写，极大程度缓解了锁的争用 2.从服务器可以使用MyISAM，提升查询性能以及节约系统开销 3.增加冗余，提高可用性 读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器
mysql体系结构
从准备更新一条数据到事务的提交的流程描述 1.首先执行器根据 MySQL 的执行计划来查询数据，先是从缓存池中查询数据，如果没有就会去数据库中查询，如果查询到了就将其放到缓存池中 2.在数据被缓存到缓存池的同时，会写入 undo log 日志文件 3.更新的动作是在 BufferPool 中完成的，同时会将更新后的数据添加到 redo log buffer 中 4. 完成以后就可以提交事务，在提交的同时会做以下三件事 将redo log buffer中的数据刷入到 redo log 文件中 将本次操作记录写入到 bin log文件中 将 bin log 文件名字和更新内容在 bin log 中的位置记录到redo log中，同时在 redo log 最后添加 commit 标记 至此表示整个更新事务已经完成 
客户端
MySQL驱动
就是这个 MySQL 驱动在底层帮我们做了对数据库的连接，只有建立了连接了，才能够有后面的交互。 java 系统在通过 MySQL 驱动和 MySQL 数据库连接的时候是基于 TCP/IP 协议的，所以如果每个请求都是新建连接和销毁连接，那这样势必会造成不必要的浪费和性能的下降，也就说上面的多线程请求的时候频繁的创建和销毁连接显然是不合理的。必然会大大降低我们系统的性能 数据库连接池：维护一定的连接数，方便系统获取连接，使用就去池子中获取，用完放回去就可以了，我们不需要关心连接的创建与销毁，也不需要关心线程池是怎么去维护这些连接的。 常见的数据库连接池有 Druid、C3P0、DBCP，连接池实现原理在这里就不深入讨论了，采用连接池大大节省了不断创建与销毁线程的开销，这就是有名的「池化」思想，不管是线程池还是 HTTP 连接池，都能看到它的身影
Server层
连接器
SQL接口
MySQL 中处理请求的线程在获取到请求以后获取 SQL 语句去交给 SQL 接口去处理。
数据库连接池
到这里，我们已经知道的是我们的系统在访问 MySQL 数据库的时候，建立的连接并不是每次请求都会去创建的，而是从数据库连接池中去获取，这样就解决了因为反复的创建和销毁连接而带来的性能损耗问题了。 不过这里有个小问题，业务系统是并发的，而 MySQL 接受请求的线程呢，只有一个？ 其实 MySQL 的架构体系中也已经提供了这样的一个池子，也是数据库连池。双方都是通过数据库连接池来管理各个连接的，这样一方面线程之前不需要是争抢连接，更重要的是不需要反复的创建的销毁连接。
查询缓存
分析器
他会将 SQL 接口传递过来的 SQL 语句进行解析，翻译成 MySQL 自己能认识的语言 现在 SQL 已经被解析成 MySQL 认识的样子的，那下一步是不是就是执行吗？理论上是这样子的，但是 MySQL 的强大远不止于此，他还会帮我们选择最优的查询路径。 什么叫最优查询路径？就是 MySQL 会按照自己认为的效率最高的方式去执行查询. 具体是怎么做到的呢？这就要说到 MySQL 的查询优化器
查询优化器
MySQL 会帮我去使用他自己认为的最好的方式去优化这条 SQL 语句，并生成一条条的执行计划，比如你创建了多个索引，MySQL 会依据成本最小原则来选择使用对应的索引，这里的成本主要包括两个方面, IO 成本和 CPU 成本 IO 成本: 即从磁盘把数据加载到内存的成本，默认情况下，读取数据页的 IO 成本是 1，MySQL 是以页的形式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理，所以 MySQL 每次会读取一整页，一页的成本就是 1。所以 IO 的成本主要和页的大小有关 CPU 成本：将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的成本，显然它与行数有关，默认情况下，检测记录的成本是 0.2。 MySQL 优化器 会计算 「IO 成本 + CPU」 成本最小的那个索引来执行 优化器执行选出最优索引等步骤后，会去调用存储引擎接口，开始去执行被 MySQL 解析过和优化过的 SQL 语句
执行器
执行器是一个非常重要的组件，因为前面那些组件的操作最终必须通过执行器去调用存储引擎接口才能被执行。执行器最终最根据一系列的执行计划去调用存储引擎的接口去完成 SQL 的执行
binlog日志文件：记录整个操作过程(server层)
bin log属于是 MySQL 级别的日志 bin log文件是如何刷入磁盘的? bin log 的刷盘是有相关的策略的，策略可以通过sync_bin log来修改，默认为 0，表示先写入 os cache，也就是说在提交事务的时候，数据不会直接到磁盘中，这样如果宕机bin log数据仍然会丢失。 所以建议将sync_bin log设置为 1 表示直接将数据写入到磁盘文件中 刷入 bin log 有以下几种模式： STATMENT 基于 SQL 语句的复制(statement-based replication, SBR)，每一条会修改数据的 SQL 语句会记录到 bin log 中 【优点】：不需要记录每一行的变化，减少了 bin log 日志量，节约了 IO , 从而提高了性能 【缺点】：在某些情况下会导致主从数据不一致，比如执行sysdate()、sleep()等 ROW 基于行的复制(row-based replication, RBR)，不记录每条SQL语句的上下文信息，仅需记录哪条数据被修改了 【优点】：不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题 【缺点】：会产生大量的日志，尤其是 alter table 的时候会让日志暴涨 MIXED 基于 STATMENT 和 ROW 两种模式的混合复制( mixed-based replication, MBR )，一般的复制使用 STATEMENT 模式保存 bin log ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 bin log 那既然bin log也是日志文件，那它是在怎么记录数据的呢？ 其实 MySQL 在提交事务的时候，不仅仅会将 redo log buffer 中的数据写入到redo log 文件中，同时也会将本次修改的数据记录到 bin log文件中，同时会将本次修改的bin log文件名和修改的内容在bin log中的位置记录到redo log中，最后还会在redo log最后写入 commit 标记，这样就表示本次事务被成功的提交了。
InnoDB存储引擎
查询优化器会调用存储引擎的接口，去执行 SQL，也就是说真正执行 SQL 的动作是在存储引擎中完成的。数据是被存放在内存或者是磁盘中的 每次在执行 SQL 的时候都会将其数据加载到内存中，这块内存就是 InnoDB 中一个非常重要的组件：缓冲池 Buffer Pool
Buffer Pool
Buffer Pool是一块连续的内存空间，当数据库操作数据的时候，把硬盘上的数据加载到buffer pool，不直接和硬盘打交道，操作的是buffer pool里面的数据 Buffer Pool 就是我们第一次在查询的时候会将查询的结果存到 Buffer Pool 中，这样后面再有请求的时候就会先从缓冲池中去查询，如果没有再去磁盘中查找，然后在放到 Buffer Pool 中  数据库启动时，会申请内存创建buffer pool，buffer pool分成一个个缓存页及其缓存页描述信息块，描述信息块加入到free链表中 数据加载到一个缓存页，free链表里会移除这个缓存页，然后lru链表的冷数据区域的头部会放入这个缓存页 如果查询了一个缓存页，那么此时就会把这个缓存页在lru链表中移动到热数据区域去，或者在热数据区域中也有可能会移动到头部去 如果更新了缓存页，会把该缓存页加入到flush链表中 如果缓存页不够用了，会把lru冷数据区尾部的缓存页刷盘，清空；该缓存页从lru链表和flush链表中移除，加入到free链表中 mysql后台线程也会定时把lru冷数据区尾部的缓存页刷盘，清空；定时把flush链表中的缓存页刷盘，清空，加入到free链表中 总结： 一边不停的加载数据到缓存页里去，不停的查询和修改缓存数据，然后free链表中的缓存页不停的在减少，flush链表中的缓存页不停的在增加，lru链表中的缓存页不停的在增加和移动 另外一边，你的后台线程不停的在把lru链表的冷数据区域的缓存页以及flush链表的缓存页，刷入磁盘中来清空缓存页，然后flush链表和lru链表中的缓存页在减少，free链表中的缓存页在增加 一个疑问： 按照我们平时开发的一套理论缓冲池中的数据和数据库中的数据不一致时候，我们就认为缓存中的数据是脏数据，那此时 Buffer Pool 中的数据岂不是成了脏数据？没错，目前这条数据就是脏数据。Buffer Pool 中的记录是小强 数据库中的记录是旺财 ，这种情况 MySQL是怎么处理的呢？ 其实 MySQL 会有一个后台线程，它会在某个时机将我们Buffer Pool中的脏数据刷到 MySQL 数据库中，这样就将内存和数据库的数据保持统一了
change Buffer
组成
控制块
1.存的是 数据页所属的表 空间号，数据页编号，数据页地址等信息 2.是放在缓存页的前面 3.控制块占缓冲页百分之5左右的内存大小
缓存页
buffer pool中存放的数据页我们叫缓存页，和磁盘上的数据页是一一对应的，都是16KB 缓存页的数据，是从磁盘上加载到buffer pool当中的
磁盘
redolog：记录数据被修改后的样子（InnoDB引擎）
除了从磁盘中加载文件和将操作前的记录保存到 undo 日志文件中，其他的操作是在内存中完成的，内存中的数据的特点就是：断电丢失。如果此时 MySQL 所在的服务器宕机了，那么 Buffer Pool 中的数据会全部丢失的。这个时候 redo 日志文件就需要来大显神通了 画外音：redo 日志文件是 InnoDB 特有的，他是存储引擎级别的，不是 MySQL 级别的 redo 记录的是数据修改之后的值，不管事务是否提交都会记录下来 例如，此时将要做的是update students set stuName='小强' where id=1; 那么这条操作就会被记录到 redo log buffer 中，啥？怎么又出来一个 redo log buffer 很简单，MySQL 为了提高效率，所以将这些操作都先放在内存中去完成，然后会在某个时机将其持久化到磁盘中 将 redo Log Buffer 中的数据持久化到磁盘中，就是将 redo log buffer 中的数据写入到 redo log 磁盘文件中，一般情况下，redo log Buffer 数据写入磁盘的策略是立即刷入磁盘
undo log：记录数据被修改前的样子
undo 顾名思义，就是没有做，没发生的意思。undo log 就是没有发生事情（原本事情是什么）的一些日志 我们刚刚已经说了，在准备更新一条语句的时候，该条语句已经被加载到 Buffer pool 中了，实际上这里还有这样的操作，就是在将该条语句加载到 Buffer Pool 中的时候同时会往 undo 日志文件中插入一条日志，也就是将 id=1 的这条记录的原来的值记录下来。 这样做的目的是什么？ Innodb 存储引擎的最大特点就是支持事务，如果本次更新失败，也就是事务提交失败，那么该事务中的所有的操作都必须回滚到执行前的样子，也就是说当事务失败的时候，也不会对原始数据有影响
两阶段提交