mysql面试重点

SQL基础知识

https://www.cnblogs.com/yocichen/p/11266917.html

关系型和⾮关系型数据库的区别你了解多少

关系型数据库的优点
容易理解。因为它采⽤了关系模型表来组织数据。

• 可以保持数据的⼀致性。

• 数据更新的开销⽐᫾⼩。

• ⽀持复杂查询（带where⼦句的查询）

⾮关系型数据库的优点

• 不需要经过SQL层的解析，读写效率⾼。

• 基于键值对，数据的扩展性很好。

• 可以⽀持多种类型数据的存储，如图⽚，⽂档等等

说⼀说Drop、Delete与Truncate的共同点和区别

• Delete⽤来删除表的全部或者⼀部分数据⾏，可以回滚，可以触发

• Drop直接删掉表，触发器不会触发，不能回滚

• Truncate删除表中的所有数据，保留表，不能回滚，不会触发器，TRUNCATE⽐delete更快，占⽤的空间更⼩

MySQL优化

为搜索字段创建索引

选择正确的存储引擎

避免使⽤ Select*，列出需要查询的字段

大批量插入，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令插入

主键优化

• 满足业务需求的情况下，尽量降低主键的长度

• 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键

order by优化

group by优化

在分组操作时，可以通过索引来提高效率
分组操作时，索引的使用也是满足最左前缀法则的

limit优化
一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

update优化
避免行锁升级为表锁

count优化
count(主键)：InnoDB引擎会遍历整张表，把每行的主键id值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加（主键不可能为空）

count(字段)：没有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加；有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加

count(1)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一层，放一个数字 1 进去，直接按行进行累加

count(*)：InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加

按效率排序：count(字段) < count(主键) < count(1) < count()，所以尽量使用 count()

介绍一下数据库分页

在MySQL中，SELECT语句默认返回所有匹配的行，它们可能是指定表中的每个行。为了返回第一行或前几行，可使用LIMIT子句，以实现分页查询。LIMIT子句的语法如下：

在所有的查询结果中，返回前5行记录。 
SELECT prod_name FROM products LIMIT 5; 
在所有的查询结果中，从第5行开始，返回5行记录。 S
ELECT prod_name FROM products LIMIT 5,5;

优化LIMIT分页
在偏移量非常大的时候，例如 LIMIT 10000,20 这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面的10000条记录都将被抛弃

