MySQL面试题汇总

基础

数据库三大范式

第一范式：数据库的每一列都是不可分割的原子项。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在第二范式的基础上，非主键只依赖于主键，不依赖于其他非主键，即不存在依赖传递。

char和varchar的区别

（1）存储方式：char类型的数据是定长的，当存储时，MySQL会将所有的字符都填充到定长，检索时会去掉空格，因此存储时占用的空间固定；而varchar类型的数据是变长的，它只会存储实际使用的字符，因此存储时占用的空间是可变的。

（2）查找效率：char的查找效率比varchar要高。

（3）字符串长度：char类型的数据长度是固定的，可以存储0到255个字符，而varchar类型的数据长度是可变的，可以存储0到65535个字符。

（4）使用场景：通常，char类型的数据适合存储长度固定的数据，例如国家代码、邮编等；而varchar类型的数据适合存储长度不固定的数据，例如用户名、地址等。

drop、truncate和delete的区别

（1）作用范围：drop会删除整张表和表结构，以及表的索引、约束和触发器；truncate只删除全部表数据，表的结构、索引、约束等会被保留；delete只删除表的全部或者部分数据，表结构、约束、索引等会被保留。

（2）作用方式：delete是DML(data maintain language)语句，执行删除操作的过程是每次从表中删除一行，并同时将该行的删除操作作为事务记录在日志中保存以便于进行回滚操作；truncate、drop是DLL(data define language)语句，删除行是不能恢复的，并且在删除过程中不会激活与表有关的触发器，执行速度比较块，原数据不放到rollback segment中，不能回滚。

（3）条件限制：truncate和drop不支持添加where条件，而delete支持where条件。

（4）执行速度：执行速度drop > truncate > delete，delete是逐行执行的，并且在执行时会把操作日志记录下来，以备日后回滚使用，所以delete的执行速度是比较慢的；而truncate的操作是先复制一个新的表结构，然后删除掉原来的表，所以它的执行速度居中，而drop的执行速度最快。

select from a,b和join的区别

（1）select from a,b使用的是SQL隐式连接（也被称为笛卡尔积），对于表a中的每一行，都会将表b中的所有行与之匹配，生成一个新的结果集。这种连接通常会导致结果集非常大，因为它将两个表中的所有数据都组合在一起，而且没有经过任何的筛选或者限制。

（2）join使用的SQL的显示连接，它使用on子句来指定连接条件，只有满足条件的数据行才会被返回，这种连接方式可以减少结果集的大小，同时还可以更加精细的筛选和限制。

left join 和 inner join的区别

• left join会返回左表中的所有记录和右表中满足连接条件的记录；而inner join只会返回两个表中满足连接条件的记录。
• left join右表关联不上的数据会用null表示；而inner join关联不上的数据直接舍弃。

left join和right join

外连接通过outer join来实现，它将返回两张表中满足连接条件的数据，同时返回不满足条件的数据。

外连接有两种形式：左连接和右连接。

• 左外连接left join on：简称为左连接，它会返回左表中的所有记录和右表中满足连接条件的记录。

  • 两表关联，左表全部保留，右表关联不上用null表示。

  select 字段列表 from 表1 left [outer] join 表2 on 条件 ...;

• 右外连接right join on：简称为右连接，它会返回右表中的所有记录和左表中满足连接条件的记录。

  • 两表关联，右表全部保留，左表关联不上用null表示。

  select 字段列表 from 表1 right [outer] join 表2 on 条件 ...;

• 全连接union：两表的内容均保留，没有关联的字段用null表示。在mysql中使用union表示。

  select 字段列表 from 表A union [ALL] select 字段列表 from 表B

  对于联合查询的多张表的列表必须要保持一致，字段类型也要保持一致。

  union all 会将全部的数据直接合并在一起，union会对合并之后的数据去重。

group by的用法

group by一般用于分组统计，就是可以根据一个或者多个字段，对查询到的数据进行分组。

group by如果单独使用的话，会返回每组第一行的数据。

group by的常规用法就是配合聚合函数，利用分组信息进行统计，同时还可以配合having进行筛选过滤。

另一方面，having子句中不能使用除了分组字段和聚合函数之外的其他字段。

Limit分页的用法

limit子句可以用于强制select语句返回指定的记录数。

select 字段 from 表名 limit 参数1,参数2;
select 字段 from 表名 limit 参数2 offset 参数1;（为了与 PostgreSQL 兼容）
参数1：指定第一个返回记录行的偏移量，从0开始
参数2：指定返回记录行的最大数目
如果只给定一个参数，那么表示返回的最大记录行数目。
如果第二个参数为-1，表示第一个参数的偏移量之后的所有的数据

