Java 面试之数据库

一文整理数据库语句：xxx

目录

• • • 1、关系型数据库和非关系型数据库的区别？数据库和非关系型数据库的区别？
    • 2、char、varchar的区别？
    • 3、DROp、DELETE、TRUNCATE的区别？
    • 4、SQL中常用的聚合函数？
    • 5、数据库中表的关联形式？
    • 6、等值连接、自连接、自然连接、内连接、外连接、全连接？
    • • 6.1、 等值连接
      • 6.2、自连接
      • 6.3、自然连接（NATURAL JOIN）
      • 6.4、内连接（INNER JOIN）
      • 6.5、左外连接（LEFT JOIN）
      • 6.6、右外连接（RIGHT JOIN）
      • 6.7、全连接（全外连接）
    • 7、元组、属性？
    • 8、主键、外键、超键、候选键？
    • 9、完整性约束有哪些？
    • 10、SQL约束有哪些？
    • 11、数据库的三大范式？
    • 12、什么是子查询？
    • 13、什么是存储过程？它的优缺点是什么？
    • 14、存储过程中IN、OUT、INOUT参数的区别？
    • 15、存储过程和函数的区别？
    • 16、什么是触发器？MySQL中有哪些触发器？
    • 17、视图是什么？
    • 18、存储过程和视图的区别？
    • 19、游标是什么？
    • 20、where、on、having有什么区别？
    • 21、IN和EXISTS的区别？
    • 22、如何实现行转列、列转行？
    • 23、数据库事务是什么？有哪些特性？
    • 24、如果不考虑隔离性，即事务之间相互影响，会导致什么问题？
    • 25、事务隔离级别？
    • 26、索引是什么？
    • 27、索引的优缺点？
    • 28、索引设计原则？
    • 29、索引的类型？
    • 30、聚簇索引？非聚簇索引？
    • 31、覆盖索引？
    • 32、前缀索引？
    • 33、MySQL支持哪几种索引结构？
    • 34、MySQL不同存储引擎对于索引结构的支持？
    • 35、MySQL为什么不采用二叉树和红黑树的结构来构造索引？
    • 36、B-Tree树？
    • 37、B+Tree树？
    • 38、Hash索引？
    • 39、Full-text索引？
    • 40、B+树索引和哈希索引的区别？
    • 41、索引中最左前缀法则是什么？
    • 42、引起索引失效的几种情况？
    • 43、SQL优化之插入数据
    • 44、SQL优化之主键优化
    • 45、SQL优化之order by优化
    • 46、SQL优化之group by优化
    • 47、SQL优化之limit优化
    • 48、MySQL中有哪几种锁？
    • 49、全局锁？
    • 50、表级锁？
    • • 50.1、表锁
      • 50.2、元数据锁（meta data lock、MDL）
      • 50.3、意向锁
    • 51、行级锁？
    • • 51.1、行锁（Record Lock）（又叫记录锁）
      • 51.2、间隙锁（Gap Lock）
      • 51.3、临键锁（Next-Key Lock）
    • 52、锁兼容、锁互斥是什么？

1、关系型数据库和非关系型数据库的区别？数据库和非关系型数据库的区别？

关系型数据库是指用关系模型来组织数据的数据库，数据以行和列（即表）的形式存储；非关系型数据库中数据是以键值对的形式存储的；

NoSQL的特点：

• 模式自由：不需要定义表结构，数据表中每条记录都可能有不同的格式
• 逆规范化：不遵循范式要求，去掉完整性约束，表之间不存在依赖
• 扩展性强：可在系统运行的过程中，动态的删除和增加节点
• 多副本异步复制：数据快速写入一个节点，其余节点通过日志来实现异步复制
• 弱事务：不保证事务的ACID特性，但可以保证事务的最终一致性

SQL的特点：

• 数据集中控制
• 数据独立：数据库中的数据独立于应用程序
• 数据共享性好：数据库中的数据供多个用户使用
• 减少数据冗余：数据统一定义、组织和存储，减少冗余度
• 数据结构化：整个数据库按一定的结构形式构成，数据在记录内部和记录类型之间相互关联，用户可通过不同的路径读取数据

2、char、varchar的区别？

char存储定长字符串，varchar存储变长字符串，varchar容易产生内存碎片。

3、DROP、DELETE、TRUNCATE的区别？

drop用来删除数据库、表、索引等，是完全清除结构和数据；
delete只会删除数据记录；
truncate会清除表中的所有数据，保留结构。
delete属于DML语句，执行完后不会自动提交，可以回滚；truncate和drop属于DDL语句，执行完后会马上自动提交，不能回滚。
执行速度上：drop > truncate > delete。

4、SQL中常用的聚合函数？

