Mysql面试问题总结关于索引优化

lianglong_1

于 2024-01-04 11:48:15 发布

阅读量948

点赞数 25

分类专栏： MySql专题文章标签： mysql 面试数据库

本文链接：https://blog.csdn.net/lianglong_1/article/details/135382572

版权

MySql专题专栏收录该内容

5 篇文章 0 订阅

订阅专栏

SQL优化技巧

尽量避免回表，尽量使用覆盖索引，或者主键索引。
避免使用SELECT *：只选择需要的列，而不是选择整个表的所有列。
避免使用子查询，多次查询导致资源开销大，可以通过连表查询
避免使用SELECT DISTINCT和SELECT COUNT()，是为了避免对数据库的性能造成不必要的负担，可以通过在查询列增加索引，或者使用group by优化查询。
尽量避免全表扫描，并且尽可能优化索引，避免过多的索引导致写操作性能下降。
合理选择数据类型，尽量使用较小的数据类型。例如，使用INT而非BIGINT，使用DATE而非DATETIME等。
增大buffer pool的大小，根据机器内存大小决定
其他：
使用全值匹配 ：查询条件中包含了联合索引中的所有字段，这样一般会走完整的联合索引。过滤掉的数据行更加精确，效率更高。（比如有三个索引 a ab abc，那么建议查询条件中全部包含abc三个字段）
遵循最左匹配原则，索引列最左字段必须出现，这是使用联合索引的前提条件。
范围条件放最后：遇到范围时后面的条件就不判断索引了，即范围右边的条件不走索引。
字段是否允许为null影响索引：
不等于要慎用

mysql使用缓存的情况：

MySQL的buffer pool是一个内存区域，用于存储数据库中的数据页。它允许MySQL在内存中缓存最常用的数据，以便快速访问和减少磁盘I/O操作。
Buffer pool的主要作用包括：
提高性能：通过将最常用的数据存储在内存中，可以加快数据库的读取速度，减少磁盘I/O操作，从而提高数据库的性能。
减少磁盘访问：通过缓存数据页，可以减少对磁盘的访问次数，降低数据库的负载和延迟。
提高并发性能：通过减少磁盘I/O操作，可以提高数据库的并发性能，允许更多的用户同时访问数据库。
总之，buffer pool是MySQL中非常重要的一个组件，它可以显著提高数据库的性能和并发能力。

mysql索引:

当MySQL执行查询时，它会根据查询的字段和条件来判断是否可以使用索引来加速查询。

如果查询中没有where条件，MySQL会尝试根据select的查询字段来判断是否可以使用索引（但是不一定使用）。
当存在where条件时，MySQL会根据查询条件来判断是否有符合的索引，并根据select的查询字段来判断是否需要回表（即是否需要访问实际的数据行）。
MySQL会根据查询的具体情况来决定是否使用索引以及如何使用索引来优化查询性能。
示例：

# 没有where条件
EXPLAIN select * from t_w11_section

EXPLAIN select id,bid_section_code from t_w11_section

第一条sql运行结果如图：

第二条sql运行结果如图；
第二条运行结果
由此可见，没有where条件时，会依据查询字段尝试使用索引，具体是否使用索引由查询优化器决定。

EXPLAIN select id,bid_section_code from t_w11_section where bid_section_code = '120000'


EXPLAIN select id,bid_section_code,bid_section_name,bid_section_content,status from t_w11_section where bid_section_code = '120000' and status > 20

上述两个sql都使用了索引。
在这里插入图片描述

执行计划

在 MySQL 的执行计划（EXPLAIN）中，有三种常见的标记来表示查询的执行情况：