where和having的区别

（1）where是一个约束声明，在对查询结果返回之前将不符合where条件的数据给去掉，where是在结果返回之前起作用的，where中不能使用聚合函数，即使用where条件过滤出特定的行。

（2）having是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在having中可以使用聚合函数。另一方面，having子句中不能使用除了分组字段和聚合函数之外的其他字段。

从性能的角度来说，HAVING子句中如果使用了分组字段作为过滤条件，应该替换成WHERE子句。因为WHERE可以在执行分组操作和计算聚合函数之前过滤掉不需要的数据，性能会更好。

一般情况下：

• where用于过滤数据行，而having用于过滤分组；
• where查询条件中不可以使用聚合函数，而having查询条件中可以使用聚合函数；
• where在数据分组前进行过滤，而having在数据分组后进行过滤；
• where是针对数据库文件进行过滤，而having是针对查询结果进行过滤；（即where是根据数据表中的字段直接进行过滤，而having是根据前面已经查询出的字段进行过滤）

union和union all的区别

union和union all的用法都是联合查询结果，它们的区别如下：

（1）显示结果不同：union会自动压缩多个结果集合中的重复结果，而union all则是将所有的结果显示出来。

（2）对重复结果的处理不同：union 是取唯一值，会筛选掉重复的记录，有去重的效果；而union all是直接链接，取到的是所有值。

（3）对排序的处理不同：union将会按照字段的顺序进行排序；union all只是简单的将两个结果和合并返回。从效率上来说，union all要比union的效率要高。

另外，使用union和union all必须保证各个select查询的列数必须相同，并且每个列的类型是一样的。但是列名不一定需要相等。

索引

什么是索引

索引是对数据库中的一列或者多列的值进行排序的一种数据结构，可以帮助MySQL实现快速查询和检索数据。

优点：

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

缺点：

• 索引需要使用物理文件存储，占用一定的空间
• 索引大大提高的查询效率，但是同时降低了SQL的执行效率，如实现增删改的时候，效率降低。

什么是全文索引

一文给你讲清楚MySql全文索引实战和原理 - 掘金 (juejin.cn)

全文索引，就是通过建立倒排索引，可以极大的提升检索效率，解决判断字段受否包含的问题。如果我们使用模糊查询%str%，那么当匹配字符在前面的时候，索引就会失效，这时检索效率比较低。全文索引就可以解决这个问题。

数据库索引有什么作用

数据库索引是一种特殊的数据结构，可以提高数据库查询操作的效率。 它通过预先建立一个包含目标数据的索引表，加快了数据库中大数据量的查询速度。

具体来说，索引可以为数据库表中的一列或多列创建一个快速查找路径，并将它们存储在内存或磁盘中。当我们执行一条 SQL 查询时，数据库引擎会首先检查是否存在适用于该查询的索引，如果找到就可以直接使用索引进行查询，避免了对整个数据库表的扫描，从而提高了查询速度。

说说你对索引的理解

MySQL索引是一种用于提高查询效率的数据结构，它可以帮助MySQL快速定位到符合查询条件的数据。通常情况下，索引会被创建在数据表的某一列或多列上，以加速这些列的查询操作。在MySQL中，常见的索引类型包括B-Tree索引、哈希索引、全文索引等。

MySQL索引一共分为三种：

• B-Tree索引是MySQL中最常用的索引类型，它是一种基于B-Tree数据结构的索引，在大多数情况下可以快速地定位到需要查询的数据。B-Tree索引对于范围查询和排序操作也比较高效，因为它可以快速地遍历索引树来定位数据。
• 哈希索引则是一种基于哈希表的索引，它可以提供O(1)的查询效率，即查询的速度非常快。但是哈希索引只适用于等值查询，不能用于范围查询和排序操作。
• 全文索引则是一种用于处理文本数据的索引，它可以快速地定位到包含查询关键词的文本数据。全文索引在处理大量文本数据时可以提高查询效率，但是它的创建和维护成本较高。

在使用MySQL索引时需要注意以下几点：

1. 索引并不是越多越好，过多的索引会影响插入、更新和删除操作的性能。
2. 对于频繁查询的列应该尽可能地创建索引，而对于不经常使用的列则不需要创建索引。
3. 在创建索引时应该考虑到查询的效率和索引的大小，避免创建过大的索引。
4. 在进行数据修改操作时，尽量减少索引的使用，避免出现频繁的索引重建和更新操作。

索引为什么能提升检索的效率

索引能提高检索的效率主要有以下几个方面：

