MySQL 大厂面试题总结（2023最新版）

1、索引

1.1 简介

索引是帮助MySQL高效获取数据的排好序的数据结构，相当于目录

每一个节点对应了一条数据，一个内存地址

1.2 索引的数据结构为什么是B+Tree和HASH？

1.2.1 二叉树

二叉树（Binary Tree）是有限个节点的集合，这个集合可以是空集，也可以是一个根节点和两颗不相交的子二叉树组成的集合，其中一颗树叫根的左子树，另一颗树叫右子树。所以二叉树是一个递归地概念。

• 只有左子节点和右子节点，每次出现自增会一直往右延伸，变成链表。
• 高度会特别高，和磁盘做交互效率较慢，
• 索引按页来查询，每页相当于这里一排
  

1.2.2 红黑树

属于二叉树的一种，多了自平衡，层级会很高（树的深度会很深）交互会较频繁

而且每次自平衡，会有自身的一个重新排序，插入效率较低
红黑树的特性
（1）每个节点或者是黑色，或者是红色。
（2）根节点是黑色。
（3）每个叶子节点（NIL）是黑色。
（4）如果一个节点是红色的，则它的子节点必须是黑色的。[注意：这里叶子节点，是指为空(NIL)的虚节点！]
（5）从一个节点到该节点的子孙节点的所有路径上包含相同数目的黑节点。

1.2.3 B-Tree

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列节点中的数据索引从左到右递增排列
• 可以将数字看成自增的主键id，data相当于除主键之外的其他数据
• 每次加载一页的时候，需要把一整行数据(主键+其他数据)都加载到内存中，但我们其实不需要data
• 相对来说耗费IO和内存空间

1.2.4 B+Tree(B-Tree变种)

• 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能
• 和内存交互的时候只需要将叶子结点的索引存储在内存中，
• 一个数字8b，指针4b，一页16K相当于16384b，可以存储1365+个
• 与数据库交互最多三次就能检索到你需要的数据在磁盘的哪个位置

1.2.5 Hash

• 对索引的key进行一次hash计算就可以定位出数据存储的位置
• 很多时候Hash索引要比B+ 树索引更高效，时间复杂度O1
• 仅能满足 “=”，“IN”，不支持范围查询（比如时间范围查询）
• hash冲突问题

1.3 索引的类型

1.3.1 MySQL的存储引擎

1.3.1.1 InnoDB存储引擎索引实现(聚集)

• 表数据文件本身就是按B+Tree组织的一个索引结构文件
• 聚集索引-叶节点包含了完整的数据记录
• 为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？（减少二级索引查找一级索引的过程）
• 我们设计完主键之后，会自动生成主键索引树Primary Key（哪怕没有设置主键，Mysql会自己找一个没有重复的字段作为主键）
• 为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)
• 我们在数据库中创建的索引都是二级索引，会通过我们创建的索引字段，找到一个id（一级索引的id），在通过这个id在主键索引中找数据（回表）

1.3.1.2 MyISAM存储引擎索引实现(非聚集)

MyISAM索引文件和数据文件是分离的(非聚集)

一个文件专门存储索引在另一个文件中的位置，检索较慢

1.3.1.3 联合索引

索引最左前缀原理：第一优先根据索引设置的时候字段顺序排序

1.4 索引使用的情况

1.4.1 EXPLAIN关键字

EXPLAIN SELECT	* FROM`user`WHERE id = 1

字段名	含义
id	sql的执行顺序
select_type	查询难度
table	表名
type	const(常量)、eqref(通过主键索引查找)、ref(通过非主键索引查找)、range(范围查询)、ALL(全表扫描)
possible_keys	可能用到的索引
key	真实用到的索引
key_len	索引的长度（用来判断索引的效率）
rows	行（索引扫的行数）

1.5 索引失效的情况

1.5.1 为什么主键要设置成自增的？

UUID：随机插入，所有索引在B+数中位置都需要重新排序，导致分裂，效率越来越慢，数据碎片化。

自增：在B+数的最后面存储，仅会影响到它的父节点。

1.5.2 建表上的优化

