Mysql总结(附思维导图)

Mysql

Mysql索引

使用

• 创建主键索引

  • 在对应字段后指定primary_key：id int primary key

• 创建唯一索引

  • 在对应字段后指定unique_key：name varchar(20) unique

• 创建普通索引

  • 在创建表的最后，指定某列或某几列：index(name)

• 创建全文索引

  • 在创建表的最后通过fulltext创建全文索引：
    create table articles(
    title varchar(200),
    body text,
    fulltext(title,body)
    );

• 修改原表添加主键索引

  • alter table EMP add primary key(empno);

• 修改原表添加唯一索引

  • add table EMP add unique(empno);

• 修改原表添加普通索引

  • alter table EMP add index(empno);

• 修改原表创建普通索引-需要指定索引名字

  • create index idx_name on EMP(name);

• 全文索引的使用

  • 全文索引的使用：select * from articles where match(title,body) against (‘database’);

• 删除主键索引：alter table articles drop primary key

• 删除非主键索引：alter table articles drop index title(索引名);

索引分类

• 主键索引

  • 一张表只能有一个主键索引，可以由多个列同时承担

  • 查询速度快

  • 主键索引所在的列值不能为NULL，不能重复

  • 一般是数字类型(方便排序)

• 唯一索引

  • 可以有多个唯一索引，可以由多个列同时承担

  • 查询效率高

  • 其列值可以为NULL，但不可重复

  • 给唯一索引设置NOT NULL属性则等价于主键索引

• 普通索引

  • 一个表可以有多个普通索引，可以由多个列同时承担

  • 其列值可以为NULL，可以重复

• 全文索引

  • 适用于文本内容，如文章、电子邮件、网页等。
    它会对文档中的所有文本内容进行索引，而不仅仅是对特定字段或关键词

  • 子主题 2

    • 根据文本内容的关键字和短语来搜索和检索相关文档或记录，可以实现高效的全文搜索功能

磁盘

• CHS----寻址方式

  • 磁头

    • 每个盘面都有一个对应的磁头，用来确定数据在哪一个盘面上

  • 柱面

    • 所有盘面中半径相同的同心磁道构成了柱面，因此柱面确定了数据在哪一个磁道上

  • 扇区

    • 每个磁道被划分成若干个扇区，数据在哪个扇区上也能被确定

• LBA----逻辑区块地址

  • 描述数据所在区块的通用机制，与CHS之间通过计算公式进行相互转换，使得系统软件不需要关心底层硬件具体的寻址方式

• 访问磁盘

  • 随机访问

    • 本次IO与上一次IO的扇区地址不连续，需要做比较大的磁头移动才能找到目标扇区

  • 连续访问

    • 本次IO与上一次IO的扇区地址是连续的，磁头很快就能定位到目标扇区

• 与操作系统的 交互

  • 在物理内存上，实际是被划分为一个个4KB大小的页框

  • 磁盘上的数据也会被划分成一个个4KB大小的页帧

  • 操作系统与磁盘IO的基本单位是4KB（大于扇区），提高数据加载和保存的效率

  • 不以扇区大小作为交互的基本单位，做到了操作系统与磁盘间的解耦

• 与Mysql交互

  • 磁盘与Mysql(innodb引擎) 进行IO的基本单位为16KB

    • show global status like ‘innodb_page_size’;

  • 对mysql中的数据进行增删查改的过程中需要经过计算才能定位到数据在磁盘上的位置，需要经过计算就得经过cpu来进行

  • mysql服务器在启动时就预先申请了一块内存空间–缓冲区BufferPool，用来缓存mysq与磁盘间的交互，mysql或者磁盘的数据都需要先加载到BufferPool中来完成交互IO，存储数据再根据刷新策略刷新到磁盘上。

  • 操作系统与磁盘交互单位为4KB，mysql与磁盘交互单位为16KB，由于数据到达磁盘过程中还需要经过内核缓冲区，因此实际上交互的基本单位是两个缓冲区间交互的基本单位。

  • 磁盘以4KB单位的数据读取到内核缓冲区，内核缓冲区以16KB单位的数据拷贝到Buffer Poll缓冲区中，这样就任务mysql与磁盘交互的单位为16KB

  • 以页(16KB)为单位进行交互，能’预先’将这一页的数据加载进缓冲区中（若查询的数据小于一页），这样若在查询数据时，缓冲区存在数据，就不再需要去磁盘获取了，减少了与磁盘的IO次数，提高了IO效率。根据局部性原理，大概率下一次访问的数据与本次访问的数据在同一页中。

索引底层结构

