[大数据]面试_Mysql-1 [17]

1.什么Mysql的事务？事务的四大特性？事务带来的什么问题？

Mysql中事务的隔离级别分为四大等级：读未提交（READ UNCOMMITTED）、读提交 （READ COMMITTED）、可重复读 （REPEATABLE READ）、串行化 （SERIALIZABLE）。

在Mysql中事务的四大特性主要包含：原子性（Atomicity）、一致性（Consistent）、隔离性（Isalotion）、持久性(Durable)，简称为ACID。

• 原子性：是指事务的原子性操作，对数据的修改要么全部执行成功，要么全部失败，实现事务的原子性，是基于日志的Redo/Undo机制。
• 一致性：是指执行事务前后的状态要一致，可以理解为数据一致性。
• 隔离性：侧重指事务之间相互隔离，不受影响，这个与事务设置的隔离级别有密切的关系。
• 持久性：则是指在一个事务提交后，这个事务的状态会被持久化到数据库中，也就是事务提交，对数据的新增、更新将会持久化到数据库中。

1.读未提交：读取到别的事务还没有提交的数据，从而产生了脏读。

2.读提交：读取别的事务已经提交的数据，从而产生不可重复读。

3.可重复读：事务开启过程中看到的数据和事务刚开始看到的数据是一样的，从而产生幻读，在Mysql的中通过MVCC多版本控制的一致性视图解决了不可重复读问题以及通过间隙锁解决了幻读问题。

4.串行化：对于同一行记录，若是读写锁发生冲突，后面访问的事务只能等前面的事务执行完才能继续访问。

脏读:

不可重复读

幻读

2.Mysql的InnoDB和MyISAM有什么区别？

（1）InnoDB和MyISAM都是Mysql的存储引擎，现在MyISAM也逐渐被InnoDB给替代，主要因为InnoDB支持事务和行级锁，MyISAM不支持事务和行级锁，MyISAM最小锁单位是表级。因为MyISAM不支持行级锁，所以在并发处理能力上InnoDB会比MyISAM好。

（2） 数据的存储上：MyISAM的索引也是由B+树构成，但是树的叶子结点存的是行数据的地址，查找时需要找到叶子结点的地址，再根据叶子结点地址查找数据。

（3）数据文件构成：MyISAM有三种存储文件分别是扩展名为：.frm（文件存储表定义）、.MYD (MYData数据文件)、.MYI (MYIndex索引文件)。而InnoDB的表只受限于操作系统文件的大小，一般是2GB

（4）查询区别：对于读多写少的业务场景，MyISAM会更加适合，而对于update和insert比较多的场景InnoDB会比较适合。

（5）coun()区别：select count() from table，MyISAM引擎会查询已经保存好的行数，这是不加where的条件下，而InnoDB需要全表扫描一遍，InnoDB并没有保存表的具体行数。

（6）其它的区别：InnoDB支持外键，但是不支持全文索引，而MyISAM不支持外键，支持全文索引，InnoDB的主键的范围比MyISAM的大。

3.执行一条查询语句的流程

1. 客户端发送查询语句给服务器。
2. 服务器首先进行用户名和密码的验证以及权限的校验。
3. 然后会检查缓存中是否存在该查询，若存在，返回缓存中存在的结果。若是不存在就进行下一步。注意：Mysql 8就把缓存这块给砍掉了。
4. 接着进行语法和词法的分析，对SQl的解析、语法检测和预处理，再由优化器生成对应的执行计划。
5. Mysql的执行器根据优化器生成的执行计划执行，调用存储引擎的接口进行查询。服务器将查询的结果返回客户端。

Mysql中语句的执行都是都是分层执行，每一层执行的任务都不同，直到最后拿到结果返回，主要分为Service层和引擎层。

在Service层中包含：连接器、分析器、优化器、执行器。引擎层以插件的形式可以兼容各种不同的存储引擎，主要包含的有InnoDB和MyISAM两种存储引擎。具体的执行流程图如下所示：

4.redo log和binlog

redo log日志也叫做WAL技术（Write- Ahead Logging），他是一种先写日志，并更新内存，最后再更新磁盘的技术，为了就是减少sql执行期间的数据库io操作，并且更新磁盘往往是在Mysql比较闲的时候，这样就大大减轻了Mysql的压力。

redo log是固定大小，是物理日志，属于InnoDB引擎的，并且写redo log是环状写日志的形式：

其中write pos是记录当前的位置，有数据写入当前位置，那么write pos就会边写入边往后移。

check point记录擦除的位置，因为redo log是固定大小，所以当redo log满的时候，也就是write pos追上check point的时候，需要清除redo log的部分数据，清除的数据会被持久化到磁盘中，然后将check point向前移动。

redo log日志实现了即使在数据库出现异常宕机的时候，重启后之前的记录也不会丢失，这就是crash-safe能力。

binlog称为归档日志，是逻辑上的日志，它属于Mysql的Server层面的日志，记录着sql的原始逻辑，主要有两种模式：一个是statement格式记录的是原始的sql，而row格式则是记录行内容。

