MYSQL八股-1

1.如何定位慢查询？

在MYSQL的配置文件（/etc/mysql/my.cnf）中配置

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

配置结束后重启MYSQL

systemctl restart mysqld.service

配置完毕之后，通过以下指令重新启动MVSOL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log

当页面加载过慢或者压测响应时间过长时，我们可以使用在MySQL中提供了慢日志查询的功能，可以在MySQL的系统配置文件中开启这个慢日志的功能，并且也可以设置SQL执行超过多少时间来记录到一个日志文件中,只要SQL执行的时间超过了2秒就会记录到日志文件中，我们就可以在日志文件找到执行比较慢的SQL了，当然也可以使用运维工具skywalking或者调试工具arthas查看。

2.如何分析一个SQL语句执行慢的原因？

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	filtered	rows	Extra

type 这条sql的连接的类型包括：

1. NULL:不访问任何表查询（select 'A'）
2. system:查询系统中的表
3. const:根据主键查询
4. req_ref:当主键或唯一键索引的相关列并联接使用时(联表查询),
5. ref:非唯一索引查询
6. range:范围查询
7. index:索引树扫描
8. all: 全盘扫描

all类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的. 而 index类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 all类型的稍快.

如果一条sql执行很慢的话，我们通常会使用mysql自动的执行计划explain来去查看这条sql的执行情况，比如在这里面可以通过key检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，第二个，可以通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了可以尝试添加索引或修改返回字段来修复。

3.了解过索引吗？

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，B+Tree索引，最常见的索引类型，大部分引擎（InnoDB,MyISAM,Memory）都支持B＋树索引
Hash索引，底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询

1.索引的优劣势?

优势：提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
劣势：索引列也是要占用空间的。
索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

2.索引的底层数据结构是什么？

2.1了解B树和B+树的区别？

在B+树中，非叶结点不含有该关键字对应记录的存储地址,可以使一个磁盘块可以包含更多个关键字，使得B+树的阶更大，树高更矮读磁盘次数更少，查找更快。

查找方式：
B树不支持顺序查找。查找成功时，查找速度“不稳定”,可能停在最下一层结点。

B+树查找成功或失败都会到达任何一层结点，查找速度稳定。支持顺序查找。

2.2为什么InnoDB存储引擎选择使用B+tree索引结构?

相对于二叉树，层级更少，搜索效率高;
对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低;
相对Hash索引，B+tree支持范围匹配及排序操作，Hash适合等值查询,还存在哈希碰撞问题 ;

二叉树： 树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树： 树的高度随着数据量增加而增加，IO代价高。

3.什么是聚集索引，什么是二级索引？

聚簇（聚集）索引主要是指数据与索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个一般情况下主键在作为聚簇索引的。
非聚簇索引值的是数据与索引分开存储，B+树的叶子节点保存对应的主键，可以有多个，一般我们自已定义的索引都是非聚簇索引。

3.1什么是回表查询？

回表查询与聚集索引和二级索引是有关系的，回表的意思就是通过二级索引找到对应的主键值，然后再通过主键值找到聚集索引中所对应的整行数据，这个过程就是回表。

3.2什么是覆盖索引？

覆盖索引是指一个索引包含了查询中所需的所有列，而不仅仅是用于筛选和排序的列。当一个查询可以完全通过索引的数据就能满足，而不需要回表查询实际的数据行时，就称为使用了覆盖索引。

使用覆盖索引可以带来一些性能上的优势：

1. 减少 I/O 操作： 覆盖索引避免了不必要的回表查询，从而减少了对磁盘的 I/O 操作，提高了查询性能。

2. 减小索引的大小： 覆盖索引只包含查询中需要的列，因此相对于包含全部列的索引来说，它的大小会更小，减小了存储空间的需求。

3. 减少锁的竞争： 由于覆盖索引减少了回表查询，可以减小锁的竞争，提高并发性能。

尽量使用覆盖索引，减少select*。

