MysqL（二：sqL调优）

SQL调优

在数据库软件级别影响性能能在有几个重要因素，例如表结构、查询语句和数据库配置。软件级别的因素会导致硬件级别的 CPU 和 I/O 操作。在优化数据库性能时，首先要学习软件级别的规则，当您成为专家时，就可以考虑如 CPU 周期和 I/O 层面的操作。但在真实的企业中，通常数据库遇到瓶颈首先考虑换一个高性能的存储设置，比如把机械硬盘换成 SSD，再考虑软件层面，最后考虑操作系统层面的优化。

优化索引

在前面的学习中我们已经知道了Innodb存储引擎使用B+树来进行组织数据，提高了存储的效率。如何利用索引来编写更高效的语句，是我们所要讨论的一些话题。

使用主键查询

当我们进行主键查询的时候，就可以看到一百万条数据的查询效率可以控制在10ms以内，说明了主键查询是非常快速的。

我们可以通过命令行进行压测，结果可以如图所示。

mysqlslap -uroot -p123456 --concurrency=100 --iterations=100 --create-schema="topic01" --engine="innodb" --number-of-queries=10000 --query "select id, sn, name, mail, age, gender, class_id from topic01.index_demo where id = 102000;"

可以看到平均值0.533s，最低可以达到0.171秒，最大可以达到1.672。 

非主键查询

当我们查询一个非主键（图中sn）索引，就会出现查询比较慢的情况。当有更多的查询没有进行主键索引查询，效率就比较低下，如果这个键频繁被使用的话，就最好进行建立索引。

mysqlslap -uroot -p123456 --concurrency=30 --iterations=3 --create-schema="topic01" --engine="innodb" --number-of-queries=100 --query "select id, sn, name, mail, age, gender, class_id from topic01.index_demo where sn = '102000';"

就可以看到没有进行主键查询的查询效率就很低，就需要进行优化。

Explain（执行计划）

explain并不会真正执行，而是进行分析sql语句执行情况。

explain select id, sn, name, mail, age, gender, class_id from index_demo where id = 1020000\G;

使用主键进行查询👇 

非主键进行查询👇 

执行字段说明 

select_type

实际上就是select的查询类型。

table

<unionM,N>：表示合并查询用到的表，M 和 N 是 id 列的值

<subqueryN>：表示子查询用到的表，N 是 id 列的值

possible_key

这个表示在查询的时候可能会用到的索引，只是可能，因为可能一个列会创建多个索引。真正的索引是key列，key_len是key的长度。在优化的时候重点关注key列。

ref

ref列表示where=？这里的？表示的内容，如果是const就是表示常数也就是类似于1,2,3。

rows

rows表示查询需要检查的行数，数据越小效率越高。

flitter

flitter表示对数据进行过滤，100%就是代表没有对数据过滤，值越大表示效率越高。

type

我们在优化sql语句的时候最终的目标就是把性能进一步提升，最后提升到更高级别。实际上不为ALL就是提升了性能。

创建索引语句，如果我们经常使用一个查询的话，我们就可以对他进行创建索引，如果该值唯一的话就可以创建唯一索引。

CREATE INDEX idx_sn ON index_demo(sn); 
-- 创建唯一索引（和ALTER TABLE添加唯一索引功能类似，但语法结构不同）
CREATE UNIQUE INDEX idx_unique_sn ON index_demo(sn); 

创建索引之后就可以看到type变成const，这时候查询就变得快了不少。

• system：system实际上只会出现在MYISAM引擎中，并且数据库中只有一行数据的时候才会触发。
• const（只能主键索引）：当查询条件通过主键索引或者唯一索引与常量进行比较的时候，结果最多只有一个匹配的行。（如where id=1）
• eq_ref：是除了system和const之外的最高效的表连接查询类型，应用于多表查询的场景，表A的一条记录在表B中只有一条与之匹配，一对一的关系，其中一个一条记录对应另一张表的唯一一条记录，并且where条件后面跟着是两张表进行关联的语句（都是唯一索引关联）。并且主键Id=“唯一值”。 
• ref（普通索引查询一个也是ref）：进行查询之后的内容不唯一，可能会出现很多条数据，不是唯一索引，普通索引就有可能出现ref的情况，返回的可能是结果集，比如查询班级Id为1的情况，这样就是有可能出现多条数据。
• ref_or_null（有索引但是字段可以为空）：可以对null进行检索，ref就只能对非空进行操作，但是ref_or_null可以检索其中加了索引但是内容中有空的字段。（有空字段才能检索到，如果字段是可以非空但是没有空内容，就还是ref）。
•  index_merge：在查询中使用了多个索引，or两边是单独索引，最终通过不同的索引检索数据，对结果集进行合并。简单点就是where 索引A=xxx and 索引B=XXX，返回的就是index_merge。用两个索引查询了两次不同的数据，匹配了两次。
• range：使用索引列对范围进行查询，比如<和>或者between和like，in模糊查询，id值在区间内。
• index：对整个索引树进行扫描而不是全表扫描 ，会稍微快一点，对索引进行排序，比如对索引进行order by操作。
• ALL：全表扫描才能匹配才能找到所在的行，要极力避免，简单的例子就是对没有建立索引的列进行查询。

