MySql知识体系总结（2021版，springcloud高级面试题

InnoDB&＃xff1a; 没有保存表的总行数&＃xff0c;如果使用select count(*) from table&＃xff1b;就会遍历整个表&＃xff0c;消耗相当大&＃xff0c;但是在加了wehre条件后&＃xff0c;myisam和innodb处理的方式都一样。

10、CRUD操作

MyISAM&＃xff1a;如果执行大量的SELECT&＃xff0c;MyISAM是更好的选择。

InnoDB&＃xff1a;如果你的数据执行大量的INSERT或UPDATE&＃xff0c;出于性能方面的考虑&＃xff0c;应该使用InnoDB表。

11、 外键

MyISAM&＃xff1a;不支持

InnoDB&＃xff1a;支持

三、sql优化简介

1、什么情况下进行sql优化

性能低、执行时间太长、等待时间太长、连接查询、索引失效。

2、sql语句执行过程

&＃xff08;1&＃xff09;编写过程


select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

&＃xff08;2&＃xff09;解析过程


from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

3、sql优化就是优化索引

索引相当于书的目录。

索引的数据结构是B&＃43;树。

四、索引

1、索引的优势

&＃xff08;1&＃xff09;提高查询效率&＃xff08;降低IO使用率&＃xff09;

&＃xff08;2&＃xff09;降低CPU使用率

比如查询order by age desc&＃xff0c;因为B&＃43;索引树本身就是排好序的&＃xff0c;所以再查询如果触发索引&＃xff0c;就不用再重新查询了。

2、索引的弊端

&＃xff08;1&＃xff09;索引本身很大&＃xff0c;可以存放在内存或硬盘上&＃xff0c;通常存储在硬盘上。

&＃xff08;2&＃xff09;索引不是所有情况都使用&＃xff0c;比如①少量数据②频繁变化的字段③很少使用的字段

&＃xff08;3&＃xff09;索引会降低增删改的效率

3、索引的分类

&＃xff08;1&＃xff09;单值索引

&＃xff08;2&＃xff09;唯一索引

&＃xff08;3&＃xff09;联合索引

&＃xff08;4&＃xff09;主键索引

备注&＃xff1a;唯一索引和主键索引唯一的区别&＃xff1a;主键索引不能为null

4、创建索引


alter table user add INDEX &＃96;user_index_username_password&＃96; (&＃96;username&＃96;,&＃96;password&＃96;)

5、MySQL索引原理 -> B&＃43;树

MySQL索引的底层数据结构是B&＃43;树

B&＃43;Tree是在B-Tree基础上的一种优化&＃xff0c;使其更适合实现外存储索引结构&＃xff0c;InnoDB存储引擎就是用B&＃43;Tree实现其索引结构。

B-Tree结构图中每个节点中不仅包含数据的key值&＃xff0c;还有data值。而每一个页的存储空间是有限的&＃xff0c;如果data数据较大时将会导致每个节点&＃xff08;即一个页&＃xff09;能存储的key的数量很小&＃xff0c;当存储的数据量很大时同样会导致B-Tree的深度较大&＃xff0c;增大查询时的磁盘I/O次数&＃xff0c;进而影响查询效率。在B&＃43;Tree中&＃xff0c;所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上&＃xff0c;而非叶子节点上只存储key值信息&＃xff0c;这样可以大大加大每个节点存储的key值数量&＃xff0c;降低B&＃43;Tree的高度。

B&＃43;Tree相对于B-Tree有几点不同&＃xff1a;

非叶子节点只存储键值信息。

所有叶子节点之间都有一个链指针。

数据记录都存放在叶子节点中。

将上一节中的B-Tree优化&＃xff0c;由于B&＃43;Tree的非叶子节点只存储键值信息&＃xff0c;假设每个磁盘块能存储4个键值及指针信息&＃xff0c;则变成B&＃43;Tree后其结构如下图所示&＃xff1a;

通常在B&＃43;Tree上有两个头指针&＃xff0c;一个指向根节点&＃xff0c;另一个指向关键字最小的叶子节点&＃xff0c;而且所有叶子节点&＃xff08;即数据节点&＃xff09;之间是一种链式环结构。因此可以对B&＃43;Tree进行两种查找运算&＃xff1a;一种是对于主键的范围查找和分页查找&＃xff0c;另一种是从根节点开始&＃xff0c;进行随机查找。

可能上面例子中只有22条数据记录&＃xff0c;看不出B&＃43;Tree的优点&＃xff0c;下面做一个推算&＃xff1a;