（1）减少了对数据的扫描的数量和范围，提高了数据的查询速度。对于没有索引的情况下，数据库需要通过全表扫描查找符合条件的数据。而如果表中建立了索引，则可以通过直接读取索引进行定位，从而避免了大量的全表扫描，减少了数据扫描的数量和范围。

（2）优化了排序操作。当 SQL 查询包含 ORDER BY 子句时，索引可以帮助优化排序操作，使结果更快地返回。

主键是什么索引

在MySQL中，主键是一种特殊的索引，用于唯一标识数据表中的每一行数据。主键可以是一个或多个列的组合，并且它们的值必须是唯一的，且不能为空。当创建主键时，MySQL会自动为主键列创建一个B-Tree索引来加速主键查找操作。

由于主键值是唯一的，因此主键索引可以帮助MySQL快速地定位到数据表中的唯一一行数据，这对于频繁查询和更新主键列的操作非常有帮助。此外，主键索引还可以作为其他索引的引用，例如外键约束，从而保证数据表之间的关联关系。

需要注意的是，当表中没有主键或主键不明确时，MySQL会使用一个名为“隐藏主键”的索引来代替。这个隐藏主键索引是一个自增整数列，用来唯一标识数据表中的每一行数据。

如何避免回表查询

（1）尽量使用覆盖索引：尽可能使用覆盖索引，即在查询结果中包含所有需要返回的列，这样MySQL就不需要回到数据表中检索数据行，可以直接从索引中获取结果。需要注意的是，覆盖索引通常只适用于查询比较简单的情况。

（2）索引优化：优化查询语句，尽量使用优化的索引。例如，如果查询语句中包含多个列，则可以创建一个包含这些列的复合索引，以提高查询效率。在创建索引时，还可以使用前缀索引、全文索引等技术来优化索引效率。

哪些列需要建立索引

（1）适合建立索引

• 主键，强制该列的唯一性和组织表中的数据结构
• 外键，经常用于表连接，加快表之间的连接速度
• 经常需要查询的列（where），建立索引，增加搜索速度
• 经常需要排序的列，因为索引已经排序，利用索引的排序，加快查询的速度。
• 经常需要统计或者分组的字段

（2）不适合建立索引

• 表中记录比较少的
• 基本用不到where条件的列
• 文本或者超长字段的列
• 频繁进行数据操作的列
• 数据重复且分布平均的列

建立索引的原则

MySQL索引是提高查询效率的重要手段之一，但是索引的建立也需要一定的原则和注意事项。下面是建立MySQL索引的一些原则：

（1）确定需要建立索引的列

在建立索引之前，需要先确定需要建立索引的列。通常，建议将索引建在经常使用作为查询条件的列上，或者在数据表中唯一或者高度重复的列上。如果索引太多或者太少，都可能会影响查询性能。

（2）选择适当的索引类型

MySQL支持多种索引类型，如B-tree索引、哈希索引等。不同的索引类型适合不同的查询场景，需要根据实际情况选择适当的索引类型。

（3）不要在过多列上建立联合索引

联合索引可以在多个列上建立索引，通常可以提高查询效率。但是在建立联合索引时需要注意，不要在过多列上建立联合索引，否则会影响索引的效率。

（4）避免在索引列上使用函数或运算符

在查询条件中，如果使用了函数或运算符，会导致MySQL无法使用索引进行查询，降低查询效率。因此，在建立索引时需要避免在索引列上使用函数或运算符。

（5）确保数据表的数据量足够大

对于数据量较小的数据表，建立索引可能会降低查询性能。因此，在建立索引之前需要确保数据表的数据量足够大，以便充分发挥索引的优势。

（6）定期优化索引

随着数据表中数据的增加和删除，索引的效率也会发生变化。因此，需要定期优化索引，以确保索引的效率最大化。

说说你对B+树的理解

B+树是一种常见的数据结构，常用于实现关系型数据库中的索引。B+树的结构类似于二叉树，但其具有以下特点：

（1）B+树的非叶子节点存储的是索引信息，而叶子节点存储的是数据信息。这使得B+树可以快速定位到数据所在的叶子节点。

（2）每个节点中的关键字按顺序排列，且相邻节点之间的关键字满足顺序约束。这使得B+树可以进行范围查询和排序操作。

（3）B+树的每个节点都有多个子节点，且每个子节点存储的关键字范围是相同的。这使得B+树可以进行快速的查找和插入操作。

（4）B+树的高度较低，通常不超过3-4层，这使得B+树的查询、插入、删除操作都具有较快的速度。