• COUNT(*)：计算表中总的行数，不管某行或某列是否有空值
• COUNT(字段)：计算指定列下总的记录数，忽略空值
• AVG()：计算指定列的平均值
• SUM()：计算指定列的总和
• MAX()：计算指定列中的最大值
• MIN()：计算指定列中的最小值

补充：COUNT()函数详解：

函数	说明
COUNT(主键)	InnoDB引擎会遍历整张表，把每一行的主键id值取出来，返回给服务层，服务层拿到主键后，直接按行进行累加（主键不可能为NULL）
COUNT(字段)	没有NOT NULL约束：InnoDB引擎会遍历整张表，把每一行的字段值取出来，返回给服务层，服务层判断值是否为NULL，不为NULL则计数累加；有NOT NULL约束：InnoDB引擎会遍历整张表，把每一行的字段值取出来，返回给服务层，服务层直接按行进行累加；
COUNT(数字)	InnoDB引擎会遍历整张表，但不取值，服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加；
COUNT(*)	InnoDB引擎会遍历整张表，但不取值，服务层直接按行进行累加；
效率	COUNT(字段) < COUNT(主键id) < COUNT(1) < COUNT(*)

5、数据库中表的关联形式？

• 一对一：两个表的主键关联或外键关联；
• 一对多：一般是两张表具有主从关系，从表的外键关联主表的主键。例如主表为班级表，主键为班级号，从表为学生表，主键为学号，外键为班级号，就可以将班级表的班级号（主键）和学生表的班级号（外键）关联起来；
• 多对多：如果两张表具有多对多的关系，那么它们就需要一张中间表来作为衔接，这个中间表有自己的主键（或者不用主键），它的外键就是需要关联的两张表的主键，所以至少应该设计两个字段；
• 自关联：一张表与自己进行关联，关联时需要取别名。

6、等值连接、自连接、自然连接、内连接、外连接、全连接？

新建两张表：
球员表：

id	name	nationality
1	Messi	Argentina
2	Cristiano	Portugal
3	Neymar	Brazil
4	De Bruyne	Belgium
5	Benzema	French

评分表：

id	name	score
1	Messi	93
2	Cristiano	91
3	Neymar	91
6	Neuer	90

6.1、 等值连接

取出左表中的每一条记录，和右表进行匹配，若满足判断条件则保留，不满足则舍弃；可以认为和内连接一样，内连接是相对于外连接提出来的概念。
如：

SELECT * FROM tb_footballer,tb_score where tb_footballer.id=tb_score.id;
SELECt * FROM tb_footballer INNER JOIN tb_score on tb_footballer.id=tb_score.id;

两者返回的结果都是一样的：

补充：从直观角度看，连接就是将两个表的需要查询的字段连接起来，两个表中字段名不同的直接拼接，字段名相同的会自动重命名，注意连接是列的连接。

6.2、自连接

将一个表与其自身做连接，可以用于排列组合等。
如：

SELECt * FROM tb_score t1,tb_score t2;	# 不加任何限制条件

再加一个限制条件：去除重复的

SELECt * FROM tb_score t1,tb_score t2 WHERe t1.name<>t2.name;

如果我们认为两个name只是位置不同的话可以视为一条记录，那么我们还可以再设置限制条件：去重
如：

SELECt * FROM tb_score t1,tb_score t2 WHERe t1.name>t2.name;

这样便实现了找出一个表中不同组合（忽略次序）的功能。

6.3、自然连接（NATURAL JOIN）

会将两张表中具有相同列名的列自动进行匹配并在连接结果中删除两个重复列中的一个，不必指定任何同等连接条件。虽然可以指定查询结果包含哪些列，但是不能人为指定匹配列。
如：

SELECt * FROM tb_footballer NATURAL JOIN tb_score;

6.4、内连接（INNER JOIN）

查询结果中仅包含左右表中均有的记录。
如：

SELECt * FROM tb_footballer tf INNER JOIN tb_score ts on tf.name=ts.name;

6.5、左外连接（LEFT JOIN）

包含左表的全部记录以及右表的匹配记录。
如：

SELECt * FROM tb_footballer tf LEFT JOIN tb_score ts on tf.name=ts.name;

6.6、右外连接（RIGHT JOIN）

包含右表的全部记录以及左表的匹配记录。
如：

SELECt * FROM tb_footballer tf RIGHT JOIN tb_score ts on tf.name=ts.name;

6.7、全连接（全外连接）

包含左右表中所有记录，Oracle支持全连接（FULL JOIN），MySQL不支持全连接，不过可以用UNIOn将左连接和右连接结合起来。
如：

