记MySQL面试篇

骑着蚂蚁去上天
186 阅读7分钟

一、数据库的三范式是什么？

  1. 第一范式：列不可再分

  2. 第二范式：行可以唯一区分（主键约束）

  3. 表的非主属性不能依赖于其它表的非主数据（外键约束）

三范式是一级一级依赖的，第二范式建立在第一范式之上，第三范式建立在第二范式之上

二、数据库引擎有哪些？

查看数据库引擎方法：show engines;

MySql数据库引擎包括MYISAM、Innodb、Memory、MERGE

  • MYISAM：权标锁，拥有较高的执行速度，不支持事务、不支持外键、并发性能差、占用空间相对较小，对事务文字性没有要求；

  • Innodb：行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对较差；

  • Memory：全表锁，存储在内存中，速度快，但会占用和数据量成正比的内存空间且在mysql重启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表；

  • MERGE：是一组MYSIAM表的组合。

三、InnoDB与MyISAM的区别是什么？

  1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，最好把多条SQL语言放在一个事务进行执行；

  2. InnoDB支持外键，MyISAM不支持；

  3. InnoDB是聚集索引，数据文件是和索引捆绑一起的，必须有主键，通过主键进行所有效率很高，但辅助索引需要两次查询，先查主键，再通过主键查询数据，因此，主键不应过大；而MyISAM是非聚集索引，数据文件是分离的，索引保存数据文件的指针，主键索引和辅助索引是独立的；

  4. InnoDB不保存具体行数，执行 select count(*) 时需要进行全表扫描，速度相对较慢，而MyISAM用一个变量保存了整个表的行数，所以执行上面语句时，只需要读取该变量即可，速度很快；

  5. InnoDB不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM会更高。

四、数据库的事务

  1. 什么是事务？

    • 事务简单来说就是，多条sql语句要么全部成功，要么全部失败

  2. 事务的特性是什么？

    • 原子性

    • 一致性

    • 隔离性

    • 持久性

原子性：组成一个事务的多个数据库操作是一个密不可分的原子单元，只有所有操作都成功事务才会提交，任何一个步骤失败了已经执行的任何操作都会被撤销。

一致性：事务操作成功后，数据库的数据是一致的，如：A转账100给B，无论成功与否，AB的账户总额都是不变的

隔离性：高并发情况下，不同事务拥有的数据空间，他们的操作不会彼此产生干扰

持久性：一旦事务成功提交，事务中的所有操作都必须持久化到数据库中

五、什么是索引？

索引是对数据库表中一个或多个列的值进行排序的结构，建立索引能有助于快速找到对应信息

六、Mysql有多少种索引？

Mysql有4钟不同的索引

  • 主键索引（PRIMARY）：

    数据列允许重复，不允许为NULL，一个表只能有一个主键

  • 唯一索引（UNIQUE）

    数据列不允许重复，允许为NULL值，一个表允许有多个唯一索引

    ALTER TABLE TABLE_NAME ADD UNIQUE (COLUMN) // 创建唯一索引ALTER TABLE TABLE_NAME ADD UNIQUE (COLUMN1， COLUMN) // 创建唯一组合索引

  • 普通索引（INDEX）

    ALTER TABLE TABLE_NAME ADD INDEX INDEX_NAME (COLUMN) // 创建普通索引ALTER TABLE TABLE_NAME ADD INDEX INDEX_NAME (COLUMN1，COLUMN2) // 创建组合普通索引

  • 全文索引（FULLTEXT）

    ALTER TABLE TABLE_NAME ADD FULLTEXT (COLUMN) // 创建全文索引

注意：索引不是越多越好，创建索引需要消耗资源

1、增加数据库存储空间

2、在插入和删除时要花费较多的时间维护索引

  • 索引加快了数据库的查询速度

  • 索引降低了插入、删除、修改等维护任务的速度

  • 唯一索引可以确保每一行数据的唯一性

  • 通过使用索引，可以在查询的过程中优化隐藏器，提高系统的性能

  • 索引需要占物理和数据空间

七、SQL优化

  1. 查询语句中不要使用 select *

  2. 尽量减少子查询，改用关联查询（left join、inner join 、right join）

  3. 减少使用 IN 或者 NOT IN，改用exists、not exists

  4. or 的查询尽量用 union或者 union all替代

  5. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则索引会失效，继而全表扫描

  6. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致索引失效继而全表扫描

八、drop、delete、truncate的区别

delete 和 truncate 只删除数据不删除表结构，而 drop 则是删除数据和删除表结构

delete 语句是dml，这个操作会放到rollback segement中，事务提交才会生效；

drop truncate 语句是ddl，操作立即生效，并且操作不会放到rollback segement中，不支持回滚。

九、什么是视图？

视图是一种虚拟的表，具有和物理表相同的功能，可以对视图进行增、改、查操作，视图通常是由一个表或多个表的行或列的子集，对视图的修改不会影响基本表。

十、什么是内联接、左外联接、右外联接

  • 内联接：匹配2张表中相关联的记录

  • 左外联接：除了匹配2张表相关联的记录外，还会匹配左表中剩余的记录，右表中未匹配到的字段用NULL表示

  • 右外联接：除了匹配2张表相关联的记录外，还会匹配右表中剩余的记录，左表中未匹配到的字段用NULL表示