B+树的优点在于其能够高效地支持范围查询、排序和快速定位数据。在实际应用中，B+树常被用于实现关系型数据库的索引结构。B+树的缺点在于其插入和删除操作可能导致树的平衡性失衡，需要进行平衡调整操作，同时节点的分裂和合并操作会导致性能的损失。

索引的数据结构是什么样的

MySQL默认的存储引擎是InnoDB，它采用的是B+树结构的索引。

B+树的特点如下：

（1）B+树的非叶子节点存储的是索引信息，而叶子节点存储的是数据信息。这样可以使得B+树快速定位到数据所在的叶子节点。

（2）非叶子节点之间使用双向指针链接，且相邻之间的关键字满足顺序约束，这使得B+树可以进行范围查询和排序。

（3）B+树的每个节点都有多个子节点，且每个子节点存储的关键字的范围是相同的，这使得B+树可以进行快速查询和插入操作。

索引底层采用的什么数据结构

数据库索引底层采用的数据结构有多种，常见的包括：

• B树（B-Tree）：是一种自平衡的树状数据结构，可用于存储排序后的数据，主要用于提高磁盘访问效率。
• B+树（B+ Tree）：在 B 树的基础上增加了一个链表结构，能够更快地进行遍历和范围查询。
• Hash 索引：使用哈希函数将索引列映射到一个哈希值，并将该值与索引表中的记录关联。它适用于等值比较的查询操作，但不适用于区间查询或模糊查询。
• R 树（R-Tree）：是一种用于空间数据索引的树状数据结构，可以支持空间数据的查询和分析。
• Bitmap 索引：将索引列中的每个值使用二进制位表示，其中每个位表示诸如“存在”、“不存在”等信息。通过位运算可以实现类似于布尔运算的复杂查询。

在选择合适的索引数据结构时需要考虑具体的应用场景和需求，如数据规模、读写比例、查询类型等因素。对于大型数据集和频繁的查询操作，通常采用 B+ 树索引。而对于空间数据和全文搜索等特殊类型的数据，可能需要采用 R 树、全文索引等特定的索引结构。

索引的分类

（1）按数据结构分类：

• B+树索引
• Hash索引
• Full-text索引

（2）按物理存储分类：

• 聚簇索引(主键索引)
• 二级索引(辅助索引)

（3）按字段特性分类：

• 主键索引
• 唯一索引
• 普通索引
• 前缀索引

（4）按字段个数分类：

• 单列索引
• 联合索引

一颗B+树能存储多少条数据

(1280 ^ (n - 1)) * 15  // n为B+树的层数

为什么要使用B+树作为索引

（查询速度、稳定性、存储数据大小、查找磁盘次数方面）

（1）相对于二叉树来说：B+树的层级更少，搜索的效率更高

（2）相对于B-Tree来说，B-Tree无论是叶子节点还是非叶子节点，都会保存数据，这样导致页中的键值减少，同样要保存大量的数据，只能是增加树的高度，这样就会降低了检索的效率。

（3）相对于Hash索引来说，B+Tree支持范围匹配以及排序操作。

（4）内存空间：一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

B+树索引和hash索引的区别

（1）是否支持排序。哈希索引不支持排序，因为哈希表是无序的。B+树索引是有序的，并且支持排序。

（2）是否支持范围查找。哈希索引无序，所以不支持范围查找。

（3）是否支持模糊查询。哈希索引不支持模糊查询以及索引最左前缀匹配。

（4）效率方面。哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定的。

B树和B+树的区别

（1）B树：一种多路自平衡搜索树，通过节点存储，并对节点进行了排序；一个节点可以存储多个元素，元素之间也是排序的。

（2）B+树：B+树是B树的变种，叶子节点之间有指针，非叶子节点上不存储元素，元素的存储都在叶子节点上，并且是排好序的。

（3）区别：

• 节点结构：B树中的每个结点既包含索引键也包含数据，并且叶子节点之间没有关系；而B+树种的每个非叶子节点只包含索引键，数据只存在叶子节点中，叶子节点之间有指针连接。
• 查找方式：B树中，通过从根节点开始，根据节点中的索引键找到对应的子节点，并继续查找，直到叶子节点中找到目标数据。而在B+树中，只需要从根节点开始，根据节点中的索引键找到叶子节点，并在叶子节点中查找目标数据。
• 插入和删除操作：在B树中，插入和删除数据时，需要将数据插入或者删除到对应的叶子节点中，如果叶子节点中的数据个数少于阈值，则需要将节点中的数据进行调整。而在B+树中，插入和删除数据时，只需要调整对应的叶子节点，并更新父节点中的索引即可。
• 范围查找：B+树比B树更适合范围查找，因为B+树的所有叶子节点都包含了数据，而B树的非叶子节点也包含了数据，因此在进行范围查找时，需要遍历B树的所有节点，而B+中只需要遍历叶子节点即可。
• 效率：B树的每个节点都存储了数据，因此每次查找的IO开销比较大，而在B+树中，非叶结点不含有数据，就可以包含更多关键字，磁盘IO的次数比较少，而且每次都需要查找到叶子节点，效率比较稳定，时间复杂度未为O(logN)。

