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中锁粒度最大的一种锁,表示对当前操作的整张表进行加锁。
优点:开销小、加锁快、不会出现死锁
缺点:发生锁冲突的概率高,并发度低