- B+树索引
在谈到MySQL时，默认使用的存储引擎为innodb。而innodb组织数据的方式为B+树，即表就是索引，索引就是表。准确来说表是主键索引所在的B+数。
简单介绍一下B+树
- B+树是一棵M叉的平衡树，由根节点、分支节点和叶子节点组成。
- 根节点和分支节点不存储具体的值，只存储索引的键。
- 叶子节点存储键和值，键位索引字段，如果是主键索引，则值为除主键以外的所有字段值，如果是二级索引，则值为主键。
- 从根节点到任何一个叶子节点的距离都是一样。
例如有如下表
主键索引大致结构如下
二级索引大致结构如下
- sql执行经历的组件
- 连接器 负责连接管理，只有跟服务器建立了连接才能向服务器发送sql
- 查询缓存 如果待执行的sql之前已经执行过，则直接从查询缓存中返回sql执行结果，从而避免了后续的执行步骤。5.7查询缓存默认为关闭状态。8.0直接从代码层面删除了查询缓存
- 分析器 对sql语句进行语法和语义分析，如果没有错误则会生成一颗语法树
- 优化器 对上一步生成的语法树进行解析，一条sql可能有多种不同的执行路径，从中选择一个最佳的路径作为执行计划
- 执行器 执行器根据优化器生成的执行计划执行并返回执行结果
- 执行计划
执行计划描述了如何从存储引擎获取数据，表之间如何做关联，以及如何过滤数据等等。要获取执行计划可以通过DESC SQL或EXPLAIN SQL命令
mysql> desc select * from emps e where e.emp_no desc format=json select * from emps e where e.emp_no desc format=tree select * from emps e where e.emp_no Filter: (e.emp_no Table scan on e (cost=30956.70 rows=299637)
|
+-----------------------------------------------------------------------------------------------------------------+
上面描述的三种执行计划并非实际的执行计划，而是一个预估的，从8.0.18开始，添加了analyze关键字，会真正的执行sql，然后显示执行计划，因此会显示真实的资源消耗。添加了analyze的执行计划默认为tree格式，不支持表格形式。
mysql> desc analyze select * from emps e where e.emp_no Filter: (e.emp_no Table scan on e (cost=30956.70 rows=299637) (actual time=5.621..916.440 rows=300024 loops=1)
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.93 sec)
- 执行计划字段解释
id:查询对应的ID。如果一个查询中有多个select语句，每个select都会分配一个ID。
select_type:查询的类型。
+-------------------------------------------------------------------------------------------------------------------------------------+
|select_type | Meaning
+-------------------------------------------------------------------------------------------------------------------------------------+
|SIMPLE | Simple SELECT (not using UNION or subqueries)
|PRIMARY | Outermost SELECT
|UNION | later SELECT statement in a UNION
|DEPENDENT UNION | Second or later SELECT statement in a UNION, dependent on outer query
|UNION RESULT | Result of a UNION.
|SUBQUERY | First SELECT in subquery
|DEPENDENT SUBQUERY | First SELECT in subquery, dependent on outer query
|DERIVED | Derived table
|DEPENDENT DERIVED | Derived table dependent on another table
|MATERIALIZED | Materialized subquery
|UNCACHEABLE SUBQUERY | A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
|UNCACHEABLE UNION | The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)
+--------------------------------------------------------------------------------------------------------------------------------------+
table:查询对应的表的别名。可以是如下值:
表名或别名
partitions:如果是分区表，显示匹配的分区。
type:表的访问类型。
system:表只有一行，并且是系统表。
const:表中仅有唯一的一行匹配，常见于使用主键或唯一索引等值比较常量或常量表达式。
eq_ref:仅匹配表中的一行。表连接时使用索引的所有部分进行匹配。索引是主键或非空唯一索引。
ref:读取匹配的所有行。使用了最左前缀或者非唯一索引的等值比较。
fulltext:使用了全文索引。
ref_or_null:类似ref类型。但是会查找null值。常见于需要匹配null值的场景。
index_merge:使用了索引合并。
unique_subquery:子查询中使用主键。value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery:同unique_subquery。但子查询中使用的是非唯一索引。value IN (SELECT key_column FROM single_table WHERE some_expr)。
range:使用了索引，并且是范围查询。
index:使用索引进行全表扫描。仅在如下两种情况下发生。
使用了覆盖索引。Extra列会显示Using index
避免排序。
ALL:全表扫描。
possible_keys:可能用到的索引。
key:实际用到的索引。
key_len:用到的索引长度。
key_len的计算规则:
+ 一般地，key_len等于索引列类型字节长度，例如tinyint类型为1字节，int类型为4字节，bigint为8字节。
+ 如果是字符串类型，还需要同时考虑字符集因素（latin1为1字节/gbk为2字节/utf8为3字节/utf8mb4为4字节），例如：CHAR(30) UTF8，则key_len至少是90字节
+ 如果是日期时间型，还需要考虑精度值
+ 若该列类型定义时允许NULL，还需要再加1字节。
+ 若该列类型为变长类型，例如 VARCHAR，还需要再加2字节来存储该变长列的实际长度。
ref:显示哪些列或常量与键列中指定的索引进行比较，以便从表中选择行。
rows:预估的读取的行数。
filtered:where过滤掉的数据的百分比。
Extra:额外的信息。
Backward index scan
Child of 'table' pushed join@1
const row not found
Deleting all rows
Distinct
FirstMatch(tbl_name)
Full scan on NULL key
Impossible HAVING
Impossible WHERE
Impossible WHERE noticed after reading const tables
LooseScan(m..n)
No matching min/max row
no matching row in const table
No matching rows after partition pruning
No tables used
Not exists
Plan isn't ready yet
Range checked for each record (index map: N)
Recursive
Rematerialize
Scanned N databases
Select tables optimized away
Skip_open_table, Open_frm_only, Open_full_table
Start temporary, End temporary
unique row not found
Using filesort
Using index
Using index condition
Using index for group-by
Using index for skip scan
Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access), Using join buffer (hash join)
Using MRR
Using sort_union(...), Using union(...), Using intersect(...)
Using temporary
Using where
Using where with pushed condition
Zero limit