• 管理页的方式

  • 由于mysql缓冲区内的数据都是以页为单位加载进来的，因此一定有大量的页在缓冲区被换入与换出，因此Mysql需要将大量的page管理起来----先描述再组织

  • 用结构体将每个页的数据与属性封装起来，并以双链表的形式组织起来，这样与缓冲区的交互就变成了对缓冲区中双链表的增删查改

  • 每个双链表的结点就是每一页的数据和属性，若需要对数据进行增删查改，还需要将数据以单链表的形式组织起来，并且各个记录之间会按照主键进行排序

• 页内目录

  • 当页内部的数据量增多时，即使是有序的数据，查询时仍然是线性遍历，时间复杂度为O(N)，效率低下。

  • 可以将数据记录按照主键划分为若干区域，页内目录中就存储这若干区域的最小键值

  • 页内目录就是对页内的各个主键之间进行区域划分并记录该区域的最小键值，通过键值能找到记录所在区域。

  • 这样在查询数据时，可以先通过遍历目录找到目标数据所在区域的起始记录，进而更快速找到目标记录

  • 添加页内目录后Page内部能够保存的数据记录就变少了，本质是一种以空间换时间的做法

• 页外目录(页目录)

  • 随着页数量增加，查询数据时需要先遍历双链表再遍历单链表，那么性能又要付出一定的代价

  • 可以将页内目录按照最小键值划分为若干区域，页目录就存储这若干区域的最小键值

  • 页目录就是对页内各个页内目录之间进行区域划分并记录该区域的最小键值，通过键值能找到页内目录所在页

  • 这样在查询数据时，会先遍历页目录，根据最小键值找到此记录所在的页，再在页中遍历页内目录，再次根据最小键值找到此记录所在的记录区域，遍历区域进而找到记录所在处

  • 页目录也是以页为单位来记录目录信息的，因此目录信息所在的页需要以双链表的形式组织起来

• 页目录之上的目录(入口目录)

  • 随着页目录数量的增加，我们也需要一张入口目录来定位记录所在的页目录，再进行后续操作

  • 可以将页目录按照最小键值划分为若干区域，入口目录就存储这若干区域的最小键值

  • 入口目录就是对各个页目录之间的最小键值进行区域划分，并记录该区域的最小键值，通过键值能找到页目录所在的页

  • 这样查询数据时，就会先从入口目录开始，根据记录的键值遍历目录找到键值所在区域的页目录，去到对应的页中，接着层层往后查询即可找到对应记录的信息

索引的其他底层结构

• 哈希表

  • 哈希点的优点是O(1)的查询时间复杂度，但缺点是不利于进行数据的范围查找----InnoDB和MyISAM不支持

• AVL和红黑树

  • 二叉树结构，会导致树的层高较高，IO会较为频繁

主键索引

• 插入时乱序插入，底层(单链表)会自动根据主键进行排序–默认升序（让页内目录变得有意义，增加查询速度）

  • MyISAM存储引擎

    • MyISAM同样采用B+树的结构作为索引的底层

    • MyISAM与InnoDB引擎的B+树不同，其底层的叶子结点存储的是数据记录对应的地址，而非数据记录

  • InnoDB存储引擎(详细部分在底层结构)

普通索引

• 根据用户指定的列作为键值进行排序

  • MyISAM存储引擎

    • 与主键索引不同的事，普通索引的键值可以重复

    • 一张表可能会存在多个B+树(根据索引键值不同而存在不同B+树)，但由于MyISAM引擎B+中存储的是数据记录的地址，因此有效数据只会存储一份

  • InnoDB存储引擎

    • 同样的，普通索引的键值可以重复

    • 但普通索引B+树的叶子结点存放的非数据记录也非记录地址，而是对应记录的主键值

    • 当根据普通索引查询记录时，会根据普通索引对应的B+树找到目标记录的主键值，然后再查找主键索引对应的B+树找到目标记录

聚簇索引和非聚簇索引

• 聚簇索引

  • 像InnoDB这种将数据记录和索引结构放在一起的索引方案叫做聚簇索引

  • InnoDB存储引擎创建表时，数据库对应目录下会新增两个文件：.frm和.ibd

    • .frm存储的是表结构相关的信息

    • .ibd文件存储的是索引和数据相关的信息，体现了聚簇索引：索引和数据是存储在同一个文件中

• 非聚簇索引

  • 像MyISAM这种将数据记录和索引结构分离的方案叫做非聚簇索引

  • MyISAM存储引擎创建表时，数据库对应目录下会新增三个文件：.frm/.MYD/.MYI

    • .frm存储的是表结构相关的信息

    • .MYD存储的是数据相关的信息

    • .MYI存储的是索引相关的信息

    • 体现了非聚簇索引，索引和数据是分开存储的

