java八股文-mysql

1. 索引

1.1 什么是索引

索引(index)是帮助Mysql高效获取数据的数据结构(有序).
提高数据的检索效率,降低数据库的IO成本(不需要全表扫描).
通过索引列对数据进行排序,降低数据排序成本,降低了CPU的消耗.

1.2 mysql索引使用的B+树?

1. 没有使用二叉树，最坏情况o（n），红黑树，的话数据量大了，层级太多

2. b+ 树的话每个节点不是只有一个节点，多路的

3. b树的话每个节点都指向数据，b+只有叶子结点

4. 这样b+更优秀，因为不会加载路径上的数据了

1.3 mysql的常用存储引擎?

1.3.1 比较

1.3.2 选择建议

事务需求：
- 需要事务：选择 InnoDB。
- 不需要事务：可选择 MyISAM、Memory 等。
数据访问模式：
- 读写并发：选择 InnoDB。
- 只读或读多写少：可选择 MyISAM。
持久化需求：
- 持久化：InnoDB、MyISAM。
- 非持久化：Memory。
数据规模和性能：
- 数据量大，索引优化重要：选择 InnoDB。
- 极高性能，数据可以丢失：选择 Memory。

1.3.3 总结

MySQL 提供了多种存储引擎以满足不同的需求。
InnoDB 是大多数场景下的首选，因其支持事务、高并发和外键。
其他存储引擎（如 MyISAM、Memory）则适用于特定的场景。

1.4 什么事聚集索引和非聚集索引（二级索引）？

简单说聚簇索引，只能有一个，表示物理排序了，聚族索引的叶子结点保存的是整条数据，非聚簇索引保存的是id。（这样使用聚集索引查询非索引字段也不用回表查询了）

1.5 回表查询?

简单说如图查询条件是有索引的，但是查询到后，他并没有全量数据，只能拿到主键id，然后在去聚族索引，所以还要回表查询。

1.6 覆盖索引?

如图，覆盖索引就是查询能返回，所需要到列。第三个sql中gender一次得不到的

1.7 mysql超大分页如何处理?

1.7.1 覆盖索引解决

1.7.2.

select id from user where id>10000 limit10 取出多少后的再去获取，有序的得

1.7.3.

业务层面说最好限制下，查询到一百页

1.8 索引创建的原则

真的数据量的的,查询频繁的表建立索引.(单表超过十万条)
针对常作为查询条件(where),排序(order by),分组(group by)操作字段建立索引.
尽量选择区分度高的列作为索引,区分度越高,使用索引效率越高.
如果是字符串字段,长度较长,针对字段特点,建立前缀索引.
尽量使用联合索引,减少单列索引,查询时联合索引很多时候可以覆盖索引,节省存储空间,避免回表查询,条高效率
控制索引数量,并不是越多越好,越多维护代价越大,影响增删改差的效率.
如果索引不能存储NULL值,建表的时候使用not null,当优化器知道每列是否包含null值时,可以更好的确定那个索引最有效的用于查询.

1.9 索引失效的情况

使用联合索引的时候,没有按照排序查询.

违反最左前缀法则.
范围查询右边的列,不能使用索引.
不要在索引上进行运算操作,索引将失效.
字符串不加单引号,造成索引失效(类型转换).
一%开头的like模糊查询,索引失效

2. 事务

2.1 事物特性

事务是一组操作的特性，一个不可以分割的工作单位，会把所有操作作为一个整体，向系统提交或者撤销，要么同成功，要么同时失败

举例子，转账A给B转账，要同时一个减少，一个增加。

2.2 ACID

原子性(atomicity) :事务是不可分割的最小操作单元,要么全成功,要么全失败.
一致性(Consistency):事务完成时,必须使所有的数据保持一致状态.
隔离性(Isolation): 数据库系统提供的隔离机制,保证事务再不受外部并发操作影响的独立环境运行.
持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的.

2.3 并发事务问题

问题：

解决：

2.4 undo log 和redo log

2.4.1 redo log

