MYSQL 数据库

MySql数据库特点

1、开源数据库，不需要支付额外费用，项目上云首选；

2、关系型数据库，支持多条件场景查询；

3、支持多种存储引擎；

MySql数据库语句执行步骤

1）创建连接，验证用户是否可进行数据库操作

2）查询缓存，若sql语句存在缓存则直接返回缓存结果

3）当不存在缓存结果时，分析sql语句语法，

4）优化sql语句，寻找最优的查询方案

4）查询数据返回结果

MySql数据库select语句执行顺序

第一步：首先对from关联的前两个表做笛卡尔集合，若存在三个以上的表，则是数据量最少的两张表做笛卡尔集合

第二步：应用on筛选器，对于第一步执行出的结果筛选出满足on条件的数据

第三步：如果是outer join则添加外部行，如果是left jion则将左表中on筛选出的数据添加进来，right jion则是将右表筛选出的数据添加进结果集

第四步：如果是多张表，则再将第三张表球笛卡尔积，重复上面的步骤

第五步：应用where筛选器筛选结果

第六步：应用having筛选器删选结果

第七步：使用select查询符合条件的结果

第八步：使用distinct去重

第九步：使用orderby排序

第十步：根据limit取出指定行数据

where语句执行顺序

mysql:从左往右查询，将可以筛选调大部分数据的条件放到前面

oracle:从右往左查询，将可以筛选掉大部分数据的条件放到后面

MySql的几种搜索引擎

首先明确MySql的默认搜索引擎是innodb，支持事务，使用B+树索引，支持行锁；

若要修改搜索引擎可修改配置文件中的defalut-storage-engine

查询数据库当前搜索引擎可通过 show varables like 'default-storage-engine';

查看数据库支持的搜索引擎命令 show engines；或show vliable like ‘having%’;

几种搜索引擎

• myisam

        不支持事务；不支持外键；锁机制为表锁；

        保存表的行数，因此对于查询表总数较快；

        drop表时先删掉表再重建；

        在磁盘上有三个文件；.from(存储表定义) myd(表数据) myi(表索引)；

        支持三种不同形式的存储：动态表、静态表和压缩表

                1）动态表：表字段长度不固定，优点是占用空间少，但频繁修改会产生碎片

                2）静态表：表字段长度固定，虽占用空间多但是存储迅速容易缓存

                3）压缩表：由myisamchk工具创建，每行数据单独压缩，占用空间少

• innodb

        支持事务；支持外键；锁机制使用行锁；

        不记录表总行数；

        drop表时使用一行行删除数据方式；

        innodb的外键索引，在删除父表数据时，子表需不存在关联数据才可以；

        使用命令set foreign_key_checks=0;临时关闭外键约束，=1可打开外键约束

Mysql 几种搜索引擎的比较_IChen.的博客-优快云博客_mysql搜索引擎有哪些?

myisam和innodb的B树索引的区别

innodb的B+树叶子节点存储为索引文件本身

myisam的B+树叶子节点存储数据的物理地址，myisam的索引文件和数据文件是分离的

drop、delete和truncate的区别

drop删除表，delete删除执行行，truncate清空表；

当drop和delete和truncate不带where语句执行时，delete和truncate清空表仍保留表结构，但drop之后对应的表也不复存在

执行速度：drop>truncate>delete

redolog和binlog

事务的永久性是依靠redolog保证的，需明确，redolog是innodb特有的；固定空间循环写入

binlog是mysql数据库的server层实现的，所有搜索引擎都可以用；追加写入不覆盖原始记录

binlog三种格式：

1）stament：基于sql语句的复制，根据commit确定数据完整

2）row：基于行的复制，根据XID EVENT确定数据完整

3）mixed：混合模式复制

redolog为什么具有crash-safe能力

crash-safr数据库崩溃后的恢复能力；

redolog是循环写入固定空间的文件，指当文件中的记录已经被写入磁盘之后会从redolog中删除，binlog是追加写入不覆盖原始记录，假设数据库崩溃，binlog无法判断哪些记录还未写入磁盘，而根据redolog的数据特性，将redolog的记录写入数据库即可；

所以只使用binlog是不具备crash-safe能力

mysql的二阶段提交，当数据库crash后，如何保证未写入磁盘的数据写入到内存中

当开启事务后，对于需要落库的数据，程序会先将更新记录写入redolog日志中，此时标记状态为prapre，然后将记录写入到binlog日志中，然后修改redolog的状态为commit，称为二阶段提交；

所以当数据库崩溃时，对于redolog中拥有完成事务的记录，即包含commit的事务可直接写入磁盘，对于prepare状态的数据，若binlog中存在且完整，则可以写入磁盘，若binlog中不存在或不完整，则此部分数据可能事务未提交不必写如磁盘，binlog中有commt标识是否完整；

为什么需要二阶段提交

假设不使用二阶段提交，先写redolog后写binlog，当redolog写完后还未写入binlog时数据库崩溃此时，数据已落库，但是binlog中未存在此数据修改记录，当使用binlog恢复数据时数据不完整；

若先写入binlog后写入redolog，binlog写完数据库崩溃，redolog未写入数据程序认为事务未提交而导致数据错误；

https://bbs.youkuaiyun.com/topics/603572653

美团二面：如何解决 bin log 与 redo log 的一致性问题

 误删数据怎么办

首先还是做好权限控制，规范数据库操作，做好sql审核防止数据误删

当数据误删后，可以工具解析binlog，然后通过反转，delete改成insert，update的条件互转等，全量数据备份等操作恢复数据

mysql自增主键不连续

可能是事务数据回滚，唯一主键冲突导致主键不连续

orderby排序的内部原理

mysql会为每个线程分配一块内存用于排序，当排序的数据量小于内存时使用内部排序；

内部排序分为两种：

        1）全字段排序：到索引树上查找满足条件的主键ID,根据主键ID获取数据进行快速排序

        2）rowid排序：通过控制排序的行数据长度保证内存中更多的存放数据

外部排序：当数据量很大时，内存空间不够会借助外部磁盘文件临时辅助排序，mysql会将数据分为几个临时文件，然后进行归并合并为一个大文件

数据库精选 60 道面试题