B+树影响IO次数的因素

（1） B+树的高度：B+树的高度越低，需要访问的节点数就越少，从而IO次数就越少。B+树的高度受到节点大小和节点个数的限制，通常情况下，节点大小越大，节点个数越多，B+树的高度就越低。

（2）磁盘块的大小：磁盘块是磁盘IO的最小单位，磁盘块大小越大，每次读取或写入的数据就越多，从而IO次数就越少。B+树节点大小的选择应该与磁盘块大小相适应，通常情况下，节点大小应该等于或略小于磁盘块大小。

除了这两个因素，B+树的节点分裂和合并操作也会影响IO次数。在插入和删除数据时，如果需要进行节点分裂或合并操作，则需要进行额外的IO操作，从而增加了IO次数。因此，B+树的节点分裂和合并操作应该尽量减少，这可以通过调整节点大小和阈值来实现

B+树的层数取决于什么因素

（1）节点大小：B+树的每个节点存储的关键字个数越多，每个节点能够覆盖的数据范围就越大，B+树的层数也就越低。通常情况下，节点大小应该等于或略小于磁盘块大小，这样可以减少IO次数，提高查询效率。

（2）关键字总数：B+树的关键字总数越多，B+树的层数就越高。在设计B+树的时候，应该考虑到数据规模和查询频率，避免出现B+树的层数过高的情况。

（3）每个节点的叶子节点数：B+树的每个节点存储的子节点数越多，B+树的层数就越低。但是，子节点数过多也会增加节点的大小，导致IO次数增加。在设计B+树的时候，应该平衡节点大小和子节点数，使B+树的层数和查询效率达到一个最优点。

（4）页分裂和合并操作：在插入和删除数据时，如果需要进行节点分裂或合并操作，则会导致B+树的层数发生变化。分裂操作会使B+树的层数增加，合并操作会使B+树的层数减少。因此，B+树的节点分裂和合并操作应该尽量减少，这可以通过调整节点大小和阈值来实现。

B+树的每一层越宽越好吗？

B+树每一层越宽不一定越好，它需要平衡节点宽度和树高度来达到最佳查询性能。

当B+树的节点宽度较大时，每个节点可以存储更多的键值对，可以减少树高度，从而减少访问磁盘的次数，提高查询性能。但是，节点宽度过大也会导致节点占用的存储空间变大，可能会出现一些存储空间浪费的情况。

当B+树的节点宽度较小时，节点可以存储的键值对数量较少，B+树的高度可能会增加，从而访问磁盘的次数也会增加。但是，节点宽度较小可以减少存储空间浪费，也可以提高节点的利用率。

因此，选择节点宽度的大小应该根据实际情况进行综合考虑。通常情况下，节点宽度应该根据磁盘块的大小和查询性能的要求进行平衡，使得B+树的高度最小化，并且节点的利用率和存储空间利用率也尽可能地高。

B+树如何实现平衡

B+树的平衡主要是通过节点的拆分和自行控制行高来实现的。

（1）节点拆分。当一个节点中的关键字数目超过了预定的数目时，需要将该节点进行拆分，以保证树的平衡。具体地，将该节点中的关键字一分为二，分别分配给两个新节点，同时将分裂出的关键字上移到父节点中。这样，树的高度会增加1，但是所有节点的关键字数目都得到了控制，从而保证了树的平衡。

（2）自行控制行高。B+树中的叶子节点只存储数据，而非数据和索引，这样可以避免非叶子节点的频繁访问，从而提高查询效率。同时，为了保证B+树的平衡，可以将所有叶子节点的高度控制在相同的水平上，即所有叶子节点的深度相等。当叶子节点中的数据发生变化时，需要将变化后的节点重新排序并重建叶子节点之间的链表，以保证所有叶子节点的深度相等。

聚簇索引和非聚簇索引的区别

聚簇索引是将数据和索引存储在一起，与索引的顺序相同。它们通常是主键索引，因为主键是表中的唯一标识符。种索引的优点是可以快速地对表进行搜索和排序。当使用聚簇索引进行查找时，数据库可以通过直接读取数据页来避免大量的I/O操作，从而提高查询效率。但缺点是当更新数据时，需要对整个表进行重组，因此更新操作可能会变得更慢。