• 设计表字段类型

• 不同的int有不同的表示范围

• 根据表对应的业务场景，主键自增的趋势，用所占磁盘空间最小的类型

• 数字：

  id一般使用bigint，如果仅需要表示几种状态，可以用tinyint（表示范围更小，占得空间也越小）

  除了varchar，其他类型设置长度其实没有意义，所占内存是定长的

• 文字：

  char和varchar的区别：char为定长，varchar是可变的

  针对大量的文字时使用text/longtest类型存储，优化策略：垂直分表，将Test字段单独分出去，关联主表

• 时间：

  datetime：都可以显示时分秒，占用空间更多

  timestamp：都可以显示时分秒，占用空间更少，缺陷有默认数据范围，只能存放1979-2050年的数据

1.5.3 索引失效的案例

• 针对联合索引，要遵循最左前缀原则，
• 查询条件按照索引设置时字段的顺序
• 范围查询要在精准查询之后
• 使用like模糊查询时，尽量将%写在右侧

2、建表规约（阿里巴巴开发手册）

2.1【强制】表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint（1 表示是，0 表示否）。

说明：任何字段如果为非负数，必须是 unsigned。

注意：POJO 类中的任何布尔类型的变量，都不要加 is 前缀，所以，需要在设置从 is_xxx 到

Xxx 的映射关系。数据库表示是与否的值，使用 tinyint 类型，坚持 is_xxx 的命名方式是为了明确其取值含

义与取值范围。

正例：表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除。

2.2【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。

说明：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、

字段名，都不允许出现任何大写字母，避免节外生枝。

正例：aliyun_admin，rdc_config，level3_name

反例：AliyunAdmin，rdcConfig，level_3_name

2.3【强制】表名不使用复数名词。

说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数形式，符合

表达习惯。

2.4【强制】禁用保留字，如 desc、range、match、delayed 等，请参考 MySQL 官方保留字。

2.5【强制】主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名。

说明：pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的简称。

2.6【强制】小数类型为 decimal，禁止使用 float 和 double。

说明：在存储的时候，float 和 double 都存在精度损失的问题，很可能在比较值的时候，得到不正确的

结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。

2.7【强制】如果存储的字符串长度几乎相等，使用 char 定长字符串类型。

2.8【强制】varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率。

2.9【强制】表必备三字段：id, gmt_create, gmt_modified。

说明：其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。gmt_create, gmt_modified

的类型均为 datetime 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新。Java 开发手册

2.10【推荐】表的命名最好是遵循“业务名称_表的作用”。

正例：alipay_task / force_project / trade_config

2.11.【推荐】库名与应用名称尽量一致。

2.12【推荐】如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。

2.13【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：

• 1） 不是频繁修改的字段。
• 2） 不是唯一索引的字段。
• 3） 不是 varchar 超长字段，更不能是 text 字段。

正例：各业务线经常冗余存储商品名称，避免查询时需要调用 IC 服务获取。

2.14【推荐】单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。

说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

2.15【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

正例：无符号值可以避免误存负数，且扩大了表示范围。

对象	年龄区间	类型	字节	表示范围
人	150 岁之内	tinyint unsigned	1	无符号值：0 到 255
龟	数百岁	smallint unsigned	2	无符号值：0 到 65535
恐龙化石	数千万年	int unsigned	4	无符号值：0 到约 43 亿
太阳	约 50 亿年	bigint unsigned	8	无符号值：0 到约 10 的 19 次方

3、索引规约（阿里巴巴开发手册）

3.1【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。

说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，

即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

3.2【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。

说明：即使双表 join 也要注意表索引、SQL 性能。

3.3【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%

以上，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

3.4【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

3.5【推荐】如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索

引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。

正例：where a=? and b=? order by c; 索引：a_b_c

反例：索引如果存在范围查询，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引 a_b 无

法排序。

3.6【推荐】利用覆盖索引来进行查询操作，避免回表。

说明：如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，这

个目录就是起到覆盖索引的作用。

正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效

果，用 explain 的结果，extra 列会出现：using index。

