Mysql面试篇笔记：

优化： 

1.如何定位慢查询：        

       首先压测接口，查看那个接口比较慢，可以通过多种工具，比如Skywaking 可以查看各个接口响应时间，查看接口最慢，然后去跟踪接口，查看详细信息，比如什么阶段执行了多久，也包括sql的执行耗时，就可以清晰看到那个sql执行耗时最久！！ 也可以通过mysql提供的开启慢查询！！(调试阶段，)，可以把执行慢的sql记录到日志中。默认关闭的，需要去my.cnf文件中去配置 ！！！

方案一：

通过多种工具：

方案二:

使用java自带的工具：

        可以利用MySQL自带的，slow_query_log来监控慢SQL，它是MySQL提供的一个日志功能，用于记录执行时间超过特定阈值的SQL语句。
对于慢查询，再使用EXPLAIN分析执行计划，查看查询的执行顺序、使用的索引、扫描的行数等，以识别潜在的性能瓶颈。
基于EXPLAIN再进行针对性的优化，常见的优化方向有：
·根据EXPLAIN的结果，检查是否有合适的索引。若缺失索引，则添加(特别是在WHERE、JOIN和ORDERBY子句中使用的列上)
·将复杂的JOIN查询拆分成多个简单查询，尽量小表驱动大表
·避免SELECT*，仅选择需要的字段

慢查询如何去配置文件针：

[mysqld]
slow_query_log = 1                    # 启用慢查询日志
slow_query_log_file = /var/log/mysql/mysql-slow.log  # 指定慢查询日志文件路径
long_query_time = 2.0                 # 设置慢查询的阈值时间（单位：秒）
log_queries_not_using_indexes = 1     # 记录未使用索引的查询

其中2秒就是一个阈值，超过这个就会被记录到指定的文件日志下：

当然也可以通过sql命令动态去配置：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 2.0;
SET GLOBAL log_queries_not_using_indexes = 'ON';

总：

1.1sql执行很慢，如何优化的呢？

可以采用EXPLAIN 或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息

通过Explain 来 查看sql 执行的情况 ！！！根据情况来优化！！！

举个列子;

新键一个表：

create table people
(id          varchar(20)                not null comment '学号'primary key,name        varchar(20)                not null comment '姓名',age         int                        null comment '年龄',position    varchar(20) default '群众' null comment '职位',create_time datetime                   null,gotime      datetime                   null comment '离职时间',dormitory   varchar(20)                not null comment '寝室',sex         int         default 1      not null comment '性别默认1',tuanw       int                        not null comment '团委'
)comment '班委情况';

查询name 为 何政，age=20，且是寝室16-330的

explain  select * from people where  name = '何政' and age = 20 and dormitory = '16-330';

 输出结果如下：

Type：ALL， （性能最差）表示MySQL需要扫描表中的所有行，即全表扫描。包可以优化的

字段所代表的意思： 

Explain 来分析SQL，主要属性： 

