引言

实战部分挑选一些比较常见的情况，事先强调个人使用的是mysql 8.0.26，所以不同版本如果出现不同测试结果也不要惊讶，新版本会对于过去一些不会优化的查询进行优化。

实战部分承接上一篇文章：三高Mysql - Mysql索引和查询优化讲解（偏理论部分） - 掘金 (juejin.cn)

前置准备

这里还是要再啰嗦一遍，所有的数据库和表均来自官方的sakila-db，作为学习和熟悉mysql数据库操作非常好。

sakila-db

sakila-db是什么？国外很火的一个概念，指的是国外电影租赁市场外国人使用租赁的方式进行电影的观看，过去十分受外国人的喜欢，这里拿出来介绍是因为后续的内容都用到了这个案例，所以我们需要提前把相关的环境准备好，从如下地址进行下载：

下载地址：dev.mysql.com/doc/index-o…

网络异常，图片无法展示

work-bench

work-bench是官方开发的数据库关系图的可视化工具，使用官方案例的具体关系图展示效果如下，通过这些图可以看到Sakila-db之间的大致关系：

work-bench 是免费软件，下载地址如下：

dev.mysql.com/downloads/w…

网络异常，图片无法展示

安装workbench和下载sakila-db的过程这里不做记录，在运行的时候需要注意先建立一个数据库运行Sheme文件，然后执行data的sql文件，最终借助navicat中查看数据和表结构关系：

网络异常，图片无法展示

正文部分

where查询太慢怎么办？

遇到where查询太慢，我们第一步是需要分析数据类型的组成以及数据表的设置是否合理，其次我们可以使用explain对于查询语句进行分析，使用方式十分简单在需要优化的查询语句前面添加explain语句，对于所有的查询来说，覆盖索引的查找方式是最优解，因为覆盖索引不需要回表查数据。

覆盖索引：覆盖索引是查询方式，他不是一个索引，指的是在查询返回结果的时候和使用的索引是同一个，这时候可以发现他压根不需要回表，直接查辅助索引树就可以得到数据，所以覆盖索引的查询效率比较高。

如何使用sql语句查看某一个表的建表语句：

回答：使用show create table 表名称即可。

那么什么情况下会使用覆盖索引：

查询字段为辅助索引的字段或者聚簇索引的字段。
符合最左匹配原则，如果不是最左匹配则不能走索引。

我们使用上面提到的sakila-db进行实验，这里可以使用inventory表作为实验，但是这个表需要进行一些调整，下面请看具体的sql：

CREATE TABLE `inventory_test` (
  `inventory_id` mediumint unsigned NOT NULL AUTO_INCREMENT,
  `film_id` smallint unsigned NOT NULL,
  `store_id` tinyint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`inventory_id`),
  -- KEY `idx_fk_film_id` (`film_id`),
  KEY `idx_store_id_film_id` (`store_id`,`film_id`)
  -- CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  -- CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

我们将原始的sql建表语句只保留一个辅助索引，比如在上面的语句中删除了idx_fk_film_id索引，下面删除这个索引之后的实验效果：

explain select * from inventory_test where film_id = 1;
-- 案例1. 不符合最左原则不走索引 
-- 1  SIMPLE  inventory_test    ALL          1  100.00  Using where
explain select * from inventory_test where store_id = 1;
-- 案例2: 使用了辅助索引（联合索引）：
-- 1  SIMPLE  inventory_test    ref  idx_store_id_film_id  idx_store_id_film_id  1  const  1  100.00  
explain select inventory_id,film_id,store_id from inventory_test where store_id = 1;
-- 案例3:  正常使用索引 
-- 1 SIMPLE  inventory_test    ref  idx_store_id_film_id  idx_store_id_film_id  1  const  1  100.00  Using index
explain select film_id,store_id from inventory_test where store_id = 1;
-- 案例4:  覆盖索引 
-- 1  SIMPLE  inventory_test    ref  idx_store_id_film_id  idx_store_id_film_id  1  const  1  100.00  Using index
explain select film_id,store_id from inventory_test where film_id = 1;
-- 案例5: 正常使用索引，但是type存在区别 
-- 1  SIMPLE  inventory_test    index  idx_store_id_film_id  idx_store_id_film_id  3    1  100.00  Using where; Using index
explain select inventory_id,film_id,store_id from inventory_test where film_id = 1;
-- 案例6: 使用索引返回结果，但是type存在区别 
-- 1  SIMPLE  inventory_test    index  idx_store_id_film_id  idx_store_id_film_id  3    1  100.00  Using where; Using index
explain select inventory_id,film_id,store_id from inventory_test where store_id = 1;
-- 案例7: 覆盖索引 
-- 1  SIMPLE  inventory_test    ref  idx_store_id_film_id  idx_store_id_film_id  1  const  1  100.00  Using index