3.7【推荐】利用延迟关联或者子查询优化超多分页场景。

说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当

offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL

改写。

正例：先快速定位需要获取的 id 段，然后再关联：

SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

3.8【推荐】SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts最好。

说明：

• 1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
• 2） ref 指的是使用普通的索引（normal index）。
• 3） range 对索引进行范围检索。

反例：explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range

还低，与全表扫描是小巫见大巫。

3.9【推荐】建组合索引的时候，区分度最高的在最左边。

正例：如果 where a=? and b=?，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。

说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c>? and d=?

那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即建立组合索引 idx_d_c。

3.10【推荐】防止因字段类型不同造成的隐式转换，导致索引失效。

3.11【参考】创建索引时避免有如下极端误解：

• 1） 索引宁滥勿缺。认为一个查询就需要建一个索引。
• 2） 吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。
• 3） 抵制惟一索引。认为惟一索引一律需要在应用层通过“先查后插”方式解决。

4、SQL语句（阿里巴巴开发手册）

4.1【强制】不要使用 count(列名)或 count(常量)来替代 count()，count()是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

4.2【强制】count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。

4.3【强制】当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为NULL，因此使用 sum()时需注意 NPE 问题。

正例：可以使用如下方式来避免 sum 的 NPE 问题：

SELECT IFNULL(SUM(column), 0) FROM table;

4.4【强制】使用 ISNULL()来判断是否为 NULL 值。

说明：NULL 与任何值的直接比较都为 NULL。

• 1） NULL<>NULL 的返回结果是 NULL，而不是 false。
• 2） NULL=NULL 的返回结果是 NULL，而不是 true。
• 3） NULL<>1 的返回结果是 NULL，而不是 true。

反例：在 SQL 语句中，如果在 null 前换行，影响可读性。

select * from table where column1 is null and column3 is not null;

而ISNULL(column)是一个整体，简洁易懂。从性能数据上分析，ISNULL(column)执行效率更快一些。

4.5【强制】代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。

4.6【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

4.7【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

4.8【强制】数据订正（特别是删除或修改记录操作）时，要先 select，避免出现误删除，确认无误才能执行更新语句。Java 开发手册

4.9【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。

说明：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且

操作列在多个表中存在时，就会抛异常。

正例：

select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;

反例：在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年后，最近在某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出 1052 异常：Column ‘name’ in field list is ambiguous。

4.10【推荐】SQL 语句中表的别名前加 as，并且以 t1、t2、t3、…的顺序依次命名。

说明：

• 1）别名可以是表的简称，或者是根据表出现的顺序，以 t1、t2、t3 的方式命名。
• 2）别名前加 as使别名更容易识别。

正例：

select t1.name from table_first as t1, table_second as t2 where t1.id=t2.id;

4.11【推荐】in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内。

4.12 【参考】因国际化需要，所有的字符存储与表示，均采用 utf8 字符集，那么字符计数方法需要注意。

• 说明：

  SELECT LENGTH("轻松工作")； 
  

• 返回为 12

  SELECT CHARACTER_LENGTH("轻松工作")； 
  

• 返回为 4

• 如果需要存储表情，那么选择 utf8mb4 来进行存储，注意它与 utf8 编码的区别。

【参考】TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE

无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句。

说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

5、什么是Buffer Pool

1.1 基本概念

缓冲池，也称BP。

由缓存数据页（Page）和对缓存数据页进行描述的控制块组成，

控制块中存储着对应缓存页的的所属的表空间、数据页的编号、以及对应缓存页在Buffer Pool中的地址等信息。

1.2 大小

默认128M，以Page页为单位，Page页默认大小16K

控制块的大小约为数据页的5%，大概800字节

1.3 作用：

缓存表数据与索引数据，减少磁盘IO，提升性能。

1.4 如何判断一个页是否在BufferPool中缓存？

Mysql中有一个哈希表数据结构，它使用表空间号+数据页号，作为一个key，然后缓冲页对应的控制块作为value。