如下图所示，第一条SQL使用的是聚集索引，第二条SQL使用的是二级索引，但是使用了覆盖索引，不需要回表，查找效率高，第三条SQL使用的是二级索引，查询的字段在二级索引中有未找到的数据，此时需要回表查询。（如果想让第三条SQL不走回表查询，此时可以建立联合索引）

 3.3MYSQL超大分页如何处理？

这里在子查询的*变成了id，引擎拷贝整行数据要比拷贝一个字段开销大得多，把这里的id换成无索引字段一样要比*快

超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决
先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了，因为查询id的时候，走的覆盖索引，所以效率可以提升很多。

3.4什么是前缀索引？

当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

前缀索引进行查询，如果查询条件可以充分利用前缀索引，并且前缀索引是覆盖索引，那么就不需要进行回表查询，因为所有需要的信息都包含在索引中。

当前缀冲突时，不论是不是覆盖索引，都会进行回表查询进行数据比对。

3.5什么是索引下推？

索引下推ICP，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。

• 有了索引下推优化，可以在减少回表次数
• 在InnoDB中只针对二级索引有效

官方文档中给的例子和解释如下：

在 people_table中有一个二级索引(zipcode，lastname，firstname)，查询是SELECT * FROM people WHERE zipcode=’95054′ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;

• 如果没有使用索引下推技术，则MySQL会通过zipcode=’95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断数据是否符合条件(逐一进行回表扫描，去聚簇索引找到完整的行记录，server层再对数据筛选)
• 如果使用了索引下推技术，则MYSQL首先会返回符合zipcode=’95054’的索引，然后根据lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接过滤掉。(存储引擎直接再联合索引里按照lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%过滤。按照过滤后的数据再一一进行回表扫描。)

4.什么时候使用索引？

• 1.针对于数据量较大，且查询比较频繁的表建立索引。
• 2. 针对于常作为查询条件 (where)、排序(order by)、分组(group by)操作的字段建立索引。
• 3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
• 4.如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
• 5.要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
• 6.如果索列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个
• 7.索引最有效地用于查询。

有一个大前提，就是表中的数据要超过10万以上，我们才会创建索引，并且添加索引的字段是查询比较频繁的字段，一般也是像作为查询条件，排序字段或分组的字段这些。
还有就是，我们通常创建索引的时候都是使用复合索引来创建，一条sql的返回值，尽量使用覆盖索引，如果字段的区分度不高的话，我们也会把它放在组合索引后面的字段。
如果某一个字段的内容较长，我们会考虑使用前缀索引来使用，当然并不是所有的字段都要添加索引，这个索引的数量也要控制，因为添加索引也会导致增删改的速度变慢。

4.1一条查询语句可以走多个单列索引吗?

GPT:

一条查询语句通常只能使用一个索引，这个索引被称为“优化器选择的主索引”或者“最佳索引”。这是因为在执行查询时，MySQL 会选择一个最适合的索引来提高查询性能。

然而，有一些情况下，MySQL 的优化器可能会选择合并多个单列索引来执行查询，这被称为“索引合并”（Index Merge）。索引合并通常发生在以下两种情况下：

1. 如果查询包含多个 OR 条件，并且每个条件可以使用不同的单列索引，MySQL 的优化器可能会考虑将这些单列索引合并使用。
2. 当使用 UNION 运算符时，每个子查询可以使用不同的索引，MySQL 的优化器可能会考虑将这些索引合并使用。

尽管索引合并是一种可能的优化策略，但并非在所有情况下都会发生。MySQL 的优化器会根据查询的复杂性、表的大小、索引的统计信息等因素进行决策。在实际应用中，通常通过适当的索引设计和查询优化来提高性能。

4.2存在联合索引，什么时候走索引，什么时候不走索引?

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列（与sql语句中的查询顺序无关）。如果跳跃某一列，索引将部分失效(后面的字段索引失效)，最左前缀法则的根本原因就是索引树的节点数据按照一定顺序排列