页面中限制分页的数量(实际开发不可能），要么是优化大偏移量的性能

通过覆盖索引加快速度，直接通过主键索引进行排序及查询
select id from tb_sku order by id limit 9000000, 10;

介绍一下SQL中的聚合函数

• count 统计数量

• max 最大值

• min 最小值

• avg 平均值

• sum 求和

和GROUP BY关键字一起使用

表跟表是怎么关联的

• 内连接：只有两个元素表相匹配的才能在结果集中显示。

• 外连接： 左外连接: 左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。

• 右外连接:右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。

• 全外连接：连接的表中不匹配的数据全部会显示出来。

• 交叉连接： 笛卡尔效应，两个表查询的所有组合情况

  • 解决方法：employee.dept = dept.id; 条件查询1表外键关联2表的主键

• 一对多
  一部门与员工一个部门对应多个员工，一个员工对应一个部门，在多的一方建立外键，指向一的一方的主键

• 多对多
  一个学生可以选多门课程，一门课程也可以供多个学生选修，建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

• 一对一
  一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率，在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

谈谈你对SQL注入的理解

SQL注入的原理是将SQL代码伪装到输入参数中，传递到服务器解析并执行的一种攻击手法。也就是说，在一些对SERVER端发起的请求参数中植入一些SQL代码

参数校验就没得说了，在一些不该有特殊字符的参数中提前进行特殊字符校验即可。

将一张表的部分数据更新到另一张表，该如何操作呢

update table1 a1,table2 a2  
set a1.要填充字段=a2.对应索取字段
where a1.id=a2.id
必须保证有对应字段写在where之后  可以正常添加筛选条件

WHERE和HAVING有什么区别

执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组；having是分组后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以。WHERE可以在执行分组操作和计算聚合函数之前过滤掉不需要的数据，性能会更好。

存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表而不是基于库的，所以存储引擎也可以被称为表引擎。
默认存储引擎是InnoDB。

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

• InnoDB: 如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，则 InnoDB 是比较合适的选择
• MyISAM: 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那这个存储引擎是非常合适的。
• Memory: 将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。Memory 的缺陷是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性

电商中的足迹和评论适合使用 MyISAM 引擎，缓存适合使用 Memory 引擎。

Innodb为什么要⽤⾃增id作为主键？

如果表使⽤⾃增主键，那么每次插⼊新的记录，记录就会顺序添加到当前索引节点的后续位置，当⼀⻚写满，就会⾃动开辟⼀个新的⻚。

如果使⽤⾮⾃增主键（如果身份证号或学号等），由于每次插⼊主键的值近似于随机，因此每次新纪录都要被插到现有索引⻚得中间某个位置， 频繁的移动、分⻚操作造成了⼤ᰁ的碎⽚，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE（optimize table）来᯿建表并优化填充⻚⾯。

说⼀下MySQL是如何执⾏⼀条SQL的？具体步骤有哪些？、

Server层按顺序执⾏sql的步骤为：

• 客户端请求->

• 连接器（验证⽤户身份，给予权限） ->

• 查询缓存（存在缓存则直接返回，不存在则执⾏后续操作）->

• 分析器（对SQL进⾏词法分析和语法分析操作） ->

• 优化器（主要对执⾏的sql优化选择最优的执⾏⽅案⽅法） ->

• 执⾏器（执⾏时会先看⽤户是否有执⾏权限，有才去使⽤这个引擎提供的接⼝）->

• 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果

• 连接器：管理连接、权限验证；

• 查询缓存：命中缓存则直接返回结果；

• 分析器：对SQL进⾏词法分析、语法分析；（判断查询的SQL字段是否存在也是在这步）

• 优化器：执⾏计划⽣成、选择索引；

• 执⾏器：操作引擎、返回结果；

• 存储引擎：存储数据、提供读写接⼝。

索引常见面试题

索引/什么时候需要 / 不需要创建索引？

索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录，索引是在存储引擎中实现的

优缺点

提高数据检索效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

索引列也是要占用空间的
索引大大提高了查询效率，但降低了更新的速度，比如 INSERT、UPDATE、DELETE

适合

• 字段有唯一性限制的，比如商品编码；

• 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。

• 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

不适合

• WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
• 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
• 表数据太少的时候，不需要创建索引；
• 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

索引的分类

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
• 如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

规则

最左前缀法则

如果索引关联了多列（联合索引），要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。
如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

覆盖索引&回表查询

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能找到），减少 select *

索引失效情况SQL 提示在SQL语句中加入一些人为的提示来达到优化操作的目的。，强制使用或者建议使用索引

前缀索引

当字段类型为字符串（varchar, text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率，此时可以只降字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

单列索引&联合索引

单列索引：即一个索引只包含单个列
联合索引：即一个索引包含了多个列
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引

B-Tree和B +

通常情况

B 树是一颗二叉树，顺序插入会形成一个链表，查询性能随着二叉树层级会大大降低
如果使用红黑树数据量过大同样会因为层级过深而出现检索过慢的情况

B +

B-Tree (多路平衡查找树) 以一棵最大度数为5（5阶）的 b-tree 为例节点最多存储4个key，5个指针

• 所有的数据都会出现在叶子节点
• 叶子节点形成一个单向链表

在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能。

Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

• Hash索引只能用于对等比较（=、in），不支持范围查询（betwwn、>、<、…）
• 无法利用索引完成排序操作
• 查询效率高，通常只需要一次检索就可以了，效率通常要高于 B+Tree 索引

对于 B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针也跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低

相对于 Hash 索引，B+Tree 支持范围匹配及排序操作

有什么优化索引的方法？

• 针对于数据量较大，且查询比较频繁的表建立索引

• 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引

• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高

• 如果是字符串类型的字段，字段长度较长，可以针对于字段的特点，建立前缀索引

• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率

• 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率

• 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询

事务

事务有哪些特性？

事务是一组操作的集合，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

四大特性ACID

• 原子性(Atomicity)：事务是不可分割的最小操作但愿，要么全部成功，要么全部失败

• 一致性(Consistency)：事务完成时，必须使所有数据都保持一致状态

• 隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

• 持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

持久性是通过 redo log （重做日志）来保证的；
原子性是通过 undo log（回滚日志） 来保证的；
隔离性是通过 MVCC（多版本并发控制） 或锁机制来保证的；
一致性则是通过持久性+原子性+隔离性来保证；

并发事务

严重性依次

脏读:一个事务读到另一个事务还没提交的数据

不可重复读:一个事务先后读取同一条记录，但两次读取的数据不同

幻读:一个事务按照条件查询数据时，没有对应的数据行，但是再插入数据时，又发现这行数据已经存在

事务的隔离级别有哪些？

隔离级别越高，性能效率就越低

读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；

读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；

可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；

串行化（serializable ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时

针对不同的隔离级别，并发事务时可能发生的现象也会不同。

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象 MySQL 并不会使用「串行化」隔离级别来避免幻读现象的发生，因为使用「串行化」隔离级别会影响性能。

锁

MySQL 有哪些锁？

在 MySQL 里，根据加锁的范围，可以分为全局锁、表级锁和行锁三类。

全局锁

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

加全局锁又会带来什么缺点呢？

加上全局锁，意味着整个数据库都是只读状态不能写，业务停摆

既然备份数据库数据的时候，使用全局锁会影响业务，那有什么其他方式可以避免？

可重复读的隔离级别的事务隔离级别，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。备份数据库之前先开启事务，

表级锁

表锁

除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。因为表锁的颗粒度太大，会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁

元数据锁（MDL）

我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

对一张表进行 CRUD 操作时，加的是 MDL 读锁；
对一张表做结构变更操作的时候，加的是 MDL 写锁；

MDL 不需要显示调用，那它是在什么时候释放的?

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

为什么线程 C 因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也会被阻塞？

这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更

意向锁

接着，说说意向锁。

在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；

也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突。

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

意向锁的目的是为了快速判断表里是否有记录被加锁

AUTO-INC 锁

表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。

之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

行级锁

待续~~~