• 当需要访问某个页的数据时，先从哈希表中根据表空间号+页号看看是否存在对应的缓冲页。
• 有，则直接使用；没有，则从free链表中选出一个空间的缓冲页
• 再将磁盘中对应的页加载到该缓冲页的位置。

6、InnoDB如何管理Page页

6.1 Page页分类

BP底层采用链表数据结构管理Page。

在InnoDB访问表记录和索引时会在Page页中缓存，减少磁盘OP操作，提升效率。

• free page ：空闲page，未被使用
• clean page：被使用page，数据没有被修改过
• dirty page：脏页，被使用page，数据被修改过，Page页中的数据和磁盘的数据产生了不一致

6.2 通过三种链表结构来维护和管理

6.2.1 free list：表示空闲缓冲区，

• 管理free page
• 把所有空闲的缓冲页对应的控制块作为一个个节点，放在一个链表中，称之为free链表
• 基结点：free链表中一个基节点是不记录缓存页信息（单独申请空间），它里面就存放了free链表的头节点的地址，尾结点的地址，还有free链表里当前有多少个节点。

6.2.2 flush list：表示需要刷新到磁盘的缓冲区，

• 管理dirty page，内部page按修改时间排序

• InnoDB引擎为了提高处理效率，在每次修改缓存页后，并不是立刻把修改刷新到磁盘上，而是在未来的某个时间点进行刷新非操作，所以需要使用flush链表存储脏页，凡是被修改过的缓冲页对应的控制块都作为节点加入到flush链表

• flush链表的结构与free链表的结构相似

6.2.2 lru list：表示正在使用的缓冲区

• 管理clean page和dirty page，缓冲区以midpoint为基点，
• 前面链表称为new列表区，存放经常访问的数据，站63%；
• 后面的链表称为old区，存放使用较少的数据，占37%。

7、为什么写缓冲区，仅适用于非唯一普通索引页？

7.1 change Buffer 基本概念

写缓冲区，针对二级索引（辅助索引）页的更新优化措施。

7.2 作用

在进行DML操作(更新操作)时，如果请求的辅助索引（二级索引）没有在缓冲池中时，

并不会立刻将磁盘页加载到缓冲池，而是在change Buffer记录缓冲变更，

等未 来数据被读取时，再将数据合并恢复到DB中。

7.3 change Buffer 的更新流程

7.4 写缓冲区，仅适用于非唯一普通索引页，为什么？

• 如果在索引设置唯一性，进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘（IO操作）。
• 直接将记录查询到BufferPool中，然后再缓冲池修改，不会再ChangeBuffer中操作。

8、MySQL为什么改进LRU算法？

8.1 普通LRU算法：

LRU = Least Recently Used （最近最少使用）：末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰。

最近被访问的数据，则其未来被访问的概率较大。

• 当要访问某个页时，如果不在Buffer Pool，需要把该页加载到缓冲池，并且把该缓冲页对应的控制块作为节点添加到LRUL链表的头部。
• 当要访问某个页时，如果在Buffer Pool中，则直接把该页对应的控制块移动到LRU链表的头部。
• 当需要释放空间时，从最末尾淘汰。

8.2 普通LRU算法的优缺点

优点：

• 所有最近使用的数据都在链表头部，最近未使用的数据都在链表尾部，保证热数据能最快获取到。

缺点：

• 如果发生全表扫描（比如：没有建立合适的索引 or 查询时使用 select * 等），则有很大可能将真正的热数据淘汰掉。
• 由于MySQL中存在预读机制，很多预读的页都会被放到LRU链表的表头。如果这些预读的页都没有用到，会导致很多尾部的缓冲页很快被淘汰掉。

8.3 改进LRU算法

• 将链表分为new和old两个部分，加入元素时并不是从表头加入
• 而是从中间midpoint位置插入（从磁盘中新读的数据放在冷数据区的头部），
• 如果数据很快被访问，那么page就会向new列表头部移动，
• 如果数据没有被访问，会逐步将old尾部移动，等待淘汰。

8.4 冷数据区的数据什么时候会被转到热数据区呢？

如果该数据页在LRU链表中存在的时间超过1s，就将其移动到链表头部（指整个LRU链表的头部）