优缺点

• 优点：提高海量数据的检索速度，正确的创建索引操作能够使得查询速度提高成百上千倍，当一张表创建索引后，在数据库底层就会为表中的数据记录构建特定的数据结构，后续在查询表中数据时就能通过查询该数据结构快速定位到目标数据

  • 比较频繁作为查询条件的字段应该创建索引

• 缺点：索引虽然提高了数据的查询速度，但在一定程度上也会降低数据增删改的效率，因为这时在对表中的数据进行增删改的操作时，除了需要进行对应的增删改操作之外，还要对底层建立的数据结构进行调整维护

  • 唯一性太差的字段不适合单独创建索引，即使频繁的作为查询条件

  • 更新非常频繁的字段不适合查询索引

  • 不会出现在where语句的字段不应该创建索引

Mysql事务

概念

• 一条或多条sql语句组成，这些语句在逻辑上存在相关性，共同完成一个任务，用于处理操作量大，复杂度高的数据。

• 同一时刻可能存在大量事务，若不加以控制，则会出现数据不一致的问题

相关命令

• begin或start transaction启动事务

• savepoint 保存点 创建保存点

• rollback to保存点 回滚到指定保存点

• rollback 直接让事务回滚到最开始

• commit命令提交事务，提交后就不能回滚

提交方式

• 自动提交

  • 可通过set autocommit=0关闭自动提交

• 手动提交

  • 通过begin或start transaction启动一个事务

  • 通过begin或start transaction启动的事务，都需要手动提交，与是否设置自动提交无关

  • 通过commit手动提交事务，提交事务后代表当前事务结束

属性

• 原子性(Atomicity)

  • 在一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。若在执行过程中发生错误，则会自动回滚到事务开始前的状态

• 一致性(Consistency)

  • 事务的执行应该使得数据库从一个一致的状态转变为另一个一致的状态、要求事务在执行中对数据的增删改必须要保证数据库的完整性和业务规则的有效性
    如果在一个银行系统中，一个事务要求从一个账户中转出一定金额到另一个账户中，那么事务的一致性要求在整个操作过程中，无论出现任何异常情况，系统都要保证总的资金不变，即必须保证转出的金额一定要在目标账户中正确地增加。如果在这个过程中发生了任何异常情况导致转出操作失败，那么被转出的账户余额不应该受到任何改变，这就是一致性的要求。

    • 需要原子性、持久性和隔离性保证
      还需要用户的业务逻辑正确有效性保证

• 隔离性(Isolation)

  • 数据库允许多个事务同时访问同一份数据，隔离性可以保证多个事务在并发执行时，不会因为由于交叉执行而导致数据不一致问题

• 持久性(Durability)

  • 事务结束后，对数据的修改是永久的，即便系统故障也不会丢失

隔离级别

• 多个事务执行自己的sql时，可能会出现互相影响的情况
  比图多个事务访问同一张表，甚至是同一条记录，为了允许事务在执行过程中受到不同程度的干扰，于是出现了隔离级别的概念

  • 读未提交–Read Uncommited

    • 所有的事务都可以看到其他事务没有提交的执行结果，相当于没有任何隔离性。

    • 存在很多并发问题，如脏读，幻读，不可重复读

  • 读提交–Read Commited

    • 大多数数据库的默认隔离级别(不是Mysql 的)，满足了隔离的简单定义：只能看到其他已经提交的事务所做的改变。

    • 存在不可重复读和幻读

  • 可重复读–Repeated Read

    • Mysql的默认隔离级别，确保在同一个事务的执行过程中，多次读取操作数据时会看到同样的数据

    • 解决了不可重复读但存在幻读（Mysql下解决了）

  • 串行化–Serializable

    • 最高隔离级别，通过强制事务排序，使之不可能相互冲突。在每个读的数据行上加上了共享锁，极端的解决了幻读问题

    • 解决了幻读，但会导致超时和锁竞争

• 查看与设置

  • 查看全局隔离级别：select @@global.tx_isolation

  • 查看会话隔离级别：select @@session.tx_isolation

  • 查看当前会话的隔离级别：select @@tx_isolation

  • 设置当前会话隔离级别：set session transaction isolation level 隔离级别

    • 只会影响当前会话，新会话依旧采用全局隔离级别

  • 设置全局隔离级别：set global transaction isolation level 隔离级别

    • 会影响后续的新会话，但当前会话的隔离级别不会发生变化，若需要变化则需要重启会话

多版本并发控制

