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将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
3、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
三、MySQL事务隔离级别
|
事务隔离级别 |
脏读 |
幻读 |
不可重复读 |
|
读未提交（read-uncommitted） |
是 |
是 |
是 |
|
不可重复读（read-committed） |
否 |
是 |
是 |
|
可重复读（repeatable-read） |
否 |
是 |
否 |
|
串行化（serializable） |
否 |
否 |
否 |
① 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种）
- .frm(存储表定义)
- MYD(MYData，存储数据)
- MYI(MYIndex，存储索引)
五、事务传播特性（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
十三、数据库索引什么时候会失效？
- 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
2.对于多列索引，不是使用的第一部分，则不会使用索引 (指联合索引，具体释义如下)
联合索引：两个或更多个列上的索引被称作复合索引。
对于复合索引：Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index （a,b,c）。 可以支持a | a,b| a,b,c 3种组 合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。
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 张||'%';
8.is null,is not null也无法使用索引
十四、mysql中常用的三种插入数据的语句
1.insert into表示插入数据，数据库会检查主键（PrimaryKey），如果出现重复会报错；
2.replace into表示插入替换数据，需求表中有PrimaryKey，或者unique索引的话，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
3.insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；
十五、VARCHAR(50)和VARCHAR(200)区别
最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)。在早期 MySQL 版本中， 50 代表字节数，现在代表字符数