如果对name like %xxx，就代表百分号可以代表任何东西，那就要进行全盘扫描，那索引就会失效。

主键索引（聚簇索引）叶子节点保存完整数据，单独创建的索引只保存了主键值和索引值。在表中每创建一个索引，都会生成一个对应的索引树。 

Extra

Extra如果出现using filesort（使用文件排序）和using temporary（使用临时表排序）的时候，将对查询有比较大的影响。

对非索引无序的数据进行排序的时候，会把数据加载到临时表，临时表如果放不下了，就会使用文件排序。需要文件io，此时开销很大，很需要优化（就是在查询的列上创建一个索引）。当使用非索引列进行分组的时候，会用临时表进行排序，优化时也可以为分组列加索引。（不知道分组情况，要先把数据加载到文件中）。

当我们进行对一个非索引列gender进行group by，此时就会把全部数据加载到文件中，然后再进行分组。有索引就是null。

合并查询union也会产生索引。

using where（where后面字段没建立索引）：是使用非索引进行检索数据的时候进行全盘扫描，没有建立索引的情况，当对索引树进行范围扫描的时候，也会变成using where。如果使用了索引，那就是查找索引树，没有的话就是扫描全表。

using index（建立索引，索引覆盖）：覆盖索引，是一个高效查询，当我们生成索引的时候，就会生成一个索引树，叶子节点。主键索引中保存索引的行数据，普通索引只会保存索引列和主键值 。找到普通索引后，再查找主键索引。

null：where后面跟着索引就可以，并且没有覆盖索引。

主键索引👇

普通索引👇

当查询的结果在索引树中，就可以直接返回结果，发生索引覆盖，这时候EXTRA就是using index。如果不在就在主表中拿数据，此时Extra为null。

范围查询

在where后面查找内容的时候比如可以class_id<14，可以给class_id创建一个索引，索引实际上就是b+树排序，就可以快速查找。

单步范围（了解一下）

mysql内部扫描，内部引擎优化。

• 对于 BTREE 和 HASH 索引当使用 =、<=>、IN () 操作符时，索引部分与常量值的比较是一个范围条件。where value in（10,20）等。
• 另外对于 BTREE 索引当使用 >、<、>=、<=、BETWEEN、<> 操作符时，索引部分与常量值的比较是一个范围条件
• LIKE 的参数是一个不以通配符开头的常量字符串也是一个范围条件。如like aaa%。
• 对于所有索引类型，多个范围条件用 OR 或 AND 组合形成一个范围条件。

mysql优化优化器如果不能构成条件将被删除，产生重叠条件将被合并，产生空范围将被删除（用true替换，表示实现单步范围）。

索引合并优化

当我们创建普通索引后，不考虑覆盖索引的情况下，就会出现查到索引，然后去查其他的值要通过主键id，那么主键id被用来回表查询就会出现很多随机IO。索引合并就是通过一个表进行多个索引扫描，将相同的主键id取交集或者并集后再次回表查询。

如上第一个，会先查出key1和key2列对应的主键id，然后拿着他们查出来的id后进行回表查询，第二个会先查出key1和key2对应的列，并且最后进行和non_key进行过滤。这边等号后边必须是常量值。也可以是范围查询比如key1>10这种的（索引下推优化）。反正他mysql内部就是会帮助优化。

索引下推优化：

• 无索引下推时：存储引擎先通过name LIKE '张%'找到所有姓 “张” 的索引项，返回主键给服务器层，服务器层回表后再过滤age > 20；
• 有索引下推时：存储引擎在找到姓 “张” 的索引项后，直接在索引中过滤age > 20，只返回符合条件的主键，减少回表次数。

