2、索引优化分析

mysql性能下降原因

性能下降SQL慢、执行时间长、等待时间长

查询语句写的烂
索引失效
复合关联查询太多join (设计缺陷或不得已的需求)
服务器调优及各个参数设置(缓冲、线程数等)

常见通用的Join查询

sql执行加载顺序

# 手写
SELECT DISTINCT
    < select_list >
FROM
    < left_table > 
< join_type > JOIN < right_table > ON < join_condition >
WHERE 
    < where_condition >
GROUP BY
    < group_by_list >
HAVING 
    < having_condition >
ORDER BY
    < order_by_condition >
LIMIT 
    < limit_number >

# 机读
FROM 
    < left_table >
ON < join_condition >
< join_type > JOIN < right_table >
WHERE 
    < where_condition >
GROUP BY 
    < group_by_list >
HAVING < having_condition >
SELECT DISTINCT 
    < select_list >
ORDER BY 
    < order_by_condition >
LIMIT 
    < limit_number >

fo j w g h sol (佛教温哥华搜了)

mysql七种JOIN理论

索引简介

索引是什么

排好序的快速查找数据结构

一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上
我们平常所说的索引,如果没有特别指明,都是指 B树(多路搜索树,并不一定是二叉的) 结构组织的索引。
其中聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引默认都是使用B+树索引,统称索引。
当然,除了B+树这种类型的索引之外,还有哈希索引(hash index)等。

索引优劣势

优势

类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本
通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗

劣势

实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的。
虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT, UPDATE和DELETE。
（因为更新表时, MySQL不仅要保存数据,而且还要保存索引文件，每次更新、添加索引列的字段,都会调整因为更新所带来的键值变化后的索引信息）
索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句

索引分类和索引命令

索引类型

FULLTEXT

即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。在没有全文索引之前，这样一个查询语句是要进行遍历数据表操作的，可见，在数据量较大时是极其的耗时的。
Innodb引擎在MySql5.6.4版本提供了对全文索引的支持，但对中文全文检索的支持依然不理想，但却确实提供了对英文的全文支持。

HASH

由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。
HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

B-TREE

平衡的多路搜索树，每个节点包含多个键值和子节点指针，设计用于高效管理大量数据的读写操作（尤其适合磁盘存储）。

B+TREE

B-TREE的变种，叶子节点包含所有数据并形成有序链表。

检索原理

初始化介绍

B+树中，每个磁盘块包含数据项（如17、35）和指向子节点的指针（P1、P2、P3）。
指针P1、P2、P3分别指引小于17、介于17-35之间、大于35的磁盘块。
真实数据仅存储于叶子节点（如3、5、9...99），非叶子节点仅存放索引项（如17、35）而不含实际数据。

查找过程

B+树查找数据项29的过程：

第一次I/O：加载根节点（磁盘块1），内存二分查找确定29位于17-35区间，
第二次I/O：加载磁盘块3；再次二分定位到26-30区间，
第三次I/O：加载磁盘块8并命中目标。
3层B+树支持千万级数据仅需3次I/O，而无索引全表扫描需千万次I/O，性能差异显著。

RTREE

RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
相对于BTREE，RTREE的优势在于范围查找。

索引分类

主键索引

它是一种特殊的唯一索引, 不允许有空值

唯一索引

索引列的值必须唯一, 允许有空值

单值索引

即一个索引只包含单个列, 一个表可以有多个单列索引

组合索引

即一个索引包含多个列, 专门用于组合搜索，其效率大于索引合并

全文索引

对文本的内容进行分词，进行搜索, MySql5.6.4版本之前只支持MyISAM

空间索引

MySql5.7版本之后支持, 只支持MyISAM，所在字段的值不能为空值

tips
索引合并: 使用多个单列索引组合搜索
覆盖索引: select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

基础语法

创建索引

写法一

CREATE 命令不能创建主键索引

CREATE UNIQUE INDEX `idx_field1` ON `table1` (`field1`); # 唯一索引
CREATE INDEX `idx_field1` ON `table1` (`field1`); # 创建普通索引
CREATE FULLTEXT INDEX `idx_name` ON `test3` (`name`); # 空间索引
CREATE INDEX `idx_field1_field2` ON `table1` (`field1`, `field2`); # 联合索引

写法二

ALTER TABLE `table1` ADD PRIMARY KEY `index_name` (`field1`); # 主键索引(索引值必须是唯一的,且不能为NULL)
ALTER TABLE `table1` ADD UNIQUE `index_name` (`field1`); # 唯一索引(这条语句创建索引的值必须是唯一的(除了NULL以外, 因为NULL可能会出现多次))
ALTER TABLE `table1` ADD INDEX `index_name` (`field1`); # 普通索引
ALTER TABLE `table1` ADD FULLTEXT `index_name` (`field1`); # 全文索引(只支持MyISAM)
ALTER TABLE `table1` ADD INDEX `index_name` (`field1`, `field2`, `field3`); # 联合索引

查看索引

show index from test1;

(如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上db_name.前缀)

查看表结构

show create table test1 \G

删除索引

drop index address on test1;

建索引情况

哪些情况适合建索引

主键自动建立唯一索引
频繁作为查询条件的字段适合创建索引
查询中与其它表关联的字段,外键关系建立索引
不频繁更新的字段适合创建索引
Where条件里经常用到的字段适合创建索引
单键/组合索引的选择问题, who? (在高并发下倾向创建组合索引)
查询中经常排序的字段,排序字段若通过索引去访问将大大提高排序速度
查询中经常统计或者分组字段

哪些情况不要建索引

表记录太少
经常增删改的表（提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT,UPDATE和DELETE因为更新表时, MysQL不仅要保存数据,还要保存一下索引文件）
数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。

性能分析

MySQL Query Optimizer (查询优化器)

Mysql中有专门负责优化SELECT语句的优化器模块,主要功能:通过计算分析系统中收集到的统计信息,为客户端请求的Query提供他认为最优的执行计划(它认为最优的数据检索方式,但不见得是DBA认为是最优的,这部分最耗费时间)
当客户端向MySQL请求一条Query,命令解析器模块完成请求分类,区别出是SELECT并转发给MySQL Query Optimizer时, MySQL Query Optimizer首先会对整条Query进行优化,处理掉一些常量表达式的预算,直接换算成常量值。并对Query中的查询条件进行简化和转换,如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint(提示)信息(如果有) ,看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划,则会读取所涉及对象的统计信息,根据Query进行写相应的计算分析,然后再得出最后的执行计划