• id：查询的执行顺序的标识符，值越大优先级越高。简单查询的id通常为1，复杂查询（如包含子查询或UNION)的id会有多个。
• select_type(重要)：查询的类型，如SIMPLE(简单查询)、PRIMARY(主查询)、SUBQUERY（子查询）等。
• table：查询的数据表。
• type(重要）：访问类型，如ALL(全表扫描)、index（索引扫描）、range(范围扫描)等。一般来说，性能从好到差的顺序是：const>eq_ref> ref > range > index > ALL。（一般ALL，index 包优化的）
• possible_keys：可能用到的索引。
• key（重要）：实际用到的索引。
• key_len：用到索引l的长度。
• ref：显示索引l的哪一列被使用。
• rows(重要）：估计要扫描的行数，值越小越好。
• filtered：显示查询条件过滤掉的行的百分比。一个高百分比表示查询条件的选择性好。
• Extra(重要)：额外信息，如Using index(表示使用覆盖索引I)、Using where(表示使用WHERE条件进行过滤)、Using temporary(表示使用临时表)、Using filesort（表示需要额外的排序步骤）。 

其中：Type详解分析:

• system：表示查询的表只有一行（系统表）。这是一个特殊的情况，不常见。
• const：表示查询的表最多只有一行匹配结果。这通常发生在查询条件是主键或唯一索引，并且是常量比较。
• eq_ref：表示对于每个来自前一张表的行，MySQL仅访问一次这个表。这通常发生在连接查询中使用主键或唯一索引l的情况下。
• ref：MySQL使用非唯一索引扫描来查找行。查询条件使用的索引是非唯一的（如普通索引)。
• range：表示MySQL会扫描表的一部分，而不是全部行。范围扫描通常出现在使用索引l的范围查询中（如BETWEEN、，，>=，<=）。
• index：表示MySQL扫描索引中的所有行，而不是表中的所有行。即使索引列的值覆盖查询，也需要扫描整个索引。
• all（性能最差）：表示MySQL需要扫描表中的所有行，即全表扫描。通常出现在没有索引l的查询条件中。

当前我们走的全表扫描，无疑质问，优化！！！根据业务经常用到3个字段来联合查询，这里建立一个联合索引：

create index index_name_age_dormitory on people(name, age, dormitory);show index from people;

 其中的索引执行顺序根据索引创建顺序来的，索引生效，name字段必须存在，其他字段没有，可能导致部分索引失效，但是name字段没有，索引一定失效的

重新查询： 

 可以看到优化后的结果：

type：ref 使用的索引是非唯一的

视频图片：

 总结：

聚合查询——新增一个临时表去解决

多表查询——优化sql语句结构

表数据量过大——添加索引

深度分页查询——覆盖索引+子查询

   

通过possible_key 查看有哪些可能索引，key 实际用到的索引，key_len 可以查看当前索引是否生效！！！type 查看当前链接类型，是否有进一步的优化条件！！！ extra 建议判断 ！！！

explain分析结果中的重要字段：key , key_len用来分析是否使用了索引extra是否出现了回表查询type 查看sql是否有进一步的优化空间 是否存在index all 全索引扫描或全盘扫描

2.了解过索引吗？ 什么是索引？ 

索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定査找算法
的数据结构(B+树)，这些数据结构以某种方式引用(指向)数据， 这样就可以在这些数据结构上实现高级查找算法
这种数据结构就是索引。

图片: 

底层索引的数据结构了解吗？

B+树的优点就是解决了他们的缺点 

其他数据结构的缺点：

二叉树：缺点： 不平衡

红黑数：缺点： 每个节点只有2个，数据大后，高度高，I/O多

B-树：    缺点：节点既存储键值，又存储数据，导致节点存储的键值数量较少，树的高度较高。B-树的键值分布在所有节点中，范围查询需要遍历多个节点，效率较低。

B+ 树与其他数据结构的对比

图片： 

 

• • 每个节点既存储索引（键值），也存储对应的数据（行记录）。

  • 数据分布在所有节点中，包括非叶子节点和叶子节点。

• 只有叶子节点存储数据（行记录），非叶子节点仅存储索引（键值）。

• 叶子节点通过指针连接，形成一个有序链表。

为什么B+树比B树更适合数据库？

• 更高的查询效率：

  • B+树的所有数据都存储在叶子节点中，且叶子节点通过指针连接，支持高效的范围查询（如WHERE id BETWEEN 10 AND 100）。

  • B树的数据分布在所有节点中，范围查询需要遍历多个层级的节点，效率较低。

• 更少的磁盘I/O：

  • B+树的非叶子节点仅存储索引，不存储数据，因此每个节点可以存储更多的键值，树的高度更低，查询时需要访问的节点更少，减少了磁盘I/O次数。

  • B树的每个节点都存储数据，导致每个节点能存储的键值更少，树的高度更高，查询时需要访问更多的节点，增加了磁盘I/O次数。

• 更适合大规模数据：

  • B+树的叶子节点形成有序链表，非常适合范围查询和全表扫描。

  • B树的数据分散在各个节点中，范围查询和全表扫描的效率较低

总问：

我答：

索引就是帮助Mysql高效获取数据的数据结构

底层就是B+树：

因为他自平衡 比二叉树，减少I/O 成本

他是多二叉的，比数据结构红黑树比较，高度更低，查找更块

然后就是叶子节点存储数据，非夜子节点存储指针来导航的比B-树更块，叶子节点是双向链表的，更便于扫库和区间查询！！

3.什么是聚集索引 什么是非聚集索引：

聚集索引: 只有一个 且叶子节点关联的是行数据，就是整行， 聚集索引的选择：首先主键  （没有主键）其次 是唯一索引  都没有就 是indon自动生成 rowid

非聚集索引：（就是二级索引 可以有多个） 叶子节点就是关联的主键

图片：

答：

什么是回表：

“回表”是指在使用二级索引（非聚簇索引)作为条件进行查询时，由于二级索引中只存储了索引字段的值和对应的主键值，无法得到其它数据。如果要查询数据行中的其它数据，需要根据主键去聚簇索引查找实际的数据行，这个过程被称为回表。

 什么叫覆盖查询？：