InnoDB存储引擎中页的大小为16KB&＃xff0c;一般表的主键类型为INT&＃xff08;占用4个字节&＃xff09;或BIGINT&＃xff08;占用8个字节&＃xff09;&＃xff0c;指针类型也一般为4或8个字节&＃xff0c;也就是说一个页&＃xff08;B&＃43;Tree中的一个节点&＃xff09;中大概存储16KB/(8B&＃43;8B)&＃61;1K个键值&＃xff08;因为是估值&＃xff0c;为方便计算&＃xff0c;这里的K取值为〖10〗^{3&＃xff09;。也就是说一个深度为3的B&＃43;Tree索引可以维护10}3 * 10^3 * 10^3 &＃61; 10亿 条记录。

实际情况中每个节点可能不能填充满&＃xff0c;因此在数据库中&＃xff0c;B&＃43;Tree的高度一般都在2_{4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的&＃xff0c;也就是说查找某一键值的行记录时最多只需要1}3次磁盘I/O操作。

数据库中的B&＃43;Tree索引可以分为聚集索引&＃xff08;clustered index&＃xff09;和辅助索引&＃xff08;secondary index&＃xff09;。上面的B&＃43;Tree示例图在数据库中的实现即为聚集索引&＃xff0c;聚集索引的B&＃43;Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据&＃xff0c;而是存储相应行数据的聚集索引键&＃xff0c;即主键。当通过辅助索引来查询数据时&＃xff0c;InnoDB存储引擎会遍历辅助索引找到主键&＃xff0c;然后再通过主键在聚集索引中找到完整的行记录数据。

五、如何触发联合索引

1、对user表建立联合索引username、password

2、触发联合索引

&＃xff08;1&＃xff09;使用联合索引的全部索引键可触发联合索引

&＃xff08;2&＃xff09;使用联合索引的全部索引键&＃xff0c;但是用or连接的&＃xff0c;不可触发联合索引

&＃xff08;3&＃xff09;单独使用联合索引的左边第一个字段时&＃xff0c;可触发联合索引

&＃xff08;4&＃xff09;单独使用联合索引的其它字段时&＃xff0c;不可触发联合索引

六、分析sql的执行计划—explain

explain可以模拟sql优化执行sql语句。

1、explan使用简介

&＃xff08;1&＃xff09;用户表

&＃xff08;2&＃xff09;部门表

&＃xff08;3&＃xff09;未触发索引

&＃xff08;4&＃xff09;触发索引

&＃xff08;5&＃xff09;结果分析

explain中第一行出现的表是驱动表。

1. 指定了联接条件时&＃xff0c;满足查询条件的记录行数少的表为[驱动表]

2. 未指定联接条件时&＃xff0c;行数少的表为[驱动表]

对驱动表直接进行排序就会触发索引&＃xff0c;对非驱动表进行排序不会触发索引。

2、explain查询结果简介

&＃xff08;1&＃xff09;id&＃xff1a;SELECT识别符。这是SELECT的查询序列号。

&＃xff08;2&＃xff09;select_type&＃xff1a;SELECT类型&＃xff1a;

1. SIMPLE&＃xff1a; 简单SELECT(不使用UNION或子查询)

2. PRIMARY&＃xff1a; 最外面的SELECT

3. UNION&＃xff1a;UNION中的第二个或后面的SELECT语句

4. DEPENDENT UNION&＃xff1a;UNION中的第二个或后面的SELECT语句&＃xff0c;取决于外面的查询

5. UNION RESULT&＃xff1a;UNION的结果

6. SUBQUERY&＃xff1a;子查询中的第一个SELECT

7. DEPENDENT SUBQUERY&＃xff1a;子查询中的第一个SELECT&＃xff0c;取决于外面的查询

8. DERIVED&＃xff1a;导出表的SELECT(FROM子句的子查询)

&＃xff08;3&＃xff09;table&＃xff1a;表名

&＃xff08;4&＃xff09;type&＃xff1a;联接类型

1. system&＃xff1a;表仅有一行(&＃61;系统表)。这是const联接类型的一个特例。

2. const&＃xff1a;表最多有一个匹配行&＃xff0c;它将在查询开始时被读取。因为仅有一行&＃xff0c;在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。

3. eq_ref&＃xff1a;对于每个来自于前面的表的行组合&＃xff0c;从该表中读取一行。这可能是最好的联接类型&＃xff0c;除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用&＃61; 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。