外连接优化

 外连接包括左连接和右连接，mysql不支持全外连接。

左外连接

• 对于一个左外连接，A LEFT JOIN B join_specification，MySQL 在实现的过程中有如下规则:
  • 表 B 依赖于表 A 以及表 A 依赖的所有表。
  • 表 A 依赖于在 LEFT JOIN 条件中使用的所有表 (表 B 除外)，在后面where条件比如join b on a.id=c.id，此时就依赖于c表。
  • LEFT JOIN 条件用于决定如何从表 b 中检索数据行 (不使用WHERE子句中的任何条件)。
  • 执行所有标准连接优化，先读取依赖的表再读取当前表，如果存在循环依赖，则报错。
  • 执行所有标准的 WHERE 优化，比较多的where条件可能会被优化成一个比较简单的where条件。
  • 如果表 A 中有一行匹配WHERE条件，但表 B 中没有匹配条件的数据行，则生成一个额外的 B 行，所有列都设置为NULL。左表有相应的数据，右表没有相应的数据就用null填充。

表B依赖于表A，表A如果依赖于其他的表的话，B也随着依赖其他表，mysql内部优化之后，发送到存储引擎，都会变成左连接。左连接把空行过滤掉就会优化成内连接。内连接效率更高。

IS NULL优化

在查询的时候不能用某字段=null，最好要用某字段 is null才可以，或者<=> null，就是不能用等号，如果用null的话就会返回空字符串。

非空列查询null值，会返回的extra是impossible where，因为非空列永远不可能是空值，永远也返回不了的内容。如果发生这种情况，mysql不会把内容带到存储引擎层。

ORDER BY

mysql可以利用索引进行orderby排序，当不能利用索引的时候，只能通过文件排序进行处理文件的顺序。

当我们创建复合索引（key1，key2）的时候，先创建哪个，就先对哪个列进行排序，依次进行排序。后面列的排序是在前面列排序的基础上进行排序。升序降序也可以。

举个例子，假设创建了一个复合索引idx_col1_col2，列的顺序是col1在前，col2在后。当执行ORDER BY col1, col2这样的排序操作时，因为排序列的顺序和复合索引的列顺序一致，数据库就会使用这个复合索引来提高排序的效率；但如果是ORDER BY col2, col1，排序列顺序和复合索引列顺序不一致，可能就无法有效使用该复合索引来优化排序了。

 如果⬆️这样使用不同索引，非联合索引，可以考虑创建复合索引。

⬆️会导致索引不连续，导致索引失效。

⬆️orderby的键和where后面跟着的不一样。

索引失效

数据库已经创建了索引，但是由于使用不当，导致查询没有走索引树，导致走全表扫描。

复合索引没有根据最左匹配原则

就好比我们查字典，查到首字母之后，后面还有辅音，只只有辅音没有首字母是查不到对应的字的。所以查询的时候必须从创建索引的顺序进行查询。

比如创建索引的顺序是A，B，C，但是这时候来个select * from table where B=1；就会出现索引失效。

在where中有or并且另外一个没有索引

虽然id是一个索引，但是age不是一个索引，就要进行全表扫描，全表扫描就不会使用索引。 

复合索引中第一个使用范围的条件或者不以%开头的模糊查询之后的列不使用索引

比如此时有两个索引age和class_id。

select * from index_demo where age < 18 and class_id > 1；

此时即使都有索引，但是只走前面的索引，由于前面的是范围查询，此时会出现索引下推，将通过前面age查出来的内容后在存储引擎实现查到另外一个内容。

like以%开头

比如like %xxx，说明是全表扫描，主要是没办法匹配前面，开头字符不确定，只能进行全表扫描。

但是实际上我们使用like xxx%，即使有索引，也很有可能不走索引，这和mysql内部优化机制有关系，但是可以from table force index （索引名）来进行强制使用索引进行查找。

隐式转化时，字符串没有加单引号

select * from index_demo where sn = 1020000；

如是字符串，但是where查询并没有使用单引号进行括号，而直接输入数字，就会发生隐式转化。要为索引传入一个相匹配的值。

索引列进行运算

select * from index_demo where id + 1 = 1000020；

或者

select * from index_demo where length(name) = 11 ；

不能对id加1之后进行查询，这样就找不到，或者进行函数运算，生成一个和索引不相干的东西。

