MySQL知识梳理

最新推荐文章于 2025-01-30 06:05:28 发布

原创最新推荐文章于 2025-01-30 06:05:28 发布

· 611 阅读

8 ·

版权

文章标签：

#mysql #数据库

1、MySQL8默认字符集utf8，支持中文

2、sql_mode： ONLY_FULL_GROUP_BY

分组sql语句时候，select后面出现什么内容？

分组字段、聚合函数、重复数据

3、索引

帮助MySQL高效获取数据的一种数据结构
优：提高数据的检索效率，降低数据库的IO成本
缺：维护成本高（修改数据维护索引），占用空间

4、树

二叉树：最多有两个子节点
二叉搜索树：左子节点比当前节点小，右子节点比当前节点大
平衡二叉树：左右子树高度差的绝对值不超过1
多叉树：树的高度和IO次数相关，可以通过降低树的高度，减少io次数，提高检索效率
B树：每个节点都包含全部数据
B+树：只有叶子节点包含全部数据，其他节点只有主键值

1.常见的存储引擎有哪些，各有什么区别？

InnoDB（mysql默认的存储引擎）、MyISAM

区别：

1.InnoDB---支持外键，支持事务，行锁

MyISAM---不支持外键，不支持事务，表锁

2.InnoDB：索引和数据全部存储在以.ibd结尾的文件中

MyISAM：索引和数据是分开进行存储的

3.InnoDB：聚簇索引时，存储的是全部值；以非主键为索引建立非聚簇索引时，存储的是非主键值，需要通过一次回表，才能获取全部数据

MyISAM：无论是聚簇索引还是非聚簇索引，也子节点你记录的都是数据的地址值

2.MySQL逻辑架构？

建立mysql服务连接
权限校验，用户的操作权限
根据sql语句类型，找到对应的操作接口
解析sql语句，得到每一个部分，变成树形结构
优化器，对查询的sql语句进行优化
通过存储引擎执行语句

3.索引底层数据结构？B+Tree跟B-Tree区别？

mysql默认的存储引擎是InnoDB，数据结构是B+树
B树和B+树的区别：

1.B树：每个节点都包含全部数据

B+树：只有叶子节点包含全部数据，其他节点只有主键值

2.B树：数据分散存储

B+树：数据从小到大存储在叶子节点中【顺序和范围查询方便】

3.B树：每个节点存储主键和主键以外其他数据，占用空间更大

4.聚簇索引&非聚簇索引区别？

聚簇索引：以主键创建索引，可以根据主键找到全部数据
非聚簇索引：以非主键创建索引，根据非主键字段，先找到主键字段，再根据主键字段进行回表查询，从而获得全部数据。
若查询的字段就是索引字段，可直接获得数据【select后面避免*，使覆盖索引生效】

5.MySQL可以从哪些方面调优？

创建索引

频繁作为where查询条件的字段
经常作为 group by 和 order by 的列
字段的值有唯一性限制
distinct去重的字段需要创建索引
多表join时，对连接字段创建索引
使用字符串前缀创建索引
一个字段 address varchar（120），我们可以创建索引的长度为（12）个字符，节省索引空间
区分度高的列（重复的数据少的列）适合作为索引
使用频繁的列，放到联合索引的左侧

sql语句优化（单、多）
集群部署（主从复制、读写分离）
分库分表（单表行数500W、容量2GB）

通过ShardingSphere中间件实现

原则1：尽量不进行划分/尽量少分--跨库操作（性能降低）

原则2：根据业务进行划分--有关联的表，尽量放到一个库中（避免跨库操作）

修改数据库参数（一般不采取）

6.索引失效的场景？如何进行SQL索引优化（单表，多表）

索引失效场景：

1.计算、函数

2.以%开头的like模糊查询

3.不为空

4.不相等

5.类型转换

单表优化法则：

6.最前缀最优法则

7.全值匹配法则

8.索引中范围条件右边的列失效

多表优化：

1.内连接：自动优化，自动选取数据量少的表为驱动表，数据量大的表为被驱动表并创建索引

2.左外连接：自己选取数据量少的表为驱动表，数据量大的表为被驱动表并创建索引

7.如何定位问题SQL？怎么分析SQL问题原因？执行计划重点查看指标有哪些？

使用慢查询日志定位问题sql

第一步：开启慢查询日志

第二步：设置项目中语句可以接受的执行时间（默认10秒）

第三步：等待语句执行，如果超时，会写入慢查询日志中

使用执行计划explain，根据explain执行之后字段各个指标，分析sql问题，进行优化
id：执行的趟数、 key：使用到的索引、 key_len：索引的充分程度、row：行数、filtered：命中率、Extra：额外的数据

8.事务特性？隔离级别有哪些分别解决怎么问题？MVCC原理？

特性：ACID--原子性、一致性、隔离性、持久性
问题：脏读、幻读、不可重复读
mysql隔离级别：读未提交、读已提交、不可重复读（repeatable-read）、序列化（serializable）
分别解决的问题：

1.读未提交：都未解决

2.读已提交：解决了脏读

3.不可重复读（repeatable-read）：解决了脏读和不可重复读【mysql默认隔离级别】

4.序列化（serializable）：全部解决【不支持并发，性能低】

MVCC原理：解决并发场景下的读写问题

9.数据库悲观锁乐观锁怎么实现？

悲观锁：for update（行锁）
乐观锁：提交数据时，会判断当前数据版本号和数据库版本号是否一样，一样，则修改数据，修改版本号

10.事务失效场景有哪些？

抛出非运行时异常

比如：编译异常

try-catch异常未抛出

catch中只打印，没有throw抛出

方法不是public的权限
调用本类的方法

aop，创建事务的代理对象，调用自己，得不到

没有被spring管理
指定的rollbackFor异常与运行时抛出的不一样
aop切面导致事务不能回滚

--spring中的事务使用aop是实现，框架封装【切面先执行】

--自己功能使用aop

--使用aop肯定有切面类【切面后执行，出现异常不能感知--失效，必须throw抛出去才行--才能回滚】

--多个切面会产生冲突问题

11、MySQL索引的设计原则

适合索引的列是出现在where子句中的列，或者连接子句中指定的列。
基数较小的表，索引效果较差，没有必要在此列建立索引
使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配
不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
定义有外键的数据列一定要建立索引。
更新频繁字段不适合创建索引。
若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
对于定义为text、image和bit的数据类型的列不要建立索引。

12、关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化过？

在业务系统中，除了使用主键进行的查询，其他的都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明白慢的原因是什么？

1、是查询条件没有命中索引？

2、是否加载了不需要的数据列？

3、还是数据量太大？

所以优化也是针对这三个方向来的：

1、首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。

2、分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。

3、如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

13、主键使用自增ID还是UUID，为什么？

如果是单机的话，选择自增ID；
如果是分布式系统，优先考虑UUID，但还是最好公司自己有一套分布式唯一ID生产方案。
自增ID：数据存储空间小，查询效率高。但是如果数据量过大,会超出自增长的值范围，多库合并，也有可能出现问题。uuid：适合大量数据的插入和更新操作，但它是无序的，插入数据效率慢，占用空间大。