# MySQL实现全连接方式
SELECt * FROM tb_footballer tf LEFT JOIN tb_score ts on tf.name=ts.name
UNIOn
SELECt * FROM tb_footballer tf RIGHT JOIN tb_score ts on tf.name=ts.name;

7、元组、属性？

通俗来说，元组就是行，属性就是列。

8、主键、外键、超键、候选键？

• 主键：数据库表中对存储数据对象予以唯一和完整标识的数据列或属性的组合，一个表只能有一个主键，且主键非空唯一；
• 外键：外键用于关联另一张表，通常是另一张表的主键，一个表可以有多个外键；
• 超键：在关系中能唯一标识元组的属性集称为关系模式的超键，也就是说，保证行记录唯一的列都可以用来定义超键；
• 候选键：最小分割的超键就是候选键，即超键的唯一可以再划分为更小的唯一，比如身份证号和学号都可以作为候选键，但是身份证号和学号拼接起来就只能作为超键，因为拼接起来的字符串不满足最小分割原则。

9、完整性约束有哪些？

• 实体完整性：规定表中的每一行记录都是唯一的实体；
• 域完整性：规定表中的每一列都必须属于数据库系统规定的数据类型；
• 参照完整性：规定两张表的主键和外键的数据应该一致，保证了关联表数据间的一致性；
• 用户定义完整性：不同数据库有不同的要求，应满足不同数据库各自的要求。

10、SQL约束有哪些？

• PRIMARY KRY：主键约束，一张表只能有一个主键；
• FOREIGN KEY：外键约束，用于指定具体关联主表的哪一列（主键列），防止破坏表之间的连接；
• NOT NULL：非空约束；
• UNIQUE：唯一约束；
• CHECK：check约束，自定义，用于控制该字段的取值范围。

11、数据库的三大范式？

• 第一范式：原子性约束，要求每列都是最小的数据单元，不可再分割；
• 第二范式：唯一性约束，在第一范式的基础上，要求每一列都和主键完全相关，而不是部分相关，例如联合主键，你必须和联合主键中的每一个主键都相关，而不是只和某一个主键相关；
• 第三范式：冗余性约束，在第二范式的基础上，要求每一列都和主键直接相关，而不是间接相关，即不能有传递依赖关系，例如学生和院系直接相关，而不是和班级依赖，再和院系依赖。

12、什么是子查询？

一条SQL语句的查询结果作为另一条SQL语句的参考输入，即多条SQL语句嵌套使用时，内部的SQL查询语句就叫子查询。
如：

SELECt * FROM tb_footballer WHERe tb_footballer.id IN (SELECt id FROM tb_score);

13、什么是存储过程？它的优缺点是什么？

存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过存储过程名来调用存储过程查出结果。简单来说，就是一组批量执行的SQL语句，类似于Docker中的Docker-compose。

存储过程的优点：

• 效率高：预编译，仅第一次调用时进行编译，后续不再进行编译
• 减少网络传输：不用频繁传输大批SQL语句
• 复用性好：存储过程往往是针对某一特定功能编写的
• 可维护性高：某一特定功能发生变化时，仅需修改对应的存储过程
• 安全性高：一般具有用户访问级别限制

存储过程的缺点：

• 存储过程里保存的基本上是业务逻辑需要的SQL，而把业务逻辑放在数据库里这种行为是不推荐的
• 调试复杂
• 移植复杂：不同数据库对SQL语句有不同要求
• 不支持集群：数据库进行分库或者集群部署之后，并不清楚存储过程会保留到哪个数据库中

14、存储过程中IN、OUT、INOUT参数的区别？

默认参数类型为IN；
IN、INOUT参数调用存储过程时没有赋初值，默认值为NULL；OUT参数不管有没有赋初值，参数值均为NULL；

• IN：输入参数，该参数不管在存储过程内部如何修改其值，返回的参数值仍然是当初赋的初值，存储过程内的修改不生效；
• OUT：输出参数，可用来接收返回值，该参数可以在存储过程内部被修改，并且修改结果也会生效；
• INOUT：输入输出参数，该参数结合了IN参数和OUT参数，在存储过程内部可修改，并且修改结果也会生效。
  

15、存储过程和函数的区别？

不同点：

• 存储过程标识符是procedure，函数标识符是function；
• 存储过程用于在数据库中执行特定操作（如查询、插入、删除）等，函数用于计算或返回特定的数据；
• 定义存储过程时不需要返回类型，只能通过OUT或INOUT来返回值，函数必须要有返回类型，且必须要有return子句，也可以通过OUT、INOUT来返回值；
• 存储过程可以作为一个独立的SQL语句执行，函数不能独立执行，必须包含在表达式中；
• 存储过程使用call调用，函数使用select调用，可以在SQL语句中调用函数，但是不能调用存储过程。

相同点：