是指二级索引中包含了查询所需的所有字段，从而使查询可以仅通过访问二级索引|而不需要访问实际的表数据（主键索引)。

覆盖索引的优点

• ·减少I/O操作：因为查询可以直接从索引中获取所有需要的数据，避免了访问实际表的数据页，从而减少了1/O操作。
• ·提高查询速度：索引比表数据更紧凑，因此从索引中读取数据比从表中读取要快。
• ·减少内存占用：只需要读取索引顶而不是表数据页，可以减少内存占用。

如图：

 Limit10000优化： 

解决：

 针对：

• id 是连续自增的:

  • 如果表中没有删除过数据，id 是连续的，可以直接使用 WHERE id > 9000000。

• id 是唯一的:

  • id 是主键或唯一索引，确保 WHERE id > 9000000 的结果是准确的。

SELECT * FROM tb_sku WHERE id > 9000000 ORDER BY id LIMIT 10;

 处理 id 不连续的情况

• 如果表中存在删除操作，id 可能不连续，可以通过以下方式解决：

  • 使用 WHERE id >= (SELECT id FROM tb_sku ORDER BY id LIMIT 9000000, 1) 来定位起始点。

SELECT * FROM tb_sku
WHERE id >= (SELECT id FROM tb_sku ORDER BY id LIMIT 9000000, 1)
ORDER BY id LIMIT 10;

核心思路就是，过滤掉不必要的数据行，如果有索引，减少回表的次数，通过二级索引来获取到我们的需要的主键索引，然后在查询我们数据 

 select * from mianshiya where name = 'yupi' and id > (select id from mianshiya where name = 'yupi' order by id limit 99999990，1)order by id limit 10;

这里讲错了，应该是因为直接分页查会让大量数据进出内存，导致IO时间长吧，使用子查询只让id进内存，减少IO时间

要回分页就是要返回一整行的数据，需表查询，然后可以用覆盖查询返回一列，再用这一列小数据去匹配回表查询整行

4.索引的创建原则：

此前列举一些mysql中索引创建方法：

1.创建索引

普通索引：

CREATE INDEX idx_name ON table_name(column_name);

• idx_name: 索引名称
• table_name：指定需要创建的索引的表
• column_name： 对应的字段

唯一索引 ：

CREATE UNIQUE INDEX idx_name ON table_name(column_name);

联合索引 ：

CREATE INDEX idx_name ON table_name(column1, column2, ...);

 全文索引：

CREATE FULLTEXT INDEX idx_name ON table_name(column_name);

2. 查看索引

SHOW INDEX FROM table_name;

3. 删除索引

删除普通索引

DROP INDEX idx_name ON table_name;

删除主键索引 

ALTER TABLE table_name DROP PRIMARY KEY;

言归正传：

创建索引注意事项：

1)不能盲目建立索引，索引并不是越多越好，索引会占用空间，且每次修改的时候可能都需要维护索引的数据，消耗资源。
2)对于字段的值有大量重复的不要建立索引。比如说：性别字段，在这种重复比例很大的数据行中，建立索引也不能提高检索速度。但是也不绝对，例如
定时任务的场景，大部分任务都是成功，少部分任务状态是失败的，这时候通过失败状态去查询任务，实际上能过滤大部分成功的任务，效率还是可以的。
3）对于一些长字段不应该建立索引。比如text、longtext这种类型字段不应该建立索引。因为占据的内存大，扫描的时候大量加载至内存中还耗时，使得
提升的性能可能不明显，甚至可能还会降低整体的性能，因为别的缓存数据可能因为它被踢出内存，下次查询还需要从磁盘中获取。
4)当数据表的修改频率远大于查询频率时，应该好好考虑是否需要建立索引。因为建立索引会减慢修改的效率，如果很少的查询较多的修改，则得不偿
失。
5)对于需要频繁作为条件查询的字段应该建立索引。在where关键词后经常查询的字段，建立索引能提高查询的效率，如果有多个条件经常一起查询，则
可以考虑联合索引，减少索引数量。
6)对经常在orderby、groupby、distinct后面的字段建立索引。这些操作通常需要对结果进行排序、分组或者去重，而索引可以帮助加快这些操作的速
度。

答：

5.索引失效的场景：

我回答;

就是以前在建立表的时候，有一个用户表，我通过姓名来查询学生，通过名字建立索引，发现模糊查询还是走的全表查询，然后我就没有给名字前面加模糊%，就走了索引

还要就是联合查询我们要遵守最左匹配原则，就是最左边的一定要存在！！！