不等号进行查询

使用<>不等号进行运算，此时就不会走索引，查询范围大，会进行全表扫描。数据量少的时候常常mysql不适用索引。

索引使用原则

• 每张表必须有主键，推荐 BIGINT 类型且自增
• DISTINCT，ORDER BY，GROUP BY，JOIN 条件，WHERE 条件的列加索引
• 对频繁进行数据操作的表，不要建立太多索引，因为维护索引也需要很大的成本
• 使用复合索引时遵守最左原则
• 多表 JOIN 有确定条件时 WHERE class_id = 1，可以分成多个单表查询，然后在程序中合并
• 避免在重复值太多的列上建立索引，比如大部分都是相同的值，查了索引之后还会回表，那么还不如全表扫描
• 使用指定的索引 use index (index_name)，force index (index_name)
• 创建索引时可以指定 ASC DESC，create index index_name (col desc, col1 asc);
• 创建一个复合索引 (a, b, c)，相当于同时创建了 (a)，(a,b)，(a,b,c)
• 创建索引之前，确保当前数据库实例没有未提交的大事务，防止数据锁死
• 对不常使用的索引进行清理
• 删除无用的索引，避免对执行计划造成负面影响

总结（常见面试题）

1. 数据库优化要考虑的层面因素

• 硬件层面：包括服务器的 CPU、内存、磁盘（如使用 SSD 提升 IO 性能）等硬件配置，硬件性能不足会成为数据库性能瓶颈。
• 系统与存储引擎层面：操作系统的参数设置（如 Linux 的文件句柄数、内存分配等），以及数据库存储引擎的选择（如 InnoDB 适合事务型场景，MyISAM 适合读多写少场景）。
• 数据库结构层面：表结构设计（如合理的字段类型、范式化与反范式化的平衡）、索引设计等。
• SQL 语句层面：SQL 语句的编写质量，如是否有低效的查询（全表扫描、不必要的连接等）。
• 缓存层面：数据库自身缓存（如 MySQL 的查询缓存）以及应用层缓存的使用，减少对数据库的直接访问。
• 并发与锁层面：并发控制机制（如锁的类型、粒度），减少锁竞争，提升并发性能。

2. 什么是索引

索引是数据库系统中用于快速查找数据的数据结构，它就像书籍的目录，通过预先对数据的某些列建立特定的数据结构，使得数据库在执行查询操作时，能快速定位到目标数据所在的位置，而无需扫描整个表。

3. 索引的作用

• 大大加快数据的查询速度，减少数据库的 IO 操作。
• 加速表与表之间的连接操作。
• 在分组（GROUP BY）和排序（ORDER BY）操作时，减少排序和分组的时间。

4. 索引用到的数据结构

• B + 树：是关系型数据库（如 MySQL 的 InnoDB、MyISAM 存储引擎）中最常用的索引数据结构。B + 树的所有数据都存储在叶子节点，且叶子节点之间通过指针连接，适合范围查询，同时具备较好的查询、插入和删除性能。
• 哈希表：部分数据库或场景会使用哈希索引，它通过哈希函数将键映射到哈希表中的位置，查询速度快（O (1) 时间复杂度），但不支持范围查询。
• 跳表：在一些分布式数据库或特定存储引擎中使用，通过多层索引结构，实现高效的查询。

5. 索引如何提升查询效率

以 B + 树索引为例，它将数据按顺序组织，查询时无需扫描整个表，而是从根节点开始，根据索引键的值快速遍历到叶子节点，找到对应的数据行（或数据行的主键）。这样就将全表扫描的线性时间复杂度（O (n)）降低到对数时间复杂度（O (log n)），极大减少了 IO 次数和数据扫描量。

6. 什么时候应该创建索引

• 表的数据量较大，且经常需要进行查询操作。
• 列经常作为查询条件（WHERE 子句中的列）、连接条件（JOIN 子句中的列）、分组（GROUP BY）或排序（ORDER BY）的列。
• 列中的数据具有较高的区分度（即不同值较多），如身份证号、手机号等列，适合创建唯一索引或普通索引；如果列重复值很多（如 “性别” 列只有男、女两个值），则不适合创建索引。

7. 在哪些列上创建索引