案例1和案例2是较为典型的索引最左匹配原则的错误使用反面教材，也是很多新手建立索引但是可能用错的陷阱之一，最左匹配原则指的是where条件需要从建立索引的最左列开始进行搜索，可以看到这里的星号和建表的时候字段的顺序是一样的，也就是inventory_id，film_id,store_id，last_update，所以是虽然是select *但是是正常走索引的。

（实际干活时候千万不要这么做，这里是为了演示偷懒而已）

不用星号我使用乱序的列查询会怎么样，其实这时候如果你把查询列的数据换一下会.....没啥影响，随意调换查询列顺序依然可以走索引。

接下来是案例3 - 案例7的几个查询，这几个查询意图解释的是针对覆盖索引使用的细节问题，在上面的测试案例语句当中可以看到案例4由于查询的结果和where条件都是使用了索引的，所以最终mysql使用了完整的覆盖索引，同时符合联合索引的最左匹配原则，所以查询的效率达到了ref级别（这个级别暂时简单理解就是非常快就行）。

接着案例5又把where条件换了一下，可以看到虽然还是走了索引，但是效率一下子就低了下来，因为他不符合最左匹配原则，另外这个案例5的查询级别可以理解为它需要把整个辅助索引也就是联合索引的树扫完再去进行where筛选，效率自然就不如直接检索排序索引值快了，但是index这个级别还是比ALL这个龟速快不少。

理解了上面的这一层意思，再来理解案例6和7就很简单了，可以看到只多了一个主键列查询。

这里读者可能会觉得你这上面不是说返回结果全是索引列才会覆盖么，怎么加入了主键列还是奏效呢？主键不是在聚簇索引上嘛不是需要回表么？其实这两个问题很好回答，因为辅助索引当中key存储的确实是索引列的值，但是他的索引值放的是主键ID，当mysql在搜索索引列的时候发现这里多了一个列，但是又发现这个列是主键，所以最后发现可以直接通过联合索引直接返回结果不需要回表，所以这样覆盖索引的条件同样是成立的。

如果读者不清楚查询explain结果列代表的含义，可以参考下面的内容对比：

id: 首先，一个select就会出现一个id, 通常在复杂的查询里面会包含多张表的查询，比如join, in等等
select_type：这个表示的是查询的类型
table：表名称
partitions：这个表示表空间，分区的概念
type : 比如查询的优化等级, const, index, all，分别代表了聚簇索引，二级索引(辅助索引)，全表扫描的查询搜索方式
Possiblekeys：和type一样确定访问方式，确定有哪些索引可以选择，
key：确定有哪些可以提供选择，同时提供索引的对应长度
key_len：表示的是索引的长度
ref：等值匹配的时候出现的一些匹配的相关信息
Rows：预估通过所索引或者别的方式读取多少条数据
filtered：经过搜索条件过滤之后的剩余数据百分比。
extra：额外的信息不重要，主要用于用户判定查询走了什么索引。

总结

通过上面的案例我们可以从下面的角度思考来如何提升索引查询速度：

使用覆盖索引查询方式提高效率，再次强调覆盖索引不是索引是优化索引查询一种方式。
如果数据不只使用索引列那么就构不成覆盖索引。
可以优化sql语句或者优化联合索引的方式提高覆盖索引的命中率。

如何确认选择用什么索引？

这里涉及一个索引基数（cardinality）的问题，索引基数是什么，其实就是利用算法和概率学统计的方式确定最优化的索引方案，这个值可以通过show index from 表名的方式进行获取，比如下面的200和121就是索引基数（cardinality）。

因为索引基数的存在如果索引不符合我们到使用预期可以尝试强制使用某索引。

> show index from actor;
actor  0  PRIMARY  1  actor_id  A  200        BTREE      YES  
actor  1  idx_actor_last_name  1  last_name  A  121        BTREE      YES

索引基数的定义官方文档的介绍：

下面一坨东西简单来说就是mysql会根据基数的数值根据一定的算法选择使用索引，但是有时候如果查询不能符合预期要求就需要强制使用索引了。

表列中不同值的数量。当查询引用具有关联索引的列时，每列的基数会影响最有效的访问方法。

例如，对于具有唯一约束的列，不同值的数量等于表中的行数。如果一个表有一百万行，但特定列只有 10 个不同的值，

则每个值（平均）出现 100,000 次。 SELECT c1 FROM t1 WHERE c1 = 50 等查询因此可能会返回 1 行或大量行，

并且数据库服务器可能会根据 c1 的基数以不同方式处理查询。

如果列中的值分布非常不均匀，则基数可能不是确定最佳查询计划的好方法。例如，SELECT c1 FROM t1 WHERE c1 = x;

当 x=50 时可能返回 1 行，当 x=30 时可能返回一百万行。在这种情况下，您可能需要使用索引提示来传递有关哪种