这条sql语句符合最左前缀法则

这条SQL语句，跳跃一列，索引将部分失效(后面的字段索引失效)

但是MySQL 8.0.13 版本中，对于range查询引入了索引跳跃扫描（Index Skip Scan）优化，支持不符合组合索引最左前缀原则条件下的SQL

联合索引是否执行sql语句中的查询顺序无关，mysql的查询优化器会进行优化

5.索引失效的情况？ 

• 在索引列上进行运算操作，索引将失效。  explain select * from tb user where substring(phone,10,2) = ‘15’；
• 字符串类型字段使用时，不加引号，索引将失效。
• 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

失效原因是因为以%开头的模糊匹配无法确定索引列的起始位置，所以无法利用索引进行快速查找。解决方法是尽量避免使用以%开头的模糊匹配（由于 B-Tree 索引的结构，它是从左到右逐级比较的，而以 % 开头的模糊匹配无法提供一个确切的起始点），或者使用覆盖索引（只包含索引列的查询）。

• 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的所偶索引都不会被用到。
• 数据分布影响，如果MySQL评估使用索引比全表更慢，则不使用索引。
• 联合索引中，出现范围查询(>,<)，！=，范围查询右侧的列索引失效，此时改用>=或者<=可以规避

比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的，use index（建议（sql会进行评估接不接受你的建议）使用某个索引（from use index(idx_name)）），ignore index（忽略），force index（强制）。

索引在使用的时候没有遵循最左匹配法则，模糊查询，如果%号在前面也会导致索引失效。如果在添加索引的字段上进行了运算操作或者类型转换也都会导致索引失效。如果使用了复合索引，中间使用了范围查询，右边的条件索引也会失效所以，通常情况下，想要判断出这条sql是否有索引失效的情况，可以使用explain执行计划来分析

4.为什么MySQL不建议使用UUID和不建议使用做主键?

UUID：

1. 性能问题：

   • 随机性： UUID 是全局唯一标识符，通常是一个128位的数值，其中包含很大一部分是随机性。这导致新的 UUID 主键值在插入表时可能会导致页面分裂（Page Split）和随机 I/O，这对于索引的性能是不利的。
   • 索引效率： UUID 作为主键的时候，其插入数据的随机性可能导致索引树频繁重建，影响性能。相比之下，自增主键（如使用 AUTO_INCREMENT）的插入是有序的，有助于减小页面分裂的风险，提高索引效率。

2. 存储空间：

   • UUID 占用空间较大： 由于 UUID 是128位的，相比整型（INT）或长整型（BIGINT）类型，它会占用更多的存储空间。在大量数据的情况下，这可能导致更大的存储需求和更慢的查询性能。

3. 查询效率：

   • 范围查询效率低： 如果在 UUID 列上进行范围查询，由于其随机性，可能导致磁盘 I/O 操作增多，性能下降。相反，自增主键上的范围查询更容易进行优化。

4. 可读性和调试：

   • 不易读： UUID 不如整数型主键易读。在调试和日志分析时，使用整数型主键更方便。

雪花算法：是一种生成唯一标识符的算法，通常用于分布式系统中，但在 MySQL 中作为主键有一些潜在的问题：

1. 不适用于数据库主键： 雪花算法通常被设计为在分布式环境中生成唯一标识符，而不是作为数据库表的主键。数据库主键需要保证唯一性，并且通常需要支持一些数据库特有的功能，如自动增长、约束等。雪花算法生成的标识符可能无法满足数据库主键的一些要求。

2. 不支持事务： 雪花算法生成的标识符通常是按照时间顺序递增的，这使得在分布式系统中生成的标识符并不总是连续的。在 MySQL 中，主键通常是支持事务的，而雪花算法生成的标识符可能无法满足事务隔离的要求。

3. 不易维护： 雪花算法生成的标识符通常包含了时间戳等信息，这样的标识符可能会占用较多的存储空间。在数据库中，一个简单的整数自增主键可能更容易维护和查询。

