MySql综合知识汇总

本文实验的测试环境：Windows 10+cmd+MySQL5.6.36+InnoDB

Mysql驱动：com.mysql.jdbc.Driver

MysqlURL:jdbc:mysql://localhost:3306/数据库名称

Oracle驱动：oracle.jdbc.OracleDriver

OracleURL：jdbc:oracle:thin:@localhost:1521:SID

jdbc:oracle:thin:@//localhost:1521:SERVICENAME

 

 

查看数据库当前存储引擎：show variables like '%storage_engine%';

　　　　　　　　　　　　show variables like '%storage_engine%';

事务：事务可以看做是由对数据库的若干操作组成的一个单元，这些操作要么都完成，要么都取消，从而保证数据满足的一致性和完整性

在没有commit事务前，所有增删改查操作，数据库的数据没有真正发生变化

一、事务的基本要素（ACID）

　　1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

　　 2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。

　　 3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

　　 4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

 

二、事务的并发问题

　　1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

　　2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

　　3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

　　小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

 

三、MySQL事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
不可重复读（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

事务隔离级别（4种）

    Serializable(串行化)：一个事务在执行过程中完全看不到其他事物对数据库所做的更新（事务执行的时候不允许别的事务并发执行，事务只能一个接着一个地执行，而不能并发执行）

   Repeatable Read（可重复读）：一个事务在执行过程中可以看到其它事务已经提交的新插入的记录，但是不能看到其它事务对已有记录的更新

  Read Commited（读已提交数据）：一个事务在执行过程中可以看到其它事务已经提交的新插入的记录，而且能看到其它事务已经提交的对已有记录的更新。

   Read Uncommitted（读未提交数据）：一个事务在执行过程中可以看到其它事务没有提交的新插入的记录的更新，而且能看其它事务没有提交到对已有记录的更新

 

      ① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

　　② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

　　③ Read committed (读已提交)：可避免脏读的发生。

　　④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

　　以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，执行效率就越低。像Serializable这样的级别，就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。在MySQL数据库中默认的隔离级别为Repeatable read (可重复读)。

　　在MySQL数据库中，支持上面四种隔离级别，默认的为Repeatable read (可重复读)；而在Oracle数据库中，只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别，其中默认的为Read committed级别。

 

MYSQL的默认隔离级别是repeatable-read

Oracle的默认隔离级别时read-committed

 

四、MYSQL存储引擎（4种）

   MyISAM

　　它不支持事务，也不支持外键，尤其是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。

　　每个MyISAM在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分别为：

• .frm(存储表定义)
• MYD(MYData，存储数据)
• MYI(MYIndex，存储索引)

InnoDB

　　InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

1)自动增长列2)外键约束

MEMORY

　　memory使用存在内存中的内容来创建表。每个MEMORY表实际对应一个磁盘文件，格式是.frm。MEMORY类型的表访问非常快，因为它到数据是放在内存中的，并且默认使用HASH索引，但是一旦服务器关闭，表中的数据就会丢失，但表还会继续存在。

MERGE

 　　merge存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，MERGE表中并没有数据，对MERGE类型的表可以进行查询、更新、删除的操作，这些操作实际上是对内部的MyISAM表进行操作。

 

五、事务传播特性（mandatory:强制的，nested。nested:嵌套的）

• 1. PROPAGATION_REQUIRED: 如果存在一个事务，则支持当前事务。如果没有事务则开启
• 2. PROPAGATION_SUPPORTS: 如果存在一个事务，支持当前事务。如果没有事务，则非事务的执行
• 3. PROPAGATION_MANDATORY: 如果已经存在一个事务，支持当前事务。如果没有一个活动的事务，则抛出异常。
• 4. PROPAGATION_REQUIRES_NEW: 总是开启一个新的事务。如果一个事务已经存在，则将这个存在的事务挂起。
• 5. PROPAGATION_NOT_SUPPORTED: 总是非事务地执行，并挂起任何存在的事务。
• 6. PROPAGATION_NEVER: 总是非事务地执行，如果存在一个活动事务，则抛出异常
• 7. PROPAGATION_NESTED：如果一个活动的事务存在，则运行在一个嵌套的事务中. 如果没有活动事务, 

 

 

六、三大范式：

1，第一大范式,列不可分，确保列的原子性 (如，中国北京需要拆分,)

2，第二大范式，确保表中的每列都和主键相关，也就是说每张表只能描述一件事情

3，第三大范式，需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。比如：现在有一张订单表,订单表里可以有用户的id（因为这是外键），但你不能在这个订单表里有用户年龄或用户电话字段，因为这2个字段依赖用户id，并不依赖订单表的主键id，2345

 

七、建索引时应该注意什么？

