整理笔记 2022-08-11 17:21 • 阅读：次 ### MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。 ### MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。 ### MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据库。 一，索引树 1，二叉树：每个节点上有且只要两个分支，本身是一个无序的，但是为了提高查找效率就是用了一种叫bst的算法 【Binary Serach Tree：插入数据时候必须顺序，左子树必须小于右节点，右子树必须保证大于跟节点】， 所以可以利用二分查找来提高查询。缺点：递增或者递减造成io（N）次，所以平衡二叉树诞生了； 2，红黑树：最长子树只要不超多最短子树的两倍即可，但是任何一个红黑树节点中的黑色节点必须一致，随着数据的插入红黑树的树深度会变深， 树的深度越深意味着io次数就越多，此时会影响数据读取的效果；所以又产生了：把原来的有序二叉树编程有序多叉树【B-tree】 3，avl（平衡二叉树）：相比红黑树avl不存在数右倾问题，大量的顺序插入不会影响查询性能的降低，这从根本上解决了红黑树的问题； 查询性能O(logn)，不存在极端的低效查找的情况；可以实现范围查找，数据排序。 但是avl数并不适合做MySQL数据库的索引数据结构，因为数据库查询的瓶颈在于磁盘IO， 如果使用的是avl树，我们每一个节点存储了一个数据，我们一次磁盘IO只能取出一个节点上的数据加载到 内存里面，所以我们设计数据库索引需要先考虑到减少磁盘IO的次数。 但是IO有个特点就是，从磁盘读取数据1B和1KB数据所消耗的时间是一样的，我们就可以根据这个思路在一个树节点上尽可能多的存储数据， 一次磁盘IO就多加载点数据到内存，这就是B-tree和B+tree的设计的原理了。 注意：平衡二叉树要多次调整二叉树的形态，会消耗很多性能； 4，B 树：具有优秀的检索速度，时间复杂度：B-tree的查询性能等于O(h*logn)，其中h为树的高，n为每个节点关键词的个数。 尽可能的减少磁盘IO，加快了检索速度；可以支持范围查找。 B-Tree每一个节点里存的是数据，而B+tree存储的是索引（地址），索引B-Tree里面一个节点存不了很多数据， 但是B+tree一个节点能存很多索引，B+tree叶子节点存所有的数据。 B+tree的叶子节点，数据阶段用了一个链表串联起来，便于范围查找； 二，MySQL结构 1，客户端：包括命令行，代码操作，可视化工具，sql 2，服务层：核心层 a，连接器：连接处理，响应客户端连接请求、线程池资源管理；用户鉴权，用户登录身份验证；安全管理，用户执行操作权限校验； b，分析器：词法分析，语法分析； 解析查询语句生成语法树 c，优化器：在表里面有多索引的时候，决定使用哪个索引，或者在一个语句有多个表关联的时候， 决定各个表的连续顺序，优化器阶段完成后，这个语句的执行方案就确定下来，然后进入执行器阶段； d，执行器：开始执行的时候，要先判断一下用户对这个表有没有操作权限，如命中缓存，会查询缓存结果的时候， 做权限判断，查询也会在优化器之前调用precheck验证权限，如果有权限，就继续打开表执行，打开表的时候执行器就会根据表的引擎定义， 去调用这个引擎提供的接口，在数据库的慢查询日志中看到一个rows_examined的字段，表示这个语句在执行过程中扫描了多少行，这个值 就是在执行器每次调用引擎获取数据结行的时候累加的，在有些场景下，执行器调用一次，在引擎内部咋扫描了多行， 因此引擎扫描行数跟rows_examined并不是完全相同； 3，存储引擎：innodb（包括日志redo.log，undo.log） a）innodb的一颗B+tree能存放约两千万数据。在计算机中，磁盘的最小存储单位是一个扇区， 一个扇区的大小是512字节，而文件系统的最小单元是块，一个块的大小是4k，而对于innodb存储引擎也 有自己的最小存储块单元页（page），一个页大小是16k。 文件系统中一个文件的大小是1(kb)字节（实际上现在很多互联网业务数据记录大小通常是1kb左右）,但是不的不占磁盘4kb的空间。 mysql中，innodb的大小默认是16k。 b）innodb存储引擎的最小单元是页，页可以用于存放数据也可以用于存放键值+指针， 在B+tree中叶子节点存放数据，非叶子节点存放键值+指针； 索引组织表通过非叶子节点的二分查找法以及指针确定的数据在哪一个页中，进而在去数据页中查找到需要的数据。 c）通常一个B+tree能存放多少数据？假设B+tree高为2，即存在一个根节点和若干个叶子节点， 那么这课B+tree的存放总记录数为：根节点指针数*单个叶子节点记录行数； 那么非叶子几点需要计算出能存放多少指针？假设主键id为bigint类型长度为8字节， 而指针大小在innodb源码中设置为6个字节，这样一共14字节。一个叶中能存放多少这样的单元，就代表有多少个指针,即16384/14=1170个 那么可以算出一颗高度为2的B+tree，能存放1170*16=18720条这样的数据记录。 即高度为3的B+tree可以存放1170*1170*16=21902400条这样的记录。 d）怎么的到innodb B+tree的高度？因为主键索引B+tree的根页在整个表空间文件中的第3页开始， 所以可以算它在文件中的偏移量：16284（页的大小）*3=49152。 三，存储引擎：就是指数据在磁盘中存储的不同组织形式； 1，MySQL底层数据引擎是以插件的形式设计的，最常见的事innodb引擎和myisam引擎， 用户可以根据个人需求选择不同的引擎作为MySQL数据表的底层引擎，B+tree作为数据MySQL的索引数据结构。 2，innodb存储引擎：支持事务，支持外键，支持行级锁，innodb最大的特色就是支持ACID兼容的事务功能， 而且他支持行锁，innodb创建后生成（.frm：创建表的语句。idb：表里面的数据+索引文件） innodb引擎底层实现：是聚簇索引方式，因此数据和索引都是存在一个文件中，首先innodb会根据主键id作为key建立索引B+tree， 而B+tree的叶子节点存的是主键id对应的数据，innodb在建表的时候就会自动建立好主键id的树，这也是为什么MySQL在建表的时候必须指定主键的原因， 3，myisam存储引擎：不支持外键，在内存不够大的情况下，查询速度要快一些，因为它的查询是直接查询硬盘； myisam索然数据查找性能极佳，但是不支持事务处理，myisam创建表后生成（frm：创建表语句；MYD：创建表语句；MYI：表里面的索引文件） myisam的底层实现：用的是非聚簇索引方式，即数据和索引落在不同的两个文件上，myisam在创建表时以主键为key来建立主键B+tree， 树的叶子节点存的是对应数据的物理地址，我们拿到这个物理地址后，就可以到myisam数据文件中直接定位到具体的数据记录了。 当我们对某个字段添加一个索引的时候，我们同样会生成对应字段的索引树，该字段的索引 树的叶子节点同是记录我数据的物理地址，然后也是拿着这个物理地址，去数据文件里面定位到具体数据记录。 四，聚簇索引和非聚簇索引：是否是聚簇索引取决于数据和索引是否是放在一起的； 1，索引是什么：索引是一种特殊的文件（innodb数据表上的索引是表空间的一个组成部分），他们包含着对数据表里所有记录的引用指针； 索引是一种数据结果，数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询， 更新数据库表中数据，索引的时间通常使用的事B-tree以及其B+tree。 1，聚簇索引：也叫聚合索引，他实际上并不是一种单独的索引类型，而是一种数据存储方式，聚簇索引的叶子节点上保存了一行记录的所有信息，也就是说， 聚簇索引的叶子结点上包含了，一个完整的记录行。 2，非聚簇索引：也叫普通索引，辅助索引，他的叶子节点上保存的是一个主键值，通过非聚簇索引查找记录要先找到的主键， 然后通过主键在聚簇索引中查找到对应的记录行，这个过程叫做回表； 3，innodb中至少有一个是聚簇索引，因为innodb在插入数据时候必须要包含一个索引key值； innodb使用的事B+tree结构对主键索引，然后叶子结点中存储记录，索引key值，可以是主键，如果没有主键， 那么就是唯一键，如果过没有唯一键，那么就是一个自生成6字节的rowid（对用户不可见的），总之需要一个key； 4，一个表中只能有一个聚簇索引（多个聚簇索引会造成数据冗余），但是一个表中可以有多个非聚簇索引； 5，回表：回表产生的原因是因为普通索引通过查询获取对应数据的主键id，然后在根据主键id查询出对应的数据， 其中经过了6次io（两个树形数据），主键查询一般根据的是主键的id直接找到对应的数据，其中经理一个树； 6，索引覆盖：查询的数据全部在索引列中，不需要从聚簇索引中查询其他的数据，此时叫做覆盖索引； 7，索引下推：对联合索引中包含的字段先做判断，现在索引列中直接过滤掉不满足条件的数据，减少回表次数。 例如（select * from table where name = ‘张%’ and age=‘’。注意：在没有索引下推之前，先根据name从符合规则的数据，然后再server层对age进行过滤； 使用索引下推后：根据name，age两个的条件来从存储引擎中获取对应数据。 （例如：索引先根据name找到符合‘张’字开头的，然后在根据age条件找到索引上叶子节点的符合age条件的主键id，然后进行会表查询）； 8，联合索引：必须遵循最左匹配原则，索引不能进行运算，索引不能遵循范围查找，索引不能包含空值。 五，mysql hash索引 1，会出现哈希冲突造成数据列不均匀，会产生大量的线性查询，比较浪费时间。 2，不支持范围查询，当进行返回查询的时候。必须要挨个遍历。 3，对内存的要求比较高。 4，等值查询的速度非常快。 5，memory存储引擎使用的是哈希索引。 6，innodb支持自适应索引。 7，例子：select * from user where id = 7;这条sql首先执行了用【addr=hash（7）=4231（注：物理映射地址）】通过独立的物理映射地址可以找到对应的数据； 这就是hash算法检索数据的计算过程。 8，使用hash表必须要保证具备好的hash算法，如果过算法不合适的话会造成hash冲突或者hash碰撞，会导致数据散列不均匀，有可能会退化成一个链表； 解决hash碰撞的一个常见的方式就是链地址发。即用链表把碰撞的数据连接起来，计算hash值之后，还需要检查该hash值是否存在碰撞数据链表， 有则一直遍历到链表尾部，值到找到真正的key对应的数据为止。从算法的时间复杂度来看，hash算法的时间复杂度是O(1)， 检索的数据的速度非常快，hash索引只要计算一次就可以获取到对应的数据。 六，MySQL事务：根据逻辑计算出实际的值：而不需要在每个状态的时候都存储一份数据； 1，undo.log（逻辑日志）是为了实现事务的原子性，在MySQL数据库innodb存储引擎中，还有undo.log来实现多版本并发控制（简称mvcc）。 2，在操作任何数据之前，首先把数据备份到一个地方（这个存储数据备份的地方称为undo.log）。 然而进行数据的修改，如果出现了错误或者用户执行了rollback语句，系统可以利用undo.log中的备份将数据恢复到事务开始之前的状态。 3，redu.log：redo.log是固定大小的，是循环写的过程，当生成数据修改的时候，innodb引擎会先将记录写到redo.log中，并更新内存，此时更新就算完成了， 同时innodb引擎会在何时的时机将记录操作到磁盘中。有了redo.log之后，innodb就可以保证及时数据库发生异常重启，之前的记录也不会丢失，叫做crash-safe； redo.log一般设置1G就差不多了。 4，change buffer： 七，MySQL锁：为了解决事务（MVCC）之间的隔离性问题，为了让事务之间互相不影响，每个事务进行操作的时候都会对数据加上一把持有的锁，防止其他事务同时操作数据。数据库里的锁是基于索引实现的，在innodb中我们的 锁都是作用在索引上的，当我们的sql命中索引时，那么锁住的就是命中条件内的索引节点（行锁），如果没有命中索引的话，那么我们锁住的就是索引树（表锁）； 1，基于所属行分类：共享锁、排它锁； a）共享锁【读锁-S】：当一个事务为数据加锁之后，其他的事务只能对数据加锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁； 共享锁可同时访问，排它锁必须等共享锁释放，共享锁的特性，主要是为了支持并打的读取数据，读取数据的时候不支持修改，避免出现读重复的问题； b）排它锁【写锁-X】：当一个事务为数据加写锁时，其他请求将不能在为数据加任何锁，知道该锁释放之后，其他事务对数据进行加锁； 排它锁独占整张表，所有锁都必须等待排它锁释放； 排它锁的目的是在数据修改的时候，不允许其他人同时修改，也不允许其他人读取，避免产生脏读和幻读的问题； 2，基于锁粒度分类：表锁、行锁、记录锁、间隙锁、临键锁； a）表 锁：是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了一锁才能进行对表行访问； 【特点：粒度大，加锁简单，容易冲突】； 单个表锁定： 格式： LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},……] 例子： lock tables db_a.tbl_aaa read; // 锁定了db_a库中的tbl_aaa表 解锁： unlock tables; 全局表锁定： 命令： FLUSH TABLES WITH READ LOCK; // 所有库所有表都被锁定只读 解锁： unlock tables; b）行 锁：是指上锁的时候锁住的是表的某一行或者多行记录；其他事务访问同一张表时，只有被锁住的记录不能访问；其他的记录可以访问； 【特点：粒度小，加锁麻烦，不容易冲突，支持并发较好】 共享锁：select * from tableName where … + lock in share more 排他锁：select * from tableName where … + for update c）记录锁：属于行锁中的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁住的只是表的某一条记录； 精准条件命中，并且命中的条件字段是唯一索引； 【记录锁的作用：加了记录锁之后，数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交钱被其他事务读取的脏读问题】 d）间隙锁：属于行锁中的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻id之间出现间隙则会形成一个区间，遵循左开右闭原则。 【比如：表里面的数据id为1、4、5、7、10，那么，会形成以下几个间隙区间（-n-1区间，1-4区间，7-10区间，10-n区间。注：-n代表负无穷大，n代表正无穷大) 触发条件：范围查询并且查询未命中记录，查询条件必须命中索引、间隙锁只会出现在REPEATABLE_READ（重复读）的事务级别中。 间隙锁的作用：防止产生幻读问题，事务并发的时候】 e）临键锁：属于行锁的一种，并且他是innodb的行锁默认算法，总结来说就是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住， 同时也会把该范围查询内的所有间隙空间也会锁住，在之他会把相邻的下一个区间也会锁住。 【例如：表user中（主键id：1、5、10、15），数据库执行select * from user where id>1 and id<=13 froupdate; 会锁住id为1,5,10的记录，同时会锁住，1至5，5至10,10至15的区间。触发条件，范围查找并且查询命中了索引； 临键锁的作用：结合记录锁和间隙锁的特性，临建锁避免了范围查找时出现脏读，重复读，幻读问题，加了临键锁之后，在范围区间内数据不允许被修改和插入】 3，隔离级别 a）幻 读【可重复读】： 事务A按照一定条件进行数据读取，期间事务B插入了相同搜索条件的新数据，事务A再次按照原来的条件进行读取时，发现事务B新插入的数据，称为幻读； 可重复读会造成这个原因； b）不可重复读【读已提交】： 一个事务范围内，多次查询某个数据，却得到不同的结果，脏读是读到未提交的数据，而不可重复读却是已经提交的数据，但实际上是违反了事务一致性原则， 如果事务A按一定条件搜索，期间事务B删除了符合条件的某一条数据，导致事务A在次读取时数据少了一条，这种情况叫做不可重复读； c）脏读【读未提交】：通俗的讲是一个事务读取了一个事务未提交的数据，如果这个事务回滚了，数据恢复成原来状态，就产生了脏读； d）串行化：是最高的隔离级别，他通过强制事务排序，使之不可能相互冲突，从而解决幻读问题，相当于一个单线程； 4，基于锁状态分类：状态锁包括意向共享锁和意向排他锁，把他们区分为状态锁的一个核心逻辑是，因为这两个多都是描述是否可以对某一个表进行加锁的状态。 a）意向锁的解释：当一个事务视图对整个表进行加锁（共享锁或排他锁）之前，首先需要获得对应类型的意向锁（意向共享锁或意向排它锁） b）意向排它锁：当一个事务视图对整个表进行加排他锁之前，首先需要获得这个表的意向排它锁； c）意向锁的概念：如果当事务A加锁成功之后，就设置一个状态告诉后面的人，已经有人对表里面的行加了一个排他锁了，你们不能对整个表交共享锁或者排他锁， 那么后面需要对整个表加锁的人只需要获取这个状态就知道自己是不是可以对表加锁， 避免了对整个索引树的每个节点扫描是否加锁，而这个状态就是我们的意向所 5，意向锁【表锁】：允许事务在行级锁和表级锁同时存在； 作用：主要用户多粒度的锁并存情况下。比如事务A要在一张表加S锁，如果表中的行已经被事务B加X锁，那么这个锁的申请应该被阻塞。 如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响；所以引出了意向锁；举个例子： 如果过一个表中有1亿条数据，事务A把其中的几行数据加上了行锁，这时事务B需要给一个表加表锁，如果没有意向锁的话，那么就要查找这一亿条数据中是否上锁了； 如果存在意向锁，那么假如事务A在更新一条数据之前，先加意向所，在家X锁，事务B先检查该表上是否存在意向所，存在的意向所是否与自己准备加的锁冲突， 如果有冲突，则等待直到事务A释放，而无需逐条记录去检测，事务B更新表时，其实无需知道到底哪一行被锁了，他只要知道有一行被锁了就行； 主要是处理行锁和表锁之间的矛盾，能够显示“某个事务正在某行上持有了锁，或者准备去持有锁”； 八 ，MySQL视图（是一个虚拟表（不保存真实数据，所以不占用多少内存空间）：视图的数据变化会影响到表的数据，表的数据修改时也会影响到视图里面的数【insert update，delete】） 1，安全：一些数据库有着重要的信息，有些字段是保密的，不能让用户直接看到，这时候就可以创建一个视图， 在这张视图中只保留一部分字段，这样用户可以查询自己需要的字段，不能查看保密的字段。 2，性能：关系型数据的数据常常会分表存储，使用外键建立这些表的之间关系 ，这时数据通常会使用到（join）。 这样做不但麻烦，效率也比较低，如果建立一个视图，将相关的的表和字段组合在一起，就可以避免使用join查询。 3，灵活：如果系统中有一个旧的表，这行表由于设计的问题，即将被废弃，然而很多应用都是基于这张表，不易修改， 这时就可以建立一张视图，视图的数据直接映射到这个新表上，这样就可以减少很多改动；也达到了提升数据表的目的。 4，视图不是一个物化视图，他相当于一个虚拟表，本身不存储数据，当sql在操作视图时所有的数据都是从其他表中获查询出来的，这带来的问题是: 使用视图并不能将常用数据分离出来，优化查询速度。且在操作视图的很多命令都与普通表一样，这会导致在普通业务代码中的无法区分表和视图，是代码变得更复杂。 5，实现方式：分别是合并算法和临时表算法，但是这两种算法都会带来计算机额外的开销； 九，MySQL执行查询的流程 1，通过网络通讯协议接口客户端传来的sql语句。 2，查看该sql的结果是不是在缓存中，如果存在直接返回，不存在就查询磁盘。 3，由解析器来解析当前sql，最终形成初步分解析树。 4，再由预处理器对最终的解析树进行调整，完成“占位符（？？？）”等操作。 5，查询优化器对最终的解析数进行优化，包括调整失去了顺序，是否使用索引。 6，根据查优后的结果得出查询语句的执行计划，交给执行引擎。 7，执行引擎调用存储引擎提供的api，有存储引擎完成数据额查询和返回结果。 十，MySQL分区原理 1，分区表由多个相关的底层表实现，这些底层表也是由句柄对象表示，所以我们也可以直接访问各个分区，存储引擎管理分区的各个底层表和管理普通表一样，所有的底层表都必须使用相同的存储引擎，分区表 的索引只是在各个底层表上各自加一个完全相同的索引； 2，分区表的逻辑顺序： a）select：在查询一个分区表的时候，分区层先打开并锁定所有的底层表，优化器先判断是否可以过滤部分分区，然后在调用相对应的存储引擎接口，访问各个分区的数据； b）insert：当写入一条记录时，分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应的底层表； c）delete：当删除一个记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对详情底层表进行删除操作数据； d）update：当更新一条数据时，分区层先打开并锁住所有的底层表，MySQL先确定需要更新的记录在哪个分区里面，然后取出数据并更新，在判断更新后的数据应该 放在哪个分区，最后对底层表进行操作， 并对原有数据所在的底层表进行删除操作。 e）注 意：有些操作是支持过滤的，例如，当删除一条记录时，MySQL需要先找到这条记录，如果where条件恰好和分区表达式匹配，就可以将所有不包含的分区过滤掉，这对update语句同样有效，如果是 insert操作，则本身就是只命中一个分区，其他分区都会被过滤掉，MySQL先确定这个记录数据那个分区，在记录写入对应的底层分区表，无需对任何其他分区进行操作，虽然每个操作都会先打开并锁住所有 底层表，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够实现行级锁，则会在分区中释放对应的表锁； 3，分区方式： a）range分区：基于属于一个给定连续数据区间的列值，把多行分配给分区； b）list 分区：类似于按range分区，区别在于list分区是基于list分区是基于列值匹配一个离散值集合中的某个值来进行选择； c）hash 分区：基于用户定义的表达式的返回值来进行选择的分区，改表达式使用将要插入到列表中的这些行的列值进行计算一列或者多列，且MySQL服务器提供其自身的hash函数，必须由一列或者多列包含 整数值； 4，分区表优点：与单个磁盘或文件系统分区项比，可以存储更多的数据；对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据，相反，在某种情况系，添加新数据 的过程可以通过我按新数据专门新增加一个新的分区，来方便地实现，通常和分区有关的其他优点包括下面列出的这些，MySQL分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；一 些查询可以得到极大的优化，主要借助一个where语句的数据可以值保存在一个或者多个分区中，这样在查找时就不用查找其他剩余的分区；适合集合查询， 5，分区表限制： a）因为需要根据分区列来确定数据所在分区，所以分区必须作为查找条件，如果不使用分区列的查询条件，那么就无法进行分区过滤，MySQL最终会扫描所有分区。 b）所有分区必须使用相同的存储引擎； c）某些存储引擎不支持分区（merge ，csv，fedreated） d）一张表只能键1024个分区； e）分区表中无法对非分区列建立唯一索引 f）分区表无法使用外键 g）打开并锁住所有底层表的成本可能更高 h）打开并锁住所有底层表的成本可能会很高；查询访问分区表的时候，MySQL需要打开并锁住所有的底层表，这是分区表的另一个开销，这个操作在分区过滤前发生，所以无法通过分区过滤降低此开销，并且 开销也和分区类型无关，会影响所有的查询。 i）维护成本可能很高：某些分区维护操作速度非常快，例如新增和删除分区，而有些操作，例如充足分区或者类似alert语句的操作；这些操作需要复制数据，重组分区的原理于alert类似，先创建一个临时 的分区，然后将数据复制到其中，然后在删除原分区 j）分区的字段，必须是表上所有的唯一索引或者是主键索引，包含的字段子集，一个表由一个或者多个唯一索引的情况下，分区的字段必须被包含在所有的主键或者唯一索引字段中； k）分区键非null约束，如果分区键所在列没有not null约束，如果reage分区表，那么null行将分保存在最小的分区，如果是list分区表，那么null行将被保存到list为0的分区；在按hash和key分 区的情况下，产生null值的表达式MySQL都视同他的返回值为0，为了避免这种情况的产生，建议分区键设为not null l）分区键必须是int类型的，或者通过表达式返回int类型，可以为null，唯一的例外是当分区类型为key分区的时候，可以使用其他类型的列作为分区键 m）只有rang和list分区能进行自分区，hash和key分区不能进行子分区； 十一，MySQL主键和外键 1，外键（primary key）：表中经常有一个列或多列的组合，其值能唯一地标识表中的每一行，这样的一列或者多列被称为主键，通过这个可以实现表的实体完整性。（联合主键：允许一列有重复值，只要不是 所有主键列都重复即可）。（选取主键的基本原则：a，不使用任何业务相关字段作为主键；b，选择成自增类型；c，定义自增的主键的字段类型一般是bigint not null aout_increment）； 2，外键（FOREIGN KEY）：如果公共关键字在一个关系中是主键关键字，那么这个公共关键字被称为另一个关系的外键，由此可见，外键标识了两个关系之间的相关联系。（选取外键原则：a，父表必须要先生成 ，如果父表和子表是同一个表，这样的表称为自参照表，这种结构称为自参照结构；b，附表必须有主键，主键不能包含空值，但是允许在外键中有空值，也就是说外键的每个非空值出现在指定的主键中，这个外键的 内容就是正确的。c，在父表的表名后面指定列名或者列名的组合，这个列或者列的组合必须是附表的主键或者后选键；d，外键中列的数目必须和主表中主键的个数据保持一致且类型必须相同；e，外键的作用：保 持数据一致性和完整性，主要目的是控制存储在外键中的数据，使两张表形成关联，外键只能引用外表中的列的值或者使用空值；）。 3，外键问题：使用外键其实将应用程序执行的判断逻辑转移到了数据库上，那么这意味数据库的性能开销就变大了，那么对dba的要求就高了。 十二，MVCC模式（多版本并发控制） 1，是一种并发控制，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务控制； 2，当前读：就是他读取的是记录最新版本，读取时还要保证其他并发事务不能修改当前记录，会对当前的记录进行加锁； 3，快照读：想不加锁的select操作就是快照读，即使不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照会退化成当前读，之所以出现快照读的情况，是基于提高并发性能的考虑，快照 读的实现是基于多版本并发控制，可以认为mvcc是行锁的一个变种，但是他很多情况下避免可加锁操作，降低了开销，既然是基于多版本，快照读可能读到的并不是数据的最新版本，二有可能是之前历史版本。说白 MVCC就是为了实现读写冲突不加锁，而这个读指的是为实现读-写冲突不加锁，而这个读值的就是快照，当前读实际上就是一种加锁的操作，是悲观锁的实现。 4，当前读和快照读和MVCC之间的关系：是维持一个数据的多个版本，使得读写操作没有冲突，而在MySQL中要实现这个操作； 5，NVCC带来的好处：能解决读写冲突的无锁并发控制， 6，MVCC的实现原理：主要是3个隐式的字段（BD_TRX_ID：占用6byte，最近修改/插入事务ID：记录创建这条记录/最后一次修改的事务ID；DB_ROLL_PTR：7byte，回滚指针，指向这条记录的上一版本； DB_ROW_ID：6byte，隐含的自增主键id，如果数据表没有主键，innodb会自动以DB_ROW_ID产生一个聚簇索引；注意：实际还有一个删除flag隐藏字段，记录被更新或删除并不代表真的删除，而是删除flag DB_ROW_ID是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID是当前提前操作改记录的日志事务ID，而DB_ROW_ID是一个回滚指针，用于配合undo日志，指向一个旧版本；） 7，undo日志：主要分成两种【回滚日志】 a）insert undo log：代表事务在insert新纪录的产生的undo log，只在事务回滚时需要，并且在事务提交后可以被立即丢弃； b）update undo log：事务在进行update或delete是产生的undo log；不仅在事务回滚时需要，在快照读取时也需要；所以不能随便删除，只有在快照读取或事务回滚不涉及该日志时，对应的日志 才会被purge线程统一删除； c）purge：更新和删除操作都只是设置了以下老记录的deleted_bit，并不是真正的将数据删除了，为了节省磁盘空间，innodb有专门的purge线程来清理deleted_bit为ture的记录，为了不影响到 MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）。如果某个记录的deleted_bit为true，并且DB_ROW_ID相对于purge线程 的read view可见，那么这条记录一定是可以被安全清除的； d）在事务修改的时候，数据库会先对该行数据加排它锁，然后在把该行数据拷贝到undo log中，是为旧记录，即在undo log中有当前的行的拷贝副本，拷贝完毕后，修改改行值，并且修改隐藏字段中的 事务id为当前事务1的id，我们默认从1开始，之后递增，回滚指针指向拷贝到undo log的副本记录，就是上一版本的记录；事务提交后释放锁。 8，redo log：具体来说就是只要事务提交成功，那么对数据库做的修改就是被永久保存下来了，不可能因为任何原因在回到原来的状态，那么MySQL是如何保证一致性的呢？最简单的办法是在每次事务提交的 时候，将该事务设计修改的数据也全部刷新到磁盘上，但是这么做会严重的性能问题，主要体现在连个方面： a）因为innodb是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷新到磁盘的话，太浪费资源了； b）一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不是连续的，使用随机io写入性能太差； 因此设计了redo log，具体来说就是只记录事务对数据页做了哪些修改，这样就能完美的解决性能问题了； redo log包括两部分，一个是内存中的日志缓冲（redo log buffer），另一个磁盘文件为（ redo log file）。MySQL每执行一条DML【表记录的操作，主要包括数据的增删改查】语句，先将记录写入redo log buffer，后续摸个时间点在一次性将操作记录写到redo log file。这种先写日志， 在写磁盘的技术就是MySQL常说的WAL技术；计算机操作系统中，用户空间（user space）下的缓存区数据一般情况下是无法直接写入到磁盘的，中间必须经过操作系统内核空间；redo log实际上是记录了数 据页的变更，而这两种变更是没必要全部保存，因此redo log实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志。redo log存在的意义主要是降低 对数据页刷盘的请求，write pos到check point之间的部分是redo log空着的部分，用于记录新的记录，check point到write pos之间是redo log带罗盘的数据页更新记录，当write post追加上check point时，会先推动 check point向前移动，空出位置在记录新日志。启动innodb的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作；因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志块 9，什么是read view（读视图）：是事务进行快照读操作的时候产生的读视图（read view），在该事务执行的快照读的那一刻，会产生成数据库系统当前的一个快照，记录并维护系统当前活跃的事务id（每 个事务开启的时候都会生成一个事务id，这个id是递增的，所以最新事务的id最大。所以read view主要是用来做可见性判断的，即当我们摸个事务执行快照读的时候对该记录创建一个读视图，把他比作条件用 来判断当前事务能够看到那个版本的数据，即可能是当前最新的数据，也可能是undo log中的某个版本的数据。） 十三，MySQL执行查询的生命周期 1，客户端--》服务器（解析---》生成执行计划---》执行----》返回结果给客户端），在完成这些任务的时候，查询需要在不同的地方花费时间（包括网络，CPU计算，生成统计信息，执行计划，锁等待等）。 2，两个步骤分析低效查询： a）确认应用程序是不是在检索大量超过需要的数据，这通常意味着访问台多的行，但是有时候也可能是查询太多的列 b）MySQL服务器层是否在分析大量超过需要的数据行 十四，sql执行计划 1，MySQL生成查询的一颗指令树，然后通过存储执行完成这可指令树并返回结果，最终的执行计划包含了重构查询的全部信息， 十五，explain：并不需要真正的去执行查询 1，id：select查询的顺序号，包含一组数字，表示查询中执行select字句或者操作表的顺序，改字段通常与table字段搭配来分析。id相同执行顺序从上到下，搭配table列进行观察可知执行顺序。id不同 ，如果是子查询，id的序号会递增，id值越大执行优先级越高；如果是子查询id序号自增，id值越大执行优先级越高，搭配table列可知。id如果相同，可认为是同一组，执行顺序从上到下，在所有组中，id值 越大执行优先级越高。总之：id的值，标识select子句或表的执行顺序，id相同，执行顺序从上到下。id不同，值越大的执行优先级越高； 2，select_type：查询的事类型，主要用于区别普通查询，联合查询，子查询等复杂的查询， a）simple ：简单的查询，其中不包含子查询或union查询； b）primary ：查询中若包含任何复杂的子部分，最外层查询为primary，也就是最后加载的就是primary； c）subquery ：在select或者where类表中包含了子查询，就为被标记为subquery； d）derived ：在from列中包含的子查询，会被标记为derived（衍生），MySQL会递归执行这些子查询，将结果放在临时表中； e）union ：若第二个select出现在union后，则被标记为union，若union包含在from字句的字查询中，外层select将被标记为derived； f）union result：从union表获取结果的select; 3，table：显示sql操作属于那张表的 4，partition：匹配的分区，该字段应该是查看table所在的分区信息， 5，type：标识差查询所访问的类型，type值主要有八种，从最好到最差一次是：system->const->eq_ref->ref->range->index->all a）system：表只有一行记录，是const的特例类型，平时不会出现； b）const ：标识通过一次索引就能找到的数据，长出现于primary key或unique索引，因为值匹配一行数据，所以查询非常快。注意：system和const意义不大，因为单条查询本类就很快 c）eq_ref：唯一索引，对于每个索引键，表中只有一条记录与之匹配，常见为主键索引和唯一键键索引扫描； d）ref ：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，返回匹配某值的多行值，数据查找和扫描的混合体； e）range ：值检索指定范围内的行，使用一个索引来检索的开始点和结束点都是固定，不用扫描全索引； f）index ：全索引扫描，index和all的区别，index遍历所有的索引树，通常比all块，因为索引文件通常比数据文件小的多，虽然说index和all都是全表扫描，但是index是从索引中读取的。 g）all ：全表扫描； 6，possible_keys和key，key_len a）possible_keys：可能应用在表中的索引，可能一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但是不一定被查询实际使用； b）key：实际中使用的索引，如为null，则表示未使用索引，若查询使用了覆盖索引，则改索引和查询的select字段重叠； c）key_len：表示索引中所使用的字节数，通过该列计算查询用的索引长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len 是根据表定义计算而来的，并不是经过表内检索出的； 7，ref：显示关联的字段，如果过使用数等值查询，则显示const，如果是连接查询则会显示关联字段 8，rows：根据表的统计信息和索引使用情况大致估算出找到做需要的读取的行数，这个值越小越好； 9，filtered：百分比表示存储引擎返回的数据经过过滤后，剩下多少满足查询条件的记录数量的比例； 10，extra：显示时分重要的额外信息，如： a）Using filesort：表明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取；MySQL中无法利用索引完成的排序操作称为“文件排序”；出现using filesort就非常危 险了，在数据量非常大的时候，标识性能很差。 b）Using temporary：使用临时表保存中间结果，常见一排序和分组查询，性能非常差； c）Using index：标识响应的操作中使用了覆盖索引，避免访问表的额外数据行，效率还不错； 十六，MySQL慢查询 1，show processlist： 查看慢查询日志，查看当前系统中正在执行的sql查询； 2，系统问题和定位：可以经过kill掉process指令，一步步排除； 3，最后还是比较慢查询指标： a）start_time：开始时间，要通过这个参数，配合系统处问题的时间，定位哪些查询是罪魁祸首； b）query_time：查询时间 c）rows_sent和rows_examined发送的结果数以及查询扫过的行数，这两个特别重要，特别是rows_esmained，基本上就能告诉我们，哪个查询是需要注意的“大”查询； 十七：MySQL特定类型优化的查询 1，count()：有两个作用：他可以统计某个列的值的数量，也可以统计行数；也可以统计结果集中的行数；count(*)实际是忽略了所有的列直接统计的行数；所以最常见的错误是count('column')， myisam的神话：myisam的count函数在没有where的条件下非常快，因为此时无需实际的去计算表的行数MySQL可以利用存储的特性直接获取这个值，如果MySQL知道某列不可能为null值，那么MySQL 内部将count(col)表达式优化count(*) 2，优化关联查询：确保on或using字句的列上有索引。确保任何的group by和order by中的表达式设计到一个表中的列，这样MySQL才有可能使用到索引来优化这个过程； 3，优化子查询：在这种查询下建议尽量改成关联查询代替， 4，优化group by和distinct： 5，limit优化：我们通常会加上适合的order by子句，如果有对应的索引，通常效率会高一点，否则MySQL需要花费大量的性能做文件排序操作；在偏移量大的时候【如：limit 1000,20的查询】，这时 MySQL需要查询出1020条记录，然后区中取出20条，前面1000条记录都将会抛弃，这样的代价非常高；这种优化方式就是尽可能是的使用覆盖索引，而不是查询所有的列。有时候可以将limit查询转换为已知 位置的查询，让MySQL通过范围查找扫描获得到对应的数据【between 20 and 60】 6，优化union查询：MySQL总是通过创建并填充临时表的方式来执行union查询。 十八、MySQL权限语句 1，通过root登录以后创建用户： a）grand all privileges on *（库）.*（表） to 用户名@localhost identifued by '密码'； //创建新用户，可以访问本地的MySQL，【all privileges 管理员权限】 b）grand all privileges on *.* to 用户名@"%" identified by "密码"； //创建一个可以远程访问的用户 c）grand all privileges on *.* to 用户名@"192.168.10.11" identified by "密码"； //创建一个192.168.10.11可以远程访问的用户 d）grand insert ,update，delete，select on *.* to 用户名@"192.168.10.11" identified by "密码"； //创建一个192.168.10.11可以远程访问的用户,f赋予增删改查 e）alter user 'root'@'localhost' IDENTIFIED BY 'root0313'; //修改root密码 2，设置用户访问数据库权限 a）grand all privileges on test_db.* to 用户名@localhost identifued by '密码'；//设置只能访问test_db数据库 b）grand all privileges on *.* to 用户名@localhost identifued by '密码'； //设置访问所有数据库 c）grand all privileges on test_db.* user 用户名@localhost identifued by '密码'；//设置只能访问test_db数据库中的user表 3，文件导入到MySQL表中 LOAD DATA INFILE '/local/access_log' INTO TABLE tbl_name FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' 4，注意：更改完权限以后当从新启动MySQL时，所有授权表的内容被读进内存，并且从此生效，当服务器注意到授权表被改变时，表和列权限在客户端的下一次请求时生效。数据库权限改变在下一个use db_name命令生效 全局权限改变和密码改变在下一次客户端连接时生效；
