1 索引
1.1 索引的分类
- 存储形式
- 聚簇索引
- 非聚簇索引
- 数据约束
- 主键索引
- 唯⼀索引
- ⾮唯⼀索引
- 索引列的数量
- 单列索引
- 组合索引
- innoDB可以创建的索引
- 主键索引
- 唯⼀索引
- 普通索引
1.2 索引的数据结构
1.2.1 二叉查找树
1.2.2 btree（b-tree）
1.2.3 b+tree
1.2.4 聚簇索引
1.2.5 非聚簇索引
磁盘IO数：辅助索引3次+获取记录回表3次
覆盖索引和回表查询：
- 回表查询是指在使⽤⾮覆盖索引（Non-Clustered Index）进⾏查询时，当需要获取查询结果所需的数据列不在索引中时，MySQL 需要通过索引的指针回到主索引（Clustered Index）或数据⻚中获取缺失的数据列。
- 在回表查询中，⾸先根据⾮覆盖索引定位到符合查询条件的索引记录，然后通过索引中的指针获取主索引或数据⻚中的相应数据列。这个过程涉及了两次磁盘访问，即⾸先访问索引⻚，再次访问主索引或数据⻚，因此相对于覆盖索引的查询，回表查询会引⼊额外的磁盘读取操作，增加了查询的开销和响应时间。
- 回表查询可能会对查询性能产⽣⼀定的影响，特别是在⼤数据量和⾼并发查询的情况下。因此，为了减少回表查询的开销，可以考虑通过 覆盖索引（Covering Index） 来进⾏优化。
- 覆盖索引（Covering Index）是指在查询过程中，索引包含了查询所需的所有数据列，⽆需回表查询主索引或数据⻚。换句话说，覆盖索引能够直接提供查询所需的数据，⽽不需要再去访问主索引或数据⻚，从⽽提⾼查询性能和效率。
- 在⼀般的索引中，只包含了被索引的列以及主索引的引⽤指针。当执⾏查询时，MySQL ⾸先通过索引定位到符合条件的记录，然后再通过主索引或数据⻚获取缺失的数据列，这个过程被称为回表查询。⽽覆盖索引则避免了回表查询的开销，因为索引本身就包含了查询所需的所有数据列。
- 覆盖索引的好处主要体现在以下⼏个⽅⾯：
- 提⾼查询性能：由于覆盖索引能够直接提供查询所需的数据，减少了磁盘的随机访问和额外的回表
查询操作，从⽽加快了查询的执⾏速度。 - 减少磁盘 I/O：回表查询需要进⾏额外的磁盘读取操作，⽽覆盖索引可以减少磁盘 I/O 操作，降低
系统的磁盘负载。 - 减少内存消耗：覆盖索引可以减少需要加载到内存中的数据量，节省了内存的使⽤，提⾼了查询的效率。
- 提⾼查询性能：由于覆盖索引能够直接提供查询所需的数据，减少了磁盘的随机访问和额外的回表
- 要创建覆盖索引，需要选择适当的索引列，以包含查询语句中涉及的所有列。这需要综合考虑查询的需求、数据列的选择性和索引的⼤⼩等因素。需要注意的是，创建过多的覆盖索引可能会增加索引的维护成本和存储空间占⽤。
总之，覆盖索引是⼀种优化⼿段，通过索引包含查询所需的所有数据列，避免了回表查询，提⾼了查询的性能和效率。使⽤覆盖索引可以在适当的情况下优化查询，但需要权衡索引的设计和维护成本。
2 查询优化
2.1 Mysql查询优化器简介
进⾏查询优化⾸选需要了解查询优化器，查询优化器是MySQL中的⼀个关键组件，它负责分析查询语句并⽣成最优的查询执⾏计划。
查询优化器根据查询的复杂度、表的统计信息和索引等因素，评估不同的执⾏计划，并选择代价最低的执⾏计划来执⾏查询。
查询优化器的⼯作原理和相关概念如下：
- 查询优化器的⼯作流程：
- 解析查询语句：查询优化器⾸先会对查询语句进⾏解析，将其转化为内部的查询树或逻辑表达式。
- 查询重写：优化器可能对查询进⾏重写，以优化查询结构和查询条件。
- 查询优化：优化器根据统计信息、索引和其他相关信息，⽣成不同的执⾏计划，并评估每个执⾏计划的代价。
- 选择最优执⾏计划：优化器选择代价最低的执⾏计划，并⽣成执⾏计划的执⾏指令。
- 执⾏查询：MySQL的执⾏引擎根据优化器⽣成的执⾏计划，执⾏查询并返回结果。
- 查询优化器的优化过程：
- 查询预估：优化器根据统计信息和查询条件预估查询结果集的⼤⼩，以决定使⽤哪个执⾏计划。
- 索引选择：优化器根据索引的选择性和列的选择性，决定是否使⽤索引以及使⽤哪个索引。
- 连接顺序选择：对于涉及多个表的查询，优化器选择合适的表连接顺序，以减少中间结果集的⼤⼩和连接操作的代价。
- ⼦查询优化：优化器尝试将⼦查询转化为连接操作或应⽤优化的技术，以减少⼦查询的执⾏次数和开销。
- 重写查询：优化器可能对查询进⾏重写，使⽤等价的查询结构，以改进查询的执⾏效率。
- 统计信息的使⽤：
- 表统计信息：优化器使⽤表的统计信息，如⾏数、列的唯⼀值数量等，来估计查询的选择性和代价。
- 索引统计信息：优化器使⽤索引的统计信息，如索引的选择性、平均数据⻚的⼤⼩等，评估索引的使⽤代价。
- 更新统计信息：统计信息会随着数据的变化⽽变化，优化器可能需要定期更新统计信息，以保持查询优化的准确性。
- 查询优化器的影响因素：
- 查询复杂度：查询的复杂度越⾼，优化器需要考虑的执⾏计划越多，优化的时间和代价也会增加。
- 数据分布：数据的分布情况会影响优化器的索引选择和连接顺序的决策，不同的数据分布可能导致不同的执⾏计划。
只有了解查询优化器的涉及逻辑，才能更好的掌握查询优化的相关⽅法。
2.2 查询优化主要手段
SQL查询优化涉及到多个⽅⾯，以下是⼀些常⻅的SQL查询优化技术和相关⽅⾯的详细描述：
-
优化查询语句：
- 使⽤恰当的SQL语句：根据查询需求选择合适的SQL语句，避免冗余或复杂的查询操作。
- 减少数据返回量：只选择需要的列，避免返回不必要的数据，减少⽹络传输和结果集处理开销。
-
创建适当的索引：
MySQL索引是⼀种⽤于加快数据检索速度和提⾼查询性能的数据结构。
它类似于书籍的⽬录，通过按照某个或多个列的值进⾏排序和存储，使得数据库可以更快地定位和访问特定的数据⾏。
其中索引的类型有以下⼏类：- B-Tree索引：B-Tree（平衡树）是MySQL最常⽤的索引类型。它将索引数据以树形结构存储，⽀持快速的范围查找和精确查找。
- 全⽂索引：全⽂索引⽤于对⽂本字段进⾏全⽂搜索，提供更⾼级的⽂本搜索功能。索引的创建和查看⽅式：
- 创建索引：使⽤CREATE INDEX语句可以创建索引，可以选择单列索引或复合索引，也可以为索引指定排序⽅式和存储类型。
- 查看索引：使⽤SHOW INDEX语句可以查看表的索引信息，包括索引名称、列名、类型等。
- 修改和删除索引：使⽤ALTER TABLE语句可以修改或删除现有的索引。
在创建索引时，可以遵循以下原则来确保索引的有效性和性能：
- 选择合适的列：选择经常⽤于查询条件、连接和排序的列作为索引列。这些列通常是频繁进⾏数据检索和过滤的列，通过索引可以提⾼查询性能。
- 考虑列的选择性：选择具有⾼选择性的列作为索引列，即不重复的值越多越好。⾼选择性的列可以更快地缩⼩搜索范围，提⾼查询效率。
- 避免过多的索引：过多的索引会增加数据插⼊、更新和删除的开销，同时占⽤更多的存储空间。需要权衡索引的数量和性能提升的程度，避免创建不必要的索引。
- 考虑复合索引：当多个列经常同时⽤于查询条件或连接操作时，可以创建复合索引。复合索引可以减少索引的数量，提⾼查询性能。
- 索引⻓度：对于字符串类型的列，尽量指定适当的索引⻓度，以减少索引的存储空间占⽤和查询性能的开销。
- 考虑查询的排序需求：如果查询经常涉及排序操作，可以在索引中包含排序列，以避免使⽤临时表和排序操作，提⾼查询性能。
总的来说，索引的创建需要综合考虑查询模式、数据特性和性能需求。
通过正确设计和使⽤索引，可以加快查询速度，减少数据扫描的开销，并提⾼数据库的整体性能。
在创建和管理索引时，需要根据实际需求和查询模式选择合适的索引类型、索引列和索引数量，以达到最佳的查询优化效果。
-
优化数据模型和表结构：
- 正规化数据模型：遵循数据库设计的规范，消除数据冗余，提⾼查询效率。
- 合理划分表和分区：将⼤表划分为更⼩的表或使⽤分区技术，提⾼查询效率和数据维护性能。
-
监测和分析查询性能：
- 使⽤性能监控⼯具：监测数据库的性能指标，如查询响应时间、锁等待时间等，及时发现性能瓶颈。
- 分析执⾏计划：使⽤EXPLAIN语句分析查询的执⾏计划，查看索引使⽤情况和性能瓶颈，优化查询语句和索引设计。
-
定期维护和优化：
- 定期收集统计信息：通过收集表的统计信息，优化查询优化器的决策，提⾼查询计划的准确性和性
能。 - 定期重建索引：当索引碎⽚化严重时，定期重建索引，提⾼索引的效率。
- 定期收集统计信息：通过收集表的统计信息，优化查询优化器的决策，提⾼查询计划的准确性和性
SQL查询优化是⼀个综合性的⼯作，需要综合考虑数据库结构、索引设计、查询语句、系统配置等多个⽅⾯。通过不断优化查询性能，可以提⾼数据库的响应速度和系统的整体性能。
2.3 Explain参考
2.3.1 Explain介绍
MySQL 提供了⼀个 EXPLAIN 命令, 它可以对 SELECT 语句的执⾏计划进⾏分析, 并输出 SELECT 执⾏的详细信息, 以供开发⼈员针对性优化.
使⽤explain这个命令来查看⼀个这些SQL语句的执⾏计划，查看该SQL语句有没有使⽤上了索引，有没有做全表扫描，这都可以通过explain命令来查看。
可以通过explain命令深⼊了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运⾏SQL语句时哪种策略预计会被优化器采⽤。
EXPLAIN 命令⽤法⼗分简单, 在 SELECT 语句前加上 explain 就可以了
2.3.2 参数说明
EXPLAIN 命令的输出内容如下:
mysql> explain select * from tuser where id = 2 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tuser
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
各列的含义如下:
- id: SELECT 查询的标识符. 每个 SELECT 都会⾃动分配⼀个唯⼀的标识符
- select_type: SELECT 查询的类型
- table: 查询的是哪个表
- partitions: 匹配的分区
- type: join 类型
- possible_keys: 此次查询中可能选⽤的索引
- key: 此次查询中确切使⽤到的索引
- ref: 哪个字段或常数与 key ⼀起被使⽤
- rows: 显示此查询⼀共扫描了多少⾏. 这个是⼀个估计值
- filtered: 表示此查询条件所过滤的数据的百分⽐
- extra: 额外的信息
1 id
每个单位查询的SELECT语句都会⾃动分配的⼀个唯⼀标识符，表示查询中操作表的顺序，有三种情况：
- id相同：执⾏顺序由上到下
- id不同：如果是⼦查询，id号会⾃增，id越⼤，优先级越⾼。
- id相同的不同的同时存在
2 select_type
单位查询的查询类型，⽐如：普通查询、联合查询(union、union all)、⼦查询等复杂查询。
- 1.simple
表示不需要union操作或者不包含⼦查询的简单select查询。有连接查询时，外层的查询为simple。
- 2.primary
⼀个需要union操作或者含有⼦查询的select，位于最外层的单位查询的select_type即为primary。
- 3.union
union连接的两个select查询，第⼀个查询是dervied派⽣表，除了第⼀个表外，第⼆个以后的表select_type都是union
- DERIVED 在FROM列表中包含的⼦查询被标记为DERIVED（衍⽣），MySQL会递归执⾏这些⼦查询，把结果放在临时表中
- UNION 若第⼆个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM⼦句的⼦查询中，外层SELECT将被标记为：DERIVED
- UNION RESULT 从UNION表获取结果的SELECT
- dependent union
与union⼀样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
- dependent union
与union⼀样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
- 5.union result
包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null
- 6.subquery
除了from字句中包含的⼦查询外，其他地⽅出现的⼦查询都可能是subquery
- 7.dependent subquery
与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
- 8.derived
from字句中出现的⼦查询，也叫做派⽣表，其他数据库中可能叫做内联视图或嵌套select
3 table
显示的单位查询的表名，有如下⼏种情况：
- 如果查询使⽤了别名，那么这⾥显示的是别名
- 如果不涉及对数据表的操作，那么这显示为null
- 如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执⾏计划中的id，表示结果来⾃于这个查询产⽣。
- 如果是尖括号括起来的<union M,N>，与类似，也是⼀个临时表，表示这个结果来⾃于union查询的id为M,N的结果集。
4 partitions
使⽤的哪些分区（对于⾮分区表值为null）。
5.7之后的版本默认会有 partitions 和 filtered两列，但是5.6版本中是没有的，需要
使⽤explain partitions select ……来显示带有partitions 的列，
使⽤explain extended select ……来显示带有filtered的列。
什么是分区表？
mysql内部实现的表的⽔平拆分，所有数据还在⼀个表中，但物理存储根据⼀定的规则放在不同的⽂件中。这个是mysql⽀持的功能，业务代码⽆需改动。
技术现状：
业内进⾏⼀些技术交流的时候也更多的是⾃⼰分库分表，⽽不是使⽤分区表。
1）分区表，分区键设计不太灵活，如果不⾛分区键，很容易出现全表锁
2）⼀旦数据量并发量上来，如果在分区表实施关联，就是⼀个灾难
3）⾃⼰分库分表，⾃⼰掌控业务场景与访问模式，可控。分区表，研发写了⼀个sql，都不确定mysql 是怎么玩的，不太可控
5 type(重要）
显示的是单位查询的连接类型或者理解为访问类型，访问性能依次从好到差：
- system
- const
- eq_ref
- ref
- fulltext
- ref_or_null
- unique_subquery
- index_subquery
- range
- index_merge
- index
- ALL
注意事项：
- 除了all之外，其他的type都可以使⽤到索引
- 除了index_merge之外，其他的type只可以⽤到⼀个索引
- 最少要使⽤到range级别
- system
表中只有⼀⾏数据或者是空表。等于系统表，这是const类型的特列，平时不会出现，这个也可以忽略不计
注：a表中只有⼀条记录，并且id为主键 - const（重要）
使⽤唯⼀索引或者主键，返回记录⼀定是1⾏记录的等值where条件时，通常type是const。其他数据库也叫做唯⼀索引扫描。
- eq_ref（重要）
唯⼀性索引扫描，对于每个索引键，表中只有⼀条记录与之匹配。常⻅于主键或唯⼀索引扫描
- ref（重要）
⾮唯⼀性索引扫描，返回匹配某个单独值的所有⾏，本质上也是⼀种索引访问，它返回所有匹配某个单独值的⾏，然⽽，它可能会找到多个符合条件的⾏，所以他应该属于查找和扫描的混合体。
-
组合索引
-
⾮唯⼀索引
- fulltext
全⽂索引检索，要注意，全⽂索引的优先级很⾼，若全⽂索引和普通索引同时存在时，mysql不管代价，优先选择使⽤全⽂索引 - ref_or_null
与ref⽅法类似，只是增加了null值的⽐较。实际⽤的不多。 - unique_subquery
⽤于where中的in形式⼦查询，⼦查询返回不重复值唯⼀值 - index_subquery
⽤于in形式⼦查询使⽤到了辅助索引或者in常数列表，⼦查询可能返回重复值，可以使⽤索引将⼦查询去重。 - range（重要）
索引范围扫描，常⻅于使⽤>,<,is null,between ,in ,like等运算符的查询中。
- index_merge
表示查询使⽤了两个以上的索引，最后取交集或者并集，常⻅and ，or的条件使⽤了不同的索引，官⽅排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能⼤部分时间都不如range - index（重要）
select结果列中使⽤到了索引，type会显示为index。
全部索引扫描，把索引从头到尾扫⼀遍，常⻅于使⽤索引列就可以处理不需要读取数据⽂件的查询、可以使⽤索引排序或者分组的查询。
- all（重要）
这个就是全表扫描数据⽂件，然后再在server层进⾏过滤返回符合要求的记录。
6 possible_keys
此次查询中可能选⽤的索引，⼀个或多个
7 key
查询真正使⽤到的索引，select_type为index_merge时，这⾥可能出现两个以上的索引，其他的select_type这⾥只会出现⼀个。
8 key_len
- ⽤于处理查询的索引⻓度，如果是单列索引，那就整个索引⻓度算进去，如果是多列索引，那么查询不⼀定都能使⽤到所有的列，具体使⽤到了多少个列的索引，这⾥就会计算进去，没有使⽤到的列，这⾥不会计算进去。
- 留意下这个列的值，算⼀下你的多列索引总⻓度就知道有没有使⽤到所有的列了。
- 另外，key_len只计算where条件⽤到的索引⻓度，⽽排序和分组就算⽤到了索引，也不会计算到key_len中。
key_len的⻓度计算公式：
varchar(10)变⻓字段且允许NULL = 10 * ( character set：
utf8=3,gbk=2,latin1=1)+1(NULL)+2(变⻓字段)
varchar(10)变⻓字段且不允许NULL = 10 *( character set：
utf8=3,gbk=2,latin1=1)+2(变⻓字段)
char(10)固定字段且允许NULL = 10 * ( character set：
utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL = 10 * ( character set：
utf8=3,gbk=2,latin1=1)
bigint的⻓度是8bytes
int key_len⻓度是4 ，tinyint的⻓度是1
smallint ⻓度是2 middleint⻓度是3
9 ref
- 如果是使⽤的常数等值查询，这⾥会显示const
- 如果是连接查询，被驱动表的执⾏计划这⾥会显示驱动表的关联字段
- 如果是条件使⽤了表达式或者函数，或者条件列发⽣了内部隐式转换，这⾥可能显示为func
10 rows(重要)
这⾥是执⾏计划中估算的扫描⾏数，不是精确值（InnoDB不是精确的值，MyISAM是精确的值，主要原因是InnoDB⾥⾯使⽤了MVCC并发机制）
11 filtered
filtered列指示将由mysql server层需要对存储引擎层返回的记录进⾏筛选的估计百分⽐，也就是说存储引擎层返回的结果中包含有效记录数的百分⽐。最⼤值为100，这意味着没有对⾏进⾏筛选。值从100减⼩表示过滤量增加。rows显示检查的估计⾏数，rows×filtered显示将与下表联接的⾏数。例如，如果rows为1000，filtered为50.00（50%），则要与下表联接的⾏数为1000×50%=500。
12 extra(重要)
这个列包含不适合在其他列中显示单⼗分重要的额外的信息，这个列可以显示的信息⾮常多，有⼏⼗种
- Using filesort
说明mysql会对数据使⽤⼀个外部的索引排序，⽽不是按照表内的索引顺序进⾏读取。MySQL中⽆法利⽤索引完成的排序操作称为“⽂件排序”。需要优化sql。
- Using temporary
使⽤了⽤临时表保存中间结果，MySQL在对查询结果排序时使⽤临时表。常⻅于排序order by和分组查询group by
- using index（重要）
查询时不需要回表查询，直接通过索引就可以获取查询的结果数据。
- 表示相应的SELECT查询中使⽤到了覆盖索引（Covering Index），避免访问表的数据⾏，效率不错！
- 如果同时出现Using Where ，说明索引被⽤来执⾏查找索引键值
- 如果没有同时出现Using Where ，表明索引⽤来读取数据⽽⾮执⾏查找动作。
- using where（重要）
表示Mysql将对storage engine提取的结果进⾏过滤，过滤条件字段⽆索引；
- impossible where
where⼦句的值 总是false ，不能⽤来获取任何元组
SELECT * FROM t_user WHERE id = '1' and id = '2'
3 查询优化实战
电商场景下，性能提升案例实战
3.1 准备工作
在进⾏实战案例演示前，我们需要准备相关数据，
我们都知道，在电商平台中，最核⼼的数据为：⽤户、商品、订单，
因此，我们需要创建了对应三张表，以及批量初始化⼤量数据，其中，表结构简单设计如下：
CREATE TABLE `my_customer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(3) DEFAULT '20' COMMENT '年龄',
`gender` tinyint(1) NOT NULL DEFAULT '0' COMMENT '性别 0-⼥ 1-男',
`phone` varchar(20) DEFAULT '' COMMENT '地址',
`address` varchar(100) DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `my_customer_name_IDX` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户';
CREATE TABLE `my_order` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`quantity` int(11) NOT NULL DEFAULT '1' COMMENT '数量',
`total_price` int(11) NOT NULL DEFAULT '1' COMMENT '总价',
`order_status` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '订单状态 0-未⽀付 1-已⽀付 2-派送中 3-已签收',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单';
CREATE TABLE `my_product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL COMMENT '商品名',
`type` int(11) NOT NULL DEFAULT '1' COMMENT '类型 1-⾐服 2-⻝品 3-书籍',
`brand` varchar(100) DEFAULT '' COMMENT '品牌',
`shop_id` int(11) NOT NULL DEFAULT '1' COMMENT '店铺ID',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品';
执⾏测试案例初始化测试数据，其中，⽤户数据量为100万，商品数据量10万，订单数据量近千万。接下来, 我们根据实际电商平台常⻅查询场景进⾏分析和优化。
3.2 场景1：用户搜索
3.2.1 不使⽤索引查询
电商后台管理系统通常需要根据⽤户名称、⼿机号、地址搜索相关⽤户信息，常⻅的查询SQL语句如下：
select * from `my_customer` where phone like '%157%'
通过分析 EXPLAIN 的输出结果，可以了解查询的执⾏计划、访问⽅法和可能存在的性能问题。可以根据输出结果中的字段信息，优化查询语句、索引设计和数据库配置，以提⾼查询性能和效率。
explain select * from `my_customer` where phone like '%157%';
我们可以看到该sql语句的执⾏计划中，type字段为ALL , 表示全表扫描，这会导致查询效率过低，耗时过⻓。
3.2.2 使⽤索引查询
⾸先我们应该考虑为查询字段加上索引，例如phone字段。
CREATE INDEX my_customer_phone_IDX USING BTREE ON store.my_customer(phone);
这⾥要注意，模糊匹配查询使⽤ % 在开头会导致索引失效。
可以尝试将查询条件改为以 % 结尾的模糊匹配，例如
接下来使⽤ explain 命令再次查看执⾏计划：
explain select * from `my_customer` where phone like '157%';
我们可以看到sql执⾏过程中实际⽤到了 my_customer_phone_IDX 索引 , 相⽐全表扫描，这⾥预计扫描函数仅10w多⾏。
3.2.3 覆盖索引和回表查询
在实际开发过程中，应该避免使⽤ SELECT * ：只选择需要的字段，⽽不是使⽤通配符 * 。只选择必要的字段可以减少数据传输和内存开销，提⾼查询性能。
例如，我们仅需要根据⽤户⼿机号查询⽤户id和姓名, 那么，sql应该改写如下：
select id, name from `my_customer` where phone like '157%';
那么到这⾥，当前sql语句能否进⼀步优化呢？答案是肯定的，⾸先我们要了解下 回表查询 这个概念。
回表查询是指在使⽤⾮覆盖索引（Non-Clustered Index）进⾏查询时，当需要获取查询结果所需的数据列不在索引中时，MySQL 需要通过索引的指针回到主索引（Clustered Index）或数据⻚中获取缺失的数据列。
在回表查询中，⾸先根据⾮覆盖索引定位到符合查询条件的索引记录，然后通过索引中的指针获取主索引或数据⻚中的相应数据列。这个过程涉及了两次磁盘访问，即⾸先访问索引⻚，再次访问主索引或数据⻚，因此相对于覆盖索引的查询，回表查询会引⼊额外的磁盘读取操作，增加了查询的开销和响应时间。
回表查询可能会对查询性能产⽣⼀定的影响，特别是在⼤数据量和⾼并发查询的情况下。因此，为了减少回表查询的开销，可以考虑通过 覆盖索引（Covering Index） 来进⾏优化
覆盖索引（Covering Index）是指在查询过程中，索引包含了查询所需的所有数据列，⽆需回表查询主索引或数据⻚。换句话说，覆盖索引能够直接提供查询所需的数据，⽽不需要再去访问主索引或数据⻚，从⽽提⾼查询性能和效率。
在⼀般的索引中，只包含了被索引的列以及主索引的引⽤指针。当执⾏查询时，MySQL ⾸先通过索引定位到符合条件的记录，然后再通过主索引或数据⻚获取缺失的数据列，这个过程被称为回表查询。⽽覆盖索引则避免了回表查询的开销，因为索引本身就包含了查询所需的所有数据列。覆盖索引的好处主要体现在以下⼏个⽅⾯：
- 提⾼查询性能：由于覆盖索引能够直接提供查询所需的数据，减少了磁盘的随机访问和额外的回表查询操作，从⽽加快了查询的执⾏速度。
- 减少磁盘 I/O：回表查询需要进⾏额外的磁盘读取操作，⽽覆盖索引可以减少磁盘 I/O 操作，降低系统的磁盘负载。
- 减少内存消耗：覆盖索引可以减少需要加载到内存中的数据量，节省了内存的使⽤，提⾼了查询的效率。
要创建覆盖索引，需要选择适当的索引列，以包含查询语句中涉及的所有列。这需要综合考虑查询的需求、数据列的选择性和索引的⼤⼩等因素。需要注意的是，创建过多的覆盖索引可能会增加索引的维护成本和存储空间占⽤。
总之，覆盖索引是⼀种优化⼿段，通过索引包含查询所需的所有数据列，避免了回表查询，提⾼了查询的性能和效率。使⽤覆盖索引可以在适当的情况下优化查询，但需要权衡索引的设计和维护成本。
这⾥，我们重新创建 my_customer_phone_IDX 索引，脚本如下：
DROP INDEX my_customer_phone_IDX on store.my_customer;
CREATE INDEX my_customer_phone_IDX USING BTREE ON store.my_customer(phone,name);
重新使⽤ explain 命令再次查看执⾏计划：
explain select * from `my_customer` where phone like '157%';
explain select id, name from `my_customer` where phone like '157%';
这⾥，我们可以看到 Extra 字段的值包含 Using index , 表明触发了索引覆盖，没有进⾏回表查询，查询时间⼤⼤减少。
同理，如果SQL如下，
select count(name) from `my_customer` where phone like '157%';
覆盖索引也会⽣效。
3.2.4 联合索引
基于多个字段创建的索引我们称为联合索引，⽐如我们创建索引create index idx on table(A,B,C) 我们称在字段A,B,C上创建了⼀个联合索引
联合索引和单个索引对⽐来讲，联合索引的所有索引项都会出现在索引上，存储引擎会先根据第⼀个索引项排序，如果第⼀个索引项相同的话才会去看第⼆个，所有我们在查询的时候，如果头索引不带的话，联合索引就会失效，因为在根节点他就不知道怎么往下⾛。⽐如我们现在select * from USER us where us.age=20 and us.money=30这个sql去查的，⾸先在根节点上age>1并且<60,那么读下⼀个节点，依次类推读到叶⼦节点上取出主键id回表查询所有的字段值。
3.2.5 最左前缀法则
如果索引了多个列，要遵循最左前缀法则，查询从索引的最左前列开始，并且不能跳过索引中的列，啥意思，组合索引（quantity，total_price，order_status）你select * from user where total_price=770 AND order_status=3跳过了age这列，索引失效
DROP INDEX my_order_price_IDX ON store.my_order
CREATE INDEX my_order_price_IDX USING BTREE ON store.my_order(quantity,total_price,order_status);
- ⽤到组合索引
explain select * from my_order where quantity = 10;
explain select * from my_order where quantity = 10 and total_price=770;
explain select * from my_order where quantity = 10 and total_price=770 and order_status=3;
explain select * from my_order where quantity = 10 and order_status=3;
- 索引失效
explain select * from my_order where total_price=770 and order_status=3;
explain select * from my_order where order_status=3;
select * from user where money>3 //跳过开头的
select * from user where money>3 and name='程序员fly' //跳过开头的索引失效
- 使⽤部分索引
explain select * from my_order where quantity = 10 and order_status=3;
3.3 场景2：订单查询
3.3.1 ⼦查询
不管是⽤户App端还是在电商后台，都存在订单查询的场景，
例如我们需要根据品牌查询对应品牌下商品的订单，
我们⾸先给商品表加个以品牌字段作为索引：
CREATE INDEX my_product_brand_IDX USING BTREE ON store.my_product (brand);
先给出⼀条常⻅的查询SQL:
select * from my_order mo where product_id in (select id from my_product mp where brand = 'Apple');
查看执⾏计划：
explain select * from my_order mo where product_id in (select id from my_product mp where brand = 'Apple');
有两条执⾏计划，其中订单表的查询使⽤了全表扫描
再给订单表的 prodcut_id 字段加上索引
CREATE INDEX my_order_product_id_IDX USING BTREE ON store.my_order(product_id);
再次查看执⾏计划：
explain select * from my_order mo where product_id in (select id from my_product mp where brand = 'Apple');
两条计划都⽤到了 prodcut_id 字段索引，加快了查询效率。
虽然⼦查询在当前情况下实现了查询需求，但使⽤⼦查询可能会导致⼀些性能问题，因此在优化查询时，通常不建议过度依赖⼦查询。以下是⼀些原因：
- 执⾏多次查询：效率太差，执⾏⼦查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，⼦查询的速度会受到⼀定的影响，这⾥多了⼀个创建和销毁临时表的过程。
- 可读性和维护性差：复杂的嵌套⼦查询可能会使查询语句变得难以理解和维护。⼦查询通常需要理解嵌套层次和各个⼦查询之间的关系，使查询语句变得冗⻓且难以阅读。
- 缺乏优化灵活性：数据库优化器在处理⼦查询时的优化能⼒相对较弱。优化器很难对复杂的嵌套⼦查询进⾏全⾯的优化，可能⽆法选择最佳执⾏计划，导致性能下降。
- 可能引发性能问题：⼦查询可能导致全表扫描或临时表的创建，增加系统的 I/O 负担和内存消耗。特别是当⼦查询涉及⼤量数据或涉及多表关联时，性能问题可能更加明显。
对于能够使⽤连接查询（JOIN）或其他更有效⽅法替代的⼦查询，通常建议使⽤更简洁和⾼效的查询⽅式。连接查询可以更好地利⽤索引和优化执⾏计划，同时提供更好的可读性和维护性。
然⽽，并⾮所有情况下都不推荐使⽤⼦查询。在某些特定的场景下，⼦查询是合理的选择，例如需要进⾏存在性检查或在查询中嵌套聚合函数等情况。在使⽤⼦查询时，需要根据实际情况综合考虑性能、可读性和维护性的权衡，确保达到最佳的查询效果。
3.4.2 连接查询
将SQL语句改写为连接查询（JOIN):
SELECT mo.id as orderId, mo.customer_id as customerId, mp.name as productName, mo.order_status as orderStatus FROM my_order mo JOIN my_product mp ON mo.product_id = mp.id WHERE mp.brand = 'Apple';
虽然多表连接查询（多表 JOIN）是常⻅的查询⽅式之⼀，但是⼀旦join涉及到的数据量很⼤效率就很难保证，这种情况下强烈推荐分别根据索引单表取数据，然后在应⽤层⾥⾯做join，merge数据。
在应⽤层关联的优势如下：
- 提⾼缓存效率：应⽤程序可以⽅便地缓存单表查询的结果对象。通过拆分关联查询，当关联表中的数据发⽣变化时，不会影响到查询缓存，从⽽提⾼缓存的效率。
- 减少锁竞争：拆分查询可以减少锁的竞争。执⾏单个查询时，只涉及到单个表，减少了锁的冲突，提⾼了并发性能。
- 易于数据库拆分：在应⽤层进⾏关联查询，更容易实现数据库的拆分，提供⾼性能和可扩展性的能⼒。
- 提升查询效率：使⽤ IN() 替代关联查询时，MySQL可以按照 ID 的顺序进⾏查询，这可能⽐随机的关联查询更⾼效。
- 减少冗余记录查询：应⽤层关联查询意味着每条记录只需要查询⼀次，⽽在数据库中进⾏关联查询可能需要重复访问部分数据。因此，这种重构还可以减少⽹络和内存的开销。
- 哈希关联效率更⾼：应⽤层关联相当于在应⽤中实现了哈希关联，⽽不是使⽤MySQL的嵌套循环关联。在某些场景下，哈希关联的效率要⾼得多。
不推荐使⽤ JOIN 的原因：
- ⼤规模表的性能压⼒：当表的数据量达到百万级别时，使⽤ JOIN 可能导致性能下降。
- 分布式分库分表：跨库 JOIN 不推荐使⽤，因为⽬前MySQL的分布式中间件对跨库 JOIN 的⽀持不佳。
- 表结构修改的复杂性：修改单表查询相对容易，⽽修改 JOIN 的 SQL 语句较为复杂，维护成本较⾼。
join在部分场景使⽤也有好处：
- 例如分⻚查询：JOIN 查询可以⽅便地进⾏分⻚，可以使⽤副表的字段作为查询条件，在查询时将副表的匹配字段作为结果集，使⽤主表进⾏ IN() 查询。
3.4 场景3：分页查询
分⻚查询情况下的优化：
⼀般典型分⻚查询语句如下：
SELECT mo.id as orderId, mo.customer_id as customerId, mo.order_status as orderStatus
FROM my_order mo
where mo.order_status = 1
order by mo.id asc limit 1000000, 10
limit是最常⽤的分⻚⽅法,它在执⾏过程中，相当于先遍历了前1000000个，然后取了第1000000到1000010个，舍弃了前1000000个, limit越⼤查询性能越低，limit仅适⽤于⼩数据范围内的分⻚查询。
explain SELECT mo.id as orderId, mo.customer_id as customerId, mo.order_status as orderStatus
FROM my_order mo
where mo.order_status = 1
order by mo.id asc limit 1000000, 10;
可以利⽤索引来进⾏优化，例如我们分⻚查询到第1000000条数据，订单ID为9397780，那么下个分⻚的所有订单ID都是⼤于9397780，
sql语句可以改写为：
SELECT mo.id as orderId, mo.customer_id as customerId, mo.order_status as orderStatus
FROM my_order mo
inner join (
select id
from my_order
where id >9397780 and order_status = 1 limit 10) mo2
on mo.id = mo2.id
order by mo.id asc
查看执⾏计划：
explain SELECT mo.id as orderId, mo.customer_id as customerId, mo.order_status as orderStatus
FROM my_order mo
inner join (
select id
from my_order
where id > 9397780 and order_status = 1
limit 10) mo2
on mo.id = mo2.id
order by mo.id asc
从查询计划我们看到，⾸先⼦查询根据主键索引，获取最多10条订单ID, 然后再根据这10条id 获取数据详情。不需要再查询上百万条数据后排序取所需⼏⾏数据。
3.5 场景4：订单统计
电商平台经常需要从多个维度统计订单数据，例如订单数、订单总额、热⻔商品排⾏等等。
这⾥假设我们需要查询不同商品的订单数和订单总额，
给出的第⼀版sql 如下所示：
select mo.product_id , count(*) as num , sum(mo.total_price)
from my_order mo
group by mo.product_id
以下是⼀些优化思路：
- 使⽤合适的索引：为⽀持分组和统计操作，可以考虑创建合适的索引。优化思路包括：
- 为分组字段和统计字段创建索引，以提⾼分组和聚合操作的效率。
- 考虑覆盖索引，即索引包含所有需要的字段，避免回表查询。
- 针对不同的查询场景和条件，选择适当的索引类型（如B-tree索引、哈希索引等）。
- 缓存结果集：对于频繁进⾏的分组统计查询，可以考虑缓存结果集，避免每次都重新计算。优化思路包括：
- 使⽤缓存技术（如Redis）存储结果集，以便快速获取统计数据。
- 设置合适的缓存失效策略，根据数据的更新频率进⾏定期更新或⼿动更新。
- 预聚合数据：对于⼤数据量和复杂的统计查询，可以考虑预先计算和存储聚合结果，以减少查询时的计算量。优化思路包括：
- 创建定期或实时的预聚合任务，将统计结果存储到特定的表中。
- 在查询时直接从预聚合表中获取结果，避免重复的计算和分组操作。
- 合理设置分组字段：对于分组统计查询，分组字段的选择会影响查询性能。优化思路包括：
- 尽量选择具有⾼基数（不同取值较多）的字段作为分组字段，以减少分组的数量和计算量。
- 避免在查询中使⽤过多复杂的表达式或函数作为分组字段，以减少计算的开销。
- 考虑并⾏计算：对于⼤规模数据的分组统计查询，可以考虑使⽤并⾏计算来提⾼查询效率。优化思路包括：
- 将查询任务拆分为多个并⾏的⼦任务，每个⼦任务处理不同的数据⼦集。
- 使⽤并⾏计算框架或数据库引擎⽀持并⾏查询，以加快查询速度和提⾼吞吐量。
当然具体的优化策略需要根据具体的业务场景和数据特点进⾏选择和调整。
因为我们在之前的场景案例⾥，已经对product_id 字段加了索引，我们可以根据第三条和第五条优化建议：并⾏计算，在应⽤层聚合数据，
考虑每条sql仅对部分商品进⾏统计，例如：
select mo.product_id , count(*) as num , sum(mo.total_price)
from my_order mo
where mo.product_id between 1000 and 2000
group by mo.product_id;
这⾥仅对商品ID在（1000,2000）范围内的订单进⾏统计，我们可以分多次查询不同的数据。
这⾥采⽤了my_order_product_id_IDX索引加快查询，另外由于数据量的减少，进⾏排序和统计
的耗时也⼤⼤减少。
explain select mo.product_id , count(*) as num , sum(mo.total_price)
from my_order mo
where mo.product_id between 1000 and 2000
group by mo.product_id;
扫一扫
1558
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
