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的用法。（先留着）。