非聚簇索引是将索引和数据分开存储。索引包含指向数据行的指针，因此当使用非聚簇索引进行查询时，数据库需要进行两次磁盘访问：一次访问索引页，另一次访问数据页。这种索引的优点是在进行更新操作时不需要重组整个表，因此更新操作更快，但缺点是查询时需要额外的I/O操作，因此查询速度可能会受到影响。

索引查询过程

（1）解析SQL并且优化查询计划。MySQL在执行查询之前，需要先解析SQL语句并进行优化，以确定如何执行查询。在优化查询计划时，MySQL会尝试使用索引来加速查询操作。

（2）查找索引。如果查询语句中包含WHERE条件或JOIN操作，MySQL会查找匹配这些条件的索引。如果找到多个索引，则会选择一个最适合的索引。如果没有找到索引，则会执行全表扫描。

（3）读取索引并获取数据行。当MySQL找到匹配条件的索引后，它会使用索引来定位数据行的位置。这通常需要从磁盘读取索引和数据行。如果数据行不在内存中，则需要从磁盘中读取数据行。这是查询操作中最耗时的步骤之一。

（4）过滤数据行。MySQL会根据WHERE条件过滤数据行，以删除不匹配的数据行。这可以减少需要返回的数据量，提高查询速度。

（5）返回查询结果。当MySQL找到匹配条件的数据行后，它会将这些数据行返回给客户端。

索引失效的场景

15个必知的Mysql索引失效场景，别再踩坑了！ - 掘金 (juejin.cn)

（1）联合索引不满足最左匹配原则。联合索引的使用遵循最左匹配原则，在联合索引中，最左侧的字段优先匹配。在查询的时候，如果想让查询条件走索引，那么最左边的字段要出现在查询条件中。

（2）运算操作或者函数操作。原因是因为如果没有进行运算操作，那么MySQL会直接走索引去B+树中查询数据。但是如果我们对字段进行了运算操作或者函数操作，实际上是拿新的字段去做查询，如果走索引，那么需要一个一个去对比，MySQL认为既然都要对比，不如直接全表扫描。

（3）字符串类型字段不加引号。字符串字段如果不加引号，会发生隐式转换成int类型，参数类型与字段类型不匹配。

（4）模糊查询匹配头部。索引本身相当于目录，将字符串字段从左到右依次排序，而左侧的占位符模糊匹配，导致无法正常按照目录进行匹配。所以索引会失效。

（5）or连接的条件。如果or连接的条件左右其中有一个不是索引字段，那么如果是单独使用的话肯定是要走全表扫描的，连接在一起再次进行索引查询反而是浪费性能了，所以索引会失效。

（6）索引列做 < 、> 或者<> 比较。

（7）查询使用is not null。如果使用is null正常走索引，但是使用is not null索引会失效。

（8）查询条件使用not in时，如果是主键走索引；如果是普通索引，则索引失效。

（9）索引区分度比较高。查询优化器发现某个值出现在表的数据行中的百分比(惯用的百分比界线是30%)很高的时候，会忽略索引，进行全表扫描。

存储引擎

MySQL的执行引擎有哪些

主要有MyISAM、InnoDB、Memery等。

• InnoDB引擎是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的存储引擎。提供了对事务ACID的支持，还提供了行锁和外键的约束。
• MyISAM是MySQL早期的默认的存储引擎，不支持事务、也不支持外键，支持表锁，访问速度较快。
• Memery将数据放在内存中，数据的处理速度很快，但是安全性和可靠性不高。

MyISAM和InnoDB的区别，如何选择

• 锁的细粒度不同：InnoDB比MyISAM更好的支持并发，因为InnoDB的支持行锁，而MyISAM支持表锁，行锁对每一条记录上锁，所以开销更大，但是可以解决脏读和不可重复读的问题，相对来说也更容易发生死锁。
• 可恢复性：InnoDB有事务日志，数据库崩溃后可以通过日志进行恢复，MyISAM没有日志支持。
• 查询性能：MyISAM要好于InnoDB，因为InnoDB在查询过程中是在维护数据缓存。并且先要定位到所在数据块，然后从数据块定位到数据内存地址来查找数据。
• 表结构文件：MyISAM 的表结构文件包括 .frm(表结构定义)，.MYI(索引)、.MYD（数据）；而InnDB的表数据文件为 .ibd(数据和索引集中存储)和.frm(表结构定义)。
• 记录存储顺序：MyISAM按照记录插入顺序，InnoDB按照主键大小顺序有序插入。
• 外键和事务：MyISAM均不支持，InnoDB支持。对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务。对一个包含外键的InnoDB表转为MYISAM会失败。
• 操作速度：对于SELECT前者更优，INSERT、UPDATE、DELETE后者更优。select count(*)使用MyISAM更块，因为内部维护了一个计数器，可以直接调度。
• 存储空间：MyISAM可被压缩，存储空间较小，InnoDB的表需要更多的内存和存储，会在主内存中建立专用的缓冲池用于高速缓存数据和索引。
• 索引方式：二者都是B+树索引，前者是堆表，后者是索引组织表。