• 创建语法相似，都携带一个或多个入参或返回一个或多个出参；
• 都是一次编译，多次执行。

使用场景：如果有多个返回值或者不返回值，就用存储过程；如果只有一个值返回，就用函数。

16、什么是触发器？MySQL中有哪些触发器？

触发器指在insert/delete/update之前（BEFORE）或之后（AFTER）触发并执行其中定义的SQL语句集合的一个数据库对象。
触发器类型有:
①BEFORE INSERT
②AFTER INSERT
③BEFORE DELETE
④AFTER DELETE
⑤BEFORE UPDATE
⑥AFTER UPDATE

使用别名OLD和NEW来引用触发器中发生变化的记录内容：

触发器类型	NEW和OLD
INSERT型触发器	NEW表示将要或者已经新增的数据
UPDATE型触发器	OLD表示修改之前的数据，NEW表示将要或修改之后的数据
DELETE型触发器	OLD表示将要或已经删除的数据

17、视图是什么？

视图是一张虚拟的表，也就是它在磁盘中并不存在。它代表一部分的表（即单表或关联的多表中的部分记录），通用场景下我们会根据不同的条件创建不同的视图，即各种SQL查询语句的集合，下次再查询时就只用查询视图名，而不用再去批量执行SQL语句了。视图可以进行增删改查操作，但是限制很多，很多情况下并不支持这些操作，不过话说回来，视图主要用于方便查询，也不推荐使用视图进行增删改。

18、存储过程和视图的区别？

• 从本质上讲，存储过程和视图都是一段已经编译好的SQL脚本，而存储过程可以提供输入参数，视图不接受参数；
• 存储过程的目的是操作数据，视图的目的是方便呈现数据（即存储过程关注的是操作数据这件事，而视图关注的是返回的结果集）。

19、游标是什么？

可以把游标看成是编程语言中的指针，用于在返回结果集中遍历数据。

20、where、on、having有什么区别？

where和having的区别在于：

• where在group by和聚合函数之前对数据进行过滤；
• having在group by和聚合函数之后对数据进行过滤。

where和on的区别在于：

• 内连接中，on和where等价；
• 外连接中，on在连接操作之前进行，where在连接操作之后进行。

三者执行顺序是：on > 连接 > where > 聚合函数 > having。

21、IN和EXISTS的区别？

• IN是子查询的表为驱动表，先执行内层查询，再执行外层查询，故适用于子查询结果集小而外层表结果集大的情况；
  EXISTS是外层表作为驱动表，先执行外层查询，再执行内层查询，故适用于外层表结果集小而子查询结果集大的情况；
• IN是非关联子查询，而对于非关联子查询，必须先完成内层查询后，外层查询才能介入；
  EXISTS是关联子查询，而对于关联子查询，必须先执行外层查询，接着对所有通过过滤条件的记录，执行内层查询，因为外层查询会把过滤后的结果传递给内层查询；
• IN只执行一次，先确定给定的值是否与子查询或列表中的值相匹配，然后再将内表和外表做一个笛卡尔积，再按照条件进行过滤；
  EXISTS先遍历循环外表，然后再看内表中的记录有没有和外表一样的，匹配上则将结果放入结果集中。

22、如何实现行转列、列转行？

。。。

23、数据库事务是什么？有哪些特性？

数据库事务是逻辑上的一组数据库操作，要么都执行，要么都不执行，不可分割。

数据库事务包含ACID四个特性：

• 原子性（Atomicity）：事务是一个不可分割的工作单位，事务中的动作要么都完成，要么都不完成；
• 一致性（Consistency）：事务必须使数据库总体状态保持不变，例如A和B加起来共有10000元钱，那么不管他们如何转账，总和必须总是10000元；
• 隔离性（Isolation）：多个用户操作同一资源时，一个用户的事务不能被另一个用户的事务所干扰，多个并发事务之间数据相互隔离，各个事务都有各自完整的数据空间；
• 持久性（Durability）：事务一旦被提交了，那么它对数据库的改变就是永久性的，即使数据库出现故障也不会有任何影响。

24、如果不考虑隔离性，即事务之间相互影响，会导致什么问题？

