Mysql面经_代码007

本文介绍: 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。

Select语句的执行 顺序

1、from 子句组装来自不同数据源的数据；
2、where 子句基于指定的条件对记录行进行筛选；
3、group by 子句将数据划分为多个分组；
4、使用聚集函数进行计算；AVG() SUM() MAX() MIN() COUNT()
5、使用 having 子句筛选分组；
6、计算所有的表达式；
7、select 的字段；
8、使用 order by 对结果集进行排序。

where和having的区别

where是一个约束声明,使用 where来约束来自数据库的数据;
where是在结果返回之前起作用的;
where中不能使用聚合函数。
having:
having是一个过滤声明;
在查询返回结果集以后，对查询结果进行的过滤操作;
在having中可以使用聚合函数。
where和having的执行顺序：where早于group by早于having。

count（*）和count（列名）的区别?

1、count(*)包含了所有的列，相当于行数，在统计结果的时候，不会忽略列值为空的情况；
2、count(1)在统计结果的时候也不会忽略列值为空的情况（即某个列为空时，仍进行统计）；
3、count(列名)在统计的时候会忽略列名为空（null）的情况（即某个列为空时，不统计）；

数据库一二三范式的作用？

第一范式就是属性不可分割，每个字段都应该是不可再拆分的。（姓名）
第二范式是在第一范式的基础上更进一步。第二范式就是要求表中要有主键，表中其他字段都依赖于主键，因此第二范式只要记住主键约束就好了。
第三范式就是确保数据表中的每一列数据都和主键直接相关，而不能间接相关。也就是要消除传递依赖，方便理解，可以看做是消除冗余，因此第三范式只要记住外键约束就好了。

范式可以避免数据冗余，减少数据库的空间，减轻维护数据完整性的麻烦。范式越高性能就会越差。一般在项目中，用得最多的也就是第三范式

InnoDB的Buffer Pool MySQL日志

redo log和undo log的区别
1.redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。

2.undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

redo log和二进制日志的区别

二进制日志是在存储引擎的上层产生的，不管是什么存储引擎，对数据库进行了修改都会产生二进制日志。而redo log是innodb层产生的，只记录该存储引擎中表的修改。并且二进制日志先于re do log被记录
二进制日志记录操作的方法是逻辑性的语句。即便它是基于行格式的记录方式，其本质也还是逻辑的SQL设置，如该行记录的每列的值是多少。而re do log是在物理格式上的日志，它记录的是数据库中每个页的修改。

（1）作用不同：re do log是用于 crash recovery的，保证MySQL宕机也不会影响持久性；binlog是用于 point–in–time recovery的，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制。

（2）层次不同：redo log是InnoDB存储引擎实现的，而binlog是MySQL的服务器层(可以参考文章前面对MySQL逻辑架构的介绍)实现的，同时支持InnoDB和其他存储引擎。

（3）内容不同：redo log是物理日志，内容基于磁盘的Page；binlog的内容是二进制的，根据binlog_form at参数的不同，可能基于 sql语句、基于数据本身或者二者的混合。

（4）写入时机不同：binlog在事务提交时写入；redo log的写入时机相对多元：

mysql中drop、truncate和delete的区别

delete和truncate只删除表的数据不删除表的结构速度,一般来说: drop> truncate >delete delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效; 如果有相应的trigger,执行的时候将被触发.
truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚.
操作不触发trigger.

事务的ACID特性

a cid，是指在数据库管理系统中事务所具有的四个特性：原子性、一致性、隔离性、持久性。
原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性（Consiste ncy）：事务完成时，必须使所有的数据都保持一致状态。
隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
而对于这四大特性，实际上分为两个部分。其中的原子性、一致性、持久化，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志。而持久性是通过数据库的锁，加上MVCC来保证的。

说一下MySQL执行一条查询语句的内部 执行?

1.首先使用登录命令登录 MySQL，登录后，输入一条查询语句；
2.执行后，会首先查询缓存，若缓存中有对应的数据，直接返回，若没有，则会找分析器进行下一步操作，注意，只有在开启查询缓存时才会执行这一步，MySQL 8.0 版本后就没有查询缓存了，语句会直接走分析器；
3.分析器首先对 SQL 语句进行词法分析，根据输入的 select，判断这条语句是查询语句，并将后面的字符串识别成对应的表名与列名，随后会对 SQL 语句进行语法分析，判断这条语句是否符合 MySQL 的语法规则，若符合，则会进入优化器阶段；
4.优化器会根据语句来选择这条语句的具体执行方案，然后交给执行器执行；
5.执行器会判断用户对要使用的表有没有执行查询的权限，若没有权限，就会返回没有权限的错误

