MYSQL加深
一、存储引擎(了解):
(一)本质:
1.表在底层存储数据的时候采用了不同的方式,是负责数据存储和检索的组件。
(二)特点:
1.默认: InnoDB;
2.存储引擎只是在mysql中特有的,Oracle中不存在,但有相同的机制,叫表的存储方式。
3.每个存储引擎都有优缺点,针对合适的方式选择合适的。常见的三个:MYISAM,InnoDB,MEMORY;
(三)三个常用的存储引擎介绍:
MYISAM
1.是MySQL数据库最常用的,但不是默认的。
2.事务:不支持事务(补充:事务控制语言TCL(Transactions Control Language))
3.空间:用三个文件表示一张表:表结构(frm),表数据(myd),表索引(myi)。
4.优点:可被压缩,只读来节省空间。
InnoDB
1.InnoDB是MYSQL的缺省引擎:
2.事务:支持事务、行级锁、外键。说白了:最安全。
3.空间:InnoDB用逻辑上的表空间tablespace来存储表内容。用xxx.frm这样的文件来存储表结构。 注:MYISAM是具体的内容来存储数据,所以可以被压缩。而InnoDB是逻辑上的,不可以被压缩来存储空间。而InnoDB在安全上更加的安全。
4、优点: 事务:安全。 提供记录事务活动的日志文件。在mysql服务器崩溃后能够回恢复。 外键,支持级联删除。
MEMORY
1.数据容易丢失,因为所有数据都存在内存当中的。
2.事务:不支持事务。
3.文件:数据和索引在内存中存储。用xxx.frm这样的文件来存储表结构。
4.(因为存在内存中)优点:查询速度快;缺点:不能存text和blob内容。
(四)选择合适的存储引擎
1、MyISAM 表最适合于大量的读少量更新的操作。另一种适用情形是使用压缩的只读表。
2、如果多数据更新操作,应使用InnoDB。其行级锁机制、为数据读取和更新、提供了良好的并发机制。
3、MEMORY存非永久数据,或是能够从基于磁盘的表中重新生成的数据。
(五) 常问问题:
(说白了就是对于以上三个优缺点的考察)
1、InnoDB/MyISAM区别:
为什么要选择InnoDB而不是MyISAM?/
InnoDB的主要特性是什么?/
MyISAM的主要特性是什么?
2、MyISAM/InnoDB+锁:
什么是锁?InnoDB如何处理并发控制?/
MyISAM和InnoDB在锁方面有何区别?/
MyISAM和InnoDB在锁方面有何区别?
3、InnoDB+事务:
什么是事务?InnoDB如何支持事务?/
什么是ACID属性?InnoDB如何确保ACID事务?/
什么是死锁?InnoDB如何处理死锁?
4、Count:
count()、count(1)、count(列名)在MyISAM/InnoDB+区别?
二、事务(必须掌握):
(一)什么是事务:
1.一个事务是一个完整的业务逻辑单元,不可再分,保证数据的完整性,安全性。只跟DML(insert,update,delete)有关。例如:a转账B,a减去100,b增加100。必须同时成功,或者同时失败。
(二)事务原理:
1.多条DML语句捆绑在一起,缓存在本地历史记录中,要全提交(commit)一下子更新硬盘。要么回滚(rollback)一下子全部删除;
(三)事务特性:
1.原子性A:事务是最小的工作单元,不可再分。
**2.一致性C:**事务必须保证多条DML语句同时成功或者失败。
**3.隔离性I:**事务A和事务B之间有隔离。
**4.持久性D:**持久性说的是最终数据必须持久化到硬盘中,事务才算成功结束。
(四)四大隔离级别:
1.第一级别:读未提交:
解释:对方事务未提交的数据我能读到。(都在同一教室,我说话,你能听见。)
问题:读未提交存在脏读(Dirty Read)现象,表示读到了脏的数据,这个数据及其不稳定,他根本不存在硬盘中。
2.第二级别:读已提交: 不可重复读。
(实际上现在的数据库都是从第二级别开始,特殊:MySQL直接是三挡起步)
解释:(我十点读到的数据跟十点一刻读到的数据不一样,对方给我改了)
问题:(对方提交之后的数据我能读到)
3.第三级别:可重复读: 解决了不可重复读的问题(对方提交之后的数据我都读不到)。
问题:读取到的数据是幻象。
解释:对方把14条数据全删了,我还能读到。
4.第四级别:序列化读:
解释:两个事务只能有一个进来
问题:效率低。
(五)常问问题:
ACID特性,事务隔离级别,什么是脏读、不可重复读、幻读?
三、索引(必须掌握):
(一)什么是索引:
1.索引相当于目录,没有索引针对where中某个字段全表扫描,有索引根据索引检索,缩小了扫描范围:
(二)创建索引,删除索引:
创建索引对象:
create index 素引名称 on 表名(字段名);
删除索引对象:
drop index 素引名称 on 表名;
(三)什么时候添加索引:
(1)数据量庞大。
(2)该字段很少的DM操作。(因为字段进行修改操作,索引也需要维护)
(3)字段经常出现在where子句中。
(四)注意:
主键和具有unique约束的字段自动会添加索引。
根据主键查询效率较高。尽量根据主键检索。
(五)索引底层原理:
B+Tree。
创建索引后,会生成索引文件,文件中有各个分区和对应的物理地址,我们先去找分区,再找对应的物理地址。在直接去本地数据文件中找到相应的物理位置查询出来。
(六)索引的分类:
单一索引: 给单个字段添加索引
复合索引: 给多个字段联合起来添加1个索引
主键索引:主键上会自动添加索引
唯一索引:有unique约束的字段上会自动添加索引
(七)索引什么时候失效:
当模糊查询第一个有百分号会失效。Select*from empt where name like ‘%m%’;
(八)常问问题:
什么是索引吗?索引优缺点?索引的分类?
什么情况下适合建索引?添加删除索引方法?
MySQL索引具体采用的哪种数据结构呢?
你提到InnoDB使用的B+树的索引模型,那么你知道为什么采用B+树吗?这和Hash索引比较起来有什么优缺点吗?(属于有难度的了)
四、三大范式:(必须掌握)
第一范式: 确保每一列的原子性不可拆分
第二范式: 在第一范式的基础上,非主字段必须依赖于主字段(一个表只做一件事)
第三范式: 在第二范式的基础上消除传递依赖
五、优化MySQL数据库:(能说出来)
1、挑选合适变量
2、连接代替子查询
3、联合取代临时表
4、事务(安全性)
5、锁定表(一致性)
6、外键(关联性)
7、使用索引
8、优化查询语句
针对索引的方法: 1、使用索引2、复合索引代替分别单个索引3、使用短索引4、索引notnull的列减少了比较性。5、排序的索引尽量少排序6、少使用带有like的子句
六、MYSQL和ORACLE区别:(能说出来)
(一)分页实现:
1.MYSQL:limit
select username from tb1 limit 50, 100;
2.Oracle:rownum:
select * from (select t.*,rownum num from tb1 t where rownum<=100 ) where num>50
(二)创建索引:
1.MYSQL:mysql中想要添加索引可以在建表时操作,也可以在后期更改;
– 1. 建表时指定
create table tb1 (username varchar(50), index username (username));
– 2. 后期更改
alter table tb1 add index username (usrrname);
2.Oracle:而在oracle中则不一样,它只能在建表完成之后操作:
CREATE INDEX tb1_username ON tb1(username);
(三)自增主键:
1.MYSQL:mysql中要使用自增主键非常方便,建表时增加 auto_increment 即可;
create table tb1 (id int(11) unsigned not null auto_increment);
2.Oracle:需要用到序列号;我们可以简单将其理解为只有一个列的表,这个表提供了 nextval 的方法,辅助我们生成自增id,样例如下:
– 1. 建普通表
create table tb1(id number not null);
– 2. 创建序列,参数比较多,自行查阅资料
create sequence seq_tb1 increment by 1 start with 1 minvalue 1 maxvalue 999999999
– 3. 插入使用
insert into tb1 (id) values (seq_tb1.nextval)
七、接下来的问题:
Java基础:八股文相关(主要是非常常见的)。Ecplain的用法。(先留着)。