MySQL进阶

MySql架构

连接层

负责接受客户端程序的连接、授权和认证

服务层

接受Sql，负责调用函数，存储过程，触发器，对Sql执行顺序排序、优化。

如果是查询操作，还可以从缓存中先查询数据，提高性能

引擎层

引擎是具体的执行者。MySQL提供不同的执行引擎，不同的引擎特点不同，根据需要选择即可。

物理文件存储层

在硬盘上存储系统文件，表数据和各种日志文件。

Mysql引擎

1.有哪些引擎

InnoDB、MyISAM、MEMORY

2.InnoDB和MyISAM的区别

• InnoDB支持事务，MyISAM不支持事务
• InnoDB支持外键，MyISAM不支持外键
• InnoDB和MyISAM均支持B+ 树数据结构的索引，InnoDB支持聚簇索引，而MyISAM支持非聚簇索引
• InnoDB不保存表的总行数，而MyISAM保存表的总行数
• InnoDB支持行锁和表锁，而MyISAM只支持表锁
• InnoDB在MySQL5.6及之后支持全文索引，MyISAM一直支持全文索引

索引

什么是索引

索引是帮助Mysql高校获取数据的数据结构，这些数据结构会以某种方式指向数据。

比如：书的目录和书的正文内容之间的关系，为了查找书中的内容，通过对内容建立索引形成目录。

索引的优缺点

• 优点
  • 可以提高数据的检索速度，降低数据库的IO成本。
  • 通过索引进行排序，降低数据排序的成本。
• 缺点
  • 索引会占磁盘空间。
  • 执行新增、修改、添加的操作时，需要对索引结构进行更新。

索引分类

• 主键索引：设定为主键后数据库会自动建立索引

• 唯一索引：索引列的值必须唯一，允许为null

• 全文索引：在MySql5.6以前的版本，只有MyISAM支持全文索引；在MySql5.6及之后，InnoDB和MyISAM支持全文检索。

  使用模糊查询时，会导致一般的索引失效，就会使用到全文索引

• 单值索引：一个索引对应一个列，一个表可以有多个单值索引

• 复合索引（组合索引）：将数据库中的多个字段组合起来形成的一个索引就是复合索引

  • 复合索引最左前缀原则：例如a,b,c三个列。a，b列创建了复合索引，在查询是要满足最左侧索引原则，否则会失效。

  ​ *最左原则：在SQL语句中，复合索引的第一个字段必须出现查询语句中，这样索引才能够被使用。

索引的数据结构

• B+树的特点

B+树的数据是有序的；

非叶子节点不存储数据，只存储索引，一个节点可以存储多个索引；

所有的数据都存储在叶子节点上，每个叶子节点之间都有指针。

扩展

B树和B+树的区别

B+ 树所有的数据都在叶子节点上，并且每个叶子节点通过指针链接，且从左到右顺序排序。

B树的叶子节点和非叶子节点都可以存储数据。

B树的查找只要匹配到元素，不管在什么位置；而B+树的查找必须匹配到叶子节点上，所以B+树更稳定。

聚簇索引和非聚簇索引

• 聚簇索引

  找到索引就找到了数据，那么这个索引就是聚簇索引，主键就是索引。

  InnoDB引擎，索引和数据都在同一文件上，找到索引就可以找到数据

• 非聚簇索引

  索引的存储和数据的存储是分离的，找到索引，还需要回表查询。

  MyISAM引擎，索引和数据存储在两个文件中，找到了索引，还需要去存储数据的文件中查找数据。

事务

什么是事务

事务就是一次数据库操作中的若干单元的管理，事务管理的目标是完整性。一次中的若干操作要么都执行成功，要么都失败。

事务管理的特征

ACID 原子性

​ 一致性

​ 隔离性

​ 持久性

• 原子性：事务是最小的执行单位，就是要保证一次操作中的多条语句，要么都执行成功，要么都失败
• 隔离性：控制多个事务并发读写时，保证互不影响，让并发事务的数据库独立。
• 持久性：事务提交之后，对数据的修改时持久的，及时数据库发生的故障也不会对其有影响。
• 一致性：事务执行之前和事务执行之后，数据库的完整性都没有被破坏。比如：不能出现转出100元，转入50元的情况。

隔离的四种级别

隔离级别	脏读	不可重复读	幻读
读未提交	Y	Y	Y
读已提交	N	Y	Y
可重复读	N	N	Y
串行化	N	N	N

• 读未提交

  一个事务可以读到另一个事务还未提交的数据。可能会出现脏读、不可重复读和幻读

• 读已提交

  一个事物只能读到另一个事务已经提交的数据。解决了脏读，未解决不可重复读和幻读

• 可重复读

  同一个事务中多次读取相同的数据返回的结果一样。解决了脏读和不可重复读，未解决幻读

• 串行化

  这是最高级的隔离级别，对表进行操作时，事务只能一个一个的执行，如果一个事务在执行中，即使读操作，其他事务也必须等待。解决了所有的问题

脏读

​ 一个事务读取到另一个事务未提交的数据。比如事务A读到事务B更新的数据，然后事务B回滚，那么事务A读到的数据为脏数据。

不可重复读

​ 一个事务中两次读取的结果不一致。比如事务A读取的数据为10，在此期间事务B将数据改为20，并且提交后，事务A读到的数据和上一次不同，这种情况称为不可重读。

幻读

​ 一个事务中两次读取的数据量不一致。事务A第一次查询到两条结果，事务B向表中添加一个新的数据，变为3条，事务A再次查询时，结果变为3条，这种情况称为幻读。

锁

锁是什么

锁是计算机协调多个进程或线程并发问某一资源的机制。锁保证数据并发访问的一致性、有效性；锁冲突也是影响数据库并发访问性能的一个重要因素。

按照粒度可以分为：行级锁、间隙锁、表级锁

MySql中不同的存储引擎支持的锁不一样的，例如MyISAM支持表锁；InnoDB支持表锁和行锁，默认支持行锁。

行级锁

​ 行级锁是MySql中锁粒度最细的一种，表示只针对当前操作的行进行加锁。行锁又分为共享锁和排他锁。

​ 优点：发生锁冲突的概率低，并发度高

​ 缺点：开销大，加锁慢，会出现死锁

共享锁
也称为读锁，为一个事务的查询语句添加共享锁后，其他事务也可以添加共享锁，但是其他事务就不能添加排他锁。也就是说其他事务只能读不能写

排他锁
排他锁也称为写锁，当执行新增、修改、删除会自动加锁，有事务进行写操作时，其他事务不能执行。

间隙锁

​ 锁的是一个区间，当我们用范围条件而不是相等条件检索数据，InnoDB 会给

符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记

录，叫做“间隙",InnoDB 也会对这个“间隙”加锁。

例如：一个表中的id有1，2，4，5

select * from user where id >= 1 and id <=5 (此时事务1为id值1~5的数据加了锁，包括不存在的id=3的行，对3来说就是间隙，所以3加了间隙锁)

表级锁

​ 表级锁是MySql中锁粒度最大的一种锁，表示对当前操作的整张表进行加锁。

​ 优点：开销小、加锁快、不会出现死锁

​ 缺点：发生锁冲突的概率高，并发度低