蓝色域界

多表连接

JOIN 语句应尽可能减少 NestedLoop 次数，即永远用小结果集驱动大结果集；

优先优化 NestedLoop 内层循环；

保证 JOIN 语句中被驱动表上的条件字段已经被索引；

不能保证 JOIN 语句中被驱动表上的条件字段已经被索引且内存资源充足的情况下可以加大 JOIN BUFFER 的设置。

双表连接

左连接时给右表条件字段加索引，右连接时给左表条件字段加索引。

EXPLAIN SELECT * FROM `member` `m` LEFT JOIN `article` `a` ON `m`.`status` = `a`.`status`;

# 给左表的字段创建索引
# 左连接时左表中符合的数据都存在再加索引也没有效果
CREATE INDEX `idx_member_status` ON `member` (`status`);

EXPLAIN SELECT * FROM `member` `m` LEFT JOIN `article` `a` ON `m`.`status` = `a`.`status`;

# 给右表的字段创建索引
# 显然左连接时给右表条件字段加索引比较好
# 同理右连接时应该左表条件字段加索引
DROP INDEX `idx_member_status` ON `member`;
CREATE INDEX `idx_article_status` ON `article` (`status`);

EXPLAIN SELECT * FROM `member` `m` LEFT JOIN `article` `a` ON `m`.`status` = `a`.`status`;

三表连接

多表的左连接应给所有右表条件字段建立索引。

# 清除之前的索引
DROP INDEX `idx_article_status` ON `article`;

EXPLAIN SELECT * FROM `member` `m` LEFT JOIN `article` `a` ON `m`.`status` = `a`.`status` LEFT JOIN `openid` `o` ON `a`.`status` = `o`.`status`;

# 所有右表字段创建索引
CREATE INDEX `idx_article_status` ON `article` (`status`);
CREATE INDEX `idx_openid_status` ON `openid` (`status`);

EXPLAIN SELECT * FROM `member` `m` LEFT JOIN `article` `a` ON `m`.`status` = `a`.`status` LEFT JOIN `openid` `o` ON `a`.`status` = `o`.`status`;

复合索引

复合索引应按序使用且不能跳过中间的列，如有索引 idx_name_status_email，如果查询时只使用到了 status 和 email 或者 name 和 email 则该索引不会生效。

EXPLAIN SELECT `id` FROM `member` WHERE `status` = 0 AND `gender` = 0 AND `ip` = '';

# 创建索引
CREATE INDEX `idx_status_gender_ip` ON `member` (`status`, `gender`, `ip`);

# 正确使用索引
EXPLAIN SELECT `id` FROM `member` WHERE `status` = 0 AND `gender` = '0';

# ref 只有一个 const

# gender 为 CHAR 类型
# 查询时字符串不加单引号索引失效，复合索引只用了一部分
EXPLAIN SELECT `id` FROM `member` WHERE `status` = 0 AND `gender` = 0;
# 查询时未从左至右连续的使用索引，复合索引只用了一部分
EXPLAIN SELECT `id` FROM `member` WHERE `status` = 0 AND `ip` = '';

# ref 为 NULL

# 未按序使用索引
EXPLAIN SELECT `id` FROM `member` WHERE `gender` = '0' AND `ip` = '';

尽可能使用等值匹配，在结果集相同的情况下应减少不必要的 WHERE 条件。

# 假设 ip = '' 不对结果集产生影响，更多的不必要条件增加了 key_len
EXPLAIN SELECT `id` FROM `member` WHERE `status` = 0 AND `gender` = '0' AND `ip` = '';

为 WHERE、ORDER BY、GROUP BY 字段创建复合索引。

存储引擎不使用复合索引中范围条件右边的列，案例：

# 清除之前的索引
DROP INDEX `idx_article_status` ON `article` (`status`);

EXPLAIN SELECT `id`, `member_id` FROM `article` WHERE `create` > '2019-10-01' AND `status` = 0 ORDER BY `category_id` DESC LIMIT 1;

CREATE INDEX `idx_mid_status_cid` ON `article` (`member_id`, `status`, `category_id`);