Using index: 表示查询使用了覆盖索引，即查询的列都包含在索引中，MySQL 可以直接从索引中获取所需的数据，而不需要回表到原始数据表。
Using where: 表示查询使用了索引进行行过滤，但仍然需要进一步的条件判断。这通常发生在使用了范围查询、LIKE 操作符等需要进一步判断的情况。
Using index condition: 表示查询使用了索引条件推送（index condition pushdown），这是 MySQL 5.6 版本引入的新特性。它表示查询使用了索引来进行条件判断，但是需要回表来获取其它的列数据。

这些标记可以帮助你了解查询的执行情况，以及是否进行了全表扫描或者回表操作，有助于优化查询性能。

使用较小的数据类型主要有以下几个原因：

存储空间：较小的数据类型占用更少的存储空间，可以减少数据库的存储需求，从而降低硬件成本。
处理速度：较小的数据类型通常在处理速度上更快，因为它们需要的内存和磁盘I/O更少。
索引效率：在索引中，较小的数据类型通常更高效，因为它们需要的磁盘空间更少，从而减少了索引的大小。
查询优化：数据库查询优化器通常更倾向于使用较小的数据类型，因为它们可以更快地处理这些数据类型。
减少冗余：使用正确的数据类型可以减少数据冗余，从而提高了数据的一致性和完整性。

总的来说，使用较小的数据类型可以减少资源占用，提高性能，并且简化数据模型和查询优化。但也要注意，不要过度缩小数据类型，因为这可能会导致精度损失或违反业务需求。

mysql断电重启后的执行过程：

在数据库重新启动后，MySQL会根据事务日志（redo log）和数据日志（undo log）的内容来进行恢复和修复操作，以确保数据库能够恢复到一致的状态。
对于已经提交的事务，它们的修改操作已经被写入了redo log并且已经应用到数据库中的实际数据页，因此在数据库重新启动后，不需要对已提交的事务进行任何处理。
而对于未提交的事务，它们的修改操作可能已经被写入了redo log，但还没有被应用到数据库中的实际数据页。此时，MySQL会根据undo log中的信息来执行回滚操作，将这些未提交的事务的修改操作进行逆操作，从而将数据库恢复到一个一致的状态。
因此，你的理解是正确的：在数据库重新启动后，会将没有提交的事务，执行undo log中的操作进行回滚，对于已经提交的事务，则不需要进行处理。

RedoLog的刷盘操作

Redolog的刷盘操作是指将redo log从内存中的缓冲区刷新到磁盘上的持久化存储中。这是为了保证redo log的数据安全性和持久性，因为在系统崩溃的情况下，只有已经刷新到磁盘的redo log数据才能够保证不丢失。
Redolog的刷盘操作通常由数据库管理系统自动管理，不需要用户手动干预。一般来说，数据库管理系统会根据配置和系统参数，自动决定何时将redo log刷新到磁盘。
在InnoDB存储引擎中，可以通过innodb_flush_log_at_trx_commit参数来控制redo log的刷盘时机。这个参数有三个值：0、1和2，分别代表不同的刷盘策略。
● 当innodb_flush_log_at_trx_commit设置为0时，表示每次事务提交时不会进行刷盘操作，而是将redo log写入内存中的缓冲区，等待后台线程定期刷新到磁盘。这种情况下，系统崩溃可能会丢失一秒钟的数据。
● 当innodb_flush_log_at_trx_commit设置为1时，表示每次事务提交时都会将redo log刷新到磁盘。这种情况下，系统崩溃不会丢失数据，但性能可能会受到影响，因为每次提交事务都需要进行磁盘I/O操作。
● 当innodb_flush_log_at_trx_commit设置为2时，表示每次事务提交时只将redo log写入内存中的缓冲区，然后由后台线程定期刷新到磁盘。这种情况下，相对于每次提交都进行刷盘操作，可以减少磁盘I/O操作次数，提高性能。
总的来说，Redolog的刷盘操作是为了保证数据的安全性和持久性，具体的刷盘时机由数据库管理系统自动管理。在InnoDB存储引擎中，可以通过innodb_flush_log_at_trx_commit参数来控制redo log的刷盘时机。