MySQL常见瓶颈

CPU: CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
I\O: 磁盘I\O瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈: top,free,iostat和vmstat来查看系统的性能状态

explain使用

是什么

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

能干嘛

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

怎么玩(explain包含的信息)

EXPLAIN SELECT * FROM `test`;
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----+
|1 |SIMPLE     |test |NULL      |ALL |NULL         |NULL|NULL   |NULL|1   |100     |NULL |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----+

id介绍

select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序

id相同,执行顺序由上至下
id不同,如果是子查询, id的序号会递增, id值越大优先级越高,越先被执行
id相同不同,同时存在

select_type介绍

SIMPLE: 简单的select查询,查询中不包含子查询或者UNION
PRIMARY: 查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY
SUBQUERY: 在SELECT或WHERE列表中包含了子查询
DERIVED: 在FROM列表中包含的子查询被标记为DERIVED(衍生) MySQL会递归执行这些子查询,把结果放在临时表里。
UNION: 若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标认为: DERIVED
UNION RESULT: 从UNION表获取结果的SELECT

table介绍

显示这一行的数据是关于哪张表的

type介绍

显示查询使用了何种类型

访问类型排列

优劣排序

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

常用优劣排序

system > const > eq_ref_ > ref > range > index > all

system：表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也可以忽略不计
const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据所以很快,如将主键置于where列表中, MySQL就能将该查询转换为一个常量
eq_ref：唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref：非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体
range：只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引,般就是在你的where语句中出现了between、 <>、in等的查询,range这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束语另一点,不用扫描全部索引。
index：Full Index Scan, index与ALL区别为index类型只遍历索引树。这通常比ALL快,因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表,但index是从索引中读取的, 而all是从硬盘中读的)
all：Full Table Scan,将遍历全表以找到匹配的行

备注:一般来说,得保证查询至少达到range级别,最好能达到ref。

possible_key介绍

显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用

key介绍

实际使用的索引。如果为NULL,则没有使用索引
查询中若使用了覆盖索引,则该索引仅出现在key列表中

key_len介绍

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确性的情况下,长度越短越好
key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的

ref介绍

显示索引的哪一列被使用于查询,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值

rows介绍

根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数

Extra介绍

Using filesort(文件排序,性能下降)

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MySQL中无法利用索引排序的操作称为"文件排序"

EXPLAIN SELECT * FROM `order_test` ORDER BY `age` ASC, `birth` DESC;
+--+-----------+----------+----------+----+-------------+----+-------+----+----+--------+--------------+
|id|select_type|table     |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra         |
+--+-----------+----------+----------+----+-------------+----+-------+----+----+--------+--------------+
|1 |SIMPLE     |order_test|NULL      |ALL |NULL         |NULL|NULL   |NULL|3   |100     |Using filesort|
+--+-----------+----------+----------+----+-------------+----+-------+----+----+--------+--------------+

Using temporary(使用临时表,性能下降)

使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于order by排序和group by分组查询
官方解释："为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
很明显就是通过where条件一次性检索出来的结果集太大了，内存放不下了，只能通过临时表来辅助处理。"

EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c3`, `c2`;
+--+-----------+------+----------+----+---------------+---------------+-------+-----+----+--------+------------------------------------------------------+
|id|select_type|table |partitions|type|possible_keys  |key            |key_len|ref  |rows|filtered|Extra                                                 |
+--+-----------+------+----------+----+---------------+---------------+-------+-----+----+--------+------------------------------------------------------+
|1 |SIMPLE     |test03|NULL      |ref |idxtest03_c1234|idxtest03_c1234|41     |const|1   |20      |Using index condition; Using temporary; Using filesort|
+--+-----------+------+----------+----+---------------+---------------+-------+-----+----+--------+------------------------------------------------------+

Using index(覆盖索引, 性能提升)

锅盖理论，盖子(索引)必须比锅(查询字段)大

表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!
如果同时出现using where,表明索引被用来执行索引键值的查找;
如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。

覆盖索引(Covering Index) ,也说为索引覆盖。
理解1:
就是select的数据列只用从索引中就能够取得,不必读取数据行, MysQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
理解2:
索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。毕竟索引叶子节点存储了它们的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。

注意: 如果要使用覆盖索引,一定要注意select列表中只取出需要的列,不可select *,因为

锅盖理论
如果将所有字段一起做索引会导致索引文件过大,查询性能下降。

Using where

表明使用了where过滤

Using where with pushed condition

仅用在ndb上。Mysql Cluster用Condition Pushdown优化改善非索引字段和常量之间的直接比较。
condition被pushed down到cluster的数据节点，并在所有数据节点同时估算，把不合条件的列剔除避免网络传输

Using index condition

Using index condition 会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；

(因为MySQL的架构原因，分成了server层和引擎层，才有所谓的“下推”的说法。
所以ICP其实就是实现了index filter技术，将原来的在server层进行的table filter中可以进行index filter的部分，
在引擎层面使用index filter进行处理，不再需要回表进行table filter。

Using index for group-by

数据访问和 Using index 一样，所需数据只须要读取索引，当Query 中使用GROUP BY或DISTINCT 子句时，如果分组字段也在索引中，Extra中的信息就会是 Using index for group-by。注：和Using index一样，只需读取覆盖索引

Using join buffer (Block Nested Loop)

表示使用了连接缓存, 连接没有使用索引, 并且必须使用块嵌套循环算法来进行连接。(常出现在join用的比较多的情况)

EXPLAIN SELECT * FROM `class` INNER JOIN `book` ON `class`.`card` = `book`.`card` INNER JOIN `phone` ON `book`.`card` = `phone`.`card`;
+--+-----------+-----+----------+-----+-------------+----+-------+---------------+----+--------+--------------------------------------------------+
|id|select_type|table|partitions|type |possible_keys|key |key_len|ref            |rows|filtered|Extra                                             |
+--+-----------+-----+----------+-----+-------------+----+-------+---------------+----+--------+--------------------------------------------------+
|1 |SIMPLE     |book |NULL      |index|Y            |Y   |4      |NULL           |1   |100     |Using index                                       |
|1 |SIMPLE     |class|NULL      |ALL  |NULL         |NULL|NULL   |NULL           |8   |12.5    |Using where; Using join buffer (Block Nested Loop)|
|1 |SIMPLE     |phone|NULL      |ref  |Z            |Z   |4      |basic.book.card|1   |100     |Using index                                       |
+--+-----------+-----+----------+-----+-------------+----+-------+---------------+----+--------+--------------------------------------------------+

const row not found

For a query such as SELECT … FROM tbl_name, the table was empty.
(类似于select …. from tbl_name，而表记录为空)

Deleting all rows

For DELETE, some storage engines (such as MyISAM) support a handler method that removes all table rows in a simple and fast way. This Extra value is displayed if the engine uses this optimization.

(对于DELETE，一些存储引擎（如MyISAM）支持一种处理方法，可以简单而快速地删除所有的表行。如果引擎使用此优化，则会显示此额外值)

Distinct

当查询使用了 DISTINCT 关键字时出现，MySQL 需要去重

MySQL is looking for distinct values, so it stops searching for more rows for the current row combination after it has found the first matching row.
(MySQL正在寻找不同的值，因此在找到第一个匹配行后，它将停止搜索当前行组合的更多行)

FirstMatch

The semi-join FirstMatch join shortcutting strategy is used for tbl_name.
(半连接去重执行优化策略，当匹配了第一个值之后立即放弃之后记录的搜索。这为表扫描提供了一个早期退出机制而且还消除了不必要记录的产生)；如下图所示：

半连接：当一张表在另一张表找到匹配的记录之后，半连接（semi-join）返回第一张表中的记录。

与条件连接相反，即使在右节点中找到几条匹配的记录，左节点的表也只会返回一条记录。

另外，右节点的表一条记录也不会返回。半连接通常使用IN或EXISTS 作为连接条件。

- Start temporary, End temporary

表示半连接中使用了DuplicateWeedout策略的临时表，具体实现过程如下图所示：

Full scan on NULL key

This occurs for subquery optimization as a fallback strategy when the optimizer cannot use an index-lookup access method.
(子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用)

LooseScan(m..n)

The semi-join LooseScan strategy is used. m and n are key part numbers.

(利用索引来扫描一个子查询表可以从每个子查询的值群组中选出一个单一的值。

松散扫描(LooseScan)策略采用了分组，子查询中的字段作为一个索引且外部SELECT语句可以与很多的内部SELECT记录相匹配。如此便会有通过索引对记录进行分组的效果。)

Impossible HAVING

The HAVING clause is always false and cannot select any rows.(HAVING子句总是为false，不能选择任何行)

EXPLAIN SELECT * FROM `article` GROUP BY `category_id` HAVING 1 = -1;
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra            |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------------+
|1 |SIMPLE     |NULL |NULL      |NULL|NULL         |NULL|NULL   |NULL|NULL|NULL    |Impossible HAVING|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------------+

Impossible WHERE

The WHERE clause is always false and cannot select any rows.(WHERE子句始终为false，不能选择任何行)

EXPLAIN SELECT * FROM `article` WHERE 1 = -1;
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+----------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra           |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+----------------+
|1 |SIMPLE     |NULL |NULL      |NULL|NULL         |NULL|NULL   |NULL|NULL|NULL    |Impossible WHERE|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+----------------+

Impossible WHERE noticed after reading const tables

MySQL has read all const (and system) tables and notice that the WHERE clause is always false.
(MySQL读取了所有的const和system表，并注意到WHERE子句总是为false)

No matching min/max row

No row satisfies the condition for a query such as SELECT MIN(…) FROM … WHERE condition.
(没有满足SELECT MIN（…）FROM … WHERE查询条件的行，示例中，emp_number最小值为1001，没有满足条件的行)

如果此时将select字段改为其他字段，比如salary，则extra如下显示，使用到ICP优化机制(ICP机制见https://dev.mysql.com/doc/refman/5.7/en/index-condition-pushdown-optimization.html)

no matching row in const table

For a query with a join, there was an empty table or a table with no rows satisfying a unique index condition.
(表为空或者表中根据唯一键查询时没有匹配的行)

No matching rows after partition pruning

For DELETE or UPDATE, the optimizer found nothing to delete or update after partition pruning. It is similar in meaning to Impossible WHERE for SELECT statements.
(对于DELETE或UPDATE，优化器在分区修剪后没有发现任何删除或更新。对于SELECT语句，它与Impossible WHERE的含义相似)

No tables used

The query has no FROM clause, or has a FROM DUAL clause.
(没有FROM子句或者使用DUAL虚拟表)

注：DUAL虚拟表纯粹是为了方便那些要求所有SELECT语句应该有FROM和可能的其他子句的人。 MySQL可能会忽略这些条款。如果没有引用表，MySQL不需要FROM DUAL(https://dev.mysql.com/doc/refman/5.7/en/select.html)

Not exists

MySQL能够对查询执行LEFT JOIN优化，并且在找到与LEFT JOIN条件匹配的一行后，不会在上一行组合中检查此表中的更多行。例如：

SELECT * FROM `t1` LEFT JOIN `t2` ON `t1`.`id` = `t2`.`id` WHERE `t2`.`id` IS NULL;

假设t2.id被定义为NOT NULL。在这种情况下，MySQL会扫描t1，并使用t1.id的值查找t2中的行。

如果MySQL在t2中找到一个匹配的行，它会知道t2.id永远不会为NULL，并且不扫描t2中具有相同id值的其余行。

换句话说，对于t1中的每一行，MySQL只需要在t2中只执行一次查找，而不考虑在t2中实际匹配的行数。

Range checked for each record (index map: N)

MySQL发现没有很好的使用索引，但是发现在前面的表的列值已知之后，可能会使用一些索引。对于上表中的每一行组合，MySQL检查是否可以使用range或index_merge访问方法来检索行。
这不是很快，但比执行没有索引的连接更快。index map N索引的编号从1开始，按照与表的SHOW INDEX所示相同的顺序。
索引映射值N是指示哪些索引是候选的位掩码值。例如，0x19（二进制11001）的值意味着将考虑索引1,4和5。
其中name属性为varchar类型；但是条件给出整数型，涉及到隐式转换。图中t2也没有用到索引，是因为查询之前我将t2中name字段排序规则改为utf8_bin导致的链接字段排序规则不匹配。

- Select tables optimized away

表示优化器已经确定不需要访问表就可以得到结果，通常是因为聚合函数和常量表达式。

在没有group by子句的情况下，使用聚合函数如MIN/MAX的查询，不必等到执行阶段再进行计算。
可以直接访问存储结构(B树或者B+树)的最左侧叶子节点或者最右侧叶子节点即可，这些可以通过index解决。
Select count(*) from table(不包含where等子句)，MyISAM保存了记录的总数，可以直接返回结果，而Innodb需要全表扫描。Query中不能有group by操作;

Skip_open_table, Open_frm_only, Open_full_table

这些值表示适用于INFORMATION_SCHEMA表查询的文件打开优化；
Skip_open_table：表文件不需要打开。信息已经通过扫描数据库目录在查询中实现可用。
Open_frm_only：只需要打开表的.frm文件。
Open_full_table：未优化的信息查找。必须打开.frm，.MYD和.MYI文件。

unique row not found

对于诸如SELECT … FROM tbl_name的查询，没有行满足表上的UNIQUE索引或PRIMARY KEY的条件。

Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)

Block Nested-Loop Join算法：
将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数。

优化器管理参数optimizer_switch中中的block_nested_loop参数控制着BNL是否被用于优化器。

默认条件下是开启，若果设置为off，优化器在选择 join方式的时候会选择NLJ(Nested Loop Join)算法。

Batched Key Access原理：
对于多表join语句，当MySQL使用索引访问第二个join表的时候，使用一个join buffer来收集第一个操作对象生成的相关列值。

BKA构建好key后，批量传给引擎层做索引查找。key是通过MRR接口提交给引擎的（mrr目的是较为顺序）MRR使得查询更有效率，

要使用BKA，必须调整系统参数optimizer_switch的值，batched_key_access设置为on，因为BKA使用了MRR，因此也要打开MRR (参考http://www.cnblogs.com/chenpingzhao/p/6720531.html)。*

Using MRR

使用MRR策略优化表数据读取，仅仅针对二级索引的范围扫描和使用二级索引进行 join 的情况；

过程：先根据where条件中的辅助索引获取辅助索引与主键的集合，再将结果集放在buffer(read_rnd_buffer_size 直到buffer满了)，然后对结果集按照pk_column排序，得到有序的结果集rest_sort。最后利用已经排序过的结果集，访问表中的数据，此时是顺序IO。即MySQL 将根据辅助索引获取的结果集根据主键进行排序，将无序化为有序，可以用主键顺序访问基表，将随机读转化为顺序读，多页数据记录可一次性读入或根据此次的主键范围分次读入，减少IO操作，提高查询效率。

注：MRR原理：Multi-Range Read Optimization，是优化器将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销的一种手段，这对IO-bound类型的SQL语句性能带来极大的提升，适用于range ref eq_ref类型的查询；

Using sort_union(…), Using union(…), Using intersect(…)

这些指示索引扫描如何合并为index_merge连接类型。

参考https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html)

索引合并交叉口访问算法(The Index Merge Intersection Access Algorithm)：

index intersect merge就是多个索引条件扫描得到的结果进行交集运算。

显然在多个索引提交之间是 AND 运算时，才会出现 index intersect merge. 下面两种where条件或者它们的组合时会进行 index intersect merge:
1. 条件使用到复合索引中的所有字段或者左前缀字段;
2. 主键上的任何范围条件。

intersect merge运行方式：多个索引同时扫描，然后结果取交集。如果所有条件字段都是索引字段，使用索引覆盖扫描，无需回表

SELECT * FROM `innodb_table` WHERE `primary_key` < 10 AND `key_col1` = 20;
SELECT * FROM `tbl_name` WHERE (`key1_part1` = 1 AND `key1_part2` = 2)AND `key2` = 2;

索引合并联合访问算法(The Index Merge Union Access Algorithm)：
index union merge就是多个索引条件扫描，对得到的结果进行并集运算，显然是多个条件之间进行的是 OR 运算。

以下几种可能会使用到index merge union:
1. 条件使用到复合索引中的所有字段或者左前缀字段(对单字段索引也适用)
2. 主键上的任何范围条件
3. 任何符合 index intersect merge 的where条件

SELECT * FROM `t1` WHERE `key1` = 1 OR `key2` = 2 OR `key3` = 3;
SELECT * FROM `innodb_table` WHERE (`key1` = 1 AND `key2` = 2)OR (`key3` = 'foo' AND `key4` = 'bar') AND `key5` = 5;

索引合并排序联合访问算法(The Index Merge Sort-Union Access Algorithm):
多个条件扫描进行 OR 运算，但是不符合 index union merge算法的，此时可能会使用 sort_union算法；

SELECT * FROM `tbl_name` WHERE `key_col1` < 10 OR `key_col2` < 20;
SELECT * FROM `tbl_name` WHERE (`key_col1` > 10 OR `key_col2` = 20)AND `nonkey_col` = 30;

热身case

第一行(执行顺序4):

id列为1,表示是union里的第一个select, select_type列的primary表示该查询为外层查询,table列被标记为(derived3),表示查询结果来自一个衍生表,其中derived3中3代表该查询衍生自第三个select查询,即id为3的select。
【select d1.name.....】

第二行(执行顺序2):

id为3,是整个查询中第三个select的一部分。因查询包含在from中,所以为derived。
【select id,namefrom t1 where other_column=''】

第三行(执行顺序3):

select列表中的子查询select_type为subquery,为整个查询中的第二个select。
【select id from t3】

第四行(执行顺序1):
select_type为union,说明第四个select是union里的第二个select,最先执行
【select name,id from t2】

第五行(执行顺序5):

代表从union的临时表中读取行的阶段, table列的(union 1,4)表示用第一个和第四个select的结果进行union操作。
【两个结果union操作】

单表优化实例

建表SQL

DROP TABLE IF EXISTS `article`;
CREATE TABLE `article`
(
    `id`          INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `author_id`   INT(10) UNSIGNED NOT NULL,
    `category_id` INT(10) UNSIGNED NOT NULL,
    `views`       INT(10) UNSIGNED NOT NULL,
    `comments`    INT(10) UNSIGNED NOT NULL,
    `title`       VARBINARY(255)   NOT NULL,
    `content`     TEXT             NOT NULL
) COMMENT = '文章表';
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`)
VALUES (1, 1, 1, 1, '1', '1'),
       (2, 2, 2, 2, '2', '2'),
       (1, 1, 3, 3, '3', '3');

