Java每日面试题(mysql优化)(day14)

连接查询

MySQL常用函数汇总

SQL Select 语句的执行顺序

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

• 聚集索引：包含主键和非主键数据以及索引
• 非聚集索引：只包含主键和索引，当没有实现索引覆盖时会进行回表，走聚集索引

数据库三范式

• 第一范式
  1NF 原子性，列或者字段不能再分，要求属性具有原子性，不可再分解；

• 第二范式
  2NF 唯一标识。即每个表只描述一种实体，每个记录都有唯一标识，不存在部分依赖关系。 主要是解决行的冗余。

  1. 每个表必须有一个主键
  2. 非主键字段要完全依赖于主键

• 第三范式
  3NF 直接性，非主键字段不依赖于其它非主键字段， 主要是解决列的冗余.

MyISAM 存储引擎 与 InnoDB 引擎区别

1. 事务支持：MyISAM 不支持事务处理，而 InnoDB 支持事务处理，可以通过使用事务来确保数据的完整性和一致性。

2. 锁定机制（锁的粒度）：MyISAM 表级锁在执行 SELECT 操作时会对 表 进行读锁定，而执行 INSERT、UPDATE 或 DELETE 操作时会对 表 进行写锁定，因此在写操作执行时，读操作会被阻塞。而 InnoDB 支持行级锁，不会对整个表进行锁定，可以减少锁定冲突和死锁的发生。

3. 外键支持：MyISAM 不支持外键约束，而 InnoDB 支持外键约束，可以通过外键约 束来保证数据的引用完整性

4. 并发性能：在并发性能方面，InnoDB 要优于 MyISAM。由于 InnoDB 支持行级锁定 和 事务处理，因此在高并发情况下，InnoDB 的并发性能更高。

因此，在设计数据库时，需要考虑具体情况选择适合的 存储引擎。

• 如果需要支持事务处理 和 外键约束，以及具有更好的并发性能，则应选择 InnoDB。
• 如果只是进行简单的读写操作，并且需要更快的查询速度，则可以选择 MyISAM。

索引

索引存储在 内存 中，为服务器存储引擎为了快速找到记录的一种数据结构。

索引的主要作用是 加快数据查找速度，提高数据库的性能。 空间换时间

索引的优缺点

优点：加快查询效率

1. 唯一性索引：保证数据库表中每一行数据的唯一性

2. 加速查询：减少数据扫描的行数，从而提高查询速度。

3. 加速排序和分组：索引可以帮助优化 ORDER BY 和 GROUP BY 操作。

缺点：

1. 占用空间：索引会增加磁盘空间的使用。
2. 影响写性能：插入、删除、更新操作需要维护索引，可能会导致性能下降。

索引的分类

1. 普通索引：加速数据的检索，允许重复值。

2. 唯一索引：确保列中的所有值唯一，加速查询。但允许有空值。

3. 主键索引：特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值。

4. 联合索引（又叫复合索引）：由多个列组成的索引，提升多列查询的效率。遵循靠左原则。

   最左前缀匹配原则：即在使用复合索引时，查询条件必须包含索引中最左边的列才能触发索引的使用。如果查询条件没有包含最左边的列，则索引无法生效。

   使用场景：

   1. 登录时，手机号+密码
   2. 商品表中，类型+状态
   3. 订单表中，订单号+用户Id

5. 全文索引：用于快速全文搜索。不过一般使用ElasticSearch做全文索引（搜索引擎库）。

索引结构

Mysql 目前提供了以下 4 种索引:

1. B+Tree 索引: 最常见的索引类型, 大部分索引都支持 B+树索引.
2. Hash 索引: 只有 Memory 引擎支持, 使用场景简单.
3. R-Tree 索引(空间索引): 空间索引是 MyISAM 引擎的一个特殊索引类型, 主要地理空间数据
4. S-Full-text(全文索引): 主要用于全文索引, InnoDB 从 Mysql5.6 版本开始支持全文索引.
   

B树与B+树的区别