innodb和myisam的特点与区别？

InnoDB支持事务，MyISAM不支持，
InnoDB支持外键，而MyISAM不支持
InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的
MyISAM是非聚集索引，索引和数据文件是分离的，索引保存的是数据文件的指针。

InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

MySQL建立索引方式简单说一下索引的优缺点

一、索引的优点
1）创建索引可以大幅提高系统性能，帮助用户提高查询的速度；
2）通过索引的唯一性，可以保证数据库表中的每一行数据的唯一性；
3）可以加速表与表之间的链接；
4）降低查询中分组和排序的时间。
二、索引的缺点
1）索引的存储需要占用磁盘空间；
2）当数据的量非常巨大时，索引的创建和维护所耗费的时间也是相当大的；
3）当每次执行CRU操作时，索引也需要动态维护，降低了数据的维护速度

索引的底层 实现？ B+树

所有的数据都会出现在叶子节点。
叶子节点形成一个单向链表。
非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

聚簇索引和非聚簇索引

含义：将数据存储与索引放一块，索引结构的叶子节点保存了行数据，将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键
特点：必须有，而且只有一个（如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个row id作为隐藏的聚集索引。）可以存在多个
聚集索引的叶子节点下挂的是这一行的数据。
二级索引的叶子节点下挂的是该字段值对应的主键值。

索引最左前缀/最左匹配了解吗？

最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段)必须存在。不存在会导致联合索引不被使用。

mysql索引命中规则——最左匹配原则索引失效的情况

最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段)必须存在。不存在会导致复合索引不被使用。

如何分析SQL语句的性能，要关注 哪些字段？

1、查询语句中不要使用select *
2、尽量减少子查询，使用关联查询（left join,right join,inner join）替代
3、减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代
4、or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，
union all会更好)
5、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表
扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0

什么是脏读，不可重复读和幻读？

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提
交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数
据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
不可重复读（Unrepea table read）: 指在一个事务内多次读同一数据。在这个事务还没有结
束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数
据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

innodb引擎的4大特性

插入缓冲（insert buffer)；

二次写(double write)；

自适应哈希索引(ahi)；

预读(read ahead)。

为什么Elasticsearch比MySql的检索快？

1）基于分词后的全文检索：例如select * from test where name like ‘%张三%’，对于mysql来说，因为索引失效，会进行全表检索；对es而言分词后，每个字都可以利用FST高速找到倒排索引的位置，并迅速获取文档 id 列表，大大的提升了性能，减少了磁盘IO。
2）精确检索：进行精确检索，有些时候可能mysql要快一些，当mysql的非聚合索引引用上了聚合索引，无需回表，则速度上可能更快；es还是通过FST找到倒排索引的位置比获取文档 id 列表，再根据文档 id 获取文档并根据相关度进行排序。但是es还有个优势，就是es即天然的分布式能够在大量数据搜索时可以通过分片降低检索规模，并且可以通过并行检索提升效率，用filter时，更是可以直接跳过检索直接走缓存。

数据的锁的种类，加锁的方式InnoDB锁的有哪几种？

MySQL 中有共享锁和排它锁，也就是读锁和写锁。

共享锁：不堵塞，多个用户可以同一时刻读取同一个资源，相互之间没有影响。
排它锁：一个写操作阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用户读取正在写入的资源。
表锁：系统开销最小，会锁定整张表，MyISAM 使用表锁。
行锁：容易出现死锁，发生冲突概率低，并发高，InnoDB 支持行锁（必须有索引才能实现，否则会自动锁全表，那么就不是行锁了）。

InnoDB怎么解决幻读的？

READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可
能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ（可重
读）事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生。

可重复读如何实现

可重复读的核心就是一致性读(consistent read);而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

乐观锁和MVCC的区别？

MVCC（Multi-Vers ion Con current Control），基于快照隔离机制(Snap shot Isolations)进行多版本并发控制，是一种以乐观锁为理论基础的，用来解决读-写冲突的无锁并发控制。也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照，也就是说，事务开启时看到是哪个版本就看到这个版本，这样在读操作不用阻塞写操作，写操作不用阻塞读操作，提升性能的同时，避免了脏读和不可重复读