2.4.2 undo log

2.4.3 区别：

2.5 事务的隔离性如何保证？

5.4.1锁机制

在高隔离级别下，数据库使用锁（如行锁、表锁等）来控制事务间的并发。通过加锁机制，事务可以在执行过程中确保数据的完整性和一致性。
行级锁：保证了同一行数据在多个事务之间的独立性，减少了锁的竞争，提高了并发性。
表级锁：锁定整个表，确保没有其他事务对表进行修改，但会降低并发性。

5.4.2 MVCC

MVCC 允许数据库在同一时间保持多个版本的数据。事务可以读取到数据的某个版本，从而避免了读锁和写锁的冲突。许多现代的关系型数据库使用 MVCC 来实现更高的并发和隔离性。
在 MVCC 中，每个事务会看到数据库中数据的某个快照，避免了幻读的问题。

记录中的隐藏字段

undo log

readview

3. 分库分表

3.1应用场景

1. 前提，项目业务数据逐渐增多，业务发展迅速，单标1000w或20G以上

2.优化解决不了新能问题（索引，主从读写分离）

3.IO瓶颈，CPU瓶颈

3.2 概念

分库是一种水平扩展数据库的技术，将数据根据一定规则划分到多个独立的数据库中。每个数据库只负责存储部分数据，实现了数据的拆分和分布式存储。分库主要是为了解决并发连接过多，单机 mysql扛不住的问题。
分表指的是将单个数据库中的表拆分成多个表，每个表只负责存储一部分数据。这种数据的垂直划分能够提高查询效率，减轻单个表的压力。分表主要是为了解决单表数据量太大，导致查询性能下降的问题。

3.2 拆分策略

3.2.1 垂直分库：

根据业务模块将不同的表分配到不同的数据库中。
例如，将用户信息表放在一个数据库中，将订单信息表放在另一个数据库中。.

3.2.2 水平分库：

将同一个表的数据按一定规则（如按用户ID分区）拆分到多个数据库中。
例如，用户ID为1-10000的数据放在数据库A中，用户ID为10001-20000的数据放在数据库B中。

3.2.3 垂直分表

将一个表的不同列拆分到不同的表中，这些表可以存储不同的列数据，但通常是基于业务逻辑或者字段访问频率来拆分。

例如一个订单状态信息会频繁进行更新、订单金额在列表会频繁被查询到作为热点数据，而下单地址、手机号码等信息基本不会改变或者改变次数很少作为非热点数据。

垂直分表的原则

（1）、把不常用的字段单独放一张表。

（2）、把text、blob等大字段拆分出来单独放在一张表。

（3）、经常组合查询的字段单独放在一张表中。

3.2.4 水平分表

将一个大表拆分成多个小表，每个小表存储数据的不同片段。

3.4 你们怎么用的?

分库分表是两回事儿，可以只分库不分表，也可以只分表不分库，分库主要解决高并发瓶颈，分表主要解决数据量大瓶颈，但是一般情况下，我们都需要同时做分库分表。

3.4.1 我们垂直分表

把一张业务表中的备注字段拆分了,平时不咋用,偶尔用,但是他还比较大,是text类型.

3.4.2 水平分表

每天数据量表较大,单表数据量过大,现在按照每天一张表

4. 主从同步

4.1 主从作用?

数据的热备,后备数据库,主库故障后可以切换到备库,避免数据丢失.
机构扩展,业务量大后,IO增加,单裤无法满足需求,提高单机的io.
读写分离,是数据库支撑更大的开发

4.2 主从同步原理

通过bionlog

主库写 binlog：主库的更新 SQL(update、insert、delete) 被写到 binlog；
主库发送 binlog：主库创建一个 log dump 线程来发送 binlog 给从库；
从库写 relay log：从库在连接到主节点时会创建一个 IO 线程，以请求主库更新的 binlog，并且把接收到的 binlog 信息写入一个叫做 relay log 的日志文件；
从库回放：从库还会创建一个 SQL 线程读取 relay log 中的内容，并且在从库中做回放，最终实现主从的一致性。