如果该数据页在LRU链表中存在的时间短于1s，其位置不变（由于全表扫描有一个特点，它对某个页的频繁访问总耗时会很短）

1s这个时间是由参数innodb_old_blocks_time控制的。

9、使用索引一定可以提升效率吗？

9.1 本质

索引就是排好的序，帮助我们进行快速查找的数据结构。

一种将数据库中的记录按照特殊形式存储的数据结构。通过索引，能够显著的提高数据查询的效率，从而提升服务器的性能。

9.2 索引的优缺点

优点：

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

缺点：

• 创建索引和维护索引要耗费时间，这种时间成本随着数据量的增加而增加。
• 索引需要站用物理空间，除了数据表占用数据空间之外，每个索引还要占用一定的物理空间。
• 当对表中的数据进行正删改操作的时候，索引也要动态的维护，降低了数据的维护速度。

创建索引的原则：

• 在经常需要搜索的列上创建索引，可以加快搜索的速度。

• 在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构。

• 在经常用连接的列上，这些列主要是一些外键，可以加快连接的速度。

• 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的

• 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间

• 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度

• 复合索引一般不超过5个字段

10、索引有哪几种类型？

10.1 普通索引

最基本的索引类型，基于普通字段建立，没有任何限制。

CREATE INDEX <索引名称> ON TABLENAME (字段名);
ALTER TABLE TABLENAME ADD INDEX [索引名称](字段名);
CREATE TABLE TABLENAME ([...],INDEX [索引名称](字段名));

10.2 唯一索引

与普通索引类似，不同点：索引字段的值必须唯一，允许空值。

CREATE UNIQUE INDEX <索引名称> ON TABLENAME (字段名);
ALTER TABLE TABLENAME ADD UNIQUE INDEX [索引名称](字段名);
CREATE TABLE TABLENAME ([...],UNIQUE [索引名称](字段名));

10.3 主键索引

一种特殊的唯一索引，不允许有空值。创建或修改时追加主键约束即可，每个表只能有一个主键。

CREATE TABLE TABLENAME ([...],PRIMARY KEY [索引名称](字段名));
ALTER TABLE TABLENAME ADD PRIMARY KEY [索引名称](字段名);

10.4 复合索引（组合索引）

用户可以在多个列上建立索引，可以代替多个单一索引，所需开销更小。

CREATE INDEX <索引名称> ON TABLENAME (字段名1,字段名2...);
ALTER TABLE TABLENAME ADD INDEX [索引名称](字段名1,字段名2...);
CREATE TABLE TABLENAME ([...],INDEX [索引名称](字段名1,字段名2...));

注意事项：

• 何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。
• 如果表已经建立了（col1,col2）就没有必要再单独建立（col1）；如果现在有（col1）索引，查询又需要col1和col2条件，可以建立（col1,col2）复合索引，对于查询有一定提高。

10.5 全文索引

查询操作在数据量较小时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。

• MySQL5.6以前，近MyISAM存储引擎支持全文检索，
• MySQL5.6之后MyISAM和InnoDB都支持。

CREATE FULLTEXT INDEX <索引名称> ON TABLENAME (字段名);
ALTER TABLE TABLENAME ADD FULLTEXT [索引名称](字段名);
CREATE TABLE TABLENAME ([...],FULLTEXT KEY [索引名称](字段名));

全文索引方式有自然语言检索 IN NATURAL LANGUAGE MODE和布尔检索 IN BOOLEAN MODE两种

和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如

SELECT * FROM user WHERE MATCH(NAME) AGAINST('aabb');

-- * 表示通配符，只能在词的后面
SELECT * FROM user WHERE MATCH(NAME) AGAINST('aa*' IN   BOOLEAN   MODE );

注意事项：

全文索引必须在字符串、文本字段上建立。

全文索引字段必须在最小字符和最大字符之间才会有效。（innodb：3-84；myisam：4-84）

11、什么是索引下推？

简称ICP，Mysql5.6版本推出，用于优化查询。

11.1 引入前

需求：查询user表中“名字是张开头的，年龄为10岁的所有记录”。

