MySQL面试题

说一下事务的ACID

1. 原子性（Atomicity）：原子性是指事务是一个不可分割的单位，事务中的操作要么不发生，要么全发生
2. 一致性（Consistency）：事务前后的数据完整性必须保持一致
3. 隔离性（Isolation）：事务的隔离性指的是多个用户并发访问数据库时，数据库为每一位用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间相互隔离
4. 持久性（Durablility）：持久性指的是一个事务一旦被提交，它对数据库中的数据改变是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

事务有哪几种隔离级别？

1. 读未提交（RU）：另一个事务修改了数据但是未提交，而本事务中的SELECT会读到这些未提交的数据脏读
2. 读已提交（RC）：事务能看到的数据都是其他事务已经提交的修改，也就是保证不会看到任何中间状态，脏读也不会出现
3. 可重复度（RR）：保证同一事务中多次读取的数据是一致的，这是MySQL InnoDB默认的隔离级别
4. 串行：并发事务之间是串行化的，通常意味着读取需要获取共享读锁，更新需要获取排他写锁，如果SQL使用WHERE语句，还会获取区间所，是最高的隔离级别
   不考虑事物的隔离级别会出现什么问题？
   并发事务可能造成脏读，不可重复度，幻读等问题，这些问题其实都是数据库一致性问题，必须由数据库提供一定的事务隔离机制来解决

什么是事物的并发，什么是脏读，事物的隔离级别每个级别解决引发什么问题，MySQL默认隔离级别是什么？

1. 事务的并发：事物的并发指两个或两个以上的事务同时发生
2. 脏读：脏读是指一个事务在处理数据时，读取到另一个事务未提交的数据
3. 不可重复读：事务A多次读取同一数据，事务B在事务A读取数据过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果因此本事务中先后两次读取的数据不一致
4. MySQL默认隔离级别是可重复度
5. MySQL支持4种事务隔离级别。数据库的隔离级别要得到底层数据库引擎的支持，而不是应用程序或框架的支持。事务的隔离级别过高事务的可读性和完整性越高，但对并发性能的影响也越大

MySQL的优化，MySQL的性能优化

1. 设计方面，选择适合的存储引擎，合适的字段类型，遵循范式（反范式设计）
   a. 存储引擎
   ⅰ. 不需要事务，不需要外键，读写较多的选择MyIsam
   ⅱ. 需要事务，需要外键的选择InnoDB
   b. 合适的字段类型
   ⅰ. 长字符用char，短字符用varchar
   ⅱ. 状态、性别等有限数量值用tinyint
   c. 遵循范式
   ⅰ. 第一范式（1NF）：原子性
   ⅱ. 第二范式（2NF）：消除部分依赖
   ⅲ. 第三范式（3NF）：消除传递依赖
2. 从功能方面对索引优化，采用缓存缓解数据库压力，分库分表
3. 从架构上可以采用主从复制，读写分离，负载均衡
   性能优化：
4. SQL语句的索引优化
5. 数据库表的结构优化
6. 系统配置的优化
7. 硬件的优化

MySQL超大分页怎么处理？

MySQL不是跳过offset行，而是取offset行+N，然后放弃前offset行，取N行，所以当offset比较法的时候效率比较低。正确做法是：先快速定位需求获取的id再关联查询获取数据

MySQL索引

索引是帮助MySQL高效获取数据的数据结构。能够加快数据的查询速度，索引通常是存储在磁盘的文件中

1. 普通索引（index）：对字段没有要求
2. 唯一索引（unique index）：要求索引字段值不能重复。同时增加唯一约束
3. 主键索引（primary key）：要求索引字段不能重复，也不能为NULL。同时增加主键约束
4. 符合索引（fulltext key）：索引字段的来源不是所有字段的数据，而是从字段中提取的特别关键字
   MySQL索引的优势和劣势
5. 优势
   a. 可以提高检索效率，降低数据库的IO成本，类似于书的目录
   b. 通过索引列对数据进行行排序，降低数据排序成本，降低了CPU的消耗
6. 劣势
   a. 索引会占据磁盘空间
   b. 索引虽然会增加检索效率，但会降低表的更新效率。比如每次对表进行增删改查操作，MySQL不仅要保存数据，还要保存和更新对应的索引文件

什么情况下索引会失效？

1. 范围查询。MySQL会一直从左到右匹配只到遇到查询范围（<，>，between，like）就停止匹配。范围列可以用到索引，但范围列后面的列无法用到索引
2. like语句。如果通配符%不出现在开头，则可以用到索引，like "value%"可以使用索引，但是like "%value%"不会使用索引，走的全表扫描
3. 列上使用函数或表达式。如何查询条件含有函数或表达式，将导致索引失效而进行全表扫描
4. 根据null值查询。只要列中含有null值都将不会包含在索引中，复合索引中只要有一列含有null值，那么这一列对此复合索引就是无效的。索引在设计数据库时不要让数据库字段默认为null

一条SQL语句执行很慢的原因？

1. 大部分时候正常，偶尔很慢
   a. 数据库刷新遇到脏页，例如redo log写满了同步到磁盘
   b. 执行的时候遇到锁，如表所、行所
2. 一直执行的很慢
   a. 没用上所有，例如该字段没有索引（由于对字段进行运算、函数操作导致无法运用索引）
   b. 数据库选错索引