4. 高并发问题： 在极高并发的情况下，雪花算法生成的标识符可能会因为并发生成而出现冲突，需要额外的处理来保证唯一性。

UUID 作为主键有一些缺点，但在某些场景下仍然有其优点，比如分布式系统中的唯一标识符。选择使用何种主键类型应该根据具体的应用需求和场景来决定，雪花算法在分布式系统中有其优势。在 MySQL 中，如果性能是首要考虑因素，而不需要全局唯一标识符，那么使用自增主键可能是一个更好的选择。

5.谈谈SQL优化的经验？

• 1.表的设计优化，数据类型的选择

比如，在定义字段的时候需要结合字段的内容来选择合适的类型，如果是数值的话，像tinyint、int、bigint这些类型，要根据实际情况选择。如果是字符串类型，也是结合存储的内容来选择char和varchar或者text类型

• 2.索引优化，索引创建原则3-4-0

数据量大，覆盖索引，前缀索引

• 3.sql语句优化，避免索引失效，避免使用select *3-5

插入数据：insert : 批量插入、手动控制事务、主键顺序插入。大批量插入: load data local infile

主键优化：主键长度尽量短、顺序插入，AUTO_INCREMENT UUID页分裂

order by优化:

using index: 直接通过索引返回数据，性能高

using filesort: 需要将返回的结果在排序缓冲区排序

group by优化：索引，多字段分组满足最左前缀法则

limit优化：覆盖索引 + 子查询3-3-3

count优化
性能: count(字段) < count(主键 id) < count(1)<count(*）

update优化：尽量根据主键/索引字段进行数据更新，更新条件无索引或索引失效行锁会升级为表锁

比如SELECT语句务必指明字段名称，不要直接使用select*，还有就是要注意SOL语句避免造成索引失效的写法;如果是聚合查询，尽量用union all代替union，union会多一次过滤，效率比较低;如果是表关联的话，尽量使用inner join，不要使用用left join right join，如必须使用一定要以小表为驱动

• 4.主从复制、读写分离，不让数据的写入，影响读操作

如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构。读写分离解决的是数据库的写入，影响了查询的效率。

• 5.分库分表

1.MySQL数据库cpu飙升到500%的话他怎么处理？(toude)

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。

如果是 mysqld 造成的， show processlist，查看当前正在运行的 MySQL 进程列表，找到消耗高资源的 SQL 查询语句，关注执行时间、状态等信息，看看执行计划是否准确，使用 EXPLAIN 命令查看查询语句的执行计划，确保索引被正确使用， index 是否缺失，或者实在是数据量太大造成。如果数据量过大，考虑优化查询语句或调整数据库配置以适应当前数据规模。

一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的会话连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

以下是一些可能导致连接数激增的常见原因和相应的处理方法：

1. 连接池问题： 应用程序连接池配置不合理，导致频繁地创建和释放连接。可以调整连接池的配置，确保连接的复用，避免频繁地创建和关闭连接。

2. 连接泄漏： 应用程序没有正确地释放数据库连接，导致连接数逐渐增加。应该检查应用程序代码，确保每次使用完数据库连接后都能正确释放。

3. 连接数限制： MySQL 服务器允许的最大连接数可能被超过，导致新的连接无法建立。可以调整 MySQL 服务器的最大连接数配置。

4. 应用程序并发请求： 应用程序中有大量并发的数据库请求，导致连接数激增。可以优化应用程序代码，减少数据库请求的并发度，或者考虑增加数据库服务器的硬件资源。

5. 数据库资源竞争： 大量并发连接导致数据库服务器的资源竞争，如锁等。可以通过优化查询、增加索引等方式降低数据库资源竞争。

6. 网络问题： 突然的连接数激增可能与网络问题有关。可以检查网络连接是否存在异常，例如是否有大量的连接失败导致重试。