目录
1、Select 语句完整的执行顺序 难度系数：⭐
查询中用到的关键词主要包含如下展示，并且他们的顺序依次为
form...on...leftjoin...where...group by...avg()/sum()...having..select... order by...asc/desc...limit...
from: 需要从哪个数据表检索数据
where: 过滤表中数据的条件
group by: 如何将上面过滤出的数据分组算结果
order by : 按照什么样的顺序来查看返回的数据
2、MySQL事务 难度系数：⭐⭐
- 原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。
- 一致性：事务的执行使得数据库从一种正确状态转换成另一种正确状态
- 隔离性：在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，
- 持久性：事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，
- 事务的处理结果也会得到保存。
MySQL事务隔离级别
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
| 读未提交（read-uncommitted) | 是 | 是 | 是 |
| 读已提交（read-committed) | 否 | 是 | 是 |
| 可重复读（repeatable0read) | 否 | 否 | 是 |
| 可串行读（serializable) | 否 | 否 | 否 |
3、MyISAM和InnoDB的区别 难度系数：⭐
1.MyISAM 存储引擎
主要特点：
MySQL5.5 版本之前的默认存储引擎，支持表级锁（表级锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁）；不支持事务，外键。
适用场景：对事务的完整性没有要求，或以 select、insert 为主的应用基本都可以选用
MYISAM。在 Web、数据仓库中应用广泛。
特点：
1、不支持事务、外键
2、每个 myisam 在磁盘上存储为 3 个文件，文件名和表名相同，扩展名分别是
.frm-------存储表定义
.MYD --------MYData，存储数据
.MYI--------MYIndex，存储索引
2.InnoDB 存储引擎
主要特点：
MySQL5.5 版本之后的默认存储引擎；支持事务；
支持行级锁（行级锁是 Mysql 中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁）；
支持聚集索引方式存储数据
4、悲观锁和乐观锁的怎么实现 难度系数：⭐⭐
悲观锁优化
-
确保使用索引：
如之前所述，使用SELECT ... FOR UPDATE时，应确保查询条件使用了索引，以避免锁定过多的行或整个表。 -
减少锁定时间：
尽量让事务保持简短，减少持有锁的时间，这样可以减少锁冲突的可能性。 -
使用更低级别的隔离级别：
根据业务需求，考虑使用如READ COMMITTED这样的较低隔离级别，而不是默认的REPEATABLE READ。较低隔离级别可以减少锁定的范围和时间。 -
分批处理：
如果需要锁定大量数据，考虑使用分批处理的方式，每次只锁定和处理一部分数据。
乐观锁优化
-
合理设计版本号字段：
版本号字段应该是一个足够大的整数类型，以支持大量的更新操作。 -
避免并发更新同一行：
如果业务逻辑允许，尝试避免多个事务并发更新同一行数据，以减少版本冲突的可能性。 -
减少数据库往返次数：
在更新数据前，尽量通过业务逻辑判断是否需要更新，减少不必要的数据库查询和更新操作。 -
处理版本冲突：
当检测到版本冲突时，应有合理的重试机制或用户提示，指导用户如何处理冲突。
通用优化
-
监控和日志：
监控数据库锁的情况，记录锁等待和冲突事件，以便及时发现和解决性能问题。 -
使用连接池：
使用数据库连接池可以减少建立和关闭连接的开销，提高系统性能。 -
优化SQL语句：
对SQL语句进行优化，减少全表扫描，提高查询效率。 -
硬件和配置优化：
根据数据库服务器的硬件资源，合理配置数据库参数，如内存、缓存等，以充分发挥硬件性能。
针对乐观锁优化的示例
在更新商品库存时，你可以使用更高级的乐观锁策略，比如结合业务逻辑来减少版本冲突的可能性：
-
业务逻辑判断：
在尝试更新库存前，先检查库存是否足够。如果不足，则直接返回错误信息，避免不必要的数据库操作。 -
重试机制：
如果检测到版本冲突，可以设计一个重试机制。例如，等待一段时间后再次尝试更新，或者提示用户稍后重试。 -
批量更新：
如果有多个商品需要同时更新库存，可以考虑使用批量更新的方式，减少数据库操作的次数。
5、聚簇索引与非聚簇索引区别 难度系数：⭐⭐
都是B+树的数据结构
聚簇索引:将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即:只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的
非聚簇索引叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置再取磁盘查找数据，这个就有点类似一本书的目录，比如我们要找第三章第一节，那我们先在这个目录里面找，找到对应的页码后再去对应的页码看文章。
优势:
1、查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率要高
2、聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的
3、聚簇索引适合用在排序的场合，非聚簇索引不适合
劣势;
1、维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(pagesplit)的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZETABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片
2、表因为使用uuId(随机ID)作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，所以建议使用int的auto_increment作为主键
3、如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值，过长的主键值，会导致非叶子节点占用占用更多的物理空间
6、什么情况下mysql会索引失效 难度系数：⭐
1. 使用了函数或表达式
当你在 WHERE 子句中对索引列使用函数或表达式时，MySQL 通常无法使用该索引。
SELECT * FROM users WHERE YEAR(created_at) = 2023;在这个例子中，created_at 字段可能有一个索引，但是由于 YEAR() 函数的使用，该索引不会被使用。
2. 隐式类型转换
当比较不同类型的值时，MySQL 可能需要进行隐式类型转换，这可能导致索引失效。
SELECT * FROM users WHERE phone_number = '1234567890';如果 phone_number 列是整数类型，但查询中使用的是字符串，MySQL 可能需要进行类型转换，从而不使用索引。
3. 使用了不等于 (!= 或 <>)
对于某些数据库和配置，使用不等于操作符可能导致索引失效。
SELECT * FROM users WHERE id != 100;在某些情况下，MySQL 优化器可能决定全表扫描比使用索引更高效。
4. LIKE 查询以通配符开头
当使用 LIKE 查询且通配符 % 在模式字符串的开头时，索引通常不会被使用。
SELECT * FROM users WHERE name LIKE '%Smith';这种情况下，MySQL 通常需要进行全表扫描。
5. OR 条件
在某些情况下，使用 OR 连接的条件可能导致索引失效，尤其是当 OR 的两侧都能使用不同的索引时。
SELECT * FROM users WHERE id = 1 OR email = 'example@example.com';如果 id 和 email 都有索引，但查询优化器决定使用全表扫描比单独使用任一索引或合并使用两者更有效时，索引会失效。
6. 数据分布问题
如果表中数据分布不均匀，可能导致 MySQL 优化器决定不使用索引，因为全表扫描可能更快。
7. 索引选择性低
当索引的选择性很低（即索引的唯一值与总行数之比很小）时，MySQL 优化器可能会认为全表扫描更高效。
8. 使用了复合索引，但查询条件没有用到最左前缀
对于复合索引，查询条件必须使用索引的最左前缀，否则索引可能不会被使用。
-- 假设有一个复合索引 (first_name, last_name)
SELECT * FROM users WHERE last_name = 'Smith';在这个例子中，只使用了 last_name，没有用到复合索引的最左前缀 first_name，因此索引可能不会被使用。
9. 强制使用索引
尽管这不是索引失效的情况，但了解如何强制 MySQL 使用索引也是很有用的。你可以使用 FORCE INDEX 提示来强制 MySQL 使用特定的索引。
SELECT * FROM users FORCE INDEX (idx_name) WHERE name = 'John';在这个例子中，即使优化器认为不使用 idx_name 索引可能更高效，MySQL 也会强制使用它。
为了避免索引失效，你应该：
- 仔细选择和设计索引。
- 监控查询执行计划，了解何时索引被使用或失效。
- 避免在 WHERE 子句中对索引列使用函数或表达式。
- 尽可能避免使用
LIKE查询以通配符开头。 - 在使用 OR 条件时，考虑是否可以重构查询或添加更多的索引来优化性能。
- 定期检查数据分布和索引选择性，确保索引仍然有效。
7、B+tree 与 B-tree区别 难度系数：⭐⭐
索引是在 Mysql 的存储引擎(InnoDB,MyISAM)层中实现的, 而不是在服务层实现
的. 所以每种存储引擎的索引都不一定完全相同, 也不是所有的存储引擎都支持所有的索引
类型的, Mysql 目前提供了以下 4 种索引:
B+Tree 索引: 最常见的索引类型, 大部分索引都支持 B+树索引. Hash 索引: 只有 Memory 引擎支持, 使用场景简单. R-Tree 索引(空间索引): 空间索引是 MyISAM 引擎的一个特殊索引类型, 主要地理
空间数据, 使用也很少. S-Full-text(全文索引): 全文索引也是 MyISAM 的一个特殊索引类型, 主要用于全
文索引, InnoDB 从 Mysql5.6 版本开始支持全文索引.
- 树中每个节点最多包含 m 个孩子.
- 除根节点与叶子节点外, 每个节点至少有[ceil(m/2)] 个孩子(ceil 函数指向上取整).
- 若根节点不是叶子节点, 则至少有两个孩子.
每个非叶子节点由 n 个 Key 和 n+1 个指针组成, 其中 [ceil(m/2) -1 ] <= n <= m-1. 以 5 叉 BTree 为例, key 的数量: 公式推导 [ceil(m/2) -1 ] <= n <= m-1. 所以 2 <= n <= 4, 中间节点分裂父节点,两边节点分裂.
3.B+Tree 结构
8、数据库分表操作 难度系数：⭐
水平分表
步长法：1000万一张表拆分
取模法：举例：根据用户id取模落入不能的表
垂直分表：大表拆小表。商品信息 spu_info spu_image ...
9、MySQL优化 难度系数：⭐
（1）尽量选择较小的列
（2）将where中用的比较频繁的字段建立索引
（3）select子句中避免使用‘*’
（4）避免在索引列上使用计算、not in 和<>等操作
（5）当只需要一行数据的时候使用limit 1
（6）保证单表数据不超过200W，适时分割表。针对查询较慢的语句，可以使用explain 来分析该语句具体的执行情况。
（7）避免改变索引列的类型。
（8）选择最有效的表名顺序，from字句中写在最后的表是基础表，将被最先处理，在from子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。
（9）避免在索引列上面进行计算。
（10）尽量缩小子查询的结果
10、SQL语句优化案例 难度系数：⭐
- 索引的设计需要遵循一些已有的原则, 这样便于提升索引的使用效率, 更高效的使用索引.
- 对查询频次较高, 且数据量比较大的表, 建立索引.
- 索引字段的选择, 最佳候选列应当从 where 子句的条件中提取, 如果 where 子句中的组合
- 比较多, 那么应当挑选最常用, 过滤效果最好的列的组合.
- 使用唯一索引, 区分度越高, 使用索引的效率越高.
- 索引并非越多越好, 如果该表赠,删,改操作较多, 慎重选择建立索引, 过多索引会降低表维 护效率.
- 使用短索引, 提高索引访问时的 I/O 效率, 因此也相应提升了 Mysql 查询效率.
- 如果 where 后有多个条件经常被用到, 建议建立符合 索引, 复合索引需要遵循最左前缀法 则, N 个列组合而成的复合索引, 相当于创建了 N 个索引. 复合索引命名规则 index_表名_列名 1_列名 2_列明 3
1478
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