1. 存储数据的位置:
   • B树: 数据既存储在所有节点中（叶子节点和非叶子节点都有数据）
   • B+树: 所有的数据记录都存储在叶子节点中，非叶子节点仅包含索引信息。叶子节点包含了完整的数据和索引键。
2. 叶子节点之间的链接:
   • B树: 叶子节点之间没有链接。
   • B+树: 叶子节点之间通过指针相互链接，形成一个 链表 或 循环链表，这使得范围查询和遍历变得高效。
     

补充：动画演示数据结构

索引失效的几种情况

1. 范围条件查询
   当查询到的记录大于总记录数30%时，就不再使用索引，直接会扫描全表

2. 索引列上操作（使用函数、计算等）导致索引失效

3. 字符串不加引号, 造成索引失效

4. OR 关键字连接：OR关键字两边的字段必须都要有索引，任一个字段没索引就会进行全表扫描

5. 使用 != 导致索引失效

6. like以通配符开头('%abc...')导致索引失效

7. 排序列包含不同索引的列

数据库锁

1. 行锁和表锁
   主要是针对锁粒度划分的，一般分为：行锁、表锁、库锁

   • 行锁：访问数据库的时候，锁定整个行数据， 防止并发错误。

   • 表锁：访问数据库的时候，锁定整个表数据，防止并发错误。

   行锁 和 表锁 的区别：

   • 行锁：
     开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高
   • 表锁：
     开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低

2. 悲观锁和乐观锁

   • 悲观锁：每次去拿数据的时候都认为会进行修改，所有每次在拿数据的时候都会上锁.
   • 乐观锁：每次去拿数据的时候都认为不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间有没有更新这个数据，使用版本号机制，如果版本号不支持舍弃这次操作，并且设置 重试机制，乐观锁适用于多读的应用类型 ，这样可以提高吞吐量 。

MySql 优化

SQL慢查询优化

1. 定位执行效率慢的 sql 语句
   • EXPLAIN：可以帮助分析查询计划，查看索引是否被使用。
   • 慢查询日志：开启 MySQL 的慢查询日志，分析执行时间较长的查询，优化相关索引。

• 拓展：
  • 使用xxl-job查询慢日志
  • 通过elk监控自己项目的日志
  • 使用skyWalking查看哪个接口比较慢

2. Sql 语句优化

   • 多表连接的字段上需要建立索引，这样可以极大提高表连接的效率.

   • 避免在索引列上使用计算

   • 避免在索引列上使用IS NULL和IS NOT NULL

   • 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上 建立索引。

   • 应尽量避免在 where 子句中对字段进行表达式操作和 null 值判断，这将导致引擎放弃使用索引而进行全表扫描

   • 排序时，尽量同时用升序或同时用降序.

   • 分组时默认会进行排序，额外的排序会降低效率. 如果不需要排序可以禁止, 使用 order by null 禁用默认排序.

   • 尽量避免子查询, 可以将子查询优化为 join 多表连接查询.

   • 避免使用SELECT *

   • 列表数据不要拿全表，要使用LIMIT来分页，每页数量也不要太大，当只要一行数据时使用LIMIT 1

索引优化

• 对查询频繁, 且数据量比较大的表, 建立索引.

• 遵循最左前缀匹配原则，合理设计复合索引。

  复合索引命名规则： index _ 表名 _ 列名 1 _ 列名 2 _ 列名 3

• 使用覆盖索引，减少回表查询。

• 定期检查并删除冗余或重复的索引。

• 利用唯一索引提高查询效率, 区分度越高, 使用索引的效率越高.

• 使用短索引, 提高索引访问时的 I/O 效率, 从而提升 Mysql 查询效率.

表优化

• 保留冗余字段：避免表之间的连接过于频繁

• 增加派生列。派生列是由表中的其它多个列的计算所得，增加派生列可以减少统计运算 这也就是反第三范式。

• 分割表：垂直拆分和水平拆分。

  • 水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。
  • 垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。

• 字段设计

  1. 表的字段尽可能用 NOT NULL

  2. 字段长度固定的表查询会更快

  3. 把数据库的大表按时间或一些标志分成小表

相关文章：数据库事务相关面试题