• 脏读：一个事务读取到了另一个事务还没有提交的数据。比如天气太热，M的老婆W准备给M多加100的零花钱去买水喝，就给M发了个红包，几分钟后越想越气，什么家务都不干还想加零花钱，就把这红包给撤回了，M看到了红包很高兴，点进去一看发现被撤回了，空欢喜一场；
• 不可重复读：一个事务操作中对同一数据读取了多次，但是这多次的结果不一样（强调的是记录本身内容的不一致）。比如M正在吃饭，买单前看了看银行卡里有500块钱，欸，很开心，可以吃顿好的，吃完了看看银行卡还是500块钱，准备去买单，M的老婆正好这时候提现了499元去买口红，结果老板告诉M卡里只有1块钱，M吃了霸王餐，只好打电话向W求救。不可重复读出现的原因就是多个事务并发修改记录；
• 幻读：在同一个事务中，同一个查询多次返回的结果不一致（强调的是记录数量的不一致）。比如M在手机上查看消费明细有5笔开销，正当他要打印消费明细时，好巧不巧，M的老婆W正好买了一个包包花了5万元，结果M打印出来的明细多了一笔5万元的开销，明细变成了6笔，就像出现了幻觉一样。幻读出现的原因是多个事务并发增加或删除记录。

补充：以上面的例子来说，不可重复读关注的余额，两次查询发现余额变了，即内容变了；而幻读关注的是明细数量，两次查询发现明细数量多了一条。

25、事务隔离级别？

• 读未提交（Read Uncommitted）：事务可以读取到其他事务尚未提交的数据，存在脏读、不可重复读和幻读的问题；
• 读已提交（Read Committed）：只有数据被事务提交之后，其他事务才能读取到改变后的数据，解决了脏读的问题，存在不可重复读和幻读的问题；
• 可重复读（Repeatable Read）：事务对同一数据多次读取的结果都是一致的，解决了脏读和不可重复读的问题，存在幻读的问题；
• 串行化（Serializable）：强制事务依次执行，不可插队，解决了脏读、不可重复读和幻读的问题。

隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
读未提交（Read Uncommitted）	是	是	是
读已提交（Read Committed）	否	是	是	Oracle
可重复读（Repeatable Read）	否	否	是	MySQL
串行化（Serializable）	否	否	否

26、索引是什么？

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息，简言之，索引就类似于书本中的目录，提前给你排好了序。举个例子，如果没有索引，那么查询时就会进行全表查询，然后丢弃不满足条件的行，而有索引时，假如索引采用的是B+Tree的结构，那么就会大大减少查询次数，提高查询效率。

27、索引的优缺点？

优点：

• 提前排好序，加快数据的检索速度
• 将随机IO变为顺序IO
• 通过创建不同种类的索引，可以对数据起到约束作用

缺点：

• 创建和维护索引耗费时间，而且随着数据量的增加而增大
• 索引需要占用物理空间
• 查询效率得到了提升，但是增删改的效率降低了

28、索引设计原则？

应该使用索引的场景：

• 经常需要查询的列上；
• 常作为查询条件（where）、排序（order by）、分组（group by）操作的列；
• 字符串类型列可以使用前缀索引；
• 尽量使用联合索引，而不是单列索引，联合索引很多时候用到了覆盖索引，不用回表查询，性能高；
• 要控制索引的数量；

不应该使用索引的场景;

• 查询中很少使用的列；
• 数据记录比较少的列；
• 定义为text、image的列，因为这些列的数据量相当大，要么使用前缀索引，要么压根就不使用索引；
• 当对修改数据的性能远远大于搜索数据的性能时。因为使用索引能提高搜索性能，但是会降低修改性能。

29、索引的类型？

• 主键索引：作用的列非空且唯一，一个表只有一个主键
• 唯一索引
• 普通索引：可以为空，可以重复
• 全文索引：也叫倒排索引，把词条出现的频率和位置做一个归纳，这样就建立了一个以词条为目录项的目录，这样在查找某个词的时候就能很快的定位到该词以及该词所在的句子，类似于ElasticSearch的倒排索引
• 联合索引：多列组合成一个索引

30、聚簇索引？非聚簇索引？

聚簇索引和非聚簇索引是一大类，几乎所有的索引都分为这两类。

聚簇索引：

• 又叫簇类索引、簇集索引、聚集索引、聚类索引。是索引项的排列顺序和表中数据记录的排列顺序一样的索引，比如新华字典按照拼音排序，索引项就是拼音，表中数据记录就是汉字，两者排序顺序是一样的，由拼音目录后面的页数也能看出来，页数是连续的；
• 正如新华字典中实际汉字所处的页数只有按拼音排序的这一种方式，一个数据库表中也只有一个聚簇索引，表中行的物理顺序和索引中行的物理顺序是一致的（也就是汉字的顺序和拼音的顺序是一致的）；
• 聚簇索引的叶子节点存放的是每一行的数据，也就是数据节点。

非聚簇索引：