查找方法对特定查询更有效的建议。

基数也可以应用于多个列中存在的不同值的数量，例如在复合索引中。

参考：列、复合索引、索引、索引提示、持久统计、随机潜水、选择性、唯一约束。

原文：
The number of different values in a table column. When queries refer to columns that have an 
associated index, the cardinality of each column influences which access method is most 
efficient. For example, for a column with a unique constraint, the number of different 
values is equal to the number of rows in the table. If a table has a million rows but 
only 10 different values for a particular column, each value occurs (on average) 100,000 times.
 A query such as SELECT c1 FROM t1 WHERE c1 = 50; thus might return 1 row or a huge number of 
 rows, and the database server might process the query differently depending on the cardinality 
 of c1.
If the values in a column have a very uneven distribution, the cardinality might not be 
a good way to determine the best query plan. For example, SELECT c1 FROM t1 WHERE c1 = x;
 might return 1 row when x=50 and a million rows when x=30. In such a case, you might need 
 to use index hints to pass along advice about which lookup method is more efficient for a 
 particular query.
Cardinality can also apply to the number of distinct values present in multiple columns, 
as in a composite index.
See Also column, composite index, index, index hint, persistent statistics, random dive,
 selectivity, unique constraint.

如何让sql强制使用索引

可以使用from表之后接条件语句：force index(索引) 的方式进行处理，使用强制索引的情况比较少，除非优化器真的选择了不符合预期的优化规则并且严重影响查询性能，使用强制索引的案例如下：

select * from actor force index(idx_actor_last_name);

count()慢的原因是什么？

count函数不用多介绍，作用是查询结果的行数，但是需要注意优化器在处理过程中会比对并且排除掉结果为null的值的数据，这意味着在行数很大的时候如果使用不正确count会因为比对数据操作进而降低查询效率。

所以这里我们只要记住一个特定的规则，那就是只要是涉及行数的查询，那就使用select(*)，原因仅仅是mysql官方针对这个做了专门的优化，也不需要去纠结为什么官方要给select(*)做优化，只能说约定大于配置，下面是常见的查询性能之间的对比：

count(非索引字段)：理论上是最慢的，因为对于每一行结果都要判断是否为null。
count(索引字段)：虽然走了索引，但是依然需要对每一行结果判断是否为null。
count(1)：虽然不涉及字段了，但是这种方式依然需要对1进行判断是否为null。
count(*)：Mysql官方进行优化，查询效率最快，只需要记住这种方式即可。

索引下推

索引下推实现版本为Mysql5.6以上。

作用：本质上是为了减少辅助索引（或者说二级索引）回表次数的一种优化手段。

案例：请看下面的建表语句，这里比较关键的是建立了store_id和film_id的联合索引。

网络异常，图片无法展示

以下面的SQL语句为例，如果是5.6之前的版本虽然他是覆盖索引的查询方式但却是不能使用索引的，数据进过索引查找之后虽然store_id是顺序排序的但是film_id是乱序的，在索引检索的时候由于没有办法顺序扫描（如果不清楚索引组织结构可以多看几遍B+树索引构造）它需要一行行使用主键回表进行查询，查询实际需要使用每一行的inentory_id回表4次去匹配film_id是否为3。

select * from inventory_3 where store_id in (1,2) and film_id = 3;

网络异常，图片无法展示

按照人的思维看起来是很不合理的，因为我们可以发现按照正常的逻辑有一种搜索的方法是通过“跳跃“索引的方式进行扫描，当扫描到索引列如果不符合条件，则直接跳跃索引到下一个索引列，有点类似我们小时候”跳房子“方式来寻找自己需要的沙袋（索引数据）。

那么索引下推是如何处理上面这种情况的呢？虽然film_id是没有办法顺序扫描的也不符合索引的排列规则，但是发现可以根据遍历film_id汇总索引之后再回表查呀！比如根据查询条件搜索遍历找到film=3之后再根据二级索引列对应的主键去查主索引，这时候只需要一次回表就可以查到数据，此时原本应该根据每个二级索引的主键值进行回表变为遍历索引并找到索引值之后再回表，最终达到减少回表次数的效果，这也是前面为什么说索引下推是为了减少了回表的次数的答案。

索引下推的开启和关闭可以参考如下命令：

-- 索引下推变量值：
mysql> select @@optimizer_switch\G;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)
-- 关闭索引下推
set optimizer_switch='index_condition_pushdown=off';
-- 开启索引下推
set optimizer_switch='index_condition_pushdown=on';

三高Mysql - Mysql索引和查询优化（偏实战部分）（上）

引言

前置准备

sakila-db

work-bench

正文部分

where查询太慢怎么办？

如何确认选择用什么索引？

count()慢的原因是什么？

索引下推

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像