面试mysql视图 mysql面试问题

转载

技术极客侠 2023-09-27 17:23:52

文章标签 面试mysql视图 mysql 面试数据 MySQL 文章分类 MySQL 数据库

精选MySQL面试题

MySQL的存储引擎

MySQL提供了很多种存储引擎最常用的是InnoDB和MyISAM。

如何选择合适的存储引擎？

提供几个选择标准，根据选择标准选择对应的存储引擎即可，例如：

是否需要支持事物
对索引和缓存的支持
是否需要做热备
存储的限制
是否需要外键支持

目前MySQL的默认存储引擎是InnoDB，也是当前最主流的选择，主要原因如下：

支持事物
支持行级锁和表级锁，能支持更多的并发量
查询不加锁，完全不影响查询
支持系统崩溃后恢复

在MySQL5.1之前的版本，默认的存储引擎为MyISAM，但是目前已经不更新了，且它有几个比较关键的缺点：

不支持事物
不支持行级锁，如果数据量大，一个插入操作锁定表后，其他请求都将阻塞

InnoDB和MyISAM的区别

	InnoDB	MyISAM
事物	支持	不支持
锁粒度	行锁	表锁
崩溃后恢复	支持	不支持
外键	支持	不支持
全文检索	5.7以后支持	支持

什么是索引？

索引，类似书籍的目录，想找到一本书的某个特定的主题，首相要找到书的目录，定位对应的页码。MySQL中存储引擎使用类似的方式进行查询，先去索引中查找对应的值，然后根据匹配的索引找到对应的数据行。

索引有什么好处？

提高数据的检索速度，降低数据库的IO成本，使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
降低数据排序的成本，降低CPU消耗，索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则正好降低了排序的成本。

索引有什么坏处？

占用存储空间，索引实际上也是一张表，记录了主键和索引字段，一般以索引文件的形式存储到硬盘上。
降低更新表的速度，表的数据发生了变化，对应的索引也需要一起改变，从而降低了更新的速度，否则索引指向的物理数据可能不对，这也是索引失效的原因之一。

索引使用的场景

对非常小的表，大部分情况下全表扫描效率更高。
对中大型表，索引都非常有效
特大型表，建立和使用索引的代价随着增长，可以使用分区技术来解决。

索引的类型

索引，都是实现在存储引擎层的，主要有六种类型：

普通索引：最基本的索引，没有任何约束
唯一索引：与普通索引相似，但具有唯一性约束。
主键索引：特殊的唯一索引，不允许有空值
复合索引：将多个列组合在一起创建索引，可以覆盖多个列
外键索引：只有InnoDB类型的表才可以有外键索引，保证数据的一致性、完整性和实现级联操作。
全文索引：MySQL自带的全文索引只能用于InnoDB、MyISAM，并且只能对英文进行全文检索，一般使用全文索引引擎。

MySQL 索引的“创建”原则？

最适合索引的列是出现在WHERE子句中的列，或者连接子句的列，而不是出现在SELECT关键字后的列。
索引列的基数越大，索引效果越好。
根据情况创建复合索引，复合索引可以提高查询效率。
避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。
主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率。
对字符串进行索引，应该定制一个前缀长度，可以节省大量的索引空间。

什么是索引的最左匹配特性？

当B+Tree的数据项是复合的数据结构，比如索引(name,age,sex)的时候，B+Tree是按照从左到右的顺讯来建立搜索树的。

比如(‘张三’,20,F)这样的数据来检索时，B+Tree会优先比较name来确定下一步所搜方向，如果name相同在依次比较age和sex，最后得到检索的数据。但当(20,F)这样没有name的数据来检索时，B+Tree就不知道下一步该查什么节点，因为建立搜索树的时候name就是第一个比较因子，必须要根据name来搜索才能知道下一步去哪里查询。比如当(‘张三’,F)这样的数据来检索时，B+Tree可以用name来搜索方向，但下一个age字段缺失，所以只能把所有name等于张三的数据找到，然后再匹配性别是F的数据了。

MySQL 索引的“使用”注意事项？

尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描，优化器将无法通过索引来确定将要命中的行数，因此需要搜索该表的所有行，column IS NULL 也是不可以使用索引的。
应尽量避免在WHERE子句中使用OR来连接条件，否则将导致引擎放弃索引而进行全表扫描。
应尽量避免在WHERE子句中对字段进行表达式操作，否则将导致引擎放弃索引而进行全表扫描。
应尽量避免在WHERE子句中对字段使用函数操作，否则将导致引擎放弃索引而进行全表扫描。
不要在WHERE子句中的= 左边进行函数、算术运算符和其他表达式运算，否则系统可能无法正确使用索引。
复合索引遵循最左匹配原则。
如果MySQL评估使用索引比全表扫描更慢，会放弃使用索引，因此如果此时想要使用索引，可以在语句中添加强制索引。
列类型是字符串类型，查询时一定要给值家引导，否则索引失效。（比如字符串类型的字段查询条件传入数值类型）
LIKE查询，% 不能在前，因为无法触发索引。如果需要使用模糊匹配，可以使用全文索引。

