mysql常见面试题_group by having 联合索引-CSDN博客

本文链接：https://blog.csdn.net/libaowen609/article/details/125137028

一、联合索引是什么?为什么需要注意联合索引中的顺序？什么是最左前缀匹配原则

MySQL可以使用多个字段同时建立一个索引,叫做联合索引.在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引.

最左前缀匹配原则，mysql会⼀直向右匹配直到遇到范围查询（>、<、between、like）就停⽌匹配。
⽐如a=3 and b=4 and c>5 and d=6如果建⽴（a，b，c，d）顺序的索引，d是⽤不到索引的，如果建⽴（a，b，d，c）的索引则都可以⽤到，a，b，d的顺序可以任意调整。=和in可以乱序，⽐如a=1 and b=2 and c=3建⽴（a，b，c）索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识

二、事务的基本特性和隔离级别，多事务的并发进行一般会造成以下几个问题

事务基本特性：

原子性（一个事务中的操作要么全部成功，要么全部失败）、
一致性（数据库总是从一个一致性状态转换成另外一个一致性的状态）、
隔离性（事务在最终提交前、对其它事务时不可见的）、
持久性

mysql的隔离级别是在mysql.ini中配置的，常见配置：

未提交读(READ UNCOMMITTED) 可能引起脏读、不可重复读、幻读
已提交读(READ COMMITTED) 可能引起不可重复读、幻读
可重复读(REPEATABLE READ() 可能引起幻读
可串行化(SERIALIZABLE)

多事务的并发进行一般会造成以下几个问题

脏读: A事务读取到了B事务未提交的内容,而B事务后面进行了回滚.
不可重复读: 当设置A事务只能读取B事务已经提交的部分,会造成在A事务内的两次查询,结果竟然不一样,因为在此期间B事务进行了提交操作.
幻读: A事务读取了一个范围的内容,而同时B事务在此期间插入了一条数据.造成"幻觉".

三、如何对mysql进行优化

索引相关的问题，一般能用explain命令帮助分析。通过它能看到用了哪些索引，大概会扫描多少行之类的信息

尽量使用主键查询: 聚簇索引上存储了全部数据, 相比普通索引查询, 减少了回表的消耗.
若频繁查询某一列数据, 可以考虑利用覆盖索引避免回表，当explain的输出结果extra字段为using index ,能触发索引的覆盖
MySQL进行了索引下推优化, 通过适当的使用联合索引, 减少回表判断的消耗.
联合索引将高频字段放在最左边.
小表驱动大表，即小的数据集驱动大的数据集
索引是否失效，explain的输出语句key为空表示没走索引，索引失效的场景有很多，比如用了不等号，隐式转换等，比如id_no字段类型为varchar，但在SQL语句中使用了int类型，导致全表扫描，需要排查
当出现 Using filesort 问题，说明sql 语句 WHERE 条件和 ORDER BY 的条件不一样，索引没建好的话，那么 ORDER BY 就使用不到索引，可以建立一个包含 WHERE 和 ORDER BY 条件的混合索引，要是不想重建索引，force index() 方法强制使用这个索引 SELECT * FROM roles FORCE INDEX (`idx_user_role`) where username='nacos'

四、MySQL 索引失效的场景！

在联合索引的场景下，查询条件不满足最左匹配原则
参数类型与字段类型不匹配，导致类型发生了隐式转换，索引失效。
模糊查询时（like语句），模糊匹配的占位符位于条件的首部。
查询条件使用or关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效；
查询条件使用is null时正常走索引，使用is not null时，不走索引。
查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效。
当查询条件为大于等于、in等范围查询时，根据查询结果占全表数据比例的不同，优化器有可能会放弃索引，进行全表扫描。

五、数据库中，select where group by having 执行顺序

1.执行where 语句对全表数据做筛选，返回第1个结果集。
2.针对第1个结果集使用group by分组，返回第2个结果集。
3.针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。
4.针对第3个结集执行having xx进行筛选，返回第4个结果集。
5.针对第4个结果集排序。

六、Mysql死锁问题

当索引是一个 普通索引 的时候，会加一个间隙锁(gap lock)来防止幻读,此gap lock会锁住一个左开右闭的区间,

事务1持有间隙锁(gap lock)，等待事务2的插入意向锁(insert intention lock)释放；

事务2持有间隙锁(gap lock)，等待事务1的插入意向锁(insert intention lock)释放，从而导致死锁

间隙锁可以用来防止幻读，事务隔离级别可重复读使用。
如果等值查询条件使用了主键或者唯一索引，那么不会使用间隙锁，而是直接使用记录锁。
使用读已提交事务隔离级别可以禁用间隙锁。

手把手教你分析解决MySQL死锁问题_后端元宇宙的博客-CSDN博客_mysql死锁在生产环境中如果出现MySQL死锁问题该如何排查和解决呢，本文将模拟真实死锁场景进行排查，最后总结下实际开发中如何尽量避免死锁发生。一、准备好相关数据和环境当前自己的数据版本是8.0.22mysql> select @@version;+-----------+| @@version |+-----------+| 8.0.22 |+-----------+1 row in set (0.00 sec)数据库隔离级别(默认隔离级别)mysql> select @@.https://blog.csdn.net/yudiandemingzi/article/details/122204362?share_token=ed0bc167-b711-4519-9b8c-19a5e8ce16fa

七、如何避免死锁问题

1）不同的应用访问同一组表时，应尽量约定以相同的顺序访问各表。对一个表而言，应尽量以固定的顺序存取表中的行。

2）在主键等值更新的时候，尽量先查询看数据库中有没有满足条件的数据，如果不存在就不用更新，存在才更新。为什么要这么做呢，因为如果去更新一条数据库不存在的数据，一样会产生间隙锁。

举例：如果表中只有id=1和id=5的数据，那么如果你更新id=3的sql，因为这条记录表中不存在，那就会产生一个(1,5)的间隙锁，但其实这个锁就是多余的，因为你去更新一个数据都不存在的数据没有任何意义。

3）尽量使用主键更新数据,因为主键是唯一索引，在等值查询能查到数据的情况下只会产生行锁，不会产生间隙锁，这样产生死锁的概率就减少了。当然如果是范围查询，一样会产生间隙锁。

4）避免长事务，小事务发生锁冲突的几率也小

八、mysql的多版本控制（MVCC）

通过保存数据在某个时间点的快照来实现，一个事务无论运行多长的时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，也就意味着在同一个时刻不同的事务看到的相同表里的数据可能时不同的

InnoDB存储引擎MVCC的实现策略

在每一行数据中额外存放两个隐藏的列：

当前行创建时的版本号和删除时的版本号（可能为空，其实还有一列成为回滚指针，用于事务回滚）。
每个事务又有自己的版本号，这样事务内指向CRUD操作时，就通过版本号的对比来达到数据版本控制的目的。

在查询时要符合以下两个条件的记录才能被事务查询出来

1：删除版本号未指定或者大于当前事务版本号，即查询事务开启后确保读取的行未被删除。
2：创建版本号小于或者等于当前版本号，就是说记录创建是在当前事务中或者当前事务启动之前的其它事务进行的insert。

九、聚簇索引和非聚簇索引、innoDB和MylSM

聚簇索引将数据存储和索引放到了一块，并且是按照一定的顺序组织的，找到索引也就找到了数据
非聚簇索引，叶子节点不存储数据，存储的是数据行的地址
innoDB中一定有主键，主键一定是聚簇索引（索引的数据域存储了数据本身），辅助索引的数据域存储了主键的值，因此从辅助索引查找数据，先通过辅助索引找到主键值，再访问数据本身，支持事务，支持外键，支持行锁定，不存储总行数
mylsm使用的是非聚簇索引，索引文件的数据域存储指向数据文件的指针。辅助索引和主索引基本一致，但是辅助索引不能保证唯一性，不支持事务，不支持外键，不支持行锁定

十、mysql索引类型

普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

唯一索引：索引列中的值必须是唯一的，但是允许为空值。
主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。
组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合

十一、主键索引与唯一索引的区别：

主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
唯一性索引列允许空值，而主键列不允许为空值。
主键索引在创建时，已经默认为非空值+ 唯一索引了。
一个表最多只能创建一个主键索引，但可以创建多个唯一索引。
主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
主键可以被其他表引用为外键，而唯一索引不能。

十二、索引的设计原则

适合做索引

适合索引的列是出现在where子句的列
基数较小的表，索引较差的表，没必要建立索引
使用短索引，如果对长字符串列表进行索引，应该指定一个前缀长度
不要过度索引
有外键的数据列一定要建立索引

不适合做索引

更新频繁的字段不适合做索引
不能有效区分数据的列不适合做索引
尽量扩展索引，不要新建索引
对于查询中很少出现的列，重复比较多的列不适合建立索引
对于text、image的数据类型的列不要建立索引

十三、如何查看mysql执行计划

执行计划就是sql的执行查询的顺序，以及如何使用索引查询，返回的结果集的行数

EXPLAIN SELECT * from A where X=? and Y=?

1. id ：是一个有顺序的编号，是查询的顺序号，有几个 select 就显示几行。id的顺序是按 select 出现
的顺序增长的。id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为
NULL最后执行

2.type：优化sql的重要字段，也是我们判断sql性能和优化程度重要指标。他的取值类型范围：

const：通过索引一次命中，匹配一行数据
system: 表中只有一行记录，相当于系统表；
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
ref: 非唯一性索引扫描,返回匹配某个值的所有
range: 只检索给定范围的行，使用一个索引来选择行，一般用于between、<、>；
index: 只遍历索引树；
ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。那么基本就是随着表的数量增多，执行效率越慢。
执行效率：
ALL < index < range< ref < eq_ref < const < system。最好是避免ALL和index
3.key：此字段是 mysql 在当前查询时所真正使用到的索引。他是possible_keys的子集

4.extra

using filesort ：表示 mysql 对结果集进行外部排序，不能通过索引顺序达到排序效果。一般有
using filesort : 都建议优化去掉，因为这样的查询 cpu 资源消耗大，延时大。
using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往
往说明性能不错。
using temporary：查询有使用临时表, 一般出现于排序，分组和多表 join 的情况，查询效率不
高，建议优化。
using where ：sql使用了where过滤,效率较高。
5.rows：mysql 查询优化器根据统计信息，估算该sql返回结果集需要扫描读取的行数，这个值相关重要，索引优化之后，扫描读取的行数越多，说明索引设置不对，或者字段传入的类型之类的问题，说明要优化空间越大

十四、Mysql主从同步原理

当 master 主服务器上的数据发生改变时，则将其改变写入二进制事件日志文件中(bin-log⽇志)
salve 从服务器会在一定时间间隔内对 master 主服务器上的二进制日志进行探测，探测其是否发生过改变，如果探测到 master 主服务器的二进制事件日志发生了改变，则开始一个 I/O Thread 请求 master 二进制事件日志
同时 master 主服务器为每个 I/O Thread 启动一个dump Thread，用于向其发送二进制事件日志
slave 从服务器将接收到的二进制事件日志保存至自己本地的中继日志文件中(relay log)
salve 从服务器将启动 SQL Thread 从中继日志中读取二进制日志，在本地重放，使得其数据和主服务器保持一致；
最后 I/O Thread 和 SQL Thread 将进入睡眠状态，等待下一次被唤醒

注意：主从复制的过程会有很小的延迟，基本没有影响,主从节点使用binlog+position偏移量来定位主从同步的位置，当从节点保存其已经接受到的偏移量，如果从节点发生宕机重启，则会自动从position的位置发起同步