如果没有特别的需求，使用默认的InnoDB即可。

要支持事务选择InnoDB，如果不需要可以考虑MyISAM；如果表中绝大多数只是读查询考虑MyISAM，如果既有读也有写考虑InnoDB。

MyISAM索引和InnoDB索引的区别

• 索引类型不同：InnDB是聚簇索引，MyISAM是非聚簇索引。
• 叶子节点存储数据不同：InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
• 查询效率不同：InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

SQL优化

深度分页优化

比如limit 100000, 10，这条SQL在执行的过程中，通过非聚簇索引去查询主键，然后拿到主键再通过聚簇索引进行回表查询，查询到满足条件的100010条数据，丢弃前面的100000条，返回最后10条。

优化的思路也很清楚，就是减少回表的次数，尽量通过索引来查询。

一般的优化方案是 覆盖索引 + 子查询的方式进行优化。

• 将非聚簇索引上使用索引覆盖，这样就可以不用回表了。
• 使用where子查询，先查询出符合条件的id，然后对id当作条件进行判断。

select * from user limit 1000000, 10;
// inner join 延迟关联
select s.* from user s, (select id from user limit 1000000,10) a where s.id = a.id;
// 覆盖索引
select id,username from user order by username limit 10000000, 10;   
// 子查询优化
select * from user where id >= (select id from user limit 10000000, 1) limit 10; 
// 记录上次查询的位置 
select * from user where id >= 10000000 limit 10;

sql执行较慢如何排查和优化

（1）没有索引或者 SQL 没有命中索引：索引可以加速 SQL 查询，如果没有合适的索引或者 SQL 没有命中索引，查询就会变得很慢。可以通过分析查询执行计划来确定是否存在索引问题，并根据执行计划的建议创建或修改索引。

（2）单表数据过多，导致查询瓶颈：如果单表数据过多，查询就会变得很慢。可以通过分区、水平或者垂直拆分表等方法来优化表结构，以减少查询数据量。

（3）网络原因或者机器负载过高：如果网络延迟高或者机器负载过高，SQL 查询就会变得很慢。可以通过增加网络带宽、优化网络拓扑、升级硬件等方法来解决网络或者机器负载问题。

（4）热点数据导致单点负载不均衡：如果热点数据过多，可能导致单点负载不均衡，影响 SQL 查询性能。可以通过负载均衡、缓存、分布式存储等方法来解决单点负载不均衡问题。

如何定位和处理慢查询

（1）开启慢查询日志。通过开启慢查询日志，MySQL会记录所有执行时间超过阈值的查询，从而可以分析和优化慢查询。可以通过设置参数slow_query_log和long_query_time来控制慢查询日志的行为和阈值。

（2）使用EXPLAIN命令或DESC命令分析查询。使用EXPLAIN命令可以分析查询的执行计划，以便识别潜在的瓶颈和优化机会。可以根据EXPLAIN的输出来调整查询语句和索引。

（3）优化查询语句。在设计查询语句时，应该尽量避免全表扫描、使用不必要的子查询和JOIN操作等，可以通过修改查询语句来提高查询性能。

（4）创建合适的索引。索引可以提高查询性能，但需要根据具体情况来选择合适的索引类型和字段。可以使用SHOW INDEX命令来查看表的索引情况。

（5）调整系统参数。可以通过调整MySQL的参数来提高查询性能，如innodb_buffer_pool_size、query_cache_size等。

如何对SQL进行调优

SQL优化主要从三点考虑：最大化利用索引、尽量避免全表扫描、减少无效数据的查询。

（1）根据慢查询日志定位慢查询SQL。慢查询日志记录了所有执行时间超过指定值的SQL语句的日志，默认为10秒。

（2）使用explain或者esc查看SQL的执行计划。