• 不是聚簇索引的二级索引，都称为非聚簇索引，也叫辅助索引。是指索引项的排序和表中数据记录的排序不一样的索引，比如新华字典还有按偏旁部首排序的方式，那么索引项就是偏旁部首，表中数据记录还是汉字，但是新华字典中汉字实际所处页数的排序只有一种就是按拼音排序，所以虽然偏旁部首也能起到索引的作用，但是它的排序和汉字的实际排序不一致，由目录后面对应的页数是不连续的也能看出来。
• 非聚簇索引的叶子节点存放的是主键的值或者是指向数据行记录的真实物理地址，所以使用聚簇索引时可以直接得到数据，而使用非聚簇索引时还需要再根据主键或者行记录物理地址来获取实际数据（回表查询）。

补充：InnoDB的主键索引是聚簇索引，非主键索引都是非聚簇索引；MyISAM的主键索引和非主键索引都是非聚簇索引。

31、覆盖索引？

覆盖索引是指，查询时使用了索引，并且查询的所有字段都能在索引中找到，覆盖索引可以提高性能。比如：

• SELECT * FROM tb_footballer WHERe id=’xx’;查询时使用了id列的主键索引，属于聚簇索引，直接返回整行数据，那么所有字段自然能够找到，属于覆盖索引，性能高；
• SELECt id,name FROM tb_footballer WHERe name=’xx’;查询时使用了name列索引，属于辅助索引（非聚簇索引），叶子节点存储的是主键id值，只经过一次索引扫描便能得到id和name信息，属于覆盖索引，性能高；
• SELECt id,name,score FROM tb_footballer WHERe name=’xx’;和上述第二种情况一样，但是一次索引扫描只能获取id和name，而对应的score还需要回表查询经过一次对应的主键索引才能查到，不属于覆盖索引，性能差。

32、前缀索引？

当字段类型为字符串时，不需要索引一整串字符串，而是只对字符串的前一部分建立索引，这样的索引就叫前缀索引。前缀长度根据索引选择性来决定，索引选择性是指不重复的索引值（基数）和数据表的记录总数的比值，比值越大，索引选择性越好，性能越好。

33、MySQL支持哪几种索引结构？

• B+Tree索引：最常见的索引类型，大部分引擎都支持
• Hash索引：底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
• R-Tree索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
• Full-text索引（全文索引）：是一种通过建立倒排索引，快速匹配文档的方式，类似于ElasticSearch

34、MySQL不同存储引擎对于索引结构的支持？

索引	InnoDB	MyISAM	Memory
B+Tree索引	Y	Y	Y
Hash索引	N	N	Y
R-Tree索引	N	Y	N
Full-text索引	Y（5.6版本后）	Y	N

35、MySQL为什么不采用二叉树和红黑树的结构来构造索引？

如果选用二叉树作为索引结构，存在如下缺点：

• 顺序插入时，会形成一条链表，查询效率大大降低；
• 数据量比较大时，树的层级比较深，检索速度慢。
  

选用红黑树作为索引结构，存在如下缺点：

• 数据量比较大时，树的层级比较深，检索速度慢，因为红黑树也是一棵二叉树。
  

36、B-Tree树？

B-Tree树就是B树，是一种多路自平衡查找树，相较于二叉树可以有多个分支。

M阶B树具有如下特点：

• 所有叶子节点都在同一层，每一个节点最多存储M-1个key，并且按升序排列；
• 每个节点最多拥有M棵子树；
• 根节点至少有2棵子树，分支节点至少拥有M/2棵子树；
• 所有键值分布在整棵树中，非叶子节点和叶子节点都会存放数据。
  

B树存在如下问题：每个节点中既有key又有data，而每一页的存储空间是有限的，如果data数据量太大那么能存储的key就会很少，与此同时还会导致树的层级变深，检索速度变慢。

37、B+Tree树？

B+Tree是B树的变体，如：

B+Tree树与B树的区别：

• 每个节点可以包含更多的键；
• 非叶子节点仅仅起到索引数据的作用，非叶子节点中不会存放数据；
• 所有的数据都只会保存在叶子节点，从而防止key和data数据量相差太大导致树层级太深的问题；
• 叶子节点形成一个单向链表，更易于检索。

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

38、Hash索引？

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中；

如果两个或多个键值映射到了同一个槽位上，就发生了hash碰撞，多余的键值就会以链表的形式继续向后关联；

Hash索引的特点：

• 只能用于对等比较（=，in），不支持范围查询；
• 无法利用索引完成排序操作；
• 查询效率高，在不存在hash碰撞的情况下只需要一次检索就够了，效率通常要高于B+Tree树。

39、Full-text索引？

也叫倒排索引，把词条出现的频率和位置做一个归纳，这样就建立了一个以词条为目录项的目录，这样在查找某个词的时候就能很快的定位到该词以及该词所在的句子，类似于ElasticSearch的倒排索引。

40、B+树索引和哈希索引的区别？