4.3 开发场景

预算项目,经常要做去全部员工薪资数据的计算,然后做报表到处,刚开始没做读写分离的时候,一旦有人操作计算,就会导致mysql压力剧增,影响其他页面的查询,所以后来切换为主从模式,主库做计算等写操作,从库只读.

4.4 遇到的问题?

4.4.1 主从延迟

这个问题在生产上就发生了,用户操作后,显示成功,但是返回页面却发现数据没有改变,就是主从数据同步出现了延迟

解决 :

从库机器性能：从库机器比主库的机器性能差，只需选择主从库一样规格的机器就好。
从库压力大：可以搞了一主多从的架构，还可以把 binlog 接入到 Hadoop 这类系统，让它们提供查询的能力。
从库过多：要避免复制的从节点数量过多，从库数据一般以3-5个为宜。
大事务：如果一个事务执行就要 10 分钟，那么主库执行完后，给到从库执行，最后这个事务可能就会导致从库延迟 10 分钟啦。日常开发中，不要一次性 delete 太多 SQL，需要分批进行，另外大表的 DDL 语句，也会导致大事务。
网络延迟：优化网络，比如带宽 20M 升级到 100M。
MySQL 版本低：低版本的 MySQL 只支持单线程复制，如果主库并发高，来不及传送到从库，就会导致延迟，可以换用更高版本的 MySQL，支持多线程复制。

5. 如何定位慢查询?

5.1原因

聚合查询,多表查询;表数据量过大;深度分页查询,索引

5.2 工具

arthas进行定位,看是不是执行sql的时候耗时比较长
运维工具 prometheus

5.3 mysql自带慢查询日志(生产不会开启,损耗性能)

5.４慢查询sql如何优化

使用explain和desc命令获取执行select语句的信息

type 是index和all的时候就要考虑优化了

6. sql优化经验（说参考阿里的开发手册）

6.1 表优化

6.2 库设计优化

6.3 sql语句优化

统计时尽量使用count()，count()≈count(1)，大于count(主键) (count(*)标准语法推荐;做了很多优化count(列名),慢还会查询不为空的数据)。

如果明确查找一条语句，请使用limit1;，因为找到一条符合条件的记录后就不会继续查找了。

优化分页查询

使用limit limit 1000000,100;偏移量十万后查询很慢了,

子查询优化 select * from table where id>=(select id from table limit 100000,1) limit 100;

换一种写法 select * from table where id >= 1000001 limit 100;

避免 Select *用到什么字段就具体写什么字段，原因除了 select * 查询所有字段会多出网络传输开销之外，还有更重要的一点是，select * 无法使用覆盖索引。

尽量使用 MySQL 5.6以后的版本

对于使用索引方面对索引字段做函数操作或者做运算操作，都不能使用上索引。所以针对这一点，除了我们索引的字段不要加函数之外。还要注意一些隐式转换，比如，交易日志表（tradelog），tradeid 的字段类型是 varchar(32)，字段有索引，但是当你执行 select * from tradelog where tradeid=110717;语句，你发现走的还是全索引扫描。这是因为它其实做了类型转换，相当于这么执行mysql> select * from tradelog

7. mysql架构

7.1 逻辑架构

MySQL 逻辑架构图主要分三层：

客户端：最上层的服务并不是MySQL所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。
Server层：大多数MySQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
存储引擎层：第三层包含了存储引擎。存储引擎负责 MySQL 中数据的存储和提取。Server 层通过 API 与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。

7.2 一条SQL查询语句在MySQL中如何执行的？

先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限会先查询缓存.
如果没有缓存，分析器进行语法分析，提取 sql 语句中 select 等关键元素，然后判断 sql 语句是否有语法错误，比如关键词是否正确等等。
语法解析之后，MySQL 的服务器会对查询的语句进行优化，确定执行的方案。
完成查询优化后，按照生成的执行计划调用数据库引擎接口，返回执行结果。