十一、并发事务带来哪些问题

  1. 脏读：当一个事务在访问数据并且对数据进行了修改，而这种修改还没提交到数据库中，这是另外一个事务也访问该数据，但是获取到的数据是未修改之前的，这样的数据就叫“脏数据“；

  2. 丢失修改：一个事务读取数据时，另外一个事务访问了该数据进行修改，第一个事务也进行了修改，这样的情况会导致第一个事务修改的数据丢失，因此叫做丢失修改

  3. 不可重复读：一个事务内多次读取同一数据，在这个事务还没结束的情况下另外一个事务也访问了该数据，那么，在第一个事务中的两次读取数据之间，由于第二个事务的修改导致第一个事务读取的数据有可能不一样；

  4. 幻读：在一个事务中读取了多行数据，但是另外一个事务插入了些数据时，第一个事务又查询了一次，获取到了一些原本不存在的记录。

十二、事务隔离级别有哪些？MySQL的默认隔离级别是？

SQL标准定义了4个隔离级别

  • READ-UNCOMMITTED（读未提交）：最低的隔离级别，允许尚未提交的数据变更，可能会导致脏读，幻读或不可重复读

  • READ-COMMOTTED（读已提交）：允许读取并发事务已经提交的数据，可以阻止脏读，但幻读或不可重复读人仍可能发生

  • REPEATABLE-READ（可重复读）：对同一字段的多次读取结果都是一致的，除非数据是被本身事务所修改，可以防止脏读和不可重复读，但幻读仍有可能发生

  • SERIALIZABLE（可串行化）：最高的隔离级别，完全服重ACID的隔离级别，所有的事务依次逐个执行，事务之间完全不会产生干扰，该级别可以防止脏读、不可重复读以及幻读

MySQL InnoDB存储引擎默认支持的隔离级别是REPEATABLE-READ（可重复读）

可以通过 SELECT @@tx_isolation命令查看

需要注意的是，InnoDB存储引擎在REPERATABLE事务隔离级别下使用的是Next-Key-Lock锁算法，因此可以防止幻读的发生，

十三、数据量大的表怎么优化？

  1. 限定数据的范围

    数据做历史查询的时候限定一个查询区间，一般控制在一个月的范围内

  2. 读/写分离

    主库负责写，从库负责读

  3. 垂直分区

    根据数据库里面的数据表关系进行拆分

  4. 水平分区

    保持表结构不变，通过某种策略存储数据分配，每一片数据分散到不同的表或者库中，达到了分布式的目的

常见的数据库分片方案

  • 客户端代理：分片逻辑在应用端，封装在jar中，通过修改或者封装JDBC层来实现，例如：ShardingJDBC（当当网）、TDDL（阿里）

  • 中间件代理：在应用和数据中间加了一个代理层，分片逻辑统一维护在中间件服务中，例如：Mycat、Atlas（360）、DDB（网易）

十四、MySQL有关权限的表是哪几个？

  • user权限表：记录允许连接到服务器的用户信息，为全局权限

  • db权限表：记录各个账号数据库上的操作权限

  • table_priv权限表：记录数据表级的操作权限

  • column_priv权限表：记录数据列级权限

  • host权限表：配合db权限表给指定主机上的数据库级操作权限作更细致的控制，该表不受CRANT和REVOKE语句影响

十五、MySQL有哪些数据类型

  • 数据类型包括：TINYINT、SMALLINT、MUDIUMINT、INT、BIGINT，分别表示1字节、2字节、3字节、4字节、8字节。

    任何整数类型都可以加上UNSIGNED属性，表示数据都是无符号的，即非负整数。

  • 实数类型包括：FLOAT、DOUBLE、DECIMAL

    DECIMAL能存储比BIGINT更大的数字，可以精确到小数位

    DECIMAL做计算的效率比FLOAT、DOUBLE更高

  • 字符串类型包括：VARCHAR、CHAR、TEXT、BLOB

    VARCHAR用于存储可变长字符串，它比定长类型更节省空间

    VARCHAR使用额外1或2个字节存储字符串长度。烈长度小于255字节时，使用1字节表示，否则使用2字节表示

    VARCHAR存储内容超过限定长度时，内容会被截断

    CHAR是定长类型，根据定义的长度分配空间

    CHAR适合存储很短长度的字符串，或者所有值都接近同一个长度

    CHAR存储内容超出设置的长度时，内容也会被截断

  • 枚举类型：ENUM，把不重复的数据存储为一个预定义的集合

    有时可以使用ENUM代替常用的字符串类型

    EMUN存储非常紧凑，会把列表值压缩到一个或两个字节

    ENUM在内部存储时，其实存的是整数

    尽量避免使用数据作为ENUM枚举的常量，避免混淆

    排序是按照内部存储的整数

  • 日期和时间类型包括：DATETIME、TIMESTAMP

    尽量使用TIMESTAMP，空间效率高于DATETIME

    用整数保存时间戳通常不方便处理

    如果需要存储至微秒，可以使用BIGINT进行存储

十六、创建索引有几种方式？

  1. 在执行 CREATE TABL E时创建索引

    CREATE TABLE TABLE_NAME (    id INT AUTO_INCREMENT PRIMARY KEY,    first_name VARCHAR(16),    last_name VARCHAR(16),    id_card VARCHAR(18),    information text,    KEY name (first_name, last_name),    FULLTEXT KEY (information),    UNIQUE KEY (id_card))

  2. 使用 ALTER TABLE 命令创建索引

    ALTER TABLE TABLE_NAME ADD INDEX (COLUMN1, COLUMN2);

    注意 ALTER TABLE 用来创建普通索引、唯一索引、主键索引

  3. 使用 CREATE INDEX 创建索引

    CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN);

    注意：CREATE INDEX 只能用来创建普通索引、唯一索引

avatar
文章
阅读
粉丝