• B+树默认有序，哈希表默认无序，所以B+树利于排序，哈希表利于查询；
• 哈希索引只能进行等值查询，不能进行范围查询，而以上B+树都支持；
• 底层实现上B+树是树形结构，哈希表是线性结构。

41、索引中最左前缀法则是什么？

如果索引了多列，即联合索引，那么就要遵守最左前缀法则，即查询从索引的最左列开始（根据创建索引时的顺序），并且不得跳过索引中的列，如果跳过某一列，该列后面的字段索引将会失效。
举例说明：对一张表中A、B、C三个字段建立了联合索引，那么查询时：
①只要指定条件中A存在，那么索引就不会失效；
②只存在A、B时，C部分索引失效；
③只存在A、C时，B、C都将失效。
需要注意的是对A、B、C的顺序并没有要求，比如SELECt * FROM tb_footballer WHERe A=’xx’ AND B=’xx’ AND C=’xx’;和SELECt * FROM tb_footballer WHERe B=’xx’ AND C=’xx’ AND A=’xx’;的效果是一样的。

42、引起索引失效的几种情况？

• 不满足最左前缀法则；
• 出现范围查询（判断条件中存在<或>）时，范围查询右侧的列索引失效，如SELECt * FROM tb_footballer WHERe Axx;那么B列索引将失效。需要注意的是<=和>=时，索引不会失效。
• 索引列上进行了运算操作，比如用了函数运算；
• 字符串类型字段使用时不加引号；
• 头部模糊查询，索引失效；尾部模糊查询，索引不会失效；
• 使用or时，or左右两侧的列都有索引时，索引才会生效；
• MySQL在查询时，会评估使用索引和全表扫描哪个效率更高，从而选择效率更高的那一个。

43、SQL优化之插入数据

• 建议批量插入数据
• 建议手动控制事务
• 根据主键进行顺序插入，主键顺序插入的性能高于乱序插入
• 对于大批量插入数据，使用load代替insert

44、SQL优化之主键优化

这个PPT写的太好，直接引用：

主键优化原则：

• 尽量降低主键长度
• 插入数据时，尽量选择顺序插入，使用AUTO_INCREMENT自增主键
• 尽量不要使用UUID或其他随机号作为主键
• 业务操作时，避免对主键的修改

45、SQL优化之order by优化

MySQL排序有两种方式：

• Using filesort：通过索引扫描或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序
• Using index：通过有序索引顺序扫描直接返回有序数据，可见这种方式效率更高

order by优化原则：

• 根据排序字段建立合适的索引，多字段排序时，遵循最左前缀法则
• 尽量使用覆盖索引
• 多字段排序，一个升序一个降序，需要在创建索引时一并指定对应的字段是升序还是降序（ASC、DESC）
• 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲器大小sort_buffer_size（默认256k）

46、SQL优化之group by优化

对指定分组列建立索引，同样遵循最左前缀法则。

47、SQL优化之limit优化

limit分页原则是先按序扫描所有记录，然后再丢弃不满足条件的记录，所以在数据量太大时，性能较差。可以通过覆盖索引+子查询的方式来进行优化。
如：

SELECt * FROM tb_footballer ORDER BY id LIMIT 10000,20;

可以优化为：

SELECt * FROM tb_footballer t1,(SELECt id FROM tb_footballer ORDER BY id LIMIT 10000,20) t2 WHERe t1.id=t2.id;

48、MySQL中有哪几种锁？

全局锁、表级锁、行锁。

补充：从事务的角度来分，锁分为：

• 共享锁（S锁、读锁）：一个事务对表或行加了S锁后，不会阻塞其他事务的读，但是会阻塞其他事务的写。什么叫阻塞呢，就是我这条语句也不会报错，会一直等待在那里不返回结果，直到事务一释放了读锁。即自己能读不能写，其他人能读能写（但是写会阻塞）；
• 排他锁（X锁、独占锁、写锁）：一个事务对表或行加了写锁后，会阻塞其他事务的读和写。即自己能读能写，其他人能读能写（读写均阻塞）。

理解上，事务对应用户。

49、全局锁？

全局锁就是对整个数据库进行加锁，加锁后整个数据库实例就处于只读状态，可用于数据库备份。
存在如下问题：
①在主库上备份，在备份期间都不能执行更新，业务就得停摆；
②在从库上备份，在备份期间就不能执行主库同步过来的二进制文件，导致主从延迟。

50、表级锁？

表级锁是对整张表进行加锁。
表级锁分为三类：

50.1、表锁

表锁又分为两类：

• 表S锁（read lock）；
• 表X锁（write lock）。

50.2、元数据锁（meta data lock、MDL）