想知道一个查询用到了哪个索引，如何查看?

EXPLAIN 显示了 MYSQL 如何使用索引来处理 SELECT 语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在 SELECT 语句前加上 EXPLAIN 就可以了。

MySQL索引的原理

MySQL支持很多的存储引擎，而各种存储引擎对索引的支持也各不同，因此MySQL数据库支持多种索引，如BTree索引、B+Tree索引、哈希索引、全文索引等。

1. 哈希索引

只有memory（内存）存储引擎支持哈希索引，哈希索引索引列的值计算该值的hashCode，然后再hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能有一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序功能。

2. 全文索引

全文索引，仅支持MySIAM和InnoDB，针对较大的数据，生成全文索引非常消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还行，但是想要匹配中间几个字符，那么就要LIKE %%来匹配，这样需要很长的时间来处理，相应时间会大大增加，这种情况，就可以使用全文索引了。在生成全文索引的时候，会为文本生产一份单词的清单，在检索时根据这个单词的清单来查询。全文索引的语法：SELECT * FROM table_name MATCH(ft_index) AGAINST('查询字符串')。

注意点：

对于较大的数据集，把数据添加到一个没有全文索引的表，然后添加全文索引的速度比把数据添加到已有全文索引的表要快。
5.7版本之前只有MySIAM支持全文索引，在5.7版本后InnoDB也支持全文索引了。
在MySQL中，全文索引只对英文有效，目前中文还不支持。5.7版本后可以通过ngram插件开始支持中文。
在MySQL中，如果检索的字符串太短则无法检索到预期的结果，检索的字符串长度至少为4个字符，此外，如果检索的字符包括停止词，那么停止词会被忽略。

3. BTree索引和B+Tree索引

《崩撤卖溜》

请说说 MySQL 的四种事务隔离级别？

事务就是对一系列的数据库操作（比如插入多条数据）进行统一的提交或回滚操作，如果插入成功，那么一起成功，如果中间有一条出现异常，那么回滚之前的所有操作。

这样可以防止出现脏数据，防止数据库数据出现问题。

事务的特性指的是？

原子性 Atomicity ：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。即，事务不可分割、不可约简。
一致性 Consistency ：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器)、级联回滚等。
隔离性 Isolation ：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性 Durability ：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务的并发问题

实际场景下，事务并不是串行的，所以会带来如下三个问题：

脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。
读已提交：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。
幻读：系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

MySQL 事务隔离级别会产生的并发问题

READ UNCOMMITTED（未提交读）：事务中的修改，即使没有提交，对其他事务也都是可见的。(会导致脏读)
READ COMMITTED（不可重复读）：事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。
REPEATABLE READ（可重复读）：一个事务按相同的查询条件读取以前检索过的数据，其他事务插入了满足其查询条件的新数据。产生幻行。（会导致幻读）
SERIALIZABLE（可串行化）：强制事务串行执行。

MySQL 默认的事务隔离级别为可重复读（repeatable-read）。

事物隔离级别	描述
读未提交 (READ-UNCOMMITTED )	存在脏读、不可重复读、幻读的问题
读已提交 (READ-COMMITTED )	解决脏读的问题，存在不可重复读、幻读的问题
可重复读 (REPEATABLE-READ )	mysql 默认级别，解决脏读、不可重复读的问题，存在幻读的问题。使用 MMVC机制实现可重复读
可串行化(SERIALIZABLE)	解决脏读、不可重复读、幻读，可保证事务安全，但完全串行执行，性能最低

可重复读解决幻读：SELECT xxx FROM t WHERE id = ? FOR UPDATE，获得悲观锁，禁止其他事物操作对应的数据，从而解决幻读问题。

请说说 MySQL 的锁机制？

表锁是日常开发中常见的问题，因此也是面试当中最常见的考察点，当多个查询同一时刻修改数据时，就会产生并发控制的问题。MySQL的共享锁和排他锁，也就是读锁和写锁。

共享锁：不阻塞，多个用户都可以同事读一个资源，互不相干。
排他锁：一个写锁会阻塞其他的读锁和写锁，这样可以值允许一个用户进行写入，防止其他用户读取正在写入的资源。

锁的粒度

表锁：系统开销小，但是会锁住整张表，MyISAM使用表锁
行锁：最大程序的支持并发处理，但是也带来了最大的锁开销，InnoDB使用行锁。

什么是悲观锁？什么是乐观锁？

悲观锁
在操作数据时，会提前把这一行数据加上锁，使其他线程阻塞，直到操作完后才释放锁，实现方式：SELECT xxx FROM t WHERE id = ? FOR UPDATE
乐观锁
在操作数据时，不会给上锁，其他线程可以查看这行数据，操作完的时候判断一下在此之间其他线程释放修改了这行数据，如果别人修改了这条数据则放弃操作。实现方式：一般会在表内添加version字段，用来判断这行数据的版本，跟查询出来的版本做比较，只有在版本相同的情况下才会执行操作。还可以通过CAS机制实现乐观锁。