• 首先查看type字段，type字段表示访问表的方式，查看是否出现了全表扫描。
• 然后查看extra字段，查看是否出现了filesort或者using template值，这两种情况都是可以使用索引优化的。
• 最后查看key字段，查看具体使用了什么类型的索引，确定是否需要回表，尽可能使用主键索引，减少回表查询。
• 另外还可以查看filtered字段，表示返回结果的行数占需读取行数的百分比，filter的值越大越好。

MySQL事务

事务如何使用

事务是一组操作的集合，它是一个不可分割的最小的工作单位，事务会把所有的操作作为一个整体一起向系统提交或者撤销操作请求，即这些操作要么同时成功，要么同时失败。

start transaction; # 开启事务 
begin; # 开启事务
commit; # 提交事务 
rollback; # 回滚事务 
select @@autocommit; # 查看事务的提交方式
set @@autocommit; # 设置事务的提交方式

事务的特性

（1）原子性（atomicity）：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。事务是操作的最小单位，不可再分。

（2）一致性（consistency）：数据库总是从一个一致性转换到另一个一致性。即事务发生前和事务发生后，数据的完整性必须保持一致。

（3）隔离性（isolation）：多个事务之间的数据是相互隔离的，当并发访问数据库时，一个正在执行的事务的修改对其他事务时不可见的。

（4）持久性（durability）：一个事务一旦被提交或者回滚，它对数据库中的数据的改变是永久性的。即使出现错误，事务也不允许撤销，只能通过“补偿性事务”。

事务的隔离级别

**读未提交(read uncommitted)**：指一个事务还没提交时，它做的变更就能被其他事务看到。读未提交的数据，称之为脏读(Dirty Read)。

**读已提交(read committed)**：指一个事务提交之后，它做的变更才能被其他事务看到。这种隔离级别支持不可重复读(Nonrepeatable Read)，因为同一事务在不同时期读取到的数据可能是不一样的。

可重复读(repeatable read)：指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别。但是会产生幻读的问题，当前事务读取某一范围的数据时，另一个事务又在该范围内插入了新行，用户再次读取时，会出现新的“幻影”。

**可串行化(serializable )**：会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

查看隔离级别

select @@transaction_isolation;

设置隔离级别

set session transaction isolation level read uncommitted;

并发事务会出现的问题

脏读：脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。

不可重复读：对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新操作。

幻读：幻读是针对数据插入操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，让用户感觉感觉出现了幻觉，这就叫幻读。

可重复读：可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。

MySQL的默认隔离级别是什么

MySQL InnoDB 存储引擎的默认支持的隔离级别时REPEATABLE-READ（可重复读）。

可以通过select @@tx_isolation; 命令来查看，MySQL8.0可以使用select @@transaction_isolation;。

MySQL中的锁

锁的分类

MySQL中的锁可以按照锁的粒度划分成：

（1）行锁：锁定单行数据，其他会话可以访问表中的其他行，适用于对数据进行细粒度操作时使用。

（2）表锁：锁定整张表，其他会话无法访问该表中的任何行，适用于对表进行全局操作时使用。

按照使用方式划分：

（1）共享锁：多个事务可以同时持有相同的共享锁，但是不允许进行写操作，只能进行读操作。使用方式是在查询语句后面加上lock in share mode。

（2）排他锁：只允许一个事务持有它，其他事务不能同时持有相同锁，也不能进行读写操作。使用方式是在需要执行的语句后面加上for update。对于update语句，MySQL会自动加上排他锁。

按照思想的划分：

（1）乐观锁：总是假设最好的情况，因此在进行操作时不加锁，而是在提交时检查是否有其他事务已经修改了数据，如果没有，则提交成功，如果有，则回滚操作。

（2）悲观锁：总是假设最坏的情况，因此在进行操作时先加锁，然后再进行数据修改。悲观锁会对数据进行加锁保护，以防止其他事务再对数据进行修改。

如何实现悲观锁和乐观锁

悲观锁

• SELECT … FOR UPDATE：在事务中，通过执行SELECT ... FOR UPDATE语句可以获得悲观锁。这会在读取时对选定的行进行加锁，确保其他事务无法在同一时间内对这些行进行修改。
• SELECT … FOR SHARE：与FOR UPDATE类似，但FOR SHARE是共享锁，用于防止其他事务获取悲观锁。

乐观锁

• 版本号：在表中增加一个版本号字段，每次更新时增加版本号，而在更新时，带上当前的版本号进行检查。如果版本号匹配，说明没有被其他事务修改，允许更新。
• CAS：使用CAS操作，通过比较当前值是否与预期值相等，然后进行更新。在MySQL中，可以通过UPDATE语句的WHERE子句来实现。