• 元数据锁是系统自动控制，无需显式使用，元数据可以理解为表结构，在表上有未提交的事务时，不能修改这张表的表结构，元数据锁的目的就是维护表结构的数据一致性；
• 当对表的数据层面进行增删改查的时候，加元数据S锁；当对表结构进行修改的时候，加元数据X锁；
• 这样理解，当一个事务对表执行某种操作时，数据库会根据操作的类型对表加上元数据S锁或者元数据X锁，如果加上的是元数据S锁，那么其他事务执行的操作若对应的元数据锁也是S锁则可以正常进行，不会阻塞，而其他加元数据X锁的操作就会被阻塞，直到原来的锁被释放为止。举个例子，SELECt * FROM tb_footballer;这条语句会给tb_footballer这个表加上元数据S锁，而INSERT、UPDATE、DELETE这些操作加的也是S锁，那么这些操作就能进行，然而ALTER TABLE对表加的是X锁，那么这个操作就会被阻塞，因为S锁和X锁之间是互斥的。
• 常见的SQL操作对应的元数据锁：

SQL操作	锁类型
lock tables xxx read/write	SHARED_READ_ONLY/SHARED_NO_READ_WRITE
select、select…lock in share mode	SHARED_READ
insert、update、delete、select…for update	SHARED_WRITE
alter table…	EXCLUSIVE
补充	共享锁（SHARED_READ、SHARED_WRITE）与排他锁（EXCLUSIVE）互斥

50.3、意向锁

• 若没有意向锁，那么一个用户在执行DML操作对表加了行锁后，另一个用户想对这张表加表锁时，就要逐行判断是否有行锁，以此来决定能否加表锁；而有了意向锁之后，在执行DML操作时不仅会对涉及的行加行锁，还会对该表加上意向锁，其他用户想对表加表锁时就只需要判断意向锁的类型而不用逐行判断行锁了。总而言之，意向锁就是为了解决行锁和表锁的冲突。
• 意向锁的类型：
  • 意向共享锁（IS）：由语句select…lock in share mode添加，与表S锁兼容，与表X锁互斥；
  • 意向排他锁（IX）：由insert、update、delete、select…for update添加，与表S锁及表X锁都互斥；
• 由意向锁的类型可知，一个用户加了IS之后，其他用户就只能对表加表S锁，而如果加的是IX，那么其他用户就不能对表加锁；
• 意向锁是一种不与行级锁冲突的表级锁；
• 意向锁之间是相互兼容的；
• 一旦事务提交了，意向共享锁、意向排他锁都会自动释放。

51、行级锁？

InnoDB的数据是基于索引组织的，行级锁是通过对索引项加锁而不是对行记录加锁来实现的。默认情况下，InnoDB使用next-key锁（即临键锁）进行搜索和索引扫描。
行级锁分为三类：

51.1、行锁（Record Lock）（又叫记录锁）

• 锁定单个行记录的锁，防止其他事务对该行进行update或delete。需要注意的是它锁定的是索引记录而不是整行的数据记录；
  
• 行锁也分为行S锁和行X锁，兼容性与表级锁一致；
• 常见的SQL语句，在执行时，所加的行锁如下：

SQL	行锁类型	说明
INSERT	X锁	自动加锁
UPDATE	X锁	自动加锁
DELETE	X锁	自动加锁
SELECT	不加锁
SELECT…LOCK IN SHARE MODE	S锁	自动加锁
SELECT…FOR UPDATE	X锁	自动加锁

• 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，临键锁会自动优化为行锁；
• 如果表中没有索引或者不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，即行锁升级为表锁。

51.2、间隙锁（Gap Lock）

• 间隙锁就是锁定索引记录的间隙（不包含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读；
  
• 对唯一索引进行检索时，对不存在的记录进行等值匹配，会使用间隙锁锁住大于该记录的第一个值之前的间隙（因为InnoDB采用B+树索引，叶子节点是有序的双向链表，所以索引项也是有序的）；
• 针对非唯一索引进行检索时，由于可能存在相同值，所以需要向右遍历至不等于该记录的第一个值，然后使用间隙锁锁住之前的间隙；
• 针对唯一索引进行范围检索时，会对左值加行锁，右值加临键锁；
• 间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一个间隙上采用间隙锁。

51.3、临键锁（Next-Key Lock）

临键锁就是行锁和间隙锁组合，同时锁住数据和数据前面的间隙。

52、锁兼容、锁互斥是什么？

当多个事务同时对同一表或行执行操作时，会根据操作的类型对该表或行加上对应的锁类型，后续操作若加上的锁或待获取的锁和已有锁类型兼容，则可以立即执行，若和已有锁互斥，则会阻塞。

锁之间的兼容性：

兼容性	S	X	IS	IX
S	Y	N	Y	N
X	N	N	N	N
IS	Y	N	Y	Y
IX	N	N	Y	Y