案例

查询category_id为1且comments大于1的情况下,views最多的article_id。

EXPLAIN SELECT `id`, `author_id` FROM `article` WHERE `category_id` = 1 AND `comments` > 1 ORDER BY `views` DESC LIMIT 1;
+--+-----------+-------+----------+----+-------------+----+-------+----+----+--------+---------------------------+
|id|select_type|table  |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra                      |
+--+-----------+-------+----------+----+-------------+----+-------+----+----+--------+---------------------------+
|1 |SIMPLE     |article|NULL      |ALL |NULL         |NULL|NULL   |NULL|3   |33.33   |Using where; Using filesort|
+--+-----------+-------+----------+----+-------------+----+-------+----+----+--------+---------------------------+

结论:

很显然,type是ALL,即最坏的情况。Extra里还出现了Using filesort,也是最坏的情况。优化是必须的。

开始优化:

1.1新建索引

ALTER TABLE `article` ADD INDEX `idx_article_ccv` (`category_id`, `comments`, `views`);

1.2第2次EXPLAIN

EXPLAIN SELECT `id`, `author_id` FROM `article` WHERE `category_id` = 1 AND `comments` > 1 ORDER BY `views` DESC LIMIT 1;
+--+-----------+-------+----------+-----+---------------+---------------+-------+----+----+--------+-------------------------------------+
|id|select_type|table  |partitions|type |possible_keys  |key            |key_len|ref |rows|filtered|Extra                                |
+--+-----------+-------+----------+-----+---------------+---------------+-------+----+----+--------+-------------------------------------+
|1 |SIMPLE     |article|NULL      |range|idx_article_ccv|idx_article_ccv|8      |NULL|1   |100     |Using index condition; Using filesort|
+--+-----------+-------+----------+-----+---------------+---------------+-------+----+----+--------+-------------------------------------+