可以少，但不能跳，少就是部分失效

索引排序: name status address

若 ： name address  ，则只有name 索引生效。中间跳了status字段

 name status  >1  address  :因为 status 使用了范围查询，则右边的address 就失效了！！！

 范围查询，但是可以用>=,<=避免

额外提示一点：SQL里面where优化后面的字段条件顺序不影响。比如先写status再写name再写addr，SQL优化器会帮你调优的

3. 不要在索引上进行位运行操作：

4. 字符串不加单引号，造成索引失效：（）

                隐式转换，默认会把字符串转换为数值，所以实际上相当于对status进行了函数运算

隐式转换，默认会把字符串转换为数值，所以实际上相当于对status进行了函数运算

5. 左 % 模糊查询导致索引失效 ！！！

答：

drop index  index_name_age_dormitory on people;explain select  * from people where  name like '何%';
explain  select  * from  people where  name like '%何%';
drop index  idx_name on people;
create  index  index_name_age_dormitory on people(name,age,dormitory);
# >索引失效的情况 dormitory索引失效了
explain select * from people where  name like '何%' and  age >16  and dormitory = '16-330';
# >=索引就不会失效了
explain select * from people where  name like '何%' and  age >=16  and dormitory = '16-330';
# 发生了类型转化 也会导致索引失效 通过key——len长度来判断是否失效
explain select * from people where  name like '何%'  and dormitory = 16-330;

 6.谈谈你对sql 优化经验： 

1. 表的设计：

比如设置合适的数值(tinyint int bigint)，要根据实际情况选择
比如设置合适的字符串类型(char和varchar)char定长效率高，varchar可变长度，效率稍低

2. sql语句优化

• SELECT语句务必指明字段名称(避免直接使用select*) 导致可能回表查询
• SQL语句要避免造成索引失效的写法
• 尽量用union all代替unibn union会多一次过滤，效率低
• 避免在where子句中对字段进行表达式操作 （条件上进行表达式操作！！！）
• Join优化 能用innerjoin 就不用left join right join，如必须使用 一定要以小表为驱动
• 内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。leftjoin 或 right join，不会重新调整顺序

小表驱动大表，就是减少与数据库的链接次数，在一次的链接次数里面处理完许多的业务次数，一次弄完就可以了，left join 不会重新去调试顺序的！！！ 

3.。主从复制，读写分离：

 

答：

其他面试题：

7.什么是事务： 

        事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

 答：

8.并发事务带来的哪些问题：怎么解决:mysql 默认隔离级别是什么？

并发事务：

面试要是问到了，记住不可重复读是update引起的，幻读是insert引起的，再以此描述一下事务的场景就行了

解决方案：

答：

9. redo log 和 undo LOG的区别

概念：

脏页：就是操作mysql的时候，一般update delete insrt 首先操作的主内存中的一个缓存池里面的数据，（内存中操作快，减少磁盘IO操作）如果没有，就会从磁盘中加载并且缓存，然后操作后，会一定大的频率刷新到磁盘上，才能保证数据的持久化，如果在这个过程中，mysql突然宕机了，就会导致数据持久化失败导致脏页！

因此，为了避免这种，有来了一个日志 redo log 来记录数据页的物理变化

redo log ： 简单就是当脏页刷新失败的时候，通过redo log 来恢复数据，实现数据的持久化！！！

为什么这样就从随机IO变成了顺序IO

undo log :

回滚日志，用于记录数据被修改前的信息，作用包含两个:提供回滚 和 MVCC(多版本并发控制)。undoloa和redo oa记录物理日志不
-样，它是逻辑日志。

可以认为当delete一条记录时，undolog中会记录一条对应的insert记录，反之亦然
当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容
并进行回滚。
undo log可以实现事务的一致性和原子性

总结：

redo Log 

 Undo Log

10. 事务隔离性如何保证的：

锁：insert update delete 都会自动添加这个排它锁：排他锁(如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁

MVCC:

MVCC的具体实现，主要依赖于数据库记录中的隐式字段、undolog日志、readView.

隐藏字段：

undolog日志：

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。
当insert的时候，产生的undolog日志只在回滚时需要，在事务提交后，可被立即删除。
而update、delete的时候，产生的undo log日志不仅在回滚时需要，mvcc版本访问也需要，不会立即被删除。

undolog版本链：

就是记录操作数据以前的数据，通过链表来连接起来，后续的查找通过readview规则隔离级别来选择读取那一个版本！！！ 只是做记录！！！

ReadView

ReadView(读视图)是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的)id。
当前读
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如
select ..lock in share mode(共享锁)，select.. for update、update、insert、delete(排他锁)都是一种当前读.
快照读
简单的select(不加锁)就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
Read Committed:每次select，都生成一个快照读。
Repeatable Read:开启事务后第一个select语句才是快照读的地方