SELECT * FROM user WHERE name LIKE "张%" AND age = 10;

• 根据最左前缀原则该语句在搜索索引树的时候，只能匹配到名字开头为张的记录，
• 从该记录开始逐个回表，到主键索引上去找出响应的记录，再对比age这个字段的值是否符合。

11.2 引入后

• MySQL5.6引入索引下推，可以在索引遍历的过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。
• 支持ICP之后，MySQL会在取出索引的同时，判断是否可以进行WHERE条件过滤，再进行索引查询。

12、什么是自适应哈希索引？

12.1 本质

自适应Hash索引简称AHI，是InnoDB的三大特性之一，

另外两大特性是Buffer Pool简称BP、双写缓冲区（Doublewrite Buffer）。

12.2 特性

• 自适应即我们不需要自己处理，当InnoDB引擎根据查询统计发现某一查询满足hash索引的数据结构特点，就会给其建立一个hash索引；

• hash索引底层的数据结构是散列表（Hash表），其数据特点就是比较适合在内存中使用，自适应Hash索引存在于InnoDB架构中（不存在与磁盘架构中），见下面的架构图；

• 自适应hash索引只适合搜索等值的查询，如

  SELECT * FROM TABLE WHERE index_col = 'xxx';
  

  而对于其他查找类型，如范围查找，是不能使用的。

• AHI是针对B+树Serch Path的优化，因此所有会涉及到Serch Path的操作，均可使用此Hash索引进行优化。

• 减少B+树从叶子结点–>根节点定位，可以根据索引的键值，快速定位。

• 根据索引键值（前缀）快速定位到叶子节点满足条件记录的Offset，减少了B+树Search Path的代价，将B+树从Root节点至Leaf节点的路径定位，优化为Hash index的快速查询。

• InnoDB的自适应Hash索引是默认开启的，可以通过配置下面的参数设置进行关闭。

  innodb_adaptive_hash_index = off;
  

• 自适应Hash索引使用分片进行实现的，分片数可以使用配置参数设置。

  innodb_adaptive_hash_index_parts = 8;
  

13、为什么LIKE以"%"开头索引会失效？

13.1 场景：

设置了一个联合索引

13.2 解决%出现在左边索引失效的方法，使用覆盖索引

通过使用索引覆盖type = index，并且extra = Using index，从全表扫描变成了全索引扫描。

13.3 like失效的原因

• %号在右：由于B+树的索引顺序，是按照首字母的大小进行排序，%号在右的匹配又是匹配首字母。所以可以在B+树上进行有序的查找，查找首字母符合要求的数据。
• %号在左：是匹配字符串尾部的数据，尾部的字母是没有顺序的，所以不能按照索引顺序查询，因此索引失效。
• 两个%%号：这个是查询任意位置的字母满足条件即可，只有首字母时进行索引排序的，其他位置的字母都是相对无序的，所以查找任意位置的字母是用不上索引的。

14、InnoDB与MyISAM的区别

14.1 事务和外键

InnoDB：支持事务和外键，具有安全性和完整性，适合大量insert和update操作。

MyISAM：不支持事务和外键，提供告诉存储和检索，适合大量select操作。

14.2 锁机制

InnoDB：支持行级锁，锁定指定记录。基于索引来加锁实现。

MyISAM：支持表级锁，锁定整张表。

14.3 索引结构

InnoDB：使用聚集索引（聚簇索引），索引和记录在一起存储。

MyISAM：使用非聚集索引（非聚簇索引），所以你和记录分开。

14.4 并发处理能力

InnoDB：读写阻塞可以与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发。

MyISAM：使用表锁，会导致写操作并发率低，读之间并不阻塞。

14.5 存储文件

InnoDB：表对应两个文件，一个.frm表结构文件，一个.ibd数据文件。最大支持64TB。

MyISAM：表对应三个文件，一个.frm表结构文件，一个MYD表数据文件，一个.MYI索引文件。从MySQL5.0开始默认限制时256TB。

14.6 InnoDB适用场景