结论:

type变成了range,这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。
但是我们已经建立了索引,为啥没用呢?
这是因为按照BTree索引的工作原理,
先排序category_id, 如果遇到相同的category_id则再排序comments,
如果遇到相同的comments则再排序views。
当comments字段在联合索引里处于中间位置时,
因comments > 1条件是一个范围值(所谓range),
MySQL无法利用索引再对后面的views部分进行检索,即range类型查询字段后面的素引无效。
1.3 删除第一次建立的素引

DROP INDEX idx_article_ccv ON article;

1.4 第2次新建索引

ALTER TABLE `article` ADD INDEX `idx_article_cv` (`category_id`, `views`);

1.5 第3次EXPLAIN

EXPLAIN SELECT `id`, `author_id` FROM `article` WHERE `category_id` = 1 AND `comments` > 1 ORDER BY `views` DESC LIMIT 1;
+--+-----------+-------+----------+----+--------------+--------------+-------+-----+----+--------+-----------+
|id|select_type|table  |partitions|type|possible_keys |key           |key_len|ref  |rows|filtered|Extra      |
+--+-----------+-------+----------+----+--------------+--------------+-------+-----+----+--------+-----------+
|1 |SIMPLE     |article|NULL      |ref |idx_article_cv|idx_article_cv|4      |const|2   |33.33   |Using where|
+--+-----------+-------+----------+----+--------------+--------------+-------+-----+----+--------+-----------+

结论

可以看到,type变为了ref,Extra中的Using filesort也消失了,结果非常理想。

两表优化实例

建表SQL

DROP TABLE IF EXISTS `class`;
DROP TABLE IF EXISTS `book`;
CREATE TABLE `class`
(
    `id`   INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `card` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB COMMENT '班级表';

CREATE TABLE `book`
(
    `id`   INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `card` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB COMMENT '书籍表';

INSERT INTO `class`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `class`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `class`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `class`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `class`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `class`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `class`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `class`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));

explain分析左联接（type有All,必须优化）

EXPLAIN SELECT * FROM `class` LEFT JOIN `book` ON `class`.`card` = `book`.`card`;
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------------------------------------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra                                             |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------------------------------------------+
|1 |SIMPLE     |class|NULL      |ALL |NULL         |NULL|NULL   |NULL|8   |100     |NULL                                              |
|1 |SIMPLE     |book |NULL      |ALL |NULL         |NULL|NULL   |NULL|1   |100     |Using where; Using join buffer (Block Nested Loop)|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------------------------------------------+

添加索引优化

ALTER TABLE `book` ADD INDEX Y ( `card` );

第2次explain

EXPLAIN SELECT * FROM `class` LEFT JOIN `book` ON `class`.`card` = `book`.`card`;
+--+-----------+-----+----------+----+-------------+----+-------+----------------+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref             |rows|filtered|Extra      |
+--+-----------+-----+----------+----+-------------+----+-------+----------------+----+--------+-----------+
|1 |SIMPLE     |class|NULL      |ALL |NULL         |NULL|NULL   |NULL            |8   |100     |NULL       |
|1 |SIMPLE     |book |NULL      |ref |Y            |Y   |4      |basic.class.card|1   |100     |Using index|
+--+-----------+-----+----------+----+-------------+----+-------+----------------+----+--------+-----------+

可以看到第二行的type变为了ref,rows也变成了优化比较明显。这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行,左边一定都有, 所以右边是我们的关键点,一定需要建立素引。

删除旧索引、添加索引优化

DROP INDEX Y ON `book`;
ALTER TABLE `class` ADD INDEX X ( `card` );

第3次explain

EXPLAIN SELECT * FROM `class` LEFT JOIN `book` ON `class`.`card` = `book`.`card`;
 +--+-----------+-----+----------+-----+-------------+----+-------+----+----+--------+--------------------------------------------------+
|id|select_type|table|partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra                                             |
+--+-----------+-----+----------+-----+-------------+----+-------+----+----+--------+--------------------------------------------------+
|1 |SIMPLE     |class|NULL      |index|NULL         |X   |4      |NULL|8   |100     |Using index                                       |
|1 |SIMPLE     |book |NULL      |ALL  |NULL         |NULL|NULL   |NULL|1   |100     |Using where; Using join buffer (Block Nested Loop)|
+--+-----------+-----+----------+-----+-------------+----+-------+----+----+--------+--------------------------------------------------+