• 主键列：主键列默认会创建索引，用于唯一标识表中的每一行数据。
• 频繁作为查询条件的列：如用户表中的 “用户名”“手机号” 列，订单表中的 “订单号” 列等。
• 与其他表进行连接的列：即外键列，如订单表中的 “用户 ID” 列（与用户表的 “ID” 列连接）。
• 经常用于分组（GROUP BY）和排序（ORDER BY）的列：如销售表中的 “日期” 列，经常用于按日期分组或排序。

8. 索引越多越好吗？为什么？

不是。因为索引虽然能加快查询速度，但会带来以下额外开销：

• 存储开销：每个索引都需要占用一定的磁盘空间。
• 写入开销：当对表进行插入、更新、删除操作时，不仅要修改表中的数据，还要维护相关的索引结构，导致写入操作的速度变慢。
• 优化器开销：数据库的查询优化器在选择查询计划时，需要考虑的索引越多，决策的时间可能越长。

9. 如何查看索引是否生效

可以使用EXPLAIN语句来分析 SQL 查询的执行计划，查看possible_keys（可能使用的索引）和key（实际使用的索引）列。如果key列显示了对应的索引名称，说明该索引生效了；如果key列为NULL，则说明索引未生效。

10. 执行计划（Explain）及作用

EXPLAIN是数据库提供的一个命令（如 MySQL、PostgreSQL 等都支持），用于分析 SQL 语句的执行计划。它的作用是展示数据库执行该 SQL 语句时的具体步骤，包括如何连接表、使用了哪些索引、扫描了多少行数据等，帮助开发者识别 SQL 语句的性能瓶颈，从而进行优化。

11. 执行计划结果中各列的含义

以 MySQL 的EXPLAIN结果为例，主要列的含义如下：

• id：SELECT 查询的标识符，用于标识多个 SELECT 语句的执行顺序。
• select_type：SELECT 的类型，如SIMPLE（简单查询，无联合、子查询）、PRIMARY（主查询）、SUBQUERY（子查询）、UNION（联合查询中的子查询）等。
• table：当前行正在访问的表名。
• type：表示表的连接类型，是判断查询性能的重要指标，如system、const、eq_ref、ref、range、index、ALL等，从左到右性能依次变差。
• possible_keys：查询可能使用的索引。
• key：查询实际使用的索引。
• key_len：使用的索引长度，可用于判断复合索引的使用情况。
• ref：与索引比较的列或常量。
• rows：MySQL 估计执行查询时需要扫描的行数。
• Extra：包含额外的信息，如Using index（使用覆盖索引）、Using where（使用 WHERE 条件过滤）、Using temporary（使用临时表）、Using filesort（使用文件排序）等。

12. 执行计划的type列的含义及包含内容

type列表示 MySQL 在表中找到所需行的方式，即表的连接类型，反映了查询的效率。常见的类型（从优到劣）有：

• system：表中只有一行数据（系统表），是const的特殊情况。
• const：通过主键或唯一索引进行等值查询，最多返回一行数据，查询效率极高。
• eq_ref：在连接查询中，从表中通过主键或唯一索引获取每行数据，只返回一行匹配结果。
• ref：通过普通索引进行等值查询，可能返回多行。
• range：对索引进行范围查询，如BETWEEN、>、<等操作。
• index：扫描整个索引来获取数据，比ALL好（因为索引通常比表数据小）。
• ALL：全表扫描，需要遍历表中所有数据行，性能最差，应尽量避免。

13. type列中显示const的意味

表示查询通过主键索引或唯一索引进行等值匹配，且最多只返回一行数据，这是非常高效的查询类型，说明查询能快速定位到目标数据。

14. Extra列中显示using index的意味

表示查询使用了覆盖索引，即查询所需的所有列都包含在索引中，无需再回表（从数据行中获取其他列数据），大大提高了查询效率。

15. 如何使用EXPLAIN命令分析查询执行计划及优化举例

• 使用方法：在 SQL 语句前加上EXPLAIN关键字，执行后即可得到执行计划。例如：EXPLAIN SELECT * FROM user WHERE id = 1;。
• 优化举例：
  假设存在表order，字段有order_id（主键）、user_id、order_date、total_amount，且user_id没有索引。执行EXPLAIN SELECT * FROM order WHERE user_id = 100;，发现type为ALL（全表扫描），key为NULL。
  优化方法：为user_id列创建索引（CREATE INDEX idx_user_id ON order(user_id);），再次执行EXPLAIN，会发现type变为ref，key为idx_user_id，rows也大幅减少，查询效率提升。