闪击学习mysql-CSDN博客

本文链接：https://blog.csdn.net/qq_29484773/article/details/137439712

一文搞定mysql面试题

大纲

1.如何定位慢查询

1）方案1：开发调试阶段开启慢查询日志，配置2秒（综合值）以上时间查询为慢查询。记录到慢查询日志

在MySQL的配置文件(/etc/my.cnf)中配置如下信息:

# 开启MySQL慢日志查询开关
slow_query _log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

2）方案:2:开源工具
调试工具: Arthas
运维工具: Prometheus 、Skywalking 可定位到具体的慢查询语句

2.分析SQL执行计划

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

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

type字段：这条sql的连接的类型，性能由好到差为NULL、system、const、eq_ref、ref、range、index、all

system:查询系统中的表
const:根据主键查询
eq_ref:主键索引查询或唯一索引查询
ref:索引查询
range:范围查询
index:索引树扫描
all:全盘扫描

最低要求为range,如果为index或all就得尝试优化此sql

3.索引是什么，为什么InnoDB默认存储引擎用B+树

1）索引是什么？

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

结构对比

二叉搜索树和红黑树的问题：数据量较大时树会很高导致检索数据效率下降

B-tree读作b树，不要读成b减树了哈，它变矮了但还不够好，主要区别在于非叶子节点是否存储数据，叶子节点有无指针链接

B+树

B树与B+树对比:
①:磁盘读写代价B+树更低;②:查询效率B+树更加稳定;③:B+树便于扫库和区间查询

4.聚簇索引和非聚簇索引(二级索引)

什么是索引覆盖？

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。
例如现有表结构

表 table1

id 主键

name 创建了索引

age 无索引

如果我这么查都是索引覆盖了的

# 条件为name，返回字段也是name，能被name的索引覆盖到无需回表
select name from table1 where name = 'zhangsan';
# 使用了主键聚簇索引，id直接找到整行数据，无需回表
select * from table1 where id= '1';
# name,id皆为 能被name上建立的索引覆盖到
select name,id table1 where name ='zhangsan';

而这样则为未覆盖需回表：

# 下面这个age是在name的索引上找不到的，所以会拿着name='zhangsan'的id去查聚簇索引上的age值
select name,age table1 where name ='zhangsan';
# 和上面同理
select * from  table1 where name ='zhangsan';

上面这个例子如何避免回表呢？

我们可以创建一个联合索引如index_name_age,这样name,age字段都能从这个联合索引中取到而无需回表

什么是回表查询？

一个查询需要通过二级索引才能找到对应的主键值，再到聚集索引中查找整行数据，这个过程就是回表

5.超大分页问题解决办法

解决办法：覆盖索引加子查询

6.索引的创建原则

1).针对于数据量较大，且查询比较频繁的表建立索引。单表超过10万数据（提升用户体验)
2).针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。

3).尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4).如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5).尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6).要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7).如果索引例不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪入个索引最有效地用于查询。

博主的测试1千万条数据的索引空间2个联合索引占用如下

这里注意，联合索引的顺序是有意义的，查询语句根据最左匹配原则来匹配联合索引

先灌数据再建索引会比先建索引再灌数据的速度快很多

7.什么情况下索引会失效

1）违反最左前缀导致失效：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引:

2) 范围查询右边的列，不能使用索引。

根据前面的两个字段name,status查询是走索引的，但是最后一个条件address没有用到索引。（这里比较了两次key,key_len，key_len第二次明显锐减）

3) 不要在索引列上进行运算操作，索引将失效。

4) 字符串不加单引号，造成索引失效。（发生了类型转换）

5) 以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

8.sql优化经验总结

表的设计优化（(参考阿里开发手册《嵩山版》)
比如设置合适的数值(tinyint int bigint)，要根据实际情况选择
比如设置合适的字符串类型(char和varchar) char定长效率高,varchar可变长度，效率稍低
SQL语句优化
SELECT语句务必指明字段名称（避免直接使用select * )
SQL语句要避免造成索引失效的写法
尽量用union all代替union union会多一次过滤，效率低
避免在where子句中对字段进行表达式操作
Join优化能用innerjoin就不用left join right join，如必须使用一定要以小表为驱动，
内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join或 right join，不会重新调整顺序

（插句题外话，本以为学到这就能应付面试了，没想到第二天就被打脸了）

博主被下面几个问题问倒了:

1.为什么要使用主从架构？

1）一般应用是读多写少，可以使用主从进行读写分离，降低了主库读压力，多个从库提升了查询能力，进而提升系统qps

2)如果主库宕机，从库可以切换为主库，保证系统高可用

3）实现容灾备份

2.主从复制的原理是什么？

MySQL的主从复制主要通过三个线程协作完成：主库的一个线程和从库的两个线程。当主库上的数据发生变化时，这些变化会被记录到二进制日志（binlog）中。从库上的I/O线程定期连接到主库，接收这些二进制日志（增删改），并将它们转换为中继日志（relay log）。接着，从库的SQL线程读取中继日志中的事件，并在从库上回放执行这些事件（增删改），从而保持与主库的数据同步

3.怎么保证主从的一致性

1）语句执行函数导致的不一致

可修改binlog模式为row行模式，在row level模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条被修改。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节，不会出现某些特定的情况下的存储过程或function调用导致的不一致问题（还有两种分别是混合模式和Statement 语句模式<默认>:只记录每条语句，可能导致类似时间函数执行不一致的问题）

2）主从延时导致的主从不一致

选取相同规格的主从机器保证性能相近；

如果从库读压力大，可以一主多从，降低从库压力，但从库节点不能过多不超过5个；

大事务导致延时过长，需开发过程避免；

主从尽量选择同一局域网，降低网络延时；

使用半同步复制（任一从节点ack后主节点提交事务），或者选择更高版本的mysql,如mysql5.7.17版本推出的MGR（组复制）,

3）遇到主从延时如何解决呢

可以使用缓存如redis，但需解决redis和db的一致性问题；

需要强一致性的业务可以强制只查主库

4.当主节点发生故障会怎么样？

1主1从会自动切换（老版本需人为切换或利用工具监测心跳）但可能同时故障；

一主多从，从库如何进行切换，选取偏移量更大的为主节点，原来的主库恢复后成为从库