• 需要事务支持（具有较好的事务特性）
• 行级锁定对高并发有很好的适应能力
• 数据更新较为频繁的场景
• 数据一致性要求较高
• 硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO

14.7 MyISAM适用场景

• 不需要事务支持
• 并发相对较低（锁定机制问题）
• 数据修改相对较少，以读为主
• 数据一致性要求不高

15、说一说三个范式？

（1）第一范式：每个列都不可以再拆分；

（2）第二范式：非主键列完全依赖于主键，而不能只依赖主键的一部分；

（3）第三范式：非主键列只依赖于主键，而不依赖于其他非主键。

16、MySQL的事务

16.1 ACID事务的特性

原子性：要么全部成功，要么全部失败。

一致性：数据库总是从一个一致性的状态转移到另一个一致性的状态，不会存在中间状态。

隔离性：一个事务在完全提交之前，对其他事务是不可见的。

持久性：一旦事务提交了，那么就永远都不会改变了。

16.2 并发事务带来了哪些问题？

（1）脏读：一个事务对数据进行了修改，还未提交到数据库；另一个事务使用了未修改的数据，依据这个脏数据所做的操作可能是不正确的。

（2）丢失修改：两个事务同时访问并修改同一个数据，那么第一个事务修改的结果就会被丢失。

（3）不可重复读：在一个事务内多次读取同一数据。在这个事务结束之前，另一个事务进修改了数据，那么第一个事务两次读取的数据就会不一样了。

（4）幻读：发生在一个事务读取了几行数据，接着另一个并发事务插入了一些数据，在随后的查询中，第一个事务就会发现多了一些原本不存在的数据。

16.3 MySQL的四种隔离级别

（1）读未提交

（2）读已提交

（3）可重复度

（4）可串行化

17、MySQL视图

17.1 为什么要使用视图？什么是视图？

• 为了提高复杂SQL语句的复用性和表操作的安全性。
• 一种虚拟表，在物理上是不存在的，其内容与真实的表相似，包含一系列带有名称的列和行数据。
• 视图并不在数据库中以储存的数据值形式存在。行和列数据来自定义视图的查询所引用基本表，并且在具体引用视图时动态生成。

17.2 视图有哪些特点？

• 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
• 视图是由基本表(实表)产生的表(虚表)。
• 视图的建立和删除不影响基本表。
• 对视图内容的更新(添加，删除和修改)直接影响基本表。
• 当视图来自多个基本表时，不允许添加和删除数据。

17.3 视图的使用场景

• 重用SQL语句；
• 简化复杂的SQL操作。在编写查询后，可以方便的重用它而不必知道它的基本查询细节；
• 使用表的组成部分而不是整个表；
• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限；
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

17.4 视图的优缺点

17.4.1 优点：

• 查询简单化。视图能简化用户的操作
• 数据安全性。视图使用户能以多种角度看待同一数据，能够对机密数据提供安全保护
• 逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性

17.4.2 缺点：

• 性能。数据库必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也把它变成一个复杂的结合体，需要花费一定的时间。

• 修改限制。当用户试图修改视图的某些行时，数据库必须把它转化为对基本表的某些行的修改。事实上，当从视图中插入或者删除时，情况也是这样。对于简单视图来说，这是很方便的，但是，对于比较复杂的视图，可能是不可修改的

• 这些视图有如下特征：1.有UNIQUE等集合操作符的视图。2.有GROUP BY子句的视图。3.有诸如AVG\SUM\MAX等聚合函数的视图。 4.使用DISTINCT关键字的视图。5.连接表的视图（其中有些例外）

18、大表数据查询优化思路

1. 优化shema、sql语句+索引；
2. 第二加缓存，memcached, redis；
3. 主从复制，读写分离；
4. 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
5. 水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；
6. 数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于select * from table where age > 20 limit 1000000,10这种查询其实也是有可以优化的余地的. 这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修改为select * from table where id in (select id from table where age > 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以select * from table where id > 1000000 limit 10,效率也是不错的,优化的可能性有许多种,但是核心思想都一样,就是减少load的数据.
7. 从需求的角度减少这种请求…主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.