readView 四核心字段：

RC ： 简单概括就是读取的上一次提交的事务，每次读取都会生成一个新的readview

RR   ：   读取的上一次未提交的事务：每次都会读取这跳：

11. 快照读和当前读的区别：

12.主从同步原理：

项

目上线，搭建mysql

原理：

复制分成三步
1.Master 主库在事务提交时，会把数据变更记录在二进制
日志文件 Binlog 中。
2.从库读取主库的二进制日志文件 Binlog ，写入到从库的
中继日志 Relay Log
slave重做中继日志中的事件，将改变反映它自己的数据。

主从同步原理
MySQL主从复制的核心就是二进制日志binog(DDL(数据定义语言)语句和 DML(数据操纵语言)语句)
①)主库在事务提交时，会把数据变更记录在二进制日志文件 Binlog 中。
② 从库读取主库的二进制日志文件 Binlog ，写入到从库的中继日志 Relay Log。
③ 从库重做中继日志中的事件，将改变反映它自己的数据 

13.分库分表：

海里数据可以分库分表：

分库分表的时机:
1，前提，项目业务数据逐渐增多，或业务发展比较迅速
单表的数据量达1000W或20G以后
2，优化已解决不了性能问题(主从读写分离、查询索引..
3，10瓶颈(磁盘I0、网络I0)、CPU瓶颈(聚合查询、连接数太多

分库分表是数据库性能优化的一种方法，通过将数据分散存储在多个数据库或表中，来提高系统的可扩展性、性能和可用性。
分库分表的类型（或策略）包括：
1）水平分表：
·将同一张表的数据按行划分，分散到多个表中。例如，可以按用户ID的范围将数据分为多个表（如user_1、user_2）。
2）垂直分表:
·将一张表的不同列拆分到多个表中，以减少每张表的字段数量和提高查询效率。例如，用户表可以分为基本信息表和详细信息表
3）水平分库：
·将相同的表结构复制一份到另一个库中，每个库的表结构是一样的，可以减少单一数据库的读写压力，在大数量的情况下提高读写性能。例如 database1、database2。
4）垂直分库：
·将数据分散到不同的数据库实例中。可以根据业务功能或模块进行分库，如将用户数据、订单数据分别存储在不同的数据库中。

图片： 

垂直分库:

 垂直分表：

        以字段为依据：

水平分库

哈希槽 找到对应的槽，可以根据id去模去存储，查找的时候，也是根据id去模去查找！！

如果没有id你要怎么查？

• 使用其他字段进行分片

• 组合字段进行分片

• 使用哈希函数

• 使用外部映射表

 水平分表：

分库分表的优缺点：

分库分表的优点

• ·提高性能：分库后，通过减少单个数据库的负载，提高读写性能。
• ·可扩展性：可以通过增加新的数据库或表来扩展系统。
• ·容错性：某个库或表的故障不会影响整个系统。

分库分表的缺点

• ·复杂性：数据的查询、维护和事务管理变得复杂，增加了开发和运维的成本。
• ·事务处理：跨库或跨表的事务处理复杂，需要额外的处理机制。
• ·数据一致性：需要额外机制来保证数据的一致性和完整性。 

问题解决：

mycat，sharding-sphere 中间件 来解决上面的问题：

 总结;

业务介绍
1，根据自己简历上的项目，想一个数据量较大业务(请求数多或业务累积大)
2，达到了什么样的量级(单表1000万或超过20G)
·具体拆分策略
1，水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题
2，水平分表，解决单表存储和性能的问题

1 2 两点可以通过中间件来解决这些问题：：
垂直分库，根据业务进行拆分，高并发下提高磁盘I0和网络连接数
3，垂直分库，根据业务进行拆分，高并发下提高磁盘I0和网络连接数
4，垂直分表，冷热数据分离，多表互不影响

水平分库，水平分表就是复制多分，担任问题就要通过中间件比如MyCat来实现解决。

垂直分库：就比如我们的微服务项目，不同的服务，我们把表分到不同的库中，只对应这个项目使用，根据暴露的接口来查询，逻辑获得

垂直分表：就是一些大字段，我们可能经常不会太用，就可以放到其他的表中，和这个关联起来，实现冷热数据隔离，比如身份证信息啊，哪些地址等可以分成另一个表中去存储！！！