# 由于 status 是范围比较，复合索引只用到了 member_id 和 status 字段
# category_id 未使用到索引而出现 Using filesort
EXPLAIN SELECT `id`, `member_id` FROM `article` WHERE `member_id` = 2 AND `status` < 2 ORDER BY `category_id` DESC LIMIT 1;

# 由 key_len 可知，复合索引只用到了 member_id 和 status 字段
# key_len 显示在索引中查询的字段长度，用于排序的字段不计算在其中
# 索引的作用：查询和排序
EXPLAIN SELECT `id`, `member_id` FROM `article` WHERE `member_id` = 2 AND `category_id` = 2 ORDER BY `status`;

# 复合索引用到了所有字段
# member_id 用于查询
# status 和 category_id 用于排序
EXPLAIN SELECT `id`, `member_id` FROM `article` WHERE `member_id` = 2 ORDER BY `status`, `category_id`;

# 将排序中的 status 和 category_id 位置对调
# 排序不再使用索引，会出现 Using filesort
EXPLAIN SELECT `id`, `member_id` FROM `article` WHERE `member_id` = 2 ORDER BY `category_id`, `status`;

# 降序也将会导致  Using filesort
EXPLAIN SELECT `id`, `member_id` FROM `article` WHERE `member_id` = 2 ORDER BY `status`, `category_id` DESC;

# GROUP BY 如此操作还有可能产生临时表

# 将排序中的 status 和 category_id 位置对调
# 由于 status 使用了索引故不会出现 Using filesort
EXPLAIN SELECT `id`, `member_id` FROM `article` WHERE `member_id` = 2 AND `status` = 0 ORDER BY `category_id`, `status`;

# GROUP BY 实际上是先排序在分组
EXPLAIN SELECT `id`, `member_id` FROM `article` WHERE `member_id` = 2 AND `status` = 0 GROUP BY `category_id`, `status`;

# 由 key_len 可知，复合索引的所有字段全部用到
EXPLAIN SELECT `id`, `member_id` FROM `article` WHERE `member_id` = 2 AND `status` = 0 AND `category_id` > 3;

# 优化后的复合索引
DROP INDEX `idx_mid_status_cid` ON `article`;
CREATE INDEX `idx_mid_cid` ON `article` (`member_id`, `category_id`);

# 复合索引的所有字段全部用到
# 索引的作用：查询和排序
EXPLAIN SELECT `id`, `member_id` FROM `article` WHERE `member_id` = 2 AND `status` < 2 ORDER BY `category_id` DESC LIMIT 1;

LIKE 以通配符开头的模糊查询将不能使用索引，如 LIKE '%ello'。

尽可能使用覆盖索引（即查询列和索引列一致），避免使用 SELECT *。

# intro 没有创建索引，未使用到覆盖索引
EXPLAIN SELECT `id`, `intro` FROM `article` WHERE `intro` LIKE '%a%';

# 创建索引
CREATE INDEX `idx_intro_status` ON `article` (`intro`, `status`);
# 索引失效
SELECT `intro` FROM `article` WHERE `intro` LIKE '%a';

# 查询列和索引列不一致，未使用到覆盖索引
EXPLAIN SELECT `category_id` FROM `article` WHERE `intro` LIKE '%a%';
EXPLAIN SELECT `category_id`, `intro`, `status` FROM `article` WHERE `intro` LIKE '%a%';
EXPLAIN SELECT * FROM `article` WHERE `intro` LIKE '%a%';

# 查询列和索引列一致，使用到了覆盖索引
EXPLAIN SELECT `id`, `intro` FROM `article` WHERE `intro` LIKE '%a%';
EXPLAIN SELECT `intro` FROM `article` WHERE `intro` LIKE '%a%';
EXPLAIN SELECT `intro`, `status` FROM `article` WHERE `intro` LIKE '%a%';

# 由 key_len 可知使用到了复合索引所有字段
EXPLAIN SELECT * FROM `article` WHERE `intro` LIKE 'a%b%' AND `status` = 0;

不要在索引列进行任何操作，如计算、函数、类型转换、IS NULL、IS NOT NULL、OR、不等于判断，否则将导致索引失效转为全表扫描。