explain分析右连接（理论同左关联）

EXPLAIN SELECT * FROM `class` RIGHT JOIN `book` ON `class`.`card` = `book`.`card`;
+--+-----------+-----+----------+----+-------------+----+-------+---------------+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref            |rows|filtered|Extra      |
+--+-----------+-----+----------+----+-------------+----+-------+---------------+----+--------+-----------+
|1 |SIMPLE     |book |NULL      |ALL |NULL         |NULL|NULL   |NULL           |1   |100     |NULL       |
|1 |SIMPLE     |class|NULL      |ref |X            |X   |4      |basic.book.card|1   |100     |Using index|
+--+-----------+-----+----------+----+-------------+----+-------+---------------+----+--------+-----------+

删除旧索引、添加索引优化

DROP INDEX X ON `class`;
ALTER TABLE `book` ADD INDEX Y ( `card` );

explain分析

EXPLAIN SELECT * FROM `class` RIGHT JOIN `book` ON `class`.`card` = `book`.`card`;
+--+-----------+-----+----------+-----+-------------+----+-------+----+----+--------+--------------------------------------------------+
|id|select_type|table|partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra                                             |
+--+-----------+-----+----------+-----+-------------+----+-------+----+----+--------+--------------------------------------------------+
|1 |SIMPLE     |book |NULL      |index|NULL         |Y   |4      |NULL|1   |100     |Using index                                       |
|1 |SIMPLE     |class|NULL      |ALL  |NULL         |NULL|NULL   |NULL|8   |100     |Using where; Using join buffer (Block Nested Loop)|
+--+-----------+-----+----------+-----+-------------+----+-------+----+----+--------+--------------------------------------------------+

优化较明显。这是因为RIGHT JOIN条件用于确定如何从左表搜索行,右边一定都有,所以左边是我们的关键点,一定需要建立索引。

索引口诀：左联加右，右联加左

三表优化实例

建表SQL

DROP TABLE IF EXISTS `phone`;
CREATE TABLE `phone`
(
    `id`   INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `card` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = INNODB COMMENT '电话表';

INSERT INTO `phone`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `phone`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `phone`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `phone`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `phone`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `phone`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `phone`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `phone`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `phone`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `phone`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `phone`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `phone`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));
INSERT INTO `phone`(`card`) VALUES (FLOOR(1 + (RAND() * 20)));

案例

添加索引

ALTER TABLE `phone` ADD INDEX Z ( `card` );
ALTER TABLE `book` ADD INDEX Y ( `card` );

explain分析

EXPLAIN SELECT * FROM `class` LEFT JOIN `book` ON `class`.`card` = `book`.`card` LEFT JOIN `phone` ON `book`.`card` = `phone`.`card`;
+--+-----------+-----+----------+----+-------------+----+-------+----------------+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref             |rows|filtered|Extra      |
+--+-----------+-----+----------+----+-------------+----+-------+----------------+----+--------+-----------+
|1 |SIMPLE     |class|NULL      |ALL |NULL         |NULL|NULL   |NULL            |8   |100     |NULL       |
|1 |SIMPLE     |book |NULL      |ref |Y            |Y   |4      |basic.class.card|1   |100     |Using index|
|1 |SIMPLE     |phone|NULL      |ref |Z            |Z   |4      |basic.book.card |1   |100     |Using index|
+--+-----------+-----+----------+----+-------------+----+-------+----------------+----+--------+-----------+

【结论】

尽可能减少Join语句中的NestedLoop的循环总次数：“永远用小结果集驱动大的结果集”。
优先优化NestedLoop的内层循环;
保证Join语句中被驱动表上Join条件字段已经被索引；
当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下,不要太吝惜JoinBuffer(联表缓存)的设置;

索引优化

建表SQL

DROP TABLE IF EXISTS `staffs`;
CREATE TABLE `staffs`
(
    `id`       INT PRIMARY KEY AUTO_INCREMENT,
    `name`     VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
    `age`      INT         NOT NULL DEFAULT 0 COMMENT '年龄',
    `pos`      VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
    `add_time` TIMESTAMP            DEFAULT NULL COMMENT '入职时间'
) CHARSET `utf8` COMMENT '员工记录表';

INSERT INTO `staffs`(`name`, `age`, `pos`, `add_time`) VALUES ('z3', 22, 'manager', NOW());
INSERT INTO `staffs`(`name`, `age`, `pos`, `add_time`) VALUES ('July', 23, 'dev', NOW());
INSERT INTO `staffs`(`name`, `age`, `pos`, `add_time`) VALUES ('2000', 23, 'dev', NOW());

ALTER TABLE `staffs` ADD INDEX `idx_staffs_nameAgePos` (`name`, `age`, `pos`);

索引失效情况(应该避免)

全值匹配我最爱
最佳左前缀法则(联合索引,从索引的最左前列开始并且不跳过索引中的列)
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换), 会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列(范围之后全失效)
尽量使用覆盖索引,减少select * (锅盖理论)
mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
is null, is not null也无法使用索引
like以通配符开头('%ab..)mysql索引失效会变成全表扫描的操作;用覆盖索引解决like '%字符串%'时索引失效的情况
字符串不加单引号，索引失效(重罪)
少用or,用它来连接时会索引失效
全值匹配我最爱

EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'July' AND `age` = 23 AND `pos` = 'dec';

最佳左前缀法则(联合索引,从索引的最左前列开始并且不跳过索引中的列)
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换), 会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列(范围之后全失效)
注:name用于查询，age用于排序
尽量使用覆盖索引,减少select * 会导致索引失效(锅盖理论)

