3-MySQL篇
01- MySQL查询语句的书写顺序
MySQL查询语句的标准书写顺序为：先写Select关键字，可搭配distinct实现结果去重，指定要查询的字段名称；接着通过from子句指定查询的基础表，若需多表关联，可添加join子句并指定join类型，同时通过on关键字设置表之间的连接条件；之后用where子句设置行级过滤条件；再通过group by子句按指定字段对数据分组；分组后可通过having子句过滤分组结果；随后用order by子句按指定字段对结果排序；最后通过limit子句指定查询结果的起始偏移量和返回行数。
完整书写结构参考：Select [distinct] <字段名称> from 表1 [ join 表2 on ] where group by <字段> having order by <排序字段> limit <起始偏移量,行数>。
02- MySQL查询语句的执行顺序
MySQL查询语句的执行顺序与书写顺序不同，核心执行步骤按序号依次为：
- 执行from子句，确定查询的基础表（表1、表2等）；
- 执行on子句，筛选表之间的连接条件；
- 执行join子句，根据on条件合并多张表的数据；
- 执行where子句，过滤连接后的数据行；
- 执行group by子句，按指定字段对数据分组；
- 执行having子句，过滤分组后的结果集；
- 执行聚合函数（如count、sum等），计算分组统计结果；
- 执行Select子句，提取指定的字段或计算结果；
- 执行distinct关键字，对查询结果去重；
- 执行order by子句，对结果集按指定字段排序；
- 执行limit子句，截取指定偏移量和行数的结果。
简言之，执行逻辑可总结为“先找表→连表→筛行→分组→筛组→算聚合→选字段→去重→排序→截结果”。
03- MySQL 如何实现多表查询
MySQL主要通过连接查询实现多表查询，核心连接方式及用法如下：
- 内连接：仅返回多张表中匹配连接条件的数据，分为两种写法：
- 隐式内连接：无需显式写join关键字，通过from后多表逗号分隔+where连接条件实现，格式为Select 字段 From 表A , 表B where 连接条件；
- 显式内连接：通过inner join关键字显式声明，格式为Select 字段 From 表A inner join 表B on 连接条件（inner可省略）；
- 外连接：返回一张表的全部数据，以及另一张表匹配连接条件的数据，分为：
- 左外连接：以左表为驱动表，返回左表全部数据，右表仅返回匹配条件的数据，不匹配的字段以NULL填充，格式为Select 字段 From 表A left join 表B on 连接条件；
- 右外连接：以右表为驱动表，返回右表全部数据，左表仅返回匹配条件的数据，不匹配的字段以NULL填充，格式为Select 字段 From 表A right join 表B on 连接条件；
- 全外连接：返回所有表中匹配和不匹配的全部数据，MySQL原生不直接支持，需通过左外连接+右外连接+union实现，实际开发中极少使用；
- 交叉连接：无连接条件，直接返回多张表的笛卡尔积（结果行数=表A行数×表B行数），无实际业务筛选意义，极少使用。
04- MySQL内连接和外连接的区别？
内连接与外连接的核心区别在于结果集的覆盖范围：
- 内连接：仅保留多张表中匹配连接条件的数据行，不匹配的行全部过滤，结果集只包含多张表的“交集”数据；
- 外连接：
- 左外连接：左表（驱动表）的所有数据行都会保留，右表仅返回匹配连接条件的行，不匹配的行对应字段以NULL填充；
- 右外连接：右表（驱动表）的所有数据行都会保留，左表仅返回匹配连接条件的行，不匹配的行对应字段以NULL填充；
- 全外连接：保留所有表的所有数据行，匹配的行正常显示，不匹配的行对应字段以NULL填充；
- 交叉连接：无连接条件，直接返回多张表的笛卡尔积，结果行数是各表行数的乘积，无实际业务价值。
05- CHAR和VARCHAR的区别？
CHAR和VARCHAR均为MySQL字符串类型，核心区别体现在长度特性、性能、存储方式上：
- 长度特性：CHAR是定长字符串，定义时需指定固定长度（如CHAR(10)），即使存储的字符不足该长度，也会用空格填充至指定长度；VARCHAR是变长字符串，定义时指定最大长度（如VARCHAR(10)），实际存储长度随字符数变化，无需填充空格；
- 性能：CHAR的存取速度远快于VARCHAR，因为定长存储减少了数据长度计算和内存分配的开销；
- 存储方式（以MySQL默认字符集utf8为例）：CHAR对英文字符、汉字均按实际字符数占用字节（utf8下1个英文字符占1字节，1个汉字占3字节）；VARCHAR除存储字符本身外，还会额外占用1-2字节记录字符串长度（长度≤255占1字节，>255占2字节），字符本身的存储规则与CHAR一致。
06- 了解过MySQL的索引嘛？
MySQL索引是提升查询效率的核心机制，按应用场景主要分为三类：单列索引、组合索引、空间索引（空间索引仅用于地理空间数据，实际开发极少使用），常用的是单列索引和组合索引：
- 单列索引：在表的单个字段上创建的索引，又细分为：
- 普通索引：最基础的索引类型，无任何限制，允许索引列存储重复值、空值，仅用于提升查询速度；
- 唯一索引：索引列的值必须唯一，允许存储空值（这是与主键索引的核心区别）；
- 主键索引：特殊的唯一索引，不允许存储空值，一张表仅能有一个主键索引，创建主键时会自动生成；
- 全文索引：仅支持MyISAM引擎（MySQL5.6前）、InnoDB引擎（MySQL5.6后），且仅能在CHAR、VARCHAR、TEXT类型字段上创建，用于全文检索（如文章内容关键词查询）；
- 组合索引：在表的多个字段组合上创建的索引（也叫联合索引），使用时需遵循“左前缀原则”，通常建议用组合索引替代多个单列索引，减少索引维护开销。
07- 索引的底层数据结构了解过嘛？
MySQL索引的底层数据结构由存储引擎决定，不同引擎支持的索引结构不同：
- MyISAM、InnoDB存储引擎：仅支持B+树索引，默认使用B+树作为索引底层结构，且无法更换；B+树的特点是叶子节点存储全部索引数据，且叶子节点间通过链表连接，适合范围查询和排序；
- MEMORY/HEAP存储引擎：支持两种索引结构——HASH索引和B+树索引，HASH索引查询等值条件效率极高，但不支持范围查询；B+树索引兼容范围查询，灵活性更高。
08- MySQL支持的存储引擎有哪些，有什么区别？
MySQL支持多种存储引擎（如MyISAM、InnoDB、MEMORY、CSV等），实际开发中最常用的是MyISAM和InnoDB，核心区别如下：
- 数据存储上限：MyISAM支持最大256TB的数据存储，InnoDB仅支持最大64TB的数据存储；
- 事务支持：MyISAM不支持事务，执行增删改操作时无原子性、一致性保障；InnoDB支持ACID事务，是业务系统的首选引擎；
- 外键支持：MyISAM不支持外键约束，InnoDB支持外键约束，可保证数据的参照完整性；
- 锁机制：MyISAM仅支持表级锁，查询效率高但写操作阻塞严重；InnoDB支持行级锁，并发读写性能更优；
- 行数统计：MyISAM保存表的总行数（count(*)无需扫描全表），InnoDB需扫描全表统计行数。
09- 什么是聚簇索引什么是非聚簇索引？
聚簇索引和非聚簇索引的核心区别是“索引与数据是否物理存储在一起”，与存储引擎强相关：
- 聚簇索引：仅InnoDB引擎支持，主键索引的B+树叶子节点直接存储整行数据（数据和索引“聚簇”在一起），InnoDB中一张表仅有一个聚簇索引（通常是主键索引，无主键时会自动生成隐式主键）；
- 非聚簇索引：MyISAM引擎的默认索引方式，索引的B+树叶子节点仅存储数据行的物理地址（指针），数据和索引分开存储，查询时需先通过索引找到指针，再根据指针读取数据。
10- 在一个非主键字段上创建了索引，想要根据该字段查询到数据，需要查询几次？
需要查询两次（即“回表查询”），不同存储引擎的查询流程略有差异：
- MyISAM存储引擎：第一次查询——通过非主键索引的B+树找到数据行的物理指针；第二次查询——根据指针到数据文件中读取整行数据；
- InnoDB存储引擎：第一次查询——通过非主键索引的B+树找到对应的主键ID；第二次查询——根据主键ID到聚簇索引（主键索引）的B+树中读取整行数据。
11- 知道什么是回表查询嘛？
回表查询是InnoDB引擎下非主键索引查询的典型场景，核心是“先查索引找主键，再查主键找数据”，性能低于直接的索引扫描：
举例：为user表的name字段创建非主键索引，执行查询语句select name,age from user where name='Alice'：
- 第一步：通过name索引的B+树，定位到name='Alice'对应的主键ID（如id=18）；
- 第二步：根据主键ID=18，到主键索引（聚簇索引）的B+树中读取整行数据（包含age字段）；
这种“先找主键、再找数据”的二次查询过程就是回表查询，会增加IO开销，降低查询效率。
12- 知道什么叫覆盖索引嘛？
覆盖索引是优化回表查询的核心手段，指“查询所需的所有字段都包含在索引中”，无需回表即可获取全部数据，查询效率极高：
实现覆盖索引的核心方法是将查询字段纳入组合索引中：
举例：执行select name,age from user where name='Alice'，若仅为name创建单列索引，会触发回表；若创建组合索引create index index_name_age on user(name,age)，则name索引的B+树叶子节点会同时存储name和age字段的值；执行查询时，只需扫描该组合索引的B+树，就能直接获取name='Alice'对应的age值，无需回表，这就是覆盖索引。
13- 知道什么是左前缀原则嘛？
左前缀原则是MySQL组合索引的核心使用规则，指“组合索引的匹配从最左侧字段开始，依次向右匹配，若最左侧字段未出现在查询条件中，索引则完全失效”：
举例：为tb_user表创建组合索引index_age_name_sex(age,name,sex)，该索引实际等价于创建了三个索引：(age)、(age,name)、(age,name,sex)；
查询时：
- 条件包含age（如where age=20）：能匹配(age)索引，索引生效；
- 条件包含age+name（如where age=20 and name='张三'）：能匹配(age,name)索引，索引生效；
- 条件包含age+name+sex（如where age=20 and name='张三' and sex='男'）：能匹配完整组合索引，索引生效；
- 条件仅包含name（如where name='张三'）：最左侧的age字段未出现，索引完全失效，触发全表扫描。
14- 什么情况下索引会失效？
MySQL索引失效会导致查询从“索引扫描”退化为“全表扫描”，核心失效场景如下：
- 索引列使用函数/运算：如where DATE(create_time)='2025-01-01'、where age+1=20，会直接导致索引失效；
- 使用否定操作符：如where age!=20、where name not in ('张三','李四')、where sex<>'男'，大概率触发全表扫描；
- 使用or连接条件：如where age=20 or name='张三'，若其中一个条件的字段无索引，整个索引失效；
- 多个单列索引替代组合索引：多张单列索引无法形成覆盖索引，易触发回表，且MySQL优化器通常仅选择最优的一个单列索引；
- 范围查询导致右侧字段索引失效：组合索引(age,name,sex)中，若age用范围查询（如where age>20 and name='张三'），则name、sex字段的索引失效；
- 索引列包含NULL值：索引不会存储NULL值，where age is null会导致索引失效；
- 隐式类型转换：如索引列是int类型，查询条件用字符串（where age='20'），会触发隐式转换，索引失效；
- like模糊查询：like '%张三'、like '%张三%'会导致索引失效，仅like '张三%'（前缀匹配）能使用索引。
15- 索引是越多越好嘛？什么样的字段需要建索引，什么样的字段不需要？
索引并非越多越好，过多索引会增加增删改操作的开销（每次修改数据需同步维护索引），需按需创建：
（1）需要创建索引的场景
- 主键字段：创建主键时自动生成主键索引，无需手动创建；
- 频繁作为查询条件的字段：如用户表的phone字段、订单表的order_no字段；
- 多表关联查询的关联字段：如order表的user_id（关联user表），需在关联字段两侧都创建索引；
- 查询中排序的字段：如order by create_time，创建create_time索引可避免排序开销；
- 频繁查找的字段：如商品表的sku字段，高频查询需建索引；
- 查询中统计/分组的字段：如group by category_id，创建category_id索引提升分组效率。
（2）不需要创建索引的场景
- 表记录极少（如少于1000行）：全表扫描效率高于索引扫描；
- 频繁增删改的表：索引会增加写操作的IO开销，如日志表；
- 频繁更新的字段：每次更新需同步维护索引，性价比低；
- where条件中使用频率极低的字段：如用户表的remark（备注）字段，极少作为查询条件，无需建索引。
16- MySQL的性能优化
MySQL性能优化需从设计、功能、架构三个维度入手，核心优化方向如下：
（1）设计维度
- 选择合适的存储引擎：无需事务、外键，且读写高频的场景用MyISAM；需事务、外键，或并发读写的场景用InnoDB；
- 选择合适的字段类型：定长字符串用CHAR，不定长用VARCHAR；状态、性别等有限枚举值用TINYINT（节省空间）；
- 遵循数据库范式（或适度反范式）：
- 第一范式（1NF）：保证字段原子性（不可再分）；
- 第二范式（2NF）：消除部分依赖（非主键字段需完全依赖主键）；
- 第三范式（3NF）：消除传递依赖（非主键字段不依赖其他非主键字段）；
高并发场景可适度反范式（如冗余字段），减少关联查询。
（2）功能维度
- 索引优化：创建合理的组合索引、覆盖索引，避免索引失效场景；
- 缓存缓解压力：用Redis等缓存中间件存储高频查询结果，减少数据库访问；
- 分库分表：数据量过大时，按业务维度分库（如用户库、订单库），按数据维度分表（如订单表按时间分表）。
（3）架构维度
- 主从复制：搭建主库（写）+从库（读）架构，同步数据；
- 读写分离：应用层将读请求路由到从库，写请求路由到主库，分散数据库压力；
- 负载均衡：通过LVS、Nginx等实现数据库集群的负载均衡，提升并发处理能力。
17- MySQL超大分页怎么处理？
MySQL默认分页（limit offset, N）的核心缺陷：并非跳过offset行，而是先读取offset+N行，再丢弃前offset行，仅返回N行；当offset值极大时（如limit 100000, 10），读取数据量过大，分页效率极低。
优化方案：先通过索引快速定位目标数据的主键ID，再关联查询获取完整数据，避免全表扫描：
举例：原低效分页语句（limit 100000, 10）：
select id, name, age from user order by id limit 100000, 10;
优化后：
select u.id, u.name, u.age
from user u
join (select id from user order by id limit 100000, 10) t on u.id = t.id;
核心逻辑：子查询仅通过主键索引获取10个目标ID（效率极高），再关联主表获取数据，大幅减少数据读取量。
18- 如何定位慢查询？
MySQL可通过开启慢查询日志定位执行耗时过长的SQL，主要有两种开启方式：
（1）修改配置文件（永久生效，需重启MySQL）
编辑my.ini（Windows）/my.cnf（Linux）配置文件，添加如下配置：
[mysqld]
log_output='FILE,TABLE' # 慢查询日志输出到文件和mysql.slow_log表
slow_query_log='ON' # 开启慢查询日志
long_query_time=0.001 # 定义慢查询阈值（单位：秒），超过该时间的SQL会被记录
（2）设置全局变量（临时生效，无需重启）
通过SQL命令动态设置：
SET GLOBAL slow_query_log = 'ON'; # 开启慢查询
SET GLOBAL log_output = 'FILE,TABLE'; # 日志输出方式（文件+表）
SET GLOBAL long_query_time = 0.001; # 慢查询阈值（单位：秒）
19- 一个SQL语句执行很慢，如何分析？
分析慢SQL的核心步骤如下：
- 定位慢SQL：开启慢查询日志，从日志文件（或mysql.slow_log表）中提取执行耗时过长的SQL语句；
- 分析执行计划：使用EXPLAIN命令（正确拼写，非EXLPAIN）分析SQL的执行过程，重点关注以下核心字段：
- select_type：表示查询类型（如SIMPLE（简单查询）、DERIVED（派生表查询）、SUBQUERY（子查询）等），不同类型的查询性能差异较大；
- type：表示MySQL查找数据的方式，性能从优到劣依次为：system > const > eq_ref > ref > range > index > ALL，若type为ALL（全表扫描），说明索引未生效，需优先优化；
- Extra：表示额外执行信息，若出现Using filesort（文件排序）、Using temporary（临时表）、Using join buffer（连接缓冲区）等，均为性能瓶颈，需针对性优化。
总结
- MySQL查询核心：书写顺序≠执行顺序，执行逻辑是“先找表→连表→筛行→分组→筛组→算聚合→选字段→去重→排序→截结果”；
- 索引优化是查询性能的核心：需遵循左前缀原则，用覆盖索引减少回表，规避索引失效场景，且索引并非越多越好；
- 慢SQL分析：先通过慢查询日志定位，再用EXPLAIN分析执行计划，重点关注type（查询方式）、Extra（额外开销）字段；
- 性能优化维度：设计层选对引擎/字段类型，功能层优化索引/缓存，架构层做主从复制/读写分离。