CAS机制（Compare And Swap）

CAS操作包括了3个操作数：

需要读写的内存位置（V）
进行比较的预期值（A）
拟写入的新值（B）

CAS的操作逻辑如下：如果内存位置V的值等于预期的A值，则将该位置更新为B值，否则不进行任何操作。需要CAS都是自旋的：如果操作不成功，会一直重试，知道操作成功为止。
CAS是由CPU支持的原子操作，其原子性是在硬件层面保证的。

下面以Java中的自增操作（i++）为例，看下悲观锁和CAS分别是如何保证线程安全的，我们知道在Java中自增操作不是原子操作，它实际上包含三个独立的操作：读取i值，加1，将新值写回i。因此，如果并发执行自增操作，可能导致计算不准确，

public class CasDemo {

    //不使用锁
    private static int value1 = 0;

    //使用CAS乐观锁
    private static AtomicInteger value2 = new AtomicInteger(0);

    //使用悲观锁
    private static int value3 = 0;

    private static synchronized void increaseValue3(){
        value3++;
    }

    public static void main(String[] args) throws InterruptedException {
        //开启1000个线程，并执行自增操作
        for (int i = 0; i < 1000; i++) {
            new Thread(() -> {
                value1++;
                value2.getAndIncrement();
                increaseValue3();
            }).start();
        }
        //等待线程执行完毕
        Thread.sleep(100);
        System.out.println("value1 " + value1);
        System.out.println("value2 " + value2);
        System.out.println("value3 " + value3);
    }
}

可以发现value1有时会小于1000，而value2和value3总是等于1000。

AtomicInteger是java.util.l.concurrent.atomic包提供的原子类，利用CPU提供的CAS操作来保证原子性；除了AtomicInteger外，还有AtomicBoolean、AtomicLong、AtomicReference等众多原子类。

下面看一下AtomicInteger的源码：

private static final long serialVersionUID = 6214790243416807050L;

    // setup to use Unsafe.compareAndSwapInt for updates
    private static final Unsafe unsafe = Unsafe.getUnsafe();
    private static final long valueOffset;

    static {
        try {
            valueOffset = unsafe.objectFieldOffset
                (AtomicInteger.class.getDeclaredField("value"));
        } catch (Exception ex) { throw new Error(ex); }
    }

    private volatile int value;

    public final int getAndIncrement() {
        return unsafe.getAndAddInt(this, valueOffset, 1);
    }

    public final int getAndAddInt(Object var1, long var2, int var4) {
        int var5;
        do {
            var5 = this.getIntVolatile(var1, var2);
        } while(!this.compareAndSwapInt(var1, var2, var5, var5 + var4));

        return var5;
    }

源码分析：

getAndIncrement()实现自增的操作是自选CAS操作，在循环中进行compareAndSwapInt()如果执行成功则退出，否则一直执行。
compareAndSwapInt()是CAS操作的核心，他是依靠Unsafe实现的。
Unsafe是帮助Java访问操作系统底层资源类。
valueOffset可以理解为value在内存中的偏移量，对应了CAS三个操作数(V/A/B)中的V；偏移量的获得也是通过Unsafe实现的。
value的volatile修饰符：Java并发编程要保证线程安全，需要保证原子性、可视性和有序性；CAS操作可以保证原子性，而volatile可以保证可视性和一定程度的有序性；在AtomicInteger中，volatile和CAS一起保证了线程安全性。

CAS有哪些缺点？

1. ABA问题(重要)

    假设有两个线程，两个线程按照顺序执行以下操作：
    - 线程1读取内存的数据为A
    - 线程2将数据修改为B
    - 线程2将数据修改为A
    - 线程1对数据进场CAS操作
    在第四步中，由于内存中的数据任然是A，因此CAS操作成功，但实际上数据A已经被线程2修改过了，这就是ABA问题。数据存储到堆中ABA问题没有什么实际的影响，但是数据如果存储到栈中，线程2更改后数据的地址已经变了，所以会导致出现问题。对于ABA问题，比较有效的方法是引入版本号，内存中的数据每发生一次改变，版本号都加1，在CAS操作中，不仅比较内存中的值，也会比较版本号，只有当二者都没有发生改变时，CAS执行才能成功。Java中的AtomicStampedReference类便是使用版本号来解决ABA问题的。

2. 高竞争下的开销问题

    在并发冲突概率大的高竞争环境下，如果CAS一直失败，会一直重试，CPU开销较大，针对这个问题引入的思路是退出机制，如果重试次数达到一定的阈值后就退出CAS操作。

3. 功能限制

    CAS只能保证单个变量的原子性，因为CAS的原子性是依赖volatile关键字来做的，当涉及到多个变量时，CAS就不支持了。