MySQL有哪几类锁？

全局锁，表级锁，行级锁。其中InnoDB支持表级锁和行级锁，MyISM只支持表级锁，乐观锁和悲观锁

什么是全局锁，它的应用场景有那些？

全局锁就是对数据库整个实例加锁。
它的典型场景就是做全库逻辑备份。这个命令可以使整个库处于只读状态，使用该语句后，数据更新语句、数据定义语句、更新类事务的提交语句等操作都会被阻塞

什么是共享锁？

1. 共享锁又称读锁（read lock），是读取操作创建的锁
2. 其他用户可以并发读取事务，但任何事物都不能对对象就行修改（获取数据上的排他所），直到已释放共享锁。当事务对读锁进行修改操作，很可能造成死锁

什么是排他锁？

1. 排他锁（exclusive lock）又称写锁（writer lock）
2. 若某个事物对某一行加上了排他锁，只能对这个事务进行读写
3. 在此事务结束前，其他事物不能对其进行加任何锁，其他进程可以读取，不能进行写操作，需要等待其释放

MySQL的默认隔离级别是什么，如何调整隔离级别？

1. MySQL默认隔离级别是可重复度（RepeatableRead），Oracle的默认隔离级别是读已提交（ReadCommited）
2. 调整隔离级别的方式：
   a. 查看隔离级别。通过，show variables like '%tx_isolation%'查看当前事务隔离级别
   b. 设置会话隔离级别，set session transaction islation level 隔离级别
   c. 设置全局隔离级别，通过修改配置文件/etc/my.cnf，在文件的文末添加配置：transaction-isolation=隔离级别来设置
   d. 程序中一般通过Spring控制事务，可以在事务属性上，通过isolation属性设置隔离级别

MySQL事务实现原理？

1. 事务的实现是基于数据库的存储引擎，不同的存储引擎对事物的事物的支持程度不一样
2. MySQL中支持事务的存储引擎有InnoDB和NDB

说说分库和分表的区别？

1. 分表和分库的目的在于，减轻单表和单库的负担，提高查询效率，缩短查询时间。通过分表可以减轻数据库的单表负担，将压力分散到不同表上。同时，因为不同表的数据量少了，可以通过查询性能，缩短查询时间的作用，此外，可以很大缓解表锁问题。
2. 分表策略可以分为水平拆分和垂直拆分
   a. 水平分表
   ⅰ. 将不常用的字段单独拆分到另一张扩展表，将大文本字段单独拆分到另一张扩展表
   ⅱ. 将不常修改的字段放在同一张表，将经常修改的字段放到同一张表
   ⅲ. 对于海量用户场景，可以考虑取模分表，数据相对比较均衡，不容易出现热的和并发访问的瓶颈
   b. 垂直分表
   ⅰ. 仅仅是解决单表数据过大的问题，但并没有把单表的数据分散到不同的物理机上，因此不能减轻MySQL服务器的压力，仍然存在同一个物理机上的竞争和瓶颈，包括CPU，内存，磁盘IO和网络宽带等
3. 分表与分库带来的分布式困境与应对之策
   a. 数据迁移与扩容问题
   ⅰ. 一般做法是通过程序先读出数据，然后按照指定的分表策略再将数据写入到各个分表中
   b. 分页与排序问题
   ⅰ. 需要在不同的表中进行排序并返回，并将不同分表返回的结果集进行汇总再次排序，最后再给用户

MySQL索引的使用注意

MySQL索引通常是被用于提高WHERE条件的数据匹配时的搜索速度

1. 不要在列上使用函数和进行运算，这将导致索引失效进行全表扫描
2. 尽量避免使用not in或!=或<>等否定操作符
3. 尽量避免使用or来连接条件
4. 索引列不会包含null值的列

CHAR和VARCHAR的区别

1. CAHR的字符长度是不可变的，用空格填充到指定长度大小，而VARCHAR长度是可变的
2. CHAR字符存取速度比VARCHAR速度快得多
3. CHAR的存储方式；对英文字符（ASCII）占用1个字符，对中文字符占用2个字符。VARCHAR的存储方式：英文字符和中文字符都是占用2个字符

内连接，自连接，外连接（左，右，全），交叉连接直接的区别

1. 内链接：只有两个元素表相匹配的才能进行结果集显示
2. 左外连接：以左边的表为驱动表，驱动表数据全部显示，匹配表不匹配的不显示
3. 右外连接：以右边的表为驱动表，驱动表数据全部显示，匹配表不匹配的不显示
4. 全外连接：连接表中不匹配的数据全部显示出来
5. 交叉连接：笛卡尔效应，显示的结果是连接表数的乘积

需要创建索引的情况和不需要创建索引的情况

需要创建索引的情况

1. 主键自动建立索引主键
2. 频繁作为条件查询的字段应该创建索引
3. 多表查询中，关联字段应该创建索引（on两边都要创建索引）
4. 查询中排序的字段应该创建索引
5. 频繁查找的字段应该创建索引
6. 查询中统计或分组字段应该创建索引
   不需要创建索引的情况
7. 表记录太少
8. 经常进行增删改的表
9. 平繁更新的字段
10. where条件李使用频率不高的字段