一、存储引擎
二、索引
三、事务
一、存储引擎:一种文件的存储机制
MySQL的默认存储引擎:V5.1之前默认存储引擎是MyISAM;在此之后默认存储引擎是InnoDB
查看默认存储引擎:show variables like '%engine%'
MyISAM: 不支持外键,不支持事务,支持全文索引,底层B+树,表锁(每次锁定一个表的数据),在线分析处理操作速度快 .frm .myi .myd 非聚集索引(数据和索引分离开来,单独存储)InnoDB:支持外键,支持事务,不支持全文索引,底层B+树,行锁,面向在线事务处理方面的应用,当没有主键唯一键时添加一个隐藏字段作为主键,采用聚集索引的方式(将索引当作数据的一部分,存储在一起),
MEMORY:将数据存储在内存,存储空间小,临时表(存储临时数据),底层哈希索引,存储效率高,varchar()当char()处理,浪费内存,不支持变长处理,不支持TEXT、BOLB,将TEXT交给MyISAM处理
ARCHIVE:归档,将数据压缩(1:10)(数据量大,查询操作不频繁)(日志数据),磁盘有多大存储多大(TB、PB)
仅支持insert/select 不支持delect
二、索引:特殊的文件、数据结构(B+/hash)
聚簇索引:按照数据存放的物理位置为顺序,能提高多行检索的速度
非聚簇索引:不按照数据存放的物理位置为顺序,对于单行的检索很快
MyISAM索引处理方式:
B+树:数据和索引分离开,叶节点为数据地址
主索引:叶节点为数据地址,依照主键(唯一键)建立,不允许键重复,
辅助索引:叶节点为数据地址,允许键重复,查询一次即可找到数据select 1InnoDB索引处理方式:
B+树:把索引当作数据的一部分进行存储,叶节点为真实数据
聚集索引:将索引当作数据的一部分,存储在一起
主索引:叶节点为真实数据,存放数据的索引
辅助索引,叶节点为主索引的索引值,避免存放真实数据造成数据冗余,不一致,select 2 O(log n)MySQL索引分类:普通索引
普通索引:最基本的索引,它没有任何限制, MyIASM默认,create index 、alter add 、create table(index)
主键索引:又叫唯一索引,索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一
全文索引:采用全文索引,数据量大,IO效率低,消耗时间,消耗空间 CHAR、VARCHAR、TEXT列作为索引 MyISAM
组合索引:最左前缀原则time tittle content
单列索引、多列索引:多个单列索引与单个多列索引的查询效果不同,执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引。索引的优化:空间换时间
那种情况下使用索引:经常查询(主键、唯一键、连接字段),范围查询,排序处理
那种情况下不使用索引:不经常查询、数值少(),数值大(add占空间大、匹配效率低),修改性能远大于检索性能(修改、调整结点,效率变差)索引的注意事项:
索引不能包含有NULL的值;使用短索引,提高IO效率,节省空间;like语句操作
三、事务:一组特定SQL的集合
事务的ACID特性:
A:原子性:一个事务的执行被视为一个不可分割的最小单元。事务里面的操作,要么全部成功执行,要么全部失败回滚,不可以只执行其中的一部分。
C:一致性:一个事务的执行不应该破坏数据库的完整性约束。
I:隔离性:通常来说,事务之间的行为不应该互相影响
D:持久性:事务提交之后,需要将提交的事务持久化到磁盘。即使系统崩溃,提交的数据也不应该丢失。隔离性、没有隔离会产生什么问题
1、脏读:事务读取了其它事务执行过程中的结果。脏读就是指当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。
2、不可重复读:事务读取了其它事务不同阶段的结果 update。是指在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的。
3、幻读:事务读取了其它事务不同阶段的结果 insert/delete。是指当事务不是独立执行时发生的一种现象,例如第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,就会发生操作第一个事务的用户发现表中还有没有修改的数据行。隔离级别
1、未提交读: 脏读,不可重复读,幻读
2、已提交读:不可重复读,幻读
3、可重复读:幻读
4、可串行化:
MySQL的隔离级别为可重复读,由间隙锁解决幻读问题,实现可串行化
查询默认的隔离级别 :select @@tx_isolation;原子性:一致性:持久性
日志:日志先行
redo log:事务将要执行的操作
undo log:事务在每一个执行点的状态
锁机制:
MyISM :表锁,非索引
select读锁
insert update delete写锁
读锁,共享读锁,兼容读锁
写锁,独占写锁,不兼容读写锁InnoDB :行锁,索引
select不加锁:事务隔离性,在事务操作过程中,不允许读取事务执行中的结果
insert update delete写锁,
读锁,共享锁,共享读锁
写锁,排它锁,排斥其它锁悲观锁:先加锁,再执行
乐观锁:先执行,遇见问题再加锁
意向锁:解决死锁问题
间隙锁:键值在条件范围内但并不存在的记录,叫做“间隙”,对这个“间隙”加锁,就是所谓的间隙锁,一方面是为了防止幻读,以满足相关隔离级别的要求,另外一方面,是为了满足其恢复和复制的需要
触发器:
触发器是一个特殊的存储过程,不同的是存储过程要用CALL来调用,而触发器不需要使用CALL,也不需要手工启动,只要当一个预定义的事件发生的时候,就会被MYSQL自动调用。创建流程:
create trigger tri_name
tri_time:时间触发器 before after
tri_event:事件触发器 insert update delete
on tbname
for each row
tri_stmt一张表最多建立多少个触发器:6:tri_time*tri_event
那些操作能触发触发器:
insert触发器:insert load replace
update触发器:update
delete触发器:delete
MySQL底层