正确使用覆盖索引方式
mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
is null, is not null也无法使用索引
like以通配符开头('%ab..)mysql索引失效会变成全表扫描的操作;

正确使用like索引(LIKE百分写最右，与范围查询的区别在于，左边是定值)

用覆盖索引解决like '%字符串%'时索引失效的情况
字符串不加单引号索引失效(重罪)
少用or,用它来连接时会索引失效

索引优化总结

小总结

口诀

全带索覆不字(七点十分不在)
1;;全值匹配我最爱, 最左前缀要常在
带头大哥不能散, 中间兄弟不能断
索引列上不计算, 范围之后全完蛋
覆盖索引不写星, like 百分右边停
不等空值还有or, 索引失效要思过
字符引号不能丢, 索引失效要被揪

联合索引的妙用

查询方式	性能排名	关键原因
JOIN 关联	Top 1	1;;单次网络请求，充分利用索引，适合报表和列表。
IN (Row Value)	Top 2	预载入场景下最均衡，索引匹配效率高。
OR 嵌套	Bottom	语句复杂，解析开销大，记录多时易失效。

1. INNER JOIN 查询

如果你只需要一次性获取关联数据，JOIN 通常是首选。

SELECT o.*, i.* FROM `orders` o 
INNER JOIN `items` i ON i.`shop_id` = o.`shop_id` AND i.`order_no` = o.`order_no`
WHERE o.`user_id` = 100;

为什么快：
- 减少往返： 只需要与数据库进行一次 1;;网络I/O 交互「1;;磁盘I/O视情况而定」。
- 优化器介入： 数据库优化器可以根据统计信息，决定是以 orders 驱动 items 还是反之。
- 索引利用率： 联合外键在 ON 条件中能完美触发覆盖索引，减少回表。

2. 行值表达式 (Row Value Expressions)

当你必须使用 with()（例如为了方便模型对象操作）时，这种格式性能最优。

SELECT * FROM `items` WHERE (`shop_id`, `order_no`) IN ((1, 'A01'), (1, 'A02'));

为什么快：
- 语义清晰： 数据库能直接将整个==1;;“元组”==与联合索引进行匹配。
- 执行计划优化： 相比于大量的 OR 条件，IN ((...)) 结构更易于被数据库解析为范围扫描（Range Scan），在索引树中定位速度极快。

3. 多组 OR 嵌套

这是早期版本或某些数据库驱动在处理联合外键预载入时的默认行为。

SELECT * FROM `items` WHERE (`shop_id` = 1 AND `order_no` = 'A01') OR (`shop_id` = 1 AND `order_no` = 'A02')...

缺点： 当父表记录较多（如 500 条）时，SQL 语句会变得极其冗长，解析成本升高，且容易导致数据库放弃使用索引而转向全表扫描。

核心建议

索引是前提： 无论 SQL 多完美，如果 items 表没有建立索引INDEX(shop_id, order_no)，以上查询都会变慢。
控制数量： 使用 with() 预载入时，尽量通过 limit 或分页控制父表数量，避免 IN 后面的参数过多。
使用 explain： TP 生成 SQL 后，务必在控制台执行 EXPLAIN 查看 key 这一列是否准确命中了你的联合索引。

索引面试题分析

面试题讲解

定值、范围还是排序,一般order by是给个范围
group by基本上都需要进行排序,会有临时表产生(先排序后分组)

一般性建议

单键索引:

尽量选择针对当前query过滤性更好的索引

组合索引：

当前Query中过滤性最好的字段在索引字段顺序中。
位置越靠左越好
尽量选择可以能够包含当前query中的where字句中更多字段的索引

尽可能通过分析统计信息(explain)和调整query的写法来达到选择合适索引的目的

建表语句

DROP TABLE IF EXISTS `test03`;
CREATE TABLE `test03`
(
    `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    `c1` CHAR(10),
    `c2` CHAR(10),
    `c3` CHAR(10),
    `c4` CHAR(10),
    `c5` CHAR(10)
) COMMENT '测试3';
INSERT INTO `test03` (`c1`, `c2`, `c3`, `c4`, `c5`) VALUES ('a1', 'a2', 'a3', 'a4', 'a5');
INSERT INTO `test03` (`c1`, `c2`, `c3`, `c4`, `c5`) VALUES ('b1', 'b2', 'b3', 'b4', 'b5');
INSERT INTO `test03` (`c1`, `c2`, `c3`, `c4`, `c5`) VALUES ('c1', 'c2', 'c3', 'c4', 'c5');
INSERT INTO `test03` (`c1`, `c2`, `c3`, `c4`, `c5`) VALUES ('d1', 'd2', 'd3', 'd4', 'd5');
INSERT INTO `test03` (`c1`, `c2`, `c3`, `c4`, `c5`) VALUES ('e1', 'e2', 'e3', 'e4', 'e5');
# 建索引、查看索引
CREATE INDEX `idxtest03_c1234` ON `test03` (`c1`, `c2`, `c3`, `c4`);
SHOW INDEX FROM `test03`;

问题:

我们创建了复合索引idx_test03_c1234,根据以下SQL分析下索引使用情况?
注:MySql8版本以上，Extra有所区别(order by会出现文件排序)

eg1

EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' AND `c3` = 'a3';
+--+-----------+------+----------+----+---------------+---------------+-------+-----------------------+----+--------+-----+
|id|select_type|table |partitions|type|possible_keys  |key            |key_len|ref                    |rows|filtered|Extra|
+--+-----------+------+----------+----+---------------+---------------+-------+-----------------------+----+--------+-----+
|1 |SIMPLE     |test03|NULL      |ref |idxtest03_c1234|idxtest03_c1234|164    |const,const,const,const|1   |100     |NULL |
+--+-----------+------+----------+----+---------------+---------------+-------+-----------------------+----+--------+-----+

查询：c1、c2、c3、c4
结论：常量情况mysql优化器会优化sql语句，可以不严格按照建索引时的顺序，

eg2

EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` > 'a3' AND `c4` = 'a4';
+--+-----------+------+----------+-----+---------------+---------------+-------+----+----+--------+---------------------+
|id|select_type|table |partitions|type |possible_keys  |key            |key_len|ref |rows|filtered|Extra                |
+--+-----------+------+----------+-----+---------------+---------------+-------+----+----+--------+---------------------+
|1 |SIMPLE     |test03|NULL      |range|idxtest03_c1234|idxtest03_c1234|123    |NULL|1   |20      |Using index condition|
+--+-----------+------+----------+-----+---------------+---------------+-------+----+----+--------+---------------------+

查询：c1、c2
排序：c3
结论：范围之后全完蛋，c4失效

eg3

EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` > 'a4' AND `c3` = 'a3';
+--+-----------+------+----------+-----+---------------+---------------+-------+----+----+--------+---------------------+
|id|select_type|table |partitions|type |possible_keys  |key            |key_len|ref |rows|filtered|Extra                |
+--+-----------+------+----------+-----+---------------+---------------+-------+----+----+--------+---------------------+
|1 |SIMPLE     |test03|NULL      |range|idxtest03_c1234|idxtest03_c1234|164    |NULL|1   |100     |Using index condition|
+--+-----------+------+----------+-----+---------------+---------------+-------+----+----+--------+---------------------+

查询：c1、c2、c3、c4
结论：mysql进行自动优化sql语句,因c4范围查询后无索引字段,所以不会导致任何索引失效

eg4

EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' ORDER BY `c3`;
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+---------------------+
|id|select_type|table |partitions|type|possible_keys  |key            |key_len|ref        |rows|filtered|Extra                |
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+---------------------+
|1 |SIMPLE     |test03|NULL      |ref |idxtest03_c1234|idxtest03_c1234|82     |const,const|1   |20      |Using index condition|
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+---------------------+

查询：c1、c2
排序：c3

eg5

EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c3`;
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+---------------------+
|id|select_type|table |partitions|type|possible_keys  |key            |key_len|ref        |rows|filtered|Extra                |
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+---------------------+
|1 |SIMPLE     |test03|NULL      |ref |idxtest03_c1234|idxtest03_c1234|82     |const,const|1   |100     |Using index condition|
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+---------------------+

查询：c1、c2
排序：c3

eg6

EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c4`;
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+-------------------------------------+
|id|select_type|table |partitions|type|possible_keys  |key            |key_len|ref        |rows|filtered|Extra                                |
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+-------------------------------------+
|1 |SIMPLE     |test03|NULL      |ref |idxtest03_c1234|idxtest03_c1234|82     |const,const|1   |100     |Using index condition; Using filesort|
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+-------------------------------------+

查询：c1、c2
结论：使用order by 没有按照索引建立顺序，产生文件排序

eg7

EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c2`, `c3`;
+--+-----------+------+----------+----+---------------+---------------+-------+-----+----+--------+----------------------------------+
|id|select_type|table |partitions|type|possible_keys  |key            |key_len|ref  |rows|filtered|Extra                             |
+--+-----------+------+----------+----+---------------+---------------+-------+-----+----+--------+----------------------------------+
|1 |SIMPLE     |test03|NULL      |ref |idxtest03_c1234|idxtest03_c1234|41     |const|1   |20      |Using index condition; Using where|
+--+-----------+------+----------+----+---------------+---------------+-------+-----+----+--------+----------------------------------+

查询：c1
排序：c2、c3

eg8

EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c2`, `c3`;
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+---------------------+
|id|select_type|table |partitions|type|possible_keys  |key            |key_len|ref        |rows|filtered|Extra                |
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+---------------------+
|1 |SIMPLE     |test03|NULL      |ref |idxtest03_c1234|idxtest03_c1234|82     |const,const|1   |100     |Using index condition|
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+---------------------+

查询：c1、c2,
排序：c3

eg9

EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY `c2`, `c3`;
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+----------------------------------+
|id|select_type|table |partitions|type|possible_keys  |key            |key_len|ref        |rows|filtered|Extra                             |
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+----------------------------------+
|1 |SIMPLE     |test03|NULL      |ref |idxtest03_c1234|idxtest03_c1234|82     |const,const|1   |20      |Using index condition; Using where|
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+----------------------------------+

查询：c1、c2,
排序：c3

eg10

EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY `c3`, `c2`;
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+----------------------------------+
|id|select_type|table |partitions|type|possible_keys  |key            |key_len|ref        |rows|filtered|Extra                             |
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+----------------------------------+
|1 |SIMPLE     |test03|NULL      |ref |idxtest03_c1234|idxtest03_c1234|82     |const,const|1   |20      |Using index condition; Using where|
+--+-----------+------+----------+----+---------------+---------------+-------+-----------+----+--------+----------------------------------+

结论：a1 a2 用于查询，c3用于排序(之所以没产生文件排序，是因为c2索引已经在它排序前固定了值)

eg11

EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c3`, `c2`;
+--+-----------+------+----------+----+---------------+---------------+-------+-----+----+--------+--------------------------------------------------+
|id|select_type|table |partitions|type|possible_keys  |key            |key_len|ref  |rows|filtered|Extra                                             |
+--+-----------+------+----------+----+---------------+---------------+-------+-----+----+--------+--------------------------------------------------+
|1 |SIMPLE     |test03|NULL      |ref |idxtest03_c1234|idxtest03_c1234|41     |const|1   |20      |Using index condition; Using where; Using filesort|
+--+-----------+------+----------+----+---------------+---------------+-------+-----+----+--------+--------------------------------------------------+

结论：order by 没有按照索引建立顺序，产生文件排序

eg12

EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c2`, `c3`;
+--+-----------+------+----------+----+---------------+---------------+-------+-----+----+--------+---------------------+
|id|select_type|table |partitions|type|possible_keys  |key            |key_len|ref  |rows|filtered|Extra                |
+--+-----------+------+----------+----+---------------+---------------+-------+-----+----+--------+---------------------+
|1 |SIMPLE     |test03|NULL      |ref |idxtest03_c1234|idxtest03_c1234|41     |const|1   |20      |Using index condition|
+--+-----------+------+----------+----+---------------+---------------+-------+-----+----+--------+---------------------+

查询：c1
排序分组：c2、c3

eg13

EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c3`, `c2`;
+--+-----------+------+----------+----+---------------+---------------+-------+-----+----+--------+------------------------------------------------------+
|id|select_type|table |partitions|type|possible_keys  |key            |key_len|ref  |rows|filtered|Extra                                                 |
+--+-----------+------+----------+----+---------------+---------------+-------+-----+----+--------+------------------------------------------------------+
|1 |SIMPLE     |test03|NULL      |ref |idxtest03_c1234|idxtest03_c1234|41     |const|1   |20      |Using index condition; Using temporary; Using filesort|
+--+-----------+------+----------+----+---------------+---------------+-------+-----+----+--------+------------------------------------------------------+

结论：group by 不满足最佳左前缀原则，产生文件排序、临时表(十死无生)