【原创】Mysql面试题
Mysql存储引擎(了解)
1.存储引擎的介绍：
存储引擎是Mysql中特有的术语，是一个表存储数据的方式。Mysql支持九大存储引擎。Mysql版本不同支持的存储引擎不同。 2.常见的存储引擎： ①MyISAM存储引擎管理表的特征：使用三个文件来表示每个表：格式文件mytable.frm(存储表结构)、数据文件mytable.MYD(存储表中的数据)，索引文件mytable.MYI(存储表上的索引)。优点：可以被转换为压缩，只读表来节省空间，缺点：不支持事务，安全性低。 ②InnoDB存储引擎：mysql默认的存储引擎。是重量级的存储引擎。支持事务(可以保证数据的安全)，支持数据库崩溃后的恢复机制。每个InnoDB表在数据库目录中以.frm格式文件存储表格式，InnoDB表空间tablespace(逻辑名称)用于存储表的内容和索引。优点：非常安全，缺点：效率低，不能压缩不能转换为只读，不能很好的节省内存空间。 ③MEMORY存储引擎：内存存储引擎，每个表的格式文件存储在.frm文件中，表数据和索引存储在内存中(查询速度快)，支持表级锁机制。优点：查询效率高。缺点：不安全，服务器关闭后，保存在内存中的数据和索引消失。
Mysql中的事务
1.事务的介绍：
一个事务本质上就是一个完整的业务逻辑(指的是完成一项业务操作所有的数据库语句就是一个事务)。事务中要求每条操作数据库的语句要么同时成功，要么同时失败。只有DML(insert,delete,update)的多条操作语句才需要事务机制。Mysql默认情况是支持事务的自动提交，每执行一条DML语句就会自动提交。InnoDB存储引擎中提供一组用来记录事务性活动的日志文件。
2.事务相关的操作：
开启事务(start transaction)：将多条DML语句中放在同一个事务中进行管理，Mysql中需要手动开启事务。 提交事务(commit)：事务接受后并全部成功时需要手动提交事务。 回滚事务(rollback)：将之前的所有DML操作全部撤销，当事务中的一条或多条事务存在问题，则需要手动进行事务的回滚。
3.事务的特性：
A：原子性 事务是最小的工作单元，不可再分 C：一致性 同一事务当中，所有操作必须同时成功，或者同时失败，保证数据的一致性 I：隔离性 多个事务之间具有一定的隔离。 D：持久性 事务最终将数据保存到硬盘上
4.事务的隔离级别：
多个事务之间的隔离，出现在多线程操作同一张表的场景 ①.读未提及(最低)：read uncommitted。事务A可以读取到事务B未提交的数据，存在脏读现象，大多数据库都不存在该隔离级别。 ②.读已提交：read committed。事务A只能读取到事务B提交之后的数据，解决了脏读问题，不可重复读取数据(一个事务读取俩次的数据可能不同)，但读取到数据都是数据库的真实数据。 ③.可重复读：repeatable read。事务A开启之后，不管多久以后，无论数据库中的数据是否改变，事务A再次读取到的数据都和第一次一致，存在幻读问题，读取到的数据库中的数据可能不是真实数据。Mysql中默认事务隔离级别就是可重复读。 ④.序列化/串行化(最高)：serializable。事务排队操作数据库，不能并发。每次读取到的数据都是真实的。效率低。类似于Java中的Synchronized同步。
5.事务传播行为：
传播行为：业务方法在调用时，事务在方法之间的传递和使用。标志方法有无事务①Required： Spring的默认传播行为，表示在业务方法在执行时，如果存在事务就执行事务，如果没有事务则新建事务，方法在新建事务中执行。 ①Supports： 支持事务。有无事务都会正常执行。 ③Requires_new： 方法每次都需要一个新事务，如果已存在事务，重新创建一个事务执行，直到新事物完成，原事务才会执行；如果没事务，则新建事务执行。
Mysql的索引(Mysql数据库优化的重要手段)
1.索引的介绍：在数据库表的字段上添加的，为了提高查询效率存在的一种机制。一张表的一个字段可以添加一个索引，多个字段也可以添加为一个索引。在Mysql数据库中索引也是需要排序的，索引的排序和TreeSet数据结构相同。TreeSet底层是个自平衡的二叉树，在Mysql中的索引是一种B-Tree数据结构。遵循左小右大的存放原则。 2.索引的实现原理：在任何数据库中，主键和添加unique约束的字段都会被自动添加索引。索引是一个单独的对象，不同的存储引擎以不同的形式存在。在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中，索引存储在一个逻辑名叫tablespace当中，在MEMORY存储引擎当中索引被存储在内存当中。 索引查找快的原因是索引是B-Tree，树结构会根据索引列进行排序，可以减小扫描范围，避免全表扫描。 3.索引使用场景：
(1)数据量庞大。
(2)该字段经常出现在Where条件后，也就是该字段总是被扫描。
(3)该字段存在很少的DML语句，因为DML操作需要重新排序。4.索引的创建和删除：
创建索引语法：create index 索引名 on 表(列);
create index 索引名 on 表(列1，列2);
删除索引语法：drop idnex 索引名 on 表;5.索引失效的情况：
(1)模糊查询中使用"%"开头的，索引就会失效。
(2)使用or字段进行查询时，or两边的条件字段都要有索引，否则索引不生效。
(3)使用符合索引(两个字段或者更多字段联合起来的索引)没有使用索引中的第一个列字段进行查询就会索引失效。
(4)在where条件中，索引列字段参加了运算，索引失效。
(5)在where当中索引列使用了函数，索引就会失效。6.索引的分类：
(1)单一索引:一个字段上添加的字段。
(2)符合索引：两个字段或者更多的字段上添加索引。
(3)主键索引：主键上添加索引。
(4)唯一性索引：具有unique约束的字段上添加索引。DBA常用命令
数据库的导入和导出：
1.导出命令(window命令窗口上执行)：mysqldump 数据库名>硬盘文件绝对路径。
2.导入命令(需要先登录到数据库才可以导入)：source sql文件路径。数据库设计三范式
1.数据库三范式的介绍：
第一范式：要求任何一张表必须有主键，每个字段原子性不可再分。
第二范式：建立在第一范式之上，要求所有非主键字段必须完全依赖主键，复合主键中不能产生部分依赖。
第三范式：建立在第二范式基础上，要求所有非主键字段直接依赖主键，不要产生传递依赖。2.数据库三范式的作用：按照数据库三范式设计的表，可以避免数据的冗余，空间的浪费。
Mysql两张表取并集，交集，差集
示例表
1.并集
查询并集（union all） SELECT oname,odesc FROM object_a UNION ALL SELECT oname,odesc FROM object_b
查询并集（union） SELECT oname,odesc FROM object_a UNION SELECT oname,odesc FROM object_b
Union自带去重
2.交集
SELECT a.oname,a.odesc FROM object_a a INNER JOIN object_b b ON a.oname=b.oname AND a.odesc=b.odesc
3.差集
SELECT a.oname, a.odesc FROM object_a a LEFT JOIN object_b b ON a.oname = b.oname AND a.odesc = b.odesc WHERE b.id IS NULL
Mysql优化
1.查看Sql执行计划：可以通过EXPLAIN关键字查看SQL执行计划(explain select * from 表)
1502604 |
|---|
关键字介绍：
type列，连接类型。一个好的sql语句至少要达到range级别。杜绝出现ALL级别。
key列,使用到的索引名。如果没有设置索引，值为NULL。
key_len列，索引长度
rows列，扫描行数。该值为预估值。
extra列，详细说明。2.SQL语句的优化：
(1)SQL语句中的IN包含的值不应过多。
(2)SELECT语句最好指定查询的字段。
(3)当只需要一条数据时，使用limit 1。
(4)给需要排序的字段添加索引，因为索引的结构为B-Tree结构，会自动对数据库进行排序。
(5)区别使用in和exists，in和exists的区别为驱动顺序的改变(查询表的顺序不同)。
子查询时，exists是以子查询的外表为驱动表，先被访问，如果是in，以内表为驱动，先被访问。
所以in适合外表大而内表小的情况，exists适合外表小而内表的的情况。
(6)not in 和 not exists查询建议使用not exists，not in效率低而且可能存在逻辑问题(如其中一个表查询的值为Null)
(7)合理使用分页，如果数据量大，则需要使用where配合limit使用。
(8)避免在where条件使用对null的判断，这样会导致索引失效。
(9)避免在有索引的字段上使用模糊查询，因为"%"开头的模糊查询会使索引失效。
(10)避免在有索引的列名进行运算，会导致索引失效。
(11)联合索引中如果不使用索引的第一个列进行查询也会导致索引失效。
(12)连接查询的优化：
LEFT JOIN 左表会作为驱动表
RIGHT JOIN 右表会作为驱动表
INNER JOIN MYSQL会自动找出数据少的表作为驱动表