答：索引应该加在唯一、不为空、经常被查询的字段。尽量不要建在一些复杂字段，一个表索引不要超过6个，因为索引不是越多越好,加索引虽然可以加快查询速度，但会减慢增删改速度，因为你每次增删改，数据库都会自动更新索引（消耗大量的IO），

字段内容重复多的不建议加索引，原因如下：当检索到这类数据的时候，要获取很多索引数据，和很多的行地址要获取很多的索引数据, 和很多的 行地址, 然后再 根据行地址,去检索行的信息.

八、什么时候用索引，

答：加索引的目的是为了加快查询速度，如有一个sql语句，select id from t where id=10000,这样查找会很慢，可以在id这一列加一个索引，因为索引的底层是基于某种算法，可以大大加快查找速度,

3.为什么重复多的字段不适合建索引

如果有很多重复值的列,建在索引上面.

那么当检索到这类数据的时候.

要获取很多的索引数据, 和很多的 行地址, 然后再 根据行地址,去检索行的信息.，不但不会加快查询速度，反而会影响效率

九、sql优化

1.’对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.select后面尽量避免使用*, 用*会进行全表扫描，要查什么自己加对应的字段即可

3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如(就是说不要使用有null值的字段作为查询)

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

4.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

5.应尽量避免在 where 子句中对字段进行表达式操作(条件有函数)，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

6.索引并不是越多越好，索引固然可以提高相应select效率,但会减慢插入和修改速度，这是因为索引是写入到磁盘中去的，一张表的索引最好不要超过6个

 

SQL优化：尽量使用where 字句，先过滤后再分组，如下

求部门编号为10的平均工资：这里涉及SQL优化

select deptno,avg(sal) from emp where deptno=10 group by deptno;

        

 

十、反三大范式（应用场景）：

例：有一张货物表，货物表里有单价和数量，但没有总金额，那应不应该加一个总金额字段呢？按照三大范式规则，不能加，但是在实际开发中，应该把总金额放入列中，为什么？

a. 这里使用了反三范式，为什么要使用反三范式？提高查询速度

b. 体现在分布计算，当添加货物时，必须先计算总金额才能加入到了货物表中（总金额的结果在插入时得到的）。查询时无需计算就能得到结果。速度提高百倍。

c. 缺点：出现数据不一致情况。

d.总结：通过空间换效率。

 

十一、数据库一对一，一对多，多对多关系如何处理，如何体现？

一对一：如学生表对学生档案表

一对多：在多的表建立外键

多对多：建立第三方的表，该表必须关联2表的主键。

拆分关系。增加一个表。使之符合范式。

比如做学生选课系统。多个学生选多门课。这是多对多关系。

这样可以写成三个表。

分别为。学生表（学号，姓名）

课程表（课程号，课程名）

选课表（学号，课程号）

通过选课表，将学生和课程联系起来了。

 

十二、SQL的执行顺序?

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. DISTINCT

7. UNION

8. ORDER BY

 

十三、数据库索引什么时候会失效？

1. 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

　　注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

　　2.对于多列索引，不是使用的第一部分，则不会使用索引

　　3.like查询是以%开头（以%结尾是可以的）

　　4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

　　5.我们知道建立索引时，给每一个索引列建立一个条目，如果查询条件为等值或范围查询时，索引可以根据查询条件去找对应的条目。反过来当查询条件为非时，索引定位就困难了，执行计划此时可能更

　　倾向于全表扫描，这类的查询条件有：<>、NOT、in、not exists

　　select * from test where id<>500;

　　select * from test where id in (1,2,3,4,5);

　　select * from test where not in (6,7,8,9,0);

　　select * from test where not exists (select 1 from test_02 where test_02.id=test.id);

　　6.某个表中，有两列（id和c_id）都建了单独索引，下面这种查询条件不会走索引

　　select * from test where id=c_id;
　 7.当使用模糊搜索时，尽量采用后置的通配符，例如：name||’%’，因为走索引时，其会从前去匹配索引列，这时候是可以找到的，如果采用前匹配，那么查索引就会很麻烦，比如查询所有姓张的人，就可以去搜索’张%’。相反如果你查询所有叫‘明’的人，那么只能是%明。
     这时候索引如何定位呢？前匹配的情况下，执行计划会更倾向于选择全表扫描。后匹配可以走INDEX RANGE SCAN。

　　　所以业务设计的时候，尽量考虑到模糊搜索的问题，要更多的使用后置通配符。

　　select * from test where name like 张||'%';

 

十四、mysql中常用的三种插入数据的语句

1.insert into表示插入数据，数据库会检查主键（PrimaryKey），如果出现重复会报错；

2.replace into表示插入替换数据，需求表中有PrimaryKey，或者unique索引的话，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；

3.insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；