• 并发场景

  • 读-读

    • 不需要并发控制

  • 读-写

    • 有线程安全问题，存在因为事务的隔离性
      而造成的脏读、幻读和不可重复读

  • 写-写

    • 有线程安全问题，会存在两类更新丢失问题

      • 第一类更新丢失：回滚丢失

        • 一个事务把另一个已经提交的事务更新的数据给覆盖了

      • 第二类更新丢失：覆盖丢失

        • 一个事务的提交把另一个已经提交的事务更新的数据覆盖了

Mysql锁

行锁

• 针对数据表中行记录的锁

• 排他锁–写锁

  • 当前操作没完成之前，会阻塞其他读和写操作

  • 会阻塞读和写操作

• 共享锁–读锁

  • 针对同一份数据，多个读操作可以同时进行而不会互相影响

  • 会阻塞写操作，不会阻塞读操作

  • 隔离级别串行化就是对所有读到的数据记录加上了行级别的读锁，极端解决了幻读问题

• 间隙锁

  • 使用范围条件而不是相等条件检索数据时，申请共享或排他锁时，InnoDB会给符合条件的已有数据记录的‘索引’加锁

  • 弱点就是会将某些不存在的键值进行无辜的锁定，造成锁定的时候无法插入锁定键值范围内的任何数据，可能会对性能造成很大危害

  • Next-Key Lock：行锁加间隙锁

    • 锁定一个范围，并锁定记录本身

    • 只存在于可重复读隔离级别，解决幻读

表锁

• 锁粒度大，发生锁冲突概率大，并发性低

• 排他锁

• 共享锁

意向锁

• 用于指示一个事务在未来可能会请求对某些资源的锁定

• 对某记录加共享锁前，需要对表加上一个意向共享锁

• 对某记录加排他锁前，需要对表加上意向排他锁

• 目的是为了快速判断表里是否有记录被加锁

悲观锁

• 并发量大的情况下，假定会发生并发冲突，通过数据库自身的锁机制来实现

• 适用于写操作多场景，在数据库层面阻止其他事务对该数据的操作权限。但并发性不好

乐观锁

• 并发量小的情况下，假定并不会发生冲突，不用每次都对数据上锁，也就是不采用数据库本身的锁机制，通过程序来实现。

• 适用于读操作多的场景，优点在于程序实现，不存在死锁问题

Mysql视图

概念

• 视图是一个虚拟表，其内容查询到的数据定义，和真实表一样，包含表的属性，包含一系列带有名称的列和行数据

• 不会单独存储在数据库，其数据来自定义视图是查询时所引用的表，在每次引用视图时动态生成

• 视图和基表对数据的修改会互相影响

• 若查询结果会被频繁的用到，就可以给上述结果创建视图

• 在数据库目录只会增加表属性文件.frm，因为其数据和基表使用的是同一份

使用

• 创建视图

  • create view (视图名) as
    select…

• 删除视图

  • drop view (视图名)

规则和限制

• 命名唯一，不能出现同名视图或表名

• 数量无限制，但要考虑复杂查询创建为视图之后的性能影响

• 视图不能添加索引，也不能有关联的触发器或者默认值

• 可以提高安全性，可以将复杂的查询逻辑封装在视图中
  同时通过对用户授予对视图的访问权限，限制用户直接访问底层数据表，提高安全性。

• 视图可以和普通表进行多表查询和内外连接等操作

SQL分类

DDL数据定义语言，维护存储数据的结构：create、drop、alter等

DML数据操作语言，对数据进行操作：insert、delete、update等

• DQL数据查询语言：select、from、where等

DCL数据控制语言，负责权限管理和事务：grant、revoke、commit等

Mysql架构

连接层

• 完成一些类似连接处理，授权认证及相关的安全方案

服务层

• 完成在处理底层数据之前的工作，包括权限判断、SQL接口、SQL解析、SQL分析优化、缓存查询的处理以及部分内置函数执行等。各个存储引擎提供的功能都集中在这一层，如存储过程，触发器，视图等

引擎层

• 由多种可拔插的存储引擎共同组成，真正负责Mysql中数据的存储和提取，每个引擎都有自己的优点和缺陷，服务层是通过存储引擎API来与他们交互的

存储层

• 将数据存储在裸设备的文件系统之上，完成存储引擎的交互

Mysql引擎

InnoDB

• 聚簇索引结构，数据记录与索引结构存储在一起，B+树的叶子结点存的就是数据记录

• 存储空间消耗大

• 支持事务，具备事务提交，事务回滚和崩溃修复能力

• 增删改使用的是行锁，适合大量的增删改操作

• 支持外键

MyISAM

• 非聚簇索引结构，数据记录与索引结构分开，B+树的叶子结点存的是数据记录的地址

• 存储空间消耗小

• 不支持事务

• 增删改是使用的是表锁，降低效率，适合大文本存储

• 不支持外键