redo log和binlog记录的形式、内容不同，这两者日志都能通过自己记录的内容恢复数据。

之所以这两个日志同时存在，是因为刚开始Mysql自带的引擎MyISAM就没有crash-safe功能的，并且在此之前Mysql还没有InnoDB引擎，Mysql自带的binlog日志只是用来归档日志的，所以InnoDB引擎也就通过自己redo log日志来实现crash-safe功能。

5.什么情况下索引会失效

1.有or的语句中如果条件有不是索引字段的话全部索引失效

2.like：最左前缀法则:like "X%"不失效

3.条件查询的字段是字符串，而错误的使用where column = 123 数字类型也会导致索引失效。

4.全表扫描的时候,会放弃索引

5.范围后的索引失效

6.索引列上进行运算操作,索引失效

7.in走，not in不走

8.在where条件查询的后面对字段进行null值判断，会导致索引失效，解决的办法就是可以把null改为0或者-1这些特殊的值代替

6.索引的种类

索引从数据结构进行划分的分为：B+树索引、hash索引、R-Tree索引、FULLTEXT索引。

索引从物理存储的角度划分为：聚族索引和非聚族索引。

从逻辑的角度分为：主键索引、普通索引、唯一索引、联合索引以及空间索引。

7.SQL优化

1.插入大量:以主键顺序插入,关闭唯一校验:如果存在唯一索引,需要关闭:set unique_checkes=0,手动提交事务set autocommit=0

2、优化insert:在事务中插入,数据有序插入

3.orderby优化:
explain select id, age from users order by age
id与age有索引,才可以不可以为*,order by根据索引,且需要和索引的顺序保持一致
不可以一升一降

4.group by
sql_mode：优化:
优化:是组函数或者聚合函数或者分组字段
select * from t_emp group by deptId; ×
select deptId, max(age) from t_emp group by deptId order by null; √

5.嵌套查询:
多表连接替换子查询
inner/left/right join
inner和where结果一样,但是where有多余的笛卡尔积,效率低

6.or优化:
使用union替换or
explain select * from user where id = 1 or id = 2
explain select * from user where id = 1 union select * from user where id = 2

7.limit优化
分页优化
1.在索引上进行排序,然后limit
select id from user order by id limit 20000, 10;
2.适用于主键自增:必须为主键自增,而且不可以有断层
可以把limit转化为某个位置的查询
select * from user where id > 20000 limit 10;

8.对于条件查询，首先考虑在条件where和order by后的字段建立索引。

9.避免索引失效，避免where条件后进行null值的判断。

10.避免where后使用!=或<>操作符。

11.避免在where后面进行使用函数。

12.避免where条件后使用or关键字来连接。

8.应用优化

1.连接池技术:c3p0.druid, dbcp
2、减少对Mysql访问:可以一次查询的不要多次查询
3.增加cache层
4.负载均衡:使用Mysql主从复制,使用分布式数据库架构

9.缓存优化

配置参数:
查看是否支持缓存:show variables like “have_query_cache”;
查看是否开启缓存:show variables like “query_cache_type”:
查看缓存大小:show variables like “query_cache_size”;
开启查询缓存:vi /user/my.cnf query_cache_type=1
查看缓存状态信息:show status like “Qcache%”;

设置query_cache_type=2:
如果设置了缓存的话加入SQL_CACHE走缓存

select SQL_CACHE id from user where id = 3;
SQL_NO_CACHE不走缓存

缓存失效:
1.语句不同
2.查询now(),不确定
3.不查询表:select ‘A’;
4.查询mysql,Information_schema,performance_schema不走缓存
5.如果改变表的话会清空缓存

10.内存优化

原则:尽量多的 内存分配给Mysql缓存
MyIsam存储引擎基于IO缓存,内存给os做IO缓存
排序区,连接区是给每一个数据库会话session专业的,需要合理给

MyISAM内存优化:
key_buffer_size
read_buffer_size
read_rnd_buffer_size
Innodb:
innodb_buffer_pool_size
innodb_log_buffer_size

11.什么是聚簇索引和非聚簇索引

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

12.主键使用自增ID还是UUID?能说说原因吗

自增ID和UUID作为主键的考虑主要有两方面，一个是性能另一个就是存储的空间大小，一般没有特定的业务要求都不推荐使用UUID作为主键。

因为使用UUID作为主键插入并不能保证插入是有序的，有可能会涉及数据的挪动，也有可能触发数据页的分裂，因为一个数据页的大小就是16KB，这样插入数据的成本就会比较高。

而自增ID作为主键的话插入数据都是追加操作，不会有数据的移动以及数据页的分裂，性能会比较好。

另一方面就是存储空间，自增主键一般整形只要4个字节，长整形才占8字节的大小空间，而使用UUID作为主键存储空间需要16字节的大小，会占用更多的磁盘，在二级索引中也会存出一份主键索引，这样多占用消耗的空间就是两倍，性能低，所以不推荐使用。