一:引擎
MySQL中的数据用各种不同的技术存储在文件(磁盘)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能。主要的存储引擎主要有:Memory,MyIsam和InnoDB
MyIsam(前默认引擎):
1. 存储结构:
.frm文件,存储表的结构
.myd文件,存储表的数据
.myi文件,存储表的索引
2.存储格式: 静态型,动态型,压缩型。
2.1:静态型:表的所有列都是静态的(定长的)。这种情况下,维护和访问数据开销很低。但是会浪费很多空间,因为在存储数据的时候,每个列都会用最大空间来存储。
2.2:动态型:表中有动态型的列(不定长的)。优点是:使用空间少。缺点是:更新数据时,需要移动数据,降低了维护和访问的效率。
2.3:压缩型:针对应用程序的声明周期中,只读的数据表,我们可以通过:myisampack工具转化为MYISAM压缩表,以减少使用的磁盘空间
Memory:
1.存储结构:Memory存储引擎也会再磁盘上形成一个 .frm的表结构文件,只是表的数据件并不以文件的形式存放在磁盘上。鉴于其数据存放在内存里,因此,访问速度更快。但需要考虑的是:内存上数据的持久性
2.索引类型:默认的是哈希索引,也支持BTree索引
InnoDB(默认引擎):
1.存储结构:
.frm(定义文件)
.idb(数据文件)
2. 优点:
InnoDB是事务型数据库的首选引擎,支持事务安全表(ACID)
事务的ACID属性:
a. 原子性:原子性也就是说这组语句要么全部执行,要么全部不执行,如果事务执行到一半出现错误,数据库就要回滚到事务开始执行的地方。(实现:主要是基于MySQ日志系统的redo和undo机制。事务是一组SQL语句,里面有选择,查询、删除等功能。每条语句执行会有一个节点。例如,删除语句执行后,在事务中有个记录保存下来,这个记录中储存了我们什么时候做了什么事。如果出错了,就会回滚到原来的位置,redo里面已经存储了我做过什么事了,然后逆向执行一遍就可以了)
b.一致性:事务开始前和结束后,数据库的完整性约束没有被破坏。(eg:比如A向B转账,不可能A扣了钱,B却没有收到)
c.隔离性:同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰;
d.持久性:事务完成后,事务对数据库的所有更新将被保存到数据库,不能回滚
InnoDB支持行级锁。行级锁可以最大程度的支持并发,行级锁是由存储引擎层实现的。
二:索引
可以理解为新华字典的拼音/偏旁索引,或者图书馆的目录索引。它的作用就是帮助我们快速找到数据。
系统通过I/O从磁盘读取数据到内存时是以磁盘块(磁盘逻辑最小单位,一般是4k或者8k,物理最小单位为扇)为基本单位的,同一个磁盘块中的数据会一次性读取出来,而不是按需读取。而InnoDB存储引擎使用页作为数据读取单位(一般为磁盘块的整数倍),默认页的大小是16k.
mysql索引常用储存结构:hash表,树(基本是B+树)
https://www.cs.usfca.edu/~galles/visualization/Algorithms.htm 这边附上一个测试树结构的地址
hash表作为索引存储结构的劣势:
- 需要优化hash算法
- 无序,所以无法进行范围查询
- 需要大量的内存空间
而B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块
B+树作为多叉树,优点
1.有序
2.减少读取次数,相比较B树来说,同样层次的索引可以查到更多的数据。
索引类型
- 普通索引:最基本的索引,它没有任何限制,用于加速查询。
语法:create index indexName on table(column); - 唯一索引: 唯一 private,一般是id;
语法:create unique index indexName on table(column); - 主键索引:是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引。
语法:CREATE TABLE mytable (id
int(11) NOT NULL AUTO_INCREMENT ,name
VARCHAR(32) , PRIMARY KEY (id
) ); - 组合索引:指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合。
语法:CREATE INDEX index_mytable_id_name ON mytable(id,name);
打一比方 alter table users add index lname_fname_age(lname,fname,age);
创建了lname_fname_age多列索引,相当于创建了(lname)单列索引,(lname,fname)联合索引以及(lname,fname,age)联合索引。 - 全文索引:主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引配合match against操作使用,而不是一般的where语句加like。
语法:CREATE FULLTEXT INDEX index_article_contents ON article(contents);
其他知识点:
- 索引为什么一般会选择int/long类型?
因为 int/long类型字节小,唯一性多且可自增。比如int和var(4) 都是4个字节,但是int可以使用auto_increment 来自增长。 - 为什么对索引建议使用自增?
InnerDB使用的B+树模型,B+树的结构:所有左节点< 节点< 右节点,当新增主键破坏了现有树形结构的时候,就需要将大于这个值得主键向后挪用。当需要挪动的数据页已经处于存满的状态,这个时候会进行页分裂,当然不足的话会有页合并。但是当我们使用主键递增ID的时候,所有新增的主键都为当前主键的最大值,所以只需要在索引树的最右边加上一个记录即可
BUT
当然并不是所有的表都用自增ID好,例如这样的情况:
1、只有一个索引
2、该索引必须是唯一索引
因为业务的特性,我们一般并不会使用自增ID,可能主要还是会使用业务ID,那么为了高效的搜索,我们会给业务ID加上索引。
三:锁
锁最大的功能:保证事务的隔离姓,一致性。
查看表的锁信息:show engine innodb
锁有很多:乐观锁,悲观锁,意向锁,自增锁,记录锁,间隙锁,临键锁,排他锁,共享锁。